blocks|key|1095674|text|我也有同样的问题:必须将数据从Excel+(Office+2003，VBA6.5)导出为UTF-8编码文件。从你的问题中找到了答案！在下面的示例中，我还使用了boost中的技巧#2剥离了BOM+(谢谢！)回答。我没有让#1工作，也从未尝试过#3。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1095675|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\%7CÄ€Í÷×äðÐ[]í³£;?¤>#&@{}<;>*~¡%5E¢°²`ÿ´½¨¸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|code-block|syntax|javascript|1095676|我用的ADO+Stream+Object+reference。|offset|length|1095677|entityMap|0|LINK|mutability|MUTABLE|url|http://www.w3schools.com/ADO/ado_ref_stream.asp^0|0|0|3|R|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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.

<pre><code>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\|Ä€Í÷×äðÐ[]í³£;?¤&gt;#&amp;@{}&lt;;&gt;*~¡^¢°²`ÿ´½¨¸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
</code></pre>

The <a href="http://www.w3schools.com/ADO/ado_ref_stream.asp" rel="noreferrer">ADO Stream Object reference</a> I used.

blocks|key|880889|text|如果其他任何人正在为adTypeText常量而苦苦挣扎，您需要在工具->引用下包含“微软ActiveX数据对象2.5对象库”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|880890|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If anyone else is struggling with the adTypeText constant, you need to include "Microsoft ActiveX Data Objects 2.5 Object Library" under Tools->References.

blocks|key|880897|text|Uf您更喜欢原生T-SQL而不是外部代码|type|unstyled|depth|inlineStyleRanges|entityRanges|data|880898|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|code-block|syntax|javascript|880899|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Uf you prefer native T-SQL instead of external code

<pre><code>DECLARE @FILE_NAME VARCHAR(255) = 'd:\utils\test.xml' --drive:\path\filename\
DECLARE @FILE_DATA VARCHAR(MAX) = '&lt;?xml version="1.0" encoding="UTF-8"?&gt;test&lt;/xml&gt;' --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
</code></pre>

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

<pre><code>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
</code></pre>

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.)

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

Can I export excel data with UTF-8 without BOM?

我通过Excel Macro(VBScript)导出Microsoft Excel数据。因为文件是lua脚本，所以我将其导出为UTF-8。我在Excel中制作UTF-8的唯一方法是使用adodb.stream，如下所示set fileLua = CreateObject("adodb.stream")fileLua.T...

问在没有BOM的情况下可以用UTF-8导出excel数据吗？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在没有BOM的情况下可以用UTF-8导出excel数据吗？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在没有BOM的情况下可以用UTF-8导出excel数据吗？
EN