How to export DataTable to Excel file in C #

In this article Xiaobian introduces in detail "how to export DataTable to Excel file in C#", the content is detailed, the steps are clear, and the details are handled properly. I hope that this article "how to export DataTable to Excel file in C#" can help you solve your doubts.

The conversion of DataTable to Excel file and the conversion of the last shared Excel file to DataTable are counter-operations to each other. DataTable is converted to an Excel file by passing in a parameter of type DataTable, and then converting the incoming DataTable into an Excel file. The key code for implementation is as follows:

Method 1:

/ DataTable data export Excel/// public static void DataTableExport (DataTable data, string filepath) {try {/ / Workbook book = new Workbook ("E:\\ test.xlsx"); / / Open workbook Workbook book = new Workbook (); / / create workbook Worksheet sheet = book.Worksheets [0]; / / create worksheet Cells cells = sheet.Cells / / Cell / / create a style Aspose.Cells.Style style = book.Styles [book.Styles.Add ()]; style.Borders [Aspose.Cells.BorderType.LeftBorder] .LineStyle = Aspose.Cells.CellBorderType.Thin; / / apply the left boundary line style.Borders [Aspose.Cells.BorderType.RightBorder] .LineStyle = Aspose.Cells.CellBorderType.Thin / / apply the right boundary line style.Borders [Aspose.Cells.BorderType.TopBorder] .LineStyle = Aspose.Cells.CellBorderType.Thin; / / apply the upper boundary line style.Borders.BorderType.BottomBorder] .LineStyle = Aspose.Cells.CellBorderType.Thin; / / apply the lower boundary line style.HorizontalAlignment = TextAlignmentType.Center / / horizontal alignment of cell contents style.Font.Name = "Arial"; / / font style1.Font.IsBold = true; / / set bold style.Font.Size = 11; / / set font size / / style.ForegroundColor = System.Drawing.Color.FromArgb (153,204,0); / / background color / / style.Pattern = Aspose.Cells.BackgroundType.Solid / / background style / / style.IsTextWrapped = true; / / automatic line wrapping of cell contents / / Table fill data int Colnum = number of data.Columns.Count;// table columns int Rownum = number of data.Rows.Count;// table rows / / generate row column name row for (int I = 0; I

< Colnum; i++) { cells[0, i].PutValue(data.Columns[i].ColumnName); //添加表头 cells[0, i].SetStyle(style); //添加样式 //cells.SetColumnWidth(i, data.Columns[i].ColumnName.Length * 2 + 1.5); //自定义列宽 //cells.SetRowHeight(0, 30); //自定义高 } //生成数据行 for (int i = 0; i < Rownum; i++) { for (int k = 0; k < Colnum; k++) { cells[1 + i, k].PutValue(data.Rows[i][k].ToString()); //添加数据 cells[1 + i, k].SetStyle(style); //添加样式 } cells[1 + i, 5].Formula = "=B" + (2 + i) + "+C" + (2 + i);//给单元格设置计算公式，计算班级总人数 } sheet.AutoFitColumns(); //自适应宽 book.Save(filepath); //保存 GC.Collect(); } catch (Exception e) { logger.Error("生成excel出错：" + e.Message); }} 方法二： public void ToExcel(DataTable dt) { #region 3s var FilePath = @"D:\ToExcel\";//产生Excel文件路径 DeleteFile(FilePath); //创建全新的Workbook var workbook = new HSSFWorkbook();//一個sheet最多65536行 var count = 0; for (double i = 0; i < Convert.ToDouble(dt.Rows.Count) / Convert.ToDouble(65534); i++)//每个Excel文件的一个页签只能存放65536行数据 { var row_index = 0; //创建Sheet workbook.CreateSheet("Sheet" + i); //根据Sheet名字获得Sheet对象 var sheet = workbook.GetSheet("Sheet" + i); IRow row; row = sheet.CreateRow(row_index); //写入标题 for (int j = 0; j < dt.Columns.Count; j++) { row.CreateCell(j).SetCellValue(dt.Columns[j].Caption.ToString()); } row = sheet.CreateRow(++row_index); //写入数据 for (int j = 0; j < (dt.Rows.Count - count >

65534? 65534: dt.Rows.Count-count); jacks +) {var r = dt.Rows [j + count]; for (int k = 0; k