This article will not go into details about how to generate an Excel file using MyXls. This article describes how to use MyXls to generate native Excel files on the Web.
For the NPOI method to instantly generate an Excel method, see http://www.cnblogs.com/downmoon/archive/2011/04/16/2017603.html
Dll to be referenced, org. in2bits. MyXls,: http://sourceforge.net/projects/myxls/files/MyXls/
Add using org. in2bits. MyXls in the header;
If a DataTable has been obtained, the method is as follows:
1. generate local files:
/// <Summary>
/// Export Excel
/// </Summary>
Public static void ExportExcelForPercent (string sheetName, string xlsname, int areaid, DateTime dt)
{
DataTable table = GetDataTableForPercent (areaid, dt );
If (table = null | table. Rows. Count = 0) {return ;}
XlsDocument xls = new XlsDocument ();
Worksheet sheet = xls. Workbook. Worksheets. Add (sheetName );
// Fill the header
Foreach (DataColumn col in table. Columns)
{
Sheet. Cells. Add (1, col. Ordinal + 1, col. ColumnName );
}
// Fill in the content
For (int I = 0; I <table. Rows. Count; I ++)
{
For (int j = 0; j <table. Columns. Count; j ++)
{
Sheet. Cells. Add (I + 2, j + 1, table. Rows [I] [j]. ToString ());
}
}
// Save
Xls. FileName = xlsname;
Xls. Save ();
Xls = null;
}
Ii. Use MemoryStream for instant generation under web conditions:
/// <Summary>
/// Myxls export Excel
/// </Summary>
Public static void ExportExcelForPercentForWeb (string sheetName, string xlsname, int areaid, DateTime curdate)
{
XlsDocument xls = new XlsDocument ();
Worksheet sheet = xls. Workbook. Worksheets. Add (sheetName );
Try
{
DataTable table = GetDataTableForPercent (areaid, curdate );
If (table = null | table. Rows. Count = 0) {return ;}
// XlsDocument xls = new XlsDocument ();
// Worksheet sheet = xls. Workbook. Worksheets. Add (sheetName );
// Fill the header
Foreach (DataColumn col in table. Columns)
{
Sheet. Cells. Add (1, col. Ordinal + 1, col. ColumnName );
}
// Fill in the content
For (int I = 0; I <table. Rows. Count; I ++)
{
For (int j = 0; j <table. Columns. Count; j ++)
{
Sheet. Cells. Add (I + 2, j + 1, table. Rows [I] [j]. ToString ());
}
}
// Save
// Xls. FileName = xlsname;
// Xls. Save ();
# Save the region Client
Using (MemoryStream MS = new MemoryStream ())
{
Xls. Save (MS );
Ms. Flush ();
Ms. Position = 0;
Sheet = null;
Xls = null;
HttpResponse response = System. Web. HttpContext. Current. Response;
Response. Clear ();
Response. Charset = "UTF-8 ";
Response. ContentType = "application/vnd-excel"; // "application/vnd. ms-excel ";
System. Web. HttpContext. Current. Response. AddHeader ("Content-Disposition", string. Format ("attachment; filename =" + xlsname ));
// System. Web. HttpContext. Current. Response. WriteFile (fi. FullName );
Byte [] data = ms. ToArray ();
System. Web. HttpContext. Current. Response. BinaryWrite (data );
}
# Endregion
// Xls = null;
}
Catch (Exception ex)
{
}
Finally
{
Sheet = null;
Xls = null;
}
}
Iii. Call method:
Private void SaveFile (int year, int month, int Areaid)
{
Try
{
String fileName = string. Empty;
FileName = SQLParser. RandomKey (10001,999 99) + ". xls ";
String sheetname = string. Format ("sales table of {0} [{1} year {2} month]", (Areaid = 1 )? "Northern Region": "Southern Region", year, month );
DateTime dt = new DateTime (year, month, Areaid );
ExcelHelper. ExportExcelForPercentForWeb (sheetname, fileName, Areaid, dt );
// Page. Response. Write ("<script> window. close (); </script> ");
// Return;
}
Catch
{
}
}
Effect:
MyXls: http://sourceforge.net/projects/myxls/files/MyXls/