https://www.cnblogs.com/zxh1314/p/8342066.html
接上篇博文《C#快速导出到excel》:由于此种方法不能导出成.xlsx格式,为解决此问题,本次分享使用NPOI。
参考:https://www.cnblogs.com/lazyneal/p/6148912.html
1、添加程序包。
在项目名右键。
选择管理NuGet程序包,浏览处搜索NPOI并安装。
2、代码引用。
using System.IO; using System.Data.SqlClient; using System.Diagnostics; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel;
3、导出Excel方法:
public void ExportDataToExcel(DataTable TableName, string FileName) { SaveFileDialog saveFileDialog = new SaveFileDialog(); //设置文件标题 saveFileDialog.Title = "导出Excel文件"; //设置文件类型 saveFileDialog.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls"; //设置默认文件类型显示顺序 saveFileDialog.FilterIndex = 1; //是否自动在文件名中添加扩展名 saveFileDialog.AddExtension = true; //是否记忆上次打开的目录 saveFileDialog.RestoreDirectory = true; //设置默认文件名 saveFileDialog.FileName = FileName; //按下确定选择的按钮 if (saveFileDialog.ShowDialog() == DialogResult.OK) { //获得文件路径 string localFilePath = saveFileDialog.FileName.ToString(); //数据初始化 int TotalCount; //总行数 int RowRead = 0; //已读行数 int Percent = 0; //百分比 TotalCount = TableName.Rows.Count; lblStatus.Text = "共有" + TotalCount + "条数据"; lblStatus.Visible = true; barStatus.Visible = true; //NPOI IWorkbook workbook; string FileExt = Path.GetExtension(localFilePath).ToLower(); if (FileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (FileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; } if (workbook == null) { return; } ISheet sheet = string.IsNullOrEmpty(FileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(FileName); //秒钟 Stopwatch timer = new Stopwatch(); timer.Start(); try { //读取标题 IRow rowHeader = sheet.CreateRow(0); for (int i = 0; i < TableName.Columns.Count; i++) { ICell cell = rowHeader.CreateCell(i); cell.SetCellValue(TableName.Columns[i].ColumnName); } //读取数据 for (int i = 0; i < TableName.Rows.Count; i++) { IRow rowData = sheet.CreateRow(i + 1); for (int j = 0; j < TableName.Columns.Count; j++) { ICell cell = rowData.CreateCell(j); cell.SetCellValue(TableName.Rows[i][j].ToString()); } //状态栏显示 RowRead++; Percent = (int)(100 * RowRead / TotalCount); barStatus.Maximum = TotalCount; barStatus.Value = RowRead; lblStatus.Text = "共有" + TotalCount + "条数据,已读取" + Percent.ToString() + "%的数据。"; Application.DoEvents(); } //状态栏更改 lblStatus.Text = "正在生成Excel..."; Application.DoEvents(); //转为字节数组 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); fs.Close(); } //状态栏更改 lblStatus.Text = "生成Excel成功,共耗时" + timer.ElapsedMilliseconds + "毫秒。"; Application.DoEvents(); //关闭秒钟 timer.Reset(); timer.Stop(); //成功提示 if (MessageBox.Show("导出成功,是否立即打开?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes) { System.Diagnostics.Process.Start(localFilePath); } //赋初始值 lblStatus.Visible = false; barStatus.Visible = false; } catch (Exception ex) { MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { //关闭秒钟 timer.Reset(); timer.Stop(); //赋初始值 lblStatus.Visible = false; barStatus.Visible = false; } } }
4、结果演示: