C#读取Excel技术概览

1、 OleDb

用这种方法读取Excel速度还是非常的快的，但这种方式读取数据的时候不太灵活。不过可以在 DataTable 中对数据进行一些删减、修改。这种方式将Excel作为一个数据源，直接用Sql语句获取数据了。所以读取之前要知道此次要读取的Sheet（当然也可以用序号，类似dt.Row[0][0]。这样倒是不需要知道Sheet）.

if (fileType == ".xls")  
   connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";  
else 
   connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";  

OleDbConnection conn = new OleDbConnection(connStr);  
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

以上是读取Excel的Sheet名，xls和xlsx的连接字符串也不一样的，可以根据文件的后缀来区别。这里需要注意的一点，Excel里面只有一个Sheet，但通过这种方式读取Sheet可能会大于一个。

在使用过程中发现取出的Sheet和实际excel不一致，会多出不少。目前总结后有两种情况：

1. 取出的名称中，包括了XL命名管理器中的名称（参见XL2007的公式的命名管理器，【Crtl+F3】）；

2. 取出的名称中，包括了FilterDatabase后缀。这是XL用来记录Filter范围的；

对于第一点比较简单，删除已有命名管理器中的内容即可；第二点处理起来比较麻烦， Filter删除后这些名称依然保留着，简单的做法是新增sheet；然后将原sheet Copy进去。但实际情况并不能为每个Excel做以上检查。下面给出了过滤的方案，当时还是有点问题，本来补充了一点。总之先看代码吧：

for (int i = 0; i < dtSheetName.Rows.Count; i++)  
{
    SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"]; 
    if (SheetName .Contains("$") && !SheetName .Replace("'", "").EndsWith("$"))
    continue; //过滤无效SheetName完毕....
 
    da.SelectCommand = new OleDbCommand(String.Format(sql_F, tblName), conn);  
    DataSet dsItem = new DataSet();  
    da.Fill(dsItem, tblName);  
}

View Code

因为读取出来无效SheetName一般情况最后一个字符都不会是$。如果SheetName有一些特殊符号，读取出来的SheetName会自动加上单引号，比如在Excel中将SheetName编辑成：MySheet(1)，此时读取出来的SheetName就为：'MySheet(1)$'，所以判断最后一个字符是不是$之前最好过滤一下单引号。

优点：读取方式简单、读取速度快

缺点：除了读取过程不太灵活之外，这种读取方式还有个弊端就是，当Excel数据量很大时。会非常占用内存，当内存不够时会抛出内存溢出的异常。

不过一般情况下还是非常不错的，以下是完整代码。

        /// <summary>  
        /// 读取Excel文件到DataSet中  
        /// </summary>  
        /// <param name="filePath">文件路径</param>  
        /// <returns></returns>  
        public static DataSet ToDataTable(string filePath)  
        {  
            string connStr = "";             
            string fileType = System.IO.Path.GetExtension(fileName);  
            if (string.IsNullOrEmpty(fileType)) return null;  
   
            if (fileType == ".xls")  
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath+ ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";  
            else 
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath+ ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";  
            string sql_F = "Select * FROM [{0}]";  
   
            OleDbConnection conn = null;  
            OleDbDataAdapter da = null;  
            DataTable dtSheetName= null;  
   
            DataSet ds = new DataSet();  
            try 
            {  
                // 初始化连接，并打开  
                conn = new OleDbConnection(connStr);  
                conn.Open();  
   
                // 获取数据源的表定义元数据                         
                string SheetName = "";  
                dtSheetName= conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });  
   
                // 初始化适配器  
                da = new OleDbDataAdapter();  
                for (int i = 0; i < dtSheetName.Rows.Count; i++)  
                {  
                    SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];  
   
                    if (SheetName .Contains("$") && !SheetName .Replace("'", "").EndsWith("$"))  
                    {  
                        continue;  
                    }  
   
                    da.SelectCommand = new OleDbCommand(String.Format(sql_F, SheetName ), conn);  
                    DataSet dsItem = new DataSet();  
                    da.Fill(dsItem, tblName);  
   
                    ds.Tables.Add(dsItem.Tables[0].Copy());  
                }  
            }  
            catch (Exception ex)  
            {  
            }  
            finally 
            {  
                // 关闭连接  
                if (conn.State == ConnectionState.Open)  
                {  
                    conn.Close();  
                    da.Dispose();  
                    conn.Dispose();  
                }  
            }  
            return ds;  
        }

读取Excel完整代码

2、Com组件的方式读取Excel

这种方式需要先引用 Microsoft.Office.Interop.Excel 。首选说下这种方式的优缺点：

优点：可以非常灵活的读取Excel中的数据

缺点：如果是Web站点部署在IIS上时，还需要服务器机子已安装了Excel，有时候还需要为配置IIS权限。最重要的一点因为是基于单元格方式读取的，所以数据很慢（曾做过试验，直接读取千行、200多列的文件，直接读取耗时15分钟。即使采用多线程分段读取来提高CPU的利用率也需要8分钟。 PS：CPU I3）

需要读取大文件的的童鞋们慎重。。。

