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
![DtExprt1.gif]()
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
- Import Master Table
- Import Child Table
- 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:
![DtExprt2.gif]()
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