public static void ExportToExcel(DataGrid dataGridView1, string sheetName)
{
SaveFileDialog fileDialog = new SaveFileDialog();
fileDialog.Filter = “Excel(97-2003)|*.xls”;
if (fileDialog.ShowDialog() == false)
{
return;
}
//不允许dataGridView显示添加行,负责导出时会报最后一行未实例化错误
//dataGridView1.AllowUserToAddRows = false;
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
sheet.SetColumnWidth(0, 20 * 256);//设置宽
sheet.SetColumnWidth(1, 30 * 256);//设置宽
sheet.SetColumnWidth(2, 50 * 256);//设置宽
sheet.SetColumnWidth(3, 20 * 256);//设置宽
IRow rowHead = sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.VerticalAlignment= NPOI.SS.UserModel.VerticalAlignment.Top;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.FillForegroundColor = 44;
headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//填写表头
for (int i = 1; i < dataGridView1.Columns.Count; i++)
{
rowHead.CreateCell(i-1, CellType.String).SetCellValue(dataGridView1.Columns[i].Header.ToString());
rowHead.GetCell(i - 1).CellStyle = headStyle;
}
//填写内容
for (int i = 0; i < dataGridView1.Items.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
DataRowView selectItem = dataGridView1.Items[i] as DataRowView;
for (int j = 1; j < 5; j++)
{
row.CreateCell(j-1, CellType.String).SetCellValue(selectItem.Row[(j==3?9:j)].ToString());
}
}
try
{
using (FileStream stream = File.OpenWrite(fileDialog.FileName))
{
workbook.Write(stream);
stream.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
GC.Collect();
return;
}
MessageBox.Show("导出数据成功!");
GC.Collect();
}