导出excel 包含隐藏列公式多个sheet构造合并单元格（懒得改了，部分代码展示）

public void ExportExpenseDetailExcel(ExportExcelModal<CompanyExpenseListDto, CompanyExpenseDetailDto> modal, List<SummaryTitle> titles, List<SheetModels> sheetModels, int type,bool isShowPayCompanyName=false)
{
int allCellCount = 53;
var other = modal.Other;
var contents = modal.Content;
//初始化一个新的HSSFWorkbook实例
HSSFWorkbook hssfworkbook = new HSSFWorkbook();

//设置excel必须的文件属性（该属性用来存储如作者、标题、标记、备注、主题等信息，右键可查看的属性信息）
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.DocumentSummaryInformation = dsi;
hssfworkbook.SummaryInformation = si;

#region 样式
//设置样式居中字体边框背景色
ICellStyle style = hssfworkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
IFont font = hssfworkbook.CreateFont();
font.FontHeight = 15 * 15;
style.SetFont(font);
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;

//设置样式居左字体边框
ICellStyle style_left = hssfworkbook.CreateCellStyle();
style_left.VerticalAlignment = VerticalAlignment.Center;
style_left.Alignment = HorizontalAlignment.Left;
style_left.SetFont(font);
style_left.BorderBottom = BorderStyle.Thin;
style_left.BorderLeft = BorderStyle.Thin;
style_left.BorderRight = BorderStyle.Thin;
style_left.BorderTop = BorderStyle.Thin;

//设置样式居中字体边框
ICellStyle style0 = hssfworkbook.CreateCellStyle();
style0.VerticalAlignment = VerticalAlignment.Center;
style0.Alignment = HorizontalAlignment.Center;
style0.SetFont(font);
style0.BorderBottom = BorderStyle.Thin;
style0.BorderLeft = BorderStyle.Thin;
style0.BorderRight = BorderStyle.Thin;
style0.BorderTop = BorderStyle.Thin;

//设置样式居中字体大
ICellStyle style1 = hssfworkbook.CreateCellStyle();
style1.VerticalAlignment = VerticalAlignment.Center;
style1.Alignment = HorizontalAlignment.Center;
IFont font0 = hssfworkbook.CreateFont();
font0.FontHeight = 20 * 20;
style1.SetFont(font0);

//设置样式居中字体
ICellStyle style2 = hssfworkbook.CreateCellStyle();
style2.VerticalAlignment = VerticalAlignment.Center;
style2.Alignment = HorizontalAlignment.Center;
style2.SetFont(font);

//设置样式
ICellStyle style3 = hssfworkbook.CreateCellStyle();
style3.VerticalAlignment = VerticalAlignment.Center;
style3.Alignment = HorizontalAlignment.Center;
style3.SetFont(font);
style3.BorderBottom = BorderStyle.Thin;
style3.BorderLeft = BorderStyle.Thin;
style3.BorderRight = BorderStyle.Thin;
style3.BorderTop = BorderStyle.Thin;
IDataFormat dataformat = hssfworkbook.CreateDataFormat();
style3.DataFormat = dataformat.GetFormat("0.00");
style3.SetFont(font);
#endregion

#region 构造汇总表
//新建一个Workbook默认都会新建3个Sheet(标准的Excel文件有3个Sheet)。所以必须加入下面的创建Sheet的代码才能保证生成的文件正常
HSSFSheet sheet_Summary = (HSSFSheet)hssfworkbook.CreateSheet("汇总");
#region 标题行和单位
IRow row0 = sheet_Summary.CreateRow(0);
//建单元格，比如创建A1位置的单元格：
var cell_title = row0.CreateCell(0);
cell_title.SetCellValue(other.Title);
sheet_Summary.AddMergedRegion(new CellRangeAddress(0, 0, 0, 50));
cell_title.CellStyle = style1;

IRow row1 = sheet_Summary.CreateRow(1);
var cell_companyName = row1.CreateCell(0);
cell_companyName.SetCellValue("编制单位:" + other.AgreementPayeeCompanyName);
sheet_Summary.AddMergedRegion(new CellRangeAddress(1, 1, 0, 4));
cell_companyName.CellStyle = style2;

#endregion
#region 表头
foreach (var title in titles)
{
IRow row = sheet_Summary.GetRow(title.Row);
if (sheet_Summary.GetRow(title.Row) == null)
{
row = sheet_Summary.CreateRow(title.Row);
}
ICell cell = row.CreateCell(title.Cell);
cell.SetCellValue(title.TitleName + title.TitleNameAdd);
//创建、合并单元格、赋值
if (title.isMargin)
{
sheet_Summary.AddMergedRegion(new CellRangeAddress(title.firstRow, title.lastRow, title.firstCol, title.lastCol));
for (int i = title.firstRow; i <= title.lastRow; i++)
{
var getrow = sheet_Summary.GetRow(i);
if (getrow == null)
{
getrow = sheet_Summary.CreateRow(i);
}
for (int j = title.firstCol; j <= title.lastCol; j++)
{
ICell singleCell = getrow.GetCell(j);
if (singleCell == null)
{
singleCell = getrow.CreateCell(j);
}
singleCell.CellStyle = style;
}
}
}
cell.CellStyle = style;
}
#endregion
#region 内容
int rowIndex = 5;

int rowRownumber = 1;
List<HiddenModel> hiddenList = new List<HiddenModel>();
Dictionary<int, object> dic_total_summary = GetKeyValue_Total(other);
foreach (var content in contents)
{
IRow row = null;
if (sheet_Summary.GetRow(rowIndex) == null)
{
row = sheet_Summary.CreateRow(rowIndex);
}
else
{
row = sheet_Summary.GetRow(rowIndex);
}
content.RowNum = rowRownumber.ToString();
#region 构造cell
Dictionary<int, object> dic = GetKeyValue(content);
int summaryAllCellCount = allCellCount;
if (isShowPayCompanyName)
{
summaryAllCellCount = summaryAllCellCount + 2;
}
for (int i = 0; i <= summaryAllCellCount; i++)
{
ICell cell = row.CreateCell(i);
if (dic[i] != null)
{
double value = 0;
if (double.TryParse(dic[i].ToString(), out value) && i > 17)
{
value = double.Parse(dic[i].ToString());
if (IsFormulaIndex(i))
{
var formula = GetFormula(rowIndex, i, content, content.IsReductionDisabledGuaranteeMoney);
if (formula.IsFormula)
{
cell.SetCellFormula(formula.FormulaStr);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(hssfworkbook);
evaluator.EvaluateFormulaCell(cell);
}
else
{
cell.SetCellValue(value);
}
}
else
{
cell.SetCellValue(value);
if (i > 17 && i <= allCellCount - 1)
{
//合计为0 明细中有不为0 的则去除
double value_total = 0;
if (double.TryParse(dic_total_summary[i].ToString(), out value_total))
{
if (value_total == 0 && !hiddenList.Any(p => p.Col == i) && IsHiddenColown(i))
{
hiddenList.Add(new HiddenModel() { Col = i, IsHidden = true });
}
else if (value != 0 && hiddenList.Any(p => p.Col == i))
{
var hidden = hiddenList.FirstOrDefault(p => p.Col == i);
hiddenList.Remove(hidden);
}
}
}

}
}
else
{
if (((i>6&&i<13)||i==16)&&double.TryParse(dic[i].ToString(), out value))
{
cell.SetCellValue(value);
}
else
{
cell.SetCellValue(dic[i].ToString());
}
}
}

cell.CellStyle = style_left;
}
#endregion

rowIndex++;
rowRownumber++;
}
foreach(var hid in hiddenList)
{
if (hid.IsHidden)
{
sheet_Summary.SetColumnHidden(hid.Col, hid.IsHidden);
}
}

#endregion
int index = 5;
#region 折让金和补助
if (other.RewardDeduction != null && (other.RewardType == RewardTypes.费用表抵扣 || other.RewardType == RewardTypes.折让金发票))
{
IRow row_rewardDeduction = sheet_Summary.CreateRow(contents.Count() + index);
var cell_rewardDeduction = row_rewardDeduction.CreateCell(0);
cell_rewardDeduction.SetCellValue("折让金：");
sheet_Summary.AddMergedRegion(new CellRangeAddress(contents.Count() + index, contents.Count() + index, 0, 8));
cell_rewardDeduction.CellStyle = style0;
for (int j = 0; j <= 17; j++)
{
ICell singleCell = row_rewardDeduction.GetCell(j);
if (singleCell == null)
{
singleCell = row_rewardDeduction.CreateCell(j);
}
singleCell.CellStyle = style0;
}

for (int i = 18; i <= allCellCount-1; i++)
{
ICell cell = row_rewardDeduction.CreateCell(i);
if (i == allCellCount-1)
{
string strValue = (other.TotalReward + other.TotalSubsidy).ToString("0.00");
double value = 0;
if (double.TryParse(strValue, out value) && i > 17)
{
cell.SetCellValue(value*-1);
}
else
{
cell.SetCellValue(strValue);
}
}
else
{
cell.SetCellValue("-");
}
cell.CellStyle = style_left;
}
ICell celllast_rewardDeduction = row_rewardDeduction.CreateCell(allCellCount);
celllast_rewardDeduction.CellStyle = style_left;
index++;
}

#endregion
#region 表合计
if (contents.Count() > 0)
{
IRow row_sum = sheet_Summary.CreateRow(contents.Count() + index);
var cell_sum = row_sum.CreateCell(0);
cell_sum.SetCellValue("合计：");
sheet_Summary.AddMergedRegion(new CellRangeAddress(contents.Count() + index, contents.Count() + index, 0, 8));
cell_sum.CellStyle = style0;
for (int j = 0; j <= 17; j++)
{
ICell singleCell = row_sum.GetCell(j);
if (singleCell == null)
{
singleCell = row_sum.CreateCell(j);
}
singleCell.CellStyle = style0;
}

//Dictionary<int, object> dic_total_summary = GetKeyValue_Total(other);
for (int i = 18; i <= allCellCount-1; i++)
{
ICell cell = row_sum.CreateCell(i);
if (dic_total_summary[i] != null)
{
double value = 0;
if (double.TryParse(dic_total_summary[i].ToString(), out value) && i > 17)
{
var formula = GetFormulaTotal(rowIndex, i,6,other.TotalReward+other.TotalSubsidy!=0);
if (formula == "")
{
value = double.Parse(dic_total_summary[i].ToString());
cell.SetCellValue(value);
}
else
{
cell.SetCellFormula(formula);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(hssfworkbook);
evaluator.EvaluateFormulaCell(cell);
}
}
else
{
cell.SetCellValue(dic_total_summary[i].ToString());
}
}

cell.CellStyle = style_left;
}
ICell celllast = row_sum.CreateCell(allCellCount);
celllast.CellStyle = style_left;
}
#endregion
#region 制表人和统计

IRow row_createTable = sheet_Summary.CreateRow(contents.Count() + 9);
var cell_createTable = row_createTable.CreateCell(1);
cell_createTable.SetCellValue("制表人：" + other.Clockmaker);
cell_createTable.CellStyle = style_left;

IRow row_total0 = sheet_Summary.CreateRow(contents.Count() + other.NotFixedCosts.Count() + 12);
var cell_total0 = row_total0.CreateCell(18);
cell_total0.SetCellValue("劳务费");
cell_total0.CellStyle = style0;
var cell_total09 = row_total0.CreateCell(19);
cell_total09.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(other.TotalLabourFee, roundType));
cell_total09.CellStyle = style3;

