Data Export to Excel and Create Group Report in .NET

Article

Background

In our normal working days, we may need export data from database into Excel. During or after exporting, we may have requirements on style settings to have the output Excel worksheet with good appearance for that readers can easily get data information, such as create a group report. Suppose we have an XML file with "Customers" and "Orders" information. One customer has multiple orders. Now, we need export the information from XML to Excel with Dataset Master-details for C#/VB.NET.

View Video on YouTube:

http://youtu.be/TbKHAW7RrQ0http://youtu.be/TbKHAW7RrQ0

Tool Introduction

A professional Excel component, Spire.XLS offers a solution which can help us export the data information into Excel file with Master-details setting. Spire.XLS is a professional Excel component which enables developers/programmers fast generate, read, write and modify Excel document for .NET and Silverlight. It supports C#, VB.NET, ASP.NET, ASP.NET MVC and Silverlight.

Steps and Details

By using Spire.XLS, there are only 4 steps to finish the job. Table of content:

Step 1 Read XML File
Step 2 Create Excel Worksheet
Step 3 Import Data into Excel Worksheet
1. Import Master Table
2. Import Child Table
3. Remove Columns Generated by System
Step 4 Save output information into Excel Worksheet

Step 1:

Read XML file. Use Dataset read XML file information from your local PC.

DataSet ds = new DataSet();
ds.ReadXml(@"..\..\Orders.xml");

Step 2:

Create a new Excel worksheet for storing data after exporting.

Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];

Step 3:

Import data information from XML to Excel worksheet. (Details are below)

Step 4:

Save output information into Excel worksheet

workbook.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("sample.xlsx");

Importing process is the core part for this solution. "Orders" information and "Customers" information are relative. It's obviously that "Orders" information is part of "Customers" information. So when we read XML information as DataTable, "orders" information is Child Table, and "Customers" information is Master Table.

1. Import Master Table

Use the following code can insert master table into Excel worksheet.

sheet.InsertDataTable(dataTable, true, 1, 1, -1, -1, exportedColumns, false);

Spire.XLS enables users to design output information. For example, we can design the borders, font and color at will.

            sheet.Range[1, 1, 1, exportedColumns.Length].Style.Borders.LineStyle = LineStyleType.Thin;
            sheet.Range[1, 1, 1, exportedColumns.Length].Borders[BordersLineType.DiagonalDown].LineStyle = LineStyleType.None;
            sheet.Range[1, 1, 1, exportedColumns.Length].Borders[BordersLineType.DiagonalUp].LineStyle = LineStyleType.None;
            sheet.Range[1, 1, 1, exportedColumns.Length].Style.Color = Color.DeepSkyBlue;
            sheet.Range[1, 1, 1, exportedColumns.Length].Style.Font.IsBold = true;
            sheet.Range[2, 1, dataTable.Rows.Count + 1, exportedColumns.Length].Style.Color = Color.LightSkyBlue;

2. Import Child Table

Because the first 2 rows of "Output Worksheet" have been taken by information of Master Table, we have to insert the Child Table information starting from the third row. Clone the Child Table information from XML to a sub DataTable which can be recognized as a temporary table.

int childDataInSheetRowIndex = 3;
DataTable subDataTable = childDataTable.Clone();

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