Export table Excel Data in asp.net, asp. netexcel

Export table Excel Data in asp.net, asp. netexcel

Step 1: reference the org. in2bits. MyXls assembly to the usage page.

Step 2: foreground code

<Asp: Button ID = "LeadingOut" runat = "server" Text = "Export" onclick = "LeadingOut_Click"/>

Step 3: Click the event of the write button in the background 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;

// Generate an Excel file
ExcelFile excel = new ExcelFile (); // ExcelFile is a public class to be parsed
// Set column attributes
Excel. SetColumnInfo (true, 90*60, 0, 15 );
Excel. SetColumnInfo (true, 90*60, 4, 4 );
Excel. SetColumnInfo (true, 90*60, 8, 8 );

// Set the cell format
XF cellXF = excel. SetXF (true, false, HorizontalAlignments. Centered, verticalignments. Centered );
XF cellXF1 = excel. SetXF (false, true, HorizontalAlignments. Centered, verticalignments. Centered );
CellXF1.Pattern = 1;
CellXF1.PatternBackgroundColor = Colors. Red;

// Red defa00a; green Default0B, light green Default0F, gray Default16, purple Default18, Mo Green: Default26, light blue: Default28, light blue defa29 29
CellXF1.PatternColor = Colors. Default28;
// Set the header information
List <string> headInfo = new List <string> ();

HeadInfo. Add ("name"); // the 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 (), // corresponding to the data field to be exported
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 ();
}

Step 4: 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 ()
{
_ Document = new XlsDocument ();
_ Sheet = _ document. Workbook. Worksheets. Add ("Sheet1 ");
}

/// <Summary>
/// Set column attributes
/// </Summary>
/// <Param name = "collapsed"> set column attributes </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, _ sheet );
CInfo. Collapsed = collapsed;
CInfo. Width = width;
CInfo. ColumnIndexStart = columnIndexStart;
CInfo. ColumnIndexEnd = columnIndexEnd;
_ Sheet. AddColumnInfo (cInfo );
}

/// <Summary>
/// Set cell attributes (scalable and refactored)
/// </Summary>
/// <Param name = "bold"> bold </param>
/// <Param name = "horizontalAlignments"> horizontal alignment </param>
/// <Param name = "verticalignments"> vertical alignment </param>
Public XF SetXF (bool textWrapRight = false, bool bold = false, HorizontalAlignments horizontalAlignments = gradient. Default, verticalignments = VerticalAlignments. Default)
{
// Set document column attributes
XF cellXF = _ document. NewXF (); // wrap automatically
CellXF. TextWrapRight = textWrapRight;

If (bold) cellXF. Font. Bold = bold;
CellXF. HorizontalAlignment = horizontalAlignments;
CellXF. VerticalAlignment = verticalAlignments;
Return cellXF;
}

/// <Summary>
/// Set the cell value
/// </Summary>
/// <Param name = "I"> row </param>
/// <Param name = "j"> column </param>
/// <Param name = "value"> value </param>
/// <Param name = "bold"> 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 the cell value
/// </Summary>
/// <Param name = "I"> row </param>
/// <Param name = "j"> column </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 the header
/// </Summary>
/// <Param name = "row"> Start row </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 {
SetCells (startColumn, startColumn + I, headers [I], cellXF );
}
}
}

/// <Summary>
/// Set the header
/// </Summary>
/// <Param name = "row"> Start row </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 {
SetCells (startRow, startColumn + I, headers [I], cellXF );
}
}
}

/// <Summary>
/// Set the header
/// </Summary>
/// <Param name = "row"> Start row </param>
/// <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 {
SetCells (startRow, startColumn + I, headers [I]);
}
}
}

