5.1将Excel转化为XML导入数据库之一

 

将Excel转化为XML。以Excel2003为例说明如何将数据导入MS SQL数据库

说先俺的系统配置：

系统：Windows Server 2003 R2 X64

开发软件：Microsoft VS 2008 Team

Office版本：Excel 2003 Excel2007

数据库：MS SQL 2008

 

5.1.1建立Excel XML 映射：数据格式见表。

 

现打算将『用户名称』，『客户联系人』，『联系电话』，『客户经理』，『客户经理联系电话』，『业务类型』，『前端数量』，『业务类别』，『区域』导入MS SQL相应字段中

制定XML XSD文件，T1schema.xls文件如下：

<?xml version="1.0" standalone="yes"?>

<QQTintDataSet xmlns="http://tempuri.org/QQTintDataSet.xsd">

  <xs:schema id="QQTintDataSet" targetNamespace="http://tempuri.org/QQTintDataSet.xsd" xmlns:mstns="http://tempuri.org/QQTintDataSet.xsd" xmlns="http://tempuri.org/QQTintDataSet.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">

    <xs:element name="QQTintDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

      <xs:complexType>

        <xs:choice minOccurs="0" maxOccurs="unbounded">

          <xs:element name="yyqint">

            <xs:complexType>

              <xs:sequence>

                <xs:element name="id" msdata:ReadOnly="true" msdata:AutoIncrement="true" msdata:AutoIncrementSeed="-1" msdata:AutoIncrementStep="-1" type="xs:int" />

                <xs:element name="Customer" minOccurs="0">

                  <xs:simpleType>

                    <xs:restriction base="xs:string">

                      <xs:maxLength value="50" />

                    </xs:restriction>

                  </xs:simpleType>

                </xs:element>

                <xs:element name="CustomerContact" minOccurs="0">

                  <xs:simpleType>

                    <xs:restriction base="xs:string">

                      <xs:maxLength value="50" />

                    </xs:restriction>

                  </xs:simpleType>

                </xs:element>

                <xs:element name="CustomerTelephone" minOccurs="0">

                  <xs:simpleType>

                    <xs:restriction base="xs:string">

                      <xs:maxLength value="50" />

                    </xs:restriction>

                  </xs:simpleType>

                </xs:element>

                <xs:element name="AccountManager" minOccurs="0">

                  <xs:simpleType>

                    <xs:restriction base="xs:string">

                      <xs:maxLength value="50" />

                    </xs:restriction>

                  </xs:simpleType>

                </xs:element>

                <xs:element name="AMTelephone" minOccurs="0">

                  <xs:simpleType>

                    <xs:restriction base="xs:string">

                      <xs:maxLength value="50" />

                    </xs:restriction>

                  </xs:simpleType>

                </xs:element>

                <xs:element name="AccessType" minOccurs="0">

                  <xs:simpleType>

                    <xs:restriction base="xs:string">

                      <xs:maxLength value="50" />

                    </xs:restriction>

                  </xs:simpleType>

                </xs:element>

                <xs:element name="AccessAmount" type="xs:int" minOccurs="0" />

                <xs:element name="BusinessName" minOccurs="0">

                  <xs:simpleType>

                    <xs:restriction base="xs:string">

                      <xs:maxLength value="50" />

                    </xs:restriction>

                  </xs:simpleType>

                </xs:element>

                <xs:element name="Regional" minOccurs="0">

                  <xs:simpleType>

                    <xs:restriction base="xs:string">

                      <xs:maxLength value="50" />

                    </xs:restriction>

                  </xs:simpleType>

                </xs:element>

                <xs:element name="PlatformLoginAccountid" type="xs:int" minOccurs="0" />

                <xs:element name="AccessInforid" type="xs:int" minOccurs="0" />

                <xs:element name="Implementationid" type="xs:int" minOccurs="0" />

              </xs:sequence>

            </xs:complexType>

          </xs:element>

        </xs:choice>

      </xs:complexType>

      <xs:unique name="Constraint1" msdata:PrimaryKey="true">

        <xs:selector xpath=".//mstns:yyqint" />

        <xs:field xpath="mstns:id" />

      </xs:unique>

    </xs:element>

  </xs:schema>

    <yyqint>

    <id></id>

    <Customer></Customer>

    <CustomerContact></CustomerContact>

    <CustomerTelephone></CustomerTelephone>

    <AccountManager> </AccountManager>

    <AMTelephone></AMTelephone>

    <AccessType> </AccessType>

    <AccessAmount></AccessAmount>

    <BusinessName> </BusinessName>

    <Regional> </Regional>

    <PlatformLoginAccountid></PlatformLoginAccountid>

    <AccessInforid></AccessInforid>

    <Implementationid></Implementationid>

  </yyqint>

</QQTintDataSet>

其实这个文件可以通过DataSet. GetXmlSchema方法获取，因为DataSet读取SQL相应表结构（目标数据）一定要同Excel表导入数据的结构（源数据）一样。打开Excel源数据表格，依次点击『数据』『XML』『XML源』

 

再点击『XML映射』，添加映射文件T1schema.xls，按确定后如图

 

拖动『ns1:Customer』到单元格『用户名称』，如此类推，建立XML映射文件，如图：

 

 

 

依次点击『数据』『XML』导出XML映射文件Book22.xml，以备下一步使用（待续...）