Data Export to Excel and Create Group Report in .NET

Then, we need insert the child table information into worksheet and together with master table information to form a group. We can use the code below to insert child data information and design child data information style.

for (int i = 0, count = dataTable.Rows.Count; i < count; i++, childDataInSheetRowIndex++)
{
DataRow parentRow = dataTable.Rows[i];
DataRow[] childRows = parentRow.GetChildRows(relation);
subDataTable.Rows.Clear();
foreach (DataRow row in childRows)
{
subDataTable.Rows.Add(row.ItemArray);
}

sheet.InsertRow(childDataInSheetRowIndex, childRows.Length + 1);
sheet.InsertDataTable(subDataTable, true, childDataInSheetRowIndex, 1, -1, -1, childTableColumns, false);
sheet.GroupByRows(childDataInSheetRowIndex, childDataInSheetRowIndex + childRows.Length, true);

//set the sheet style
sheet.Range[childDataInSheetRowIndex-1, 1, childDataInSheetRowIndex-1, exportedColumns.Length].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range[childDataInSheetRowIndex -1, 1, childDataInSheetRowIndex-1, exportedColumns.Length].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range[childDataInSheetRowIndex-1, 1, childDataInSheetRowIndex-1, exportedColumns.Length].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
sheet.Range[childDataInSheetRowIndex, 1, childDataInSheetRowIndex, childTableColumns.Length].Style.Color = Color.Yellow;
sheet.Range[childDataInSheetRowIndex + 1, 1, childDataInSheetRowIndex + childRows.Length, childTableColumns.Length].Style.Color = Color.GreenYellow;
sheet.Range[childDataInSheetRowIndex, 1, childDataInSheetRowIndex + childRows.Length, childTableColumns.Length].Style.Borders.LineStyle = LineStyleType.Thin;
sheet.Range[childDataInSheetRowIndex, 1, childDataInSheetRowIndex + childRows.Length, childTableColumns.Length].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
sheet.Range[childDataInSheetRowIndex, 1, childDataInSheetRowIndex + childRows.Length, childTableColumns.Length].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;

childDataInSheetRowIndex = childDataInSheetRowIndex + childRows.Length + 1;

3. Remove Columns Generated by System

When load XML file, system will automatically add a column for both Master and Child tables. We need remove them.

Remove automatically generated column from Master table:

DataColumn[] exportedColumns = GetAvailableColumns(dataTable, relation.ParentColumns);

Remove automatically generated column from Child table:

DataColumn[] childTableColumns = GetAvailableColumns(subDataTable, relation.ChildColumns);

Method:

private static DataColumn[] GetAvailableColumns(DataTable dataTable, DataColumn[] dynamicalColumns)
{
List<String> dynamicalColumnList
= new List<String>();
foreach (DataColumn column in dynamicalColumns)
{
dynamicalColumnList.Add(column.ColumnName);
}
List<DataColumn> exportedColumnList = new List<DataColumn>();
foreach (DataColumn column in dataTable.Columns)
{
if (!dynamicalColumnList.Contains(column.ColumnName))
{
exportedColumnList.Add(column);
}
}
return exportedColumnList.ToArray();
}

Now, we've exported all the information from XML to Excel.

Output Effective Screenshot:



Full Demo Code attached

Conclusion

This solution is very flexible for most of users. We can design style by ourselves. And furthermore, it's available for different dataset. With the demo above, this solution can be also very fast. When we have similar files need to be exported data information, we can just replace the local XML file and let the program do the rest of job.

Note: This demo is created through Spire.XLS

Good News

Spire.XLS Store, E-iceblue luanches a Christmas Promation(5% - 25% off). Click here to learn more.

Merry Chirstmas and Happy New Year