将生产日报的EXCEL数据读出并利用EXCEL读取数据并生成SQL文件,但遇到编码问题,数据库使用的是UTF8编码,而EXCEL生成的文件是根据系统生成的ASICII格式。
'函数名称:ReadTextFile '作用:利用Adodb.Stream对象来读取UTF-8编码的文件 '示例:Call ReadFromTextFile("File/FileName.htm","UTF-8") Function ReadFromTextFile(FileUrl,CharSet) dim str set stm=server.CreateObject("adodb.stream") stm.Type=2 '2-文本模式读取,1-二进制模式 stm.mode=3 '3-读写,1-读,2-写 stm.charset=CharSet ‘unicode|utf-8;Ascii; gb2312; big5; gbk stm.open stm.loadfromfile server.MapPath(FileUrl) str=stm.readtext stm.Close set stm=nothing ReadFromTextFile=str End Function '函数名称:WriteToTextFile '作用:利用Adodb.Stream对象来写入UTF-8编码的文件 '示例:Call WriteToTextFile("File/FileName.htm",Content,"UTF-8") Sub WriteToTextFile(FileUrl,byval Str,CharSet) set stm=server.CreateObject("adodb.stream") stm.Type=2 '2-文本模式读取,1-二进制模式 stm.mode=3 '3-读写,1-读,2-写 stm.charset=CharSet 'unicode|utf-8;Ascii; gb2312; big5; gbk; stm.open stm.WriteText str stm.SaveToFile server.MapPath(FileUrl),2 '2可省略,adSaveCreateNotExist =1 , adSaveCreateOverWrite =2 stm.flush stm.Close set stm=nothing End Sub
文件转换格式的代码
Private Sub ConvFile(InputFile As String, OutputFile As String) Dim ReadStream As Object Set ReadStream = CreateObject("ADODB.Stream") Dim FileContent As String With ReadStream .Type = 2 'adTypeText .Charset = "UNICODE" .Open .LoadFromFile InputFile FileContent = .ReadText .Close End With Set ReadStream = Nothing Dim WriteStream As Object Set WriteStream = CreateObject("ADODB.Stream") With WriteStream .Type = 2 'adTypeText .Charset = "UTF-8" .Open .WriteText FileContent .SaveToFile OutputFile, 2 'adSaveCreateOverWrite .Flush .Close End With Set WriteStream = Nothing End Sub