public class ExcelOptions  
    {  
        private Stopwatch wath = new Stopwatch();  
   
        /// <summary>  
        /// 使用COM读取Excel  
        /// </summary>  
        /// <param name="excelFilePath">路径</param>  
        /// <returns>DataTabel</returns>  
        public System.Data.DataTable GetExcelData(string excelFilePath)  
        {  
            Excel.Application app = new Excel.Application();  
            Excel.Sheets sheets;  
            Excel.Workbook workbook = null;  
            object oMissiong = System.Reflection.Missing.Value;  
            System.Data.DataTable dt = new System.Data.DataTable();  
   
            wath.Start();  
   
            try 
            {  
                if (app == null)  
                {  
                    return null;  
                }  
   
                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);  
   
                //将数据读入到DataTable中——Start    
   
                sheets = workbook.Worksheets;  
                Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//读取第一张表  
                if (worksheet == null)  
                    return null;  
   
                string cellContent;  
                int iRowCount = worksheet.UsedRange.Rows.Count;  
                int iColCount = worksheet.UsedRange.Columns.Count;  
                Excel.Range range;  
   
                //负责列头Start  
                DataColumn dc;  
                int ColumnID = 1;  
                range = (Excel.Range)worksheet.Cells[1, 1];  
                while (range.Text.ToString().Trim() != "")  
                {  
                    dc = new DataColumn();  
                    dc.DataType = System.Type.GetType("System.String");  
                    dc.ColumnName = range.Text.ToString().Trim();  
                    dt.Columns.Add(dc);  
   
                    range = (Excel.Range)worksheet.Cells[1, ++ColumnID];  
                }  
                //End  
   
                for (int iRow = 2; iRow <= iRowCount; iRow++)  
                {  
                    DataRow dr = dt.NewRow();  
   
                    for (int iCol = 1; iCol <= iColCount; iCol++)  
                    {  
                        range = (Excel.Range)worksheet.Cells[iRow, iCol];  
   
                        cellContent = (range.Value2 == null) ? "" : range.Text.ToString();  
   
                        //if (iRow == 1)  
                        //{  
                        //    dt.Columns.Add(cellContent);  
                        //}  
                        //else  
                        //{  
                            dr[iCol - 1] = cellContent;  
                        //}  
                    }  
   
                    //if (iRow != 1)  
                    dt.Rows.Add(dr);  
                }  
   
                wath.Stop();  
                TimeSpan ts = wath.Elapsed;  
   
                //将数据读入到DataTable中——End  
                return dt;  
            }  
            catch 
            {  
                   
                return null;  
            }  
            finally 
            {  
                workbook.Close(false, oMissiong, oMissiong);  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);  
                workbook = null;  
                app.Workbooks.Close();  
                app.Quit();  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);  
                app = null;  
                GC.Collect();  
                GC.WaitForPendingFinalizers();  
            }  
        }  
   
   
        /// <summary>  
        /// 使用COM，多线程读取Excel（1 主线程、4 副线程）  
        /// </summary>  
        /// <param name="excelFilePath">路径</param>  
        /// <returns>DataTabel</returns>  
        public System.Data.DataTable ThreadReadExcel(string excelFilePath)  
        {  
            Excel.Application app = new Excel.Application();  
            Excel.Sheets sheets = null;  
            Excel.Workbook workbook = null;  
            object oMissiong = System.Reflection.Missing.Value;  
            System.Data.DataTable dt = new System.Data.DataTable();  
   
            wath.Start();  
   
            try 
            {  
                if (app == null)  
                {  
                    return null;  
                }  
   
                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);  
   
                //将数据读入到DataTable中——Start    
                sheets = workbook.Worksheets;  
                Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//读取第一张表  
                if (worksheet == null)  
                    return null;  
   
                string cellContent;  
                int iRowCount = worksheet.UsedRange.Rows.Count;  
                int iColCount = worksheet.UsedRange.Columns.Count;  
                Excel.Range range;  
   
                //负责列头Start  
                DataColumn dc;  
                int ColumnID = 1;  
                range = (Excel.Range)worksheet.Cells[1, 1];  
                //while (range.Text.ToString().Trim() != "")  
                while (iColCount >= ColumnID)  
                {  
                    dc = new DataColumn();  
                    dc.DataType = System.Type.GetType("System.String");  
   
                    string strNewColumnName = range.Text.ToString().Trim();  
                    if (strNewColumnName.Length == 0) strNewColumnName = "_1";  
                    //判断列名是否重复  
                    for (int i = 1; i < ColumnID; i++)  
                    {  
                        if (dt.Columns[i - 1].ColumnName == strNewColumnName)  
                            strNewColumnName = strNewColumnName + "_1";  
                    }  
   
                    dc.ColumnName = strNewColumnName;  
                    dt.Columns.Add(dc);  
   
                    range = (Excel.Range)worksheet.Cells[1, ++ColumnID];  
                }  
                //End  
   
                //数据大于500条，使用多进程进行读取数据  
                if (iRowCount - 1 > 500)  
                {  
                    //开始多线程读取数据  
                    //新建线程  
                    int b2 = (iRowCount - 1) / 10;  
                    DataTable dt1 = new DataTable("dt1");  
                    dt1 = dt.Clone();  
                    SheetOptions sheet1thread = new SheetOptions(worksheet, iColCount, 2, b2 + 1, dt1);  
                    Thread othread1 = new Thread(new ThreadStart(sheet1thread.SheetToDataTable));  
                    othread1.Start();  
   
                    //阻塞 1 毫秒，保证第一个读取 dt1  
                    Thread.Sleep(1);  
   
                    DataTable dt2 = new DataTable("dt2");  
                    dt2 = dt.Clone();  
                    SheetOptions sheet2thread = new SheetOptions(worksheet, iColCount, b2 + 2, b2 * 2 + 1, dt2);  
                    Thread othread2 = new Thread(new ThreadStart(sheet2thread.SheetToDataTable));  
                    othread2.Start();  
   
                    DataTable dt3 = new DataTable("dt3");  
                    dt3 = dt.Clone();  
                    SheetOptions sheet3thread = new SheetOptions(worksheet, iColCount, b2 * 2 + 2, b2 * 3 + 1, dt3);  
                    Thread othread3 = new Thread(new ThreadStart(sheet3thread.SheetToDataTable));  
                    othread3.Start();  
   
                    DataTable dt4 = new DataTable("dt4");  
                    dt4 = dt.Clone();  
                    SheetOptions sheet4thread = new SheetOptions(worksheet, iColCount, b2 * 3 + 2, b2 * 4 + 1, dt4);  
                    Thread othread4 = new Thread(new ThreadStart(sheet4thread.SheetToDataTable));  
                    othread4.Start();  
   
                    //主线程读取剩余数据  
                    for (int iRow = b2 * 4 + 2; iRow <= iRowCount; iRow++)  
                    {  
                        DataRow dr = dt.NewRow();  
                        for (int iCol = 1; iCol <= iColCount; iCol++)  
                        {  
                            range = (Excel.Range)worksheet.Cells[iRow, iCol];  
                            cellContent = (range.Value2 == null) ? "" : range.Text.ToString();  
                            dr[iCol - 1] = cellContent;  
                        }  
                        dt.Rows.Add(dr);  
                    }  
   
                    othread1.Join();  
                    othread2.Join();  
                    othread3.Join();  
                    othread4.Join();  
   
                    //将多个线程读取出来的数据追加至 dt1 后面  
                    foreach (DataRow dr in dt.Rows)  
                        dt1.Rows.Add(dr.ItemArray);  
                    dt.Clear();  
                    dt.Dispose();  
   
                    foreach (DataRow dr in dt2.Rows)  
                        dt1.Rows.Add(dr.ItemArray);  
                    dt2.Clear();  
                    dt2.Dispose();  
   
                    foreach (DataRow dr in dt3.Rows)  
                        dt1.Rows.Add(dr.ItemArray);  
                    dt3.Clear();  
                    dt3.Dispose();  
   
                    foreach (DataRow dr in dt4.Rows)  
                        dt1.Rows.Add(dr.ItemArray);  
                    dt4.Clear();  
                    dt4.Dispose();  
   
                    return dt1;  
                }  
                else 
                {  
                    for (int iRow = 2; iRow <= iRowCount; iRow++)  
                    {  
                        DataRow dr = dt.NewRow();  
                        for (int iCol = 1; iCol <= iColCount; iCol++)  
                        {  
                            range = (Excel.Range)worksheet.Cells[iRow, iCol];  
                            cellContent = (range.Value2 == null) ? "" : range.Text.ToString();  
                            dr[iCol - 1] = cellContent;  
                        }  
                        dt.Rows.Add(dr);  
                    }  
                }  
   
                wath.Stop();  
                TimeSpan ts = wath.Elapsed;  
                //将数据读入到DataTable中——End  
                return dt;  
            }  
            catch 
            {  
   
                return null;  
            }  
            finally 
            {  
                workbook.Close(false, oMissiong, oMissiong);  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);  
                workbook = null;  
                app.Workbooks.Close();  
                app.Quit();  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);  
                app = null;  
                GC.Collect();  
                GC.WaitForPendingFinalizers();  
                   
                /*  
                object objmissing = System.Reflection.Missing.Value;  
   
Excel.ApplicationClass application = new ApplicationClass();  
Excel.Workbook book = application.Workbooks.Add(objmissing);  
Excel.Worksheet sheet = (Excel.Worksheet)book.Worksheets.Add（objmissing,objmissing,objmissing,objmissing);  
   
//操作过程 ^&%&×&……&%&&……  
   
//释放  
sheet.SaveAs(path,objmissing,objmissing,objmissing,objmissing,objmissing,objmissing,objmissing,objmissing);  
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet);  
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)book);  
application.Quit();  
System.Runtime.InteropServices.Marshal.ReleaseComObject((object)application);  
System.GC.Collect();  
                 */ 
            }  
        }  
   
   
        /// <summary>  
        /// 删除Excel行  
        /// </summary>  
        /// <param name="excelFilePath">Excel路径</param>  
        /// <param name="rowStart">开始行</param>  
        /// <param name="rowEnd">结束行</param>  
        /// <param name="designationRow">指定行</param>  
        /// <returns></returns>  
        public string DeleteRows(string excelFilePath, int rowStart, int rowEnd, int designationRow)  
        {  
            string result = "";  
            Excel.Application app = new Excel.Application();  
            Excel.Sheets sheets;  
            Excel.Workbook workbook = null;  
            object oMissiong = System.Reflection.Missing.Value;  
            try 
            {  
                if (app == null)  
                {  
                    return "分段读取Excel失败";  
                }  
   
                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);  
                sheets = workbook.Worksheets;  
                Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//读取第一张表  
                if (worksheet == null)  
                    return result;  
                Excel.Range range;  
   
                //先删除指定行，一般为列描述  
                if (designationRow != -1)  
                {  
                    range = (Excel.Range)worksheet.Rows[designationRow, oMissiong];  
                    range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);  
                }  
                Stopwatch sw = new Stopwatch();  
                sw.Start();  
   
                int i = rowStart;  
                for (int iRow = rowStart; iRow <= rowEnd; iRow++, i++)  
                {  
                    range = (Excel.Range)worksheet.Rows[rowStart, oMissiong];  
                    range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);  
                }  
   
                sw.Stop();  
                TimeSpan ts = sw.Elapsed;  
                workbook.Save();  
   
                //将数据读入到DataTable中——End  
                return result;  
            }  
            catch 
            {  
   
                return "分段读取Excel失败";  
            }  
            finally 
            {  
                workbook.Close(false, oMissiong, oMissiong);  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);  
                workbook = null;  
                app.Workbooks.Close();  
                app.Quit();  
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);  
                app = null;  
                GC.Collect();  
                GC.WaitForPendingFinalizers();  
            }  
        }  
   
        public void ToExcelSheet(DataSet ds, string fileName)  
        {  
            Excel.Application appExcel = new Excel.Application();  
            Excel.Workbook workbookData = null;  
            Excel.Worksheet worksheetData;  
            Excel.Range range;  
            try 
            {  
                workbookData = appExcel.Workbooks.Add(System.Reflection.Missing.Value);  
                appExcel.DisplayAlerts = false;//不显示警告  
                //xlApp.Visible = true;//excel是否可见  
                //  
                //for (int i = workbookData.Worksheets.Count; i > 0; i--)  
                //{  
                //    Microsoft.Office.Interop.Excel.Worksheet oWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.get_Item(i);  
                //    oWorksheet.Select();  
                //    oWorksheet.Delete();  
                //}  
   
                for (int k = 0; k < ds.Tables.Count; k++)  
                {  
                    worksheetData = (Excel.Worksheet)workbookData.Worksheets.Add(System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);  
                    // testnum--;  
                    if (ds.Tables[k] != null)  
                    {  
                        worksheetData.Name = ds.Tables[k].TableName;  
                        //写入标题  
                        for (int i = 0; i < ds.Tables[k].Columns.Count; i++)  
                        {  
                            worksheetData.Cells[1, i + 1] = ds.Tables[k].Columns[i].ColumnName;  
                            range = (Excel.Range)worksheetData.Cells[1, i + 1];  
                            //range.Interior.ColorIndex = 15;  
                            range.Font.Bold = true;  
                            range.NumberFormatLocal = "@";//文本格式  
                            range.EntireColumn.AutoFit();//自动调整列宽  
                            // range.WrapText = true; //文本自动换行    
                            range.ColumnWidth = 15;  
                        }  
                        //写入数值  
                        for (int r = 0; r < ds.Tables[k].Rows.Count; r++)  
                        {  
                            for (int i = 0; i < ds.Tables[k].Columns.Count; i++)  
                            {  
                                worksheetData.Cells[r + 2, i + 1] = ds.Tables[k].Rows[r][i];  
                                //Range myrange = worksheetData.get_Range(worksheetData.Cells[r + 2, i + 1], worksheetData.Cells[r + 3, i + 2]);  
                                //myrange.NumberFormatLocal = "@";//文本格式  
                                //// myrange.EntireColumn.AutoFit();//自动调整列宽  
                                ////   myrange.WrapText = true; //文本自动换行    
                                //myrange.ColumnWidth = 15;  
                            }  
                            //  rowRead++;  
                            //System.Windows.Forms.Application.DoEvents();  
                        }  
                    }  
                    worksheetData.Columns.EntireColumn.AutoFit();  
                    workbookData.Saved = true;  
                }  
            }  
            catch (Exception ex) { }  
            finally 
            {  
                workbookData.SaveCopyAs(fileName);  
                workbookData.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);  
                appExcel.Quit();  
                GC.Collect();  
            }  
        }  
       
    }