IRow row_total1 = sheet_Summary.CreateRow(contents.Count() + other.NotFixedCosts.Count() + 13);
var cell_total1 = row_total1.CreateCell(18);
cell_total1.SetCellValue("管理费");
cell_total1.CellStyle = style0;
var cell_total11 = row_total1.CreateCell(19);
cell_total11.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(other.TotalManageFee, roundType));
cell_total11.CellStyle = style3;

int rowindex = 14;
if (other.RewardDeduction != null && other.RewardDeduction == RewardDeduction.税前抵扣
&& (other.TotalReward + other.TotalSubsidy) > 0 && other.RewardType == RewardTypes.费用表抵扣)
{
IRow row_total4 = sheet_Summary.CreateRow(contents.Count() + other.NotFixedCosts.Count() + rowindex);
var cell_total4 = row_total4.CreateCell(18);
cell_total4.SetCellValue("折让金");
cell_total4.CellStyle = style0;
var cell_total44 = row_total4.CreateCell(19);
cell_total44.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(-1*(other.TotalReward + other.TotalSubsidy), roundType));
cell_total44.CellStyle = style3;

rowindex++;
}

IRow row_total2 = sheet_Summary.CreateRow(contents.Count() + other.NotFixedCosts.Count() + rowindex);
var cell_total2 = row_total2.CreateCell(18);
cell_total2.SetCellValue("税费");
cell_total2.CellStyle = style0;
var cell_total22 = row_total2.CreateCell(19);
cell_total22.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(other.TotalTaxAmount, roundType));
cell_total22.CellStyle = style3;
rowindex++;

if (other.RewardDeduction != null && other.RewardDeduction == RewardDeduction.税后抵扣
&& (other.TotalReward + other.TotalSubsidy) > 0 && other.RewardType == RewardTypes.费用表抵扣)
{
IRow row_total4 = sheet_Summary.CreateRow(contents.Count() + other.NotFixedCosts.Count() + rowindex);
var cell_total4 = row_total4.CreateCell(18);
cell_total4.SetCellValue("折让金");
cell_total4.CellStyle = style0;
var cell_total44 = row_total4.CreateCell(19);
cell_total44.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(-1*(other.TotalReward + other.TotalSubsidy), roundType));
cell_total44.CellStyle = style3;
rowindex++;
}

IRow row_total3 = sheet_Summary.CreateRow(contents.Count() + other.NotFixedCosts.Count() + rowindex);
var cell_total3 = row_total3.CreateCell(18);
cell_total3.SetCellValue("合计");
cell_total3.CellStyle = style0;
var cell_total33 = row_total3.CreateCell(19);
cell_total33.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(other.AllTotalCost, roundType));
cell_total33.CellStyle = style3;
rowindex++;

if (other.RewardType == RewardTypes.折让金发票 && (other.TotalReward + other.TotalSubsidy) > 0)
{
rowindex++;
IRow row_total5 = sheet_Summary.CreateRow(contents.Count() + other.NotFixedCosts.Count() + rowindex);
var cell_total5 = row_total5.CreateCell(18);
cell_total5.SetCellValue("折让金");
cell_total5.CellStyle = style0;
var cell_total55 = row_total5.CreateCell(19);
cell_total55.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(-1 * (other.TotalReward + other.TotalSubsidy), roundType));
cell_total55.CellStyle = style3;
rowindex++;

