XMLTextWriter BOM

最新推荐文章于 2022-12-28 09:36:44 发布

ticojj

最新推荐文章于 2022-12-28 09:36:44 发布

阅读量151

点赞数

分类专栏： vb

本文链接：https://blog.csdn.net/ticojj/article/details/84613858

版权

vb 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

我写的一个关于xml text reader的，比较乱。。。其实很简单。。。

特此讲一下微软自己定义的BOM=Byte Order Mark, 本来我是用 msxml generate xml file .. but it cannot contain BOM in it . and user requried BOM , should I change to XMLTextWriter

俗话说，天无绝人之路， right ? if you can try ,you will find to s

----------------------------------------

'09 Jul 2014 (Kenny Cheung): Add to generate tax file in XML format
    Private Sub GenerateTaxFileXml(ByRef iTaxYr As Short)
        On Error GoTo erhd
        Dim sFileName As String
        Dim oFileSys As Scripting.FileSystemObject

Dim oTextStream As Scripting.TextStream

        Dim oSQL As ClsMySQL
        'Dim rsRv As ADODB.Recordset
        Dim sBuffer As String
        Dim sBufferHeader As String

        Dim sEmpyrTaxFileNo As String
        Dim sEmpyrNm As String
        Dim sDesgn As String
        Dim lRecordCount As String
        Dim cTotAmt As String
        Dim lBtNo As String
        Dim sSubDte As String
        Dim sMsg As String

oSQL = New ClsMySQL

sFileName = GetExportDir() & VB6.Format(iTaxYr, "0000") & sTaxFile
oFileSys = New Scripting.FileSystemObject

        'delete old tax file
        If oFileSys.FileExists(sFileName) Then
            oFileSys.DeleteFile(sFileName)
        End If

        Using gConAPCA As New OleDbConnection(gStrAPCA)
            oSQL.ReSet_Renamed()
            oSQL.SqlType = ClsMySQL.StatmentType.TYPE_SELECT
            oSQL.AddTable("TBL_APCA_FST_PTY_INFO")
            oSQL.AddFields("TAX_FL_NO", "NM", "DESGN")

gReader = QueryByReader(gConAPCA, oSQL.SQL)

            If gReader.Read Then
                sEmpyrTaxFileNo = Null2Str(gReader("TAX_FL_NO"))
                sEmpyrNm = Null2Str(gReader("NM"))
                sDesgn = Null2Str(gReader("DESGN"))

End If
gReader.Close()

            oSQL.ReSet_Renamed()
            oSQL.AddTable("TBL_APCA_TAX_REPORT")
            oSQL.AddSimpleFuncField("COUNT", , , "REC_COUNT")
            oSQL.AddSimpleFuncField("SUM", "TOT_INCOME", , "TOT")
            oSQL.AddFields("BT_NO", "SUB_DTE")
            oSQL.AddGroupBy("BT_NO")
            oSQL.AddGroupBy("SUB_DTE")

gReader = QueryByReader(gConAPCA, oSQL.SQL)

            If gReader.Read Then
                lRecordCount = Null2Str(gReader("REC_COUNT"))
                cTotAmt = Null2Str(gReader("TOT"))
                lBtNo = CStr(Null2Str(gReader("BT_NO")))
                sSubDte = VB6.Format(gReader("SUB_DTE").ToString, "YYYYMMDD")
            End If
            gReader.Close()

Dim writer As New Xml.XmlTextWriter(sFileName, System.Text.Encoding.GetEncoding("utf-8"))
'使用自动缩进便于阅读

writer.Formatting = Xml.Formatting.Indented

writer.WriteRaw("<?xml version=""1.0"" encoding=""utf-8"" ?>")

'书写根元素

writer.WriteStartElement("IR56B")

writer.WriteAttributeString("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance")

writer.WriteAttributeString("xsi:noNamespaceSchemaLocation", "ir56b.xsd")

'添加次级元素

writer.WriteElementString("Section", sEmpyrTaxFileNo)

writer.WriteElementString("ERN", lBtNo)

writer.WriteElementString("YrErReturn", iTaxYr)

