asp.net与excel互操作实现代码

代码如下:

/// <summary> 
/// 将datatable中的数据导出到指定的excel文件中 
/// </summary> 
/// <param name="page">web页面对象</param> 
/// <param name="tab">包含被导出数据的datatable对象</param> 
/// <param name="filename">excel文件的名称</param> 
public static void export(system.web.ui.page page,system.data.datatable tab,string filename) 
{ 
system.web.httpresponse httpresponse = page.response; 
system.web.ui.webcontrols.datagrid datagrid=new system.web.ui.webcontrols.datagrid(); 
datagrid.datasource=tab.defaultview; 
datagrid.allowpaging = false; 
datagrid.headerstyle.backcolor = system.drawing.color.green; 
datagrid.headerstyle.horizontalalign = horizontalalign.center; 
datagrid.headerstyle.font.bold = true; 
datagrid.databind(); 
httpresponse.appendheader("content-disposition","attachment;filename="+httputility.urlencode(filename,system.text.encoding.utf8)); //filename="*.xls"; 
httpresponse.contentencoding=system.text.encoding.getencoding("gb2312"); 
httpresponse.contenttype ="application/ms-excel"; 
system.io.stringwriter tw = new system.io.stringwriter() ; 
system.web.ui.htmltextwriter hw = new system.web.ui.htmltextwriter (tw); 
datagrid.rendercontrol(hw); 

string filepath = page.server.mappath("..")+"\\files\\" +filename; 
system.io.streamwriter sw = system.io.file.createtext(filepath); 
sw.write(tw.tostring()); 
sw.close(); 

downfile(httpresponse,filename,filepath); 

httpresponse.end(); 
} 
private static bool downfile(system.web.httpresponse response,string filename,string fullpath) 
{ 
try 
{ 
response.contenttype = "application/octet-stream"; 

response.appendheader("content-disposition","attachment;filename=" + 
httputility.urlencode(filename,system.text.encoding.utf8) + ";charset=gb2312"); 
system.io.filestream fs= system.io.file.openread(fullpath); 
long flen=fs.length; 
int size=102400;//每100k同时下载数据 
byte[] readdata = new byte[size];//指定缓冲区的大小 
if(size>flen)size=convert.toint32(flen); 
long fpos=0; 
bool isend=false; 
while (!isend) 
{ 
if((fpos+size)>flen) 
{ 
size=convert.toint32(flen-fpos); 
readdata = new byte[size]; 
isend=true; 
} 
fs.read(readdata, 0, size);//读入一个压缩块 
response.binarywrite(readdata); 
fpos+=size; 
} 
fs.close(); 
system.io.file.delete(fullpath); 
return true; 
} 
catch 
{ 
return false; 
} 
} 

/// <summary> 
/// 将指定excel文件中的数据转换成datatable对象，供应用程序进一步处理 
/// </summary> 
/// <param name="filepath"></param> 
/// <returns></returns> 
public static system.data.datatable import(string filepath) 
{ 
system.data.datatable rs = new system.data.datatable(); 
bool canopen=false; 

oledbconnection conn = new oledbconnection("provider=microsoft.jet.oledb.4.0;"+ 
"data source=" + filepath + ";" + 
"extended properties=\"excel 8.0;\""); 

try//尝试数据连接是否可用 
{ 
conn.open(); 
conn.close(); 
canopen=true; 
} 
catch{} 

if(canopen) 
{ 
try//如果数据连接可以打开则尝试读入数据 
{ 
oledbcommand myoledbcommand = new oledbcommand("select * from [sheet1$]",conn); 
oledbdataadapter mydata = new oledbdataadapter(myoledbcommand); 
mydata.fill(rs); 
conn.close(); 
} 
catch//如果数据连接可以打开但是读入数据失败，则从文件中提取出工作表的名称，再读入数据 
{ 
string sheetname=getsheetname(filepath); 
if(sheetname.length>0) 
{ 
oledbcommand myoledbcommand = new oledbcommand("select * from ["+sheetname+"$]",conn); 
oledbdataadapter mydata = new oledbdataadapter(myoledbcommand); 
mydata.fill(rs); 
conn.close(); 
} 
} 
} 
else 
{ 
system.io.streamreader tmpstream=file.opentext(filepath); 
string tmpstr=tmpstream.readtoend(); 
tmpstream.close(); 
rs=getdatatablefromstring(tmpstr); 
tmpstr=""; 
} 
return rs; 
} 
/// <summary> 
/// 将指定html字符串的数据转换成datatable对象 --根据“<tr><td>”等特殊字符进行处理 
/// </summary> 
/// <param name="tmphtml">html字符串</param> 
/// <returns></returns> 
private static datatable getdatatablefromstring(string tmphtml) 
{ 
string tmpstr=tmphtml; 
datatable tb=new datatable(); 
//先处理一下这个字符串，删除第一个<tr>之前合最后一个</tr>之后的部分 
int index=tmpstr.indexof("<tr"); 
if(index>-1) 
tmpstr=tmpstr.substring(index); 
else 
return tb; 

index=tmpstr.lastindexof("</tr>"); 
if(index>-1) 
tmpstr=tmpstr.substring(0,index+5); 
else 
return tb; 

bool existssparator=false; 
char separator=convert.tochar("^"); 

//如果原字符串中包含分隔符“^”则先把它替换掉 
if(tmpstr.indexof(separator.tostring())>-1) 
{ 
existssparator=true; 
tmpstr=tmpstr.replace("^","^$&^"); 
} 

//先根据“</tr>”分拆 
string[] tmprow=tmpstr.replace("</tr>","^").split(separator); 

for(int i=0;i<tmprow.length-1;i++) 
{ 
datarow newrow=tb.newrow(); 

string tmpstri=tmprow[i]; 
if(tmpstri.indexof("<tr")>-1) 
{ 
tmpstri=tmpstri.substring(tmpstri.indexof("<tr")); 
if(tmpstri.indexof("display:none")<0||tmpstri.indexof("display:none")>tmpstri.indexof(">")) 
{ 
tmpstri=tmpstri.replace("</td>","^"); 
string[] tmpfield=tmpstri.split(separator); 

for(int j=0;j<tmpfield.length-1;j++) 
{ 
tmpfield[j]=removestring(tmpfield[j],"<font>"); 
index=tmpfield[j].lastindexof(">")+1; 
if(index>0) 
{ 
string field=tmpfield[j].substring(index,tmpfield[j].length-index); 
if(existssparator) field=field.replace("^$&^","^"); 
if(i==0) 
{ 
string tmpfieldname=field; 
int sn=1; 
while(tb.columns.contains(tmpfieldname)) 
{ 
tmpfieldname=field+sn.tostring(); 
sn+=1; 
} 
tb.columns.add(tmpfieldname); 
} 
else 
{ 
newrow[j]=field; 
} 
}//end of if(index>0) 
} 

if(i>0) 
tb.rows.add(newrow); 
} 
} 
} 

tb.acceptchanges(); 
return tb; 
} 

