c#导出Excel文件，多表加分页。。。
2010-06-04 17:23 cnb_mtime 阅读(6789) 评论(4) 编辑 收藏 举报最近项目中需要导出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;
}
}
}
}
