C#中Excel檔案的讀取

來源:互聯網
上載者:User
excel 提供兩種方法:一個是直接開啟excel檔案,然後逐行讀取,速度較慢;還有一種方法是通過OleDb串連,把excel檔案作為資料來源來讀取
方法一:這種直接讀取儲存格的方法釋放很重要。

   Excel.Application excel = null;
   Excel.Workbooks wbs = null;
   Excel.Workbook wb = null;
   Excel.Worksheet ws = null;
   Excel.Range range1 = null;
   object Nothing = System.Reflection.Missing.Value;
    
   try
   {
    excel = new Excel.Application();
    excel.UserControl = true;
    excel.DisplayAlerts = false;
                    
    excel.Application.Workbooks.Open(this.FilePath,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing ) ;
    
    wbs = excel.Workbooks;
    wb = wbs[1];
    ws = (Excel.Worksheet)wb.Worksheets["Sheet2"];
    
         
    int rowCount = ws.UsedRange.Rows.Count;
    int colCount = ws.UsedRange.Columns.Count;
    if (rowCount <= 0)
     throw new InvalidFormatException("檔案中沒有資料記錄");
    if (colCount < 4 )
     throw new InvalidFormatException("欄位個數不對");
    
    for (int i = 0;i    {

     this.rowNo = i + 1;
     object[] row = new object[4];
     for (int j = 0;j<4;j++)
     {
      range1 = ws.get_Range(ws.Cells[i+2,j+1],ws.Cells[i+2,j+1]);
      row[j] = range1.Value;

      if (row[0] == null)
      {
       this.isNullRecord++;
       break;
      }
     }
                   
     if (this.isNullRecord > 0)
      continue;

     DataRow dataRow = this.readExcel(row);

     if (this.isNullRecord == 1)
      continue;
  
     if (this.verifyData(dataRow) == false)
      errFlag++;
  
     this.updateTableCurr(dataRow);
    }
    
   }
   finally
   {
    if (excel != null)
    {
     if (wbs != null)
     {
      if (wb != null)
      {
       if (ws != null)
       {
        if (range1 != null)
        {
         System.Runtime.InteropServices.Marshal.ReleaseComObject(range1);
         range1 = null;
        }
        System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
        ws = null;
       }
       wb.Close(false,Nothing,Nothing); 
       System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
       wb = null;
      }
      wbs.Close();
      System.Runtime.InteropServices.Marshal.ReleaseComObject(wbs);
      wbs = null;
     }
     excel.Application.Workbooks.Close();
     excel.Quit();
     System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
     excel = null;
     GC.Collect();
    }
   }

方法二:這裡是fill進dataset,也可以返回OleDbDataReader來逐行讀,資料較快
註:這種方法容易把混合型的欄位作為null值讀取進來,解決辦法是改造連接字串
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Erp1912.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
通過Imex=1來把混合型作為文本型讀取,避免null值,詳細請見http://www.connectionstrings.com
private DataSet importExcelToDataSet(string FilePath)
  {
   string strConn;
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + FilePath + ";Extended Properties=Excel 8.0;";
   OleDbConnection conn = new OleDbConnection(strConn);
   OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$]",strConn);
   DataSet myDataSet = new DataSet();
   try
   {
    myCommand.Fill(myDataSet);
   }
   catch(Exception ex)
   {
    throw new InvalidFormatException("該Excel檔案的工作表的名字不正確," + ex.Message);
   }
   return myDataSet;
  }
 
 
 



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。