单线程和多线程读取类

3、NPOI方式读取Excel

NPOI是一组开源的组件，类似Java的 POI。包括：NPOI、NPOI.HPSF、NPOI.HSSF、NPOI.HSSF.UserModel、NPOI.POIFS、NPOI.Util，下载的时候别只下一个噢

优点：读取Excel速度较快，读取方式操作灵活性

缺点：只支持03的Excel，xlsx的无法读取。由于这点，使用这种方式的人不多啊，没理由要求客户使用03版Excel吧，再说03版Excel对于行数还有限制，只支持65536行。

（听他们的开发人员说会在2012年底推出新版，支持xlsx的读取。但一直很忙没时间去关注这个事情，有兴趣的同学可以瞧瞧去）

using System;  
using System.Data;  
using System.IO;  
using System.Web;  
using NPOI;  
using NPOI.HPSF;  
using NPOI.HSSF;  
using NPOI.HSSF.UserModel;  
using NPOI.POIFS;  
using NPOI.Util;  
using System.Text;  
using System.Configuration;  
   
public class NPOIHelper  
{  
    private static int ExcelMaxRow = Convert.ToInt32(ConfigurationManager.AppSettings["ExcelMaxRow"]);  
    /// <summary>  
    /// 由DataSet导出Excel  
    /// </summary>  
    /// <param name="sourceTable">要导出数据的DataTable</param>     
    /// <param name="sheetName">工作表名称</param>  
    /// <returns>Excel工作表</returns>     
    private static Stream ExportDataSetToExcel(DataSet sourceDs)  
    {  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        MemoryStream ms = new MemoryStream();  
   
        for (int i = 0; i < sourceDs.Tables.Count; i++)  
        {  
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sourceDs.Tables[i].TableName);  
            HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);  
            // handling header.             
            foreach (DataColumn column in sourceDs.Tables[i].Columns)  
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);  
            // handling value.             
            int rowIndex = 1;  
            foreach (DataRow row in sourceDs.Tables[i].Rows)  
            {  
                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);  
                foreach (DataColumn column in sourceDs.Tables[i].Columns)  
                {  
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());  
                }  
                rowIndex++;  
            }  
        }  
        workbook.Write(ms);  
        ms.Flush();  
        ms.Position = 0;  
        workbook = null;  
        return ms;  
    }  
    /// <summary>  
    /// 由DataSet导出Excel  
    /// </summary>    
    /// <param name="sourceTable">要导出数据的DataTable</param>  
    /// <param name="fileName">指定Excel工作表名称</param>  
    /// <returns>Excel工作表</returns>     
    public static void ExportDataSetToExcel(DataSet sourceDs, string fileName)  
    {  
        //检查是否有Table数量超过65325  
        for (int t = 0; t < sourceDs.Tables.Count; t++)  
        {  
            if (sourceDs.Tables[t].Rows.Count > ExcelMaxRow)  
            {  
                DataSet ds = GetdtGroup(sourceDs.Tables[t].Copy());  
                sourceDs.Tables.RemoveAt(t);  
                //将得到的ds插入 sourceDs中  
                for (int g = 0; g < ds.Tables.Count; g++)  
                {  
                    DataTable dt = ds.Tables[g].Copy();  
                    sourceDs.Tables.Add(dt);  
                }  
                t--;  
            }  
        }  
   
        MemoryStream ms = ExportDataSetToExcel(sourceDs) as MemoryStream;  
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);  
        HttpContext.Current.Response.BinaryWrite(ms.ToArray());  
        HttpContext.Current.ApplicationInstance.CompleteRequest();  
        //HttpContext.Current.Response.End();  
        ms.Close();  
        ms = null;  
    }  
    /// <summary>  
    /// 由DataTable导出Excel  
    /// </summary>  
    /// <param name="sourceTable">要导出数据的DataTable</param>  
    /// <returns>Excel工作表</returns>     
    private static Stream ExportDataTableToExcel(DataTable sourceTable)  
    {  
        HSSFWorkbook workbook = new HSSFWorkbook();  
        MemoryStream ms = new MemoryStream();  
        HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sourceTable.TableName);  
        HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);  
        // handling header.       
        foreach (DataColumn column in sourceTable.Columns)  
            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);  
        // handling value.       
        int rowIndex = 1;  
        foreach (DataRow row in sourceTable.Rows)  
        {  
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);  
            foreach (DataColumn column in sourceTable.Columns)  
            {  
                dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());  
            }  
            rowIndex++;  
        }  
        workbook.Write(ms);  
        ms.Flush();  
        ms.Position = 0;  
        sheet = null;  
        headerRow = null;  
        workbook = null;  
        return ms;  
    }  
    /// <summary>  
    /// 由DataTable导出Excel  
    /// </summary>  
    /// <param name="sourceTable">要导出数据的DataTable</param>  
    /// <param name="fileName">指定Excel工作表名称</param>  
    /// <returns>Excel工作表</returns>  
    public static void ExportDataTableToExcel(DataTable sourceTable, string fileName)  
    {  
        //如数据超过65325则分成多个Table导出  
        if (sourceTable.Rows.Count > ExcelMaxRow)  
        {  
            DataSet ds = GetdtGroup(sourceTable);  
            //导出DataSet  
            ExportDataSetToExcel(ds, fileName);  
        }  
        else 
        {  
            MemoryStream ms = ExportDataTableToExcel(sourceTable) as MemoryStream;  
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);  
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());  
            HttpContext.Current.ApplicationInstance.CompleteRequest();  
            //HttpContext.Current.Response.End();  
            ms.Close();  
            ms = null;  
        }  
    }  
   
    /// <summary>  
    /// 传入行数超过65325的Table，返回DataSet  
    /// </summary>  
    /// <param name="dt"></param>  
    /// <returns></returns>  
    public static DataSet GetdtGroup(DataTable dt)  
    {  
        string tablename = dt.TableName;  
   
        DataSet ds = new DataSet();  
        ds.Tables.Add(dt);  
   
        double n = dt.Rows.Count / Convert.ToDouble(ExcelMaxRow);  
   
        //创建表  
        for (int i = 1; i < n; i++)  
        {  
            DataTable dtAdd = dt.Clone();  
            dtAdd.TableName = tablename + "_" + i.ToString();  
            ds.Tables.Add(dtAdd);  
        }  
   
        //分解数据  
        for (int i = 1; i < ds.Tables.Count; i++)  
        {  
            //新表行数达到最大 或 基表数量不足  
            while (ds.Tables[i].Rows.Count != ExcelMaxRow && ds.Tables[0].Rows.Count != ExcelMaxRow)  
            {  
                ds.Tables[i].Rows.Add(ds.Tables[0].Rows[ExcelMaxRow].ItemArray);  
                ds.Tables[0].Rows.RemoveAt(ExcelMaxRow);  
   
            }  
        }  
   
        return ds;  
    }  
   
    /// <summary>  
    /// 由DataTable导出Excel  
    /// </summary>  
    /// <param name="sourceTable">要导出数据的DataTable</param>  
    /// <param name="fileName">指定Excel工作表名称</param>  
    /// <returns>Excel工作表</returns>  
    public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName)  
    {  
        int rowIndex = 2;//从第二行开始，因为前两行是模板里面的内容  
        int colIndex = 0;  
        FileStream file = new FileStream(modelpath + modelName + ".xls", FileMode.Open, FileAccess.Read);//读入excel模板  
        HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);  
        HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");  
        sheet1.GetRow(0).GetCell(0).SetCellValue("excelTitle");      //设置表头  
        foreach (DataRow row in sourceTable.Rows)  
        {   //双循环写入sourceTable中的数据  
            rowIndex++;  
            colIndex = 0;  
            HSSFRow xlsrow = (HSSFRow)sheet1.CreateRow(rowIndex);  
            foreach (DataColumn col in sourceTable.Columns)  
            {  
                xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString());  
                colIndex++;  
            }  
        }  
        sheet1.ForceFormulaRecalculation = true;  
        FileStream fileS = new FileStream(modelpath + fileName + ".xls", FileMode.Create);//保存  
        hssfworkbook.Write(fileS);  
        fileS.Close();  
        file.Close();  
    }  
}

