在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; i++)
{
PropertyInfo property = typeof (T).GetProperty(properties[i], BindingFlags.Public);
if(property!=null)
{
SetCells(row, column + i, property.GetValue(model, null).ToString(),cellXF);
}
}
}
/// <summary>
/// 设置单元格内容
/// </summary>
/// <param name="row">开始行</param>
/// <param name="column">开始列</param>
/// <param name="cellXF">单元格属性</param>
/// <param name="values">值</param>
public void SetDataValue(int row, ref int column, XF cellXF, params string[] values)
{
if (values == null)
return;
for (int i = 0; i < values.Length; i++)
{
SetCells(row, column + i, values[i],cellXF);
}
column += values.Length;
}
/// <summary>
/// 设置单元格内容
/// </summary>
/// <param name="row">开始行</param>
/// <param name="column">开始列</param>
/// <param name="cellXF">单元格属性</param>
/// <param name="values">值</param>
public void SetDataValue(int row, ref int column, params string[] values)
{
if (values == null)
return;
for (int i = 0; i < values.Length; i++)
{
SetCells(row, column + i, values[i]);
}
column += values.Length;
}
/// <summary>
/// 设置数据
/// </summary>
/// <typeparam name="T">数据类型</typeparam>
/// <param name="row">开始行</param>
/// <param name="column">开始列</param>
/// <param name="models">数据对象列表</param>
/// <param name="properties">数据对象填充属性</param>
public void SetData<T>(int row, int column,XF cellXF, List<T> models, params string[] properties) where T : class
{
if (models == null || models.Count== 0 || properties == null)
return;
for (int i = 0; i < properties.Length; i++)
{
PropertyInfo property = typeof(T).GetProperty(properties[i], BindingFlags.Public);
if (property != null)
{
for (int j = 0; j < models.Count; j++)
{
SetCells(row + j, column + i, property.GetValue(models[i], null).ToString(),cellXF);
}
}
}
}
/// <summary>
/// 返回Excel文件字节符
/// </summary>
/// <returns></returns>
public byte[] Download()
{
return _document.Bytes.ByteArray;
}
}
}
