62,243
社区成员




public static MemoryStream ExportToExcel(DataSet ds,ExcelType excelType,string header)
{
IWorkbook workbook = null;
if (excelType==ExcelType.Office2003)
{
workbook = new HSSFWorkbook();//2003
}
else
{
workbook = new XSSFWorkbook();//2007
}
#region 开始循环DS中的Table,DS中的每个表创建一个Sheet
for (int p = 0; p < ds.Tables.Count; p++)
{
#region 创建一个sheet
ISheet sheet = workbook.CreateSheet("sheet" + (p + 1));
//设置大标题行
int rowCount = 0;
//设置全局列宽和行高
sheet.DefaultColumnWidth = 14; //全局列宽
sheet.DefaultRowHeightInPoints = 15; //全局行高
//设置标题行数据
int a = 0;
IRow row1 = sheet.CreateRow(rowCount); //创建报表表头标题列
//for (int k = 0; k < ds.Tables[p].Columns.Count; k++)
//{ //将DataTable的列标题输出到Excel
// columnName = ds.Tables[p].Columns[k].ColumnName;
// row1.CreateCell(a).SetCellValue(columnName);
// a++;
//}
string[] columnHeaders = header.Split(new char[] {','});//按逗号拆分标题
var style = SetCellBorder(workbook);
for (int k = 0; k <columnHeaders.Length; k++)
{ //将传递过来的字符串表头进行拆分到Excel
string columnName = columnHeaders[k];
ICell cell=row1.CreateCell(a);
cell.SetCellValue(columnName);
#region 设置单元格的边框
cell.CellStyle = style;
#endregion
a++;
}
//填写ds数据进excel
for (int i = 0; i < ds.Tables[p].Rows.Count; i++) //写行数据
{
IRow row2 = sheet.CreateRow(i + rowCount + 1);
int b = 0;
for (int j = 0; j < ds.Tables[p].Columns.Count; j++)
{
string dgvValue = string.Empty;
dgvValue = ds.Tables[p].Rows[i][j].ToString();
ICell cell = row2.CreateCell(b);
cell.SetCellValue(dgvValue);
#region 设置单元格的边框
cell.CellStyle = style;
#endregion
b++;
}
}
#endregion
}
#endregion
//创建excel
MemoryStream memoryStream = new MemoryStream();
workbook.Write(memoryStream);
memoryStream.Seek(0, SeekOrigin.Begin);
return memoryStream;
}
string header = "编号,用户名,姓名,角色";
MemoryStream ms =ExcelHelper.ExportToExcel(ds, ExcelType.Office2003, header);
return File(ms, "application/vnd.ms-excel", "用户信息");
MemoryStream ms =ExcelHelper.ExportToExcel(ds, ExcelType.Office2007, header);