#region 从DataTable读取内容来创建Workbook对象:
public static MemoryStream RenderToExcel(DataTable table)
{
MemoryStream ms = new MemoryStream();
using (table)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
{
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
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());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return ms;
}
}
#endregion
#region 保存Excel
static void SaveToFile(MemoryStream ms, string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
data = null;
}
}
#endregion
//导出
private void exportBtn_Click(object sender, EventArgs e)
{
DataTable dt = (DataTable)gridControl1.DataSource;
MemoryStream ms= RenderToExcel(dt);
SaveFileDialog sf = new SaveFileDialog();
string excelName; //excel 名称
sf.Filter = "Excel文件(*.xls,xlsx)|*.xls;*.xlsx";
if (sf.ShowDialog() == DialogResult.OK)
{
excelName= sf.FileName;
SaveToFile(ms, excelName);
}
}
请参考