Detailed example of how asp.net (C#) reads Excel files

.xls format Office2003 and below
.xlsx format Office2007 and above
.csv format Comma-separated string text (the above two file types can be saved in this format)
Read There are two different methods for taking the first two formats and reading the latter format.

Look at the program below:
Page frontend:

<div>       <%-- 文件上传控件  用于将要读取的文件上传 并通过此控件获取文件的信息--%>      
<asp:FileUpload ID="fileSelect" runat="server" />          
<%-- 点击此按钮执行读取方法--%>       
<asp:Button ID="btnRead" runat="server" Text="ReadStart" />
</div>　　

Backend code:

//声明变量（属性）
 string currFilePath = string.Empty; //待读取文件的全路径 
 string currFileExtension = string.Empty;  //文件的扩展名 
 //Page_Load事件 注册按钮单击事件 
 protected void Page_Load(object sender,EventArgs e) 
 { 
     this.btnRead.Click += new EventHandler(btnRead_Click); 
 }
 
 //按钮单击事件   //里面的3个方法将在下面给出
 protected void btnRead_Click(object sender,EventArgs e)
 {
     Upload();  //上传文件方法
     if(this.currFileExtension ==".xlsx" || this.currFileExtension ==".xls")
       {
            DataTable dt = ReadExcelToTable(currFilePath);  //读取Excel文件（.xls和.xlsx格式）
       }
       else if(this.currFileExtension == ".csv")
         {
               DataTable dt = ReadExcelWidthStream(currFilePath);  //读取.csv格式文件
         }
 }

The three methods in the button click event are listed below

///<summary>
  ///上传文件到临时目录中
  ///</ummary>
  private void Upload()
  {
  HttpPostedFile file = this.fileSelect.PostedFile;
  string fileName = file.FileName;
  string tempPath = System.IO.Path.GetTempPath(); //获取系统临时文件路径
  fileName = System.IO.Path.GetFileName(fileName); //获取文件名（不带路径）
  this.currFileExtension = System.IO.Path.GetExtension(fileName); //获取文件的扩展名
  this.currFilePath = tempPath + fileName; //获取上传后的文件路径 记录到前面声明的全局变量
  file.SaveAs(this.currFilePath); //上传
  }

  ///<summary>
    ///读取xls\xlsx格式的Excel文件的方法
    ///</ummary>
    ///<param name="path">待读取Excel的全路径</param>
    ///<returns></returns>
    private DataTable ReadExcelToTable(string path)
    {
    //连接字符串
    string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=&#39;Excel 8.0;HDR=NO;IMEX=1&#39;;"; // Office 07及以上版本 不能出现多余的空格 而且分号注意
    //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties=&#39;Excel 8.0;HDR=NO;IMEX=1&#39;;"; //Office 07以下版本 因为本人用Office2010 所以没有用到这个连接字符串 可根据自己的情况选择 或者程序判断要用哪一个连接字符串
    using(OleDbConnection conn = new OleDbConnection(connstring))
    {
    conn.Open();
    DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[]{null,null,null,"Table"}); //得到所有sheet的名字
    string firstSheetName = sheetsName.Rows[0][2].ToString(); //得到第一个sheet的名字
    string sql = string.Format("SELECT * FROM [{0}],firstSheetName); //查询字符串
    OleDbDataAdapter ada =new OleDbDataAdapter(sql,connstring);
    DataSet set = new DataSet();
    ada.Fill(set);
    return set.Tables[0];
    }
    }

    ///<summary>
      ///读取csv格式的Excel文件的方法
      ///</ummary>
      ///<param name="path">待读取Excel的全路径</param>
      ///<returns></returns>
      private DataTable ReadExcelWithStream(string path)
      {
      DataTable dt = new DataTable();
      bool isDtHasColumn = false; //标记DataTable 是否已经生成了列
      StreamReader reader = new StreamReader(path,System.Text.Encoding.Default); //数据流
      while(!reader.EndOfStream)
      {
      string meaage = reader.ReadLine();
      string[] splitResult = message.Split(new char[]{&#39;,&#39;},StringSplitOption.None); //读取一行 以逗号分隔 存入数组
      DataRow row = dt.NewRow();
      for(int i = 0;i<splitResult.Length;i++)
      {
      if(!isDtHasColumn) //如果还没有生成列
      {
      dt.Columns.Add("column" + i,typeof(string));
      }
      row[i] = splitResult[i];
      }
      dt.Rows.Add(row); //添加行
      isDtHasColumn = true; //读取第一行后 就标记已经存在列 再读取以后的行时，就不再生成列
      }
      return dt;
      }

The above is the detailed content of Detailed example of how asp.net (C#) reads Excel files. For more information, please follow other related articles on the PHP Chinese website!