ASP.net and Excel Interoperability Implementation code _ Practical skills

Copy Code code as follows:

<summary>
Export data from a DataTable to a specified Excel file
</summary>
<param name= "page" >web Page object </param>
<param name= "tab" > DataTable object containing exported data </param>
<param name= "filename" >excel file name </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;//Download Data every 100k
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 a compressed block
Response.BinaryWrite (ReadData);
Fpos+=size;
}
Fs.close ();
System.io.file.delete (FullPath);
return true;
}
Catch
{
return false;
}
}

<summary>
Converts the data in the specified Excel file into a DataTable object for further processing by the application
</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 the data connection is available
{
Conn.Open ();
Conn.close ();
Canopen=true;
}
catch{}

if (CANopen)
{
try//try to read 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 data fails, the name of the worksheet is extracted from the file, and then the data is read
{
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>
Converts data from the specified HTML string into a DataTable object--processing according to special characters such as "<tr><td>"
</summary>
<param name= "tmphtml" >html string </param>
<returns></returns>
private static DataTable getdatatablefromstring (String tmphtml)
{
String tmpstr=tmphtml;
DataTable tb=new DataTable ();
Take care of this string first, delete the first <tr> before the last one </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 the original string contains the delimiter "^" Replace it first
if (Tmpstr.indexof (separator.tostring ()) >-1)
{
Existssparator=true;
Tmpstr=tmpstr.replace ("^", "^$&^");
}

Split by "</tr>" first
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>
Excludes the specified object from the specified HTML string
</summary>
<param name= "tmphtml" >html string </param>
<param name= "Remove" > objects that need to be removed-for example, "<font>" to eliminate "<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>
Used only for Method removestring ()
</summary>
<returns></returns>
private static string Removestringhead (String tmphtml,string Remove)
{
For easy annotation, suppose the input parameter remove= "<font>"
if (remove.length<1) return tmphtml;//parameter remove is null: Returns not processed
if ((remove.substring (0,1)!= "<") | | (Remove.substring (remove.length-1)!= ">")) Return tmphtml;//parameter remove is not <????? Do not process return

int Indexs=tmphtml.indexof (Remove.replace (">"));//Find position of "<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>
Reads the name of the first sheet in the specified Excel file
</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 (one), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0),
Convert.tobyte (one), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0), Convert.tobyte (0),
Convert.tobyte (Convert.tobyte), 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>
Used only for Method 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;
}