/// <summary>
/// DataTable to Excel（将数据表中的数据读取到excel格式内存中）
/// </summary>
/// <param name="dataTable">数据表</param>
/// <param name="excelType">excel格式</param>
/// <param name="sheetName">excel工作表名称</param>
/// <returns>内存流数据</returns>
public static Stream DataTableToExcel(DataTable dataTable, string excelType = ".xlsx", string sheetName = "sheet1")
{
Stream stream;
try
{
//根据excel文件类型创建excel数据结构
switch (excelType)
{
case ".xlsx":
stream = DataTableToExcelXlsx(dataTable, sheetName);
break;
case ".xls":
stream = DataTableToExcelXls(dataTable, sheetName);
break;
default:
stream = null;
break;
}
}
catch (Exception ex)
{
throw ex;
}
return stream;
}
2007版本使用EPPlus创建excel内存数据
/// <summary>
/// DataTable to Excel2007（将数据表中的数据读取到excel格式内存中）
/// </summary>
/// <param name="dataTable">数据表</param>
/// <param name="sheetName">excel工作表名称</param>
/// <returns>内存流数据</returns>
public static Stream DataTableToExcelXlsx(DataTable dataTable, string sheetName)
{
try
{
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName);
ws.Cells["A1"].LoadFromDataTable(dataTable, true);
MemoryStream ms = new MemoryStream();
pck.SaveAs(ms);
ms.Flush();
ms.Position = ;//指定当前流的位置从0开始
return ms;
}
}
catch (Exception ex)
{
throw ex;
}
}
97-2003版本使用NPOI创建excel内存数据
/// <summary>
/// DataTable to Excel97-2003（将数据表中的数据读取到excel格式内存中）
/// </summary>
/// <param name="dataTable">数据表</param>
/// <param name="sheetName">excel工作表名称</param>
/// <returns>内存流数据</returns>
public static Stream DataTableToExcelXls(DataTable dataTable, string sheetName)
{
try
{
const int startIndex = ;
var fields = dataTable.Columns;
//创建excel数据结构
var workbook = new HSSFWorkbook();
//创建excel工作表
var sheet = workbook.CreateSheet(sheetName);
sheet.DefaultRowHeight = * ;
#region 创建标题行
var row = sheet.CreateRow(startIndex);
var headStyle = GetHeadStyle(workbook);
foreach (DataColumn column in dataTable.Columns)
{
var cellIndex = fields.IndexOf(column) + startIndex;
var cell = row.CreateCell(cellIndex);
cell.SetCellValue(column.ColumnName);
cell.CellStyle = headStyle;
sheet.AutoSizeColumn(cellIndex);
}
#endregion
#region 创建数据行
int rowIndex = ;
foreach (DataRow dataRow in dataTable.Rows)
{
row = sheet.CreateRow(rowIndex + );
foreach (DataColumn column in dataTable.Columns)
{
var cellIndex = fields.IndexOf(column) + startIndex;
var dataStyle = GetDataStyle(workbook);
var cell = row.CreateCell(cellIndex);
cell.CellStyle = dataStyle;
var value = dataRow[column.ColumnName];
switch ((value ?? string.Empty).GetType().Name.ToLower())
{
case "int32":
case "int64":
case "decimal":
dataStyle.Alignment = HorizontalAlignment.RIGHT;
cell.SetCellValue(ZConvert.To<double>(value, ));
break;
default:
cell.CellStyle.Alignment = HorizontalAlignment.LEFT;
cell.SetCellValue(ZConvert.ToString(value));
break;
}
}
rowIndex++;
}
#endregion
#region 将数据写到内存数据流
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = ;//指定当前流的位置从0开始 workbook = null;
sheet = null;
row = null;
#endregion
return ms;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 设置excel表头样式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
private static ICellStyle GetHeadStyle(HSSFWorkbook workbook)
{
//表头样式
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.LEFT;//居中对齐
//表头单元格背景色
headStyle.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
//表头单元格边框
headStyle.BorderTop = BorderStyle.THIN;
headStyle.TopBorderColor = HSSFColor.BLACK.index;
headStyle.BorderRight = BorderStyle.THIN;
headStyle.RightBorderColor = HSSFColor.BLACK.index;
headStyle.BorderBottom = BorderStyle.THIN;
headStyle.BottomBorderColor = HSSFColor.BLACK.index;
headStyle.BorderLeft = BorderStyle.THIN;
headStyle.LeftBorderColor = HSSFColor.BLACK.index;
//表头字体设置
var font = workbook.CreateFont();
font.FontHeightInPoints = ;//字号
font.Boldweight = ;//加粗
//font.Color = HSSFColor.WHITE.index;//颜色
headStyle.SetFont(font); return headStyle;
}
/// <summary>
/// 设置excel数据行样式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
private static ICellStyle GetDataStyle(HSSFWorkbook workbook)
{
//数据样式
var dataStyle = workbook.CreateCellStyle();
dataStyle.Alignment = HorizontalAlignment.LEFT;//左对齐
//数据单元格的边框
dataStyle.BorderTop = BorderStyle.THIN;
dataStyle.TopBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderRight = BorderStyle.THIN;
dataStyle.RightBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderBottom = BorderStyle.THIN;
dataStyle.BottomBorderColor = HSSFColor.BLACK.index;
dataStyle.BorderLeft = BorderStyle.THIN;
dataStyle.LeftBorderColor = HSSFColor.BLACK.index;
//数据的字体
var datafont = workbook.CreateFont();
datafont.FontHeightInPoints = ;//字号
dataStyle.SetFont(datafont); return dataStyle;
}