xlApp = new Excel.ApplicationClass();
xlBook = xlApp.Application.Workbooks.Open(path,null,null,null,null,null,null,null,null,null,null,null,null,null,null);我在调用Workbooks.Open的时候报了一个{System.Runtime.InteropServices.COMException}的错:Exception from HRESULT: 0x800A03EC.大家知道该怎么解决吗?
xlBook = xlApp.Application.Workbooks.Open(path,null,null,null,null,null,null,null,null,null,null,null,null,null,null);我在调用Workbooks.Open的时候报了一个{System.Runtime.InteropServices.COMException}的错:Exception from HRESULT: 0x800A03EC.大家知道该怎么解决吗?
Excel.ApplicationClass exec= new Excel.ApplicationClass();
exec.Visible = true ;
Excel.Workbooks works= exec.Workbooks ;
Excel._Workbook workbook = works.Add (Excel.XlWBATemplate.xlWBATWorksheet);
Excel._Worksheet worksheet = (Excel._Worksheet)workbook.ActiveSheet;
具體內容﹐可參考﹕
http://dev.csdn.net/develop/article/79/79507.shtm
OleDbConnection objConn=null;
DataSet data=new DataSet();
try
{
string strConn= "Provider=Microsoft.Jet.OleDb.4.0;" + "data source="+FileName+ ";Extended Properties=Excel 8.0;";
objConn=new OleDbConnection(strConn);
objConn.Open();
DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
int SheetIndex =0;
try{ SheetIndex =Convert.ToInt32( textBox1.Text.Trim())-1;}
catch
{
SheetIndex =0;
}
string tableName=schemaTable.Rows[SheetIndex][2].ToString().Trim();
label2.Text =tableName; string strSql= "Select * From ["+tableName+"]";
OleDbCommand objCmd=new OleDbCommand(strSql, objConn);
OleDbDataAdapter sqlada=new OleDbDataAdapter();
sqlada.SelectCommand=objCmd;
sqlada.Fill(data,"BuyPrice");
objConn.Close();
}
catch
{
objConn.Close();
throw;
}
相关部分自己修改.
Object oMissing =System.Reflection.Missing.Value ;
Excel.Application excel =new Excel.ApplicationClass();
Excel.Workbook workBook;
Excel.Worksheet workSheet1;
excel.Visible=false;
workBook=excel.Workbooks.Add(oMissing); workSheet1 =(Excel.Worksheet)workBook.Worksheets .get_Item(1);
Excel.Range rangeTitle,range,rangeHeader;
range =workSheet1.get_Range("a2",oMissing);
不过我要实现的功能是把excel文件的数据读到dataset
所以会传一个文件的路径进来,然后打开这个文件,读到dataset
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + myFile.PostedFile.FileName + ";Extended Properties=Excel 8.0;" Dim dt_excel As DataTable Dim conn As OleDb.OleDbConnection Try
conn = New OleDb.OleDbConnection(strCon)
Dim myAdap As OleDb.OleDbDataAdapter
myAdap = New OleDb.OleDbDataAdapter("select * from [Sheet1$]", conn) Dim myDs As New DataSet
myAdap.Fill(myDs, "T_GZB")
dt_excel = myDs.Tables(0)
string strConn= "Provider=Microsoft.Jet.OleDb.4.0;" + "data source="+FileName+ ";Extended Properties=Excel 8.0;";
FILENAME用你的文件路径就行了
for(int i=0;i<schemaTable.Rows.Count;i++)
aaa+=schemaTable.Rows[i][2].ToString();
所有表名都在这了
我只是想第一个sheet,应该怎么办呢
schemaTable里的Rows[0]就是Agency,Rows[1]是Application,Rows[2]才是真正的第一个sheet:UserID所以Rows[0]取到的就不一定是文件里的sheet1
因为如果有一个restrictions的字段,其中有三个选项,那么在schemaTable.Rows中就会多出三条记录
可是schemaTable中的rows是按照sheet的名字的字母排序的
我只是想第一个sheet,应该怎么办呢
//*************************************************************
//創建一個數據Excel連接******************************************************************
string strCon = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+FilePath+";Extended Properties=Excel 8.0" ;
OleDbConnection myConn = new OleDbConnection ( strCon ) ;
string strCom = " SELECT * FROM [COQ$]" ;
//COQ是sheet名字
myConn.Open ();
名字不是固定的,不可以hardcode啊
Object oMissing =System.Reflection.Missing.Value ;
Excel.Application excel =new Excel.ApplicationClass();
//excel.Application.Workbooks.Open(FileName,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);
Excel.Workbook work =excel.Application.Workbooks.Open(FileName,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);
string aaa="";
foreach(Excel.Worksheet ws in work.Worksheets)
{
aaa+=ws.Name;
}
调用Excel.Application excel =new Excel.ApplicationClass();
很慢的,有performance的问题的
而且还要回收Excel
当excel文件中有restrictions的字段的时候,调用sqlada.Fill(data)的时候,restrictions的字段没有fill进DataSet
:(
当excel文件中有restrictions的字段的时候,调用sqlada.Fill(data)的时候,restrictions的字段没有fill进DataSet
:(
我也不懂;(
http://www.51team.com/document/DocumentDetail.aspx?ID=_ed75a587-429b-4ec3-a6da-9dcd6eb&doctype=0--------------------------------
www.51team.com
我已经说服了designer,sheet name可以hard code了
再次谢谢各位啦
还有个问题,就是当excel中有cell的format是Number时,这个cell的值读到dataset就是null。
各位知道改怎么解决吗?