读取Excel文件

第一种：

采用OleDB读取EXCEL文件：

  bool hasTitle = false;
             OpenFileDialog openFile = new OpenFileDialog();
             openFile.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
             openFile.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
             openFile.Multiselect = false;
             openFile.ShowDialog();
             var filePath = openFile.FileName;
             string fileType = System.IO.Path.GetExtension(filePath);
   string strCon = string.Format("Provider=Microsoft.ACE.OLEDB.{0}.0;" +
                      "Extended Properties="Excel {1}.0;HDR={2};IMEX=1;";" +
                      "data source={3};",
                      (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), filePath);
             OleDbConnection myConn = new OleDbConnection(strCon);
             string strCom = " SELECT * FROM [M_Virgil_Port$]";
             myConn.Open();
             OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
             DataSet myDataSet = new DataSet();
             myCommand.Fill(myDataSet, "[M_Virgil_Port]");
             DataTable item = myDataSet.Tables[0];

第二种：

NPOI读取EXCEL

 

1、整个Excel表格叫做工作表：WorkBook（工作薄），包含的叫页（工作表）：Sheet；行：Row；单元格Cell。

2、NPOI是POI的C#版本，NPOI的行和列的index都是从0开始

3、POI读取Excel有两种格式一个是HSSF，另一个是XSSF。

HSSF和XSSF的区别如下：

HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.

XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

即：HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。

   DataTable dt = new DataTable();
             HSSFWorkbook hssfworkbook = null; //HSSF适用2007以前的版本
             XSSFWorkbook xssfworkbook = null; //XSSF适用2007版本及其以上的。
             //string fileExt = Path.GetExtension(strFileName);//获取文件的后缀名
             using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
             {
                 if (fileType == ".xls")
                     hssfworkbook = new HSSFWorkbook(file);
                 else if (fileType == ".xlsx")
                     xssfworkbook = new XSSFWorkbook(file);//初始化太慢了，不知道这是什么bug
             }
             if (hssfworkbook != null)
             {
                 HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheet("Manifest");
                 if (sheet != null)
                 {
                     System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                     HSSFRow headerRow = (HSSFRow)sheet.GetRow(5);
                     int cellCount = headerRow.LastCellNum;
                     for (int j = 0; j < cellCount; j++)
                     {
                         HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                         dt.Columns.Add(Convert.ToString(cell));
                     }
                     for (int i = 6; i <= sheet.LastRowNum; i++)
                     {
                         HSSFRow row = (HSSFRow)sheet.GetRow(i);


                         DataRow dataRow = dt.NewRow();
                         for (int j = row.FirstCellNum; j < cellCount; j++)
                         {
                             var aa = row.GetCell(j);
                             if (row.GetCell(j)!=null)
                                 dataRow[j] =Convert.ToString(row.GetCell(j));
                         }
                         dt.Rows.Add(dataRow);
                     }
                 }
             }
             else if (xssfworkbook != null)
             {
                 XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheet("Manifest");
                 if (xSheet != null)
                 {
                     System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
                     XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
                     int cellCount = headerRow.LastCellNum;
                     for (int j = 0; j < cellCount; j++)
                     {
                         XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                         dt.Columns.Add(cell.ToString());
                     }
                     for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
                     {
                        

                         XSSFRow row = (XSSFRow)xSheet.GetRow(i);

                        
                         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);
                     }
                 }
             }