操作代码:
string a = "D:" + "\\KKHMD.xls";
//Class1.ExportExcels(a, dataGridView1);
ExportDataTableToExcel(a, "sheet1", dataGridView1);
#region NPOI导出到Excel
public static void ExportDataTableToExcel(string fileName,string sheetName, DataGridView dataTable)
{
var workBook = new HSSFWorkbook();
var table = workBook.CreateSheet(sheetName);
//写入表头
var headRow = table.CreateRow(0);
for (int k = 0; k < dataTable.Columns.Count; k++)
{
var columnInfo = dataTable.Columns[k];
headRow.CreateCell(k).SetCellValue(columnInfo.Name);
}
//写入数据
for (int i = 0; i < dataTable.Rows.Count; i++)
{
var excelRow = table.CreateRow(i + 1);
var currentRow = dataTable.Rows[i];
int columnIndex = 0;
foreach(DataGridViewCell dataGridViewCell in currentRow.Cells)
{
object value = dataGridViewCell.Value;
var cell = excelRow.CreateCell(columnIndex++);
if (value is byte byteValue)
{
cell.SetCellValue(byteValue);
}
else if (value is sbyte sbyteValue)
{
cell.SetCellValue(sbyteValue);
}
else if (value is short shortValue)
{
cell.SetCellValue(shortValue);
}
else if (value is ushort ushortValue)
{
cell.SetCellValue(ushortValue);
}
else if (value is int intValue)
{
cell.SetCellValue(intValue);
}
else if (value is uint uintValue)
{
cell.SetCellValue(uintValue);
}
else if (value is long int64Value)
{
cell.SetCellValue(int64Value);
}
else if (value is ulong uInt64Value)
{
cell.SetCellValue(uInt64Value);
}
else if (value is double doubleValue)
{
cell.SetCellValue(doubleValue);
}
else if (value is float floatValue)
{
cell.SetCellValue(floatValue);
}
else if (value is decimal decimalValue)
{
cell.SetCellValue((double)decimalValue);
}
else
{
cell.SetCellValue(value.ToString());
}
}
}
using (FileStream fileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None))
{
workBook.Write(fileStream);
}
}
#endregion