/// <summary>
/// 转换内存表为Office2007版本以上的EXCEL文件流
/// </summary>
/// <param name="sourceTable"></param>
/// <param name="sheetName"></param>
/// <param name="dateTimeFormat"></param>
/// <returns></returns>
public static MemoryStream DataTableToXLSXStream(DataTable sourceTable, string sheetName = "Sheet1", string dateTimeFormat = "yyyy-MM-dd HH:mm:ss")
{
XSSFWorkbook workbook = new XSSFWorkbook();
WriteReportToXLSXSheet(workbook, sourceTable, sheetName, dateTimeFormat);
ISheet sheet = workbook.GetSheetAt(0);
//设置自适应宽度,9为Excel列数,根据需要自已修改
for (int columnNum = 0; columnNum <= 9; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
//5为开始修改的行数,默认为0行开始
for (int rowNum = 5; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow = sheet.GetRow(rowNum);
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length+1;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms, true);
ms.Seek(0, SeekOrigin.Begin);
return ms;
}
View Code
C# NPOI导出Excel 表格宽度自适应,支持中文