NPOI export Excel, npoi export excel

NPOI export Excel, npoi export excel

1 /// <summary> 2 /// export DataTable data 3 /// </summary> 4 /// <param name = "dt"> </param> 5 private void ExprotDataByDataTable (DataTable dt) 6 {7 try 8 {9 if (dt. rows. count <= 0) 10 throw new Exception ("no data to be exported"); 11 const int rowCount = 60000; 12 // if (dt. rows. count> 10 * rowCount) 13 // {14 // throw new Exception ("the data volume has exceeded 0.6 million. Please clear some data and export it again! "); 15 //} 16 HSSFWorkbook hssfworkbook = new HSSFWorkbook (); 17 int iNum = 0; 18 int I = 0; 19 while (true) 20 {21 if (iNum> = I * rowCount) 22 {23 iNum = ExprotDataCreateSheet ("Sheet" + I, hssfworkbook, dt, iNum, (I + 1) * rowCount ); 24 I ++; 25} 26 27 if (iNum> = dt. rows. count-1) 28 break; 29} 30 31 string fileName = "list" + DateTime. now. toString ("yyyyMMddmmss") + ". xls "; 32 SaveFileDialog s AveFileDialog = new SaveFileDialog (); 33 saveFileDialog. filter = "Execl files (*. xls) | *. xls "; 34 saveFileDialog. filterIndex = 0; 35 saveFileDialog. restoreDirectory = true; // whether to remember the last opened directory 36 // saveFileDialog in the SAVE dialog box. createPrompt = true; 37 saveFileDialog. title = "Export an Excel file to"; 38 saveFileDialog. fileName = fileName; 39 40 // click the Save button to enter 41 if (saveFileDialog. showDialog () = DialogResult. OK) 42 {43 if (saveFi LeDialog. fileName. trim () = "") 44 {45 MessageBox. show ("Enter the file name to save", "prompt"); 46 return; 47} 48 using (FileStream file = new FileStream (saveFileDialog. fileName, FileMode. create) 49 {50 hssfworkbook. write (file); // create an xls file. 51 file. close (); 52} 53} 54} 55 catch (Exception ex) 56 {57 throw ex; 58} 59} 60 61 // <summary> 62 // Add data 63 by PAGE /// </summary> 64 /// <param name = "sheetName"> </param> 65 // <param name = "hssfworkbook"> </param> 66 // <param name = "dt"> </param> 67 // <param name = "dtNum"> </param> 68 // <param name = "constNum"> </param> 69 // <returns> </returns> 70 private int ExprotDataCreateSheet (string sh EetName, HSSFWorkbook hssfworkbook, DataTable dt, int dtNum, int constNum) 71 {72 int iNum = 0; 73 HSSFSheet sheet = (HSSFSheet) hssfworkbook. createSheet (sheetName); // create a SHEET 74 sheet. printSetup. paperSize = 9; // A4 paper 75 sheet. printSetup. landscape = false; // true: horizontal false: vertical 76 // set the page margin 77 sheet. setMargin (MarginType. rightMargin, (double) 0.2); 78 sheet. setMargin (MarginType. top margin, (double) 0.2 ); 79 sheet. setMargin (MarginType. leftMargin, (double) 0.2); 80 sheet. setMargin (MarginType. bottomMargin, (double) 0.2); 81 // sheet. printSetup. scale = (short) 72; // Scale 72% 82 83 // The first style, head up 84 HSSFCellStyle style = (HSSFCellStyle) hssfworkbook. createCellStyle (); 85 HSSFFont font = (HSSFFont) hssfworkbook. createFont (); 86 font. fontName = "" "; 87 font. fontHeight = 14*14; // set the font size to 88 font. boldweight = short. M AxValue; 89 style. setFont (font); // loads the font style 90 style in the style. alignment = NPOI. SS. userModel. horizontalAlignment. center; // horizontal Center 91 style. verticalAlignment = verticalignment. center; // Center 92 style. borderTop = NPOI. SS. userModel. borderStyle. thin; // set the cell border 93 style. borderLeft = NPOI. SS. userModel. borderStyle. thin; 94 style. borderRight = NPOI. SS. userModel. borderStyle. thin; 95 style. borderBottom = NPOI. SS. userM Odel. borderStyle. thin; 96 97 int rowNmu = 0; 98 HSSFRow row = (HSSFRow) sheet. createRow (rowNmu); // create a 99 string [] strColumn = excelColumn. split (new char [] {','}); 100 int iNo = 0; 101 foreach (string s in strColumn) 102 {103 if (! String. isNullOrEmpty (s) 104 {105 // set the width of each column. The first parameter is column N, and the second parameter is column 106 sheet. setColumnWidth (iNo, 20*256); 107 108 ExcelSetValue (row, iNo, style, s. trim (); 109} 110 iNo ++; 111} 112 for (int I = dtNum; I <dt. rows. count; I ++) 114 {115 iNum = I; 116 if (constNum = I) 117 break; 118 rowNmu ++; 119 row = (HSSFRow) sheet. createRow (rowNmu); // create a new row 120 121 iNo = 0; 122 foreach (string s in strColumn) 123 {124 if (! String. isNullOrEmpty (s) 125 {126 ExcelSetValue (row, iNo, style, dt. rows [I] [s. trim ()]. toString (); 127 iNo ++; 128} 129} 130} 131 return iNum; 132} 133 134 // <summary> 135 // create a cell and assign a value of 136 /// </summary> 137 /// <param name = "row"> row </param> 138 // <param name = "cellNum"> Number of columns </param> 139 // <param name = "style"> cell style </param> 140/ // <param name = "strValue"> cell value </param> 141 private void ExcelSetValue (HSSFRow row, 142 int cellNum, HSSFCellStyle style, string strValue) 143 {144 HSSFCell cell = (HSSFCell) row. createCell (cellNum); 145 cell. cellStyle = style; 146 cell. setCellValue (strValue); 147}

