ASP. NET Excel template, import and export operations, asp. netexcel

ASP. NET Excel template, import and export operations, asp. netexcel

This article describes how to download templates, Import and Export Excel files in ASP. NET for your reference. The specific content is as follows:

1. Download Template Function

Protected void btnDownload_Click (object sender, EventArgs e) {var path = Server. mapPath ("upfiles \") + "test. xlt "); // upfiles-Folder test. xlt-file var name = "test. xlt "; try {var file = new FileInfo (path); Response. clear (); Response. charset = "GB2312"; Response. contentEncoding = System. text. encoding. UTF8; Response. addHeader ("Content-Disposition", "attachment; filename =" + Server. urlEncode (name); // header information, specifying the default file name Response. addHeader ("Content-Length", file. length. toString (); // display the download progress Response. contentType = "application/ms-excel"; // specify a stream that cannot be read by the client. The Response must be downloaded. writeFile (file. fullName); // sends the file stream to the client HttpContext. current. applicationInstance. completeRequest ();} catch (Exception ex) {Response. write ("<script> alert ('error:" + ex. message + ", please contact the Administrator as soon as possible ') </script> ");}}

2. Import Data
Import Excel data to the database.

Protected void btnImport_Click (object sender, EventArgs e) {if (FileUpload1.HasFile = false) // you can check whether a file is included. {Response. write ("<script> alert ('select an Excel file! ') </Script> "); // click the import button return if the file is not uploaded;} string isXls = Path. getExtension (FileUpload1.FileName ). toString (). toLower (); // get the file extension var extenLen = isXls. length; if (! IsXls. Contains (". xls") // determines whether it is an excel file {Response. Write ("<script> alert ('You can only select an Excel file! ') </Script> "); return;} string filename = FileUpload1.FileName; // obtain the Excle file name string savePath = Server. mapPath ("upfiles \") + filename); // Server. mapPath obtains the relative path of the Virtual Server string savePath2 = Server. mapPath ("upfiles \"); if (! Directory. exists (savePath2) // If the upfiles Folder does not exist, create {Directory. createDirectory (savePath2);} FileUpload1.SaveAs (savePath); // SaveAs saves the uploaded file content on the server var ds = ExcelSqlConnection (savePath, filename ); // convert Excel to DataSet var dtRows = ds. tables [0]. rows. count; var dt = ds. tables [0]; if (dtRows = 0) {Response. write ("<script> alert ('no data in the Excel table! ') </Script> "); return;} try {for (int I = 0; I <dt. rows. count; I ++) {string ve = dt. rows [I] ["vehicle ID"]. toString (); if (string. isNullOrEmpty (ve) // skip this line if the vehicle number in the table is empty because the car number in the database cannot be blank {continue ;} // save it in the database ADO/EF /... var model = new TEST (); // entity model. id = 1; model. ve = ve; model. name = dt. rows [I] ["name"]. toString (); model. update () ;}} catch (Exception ex) {Response. write ("<script> alert ('" + ex. message + "') </script>") ;}} private DataSet ExcelSqlConnection (string savePath, string tableName) {// string strCon = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "+ savePath +"; Extended Properties = 'excel 8.0; HDR = YES; IMEX = 1' "; string strCon =" Provider = Microsoft. ace. OLEDB.12.0; "+" data source = "+ savePath +"; Extended Properties = 'excel 12.0; HDR = Yes; IMEX = 1 '"; // HDR = YES the first line of the Excel file is the column name rather than the data IMEX = 1 which is required to avoid data type conflict var excelConn = new OleDbConnection (strCon); try {string strCom = string. format ("SELECT * FROM [Sheet1 $]"); excelConn. open (); OleDbDataAdapter myCommand = new OleDbDataAdapter (strCom, excelConn); DataSet ds = new DataSet (); myCommand. fill (ds, "[" + tableName + "$]"); excelConn. close (); return ds;} catch (Exception) {excelConn. close (); // Response. write ("<script> alert ('" + ex. message + "') </script>"); return null ;}}

3. export data to Excel
The plug-in uses MyXLS.
Most of the following code does not need to be changed.

Private void Export () {XlsDocument xls = new XlsDocument (); org. in2bits. myXls. cell cell; int rowIndex = 2; xls. fileName = DateTime. now. toString (). replace ("-",""). replace (":",""). replace ("", "") + HttpUtility. urlEncode ("TEST") + ". xls "; // TEST to change Worksheet sheet = xls. workbook. worksheets. addNamed ("TEST"); // name of the title of the status bar org. in2bits. myXls. cells cells = sheet. cells; # region header MergeArea area = new MergeArea (1, 1, 1, 2); // MergeArea (int rowMin, int rowMax, int colMin, int colMax) org. in2bits. myXls. cell cellTitle = cells. addValueCell (1, 1, "TEST"); // TEST sheet is displayed in columns 1st to 2 of the first row of Excel. addMergeArea (area); cellTitle. font. height = 20*20; cellTitle. font. bold = true; // set the font of the header line to Bold cellTitle. font. fontFamily = FontFamilies. roman; // set the FontFamilies font of the title line. roman cellTitle. horizontalAlignment = HorizontalAlignments. centered; area = new MergeArea (2, 2, 1, 1); cellTitle = cells. addValueCell (2, 1, "Car Number"); // the first column of the Second row shows the car number sheet. addMergeArea (area); cellTitle. font. bold = true; cellTitle. font. height = 16*16; cellTitle. font. fontFamily = FontFamilies. roman; cellTitle. horizontalAlignment = HorizontalAlignments. centered; cellTitle. verticalAlignment = verticalignments. centered; cellTitle. topLineStyle = 1; cellTitle. bottomLineStyle = 1; cellTitle. leftLineStyle = 1; cellTitle. rightLineStyle = 1; area = new MergeArea (2, 2, 2, 2); cellTitle = cells. addValueCell (2, 2, "name"); sheet. addMergeArea (area); cellTitle. font. bold = true; cellTitle. font. height = 16*16; cellTitle. font. fontFamily = FontFamilies. roman; cellTitle. horizontalAlignment = HorizontalAlignments. centered; cellTitle. verticalAlignment = verticalignments. centered; cellTitle. topLineStyle = 1; cellTitle. bottomLineStyle = 1; cellTitle. leftLineStyle = 1; cellTitle. rightLineStyle = 1; # endregion var list = GetList (); // get data for (int I = 0; I <list. count; I ++) {rowIndex ++; cell = cells. addValueCell (rowIndex, 1, list [I]. vehicleNO); // vehicle ID cell. topLineStyle = 1; cell. bottomLineStyle = 1; cell. leftLineStyle = 1; cell. rightLineStyle = 1; cell = cells. addValueCell (rowIndex, 2, list [I]. name); // Name cell. topLineStyle = 1; cell. bottomLineStyle = 1; cell. leftLineStyle = 1; cell. rightLineStyle = 1;} xls. send ();}

4. Error-Microsoft. ACE. OLEDB.12.0 provider not registered on the Local Computer
01. Change platform to X86
02. Install AccessDatabaseEngine.exe (click to download)

5. Error-The server cannot set the content type after sending the HTTP Header
Add 'Global refresh 'to the export button. In this example, aspx adds the following code to the <asp: UpdatePanel> label.
 Copy codeThe Code is as follows: <Triggers>
<% -- <Asp: AsyncPostBackTrigger ControlID = ""/> -- %> <% -- partial refresh value refresh inside UpdatePanel -- %>
<Asp: postBackTrigger ControlID = "btnExport"/> <% -- refresh all -- %> <% -- the error "the server cannot set the content type after sending the HTTP header" is reported when you click Export on April 9, July 1, 2016 -- %>
</Triggers>

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.