Microsoft Excel 2002 ?? Microsoft Excel 2003 ?? ??? ????? C# ?? ????? ?? XML ???? ??????????? ???? ?? ??? ???? ????

???? ?????? ???? ??????
???? ID: 307029 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
?? ???? ?? Microsoft Visual Basic .NET ??????? ?? ???, ?????307021.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

Excel 2002 introduces ??????????? ??????? ???? (XML) ?????? ??? ?????? ?? ????? ?? ??? ??????????? ??? XML ???? ??? ?????? ??? ?? ?? Excel 2002 ?? Excel 2003 ??? ???? ?????????? ???????? ?? ??? ?? ????? ???? ???? ?? ???? ???

Microsoft Visual C# 2005 ?? Visual Microsoft C# .NET, ?? ??? ?? Excel ?? ???? ??????? ?? ??? ????? ???? ?? ??? ???? ????????????? ??? ??? ???? ??????????? ???? ?? ??? XML ??????????? ?? ??? ?? ???? ???? ?? ??? ???????? ?? ???? ???? ?? ???? ????????? ???? ?? ?? ?? ????? ?? ???? ???? ?? ??? ???? ?????

Excel 2002 ?? Excel 2003 ??? ????? ?? ??? ??? DataSet ?? XML ?????

?? ??? ?? ????? ?? ??? ???? illustrates ??DataSet????? ???? ?? ????? ?? ???? XML ???? ?? ??????? ???? ?? ????????WriteXML?????? ??? XML ???? ?? ????? ???? ??? ?? ?? Excel ??? ???? ?? ???? ??? Illustration ?? ????????, ?? ???DataSet??? OLEDB ??????? ?? ????? ?? Microsoft Access ????????? ????? ??????? ?? ???????? ????? ??? ??? ???????, ???? ??? ?? ??? ??? ?? ?????DataSet???????? ?? ????? C# 2005 ?? Visual C# .NET ?? ??????
  1. Microsoft Visual Studio 2005 ?? Microsoft Visual Studio .NET ??????? ????.. ????? ???????????? ??,????? ????-????? ????, ?? ???? ????????????. ??? ????Windows ??????????? ????? C# ????????? ??????? Form1 ???????? ??? ?? ???? ??..
  2. ????? ?????????????, ?? ??? ????????? ?????????? ????? ??? ????????? ???? ?? Form1 ?? ?? ??? ???????
  3. ???-????? ????Button1. ?????? ?? ??? ??? ????? ????? ????..
  4. ????? ???????? ????? ????directives Form1.cs ?? ???? ??? ?? ???:
    using System.Data.OleDb;
    using System.Xml;
    					
  5. Form1 ???? ?? ??? ???? ????? ?? ??????:
    private string strConn ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
    	+ " C:\\Program Files\\Microsoft Office\\Office10\\Samples\\"
    	+ "Northwind.mdb;";
    					
    ???:???? ??????? ?? ??? ???? ?? ??? ??????? ???????? ??? Northwind.mdb ???? ?? ??? ?? ?? ??????? ???? ?? ???????? ?? ?????
  6. ????? ??? ??????button1_Click??????:
    //Connect to the data source.
             OleDbConnection objConn = new OleDbConnection (strConn);
             try
             {
                objConn.Open();			
    
                //Fill a dataset with records from the Customers table.
                OleDbCommand objCmd = new OleDbCommand(
                   "Select CustomerID, CompanyName, ContactName, " 
                   + "Country, Phone from Customers", objConn);
                OleDbDataAdapter objAdapter = new OleDbDataAdapter();
                objAdapter.SelectCommand = objCmd;
                DataSet objDataset = new DataSet();
                objAdapter.Fill(objDataset);
    
    
                //Create the FileStream to write with.
                System.IO.FileStream fs = new System.IO.FileStream(
                   "C:\\Customers.xml", System.IO.FileMode.Create);
    
                //Create an XmlTextWriter for the FileStream.
                System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(
                   fs, System.Text.Encoding.Unicode);
    
                //Add processing instructions to the beginning of the XML file, one 
                //of which indicates a style sheet.
                xtw.WriteProcessingInstruction("xml", "version='1.0'");
                //xtw.WriteProcessingInstruction("xml-stylesheet", 
                  // "type='text/xsl' href='customers.xsl'");
    
                //Write the XML from the dataset to the file.
                objDataset.WriteXml(xtw);
                xtw.Close();
    
                //Close the database connection.
                objConn.Close();
             }
             catch (System.Exception ex)
             {
                MessageBox.Show(ex.Message);
             } 
    					
    ???:?? Visual Studio 2005 ??? ??? ?? ????????? ???? ?????? ??? ???????? ??? ??, ????? C# ????? ??????? ?? ?? ????????? ?? ??? ?? ?? ?? Windows ??????? ????????? ????? ???? ?????? ?? Form1 ??? ??? ??????? ?? ???????????? ???? ???? ?? ??????? ??? Form1.cs ?? Form1.designer.cs ??? ???? Form1.cs ??? ??? ?????? Form1.designer.cs ????? ????? ???? Windows ??????? ??????? ?? ??? ?? ??? ???????? ???? ???? ?? ?? ?? ?????? ?? ????? ????? ?? ???????? ?? ??? ?? ?? ???

    ????? C# 2005 ??? Windows ??????? ??????? ?? ???? ??? ???? ??????? ?? ??? ????? Microsoft ?????? ?? ???? ??????? (MSDN) ??? ????:
    HTTP://msdn2.Microsoft.com/en-us/library/ms173077.aspx
  7. ????????? ?? ????? ?? ????? ?? ??? F5 ?????..
  8. ????? ????,Button1XML ???? ?????, ???? ??? Form1 ????????? ?????? ???? ?? ??? ??? ?????
  9. Excel 2002 ?? Excel 2003 ?? ??????? ????, ?? C:\Customers.xml ?????? ????? ??????
  10. ?? ???? XML ?? ??? ????? ?? ????????????? ??? ????????? ?? ??????? ??? ????????? ???, ?? ??? ????? ?? ??? ???? ?? Excel ?? ???? ???

