.NET导入导出Excel方法总结

最近，应项目的需求，需要实现Excel的导入导出功能，对于Web架构的Excel导入导出功能，比较传统的实现方式是：

1）导入Excel：将Excel文件上传到服务器的某一文件夹下，然后在服务端完成Excel的读取及数据的存储；

2）导出Excel：在服务端生成需要导出的Excel，然后下载到客户端。

其中，文件的上传和下载本文不在详述，以下主要写一些DataTable或DataSet与Excel之间的相互转换。

转换方式多种多样，网上也有很多前辈分享的代码实现，本文也借鉴了前辈的诸多思路及代码，具体方法如下：

1. DCOM方式

使用DCOM方式实现Excel的导入导出功能，首先需要在服务端安装office软件。

具体实现代码如下：

1）数据导出到Excel

    public class ExportExcel : IDisposable
    {
        private Excel.ApplicationClass excelApp;
        private Excel.Workbook workBook;
        private Excel.Worksheet workSheet;
        private Excel.Range range;

        public void DataTableToExcel(DataTable sourceTable, string fileName)
        {
            excelApp = new Excel.ApplicationClass();
            if (excelApp == null)
            {
                throw new Exception("打开Excel程序错误！");
            }

            workBook = excelApp.Workbooks.Add(true);
            workSheet = (Excel.Worksheet)workBook.Worksheets[];
            int rowIndex = ;          

            //写入列名
            ++rowIndex;
            for (int i = ; i < sourceTable.Columns.Count; i++)
            {
                workSheet.Cells[rowIndex, i + ] = sourceTable.Columns[i].ColumnName;
            }
            range = workSheet.get_Range(workSheet.Cells[rowIndex, ], workSheet.Cells[rowIndex, sourceTable.Columns.Count]);

            FontStyle headerStyle = new FontStyle
            {
                FontSize = ,
                BordersValue = ,
                FontBold = true,
                EntireColumnAutoFit = true
            };
            FontStyleHelper.SetFontStyleForRange(range, headerStyle);

            //写入数据
            ++rowIndex;
            for (int r = ; r < sourceTable.Rows.Count; r++)
            {
                for (int i = ; i < sourceTable.Columns.Count; i++)
                {
                    workSheet.Cells[rowIndex, i + ] = ExportHelper.ConvertToCellData(sourceTable, r, i);
                }
                rowIndex++;
            }
            range = workSheet.get_Range(workSheet.Cells[, ], workSheet.Cells[sourceTable.Rows.Count + , sourceTable.Columns.Count]);
            FontStyle bodyStyle = new FontStyle
            {
                FontSize = ,
                BordersValue = ,
                FontAlign = Infrastruction.FontAlign.Right,
                EntireColumnAutoFit = true
            };
            FontStyleHelper.SetFontStyleForRange(range, bodyStyle);

            //只保存一个sheet页
            //workSheet.SaveAs(fileName, Excel.XlFileFormat.xlTemplate, Type.Missing, Type.Missing, Type.Missing,
            //        Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
            //保存整个Excel
            workBook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
            workBook.Close(false, Type.Missing, Type.Missing);
            excelApp.Quit();

            Dispose();
        }

        public void Dispose()
        {
            GC.Collect();
            BaseExcel.Dispose(excelApp, workSheet, workBook, range);
        }
    }

