循环读取Excel表中的每一个单元格 C#如何循环读取Excel表中的每一个单元格? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 学习,不知道用DATASET能行不??? private DataTable getTable(){ try {// path即是excel文档的路径。 string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= Path"+";Extended Properties=Excel 8.0;"; //Sheet1为excel中表的名字 string sql = "select * from ["Sheet1$]"; OleDbCommand cmd = new OleDbCommand(sql, new OleDbConnection(conn)); OleDbDataAdapter ad = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); ad.Fill(ds); return ds.Tables[0]; } catch (Exception ex) { MessageBox.Show(ex.Message); return null; }} 如果只是读纪录把EXCEL作为一个数据表用SQL来查询,这个是最简单的 如果你读取到DGV中,直接用OLEDB去读取就好了//读取Excel public void ReadExcel() { //创建一个数据链接 string strCon = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source =d:\\XXX.xls;Extended properties=Excel 8.0"; OleDbConnection myConn = new OleDbConnection(strCon); string strCom = " SELECT * FROM [Sheet1$] "; myConn.Open(); //打开数据链接,得到一个数据集 OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn); //得到自己的DataSet对象 myCommand.Fill(ds, "[Sheet1$]"); //关闭此数据链接 myConn.Close(); } 同志们!!!!必须得是获取excel表 循环表中的每一个单元格 string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties=Excel 8.0;" + "data source=f:\\Excel\\excel.xls"; try { OleDbDataAdapter dr = new OleDbDataAdapter("select * from [Sheet1$]", connStr); DataSet dt = new DataSet(); dr.Fill(dt); foreach (DataRow row in dt.Tables[0].Rows) { for (int i = 0; i < dt.Tables[0].Columns.Count; i++) { Response.Write(row[i].ToString()+","); } } } catch (Exception ex) { Response.Write(ex.Message); return; } 大哥:dr.Fill(dt); 如果excel单元格不是字符串是其他类型 读不出来。不用 OLEDB 直接读表 怎么解决?谢谢。(网上找的代码有点乱,看不懂) 把字符串更新为如下就可以啦:connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1;';" + "data source=f:\\Excel\\excel.xls;"; 把字符串的连接改为:string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + "Extended Properties=Excel 8.0 Xml;HDR=NO"; 这个我在office2007中试验过,是对的 HSL888谢谢啦!现在的问题是select * from [Sheet1$]表名不是Sheet1怎么解决。如果你知道怎么读表中的单元格一块解决吧!谢谢啦! 你工作表的名是什么那么Sheet1就代表什么 如果想从工作表中循环取出表并循环表中的每个单元格 就如下: string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1;';" + "data source=f:\\Excel\\excel.xls;"; try { OleDbConnection cn = new OleDbConnection(connStr); cn.Open(); DataTable Table_Name = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); cn.Close(); foreach (DataRow row_table in Table_Name.Rows) { string TabelName = row_table["Table_Name"].ToString();//表名 Response.Write("表:" + TabelName + "<br/>"); OleDbDataAdapter dr = new OleDbDataAdapter("select * from [" + TabelName + "]", connStr); DataSet dt = new DataSet(); dr.Fill(dt); string str = ""; foreach (DataRow row in dt.Tables[0].Rows) { for (int i = 0; i < dt.Tables[0].Columns.Count; i++) { str += row[i].ToString() + ","; } } Response.Write(str + "<br/>"); } } catch (Exception ex) { Response.Write(ex.Message); return; } public void RederExcel(string PfileName) { Microsoft.Office.Interop.Excel.Application AExcelApp = null; Microsoft.Office.Interop.Excel.Workbook AWorkBook = null; object missing = System.Reflection.Missing.Value; AExcelApp = new Microsoft.Office.Interop.Excel.Application(); if (!System.IO.File.Exists(PfileName)) { Console.WriteLine("Excel文件不存在!", "错误"); Console.Read(); } else { AWorkBook = AExcelApp.Workbooks.Open (PfileName, missing, true, missing, missing, missing , missing, missing, missing, missing, missing, missing, missing, missing, missing); AExcelApp.Visible = true; Microsoft.Office.Interop.Excel.Worksheet AWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)AWorkBook.ActiveSheet; //取得当前活动的Sheet //_WorkSheet = (Excel.Worksheet)_WorkBook.Sheets["name"]; //取得指定名字的Sheet for (int i = 0; i < AWorkSheet.Rows.Count; i++) { for (int j = 0; j < AWorkSheet.Columns.Count; j++) { Microsoft.Office.Interop.Excel.Range Arange = (Microsoft.Office.Interop.Excel.Range)AWorkSheet.Cells[i + 1, j + 1]; if (Arange.Value2 != null) { string s = Arange.Value2.ToString(); } } } AExcelApp.Quit(); AExcelApp = null;} 就这几种方式 1 通过数据库, 2 通过com(如14楼) 知道方法 就可以自己 google了 这样 学习起来比较深刻 获取Range(可以是一个单元格,也可以是多个单元格),然后获取Value2属性即可 菜鸟级的笨问题, 高手发发善心。。。 关于绘制窗体背景颜色(渐变色) c# 如何用xml来读图片和存图片? 项目寻求合作,有能力现金交易 三目运算问题,请大家看看输出结果是多少? 点击treeview控件节点前的+号扩展节点时,怎样让该节点增加一些新节点? ■■现金100元奖励!求win2003server登陆窗口图片修改方法!! C#如何启动autocad2014 急!急急!!!我的查询语句错在哪? base64反编码,还原的问题? Visual Studio 2008里写代码如何快速定位代码行? DataGridView 的CellErrorTextNeeded事件问题
{
try
{// path即是excel文档的路径。 string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= Path"+";Extended Properties=Excel 8.0;"; //Sheet1为excel中表的名字
string sql = "select * from ["Sheet1$]";
OleDbCommand cmd = new OleDbCommand(sql, new OleDbConnection(conn));
OleDbDataAdapter ad = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
ad.Fill(ds);
return ds.Tables[0];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return null;
}
}
用SQL来查询,这个是最简单的
public void ReadExcel()
{
//创建一个数据链接
string strCon = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source =d:\\XXX.xls;Extended properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] ";
myConn.Open();
//打开数据链接,得到一个数据集
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
//得到自己的DataSet对象
myCommand.Fill(ds, "[Sheet1$]");
//关闭此数据链接
myConn.Close();
}
"Extended Properties=Excel 8.0;" +
"data source=f:\\Excel\\excel.xls";
try
{
OleDbDataAdapter dr = new OleDbDataAdapter("select * from [Sheet1$]", connStr);
DataSet dt = new DataSet(); dr.Fill(dt);
foreach (DataRow row in dt.Tables[0].Rows)
{
for (int i = 0; i < dt.Tables[0].Columns.Count; i++)
{
Response.Write(row[i].ToString()+",");
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
return;
}
不用 OLEDB 直接读表 怎么解决?谢谢。(网上找的代码有点乱,看不懂)
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties='Excel 8.0;HDR=NO;IMEX=1;';" +
"data source=f:\\Excel\\excel.xls;";
这个我在office2007中试验过,是对的
如果你知道怎么读表中的单元格一块解决吧!谢谢啦!
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Extended Properties='Excel 8.0;HDR=NO;IMEX=1;';" +
"data source=f:\\Excel\\excel.xls;";
try
{
OleDbConnection cn = new OleDbConnection(connStr);
cn.Open();
DataTable Table_Name = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
cn.Close();
foreach (DataRow row_table in Table_Name.Rows)
{
string TabelName = row_table["Table_Name"].ToString();//表名
Response.Write("表:" + TabelName + "<br/>");
OleDbDataAdapter dr = new OleDbDataAdapter("select * from [" + TabelName + "]", connStr);
DataSet dt = new DataSet(); dr.Fill(dt);
string str = "";
foreach (DataRow row in dt.Tables[0].Rows)
{
for (int i = 0; i < dt.Tables[0].Columns.Count; i++)
{
str += row[i].ToString() + ",";
}
}
Response.Write(str + "<br/>");
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
return;
}
{
Microsoft.Office.Interop.Excel.Application AExcelApp = null;
Microsoft.Office.Interop.Excel.Workbook AWorkBook = null;
object missing = System.Reflection.Missing.Value;
AExcelApp = new Microsoft.Office.Interop.Excel.Application();
if (!System.IO.File.Exists(PfileName))
{
Console.WriteLine("Excel文件不存在!", "错误");
Console.Read();
}
else
{
AWorkBook = AExcelApp.Workbooks.Open (PfileName, missing, true, missing, missing, missing
, missing, missing, missing, missing, missing, missing, missing, missing, missing); AExcelApp.Visible = true;
Microsoft.Office.Interop.Excel.Worksheet AWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)AWorkBook.ActiveSheet; //取得当前活动的Sheet
//_WorkSheet = (Excel.Worksheet)_WorkBook.Sheets["name"]; //取得指定名字的Sheet
for (int i = 0; i < AWorkSheet.Rows.Count; i++)
{
for (int j = 0; j < AWorkSheet.Columns.Count; j++)
{
Microsoft.Office.Interop.Excel.Range Arange = (Microsoft.Office.Interop.Excel.Range)AWorkSheet.Cells[i + 1, j + 1];
if (Arange.Value2 != null)
{
string s = Arange.Value2.ToString();
}
}
}
AExcelApp.Quit();
AExcelApp = null;
}