The Method to Import Data to Excel from DataTable and Export Excel Data
Microsoft Excel is an essential tool for office workers. It is very powerful on managing and calculating data. For example, if there are some data about sales information, we can use Excel to calculate average sales and total sales within one quarter. Also, it is convenient for printing if the data in Excel is well formatted.
Actually, if we want to display and operate data, we must import data to Excelfirstly. Where can we get the data? Sometimes, we can type data in Excel, but most of time, we import data from database or datatable.
Then, I want to show a method about how to import data from DataTable to Excel with C#. In this method, I have used a development component: Spire.XLS. If you want to use this method, please download and install it firstly and add DLL file in project.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
//Initailize worksheet
Worksheet sheet = workbook.Worksheets[0];
sheet.InsertDataTable((DataTable)this.dataGrid1.DataSource,true,2,1,-1,-1);
//Sets body style
CellStyle oddStyle = workbook.Styles.Add("oddStyle");
oddStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
oddStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
oddStyle.KnownColor = ExcelColors.LightGreen1;
CellStyle evenStyle = workbook.Styles.Add("evenStyle");
evenStyle.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
evenStyle.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
evenStyle.KnownColor = ExcelColors.LightTurquoise;
foreach( CellRange range in sheet.AllocatedRange.Rows)
{
if (range.Row % 2 == 0)
range.CellStyleName = evenStyle.Name;
else
range.CellStyleName = oddStyle.Name;
}
//Sets header style
CellStyle styleHeader = sheet.Rows[0].Style;
styleHeader.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
styleHeader.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
styleHeader.VerticalAlignment = VerticalAlignType.Center;
styleHeader.KnownColor = ExcelColors.Green;
styleHeader.Font.KnownColor = ExcelColors.White;
styleHeader.Font.IsBold = true;
sheet.Columns[sheet.AllocatedRange.LastColumn - 1].Style.NumberFormat = "\"$\"#,##0";
sheet.Columns[sheet.AllocatedRange.LastColumn - 2].Style.NumberFormat = "\"$\"#,##0";
sheet.AllocatedRange.AutoFitColumns();
sheet.AllocatedRange.AutoFitRows();
sheet.Rows[0].RowHeight = 20;
workbook.SaveToFile("sample.xls");
ExcelDocViewer( workbook.FileName );
}
private void Form1_Load(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\DataTableSample.xls");
//Initailize worksheet
Worksheet sheet = workbook.Worksheets[0];
this.dataGrid1.DataSource = sheet.ExportDataTable();
}
private void ExcelDocViewer( string fileName )
{
try
{
System.Diagnostics.Process.Start(fileName);
}
catch{}
}
What's more, sometimes we need to save the Excel data to datatable. So, I will share the method to export Excel data with C#as well by using this component.
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\..\..\..\..\Data\DataTableSample.xls");
//Initailize worksheet
Worksheet sheet = workbook.Worksheets[0];
this.dataGrid1.DataSource = sheet.ExportDataTable();
}
In : Excel