2）数据导入到内存

    public class ImportExcel : IDisposable
    {
        private Excel.ApplicationClass excelApp;
        private Excel.Workbook workBook;
        private Excel.Worksheet workSheet;
        private Excel.Range range;

        public DataSet ExcelToDataSet(string fileName)
        {
            if (!File.Exists(fileName))
            {
                return null;
            }
            FileInfo file = new FileInfo(fileName);
            string strConnection = string.Empty;
            string extension = file.Extension;
            string vsSql = string.Empty;
            switch (extension)
            {
                case ".xls":
                    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                case ".xlsx":
                    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;
                default:
                    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;
            }
            DataSet ds = ImportHelper.GetDataSetFromExcel(strConnection);

            Dispose();
            ds = ImportHelper.ConvertDataSet(ds);
            return ds;
        }

        public DataSet ExcelToDataSetByDcom(string fileName)
        {
            DataSet result = null;
            excelApp = new Excel.ApplicationClass();
            if (excelApp == null)
            {
                throw new Exception("打开Excel程序错误！");
            }

            excelApp.Visible = false; excelApp.UserControl = true;
            // 以只读的形式打开EXCEL文件
            workBook = excelApp.Application.Workbooks.Open(fileName, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing,
             Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            int sheets = workBook.Worksheets.Count;
            if (sheets >= )
            {
                result = new DataSet();
            }
            for(int i = ; i <= sheets; i++)
            {
                workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
                string sheetName = workSheet.Name;

                DataTable dt = new DataTable();
                dt.TableName = sheetName;

                //取得总记录行数
                int rows = workSheet.UsedRange.Cells.Rows.Count; //得到行数
                int columns = workSheet.UsedRange.Cells.Columns.Count;//得到列数
                if (rows ==  || columns == ) return null;
                //取得数据范围区域
                range = workSheet.get_Range(workSheet.Cells[, ], workSheet.Cells[rows, columns]);
                object[,] arryItem = (object[,])range.Value2; //get range's value

                //生成DataTable的列
                for(int col = ; col <= columns; col++)
                {
                    string dcName = arryItem[, col].ToString().Trim();
                    DataColumn dc = new DataColumn(dcName, typeof(string));
                    dt.Columns.Add(dc);
                }
                //将数据填充到DataTable
                for(int row = ; row <= rows; row++)
                {
                    object[] rowvalue = new object[columns];
                    for (int col = ; col <= columns; col++)
                    {
                        rowvalue[col - ] = arryItem[row, col];
                    }
                    dt.Rows.Add(rowvalue);
                }
                //将DataTable填充到DataSet
                result.Tables.Add(dt);
            }

            //清理非托管对象
            workBook.Close(false, Type.Missing, Type.Missing);
            excelApp.Quit();
            Dispose();
            return result;
        }

        public void Dispose()
        {
            GC.Collect();
            BaseExcel.Dispose(excelApp, workSheet, workBook, range);
        }
    }

3）其他辅助类

    public class BaseExcel
    {
        /// <summary>
        /// 释放Excel资源
        /// </summary>
        /// <param name="excelApp"></param>
        public static void Dispose(Excel.ApplicationClass excelApp, Excel.Worksheet workSheet, Excel.Workbook workBook, Excel.Range range)
        {
            //清理非托管的代码
            if (workSheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
                workSheet = null;
            }
            if (workBook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
                workBook = null;
            }
            if (excelApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                excelApp = null;
            }
            if (range != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                range = null;
            }
            KillProcess();
        }
        /// <summary>
        /// 关闭进程
        /// </summary>
        /// <param name="hwnd"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        private static void Kill(Excel.Application excel)
        {
            int id = ;
            IntPtr intptr = new IntPtr(excel.Hwnd);    //得到这个句柄，具体作用是得到这块内存入口
            System.Diagnostics.Process p = null;
            try
            {
                GetWindowThreadProcessId(intptr, out id);  //得到本进程唯一标志
                p = System.Diagnostics.Process.GetProcessById(id);  //得到对进程k的引用
                if (p != null)
                {
                    p.Kill();  //关闭进程k
                    p.Dispose();
                }
            }
            catch
            {
            }
        }
        //强制结束进程
        private static void KillProcess()
        {
            System.Diagnostics.Process[] allProcess = System.Diagnostics.Process.GetProcesses();
            foreach (System.Diagnostics.Process thisprocess in allProcess)
            {
                string processName = thisprocess.ProcessName;
                if (processName.ToLower() == "excel")
                {
                    try
                    {
                        thisprocess.Kill();
                    }
                    catch
                    {
                    }
                }
            }
        }
    }

BaseExcel

    public class FontStyle
    {
        /// <summary>
        /// 字体大小
        /// </summary>
        public int FontSize { get; set; }
        /// <summary>
        /// 字体名称
        /// </summary>
        public string FontName { get; set; }
        /// <summary>
        /// 是否为粗体
        /// </summary>
        public bool FontBold { get; set; }
        /// <summary>
        /// 字体对齐方式
        /// </summary>
        public FontAlign FontAlign { get; set; }
        /// <summary>
        /// 边框样式
        /// </summary>
        public int BordersValue { get; set; }
        /// <summary>
        /// 字体颜色索引
        /// </summary>
        public int FontColorIndex { get; set; }
        /// <summary>
        /// 背景颜色索引
        /// </summary>
        public int InteriorColorIndex { get; set; }
        /// <summary>
        /// 列宽自适应
        /// </summary>
        public bool EntireColumnAutoFit { get; set; }
    }

    public enum FontAlign
    {
        Center,
        Right,
        Left
    }

FontStyle

    public class FontStyleHelper
    {
        /// <summary>
        /// 对选中区域设置格式
        /// </summary>
        /// <param name="range">选中区域</param>
        /// <param name="fontStyle">样式表</param>
        public static void SetFontStyleForRange(Excel.Range range, FontStyle fontStyle)
        {
            if (fontStyle.FontSize != )
            {
                range.Font.Size = fontStyle.FontSize;
            }
            if (fontStyle.FontName != null)
            {
                range.Font.Name = fontStyle.FontName;
            }
            if (fontStyle.FontBold != false)
            {
                range.Font.Bold = fontStyle.FontBold;
            }
            if (fontStyle.FontAlign == FontAlign.Center)
            {
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            }
            else if (fontStyle.FontAlign == FontAlign.Left)
            {
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
            }
            else if (fontStyle.FontAlign == FontAlign.Right)
            {
                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            }
            if (fontStyle.BordersValue != )
            {
                range.Borders.Value = fontStyle.BordersValue;
            }
            if (fontStyle.FontColorIndex != )
            {
                range.Font.ColorIndex = fontStyle.FontColorIndex;
            }
            if (fontStyle.InteriorColorIndex != )
            {
                range.Interior.ColorIndex = fontStyle.InteriorColorIndex;
            }
            if (fontStyle.EntireColumnAutoFit == true)
            {
                range.EntireColumn.AutoFit();
            }
        }
    }

FontStyleHelper

    public class ExportHelper
    {
        public static string ConvertToCellData(DataTable sourceTable, int rowIndex, int colIndex)
        {
            DataColumn col = sourceTable.Columns[colIndex];
            object data = sourceTable.Rows[rowIndex][colIndex];
            if (col.DataType == System.Type.GetType("System.DateTime"))
            {
                if (data.ToString().Trim() != "")
                {
                    return Convert.ToDateTime(data).ToString("yyyy-MM-dd HH:mm:ss");
                }
                else
                {
                    return (Convert.ToDateTime(DateTime.Now)).ToString("yyyy-MM-dd HH:mm:ss");
                }
            }
            else if (col.DataType == System.Type.GetType("System.String"))
            {
                return "'" + data.ToString().Trim();
            }
            else
            {
                return data.ToString().Trim();
            }
        }
    }

ExportHelper

    public class ImportHelper
    {
        /// <summary>
        /// 通过OleDb获得DataSet
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="sheetNames"></param>
        /// <returns></returns>
        public static DataSet GetDataSetFromExcel(string connStr)
        {
            DataSet ds = null;
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                try
                {
                    conn.Open();
                    DataTable tblName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    if (tblName.Rows.Count <  || tblName == null)
                    {
                        conn.Close();
                        return null;
                    }
                    else
                    {
                        ds = new DataSet();
                        DataTable tbl = null;
                        for (int i = ; i < tblName.Rows.Count; i++)
                        {
                            tbl = new DataTable();
                            tbl.TableName = tblName.Rows[i]["TABLE_NAME"].ToString().Replace("$", "");
                            string vsSql = "SELECT * FROM [" + tblName.Rows[i]["TABLE_NAME"].ToString() + "]";
                            OleDbDataAdapter myCommand = new OleDbDataAdapter(vsSql, conn);
                            myCommand.Fill(tbl);
                            ds.Tables.Add(tbl.Copy());
                            tbl.Dispose();
                            tbl = null;
                        }
                        conn.Close();
                    }
                }
                catch (Exception ex)
                {
                    conn.Close();
                    throw new Exception(ex.Source + ":" + ex.Message);
                }
            }
            return ds;
        }

        public static DataSet ConvertDataSet(DataSet source)
        {
            if (source == null) return null;

            DataSet result = new DataSet();
            int dataTableCount = source.Tables.Count;
            DataTable temp = null;
            for (int i = ; i < dataTableCount; i++)
            {
                temp = ConvertDataTable(source.Tables[i]);
                result.Tables.Add(temp);
                result.Tables[i].TableName = source.Tables[i].TableName;
            }
            return result;
        }

        private static DataTable ConvertDataTable(DataTable source)
        {
            DataTable result = new DataTable();
            int columnsCount = source.Columns.Count;
            int rowsCount = source.Rows.Count;
            for (int i = ; i < columnsCount; i++)
            {
                DataColumn column = new DataColumn(source.Rows[][i].ToString().Trim());
                result.Columns.Add(column);
            }
            DataRow dr;
            for (int r = ; r < rowsCount; r++)
            {
                dr = result.NewRow();
                for (int c = ; c < columnsCount; c++)
                {
                    dr[c] = source.Rows[r][c].ToString().Trim();
                }
                result.Rows.Add(dr);
            }
            return result;
        }
    }

