ASP. NET Excel download template import export operations

  • 2021-08-03 10:06:07
  • OfStack

This article introduces ASP. NET Excel download template, import, export operation, for your reference, the specific content is as follows

1. Download template functionality


protected void btnDownload_Click(object sender, EventArgs e)
{
 var path = Server.MapPath(("upfiles\\") + "test.xlt"); //upfiles- Folder  test.xlt- Documents 
 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());// Show download progress 
 Response.ContentType = "application/ms-excel"; //  Specifies that the returned is 1 Streams that cannot be read by the client 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(' Errors :" + 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 1 Files 
 {
 Response.Write("<script>alert(' Please choose Excel Files! ')</script>");// Click the import button without uploading 
 return;
 }
 string isXls = Path.GetExtension(FileUpload1.FileName).ToString().ToLower();// Get the extension of the file 
 var extenLen = isXls.Length;

 if (!isXls.Contains(".xls")) // Judge whether   Yes excel Documents 
 {
 Response.Write("<script>alert(' You can only select Excel Files! ')</script>");
 return;
 }

 string filename = FileUpload1.FileName;  // Get Excle Filename 
 string savePath = Server.MapPath(("upfiles\\") + filename);//Server.MapPath  Get the relative path of virtual server 
 string savePath2 = Server.MapPath(("upfiles\\"));

 if (!Directory.Exists(savePath2)) // If it doesn't exist upfiles Folder is created 
 {
 Directory.CreateDirectory(savePath2);
 }
 FileUpload1.SaveAs(savePath); //SaveAs  Save the uploaded file contents on the server 
 var ds = ExcelSqlConnection(savePath, filename);  // Will Excel Turn into DataSet
 var dtRows = ds.Tables[0].Rows.Count;
 var dt = ds.Tables[0];
 if (dtRows == 0)
 {
 Response.Write("<script>alert('Excel Table has 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)) // Because the car number in the database cannot be empty   So skip this row if the car number in the table is empty 
  {
  continue;
  }
  // Save to the database in your own way 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 Excel Article 1 of the document 1 Rows are column names, not data  IMEX=1 Data type conflicts can be avoided 
 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 adopts MyXLS.
Most of the following codes need not 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");// Status bar title 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"); //Excel  No. 1 1 Line number 1 To 2 Column display TEST
 sheet.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 font of the header row to FontFamilies.Roman
 cellTitle.HorizontalAlignment = HorizontalAlignments.Centered;

 area = new MergeArea(2, 2, 1, 1);
 cellTitle = cells.AddValueCell(2, 1, " Car number "); // No. 1 2 Line number 1 Column   Display 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 = 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(); // Get 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-Server cannot set content type after sending HTTP header
Add the ability of 'Global Refresh' to the export button. The example in this article is done by aspx in < asp:UpdatePanel > Add the following code to the tag

<Triggers>
    <%--<asp:AsyncPostBackTrigger ControlID="" />--%> <%-- Local refresh Value refresh UpdatePanel Internal --%>
   <asp:PostBackTrigger ControlID="btnExport" /> <%-- Refresh All --%> <%--2016 Year 7 Month 1 Day To solve the problem of clicking the export button to report an error, "the server cannot send HTTP Error setting content type after header " --%>
</Triggers>


Related articles: