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