Winform程序中将datagridview导出到excel (推荐)

使用DEEPSEEK推荐的导出方法，好的很！

用前要用nuget 安装 ClosedXML 插件包

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ClosedXML.Excel;
namespace drp.Common
{public class ExcelIO{public void ExportWithClosedXML(DataGridView dgv){using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Files|*.xlsx" }){if (sfd.ShowDialog() == DialogResult.OK){using (XLWorkbook wb = new XLWorkbook()){var ws = wb.Worksheets.Add("Sheet1");// 导出列头for (int i = 0; i < dgv.Columns.Count; i++){// 使用 SetValue 方法避免类型错误ws.Cell(1, i + 1).SetValue(dgv.Columns[i].HeaderText);}// 导出数据for (int i = 0; i < dgv.Rows.Count; i++){// 跳过新行（如果允许添加行）if (dgv.Rows[i].IsNewRow) continue;for (int j = 0; j < dgv.Columns.Count; j++){var cellValue = dgv.Rows[i].Cells[j].Value;// 处理空值if (cellValue == null || cellValue == DBNull.Value){ws.Cell(i + 2, j + 1).SetValue(string.Empty);}else{// 根据数据类型设置值if (dgv.Columns[j].ValueType == typeof(DateTime)){ws.Cell(i + 2, j + 1).SetValue((DateTime)cellValue);ws.Cell(i + 2, j + 1).Style.DateFormat.Format = "yyyy-mm-dd";}else if (IsNumericType(dgv.Columns[j].ValueType)){ws.Cell(i + 2, j + 1).SetValue(Convert.ToDouble(cellValue));}else{ws.Cell(i + 2, j + 1).SetValue(cellValue.ToString());}}}}for (int i = 0; i < dgv.Columns.Count; i++){// 获取DataGridView列的宽度（像素）int colWidth = dgv.Columns[i].Width;// 计算Excel列宽double excelWidth = (colWidth / 7.0) + 1;// 设置最小和最大宽度限制if (excelWidth < 2) excelWidth = 2;if (excelWidth > 50) excelWidth = 50;// 设置Excel列宽ws.Column(i + 1).Width = excelWidth;// 如果内容比列宽更宽，则自动调整列宽if (ws.Column(i + 1).Width < ws.Column(i + 1).CellsUsed().Max(c => c.Value.ToString().Length) * 0.8){ws.Column(i + 1).AdjustToContents();}}// 自动调整列宽
                        ws.Columns().AdjustToContents();wb.SaveAs(sfd.FileName);}MessageBox.Show("导出成功！");}}}public void ExportToExcel(DataGridView dgv){using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Files|*.xlsx" }){if (sfd.ShowDialog() == DialogResult.OK){using (XLWorkbook wb = new XLWorkbook()){var ws = wb.Worksheets.Add("Sheet1");// 创建数据表var dataTable = new System.Data.DataTable();// 添加列头foreach (DataGridViewColumn column in dgv.Columns){dataTable.Columns.Add(column.HeaderText);}// 添加数据行foreach (DataGridViewRow row in dgv.Rows){if (row.IsNewRow) continue;var dataRow = dataTable.NewRow();foreach (DataGridViewCell cell in row.Cells){dataRow[cell.ColumnIndex] = cell.Value ?? DBNull.Value;}dataTable.Rows.Add(dataRow);}// 批量插入数据（从第一行第一列开始）ws.Cell(1, 1).InsertTable(dataTable);// 设置列宽for (int i = 0; i < dgv.Columns.Count; i++){// 获取DataGridView列的宽度（像素）int colWidth = dgv.Columns[i].Width;// 计算Excel列宽double excelWidth = (colWidth / 7.0) + 1;// 设置最小和最大宽度限制if (excelWidth < 2) excelWidth = 2;if (excelWidth > 50) excelWidth = 50;// 设置Excel列宽ws.Column(i + 1).Width = excelWidth;// 如果内容比列宽更宽，则自动调整列宽if (ws.Column(i + 1).Width < ws.Column(i + 1).CellsUsed().Max(c => c.Value.ToString().Length) * 0.8){ws.Column(i + 1).AdjustToContents();}}wb.SaveAs(sfd.FileName);}MessageBox.Show("导出成功！");}}}public void Export(DataGridView dgv){using (SaveFileDialog sfd = new SaveFileDialog() { Filter = "Excel Files|*.xlsx" }){if (sfd.ShowDialog() == DialogResult.OK){using (XLWorkbook wb = new XLWorkbook()){var ws = wb.Worksheets.Add("Sheet1");// 创建数据表var dataTable = new DataTable();// 添加列头并设置数据类型foreach (DataGridViewColumn column in dgv.Columns){// 使用DataGridView列的数据类型（如果可用）Type dataType = column.ValueType ?? typeof(object);dataTable.Columns.Add(column.HeaderText, dataType);}// 添加数据行foreach (DataGridViewRow row in dgv.Rows){if (row.IsNewRow) continue;var dataRow = dataTable.NewRow();foreach (DataGridViewCell cell in row.Cells){if (cell.Value == null || cell.Value == DBNull.Value){// 根据列的数据类型设置默认值if (dataTable.Columns[cell.ColumnIndex].DataType == typeof(string)){dataRow[cell.ColumnIndex] = "";}else if (IsNumericType(dataTable.Columns[cell.ColumnIndex].DataType)){dataRow[cell.ColumnIndex] = 0;}else{dataRow[cell.ColumnIndex] = DBNull.Value;}}else{dataRow[cell.ColumnIndex] = cell.Value;}}dataTable.Rows.Add(dataRow);}// 批量插入数据（从第一行第一列开始）var tableRange = ws.Cell(1, 1).InsertTable(dataTable);// 设置列宽for (int i = 0; i < dgv.Columns.Count; i++){int colWidth = dgv.Columns[i].Width;double excelWidth = (colWidth / 7.0) + 1;if (excelWidth < 2) excelWidth = 2;if (excelWidth > 50) excelWidth = 50;ws.Column(i + 1).Width = excelWidth;}// 关键：设置数值列的格式为数字for (int i = 0; i < dgv.Columns.Count; i++){if (IsNumericType(dataTable.Columns[i].DataType)){// 设置整列为数字格式ws.Column(i + 1).Style.NumberFormat.Format = "#,##0.00";// 对于整数类型，去掉小数部分if (dataTable.Columns[i].DataType == typeof(int) ||dataTable.Columns[i].DataType == typeof(long)){ws.Column(i + 1).Style.NumberFormat.Format = "#,##0";}}}wb.SaveAs(sfd.FileName);}MessageBox.Show("导出成功！");}}}// 辅助方法：检查是否为数值类型private bool IsNumericType(Type type){switch (Type.GetTypeCode(type)){case TypeCode.Byte:case TypeCode.SByte:case TypeCode.UInt16:case TypeCode.UInt32:case TypeCode.UInt64:case TypeCode.Int16:case TypeCode.Int32:case TypeCode.Int64:case TypeCode.Decimal:case TypeCode.Double:case TypeCode.Single:return true;default:return false;}}}
}