ImportExcel


  1         /// <summary>
  2         /// 在磁盘上创建一个临时的excel文件
  3         /// </summary>
  4         private void CreateExcelFile(string filePath, List<Entity> list)
  5         {
  6             if (File.Exists(filePath))
  7             {
  8                 File.Delete(filePath);
  9             }
 10 
 11             OleDbConnection oleDbConn = new OleDbConnection();
 12             OleDbCommand oleDbCmd = new OleDbCommand();
 13             string sSql = "";
 14 
 15             try
 16             {
 17                 //打开Microsoft.Jet.OleDb.4.0连接
 18                 oleDbConn.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + filePath + @";Extended ProPerties=""Excel 8.0;HDR=Yes;""";
 19                 oleDbConn.Open();
 20                 oleDbCmd.CommandType = System.Data.CommandType.Text;
 21                 oleDbCmd.Connection = oleDbConn;
 22 
 23                 //写列名
 24                 sSql = "CREATE TABLE [Sheet1](";
 25                 for (int i = 0; i < this.gvData.Columns.Count - 2; i++)
 26                 {
 27                     if (i < this.gvData.Columns.Count - 1)
 28                         sSql += this.gvData.Columns[i].HeaderText + " Text(200),";
 29                 }
 30                 oleDbCmd.CommandText = sSql.Remove(sSql.Length - 1) + ")";
 31                 oleDbCmd.ExecuteNonQuery();
 32 
 33                 //写行数据 
 34                 for (int j = 0; j < list.Count; j++)
 35                 {
 36                     sSql = "INSERT INTO [Sheet1] VALUES('";
 37                     sSql += list[j].列1 + " ','";
 38                     sSql += list[j].列2 + " ');";
 39                     oleDbCmd.CommandText = sSql;
 40                     oleDbCmd.ExecuteNonQuery();
 41                 }
 42             }
 43             catch (System.Exception ex)
 44             {
 45                 throw ex;
 46             }
 47             finally
 48             {
 49                 //断开连接
 50                 oleDbCmd.Dispose();
 51                 oleDbConn.Close();
 52                 oleDbConn.Dispose();
 53             }
 54 
 55         }
 56 
 57         /// <summary>
 58         ///     数据导出
 59         /// </summary>
 60         private void ImportExcel(List<Entity> list)
 61         {
 62             try
 63             {
 64                 //随机文件名 
 65                 string FileName = "temp//Sheet1.xls";
 66                 //临时文件路径
 67                 string filePath = this.Request.PhysicalApplicationPath + FileName;
 68 
 69                 //创建临时文件
 70                 CreateExcelFile(filePath, list);
 71 
 72                 using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate))
 73                 {
 74                     //让用户输入下载的本地地址
 75                     Response.Clear();
 76                     Response.Buffer = true;
 77                     Response.Charset = "GB2312";
 78 
 79                     Response.AppendHeader("Content-Disposition", "attachment;filename=Sheet1.xls");
 80                     Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
 81                     Response.ContentType = "application/ms-excel";
 82 
 83                     // 读取excel数据到内存
 84                     byte[] buffer = new byte[fs.Length - 1];
 85                     fs.Read(buffer, 0, (int)fs.Length - 1);
 86 
 87                     // 写到aspx页面
 88                     Response.BinaryWrite(buffer);
 89                     Response.Flush();
 90                     this.ApplicationInstance.CompleteRequest(); //停止页的执行
 91 
 92 
 93                     fs.Close();
 94                     fs.Dispose();
 95 
 96                     //删除临时文件
 97                     File.Delete(filePath);
 98                 }
 99 
100             }
101             catch (Exception ex)
102             {
103             }
104         }
105 
106
来自 http://blog.csdn.net/houlinghouling/article/details/3851128