asp. net Upload Excel File and Read Data

Preface

This article mainly introduces the related contents about asp. net uploading Excel files and reading data, and shares them for your reference and study. The following words are not much to say, let's take a look at the detailed introduction

The implementation is as follows:

Foreground code: Upload using server control

  <form id="form1" runat="server">
   <div>
    <asp:FileUpload ID="ExcelFileUpload" runat="server" />
    <asp:Button ID="UploadBtn" runat="server" Text=" Confirm upload " OnClick="UploadBtn_Click" />
   </div>
  </form>

Server-side code:

protected void UploadBtn_Click(object sender, EventArgs e)
{
 if (ExcelFileUpload.HasFile == false)//HasFile Used to check FileUpload Is there a file 
 {
  Response.Write("<script>alert(' Please choose Excel Documents ')</script> ");
  return;// When there is no file , Return 
 }
 string IsXls = Path.GetExtension(ExcelFileUpload.FileName).ToString().ToLower();//System.IO.Path.GetExtension Get the extension of the file 
 if (IsXls != ".xlsx" && IsXls != ".xls")
 {
  Response.Write(ExcelFileUpload.FileName);
  Response.Write("<script>alert(' You can only select Excel Documents ')</script>");
  return;// When the choice is not Excel File time , Return 
 }

 string filename = ExcelFileUpload.FileName;// Get Execle Filename  
 string savePath = Server.MapPath(("UploadExcel\\") + filename);//Server.MapPath  The physical file path corresponding to the specified virtual path on the server 
 //savePath ="D:\vsproject\Projects\exceltestweb\exceltestweb\uploadfiles\test.xls"
 //Response.Write(savePath);
 DataTable ds = new DataTable();
 ExcelFileUpload.SaveAs(savePath);// Saves the file to the specified path 

 DataTable dt = GetExcelDatatable(savePath);// Read excel Data 
 List<RegNumInfo> regList = ConvertDtToInfo(dt);// Will datatable Convert to list
 File.Delete(savePath);// Delete a file 

 Response.Write("<script>alert(' Upload file to read data successfully! ');</script>");
}
/// <summary>
///  From excel Read data from a file 
/// </summary>
/// <param name="fileUrl"> Storage path of entity file </param>
/// <returns></returns>
private static DataTable GetExcelDatatable(string fileUrl)
{
 // Support .xls And .xlsx Which includes office2010 Equal version of ;HDR=Yes On behalf of 1 Rows are headers, not data; 
 string cmdText = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileUrl + "; Extended Properties=\"Excel 12.0;HDR=Yes\"";
 System.Data.DataTable dt = null;
 // Establish a connection 
 OleDbConnection conn = new OleDbConnection(cmdText);
 try
 {
  // Open a connection 
  if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
  {
   conn.Open();
  }

  System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
  string strSql = "select * from [Sheet1$]"; // This specifies that it is indicated as Sheet1, If you have modified the name of the form, use the modified name 
  OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
  DataSet ds = new DataSet();
  da.Fill(ds);
  dt = ds.Tables[0]; ;
  return dt;
 }
 catch (Exception exc)
 {
  throw exc;
 }
 finally
 {
  conn.Close();
  conn.Dispose();
 }
}
/// <summary>
///  Will datatable Convert to list Set 
/// </summary>
/// <param name="dt">DataTable</param>
/// <returns></returns>
private static List<RegNumInfo> ConvertDtToInfo(DataTable dt)
{
 List<RegNumInfo> list = new List<RegNumInfo>();
 if (dt.Rows.Count > 0)
 {
  foreach (DataRow item in dt.Rows)
  {
   RegNumInfo info = new RegNumInfo();
   info.RegNum = item[0].ToString();
   info.Name = item[1].ToString();
   info.Period = item[2].ToString();
   info.Remark = item[3].ToString();
   list.Add(info);
  }
 }
 return list;
}

public class RegNumInfo
{
 public string RegNum { get; set; }
 public string Name { get; set; }
 public string Period { get; set; }
 public string Remark { get; set; }
}

Note: Solution for error report "Not registered on local computer" microsoft. ACE. oledb. 12.0 "Provider"

1. Because OleDb is used to read excel file, if office is not installed on the server, data access component (AccessDatabaseEngine) needs to be installed;

* For office 2010

Microsoft Access Database Engine 2010 Redistributable

https://www.microsoft.com/zh-CN/download/details. aspx? id=13255

2. In the IIS application pool, set "Enable compatible 32-bit applications";

For specific solutions, please refer to this article: https://www.ofstack.com/article/157457. htm

Summarize