Asp. NET Excel download template, import, export operations _ practical skills

This article introduces the asp.net Excel downloads the template, the import, the export operation, for everybody reference, the concrete 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 filename
 response.addheader ("Content-length", file. Length.tostring ())//Show Download progress
 Response.ContentType = "application/ms-excel";//specifies that a stream that cannot be read by the client is returned and must be downloaded
 Response.WriteFile (file. FullName); Send 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
Excel data is imported into the database.  

protected void Btnimport_click (object sender, EventArgs e) {if (Fileupload1.hasfile = false)//determine whether to include a file {Response . Write (' <script>alert (' Please select Excel file!
 </script>);//no upload clicked on the import button return; String Isxls = Path.getextension (fileupload1.filename). ToString ().

 ToLower ()//Get file extension var extenlen = isxls.length; if (!isxls.contains (". xls"))//Determine if the Excel file {Response.Write ("<script>alert" only can select Excel file!)
 ') </script> ");
 Return  string filename = Fileupload1.filename; Gets the excle filename string savepath = Server.MapPath (("upfiles\\") + filename);//server.mappath get Virtual Server relative path string savePath2 =

 Server.MapPath (("upfiles\\")); if (!
 Directory.Exists (SAVEPATH2))///If Upfiles folder is not present, create {directory.createdirectory (savePath2); } fileupload1.saveas (Savepath);  SaveAs will upload the contents of the file 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 (' Excel table no data!
 ') </script> ");
 Return try {for (int i = 0; i < dt. Rows.Count; i++) {string ve = dt. rows[i]["car number"].
  ToString (); if (string.
  IsNullOrEmpty (ve)//due to the database in the car number can not be empty, so the form of the car number is empty skip this line {continue; //save in your own way into 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 ' ";
 The first line of the Hdr=yes Excel file is the column name rather than the data imex=1 data type conflicts 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.
The following code is largely without modification.  

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");//status bar header name 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"); The first row in Excel displays test sheet in the 1th to 2nd column.
 Addmergearea (area);
 CellTitle.Font.Height = 20 * 20; CellTitle.Font.Bold = true;//Sets the font for the header row to bold cellTitle.Font.FontFamily = fontfamilies.roman;//

 Sets the font for the header row to Fontfamilies.roman celltitle.horizontalalignment = horizontalalignments.centered;
 area = new MergeArea (2, 2, 1, 1); Celltitle = cells. Addvaluecell (2, 1, "car number"); The first column in the second row shows the number sheet.
 Addmergearea (area);
 CellTitle.Font.Bold = true; CellTitle.Font.Height = 16 * 16;
 cellTitle.Font.FontFamily = Fontfamilies.roman;
 Celltitle.horizontalalignment = horizontalalignments.centered;
 Celltitle.verticalalignment = verticalalignments.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 = verticalalignments.centered;
 Celltitle.toplinestyle = 1;
 Celltitle.bottomlinestyle = 1;
 Celltitle.leftlinestyle = 1;

 Celltitle.rightlinestyle = 1; #endregion var list = GetList (); Gets the data for (int i = 0; i < list. Count;
 i++) {rowindex++; Cell = cells. Addvaluecell (RowIndex, 1, list[i]. Vehicleno); Car number 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- the "microsoft.ace.oledb.12.0" provider is not registered on the local computer
01. Replace the platform with X86
02. Install AccessDatabaseEngine.exe (click to download)

5. Error- The server cannot set the content type after sending the HTTP headers
The ability to add ' global refresh ' to the export button. The example in this article is aspx to add the following code to the <asp:UpdatePanel> tag

Copy Code code as follows:

<Triggers>
<%--<asp:asyncpostbacktrigger controlid= ""/>--%> <%--local Refresh value Refresh UpdatePanel internal--%>
<asp:postbacktrigger controlid= "Btnexport"/> <%--all refresh--%> <%--2016 year July 1 Resolution Click export button Error " The server could not set the content type after sending HTTP headers error--%>
</Triggers>

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.