NPOI读取Excel类

4、一种最新的方式来处理Excel ---- open xml

这种方式在Msdn上有详细的解释，可参考msdn sdk。这种方式只能处理2007以上的版本，不支持2003。

项目中发现使用OleDb(using System.Data.OleDb)相关对象处理Excel导入功能，不是很稳定经常出问题，需要把这个问题解决掉。项目组提出使用OpenXML来处理Excel的导入、导出问题，出于兴趣对OpenXML了解一下，做了简单Demo。

4.1 Open XML准备

使用Open XML操作Excel需要安装Open XML Format SDK 2.0及其以上版本，其相关对象和接口建立在Open XML SDK CTP 2基础上的，使用前先下载Open XML Format SDK 2.0及其以上版本。SDK默认会安装在C:\Program Files (x86)\Open XML Format SDK\V2.0 (64bit)目录下，lib子目录下的DocumentFormat.OpenXml.dll必须被引用到项目中。Open XML支持Office 2007及其以上版本，Open XML好像升级到2.5版本了，对于Open XML 2.0和2.5其对象和API接口有所不同，请查阅相关文档。把这个小Demo整出来，花了一些时间，主要是对其中的相关对象和API接口使用的不了解。

4.2 简单Excel zip包介绍

大家应该知道Office 2007都是一些XML文件的压缩包，可以创建一个Office 2007的Excel文件，简单录入几条数据，保存一下。复制一下，做个副本，修改其后缀为zip格式，这样就可以看到Excel的一些相关文件。因需要测试功能，做了简单的Office 2007的文件，修改为zip解压查看相关文件如下图：