XML ??? Stylesheet ????? ???????? ????

?? ??? ?? ???? XML ???? ?? ???????? ?? Excel ?? ???? ????????????? ??? ????????? transform ???? ?? ??? ??? stylesheet (XSL) ?? ????? ???? ?? ??? ???? ?????? ???
  1. C:\Customers.xsl ?? ??? ??? ????? XSL ?????? ???? HTML ?????? ?? ???? ??? ?????? (???? Notepad.exe) ?? ????? ????:
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
      <xsl:template match="/">
        <HTML>
          <HEAD>
            <STYLE>   
              .HDR { background-color:bisque;font-weight:bold }
            </STYLE>
          </HEAD>
          <BODY>
            <TABLE>
              <COLGROUP WIDTH="100" ALIGN="CENTER"></COLGROUP>
              <COLGROUP WIDTH="200" ALIGN="LEFT"></COLGROUP>
              <COLGROUP WIDTH="200" ALIGN="LEFT"></COLGROUP>
              <COLGROUP WIDTH="100" ALIGN="LEFT"></COLGROUP>
              <COLGROUP WIDTH="100" ALIGN="LEFT"></COLGROUP>
              <TD CLASS="HDR">Customer ID</TD>
              <TD CLASS="HDR">Company</TD>
              <TD CLASS="HDR">Contact</TD>
              <TD CLASS="HDR">Country</TD>
              <TD CLASS="HDR">Phone</TD>
              <xsl:for-each select="NewDataSet/Table">
                <TR>
                  <TD><xsl:value-of select="CustomerID"/></TD>
                  <TD><xsl:value-of select="CompanyName"/></TD>
                  <TD><xsl:value-of select="ContactName"/></TD>
                  <TD><xsl:value-of select="Country"/></TD>
                  <TD><xsl:value-of select="Phone"/></TD>
                </TR>
              </xsl:for-each>
            </TABLE>
          </BODY>
        </HTML>
      </xsl:template>
    </xsl:stylesheet> 
    					
  2. ??? ??? ?? ????? ?????? ?? uncommentbutton1_Click??????:
    xtw.WriteProcessingInstruction("xml-stylesheet", 
    	"type='text/xsl' href='customers.xsl'");
    					
    ??? ?? ?????? ?? XML ???? ?? Excel stylesheet (Customers.xsl) ?????? ?? ??? ????? ???? ?? ??? ??? ?????? ??????? ????? ???

  3. ????????? ?? ????? ?? ????? ?? ??? F5 ?????..
  4. ????? ????,Button1XML ???? ?????, ???? ??? Form1 ????????? ?????? ???? ?? ??? ??? ?????
  5. Excel 2002 ?? Excel 2003 ?? ??????? ????, ?? C:\Customers.xml ?????? ????? ??????
  6. Excel XML ??? stylesheet ?? ??? ?????? ??????? ????? ??, ??????? ???? ???? ????? ????? ????? ?? ?? ????? ?????? ?????XML ???? ????????? ????? ???, ?? ??? ????????? stylesheet ???? ?? ?? ???? ?????. ???? ??? ??? ????Customers.XSL?? ????? ????OK. ??? ???? ?? XML ???? ???????? ?? ?? ?? ?? ????? ?? ?????? stylesheet ????????? ???? ??? ???
  7. ???? ??? ???? ?? Excel ?? ???? ???????

Transformed XML ?? ????? ?? ??? ??? ?? ????? ????

