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