其中需要注意的几个文件styles.xml、sharedStrings.xml、workbook.xml、worksheets中各个sheet。

styles.xml:主要用来存放Excel中样式的(包括格式化的数据格式，如日期、货币等等)；

sharedStrings.xml:主要存放共享的String数据的，在没有对Excel中相关单元格和数据格式化都可以通过这个文件读取；

workbook.xml:主要存放工作簿中各个工作表的命名和命名空间，在获取各个工作表的名称可以通过寻址找到节点，获取各表名称；

worksheets中各个sheet:主要存放各个工作表的相关数据库可以通过下面这个图了解各个对象的关系，这个是Open XML 2.5开发的相关对象(http://msdn.microsoft.com/zh-cn/library/office/gg278316.aspx)：

4.3 简单功能介绍

使用OpenXML操作Excel，将Excel中的数据正确读取出来，保持到数据库。但是碰到一些问题，就是如何读取格式化的数据，把日期、时间、货币进行数据格式化，就不能正确的读取数据，由于不是很了解，花了些时间，在网上查了查相关资料解决了一下。估计不是最优解，如果对这方面了解的大牛，希望能指导一下，提供一些更好的方法。

这里测试了两块，一块把Excel的所有数据按Excel定义的格式转换成DataTable和DataSet；另一块把Excel中数据对照相关数据库实体对象，使用反射进行实体属性赋值，转换失败，则Excel中的数据就有问题。

一块是将Excel的数据全部搬到DataTable或DataSet,不考虑这些数据是来自数据库的几个表，如果业务需要可以对这个DataTable或DataSet操作；

另一块是进行数据库实体对象校验，必须Excel中单元格的数据格式和数据库中字段的存储格式一致，当然也可以根据业务的需要继续添加各种验证，你可以继续丰富、优化代码。

稍加改进了一下，可以支持泛型对象的转换，可以将符合规格的Excel的数据转换成对应的实体对象，即可以映射任何实体对象。那么就可以根据需要转换成DataTable或DataSet，或者对多表数据的转换，可以在此基础上优化，希望对你有帮助。

4.4 简单实现介绍

实现就是建一个WinForm程序，一个导入按钮，一个DataView呈现数据，一个OpenFileDialog选择文件。两个辅助解析Excel的类，ExcelOper和ExcelOperMatch，一个是不进行校验之间转化为DataTable\DataSet的；一个是需要数据校验的，其中 ExcelOperMatch调用ExcelOper写好的两个方法：GetWorkBookPartRows(获取WorkBookPart中所有的行数据)和GetCellValue(获取单元格的值)。其中对于格式化样式不太好处理,测试数据2的样式：

4.5 简单实现效果

1).测试数据1

2).实现数据1

3).测试数据2

4).实现数据2

5).测试数据3

6).实现数据3

4.6 示例Demo代码：

1) ExcelOper.cs文件代码

(注意DLL和命名空间的引入：using DocumentFormat.OpenXml.Packaging和using DocumentFormat.OpenXml.Spreadsheet和using System.Diagnostics;)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Xml;
using System.Diagnostics;
using DocumentFormat.OpenXml;
using System.Reflection;