writer.WriteElementString("SubDate", sSubDte)

writer.WriteElementString("ErName", sEmpyrNm)

            writer.WriteElementString("Designation", sDesgn)

            writer.WriteElementString("NoRecordBatch", lRecordCount)

writer.WriteElementString("TotIncomeBatch", cTotAmt)

            'Build employee tree
            oSQL.ReSet_Renamed()
            oSQL.SqlType = ClsMySQL.StatmentType.TYPE_SELECT
            oSQL.AddTable("TBL_APCA_TAX_REPORT")

gReader = QueryByReader(gConAPCA, oSQL.SQL)

Do While gReader.Read

                Dim SHEET_NO As String
                Dim HK_ID As String
                Dim STUS As String
                Dim S_NM As String
                Dim NM As String
                Dim C_NM As String
                Dim GENDER As String
                Dim M_STUS As String
                Dim PASPT_NO As String
                Dim SPO_NM As String
                Dim SPO_HKID As String
                Dim SPO_PASPT_NO As String
                Dim R_ADDR As String
                Dim AR_CDE As String
                Dim CORR_ADDR As String
                Dim CAPCTY As String
                Dim PRIN_EMPYR As String
                Dim JOIN_DTE As String
                Dim CESS_DTE As String
                Dim PRD_SLRY As String
                Dim SLRY As String
                Dim PRD_LEV_PAY As String
                Dim LEV_PAY As String
                Dim PRD_DIR_FEE As String
                Dim DIR_FEE As String
                Dim PRD_COMM As String
                Dim COMM As String
                Dim PRD_BNS As String
                Dim BNS As String
                Dim PRD_BACK_PAY As String
                Dim BACK_PAY As String

                Dim PRD_RETR_SCHM_PMNT As String
                Dim RETR_SCHM_PMNT As String
                Dim PRD_SLRY_TAX_EMPYR As String
                Dim SLRY_TAX_EMPYR As String
                Dim PRD_EDUC_BNF As String
                Dim EDUC_BNF As String
                Dim PRD_SHR_OPT_GAIN As String
                Dim SHR_OPT_GAIN As String
                Dim RWD_NATURE1 As String
                Dim RWD_PRD1 As String
                Dim RWD_AMT1 As String
                Dim RWD_NATURE2 As String
                Dim RWD_PRD2 As String
                Dim RWD_AMT2 As String

                Dim RWD_NATURE3 As String
                Dim RWD_PRD3 As String
                Dim RWD_AMT3 As String

                Dim PRD_PNSN As String
                Dim PNSN As String
                Dim TOT_INCOME As String
                Dim R_IND As String

                Dim R_ADDR_1 As String
                Dim R_NATURE_1 As String
                Dim PRD_R_1 As String
                Dim RENT_EMPYR_1 As String
                Dim RENT_EMPYE_1 As String
                Dim RENT_RFND_EMPYE_1 As String
                Dim RENT_EMPYR_EMPYE_1 As String

                Dim R_ADDR_2 As String
                Dim R_NATURE_2 As String
                Dim PRD_R_2 As String
                Dim RENT_EMPYR_2 As String
                Dim RENT_EMPYE_2 As String
                Dim RENT_RFND_EMPYE_2 As String
                Dim RENT_EMPYR_EMPYE_2 As String
                Dim OSEA_IncInd As String
                Dim OSEA_AMT As String
                Dim OSEA_NM As String
                Dim OSEA_ADDR As String
                Dim RMK As String

                SHEET_NO = Null2Str(gReader("SHEET_NO"))
                HK_ID = Null2Str(gReader("HK_ID"))
                STUS = Null2Str(gReader("STUS"))
                S_NM = Null2Str(gReader("S_NM"))
                NM = Null2Str(gReader("NM"))
                C_NM = Null2Str(gReader("C_NM"))
                GENDER = Null2Str(gReader("GENDER"))
                M_STUS = Null2Str(gReader("M_STUS"))
                PASPT_NO = Null2Str(gReader("PASPT_NO"))
                SPO_NM = Null2Str(gReader("SPO_NM"))
                SPO_HKID = Null2Str(gReader("SPO_HKID"))
                SPO_PASPT_NO = Null2Str(gReader("SPO_PASPT_NO"))
                R_ADDR = Null2Str(gReader("R_ADDR"))
                AR_CDE = Null2Str(gReader("AR_CDE"))
                CORR_ADDR = Null2Str(gReader("CORR_ADDR"))
                CAPCTY = Null2Str(gReader("CAPCTY"))
                PRIN_EMPYR = Null2Str(gReader("PRIN_EMPYR"))
                JOIN_DTE = VB6.Format(gReader("JOIN_DTE").ToString, "YYYYMMDD")
                CESS_DTE = VB6.Format(gReader("CESS_DTE").ToString, "YYYYMMDD")
                PRD_SLRY = Null2Str(gReader("PRD_SLRY"))
                SLRY = Null2Str(gReader("SLRY"))
                PRD_LEV_PAY = Null2Str(gReader("PRD_LEV_PAY"))
                LEV_PAY = Null2Str(gReader("LEV_PAY"))
                PRD_DIR_FEE = Null2Str(gReader("PRD_DIR_FEE"))
                DIR_FEE = Null2Str(gReader("DIR_FEE"))
                PRD_COMM = Null2Str(gReader("PRD_COMM"))
                COMM = Null2Str(gReader("COMM"))
                PRD_BNS = Null2Str(gReader("PRD_BNS"))
                BNS = Null2Str(gReader("BNS"))
                PRD_BACK_PAY = Null2Str(gReader("PRD_BACK_PAY"))
                BACK_PAY = Null2Str(gReader("BACK_PAY"))
                PRD_RETR_SCHM_PMNT = Null2Str(gReader("PRD_RETR_SCHM_PMNT"))
                RETR_SCHM_PMNT = Null2Str(gReader("RETR_SCHM_PMNT"))
                PRD_SLRY_TAX_EMPYR = Null2Str(gReader("PRD_SLRY_TAX_EMPYR"))
                SLRY_TAX_EMPYR = Null2Str(gReader("SLRY_TAX_EMPYR"))
                PRD_EDUC_BNF = Null2Str(gReader("PRD_EDUC_BNF"))
                EDUC_BNF = Null2Str(gReader("EDUC_BNF"))
                PRD_SHR_OPT_GAIN = Null2Str(gReader("PRD_SHR_OPT_GAIN"))
                SHR_OPT_GAIN = Null2Str(gReader("SHR_OPT_GAIN"))

                RWD_NATURE1 = Null2Str(gReader("RWD_NATURE1"))
                RWD_PRD1 = Null2Str(gReader("RWD_PRD1"))
                RWD_AMT1 = Null2Str(gReader("RWD_AMT1"))

                RWD_NATURE2 = Null2Str(gReader("RWD_NATURE2"))
                RWD_PRD2 = Null2Str(gReader("RWD_PRD2"))
                RWD_AMT2 = Null2Str(gReader("RWD_AMT2"))

                RWD_NATURE3 = Null2Str(gReader("RWD_NATURE3"))
                RWD_PRD3 = Null2Str(gReader("RWD_PRD3"))
                RWD_AMT3 = Null2Str(gReader("RWD_AMT3"))

                PRD_PNSN = Null2Str(gReader("PRD_PNSN"))
                PNSN = Null2Str(gReader("PNSN"))
                TOT_INCOME = Null2Str(gReader("TOT_INCOME"))
                R_IND = Null2Str(gReader("R_IND"))

                R_ADDR_1 = Null2Str(gReader("R_ADDR_1"))
                R_NATURE_1 = Null2Str(gReader("R_NATURE_1"))
                PRD_R_1 = Null2Str(gReader("PRD_R_1"))
                RENT_EMPYR_1 = Null2Str(gReader("RENT_EMPYR_1"))
                RENT_EMPYE_1 = Null2Str(gReader("RENT_EMPYE_1"))
                RENT_RFND_EMPYE_1 = Null2Str(gReader("RENT_RFND_EMPYE_1"))
                RENT_EMPYR_EMPYE_1 = Null2Str(gReader("RENT_EMPYR_EMPYE_1"))

                R_ADDR_2 = Null2Str(gReader("R_ADDR_2"))
                R_NATURE_2 = Null2Str(gReader("R_NATURE_2"))
                PRD_R_2 = Null2Str(gReader("PRD_R_2"))
                RENT_EMPYR_2 = Null2Str(gReader("RENT_EMPYR_2"))
                RENT_EMPYE_2 = Null2Str(gReader("RENT_EMPYE_2"))
                RENT_RFND_EMPYE_2 = Null2Str(gReader("RENT_RFND_EMPYE_2"))
                RENT_EMPYR_EMPYE_2 = Null2Str(gReader("RENT_EMPYR_EMPYE_2"))

                If gReader("OSEA_AMT").Equals(DBNull.Value) And gReader("OSEA_ADDR").Equals(DBNull.Value) And gReader("OSEA_NM").Equals(DBNull.Value) Then
                    OSEA_IncInd = "0"
                Else
                    OSEA_IncInd = "1"
                End If

                OSEA_AMT = Null2Str(gReader("OSEA_AMT"))
                OSEA_NM = Null2Str(gReader("OSEA_NM"))
                OSEA_ADDR = Null2Str(gReader("OSEA_ADDR"))
                RMK = Null2Str(gReader("RMK"))