IRow row_total6 = sheet_Summary.CreateRow(contents.Count() + other.NotFixedCosts.Count() + rowindex);
var cell_total6 = row_total6.CreateCell(18);
cell_total6.SetCellValue("发票合计");
cell_total6.CellStyle = style0;
var cell_total66 = row_total6.CreateCell(19);
cell_total66.SetCellValue((double)_companyExpenseManager.RandTwoDecimal((other.AllTotalCost - (other.TotalReward + other.TotalSubsidy)), roundType));
cell_total66.CellStyle = style3;
}

#endregion

#region 开票
if (other.IsShow)
{
int indexCount = contents.Count + 10;

for (int i = contents.Count + 10; i <= contents.Count + 10 + other.Rowcount + 7; i++)
{
var getrow = sheet_Summary.GetRow(i);
if (getrow == null)
{
getrow = sheet_Summary.CreateRow(i);
}
for (int j = 3; j <= 11; j++)
{
ICell singleCell = getrow.GetCell(j);
if (singleCell == null)
{
singleCell = getrow.CreateCell(j);
}
singleCell.CellStyle = style0;
}
}

IRow row_l0 = sheet_Summary.GetRow(indexCount);
if (row_l0 == null)
{
row_l0 = sheet_Summary.CreateRow(indexCount);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 11));
var cell_l0_1 = row_l0.GetCell(3);
if (cell_l0_1 == null)
{
cell_l0_1 = row_l0.CreateCell(3);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 3, 5));
cell_l0_1.SetCellValue("收款单位");
cell_l0_1.CellStyle = style0;
var cell_l0_2 = row_l0.GetCell(6);
if (cell_l0_2 == null)
{
cell_l0_2 = row_l0.CreateCell(6);
}
cell_l0_2.SetCellValue(other.Enterprise_Name);
cell_l0_2.CellStyle = style0;
indexCount++;

IRow row_l1 = sheet_Summary.GetRow(indexCount);
if (row_l1 == null)
{
row_l1 = sheet_Summary.CreateRow(indexCount);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 11));
var cell_l1_1 = row_l1.GetCell(3);
if (cell_l1_1 == null)
{
cell_l1_1 = row_l1.CreateCell(3);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 3, 5));
cell_l1_1.SetCellValue("开户行");
cell_l1_1.CellStyle = style0;
var cell_l1_2 = row_l1.GetCell(6);
if (cell_l1_2 == null)
{
cell_l1_2 = row_l1.CreateCell(6);
}
cell_l1_2.SetCellValue(other.Enterprise_BankName);
cell_l1_2.CellStyle = style0;
indexCount++;

IRow row_l2 = sheet_Summary.GetRow(indexCount);
if (row_l2 == null)
{
row_l2 = sheet_Summary.CreateRow(indexCount);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 11));
var cell_l2_1 = row_l2.GetCell(3);
if (cell_l2_1 == null)
{
cell_l2_1 = row_l2.CreateCell(3);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 3, 5));
cell_l2_1.SetCellValue("账号");
cell_l2_1.CellStyle = style0;
var cell_l2_2 = row_l2.GetCell(6);
if (cell_l2_2 == null)
{
cell_l2_2 = row_l2.CreateCell(6);
}
cell_l2_2.SetCellValue(other.Enterprise_BankAccount);
cell_l2_2.CellStyle = style0;
indexCount++;

IRow row_l3 = sheet_Summary.GetRow(indexCount);
if (row_l3 == null)
{
row_l3 = sheet_Summary.CreateRow(indexCount);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount + other.Rowcount - 1, 3, 5));
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 8));
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount,9, 11));
var cell_l3_1 = row_l3.GetCell(3);
if (cell_l3_1 == null)
{
cell_l3_1 = row_l3.CreateCell(3);
}
cell_l3_1.SetCellValue("开票明细");
cell_l3_1.CellStyle = style0;
var cell_l3_2 = row_l3.GetCell(6);
if (cell_l3_2 == null)
{
cell_l3_2 = row_l3.CreateCell(6);
}
cell_l3_2.SetCellValue(other.Invoice_Type.GetName());
cell_l3_2.CellStyle = style0;
var cell_l3_3 = row_l3.GetCell(9);
if (cell_l3_3 == null)
{
cell_l3_3 = row_l3.CreateCell(9);
}
cell_l3_3.SetCellValue("金额");
cell_l3_3.CellStyle = style0;
indexCount++;

for (int i = 0; i < other.InvoiceItems.Count; i++)
{
var indexCost = i + 4;
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 8));
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 9, 11));
IRow rowCost_14 = sheet_Summary.GetRow(contents.Count + 10 + indexCost);
if (rowCost_14 == null)
{
rowCost_14 = sheet_Summary.CreateRow(contents.Count + 10 + indexCost);
}
var cellCost_14_4 = rowCost_14.GetCell(6);
if (cellCost_14_4 == null)
{
cellCost_14_4 = rowCost_14.CreateCell(6);
}
cellCost_14_4.SetCellValue(other.InvoiceItems[i].Invoice_CostName + "(" + other.InvoiceItems[i].Invoice_ReciptTypeText + other.InvoiceItems[i].Invoice_Tax.ToString("#0.00") + "%)");
cellCost_14_4.CellStyle = style0;
var cellCost_14_5 = rowCost_14.GetCell(9);
if (cellCost_14_5 == null)
{
cellCost_14_5 = rowCost_14.CreateCell(9);
}
cellCost_14_5.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(other.InvoiceItems[i].Invoice_Cost,roundType));
cellCost_14_5.CellStyle = style3;
indexCount++;
}
if (other.RewardType == RewardTypes.折让金发票)
{
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 8));
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 9, 11));
IRow rowCost_16 = sheet_Summary.GetRow(indexCount);
if (rowCost_16 == null)
{
rowCost_16 = sheet_Summary.CreateRow(indexCount);
}
var cellCost_16_4 = rowCost_16.GetCell(6);
if (cellCost_16_4 == null)
{
cellCost_16_4 = rowCost_16.CreateCell(6);
}
cellCost_16_4.SetCellValue("合计");
cellCost_16_4.CellStyle = style0;
var cellCost_16_5 = rowCost_16.GetCell(9);
if (cellCost_16_5 == null)
{
cellCost_16_5 = rowCost_16.CreateCell(9);
}
cellCost_16_5.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(other.Invoice_Total,roundType));
cellCost_16_5.CellStyle = style3;
indexCount++;

sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 8));
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 9, 11));
IRow rowCost_17 = sheet_Summary.GetRow(indexCount);
if (rowCost_17 == null)
{
rowCost_17 = sheet_Summary.CreateRow(indexCount);
}
var cellCost_17_4 = rowCost_17.GetCell(6);
if (cellCost_17_4 == null)
{
cellCost_17_4 = rowCost_17.CreateCell(6);
}
cellCost_17_4.SetCellValue("折让金");
cellCost_17_4.CellStyle = style0;
var cellCost_17_5 = rowCost_17.GetCell(9);
if (cellCost_17_5 == null)
{
cellCost_17_5 = rowCost_17.CreateCell(9);
}
cellCost_17_5.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(-1*(other.TotalReward + other.TotalSubsidy),roundType));
cellCost_17_5.CellStyle = style3;
indexCount++;
}

sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 8));
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 9, 11));
IRow rowCost_18 = sheet_Summary.GetRow(indexCount);
if (rowCost_18 == null)
{
rowCost_18 = sheet_Summary.CreateRow(indexCount);
}
var cellCost_18_4 = rowCost_18.GetCell(6);
if (cellCost_18_4 == null)
{
cellCost_18_4 = rowCost_18.CreateCell(6);
}
cellCost_18_4.SetCellValue("开票合计：");
cellCost_18_4.CellStyle = style0;
var cellCost_18_5 = rowCost_18.GetCell(9);
if (cellCost_18_5 == null)
{
cellCost_18_5 = rowCost_18.CreateCell(9);
}
cellCost_18_5.SetCellValue((double)_companyExpenseManager.RandTwoDecimal(other.Invoice_LastTotal,roundType));
cellCost_18_5.CellStyle = style3;
indexCount++;