namespace OpenXMLTest
{
    /// <summary>
    /// 思考问题：
    /// 1.对于Excel中所有不进行任何验证,直接转化为Table(有列头和无列头)
    /// 2.对于Excel中数据匹配某一指定表的列头及其数据(有列头)
    /// 3.对于Excel中数据不是处理在一张表中(有列头和无列头)
    /// 4.对于Excel中数据多表处理和单表处理
    /// 5.对于Excel中一个Sheet的数据来自多张数据库表
    /// </summary>
    public class ExcelOper
    {
        /// <summary>
        /// 将DataTable转化为XML输出
        /// </summary>
        /// <param name="dataTable">DataTable</param>
        /// <param name="fileName">文件名称</param>
        public void DataTableToXML(DataTable dataTable, string fileName)
        {
            //指定程序安装目录
            string filePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + fileName;
            using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write))
            {
                using (XmlWriter xmlWriter = XmlWriter.Create(fs))
                {
                    dataTable.WriteXml(xmlWriter, XmlWriteMode.IgnoreSchema);
                }
            }
            Process.Start(filePath);
        }

        /// <summary>
        /// 将Excel多单一表转化为DataSet数据集对象
        /// </summary>
        /// <param name="filePath">Excel文件路径</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">SheetName</param>
        /// <param name="stream">Excel文件路径</param>
        /// <returns>DataTable对象</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>
        /// 根据WorkbookPart获取所有SheetName
        /// </summary>
        /// <param name="workBookPart"></param>
        /// <returns>SheetName集合</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>
        /// 根据WorkbookPart和sheetName获取该Sheet下所有Row数据
        /// </summary>
        /// <param name="workBookPart">WorkbookPart对象</param>
        /// <param name="sheetName">SheetName</param>
        /// <returns>该SheetName下的所有Row数据</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() == 0)
            {
                return null;//没有数据
            }

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

            return sheetRows;
        }

        /// <summary>
        /// 根据WorkbookPart和表名创建DataTable对象
        /// </summary>
        /// <param name="workBookPart">WorkbookPart对象</param>
        /// <param name="tableName">表名</param>
        /// <returns>转化后的DataTable</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() <= 0)
            {
                return null;
            }

            //将数据导入DataTable,假定第一行为列名,第二行以后为数据
            foreach (Row row in sheetRows)
            {
                //获取Excel中的列头
                if (row.RowIndex == 1)
                {
                    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>
        /// 根据WorkbookPart获取NumberingFormats样式集合
        /// </summary>
        /// <param name="workBookPart">WorkbookPart对象</param>
        /// <returns>NumberingFormats样式集合</returns>
        private List<string> GetNumberFormatsStyle(WorkbookPart workBookPart)
        {
            List<string> dicStyle = new List<string>();
            Stylesheet styleSheet = workBookPart.WorkbookStylesPart.Stylesheet;
            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 > 0)
                            {
                                string numFmtId = reader.Attributes[0].Value;//格式化ID
                                string formatCode = reader.Attributes[1].Value;//格式化Code
                                dicStyle.Add(formatCode);//将格式化Code写入List集合
                            }
                        }
                    }
                }
            }
            return dicStyle;
        }

        /// <summary>
        /// 根据行对象和WorkbookPart对象获取DataColumn集合
        /// </summary>
        /// <param name="row">Excel中行记录</param>
        /// <param name="workBookPart">WorkbookPart对象</param>
        /// <returns>返回DataColumn对象集合</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>
        /// 根据Excel行\数据库表\WorkbookPart对象获取数据DataRow
        /// </summary>
        /// <param name="row">Excel中行对象</param>
        /// <param name="dateTable">数据表</param>
        /// <param name="workBookPart">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 = 0;//单元格索引
            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>
        /// 根据Excel单元格和WorkbookPart对象获取单元格的值
        /// </summary>
        /// <param name="cell">Excel单元格对象</param>
        /// <param name="workBookPart">Excel WorkbookPart对象</param>
        /// <returns>单元格的值</returns>
        public string GetCellValue(Cell cell, WorkbookPart workBookPart)
        {
            string cellValue = string.Empty;
            if (cell.ChildElements.Count == 0)//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 == "1") ? "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//格式化数据
                {
                    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();
                        }
                    }
                }
            }
            catch (Exception exp)
            {
                //string expMessage = string.Format("Excel中{0}位置数据有误,请确认填写正确！", cellRefId);
                //throw new Exception(expMessage);
                cellValue = "N/A";
            }
            return cellValue;
        }

        /// <summary>
        /// 获取Excel中多表的表名
        /// </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) == 0).First();
        }
        private static Row ReturnRow(Worksheet worksheet, uint row)
        {
            return worksheet.GetFirstChild<SheetData>().
            Elements<Row>().Where(r => r.RowIndex == row).First();
        }
        #endregion
    }
}

ExcelOper

2) ExcelOperMatch.cs代码

(注意命名空间的引入：using System.Reflection;[稍作改进添加反射实体泛型支持方法,这样就可以将符合规则的Excel数据转换成对应的数据表])

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Reflection;
using System.Globalization;
                
