/// <summary>
/// will datatable Is exported to the specified excel In the file
/// </summary>
/// <param name="page">web The page object </param>
/// <param name="tab"> Containing the exported data datatable object </param>
/// <param name="filename">excel Name of file </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;// every 100k Download data at the same time
byte[] readdata = new byte[size];// Specifies the size of the buffer
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);// Read in 1 A compression block
response.binarywrite(readdata);
fpos+=size;
}
fs.close();
system.io.file.delete(fullpath);
return true;
}
catch
{
return false;
}
}
/// <summary>
/// Will specify excel The data in the file is converted to datatable Object to feed the application 1 Step process
/// </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// Try to see if the data connection is available
{
conn.open();
conn.close();
canopen=true;
}
catch{}
if(canopen)
{
try// Try to read in the data if the data connection can be opened
{
oledbcommand myoledbcommand = new oledbcommand("select * from [sheet1$]",conn);
oledbdataadapter mydata = new oledbdataadapter(myoledbcommand);
mydata.fill(rs);
conn.close();
}
catch// If the data connection can be opened but the read in data fails, the name of the worksheet is extracted from the file and the data is read in
{
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>
/// Will specify html String of data converted to datatable object -- According to the" <tr><td> "And other special characters for processing
/// </summary>
/// <param name="tmphtml">html string </param>
/// <returns></returns>
private static datatable getdatatablefromstring(string tmphtml)
{
string tmpstr=tmphtml;
datatable tb=new datatable();
// To deal with 1 Under this string, delete the first 1 a <tr> Before and after 1 a </tr> After that
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 the original string contains the delimiter" ^ "Replace it first
if(tmpstr.indexof(separator.tostring())>-1)
{
existssparator=true;
tmpstr=tmpstr.replace("^","^$&^");
}
// According to the first" </tr> "Split
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>
/// From the specified html Culls the specified object from the string
/// </summary>
/// <param name="tmphtml">html string </param>
/// <param name="remove"> The object to be culled -- For example, the input "<font>" Is to eliminate "<font ???????>" and "</font>>"</param>
/// <returns></returns>
public static string removestring(string tmphtml,string remove)
{
tmphtml=tmphtml.replace(remove.replace("<","</"),"");
tmphtml=removestringhead(tmphtml,remove);
return tmphtml;
}
/// <summary>
/// Only for method removestring() use
/// </summary>
/// <returns></returns>
private static string removestringhead(string tmphtml,string remove)
{
// For annotation purposes, assume the input parameters remove="<font>"
if(remove.length<1) return tmphtml;// parameter remove Null: does not process the return
if((remove.substring(0,1)!="<")||(remove.substring(remove.length-1)!=">")) return tmphtml;// parameter remove not <?????> : does not process return
int indexs=tmphtml.indexof(remove.replace(">",""));// Looking for" <font The location of the"
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>
/// Will specify excel The file reads the first 1 The name of the sheet
/// </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>
/// Only for method getsheetname() use
/// </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;
}