在没有BOM的情况下可以用UTF-8导出excel数据吗？
在没有BOM的情况下可以用UTF-8导出excel数据吗？
提问于 2010-11-10 18:36:34
我通过Excel Macro(VBScript)导出Microsoft Excel数据。因为文件是lua脚本，所以我将其导出为UTF-8。我在Excel中制作UTF-8的唯一方法是使用adodb.stream，如下所示
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中消除物料清单，但我不知道怎么做。(因为Test.lua有一些unicode文本，所以我必须使用UTF-8格式。)
你知道如何在excel文件中制作没有BOM的UTF-8文件吗？提前谢谢。
回答 3
Stack Overflow用户
回答已采纳
发布于 2010-12-16 21:33:43
我也有同样的问题:必须将数据从Excel (Office 2003，VBA6.5)导出为UTF-8编码文件。从你的问题中找到了答案！在下面的示例中，我还使用了boost中的技巧#2剥离了BOM (谢谢！)回答。我没有让#1工作，也从未尝试过#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 SubStack Overflow用户
发布于 2011-07-21 18:45:20
如果其他任何人正在为adTypeText常量而苦苦挣扎，您需要在工具->引用下包含“微软ActiveX数据对象2.5对象库”。
Stack Overflow用户
发布于 2015-05-26 16:32:36
Uf您更喜欢原生T-SQL而不是外部代码
DECLARE @FILE_NAME VARCHAR(255) = 'd:\utils\test.xml' --drive:\path\filename\
DECLARE @FILE_DATA VARCHAR(MAX) = '<?xml version="1.0" encoding="UTF-8"?>test</xml>' --binary as varchar(max)
DECLARE @FILE_NAME_TO VARCHAR(255) --Temp name for text stream
DECLARE @FSO_ID_TXTSTRM INT --Text Stream
DECLARE @FSO_ID_BINSTRM INT --Binary Stream
DECLARE @RC INT
EXEC @RC = sp_OACreate 'ADODB.Stream', @FSO_ID_TXTSTRM OUTPUT
EXEC @RC = sp_OASetProperty @FSO_ID_TXTSTRM, 'Type', 2 --1 = binary, 2 = text
EXEC @RC = sp_OASetProperty @FSO_ID_TXTSTRM, 'Mode', 3 --0 = not set, 1 read, 2 write, 3 read/write
EXEC @RC = sp_OASetProperty @FSO_ID_TXTSTRM, 'Charset', 'UTF-8' --'ISO-8859-1'
EXEC @RC = sp_OASetProperty @FSO_ID_TXTSTRM, 'LineSeparator', 'adLF'
EXEC @RC = sp_OAMethod @FSO_ID_TXTSTRM, 'Open'
EXEC @RC = sp_OAMethod @FSO_ID_TXTSTRM, 'WriteText', NULL, @FILE_DATA --text method
--Create binary stream
EXEC @RC = sp_OACreate 'ADODB.Stream', @FSO_ID_BINSTRM OUTPUT
EXEC @RC = sp_OASetProperty @FSO_ID_BINSTRM, 'Type', 1 --1 = binary, 2 = text
EXEC @RC = sp_OAMethod @FSO_ID_BINSTRM, 'Open'
EXEC @RC = sp_OASetProperty @FSO_ID_BINSTRM, 'Mode', 3 --0 = not set, 1 read, 2 write, 3 read/write
--Move 3 positions forward in text stream (BOM is first 3 positions)
EXEC @RC = sp_OASetProperty @FSO_ID_TXTSTRM, 'Position', 3
--Copy text stream to binary stream
EXEC @RC = sp_OAMethod @FSO_ID_TXTSTRM, 'CopyTo', NULL, @FSO_ID_BINSTRM
--Commit data and close text stream
EXEC @RC = sp_OAMethod @FSO_ID_TXTSTRM, 'Flush'
EXEC @RC = sp_OAMethod @FSO_ID_TXTSTRM, 'Close'
EXEC @RC = sp_OADestroy @FSO_ID_TXTSTRM
--Save binary stream to file and close
EXEC @RC = sp_OAMethod @FSO_ID_BINSTRM, 'SaveToFile', NULL, @FILE_NAME, 2 --1 = notexist 2 = overwrite
EXEC @RC = sp_OAMethod @FSO_ID_BINSTRM, 'Close'
EXEC @RC = sp_OADestroy @FSO_ID_BINSTRM页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/4143524
复制相关文章
相似问题
腾讯云开发者