namespace OpenXMLTest
{
    public class ExcelOperMatch
    {
        /// <summary>
        /// 根据SheetName和文件路径转换实体对象
        /// 将Excel中的数据映射实体对象集合
        /// </summary>
        /// <param name="sheetName">操作SheetName</param>
        /// <param name="filePath">文件路径</param>
        /// <returns>实体对象集合</returns>
        public  List<TestPerson> ExcelToPersons(string sheetName, string filePath)
        {
            List<TestPerson> listTestPersons = new List<TestPerson>();
            try
            {
                using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))//Excel文档包
                {
                    WorkbookPart workBookPart = spreadDocument.WorkbookPart;
                    listTestPersons = ExtcelToObjects(workBookPart, sheetName);
                }
            }
            catch(Exception exp)
            {
               // throw new Exception("可能Excel正在打开中,请关闭重新操作！");
            }
            return listTestPersons;
        }

        /// <summary>
        /// 根据WorkbookPart和SheetName获取实体对象集合
        /// </summary>
        /// <param name="workBookPart">WorkbookPart对象</param>
        /// <param name="sheetName">sheetName</param>
        /// <returns>实体对象集合</returns>
        private List<TestPerson> ExtcelToObjects(WorkbookPart workBookPart, string sheetName)
        {
            List<TestPerson> listPersons = new List<TestPerson>();
            List<string> columnValues = new List<string>();//列头值集合
            List<string> rowCellValues = null;//行数据集合

            //获取WorkbookPart下名为sheetName的Sheet的所有行数据
            IEnumerable<Row> sheetRows = new ExcelOper().GetWorkBookPartRows(workBookPart, sheetName);
            if (sheetRows == null || sheetRows.Count() <= 0)
            {
                return null;
            }

            //将数据导入DataTable,假定第一行为列名,第二行以后为数据
            foreach (Row row in sheetRows)
            {
                rowCellValues = new List<string>();//新行数据
                foreach (Cell cell in row)
                {
                    //获取单元格的值
                    string cellValue = new ExcelOper().GetCellValue(cell, workBookPart);
                    if (row.RowIndex == 1)
                    {
                        columnValues.Add(cellValue);
                    }
                    else
                    {
                        rowCellValues.Add(cellValue);
                    }
                }
                if (row.RowIndex > 1)
                {
                    int rowIndex = Convert.ToInt32(row.RowIndex.ToString());

                    //使用强类型处理
                    TestPerson singlePerson = ConvertToTestPerson(rowIndex, columnValues, rowCellValues);
                    
                    //使用泛型处理,可以转换任意实体
                    //TestPerson singlePerson = ConvertToObject<TestPerson>(rowIndex, columnValues, rowCellValues);
                   
                    listPersons.Add(singlePerson);
                }
            }
            return listPersons;
        }

        /// <summary>
        /// 根据行号\列集合\行集合转换实体对象
        /// </summary>
        /// <param name="rowIndex">行索引</param>
        /// <param name="columnValues">列头集合</param>
        /// <param name="rowCellValues">一行数据集合</param>
        /// <returns>映射的实体对象</returns>
        private TestPerson ConvertToTestPerson(int rowIndex, List<string> columnValues, List<string> rowCellValues)
        {
            TestPerson singlePerson = new TestPerson();//TestPerson对象
            foreach (PropertyInfo pi in singlePerson.GetType().GetProperties())
            {
                for (int index = 0; index < columnValues.Count; index++)
                {
                    try
                    {
                        if (pi.Name.Equals(columnValues[index], StringComparison.OrdinalIgnoreCase))
                        {
                            String propertyType = pi.PropertyType.Name;
                            switch (propertyType)
                            {
                                case "Int32":
                                    pi.SetValue(singlePerson, int.Parse(rowCellValues[index]), null);
                                    break;
                                case "DateTime":
                                    pi.SetValue(singlePerson, DateTime.Parse(rowCellValues[index]), null);
                                    break;
                                case "Decimal":
                                    pi.SetValue(singlePerson, Decimal.Parse(rowCellValues[index]), null);
                                    break;
                                case "Double":
                                    pi.SetValue(singlePerson, Double.Parse(rowCellValues[index]), null);
                                    break;
                                case "String":
                                    pi.SetValue(singlePerson, rowCellValues[index], null);
                                    break;
                                case "Boolean":
                                    pi.SetValue(singlePerson, Boolean.Parse(rowCellValues[index]), null);
                                    break;
                            }
                            break;
                        }
                    }
                    catch (Exception exp)
                    {
                        index = (index - 1) % 26;
                        string cellRef = Convert.ToChar(65 + index).ToString() +rowIndex;
                        string expMessage = string.Format("请确认Excel中{0}位置数据填写正确！", cellRef);
                        throw new Exception(expMessage);
                    }
                }
            }
            return singlePerson;
        }

        /// <summary>
        /// 使用泛型,这样可以针对任何实体对象进行映射参照
        /// </summary>
        /// <typeparam name="T">映射实体对象</typeparam>
        /// <param name="rowIndex">行号</param>
        /// <param name="columnValues">列集合</param>
        /// <param name="rowCellValues">行单元格集合</param>
        /// <returns>实体对象</returns>
        private T ConvertToObject<T>(int rowIndex, List<string> columnValues, List<string> rowCellValues)
        {
            T singleT = Activator.CreateInstance<T>();//创建实体对象T
            foreach (PropertyInfo pi in singleT.GetType().GetProperties())
            {
                for (int index = 0; index < columnValues.Count; index++)
                {
                    try
                    {
                        if (pi.Name.Equals(columnValues[index], StringComparison.OrdinalIgnoreCase))
                        {
                            String propertyType = pi.PropertyType.Name;
                            switch (propertyType)
                            {
                                case "Int32":
                                    pi.SetValue(singleT, int.Parse(rowCellValues[index]), null);
                                    break;
                                case "DateTime":
                                    pi.SetValue(singleT, DateTime.Parse(rowCellValues[index]), null);
                                    break;
                                case "Decimal":
                                    pi.SetValue(singleT, Decimal.Parse(rowCellValues[index]), null);
                                    break;
                                case "Double":
                                    pi.SetValue(singleT, Double.Parse(rowCellValues[index]), null);
                                    break;
                                case "String":
                                    pi.SetValue(singleT, rowCellValues[index], null);
                                    break;
                                case "Boolean":
                                    pi.SetValue(singleT, Boolean.Parse(rowCellValues[index]), null);
                                    break;
                            }
                            break;
                        }
                    }
                    catch (Exception exp)
                    {
                        index = (index - 1) % 26;
                        string cellRef = Convert.ToChar(65 + index).ToString() + rowIndex;
                        string expMessage = string.Format("请确认Excel中{0}位置数据填写正确！", cellRef);
                        throw new Exception(expMessage);

                        //singleT = default(T);
                    }
                }
            }
            return singleT;
        }

    }

    /// <summary>
    /// 辅助测试类
    /// </summary>
    public class TestPerson
    {
        #region 公共属性
        //int类型测试
        public int PersonID { get; set; }
        //bool类型测试
        public bool PersonSex { get; set; }
        //string类型测试
        public string PersonName { get; set; }
        //DateTime 日期测试
        public DateTime PersonBirth { get; set; }
        //DateTime 时间测试
        public DateTime PersonLogTime { get; set; }
        //decimal类型测试
        public decimal PersonAmountMoney { get; set; }
        #endregion
    }
}

ExcelOperMatch

3) WinForm的Button事件代码

private void btnOperExcel_Click(object sender, EventArgs e)
        {
            openFileDialog1.Filter = "Text Documents (*.xlsx)|*.xlsx|All Files|*.*";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                string filePath = openFileDialog1.FileName;

                ExcelOper excelOperation = new ExcelOper();

                //DataTable dataTable = excelOperation.ExcelToDataTable("Sheet1", filePath);
                //excelOperation.DataTableToXML(dataTable,"Excel.xml");
                //this.dataGridView1.DataSource = dataTable;

                //DataSet dataSet = excelOperation.ExcelToDataSet(filePath);
                //this.dataGridView1.DataSource=dataSet.Tables[0];

                //ExcelOperMatch excelMatch = new ExcelOperMatch();
                //List<TestPerson> listTestPersons = excelMatch.ExcelToPersons("Sheet1", filePath);
                //this.dataGridView1.DataSource = listTestPersons;
            }
        }

