我可以在没有 BOM 的情况下使用 UTF-8 导出 Excel 数据吗? [英] Can I export excel data with UTF-8 without BOM?

问题描述

我通过 Excel 宏 (VBScript) 导出 Microsoft Excel 数据.因为文件是 lua 脚本，所以我将其导出为 UTF-8.我可以在 Excel 中制作 UTF-8 的唯一方法是像这样使用 adodb.stream

I export Microsoft Excel data by Excel Macro(VBScript). Because file is lua script, I export it as UTF-8. The only way I can make UTF-8 in Excel is using adodb.stream like this

set fileLua = CreateObject("adodb.stream")
fileLua.Type = 2
fileLua.Mode = 3
fileLua.Charset = "UTF-8"
fileLua.Open
fileLua.WriteText("test")
fileLua.SaveToFile("Test.lua")
fileLua.flush
fileLua.Close

我想从 Test.lua 中消除 BOM，但我不知道如何.(因为Test.lua有一些unicode文本，所以我必须使用UTF-8格式.)

I want to make eliminate BOM from Test.lua but I don't know how. (Because Test.lua has some unicode text, I have to use UTF-8 format.)

你知道如何在excel文件中制作没有BOM的UTF-8文件吗?提前致谢.

Do you know how to make UTF-8 file without BOM in excel file? Thanks in advance.

推荐答案

我也有同样的问题:必须将数据从 Excel(Office 2003、VBA6.5)导出到 UTF-8 编码文件.从你的问题中找到答案！在我的示例下方，我还使用 boost 的(谢谢！)答案中的技巧 #2 去除了 BOM.我没有让 #1 工作，也从未尝试过 #3.

I have also the same issue: have to export data from Excel (Office 2003, VBA6.5) to UTF-8 encoded file. Found the answer from your question ! Below my example where I also strip the BOM using trick #2 from boost's (thanks!) answer. I didn't get #1 working and never tried #3.

Sub WriteUTF8WithoutBOM()
    Dim UTFStream As Object
    Set UTFStream = CreateObject("adodb.stream")
    UTFStream.Type = adTypeText
    UTFStream.Mode = adModeReadWrite
    UTFStream.Charset = "UTF-8"
    UTFStream.LineSeparator = adLF
    UTFStream.Open
    UTFStream.WriteText "This is an unicode/UTF-8 test.", adWriteLine
    UTFStream.WriteText "First set of special characters: öäåñüûú€", adWriteLine
    UTFStream.WriteText "Second set of special characters: qwertzuiopõúasdfghjkléáûyxcvbnm|Ä€Í÷×äðÐ[]í³£;?¤>#&@{}<;>*~¡^¢°²`ÿ´½¨¸0", adWriteLine

    UTFStream.Position = 3 'skip BOM

    Dim BinaryStream As Object
    Set BinaryStream = CreateObject("adodb.stream")
    BinaryStream.Type = adTypeBinary
    BinaryStream.Mode = adModeReadWrite
    BinaryStream.Open

    'Strips BOM (first 3 bytes)
    UTFStream.CopyTo BinaryStream

    'UTFStream.SaveToFile "d:adodb-stream1.txt", adSaveCreateOverWrite
    UTFStream.Flush
    UTFStream.Close

    BinaryStream.SaveToFile "d:adodb-stream2.txt", adSaveCreateOverWrite
    BinaryStream.Flush
    BinaryStream.Close
End Sub

我使用的 ADO 流对象参考.

这篇关于我可以在没有 BOM 的情况下使用 UTF-8 导出 Excel 数据吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！