IRow row_l9 = sheet_Summary.GetRow(indexCount);
if (row_l9 == null)
{
row_l9 = sheet_Summary.CreateRow(indexCount);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 11));
var cell_l9_1 = row_l9.GetCell(3);
if (cell_l9_1 == null)
{
cell_l9_1 = row_l9.CreateCell(3);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 3, 5));
cell_l9_1.SetCellValue("开票抬头：");
cell_l9_1.CellStyle = style0;
var cell_l9_2 = row_l9.GetCell(6);
if (cell_l9_2 == null)
{
cell_l9_2 = row_l9.CreateCell(6);
}
cell_l9_2.SetCellValue(other.Cus_InvoiceTitle);
cell_l9_2.CellStyle = style0;
indexCount++;

IRow row_20 = sheet_Summary.GetRow(indexCount);
if (row_20 == null)
{
row_20 = sheet_Summary.CreateRow(indexCount);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 11));
var cell_20_1 = row_20.GetCell(3);
if (cell_20_1 == null)
{
cell_20_1 = row_20.CreateCell(3);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 3, 5));
cell_20_1.SetCellValue("纳税人识别号：");
cell_20_1.CellStyle = style0;
var cell_20_2 = row_20.GetCell(6);
if (cell_20_2 == null)
{
cell_20_2 = row_20.CreateCell(6);
}
cell_20_2.SetCellValue(other.Cus_TaxNumber);
cell_20_2.CellStyle = style0;
indexCount++;

IRow row_21 = sheet_Summary.GetRow(indexCount);
if (row_21 == null)
{
row_21 = sheet_Summary.CreateRow(indexCount);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 11));
var cell_21_1 = row_21.GetCell(3);
if (cell_21_1 == null)
{
cell_21_1 = row_21.CreateCell(3);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 3, 5));
cell_21_1.SetCellValue("客户公司地址、电话：");
cell_21_1.CellStyle = style0;
var cell_21_2 = row_21.GetCell(6);
if (cell_21_2 == null)
{
cell_21_2 = row_21.CreateCell(6);
}
cell_21_2.SetCellValue(other.Cus_RegisteredAddress + other.Cus_Phone);
cell_21_2.CellStyle = style0;
indexCount++;

IRow row_22 = sheet_Summary.GetRow(indexCount);
if (row_22 == null)
{
row_22 = sheet_Summary.CreateRow(indexCount);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 11));
var cell_22_1 = row_22.GetCell(3);
if (cell_22_1 == null)
{
cell_22_1 = row_22.CreateCell(3);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 3, 5));
cell_22_1.SetCellValue("客户开户行及账号：");
cell_22_1.CellStyle = style0;
var cell_22_2 = row_22.GetCell(6);
if (cell_22_2 == null)
{
cell_22_2 = row_22.CreateCell(6);
}
cell_22_2.SetCellValue(other.Cus_BankName+other.Cus_BankAccount);
cell_22_2.CellStyle = style0;
indexCount++;

IRow row_23 = sheet_Summary.GetRow(indexCount);
if (row_23 == null)
{
row_23 = sheet_Summary.CreateRow(indexCount);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 6, 11));
var cell_23_1 = row_23.GetCell(3);
if (cell_23_1 == null)
{
cell_23_1 = row_23.CreateCell(3);
}
sheet_Summary.AddMergedRegion(new CellRangeAddress(indexCount, indexCount, 3, 5));
cell_23_1.SetCellValue("发票寄送地址：");
cell_23_1.CellStyle = style0;
var cell_23_2 = row_23.GetCell(6);
if (cell_23_2 == null)
{
cell_23_2 = row_23.CreateCell(6);
}
cell_23_2.SetCellValue(other.Cus_Address);
cell_23_2.CellStyle = style0;
indexCount++;
}
#endregion

#region 不固定费用
if (other.NotFixedCosts.Count > 0)
{
IRow row_l0 = sheet_Summary.GetRow(contents.Count + 10);
if (row_l0 == null)
{
row_l0 = sheet_Summary.CreateRow(contents.Count + 10);
}

var cell_l0 = row_l0.GetCell(18);
if (cell_l0 == null)
{
cell_l0 = row_l0.CreateCell(18);
}
cell_l0.SetCellValue("费用名");
cell_l0.CellStyle = style0;

var cell_l1 = row_l0.GetCell(19);
if (cell_l1 == null)
{
cell_l1 = row_l0.CreateCell(19);
}
cell_l1.SetCellValue("金额");
cell_l1.CellStyle = style0;

var cell_12 = row_l0.GetCell(120);
if (cell_12 == null)
{
cell_12 = row_l0.CreateCell(20);
}
cell_12.SetCellValue("金额类型");
cell_12.CellStyle = style0;

var cell_13 = row_l0.GetCell(21);
if (cell_13 == null)
{
cell_13 = row_l0.CreateCell(21);
}
cell_13.SetCellValue("备注");
cell_13.CellStyle = style0;

for (int i = 0; i < other.NotFixedCosts.Count; i++)
{
var indexCost = i + 1;
IRow rowCost_10 = sheet_Summary.GetRow(contents.Count + 10 + indexCost);
if (rowCost_10 == null)
{
rowCost_10 = sheet_Summary.CreateRow(contents.Count + 10 + indexCost);
}

var cellCost_l0 = rowCost_10.GetCell(18);
if (cellCost_l0 == null)
{
cellCost_l0 = rowCost_10.CreateCell(18);
}
cellCost_l0.SetCellValue(string.IsNullOrEmpty(other.NotFixedCosts[i].CostName)? other.NotFixedCosts[i].CostCategories.GetName(): other.NotFixedCosts[i].CostName);
cellCost_l0.CellStyle = style0;

var cellCost_l1 = rowCost_10.GetCell(19);
if (cellCost_l1 == null)
{
cellCost_l1 = rowCost_10.CreateCell(19);
}
cellCost_l1.SetCellValue((double)_companyExpenseManager.RandTwoDecimal( other.NotFixedCosts[i].Cost,roundType));
cellCost_l1.CellStyle = style3;

var cellCost_l2 = rowCost_10.GetCell(20);
if (cellCost_l2 == null)
{
cellCost_l2 = rowCost_10.CreateCell(20);
}
cellCost_l2.SetCellValue(other.NotFixedCosts[i].CostType.GetName());
cellCost_l2.CellStyle = style0;

var cellCost_l3 = rowCost_10.GetCell(21);
if (cellCost_l3 == null)
{
cellCost_l3 = rowCost_10.CreateCell(21);
}
cellCost_l3.SetCellValue(other.NotFixedCosts[i].Remark);
cellCost_l3.CellStyle = style0;
}
}
#endregion

//自适应宽度
for (int j = 0; j <= contents.Count(); j++)
{
sheet_Summary.AutoSizeColumn(j, true);
}
#endregion

