Npoi Export Excel Sub-table

<summary>
Exported by DataTable excel[beyond 65536 Auto-sub-table]
</summary>
<param name= "SourceTable" > datatable</param> to Export Data
<returns>excel Worksheets </returns>
Public 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);//There is no isheet here, copy the blame
Tempindex = 0;
}
rowindex++;
tempindex++;

}

Workbook. Write (MS);
Ms. Flush ();
Ms. Position = 0;

sheet = null;
HeaderRow = null;
workbook = null;

return MS;
}

Console Invoke Instance

static void Main (string[] args)
{

Npoiexcelhelper h = new Npoiexcelhelper ();
H.exportdatatabletoexcel (S.test (), "Ceshiceshi.xls", "Ceshiceshi");

}

<summary>
Exporting Excel from a DataTable
</summary>
<param name= "SourceTable" > datatable</param> to Export Data
<param name= "FileName" > Specify Excel Worksheet name </param>
<returns>excel Worksheets </returns>
public void Exportdatatabletoexcel (DataTable sourcetable, String fileName, String sheetname)
{
MemoryStream ms = Exportdatatabletoexcel (SourceTable, sheetname) as MemoryStream;
HttpContext.Current.Response.AppendHeader ("Content-disposition", "attachment;filename=" + filename);
HttpContext.Current.Response.BinaryWrite (Ms. ToArray ());
HttpContext.Current.Response.End ();
Ms. Close ();
ms = NULL;
Hssfworkbook wk = new Hssfworkbook ();
using (MemoryStream ms = Exportdatatabletoexcel (SourceTable, SheetName))
{
using (FileStream fs = new FileStream (FileName, FileMode.Create, FileAccess.Write))
{
byte[] data = Ms. ToArray ();
Fs. Write (data, 0, data. Length);
Wk. Write (FS);
Fs. Flush ();
}
}
}

= = = The following is an MVC invocation instance

Public Fileresult ExportStu4 ()
{
System.IO.MemoryStream ms = new System.IO.MemoryStream ();
Npoiexcelhelper n = new Npoiexcelhelper ();
ms = N.exportdatatabletoexcel (ds. Tables[0], "Sheet1");
Exportdatatabletoexcel
ms = N.exportdatatabletoexcel (ds. Tables[0]);
String fileName = System.DateTime.Now.ToString ();
Return File (MS, "application/vnd.ms-excel", FileName + ". xls");
}

===================== also has an export non-table, call instance ibid. =========================//

Public MemoryStream Exportdatatabletoexcel (DataTable sourcetable, String sheetname)
{
Hssfworkbook workbook = new Hssfworkbook ();
MemoryStream ms = new MemoryStream ();
Isheet sheet = workbook. Createsheet (SheetName);
IRow HeaderRow = sheet. CreateRow (0);
Handling header.
foreach (DataColumn column in Sourcetable.columns)
Headerrow.createcell (column. Ordinal). Setcellvalue (column. ColumnName);

Handling value.
int rowIndex = 1;

foreach (DataRow row in sourcetable.rows)
{
Hssfrow DataRow = (hssfrow) sheet. CreateRow (RowIndex);

foreach (DataColumn column in Sourcetable.columns)
{
Datarow.createcell (column. Ordinal). Setcellvalue (Row[column]. ToString ());
}

rowindex++;
}

Workbook. Write (MS);
Ms. Flush ();
Ms. Position = 0;

sheet = null;
HeaderRow = null;
workbook = null;

return MS;
}

PS: I heard that Npoi 2.0 version can be directly exported 07 version of Excel, it does not matter the table is not divided into tables, an Excel sheet table more than 1 million data to meet the needs of the basic, another time to Npoi 2.0 version of the code example posted out for your reference.

Npoi Export Excel Sub-table