在asp.net中匯出表格Excel資料

標籤：http   color   使用   os   檔案   資料   

第一步:需要引用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;
}
}
}