?? ????? ?? ?? XML ???? ?? Excel ??? ?????????? ??????? ?? ????? ???? ???? ??? ?? ?????? ??? ????????? ???? ?? ?? Excel ????????????? ?? ????????? ????? ?? ??? ???????? ???? ?? ??? ???? ????? ????? ????? illustrates ???? transforming ??? XML ?? ?????????? ????????? ?? ???? transformed XML ?? ????? ?? ??? ???? ????DataSet???????? ?? HTML.
  1. ???? ?????? ??????Microsoft Excel 10.0 ???????? ????????? ?? Microsoft Excel 11.0 ???????? ?????????. ??? ???? ?? ???, ????? ????? ?? ???? ????::
    1. ????? ????????????????? ??,?????? ??????.
    2. ????? ????COM??? ??, ??????Microsoft Excel 10.0 ???????? ????????? ?? Microsoft Excel 11.0 ???????? ??????????? ????? ??????? ????.
    3. ????? ????,OK??????????? ??????????? ????? ???? ??? ?? ??????? ???? ?? ??? ??? ??? ???? ???? ?????? ????? ????????? ?? ??? wrappers ????? ???? ?? ??? ?? ????? ??????? ???? ??, ?? ????? ???????.
  2. ????? ???????? ????? ????directives Form1.cs ?? ???? ??? ?? ???:
    using Excel = Microsoft.Office.Interop.Excel;
    					
  3. ????? C# 2005 ?? Visual C# .NET ????????? ???, Form1 ?? ??? ???? ???? ??? ???????
  4. ???-????? ????Button2. ?? ??? ????? ?? ??? ??? ????? ??, ?????? ?? ??? ????? ???Button2_Click??????:
    //Connect to the data source.
    OleDbConnection objConn = new OleDbConnection (strConn);
    objConn.Open();			
    
    //Fill a dataset with records from the Customers table.
    OleDbCommand objCmd = new OleDbCommand(
    	"Select CustomerID, CompanyName, ContactName, " 
    	+ "Country, Phone from Customers", objConn);
    OleDbDataAdapter objAdapter = new OleDbDataAdapter();
    objAdapter.SelectCommand = objCmd;
    DataSet objDataset = new DataSet();
    objAdapter.Fill(objDataset);
    
    //Create the FileStream to write with.
    System.IO.FileStream fs = new System.IO.FileStream(
    	"C:\\Customers.htm", System.IO.FileMode.Create);
    
    //Create an XmlTextWriter for the FileStream.
    System.Xml.XmlTextWriter xtw = new System.Xml.XmlTextWriter(
    	fs, System.Text.Encoding.Unicode);
    
    //Transform the XML using the stylesheet.
    XmlDataDocument xmlDoc = new XmlDataDocument(objDataset);
    System.Xml.Xsl.XslTransform xslTran = new System.Xml.Xsl.XslTransform();
    xslTran.Load("C:\\Customers.xsl");
    xslTran.Transform(xmlDoc, null, xtw);
    
    //Open the HTML file in Excel.
    Excel.Application oExcel = new Excel.Application();
    oExcel.Visible=true;
    oExcel.UserControl=true;
    Excel.Workbooks oBooks = oExcel.Workbooks;
    object oOpt = System.Reflection.Missing.Value; //for optional arguments
    oBooks.Open("c:\\customers.htm", oOpt, oOpt, oOpt, 
    	oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, oOpt, 
    	oOpt, oOpt, oOpt);
    
    					
  5. ????????? ?? ????? ?? ????? ?? ??? F5 ?????..
  6. ????? ????,Button2transformed XML ?? Excel ??? ??????
???:While the Excel 2002 and Excel 2003 Object Model does expose anOpenXMLmethod that enables you to programmatically open an XML file with stylesheets applied, the previous sample does not call this method due to a known problem with using this method from an Automation client. TheOpenXMLmethod works as expected when it is called from an Excel macro; however, when this method is called from an Automation client, theStyleSheetparameter is ignored.???????? ??????? ?? ???, ???? ?? ???? ?????? ?? ????? ?? ???? ?? Microsoft ???????? ??? ?????::
307230BUG: ????? OpenXML ???? ?? StyleSheets ???????? ???? ?? Excel 2002 ?? ???????

??????

???? ??????? ?? ???, ????? ???????? ???? ?????:
288215?????: Microsoft Excel 2002 ?? XML
302084Microsoft Excel ?? ????? C# .NET ?? ???????? ???? ?? ??? ???? ????
301216Visual Basic .NET ?? ????? ?? ?? ??????? ?? ?? DataSet ???????? ?? ????????? ???? ????
306023??? Excel ????????????? ?? ??? ???? ????? C# .NET ?? ????? ???? ?????????? ???? ????

???

???? ID: 307029 - ????? ???????: 04 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft Visual C# 2005
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft Excel 2002 Standard Edition
??????: 
kbhowtomaster kbmt KB307029 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:307029

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com