'添加次级Employee元素

writer.WriteStartElement("Employee")

'添加次级元素
writer.WriteElementString("SheetNo", SHEET_NO)

writer.WriteElementString("HKID", HK_ID)

writer.WriteElementString("TypeOfForm", STUS)

writer.WriteElementString("Surname", S_NM)

writer.WriteElementString("GivenName", NM)

writer.WriteElementString("NameInChinese", C_NM)

writer.WriteElementString("Sex", GENDER)

writer.WriteElementString("MaritalStatus", M_STUS)

writer.WriteElementString("PpNum", PASPT_NO)

writer.WriteElementString("SpouseName", SPO_NM)

writer.WriteElementString("SpouseHKID", SPO_HKID)

writer.WriteElementString("SpousePpNum", SPO_PASPT_NO)

writer.WriteElementString("ResAddr", R_ADDR)

writer.WriteElementString("AreaCodeResAddr", AR_CDE)

writer.WriteElementString("PosAddr", CORR_ADDR)

writer.WriteElementString("Capacity", CAPCTY)

writer.WriteElementString("PtPrinEmp", PRIN_EMPYR)

writer.WriteElementString("StartDateOfEmp", JOIN_DTE)

writer.WriteElementString("EndDateOfEmp", CESS_DTE)

writer.WriteElementString("PerOfSalary", PRD_SLRY)