/// <Summary>
/// Set Data
/// </Summary>
/// <Typeparam name = "T"> data type </typeparam>
/// <Param name = "row"> Start row </param>
/// <Param name = "column"> Start column </param>
/// <Param name = "model"> Data Object </param>
/// <Param name = "properties"> Data Object filling attribute </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>
/// Set the cell content
/// </Summary>
/// <Param name = "row"> Start row </param>
/// <Param name = "column"> Start column </param>
/// <Param name = "cellXF"> cell attributes </param>
/// <Param name = "values"> value </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 the cell content
/// </Summary>
/// <Param name = "row"> Start row </param>
/// <Param name = "column"> Start column </param>
/// <Param name = "cellXF"> cell attributes </param>
/// <Param name = "values"> value </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>
/// Set Data
/// </Summary>
/// <Typeparam name = "T"> data type </typeparam>
/// <Param name = "row"> Start row </param>
/// <Param name = "column"> Start column </param>
/// <Param name = "models"> Data Object List </param>
/// <Param name = "properties"> Data Object filling attribute </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>
/// Returns the Excel file byte
/// </Summary>
/// <Returns> </returns>
Public byte [] Download ()
{
Return _ document. Bytes. ByteArray;
}
}
}

How does aspnet (C #) export the queried data to an Excel table?

Hello, you can use the same principle to export data to excel Based on the DataGrid.
Public void ExportToExcel (Page myPage, DataGrid ctl, string filename)
{
HttpResponse Response;
Response = myPage. Response;

Bool CurrCtlVisible = ctl. Visible;
Ctl. Visible = true;
Response. AppendHeader ("Content-Disposition", "attachment; filename =" + filename + ". xls ");
Response. ContentEncoding = System. Text. Encoding. GetEncoding ("UTF-8 ");
Response. ContentType = "application/ms-excel ";
Ctl. Page. EnableViewState = false;
System. IO. StringWriter tw = new System. IO. StringWriter ();
System. Web. UI. HtmlTextWriter hw = new HtmlTextWriter (tw );
Ctl. RenderControl (hw );
Response. Write (tw. ToString ());
Response. End ();

Ctl. Page. EnableViewState = true;
Ctl. Visible = CurrCtlVisible;
}

Export an Excel table in ASPnet

Public void CreateExcel (DataSet ds, string typeid, string FileName)
{
HttpResponse resp;
Resp = Page. Response;
Resp. ContentEncoding = System. Text. Encoding. GetEncoding ("GB2312 ");
Resp. AppendHeader ("Content-Disposition", "attachment; filename =" + FileName );
String colHeaders = "", ls_item = "";
Int I = 0;

// Define the table object and row object, and use DataSet to initialize its value
DataTable dt = ds. Tables [0];
DataRow [] myRow = dt. Select ("");
// When typeid = "1", the exported file is in EXCEL format; When typeid = "2", the exported file is in XML format.
If (typeid = "1 ")
{
// Obtain the titles of each column in the data table, separated by \ t, and a carriage return is added after the title of the last column.
For (I = 0; I <dt. Columns. Count-1; I ++)
ColHeaders + = dt. Columns [I]. Caption. ToString () + "\ t ";
ColHeaders + = dt. Columns [I]. Caption. ToString () + "\ n ";
// Write the obtained data to the HTTP output stream
Resp. Write (colHeaders );
// Process data row by row
Foreach (DataRow row in myRow)
{
// In the current row, data is obtained one by one, separated by \ t, and carriage return \ n is added at the end
For (I = 0; I <row. ItemArray. Length-1; I ++)
Ls_item + = row [I]. ToString () + "\ t ";
Ls_item + = row [I]. ToString () + "\ n ";
// Write the data in the current row to the HTTP output stream, and leave ls_item empty for downstream data
Resp. Write (ls_item );
Ls_item = "";
}
}
Else
{
If (typeid = "2 ")
{
// Export XML data directly from DataSet and write it to the HTTP output stream
Resp. Write (ds. GetXml ());
}
}
// Write the data in the buffer to the HTTP header file
Resp. End ();

}... Remaining full text>