使用到的命名空间有

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;

}

}

}