EXCEL导出，列表宽度大于内容列

//TIPS:列宽设置在最后

public static string Export<T>(string rootPath, List<T> list, string fileName, IDictionary<string, string> header, Action<int, string, string, XSSFWorkbook, ICell> action = null)
{
string relidateDict = "\\Excel\\" + DateTime.Now.ToString("yyyyMM") + "\\";

string rootDict = rootPath.TrimEnd('\\') + relidateDict;
rootDict = rootDict.Replace("%20", " ");

if (!System.IO.Directory.Exists(rootDict))
{
System.IO.Directory.CreateDirectory(rootDict);
}
string fileFullName = fileName + "_" + Guid.NewGuid().ToString("N") + ".xlsx";
var absolutePath = rootDict + fileFullName;
var workbook = ExportToWorkbook(list, header, action);
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
using (FileStream fs = new FileStream(absolutePath, FileMode.Create, FileAccess.ReadWrite))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}

}
var webPath = (relidateDict + fileFullName).Replace("\\", "/");
return webPath;
}

 

/// <summary>
/// list导出到Excel的MemoryStream
/// </summary>
/// <param name="list">源数据</param>
/// <param name="strSheetName">工作表名称</param>
private static XSSFWorkbook ExportToWorkbook<T>(List<T> list, IDictionary<string, string> header, Action<int, string, string, XSSFWorkbook, ICell> action = null)
{
string[] oldColumnNames = header.Keys.ToArray();
string[] newColumnNames = header.Values.ToArray();

XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
int sheetNum = 1;
int[,] arrWidth = new int[list.Count, oldColumnNames.Length];

int rowIndex = 0;
foreach (var item in list)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheetNum++;
sheet = workbook.CreateSheet("Sheet" + sheetNum.ToString());
}

#region 列头及样式
{
IRow headerRow = sheet.CreateRow(0);

ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
//IFont font = workbook.CreateFont();
//font.FontHeightInPoints = 10;
//font.Boldweight = 700;
//headStyle.SetFont(font);

for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
}
}
#endregion

rowIndex = 1;
}
#endregion

#region 填充内容

var entity = item.GetType();
PropertyInfo[] piList = entity.GetProperties();

IRow dataRow = sheet.CreateRow(rowIndex);

for (int i = 0; i < piList.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);

string drValue = piList[i].GetValue(item) == null ? "" : piList[i].GetValue(item).ToString();
arrWidth[rowIndex - 1, i] = Encoding.GetEncoding(936).GetBytes(drValue).Length;

string drType = piList[i].GetValue(item) == null ? "System.String" : piList[i].GetValue(item).GetType().ToString();
switch (drType)
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}

action?.Invoke(rowIndex, oldColumnNames[i], drValue, workbook, newCell);
}

#endregion

rowIndex++;
}
//设置列宽 （table数据源设置相同）
for (int i = 0; i < oldColumnNames.Length; i++)
{
int tmpWidth = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
for (int j = 0; j < list.Count - 1; j++)
{
if (arrWidth[j, i] > tmpWidth)
{
tmpWidth = arrWidth[j, i];
}
}
sheet.SetColumnWidth(i, (tmpWidth + 1) * 256);
}

return workbook;

}