protected override void SetCellValue(ICell cell, int rowIndex, string drValue, ColumnsMapping columns)
{
if (columns.ColumnsIndex == 13 || columns.ColumnsIndex == 14)
{
int currRowIndex = rowIndex + 1;
string colD = string.Format("{0}{1}", CellReference.ConvertNumToColString(3), currRowIndex); // 邮政地勤费 GroundHandlingFee
string colE = string.Format("{0}{1}", CellReference.ConvertNumToColString(4), currRowIndex); // 邮政仓租 CostStoreFee
string colF = string.Format("{0}{1}", CellReference.ConvertNumToColString(5), currRowIndex); // 邮政运费 CostExpressFee
string colG = string.Format("{0}{1}", CellReference.ConvertNumToColString(6), currRowIndex); // 邮政邮件处理费 CostOperateFee
string colH = string.Format("{0}{1}", CellReference.ConvertNumToColString(7), currRowIndex); // 其他费用 CostOtherFee
string colJ = string.Format("{0}{1}", CellReference.ConvertNumToColString(8), currRowIndex); // 客户提货费 InComeLoadFee
string colK = string.Format("{0}{1}", CellReference.ConvertNumToColString(9), currRowIndex); // 客户仓租 InComeStoreFee
string colL = string.Format("{0}{1}", CellReference.ConvertNumToColString(10), currRowIndex); // 客户运费 InComeExpressFee
string colM = string.Format("{0}{1}", CellReference.ConvertNumToColString(11), currRowIndex); // 客户操作费 InComeOperateFee
string colN = string.Format("{0}{1}", CellReference.ConvertNumToColString(12), currRowIndex); // 其他费用 InComeOtherFee
string DToH = string.Format("({0} + {1} + {2} + {3} + {4})", colD, colE, colF, colG, colH);
string JToN = string.Format("({0} + {1} + {2} + {3} + {4})", colJ, colK, colL, colM, colN);
cell.CellStyle = this.ContentsStyle;
cell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
if (columns.ColumnsIndex == 13)
{
// (J3 + K3 + L3 + M3 + N3) - (D3 + E3 + F3 + G3 + H3)
string formula = string.Format("{0} - {1}", JToN, DToH);
cell.SetCellFormula(formula); // 设置公式
}
else if (columns.ColumnsIndex == 14)
{
// if ((J3 + K3 + L3 + M3 + N3) = 0, 0, (1 - (D3 + E3 + F3 + G3 + H3) / (J3 + K3 + L3 + M3 + N3)) * 100)
string formula = string.Format("if ({0} = 0, 0, (1 - {1} / {2}) * 100)", JToN, DToH, JToN);
cell.SetCellFormula(formula); // 设置公式
}
}
else
{
base.SetCellValue(cell, rowIndex, drValue, columns);
}
}
/// <summary>
/// 设置总计单元格的数据
/// </summary>
/// <param name="cell">总计单元格</param>
/// <param name="rowIndex">当前行的索引</param>
/// <param name="startRowIndex">内容数据的开始行</param>
/// <param name="columns">当前列信息</param>
protected virtual void SetTotalCellValue(ICell cell, int rowIndex, int startRowIndex, ColumnsMapping columns)
{
if (columns.IsTotal)
{
string colItem = CellReference.ConvertNumToColString(columns.ColumnsIndex);
cell.CellStyle = totalStyle;
cell.SetCellFormula(string.Format("SUM({0}{1}:{2}{3})", colItem, startRowIndex, colItem, rowIndex));
}
}
protected override void SetTotalCellValue(ICell cell, int rowIndex, int startRowIndex, ColumnsMapping columns)
{
if (columns.ColumnsIndex == 0)
{
cell.CellStyle = this.TotalStyle;
cell.SetCellValue("总计：");
}
else
{
base.SetTotalCellValue(cell, rowIndex, startRowIndex, columns);
}
}
/// <summary>
/// 设置单元格的数据
/// </summary>
/// <param name="cell">单元格对像</param>
/// <param name="rowIndex">单元格行索引</param>
/// <param name="drValue">单元格数据</param>
/// <param name="columns">单元格的列信息</param>
protected virtual void SetCellValue(ICell cell, int rowIndex, string drValue, ColumnsMapping columns)
{
cell.CellStyle = contentsStyle;
if (!string.IsNullOrEmpty(columns.ColumnsData))
{
PropertyInfo info = GetObjectProperty(columns.ColumnsData);
switch (info.PropertyType.FullName)
{
case "System.String": //字符串类型
double result;
if (IsNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
cell.SetCellValue(result);
break;
}
else
{
cell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
cell.SetCellValue(dateV);
cell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
cell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
cell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
cell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
cell.SetCellValue("");
break;
default:
cell.SetCellValue("");
break;
}
}
else
{
cell.SetCellValue("");
}
}
protected override void SetCellValue(ICell cell, int rowIndex, string drValue, ColumnsMapping columns)
{
if (columns.ColumnsIndex == 11 || columns.ColumnsIndex == 12)
{
int currRowIndex = rowIndex + 1;
string colE = string.Format("{0}{1}", CellReference.ConvertNumToColString(4), currRowIndex); // 邮政邮资 WayBillFee
string colF = string.Format("{0}{1}", CellReference.ConvertNumToColString(5), currRowIndex); // 邮政邮件处理费 ProcessingFee
string colG = string.Format("{0}{1}", CellReference.ConvertNumToColString(6), currRowIndex); // 其他费用 CostOtherFee
string colH = string.Format("{0}{1}", CellReference.ConvertNumToColString(7), currRowIndex); // 客户运费 ExpressFee
string colI = string.Format("{0}{1}", CellReference.ConvertNumToColString(8), currRowIndex); // 客户操作费 OperateFee
string colJ = string.Format("{0}{1}", CellReference.ConvertNumToColString(9), currRowIndex); // 客户其他费用 InComeOtherFee
string HToJ = string.Format("({0} + {1} + {2})", colH, colI, colJ);
string EToG = string.Format("({0} + {1} + {2})", colE, colF, colG);
cell.CellStyle = this.ContentsStyle;
if (columns.ColumnsIndex == 11)
{
//(H7 + I7 + J7) - (E7 + F7 + G7)
cell.SetCellFormula(string.Format("{0} - {1}", HToJ, EToG)); // 设置公式
}
else if (columns.ColumnsIndex == 12)
{
// if ((H7 + I7 + J7) = 0, 0, ((H7 + I7 + J7) - (E7 + F7 + G7) / (H7 + I7 + J7))
cell.SetCellFormula(string.Format("if ({0} = 0, 0, ({1} - {2} / {3})", HToJ, HToJ, EToG, HToJ)); // 设置公式
}
}
else
{
base.SetCellValue(cell, rowIndex, drValue, columns);
}
}