ImportHelper

2. Open XML方式

  该方法只针对office2007及以上版本有效，因为office2007以上版本是基于XML实现的数据存储，详细内容不做讲解，有兴趣的网友可以将Excel文件扩展名修改为zip并解压，然后对解压出的文件进行分析。

　以Open XML的方式实现Excel的导入导出，需要先下载并安装Open XML Format SDK 2.0及其以上版本，具体下载地址为：https://www.microsoft.com/en-us/download/details.aspx?id=5124。SDK默认会安装在C:\Program Files (x86)\Open XML Format SDK\V2.0 (64bit)目录下，lib子目录下的DocumentFormat.OpenXml.dll必须被引用到项目中。

具体代码如下：

    public class ExcelOperater
    {
        #region 读取Excel
        /// <summary>
        /// 将Excel数据读取到DataSet
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public DataSet ExcelToDataSet(string filePath)
        {
            DataSet dataSet = new DataSet();
            try
            {
                using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))
                {
                    //指定WorkbookPart对象
                    WorkbookPart workBookPart = spreadDocument.WorkbookPart;
                    //获取Excel中SheetName集合
                    List<string> sheetNames = GetSheetNames(workBookPart);

                    foreach (string sheetName in sheetNames)
                    {
                        DataTable dataTable = WorkSheetToTable(workBookPart, sheetName);
                        if (dataTable != null)
                        {
                            dataSet.Tables.Add(dataTable);//将表添加到数据集
                        }
                    }
                }
            }
            catch (Exception exp)
            {
                //throw new Exception("可能Excel正在打开中,请关闭重新操作！");
            }
            return dataSet;
        }

        /// <summary>
        /// 将Excel数据读取到DataTable
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(string sheetName, string filePath)
        {
            DataTable dataTable = new DataTable();
            try
            {
                //根据Excel流转换为spreadDocument对象
                using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))//Excel文档包
                {
                    //Workbook workBook = spreadDocument.WorkbookPart.Workbook;//主文档部件的根元素
                    //Sheets sheeets = workBook.Sheets;//块级结构（如工作表、文件版本等）的容器
                    WorkbookPart workBookPart = spreadDocument.WorkbookPart;
                    //获取Excel中SheetName集合
                    List<string> sheetNames = GetSheetNames(workBookPart);

                    if (sheetNames.Contains(sheetName))
                    {
                        //根据WorkSheet转化为Table
                        dataTable = WorkSheetToTable(workBookPart, sheetName);
                    }
                }
            }
            catch (Exception exp)
            {
                //throw new Exception("可能Excel正在打开中,请关闭重新操作！");
            }
            return dataTable;
        }

        /// <summary>
        /// 获取Excel中的sheet页名称
        /// </summary>
        /// <param name="workBookPart"></param>
        /// <returns></returns>
        private List<string> GetSheetNames(WorkbookPart workBookPart)
        {
            List<string> sheetNames = new List<string>();
            Sheets sheets = workBookPart.Workbook.Sheets;
            foreach (Sheet sheet in sheets)
            {
                string sheetName = sheet.Name;
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheetNames.Add(sheetName);
                }
            }
            return sheetNames;
        }

        /// <summary>
        /// 获取指定sheet名称的Excel数据行集合
        /// </summary>
        /// <param name="workBookPart"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public IEnumerable<Row> GetWorkBookPartRows(WorkbookPart workBookPart, string sheetName)
        {
            IEnumerable<Row> sheetRows = null;
            //根据表名在WorkbookPart中获取Sheet集合
            IEnumerable<Sheet> sheets = workBookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
            if (sheets.Count() == )
            {
                return null;//没有数据
            }

            WorksheetPart workSheetPart = workBookPart.GetPartById(sheets.First().Id) as WorksheetPart;
            //获取Excel中得到的行
            sheetRows = workSheetPart.Worksheet.Descendants<Row>();

            return sheetRows;
        }

        /// <summary>
        /// 将指定sheet名称的数据转换成DataTable
        /// </summary>
        /// <param name="workBookPart"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        private DataTable WorkSheetToTable(WorkbookPart workBookPart, string sheetName)
        {
            //创建Table
            DataTable dataTable = new DataTable(sheetName);

            //根据WorkbookPart和sheetName获取该Sheet下所有行数据
            IEnumerable<Row> sheetRows = GetWorkBookPartRows(workBookPart, sheetName);
            if (sheetRows == null || sheetRows.Count() <= )
            {
                return null;
            }

            //将数据导入DataTable,假定第一行为列名,第二行以后为数据
            foreach (Row row in sheetRows)
            {
                //获取Excel中的列头
                if (row.RowIndex == )
                {
                    List<DataColumn> listCols = GetDataColumn(row, workBookPart);
                    dataTable.Columns.AddRange(listCols.ToArray());
                }
                else
                {
                    //Excel第二行同时为DataTable的第一行数据
                    DataRow dataRow = GetDataRow(row, dataTable, workBookPart);
                    if (dataRow != null)
                    {
                        dataTable.Rows.Add(dataRow);
                    }
                }
            }
            return dataTable;
        }

        /// <summary>
        /// 获取数字类型格式集合
        /// </summary>
        /// <param name="workBookPart"></param>
        /// <returns></returns>
        private List<string> GetNumberFormatsStyle(WorkbookPart workBookPart)
        {
            List<string> dicStyle = new List<string>();
            Stylesheet styleSheet = workBookPart.WorkbookStylesPart.Stylesheet;
            var test = styleSheet.NumberingFormats;
            if (test == null) return null;
            OpenXmlElementList list = styleSheet.NumberingFormats.ChildElements;//获取NumberingFormats样式集合

            foreach (var element in list)//格式化节点
            {
                if (element.HasAttributes)
                {
                    using (OpenXmlReader reader = OpenXmlReader.Create(element))
                    {
                        if (reader.Read())
                        {
                            if (reader.Attributes.Count > )
                            {
                                string numFmtId = reader.Attributes[].Value;//格式化ID
                                string formatCode = reader.Attributes[].Value;//格式化Code
                                dicStyle.Add(formatCode);//将格式化Code写入List集合
                            }
                        }
                    }
                }
            }
            return dicStyle;
        }

        /// <summary>
        /// 获得DataColumn
        /// </summary>
        /// <param name="row"></param>
        /// <param name="workBookPart"></param>
        /// <returns></returns>
        private List<DataColumn> GetDataColumn(Row row, WorkbookPart workBookPart)
        {
            List<DataColumn> listCols = new List<DataColumn>();
            foreach (Cell cell in row)
            {
                string cellValue = GetCellValue(cell, workBookPart);
                DataColumn col = new DataColumn(cellValue);
                listCols.Add(col);
            }
            return listCols;
        }

        /// <summary>
        /// 将sheet页中的一行数据转换成DataRow
        /// </summary>
        /// <param name="row"></param>
        /// <param name="dateTable"></param>
        /// <param name="workBookPart"></param>
        /// <returns></returns>
        private DataRow GetDataRow(Row row, DataTable dateTable, WorkbookPart workBookPart)
        {
            //读取Excel中数据,一一读取单元格,若整行为空则忽视该行
            DataRow dataRow = dateTable.NewRow();
            IEnumerable<Cell> cells = row.Elements<Cell>();

            int cellIndex = ;//单元格索引
            int nullCellCount = cellIndex;//空行索引
            foreach (Cell cell in row)
            {
                string cellVlue = GetCellValue(cell, workBookPart);
                if (string.IsNullOrEmpty(cellVlue))
                {
                    nullCellCount++;
                }

                dataRow[cellIndex] = cellVlue;
                cellIndex++;
            }
            if (nullCellCount == cellIndex)//剔除空行
            {
                dataRow = null;//一行中单元格索引和空行索引一样
            }
            return dataRow;
        }

        /// <summary>
        /// 获得单元格数据值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="workBookPart"></param>
        /// <returns></returns>
        public string GetCellValue(Cell cell, WorkbookPart workBookPart)
        {
            string cellValue = string.Empty;
            if (cell.ChildElements.Count == )//Cell节点下没有子节点
            {
                return cellValue;
            }
            string cellRefId = cell.CellReference.InnerText;//获取引用相对位置
            string cellInnerText = cell.CellValue.InnerText;//获取Cell的InnerText
            cellValue = cellInnerText;//指定默认值(其实用来处理Excel中的数字)

            //获取WorkbookPart中NumberingFormats样式集合
            //List<string> dicStyles = GetNumberFormatsStyle(workBookPart);
            //获取WorkbookPart*享String数据
            SharedStringTable sharedTable = workBookPart.SharedStringTablePart.SharedStringTable;

            try
            {
                EnumValue<CellValues> cellType = cell.DataType;//获取Cell数据类型
                if (cellType != null)//Excel对象数据
                {
                    switch (cellType.Value)
                    {
                        case CellValues.SharedString://字符串
                            //获取该Cell的所在的索引
                            int cellIndex = int.Parse(cellInnerText);
                            cellValue = sharedTable.ChildElements[cellIndex].InnerText;
                            break;
                        case CellValues.Boolean://布尔
                            cellValue = (cellInnerText == "") ? "TRUE" : "FALSE";
                            break;
                        case CellValues.Date://日期
                            cellValue = Convert.ToDateTime(cellInnerText).ToString();
                            break;
                        case CellValues.Number://数字
                            cellValue = Convert.ToDecimal(cellInnerText).ToString();
                            break;
                        default: cellValue = cellInnerText; break;
                    }
                }
                else//格式化数据
                {
                    #region 根据Excel单元格格式设置数据类型，该部分代码有误，暂未处理
                    /*
                    if (dicStyles.Count > 0 && cell.StyleIndex != null)//对于数字,cell.StyleIndex==null
                    {
                        int styleIndex = Convert.ToInt32(cell.StyleIndex.Value);
                        string cellStyle = dicStyles[styleIndex - 1];//获取该索引的样式
                        if (cellStyle.Contains("yyyy") || cellStyle.Contains("h")
                            || cellStyle.Contains("dd") || cellStyle.Contains("ss"))
                        {
                            //如果为日期或时间进行格式处理,去掉“;@”
                            cellStyle = cellStyle.Replace(";@", "");
                            while (cellStyle.Contains("[") && cellStyle.Contains("]"))
                            {
                                int otherStart = cellStyle.IndexOf('[');
                                int otherEnd = cellStyle.IndexOf("]");

                                cellStyle = cellStyle.Remove(otherStart, otherEnd - otherStart + 1);
                            }
                            double doubleDateTime = double.Parse(cellInnerText);
                            DateTime dateTime = DateTime.FromOADate(doubleDateTime);//将Double日期数字转为日期格式
                            if (cellStyle.Contains("m")) { cellStyle = cellStyle.Replace("m", "M"); }
                            if (cellStyle.Contains("AM/PM")) { cellStyle = cellStyle.Replace("AM/PM", ""); }
                            cellValue = dateTime.ToString(cellStyle);//不知道为什么Excel 2007中格式日期为yyyy/m/d
                        }
                        else//其他的货币、数值
                        {
                            cellStyle = cellStyle.Substring(cellStyle.LastIndexOf('.') - 1).Replace("\\", "");
                            decimal decimalNum = decimal.Parse(cellInnerText);
                            cellValue = decimal.Parse(decimalNum.ToString(cellStyle)).ToString();
                        }
                    }
                    */
                    #endregion
                }
            }
            catch
            {
                //string expMessage = string.Format("Excel中{0}位置数据有误,请确认填写正确！", cellRefId);
                //throw new Exception(expMessage);
                cellValue = "N/A";
            }
            return cellValue;
        }

        /// <summary>
        /// 获得sheet页集合
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private List<string> GetExcelSheetNames(string filePath)
        {
            string sheetName = string.Empty;
            List<string> sheetNames = new List<string>();//所有Sheet表名
            using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))
            {
                WorkbookPart workBook = spreadDocument.WorkbookPart;
                Stream stream = workBook.GetStream(FileMode.Open);
                XmlDocument xmlDocument = new XmlDocument();
                xmlDocument.Load(stream);

                XmlNamespaceManager xmlNSManager = new XmlNamespaceManager(xmlDocument.NameTable);
                xmlNSManager.AddNamespace("default", xmlDocument.DocumentElement.NamespaceURI);
                XmlNodeList nodeList = xmlDocument.SelectNodes("//default:sheets/default:sheet", xmlNSManager);

                foreach (XmlNode node in nodeList)
                {
                    sheetName = node.Attributes["name"].Value;
                    sheetNames.Add(sheetName);
                }
            }
            return sheetNames;
        }

        #region SaveCell
        private void InsertTextCellValue(Worksheet worksheet, string column, uint row, string value)
        {
            Cell cell = ReturnCell(worksheet, column, row);
            CellValue v = new CellValue();
            v.Text = value;
            cell.AppendChild(v);
            cell.DataType = new EnumValue<CellValues>(CellValues.String);
            worksheet.Save();
        }
        private void InsertNumberCellValue(Worksheet worksheet, string column, uint row, string value)
        {
            Cell cell = ReturnCell(worksheet, column, row);
            CellValue v = new CellValue();
            v.Text = value;
            cell.AppendChild(v);
            cell.DataType = new EnumValue<CellValues>(CellValues.Number);
            worksheet.Save();
        }
        private static Cell ReturnCell(Worksheet worksheet, string columnName, uint row)
        {
            Row targetRow = ReturnRow(worksheet, row);

            if (targetRow == null)
                return null;

            return targetRow.Elements<Cell>().Where(c =>
               string.Compare(c.CellReference.Value, columnName + row,
               true) == ).First();
        }
        private static Row ReturnRow(Worksheet worksheet, uint row)
        {
            return worksheet.GetFirstChild<SheetData>().
            Elements<Row>().Where(r => r.RowIndex == row).First();
        }
        #endregion

        #endregion

        #region 写入Excel
        /// <summary>
        /// 在指定路径创建SpreadsheetDocument文档
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        private SpreadsheetDocument CreateParts(string filePath)
        {
            SpreadsheetDocument document = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);

            WorkbookPart workbookPart = document.AddWorkbookPart();

            workbookPart.Workbook = new Workbook();

            return document;
        }

        /// <summary>
        /// 创建WorksheetPart
        /// </summary>
        /// <param name="workbookPart"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        private WorksheetPart CreateWorksheet(WorkbookPart workbookPart, string sheetName)
        {
            WorksheetPart newWorksheetPart = workbookPart.AddNewPart<WorksheetPart>();

            newWorksheetPart.Worksheet = new Worksheet(new SheetData());

            newWorksheetPart.Worksheet.Save();

            Sheets sheets = workbookPart.Workbook.GetFirstChild<Sheets>();
            if (sheets == null)
                sheets = workbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            string relationshipId = workbookPart.GetIdOfPart(newWorksheetPart);

            uint sheetId = ;

            if (sheets.Elements<Sheet>().Count() > )
            {//确定sheet的唯一编号
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + ;
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                sheetName = "Sheet" + sheetId;
            }

            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);

            workbookPart.Workbook.Save();

            return newWorksheetPart;
        }

        /// <summary>
        /// 创建sheet样式
        /// </summary>
        /// <param name="workbookPart"></param>
        /// <returns></returns>
        private Stylesheet CreateStylesheet(WorkbookPart workbookPart)
        {
            Stylesheet stylesheet = null;

            if (workbookPart.WorkbookStylesPart != null)
            {
                stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
                if (stylesheet != null)
                {
                    return stylesheet;
                }
            }
            workbookPart.AddNewPart<WorkbookStylesPart>("Style");
            workbookPart.WorkbookStylesPart.Stylesheet = new Stylesheet();
            stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;

            stylesheet.Fonts = new Fonts()
            {
                Count = (UInt32Value)3U
            };

            //fontId =0,默认样式
            Font fontDefault = new Font(
                                         new FontSize() { Val = 11D },
                                         new FontName() { Val = "Calibri" },
                                         new FontFamily() { Val =  },
                                         new FontScheme() { Val = FontSchemeValues.Minor });

            stylesheet.Fonts.Append(fontDefault);

            //fontId =1，标题样式
            Font fontTitle = new Font(new FontSize() { Val = 15D },
                                         new Bold() { Val = true },
                                         new FontName() { Val = "Calibri" },
                                         new FontFamily() { Val =  },
                                         new FontScheme() { Val = FontSchemeValues.Minor });
            stylesheet.Fonts.Append(fontTitle);

            //fontId =2，列头样式
            Font fontHeader = new Font(new FontSize() { Val = 13D },
                              new Bold() { Val = true },
                              new FontName() { Val = "Calibri" },
                              new FontFamily() { Val =  },
                              new FontScheme() { Val = FontSchemeValues.Minor });
            stylesheet.Fonts.Append(fontHeader);

            //fillId,0总是None，1总是gray125，自定义的从fillid =2开始
            stylesheet.Fills = new Fills()
            {
                Count = (UInt32Value)3U
            };

            //fillid=0
            Fill fillDefault = new Fill(new PatternFill() { PatternType = PatternValues.None });
            stylesheet.Fills.Append(fillDefault);

            //fillid=1
            Fill fillGray = new Fill();
            PatternFill patternFillGray = new PatternFill()
            {
                PatternType = PatternValues.Gray125
            };
            fillGray.Append(patternFillGray);
            stylesheet.Fills.Append(fillGray);

            //fillid=2
            Fill fillYellow = new Fill();
            PatternFill patternFillYellow = new PatternFill(new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } })
            {
                PatternType = PatternValues.Solid
            };
            fillYellow.Append(patternFillYellow);
            stylesheet.Fills.Append(fillYellow);

            stylesheet.Borders = new Borders()
            {
                Count = (UInt32Value)2U
            };

            //borderID=0
            Border borderDefault = new Border(new LeftBorder(), new RightBorder(), new TopBorder() { }, new BottomBorder(), new DiagonalBorder());
            stylesheet.Borders.Append(borderDefault);

            //borderID=1
            Border borderContent = new Border(
                new LeftBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                new RightBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                new TopBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                new BottomBorder(new Color() { Auto = true }) { Style = BorderStyleValues.Thin },
                new DiagonalBorder()
                );
            stylesheet.Borders.Append(borderContent);

            stylesheet.CellFormats = new CellFormats();
            stylesheet.CellFormats.Count = ;

            //styleIndex =0U
            CellFormat cfDefault = new CellFormat();
            cfDefault.Alignment = new Alignment();
            cfDefault.NumberFormatId = ;
            cfDefault.FontId = ;
            cfDefault.BorderId = ;
            cfDefault.FillId = ;
            cfDefault.ApplyAlignment = true;
            cfDefault.ApplyBorder = true;
            stylesheet.CellFormats.Append(cfDefault);

            //styleIndex =1U
            CellFormat cfTitle = new CellFormat();
            cfTitle.Alignment = new Alignment();
            cfTitle.NumberFormatId = ;
            cfTitle.FontId = ;
            cfTitle.BorderId = ;
            cfTitle.FillId = ;
            cfTitle.ApplyBorder = true;
            cfTitle.ApplyAlignment = true;
            cfTitle.Alignment.Horizontal = HorizontalAlignmentValues.Center;
            stylesheet.CellFormats.Append(cfTitle);

            //styleIndex =2U
            CellFormat cfHeader = new CellFormat();
            cfHeader.Alignment = new Alignment();
            cfHeader.NumberFormatId = ;
            cfHeader.FontId = ;
            cfHeader.BorderId = ;
            cfHeader.FillId = ;
            cfHeader.ApplyAlignment = true;
            cfHeader.ApplyBorder = true;
            cfHeader.ApplyFill = true;
            cfHeader.Alignment.Horizontal = HorizontalAlignmentValues.Center;
            stylesheet.CellFormats.Append(cfHeader);

            //styleIndex =3U
            CellFormat cfContent = new CellFormat();
            cfContent.Alignment = new Alignment();
            cfContent.NumberFormatId = ;
            cfContent.FontId = ;
            cfContent.BorderId = ;
            cfContent.FillId = ;
            cfContent.ApplyAlignment = true;
            cfContent.ApplyBorder = true;
            stylesheet.CellFormats.Append(cfContent);

            workbookPart.WorkbookStylesPart.Stylesheet.Save();
            return stylesheet;
        }

        /// <summary>
        /// 创建文本单元格,Cell的内容均视为文本
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="cellValue"></param>
        /// <param name="styleIndex"></param>
        /// <returns></returns>
        private Cell CreateTextCell(int columnIndex, int rowIndex, object cellValue, Nullable<uint> styleIndex)
        {
            Cell cell = new Cell();

            cell.DataType = CellValues.InlineString;

            cell.CellReference = GetCellReference(columnIndex) + rowIndex;

            if (styleIndex.HasValue)
                cell.StyleIndex = styleIndex.Value;

            InlineString inlineString = new InlineString();
            Text t = new Text();

            t.Text = cellValue.ToString();
            inlineString.AppendChild(t);
            cell.AppendChild(inlineString);

            return cell;
        }

        /// <summary>
        /// 创建值单元格，Cell会根据单元格值的类型
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="cellValue"></param>
        /// <param name="styleIndex"></param>
        /// <returns></returns>
        private Cell CreateValueCell(int columnIndex, int rowIndex, object cellValue, Nullable<uint> styleIndex)
        {
            Cell cell = new Cell();
            cell.CellReference = GetCellReference(columnIndex) + rowIndex;
            CellValue value = new CellValue();
            value.Text = cellValue.ToString();

            //apply the cell style if supplied
            if (styleIndex.HasValue)
                cell.StyleIndex = styleIndex.Value;

            cell.AppendChild(value);

            return cell;
        }

        /// <summary>
        /// 获取行引用，如A1
        /// </summary>
        /// <param name="colIndex"></param>
        /// <returns></returns>
        private string GetCellReference(int colIndex)
        {
            int dividend = colIndex;
            string columnName = String.Empty;
            int modifier;

            while (dividend > )
            {
                modifier = (dividend - ) % ;
                columnName =
                    Convert.ToChar( + modifier).ToString() + columnName;
                dividend = (int)((dividend - modifier) / );
            }
            return columnName;
        }

        /// <summary>
        /// 创建行数据,不同类型使用不同的styleIndex
        /// </summary>
        /// <param name="dataRow"></param>
        /// <param name="rowIndex"></param>
        /// <returns></returns>
        private Row CreateDataRow(DataRow dataRow, int rowIndex)
        {
            Row row = new Row
            {
                RowIndex = (UInt32)rowIndex
            };

            //Nullable<uint> styleIndex = null;
            double doubleValue;
            int intValue;
            DateTime dateValue;
            decimal decValue;

            for (int i = ; i < dataRow.Table.Columns.Count; i++)
            {
                Cell dataCell;
                if (DateTime.TryParse(dataRow[i].ToString(), out dateValue) && dataRow[i].GetType() == typeof(DateTime))
                {
                    dataCell = CreateTextCell(i + , rowIndex, dataRow[i], 3u);
                    //dataCell.DataType = CellValues.Date;
                }
                else if (decimal.TryParse(dataRow[i].ToString(), out decValue) && dataRow[i].GetType() == typeof(decimal))
                {
                    dataCell = CreateValueCell(i + , rowIndex, decValue, 3u);
                }
                else if (int.TryParse(dataRow[i].ToString(), out intValue) && dataRow[i].GetType() == typeof(int))
                {
                    dataCell = CreateValueCell(i + , rowIndex, intValue, 3u);
                }
                else if (Double.TryParse(dataRow[i].ToString(), out doubleValue) && dataRow[i].GetType() == typeof(double))
                {
                    dataCell = CreateValueCell(i + , rowIndex, doubleValue, 3u);
                }
                else
                {
                    dataCell = CreateTextCell(i + , rowIndex, dataRow[i], 3u);
                }

                row.AppendChild(dataCell);
                //styleIndex = null;
            }
            return row;
        }

        /// <summary>
        /// 将DataTable的列名称导入Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetData"></param>
        private void CreateTableHeader(DataTable dt, SheetData sheetData)
        {
            Row header = new Row
            {
                RowIndex = (UInt32)
            };
            int colCount = dt.Columns.Count;
            for(int i = ; i < colCount; i++)
            {
                string colName = dt.Columns[i].ColumnName;
                Cell dataCell = CreateTextCell( i + , , colName, 3u);
                header.AppendChild(dataCell);
            }
            //Row contentRow = CreateDataRow(header, 1);
            sheetData.AppendChild(header);
        }

        /// <summary>
        /// 将DataTable的数据导入Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetData"></param>
        private void InsertDataIntoSheet(DataTable dt, SheetData sheetData)
        {
            //SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild<SheetData>();

            //CreateTableHeader(dt, sheetData);

            for (int i = ; i < dt.Rows.Count; i++)
            {
                Row contentRow = CreateDataRow(dt.Rows[i], i + );
                sheetData.AppendChild(contentRow);
            }
            return;
        }

        /// <summary>
        /// 创建一个SharedStringTablePart(相当于各Sheet共用的存放字符串的容器)
        /// </summary>
        /// <param name="workbookPart"></param>
        /// <returns></returns>
        private SharedStringTablePart CreateSharedStringTablePart(WorkbookPart workbookPart)
        {
            SharedStringTablePart shareStringPart = null;
            if (workbookPart.GetPartsOfType<SharedStringTablePart>().Count() > )
            {
                shareStringPart = workbookPart.GetPartsOfType<SharedStringTablePart>().First();
            }
            else
            {
                shareStringPart = workbookPart.AddNewPart<SharedStringTablePart>();
            }
            return shareStringPart;
        }

        /// <summary>
        /// 导出Excel，执行函数
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filePath"></param>
        public void DataTableToExcel(DataTable dt, string filePath)
        {
            try
            {
                using (SpreadsheetDocument document = CreateParts(filePath))
                {
                    WorksheetPart worksheetPart = CreateWorksheet(document.WorkbookPart, dt.TableName);

                    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                    Stylesheet styleSheet = CreateStylesheet(document.WorkbookPart);

                    //InsertTableTitle(parameter.SheetName, sheetData, styleSheet);

                    // MergeTableTitleCells(dt.Columns.Count, worksheetPart.Worksheet);

                    CreateTableHeader(dt, sheetData);

                    InsertDataIntoSheet(dt, sheetData);

                    SharedStringTablePart sharestringTablePart = CreateSharedStringTablePart(document.WorkbookPart);
                    sharestringTablePart.SharedStringTable = new SharedStringTable();

                    sharestringTablePart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text("ExcelReader")));
                    sharestringTablePart.SharedStringTable.Save();
                }
                //result = 0;
            }
            catch (Exception ex)
            {
                //iSession.AddError(ex);
                //result = error_result_prefix - 99;
            }
            //return result;
        }

        #endregion
    }