Summary of ASP. NET exporting data to excel [medium]

The dataset is directly output into excel, which solves the problem that the grid control only displays part of the data on pages.

Introduction

I did this when I wanted to do a quick export of an entireDataset(Multiple tables) to excel. I didn't add any additional mizmization to the fields, but I did want to make sure that dates, Boolean, numbers, and text were all formatted correctly.

This Code does that.

At some point, I 'd like to makeGridviewType component that wocould allow me to detail more about each item. for example, my latest project required me to make a column formatted with a given barcode font ("free 3 of 9") that required that I put an * Before and After the item number. the Solution Below doesn't make this easy to do, though... so yeah, not perfect. if anyone else has done something like this, let me know

For importing EXCEL to XML, see this post.

Note: This method does not require EXCEL to be installed on the server.

Background

I prefer to see each table in the dataset to be named.

DS. Tables [ 0 ]. Tablename = " Colors " ;
DS. Tables [ 1 ]. Tablename = " Shapes " ;

I changed it to allow you to pass inList <Table>In case you don't put them inDataset. No big deal either way.

Why did I useXmltextwriterWhen I seem to be only usingWriteraw? I wanted to be able to have it fix any special characters with"X. writestring (row [I]. tostring ());". Note, this still may have problems with certain characters, since I haven't tested it much.

Using the code Using System;
Using System. collections;
Using System. Collections. Generic;
Using System. Data;
Using System. text;
Using System. xml;

Public Void Convert (Dataset ds, String Filename ){
Convert (Ds. Tables, filename );
}
Public Void Convert (ienumerable tables, String Filename ){
Response. clearcontent ();
Response. clearheaders ();
Response. Buffer =True ;
Response. charset = "" ;
Response. contenttype = " Application/vnd. MS-Excel " ;
Response. addheader ( " Content-Disposition " ,
" Attachment; filename = " + Filename + " . Xls " );

Using (Xmltextwriter x = New Xmltextwriter (response. outputstream, encoding. utf8 )){
Int Sheetnumber = 0 ;
X. writeraw (" <? XML version = \ "1.0 \"?> <? MSO-application progid = \ "Excel. Sheet \"?> " );
X. writeraw ( " <Workbook xmlns = \ "urn: Schemas-Microsoft-com: Office: spreadsheet \" " );
X. writeraw ( " Xmlns: O = \ "urn: Schemas-Microsoft-com: Office \" " );
X. writeraw ( " Xmlns: x = \ "urn: Schemas-Microsoft-com: Office: Excel \"> " );
X. writeraw ( " <Styles> <style SS: Id = 'stext '> " +
" <Numberformat SS: format = '@'/> </style> " );
X. writeraw ( " <Style SS: Id = 'sdate'> <numberformat " +
" SS: format = '[$-409] M/D/yy \ H: mm \ am/PM; @'/> " );
X. writeraw ( " </Style> </styles> " );
Foreach (Datatable dt In Tables ){
Sheetnumber ++;
String Sheetname =! String . Isnullorempty (Dt. tablename )?
DT. tablename: " Sheet " + Sheetnumber. tostring ();
X. writeraw ( " <Worksheet SS: Name =' " + Sheetname + " '> " );
X. writeraw ( " <Table> " );
String [] Columntypes = New String [DT. Columns. Count];

For(IntI =0; I <DT. Columns. Count; I ++ ){
StringColtype = DT. Columns [I]. datatype. tostring (). tolower ();

If (coltype. contains ( " datetime " )) {
columntypes [I] = " datetime " ;
X. writeraw ( " ");

}< span style = "color: # 0000ff;"> else If (coltype. contains ( " string " )) {
columntypes [I] = " string " ;
X. writeraw ( " ");

}Else{
X. writeraw ("<Column/>");

If (Coltype. Contains ( " Boolean " )){
Columntypes [I] = " Boolean " ;
} Else {
// Default is some kind of number.
Columntypes [I] = " Number " ;
}

}
}
// Column headers
X. writeraw ( " <Row> " );
Foreach (Datacolumn col In DT. columns ){
X. writeraw ( " <Cell SS: styleid = 'stext '> <data SS: TYPE = 'string'> " );
X. writeraw (Col. columnname );
X. writeraw ( " </Data> </cell> " );
}
X. writeraw ( " </Row> " );
// Data
Bool Missednullcolumn = False ;
Foreach (Datarow row In DT. Rows ){
X. writeraw ( " <Row> " );
For ( Int I = 0 ; I <DT. Columns. Count; I ++ ){
If (! Row. isnull (I )){
If (Missednullcolumn ){
Int Displayindex = I + 1 ;
X. writeraw ( " <Cell SS: Index =' " + Displayindex. tostring () +
" '> <Data SS: TYPE =' " +
Columntypes [I] + " '> " );
Missednullcolumn = False ;
} Else {
X. writeraw ( " <Cell> <data SS: TYPE =' " +
Columntypes [I] + " '> " );
}

Switch (Columntypes [I]) {
Case " Datetime " :
X. writeraw (datetime) Row [I]). tostring ( " S " ));
Break ;
Case " Boolean " :
X. writeraw (((Bool ) Row [I])? " 1 " : " 0 " );
Break ;
Case " String " :
X. writestring (row [I]. tostring ());
Break ;
Default :
X. writestring (row [I]. tostring ());
Break ;
}

X. writeraw (" </Data> </cell> " );
} Else {
Missednullcolumn = True ;
}
}
X. writeraw ( " </Row> " );
}
X. writeraw ( " </Table> </worksheet> " );
}
X. writeraw ( " </Workbook> " );
}
Response. End ();
}

Source: http://www.codeproject.com/KB/office/OutputDatasetToExcel.aspx

I willCodeCombined with the code in the previous summary [I] about exporting data to excel using ASP. NET, we made a complete demo, which is quite good to use.

Download demo