C#操作Excel文件(.xls或.xlsx)

原作者: [db:作者] 来自: [db:来源] 收藏邀请

在.net中，常用的操作excel文件的方式，有三种： OLE DB的形式，第三方框架NPOI， Office组件。

总结： 通过对比，在读取大数据量的excel文件，建议用OLE DB的形式，把excel文件当作数据源，效率比较高。

1. 用OLE DB 方法

public static DataTable CreateDataTable(string excelFileName, string sheetName)
        {
            DataTable dt = new DataTable();
            try
            {
                //For ".xlsx" excel file.
                //oleDbConnection = new OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source='" + files[0] + "'" + "; Extended Properties='Excel 8.0;HDR=No;IMEX=1;'");

                using (OleDbConnection oleDbConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + excelFileName + "'" + "; Extended Properties= 'Excel 8.0;HDR=No;IMEX=1;'"))
                {
                    OleDbDataAdapter oleDbAdapter = new OleDbDataAdapter("select * from [" + sheetName + "]", oleDbConnection);
                    oleDbAdapter.Fill(dt);
                }

                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("CreateDataTable Function error for : {0}", ex.Message));
            }
        }

2. 用NPOI 方法

public static DataTable CreateDataTableByNPOI(string excelFileName, string sheetName)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook = null;
            ISheet sheet = null;
            try
            {
                string prefix = excelFileName.Substring(excelFileName.IndexOf('.')).ToLower();

                using (FileStream fs = new FileStream(excelFileName, FileMode.Open, FileAccess.Read))
                {
                    if (prefix == ".xls")
                    {
                        workbook = new HSSFWorkbook(fs);
                    }
                    else if (prefix == ".xlsx")
                    {
                        workbook = new XSSFWorkbook(fs);
                    }
                }

                sheet = workbook.GetSheet(sheetName);

                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(0);
                    int cellCount = firstRow.LastCellNum;

                    for (int i = firstRow.FirstCellNum; i < cellCount; i++)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;

                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                dt.Columns.Add(column);
                            }
                        }
                    }

                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            continue;
                        }

                        DataRow dataRow = dt.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                        dt.Rows.Add(dataRow);
                    }
                }

                return dt;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("CreateDataTableByNPOI Function error for : {0}", ex.Message));
            }
            finally
            {
                workbook.Close();
            }
        }

　　注意：要引入NPOI 相关的DLL文件。