最近遇到一个问题,就是导出gridview里面的数据到Excel表里去。
通过NPOI来做
using Excel = Microsoft.Office.Interop.Excel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
public static void npoigetexcel(DataTable dt,string path)
{
IWorkbook workbook;
//.xls和xlsx的区别就是HSSF还是XSSF
workbook = new HSSFWorkbook();
//if (workbook == null)
//{
// // return null;
//}
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//给表头赋值,看你需要有多少列。
IRow row = sheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("0");
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("1");
ICell cell2 = row.CreateCell(2);
cell2.SetCellValue("2");
ICell cell3 = row.CreateCell(3);
cell3.SetCellValue("3");
ICell cell4 = row.CreateCell(4);
cell4.SetCellValue("4");
ICell cell5 = row.CreateCell(5);
cell5.SetCellValue("5");
ICell cell6 = row.CreateCell(6);
cell6.SetCellValue("6");
ICell cell7 = row.CreateCell(7);
cell7.SetCellValue("7");
ICell cell8 = row.CreateCell(8);
cell8.SetCellValue("8");
ICell cell9 = row.CreateCell(9);
cell9.SetCellValue("9");
ICell cell10 = row.CreateCell(10);
cell10.SetCellValue("10");
//数据 循环将数据表中的数据赋值到excel表中去
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//设置宽度
for (int i = 0; i < dt.Columns.Count; i++)
{
int length = Encoding.Default.GetBytes(row.GetCell(i).ToString()).Length;
sheet.SetColumnWidth(i, (length + 2) * 256);
// sheet.SetColumnWidth(dt.Columns.Count-1, (length + 2) * 1024);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
byte[] buffer = ms.GetBuffer();
ms.Close();
//主要在这里的文件创建的位置,path表示路径和文件名
//这里很容易出错,这里是先创建文件,再写入数据。路径问题要好好研究下,这里的path是路径加文件名包括后缀,你自定义。
FileStream fs = new FileStream(@path, FileMode.Create, FileAccess.Write);
for (int i = 0; i < buffer.Length; i++)
{
fs.WriteByte(buffer[i]);
}
fs.Close();
}
}