(C# - Excel) How do I write a formula to a newly created column for each existing row?

Question

Say you have an excel file with a bunch of numbers and rows, about 100 rows.

I want to programatically write a column with a formula that adds the values of a bunch of fields together for each existing row.

So first of all, of course, I would create the Openfiledialog for the Excel file, find the path, open it up through OleDb. Got this down. Now say I have the following table:

[Friends | Money | Money Lost | Days passed | Solution]

Bilbo , 50 , 50 , 7 , *Formula here

Bilso , 80 , 50 , 7 , *Formula here

etc...

Problem is, I don't have an exact number for the rows so I'll have to enumerate whatever is found by the OleDb connection. The formula would be something like =(B2-C2)*D2 but 2 is also wrong and it would have to be relative to whatever row it's on, ie: row 3 would be =(B3-C3)*D3. I'm not sure how to do this.

I found out writing directly to the cell also doesn't make the formula process the numbers outright.

=====

EDIT:

    private Excel.Application Xls;
    private Excel.Workbooks WBs;
    private Excel.Workbook WB;
    private Excel.Worksheet WS;
    private Excel.Sheets SS;
    Excel.Range cellsRange = null;
    Excel.Range columnRange = null;
    Excel.Range rowRange = null;
    int numberOfColumns = 0;
    int numberOfRows = 0;
    private void btnColumn_Click(object sender, EventArgs e)
    {
        if (btnColumn.FlatStyle == FlatStyle.Flat)
            btnColumn.FlatStyle = FlatStyle.Standard;
        else
        {
            btnColumn.FlatStyle = FlatStyle.Flat;
        }

        OpenFileDialog openFileDialog = new OpenFileDialog();
        openFileDialog.Filter = "Excel Files|*.xls;*.xlsx";
        openFileDialog.ShowDialog();
        string pather = openFileDialog.FileName;

        if (pather == "" || pather == " " || pather == null)
        {
            return;
        }

        if (!string.IsNullOrEmpty(openFileDialog.FileName))
        {
            try
            {
                Xls = new Excel.Application();
                WBs = Xls.Workbooks;
                WB = WBs.Open(pather, 0, false, 5, "", "", true,
                    XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                SS = WB.Worksheets;
                WS = SS.get_Item(1);                    
                cellsRange = WS.Cells;
                columnRange = cellsRange.Columns;
                rowRange = cellsRange.Rows;
                numberOfColumns = columnRange.Count;
                numberOfRows = rowRange.Count;
                int LastCell = numberOfColumns+1;
                WS.Cells[1, LastCell] = "Tax Formula";
                for (int i = 2; i < numberOfRows; i++)
                {
                    //string niceFormula = "=SUM(L" + i + ",M" + i + ")*N"+ i ;
                    //WS.Cells[i, LastCell].Formula = niceFormula;
                    WS.Cells[i, LastCell].Value = (WS.Cells[i, 12] + WS.Cells[i, 13]) * WS.Cells[i, 14];
                }
                //==========================
                WB.Save();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Write Excel: " + ex.Message);
            }
            finally
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
                WB.Close();
                Xls.Quit();

                releaseObject(cellsRange);
                releaseObject(columnRange);
                releaseObject(rowRange);
                releaseObject(SS);
                releaseObject(WS);
                releaseObject(WBs);
                releaseObject(WB);
                releaseObject(Xls);
            }
        MessageBox.Show("Finished Updating File", "Task complete");
    }
}

Anyone know why this code is throwing the following error on write attempt?

HRESULT: 0x800A03EC

Repasted entire code for your convenience. It's still spitting out the HRESULT error.

Targeted item is 4 rows deep with 1 row of headers and about 16 columns wide.

It would be slow to process each cell values one by one. I would suggest you to get the entire data in the Excel into a data table and do the calculation (using LINQ or regular loops) and add the calculated value to a new column within the same data table. At the end export the data table to Excel sheet in the required format. — Souvik Ghosh, Nov 29 '16 at 08:24
you did not tell us, how you write to the date. VBA? Or a Winform Application with Excel Interop? Or a WinForm Application with EPPlus? Please specify more details. — nabuchodonossor, Nov 29 '16 at 08:36
@nabuchodonossor, I initially left it open because sometimes someone would come in ready with a VBA answer but get turned-off by the C# tag. In some cases I can convert the logic myself into one suitable for C#, that way there would be more total answers, but yeah, this is Winform with Excel Interop. — Avan, Nov 29 '16 at 09:15
@SouvikGhosh Okay, I suppose I now how to add the data to a data table though I'm not sure how to do the opposite, (data table to excel). But afterwards I'm not sure how to go about it. Not an expert at all with LinQ but I know my way around editing one. — Avan, Nov 29 '16 at 09:18
There's a ton of articles and answer to export datatable to excel. Here is one- http://stackoverflow.com/questions/8207869/how-to-export-datatable-to-excel. I suppose you can ignore the LINQ for now. Just do the calculation within a regular loop and update the data table accordingly. — Souvik Ghosh, Nov 29 '16 at 09:23
@Vendredi: You create your formula as string (=B...-C...)*D... (replace the dots with your row) and put this string into the cell. And of course you should work with a range, not a single cell. There is no big performance problem with writing thousends of cells (if you write a range at once) — nabuchodonossor, Nov 29 '16 at 09:47
@nabuchodonossor, So I'll fill you in on what I've gotten so far. Check my edit of the OP, i dont know how to add code into a comment. As you can see, I've got the .Formula = *Formula here* but I'm not sure how to make it relative to the respective row. I managed to create the column location and the row ceiling though. I'm not quite sure what you mean by the formula string, is it like (=B1-C2)*D2? Then i'd be hardcoding it and not making it relative... oh wait. Let me experiment abit first. — Avan, Nov 30 '16 at 04:49
@nabuchodonossor Alright, i edited it abit to reflect what you suggested but it's throwing a HRESULT: 0x800A03EC Not sure what's wrong really. — Avan, Nov 30 '16 at 05:12
Why use a formula at all instead of simply doing the sums via C#? `WS.Cells[i, LastCell].Value = (WS.Cells[i, 12] + WS.Cells[i, 13]) * WS.Cells[i, 14];` — user1274820, Nov 30 '16 at 05:16
Well, @user1274820, the program is targeted to automate my friend's excel tasks and the formula is a required footprint for their accountant's double checking. At any rate, I actually plugged your code in as 'WS.Cells[i, LastCell].Value = WS.Cells[i, 12] + WS.Cells[i, 13] * WS.Cells[i, 14];' But it threw the same error as above HRESULT something. I researched abit about the error code and it seems to be related to ranges being out of bound? or the file being xls not xlsx, to which both of these are not the cause because it is an xlsx file and i'm literally modifying 3 rows at most. — Avan, Nov 30 '16 at 05:24
Use `numberOfColumns = WS.UsedRange.Columns.Count; numberOfRows = WS.UsedRange.Rows.Count;` — user1274820, Nov 30 '16 at 06:49
You're a genius @user1274820 ! Thank you so much. Your answer disappeared so I can't mark it as correct right now! Any particular reason why CountLarge/Long was the answer? I assumed those were necessary only for large ranges, seeing as I'm only modifying 3 rows it didn't seem necessary. — Avan, Dec 01 '16 at 04:40
Oh and @user1274820, I can't find your Excel to DataTable suggestion, could you re-send that too? I wasn't able to read it thoroughly — Avan, Dec 01 '16 at 05:32
Hey sorry I deleted my answer - wasn't sure if it actually helped you (was doing it late at night and was a bit burnt out). I undeleted it. I also added the data table fill code back into it — user1274820, Dec 02 '16 at 15:52

score 2 · Accepted Answer · edited May 23 '17 at 10:30

Edit:

You may want to try this:

numberOfColumns = WS.UsedRange.Columns.CountLarge;
numberOfRows = WS.UsedRange.Rows.CountLarge;

Ugh, also change them to long instead of int if you use CountLarge instead of Count

Okay - I got this working finally - no clue why I was having so many issues.

For me, I apparently have to make the application visible or it doesn't work.

There may be another workaround - I pulled it from here: https://stackoverflow.com/a/17061714/1274820

This code worked for me finally:

Note that this is a console application (figured I would rule that out), but you should just be able to add the magic lines.

Application excelApp = new Application();
excelApp.SheetsInNewWorkbook = 1;
////////////////////////////////////////////
//Add these lines to make it work???
////////////////////////////////////////////
try {
    excelApp.Visible = true;
}
catch {} 
////////////////////////////////////////////
Workbook excelWB = excelApp.Workbooks.Open(@"C:\test.xls", Type.Missing, false);
_Worksheet excelWS = excelWB.Sheets[1];
Range cellsRange = excelWS.UsedRange;
long LastCell = cellsRange.Columns.CountLarge + 1;
long numberOfRows = cellsRange.Rows.CountLarge;
excelWS.Cells[1, LastCell] = "Tax Formula";
for (int i = 2; i <= numberOfRows; i++)
{
    string niceFormula = "=SUM(L" + i + ",M" + i + ")*N" + i;
    excelWS.Cells[i, LastCell].Formula = niceFormula;
}
excelWB.Close(true);
excelApp.Quit();

Before:

After:

How to fill a DataTable with an excel spreadsheet:

//////////////////////////////////////////////////////////////////////////////////
//This function is absolute magic >.> - Fill DataTable with excel spreadsheet
//HDR=YES means "Spreadsheet has headers" Change to NO if not.
//name = "Log"; - This is the Sheet name to pull the data from
//////////////////////////////////////////////////////////////////////////////////
//oconn takes an SQL like command (Select Everything from name sheet) using con
//HDR=YES means that our data has headers :)
//////////////////////////////////////////////////////////////////////////////////
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + copyToPath + ";Extended Properties='Excel 12.0 XML;HDR=YES;';";
OleDbConnection con = new OleDbConnection(constr);
OleDbCommand oconn = new OleDbCommand("Select * From [Log$]", con);
con.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
DataTable data = new DataTable();
sda.Fill(data);
con.Close();
//////////////////////////////////////////////////////////////////////////////////

Huh, that is alot neater than what I had around, though I wasn't planting it into a datatable, but rather a DataGridView. Using this, how would you access the data in, data? Like you would an array? int `foo = data[1,1]`? The only times I've worked with a Data Table were to fill a DataGridView (Which I _then_ interfaced) abit round about but it worked. Oh and btw, as I commented above, I tried your code but I'm getting the same error as before. Perhaps there's something wrong with my range declarations i'm not noticing? — Avan, Nov 30 '16 at 05:38
I'm working backwards on yours atm - you declare `Xls` and `Wbs` twice in your code so there's obviously an issue there. You're also getting the workbooks object before you open the file. That's probably not a good idea. — user1274820, Nov 30 '16 at 05:39
I posted my entire code, without the formula bit, a similar code works in another function that simply adds values to cells, so the basic structure of the code should be working. Yet I'm still getting the HRESULT error for some reason. — Avan, Nov 30 '16 at 06:10
`try { Xls.Visible = true; } catch {} ` Add that after you declare Xls — user1274820, Nov 30 '16 at 06:28
I take it back - your whole problem is probably different than mine - you're just counting the cells in your range. Use `numberOfColumns = WS.UsedRange.Columns.CountLarge;` `numberOfRows = WS.UsedRange.Rows.CountLarge;` — user1274820, Nov 30 '16 at 06:31
also change them to long instead of int if you use CountLarge instead of Count — user1274820, Nov 30 '16 at 06:35

score 0 · Answer 2 · answered Nov 30 '16 at 05:40

0

Why not use the R1C1 reference style? That way your formula does not need to be relative to the column.

For reference: https://excelmate.wordpress.com/2013/04/22/excel-r1c1-reference-style-vs-a1/

answered Nov 30 '16 at 05:40

CBoolMe

41
8

(C# - Excel) How do I write a formula to a newly created column for each existing row?

2 Answers2