MemoryStream DtToMs(DataTable sourceTable, string sheetName)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet(sheetName);
IRow rowhead = sheet1.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
ICell headCell = rowhead.CreateCell(column.Ordinal);
headCell.SetCellValue(column.ColumnName);
#region 设置自适应行宽
int CurrentColumn = column.Ordinal;
int columnWidth = sheet1.GetColumnWidth(CurrentColumn) / 256; //获取当前列宽度
int length = Encoding.UTF8.GetBytes(headCell.CString("")).Length;
if (columnWidth < length + 1) //若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
{
columnWidth = length + 1;
}
sheet1.SetColumnWidth(CurrentColumn, columnWidth * 256);
#endregion
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet1.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
ICell icell = dataRow.CreateCell(column.Ordinal);
icell.SetCellValue(row[column].ToString());
#region 设置自适应行宽
int CurrentColumn = column.Ordinal;
int columnWidth = sheet1.GetColumnWidth(CurrentColumn) / 256; //获取当前列宽度
int length = Encoding.UTF8.GetBytes(icell.CString("")).Length;
if (columnWidth < length + 1) //若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
{
columnWidth = length + 1;
}
sheet1.SetColumnWidth(CurrentColumn, columnWidth * 256);
#endregion
}
rowIndex++;
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
return(file);
}
/// <summary>
/// 生成Excel
/// </summary>
/// <param name="sourceTable">数据源</param>
/// <param name="sheetName">表名</param>
/// <param name="TotalDes">汇总列显示</param>
/// <returns></returns>
MemoryStream DtToMsFormat(DataTable sourceTable, string sheetName, string TotalDes, string fname)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet(sheetName);
#region 第二行为汇总行
CellRangeAddress region = new CellRangeAddress(0, 0, 0, sourceTable.Columns.Count - 1); //合并第一行
sheet1.AddMergedRegion(region);
IRow rowTotal = sheet1.CreateRow(0);
ICell Icell = rowTotal.CreateCell(0);
Icell.SetCellValue(fname + ":" + TotalDes);
ICellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 14;
font.Boldweight = 14;
font.Color = HSSFColor.Red.Index;
style.SetFont(font);
Icell.CellStyle = style;
#endregion
IRow rowhead = sheet1.CreateRow(1);
// handling header.
foreach (DataColumn column in sourceTable.Columns)
{
ICell headCell = rowhead.CreateCell(column.Ordinal);
headCell.SetCellValue(column.ColumnName);
int columnWidth = sheet1.GetColumnWidth(column.Ordinal) / 256; //获取当前列宽度
int length = Encoding.UTF8.GetBytes(headCell.CString("")).Length;
if (columnWidth < length + 1) //若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
{
columnWidth = length + 1;
}
sheet1.SetColumnWidth(column.Ordinal, columnWidth * 256);
}
// handling value.
int rowIndex = 2;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet1.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
int currentColumn = column.Ordinal;
ICell newCell = dataRow.CreateCell(currentColumn);
newCell.SetCellValue(row[column].ToString());
int columnWidth = sheet1.GetColumnWidth(currentColumn) / 256; //获取当前列宽度
int length = Encoding.UTF8.GetBytes(newCell.CString("")).Length;
if (columnWidth < length + 1) //若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符
{
columnWidth = length + 1;
}
sheet1.SetColumnWidth(currentColumn, columnWidth * 256);
}
rowIndex++;
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
return(file);
}
