asp.net用类似操纵Sql语句的方式快速实现Excel读写的方法（oleDB 方式）

最新推荐文章于 2021-10-27 20:05:07 发布

Dr-远古

最新推荐文章于 2021-10-27 20:05:07 发布

阅读量800

点赞数

1.首先配置web.config 文件

2.编写个公用函数封装字符串调用，分Excel读和写两种不同的字符串调用连接

返回Excel的数据源连接字符串[写]：GetExcelOutPutConString(filepath);
返回Excel的数据源连接字符串[读]：GetExcelInPutConString(filepath);

其主要区别在于IMEX参数，在我们采用OleDB方式进行Excel读写的时候一定要注意IMEX 和HDR参数的区别，详细如下

用OLEDB进行Excel文件数据的读取，并返回DataSet数据集。其中有几点需要注意的：
1.连接字符串中参数IMEX 的值：

当 IMEX=0 时为“汇出模式”，这个模式开启的 Excel 档案只能用来做“写入”用途。

当 IMEX=1 时为“汇入模式”，这个模式开启的 Excel 档案只能用来做“读取”用途。

当IMEX=2 时为“连結模式”，这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
但我们需奥特别注意：当IMEX=2 时，将EXCEL数据导出的时候如果同一列数据中既有文字，又有数字！读取时一列中要么文字丢失只剩下数字，要么数字丢失，只剩下文字

例如第C列有3个值，2个为数值型 123，1个为字符型 ABC，当导入时，
页面不报错了，但库里只显示数值型的123，而字符型的ABC则呈现为空值。当IMEX=1时，无上述情况发生，库里可正确呈现 123 和 ABC.
2.参数HDR的值：
HDR=Yes，这代表第一行是标题，不做为数据使用，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES
3.参数Excel 8.0
对于Excel 97用啥，我没有实现过；对于Excel 2003版本用Excel 8.0，对于Excel 2007及以上版本都用Excel 12.0

3.读ExcelExcel的某个Sheet数据到导入DataTable中

#region 返回Excel的数据源连接字符串[导入模式]
      /// <summary>
      /// 返回Excel的数据源连接字符串
      /// </summary>
      /// <param name="filePath"></param>
      /// <returns></returns>
      public static string GetExcelInPutConString(string filePath)
      {
          string conn = string.Empty;
          if (filePath.Contains(".xlsx"))
          {
              conn = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\";";
          }
          else
          {
              conn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;;HDR=YES;IMEX=1\"";
          }
          return conn;
      }
      #endregion

      #region 返回Excel的数据源连接字符串[导出模式]
      /// <summary>
      /// 返回Excel的数据源连接字符串[导出模式]
      /// </summary>
      /// <param name="filePath">导出文件名</param>
      /// <returns></returns>
      public static string GetExcelOutputConString(string filePath)
      {
          string conn = string.Empty;
          if (filePath.Contains(".xlsx"))
          {
              conn = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=0\";";
          }
          else
          {
              conn = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;;HDR=YES;IMEX=0\"";
          }

          return conn;
      }

#endregion

/// <summary>
  /// Excel数据到导入DataTable中
  /// </summary>
  /// <param name="filepath">文件路径</param>
  /// <returns></returns>
  protected DataTable CallExcel(string filepath)
  {
      string conn = GetExcelInPutConString (filepath);
      OleDbConnection con = new OleDbConnection(conn);
      con.Open();
      DataTable table = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
      string tableName = table.Rows[0][2].ToString().Trim();
      string sql = "Select * From [" + tableName + "]";//选择第一个数据SHEET
    
      OleDbDataAdapter adapter = new OleDbDataAdapter(sql, con);
      DataSet ds = new DataSet();
      DataTable dt = new DataTable();
      adapter.Fill(ds);
      dt = ds.Tables[0];
      con.Close();
      con.Dispose();
      return dt;
  }

public static void ExportDataTableToExcel(DataTable dt, string fileName, bool showNum)
       {
           string tempFile = string.Empty;
           try
           {
               string souceFileName = System.Web.HttpContext.Current.Server.MapPath("~/ExcelTemplate/RestockingTemp.xls");
               string destFileName = System.Web.HttpContext.Current.Server.MapPath("~/TempExcel/" + Guid.NewGuid().ToString() + ".xls");
               tempFile = destFileName;
               File.Copy(souceFileName, destFileName);
               string strConn = GetExcelConString(destFileName);
               OleDbConnection conn = new OleDbConnection(strConn);
               conn.Open();
               System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
               cmd.Connection = conn;
               int i = 1;
               foreach (DataRow dr in dt.Rows)
               {
                   try
                   {
                       cmd.CommandText = "INSERT INTO [Sheet1$] VALUES('"  +
                   dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() +
                   "','" + dr[3].ToString() + "')";
                       cmd.ExecuteNonQuery();
                   }
                   catch (Exception ex)
                   {

                       cmd.CommandText = "INSERT INTO [Sheet1$] VALUES('"  +
                       dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() +
                       "','" + dr[3].ToString() +"')";
                       cmd.ExecuteNonQuery();
                       OpLogService.MakeLogError(ex.Message, "物料号：" + dr[2].ToString(), "工位补货量维护 WEB.PAGE");
                   }
                   i++;
               }
               conn.Close();

               FileStream fs = new FileStream(destFileName, FileMode.Open, FileAccess.Read);
               BinaryReader r = new BinaryReader(fs);
               byte[] content = new byte[fs.Length];
               fs.Read(content, 0, content.Length);
               fs.Close();

               HttpContext.Current.Response.Clear();
               HttpContext.Current.Response.Charset = "GB2312";
               //HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
               HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + fileName + ".xls");
               HttpContext.Current.Response.AddHeader("Content-Length", content.Length.ToString());
               HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
               HttpContext.Current.Response.ContentType = "application/ms-excel";
               HttpContext.Current.Response.Filter.Close();
               HttpContext.Current.Response.OutputStream.Write(content, 0, content.Length);
               HttpContext.Current.Response.OutputStream.Flush();
               HttpContext.Current.Response.End();
               DeleteFile(destFileName);
           }
           catch (System.Data.OleDb.OleDbException ex)
           {
               OpLogService.MakeLogError(ex.Message, "Excel", "WEB.PAGE");
           }
       }

