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

Http://delia.org.ru/ArticleView/2005-9-6/Article_View_153237.Htm

/// <Summary>
/// Export the data in the datatable to the specified Excel File
/// </Summary>
/// <Param name = page> Web Page Object </param>
/// <Param name = tab> datatable object containing exported data </param>
/// <Param name = FILENAME> name of the Excel file </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. 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 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 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;
}
}

//

// converts the data in the specified Excel file to a able object, further processing by the application
///
///
///
Public static system. data. datatable import (string filepath)
{< br> 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
{< br> Conn. open ();
Conn. close ();
CANOPEN = true;
}< br> catch {}

If (CANOPEN)
{
Try // if the data connection can be opened, try to read the 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 file 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 (^ );

// If the original string contains the separator "^", replace it
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;
}

/// <Summary>
/// Remove the specified object from the specified HTML string
/// </Summary>
/// <Param name = tmphtml> HTML string </param>
/// <Param name = remove> objects to be excluded -- for example, if <font> is input, <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 used by the removestring () method
/// </Summary>
/// <Returns> </returns>
Private Static string removestringhead (string tmphtml, string remove)
{
// For convenience of annotation, assume that the input parameter remove = <font>
If (remove. Length <1) return tmphtml; // The parameter remove is null: no processing is returned.
If (remove. substring (0, 1 )! = <) | (Remove. substring (remove. Length-1 )! =>) Return tmphtml; // The parameter remove is not <???> : Do not process returned results

Int indexs = tmphtml. indexof (remove. Replace (>,); // search for the "<font" Location
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;
}

///

// read the name of the first worksheet from the specified Excel file
///
/ //
//
Private Static string getsheetname (string filepath)
{< br> 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 --)
{< br> system. collections. arraylist tmplist = new system. collections. arraylist ();
int find = 0;
for (Int J = 0; j {< br> If (findtarget [I + J] = finditem [J]) Find + = 1;
}< br> If (find = finditemlength)
{< br> Index = I;
break;
}< BR >}< br> Return Index;
}