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();

                   }