Excel导入，导出，模板生成-公共模块整理
添加引用，选择.NET选项卡，找到 Microsoft.Office.Interop.Excel.dll 确定
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;
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 }
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 }
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 }
使用方法：
GetExcelSheetNameList
//获取sheet表名
ArrayList sheetList = ExcelOperate._.GetExcelSheetNameList(filepath);
ArrayList sheetList = ExcelOperate._.GetExcelSheetNameList(filepath);
GetDataTableBySheetName
//显示数据
GridView1.DataSource = ExcelOperate._.GetDataTableBySheetName(filepath, sheet);
this.DataBind();
GridView1.DataSource = ExcelOperate._.GetDataTableBySheetName(filepath, sheet);
this.DataBind();
ExportExcelByDataTable
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>");
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>");
ExportExcelByModel
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 }
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 }
网上找了好多种一是不全，二是代码有错误，本人整理后，调试成功，功能全部可用。