if (type == 2)//所有方案多个表，在一个sheet里
{
#region 构造费用表
if (sheetModels.Count() > 0)
{
var detail = sheetModels.FirstOrDefault();
var sheet_detail = hssfworkbook.CreateSheet(detail.SheetName);
#region 标题行和单位
IRow row_child0 = sheet_detail.CreateRow(0);
//建单元格，比如创建A1位置的单元格：
var cell_child_title = row_child0.CreateCell(0);
cell_child_title.SetCellValue(other.Title);
sheet_detail.AddMergedRegion(new CellRangeAddress(0, 0, 0, 50));
cell_child_title.CellStyle = style1;

IRow row_child1 = sheet_detail.CreateRow(1);
var cell_child_companyName = row_child1.CreateCell(0);
cell_child_companyName.SetCellValue("编制单位:" + other.AgreementPayeeCompanyName);
sheet_detail.AddMergedRegion(new CellRangeAddress(1, 1, 0, 4));
cell_child_companyName.CellStyle = style2;

#endregion
int rowIndex_child = 2;//当前位置
int high = 2;//前面表格的行数和
var sumData = sheetModels.Select(p => p.SumData).ToList();
Dictionary<int, object> dic_total_all = GetAllKeyValue_Total(sumData);
foreach (var model in sheetModels)
{
int rowRowNumber_child = 1;
#region 表头
foreach (var title in model.Titles)
{
IRow row = sheet_detail.GetRow(title.Row + rowIndex_child - 2);
if (sheet_detail.GetRow(title.Row + rowIndex_child - 2) == null)
{
row = sheet_detail.CreateRow(title.Row + rowIndex_child - 2);
}
ICell cell = row.CreateCell(title.Cell);
cell.SetCellValue(title.TitleName + title.TitleNameAdd);
//创建、合并单元格、赋值
if (title.isMargin)
{
sheet_detail.AddMergedRegion(new CellRangeAddress(title.firstRow + high - 2, title.lastRow + high - 2, title.firstCol, title.lastCol));
for (int i = title.firstRow + high - 2; i <= title.lastRow + high - 2; i++)
{
var getrow = sheet_detail.GetRow(i);
if (getrow == null)
{
getrow = sheet_detail.CreateRow(i);
}
for (int j = title.firstCol; j <= title.lastCol; j++)
{
ICell singleCell = getrow.GetCell(j);
if (singleCell == null)
{
singleCell = getrow.CreateCell(j);
}
singleCell.CellStyle = style;
}
}
}
cell.CellStyle = style;
}
rowIndex_child += 3;
#endregion
#region 内容
Dictionary<int, object> dic_total = GetKeyValue_Total(model.SumData);
List<HiddenModel> hiddenList_child = new List<HiddenModel>();
foreach (var content in model.Contents)
{
IRow row = null;
if (sheet_detail.GetRow(rowIndex_child) == null)
{
row = sheet_detail.CreateRow(rowIndex_child);
}
else
{
row = sheet_detail.GetRow(rowIndex_child);
}
content.RowNum = rowRowNumber_child.ToString();
#region 构造cell
Dictionary<int, object> dic = GetKeyValue(content);
for (int i = 0; i <= allCellCount; i++)
{
ICell cell = row.CreateCell(i);
if (dic[i] != null)
{
double value = 0;
if (double.TryParse(dic[i].ToString(), out value) && i > 17)
{
value = double.Parse(dic[i].ToString());
if (IsFormulaIndex(i))
{
var formula = GetFormula(rowIndex_child, i, content, content.IsReductionDisabledGuaranteeMoney);
if (formula.IsFormula)
{
cell.SetCellFormula(formula.FormulaStr);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(hssfworkbook);
evaluator.EvaluateFormulaCell(cell);
}
else
{
cell.SetCellValue(value);
}
}
else
{
cell.SetCellValue(value);
if (i > 17 && i <= allCellCount - 1)
{
//合计为0 明细中有不为0 的则去除
if (double.Parse(dic_total_all[i].ToString()) == 0 && !hiddenList_child.Any(p => p.Col == i) && IsHiddenColown(i))
{
hiddenList_child.Add(new HiddenModel() { Col = i, IsHidden = true });
}
else if (value != 0 && hiddenList_child.Any(p => p.Col == i))
{
var hidden = hiddenList_child.FirstOrDefault(p => p.Col == i);
hiddenList_child.Remove(hidden);
}
}
}
}
else
{
if (((i > 6 && i < 13) || i == 16) && double.TryParse(dic[i].ToString(), out value))
{
cell.SetCellValue(value);
}
else
{
cell.SetCellValue(dic[i].ToString());
}
}
}
cell.CellStyle = style_left;
}
#endregion

rowIndex_child++;
rowRowNumber_child++;
}
foreach (var hid in hiddenList_child)
{
if (hid.IsHidden)
{
sheet_detail.SetColumnHidden(hid.Col, hid.IsHidden);
}
}
#endregion
#region 表合计
if (model.Contents.Count() > 0)
{
IRow row_child_sum = sheet_detail.CreateRow(rowIndex_child);
var cell_child_sum = row_child_sum.CreateCell(0);
cell_child_sum.SetCellValue("合计：");
sheet_detail.AddMergedRegion(new CellRangeAddress(rowIndex_child, rowIndex_child, 0, 8));
cell_child_sum.CellStyle = style0;
for (int j = 0; j <= 17; j++)
{
ICell singleCell = row_child_sum.GetCell(j);
if (singleCell == null)
{
singleCell = row_child_sum.CreateCell(j);
}
singleCell.CellStyle = style0;
}

for (int i = 18; i <= allCellCount - 1; i++)
{
ICell cell = row_child_sum.CreateCell(i);
double value = 0;
if (double.TryParse(dic_total[i].ToString(), out value) && i > 17)
{
var formula = GetFormulaTotal(rowIndex_child, i, rowIndex_child - model.Contents.Count() + 1, false);
if (formula == "")
{
value = double.Parse(dic_total[i].ToString());
cell.SetCellValue(value);
}
else
{
cell.SetCellFormula(formula);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(hssfworkbook);
evaluator.EvaluateFormulaCell(cell);
}
}
else
{
cell.SetCellValue(dic_total[i].ToString());
}
cell.CellStyle = style_left;
}
ICell celllastchild_sum = row_child_sum.CreateCell(allCellCount);
celllastchild_sum.CellStyle = style_left;
#endregion
rowIndex_child += 1;
high += 3 + model.Contents.Count() + 1;
}
}
//自适应宽度
for (int j = 0; j <= rowIndex_child - 2; j++)
{
sheet_detail.AutoSizeColumn(j, true);
}
}
#endregion
}
else if (type == 1)//所有方案多个表，在多个sheet里
{
if (sheetModels.Count() > 0)
{
List<SheetModels> models = new List<SheetModels>();
List<ISheet> sheets = new List<ISheet>();
foreach (var model in sheetModels)
{
var sumData = sheetModels.Where(p => p.SheetName == model.SheetName).Select(p=>p.SumData).ToList();
Dictionary<int, object> dic_total_all = GetAllKeyValue_Total(sumData);
if (models.Any(p => p.SheetName == model.SheetName))
{
int rowRowNumber_child = 1;
var sheet_Child = sheets.FirstOrDefault(p => p.SheetName == model.SheetName);
#region 同社保方案在一个sheet拼接
int rownow = sheet_Child.LastRowNum + 1;
#region 表头
foreach (var title in model.Titles)
{
IRow row = sheet_Child.GetRow(title.Row + rownow - 2);
if (sheet_Child.GetRow(title.Row + rownow - 2) == null)
{
row = sheet_Child.CreateRow(title.Row + rownow - 2);
}
ICell cell = row.CreateCell(title.Cell);
cell.SetCellValue(title.TitleName + title.TitleNameAdd);
//创建、合并单元格、赋值
if (title.isMargin)
{
sheet_Child.AddMergedRegion(new CellRangeAddress(title.firstRow + rownow - 2, title.lastRow + rownow - 2, title.firstCol, title.lastCol));
for (int i = title.firstRow + rownow - 2; i <= title.lastRow + rownow - 2; i++)
{
var getrow = sheet_Child.GetRow(i);
if (getrow == null)
{
getrow = sheet_Child.CreateRow(i);
}
for (int j = title.firstCol; j <= title.lastCol; j++)
{
ICell singleCell = getrow.GetCell(j);
if (singleCell == null)
{
singleCell = getrow.CreateCell(j);
}
singleCell.CellStyle = style;
}
}
}
cell.CellStyle = style;
}
#endregion
#region 内容
Dictionary<int, object> dic_total = GetKeyValue_Total(model.SumData);
List<HiddenModel> hiddenList_child = new List<HiddenModel>();
int rowIndex_child = rownow + 3;

foreach (var content in model.Contents)
{
IRow row = null;
if (sheet_Child.GetRow(rowIndex_child) == null)
{
row = sheet_Child.CreateRow(rowIndex_child);
}
else
{
row = sheet_Child.GetRow(rowIndex_child);
}
content.RowNum = rowRowNumber_child.ToString();
#region 构造cell
Dictionary<int, object> dic = GetKeyValue(content);
for (int i = 0; i <= allCellCount; i++)
{
ICell cell = row.CreateCell(i);
if (dic[i] != null)
{
double value = 0;
if (double.TryParse(dic[i].ToString(), out value) && i > 17)
{
value = double.Parse(dic[i].ToString());
if (IsFormulaIndex(i))
{
var formula = GetFormula(rowIndex_child, i, content, content.IsReductionDisabledGuaranteeMoney);
if (formula.IsFormula)
{
cell.SetCellFormula(formula.FormulaStr);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(hssfworkbook);
evaluator.EvaluateFormulaCell(cell);
}
else
{
cell.SetCellValue(value);
}
}
else
{
cell.SetCellValue(value);
if (i > 17 && i <= allCellCount - 1)
{
//合计为0 明细中有不为0 的则去除
if (double.Parse(dic_total_all[i].ToString()) == 0 && !hiddenList_child.Any(p => p.Col == i) && IsHiddenColown(i))
{
hiddenList_child.Add(new HiddenModel() { Col = i, IsHidden = true });
}
else if (value != 0 && hiddenList_child.Any(p => p.Col == i))
{
var hidden = hiddenList_child.FirstOrDefault(p => p.Col == i);
hiddenList_child.Remove(hidden);
}
}
}
}
else
{
if (((i > 6 && i < 13) || i == 16) && double.TryParse(dic[i].ToString(), out value))
{
cell.SetCellValue(value);
}
else
{
cell.SetCellValue(dic[i].ToString());
}
}
}

cell.CellStyle = style_left;
}
#endregion

rowIndex_child++;
rowRowNumber_child++;
}
foreach (var hid in hiddenList_child)
{
if (hid.IsHidden)
{
sheet_Child.SetColumnHidden(hid.Col, hid.IsHidden);
}
}
#endregion
#region 表合计
if (model.Contents.Count() > 0)
{
IRow row_child_sum = sheet_Child.CreateRow(rowIndex_child);
var cell_child_sum = row_child_sum.CreateCell(0);
cell_child_sum.SetCellValue("合计：");
sheet_Child.AddMergedRegion(new CellRangeAddress(rowIndex_child, rowIndex_child, 0, 8));
cell_child_sum.CellStyle = style0;
for (int j = 0; j <= 17; j++)
{
ICell singleCell = row_child_sum.GetCell(j);
if (singleCell == null)
{
singleCell = row_child_sum.CreateCell(j);
}
singleCell.CellStyle = style0;
}
//Dictionary<int, object> dic_total = GetKeyValue_Total(model.SumData);
for (int i = 18; i <= allCellCount - 1; i++)
{
ICell cell = row_child_sum.CreateCell(i);
double value = 0;
if (double.TryParse(dic_total[i].ToString(), out value) && i > 17)
{
var formula = GetFormulaTotal(rowIndex_child, i, rowIndex_child - model.Contents.Count() + 1, false);
if (formula == "")
{
value = double.Parse(dic_total[i].ToString());
cell.SetCellValue(value);
}
else
{
cell.SetCellFormula(formula);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(hssfworkbook);
evaluator.EvaluateFormulaCell(cell);
}
}
else
{
cell.SetCellValue(dic_total[i].ToString());
}
cell.CellStyle = style_left;
}
ICell celllast_child_sum = row_child_sum.CreateCell(allCellCount);
celllast_child_sum.CellStyle = style_left;
}
#endregion
#endregion
}
else
{
int rowRowNumber_child = 1;
models.Add(model);
var sheet_Child = hssfworkbook.CreateSheet(model.SheetName);
sheets.Add(sheet_Child);
#region 构造单独的sheet
#region 标题行和单位
IRow row_child0 = sheet_Child.CreateRow(0);
//建单元格，比如创建A1位置的单元格：
var cell_child_title = row_child0.CreateCell(0);
cell_child_title.SetCellValue(other.Title);
sheet_Child.AddMergedRegion(new CellRangeAddress(0, 0, 0, 40));
cell_child_title.CellStyle = style1;

IRow row_child1 = sheet_Child.CreateRow(1);
var cell_child_companyName = row_child1.CreateCell(0);
cell_child_companyName.SetCellValue("编制单位:" + other.AgreementPayeeCompanyName);
sheet_Child.AddMergedRegion(new CellRangeAddress(1, 1, 0, 4));
cell_child_companyName.CellStyle = style2;

#endregion
#region 表头
foreach (var title in model.Titles)
{
IRow row = sheet_Child.GetRow(title.Row);
if (sheet_Child.GetRow(title.Row) == null)
{
row = sheet_Child.CreateRow(title.Row);
}
ICell cell = row.CreateCell(title.Cell);
cell.SetCellValue(title.TitleName + title.TitleNameAdd);
//创建、合并单元格、赋值
if (title.isMargin)
{
sheet_Child.AddMergedRegion(new CellRangeAddress(title.firstRow, title.lastRow, title.firstCol, title.lastCol));
for (int i = title.firstRow; i <= title.lastRow; i++)
{
var getrow = sheet_Child.GetRow(i);
if (getrow == null)
{
getrow = sheet_Child.CreateRow(i);
}
for (int j = title.firstCol; j <= title.lastCol; j++)
{
ICell singleCell = getrow.GetCell(j);
if (singleCell == null)
{
singleCell = getrow.CreateCell(j);
}
singleCell.CellStyle = style;
}
}
}
cell.CellStyle = style;
}
#endregion
#region 内容
Dictionary<int, object> dic_total = GetKeyValue_Total(model.SumData);
List<HiddenModel> hiddenList_child = new List<HiddenModel>();
int rowIndex_child = 5;

for (int i = 0; i <= allCellCount; i++)
{
ICell cell = row.CreateCell(i);
if (dic[i] != null)
{
double value = 0;
if (double.TryParse(dic[i].ToString(), out value) && i > 17)
{
value = double.Parse(dic[i].ToString());
if (IsFormulaIndex(i))
{
var formula = GetFormula(rowIndex_child, i, content, content.IsReductionDisabledGuaranteeMoney);
if (formula.IsFormula)
{
cell.SetCellFormula(formula.FormulaStr);
IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(hssfworkbook);
evaluator.EvaluateFormulaCell(cell);
}
else
{
cell.SetCellValue(value);
}

}
else
{
cell.SetCellValue(value);
if (i > 17 && i <= allCellCount - 1)
{
//合计为0 明细中有不为0 的则去除
if (double.Parse(dic_total_all[i].ToString()) == 0 && !hiddenList_child.Any(p => p.Col == i) && IsHiddenColown(i))
{
hiddenList_child.Add(new HiddenModel() { Col = i, IsHidden = true });
}
else if (value != 0 && hiddenList_child.Any(p => p.Col == i))
{
var hidden = hiddenList_child.FirstOrDefault(p => p.Col == i);
hiddenList_child.Remove(hidden);
}
}
}
}
else
{
if (((i > 6 && i < 13) || i == 16) && double.TryParse(dic[i].ToString(), out value))
{
cell.SetCellValue(value);
}
else
{
cell.SetCellValue(dic[i].ToString());
}
}
}
cell.CellStyle = style_left;
}
#endregion

var filePath = Path.Combine(modal.FilePath, modal.FileToken);
var writeFileStream = File.Create(filePath);
hssfworkbook.Write(writeFileStream);
hssfworkbook.Close();
writeFileStream.Close();

}

/// <summary>
/// 获取公式（有公式的返回公式公式计算与系统存储的数据不一致的直接用系统数据，公式不展示）
/// </summary>
/// <param name="rowIndex"></param>
/// <param name="cellIndex"></param>
/// <param name="content"></param>
/// <param name="isDisabledGuaranteeMoney"></param>
/// <returns></returns>
private FormulaModel GetFormula(int rowIndex,int cellIndex,CompanyExpenseListDto content, bool isDisabledGuaranteeMoney=false)
{
FormulaModel res = new FormulaModel() { IsFormula = true,FormulaStr="" };
rowIndex++;
int decimalDigits = 2;
int providentfundSchemeDecimalDigits = 2;
var proportion = "";
switch (cellIndex)
{
case 18:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForEndowmentP;
}
if (SchemeManager.Round(content.SchemeRoundTypeForEndowmentP, decimalDigits, content.BaseForEndowmentU * content.ProportionForEndowmentP + content.QuotaForEndowmentP)
!= _companyExpenseManager.RandTwoDecimal(content.AmountForEndowmentP, roundType)|| content.BaseForEndowmentU==0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForEndowmentP * 100 + "%";
if (content.QuotaForEndowmentP > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForEndowmentP, decimalDigits, content.BaseForEndowmentU * content.ProportionForEndowmentP,
"H" + rowIndex + "*" + proportion + "+" + content.QuotaForEndowmentP);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForEndowmentP, decimalDigits, content.BaseForEndowmentU * content.ProportionForEndowmentP,
"H" + rowIndex + "*" + proportion);
}
}
break;
case 19:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForMedicalP;
}
if (SchemeManager.Round(content.SchemeRoundTypeForMedicalP, decimalDigits, content.BaseForMedicalU * content.ProportionForMedicalP + content.QuotaForMedicalP)
!= _companyExpenseManager.RandTwoDecimal(content.AmountForMedicalP,roundType)|| content.BaseForMedicalU==0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForMedicalP * 100 + "%";
if (content.QuotaForMedicalP > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForMedicalP, decimalDigits, content.BaseForMedicalU * content.ProportionForMedicalP,
"I" + rowIndex + "*" + proportion + "+" + content.QuotaForMedicalP);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForMedicalP, decimalDigits, content.BaseForMedicalU * content.ProportionForMedicalP,
"I" + rowIndex + "*" + proportion);
}
}
break;

