I am working on exporting some information to excel using EPPlus, and I hit the stumbling block in the summary line using formulas. When you open the file for the first time, only the first formula is calculated, and the rest are calculated only when I turn on and leave the formula cell twice. Below is the code that creates the table and the result. Hopefully someone can offer me some insight on how to get around this problem.
private void BuildSalesTable(string label, ISalesTable table) { var sheet = ExcelPackage.Workbook.Worksheets.Add(string.Format("Sales{0}", label)); //table header //BuildHeader(startRow, 2, startRow, 9, string.Format("Sales Tons - {0}", label)); //table lables sheet.Cells[1, 2].Value = "Joist Qtd"; sheet.Cells[1, 3].Value = "PP/Ton"; sheet.Cells[1, 4].Value = "Deck Qtd"; sheet.Cells[1, 5].Value = "PP/Ton"; sheet.Cells[1, 6].Value = "Joist Sold"; sheet.Cells[1, 7].Value = "PP/Ton"; sheet.Cells[1, 8].Value = "Deck Sold"; sheet.Cells[1, 9].Value = "PP/Ton"; for (int i = 0; i < table.Rows.Count; i++) { var row = 2 + i; //every other row coloring if (i == 0 || i%2 == 0) { sheet.Cells[row, 1, row, 9].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[row, 1, row, 9].Style.Fill.BackgroundColor.SetColor(Color.Khaki); } //data sheet.Cells[row, 1].Value = table.Rows[i].Location; sheet.Cells[row, 2].Value = table.Rows[i].JoistQuoted; sheet.Cells[row, 3].Value = table.Rows[i].JoistQuotedPP; sheet.Cells[row, 4].Value = table.Rows[i].DeckQuoted; sheet.Cells[row, 5].Value = table.Rows[i].DeckQuotedPP; sheet.Cells[row, 6].Value = table.Rows[i].JoistSold; sheet.Cells[row, 7].Value = table.Rows[i].JoistSoldPP; sheet.Cells[row, 8].Value = table.Rows[i].DeckSold; sheet.Cells[row, 2].Value = table.Rows[i].JoistQuoted; sheet.Cells[row, 9].Value = table.Rows[i].DeckSoldPP; } //totals row sheet.Cells[table.Rows.Count + 2, 1].Value = "Total"; sheet.Cells[table.Rows.Count + 2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[table.Rows.Count + 2, 1].Style.Font.Color.SetColor(Color.Gray); sheet.Cells[table.Rows.Count + 2, 2].Formula = string.Format("=SUM(B2:B{0})", table.Rows.Count + 1); sheet.Cells[table.Rows.Count + 2, 3].Value = string.Format("=SUMPRODUCT(B2:B{0},C2:C{0})/SUM(B2:B{0})", table.Rows.Count + 1); sheet.Cells[table.Rows.Count + 2, 4].Value = string.Format("=SUM(D2:D{0})", table.Rows.Count + 1); sheet.Cells[table.Rows.Count + 2, 5].Value = string.Format("=SUMPRODUCT(D2:D{0},E2:E{0})/SUM(D2:D{0})", table.Rows.Count + 1); sheet.Cells[table.Rows.Count + 2, 6].Value = string.Format("=SUM(F2:F{0})", table.Rows.Count + 1); sheet.Cells[table.Rows.Count + 2, 7].Value = string.Format("=SUMPRODUCT(F2:F{0},G2:G{0})/SUM(F2:F{0})", table.Rows.Count + 1); sheet.Cells[table.Rows.Count + 2, 8].Value = string.Format("=SUM(H2:H{0})", table.Rows.Count + 1); sheet.Cells[table.Rows.Count + 2, 9].Value = string.Format("=SUMPRODUCT(H2:H{0},I2:I{0})/SUM(H2:H{0})", table.Rows.Count + 1); sheet.Cells[table.Rows.Count + 2, 2, table.Rows.Count + 2, 9].Style.Font.Color.SetColor(Color.DarkBlue); //format data sheet.Cells[2, 1, 2 + table.Rows.Count, 10].Style.Numberformat.Format = "#,##0"; sheet.Workbook.CalcMode = ExcelCalcMode.Automatic; }
Excel is as follows:
