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

            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 =  * ;

            rol1top1.RowIndexStart = ;

            rol1top1.RowIndexEnd = ;

            ws.AddRowInfo(rol1top1);

            RowInfo rol1top2 = new RowInfo();

            rol1top2.RowHeight =  * ;

            rol1top2.RowIndexStart = ;

            rol1top2.RowIndexEnd = (ushort)(dt.Rows.Count + ); //到最后一行

            ws.AddRowInfo(rol1top2);

            #endregion

            #region   设置xls文档的指定工作页的列属性

            //所属城市 1

            ColumnInfo colInfo0 = new ColumnInfo(xls, ws);

            colInfo0.ColumnIndexStart = ;

            colInfo0.ColumnIndexEnd = ;

            colInfo0.Width =  * ;

            ws.AddColumnInfo(colInfo0);

            //社区编号

            ColumnInfo colInfo1 = new ColumnInfo(xls, ws);

            colInfo1.ColumnIndexStart = ;

            colInfo1.ColumnIndexEnd = ;

            colInfo1.Width =  * ;

            ws.AddColumnInfo(colInfo1);

            //名称

            ColumnInfo colInfo12 = new ColumnInfo(xls, ws);

            colInfo12.ColumnIndexStart = ;

            colInfo12.ColumnIndexEnd = ;

            colInfo12.Width =  * ;

            ws.AddColumnInfo(colInfo12);

            //社区地址

            ColumnInfo colInfo2 = new ColumnInfo(xls, ws);

            colInfo2.ColumnIndexStart = ;

            colInfo2.ColumnIndexEnd = ;

            colInfo2.Width =  * ;

            ws.AddColumnInfo(colInfo2);

            // 社区介绍

            ColumnInfo colInfo3 = new ColumnInfo(xls, ws);

            colInfo3.ColumnIndexStart = ;

            colInfo3.ColumnIndexEnd = ;

            colInfo3.Width =  * ;

            ws.AddColumnInfo(colInfo3);

            //登记人

            ColumnInfo colInfo6 = new ColumnInfo(xls, ws);

            colInfo6.ColumnIndexStart = ;

            colInfo6.ColumnIndexEnd = ;

            colInfo6.Width =  * ;

            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 =  * ;

            //自动换行

            xfhead.TextWrapRight = true;

            #endregion

            #region Excel导出的头

            //Worksheet,单元格样式，列名，开始行，开始列，结束行，结束列

             MergeRegion(ref ws, xfhead, "自定义导出", , , , dt.Columns.Count);

            #endregion

             #region 创建单元格样式

             //第一种单元格样式

             XF xf = xls.NewXF();

             xf.HorizontalAlignment = HorizontalAlignments.Centered;

             xf.VerticalAlignment = VerticalAlignments.Centered;

             xf.Pattern = ;////设定单元格填充风格。如果设定为0，则是纯色填充

             xf.PatternColor = Colors.White;//设定填充线条的颜色

             xf.UseBorder = true;

             //设置列的上下左右的样式

             xf.TopLineStyle = ;

             xf.TopLineColor = Colors.Black;

             xf.BottomLineStyle = ;

             xf.BottomLineColor = Colors.Black;

             xf.LeftLineStyle = ;

             xf.LeftLineColor = Colors.Black;

             xf.RightLineStyle = ;

             xf.RightLineColor = Colors.Black;

             xf.Font.Bold = true;

             xf.Font.FontName = "仿宋";

             xf.Font.Height =  * ;

             //自动换行

             xf.TextWrapRight = true;

             #endregion

             #region 列位置排好

             int ViewStatestartrow = startrow;

             //Worksheet,单元格样式，列名，开始行，开始列，结束行，结束列

             MergeRegion(ref ws, xf, "所属城市", ViewStatestartrow, , ViewStatestartrow, );

             MergeRegion(ref ws, xf, "社区编号", ViewStatestartrow, , ViewStatestartrow, );

             MergeRegion(ref ws, xf, "社区名称", ViewStatestartrow, , ViewStatestartrow, );

             MergeRegion(ref ws, xf, "社区地址", ViewStatestartrow, , ViewStatestartrow, );

             MergeRegion(ref ws, xf, "社区介绍", ViewStatestartrow, , ViewStatestartrow, );

             MergeRegion(ref ws, xf, "登记人", ViewStatestartrow, , ViewStatestartrow, );

             #endregion

             //第二种单元格样式

             XF xf1 = xls.NewXF();

             xf1 = xf;

             xf1.Font.Bold = false;

             xf1.Font.FontName = "仿宋_GB2312";

             xf1.Font.Height =  * ;

             int ViewStateEndrow = int.Parse(ViewStatestartrow.ToString());

             int dtcolcount = dt.Columns.Count;

               //循环数据表  逐行添加数据

            //ViewStatestartrow 为开始行

             for (int i = ; i < dt.Rows.Count; i++)

             {

                 int StatRow = i + ViewStatestartrow + ;

                 for (int k = ; k < dt.Columns.Count; k++)

                 {

                     //行，列，列值，单元格

                     ws.Cells.Add(StatRow, k + , 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中的控件，改为不触发异步Postback，而是触发传统的Postback事件

            //不然会把下载框刷新调

            ((ScriptManager)Master.FindControl("ScriptManager1")).RegisterPostBackControl(btnExport);