Excel导入，导出，模板生成公共模块整理

1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Linq;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.HtmlControls;
9 using System.Web.UI.WebControls;
10 using System.Web.UI.WebControls.WebParts;
11 using System.Xml.Linq;
12 using System.Collections;
13 using Microsoft.Office.Interop.Excel;
14 using System.Data.OleDb;
15 using System.IO;

  1 namespace WebTest
  2 {
  3     /// <summary>
  4     /// Excel导入，导出，模板生成-公共模块整理
  5     /// </summary>
  6     public class ExcelOperate
  7     {
  8         /// <summary>
  9         /// 自定义类，键值对应
10         /// </summary>
11         public class ArrList
12         {
13             public string Key { get; set; }
14             public string Value { get; set; }
15             /// <summary>
16             /// 构造函数赋值
17             /// </summary>
18             /// <param name="key">键</param>
19             /// <param name="value">值</param>
20             public ArrList(string key, string value)
21             {
22                 Key = key;
23                 Value = value;
24             }
25         }
26         /// <summary>
27         /// 单例
28         /// </summary>
29         private static ExcelOperate excelOperate = new ExcelOperate();
30         public static ExcelOperate _
31         {
32             get { return excelOperate; }
33         }
34
35         /// <summary>
36         /// 创建OLEDB数据库连接
37         /// </summary>
38         /// <param name="filepath">excel文件全路径</param>
39         /// <returns>Oledb的连接实例</returns>
40         private OleDbConnection CreateConnection(string filepath)
41         {
42             string connectionstring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + filepath + ";Extended Properties=Excel 8.0";
43             return new OleDbConnection(connectionstring);
44         }
45         /// <summary>
46         /// 获取所有当前Excel所有表
47         /// </summary>
48         /// <param name="filepath">excel文件全路径</param>
49         /// <returns>sheet表名的集合</returns>
50         public ArrayList GetExcelSheetNameList(string filepath)
51         {
52             ArrayList sheetList = new ArrayList();
53             //连接
54             OleDbConnection connection = CreateConnection(filepath);
55             try
56             {
57                 connection.Open();
58                 //取sheet表名
59                 System.Data.DataTable dtExcelSchema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
60                 for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
61                 {
62                     sheetList.Add(dtExcelSchema.Rows[i]["TABLE_NAME"].ToString());
63                 }
64             }
65             catch (Exception ex)
66             {
67                 throw new Exception(ex.Message);
68             }
69             finally
70             {
71                 //关闭连接
72                 connection.Close();
73             }
74             return sheetList;
75         }
76         /// <summary>
77         /// 查询Excel中指定的Sheet表
78         /// </summary>
79         /// <param name="filepath">excel文件全路径</param>
80         /// <param name="sheet">sheet表名</param>
81         /// <returns>返回DataTable</returns>
82         public System.Data.DataTable GetDataTableBySheetName(string filepath, string sheetname)
83         {
84             System.Data.DataTable tempTable = new System.Data.DataTable();
85             OleDbConnection connection = CreateConnection(filepath);
86             try
87             {
88                 //读取
89                 connection.Open();
90                 string sql = "select * from [" + sheetname + "]";
91                 OleDbCommand cmd = connection.CreateCommand();
92                 cmd.CommandText = sql;
93                 OleDbDataAdapter oda = new OleDbDataAdapter(cmd);
94                 //填充
95                 oda.Fill(tempTable);
96                 //验证
97                 if (tempTable != null && tempTable.Rows.Count <= 0)
98                 {
99                     return null;
100                 }
101             }
102             catch (Exception ex)
103             {
104                 throw new Exception(ex.Message);
105             }
106             finally
107             {
108                 //关闭
109                 connection.Close();
110             }
111             return tempTable;
112         }

  1         /// <summary>
  2         /// 根据模板生成Excel
  3         /// </summary>
  4         /// <param name="modelpath">模板excel文件全路径</param>
  5         /// <param name="newpath">生成的目录全路径</param>
  6         /// <param name="values">ExcelOperate.ArrList类型的键值数组</param>
  7         /// <returns></returns>
  8         public string ExportExcelByModel(string modelpath, string newpath, params ArrList[] values)
  9         {
10             //调用的模板文件
11             FileInfo mode = new FileInfo(modelpath);
12             if (!mode.Exists)
13             {
14                 return "Error：文件不存在！";
15             }
16             //定义
17             Application objExcel = new Application();
18             objExcel.Application.DisplayAlerts = false;
19             objExcel.Visible = false;
20             Workbook objBook = null;
21             Object missing = System.Reflection.Missing.Value;
22             if (objExcel == null)
23             {
24                 return "Error：无法创建Excel对象，可能您的机子未安装Excel";
25             }
26             try
27             {
28                 objExcel.Workbooks.Add(missing);
29                 //调用模板
30                 objBook = objExcel.Workbooks.Open(mode.FullName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
31                 //取表
32                 Worksheet objSheet = (Worksheet)objBook.Worksheets[1];
33                 //成功条数记录
34                 bool isSuccess = false;
35                 //值
36                 for (int i = 0; i < values.Length; i++)
37                 {
38                     isSuccess = false;
39                     //行
40                     for (int j = 1; j <= objSheet.UsedRange.Rows.Count; j++)
41                     {
42                         //列
43                         for (int k = 1; k < objSheet.UsedRange.Columns.Count; k++)
44                         {
45                             //取值
46                             Range objRange = (Range)objSheet.Cells[j, k]; //objSheet.get_Range("A" + i, missing);
47                             if (objRange.Value2 == null)
48                             {
49                                 continue;
50                             }
51                             //验证
52                             string value = objRange.Value2.ToString();
53                             //string value = values[i].Split("===");
54                             if (value != values[i].Key)
55                             {
56                                 continue;
57                             }
58                             //赋值
59                             objSheet.Cells[j, k] = values[i].Value;
60                             isSuccess = true;
61                             break;
62                         }
63                         if (isSuccess)
64                         {
65                             break;
66                         }
67                     }
68                 }
69                 //保存
70                 objSheet.SaveAs(newpath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, missing, missing, false, false, missing, missing, missing, missing);
71             }
72             catch (Exception ex)
73             {
74                 return "Error：" + ex.Message;
75             }
76             finally
77             {
78                 //关闭
79                 objBook.Close(false, mode.FullName, missing);
80                 objExcel.Workbooks.Close();
81                 objExcel.Quit();
82                 //结束进程
83                 if (objExcel != null)
84                 {
85                     foreach (System.Diagnostics.Process p in System.Diagnostics.Process.GetProcessesByName("Excel"))
86                     {
87                         //先判断当前进程是否是excel
88                         if (!p.CloseMainWindow())
89                         {
90                             p.Kill();
91                         }
92                     }
93                 }
94                 objBook = null;
95                 objExcel = null;
96                 //强制对所有代进行垃圾回收
97                 GC.Collect();
98             }
99             return "Success";
100         }
101         /// <summary>
102         /// 读取DataTable生成Excel
103         /// </summary>
104         /// <param name="filepath">保存的excel文件全路径</param>
105         /// <param name="filename">保存时的文件名</param>
106         /// <param name="table">数据来源DataTable</param>
107         /// <returns></returns>
108         public string ExportExcelByDataTable(string filepath, string filename, System.Data.DataTable table)
109         {
110             string ret = "导出成功";
111             if (table == null)
112             {
113                 return ret = "无数据";
114             }
115             //定义
116             Application objExcel = new Application();
117             if (objExcel == null)
118             {
119                 ret = "Error：无法创建Excel对象，可能您的机子未安装Excel";
120             }
121             Workbooks objBooks = objExcel.Workbooks;
122             Workbook objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);
123             Worksheet objSheet = (Worksheet)objBook.Worksheets[1];
124
125             //写入字段
126             for (int i = 0; i < table.Columns.Count; i++)
127             {
128                 objSheet.Cells[1, i + 1] = table.Columns[i].ColumnName;
129             }
130             //写入值
131             for (int r = 0; r < table.Rows.Count; r++)
132             {
133                 for (int i = 0; i < table.Columns.Count; i++)
134                 {
135                     objSheet.Cells[r + 2, i + 1] = "'" + table.Rows[r][i].ToString();
136                 }
137             }
138             //worksheet.Columns.EntireColumn.AutoFit();
139             try
140             {
141                 if (filename == "")
142                 {
143                     ret = "Error：请传入导出后的文件名";
144                 }
145                 string savePath = filepath + filename;
146                 objBook.Saved = true;
147                 objBook.SaveCopyAs(savePath);
148                 ret = "Success";
149             }
150             catch (Exception ex)
151             {
152                 ret = "Error：导出文件时出错,文件可能正被打开！\n" + ex.Message;
153             }
154             finally
155             {
156                 //关闭
157                 objExcel.Quit();
158                 objBook = null;
159                 objExcel = null;
160                 GC.Collect();//强行销毁
161             }
162             return ret;
163         }
164     }
165 }

