Mikel
[转载]c#导出Excel文件，多表加分页。。。 – ｛{°莪乖乖 – 博客园.
最近项目中需要导出Excel文件：
要求是这样的：
实现的没啥技术，就是调用了几个方法工具方法而已，以后备用
——————————-
——————————–
先看下基本功能吧–：
——————————–
——————————–
——————————–
——————————–
前台页面，只有俩按钮–~！
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ToExcle.aspx.cs" Inherits="Maps_ToExcle" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> <script type="text/javascript" src="static/jquery-1.4.1.min.js"></script> <script type="text/javascript"> var currpage; var downurl = ""; //下载文件地址 var timer = ""; //定时器 var runcount = 0; //要输出的>个数 $(function() {//初始化绑定 $("#btn_toExcel").click(function() { $(this).attr("disabled", "disabled"); saveToExcelData(); }); $("#btn_downExcel").click(function() { btn_downExcel() }); }) function getAjax(_url) {//ajax操作 $.ajax({ url: _url, type: 'get', beforeSend: function(XMLHttpRequest) { $('#ajaxloadimg,#ajax_msg').show(); timer = window.setInterval("intervalRun()", 500); }, success: function(data, textStatus) { downurl = data; $('#ajaxloadimg').hide(); $("#btn_downExcel").attr("disabled", ""); window.clearInterval(timer); $('#ajax_msg').text("生成完毕，如没有自动下载，请点击下载按钮。"); openfile(data); }, error: function(XMLHttpRequest, textStatus) { $("#btn_toExcel").attr("disabled", ""); $('#ajaxloadimg').hide(); window.clearInterval(timer); $('#ajax_msg').text("生成错误，请重试。"); }, complete: function(XMLHttpRequest, textStatus) { window.clearInterval(timer); } }); } function saveToExcelData() {//设置要请求的地址数据 var url = 'ToExcelHandler.ashx?' + new Date() + '&action=test'; getAjax(url); } function openfile(_serverPath) {//打开下载对话框 var _left = (screen.width - 350) / 2; var _top = (screen.height - 130 - 63) / 2; var a = window.open(_serverPath, "_blank", "scrollbars=yes, resizable=yes,menubar=no,toolbar=yes,width=350,height=130,left=" + _left + ",top=" + _top); a.document.execCommand("SaveAs"); //a.close(); } function intervalRun() {//定时运行方法，提示用 runcount++; if (runcount > 3) { runcount = 0; } var _str = ""; for (var _i = 1; _i <= runcount; _i++) { _str += ">"; } $("#ajax_msg").text("正在生成打印文件，请稍后" + _str); } function btn_downExcel() {//下载按钮事件 openfile(downurl); } </script> </head> <body> <form id="form1" runat="server"> <div> <input id="btn_toExcel" type="button" value="导出" /> <input id="btn_downExcel" type="button" value="下载" disabled="disabled" /> <img style='display: none;' id='ajaxloadimg' src='image/ajax-loader.gif' alt="请稍后" /> <span style="display: none; font-size: 12px; height: 20px; line-height: 20px;" id="ajax_msg"></span> </div> </form> </body> </html>
后台处理代码：
using System; using System.Web; using System.Reflection; using Microsoft.Office.Interop.Excel; using System.IO; using System.Collections; using System.Collections.Generic; using System.Web.SessionState; namespace prientExcel { public class ToExcelHandler : IHttpHandler, IRequiresSessionState { #region HttpRequest request = null; HttpResponse response = null; string action = string.Empty; object miss = Missing.Value; Dictionary<string, string> diction = new Dictionary<string, string>(); ArrayList arrayColName; ArrayList arrayShowName; ArrayList arrayType; dbcommeninit dbcmm = new dbcommeninit(); Application excelApp = null; Worksheet workSheet = null; string FilePath = @"打印数据.xls"; string DirPath = @"Download"; int TYPE = 0; System.Data.DataTable dt = null; List<string> tablename_list = new List<string>(); int recount = 0; int onpage = 1; int pagesize = 8; string tablename = ""; int LOOP1 = 1;//需要输出的分类数量 int LOOP2 = 1;//需要输出的工作表数量 #endregion public void ProcessRequest(HttpContext context) { request = context.Request; response = context.Response; action = request.Params["action"]; codeHandle(action); } #region 请求处理 private void codeHandle(string _action) { switch (_action) { case "test": createExcel(); break; default: break; } } #endregion private void DataTest() { tablename_list.Add("YG_PL_RPTDWZD"); tablename_list.Add("YG_PL_DIARYLEDGER"); tablename_list.Add("YG_PL_DWLEDGER"); //tablename_list.Add("YG_PL_KMYE"); //tablename_list.Add("YG_PL_WLLEDGER"); //tablename_list.Add("yg_pl_voucher"); } private void getDataTable(string _tablename) { recount = Convert.ToInt32(YG.DBUtility.DbHelperOra.ExecuteSql_obj("select count(*) from " + _tablename)); if (TYPE == 1) { recount = 10; } dt = YG.DBUtility.DbHelperOra.PageBind(_tablename, "", getColumns(_tablename), " id DESC", 20, onpage, out recount); dt.Columns.Remove("rk"); } #region Excel文件操作 /// <summary> /// 创建excel /// </summary> private void createExcel() { excelApp = new Application(); excelApp.Workbooks.Add(miss); DataTest(); int _workSheetCount = getWorkSheetCount();//获取需要的工作表数量 this.initExcel();//初始化EXCEL try { do { TYPE = LOOP1; workSheet = (Worksheet)excelApp.Worksheets[LOOP2]; workSheet.Activate(); tablename = tablename_list[LOOP1 - 1];//测试 getDataTable(tablename_list[LOOP1 - 1]); addDataExcelSheet(); LOOP1++; if (LOOP1 != tablename_list.Count + 1) { excelApp.Worksheets.Add(miss, (Worksheet)excelApp.Worksheets[LOOP2], miss, miss); LOOP2++; } workSheet = null; } while (LOOP1 != tablename_list.Count + 1); } catch (Exception ex) { } finally { getFilePath(); saveExcel(); resExcelStream(); } } /// <summary> /// Excel初始化，删除初始工作表，只留一个 /// </summary> /// <param name="_excelApp"></param> private void initExcel() { for (int i = excelApp.Worksheets.Count; i > 1; i--) { workSheet = (Worksheet)excelApp.Worksheets[i]; workSheet.Delete(); } } /// <summary> /// 保存excel文件 /// </summary> private void saveExcel() { workSheet = (Worksheet)excelApp.Worksheets[1]; workSheet.Activate(); Workbook _workBook = excelApp.Workbooks[1]; _workBook.RefreshAll(); string _truepath = HttpContext.Current.Server.MapPath(FilePath); System.IO.FileInfo _fi = new System.IO.FileInfo(_truepath); if (!Directory.Exists(_fi.DirectoryName))//判断目录是否存在 { Directory.CreateDirectory(_fi.DirectoryName); } if (System.IO.File.Exists(_truepath))//判断文件是否存在 { System.IO.File.Delete(_truepath); } _workBook.SaveAs(_truepath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss); _workBook.Close(false, miss, miss); _workBook = null; Kill(); GC.Collect(); } /// <summary> /// 杀死EXCEL进程 /// </summary> /// <param name="_eApp"></param> private void Kill() { IntPtr t = new IntPtr(excelApp.Hwnd); int k = 0; GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用 p.Kill(); //关闭进程k } [System.Runtime.InteropServices.DllImport("User32.dll", CharSet = System.Runtime.InteropServices.CharSet.Auto)] public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); /// <summary> /// 输出生成的EXCEL地址 /// </summary> /// <param name="_path">相对路径</param> private void resExcelStream() { System.IO.FileInfo _fi = new System.IO.FileInfo(HttpContext.Current.Server.MapPath(FilePath));//FilePath为文件在服务器上的地址 response.Clear(); response.Buffer = true; response.Charset = "GBK"; //设置了类型为中文防止乱码的出现 //response.AppendHeader("Content-Disposition", String.Format("attachment;filename={0}", _path)); //定义输出文件和文件名 //response.AppendHeader("Content-Length", _fi.Length.ToString()); response.ContentEncoding = System.Text.Encoding.Default; response.ContentType = "text/HTML";//设置输出文件类型。因为是ajax接收不了文件流，只能返回文件地址了。 response.Write(reques t.UrlReferrer.OriginalString.Substring(0, request.UrlReferrer.OriginalString.LastIndexOf("/") + 1) + FilePath); response.Flush(); response.End(); } #endregion #region 工作表样式设置及初始化 /// <summary> /// Sheet样式初始化 /// </summary> /// <param name="_type">标志哪一类工作表，如果为1，则是第一张工作表</param> /// <param name="_workSheet"></param> private void initExcelSheet(int _rowCount, int _colCount) { switch (TYPE) { case 1: this.setStyleSheet_1(_rowCount, _colCount); break; default: this.setStyleSheet_default(_rowCount, _colCount); break; } } /// <summary> /// 设置公共样式 /// </summary> /// <param name="_workSheet"></param> /// <param name="_rowCount"></param> /// <param name="_colCount"></param> private void setStyleSheet(int _rowCount, int _colCount) { workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[_rowCount, _colCount]).Columns.AutoFit();//自动适应宽度 workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[_rowCount, _colCount]).Borders.LineStyle = 1; workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[_rowCount, _colCount]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlLeft; //_workSheet.get_Range(_workSheet.Cells[1, 1], _workSheet.Cells[_rowCount, 1]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//设置左边线加粗 //_workSheet.get_Range(_workSheet.Cells[1, 1], _workSheet.Cells[1, _colCount]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//设置上边线加粗 //_workSheet.get_Range(_workSheet.Cells[1, _colCount], _workSheet.Cells[_rowCount, _colCount]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//设置右边线加粗 //_workSheet.get_Range(_workSheet.Cells[_rowCount, 1], _workSheet.Cells[_rowCount, _colCount]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//设置下边线加粗 //double top = 0;//上边距 //double left = 0;//左边距 //double right = 0;//右边距 //double footer = 0; //下边距 //workSheet.PageSetup.TopMargin = excelApp.InchesToPoints(top / 2.54);//上 //workSheet.PageSetup.BottomMargin = excelApp.InchesToPoints(footer / 2.54);//下 //workSheet.PageSetup.LeftMargin = excelApp.InchesToPoints(left / 2.54);//左 //workSheet.PageSetup.RightMargin = excelApp.InchesToPoints(right / 2.54);//右 workSheet.DisplayAutomaticPageBreaks = true;//显示分页线 workSheet.PageSetup.CenterFooter = "第 &P 页，共 &N 页"; workSheet.PageSetup.CenterHorizontally = true;//水平居中 //_workSheet.PageSetup.PrintTitleRows = "$1:$1";//顶端标题行 workSheet.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;//A4纸张大小 workSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;//纸张方向.纵向 } private void setStyleSheet_1(int _rowCount, int _colCount)//首页待打印属于特例，需要单独设置 { setStyleSheet(12 + _rowCount, 8);//页面设置 workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[12 + _rowCount, 8]).Borders.LineStyle = 0;//去掉所有边框 //实体数据上部表格设置 workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, 8]).MergeCells = true; workSheet.get_Range(workSheet.Cells[2, 1], workSheet.Cells[2, 8]).MergeCells = true; workSheet.get_Range(workSheet.Cells[3, 2], workSheet.Cells[3, 4]).MergeCells = true; workSheet.get_Range(workSheet.Cells[4, 2], workSheet.Cells[4, 4]).MergeCells = true; workSheet.get_Range(workSheet.Cells[3, 6], workSheet.Cells[3, 8]).MergeCells = true; workSheet.get_Range(workSheet.Cells[4, 6], workSheet.Cells[4, 8]).MergeCells = true; workSheet.get_Range(workSheet.Cells[5, 1], workSheet.Cells[5, 8]).MergeCells = true; workSheet.get_Range(workSheet.Cells[6, 1], workSheet.Cells[6, 8]).MergeCells = true; workSheet.get_Range(workSheet.Cells[7, 1], workSheet.Cells[7, 8]).MergeCells = true; //实体数据部分 for (int i = 7; i <= _rowCount + 7 + 1; i++) { workSheet.get_Range(workSheet.Cells[i, 1], workSheet.Cells[i, 4]).MergeCells = true; //workSheet.get_Range(workSheet.Cells[i, 3], workSheet.Cells[i, 4]).MergeCells = true; workSheet.get_Range(workSheet.Cells[i, 5], workSheet.Cells[i, 8]).MergeCells = true; } workSheet.get_Range(workSheet.Cells[8, 1], workSheet.Cells[7 + _rowCount, 8]).Borders.LineStyle = 1; //接收人上下表格设置 workSheet.get_Range(workSheet.Cells[8 + _rowCount, 1], workSheet.Cells[8 + _rowCount, 8]).MergeCells = true; workSheet.get_Range(workSheet.Cells[9 + _rowCount, 1], workSheet.Cells[9 + _rowCount, 8]).MergeCells = false; workSheet.get_Range(workSheet.Cells[9 + _rowCount, 2], workSheet.Cells[9 + _rowCount, 8]).MergeCells = true; //workSheet.get_Range(workSheet.Cells[9 + _rowCount, 2], workSheet.Cells[9 + _rowCount, 8]).Borders.LineStyle = 1; workSheet.get_Range(workSheet.Cells[10 + _rowCount, 1], workSheet.Cells[10 + _rowCount, 8]).MergeCells = true; //审核接收意见部分设置 workSheet.get_Range(workSheet.Cells[11 + _rowCount, 1], workSheet.Cells[11 + _rowCount, 8]).MergeCells = true; workSheet.get_Range(workSheet.Cells[12 + _rowCount, 1], workSheet.Cells[12 + _rowCount, 1]).MergeCells = true; workSheet.get_Range(workSheet.Cells[12 + _rowCount, 2], workSheet.Cells[12 + _rowCount, 4]).MergeCells = true; workSheet.get_Range(workSheet.Cells[12 + _rowCount, 5], workSheet.Cells[12 + _rowCount, 5]).MergeCells = true; workSheet.get_Range(workSheet.Cells[12 + _rowCount, 6], workSheet.Cells[12 + _rowCount, 8]).MergeCells = true; workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, 8]).Font.Size = 28; workSheet.get_Range(workSheet.Cells[5, 1], workSheet.Cells[5, 1]).Font.Size = 16; workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, 8]).Font.Bold = true; workSheet.get_Range(workSheet.Cells[6, 1], workSheet.Cells[6, 1]).Font.Bold = true; workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, 8]).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; workSheet.get_Range(workSheet.Cells[8, 1], workSheet.Cells[8, 8]).Interior.ColorIndex = 15; workSheet.Cells[1, 1] = "移交单"; workSheet.Cells[2, 1] = "移交信息:"; workSheet.Cells[3, 1] = "移交单号:"; workSheet.Cells[3, 2] = "_________________________"; workSheet.Cells[3, 5] = "移交日期:"; workSheet.Cells[3, 6] = "_________________________"; workSheet.Cells[4, 1] = "移 交 人:"; workSheet.Cells[4, 2] = "_________________________"; workSheet.Cells[4, 5] = "所属部门:"; workSheet.Cells[4, 6] = "_________________________"; workSheet.Cells[6, 1] = @"目录结构\..\.."; workSheet.Cells[7, 1] = @"移交内容:"; workSheet.Cells[8, 1] = @"分类"; workSheet.Cells[8, 5] = @"数量"; workSheet.Cells[9 + _rowCount, 1] = @"接收人:"; workSheet.Cells[9 + _rowCount, 2] = "_________________________"; workSheet.Cells[11 + _rowCount, 1] = @"意见:"; workSheet.Cells[12 + _rowCount, 1] = @"提交意见"; workSheet.Cells[12 + _rowCount, 2] = "_________________________"; workSheet.Cells[12 + _rowCount, 5] = @"接收意见"; workSheet.Cells[12 + _rowCount, 6] = "_________________________"; } private void setStyleSheet_default(int _rowCount, int _colCount) { this.setStyleSheet(_rowCount, _colCount); workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[1, _colCount]).Font.Bold = true; workSheet.PageSetup.PrintTitleRows = "$1:$1";//设置打印表头 } #endregion #region 向工作表添加数据 /// <summary> /// 向工作表添加数据 /// </summary> /// <param name="_type"></param> /// <param name="_workSheet"></param> /// <param name="_dt"></param> private void addDataExcelSheet() { switch (TYPE) { case 1: this.addDataSheet_1(); break; default: this.addDataSheet_default(); break; } } private void addDataSheet_1() { workSheet.Name = "移交单"; initExcelSheet(dt.Rows.Count, dt.Columns.Count); } private void addDataSheet_default() { int _pagecount = getPageCount(pagesize, dt.Rows.Count); System.Data.DataTable _dt = null; for (int _i = 1; _i <= _pagecount; _i++)//分页打印数据 { workSheet.Name = tablename + "-" + _i;//重命名工作表 _dt = new System.Data.DataTable(); _dt = GetPagedTable(pagesize, _i);//获取分页（工作表）数据 initExcelSheet(_dt.Rows.Count + 1, _dt.Columns.Count);//当前工作表初始化 for (int i = 1; i <= _dt.Rows.Count + 1; i++)//要打印的工作表行数（由于加上表头，所以加1） { for (int j = 1; j <= _dt.Columns.Count; j++)//待打印数据的列数 { if (i == 1)//设置表头数据 { workSheet.Cells[i, j] = diction.ContainsKey(_dt.Columns[j - 1].ColumnName.ToLower()) ? diction[_dt.Columns[j - 1].ColumnName.ToLower()] : ""; } else//其余行 { workSheet.Cells[i, j] = _dt.Rows[i - 2][j - 1]; } } } if (_i != _pagecount)//判断待打印数据的当前页是不是最后一页，不是则在当前工作表后添加一张工作表 { excelApp.Worksheets.Add(miss, (Worksheet)excelApp.Worksheets[LOOP2], miss, miss); LOOP2++; workSheet = (Worksheet)excelApp.Worksheets[LOOP2]; workSheet.Activate(); } } } #endregion #region GetPagedTable DataTable分页 /// <summary> /// DataTable分页 /// </summary> /// <param name="dt">DataTable</param> /// <param name="PageIndex">页索引,注意：从1开始</param> /// <param name="PageSize">每页大小</param> /// <returns></returns> public System.Data.DataTable GetPagedTable(int _PageSize, int _PageIndex) { if (_PageIndex == 0) return dt; System.Data.DataTable newdt = dt.Copy(); newdt.Clear(); int rowbegin = (_PageIndex - 1) * _PageSize; int rowend = _PageIndex * _PageSize; if (rowbegin >= dt.Rows.Count) return newdt; if (rowend > dt.Rows.Count) rowend = dt.Rows.Count; for (int i = rowbegin; i <= rowend - 1; i++) { System.Data.DataRow newdr = newdt.NewRow(); System.Data.DataRow dr = dt.Rows[i]; foreach (System.Data.DataColumn column in dt.Columns) { newdr[column.ColumnName] = dr[column.ColumnName]; } newdt.Rows.Add(newdr); } return newdt; } #endregion /// <summary> ///计算总页数,循环添加数据 /// </summary> /// <param name="_pagesize"></param> /// <param name="_recount"></param> /// <returns></returns> private int getPageCount(int _pagesize, int _recount) { int _pagecount = 0; _pagecount = (_recount + _pagesize - 1) / _pagesize; if (_pagecount == 0) { _pagecount = 1; } return _pagecount; } /// <summary> /// 计算工作表数量（此时返回数量不包括将来分页后的数量） /// </summary> private int getWorkSheetCount() { int _workSheetCount = tablename_list.Count; if (_workSheetCount > excelApp.Sheets.Count) { return _workSheetCount; } return _workSheetCount; } /// <summary> /// 添加表头,作废，暂时只用于插入一行改变dt的行数 /// </summary> private void insertTableName() { System.Data.DataRow _dr = dt.NewRow(); //for (int i = 0; i < _dt.Columns.Count; i++) //{ // _dr[i] = diction.ContainsKey(_dt.Columns[i].ColumnName.ToLower()) ? diction[_dt.Columns[i].ColumnName.ToLower()] : _dt.Columns[i].ColumnName; //} dt.Rows.InsertAt(_dr, 0); } /// <summary> /// 检索表信息,取得汉化表头信息 /// </summary> /// <returns>返回select字段</returns> private string getColumns(string _tabName) { string allColumns = dbcmm.f_str0(_tabName).ToLower(); arrayColName = dbcmm.getFieldName0(allColumns, 0); arrayShowName = dbcmm.getFieldName0(allColumns, 1); arrayType = dbcmm.getFieldName0(allColumns, 2); string _select = dbcmm.getColsName0(arrayColName, arrayType); ArryToDict(arrayColName, arrayShowName); return _select; } /// <summary> /// 获取ColName和ShowName键值对 /// </summary> /// <param name="_arrayColName">列名</param> /// <param name="_arrayShowName">汉化名</param> private void ArryToDict(ArrayList _arrayColName, ArrayList _arrayShowName) { for (int _i = 0; _i < _arrayColName.Count; _i++) { if (!diction.ContainsKey(_arrayColName[_i].ToString())) { diction.Add(_arrayColName[_i].ToString(), _arrayShowName[_i].ToString()); } } } private void getFilePath() { string _fileName = string.Empty; _fileName = DateTime.Now.ToString("yyyyMMddhhmmss"); FilePath = DirPath + "/" + _fileName + ".xls"; } public bool IsReusable { get { return false; } } } }
