Export tabular Excel data in ASP.

First step: Need to reference ORG.IN2BITS.MYXLS assembly to use page

Step Two: Foreground code

<asp:button id= "leadingout" runat= "server" text= "Export" onclick= "Leadingout_click"/>

Step three: Click events in the Background write button of the ASPX file

protected void Leadingout_click (object sender, EventArgs e)
{

DataSet ds = Consumablebll.getlist ("isdel= ' false ');//table data to be exported

if (null = = ds. Tables[0])
Return

Build Excel
Excelfile Excel = new Excelfile ();//excelfile is the public class to parse
Set Column Properties
Excel. SetColumnInfo (True, 90 * 60, 0, 15);
Excel. SetColumnInfo (True, 90 * 60, 4, 4);
Excel. SetColumnInfo (True, 90 * 60, 8, 8);

Formatting cells
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;

Red default0a, Green default0b, light green default0f, gray Default16, purple Default18, dark black: Default26, pale blue: Default28, Blue Default29
Cellxf1.patterncolor = Colors.default28;
Set Header information
list<string> headinfo = new list<string> ();

Headinfo.add ("name");//field to be exported
Headinfo.add ("Quantity");
Headinfo.add ("validity period");

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 (),//The data field to export corresponds to
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 ();
}

Fourth step: Generate public class Excelfile

Using System.Collections.Generic;
Using System.Reflection;
Using Org.in2bits.MyXls;

Namespace Webui.common
{
//<summary>
///Excel File Help class
//</summary>
public class Excelfile
{
protected xlsdocument _document;
protected Worksheet _sheet;
Public excelfile ()
{
_do Cument = new Xlsdocument ();
_sheet = _document. WORKBOOK.WORKSHEETS.ADD ("Sheet1");
}

//<summary>
//Set column Properties
///</summary>
//<param name= collapsed > Setting Column Properties </ Param>
//<param name= "width" > Width </param>
//<param name= "Columnindexstart" > Start column </ Param>
//<param name= "Columnindexend" > End column </param>
public void SetColumnInfo (BOOL collapsed, ushort width, ushort columnindexstart, ushort columnindexend)
{
Columninfo cinfo = new Columninfo (_document, _shee T);
cinfo.collapsed = collapsed;
Cinfo.width = Width;
Cinfo.columnindexstart = Columnindexstart;
Cinfo.columnindexend = columnindexend;
_sheet. Addcolumninfo (Cinfo);
}

//<summary>
//Set cell properties (extensible reconfigurable)
//</summary>
//<param name= "bold" > Bold </ Param>
//<param name= "horizontalalignments" > Horizontal alignment </param>
//<param name= " Verticalalignments "> Vertical alignment </param>
Public XF setxf (bool Textwrapright = FALSE, bool Bold = false, Horizontal Alignments horizontalalignments = Horizontalalignments.default, verticalalignments VerticalAlignments = Verticalalignments.default)
{
//Set document column Properties
XF cellxf = _document. NEWXF ();//wrap
Cellxf.textwrapright = textwrapright;

if (bold) CellXF.Font.Bold = bold;
Cellxf.horizontalalignment = horizontalalignments;
Cellxf.verticalalignment = verticalalignments;
return CELLXF;
}

//<summary>
//Set cell value
//</summary>
//<param name= "I" > Line </param>
//< ;p Aram Name= "J" > Column </param>
//<param name= "value" > Value </param>
//<param name= "bold" > is 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>
Set cell values
</summary>
<param name= "I" > Lines </param>
<param name= "J" > Columns </param>
<param name= "Value" > Value </param>
<param name= "Bold" > 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>
Set up the table header
</summary>
<param name= "Row" > Start line </param>
<param name= "column" > Start column </param>
<param name= "Headers" > Header content </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>
Set up the table header
</summary>
<param name= "Row" > Start line </param>
<param name= "column" > Start column </param>
<param name= "Headers" > Header content </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>
//Set Header
//</summary>
//<param name= "Row" > Start line </param>
// Lt;param name= "column" > Start column </param>
//<param name= "headers" > Header content </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>
Setting up data
</summary>
<typeparam name= "T" > Data type </typeparam>
<param name= "Row" > Start line </param>
<param name= "column" > Start column </param>
<param name= "Model" > Data Objects </param>
<param name= "Properties" > Data Object Fill Properties </param>
public void setdataproperties<t> (int row, int column, XF cellxf, T model, params string[] properties) where T:clas S
{
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>
Set cell contents
</summary>
<param name= "Row" > Start line </param>
<param name= "column" > Start column </param>
<param name= "CELLXF" > Cell Properties </param>
<param name= "Values" > 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>
Set cell contents
</summary>
<param name= "Row" > Start line </param>
<param name= "column" > Start column </param>
<param name= "CELLXF" > Cell Properties </param>
<param name= "Values" > 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>
Setting up data
</summary>
<typeparam name= "T" > Data type </typeparam>
<param name= "Row" > Start line </param>
<param name= "column" > Start column </param>
<param name= "Models" > Data Objects List </param>
<param name= "Properties" > Data Object Fill Properties </param>
public void setdata<t> (int row, int column,xf cellxf, list<t> models, params string[] properties) where T:CL The
{
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>
Returns the Excel file Byte character
</summary>
<returns></returns>
Public byte[] Download ()
{
Return _document. Bytes.bytearray;
}
}
}