C#中怎么去读取Excel表格
C#中如何去读取Excel表格？
首先，如何去读取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>");
}