在asp.net中导出表格Excel数据

第一步:需要引用org.in2bits.MyXls程序集到使用页面

第二步:前台代码

<asp:Button ID="LeadingOut" runat="server" Text="导出" onclick="LeadingOut_Click" />

第三步:在aspx文件的后台写按钮的点击事件

PRotected void LeadingOut_Click(object sender, EventArgs e) {

DataSet ds = consumableBll.GetList(" IsDel='false'");//要导出的表数据

if (null == ds.Tables[0]) return;

//生成Excel ExcelFile excel = new ExcelFile();//ExcelFile 是公共类要解析 //设置列属性 excel.SetColumnInfo(true, 90 * 60, 0, 15); excel.SetColumnInfo(true, 90 * 60, 4, 4); excel.SetColumnInfo(true, 90 * 60, 8, 8);

//设置单元格格式 XF cellXF = excel.SetXF(true, false, HorizontalAlignments.Centered, VerticalAlignments.Centered); XF cellXF1 = excel.SetXF(false, true, HorizontalAlignments.Centered, VerticalAlignments.Centered); cellXF1.Pattern = 1; cellXF1.PatternBackgroundColor = Colors.Red;

//红色Default0A；绿Default0B,浅绿Default0F,灰色Default16，紫色Default18,墨绿：Default26，淡蓝色：Default28，浅蓝Default29 cellXF1.PatternColor = Colors.Default28; //设置表头信息 List<string> headInfo = new List<string>();

headInfo.Add("名称");//要导出的字段 headInfo.Add("数量"); headInfo.Add("有效期");

excel.SetHeader(1, 1, cellXF1, headInfo); int icount = 1; int hcount = 1; foreach (DataRow row in ds.Tables[0].Rows) { excel.SetDataValue(++icount, ref hcount, cellXF,

row["Name"].ToString(),//要导出的数据字段对应 row["Number"].ToString(), row["AddTime"].ToString()

); hcount = 1; } string fileName = DateTime.Now.ToString("yyyyMMddhhmmss"); Response.Clear(); Response.ClearHeaders(); Response.Buffer = true; Response.Charset = "UTF-8"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); Response.ContentType = "application/octet-stream"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); Response.BinaryWrite(excel.Download()); HttpContext.Current.Response.End(); }

第四步：生成公共类ExcelFile

using System.Collections.Generic;using System.Reflection;using org.in2bits.MyXls;

namespace WebUI.Common{ /// <summary> /// Excel文件帮助类 /// </summary> public class ExcelFile { protected XlsDocument _document; protected Worksheet _sheet; public ExcelFile() { _document = new XlsDocument(); _sheet = _document.Workbook.Worksheets.Add("Sheet1"); }

/// <summary> /// 设置列属性 /// </summary> /// <param name="collapsed">设置列的属性</param> /// <param name="width">宽度</param> /// <param name="columnIndexStart">开始列</param> /// <param name="columnIndexEnd">结束列</param> public void SetColumnInfo(bool collapsed, ushort width, ushort columnIndexStart, ushort columnIndexEnd) { ColumnInfo cInfo = new ColumnInfo(_document, _sheet); cInfo.Collapsed = collapsed; cInfo.Width = width; cInfo.ColumnIndexStart = columnIndexStart; cInfo.ColumnIndexEnd = columnIndexEnd; _sheet.AddColumnInfo(cInfo); }

/// <summary> /// 设置单元格属性(可扩展可重构) /// </summary> /// <param name="bold">是否加粗</param> /// <param name="horizontalAlignments">水平对齐方式</param> /// <param name="verticalAlignments">垂直对齐方式</param> public XF SetXF(bool textWrapRight = false, bool bold = false, HorizontalAlignments horizontalAlignments = HorizontalAlignments.Default, VerticalAlignments verticalAlignments = VerticalAlignments.Default) { //设置文档列属性 XF cellXF = _document.NewXF();//自动换行 cellXF.TextWrapRight = textWrapRight; if (bold) cellXF.Font.Bold = bold; cellXF.HorizontalAlignment = horizontalAlignments; cellXF.VerticalAlignment = verticalAlignments; return cellXF; }

/// <summary> /// 设置单元格值 /// </summary> /// <param name="i">行</param> /// <param name="j">列</param> /// <param name="value">值</param> /// <param name="bold">是否粗体</param> private void SetCells(int i,int j,string value,XF cellXF) { if(_document.Workbook.Worksheets.Count == 0) { _sheet= _document.Workbook.Worksheets.Add("Sheet1"); } Cells cells = _document.Workbook.Worksheets[0].Cells; cells.Add(i, j, value, cellXF); }

/// <summary> /// 设置单元格值 /// </summary> /// <param name="i">行</param> /// <param name="j">列</param> /// <param name="value">值</param> /// <param name="bold">是否粗体</param> private void SetCells(int i, int j, string value) { if (_document.Workbook.Worksheets.Count == 0) { _sheet = _document.Workbook.Worksheets.Add("Sheet1"); } Cells cells = _document.Workbook.Worksheets[0].Cells;

cells.Add(i, j, value); }

/// <summary> /// 设置表头 /// </summary> /// <param name="row">开始行</param> /// <param name="column">开始列</param> /// <param name="headers">表头内容</param> public void SetHeader(int startRow,int startColumn,XF cellXF,params string[] headers) { if(headers!= null) { for (int i = 0; i < headers.Length; i++) { SetCells(startColumn, startColumn + i, headers[i], cellXF); } } }

/// <summary> /// 设置表头 /// </summary> /// <param name="row">开始行</param> /// <param name="column">开始列</param> /// <param name="headers">表头内容</param> public void SetHeader(int startRow,int startColumn,XF cellXF,List<string> headers) { if(headers!= null) { for (int i = 0; i < headers.Count; i++) { SetCells(startRow, startColumn + i, headers[i], cellXF); } } }

/// <summary> /// 设置表头 /// </summary> /// <param name="row">开始行</param> /// <param name="column">开始列</param> /// <param name="headers">表头内容</param> public void SetHeader(int startRow, int startColumn, List<string> headers) { if (headers != null) { for (int i = 0; i < headers.Count; i++) { SetCells(startRow, startColumn + i, headers[i]); } } }

/// <summary> /// 设置数据 /// </summary> /// <typeparam name="T">数据类型</typeparam> /// <param name="row">开始行</param> /// <param name="column">开始列</param> /// <param name="model">数据对象</param> /// <param name="properties">数据对象填充属性</param> public void SetDataProperties<T>(int row, int column, XF cellXF, T model, params string[] properties) where T : class { if(model==null || properties == null) return;

for (int i = 0; i < properties.Length;