. Net Excel operations

Public void outputexcel (dataview DV, string Str)
{

// DV indicates the data to be output to excel, and STR indicates the title name.
GC. Collect ();
Application Excel;
Int rowindex = 4;
Int colindex = 1;

_ Workbook xbk;
_ Worksheet XST;

Excel = new applicationclass ();

Xbk = excel. workbooks. Add (true );

XST = (_ worksheet) xbk. activesheet;

//
// Obtain the title
//
Foreach (datacolumn Col in DV. Table. columns)
{
Colindex ++;
Excel. cells [4, colindex] = col. columnname;
XST. get_range (Excel. cells [4, colindex], Excel. cells [4, colindex]). horizontalalignment = xlvalign. xlvaligncenter; // set the title format to center and align
}

//
// Obtain the data in the table
//
Foreach (datarowview row in DV)
{
Rowindex ++;
Colindex = 1;
Foreach (datacolumn Col in DV. Table. columns)
{
Colindex ++;
If (Col. datatype = system. type. GetType ("system. datetime "))
{
Excel. cells [rowindex, colindex] = (convert. todatetime (row [col. columnname]. tostring (). tostring ("yyyy-mm-dd ");
XST. get_range (Excel. cells [rowindex, colindex], Excel. cells [rowindex, colindex]). horizontalalignment = xlvalign. xlvaligncenter; // set the Field Format of the date type to center-aligned.
}
Else
If (Col. datatype = system. type. GetType ("system. String "))
{
Excel. cells [rowindex, colindex] = "'" + row [col. columnname]. tostring ();
XST. get_range (Excel. cells [rowindex, colindex], Excel. cells [rowindex, colindex]). horizontalalignment = xlvalign. xlvaligncenter; // you can specify center-aligned fields.
}
Else
{
Excel. cells [rowindex, colindex] = row [col. columnname]. tostring ();
}
}
}
//
// Load a total row
//
Int rowsum = rowindex + 1;
Int colsum = 2;
Excel. cells [rowsum, 2] = "Total ";
XST. get_range (Excel. cells [rowsum, 2], Excel. cells [rowsum, 2]). horizontalalignment = xlhalign. xlhaligncenter;
//
// Set the color of the selected part
//
XST. get_range (Excel. cells [rowsum, colsum], Excel. cells [rowsum, colindex]). Select ();
XST. get_range (Excel. cells [rowsum, colsum], Excel. cells [rowsum, colindex]). Interior. colorindex = 19; // set to light yellow. There are 56 in total
//
// Obtain the title of the entire report
//
Excel. cells [2, 2] = STR;
//
// Set the title format of the entire report
//
XST. get_range (Excel. cells [2, 2], Excel. cells [2, 2]). Font. Bold = true;
XST. get_range (Excel. cells [2, 2], Excel. cells [2, 2]). Font. size = 22;
//
// Set the report table to the optimum width
//
XST. get_range (Excel. cells [4, 2], Excel. cells [rowsum, colindex]). Select ();
XST. get_range (Excel. cells [4, 2], Excel. cells [rowsum, colindex]). Columns. autofit ();
//
// Set the title of the entire report to center across Columns
//
XST. get_range (Excel. cells [2, 2], Excel. cells [2, colindex]). Select ();
XST. get_range (Excel. cells [2, 2], Excel. cells [2, colindex]). horizontalalignment = xlhalign. xlhaligncenter1_ssselection;
//
// Draw a border
//
XST. get_range (Excel. cells [4, 2], Excel. cells [rowsum, colindex]). Borders. linestyle = 1;
XST. get_range (Excel. cells [4, 2], Excel. cells [rowsum, 2]). borders [xlbordersindex. xledgeleft]. weight = xlborderweight. xlthick; // set the left line to bold
XST. get_range (Excel. cells [4, 2], Excel. cells [4, colindex]). borders [xlbordersindex. xledgetop]. weight = xlborderweight. xlthick; // set the upper line to bold
XST. get_range (Excel. cells [4, colindex], Excel. cells [rowsum, colindex]). borders [xlbordersindex. xledgeright]. weight = xlborderweight. xlthick; // you can specify the width of the right line.
XST. get_range (Excel. cells [rowsum, 2], Excel. cells [rowsum, colindex]). borders [xlbordersindex. xledgebottom]. weight = xlborderweight. xlthick; // you can specify the width of the lower line.
//
// Display Results
//
Excel. Visible = true;

// XST. export (server. mappath (". ") +" \ "+ this. xlfile. text + ". xls ", sheetexportactionenum. ssexportactionnone, Microsoft. office. interOP. owc. sheetexportformat. ssexporthtml );
Xbk. savecopyas (server. mappath (".") + "\" + this. xlfile. Text + ". xls ");

DS = NULL;
Xbk. Close (false, null, null );

Excel. Quit ();
System. runtime. interopservices. Marshal. releasecomobject (xbk );
System. runtime. interopservices. Marshal. releasecomobject (Excel );
System. runtime. interopservices. Marshal. releasecomobject (XST );
Xbk = NULL;
Excel = NULL;
XST = NULL;
GC. Collect ();
String Path = server. mappath (this. xlfile. Text + ". xls ");

System. Io. fileinfo file = new system. Io. fileinfo (PATH );
Response. Clear ();
Response. charset = "gb2312 ";
Response. contentencoding = system. Text. encoding. utf8;
// Add the header information and specify the default file name for the "download/Save as" dialog box
Response. addheader ("content-disposition", "attachment; filename =" + server. urlencode (file. Name ));
// Add header information and specify the file size so that the browser can display the download progress.
Response. addheader ("Content-Length", file. length. tostring ());

// Specify a stream that cannot be read by the client and must be downloaded.
Response. contenttype = "application/MS-excel ";

// Send the file stream to the client
Response. writefile (file. fullname );
// Stop page execution

Response. End ();
}