C读取excel.doc

《C读取excel.doc》由会员分享，可在线阅读，更多相关《C读取excel.doc（14页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、【精品文档】如有侵权，请联系网站删除，仅供学习与交流C读取excel.精品文档.C# 读取Excel文件代码的几种方法片段 1usingSystem.Data.OleDb;.staticvoidMain()Application.Run(newForm1();privatevoidForm1_Load(objectsender,System.EventArgse)DataTablemyT=ExcelToDataTable(D:/文件/新武昌站点资料.xls,sheet1);Stringmystr=myT.Rows00.ToString();this.textBox1.Text=mystr;pu

2、blicstaticDataTableExcelToDataTable(stringstrExcelFileName,stringstrSheetName)/源的定义stringstrConn=Provider=Microsoft.Jet.OLEDB.4.0;+DataSource=+strExcelFileName+;+ExtendedProperties=Excel8.0;HDR=NO;IMEX=1;/Sql语句/stringstrExcel=string.Format(select*from0$,strSheetName);这是一种方法stringstrExcel=select*from

3、sheet1$;/定义存放的数据表DataSetds=newDataSet();/连接数据源OleDbConnectionconn=newOleDbConnection(strConn);conn.Open();/适配到数据源OleDbDataAdapteradapter=newOleDbDataAdapter(strExcel,strConn);adapter.Fill(ds,strSheetName);conn.Close();returnds.TablesstrSheetName;很简单的代码，但是问题就出在连接字符串上面，后面一定要加上Extended Properties=Excel

4、 8.0;HDR=NO;IMEX=1，HDR和IMEX也一定要配合使用，哈哈,老实说,我也不知道为什么,这样配合的效果最好,这是我艰苦调试的结果.IMEX=1应该是将所有的列全部视为文本,我也有点忘记了.至于HDR本来只是说是否要出现一行标题头而已,但是结果却会导致某些字段值丢失,所以其实我至今也搞不明白为什么,很可能是驱动的问题.片段 2提供两种方法：一个是直接打开excel文件，然后逐行读取，速度较慢；还有一种方法是通过OleDb连接，把excel文件作为数据源来读取方法一：这种直接读取单元格的方法释放很重要。Excel.Applicationexcel=null;Excel.Workbo

5、okswbs=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.FilePath,Nothing,Nothing,Nothing,Nothing,Nothing,Nothin

6、g,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(文件中没有数据记录);if(colCount4)thrownewInvalidFormatException(字段个数不对

7、);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!=null)if(wb!=null)if(ws!=null)if(range1!=null)System.Runti

8、me.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(

9、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:Erp19

10、12.xls;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1;通过Imex=1来把混合型作为文本型读取，避免null值,详细请见privateDataSetimportExcelToDataSet(stringFilePath)stringstrConn;strConn=Provider=Microsoft.Jet.OLEDB.4.0;+DataSource=+FilePath+;ExtendedProperties=Excel8.0;OleDbConnectionconn=newOleDbConnection(strConn);OleDbDataAd

11、aptermyCommand=newOleDbDataAdapter(SELECT*FROMSheet1$,strConn);DataSetmyDataSet=newDataSet();trymyCommand.Fill(myDataSet);catch(Exceptionex)thrownewInvalidFormatException(该Excel文件的工作表的名字不正确,+ex.Message);returnmyDataSet;片段 3用c#读取excel文件,写到datagridview控件中用c#读取excel文件,写到datagridview控件中stringstrconn=Pro

12、vider=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(OleDbSchemaGuid.Tables,newobjectnull,null,null,TABLE);foreach(DataRow

13、drindt.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=newDataSet();aper.Fill(myset,comboBox1.Text);dataGridView1.D

14、ataSource=myset.TablescomboBox1.Text;conn.Close();备注：Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:MyExcel.xls;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1HDR=Yes; indicates that the first row contains columnnames, not dataIMEX=1; tells the driver to always read intermixed data columns as textTIP!

15、SQL syntax: SELECT * FROM sheet1$ - i.e. worksheet name followed by a $ and wrapped in brackets.如果第一行是数据而不是标题的话, 应该写: HDR=No;IMEX=1; tells the driver to always read intermixed data columns as text片段 4C#读取Excel文件数据 相当简单,Excel就像数据库,每个Sheet就是一个Table. Microsoft.Jet.OLEDB驱动.之后是DataReader循环,或DataSet处理都非常简

16、单.HTTP:/BLOG.CSDN.NET/CRABO/注意:数据类型的转换!#regionsetconnectionstringstrConn=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=+this.txtPath.Text+;ExtendedProperties=Excel8.0;myDataReader=null;craboDbConnection=newOleDbConnection(strConn);OleDbCommandmyOleDbCommand=newOleDbCommand(SELECT*FROMSheet1$,myOleDbCo

17、nnection);#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)

18、;/列3#regionCatchcatch(System.Threading.ThreadAbortExceptione)System.Threading.Thread.ResetAbort();this.lblResult.Text=线程被中断.+e.Message;catch(Exceptionex)System.Windows.Forms.MessageBox.Show(ex.ToString();finally/AlwayscallClosewhendonereading.if(myDataReader!=null)myDataReader.Close();/Closetheconnectionwhendonewithit.if(craboDbConnection!=null&craboDbConnection.State=ConnectionState.Open)craboDbConnection.Close();if(webResponse!=null)webResponse.Close();#endregion