NPOI 导出excel表格

 

使用到的命名空间有

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util; 
using MySql.Data.MySqlClient;

 

 

 

 

 

public static MemoryStream RenderToExcel(DataTable table)
{
MemoryStream ms = new MemoryStream();

using (table)
{
IWorkbook workbook = new HSSFWorkbook(); //创建工作簿
ISheet sheet = workbook.CreateSheet(); //默认有3个工作表，sheet1是工作表1，
IRow headerRow = sheet.CreateRow(0);

// handling header.
foreach (DataColumn column in table.Columns)//DataColumn表示 DataTable 中列的架构。Ordinal 获取列在 DataColumnCollection 集合中的位置。
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value,获取或设置列的标题。

// handling value.
int rowIndex = 1;

foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);

foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex-1, rowIndex-1, 3, 4);
sheet.AddMergedRegion(cellRangeAddress);
rowIndex++;
}

FileStream file = new FileStream("test.xls", FileMode.Create);


workbook.Write(file);

}


MessageBox.Show("执行成功！");
return ms;

}
}
}