在上一篇（http://www.cnblogs.com/fengchengjushi/p/3369386.html）介绍过，Excel也是数据持久化的一种实现方式。在C#中。我们常常会与Excel文件打交到。那么今天就讲讲在C#程序中操作Excel的问题。这里主要是导入和导出。
在页面中常常会遇到点击按钮实现GridView等控件的展示信息的下载，那么在程序中是如何实现数据下载的呢？其实简单的办法就是将这组规整的数据以Excel的方式保存到本地文件系统。我姑且称这种方式为"程序的方式"吧。
下面看具体代码实现:
/// <summary> /// 程序的方式 /// </summary> /// <param name="ctl"></param> /// <param name="fileName"></param> public static void ExportByApplication(System.Web.UI.Control ctl, string fileName) { string style = @"<style> .text { mso-number-format:\@; } </script> "; HttpContext.Current.Response.Charset = "UTF-8"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.Default) + ".xls"); ctl.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(style); HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End(); }
当我们把这个方法在Button点击按钮下调用的时候，我们运行页面会惊奇的发现，如下场景:
看到这里你是不是”惊呆了"也。呵呵，明明我的控件Gridview是放在了具有runat=server的form表单中的呀？其实你只需要重写VerifyRenderingInServerForm事件。代码如下:
public override void VerifyRenderingInServerForm(Control control) { // Confirms that an HtmlForm control is rendered for }
这里介绍一种与到类似的解决办法，无需重写上面的事件，方法如下:
/// <summary> /// 程序的方式 /// </summary> /// <param name="page"></param> /// <param name="table"></param> /// <param name="FileName"></param> public static void ExportByApplication(System.Web.UI.Page page, System.Data.DataTable table, string FileName)//整个GRIDVIEW导出到EXCEL.xls { FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);//解决导出时文件名汉字显示乱码的问题 HttpResponse resp; resp = page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName); string colHeaders = "", ls_item = ""; //定义表对象与行对象，同时用DataSet对其值进行初始化 System.Data.DataRow[] myRow = table.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的 int i = 0; int cl = table.Columns.Count; //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 for (i = 0; i < cl; i++) { if (i == (cl - 1))//最后一列，加n { colHeaders += table.Columns[i].Caption.ToString() + "\n"; } else { colHeaders += table.Columns[i].Caption.ToString() + "\t"; } } resp.Write(colHeaders); //向HTTP输出流中写入取得的数据信息 //逐行处理数据 foreach (DataRow row in myRow) { //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 for (i = 0; i < cl; i++) { if (i == (cl - 1))//最后一列，加n { ls_item += row[i].ToString() + "\n"; } else { ls_item += row[i].ToString() + "\t"; } } resp.Write(ls_item); ls_item = ""; } resp.End(); }
这两个种方式(当然类似的方法很多)都是通过写入HTTP 响应输出流的方式写入到本地文件系统。
我们知道Excel本来就是小型的数据库模型，我姑且称之为"Oledb"的方式，因此我们可以考虑通过数据访问的形式来实现上面的效果。首先要引入Microsoft.Office.Interop.Excel;
/// <summary> /// Oledb的方式 /// </summary> /// <param name="fileName">Excel文件名</param> /// <param name="table">要导出的DataTable</param> /// <param name="AddHeader">是否要增加表头</param> public static void ExporExportByOledbtExcel(string fileName, System.Data.DataTable table, bool AddHeader) { object missing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Application.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)app.ActiveWorkbook; Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; if (AddHeader) for (int i = 0; i < table.Columns.Count; i++) sheet.Cells[1, i + 1] = table.Columns[i].ColumnName; for (int i = 0, ei = AddHeader ? 2 : 1; i < table.Rows.Count; i++, ei++) for (int j = 0; j < table.Columns.Count; j++) sheet.Cells[ei, j + 1] = table.Rows[i][j]; string path = fileName.Substring(0, fileName.LastIndexOf('\\') + 1).Trim('\\'); if (!System.IO.Directory.Exists(path)) System.IO.Directory.CreateDirectory(path); book.SaveCopyAs(fileName); //关闭文件 book.Close(false, missing, missing); //退出excel app.Quit(); }
以上是Excl的导出。
再来看Excel的导入
/// <summary> /// 以Oledb的方式导入 /// </summary> /// <param name="fileName">包含excel文件的完整路径</param> /// <param name="sheetName">工作表名</param> /// <returns></returns> public static System.Data.DataTable ImportByOledb(string fileName, string sheetName) { System.Data.DataTable dt = new System.Data.DataTable(); using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0")) { OleDbCommand cmd = new OleDbCommand("select * from [" + sheetName + "$]", con); OleDbDataAdapter oda = new OleDbDataAdapter(cmd); oda.Fill(dt); } return dt; }
这里涉及到连接字符串问题，请看:
string strConn = @"Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + filePath + ";Extended Properties='Excel 8.0; HDR=Yes; IMEX=1'"; //此连接只能操作Excel2007之前(.xls)文件
string strCon = @"Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileName + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'"
对已不同的office版本，连接字符串也不同。
主意，当我们使用程序导出，然后通过Excel "Oledb"的方式导入的时候，会出现如下问题。且看:
这是程序导出时导致的非标准的excel文件的缘故，这个是程序导出的弊端。需要加以适当的处理。在导出的时候，这里由于时间原因，先介绍到这儿。
下面是我的ExcelOperation类:
/// <summary> /// Excelde导入和导出 /// </summary> public class ExcelOperation { #region 导出到Excel /// <summary> /// 程序的方式 /// </summary> /// <param name="ctl"></param> /// <param name="fileName"></param> public static void ExportByApplication(System.Web.UI.Control ctl, string fileName) { string style = @"<style> .text { mso-number-format:\@; } </script> "; HttpContext.Current.Response.Charset = "UTF-8"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + "" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.Default) + ".xls"); ctl.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(style); HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End(); } /// <summary> /// 程序的方式 /// </summary> /// <param name="page"></param> /// <param name="table"></param> /// <param name="FileName"></param> public static void ExportByApplication(System.Web.UI.Page page, System.Data.DataTable table, string FileName)//整个GRIDVIEW导出到EXCEL.xls { FileName = HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);//解决导出时文件名汉字显示乱码的问题 HttpResponse resp; resp = page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName); string colHeaders = "", ls_item = ""; //定义表对象与行对象，同时用DataSet对其值进行初始化 System.Data.DataRow[] myRow = table.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的 int i = 0; int cl = table.Columns.Count; //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符 for (i = 0; i < cl; i++) { if (i == (cl - 1))//最后一列，加n { colHeaders += table.Columns[i].Caption.ToString() + "\n"; } else { colHeaders += table.Columns[i].Caption.ToString() + "\t"; } } resp.Write(colHeaders); //向HTTP输出流中写入取得的数据信息 //逐行处理数据 foreach (DataRow row in myRow) { //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 for (i = 0; i < cl; i++) { if (i == (cl - 1))//最后一列，加n { ls_item += row[i].ToString() + "\n"; } else { ls_item += row[i].ToString() + "\t"; } } resp.Write(ls_item); ls_item = ""; } resp.End(); } //////////////////////////////////鉴于程序的方是比较多我就介绍两个，其目的最终都是将数据集导出 /// <summary> /// Oledb的方式 /// </summary> /// <param name="fileName">Excel文件名</param> /// <param name="table">要导出的DataTable</param> /// <param name="AddHeader">是否要增加表头</param> public static void ExporExportByOledbtExcel(string fileName, System.Data.DataTable table, bool AddHeader) { object missing = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); app.Application.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)app.ActiveWorkbook; Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet; if (AddHeader) for (int i = 0; i < table.Columns.Count; i++) sheet.Cells[1, i + 1] = table.Columns[i].ColumnName; for (int i = 0, ei = AddHeader ? 2 : 1; i < table.Rows.Count; i++, ei++) for (int j = 0; j < table.Columns.Count; j++) sheet.Cells[ei, j + 1] = table.Rows[i][j]; string path = fileName.Substring(0, fileName.LastIndexOf('\\') + 1).Trim('\\'); if (!System.IO.Directory.Exists(path)) System.IO.Directory.CreateDirectory(path); book.SaveCopyAs(fileName); //关闭文件 book.Close(false, missing, missing); //退出excel app.Quit(); } #endregion #region 从Excel导入 /// <summary> /// 以Oledb的方式导入 /// </summary> /// <param name="fileName">包含excel文件的完整路径</param> /// <param name="sheetName">工作表名</param> /// <returns></returns> public static System.Data.DataTable ImportByOledb(string fileName, string sheetName) { System.Data.DataTable dt = new System.Data.DataTable(); using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0")) { OleDbCommand cmd = new OleDbCommand("select * from [" + sheetName + "$]", con); OleDbDataAdapter oda = new OleDbDataAdapter(cmd); oda.Fill(dt); } return dt; } #endregion }
哈哈，时间有点晚了，菜鸟的我要睡觉了。准备明天悲哀的生活了。2012.10.16晚。
