///
<summary>
/// To create
Document
///
</summary>
///
<param name="filePath"></param>
public void
CreatePackage(string filePath)
{
//To create package
using (SpreadsheetDocument package =
SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
//To create the sheet
WorkbookPart workbookPart1 = package.AddWorkbookPart();
GenerateWorkbookPart1Content(workbookPart1);
//To add the Cell Numbers
WorksheetPart worksheetPart1 = workbookPart1.AddNewPart<WorksheetPart>("rId1");
GenerateWorksheetPart1Content(worksheetPart1, mainPerson);
//To add the Actual data
SharedStringTablePart sharedStringTablePart1 =
workbookPart1.AddNewPart<SharedStringTablePart>("rId4");
GenerateSharedStringTablePart1Content(sharedStringTablePart1,
mainPerson);
//To store the matadata
SetPackageProperties(package);
}
}
To create the Sheet
///
<summary>
/// To Create
Sheet
///
</summary>
///
<param name="workbookPart1"></param>
private void
GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name =
"Sheet1", SheetId = (UInt32Value)1U, Id =
"rId1" };
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
///
<summary>
/// Create the Row
With Formating
///
</summary>
///
<param name="rowIndex"></param>
///
<param name="Outline"></param>
///
<param name="row"></param>
///
<returns></returns>
private static
Row CreateRowWithFormateAndStyle(UInt32
rowIndex, bool Outline, Row row)
{
if (rowIndex == 1)
{
row = new Row() { RowIndex =
(UInt32Value)rowIndex, Spans = new ListValue<StringValue>()
{ InnerText = "1:3" }, DyDescent = 0.25D };//Simple
row
}
else
{
if (Outline)
{
row = new Row() { RowIndex =
(UInt32Value)rowIndex, Spans = new ListValue<StringValue>()
{ InnerText = "1:3" }, DyDescent = 0.25D,
Collapsed = true };//To
give expander
}
else
{
row = new Row() { RowIndex =
(UInt32Value)rowIndex, Spans = new ListValue<StringValue>()
{ InnerText = "1:3" }, DyDescent = 0.25D,
OutlineLevel = 1, Hidden = true, };//To
hide the Row
}
}
return row;
}
Here we are creating the Simple Header Row and then Row with Expander or row
with Outline as per the requirements.
To store the Cell Value
private static
void StoreCellValues(UInt32
rowIndex, bool Outline,
int collectioncount, Row row, ref
int tempCellValue, ref
int val, ref
bool result, List<string>
subTotalColumnArray)
{
foreach (var
item in subTotalColumnArray)
{
string cellRef = item + rowIndex;
if (Outline ==
true && rowIndex > 1)
{
CreateCellandAppendInRow(row, cellRef, tempCellValue);
tempCellValue = tempCellValue + collectioncount;
result = true;
}
else
{
if (cellValue >= 3)//3
means number columns if column counts increse specify that count here
{
CreateCellandAppendInRow(row, cellRef, tempCellValue);
tempCellValue = tempCellValue + collectioncount;
result = true;
}
else
{
CreateCellandAppendInRow(row, cellRef, val);
cellValue = cellValue + 1;
val = cellValue;
result = false;
}
}
}
if (result)
cellValue = cellValue + 1;
}
Now we need to create a cell and add that cell value into it; to do that we need
to write the followng code:
private static
void CreateCellandAppendInRow(Row row1,
string cellRef, int
cellValue, Nullable<UInt32>
styleIndex = null)
{
Cell cell1 = new Cell() { CellReference
= cellRef, DataType = CellValues.SharedString };
CellValue cellValue1 = new CellValue();
cellValue1.Text = Convert.ToString(cellValue);
if (styleIndex !=
null)
cell1.StyleIndex = styleIndex;
cell1.Append(cellValue1);
row1.Append(cell1);
}
This will create the cell and add the value to that cell and append that cell to
the particular row.
To insert Data Column Wise
///
<summary>
/// To store the
Data in excel Column Wise
///
</summary>
///
<param name="sharedStringTablePart1"></param>
///
<param name="persons"></param>
private void
GenerateSharedStringTablePart1Content(SharedStringTablePart
sharedStringTablePart1, List<Mainperson>
persons)
{
SharedStringTable sharedStringTable1 = new
SharedStringTable();
//To give Header values
AddSheredString(sharedStringTable1, "Date");
AddSheredString(sharedStringTable1, "FirstName");
AddSheredString(sharedStringTable1, "LastName");
//TO store values Column Wise
foreach (var
item in persons)//First
Column
{
AddSheredString(sharedStringTable1, item.Date);
foreach (var
item1 in item.person)
{
AddSheredString(sharedStringTable1,
"");
}
}
foreach (var
item in persons)//Second
Column
{
AddSheredString(sharedStringTable1, "");
foreach (var
item1 in item.person)
{
AddSheredString(sharedStringTable1, item1.FName);
}
}
foreach (var
item in persons)//Third
Column
{
AddSheredString(sharedStringTable1, item.Total);
foreach (var
item1 in item.person)
{
AddSheredString(sharedStringTable1, item1.LName);
}
}
//Add the data into the sharedStringTable
sharedStringTablePart1.SharedStringTable = sharedStringTable1;
}
Now we can add the metadata of the Excel file:
private void
SetPackageProperties(OpenXmlPackage document)
{
document.PackageProperties.Creator = "Author
Name";
document.PackageProperties.Created =
DateTime.Now;
document.PackageProperties.Modified =
DateTime.Now;
document.PackageProperties.LastModifiedBy =
"Author Name";