/// <summary> 
/// 从指定html字符串中剔除指定的对象 
/// </summary> 
/// <param name="tmphtml">html字符串</param> 
/// <param name="remove">需要剔除的对象--例如输入"<font>"则剔除"<font ???????>"和"</font>>"</param> 
/// <returns></returns> 
public static string removestring(string tmphtml,string remove) 
{ 
tmphtml=tmphtml.replace(remove.replace("<","</"),""); 
tmphtml=removestringhead(tmphtml,remove); 
return tmphtml; 
} 
/// <summary> 
/// 只供方法removestring()使用 
/// </summary> 
/// <returns></returns> 
private static string removestringhead(string tmphtml,string remove) 
{ 
//为了方便注释，假设输入参数remove="<font>" 
if(remove.length<1) return tmphtml;//参数remove为空：不处理返回 
if((remove.substring(0,1)!="<")||(remove.substring(remove.length-1)!=">")) return tmphtml;//参数remove不是<?????>：不处理返回 

int indexs=tmphtml.indexof(remove.replace(">",""));//查找“<font”的位置 
int indexe=-1; 
if(indexs>-1) 
{ 
string tmpright=tmphtml.substring(indexs,tmphtml.length-indexs); 
indexe=tmpright.indexof(">"); 
if(indexe>-1) 
tmphtml=tmphtml.substring(0,indexs)+tmphtml.substring(indexs+indexe+1); 
if(tmphtml.indexof(remove.replace(">",""))>-1) 
tmphtml=removestringhead(tmphtml,remove); 
} 
return tmphtml; 
} 

/// <summary> 
/// 将指定excel文件中读取第一张工作表的名称 
/// </summary> 
/// <param name="filepath"></param> 
/// <returns></returns> 
private static string getsheetname(string filepath) 
{ 
string sheetname=""; 

system.io.filestream tmpstream=file.openread(filepath); 
byte[] filebyte=new byte[tmpstream.length]; 
tmpstream.read(filebyte,0,filebyte.length); 
tmpstream.close(); 

byte[] tmpbyte=new byte[]{convert.tobyte(11),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0), 
convert.tobyte(11),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0),convert.tobyte(0), 
convert.tobyte(30),convert.tobyte(16),convert.tobyte(0),convert.tobyte(0)}; 

int index=getsheetindex(filebyte,tmpbyte); 
if(index>-1) 
{ 

index+=16+12; 
system.collections.arraylist sheetnamelist=new system.collections.arraylist(); 

for(int i=index;i<filebyte.length-1;i++) 
{ 
byte temp=filebyte[i]; 
if(temp!=convert.tobyte(0)) 
sheetnamelist.add(temp); 
else 
break; 
} 
byte[] sheetnamebyte=new byte[sheetnamelist.count]; 
for(int i=0;i<sheetnamelist.count;i++) 
sheetnamebyte[i]=convert.tobyte(sheetnamelist[i]); 

sheetname=system.text.encoding.default.getstring(sheetnamebyte); 
} 
return sheetname; 
} 
/// <summary> 
/// 只供方法getsheetname()使用 
/// </summary> 
/// <returns></returns> 
private static int getsheetindex(byte[] findtarget,byte[] finditem) 
{ 
int index=-1; 

int finditemlength=finditem.length; 
if(finditemlength<1) return -1; 
int findtargetlength=findtarget.length; 
if((findtargetlength-1)<finditemlength) return -1; 

for(int i=findtargetlength-finditemlength-1;i>-1;i--) 
{ 
system.collections.arraylist tmplist=new system.collections.arraylist(); 
int find=0; 
for(int j=0;j<finditemlength;j++) 
{ 
if(findtarget[i+j]==finditem[j]) find+=1; 
} 
if(find==finditemlength) 
{ 
index=i; 
break; 
} 
} 
return index; 
}