Excel (.xls) Document for exporting/importing rules under ASP. NET

Data export in datatable Excel document
/// <Summary>
/// Export the data in the datatable to the specified Excel document
/// </Summary>
/// <Param name = "page"> Web Page Object </param>
/// <Param name = "tab"> datatable object containing exported data </param>
/// <Param name = "FILENAME"> name of the Excel document </param>
Public static void Export (system. Web. UI. Page, system. Data. datatable tab, string filename)
{
System. Web. httpresponse = page. response;
System. Web. UI. webcontrols. 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.html textwriter hW = new system.web.ui.html textwriter (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 at the same time every k
byte [] readdata = new byte [size]; // specify the buffer size
If (size> FLEN) size = convert. toint32 (FLEN);
long FPOs = 0;
bool ise ND = false;
while (! Isend)
{< br> If (FPOs + size)> FLEN)
{< br> size = convert. toint32 (FLEN-FPOs);
readdata = new byte [size];
isend = true;
}< br> FS. read (readdata, 0, size); // read a compression block
response. binarywrite (readdata);
FPOs + = size;
}< br> FS. close ();
system. io. file. delete (fullpath);
return true;
}< br> catch
{< br> return false;
}< BR >}

Converts data in a specified Excel file to a able
/// <Summary>
/// Convert the data in the specified Excel document to a able object for further processing by the program
/// </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 whether the data connection is available
{
Conn. open ();
Conn. Close ();
CANOPEN = true;
}
Catch {}

If (CANOPEN)
{
Try // if the data connection can be opened, try to read data
{
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 Data Reading fails, extract the worksheet name from the document and then read the data
{
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>
/// Convert the data of the specified HTML string to a able object. The data is processed based on 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 ();
// Process the string and delete the part after the first <tr> is merged with the last </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 ("^ ");

// Replace the separator "^" in the original string
If (tmpstr. indexof (separator. tostring ()>-1)
{
Existssparator = true;
Tmpstr = tmpstr. Replace ("^", "^ $ & ^ ");
}

// Split the Image Based on "</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;
}

///

/// remove the specified object from the specified HTML string
///
/// HTML string
// object to be excluded-for example, input "" remove " "And" "
//
Public static string removestring (string tmphtml, string remove)
{< br> tmphtml = tmphtml. replace (remove. replace ("<", " tmphtml = removestringhead (tmphtml, remove);
return tmphtml;
}< BR >///
// The removestring () method is used only () use
///
///
Private Static string removestringhead (string tmph TML, string remove)
{< br> // for convenience of annotation, assume that the input parameter remove = ""
If (remove. length <1) return tmphtml; // If (remove. substring (0, 1 )! = "<") | (Remove. substring (remove. Length-1 )! = ">") Return tmphtml; // The parameter remove is not : No processing returns

Int indexs = tmphtml. indexof (remove. Replace (">", ""); // find the location 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>
/// Read the name of the first worksheet in the specified Excel document
/// </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 used by the getsheetname () method
/// </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;
}