Asp.net realizes the function of uploading excel

Time:2021-12-5

These days, it’s just necessary to upload excel and do corresponding processing according to the data in Excel, so it’s sorted for standby.

Resources used:

(1) Nopi 2.2.0.0 can be downloaded from the official website or click: http://pan.baidu.com/s/1b1EMdg

(2) Some common public method classes for processing files can be added to the project: http://pan.baidu.com/s/1bJpHuQ

If the above connection cannot be used for some reason, you can leave an email in the comment and I’ll package and send it. If you have better suggestions, welcome guidance.

The background prompt method showmsghelper can be rewritten according to your own.

Foreground Code:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
 < title > Import Excel to generate datatable < / Title >
 <script src="../../Themes/Scripts/jquery-1.8.2.min.js"></script>
 <link href="/Themes/Styles/Site.css" rel="external nofollow" rel="stylesheet" type="text/css" />
 <script src="/Themes/Scripts/FunctionJS.js" type="text/javascript"></script>
 
 <script type="text/javascript">
 $(document).ready(function () {
  $("#Import").click(function () {
  var filename = $("#FileUpload1").val();
  if (filename == '') {
   Alert ('Please select the uploaded excel file ');
   return false;
  }
  else {
   var exec = (/[.]/.exec(filename)) ? /[^.]+$/.exec(filename.toLowerCase()) : '';
   if (!(exec == "xlsx" || exec == "xls")) {
   Alert ("the file format is incorrect, please upload the excel file!");
   return false;
   }
  }
  return true;
  });
 });
 </script>
</head>
<body>
 <form runat="server">
 <div>
 < ASP: fileUpload id = "fileupload1" runat = "server" / > < ASP: button id = "import" runat = "server" text = "import" onclick = "impclick" / >
 </div>
 </form>
</body>
</html>

Background code;

protected void ImpClick(object sender, EventArgs e)
 {
  try
  {
  #Region verification
  var fileName = this.FileUpload1.FileName;
  if (string.IsNullOrWhiteSpace(fileName))
  {
   //Prompt information
   Showmsghelper.alert ("please select to upload excel file");
   return;
  }
  //Get the extension name of the uploaded file
  if (!(fileName.IndexOf(".xlsx") > 0 || fileName.IndexOf(".xls") > 0))
  {
   Showmsghelper. Alert ("the uploaded file format is incorrect, please check!");
   return;
  }
  #endregion
  #Region uploads Excel files to a temporary folder on the server
  //Temporary folder, under the root directory / upload / TMP /, select according to your own configuration
  string path = Server.MapPath("~/") + "Upload\\tmp\\";
  string retStr=UploadHelper.FileUpload(path, this.FileUpload1);
  If (! Retstr.equals ("upload succeeded"){
   ShowMsgHelper.Alert(retStr);
   return;
  }
  #endregion
  #Region reads the first table of the excel file, obtains the content and converts it into a DataTable, deletes the temporary file, or adds a timestamp for maintenance
  DataTable dt = this.ExcelToDataTable(path + this.FileUpload1.FileName, true);
  if (dt == null) {
   ShowMsgHelper.Alert_ Error ("get failed");
   return;
  }
  //Example: get the value in DT
  string test = dt.Rows[0]["name"].ToString();
  string test2 = dt.Rows[1]["class"].ToString();
  //Delete temporary file
  DirFileHelper.DeleteFile("Upload\\tmp\\" + fileName);
  #endregion 
  }
  catch (Exception ex) {
  throw ex;
  }
 }
 /// <summary>
 ///Import excel into datatable
 /// </summary>
 ///< param name = "filepath" > excel path < / param >
 ///< param name = "iscolumnname" > is the first row column name < / param >
 ///< returns > return datatable < / returns >
 public DataTable ExcelToDataTable(string filePath, bool isColumnName)
 {
  DataTable dataTable = null;
  FileStream fs = null;
  DataColumn column = null;
  DataRow dataRow = null;
  IWorkbook workbook = null;
  ISheet sheet = null;
  IRow row = null;
  ICell cell = null;
  int startRow = 0;
  try
  {
  using (fs = File.OpenRead(filePath))
  {
   //Version 2007
   if (filePath.IndexOf(".xlsx") > 0)
   workbook = new XSSFWorkbook(fs);
   //2003 version
   else if (filePath.IndexOf(".xls") > 0)
   workbook = new HSSFWorkbook(fs);
   if (workbook != null)
   {
   sheet = workbook.GetSheetAt(0);// Read the first sheet. Of course, you can also read each sheet circularly
   dataTable = new DataTable();
   if (sheet != null)
   {
    int rowCount = sheet.LastRowNum;// Total number of rows
    if (rowCount > 0)
    {
    IRow firstRow = sheet.GetRow(0);// first line
    int cellCount = firstRow.LastCellNum;// Number of columns

    //Building columns of datatable
    if (isColumnName)
    {
     startRow = 1;// If the first row is a column name, it is read from the second row
     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
     {
     cell = firstRow.GetCell(i);
     if (cell != null)
     {
      if (cell.StringCellValue != null)
      {
      column = new DataColumn(cell.StringCellValue);
      dataTable.Columns.Add(column);
      }
     }
     }
    }
    else
    {
     for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
     {
     column = new DataColumn("column" + (i + 1));
     dataTable.Columns.Add(column);
     }
    }
    //Fill row
    for (int i = startRow; i <= rowCount; ++i)
    {
     row = sheet.GetRow(i);
     if (row == null) continue;

     dataRow = dataTable.NewRow();
     for (int j = row.FirstCellNum; j < cellCount; ++j)
     {
     cell = row.GetCell(j);
     if (cell == null)
     {
      dataRow[j] = "";
     }
     else
     {
      //CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)
      switch (cell.CellType)
      {
      case CellType.Blank:
       dataRow[j] = "";
       break;
      case CellType.Numeric:
       short format = cell.CellStyle.DataFormat;
       //Processing of time format (2015.12.5, 2015 / 12 / 5, 2015-12-5, etc.)
       if (format == 14 || format == 31 || format == 57 || format == 58)
dataRow[j] = cell.DateCellValue;
else       dataRow[j] = cell.NumericCellValue;
break;
      case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
      }
     }
     }
     dataTable.Rows.Add(dataRow);
    }
    }
   }
   }
  }
  return dataTable;
  }
  catch (Exception)
  {
  if (fs != null)
  {
   fs.Close();
  }
  return null;
  }
 }

The above is the whole content of this article. I hope the content of this article can bring some help to your study or work. At the same time, I also hope to support developpaer!

Recommended Today

JS generate guid method

JS generate guid method https://blog.csdn.net/Alive_tree/article/details/87942348 Globally unique identification(GUID) is an algorithm generatedBinaryCount Reg128 bitsNumber ofidentifier , GUID is mainly used in networks or systems with multiple nodes and computers. Ideally, any computational geometry computer cluster will not generate two identical guids, and the total number of guids is2^128In theory, it is difficult to make two […]