Asp. NET export of Excel (with Npoi and epplus libraries, no need to install Office)

There are many ways to work with Excel on the Web, most of which are calling Microsoft's Office components, which provide three ways to export Excel from ASP without installing Office.

1 Simple methods

The following code outputs Excel with three columns (name, age, gender)
Columns are separated by \ t
StringWriter SW = new StringWriter ();
Sw. WriteLine ("name \ t age \ t sex"); column headings for Excel tables
Sw. WriteLine ("Zhang San \t29\t male"); Row data
Sw. WriteLine ("John Doe \t35\t Male");
Sw. WriteLine ("Harry \t20\t female");

/* If the data returned from the database
DataTable DT; Suppose DT already has data, data format name, age, sex
foreach (DataRow row in dt. Rows)
{
Sw. WriteLine (String. Format ("{0}\t{1}\t{2}", row["name"], row["age"], row["Sex"]));
}
*/

Excel file name for ASP.
If the file name is in Chinese, you need to encode the conversion, otherwise the browser will see the download file is garbled.
String fileName = Httputility.urlencode ("Excel.xls");;

Response.Buffer = true;
Response.Clear ();

The data type returned by ASP. Download is set here.
The browser will treat the returned data as a file download.
Response.ContentType = "Application/download";

Sets the encoding of the returned data, if the item is not set, the Chinese of Excel is garbled.
response.contentencoding = System.Text.Encoding.GetEncoding ("GB2312");
Response.AddHeader ("Content-disposition", "attachment;filename=" + filename + ";");

Response.Write (SW); Writes data to the output stream.
Response.Flush ();
Response.close ();

2 Npoi Export Excel 2003

Npoi is an open source library that supports a variety of Excel operations, but only works with the 97~2003 version of Excel, which is not compatible with Excel 2007.

Npoi is http://npoi.codeplex.com/, the current version is 1.2.5.

Introducing the Npoi Library
Using Npoi. HSSF. Usermodel;
Using Npoi. HPSF;
Using Npoi. Ss. Usermodel;

//////////////////////////////////////////////////////////////////////////////////////
Hssfworkbook Hssfworkbook = new Hssfworkbook ();

Summary information for Excel files
Documentsummaryinformation dsi = Propertysetfactory.createdocumentsummaryinformation ();
Dsi.company = "Blog.csdn.net";
Hssfworkbook. documentsummaryinformation = DSi;

SummaryInformation si = propertysetfactory.createsummaryinformation ();
Si. Subject = "Export Excel";
Hssfworkbook. SummaryInformation = si;

The following code outputs Excel with three columns (name, age, gender)
Isheet Sheet1 = Hssfworkbook. Createsheet ("Sheet1");

IRow row0 = Sheet1. CreateRow (0);
Row0. Createcell (0). Setcellvalue ("name");
Row0. Createcell (1). Setcellvalue ("Age");
Row0. Createcell (2). Setcellvalue ("gender");

IRow Row1 = Sheet1. CreateRow (1);
Row1. Createcell (0). Setcellvalue ("Zhang San");
Row1. Createcell (1). Setcellvalue (29);
Row1. Createcell (2). Setcellvalue ("male");

IRow Row2 = Sheet1. CreateRow (2);
Row2. Createcell (0). Setcellvalue ("John Doe");
Row2. Createcell (1). Setcellvalue (35);
Row2. Createcell (2). Setcellvalue ("male");

IRow row3 = Sheet1. CreateRow (3);
Row3. Createcell (0). Setcellvalue ("Harry");
Row3. Createcell (1). Setcellvalue (20);
Row3. Createcell (2). Setcellvalue ("female");

/* If the data obtained from the database
DataTable dt = null; Suppose DT already has data, data format name, age, sex

for (int i = 0; i < dt. Rows.Count; i++)
{
Rows in a DataTable correspond to rows in Excel
DataRow row = dt. Rows[i];
IRow Excelrow = Sheet1. CreateRow (i);
Excelrow.createcell (0). Setcellvalue (row["name"]. ToString ());
Excelrow.createcell (1). Setcellvalue (row["age"). ToString ());
Excelrow.createcell (2). Setcellvalue (row["sex"). ToString ());

Column in the DataTable corresponds to cell in Excel
You can also loop through the columns in a DataTable to get the field values
for (int j = 0; j < dt. Columns.count; J + +)
{
String value = dt. ROWS[I][J]. ToString ();
Excelrow.createcell (j). Setcellvalue (value);
}
}
*/

MemoryStream ms = new MemoryStream ();
Hssfworkbook. Write (MS);

Excel file name for ASP.
If the file name is in Chinese, you need to encode the conversion, otherwise the browser will see the download file is garbled.
String fileName = Httputility.urlencode ("Excel.xls");

Response.ContentType = "application/vnd.ms-excel";
Response.ContentType = "Application/download"; can also be set into download
Response.AddHeader ("Content-disposition", String. Format ("attachment;filename={0}", filename);

Response.Buffer = true;
Response.Clear ();
Response.BinaryWrite (Ms. GetBuffer ());
Response.End ();

3 Epplus Library Build Excel 2007

Epplus supports Excel 2007,http://epplus.codeplex.com/.

Introducing the Epplus namespace
Using Officeopenxml;

Excelpackage Excel = new Excelpackage ();
Excelworksheet sheet = Excel. WORKBOOK.WORKSHEETS.ADD ("Sheet1");
Sheet. cells["A1"]. Value = "name";
Sheet. cells["B1"]. Value = "Age";
Sheet. cells["C1"]. Value = "gender";

Sheet. cells["A2"]. Value = "Zhang San";
Sheet. cells["B2"]. Value = 29;
Sheet. cells["C2"]. Value = "male";

Sheet. cells["A3"]. Value = "John Doe";
Sheet. cells["B3"]. Value = 35;
Sheet. cells["C3"]. Value = "male";

Sheet. cells["A4"]. Value = "Harry";
Sheet. cells["B4"]. Value = 20;
Sheet. cells["C4"]. Value = "female";

/*
You can also use a 2-D array
Index of array starting from 1
Sheet. Cells[1, 1]. Value = "name";
Sheet. Cells[1, 2]. Value = "Age";
Sheet. Cells[1, 3]. Value = "gender";

Sheet. Cells[2, 1]. Value = "Zhang San";
Sheet. Cells[2, 2]. Value = 29;
Sheet. Cells[2, 3]. Value = "male";

Sheet. Cells[3, 1]. Value = "John Doe";
Sheet. Cells[3, 2]. Value = 35;
Sheet. Cells[3, 3]. Value = "male";

Sheet. Cells[4, 1]. Value = "Harry";
Sheet. Cells[4, 2]. Value = 20;
Sheet. Cells[4, 3]. Value = "female";
*/

MemoryStream ms = new MemoryStream ();
Excel. SaveAs (MS);

Excel file name for ASP.
If the file name is in Chinese, you need to encode the conversion, otherwise the browser will see the download file is garbled.
String fileName = Httputility.urlencode ("excel.xlsx");

Response.ContentType = "application/vnd.ms-excel";
Response.ContentType = "Application/download"; can also be set into download
Response.AddHeader ("Content-disposition", String. Format ("attachment;filename={0}", filename);

Response.Buffer = true;
Response.Clear ();
Response.BinaryWrite (Ms. GetBuffer ());
Response.End ();

Asp. NET export of Excel (with Npoi and epplus libraries, no need to install Office)