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); } }