文章目录
前言
实现步骤
一、安装NPOI
二、创建类
三、调用
前言
本地数据库表中有46785条数据，测试正常初次运行程序第一次导出，用时在4-5s左右；此后再导出用时在2-3s左右；可能与缓存有关支持导出为.xls和.xlsx格式可以自定义字体和字号exe测试文件下载地址：/i95st5g密码：test
实现步骤
一、安装NPOI
右键项目 → “管理NuGet程序包” → 搜索NPOI → 安装
二、创建类
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using NPOI.SS.UserModel;using NPOI.HSSF.UserModel;//导出xls格式用HSSFusing NPOI.XSSF.UserModel;//导出xlsx格式用XSSFusing System.IO;using System.Runtime.InteropServices;using System.Diagnostics;using NPOI.SS.Util;namespace ExportToExcel{public class ExportDgvToExcel{#region NPOI DataGridView 导出 EXCEL/// <summary>/// NPOI DataGridView 导出 EXCEL/// 03版Excel-xls最大行数是65536行,最大列数是256列/// 07版Excel-xlsx最大行数是1048576行,最大列数是16384列/// </summary>/// <param name="fileName">默认保存文件名</param>/// <param name="dgv">DataGridView</param>/// <param name="fontname">字体名称</param>/// <param name="fontsize">字体大小</param> public void ExportExcel(string fileName, DataGridView dgv, string fontname, short fontsize){IWorkbook workbook;ISheet sheet;Stopwatch sw = null; //判断datagridview中内容是否为空if (dgv.Rows.Count == 0){MessageBox.Show("DataGridView中内容为空,请先导入数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);return;}//保存文件string saveFileName = "";SaveFileDialog saveFileDialog = new SaveFileDialog();saveFileDialog.DefaultExt = "xls";saveFileDialog.Filter = "Excel文件(*.xls)|*.xls|Excel文件(*.xlsx)|*.xlsx";saveFileDialog.RestoreDirectory = true;saveFileDialog.Title = "Excel文件保存路径";saveFileDialog.FileName = fileName;MemoryStream ms = new MemoryStream(); //MemoryStreamif (saveFileDialog.ShowDialog() == DialogResult.OK){ //**程序开始计时**//sw = new Stopwatch();sw.Start();saveFileName = saveFileDialog.FileName;//检测文件是否被占用if (!CheckFiles(saveFileName)){MessageBox.Show("文件被占用,请关闭文件" + saveFileName);workbook = null;ms.Close();ms.Dispose();return;}}else{workbook = null;ms.Close();ms.Dispose();} //*** 根据扩展名xls和xlsx来创建对象string fileExt = Path.GetExtension(saveFileName).ToLower();if (fileExt == ".xlsx"){workbook = new XSSFWorkbook(); }else if (fileExt == ".xls"){workbook = new HSSFWorkbook(); }else{workbook = null;}//***//创建Sheetif (workbook != null){sheet = workbook.CreateSheet("Sheet1");//Sheet的名称 }else{return;}//设置单元格样式ICellStyle cellStyle = workbook.CreateCellStyle();//水平居中对齐和垂直居中对齐cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//设置字体IFont font = workbook.CreateFont();font.FontName = fontname;//字体名称font.FontHeightInPoints = fontsize;//字号font.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;//字体颜色cellStyle.SetFont(font);//添加列名IRow headRow = sheet.CreateRow(0);for (int i = 0; i < dgv.Columns.Count; i++){//隐藏行列不导出if (dgv.Columns[i].Visible == true){headRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);headRow.GetCell(i).CellStyle = cellStyle;}} //根据类型写入内容for (int rowNum = 0; rowNum < dgv.Rows.Count; rowNum++){///跳过第一行,第一行为列名IRow dataRow = sheet.CreateRow(rowNum + 1);for (int columnNum = 0; columnNum < dgv.Columns.Count; columnNum++){int columnWidth = sheet.GetColumnWidth(columnNum) / 256; //列宽//隐藏行列不导出if (dgv.Rows[rowNum].Visible == true && dgv.Columns[columnNum].Visible == true){//防止行列超出Excel限制if (fileExt == ".xls"){//03版Excel最大行数是65536行,最大列数是256列if (rowNum > 65536){MessageBox.Show("行数超过Excel限制!");return;}if (columnNum > 256){MessageBox.Show("列数超过Excel限制!");return;}}else if (fileExt == ".xlsx"){//07版Excel最大行数是1048576行,最大列数是16384列if (rowNum > 1048576){MessageBox.Show("行数超过Excel限制!");return;}if (columnNum > 16384){MessageBox.Show("列数超过Excel限制!");return;}}ICell cell = dataRow.CreateCell(columnNum);if (dgv.Rows[rowNum].Cells[columnNum].Value == null){cell.SetCellType(CellType.Blank);}else{if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Int32")){cell.SetCellValue(Convert.ToInt32(dgv.Rows[rowNum].Cells[columnNum].Value));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.String")){cell.SetCellValue(dgv.Rows[rowNum].Cells[columnNum].Value.ToString());}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Single")){cell.SetCellValue(Convert.ToSingle(dgv.Rows[rowNum].Cells[columnNum].Value));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Double")){cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.Decimal")){cell.SetCellValue(Convert.ToDouble(dgv.Rows[rowNum].Cells[columnNum].Value));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DateTime")){cell.SetCellValue(Convert.ToDateTime(dgv.Rows[rowNum].Cells[columnNum].Value).ToString("yyyy-MM-dd"));}else if (dgv.Rows[rowNum].Cells[columnNum].ValueType.FullName.Contains("System.DBNull")){cell.SetCellValue("");} }//设置列宽IRow currentRow;if (sheet.GetRow(rowNum) == null){currentRow = sheet.CreateRow(rowNum);}else{currentRow = sheet.GetRow(rowNum);}if (currentRow.GetCell(columnNum) != null){ICell currentCell = currentRow.GetCell(columnNum);int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;if (columnWidth < length){columnWidth = length + 10; //设置列宽数值}}sheet.SetColumnWidth(columnNum, columnWidth * 256);//单元格样式dataRow.GetCell(columnNum).CellStyle = cellStyle;}}}//保存为Excel文件 workbook.Write(ms);FileStream file = new FileStream(saveFileName, FileMode.Create);workbook.Write(file);file.Close();workbook = null;ms.Close();ms.Dispose();//**程序结束计时**//sw.Stop();double totalTime = sw.ElapsedMilliseconds / 1000.0;MessageBox.Show(fileName + " 导出成功\n耗时" + totalTime + "s", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);}#endregion#region 检测文件是否被占用 /// <summary>/// 判定文件是否打开/// </summary> [DllImport("kernel32.dll")]public static extern IntPtr _lopen(string lpPathName, int iReadWrite);[DllImport("kernel32.dll")]public static extern bool CloseHandle(IntPtr hObject);public const int OF_READWRITE = 2;public const int OF_SHARE_DENY_NONE = 0x40;public readonly IntPtr HFILE_ERROR = new IntPtr(-1);/// <summary>/// 检测文件被占用 /// </summary>/// <param name="FileNames">要检测的文件路径</param>/// <returns></returns>public bool CheckFiles(string FileNames){if (!File.Exists(FileNames)){//文件不存在return true;}IntPtr vHandle = _lopen(FileNames, OF_READWRITE | OF_SHARE_DENY_NONE);if (vHandle == HFILE_ERROR){//文件被占用return false;}//文件没被占用CloseHandle(vHandle);return true;}#endregion }}
三、调用
private void btnExport_Click_1(object sender, EventArgs e){ExportDgvToExcel export = new ExportDgvToExcel();export.ExportExcel("", dgv, "宋体", 11);//默认文件名,DataGridView控件的名称,字体,字号 }
———————————————— 版权声明：本文为CSDN博主「人生、蜕变」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：/IT_xiao_guang_guang/article/details/104217491
/IT_xiao_guang_guang/article/details/104217491?utm_medium=distribute.pc_relevant.none-task-blog-baidujs-4
