说老实话导入导出excel已经是个很古老的话题,好象是不太新意的了,但现在我真的在它的身上花费了我三天的时间。
第一讲:excel的一个文件多个sheet表的导入,无模板,换句话说就是空模板,建立一个空excel。
这个excel的应用,
我的程序主要是在vs2003的环境下做的:
private void Button1_ServerClick(object sender, System.EventArgs e)//取值
{
string ExeclFile=\"\";
Object oMissing =System.Reflection.Missing.Value ; //这是excel特有的类型
Excel.Application objExcelApp=new Excel.Application();
ExeclFile=Server .MapPath(\"../upload_temp/test0925.xls\");//要连接的excel文件
Excel.Application excel =new Excel.ApplicationClass();
Excel.Workbook work =excel.Application.Workbooks.Open(ExeclFile,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing); //打开当前excel文件
string name1=\"\";
foreach(Excel.Worksheet ws in work.Worksheets) //取出不同sheet表的名称
{
name1+=ws.Name+\",\";
}
//调运函数插入数据库
string[] names=name1.Split(',');
DataSet ds=new DataSet();
string insertstr0=\"insert into yang_test1(testid1,testname1)values(@testid1,@testname1)\";
string insertstr1=\"insert into yang_test2(testid2,testname2)values(@testid2,@testname2)\";
//string insertstr2=\"insert into yang_test3(testid3,testname3,testage3)values(@testid3,@testname3,@testage3)\";
string insertstr2=\"insert into yang_test3(testid3,testname3)values(@testid3,@testname3)\";
bool ifis=false;
for(int ii=0;ii<names.Length;ii++)
{
//[Sheet1$]
if(names[ii]!=\"\"){names[ii]=\"[\"+names[ii]+\"$]\";ds=conExcel(ExeclFile,names[ii]);}
if(ds!=null)
{
if(ii==0){ifis=ifexceltosql1(ds,insertstr0);Response .Write (\"ok1\");}
if(ii==1){ifis=ifexceltosql2(ds,insertstr1);Response .Write (\"ok2\");}
if(ii==2){ifis=ifexceltosql3(ds,insertstr2);Response .Write (\"ok3\");}
if(ifis==false)return;
}
}
objExcelApp.Quit() ;
}
public DataSet conExcel(string fpath,string name1)//取到excel某表的值
{
string strConn = \"rovider=Microsoft.Jet.OLEDB.4.0;Data Source='\"+fpath+\"';\"
+\"Extended Properties=Excel 8.0;\";
try
{
using (OleDbDataAdapter command = new OleDbDataAdapter(\"SELECT * FROM \"+name1+\"\", strConn))
{
using (DataSet dataSet1 = new DataSet())
{
command.Fill(dataSet1);
return dataSet1;
}
}
}
catch(Exception xx)
{
Response .Write (xx);
return null;
}
} |