C#中如何去读取Excel表格？
首先，如何去读取Excel表格？其次，怎样去读取特定的行，特定的列，特定的单元格？
请高手指教，有例子的话更好，谢谢各位大侠啊！
[解决办法]
- C# code
using System;using System.Collections.Generic;using System.Linq;using System.Text;using Microsoft.Office.Interop.Excel;using System.Reflection;using System.Data;using System.Data.OleDb;namespace xiaoh{ public class ExcelHelper { /// <summary> /// 写入Excel文档 /// </summary> /// <param name="xPath">文件名称</param> /// <param name="source">需要写入的内容</param> /// <param name="sheetIndex">sheet序列</param> public static void writeExcel(string xPath, string[][] source, int sheetIndex) { Application ExcelRS = new ApplicationClass(); Workbook RSbook = ExcelRS.Workbooks.Open(xPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Worksheet RSsheet = (Worksheet)RSbook.Sheets.get_Item(sheetIndex); for (int i = 0; i < source.Length; i++) { for (int j = 0; j < source[i].Length; j++) { RSsheet.Cells[i + 1, j + 1] = source[i][j]; } } RSbook.Save(); RSbook.Close(); RSsheet = null; RSbook = null; ExcelRS = null; collect(); } /// <summary> /// 读取Excel文档 /// </summary> /// <param name="xPath">文件名称</param> /// <param name="sheetIndex">sheet序号</param> /// <returns>返回一个数据集</returns> public static DataSet readExcel(string xPath, int sheetIndex) { Microsoft.Office.Interop.Excel.Application ExcelRS = new ApplicationClass(); Workbook RSbook = ExcelRS.Workbooks.Open(xPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Worksheet RSsheet = (Worksheet)RSbook.Sheets.get_Item(sheetIndex); string sheetName = RSsheet.Name; RSbook.Close(); RSsheet = null; RSbook = null; ExcelRS = null; collect(); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + xPath + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); try { conn.Open(); string strExcel = string.Format("select * from [{0}$]", sheetName); OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn); DataSet ds = new DataSet(); myCommand.Fill(ds); return ds; } catch { return null; } finally { conn.Close(); } } private static void collect() { GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }}
[解决办法]
http://topic.csdn.net/u/20110425/10/33fd08e6-54c2-4c89-adb4-e3507273ef66.html看这个帖子我发的例子
[解决办法]
- C# code
private void Button4_Click(object sender, System.EventArgs e) { string Todate =System.DateTime.Now.Year.ToString().Trim()+System.DateTime.Now.Month.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Day.ToString().Trim().PadLeft(2,'0'); if(this.File1.Value=="") { this.RegisterClientScriptBlock("er01","<script language=JavaScript>alert('一文件')</script>"); return; } string workno=this.Session["user_id"].ToString().Trim(); string filename =System.DateTime.Now.Year.ToString().Trim()+System.DateTime.Now.Month.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Day.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Hour.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Minute.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Second.ToString().Trim().PadLeft(2,'0')+System.DateTime.Now.Millisecond.ToString().Trim(); this.File1.PostedFile.SaveAs(@"D:\KQConfirm\data\"+workno+"_"+filename+".xls"); //--------------check data string filestr =@"D:\KQConfirm\data\"+workno+"_"+filename+".xls"; string con = "Provider =Microsoft.Jet.OLEDB.4.0; Data Source ="+filestr+";Extended Properties = Excel 8.0"; string sql ="select * from [Sheet1$]"; int i=0,j=0; OleDbConnection olecon = new OleDbConnection(con); OleDbCommand olecmd = new OleDbCommand(sql,olecon); OleDbDataAdapter oleda = new OleDbDataAdapter(olecmd); DataSet myds = new DataSet(); try { oleda.Fill(myds ,"jiarecordtemp"); } catch { this.RegisterClientScriptBlock("er01","<script language=JavaScript>alert('文件不正,查!')</script>"); return; } oleda.Dispose(); olecmd.Dispose(); olecon.Dispose(); i=myds.Tables["jiarecordtemp"].DefaultView.Count; DataTable dttemp = myds.Tables["jiarecordtemp"].Clone(); DataColumn dc = new DataColumn(""); dttemp.Columns.Add(dc); //--------------- Check wno at first for(j=0;j<i;j++) { object[] to =new object[6]; to[0] = myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim() ; to[1] = myds.Tables["jiarecordtemp"].DefaultView[j][1].ToString().Trim() ; to[2] = myds.Tables["jiarecordtemp"].DefaultView[j][2].ToString().Trim() ; to[3] = myds.Tables["jiarecordtemp"].DefaultView[j][3].ToString().Trim() ; to[4] = myds.Tables["jiarecordtemp"].DefaultView[j][4].ToString().Trim() ; if(to[0].ToString()=="") { to[5]="位同仁的名不正!"; dttemp.Rows.Add(to); continue; } //string str=myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim(); try { DateTime kqdate = DateTime.ParseExact(to[0].ToString(),"yyyyMMdd",null); DateTime ndate=Convert.ToDateTime(System.DateTime.Now.Date); System.TimeSpan ND=ndate-kqdate; int n=ND.Days; if (n>2) { // to[5]="已超名!"; // dttemp.Rows.Add(to); // continue; } } catch { to[5]="位同仁的名不正!"; dttemp.Rows.Add(to); continue;} if(myds.Tables["jiarecordtemp"].DefaultView[j]["工"].ToString().Trim().Length!=6 && myds.Tables["jiarecordtemp"].DefaultView[j]["工"].ToString().Trim().Length!=7) { to[5]="位同仁的工不正!"; dttemp.Rows.Add(to); continue; } if(to[3].ToString()!="N" &&to[3].ToString()!="Y") { to[5]="正名!"; dttemp.Rows.Add(to); continue; } SqlCommand pcmd = new SqlCommand("select TYPE from dbo.TYPEDefine where type='ETYPE' and code='"+to[4]+"'"); SqlConnection con1 = new SqlConnection(ConnectionString1); pcmd.Connection = con1 ; pcmd.CommandTimeout = 300000 ; pcmd.Connection.Open(); SqlDataReader pdr = pcmd.ExecuteReader(CommandBehavior.SingleRow); if (!pdr.HasRows && to[3].ToString()=="N") { to[5]="按照考勤常代"; dttemp.Rows.Add(to); //dttemp.Rows.Add( myds.Tables["jiarecordtemp"].Rows[j].ItemArray ); continue; } pcmd.Connection.Close(); pdr.Close(); pcmd.Dispose(); string dd1 =myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim(); pcmd = new SqlCommand("select top 1 * from Needpepole where workno ='"+myds.Tables["jiarecordtemp"].DefaultView[j]["工"].ToString().Trim()+"' and dateoccur='"+dd1+"'" ); con1 = new SqlConnection(ConnectionString1); pcmd.Connection = con1 ; pcmd.CommandTimeout = 30000 ; pcmd.Connection.Open(); pdr = pcmd.ExecuteReader(CommandBehavior.SingleRow); if (!pdr.HasRows) { to[5]="下列的工、班有或日尚未生!"; dttemp.Rows.Add(to); continue; } pcmd.Connection.Close(); pdr.Close(); pcmd.Dispose(); } if (dttemp.DefaultView.Count!=0) { this.DataGrid1.DataSource=dttemp; this.DataGrid1.DataBind(); this.Label1.Text="下列的常!"; this.DataGrid1.Visible=true; dttemp.Rows.Clear(); return; } dttemp.Rows.Clear(); this.DataGrid1.DataSource=dttemp; this.DataGrid1.DataBind(); this.DataGrid1.Visible=false; this.Label1.Text=""; //update //string update=System.DateTime.Now.ToString("yyyy-mm-dd hh:mm:ss"); StringBuilder sb = new StringBuilder(); string sal="declare @d datetime set @d=getdate();"; sb.Append(sal); // string workno=this.Session["user_id"].ToString().Trim(); for(j=0;j<i;j++) { string Dmstatus=myds.Tables["jiarecordtemp"].DefaultView[j][3].ToString().Trim(); string status; string Dmcomment=myds.Tables["jiarecordtemp"].DefaultView[j][4].ToString().Trim(); string Dmno=myds.Tables["jiarecordtemp"].DefaultView[j]["工"].ToString().Trim(); string dd =myds.Tables["jiarecordtemp"].DefaultView[j][0].ToString().Trim(); if (Dmstatus!="Y") {status="N"; sal=" update Needpepole set DMstatus='"+Dmstatus+ "', status='"+status+"', Dmcomment='" +Dmcomment+ "', batchID='" +filename+"',Lasteditby='"+u_name+"',lastedittime=@d where workno='"+Dmno+"' and dateoccur='"+dd+"';" ;} else { sal=" update Needpepole set DMstatus='"+Dmstatus+"', status=case when KQstatus='Y' and fabstatus='Y' then 'Y' else 'N' END, Dmcomment='" +Dmcomment+ "', batchID='" +filename+"',Lasteditby='"+u_name+"',lastedittime=@d where workno='"+Dmno+"' and dateoccur='"+dd+"' ; " ; } sb.Append(sal); sal="insert into CheckRecord(Workno,CheckDate,KQstatus,Comment,Lastedittime,Lasteditby,BatchID) values('"+Dmno+"','" +dd+"','"+Dmstatus+ "','" +Dmcomment+ "',@d,'"+workno+"','"+filename+"');"; sb.Append(sal); } SqlConnection con2 = new SqlConnection(ConnectionString1); SqlCommand cmd = new SqlCommand(); cmd.Connection = con2 ; cmd.CommandTimeout=10000; sb.Append("update Needpepole set dmcomment=codename from TYPEDefine b where dmcomment=cast(b.code as varchar(3)) and TYPE='ETYPE' and batchID='" +filename+ "';" ); cmd.CommandText =sb.ToString(); cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); this.RegisterClientScriptBlock("er03","<script language=JavaScript>alert('成功入名!')</script>"); }
[解决办法]
用office自带的Excel就行。
代码如下：
ExcelUtil.cs
- C# code
using System;using System.IO;using Microsoft.Office.Interop.Excel;using System.Runtime.InteropServices;using System.Collections.Generic;namespace zMoneyKeeper.business{class ExcelUtil{ /// <summary> /// 释放资源 /// </summary> /// <param name="pObj"></param> public void ReleaseCOM(object pObj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj); } catch { throw new Exception("释放资源时发生错误！"); } finally { pObj = null; } } public ExcelUtil(string filePath) { this.filePath = filePath; app = new Microsoft.Office.Interop.Excel.Application();//打开一个Excel应用 if (app == null) { return; } wbs = app.Workbooks; wb = wbs.Add(filePath); shs = wb.Sheets; int sheetNumber = shs.Count; for (int i = 1; i <= sheetNumber; i++ ) { _Worksheet sh = (_Worksheet)shs.get_Item(i); this.ShList.Add(sh); } } /// <summary> /// 保存文件 /// </summary> public void save() { if (filePath != null) { FileInfo old = new FileInfo(filePath); if(old.Exists) { File.Delete(filePath); } wb.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); foreach (_Worksheet inst in shList) { ReleaseCOM(inst); } ReleaseCOM(shs); ReleaseCOM(wb); ReleaseCOM(wbs); app.Quit(); ReleaseCOM(app); } } /// <summary> /// 杀掉进程的方式保存excel。 /// 用来解决正常保存时出现的公式异常问题。 /// </summary> public void saveByKillProcess() { if (filePath != null) { FileInfo old = new FileInfo(filePath); if (old.Exists) { File.Delete(filePath); } wb.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); foreach (_Worksheet inst in shList) { ReleaseCOM(inst); } ReleaseCOM(shs); ReleaseCOM(wb); ReleaseCOM(wbs); KillExcel(app); } } [DllImport("User32.dll", CharSet = CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); private void KillExcel(Microsoft.Office.Interop.Excel.Application excel) { IntPtr t = new IntPtr(excel.Hwnd); //得到这个句柄，具体作用是得到这块内存入口 int k = 0; GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 p.Kill(); //关闭进程k } private Microsoft.Office.Interop.Excel.Application app; private Workbooks wbs; private _Workbook wb; private Sheets shs; private List<_Worksheet> shList = new List<_Worksheet>(); public List<_Worksheet> ShList { get { return shList; } set { shList = value; } } private string filePath;}}