writer.WriteElementString("AmtOfSalary", SLRY)

writer.WriteElementString("PerOfLeavePay", PRD_LEV_PAY)

writer.WriteElementString("AmtOfLeavePay", LEV_PAY)

writer.WriteElementString("PerOfDirectorFee", PRD_DIR_FEE)

writer.WriteElementString("AmtOfDirectorFee", DIR_FEE)

writer.WriteElementString("PerOfCommFee", PRD_COMM)

writer.WriteElementString("AmtOfCommFee", COMM)

writer.WriteElementString("PerOfBonus", PRD_BNS)

writer.WriteElementString("AmtOfBonus", BNS)

writer.WriteElementString("PerOfBpEtc", PRD_BACK_PAY)

writer.WriteElementString("AmtOfBpEtc", BACK_PAY)

writer.WriteElementString("PerOfPayRetire", PRD_RETR_SCHM_PMNT)

writer.WriteElementString("AmtOfPayRetire", RETR_SCHM_PMNT)

writer.WriteElementString("PerOfSalTaxPaid", PRD_SLRY_TAX_EMPYR)

writer.WriteElementString("AmtOfSalTaxPaid", SLRY_TAX_EMPYR)

writer.WriteElementString("PerOfEduBen", PRD_EDUC_BNF)

writer.WriteElementString("AmtOfEduBen", EDUC_BNF)