//获取sheet表名
ArrayList sheetList = ExcelOperate._.GetExcelSheetNameList(filepath);

       //显示数据
       GridView1.DataSource = ExcelOperate._.GetDataTableBySheetName(filepath, sheet);
       this.DataBind();

1             //数据源
2             DataTable dt = (DataTable)GridView1.DataSource;
3             //文件名
4             string filename = "down" + DateTime.Now.ToString("hhmmssfff") + ".xls";
5             //保存地址
6             string filepath = Server.MapPath("exceldown/");
7             //生成
8             string ret = ExcelOperate._.ExportExcelByDataTable(filepath, filename, dt);
9             //错误
10             if (ret.Contains("Error"))
11             {
12                 Response.Write(ret);
13                 return;
14             }
15             //下载
16             Response.Write("<a href=\"/exceldown/" + filename + "\">" + filename + "</a>");

1         protected void btnOut_Click(object sender, EventArgs e)
2         {
3             //模板地址
4             string modelpath = Server.MapPath("excelmodel") + "\\" + "testmodel.xls";
5             //生成地址
6             string newpath = Server.MapPath("exceldown") + "\\" +
7                 DateTime.Now.ToString("yyyyMMddhhmmssfff") + ".xls";
8             //参数
9             ExcelOperate.ArrList al1 = new ExcelOperate.ArrList("#Title", "Excel导入，导出，模板生成-公共模块整理");
10             ExcelOperate.ArrList al2 = new ExcelOperate.ArrList("#No", "1001");
11             ExcelOperate.ArrList al3 = new ExcelOperate.ArrList("#User", "tearer");
12             ExcelOperate.ArrList al4 = new ExcelOperate.ArrList("#Tel", "1234567890");
13             ExcelOperate.ArrList al5 = new ExcelOperate.ArrList("#Date", "2010-04-27");
14             ExcelOperate.ArrList al6 = new ExcelOperate.ArrList("#Addr", "上海市浦东新区");
15             //生成
16             string ret = ExcelOperate._.ExportExcelByModel(modelpath, newpath, al1, al2, al3, al4, al5, al6, new ExcelOperate.ArrList("#Operate", "AmwITx"));
17             //错误
18             if (ret.Contains("Error"))
19             {
20                 Response.Write(ret);
21                 return;
22             }
23             //弹出
24             WriteExcel(newpath);
25         }
26
27         private void WriteExcel(string newpath)
28         {
29             FileInfo fi = new FileInfo(newpath);
30             //打开保存对话框
31             Response.Clear();
32             Response.ClearHeaders();
33             Response.Buffer = false;
34             Response.Charset = "UTF-8";
35             Response.ContentType = "application/ms-excel";
36             Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode(fi.Name));
37             Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
38             Response.AppendHeader("Content-Length", fi.Length.ToString());
39             Response.Charset = "";
40             this.EnableViewState = false;
41             Response.WriteFile(newpath);
42             //删除创建的Excel文件
43             //FileInfo fileinf = new FileInfo(newpath);
44             //fileinf.Delete();
45             //关闭连接
46             Response.Flush();
47             Response.End();
48         }