case 20:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForUnemploymentP;
}
if (SchemeManager.Round(content.SchemeRoundTypeForUnemploymentP, decimalDigits, (content.BaseForUnemploymentU * content.ProportionForUnemploymentP + content.QuotaForUnemploymentP))
!= _companyExpenseManager.RandTwoDecimal(content.AmountForUnemploymentP,roundType) || content.BaseForUnemploymentU == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForUnemploymentP * 100 + "%";
if (content.QuotaForUnemploymentP > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForUnemploymentP, decimalDigits, content.BaseForUnemploymentU * content.ProportionForUnemploymentP,
"J" + rowIndex + "*" + proportion + "+" + content.QuotaForUnemploymentP);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForUnemploymentP, decimalDigits, content.BaseForUnemploymentU * content.ProportionForUnemploymentP,
"J" + rowIndex + "*" + proportion);
}
}
break;
case 21:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForSerioueIllnessP;
}
if (SchemeManager.Round(content.SchemeRoundTypeForSerioueIllnessP, decimalDigits, (content.BaseForSerioueIllnessU * content.ProportionForSerioueIllnessP + content.QuotaForSerioueIllnessP))
!= _companyExpenseManager.RandTwoDecimal(content.AmountForSerioueIllnessP, roundType)||content.BaseForSerioueIllnessU == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForSerioueIllnessP * 100 + "%";
if (content.QuotaForSerioueIllnessP > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForSerioueIllnessP, decimalDigits, content.BaseForSerioueIllnessU * content.ProportionForSerioueIllnessP,
"K" + rowIndex + "*" + proportion + "+" + content.QuotaForSerioueIllnessP);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForSerioueIllnessP, decimalDigits, content.BaseForSerioueIllnessU * content.ProportionForSerioueIllnessP,
"K" + rowIndex + "*" + proportion);
}
}
break;
case 22:
if (content.AmountForEndowmentP + content.AmountForMedicalP + content.AmountForUnemploymentP + content.AmountForSerioueIllnessP != content.TotalForSocialSecurityP)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
res.FormulaStr = "SUM(S" + rowIndex + ":V" + rowIndex + ")";
}
break;
case 23:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForEndowmentU;
}
if (SchemeManager.Round(content.SchemeRoundTypeForEndowmentU, decimalDigits, (content.BaseForEndowmentU * content.ProportionForEndowmentU + content.QuotaForEndowmentU))
!= _companyExpenseManager.RandTwoDecimal(content.AmountForEndowmentU, roundType) || content.BaseForEndowmentU == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForEndowmentU * 100 + "%";
if (content.QuotaForEndowmentU > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForEndowmentU, decimalDigits, content.BaseForEndowmentU * content.ProportionForEndowmentU,
"H" + rowIndex + "*" + proportion + "+" + content.QuotaForEndowmentU);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForEndowmentU, decimalDigits, content.BaseForEndowmentU * content.ProportionForEndowmentU,
"H" + rowIndex + "*" + proportion);
}
}
break;
case 24:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForMedicalU;
}
if (SchemeManager.Round(content.SchemeRoundTypeForEndowmentU, decimalDigits, (content.BaseForMedicalU * content.ProportionForMedicalU + content.QuotaForMedicalU))
!= _companyExpenseManager.RandTwoDecimal(content.AmountForMedicalU, roundType) || content.BaseForMedicalU == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForMedicalU * 100 + "%";
if (content.QuotaForMedicalU > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForEndowmentU, decimalDigits, content.BaseForMedicalU * content.ProportionForMedicalU,
"I" + rowIndex + "*" + proportion + "+" + content.QuotaForMedicalU);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForEndowmentU, decimalDigits, content.BaseForMedicalU * content.ProportionForMedicalU,
"I" + rowIndex + "*" + proportion);
}
}
break;
case 25:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForUnemploymentU;
}
if (SchemeManager.Round(content.SchemeRoundTypeForUnemploymentU, decimalDigits, (content.BaseForUnemploymentU * content.ProportionForUnemploymentU + content.QuotaForUnemploymentU))
!= _companyExpenseManager.RandTwoDecimal(content.AmountForUnemploymentU, roundType) || content.BaseForUnemploymentU == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForUnemploymentU * 100 + "%";
if (content.QuotaForUnemploymentU > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForUnemploymentU, decimalDigits, content.BaseForUnemploymentU * content.ProportionForUnemploymentU,
"J" + rowIndex + "*" + proportion + "+" + content.QuotaForUnemploymentU);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForUnemploymentU, decimalDigits, content.BaseForUnemploymentU * content.ProportionForUnemploymentU,
"J" + rowIndex + "*" + proportion);
}
}
break;
case 26:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForSerioueIllnessU;
}
if (SchemeManager.Round(content.SchemeRoundTypeForSerioueIllnessU, decimalDigits, (content.BaseForSerioueIllnessU * content.ProportionForSerioueIllnessU + content.QuotaForSerioueIllnessU))
!= _companyExpenseManager.RandTwoDecimal(content.AmountForSerioueIllnessU, roundType) || content.BaseForSerioueIllnessU == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForSerioueIllnessU * 100 + "%";
if (content.QuotaForSerioueIllnessU > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForSerioueIllnessU, decimalDigits, content.BaseForSerioueIllnessU * content.ProportionForSerioueIllnessU,
"K" + rowIndex + "*" + proportion + "+" + content.QuotaForSerioueIllnessU);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForSerioueIllnessU, decimalDigits, content.BaseForSerioueIllnessU * content.ProportionForSerioueIllnessU,
"K" + rowIndex + "*" + proportion);
}
}
break;
case 27:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForInductrialInjury;
}
if (SchemeManager.Round(content.SchemeRoundTypeForInductrialInjury, decimalDigits, (content.BaseForInductrialInjury * content.ProportionForInductrialInjury + content.QuotaForInductrialInjury))
!= _companyExpenseManager.RandTwoDecimal(content.AmountForInductrialInjury, roundType) || content.BaseForInductrialInjury == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForInductrialInjury * 100 + "%";
if (content.QuotaForInductrialInjury > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForInductrialInjury, decimalDigits, content.BaseForInductrialInjury * content.ProportionForInductrialInjury,
"L" + rowIndex + "*" + proportion + "+" + content.QuotaForInductrialInjury);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForInductrialInjury, decimalDigits, content.BaseForInductrialInjury * content.ProportionForInductrialInjury,
"L" + rowIndex + "*" + proportion);
}
}
break;
case 28:
if (content.DecimalDigits == 2 || content.DecimalDigits == 1)
{
decimalDigits = content.DecimalDigitsForFertility;
}
if (SchemeManager.Round(content.SchemeRoundTypeForFertility, decimalDigits, (content.BaseForFertility * content.ProportionForFertility + content.QuotaForFertility))
!= _companyExpenseManager.RandTwoDecimal(content.AmountForFertility, roundType) || content.BaseForFertility == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForFertility * 100 + "%";
if (content.QuotaForFertility > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForFertility, decimalDigits, content.BaseForFertility * content.ProportionForFertility,
"M" + rowIndex + "*" + proportion + "+" + content.QuotaForFertility);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.SchemeRoundTypeForFertility, decimalDigits, content.BaseForFertility * content.ProportionForFertility,
"M" + rowIndex + "*" + proportion);
}
}

