2013年3月11日星期一

5. C# Convert Excel to XML by OLEDB and DataSet


public XmlDocument MyConvertExcelToXml(string excelFilePath)
        {
                XmlDocument excelData = new XmlDocument();
                DataSet excelTableDataSet = new DataSet();
                StreamReader excelContent = new StreamReader(excelFilePath, System.Text.Encoding.Default);
                string stringConnectToExcelFile = string.Format("provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 12.0;");
                System.Data.OleDb.OleDbConnection oleConnectionToExcelFile = new System.Data.OleDb.OleDbConnection(stringConnectToExcelFile);
                System.Data.OleDb.OleDbDataAdapter oleDataAdapterForGetExcelTable = new System.Data.OleDb.OleDbDataAdapter(string.Format("select * from [Output$]"), oleConnectionToExcelFile);
                try
                {
                    oleDataAdapterForGetExcelTable.Fill(excelTableDataSet);
                }
                catch
                {
                    return null;
                }
                string excelOutputXml = Path.GetTempFileName();
                excelTableDataSet.WriteXml(excelOutputXml);
                excelData.Load(excelOutputXml);
                File.Delete(excelOutputXml);
                return excelData;
        }

Remember to using the namespace:
using System.Xml;
using System.Data;
And change the sheetname “Output” to your sheet name.

没有评论:

发表评论