Work encountered problems, the Internet to find some information to collate a more feasible solution.
Npoi large data volumes into multiple sheet exports
Code snippet
<summary>
The DataTable is converted into an Excel document stream and output to the client
</summary>
<param name= "Table" ></param>
<param name= "Response" ></param>
<param name= "filename" > Output filename </param>
public static void Rendertodatatabletoexcel (DataTable table, HttpContext context, string fileName)
{
using (MemoryStream ms = Exportdatatabletoexcel (table))
{
Rendertobrowser (MS, context, fileName);
}
}
<summary>
DataTable converted to Excel document stream (export data volume exceeds 65,535, sheet)
</summary>
<param name= "Table" ></param>
<returns></returns>
public static MemoryStream Exportdatatabletoexcel (DataTable sourcetable)
{
Hssfworkbook workbook = new Hssfworkbook ();
MemoryStream ms = new MemoryStream ();
int dtrowscount = SourceTable.Rows.Count;
int sheetcount = Convert.ToInt32 (math.ceiling (convert.todouble (dtrowscount)/65536));
int sheetnum = 1;
int rowIndex = 1;
int tempindex = 1; Marked
Isheet sheet = workbook. Createsheet ("Sheet1" + sheetnum);
for (int i = 0; i < Dtrowscount; i++)
{
if (i = = 0 | | tempindex = = 1)
{
IRow HeaderRow = sheet. CreateRow (0);
foreach (DataColumn column in Sourcetable.columns)
Headerrow.createcell (column. Ordinal). Setcellvalue (column. ColumnName);
}
Hssfrow DataRow = (hssfrow) sheet. CreateRow (Tempindex);
foreach (DataColumn column in Sourcetable.columns)
{
Datarow.createcell (column. Ordinal). Setcellvalue (Sourcetable.rows[i][column]. ToString ());
}
if (Tempindex = = 65535)
{
sheetnum++;
Sheet = workbook. Createsheet ("sheet" + sheetnum);//
Tempindex = 0;
}
rowindex++;
tempindex++;
Autosizecolumns (sheet);
}
Workbook. Write (MS);
Ms. Flush ();
Ms. Position = 0;
sheet = null;
HeaderRow = null;
workbook = null;
return MS;
}
<summary>
Output file to Browser
</summary>
<param name= "MS" >excel document Flow </param>
<param name= "Context" >http contexts </param>
<param name= "filename" > file name </param>
private static void Rendertobrowser (MemoryStream MS, HttpContext context, string fileName)
{
if (context. Request.Browser.Browser = = "IE")
filename = httputility.urlencode (filename);
Context. Response.AddHeader ("Content-disposition", "attachment;filename=" + fileName);
Context. Response.BinaryWrite (Ms. ToArray ());
}
Npoi export Excel Data Super 65535 automatic sub-table