Excel To Xml

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
using System.Xml;
using System.Drawing;

namespace RWExcel
{
    public class RWExcel
    {
        #region 成员数据
        private OleDbConnection connection;
        private OleDbDataAdapter dataAdapter;
        private String strConnection;
        #endregion

        #region 构造数据链接的函数
        public RWExcel(String strFileName)
        {
            strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1/"";
            connection = new OleDbConnection(strConnection);
        }
        public RWExcel(String strFileName, bool isHead)
        {
            if (isHead)
            {
                strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties=/"Excel 8.0;HDR=YES;IMEX=1/"";
                connection = new OleDbConnection(strConnection);
            }
            else
            {
                strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + ";Extended Properties=/"Excel 8.0;HDR=NO;IMEX=1/"";
                connection = new OleDbConnection(strConnection);
            }
        }
        #endregion

        #region  获取excel文件中电子表格的名字
        /// <summary>
        /// 获取excel文件中电子表格的名字
        /// </summary>
        /// <returns></returns>
        public string[]  GetExcelName()
        {
            System.Data.DataTable dataTable = null;
            string[] sheetName = null;
            try
            {
                connection.Open();
                dataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dataTable == null)
                {
                    return null;
                }
                sheetName = new string[dataTable.Rows.Count];
                int i = 0;
                foreach (DataRow row in dataTable.Rows)
                {
                    string strTmp = row["TABLE_NAME"].ToString();
                    sheetName[i] = strTmp.Substring(0, strTmp.Length - 1); //删除表名的最后一个字符$
                    i++;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());

            }
            finally
            {
                connection.Close();
            }
            return sheetName;
        }
        #endregion

        #region Excel文件和xml文件的相互转化