writer.WriteElementString("PerOfGainShareOption", PRD_SHR_OPT_GAIN)

writer.WriteElementString("AmtOfGainShareOption", SHR_OPT_GAIN)

writer.WriteElementString("NatureOtherRAP1", RWD_NATURE1)

writer.WriteElementString("PerOfOtherRAP1", RWD_PRD1)

writer.WriteElementString("AmtOfOtherRAP1", RWD_AMT1)

writer.WriteElementString("NatureOtherRAP2", RWD_NATURE2)

writer.WriteElementString("PerOfOtherRAP2", RWD_PRD2)

writer.WriteElementString("AmtOfOtherRAP2", RWD_AMT2)

writer.WriteElementString("NatureOtherRAP3", RWD_NATURE3)

writer.WriteElementString("PerOfOtherRAP3", RWD_PRD3)

writer.WriteElementString("AmtOfOtherRAP3", RWD_AMT3)

writer.WriteElementString("PerOfPension", PRD_PNSN)

writer.WriteElementString("AmtOfPension", PNSN)

writer.WriteElementString("TotalIncome", TOT_INCOME)

writer.WriteElementString("PlaceOfResInd", R_IND)

writer.WriteElementString("AddrOfPlace1", R_ADDR_1)

writer.WriteElementString("NatureOfPlace1", R_NATURE_1)

writer.WriteElementString("PerOfPlace1", PRD_R_1)

writer.WriteElementString("RentPaidEr1", RENT_EMPYR_1)

writer.WriteElementString("RentPaidEe1", RENT_EMPYE_1)

writer.WriteElementString("RentRefund1", RENT_RFND_EMPYE_1)

writer.WriteElementString("RentPaidErByEe1", RENT_EMPYR_EMPYE_1)

writer.WriteElementString("AddrOfPlace2", R_ADDR_2)

writer.WriteElementString("NatureOfPlace2", R_NATURE_2)

writer.WriteElementString("PerOfPlace2", PRD_R_2)

writer.WriteElementString("RentPaidEr2", RENT_EMPYR_2)

writer.WriteElementString("RentPaidEe2", RENT_EMPYE_2)

writer.WriteElementString("RentRefund2", RENT_RFND_EMPYE_2)

writer.WriteElementString("RentPaidErByEe2", RENT_EMPYR_EMPYE_2)

writer.WriteElementString("OverseaIncInd", OSEA_IncInd)

writer.WriteElementString("AmtPaidOverseaCo", OSEA_AMT)

writer.WriteElementString("NameOfOverseaCo", OSEA_NM)

writer.WriteElementString("AddrOfOverseaCo", OSEA_ADDR)

writer.WriteElementString("Remarks", RMK)

writer.WriteEndElement()

Loop

'关闭根元素

writer.WriteEndElement()

'将XML写入文件并关闭writer

writer.Close()

gReader.Close()

            oSQL.ReSet_Renamed()
            oSQL.SqlType = ClsMySQL.StatmentType.TYPE_INSERT
            oSQL.AddTable("TBL_APCA_AUD_LOG")

oSQL.AddField("USR")
oSQL.AddValue(sUserID)

oSQL.AddField("ACT")
oSQL.AddValue("S")

oSQL.AddField("LOG_TM")
oSQL.AddValue(VB6.Format(Today, "dd MMM YYYY") & " " & TimeOfDay)

oSQL.AddField("DESC")

sMsg = FormatMsg(My.Resources.str19011, CStr(iTaxYr), oFileSys.GetAbsolutePathName(sFileName))
oSQL.AddValue(sMsg)

Call ExeNonQuery(gConAPCA, oSQL.SQL)

'Open windows explorer to navigate the file
ShellExecute(Me.Handle.ToInt32, "explore", oFileSys.GetParentFolderName(sFileName) & vbNullChar, "", "", modShell.enuShowWindow.SW_SHOW)

oFileSys = Nothing
oSQL = Nothing

End Using

        Exit Sub
erhd:
        oTextStream = Nothing
        oFileSys = Nothing
        oSQL = Nothing
        MyErrorRaise(Err.Description)
    End Sub