EXCEL问题,应该还没有人问过? 利用OLEDB方式开启EXCEL, 但怎样才能将EXCEL中的WorkSheet抓出来呢? 如Sheet1, Sheet2等 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 如:Select * From [Sheet1$] '[Sheet1$]相当于数据库中的表名.也就是Excel中的WorkSheet.. 我知道是这样引用, 我的意思, 假如我事先不知道此EXCEL中有几个Sheet,也不知道每个Sheet的名称,我想把此EXCEL中的Sheet自动抓取出来. ConnOledbconn.Open();ConnOledbconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});打开连接后,就一句话。返回的是一个表(DataTable)你可以放到DataGrid里面看看 切记:一定要先上传的服务器才行!private void Button1_Click(object sender, System.EventArgs e) { try { if (File1.PostedFile!=null) { rd=new Random(1); string filename=DateTime.Now.Date.ToString("yyyymmdd")+DateTime.Now.Day.ToString().Replace(":","")+rd.Next(9999).ToString()+".xls"; File1.PostedFile.SaveAs(@Server.MapPath("file/")+filename); Label1.Text="文件名为"+filename; string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+Server.MapPath("file")+"/"+filename+";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'" ; OleDbConnection thisconnection=new OleDbConnection(conn); thisconnection.Open();// Excel.Range range;// range = worksheet.get_Range(A1,B3);// range.Select();// Response.Write(range); string Sql="select [哈哈],[嘿嘿],[西西],[拉拉//哈哈] from [Sheet1$]"; OleDbDataAdapter mycommand=new OleDbDataAdapter(Sql,thisconnection); DataSet ds=new DataSet(); mycommand.Fill(ds,"[Sheet1$]"); DataGrid1.Style.Add("width","40%"); DataGrid1.DataSource=ds; DataGrid1.DataBind(); thisconnection.Close(); string conn1="User ID=sa;Data Source=127.0.0.1;Password=sa;Initial Catalog=index;Provider=SQLOLEDB.1;"; OleDbConnection thisconnection1=new OleDbConnection(conn1); thisconnection1.Open(); int count=ds.Tables["[Sheet1$]"].Rows.Count; for (int i=0;i<count;i++) { string id_0,id_1,id_2,id_3; id_0=ds.Tables["[Sheet1$]"].Rows[i]["哈哈"].ToString(); id_1=ds.Tables["[Sheet1$]"].Rows[i]["嘿嘿"].ToString(); id_2=ds.Tables["[Sheet1$]"].Rows[i]["西西"].ToString(); id_3=ds.Tables["[Sheet1$]"].Rows[i]["拉拉//哈哈"].ToString(); string excelsql="insert into excel([id_0],[id_1],[id_2],[id_3]) values ('"+id_0+"','"+id_1+"','"+id_2+"','"+id_3+"') "; OleDbCommand mycommand1=new OleDbCommand(excelsql,thisconnection1); mycommand1.ExecuteNonQuery(); } Response.Write("更新成功"); thisconnection1.Close(); if (System.IO.File.Exists(@Server.MapPath("file/")+filename)==true) { Response.Write("true"); } System.IO.File.Delete(@Server.MapPath("file/")+filename); if (System.IO.File.Exists(@Server.MapPath("file/")+filename)==false) { Response.Write("false"); } } } catch(Exception ex) { throw new Exception("你输入的格式有错,请重新输入"); } } 你的意思是找sheet的个数和名字吗?Excel.Application m_objExcel;Excel._Workbook m_objBook;Excel.Workbooks m_objBooks;Excel.Sheets m_objSheets;Excel._Worksheet m_objSheet;m_objExcel = new Excel.Application();m_objBooks = m_objExcel.Workbooks;m_objBook = m_objBooks.Open("你的文件路径",miss,miss,miss,miss,miss,miss,miss,miss,miss,miss,miss,miss);m_objSheets = m_objBook.Worksheets;m_objSheets.Count;//sheet个数m_objSheet = (_Worksheet)m_objSheets.get_Item(1);//第一个sheetm_objSheet.Name;//当前sheet名称 我的意思是要找Sheet的个数和名字, 但不是用EXCEL Application, 而是用OLEDBConnection, 不知道用什么方法, 我查找OLEDBConnection的属性和方法中都没有 刚刚测试了一下,发现还真的好用楼主试试吧string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '"+FileName+"';Extended Properties=Excel 8.0";OleDbConnection cnnxls = new OleDbConnection (mystring);cnnxls.Open();DataTable FromExcel=cnnxls.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});string TableName=FromExcel.TableName;int RowCount=FromExcel.Rows.Count;string SheetName="";for(int i=0;i<RowCount;i++){ DataRow TempRow=FromExcel.Rows[i]; SheetName=TempRow[2].ToString();}TempRow第二列里面的值就是sheet的名字,RowCount的值就是sheet的个数得到了这两个以后,剩下的就是和普通的一样的做法了OleDbDataAdapter myDa =new OleDbDataAdapter("select * from ["+SheetName+"]",cnnxls);DataSet myDs =new DataSet();myDa.Fill(myDs,Test); 补充一下,读出来的sheetname的顺序和excel表里的顺序有区别的我测试的时候一共是4个sheet,第一个读出来的是第4个sheet,然后是第2个,再是第1个,最后是第3个楼主主意一下,顺序不定就象读sql里面读excel表一样,select *的时候,列的顺序和原顺序不同 为什么sql的返回值与TextBox1.Text.Trim().ToString()不等 visual studio中所有控件的功能谁有? studio2008有自带的asp.net性能、压力测试工具吗? 何用在静态html页面里用jsp调用aspx 如何实现网页的自动登录功能? 思归,孟子等大虾专家帮忙,如何将Asp.net页面输出为格式良好的XML文档 如何判断父窗口是不是顶级窗口 哪位好心的大神拉把手吧!!! 超链接问题 树型展开什么做? 错误信息 关于在页面中嵌入rm播放器的问题!vs.net霸道啊
ConnOledbconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});打开连接后,就一句话。返回的是一个表(DataTable)你可以放到DataGrid里面看看
private void Button1_Click(object sender, System.EventArgs e)
{
try
{
if (File1.PostedFile!=null)
{
rd=new Random(1);
string filename=DateTime.Now.Date.ToString("yyyymmdd")+DateTime.Now.Day.ToString().Replace(":","")+rd.Next(9999).ToString()+".xls";
File1.PostedFile.SaveAs(@Server.MapPath("file/")+filename);
Label1.Text="文件名为"+filename;
string conn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source ="+Server.MapPath("file")+"/"+filename+";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'" ;
OleDbConnection thisconnection=new OleDbConnection(conn);
thisconnection.Open();
// Excel.Range range;
// range = worksheet.get_Range(A1,B3);
// range.Select();
// Response.Write(range);
string Sql="select [哈哈],[嘿嘿],[西西],[拉拉//哈哈] from [Sheet1$]";
OleDbDataAdapter mycommand=new OleDbDataAdapter(Sql,thisconnection);
DataSet ds=new DataSet();
mycommand.Fill(ds,"[Sheet1$]");
DataGrid1.Style.Add("width","40%");
DataGrid1.DataSource=ds;
DataGrid1.DataBind();
thisconnection.Close(); string conn1="User ID=sa;Data Source=127.0.0.1;Password=sa;Initial Catalog=index;Provider=SQLOLEDB.1;";
OleDbConnection thisconnection1=new OleDbConnection(conn1);
thisconnection1.Open();
int count=ds.Tables["[Sheet1$]"].Rows.Count;
for (int i=0;i<count;i++)
{
string id_0,id_1,id_2,id_3;
id_0=ds.Tables["[Sheet1$]"].Rows[i]["哈哈"].ToString();
id_1=ds.Tables["[Sheet1$]"].Rows[i]["嘿嘿"].ToString();
id_2=ds.Tables["[Sheet1$]"].Rows[i]["西西"].ToString();
id_3=ds.Tables["[Sheet1$]"].Rows[i]["拉拉//哈哈"].ToString();
string excelsql="insert into excel([id_0],[id_1],[id_2],[id_3]) values ('"+id_0+"','"+id_1+"','"+id_2+"','"+id_3+"') ";
OleDbCommand mycommand1=new OleDbCommand(excelsql,thisconnection1);
mycommand1.ExecuteNonQuery();
}
Response.Write("更新成功");
thisconnection1.Close();
if (System.IO.File.Exists(@Server.MapPath("file/")+filename)==true)
{
Response.Write("true");
}
System.IO.File.Delete(@Server.MapPath("file/")+filename);
if (System.IO.File.Exists(@Server.MapPath("file/")+filename)==false)
{
Response.Write("false");
}
}
}
catch(Exception ex)
{
throw new Exception("你输入的格式有错,请重新输入");
}
}
Excel.Application m_objExcel;
Excel._Workbook m_objBook;
Excel.Workbooks m_objBooks;
Excel.Sheets m_objSheets;
Excel._Worksheet m_objSheet;m_objExcel = new Excel.Application();
m_objBooks = m_objExcel.Workbooks;
m_objBook = m_objBooks.Open("你的文件路径",miss,miss,miss,miss,miss,miss,miss,miss,miss,miss,miss,miss);
m_objSheets = m_objBook.Worksheets;
m_objSheets.Count;//sheet个数
m_objSheet = (_Worksheet)m_objSheets.get_Item(1);//第一个sheet
m_objSheet.Name;//当前sheet名称
楼主试试吧string mystring="Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '"+FileName+"';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection (mystring);
cnnxls.Open();
DataTable FromExcel=cnnxls.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
string TableName=FromExcel.TableName;
int RowCount=FromExcel.Rows.Count;
string SheetName="";
for(int i=0;i<RowCount;i++)
{
DataRow TempRow=FromExcel.Rows[i];
SheetName=TempRow[2].ToString();
}TempRow第二列里面的值就是sheet的名字,RowCount的值就是sheet的个数
得到了这两个以后,剩下的就是和普通的一样的做法了OleDbDataAdapter myDa =new OleDbDataAdapter("select * from ["+SheetName+"]",cnnxls);
DataSet myDs =new DataSet();
myDa.Fill(myDs,Test);
第一个读出来的是第4个sheet,
然后是第2个,再是第1个,最后是第3个
楼主主意一下,顺序不定
就象读sql里面读excel表一样,select *的时候,列的顺序和原顺序不同