正文

ASP.NET导入导出Excel方法大全

IP云
IP云V管理员/4 阅读
11/28
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

    • 本文介绍下，C#实现的可以导出与导入excel的代码一例，有需要的朋友，参考下吧。

      C#实现导出与导入excel。
      代码1：

      复制代码 代码示例:
      #region  导出Excel
      /// <summary>
      /// 导出Excel
      /// </summary>
      /// <param name="page">请求的页面this</param>
      /// <param name="dataTable">导出的数据源</param>
      /// <param name="fileName">保存文件名称</param>
      /// <returns>布尔值</returns>
      public bool ExportExcel(Page page, DataTable dataTable, string fileName)
      {
      try
      {
      HttpContext.Current.Response.Clear();
      HttpContext.Current.Response.Buffer = true;
      HttpContext.Current.Response.ContentType = "application/vnd.ms-excel.numberformat:@";
      page.EnableViewState = false;
      HttpContext.Current.Response.Charset = "UTF-8";
      HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//设置输出流为简体中文
      HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
      //输出列名
      for (int i = 0; i < dataTable.Columns.Count; i++)
      HttpContext.Current.Response.Write(dataTable.Columns[i].ColumnName + "\t");
      HttpContext.Current.Response.Write("\r\n");
      //输出数据
      for (int i = 0; i < dataTable.Rows.Count; i++)
      {
      for (int j = 0; j < dataTable.Columns.Count; j++)
      {
      HttpContext.Current.Response.Write(dataTable.Rows[i][j].ToString() + "\t");
      }
      HttpContext.Current.Response.Write("\r\n");
      }
      //输出当前缓存内容
      //HttpContext.Current.Response.Flush();
      HttpContext.Current.Response.End();
      return true;
      }
      catch
      {
      return false;
      }
      }
      #endregion

      代码2

      代码示例:
      #region 导出Excel 自定义格式
      /// <summary>
      /// 导出Excel
      /// 1.文本：vnd.ms-excel.numberformat:@
      /// 2.日期：vnd.ms-excel.numberformat:yyyy/mm/dd
      /// 3.数字：vnd.ms-excel.numberformat:#,##0.00
      /// 4.货币：vnd.ms-excel.numberformat:￥#,##0.00
      /// 5.百分比：vnd.ms-excel.numberformat: #0.00%
      /// </summary>
      /// <param name="fileName"></param>
      /// <param name="dt"></param>
      /// <returns></returns>
      public bool Export(string fileName, DataTable dt)
      {
      try
      {
      HttpResponse resp;
      resp = System.Web.HttpContext.Current.Response;
      resp.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
      resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
      resp.AppendHeader("Content-Type", "application/ms-excel");

      StringBuilder colHeaders = new StringBuilder();
      StringBuilder ls_item = new StringBuilder();
      DataRow[] myRow = dt.Select();
      int cl = dt.Columns.Count;

      colHeaders.Append(" <html><head> \n ");
      colHeaders.Append(" <meta http-equiv='Content-Type' content='text/html; charset=gb2312' /> \n ");
      colHeaders.Append(" </head> \n ");
      colHeaders.Append(" <body> \n ");
      colHeaders.Append(" <table border='1'> ");
      colHeaders.Append(" <tr> ");
      //输出列名
      for (int i = 0; i < dt.Columns.Count; i++)
      colHeaders.Append("<td  style='background-color:#CCCCCC'>" + dt.Columns[i].ColumnName + "</td>");
      colHeaders.Append("</tr> ");
      resp.Write(colHeaders.ToString());
      foreach (DataRow row in myRow)
      {
      ls_item.Append("<tr>");
      for (int i = 0; i < cl; i++)
      {
      if (i == (cl - 1))
      {
      ls_item.Append("<td style='vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>" + "\n");
      }
      else
      {
      ls_item.Append("<td style= 'vnd.ms-excel.numberformat:@ '>" + row[i].ToString() + "</td>");
      }
      }
      ls_item.Append("</tr>");
      }
      ls_item.Append(" </table> \n ");
      ls_item.Append(" </body> \n ");
      ls_item.Append(" </html>");
      resp.Write(ls_item.ToString());
      resp.End();
      return true;
      }
      catch
      {
      return false;
      }
      }
      #endregion
      代码3

      代码示例:
      #region 导入Excel
      public string ImportExcel(string[] list, string filePath)
      {
      string isXls = System.IO.Path.GetExtension(filePath).ToLower(); //System.IO.Path.GetExtension获得文件的扩展名
      if (isXls != ".xls")
      return "请选择Excel文件导入！";
      DataSet ds = ExecleDataSet(filePath); //调用自定义方法
      DataRow[] dr = ds.Tables[0].Select(); //定义一个DataRow数组
      int rowsnum = ds.Tables[0].Rows.Count;
      if (ds.Tables[0].Rows.Count == 0)
      return "Excel无数据！";
      return "";
      }

      //OleDB连接读取Excel中数据
      public DataSet ExecleDataSet(string filePath)
      {
      string OleDbConnection = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
      OleDbConnection conn = new OleDbConnection(OleDbConnection);
      conn.Open();
      DataSet ds = new DataSet();
      OleDbDataAdapter odda = new OleDbDataAdapter("select * from [Sheet1$]", conn);
      odda.Fill(ds);
      conn.Close();
      return ds;
      }
      #endregion

    • 转载自：http://www.hello-code.com/blog/asp.net/201401/2645.html

相关阅读

目录[+]