excel - Excel 生成的 CSV 文件中的数字

Question

不幸的是，Excel 在生成 CSV 文件时对程序员不是很友好，因为它会存储出现在屏幕上的数字。因此，CSV 中数字的外观取决于区域设置和用户的喜好。

结果，我可能最终得到的数字看起来像

2 123
3'322
1,233.44
2123,45

等等

现在我必须在我的程序中处理这样的 CSV 文件。

除了告诉用户如何构建他们的 CSV 文件之外——是否有任何智能或规范的解决方案来处理这个问题？切换到另一种数据格式也是一种选择——但是哪一种呢？它必须由 Excel 原生支持，并且应该易于处理(所以我想避免使用 xlsx)。

Answer 1

我和一个客户有同样的问题，我给他们写了一个导出到 csv 宏，它明确告诉 excel 每一列的格式，这里是一个代码示例

Public Sub Export_To_CSV()
'Give excel the sheet with data to be exported to CSV

Sheets("Sheet1").Activate

'Declare your string array to be filled - currently set to 5 columns

Dim csvread(1 To 5) As String
Dim i As Integer
i = 1

Dim ObjFso
Dim StrFileName
Dim ObjFile

'Name the export csv - This will generate it in the folder you have the excel you are working with in and then names it CSVexport-Day-Month.csv

StrFileName = Application.ThisWorkbook.Path & "\CSVExport-" & Day(Date) & "-" & Month(Date) & ".csv"
Set ObjFso = CreateObject("Scripting.FileSystemObject")
'Creating a file for writing data
Set ObjFile = ObjFso.CreateTextFile(StrFileName)

'takes the headers from row 1 - set by i if different

csvread(1) = Range("A" & i).Value
csvread(2) = Range("B" & i).Value
csvread(3) = Range("C" & i).Value
csvread(4) = Range("D" & i).Value
csvread(5) = Range("E" & i).Value

'Writes the Headers

ObjFile.WriteLine (csvread(1) & "," & csvread(2) & "," & csvread(3) & "," & csvread(4) & "," & csvread(5))

i = i + 1

'Do until loop can be altered to meet your parameters

Do Until Range("A" & i).Value = 0 Or i = 1000

'format your data however you require

csvread(15) = Format(Range("A" & i).Value, "###0.00")
csvread(15) = Format(Range("B" & i).Value, "###0.00")
csvread(15) = Format(Range("C" & i).Value, "###0.00")
csvread(15) = Format(Range("D" & i).Value, "###0.00")
csvread(15) = Format(Range("E" & i).Value, "###0.00")

'write the line to the file

ObjFile.WriteLine (csvread(1) & "," & csvread(2) & "," & csvread(3) & "," & csvread(4) & "," & csvread(5))
i = i + 1
Loop

ObjFile.Close

p = MsgBox("Exported", vbOKOnly)


End Sub