java导出ansi csv,如何将excel导出到具有“|”的csv文件分隔符和utf-8代码

I had to export excel file to csv file through vba script.

csv file must be utf-8 code and delimted is "|"

I use SetLocaleInfo to set list separate as "|", but it only effect "xlcsv" not effect "xlunicodetext".

Private Function SetLocalSetting(LC_CONST As Long, strSetting As String) As Boolean

SetLocaleInfo GetUserDefaultLCID(), LC_CONST, strSetting

End Function

SetLocalSetting LOCALE_SLIST, "|"

When to use "xlUnicodeText", code is UTF-8 ,but the list seperator still is "Tab"

.SaveAs filename:="C:\temp\1.csv", FileFormat:=xlUnicodeText, Local:=True

When to use "xlCSV", the list seperator still is "|" what I need, but codepage is ANSI.

.SaveAs filename:="C:\temp\1.csv", FileFormat:=xlcsv, Local:=True

How to export a csv file with "|" seperator and UTF-8 ?

解决方案

Sub saveUnicodeCSV()

Set oAdoS = CreateObject("ADODB.Stream")

oAdoS.Charset = "UTF-8"

oAdoS.Mode = 3

oAdoS.Type = 2

oAdoS.Open

lRow = 1

lCol = 1

Do Until Sheets(1).Cells(lRow, lCol).Value = ""

oAdoS.WriteText (Sheets(1).Cells(lRow, lCol).Text)

lCol = lCol + 1

Do Until Sheets(1).Cells(lRow, lCol).Value = ""

oAdoS.WriteText ("|" & Sheets(1).Cells(lRow, lCol).Text)

lCol = lCol + 1

Loop

oAdoS.WriteText (vbCrLf)

lCol = 1

lRow = lRow + 1

Loop

oAdoS.SaveToFile "test.csv", 2

oAdoS.Close

Set oAdoS = Nothing

End Sub

Greetings

Axel