Asp.Net使用org.in2bits.MyXls.dll操作excel的应用 （转）

转：http://www.cnblogs.com/panmy/p/5874950.html

首先下载org.in2bits.MyXls.dll(自己的在~About ASP.NetAsp.Net操作excel)

添加命名空间：

using org.in2bits.MyXls;
using System.IO;

思路：

 添加引用 (using org.in2bits.MyXls)→  创建空xls文档(XlsDocument) → 得到数据 →  创建一个工作页(Worksheet) →  设置xls文档的指定工作页的行(RowInfo) →  设置xls文档的指定工作页的列(ColumnInfo) →  创建列样式创建列时引用(XF) → 列位置排好 →  增加数据(ws.Cells.Add) 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using org.in2bits.MyXls;
using System.Web;
using System.IO;

namespace LowProtetction.Bizlogic
{
    public class DB_CommunityInfo
    {
        /// <summary>
        /// 自定义导出
        /// </summary>
        /// <param name="Condition">查询条件</param>
        public static void DaoChuExecl( string therCondtion)
        {
            string filename = "自定义导出";
            DataView dv = null;
            XlsDocument doc = new XlsDocument(); //创建空xls文档
            string strSql = "select BelongCity,CommunityCode,CommunityName,CommunityAddress,Remark,AddUserName from CommunityInfo where 1=1 " + therCondtion + "";
            dv = Epoint.MisBizLogic2.DB.ExecuteDataView(strSql);
            ExcelExport(filename,dv.ToTable(), doc, 2, 1);


            doc.FileName = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);
            doc.Send();
        }
        private static void ExcelExport(string workSheetName, DataTable dt, XlsDocument xls, int startrow, int displaynum) 
        {

            //创建一个工作页为Dome   
            Worksheet ws = xls.Workbook.Worksheets.Add(workSheetName);

            #region  设置xls文档的指定工作页的行属性
            RowInfo rol1top1 = new RowInfo();
            rol1top1.RowHeight = 50 * 20;
            rol1top1.RowIndexStart = 0;
            rol1top1.RowIndexEnd = 1;
            ws.AddRowInfo(rol1top1);

            RowInfo rol1top2 = new RowInfo();
            rol1top2.RowHeight = 24 * 20;
            rol1top2.RowIndexStart = 2;
            rol1top2.RowIndexEnd = (ushort)(dt.Rows.Count + 5); //到最后一行
            ws.AddRowInfo(rol1top2); 
            #endregion

            #region   设置xls文档的指定工作页的列属性 
            //所属城市 1  
            ColumnInfo colInfo0 = new ColumnInfo(xls, ws);
            colInfo0.ColumnIndexStart = 0;
            colInfo0.ColumnIndexEnd = 1;
            colInfo0.Width = 15 * 256;
            ws.AddColumnInfo(colInfo0);

            //社区编号
            ColumnInfo colInfo1 = new ColumnInfo(xls, ws);
            colInfo1.ColumnIndexStart = 1;
            colInfo1.ColumnIndexEnd = 2;
            colInfo1.Width = 15 * 256;
            ws.AddColumnInfo(colInfo1);

            //名称
            ColumnInfo colInfo12 = new ColumnInfo(xls, ws);
            colInfo12.ColumnIndexStart = 2;
            colInfo12.ColumnIndexEnd = 3;
            colInfo12.Width = 15 * 256;
            ws.AddColumnInfo(colInfo12);
            //社区地址
            ColumnInfo colInfo2 = new ColumnInfo(xls, ws);
            colInfo2.ColumnIndexStart = 3;
            colInfo2.ColumnIndexEnd = 4;
            colInfo2.Width = 15 * 256;
            ws.AddColumnInfo(colInfo2);

            // 社区介绍
            ColumnInfo colInfo3 = new ColumnInfo(xls, ws);
            colInfo3.ColumnIndexStart = 4;
            colInfo3.ColumnIndexEnd = 5;
            colInfo3.Width = 15 * 256;
            ws.AddColumnInfo(colInfo3);


            //登记人
            ColumnInfo colInfo6 = new ColumnInfo(xls, ws);
            colInfo6.ColumnIndexStart = 5;
            colInfo6.ColumnIndexEnd = 7;
            colInfo6.Width = 15 * 256;
            ws.AddColumnInfo(colInfo6); 
             
            #endregion 

            #region 创建列样式创建列时引用
            XF xfhead = xls.NewXF();
            xfhead.HorizontalAlignment = HorizontalAlignments.Centered;
            xfhead.VerticalAlignment = VerticalAlignments.Centered;
            xfhead.Font.Bold = false;
            xfhead.Font.FontName = "宋体";
            xfhead.Font.Height = 16 * 20;
            //自动换行
            xfhead.TextWrapRight = true;
            #endregion
            #region Excel导出的头 
            //Worksheet,单元格样式，列名，开始行，开始列，结束行，结束列
             MergeRegion(ref ws, xfhead, "自定义导出", 1, 1, 1, dt.Columns.Count);
            #endregion

             #region 创建单元格样式
             //第一种单元格样式
             XF xf = xls.NewXF();
             xf.HorizontalAlignment = HorizontalAlignments.Centered;
             xf.VerticalAlignment = VerticalAlignments.Centered;
             xf.Pattern = 1;////设定单元格填充风格。如果设定为0，则是纯色填充
             xf.PatternColor = Colors.White;//设定填充线条的颜色 
             xf.UseBorder = true;
             //设置列的上下左右的样式
             xf.TopLineStyle = 1;
             xf.TopLineColor = Colors.Black;
             xf.BottomLineStyle = 1;
             xf.BottomLineColor = Colors.Black;
             xf.LeftLineStyle = 1;
             xf.LeftLineColor = Colors.Black;
             xf.RightLineStyle = 1;
             xf.RightLineColor = Colors.Black;
             xf.Font.Bold = true;
             xf.Font.FontName = "仿宋";
             xf.Font.Height = 12 * 20;
             //自动换行
             xf.TextWrapRight = true;

          
             #endregion
             
             #region 列位置排好
             int ViewStatestartrow = startrow;
             //Worksheet,单元格样式，列名，开始行，开始列，结束行，结束列
             MergeRegion(ref ws, xf, "所属城市", ViewStatestartrow, 1, ViewStatestartrow, 1); 
             MergeRegion(ref ws, xf, "社区编号", ViewStatestartrow, 2, ViewStatestartrow, 2); 
             MergeRegion(ref ws, xf, "社区名称", ViewStatestartrow, 3, ViewStatestartrow, 3); 
             MergeRegion(ref ws, xf, "社区地址", ViewStatestartrow, 4, ViewStatestartrow, 4); 
             MergeRegion(ref ws, xf, "社区介绍", ViewStatestartrow, 5, ViewStatestartrow, 5); 
             MergeRegion(ref ws, xf, "登记人", ViewStatestartrow, 6, ViewStatestartrow, 6);

             #endregion



             //第二种单元格样式
             XF xf1 = xls.NewXF();
             xf1 = xf;
             xf1.Font.Bold = false;
             xf1.Font.FontName = "仿宋_GB2312";
             xf1.Font.Height = 10 * 20;
             int ViewStateEndrow = int.Parse(ViewStatestartrow.ToString()); 
             int dtcolcount = dt.Columns.Count;

               //循环数据表  逐行添加数据
            //ViewStatestartrow 为开始行
             for (int i = 0; i < dt.Rows.Count; i++)
             {
                 int StatRow = i + ViewStatestartrow + 1;

                 for (int k = 0; k < dt.Columns.Count; k++)
                 {
                     //行，列，列值，单元格
                     ws.Cells.Add(StatRow, k + 1, dt.Rows[i][k].ToString(), xf1);//给单元格赋值： 
                 }
                 ViewStateEndrow = StatRow;
             }

        }

        /// <summary>
        /// 格式设置 合并
        /// </summary>
        /// <param name="ws">Worksheet </param>
        /// <param name="xf"> </param>
        /// <param name="title"> 列名</param>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        /// <param name="endRow">结束行</param>
        /// <param name="endCol">结束列</param>
        public static void MergeRegion(ref Worksheet ws, XF xf, string title, int startRow, int startCol, int endRow, int endCol)
        {
            for (int i = startCol; i <= endCol; i++)
            {
                for (int j = startRow; j <= endRow; j++)
                {
                    //行，列，列值，单元格
                    ws.Cells.Add(j, i, title, xf); 
                }
            }
            ws.Cells.Merge(startRow, endRow, startCol, endCol);
        }
    }
}

还有一个重要的一点就是，弹出下载页面的时候你电脑要刷屏，这样是消失不见，而你加了UpdatePanel的话也会刷掉

重点来了

 //通过此方法，我们可以把某个位于UpdatePanel中的控件，改为不触发异步Postback，而是触发传统的Postback事件
            //不然会把下载框刷新调
            ((ScriptManager)Master.FindControl("ScriptManager1")).RegisterPostBackControl(btnExport);