C#将DataGridView中显⽰的数据导出到Excel（.xls和.xlsx格式）—NPOI 前⾔
getsavefilename 本地数据库表中有46785条数据，测试正常
初次运⾏程序第⼀次导出，⽤时在4-5s左右；此后再导出⽤时在2-3s左右；可能与缓存有关
⽀持导出为.xls和.xlsx格式
可以⾃定义字体和字号
exe测试⽂件下载地址：
密码：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格式⽤HSSF
using NPOI.XSSF.UserModel;//导出xlsx格式⽤XSSF
using 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(); //MemoryStream
if (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;
}
//***
//创建Sheet
if (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控件的名称，字体，字号
}
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论