        /// <summary>
        /// 根据提供的Excel路径将Excel文件转换成xml文件并存放在特定目录下
        /// </summary>
        /// <param name="strFileName"></param>
        /// <param name="strDesPath"></param>
        public bool GetAndWrite(String strFileName, string strDesPath)
        {

            Microsoft.Office.Interop.Excel.Application ExcelRead = new ApplicationClass();
            Microsoft.Office.Interop.Excel.Workbooks workbooks = ExcelRead.Workbooks;
            object mo = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Workbook workbook = null;
            try
            {
                workbook = workbooks.Open(strFileName, mo, mo, mo, mo, mo, mo, mo, mo, mo, mo, mo, mo, mo, mo);

                string[] strSheetNames = this.GetExcelName();
                //设置保存xml文件
                string FileName = strDesPath;
                XmlTextWriter writexml = new XmlTextWriter(FileName, Encoding.Default);

                writexml.Formatting = Formatting.Indented;
                writexml.WriteStartDocument();
                writexml.WriteStartElement("DataSet");

                for (int m = 0; m < strSheetNames.Length; ++m)
                {

                    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[strSheetNames[m]];
                    int bgnRow = (worksheet.UsedRange.Cells.Row > 1) ? worksheet.UsedRange.Cells.Row - 1 : worksheet.UsedRange.Cells.Row;
                    int bgnColumn = (worksheet.UsedRange.Cells.Column > 1) ? worksheet.UsedRange.Cells.Column - 1 : worksheet.UsedRange.Cells.Column;

                    writexml.WriteStartElement("DataTable");
                    writexml.WriteAttributeString("name", strSheetNames[m].ToString());
                    writexml.WriteStartElement("table");
                    writexml.WriteAttributeString("sumRow", worksheet.UsedRange.Cells.Rows.Count.ToString());
                    writexml.WriteAttributeString("sumCol", worksheet.UsedRange.Cells.Columns.Count.ToString());
                    List<string> columnName = new List<string>();
                    string column = string.Empty;
                    for (int j = bgnColumn; j < worksheet.UsedRange.Cells.Columns.Count + bgnColumn; ++j)
                    {
                        column = ((Microsoft.Office.Interop.Excel.Range)worksheet.UsedRange.Cells[bgnRow, j]).Text.ToString();
                        if (column.Contains("("))
                        {
                            column = column.Replace("(", "_x0028_");
                        }
                        if (column.Contains(")"))
                        {
                            column = column.Replace("(", "_x0029_");
                        }
                        columnName.Add(column);
                    }

                    for (int i = bgnRow + 1; i < worksheet.UsedRange.Cells.Rows.Count + bgnRow; ++i)
                    {
                        writexml.WriteStartElement(strSheetNames[m].ToString());
                        for (int j = bgnColumn; j < worksheet.UsedRange.Cells.Columns.Count + bgnColumn; ++j)
                        {
                            string strData = ((Microsoft.Office.Interop.Excel.Range)worksheet.UsedRange.Cells[i, j]).Text.ToString();

                            #region 字符串xml化
                            if (strData.Contains("("))
                            {
                                strData = strData.Replace("(", "_x0028_");
                            }
                            if (strData.Contains(")"))
                            {
                                strData = strData.Replace("(", "_x0029_");
                            }
                            if (strData=="TRUE")
                            {
                                strData = "true";
                            }
                            //strData = strData.ToString("O");
                            #endregion

                            int MergeAcross = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).MergeArea.Count;
                            MergeAcross -= 1;
                            string backgroundColor = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Interior.Color.ToString();
                            Int32 bgcolor = Int32.Parse(backgroundColor);
                            System.Drawing.Color backClor = System.Drawing.Color.FromArgb(bgcolor);
                            string Bgcolor = System.Drawing.ColorTranslator.ToHtml(backClor).ToString();

                            string fontFamily = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Font.Name.ToString();

                            string fontColor = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Font.Color.ToString();
                            Int32 fcolor = Int32.Parse(fontColor);
                            System.Drawing.Color FontColor = System.Drawing.Color.FromArgb(fcolor);
                            string font_color = System.Drawing.ColorTranslator.ToHtml(FontColor).ToString();

                            string fontSize = ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Font.Size.ToString();
                            if (MergeAcross > 1)
                            {
                                j += MergeAcross;

                            }
                            writexml.WriteStartElement(columnName[j - 1]);
                            writexml.WriteAttributeString("isMerge", MergeAcross.ToString());
                            if ((backgroundColor == "16777215") && (object.Equals(fontFamily, "宋体")) && (fontColor == "0") && (fontSize == "12"))
                            {
                                writexml.WriteAttributeString("style", "");
                            }
                            else
                            {
                                writexml.WriteAttributeString("style", " background-color:" + Bgcolor + ";" + "font-family:" + fontFamily + ";" + "font-color:" + font_color + ";" + "font-size:" + fontSize + ";");
                            }
                            writexml.WriteString(strData);
                            writexml.WriteEndElement();

                        }
                        writexml.WriteEndElement();

                    }
                    writexml.WriteEndElement();
                    writexml.WriteEndElement();

                }
                writexml.WriteEndDocument();
                writexml.Close();
                writexml = null;

                workbook.Close(Type.Missing, Type.Missing, Type.Missing);
                workbooks.Close();
                ExcelRead.Quit();

                workbooks = null;
                workbook = null;
                ExcelRead = null;
                GC.Collect();
            }
            catch (Exception ex)
            {
                workbook.Close(Type.Missing, Type.Missing, Type.Missing);
                workbooks.Close();
                ExcelRead.Quit();
               
                workbooks = null;
                workbook = null;
                ExcelRead = null;
                GC.Collect();
                return false;
            }

            return true;

        }

        /// <summary>
        /// 读取xml文件并转化为excel文件
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="newname"></param>
        public void ExportExcel(string filepath, string newname)
        {
            XmlDocument doc = new XmlDocument();
            doc.Load(filepath);
            XmlNode table = doc.SelectSingleNode("//*[name()='table']");

            Microsoft.Office.Interop.Excel.Application ExcelWrite = new ApplicationClass();
            ExcelWrite.Visible = false;
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Workbooks workbooks = ExcelWrite.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

            int i, j;
            i = j = 1;
            foreach (XmlNode row in table.ChildNodes)
            {
                j = 1;
                foreach (XmlNode col in row.ChildNodes)
                {
                    worksheet.Cells[i, j] = col.InnerText;
                    j++;
                }
                i++;

            }
            worksheet.SaveAs(newname, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            workbook.Close(missing, missing, missing);
            ExcelWrite.Quit();

        }
         #endregion

     }
}