Call method:

1 /// <summary> 2 /// export 3 on a single page /// </summary> 4 /// <param name = "sender"> </param> 5 // /<param name = "e"> </param> 6 private void sbtnExport_Click (object sender, eventArgs e) 7 {8 try 9 {10 if (! String. IsNullOrEmpty (excelColumn) 11 {12 string SQL = string. Empty; 13 string strSort = string. Empty; 14 if (! String. isNullOrEmpty (sqlSort) 15 strSort = ("order by" + sqlSort); 16 string [] strCondition = sqlSort. split (new char [] {','}); 17 SQL = @ "SELECT * FROM (18 SELECT row_number () OVER (order by "+ strCondition [0] + @") AS sequence number, * 19 FROM (SELECT "+ sqlColumn +" from "+ sqlTable + 20" WHERE 1 = 1 "; 21 if (! String. isNullOrEmpty (sqlCondition) 22 SQL + = sqlCondition; 23 SQL + = ") a"; 24 25 SQL + = ("WHERE serial number BETWEEN" + (pageCurrent-1) * pageSize + 1) + "AND" + 26 pageCurrent * pageSize); 27 SQL + = strSort; 28 ExprotDataByDataTable (BaseDataAccess. executeQuery (SQL); 29} 30} 31 catch (Exception ex) 32 {33 MessageBox. show ("Excel export error:" + ex. message); 34} 35} 36 // <summary> 37 // export all the queried data 38 // </summar Y> 39 // <param name = "sender"> </param> 40 // <param name = "e"> </param> 41 private void sbtnExprotAll_Click (object sender, eventArgs e) 42 {43 try44 {45 if (! String. IsNullOrEmpty (excelColumn) 46 {47 string SQL = string. Empty; 48 string strSort = string. Empty; 49 if (! String. isNullOrEmpty (sqlSort) 50 strSort = ("order by" + sqlSort); 51 string [] strCondition = sqlSort. split (new char [] {','}); 52 SQL = @ "SELECT * 53 FROM (SELECT" + sqlColumn + "from" + sqlTable + 54 "WHERE 1 = 1"; 55 if (! String. isNullOrEmpty (sqlCondition) 56 SQL + = sqlCondition; 57 SQL + = ") a"; 58 59 SQL + = strSort; 60 ExprotDataByDataTable (BaseDataAccess. executeQuery (SQL); 61} 62} 63 catch (Exception ex) 64 {65 MessageBox. show ("Excel export error:" + ex. message); 66} 67}

 

 

Reference to add: