By LRFalk01


2013-08-01 13:30:59 8 Comments

I am working on exporting some information into excel using EPPlus, and I have hit a stumbling block on the totals row with formulas. Only the first formula is calculated when I open the file, and the others only calculate when I double go into and leave the formula cell. Below is the code that builds the table, and the result. Hopefully someone can offer me some insight on how to work around this issue.

    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 looks like this:

Temporary image of excel document

1 comments

@LRFalk01 2013-08-01 13:44:06

I am an idiot. I was setting the values for the cells that were not running the formula instead of setting the formula. I cannot believe how long it took me to see this.

@MatthewD 2015-08-06 20:06:09

Happens to the best of us.

Related Questions

Sponsored Content

1 Answered Questions

how to sum values in a table with multiple criterias

6 Answered Questions

[SOLVED] How do I prevent Excel from automatically replicating formulas in tables?

6 Answered Questions

[SOLVED] set sum formula in excel using vba

2 Answered Questions

[SOLVED] Formula to condense the difference of two sums

1 Answered Questions

1 Answered Questions

[SOLVED] EPPlus formatting value from formula

  • 2015-11-21 14:21:55
  • GreenGeckoZA
  • 798 View
  • 1 Score
  • 1 Answer
  • Tags:   c# epplus

4 Answered Questions

[SOLVED] Use formula in custom calculated field in Pivot Table

1 Answered Questions

Excel formula sumproduct consuming more time (EPPlus)

  • 2014-05-29 10:39:46
  • atulya
  • 242 View
  • 0 Score
  • 1 Answer
  • Tags:   excel epplus

1 Answered Questions

2 Answered Questions

[SOLVED] Custom Formula for Grand Total column

Sponsored Content