Is there a way to programmatically insert formulas into a csv file using vb.net?

Question

I need to add formulas to a CSV file that already exists. Can this be done using VB.Net?

The idea is that I already have a CSV file, and I need one column that will be filled in each cell using a special formula. This must be done programmatically because these files are created dynamically.

Does anyone know how to do this? Thanks.

Answer 1

Although I adhere to the fact that my initial answer is technically correct, I received a lot of negative reviews about this. Apparently, popular spreadsheet software such as Microsoft Excel, Open Office, Libre Office Calc will calculate the formulas that are entered into the CSV file. However, I would not recommend relying on this opportunity.

Original answer:
CSV format does not support formulas. This is a simple text format.

Answer 2

1,2,3, = SUM (A1: C1)

Surprised me when I tried, but Excel will save the formula.

You can even export formulas to CSV by first displaying them on the screen. (CTRL + `)

Answer 3

You can import the formula into excel via a text file using comma-separated values. Just remember to make sure it is specified with the suffix .txt.

Then import.

My import example (data table, from text)

Column1, Column2, ResultColumn

1,2, = A2 + B2

He imported and calculated only thin

Answer 4

Are you creating a CSV file? If so, consider writing the actual Excel file. (I assume that you are importing into Excel, since you used the term "cell", which does not make sense in CSV.)

Here is a link on how to do this: Create an Excel file (.XLS and .XLSX) with C #

If you do not create a CSV, and if you want to add a new calculated value (instead of a formula that will dynamically change as the cell values ​​change), you can do this quite easily by reading in a CSV file, parsing each line is enough to get the values ​​you need for your formula, calculating the result and adding it (after the decimal point) to each line before writing the line to a new file.

Answer 5

You can open csv in Excel and then add the formulas in Excel and save back to csv. This can be done using the Microsoft Excel 11.0 object library. Then

dim wb as Excel.Workbook dim exApp as New Excel.Application dim exSheet as Excel.Worksheet dim rowNum as integer = 1 wb = System.Runtime.InteropServices.Marshal.BindToMoniker(pathAndFileNameOfCSV) exApp = wb.Parent exApp.DisplayAlerts = False exApp.AlertBeforeOverwriting = False exSheet = exApp.ActiveWorkbook.Sheets.Item(1) 'do your functions in a loop here ie exSheet.Range("A" & rowNum).Value = "=SUM($A$1:$D$4)" rowNum += 1 wb.Close (True) 'closes and saves 

Saving a book should convert formulas back to values ​​when they are closed.

Answer 6

To be more specific, it seems that Excel (2013, where I work) will import and evaluate the formula if the formula does not contain commas. This includes commas that separate function parameters such as =IF(A1=C1,D1-A1,D1+A1) . As a result, the division into three cells leads to the fact that the input tool for formulas (=) is considered as part of the text =IF(A1=C1 .

Another feature of Excel is that if you have a string consisting entirely of numbers, you must present it as a formula to preserve leading zeros. "00012345" is imported as 12345, ignoring quotation marks. To import as text, a .CSV file must present it as = "00012345".