NET导出Excel文件

.NET导出Excel文件

最近做ASP.NET Excel导出功能，顺便整理了一下可用的一些导出Excel方法 一般导出方式

1，客户提出要将统计的结果导出到excel文件，首先利用如下方式：

增加 <%@page contentType=\头部说明，然后放一个table在该页面中即可了。

2，客户看过后提出了改进意见，要求保存文件的时候自动给文件一个名称，这样可以直接保存。 改进如下：

增加 response.setHeader(\\但是却又遇到了乱码的问题：

response.setHeader(\

\将文件名称的编码由GBK转换为ISO-8859-1就正常了。 GridView导出Excel

尝试了一下Gridview导出为Excel，原本以为很简单，可是真正应用起来还是不太好弄的，呵呵，所想非所得。总结了一下应该注意下面几点：

1.由于gridview的内容可能是分页显示的，因此，这里在每次导出excel时，先将gridview的allowpaging属性设置为false，然后databind()一下，确保搂到所有数据； 2.不用单独设置导出的路径，导出时会弹出对话框让你确认保存位置；

3.要写一个空的VerifyRenderingInServerForm方法（必须写），以确认在运行时为指定的ASP.NET 服务器控件呈现HtmlForm 控件； 4.导出后别忘记再重新设置其allowpaging属性；

当我把这些都设置好以后，点击[导出]，出现了 只能在执行 Render() 的过程中调用 RegisterForEventValidation(RegisterForEventValidation can only be called during

Render(); ) 的错误，又检查代码，没发现问题啊，搞了一会弄不出来，然后搜索了一下，发现了解决办法：

修改你的aspx文件中的：

<%@ Page Language=\EnableEventValidation = \ AutoEventWireup=\CodeFile=\增加红色的部分就ok了。 下面是代码和截图： #region 导出为Excel

public override void VerifyRenderingInServerForm(Control control) {

// Confirms that an HtmlForm control is rendered for }

private void ToExcel(Control ctl, string FileName) {

HttpContext.Current.Response.Charset = \

HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; HttpContext.Current.Response.ContentType = \ HttpContext.Current.Response.AppendHeader(\\ ctl.Page.EnableViewState = false;

System.IO.StringWriter tw = new System.IO.StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); ctl.RenderControl(hw);

HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End(); }

private void toExcelClk() {

gvSysUser.AllowPaging = false; gvSysUser.AllowSorting = false; gvSysUser.DataBind();

ToExcel(gvSysUser, \ gvSysUser.AllowPaging = true; gvSysUser.AllowSorting = true; gvSysUser.DataBind(); }

#endregion

最新ASP.NET导出EXCEL类

说明：可以导出ASP.NET页面和DATAGRID（WebControl）数据，可以导出表单头

using System;

using System.Data; using System.Text; using System.Web; using System.Web.UI; using System.Diagnostics;

using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Collections;

namespace bookstore { ///

/// Excel 的摘要说明。 ///

public class Excel {

public Excel() { //

// TODO: 在此处添加构造函数逻辑 // }

public void SaveToExcel(Page myPage, DataTable dt,DataGrid DG,string myExcelHeader,HtmlTable Tab,string myFileName) {

HttpResponse resp; resp=myPage.Response;

resp.ContentEncoding=Encoding.GetEncoding(\

resp.AppendHeader(\);

resp.ContentType=\

string colHeaders = \ colHeaders+=tableHeader(Tab)+\ StringBuilder sb=new StringBuilder();

int mycol=DG.Columns.Count; ArrayList myAL=new ArrayList();

for(int i=0;i {

colHeaders +=DG.Columns[i].HeaderText+\

myAL.Add(((System.Web.UI.WebControls.BoundColumn)(DG.Columns[i])).DataField); }

colHeaders += \

sb.Append(colHeaders); int myrow=dt.Rows.Count; for(int k=0;k {

foreach(string field in myAL) {

sb.Append(dt.Rows[k][field]); sb.Append(\ }

sb.Append(\ }

colHeaders=sb.ToString(); colHeaders=colHeaders+\ resp.Write(colHeaders); resp.End(); resp.Clear(); resp.Close(); }

/*得到表单头子*/

/*表单头子有TABLE组成，偶次项排列，TABLE在HTML中加 RUNAT=SERVER*/ public string tableHeader(HtmlTable Tab) {

int iCols=Tab.Rows[0].Cells.Count; int iRows=Tab.Rows.Count; string str=\

for(int row=0;row { for(int col=0;col {

if(col%2==1)//取偶次项的控件数据（目前只有TextBox和DropDownList，没有包含LABEL） { try {

if(Tab.Rows[row].Cells[col].Controls[0].ToString()==\ {

if(Tab.Rows[row].Cells[col].Controls[1].ToString()==\extBox\

{

str+=((System.Web.UI.WebControls.TextBox)(Tab.Rows[row].Cells[col].Controls[1])).Text+\ }

if(Tab.Rows[row].Cells[col].Controls[1].ToString()==\DropDownList\ {

str+=((System.Web.UI.WebControls.ListControl)(((System.Web.UI.WebControls.DropDownList)((Tab.Rows[row].Cells[col].Controls[1]))))).SelectedValue+\ } } else {

if(Tab.Rows[row].Cells[col].Controls[0].ToString()==\extBox\ {

str+=((System.Web.UI.WebControls.TextBox)(Tab.Rows[row].Cells[col].Controls[0])).Text+\ }

if(Tab.Rows[row].Cells[col].Controls[0].ToString()==\DropDownList\ {

str+=((System.Web.UI.WebControls.ListControl)(((System.Web.UI.WebControls.DropDownList)((Tab.Rows[row].Cells[col].Controls[0]))))).SelectedValue+\ } } }

catch {

str+=Tab.Rows[row].Cells[col].InnerHtml+\ }

if((col+1)%iCols==0) {

str+=\ } } else {

str+=\ } } }

return(str); }

} }

ASP.NET导出Excel、World文档

对于大量的数据，有时候导出到Excel中将更加方便进行数据统计分析，而对于排版打印则导出到World文档中更加方便。在ASP.NET可以通过少量代码实现这两种导出。 新建一页面，该页面布局Html源码如下：

这里没有什么特别的，只需要注意在线打开与本地保存两个按钮通过CommandName进行检验并触发相应的动作，DropDownList控件存放了可以导出的格式，可以自己添加如txt、html等格式。准备工作就绪以后，可以进行代码的书写，以完成我们需要的功能： protected void Page_Load(object sender, EventArgs e) {

FillGridView(); }

private void OutPut(string fileType, string strType) {

Response.Clear();

Response.Buffer = true;

Response.Charset = \

Response.AppendHeader(\ Response.ContentType = strType; this.EnableViewState = false;

System.IO.StringWriter swOut = new System.IO.StringWriter(); HtmlTextWriter hTw = new HtmlTextWriter(swOut);

myGW.RenderControl(hTw);

Response.Write(swOut.ToString()); Response.End(); }

protected void Button_Click(object sender, CommandEventArgs e) {

switch (e.CommandName) {

case \

switch (listType.SelectedValue) {

case \

OutPut(\; break; case \

OutPut(\ break; }

break; case \

switch (listType.SelectedValue) {

case \

OutPut(\ break; case \

OutPut(\ break; }

break; } }

public override void VerifyRenderingInServerForm(Control control) { }

private void FillGridView() {

string strConn = \id=sa;password=sa123\

SqlConnection conn = new SqlConnection(strConn); SqlCommand comm = new SqlCommand(); comm.Connection = conn;

string strSql = \ comm.CommandText = strSql;

conn.Open();

this.myGW.DataSource = comm.ExecuteReader(); this.myGW.DataBind(); conn.Close(); }

在以上的程序中，最主要的就是函数OutPut(string fileType, string strType)，它完成导出或在线打开的动作，这里接受两个参数其中fileType指定是保存或在线打开，并且指名了文件的名字如：attachment;filename=out.xls（作为附件保存，文件名为out.xls）、online;filename=out.xls（在线打开）；strType指定了带出文档的格式如application/ms-excel。

注意如果想要导出GridView中的数据并且要我们的程序顺利执行，则必须重写VerifyRenderingInServerForm(Control control)，否则会报错的。

如果我们想要将整个页面导出到Excel中，则不需要重写

VerifyRenderingInServerForm，只需要将OutPut函数中的myGW.RenderControl(hTw)改为this.RenderControl(hTw)就可以了。

完成以上工作，在浏览器中测试，我们想要的导出功能就可以很好的工作了。 利用Office中导出的Excel.dll（DCOM方式）

这个类可以很好的控制Excel。读出内容想怎么导就怎么导 ///

public class ExcelHander {

public ExcelHander() { //

// TODO: Add constructor logic here // }

private string AList=\

public string GetAix(int x,int y) {

char [] AChars=AList.ToCharArray(); if(x>=26){return \ string s=\

s=s+AChars[x-1].ToString(); s=s+y.ToString(); return s; }

public void setValue(int x,int y,string align,string text) {

Excel.Range range=sheet.get_Range(this.GetAix(x,y),miss); range.set_Value(miss,text); if(align.ToUpper()==\ {

range.HorizontalAlignment=Excel.XlHAlign.xlHAlignCenter; }

if(align.ToUpper()==\ {

range.HorizontalAlignment=Excel.XlHAlign.xlHAlignLeft; }

if(align.ToUpper()==\ {

range.HorizontalAlignment=Excel.XlHAlign.xlHAlignRight; } }

public void setValue(int x,int y,string text) {

Excel.Range range=sheet.get_Range(this.GetAix(x,y),miss); range.set_Value(miss,text); }

public void setValue(int x,int y,string text,System.Drawing.Font font,System.Drawing.Color color) {

this.setValue(x,y,text);

Excel.Range range=sheet.get_Range(this.GetAix(x,y),miss); range.Font.Size=font.Size; range.Font.Bold=font.Bold; range.Font.Color=color;

range.Font.Name=font.Name; range.Font.Italic=font.Italic;

range.Font.Underline=font.Underline; }

public void insertRow(int y) {

Excel.Range range=sheet.get_Range(GetAix(1,y),GetAix(25,y)); range.Copy(miss);

range.Insert(Excel.XlDirection.xlDown,miss); range.get_Range(GetAix(1,y),GetAix(25,y)); range.Select();

sheet.Paste(miss,miss);

}

public void past() {

string s=\

sheet.Paste(sheet.get_Range(this.GetAix(10,10),miss),s); }

public void setBorder(int x1,int y1,int x2,int y2,int Width) {

Excel.Range range=sheet.get_Range(this.GetAix(x1,y1),this.GetAix(x2,y2)); range.Borders.Weight=Width; }

public void mergeCell(int x1,int y1,int x2,int y2) {

Excel.Range range=sheet.get_Range(this.GetAix(x1,y1),this.GetAix(x2,y2)); range.Merge(true); }

public Excel.Range getRange(int x1,int y1,int x2,int y2) {

Excel.Range range=sheet.get_Range(this.GetAix(x1,y1),this.GetAix(x2,y2)); return range; }

private Missing miss=Missing.Value; //忽略的参数OLENULL public static Missing MissValue=Missing.Value;

private Excel.Application m_objExcel;//Excel应用程序实例 private Excel.Workbooks m_objBooks;//工作表集合 private Excel.Workbook m_objBook;//当前操作的工作表 private Excel.Worksheet sheet;//当前操作的表格

public Excel.Worksheet CurrentSheet { get {

return sheet; } set {

this.sheet=value; } }

public Excel.Workbooks CurrentWorkBooks {

get {

return this.m_objBooks; } set {

this.m_objBooks=value; } }

public Excel.Workbook CurrentWorkBook { get {

return this.m_objBook; } set {

this.m_objBook=value; } }

public void OpenExcelFile(string filename) {

m_objExcel = new Excel.Application(); UserControl(false);

m_objExcel.Workbooks.Open( filename, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);

m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;

m_objBook = m_objExcel.ActiveWorkbook;

sheet = (Excel.Worksheet)m_objBook.ActiveSheet; }

public void UserControl(bool usercontrol) {

if(m_objExcel==null){return ;}

m_objExcel.UserControl=usercontrol; m_objExcel.DisplayAlerts=usercontrol; m_objExcel.Visible = usercontrol; }

public void CreateExceFile() {

m_objExcel = new Excel.Application(); UserControl(false);

m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel.Workbook)(m_objBooks.Add(miss)); sheet = (Excel.Worksheet)m_objBook.ActiveSheet; }

public void SaveAs(string FileName) {

m_objBook.SaveAs(FileName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange,

Excel.XlSaveConflictResolution.xlLocalSessionChanges, miss,miss, miss, miss);

//m_objBook.Close(false, miss, miss); }

public void ReleaseExcel() {

m_objExcel.Quit();

Marshal.ReleaseComObject(m_objExcel); Marshal.ReleaseComObject(m_objBooks); Marshal.ReleaseComObject(m_objBook); Marshal.ReleaseComObject(sheet); GC.Collect(); } } 前台

<%@ Page Language=\ AutoEventWireup=\ CodeFile=\Excle.aspx.cs\ Inherits=\ %>

后台处理代码：

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 diction = new Dictionary();

ArrayList arrayColName; ArrayList arrayShowName; ArrayList arrayType;

dbcommeninit dbcmm = new dbcommeninit();

Application excelApp = null; Worksheet workSheet = null; string FilePath = @\打印数据.xls\; string DirPath = @\; int TYPE = 0;

System.Data.DataTable dt = null;

List tablename_list = new List();

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[\]; codeHandle(action); }

#region 请求处理

private void codeHandle(string _action) {

switch (_action) {

case \: createExcel(); break; default: break; } }

#endregion

private void DataTest() {

tablename_list.Add(\); tablename_list.Add(\); tablename_list.Add(\); //tablename_list.Add(\ //tablename_list.Add(\ //tablename_list.Add(\ }

private void getDataTable(string _tablename) {

recount = Convert.ToInt32(YG.DBUtility.DbHelperOra.ExecuteSql_obj(\ + _tablename));

if (TYPE == 1) {

recount = 10; }

dt = YG.DBUtility.DbHelperOra.PageBind(_tablename, \, getColumns(_tablename), \, 20, onpage, out recount);

dt.Columns.Remove(\);

}

#region Excel文件操作 ///

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(); }

}

///

///

for (int i = excelApp.Worksheets.Count; i > 1; i--)

{

workSheet = (Worksheet)excelApp.Worksheets

[i];

workSheet.Delete(); } }

///

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(); }

///

///

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(\ll\, CharSet = System.Runtime.InteropServices.CharSet.Auto)]

public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID); ///

/// private void resExcelStream() {

System.IO.FileInfo _fi = new System.IO.FileInfo(HttpContext.Current.Server.MapPath(FilePath));//FilePath为文件在服务器上的地址

response.Clear(); response.Buffer = true;

response.Charset = \; //设置了类型为中文防止乱码的出现

//response.AppendHeader(\String.Format(\定义输出文件和文件名

//response.AppendHeader(\ength.ToString());

response.ContentEncoding = System.Text.Encoding.

Default;

response.ContentType = \;//设置输出文件类型。因为是ajax接收不了文件流，只能返回文件地址了。

response.Write(reques t.UrlReferrer.OriginalString.Substring(0, request.UrlReferrer.OriginalString.LastIndexOf(\) + 1) + FilePath); response.Flush(); response.End(); }

#endregion

#region 工作表样式设置及初始化 ///

///

///

private void initExcelSheet(int _rowCount, int _colCount) {

switch (TYPE) {

case 1:

this.setStyleSheet_1(_rowCount, _colCount);

break; default:

this.setStyleSheet_default(_rowCount, _colCount);

break; } }

///

///

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 = \//顶端标题行

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 + _rowCoun

t, 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 = \;//设置打印表头 }

#endregion

#region 向工作表添加数据 ///

///

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.Co

ntainsKey(_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分页 ///

///

///

/// ///

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

///

///

///

private int getPageCount(int _pagesize, int _recount) {

int _pagecount = 0;

_pagecount = (_recount + _pagesize - 1) / _pagesize;

if (_pagecount == 0) {

_pagecount = 1; }

return _pagecount; }

///

private int getWorkSheetCount() {

int _workSheetCount = tablename_list.Count;

if (_workSheetCount > excelApp.Sheets.Count) {

return _workSheetCount; }

return _workSheetCount; }

///

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); }

///

/// 返回select字段 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; }

///

/// /// 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(\s\);

FilePath = DirPath + \ + _fileName + \;

}

public bool IsReusable { get {

return false; } } } }

本文来源：https://www.bwwdw.com/article/3yiw.html