#region

      /// <summary>
      /// 导出数据 added by ldb on 20110826 导出Excel无乱码
      /// </summary>
      /// <param name="dt">数据集</param>
      /// <param name="outFileName">导出文件名</param>
      /// <param name="header">表头键值对<字段,类型></param>
      public static void ExportDataTableToExcel(DataTable dt, string outFileName, System.Collections.ArrayList header)
      {
          string tempFile = string.Empty;
          try
          {
              //string souceFileName = System.Web.HttpContext.Current.Server.MapPath("~/ExcelTemplate/" + templateName + ".xls");
              string destFileName = System.Web.HttpContext.Current.Server.MapPath("~/TempExcel/" + Guid.NewGuid().ToString() + ".xls");
              tempFile = destFileName;
              //FileStream fs = File.Create(destFileName, 10000, FileOptions.Asynchronous);
              //fs.Close();

 

              int columnNum = dt.Columns.Count;

              string strConn = GetExcelOutputConString(destFileName);
              OleDbConnection conn = new OleDbConnection(strConn);
              conn.Open();
              System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
              cmd.Connection = conn;
              try
              {
                  StringBuilder createStr = new StringBuilder();
                  createStr.Append(@" CREATE TABLE [Sheet1](");
                  foreach (object key in header.ToArray())
                  {
                      KeyValuePair<string, string> kvp = (KeyValuePair<string, string>)key;
                      createStr.Append("[" + kvp.Key + "] " + kvp.Value + ",");
                  }

                  /*for (int j = 0; j < columnNum; j++)
                  {
                      createStr += "["+dt.Columns[j].ColumnName + "] VARCHAR,";
                  }*/
                  createStr = createStr.Remove(createStr.Length - 1, 1);
                  createStr.Append(")");

                  cmd.CommandText = createStr.ToString();
                  cmd.ExecuteNonQuery();
              }
              catch (Exception ex)
              {
                  OpLogService.MakeLogError(ex.Message, "创建Excel失败!", "ExportDataTableToExcel");
                  return;
              }
              int i = 1;
              foreach (DataRow dr in dt.Rows)
              {
                  try
                  {
                      //字段项
                      StringBuilder filedString = new StringBuilder();
                      filedString.Append("(");

                      //value项
                      StringBuilder valueString = new StringBuilder();
                      valueString.Append("(");
                      for (int k = 0; k < header.Count; k++)
                      {
                          KeyValuePair<string, string> kvp = (KeyValuePair<string, string>)header[k];
                          filedString.Append("[" + kvp.Key + "]");
                          //若为字符串则添加‘’
                          if (kvp.Value == "NVARCHAR")
                          {
                              valueString.Append("'");
                              valueString.Append(dr[k].ToString());
                              valueString.Append("'");
                          }
                          else//若为数字
                          {

                              valueString.Append(string.IsNullOrEmpty(dr[k].ToString()) ? "NULL" : dr[k].ToString());

                          }
                          if (k != header.Count - 1)
                          {
                              valueString.Append(",");
                              filedString.Append(",");
                          }

                      }
                      filedString.Append(")");
                      valueString.Append(")");

 

                      cmd.CommandText = "INSERT INTO [Sheet1$]" + filedString + " VALUES " + valueString.ToString();
                      cmd.ExecuteNonQuery();
                  }
                  catch (Exception ex)
                  {
                      OpLogService.MakeLogError(ex.Message, "第一列：" + dr[0].ToString());
                  }
                  i++;
              }
              conn.Close();

              FileStream fs = new FileStream(destFileName, FileMode.Open, FileAccess.Read);
              BinaryReader r = new BinaryReader(fs);
              byte[] content = new byte[fs.Length];
              fs.Read(content, 0, content.Length);
              fs.Close();

              HttpContext.Current.Response.Clear();
              HttpContext.Current.Response.Charset = "GB2312";
              //HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
              HttpContext.Current.Response.AddHeader("Content-Disposition", "inline; filename=" + outFileName + ".xls");
              HttpContext.Current.Response.AddHeader("Content-Length", content.Length.ToString());
              HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
              HttpContext.Current.Response.ContentType = "application/ms-excel";
              HttpContext.Current.Response.Filter.Close();
              HttpContext.Current.Response.OutputStream.Write(content, 0, content.Length);
              HttpContext.Current.Response.OutputStream.Flush();
              HttpContext.Current.Response.End();
              DeleteFile(destFileName);
          }
          catch (System.Data.OleDb.OleDbException ex)
          {
              //log error
          }
      }

      #endregion