NPOI exports a large amount of EXCEL Data, multiple sheet display data, npoisheet

NPOI exports a large amount of EXCEL Data, multiple sheet display data, npoisheet

// NPOIHelper class key code using System; using System. collections. generic; using System. linq; using System. text; using System. data; using System. IO; using NPOI. HSSF. userModel; using System. collections; using System. web; namespace Yikeba_htmlConverter {public class NPOIHelper {// <summary> // a collection of column names /// </summary> public static System. collections. sortedList ListColumnsName; /// <summary> /// export an Excel file using the file path /// </summ Ary> // <param name = "dtSource"> DataTable data source </param> /// <param name = "filePath"> file path </param> public static void ExportExcel (DataTable dtSource, string filePath) {if (ListColumnsName = null | ListColumnsName. count = 0) throw (new Exception ("set the name of the column to be exported for ListColumnsName! "); HSSFWorkbook excelWorkbook = CreateExcelFile (); InsertRow (dtSource, excelWorkbook); SaveExcelFile (excelWorkbook, filePath );} /// <summary> /// export an Excel stream // </summary> /// <param name = "dtSource"> DataTable data source </param> /// <param name = "excelStream"> stream operation </param> public static void ExportExcel (DataTable dtSource, stream excelStream) {if (ListColumnsName = null | ListColumnsName. count = 0) thro W (new Exception ("Please set the list to be exported for ListColumnsName! "); HSSFWorkbook excelWorkbook = CreateExcelFile (); // create a work area InsertRow (dtSource, excelWorkbook); // The data import method SaveExcelFile (excelWorkbook, excelStream ); // save excel} /// <summary> // Save the Excel file by path /// </summary> /// <param name = "excelWorkBook"> </param >/// <param name = "filePath"> </param> protected static void SaveExcelFile (HSSFWorkbook excelWorkBook, string filePath) {FileStream file = null; try {f Ile = new FileStream (filePath, FileMode. Create); excelWorkBook. Write (file);} finally {if (file! = Null) {file. close ();}}} /// <summary> /// Save the Excel file /// </summary> /// <param name = "excelWorkBook"> </param> /// <param name = "filePath"> </param> protected static void SaveExcelFile (HSSFWorkbook excelWorkBook, stream excelStream) {try {excelWorkBook. write (excelStream );} finally {}/// <summary> // create an Excel file /// </summary> /// <param name = "filePath"> </param> protected static HSSFWorkbook CreateExcelFile () {HSSFWorkbook hssfworkbook = new HSSFWorkbook (); return hssfworkbook ;} /// <summary> /// create an excel header /// </summary> /// <param name = "dgv"> </param> /// <param name = "excelSheet"> </param> protected static void CreateHeader (HSSFSheet excelSheet) {int cellIndex = 0; // The foreach (System. collections. dictionaryEntry de in ListColumnsName) {HSSFRow newRow = excelSheet. createRow (0 ); HSSFCell newCell = newRow. createCell (cellIndex); newCell. setCellValue (de. value. toString (); cellIndex ++ ;}/// <summary >/// insert data rows /// </summary> protected static void InsertRow (DataTable dtSource, HSSFWorkbook excelWorkbook) {int rowCount = 0; int sheetCount = 1; HSSFSheet newsheet = null; // export the dataset newsheet = excelWorkbook from the cyclic data source. createSheet ("Sheet" + sheetCount); // create the first sheet CreateHeader (newshe Et); // load the sheet header information foreach (DataRow dr in dtSource. rows) // loop the data Rows in the DataTable {rowCount ++; // create a new workbook if (rowCount = 10000) with more than 10000 data entries) // create a new sheet {rowCount = 1; sheetCount ++; newsheet = excelWorkbook if there are more than 10000 rows of data. createSheet ("Sheet" + sheetCount); // create a sheet CreateHeader (newsheet); // create a sheet header} HSSFRow newRow = newsheet. createRow (rowCount); // create the current row InsertCell (dtSource, dr, newRow, newsheet, e XcelWorkbook ); // Add data to each column in the current row in a loop} // <summary> // export data rows /// </summary> /// <param name = "dtSource"> </param> // <param name = "drSource"> </param> // <param name = "currentExcelRow"> </param> // /<param name = "excelSheet"> </param> // <param name = "excelWorkBook"> </param> protected static void InsertCell (DataTable dtSource, dataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkboo K excelWorkBook) {for (int cellIndex = 0; cellIndex <ListColumnsName. count; cellIndex ++) {// column name string columnsName = ListColumnsName. getKey (cellIndex ). toString (); HSSFCell newCell = null; System. type rowType = drSource [cellIndex]. getType (); string drValue = drSource [cellIndex]. toString (). trim (); switch (rowType. toString () {case "System. string ": // String type drValue = drValue. replace ("&", "&"); drVa Lue = drValue. replace (">", ">"); drValue = drValue. replace ("<", "<"); newCell = currentExcelRow. createCell (cellIndex); newCell. setCellValue (drValue); break; case "System. dateTime ": // DateTime dateV; DateTime. tryParse (drValue, out dateV); newCell = currentExcelRow. createCell (cellIndex); newCell. setCellValue (dateV); // format HSSFCellStyle cellStyle = excelWorkBook. createCellStyle (); HSSFDataForma T format = excelWorkBook. createDataFormat (); cellStyle. dataFormat = format. getFormat ("yyyy-mm-dd hh: mm: ss"); newCell. cellStyle = cellStyle; break; case "System. boolean ": // Boolean bool boolV = false; bool. tryParse (drValue, out boolV); newCell = currentExcelRow. createCell (cellIndex); newCell. setCellValue (boolV); break; case "System. int16 ": // integer case" System. int32 ": case" System. int64 ": case" System. byte": Int intV = 0; int. tryParse (drValue, out intV); newCell = currentExcelRow. createCell (cellIndex); newCell. setCellValue (intV. toString (); break; case "System. decimal ": // floating case" System. double ": double doubV = 0; double. tryParse (drValue, out doubV); newCell = currentExcelRow. createCell (cellIndex); newCell. setCellValue (doubV); break; case "System. DBNull ": // null value processing newCell = currentExcelRow. createCell (c EllIndex); newCell. SetCellValue (""); break; default: throw (new Exception (rowType. ToString () + ": type data cannot be processed! ") ;}}}// The sorting implementation interface does not sort. Export public class NoSort: System. collections. IComparer {public int Compare (object x, object y) {return-1 ;}}// create a WebForm1 page to test using System; using System. collections; using System. configuration; using System. data; using System. linq; using System. web; using System. web. security; using System. web. UI; using System. web. UI. htmlControls; using System. web. UI. webControls; using Sy Stem. web. UI. webControls. webParts; using System. xml. linq; using Yikeba_htmlConverter; using System. xml; using System. IO; namespace WebApplication1 {public partial class WebForm1: System. web. UI. page {protected void Page_Load (object sender, EventArgs e) {// export the data column to export the column NPOIHelper according to the adding sequence. listColumnsName = new SortedList (new NoSort (); NPOIHelper. listColumnsName. add ("MemberName", "name"); NPOIHelper. listColu MnsName. add ("username", "account"); NPOIHelper. listColumnsName. add ("starttime", "Logon Time"); NPOIHelper. listColumnsName. add ("lasttime", "online expiration time"); NPOIHelper. listColumnsName. add ("state", "state"); Response. clear (); Response. bufferOutput = false; Response. contentEncoding = System. text. encoding. UTF8; string filename = HttpUtility. urlEncode (DateTime. now. toString ("online user yyyyMMdd"); Response. addHeader ("Content-Disp Osition "," attachment; filename = DownExcel "+ ". xls "); Response. contentType = "application/ms-excel "; string str = "<table> <tr> <td> CSBT-120906-TG6 </td> <td> liao jianjun </td> <td> LIAO </td> <td> JIANJUN </td> <td> male </td> </tr> <td> CSBT-120906-TG7 </td> <td> WANG Hangang </td> <td> WANG </td> <td> HANGANG </td> <td> male </td> </tr> </table> "; str = "<? Xml version = \ "1.0 \" standalone = \ "yes \"?> "+ Str; // clean XmlDocument doc = new XmlDocument (); doc. loadXml (str); XmlNodeList nodes = doc. selectNodes ("table/tr"); foreach (XmlElement node in nodes) {node. attributes. removeAll (); // all attributes of Tr are removed here for (int I = 0; I <node. childNodes. count; I ++) // column loop, specify the name {XmlNode n = doc for each column. createNode (XmlNodeType. element, "col" + I. toString (), ""); n. innerXml = node. childNodes [I]. innerXml; node. replaceChild (n, node. childNodes [I]) ;}// import Dataset StringReader tr = new StringReader (doc. innerXml); DataSet ds = new DataSet (); ds. readXml (tr); DataTable dtSource = ds. tables [0]; // DataTable dtSource = new DataTable (); NPOIHelper. exportExcel (dtSource, Response. outputStream); Response. close ();}}} //////////////////////////////////////// //////////////////////////////////////// //////////////////////////////////////// /// //