Import and Export Standard Excel files in ASP. NET

The Import and Export Methods demonstrated in this Article do not use the Excel COM component, but use the original ASP. NET OleDb driver, which makes ASP.. NET applications support importing and exporting calibrated Excel files even if they are deployed on servers where Excel is not installed.

The Import and Export Methods demonstrated in this Article do not use the Excel COM component, but use the original ASP. NET OleDb driver, which makes ASP.. NET applications support importing and exporting calibrated Excel files even if they are deployed on servers where Excel is not installed.

1. Export

There are a lot of questions about ASP on the Internet. most of the methods for exporting Excel files by using the. NET application program are to set the Content-Type of the Response object to "application/ms-excel" and output data as strings for users to download (refer:Http://blog.csdn.net/jilm168/archive/2007/11/06/1869118.aspx).

Although this method can indeed achieve the purpose of exporting Excel files, the exported Excel files are not standard. If you use NotePad to open a file exported using this method, you will find that the file is a plain text file, which is secondary, because the powerful Excel file is absolutely capable of identifying and displaying such "pseudo" Excel files, but if our application needs to support import, this file cannot be correctly recognized by the program.

To export a standard Excel file, you can first prepare a standard Excel file as an export template. When exporting data, you must first create a copy of the template and use the OleDb driver to write data to the copy, output the binary byte stream of this copy to the client for users to download, and then delete the copy.

How to create an Excel file template? You can create a new "Microsoft Excel worksheet" on the machine where Excel is installed, set the required fields in the first row of the worksheet, and select the entire table, right-click and select "set cell format", select "text" in the "classification" List of the "Number" tab, click "OK", and save the file. The cell format is set to "text" to prevent some data formats from being recognized when data is imported in the future.

With this template, we can start writing code to export data. The template file used in this example is stored in the site root directory named includemo.xls. It contains three columns: "ID", "name", and "Birthday ".

Excel_template

// Create a copy based on the template file
String filePath = Server. MapPath ("~ /"+ Guid. NewGuid (). ToString () +". xls ");
File. Copy (Server. MapPath ("~ /Demo.xls "), filePath );
// Use the OleDb driver to connect to the replica
OleDbConnection conn = new OleDbConnection ("Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + filePath + "; Extended Properties = Excel 8.0 ;");
Using (conn)
{
Conn. Open ();
// Add record
OleDbCommand cmd = new OleDbCommand ("insert into [Sheet1 $] ([ID], [Name], [Birthday]) VALUES (@ Id, @ Name, @ Birthday )", conn );
Cmd. Parameters. AddWithValue ("@ Id", "1 ");
Cmd. Parameters. AddWithValue ("@ Name", "Hsu Yencheng ");
Cmd. Parameters. AddWithValue ("@ Birthday", "1981-10-13 ");
Cmd. ExecuteNonQuery ();
}
// Output the binary byte stream of the copy
Response. ContentType = "application/ms-excel ";
Response. AppendHeader ("Content-Disposition", "attachment?filename=info.xls ");
Response. BinaryWrite (File. ReadAllBytes (filePath ));
// Delete a copy
File. Delete (filePath );

 

Ii. Import

Compared with export, the import code is much simpler:
String filePath = Server. MapPath ("~ /Info.xls ");
OleDbDataAdapter da = new OleDbDataAdapter (
"SELECT * FROM [Sheet1 $]",
"Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + filePath + "; Extended Properties = Excel 8.0 ");
DataTable dt = new DataTable ();
Da. Fill (dt );
// Then you can do whatever you want for dt ......
In the website code, we imported the website-specific info.xls. During the import, we still use the OleDb driver to access the Excel file. Remind the reader again that this method only supports importing standard Excel files, which is also the reason for using prepared Excel template files in the export section above.

In addition, the preceding demo is only for files in Excel 2000-2003 format. If you want to import or export an Excel 2007 file (*. xlsx), you need to change the connection string to (refer:Http://www.connectionstrings.com/):
Provider = Microsoft. ACE. OLEDB.12.0; Data Source=info.xlsx; Extended Properties = "Excel 12.0 Xml; HDR = YES ";
Provider = Microsoft. ACE. OLEDB.12.0; Data Source=info.xlsx; Extended Properties = "Excel 12.0 Xml; HDR = YES ";

----- After practice, the above method is feasible, but there is a problem: the execution efficiency is very low. When more than 255 rows of data are exported, it is particularly slow (the MS Limit is found on the Internet ). solution: directly concatenate a string into an insert SQL statement without using the parameter mode. The execution speed is very fast, and the export speed is very fast, and the execution speed exceeds 255, I have only tested how to generate and export 2000 SQL string statements. The file size is 3 MB, which is not slow and acceptable. using the original method, the system felt slow in 256 cases.

In OLEDB mode, you can use create table to generate an EXCEL worksheet, such as sheet 1 $, and then INSERT data, which proves that it is feasible. in this way, only one blank EXCEL file is required for the template file. high flexibility.