Buttton事件

4) ExcelOperMatchObject.cs

([新增泛型处理Excel的数据,这样可以轻松将Excel的数据转换成数据库表])

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Reflection;
using System.Globalization;

namespace OpenXMLTest
{
    public class ExcelOperMatchObject<T>
    {

        /// <summary>
        /// 根据SheetName和文件路径转换实体对象
        /// 将Excel中的数据映射实体对象集合
        /// </summary>
        /// <param name="sheetName">操作SheetName</param>
        /// <param name="filePath">文件路径</param>
        /// <returns>实体对象集合</returns>
        public List<T> ExcelToObjects(string sheetName, string filePath)
        {
            List<T> listTestPersons = new List<T>();
            try
            {
                using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(filePath, false))//Excel文档包
                {
                    WorkbookPart workBookPart = spreadDocument.WorkbookPart;
                    listTestPersons = ExtcelToObjects(workBookPart, sheetName);
                }
            }
            catch (Exception exp)
            {
                // throw new Exception("可能Excel正在打开中,请关闭重新操作！");
            }
            return listTestPersons;
        }

        /// <summary>
        /// 根据WorkbookPart和SheetName获取实体对象集合
        /// </summary>
        /// <param name="workBookPart">WorkbookPart对象</param>
        /// <param name="sheetName">sheetName</param>
        /// <returns>实体对象集合</returns>
        private List<T> ExtcelToObjects(WorkbookPart workBookPart, string sheetName)
        {
            List<T> listPersons = new List<T>();
            List<string> columnValues = new List<string>();//列头值集合
            List<string> rowCellValues = null;//行数据集合

            //获取WorkbookPart下名为sheetName的Sheet的所有行数据
            IEnumerable<Row> sheetRows = new ExcelOper().GetWorkBookPartRows(workBookPart, sheetName);
            if (sheetRows == null || sheetRows.Count() <= 0)
            {
                return null;
            }

            //将数据导入DataTable,假定第一行为列名,第二行以后为数据
            foreach (Row row in sheetRows)
            {
                rowCellValues = new List<string>();//新行数据
                foreach (Cell cell in row)
                {
                    //获取单元格的值
                    string cellValue = new ExcelOper().GetCellValue(cell, workBookPart);
                    if (row.RowIndex == 1)
                    {
                        columnValues.Add(cellValue);
                    }
                    else
                    {
                        rowCellValues.Add(cellValue);
                    }
                }
                if (row.RowIndex > 1)
                {
                    int rowIndex = Convert.ToInt32(row.RowIndex.ToString());

                    T singlePerson = ConvertToObject<T>(rowIndex, columnValues, rowCellValues);
                    listPersons.Add(singlePerson);
                }
            }
            return listPersons;
        }

        /// <summary>
        /// 根据行号\列头集合\行数据集合,转换数据实体对象 
        /// </summary>
        /// <typeparam name="T">映射实体对象</typeparam>
        /// <param name="rowIndex">行号</param>
        /// <param name="columnValues">列头集合</param>
        /// <param name="rowCellValues">行数据集合</param>
        /// <returns>实体对象</returns>
        private T ConvertToObject<T>(int rowIndex, List<string> columnValues, List<string> rowCellValues)
        {
            //T singlePerson = default(T);
            T singlePerson = Activator.CreateInstance<T>();
            //Type singlePerson = typeof(T);
            foreach (PropertyInfo pi in singlePerson.GetType().GetProperties())
            {
                for (int index = 0; index < columnValues.Count; index++)
                {

                    if (pi.Name.Equals(columnValues[index], StringComparison.OrdinalIgnoreCase))
                    {
                        String propertyType = pi.PropertyType.Name;
                        switch (propertyType)
                        {
                            case "Int32":
                                pi.SetValue(singlePerson, int.Parse(rowCellValues[index]), null);
                                break;
                            case "DateTime":
                                pi.SetValue(singlePerson, DateTime.Parse(rowCellValues[index]), null);
                                break;
                            case "Decimal":
                                pi.SetValue(singlePerson, Decimal.Parse(rowCellValues[index]), null);
                                break;
                            case "Double":
                                pi.SetValue(singlePerson, Double.Parse(rowCellValues[index]), null);
                                break;
                            case "String":
                                pi.SetValue(singlePerson, rowCellValues[index], null);
                                break;
                            case "Boolean":
                                pi.SetValue(singlePerson, Boolean.Parse(rowCellValues[index]), null);
                                break;
                        }
                        break;
                    }

                }
            }
            return singlePerson;
        }
    }
}

数据的泛型处理

5）ExcelOperMatchObject类的调用

(还是在WinForm的Button事件中调用)。

//Excel数据导入,泛型支持与任意实体对象匹配
//这里使用TestPerson实体对象做测试对象,你也可以用多个对象,转换成DataTable或DataSet
ExcelOperMatchObject<TestPerson> execelOperObject = new ExcelOperMatchObject<TestPerson>();
List<TestPerson> listTestPersons = execelOperObject.ExcelToObjects("Sheet1", filePath);
this.dataGridView1.DataSource = listTestPersons;

4.7 代码结构

1) ExcelOper.cs文件代码

2) ExcelOperMatch.cs代码

未完，第二部分请参考文章 C#读取Excel技术概览（2）。

posted @ 2016-08-08 20:48 wenglabs 阅读(3482) 评论(0) 编辑收藏举报

刷新页面返回顶部

WENG'S LABS

Growing up with the help of the world's genius!

C#读取Excel技术概览

1、 OleDb

2、Com组件的方式读取Excel

3、NPOI方式读取Excel

4、一种最新的方式来处理Excel ---- open xml

4.1 Open XML准备

4.2 简单Excel zip包介绍

4.3 简单功能介绍

4.4 简单实现介绍

4.5 简单实现效果

4.6 示例Demo代码：

1) ExcelOper.cs文件代码

2) ExcelOperMatch.cs代码

3) WinForm的Button事件代码

4) ExcelOperMatchObject.cs

5）ExcelOperMatchObject类的调用

4.7 代码结构

1) ExcelOper.cs文件代码

2) ExcelOperMatch.cs代码

公告