C讀取excel電子版本

1、Good is good, but better carries it.精益求精，善益求善。C讀取excel-HYPERLINK/koon/archive/2011/06/29/2093140.htmlC#讀取Excel文件代碼的幾種方法片段1usingSystem.Data.OleDb;.staticvoidMain()Application.Run(newForm1();privatevoidForm1_Load(objectsender,System.EventArgse)DataTablemyT=ExcelToDataTable(D:/文件/新武昌站點資料.xls,sheet1);St

2、ringmystr=myT.Rows00.ToString();this.textBox1.Text=mystr;publicstaticDataTableExcelToDataTable(stringstrExcelFileName,stringstrSheetName)/源的定義stringstrConn=Provider=Microsoft.Jet.OLEDB.4.0;+DataSource=+strExcelFileName+;+ExtendedProperties=Excel8.0;HDR=NO;IMEX=1;/Sql語句/stringstrExcel=string.Format(s

3、elect*from0$,strSheetName);這是一種方法stringstrExcel=select*fromsheet1$;/定義存放的數(shù)據(jù)表DataSetds=newDataSet();/連接數(shù)據(jù)源OleDbConnectionconn=newOleDbConnection(strConn);conn.Open();/適配到數(shù)據(jù)源OleDbDataAdapteradapter=newOleDbDataAdapter(strExcel,strConn);adapter.Fill(ds,strSheetName);conn.Close();returnds.TablesstrSheet

4、Name;很簡單的代碼，但是問題就出在連接字符串上面，后面一定要加上ExtendedProperties=Excel8.0;HDR=NO;IMEX=1，HDR和IMEX也一定要配合使用，哈哈,老實說,我也不知道為什么,這樣配合的效果最好,這是我艱苦調(diào)試的結(jié)果.IMEX=1應(yīng)該是將所有的列全部視為文本,我也有點忘記了.至于HDR本來只是說是否要出現(xiàn)一行標(biāo)題頭而已,但是結(jié)果卻會導(dǎo)致某些字段值丟失,所以其實我至今也搞不明白為什么,很可能是驅(qū)動的問題.片段2提供兩種方法：一個是直接打開excel文件，然后逐行讀取，速度較慢；還有一種方法是通過OleDb連接，把excel文件作為數(shù)據(jù)源來讀取方法一：這種

5、直接讀取單元格的方法釋放很重要。Excel.Applicationexcel=null;Excel.Workbookswbs=null;Excel.Workbookwb=null;Excel.Worksheetws=null;Excel.Rangerange1=null;objectNothing=System.Reflection.Missing.Value;tryexcel=newExcel.Application();excel.UserControl=true;excel.DisplayAlerts=false;excel.Application.Workbooks.Open(this

6、.FilePath,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing,Nothing);wbs=excel.Workbooks;wb=wbs1;ws=(Excel.Worksheet)wb.WorksheetsSheet2;introwCount=ws.UsedRange.Rows.Count;intcolCount=ws.UsedRange.Columns.Count;if(rowCount=0)thrownewInvalidFormatException(文件中沒有

7、數(shù)據(jù)記錄);if(colCount4)thrownewInvalidFormatException(字段個數(shù)不對);for(inti=0;ithis.rowNo=i+1;objectrow=newobject4;for(intj=0;j0)continue;DataRowdataRow=this.readExcel(row);if(this.isNullRecord=1)continue;if(this.verifyData(dataRow)=false)errFlag+;this.updateTableCurr(dataRow);finallyif(excel!=null)if(wbs!=n

8、ull)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();

9、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來逐行讀，數(shù)據(jù)較快注：這種方法容易把混合型的字段作為null值讀取進來，解決辦法是改造連接字符串strCon

10、n=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:Erp1912.xls;ExtendedProperties=Excel8.0;HDR=Yes;IMEX=1;通過Imex=1來把混合型作為文本型讀取，避免null值,詳細請見HYPERLINK/privateDataSetimportExcelToDataSet(stringFilePath)stringstrConn;strConn=Provider=Microsoft.Jet.OLEDB.4.0;+DataSource=+FilePath+;ExtendedProperties=Excel8.

11、0;OleDbConnectionconn=newOleDbConnection(strConn);OleDbDataAdaptermyCommand=newOleDbDataAdapter(SELECT*FROMSheet1$,strConn);DataSetmyDataSet=newDataSet();trymyCommand.Fill(myDataSet);catch(Exceptionex)thrownewInvalidFormatException(該Excel文件的工作表的名字不正確,+ex.Message);returnmyDataSet;片段3用c#讀取excel文件,寫到da

12、tagridview控件中用c#讀取excel文件,寫到datagridview控件中stringstrconn=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:MyExcel.xls;ExtendedProperties=Excel8.0;HDR=Yes;IMEX=1;OleDbConnectionconn=newOleDbConnection(strconn);conn.Open();if(bo=false)comboBox1.Items.Clear();DataTabledt=conn.GetOleDbSchemaTable(OleDbSche

13、maGuid.Tables,newobjectnull,null,null,TABLE);foreach(DataRowdrindt.Rows)comboBox1.Items.Add(String)drTABLE_NAME);/MessageBox.Show(String)drTABLE_NAME);/comboBox1.Text=comboBox1.Items0.ToString();elsestringsql=select*from+comboBox1.Text;OleDbDataAdapteraper=newOleDbDataAdapter(sql,conn);DataSetmyset=

14、newDataSet();aper.Fill(myset,comboBox1.Text);dataGridView1.DataSource=myset.TablescomboBox1.Text;conn.Close();備注：Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:MyExcel.xls;ExtendedProperties=Excel8.0;HDR=Yes;IMEX=1HDR=Yes;indicatesthatthefirstrowcontainscolumnnames,notdataIMEX=1;tellsthedrivertoalway

15、sreadintermixeddatacolumnsastextTIP!SQLsyntax:SELECT*FROMsheet1$-i.e.worksheetnamefollowedbya$andwrappedinbrackets.如果第一行是數(shù)據(jù)而不是標(biāo)題的話,應(yīng)該寫:HDR=No;IMEX=1;tellsthedrivertoalwaysreadintermixeddatacolumnsastext片段4C#讀取Excel文件數(shù)據(jù)相當(dāng)簡單,Excel就像數(shù)據(jù)庫,每個Sheet就是一個Table.Microsoft.Jet.OLEDB驅(qū)動.之后是DataReader循環(huán),或DataSet處理都

16、非常簡單.HTTP:/BLOG.CSDN.NET/CRABO/注意:數(shù)據(jù)類型的轉(zhuǎn)換!#regionsetconnectionstringstrConn=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=+this.txtPath.Text+;ExtendedProperties=Excel8.0;myDataReader=null;craboDbConnection=newOleDbConnection(strConn);OleDbCommandmyOleDbCommand=newOleDbCommand(SELECT*FROMSheet1$,myOleD

17、bConnection);#endregiontrymyOleDbConnection.Open();myDataReader=myOleDbCommand.ExecuteReader();while(myDataReader.Read()this.txtSeq.Text=Convert.ToString(myDataReader.GetValue(0);/列1this.txtName.Text=Convert.ToString(myDataReader.GetValue(1);/列2this.txtPIN.Text=Convert.ToString(myDataReader.GetValue(2);/列3#regionCatchcatch(System.Threading.ThreadAbortExceptione)System.Threading.Thread.ResetAbort();this.lblResult.Text=線程被中斷.+e.Message;catch(Exceptionex)System.Windows.Forms.MessageBox.Show(e