有没有办法使用 vb.net 以编程方式将公式插入到 csv 文件中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17048316/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to programmatically insert formulas into a csv file using vb.net?
提问by TWhite
I need to add formulas to a CSV file that already exists. Is this possible to do this using VB.Net?
我需要将公式添加到已经存在的 CSV 文件中。使用 VB.Net 可以做到这一点吗？
The idea is I already have a CSV file and I need one column to be populated in each cell with a custom formula. This has to be done programmatically because these files are being created dynamically.
这个想法是我已经有一个 CSV 文件，我需要在每个单元格中使用自定义公式填充一列。这必须以编程方式完成，因为这些文件是动态创建的。
Does anyone know how to do this? Thanks.
有谁知道如何做到这一点？谢谢。
采纳答案by Tim B
While I stand by that my original answer is technically correct, I have been getting a lot of downvotes on this. Apparently popular spreadsheet software such as Microsoft Excel, Open Office, Libre Office Calc will calculate formulas that are entered into a CSV file. However, I would still not recommend relying in this capability.
虽然我坚持我的原始答案在技术上是正确的，但我对此却遭到了很多反对。显然流行的电子表格软件，例如 Microsoft Excel、Open Office、Libre Office Calc，将计算输入到 CSV 文件中的公式。但是，我仍然不建议依赖此功能。
Original answer:
The CSV format does not support formulas. It is a plain text only format.
原答案：
CSV 格式不支持公式。它是纯文本格式。
回答by Greg Little
1,2,3,=SUM(A1:C1)
1,2,3,=SUM(A1:C1)
Surprised me when I tried it, but Excel will keep the formula.
当我尝试时让我感到惊讶，但 Excel 会保留公式。
You can even export formulas into a CSV by first displaying them on screen. (Ctrl-`)
您甚至可以通过首先在屏幕上显示公式将公式导出为 CSV。(Ctrl-`)
回答by Ed Michalski
You can import formula's into excel via a text file using comma separated values. Just remember to make sure it is named with the suffix .txt.
您可以使用逗号分隔值通过文本文件将公式导入到 Excel 中。请记住确保它以 .txt 后缀命名。
Then do the import.
然后进行导入。
My example import ( Data table, From Text)
我的示例导入（数据表，来自文本）
Column1,Column2,ResultColumn
列 1、列 2、结果列
1,2,=A2+B2
1,2,=A2+B2
It imported and computed just fine
它导入并计算得很好
回答by Ann L.
Are you generating the CSV file? If so, consider writing an actual Excel file. (I'm assuming you're importing into Excel, since you used the term "cell", which has no meaning in CSV.)
您正在生成 CSV 文件吗？如果是这样，请考虑编写一个实际的 Excel 文件。（我假设您正在导入 Excel，因为您使用了术语“单元格”，这在 CSV 中没有意义。）
Here's a link on how to do it: Create Excel (.XLS and .XLSX) file from C#
这是有关如何操作的链接： Create Excel (.XLS and .XLSX) file from C#
If you aren't generating the CSV, and if all you want is to add a new, calculated value,(rather than a formula that will change dynamically as cells change values) you can do this easily enough by reading in the CSV file, parsing each line enough to get the values you need for your formula, calculating the result, and appending it (after a comma) to each line before writing the line out to a new file.
如果您不生成 CSV，并且您只想添加一个新的计算值（而不是会随着单元格更改值而动态更改的公式），您可以通过读取 CSV 文件轻松完成此操作，足够解析每一行以获得公式所需的值，计算结果，并将其（在逗号之后）附加到每一行，然后再将行写入新文件。
回答by APrough
You could open the csv in Excel and then add the formulas to Excel and save back out to csv. You would do this by using the Microsoft Excel 11.0 Object Library. Then
您可以在 Excel 中打开 csv，然后将公式添加到 Excel 并保存回 csv。您可以使用 Microsoft Excel 11.0 对象库来完成此操作。然后
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 i.e.
exSheet.Range("A" & rowNum).Value = "=SUM($A:$D)"
rowNum += 1
wb.Close (True) 'closes and saves
Saving the workbook should convert the formulas back to the values when it is closed.
保存工作簿应在关闭时将公式转换回值。
回答by Richard Luketich
In Excel, to import formulas with commas the formula must be encapsulated by double quotes to prevent the formula being spread across cells. For example:
在 Excel 中，要导入带逗号的公式，公式必须用双引号括起来，以防止公式跨单元格传播。例如：
2,4,6,13,=sum(A1:C1),"=if(A1=C1,D1-A1,D1+A1)"
2,4,6,13,=sum(A1:C1),"=if(A1=C1,D1-A1,D1+A1)"
Another quirk of Excel is that if you have a string consisting entirely of numbers, you must present it as a formula to retain leading zeros. "00012345" imports as 12345, ignoring the quotes. To import as text, the .CSV file must present this as ="00012345".
Excel 的另一个怪癖是，如果您有一个完全由数字组成的字符串，则必须将其显示为公式以保留前导零。"00012345" 导入为 12345，忽略引号。要作为文本导入，.CSV 文件必须将其显示为 ="00012345"。