break;
case 29:
if (content.AmountForEndowmentU + content.AmountForMedicalU + content.AmountForUnemploymentU + content.AmountForSerioueIllnessU
+content.AmountForInductrialInjury+content.AmountForFertility != content.TotalForSocialSecurityU)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
res.FormulaStr = "SUM(X" + rowIndex + ":AC" + rowIndex + ")";
}
break;
case 30:
if (content.ProvidentfundSchemeDecimalDigits == 2 || content.ProvidentfundSchemeDecimalDigits == 1)
{
providentfundSchemeDecimalDigits = content.ProvidentfundSchemeDecimalDigitsP;
}
if (SchemeManager.Round(content.ProvidentfundSchemeRoundTypeP, providentfundSchemeDecimalDigits, (content.BaseForProvidentfundU * content.ProportionForProvidentfundP + content.QuotaForProvidentfundP))
!= _companyExpenseManager.RandTwoDecimal(content.AmountForProvidentfundP, roundType) || content.BaseForProvidentfundU == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForProvidentfundP * 100 + "%";
if (content.QuotaForProvidentfundP > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.ProvidentfundSchemeRoundTypeP, providentfundSchemeDecimalDigits, content.BaseForProvidentfundU * content.ProportionForProvidentfundP,
"Q" + rowIndex + "*" + proportion + "+" + content.QuotaForProvidentfundP);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.ProvidentfundSchemeRoundTypeP, providentfundSchemeDecimalDigits, content.BaseForProvidentfundU * content.ProportionForProvidentfundP,
"Q" + rowIndex + "*" + proportion);
}
}

