首页 > 代码库 > Asp.Net使用org.in2bits.MyXls.dll操作excel的应用

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

首先下载org.in2bits.MyXls.dll(自己的在~\About ASP.Net\Asp.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);

 

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