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

时间:2022-03-22 16:59:56

代码如下:


/// <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; 
}