Summary of exporting data to excel using ASP. NET [Supplement]

 

// Open an empty excelmicrosoft. Office. InterOP. Excel. Application APP = New Microsoft. Office. InterOP. Excel. Application ();
App. workbooks. Add ( True );
App. Visible = True ;

 

// Open an existing Excel File

Microsoft. Office. InterOP. Excel. Application APP = New Microsoft. Office. InterOP. Excel. Application ();
App. workbooks. Add (path and file name );
App. Visible = True ;

// Read excel2003

Conexcel. connectionstring = " Provider = Microsoft. Jet. oledb.4.0; Data Source = " + Filepath + " ; Extended properties = \ "Excel 8.0; HDR = yes; IMEX = 1 \" " ;

 

// Read excel2007

Conexcel. connectionstring = " Provider = Microsoft. Ace. oledb.12.0; Data Source = " + Filepath + " ; Extended properties = \ "Excel 12.0; HDR = Yes \" " ;

 

// Set to prohibit the pop-up of the SAVE and overwrite inquiry box app. displayalerts = false;
App. alertbeforeoverwriting = false;

// Copy to the specified location. The default path is my document WB. savecopyas (@ "D: \ 111.xlsx ");

// Create an Excel file and save it to the specified path APP =NewMicrosoft. Office. InterOP. Excel. Application ();
WB = app. workbooks. Add (True);
App. cells [1,1] ="Test";

App. displayalerts =False;
App. alertbeforeoverwriting =False;
WB. savecopyas (@"C: \ 111.xlsx");
Close ();

// Directly read the Excel file datatable MYT = exceltodatatable ("D:/file/xinwuchang site information .xls","Sheet1");
String mystr = MYT. Rows [0] [0]. Tostring ();
This. Textbox1.text = mystr;

// Version check String Strpathresult = "" ;
String Strversionresult = "" ;
String Strkeyname = " Path " ;
Object Objresult = Null ;
Microsoft. win32.registryvaluekind regvaluekind;
Microsoft. win32.registrykey regkey =Null ;
Microsoft. win32.registrykey regsubkey = Null ;

Try
{
Regkey = Microsoft. win32.registry. localmachine;

If (Regsubkey = Null )
{ // Office97
Regsubkey = regkey. opensubkey ( @" Software/Microsoft \ Office \ 8.0 \ common \ installroot " , False );
Strversionresult = " Office97 " ;
Strkeyname = " Officebin " ;
}

If (Regsubkey = Null )
{ // Office2000
Regsubkey = regkey. opensubkey ( @" Software/Microsoft \ Office \ 9.0 \ common \ installroot " , False );
Strversionresult = " Office2000 " ;
Strkeyname = " Path " ;
}

If (Regsubkey = Null )
{ // Officexp
Regsubkey = regkey. opensubkey ( @" Software/Microsoft \ Office \ 10.0 \ common \ installroot " , False );
Strversionresult = " Officexp " ;
Strkeyname = " Path " ;
}

If (Regsubkey = Null )
{ // Office2003
Regsubkey = regkey. opensubkey ( @" Software/Microsoft \ Office \ 11.0 \ common \ installroot " , False );
Strversionresult = " Office2003 " ;
Strkeyname = " Path " ;
}

If (Regsubkey = Null )
{ // Office2007
Regsubkey = regkey. opensubkey ( @" Software/Microsoft \ Office \ 12.0 \ common \ installroot " , False );
Strversionresult = " Office2007 " ;
Strkeyname = " Path " ;
}

Objresult = regsubkey. getvalue (strkeyname );
Regvaluekind = regsubkey. getvaluekind (strkeyname );
If (Regvaluekind = Microsoft. win32.registryvaluekind. String)
{
Strpathresult = objresult. tostring ();
}
}
Catch (System. Security. securityexception ex)
{
Throw New System. Security. securityexception ( " You are not authorized to read the registry. " , Ex );
}
Catch (Exception ex)
{
Throw New Exception ( " An error occurred while reading the registry! " , Ex );
}
Finally
{

If (regkey! = null )
{< br> regkey. close ();
regkey = null ;
}

If(Regsubkey! =Null)
{
Regsubkey. Close ();
Regsubkey =Null;
}
}

MessageBox. Show (strversionresult );

// Obtain the number of rows APP =NewMicrosoft. Office. InterOP. Excel. Application ();
WB = app. workbooks. Open (path, missing. Value, missing. Value
, Missing. Value, missing. value );
Ws = (Microsoft. Office. InterOP. Excel. worksheet) WB. worksheets [1];

// Number of rows
String COUNT = ws. usedrange. Rows. Count. tostring ();

// Read the range R = (range) WS. cells [1, 1].
MessageBox. Show (R. value2.tostring ());

// Delete a row of data (the position of the first cell)

Public Void Delrow ( Int Row, String Fpath)
{
Openapp (fpath );
Range = ws. usedrange;
Int Columns = range. Columns. count;
For ( Int I = 0 ; I <columns; I ++)
{
(Range) WS. cells [row, I + 1 ]). Delete (missing. value );
}
WB. Save ();
Close ();
}

 

// Delete a column

Public Void Delcol ( Int Col, String Fpath)
{
Openapp (fpath );
Range = ws. usedrange;
Int Rows = range. Rows. count;
For ( Int I = 0 ; I <rows; I ++)
{
(Range) WS. cells [ 1 , Col]). Delete (missing. value );
}
WB. Save ();
Close ();
}

// delete a cell Public void delcell ( int row, int Col, string fpath)
{< br> openapp (fpath );
range = (range) WS. cells [row, Col];
range. delete (missing. value);
WB. save ();
close ();
}

In the Excel table, delete and clear are different. Clear only clears the content, while the cell is still deleting (DEL) delete a cell. The following data will be automatically deleted, which will cause typographical problems.

// Release resources
The COM component is referenced and is not hosted. Code Therefore, the code for releasing resources needs to be manually written. It is better to find a lot of code on the Internet. Public Void Close ()
{
If (APP = Null ) Return ;
If (WB! = Null )
{
WB. Close ( False , Missing. Value, missing. value );
Releasecom (WB );
WB = Null ;
}
This . App. Quit ();
Releasecom (APP );
APP = Null ;
GC. Collect ();
}
Static Private Void Releasecom ( Object O)
{
Try
{
System. runtime. interopservices. Marshal. releasecomobject (O ); // Force release an object
}
Catch {}
Finally
{
O = Null ;
}
}

// Merge cells {
WS. get_range (WS. cells [X1, Y1], WS. cells [X2, y2]). Merge (type. Missing );
}

Public void unitecells (string WS, int X1, int Y1, int X2, int Y2)
// Merge Cells
{
Getsheet (WS). get_range (getsheet (WS). cells [X1, Y1], getsheet (WS). cells [X2, y2]). Merge (type. Missing );

}

// Range can be used to obtain a cell or multiple cells.

Get a range ran = (range) WS. cells [row, Col];

 

Obtain multiple rows

Range ran = ws. get_range (cell in the upper left corner and cell in the lower right corner );

Range ran = ws. get_range (WS. cells [row, Col], WS. cells [row, Col]);

// Set the format range Ra = (range) WS. cells [row + 4, Col];
RA. horizontalalignment = Microsoft. Office. InterOP. Excel. xlhalign. xlhalignright;
RA. numberformatlocal = "#,## 0.00 ";

// Copy the selected ran area to the range ran = ws. get_range (WS. cells [3, 1], WS. cells [4, 9]) in the selected area of Ra.
Range Ra = ws. get_range (WS. cells [5, 1], WS. cells [6, 9]);
Ran. Copy (RA );