break;
case 31:
if (content.ProvidentfundSchemeDecimalDigits == 2 || content.ProvidentfundSchemeDecimalDigits == 1)
{
providentfundSchemeDecimalDigits = content.ProvidentfundSchemeDecimalDigitsU;
}
if (SchemeManager.Round(content.ProvidentfundSchemeRoundTypeU, providentfundSchemeDecimalDigits,(content.BaseForProvidentfundU * content.ProportionForProvidentfundU + content.QuotaForProvidentfundU) )
!= _companyExpenseManager.RandTwoDecimal(content.AmountForProvidentfundU, roundType) || content.BaseForProvidentfundU == 0)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
proportion = content.ProportionForProvidentfundU * 100 + "%";
if (content.QuotaForProvidentfundU > 0)
{
res.FormulaStr = GetRoundTypeFormula(content.ProvidentfundSchemeRoundTypeU, providentfundSchemeDecimalDigits, content.BaseForProvidentfundU * content.ProportionForProvidentfundU,
"Q" + rowIndex + "*" + proportion + "+" + content.QuotaForProvidentfundU);
}
else
{
res.FormulaStr = GetRoundTypeFormula(content.ProvidentfundSchemeRoundTypeU, providentfundSchemeDecimalDigits, content.BaseForProvidentfundU * content.ProportionForProvidentfundU,
"Q" + rowIndex + "*" + proportion);
}
}
break;
case 32:
if (content.AmountForProvidentfundP + content.AmountForProvidentfundU != content.TotalForProvidentfund)
{
res.IsFormula = false;
res.FormulaStr = "";
}
else
{
res.FormulaStr = "SUM(AE" + rowIndex + ":AF" + rowIndex + ")";
}
break;
case 52:
if (isDisabledGuaranteeMoney)
{
res.FormulaStr = "SUM(W" + rowIndex + ",AD" + rowIndex + ",AG" + rowIndex + ",AI" + rowIndex + ":AM" + rowIndex + ",AO" + rowIndex + ":AZ" + rowIndex + ")";
}
else
{
res.FormulaStr = "SUM(W" + rowIndex + ",AD" + rowIndex + ",AG" + rowIndex + ":AM" + rowIndex + ",AO" + rowIndex + ":AZ" + rowIndex+ ")";
}
break;
default:
res.IsFormula = false;
res.FormulaStr = "";
break;
}

return res;
}

导出excel 包含隐藏列 公式 多个sheet构造 合并单元格（懒得改了，部分代码展示）

导出excel 包含隐藏列公式多个sheet构造合并单元格（懒得改了，部分代码展示）