Simple Grouping of Data in Excel Using Open XML SDK 2.0

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";