private static void CreateHeader(ISheet excelSheet, ICellStyle style, List <ExeclCellStyle> execlCellStyleList)
{
int cellIndex = 0;
IRow newRow = excelSheet.CreateRow(1);
//循环导出列
foreach (var de in ListColumnsName)
{
ICell newCell = newRow.CreateCell(cellIndex);
newCell.SetCellValue(de.ToString());
//按表头文字的宽度
excelSheet.SetColumnWidth(cellIndex, de.ToString().Length * 650);
ExeclCellStyle execlCellStyle = execlCellStyleList.FirstOrDefault(it => it.ColumnsName == de.ToString());
if (execlCellStyle != null)
{
newCell.CellStyle = execlCellStyle.TitleStyle;//用户自定义表头样式
}
else
{
newCell.CellStyle = style;//赋默认值
}
cellIndex++;
}
}
/// <summary>
/// 合同单元格样式
/// </summary>
/// <param name="dicTitle"></param>
/// <param name="excelWorkbook">excelWorkbook</param>
/// <returns></returns>
public static List <ExeclCellStyle> GetContractExeclCellStyleList(Dictionary <string, string> dicTitle, IWorkbook excelWorkbook)
{
List <ExeclCellStyle> execlCellStyleList = new List <ExeclCellStyle>();
foreach (var item in dicTitle)
{
string columnsName = item.Key.ToString();
ExeclCellStyle execlCellStyleEntity = new ExeclCellStyle();
execlCellStyleEntity.IsLock = false;
execlCellStyleEntity.ColumnsName = columnsName;
execlCellStyleEntity.BackGrandIndexed = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
execlCellStyleEntity.IsHidden = false;
execlCellStyleEntity.TitleStyle = NoEditTitleStyle(excelWorkbook);
execlCellStyleEntity.CellStyle = NoEditStyle(excelWorkbook);
switch (columnsName)
{
case "ProductCode":
execlCellStyleEntity.Width = 20; break;
case "ProdcutName":
execlCellStyleEntity.Width = 30; break;
case "ProductTypeName":
execlCellStyleEntity.Width = 10; break;
case "ProductAttribute":
execlCellStyleEntity.Width = 80; break;
case "ProductUnit":
execlCellStyleEntity.Width = 10; break;
case "TacticCgAgreementPrice":
execlCellStyleEntity.Width = 30;
execlCellStyleEntity.CellStyle.Alignment = HorizontalAlignment.Right; //金额右对齐
break;
case "ContractCount":
execlCellStyleEntity.Width = 10;
execlCellStyleEntity.BackGrandIndexed = NPOI.HSSF.Util.HSSFColor.White.Index;
execlCellStyleEntity.TitleStyle = EditTitleStyle(excelWorkbook);
execlCellStyleEntity.CellStyle = EditStyle(excelWorkbook);
execlCellStyleEntity.CellStyle.Alignment = HorizontalAlignment.Right; //金额右对齐
break;
case "ContractPrice":
execlCellStyleEntity.Width = 15;
execlCellStyleEntity.BackGrandIndexed = NPOI.HSSF.Util.HSSFColor.White.Index;
execlCellStyleEntity.TitleStyle = EditTitleStyle(excelWorkbook);
execlCellStyleEntity.CellStyle = EditStyle(excelWorkbook);
execlCellStyleEntity.CellStyle.Alignment = HorizontalAlignment.Right; //金额右对齐
break;
case "TotalPrice":
execlCellStyleEntity.Width = 15;
execlCellStyleEntity.CellStyle.Alignment = HorizontalAlignment.Right; //金额右对齐
execlCellStyleEntity.SetCellFormula = "I{0}*J{0}";
break;
case "ContractProductRemark":
execlCellStyleEntity.Width = 80;
execlCellStyleEntity.BackGrandIndexed = NPOI.HSSF.Util.HSSFColor.White.Index;
execlCellStyleEntity.TitleStyle = EditTitleStyle(excelWorkbook);
execlCellStyleEntity.CellStyle = EditStyle(excelWorkbook);
break;
case "ContractGUID":
execlCellStyleEntity.Width = 5;
execlCellStyleEntity.IsHidden = true; break;
case "ProductGUID":
execlCellStyleEntity.Width = 5;
execlCellStyleEntity.IsHidden = true; break;
default:
break;
}
execlCellStyleList.Add(execlCellStyleEntity);
}
return(execlCellStyleList);
}
/// <summary>
/// 单元格格式
/// </summary>
/// <param name="execlCell"></param>
/// <param name="execlCellStyle"></param>
/// <returns></returns>
private static void SetExeclCellStyle(ICell execlCell, ExeclCellStyle execlCellStyle)
{
execlCell.CellStyle = execlCellStyle.CellStyle;
}
/// <summary>
/// 列锁定
/// </summary>
/// <param name="columnStyle"></param>
/// <param name="excelWorkBook"></param>
/// <param name="execlCellStyle"></param>
private static ICellStyle SetExeclColumnStyle(ICellStyle columnStyle, IWorkbook excelWorkBook, ExeclCellStyle execlCellStyle)
{
if (columnStyle == null)
{
columnStyle = excelWorkBook.CreateCellStyle();
}
if (execlCellStyle != null)
{
columnStyle.IsLocked = execlCellStyle.IsLock;
columnStyle.IsHidden = execlCellStyle.IsHidden;
}
return(columnStyle);
}
/// <summary>
/// 导出数据行
/// </summary>
/// <param name="dtSource">数据表</param>
/// <param name="drSource">来源数据行</param>
/// <param name="currentExcelRow">当前数据行</param>
/// <param name="excelSheet">sheet集合</param>
/// <param name="excelWorkBook">数据表</param>
/// <param name="execlCellStyleList">表头样式</param>
/// <param name="rowCount">execl当前行</param>
private static void InsertCell(DataTable dtSource, DataRow drSource, IRow currentExcelRow, ISheet excelSheet, IWorkbook excelWorkBook, List <ExeclCellStyle> execlCellStyleList, int rowCount)
{
int cellIndex = 0;
foreach (var item in ListColumnsName)
{
//列名称
string columnsName = item.ToString();
//根据列名称设置列样式
ExeclCellStyle execlCellStyle = execlCellStyleList?.FirstOrDefault(it => it.ColumnsName == columnsName) ?? null;
ICellStyle columnStyle = excelSheet.GetColumnStyle(cellIndex);
//设置列样式
//excelSheet.SetDefaultColumnStyle(cellIndex, SetExeclColumnStyle(columnStyle, excelWorkBook, execlCellStyle));
//设置列宽
if ((execlCellStyle?.Width ?? 0) > 0)
{
excelSheet.SetColumnWidth(cellIndex, execlCellStyle.Width);
}
if (execlCellStyle?.IsHidden ?? false)
{
excelSheet.SetColumnHidden(cellIndex, true);
}
ICell newCell = null;
System.Type rowType = drSource[columnsName].GetType();
string drValue = drSource[columnsName].ToString().Trim();
//错误消息列
string errColumn = columnsName + "ErrMsg";
string errMsg = string.Empty;
if (dtSource.Columns.Contains(errColumn))
{
errMsg = drSource[errColumn].ToString().Trim();
}
switch (rowType.ToString())
{
case "System.String": //字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.Guid": //GUID类型
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
//格式化显示
ICellStyle cellStyle = excelWorkBook.CreateCellStyle();
IDataFormat format = excelWorkBook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
newCell.CellStyle = cellStyle;
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = currentExcelRow.CreateCell(cellIndex);
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 = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell = currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
default:
throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
}
//设置单元格样式
SetExeclCellStyle(newCell, execlCellStyle);
//设置批注
if (!string.IsNullOrWhiteSpace(errMsg))
{
SetComment(errMsg, currentExcelRow, newCell, excelWorkBook.GetCreationHelper(), (HSSFPatriarch)excelSheet.CreateDrawingPatriarch());
}
//设置计算表达式
if (!string.IsNullOrEmpty(execlCellStyle.SetCellFormula))
{
int rowIndex = rowCount + 1;
newCell.SetCellFormula(string.Format(execlCellStyle.SetCellFormula, rowIndex));
if (excelSheet.ForceFormulaRecalculation == false)
{
excelSheet.ForceFormulaRecalculation = true;//没有此句，则不会刷新出计算结果
}
}
cellIndex++;
}
bool shouldLock = execlCellStyleList?.FirstOrDefault(it => it.IsLock == true)?.IsLock ?? false;
if (shouldLock == true)
{
excelSheet.ProtectSheet("password");//设置密码保护
}
}