Use poi to create and download Excel files

Poiexcelutils. Java:

Package COM. saicfc. pmpf. internal. manage. utils; import Java. io. file; import Java. io. fileoutputstream; import Java. io. ioexception; import Java. util. calendar; import Java. util. iterator; import Java. util. linkedhashmap; import Java. util. list; import Org. apache. poi. hssf. usermodel. hssfcell; import Org. apache. poi. hssf. usermodel. hssfcellstyle; import Org. apache. poi. hssf. usermodel. hssfdataformat; import Org. apache. poi. Hssf. usermodel. hssfrow; import Org. apache. poi. hssf. usermodel. hssfsheet; import Org. apache. poi. hssf. usermodel. hssfworkbook; import COM. saicfc. pmpf. common. enums. channelcodeenums;/*** @ author lizhiyong * @ version $ ID: poiexcelutils. java, V 0.12014-September 18 9:28:30 exp $ */public class poiexcelutils {/*** custom date format */Private Static string date_format = "yyyy-mm-dd hh: mm: SS ";/*** custom floating point format */private stat IC string number_format = "#,## 0.00";/*** custom percentage format */Private Static string precent_format = "0.00%"; Private Static hssfworkbook workbook = new hssfworkbook (); private Static hssfsheet sheet = workbook. createsheet (); Private Static hssfrow row;/*** export Excel file * @ Param filepath * @ throws ioexception */public static void exportxls (string filepath) throws ioexception {try {fileoutputstream fout = New fileoutputstream (filepath); workbook. write (fout); fout. flush (); fout. close ();} catch (ioexception e) {e. getstacktrace () ;}}/*** export Excel file * @ Param file * @ throws ioexception */public static void exportxls (File file) throws ioexception {try {fileoutputstream fout = new fileoutputstream (File); workbook. write (fout); fout. flush (); fout. close ();} catch (ioexception e) {e. getstacktrace () ;}}/*** Add a row * @ Param index row number */public static void createrow (INT index) {ROW = sheet. createrow (INDEX);}/*** set the character value format of the cell * @ Param index column number * @ Param value the filled value of the cell */public static void setstringcell (INT index, string Value) {hssfcell cell = row. createcell (INDEX); cell. setcellvalue (value); cell. setcelltype (hssfcell. cell_type_string);}/*** set the cell date format * @ Param index column number * @ Param value in Cells Recharge */public static void setdatecell (INT index, calendar value) {hssfcell cell = row. createcell (INDEX); cell. setcellvalue (value. gettime (); // create a new cell style hssfcellstyle cellstyle = workbook. createcellstyle (); hssfdataformat format = workbook. createdataformat (); // set the cell style to the custom Date Format cellstyle. setdataformat (format. getformat (date_format); // center cellstyle. setalignment (hssfcellstyle. align_center); // sets the CE Ll date display format cell. setcellstyle (cellstyle);}/*** set the cell integer value format * @ Param index column number * @ Param Value cell fill value */public static void setintcell (INT index, int value) {hssfcell cell = row. createcell (INDEX); cell. setcelltype (hssfcell. cell_type_numeric); cell. setcellvalue (value);}/*** set the format of the floating point value of the cell * @ Param index column number * @ Param Value cell fill value */public static void setnumbercell (INT index, double value) {hssfcell Cell = row. createcell (INDEX); cell. setcelltype (hssfcell. cell_type_numeric); cell. setcellvalue (value); // create a new cell style hssfcellstyle cellstyle = workbook. createcellstyle (); hssfdataformat format = workbook. createdataformat (); // set the cell style to the custom floating point format cellstyle. setdataformat (format. getformat (number_format); // you can specify the display format of the cell floating point. setcellstyle (cellstyle);}/*** sets the cell percentage format * @ Param index column number * @ Param Value Cell fill value */public static void setpercentcell (INT index, double value) {hssfcell cell = row. createcell (INDEX); cell. setcellvalue (value); // create a new cell style hssfcellstyle cellstyle = workbook. createcellstyle (); hssfdataformat format = workbook. createdataformat (); cellstyle. setdataformat (format. getformat (precent_format); cell. setcellstyle (cellstyle);} public static void main (string [] ARGs) {system. out. P Rintln ("start exporting Excel files"); createrow (0); setstringcell (0, "No."); setstringcell (1, "name"); setstringcell (2, "date"); setstringcell (3, "amount"); createrow (1); setintcell (0, 1); setstringcell (1, "ICBC"); setdatecell (2, calendar. getinstance (); setnumbercell (3, 111123.99); createrow (2); setintcell (0, 2); setstringcell (1, "China Merchants Bank"); setdatecell (2, calendar. getinstance (); setnumbercell (3, 222456.88 ); Try {string filepath = "C:/lizhiyong.xls"; exportxls (filepath); system. out. println ("Excel file exported [succeeded]");} catch (ioexception E1) {system. out. println ("Excel File Export [failed]"); e1.printstacktrace ();}} /*** generate an Excel file poi * @ Param inputfile input template file path * @ Param outputfile the input file is stored in the server path * @ Param datalist data to be exported * @ throws exception */ @ suppresswarnings ("rawtypes ") public static file exportexcelfile (string ch Annelcode, string filepath, list titlelist, list datalist, string filename) throws exception {file = new file (filepath); If (! File. exists () {file. mkdir (); system. out. println ("folder created");} If (channelcodeenums. pingan. getchannelcode (). equals (channelcode) {// sets the width of the column sheet. setcolumnwidth (0, 5000); sheet. setcolumnwidth (1, 4000); sheet. setcolumnwidth (2, 8000);} // define the file name format and Create File excelfile = file. createtempfile (filename ,". xls ", new file (filepath); // Add the header information int ROW = 0; For (iterator = titlelist. iterator (); iterator. hasnext ();) {linkedhashmap titlemap = (linkedhashmap) iterator. next (); // Add a row createrow (ROW); int cell = 0; For (iterator titleiterator = titlemap. entryset (). iterator (); titleiterator. hasnext ();) {Java. util. map. entry titleentry = (Java. util. map. entry) titleiterator. next (); // Add Value-Added setstringcell (cell, (string) titleentry to the column. getvalue (); cell ++;} row ++;} // Add the cell information int rows = titlelist. size (); For (iterator = datalist. iterator (); iterator. hasnext ();) {linkedhashmap datamap = (linkedhashmap) iterator. next (); // Add a row of createrow (rows); int cells = 0; For (iterator dataiterator = datamap. entryset (). iterator (); dataiterator. hasnext ();) {Java. util. map. entry dataentry = (Java. util. map. entry) dataiterator. next (); If (channelcodeenums. pingan. getchannelcode (). equals (channelcode) {If ("refchannelorderno ". equals (dataentry. getkey () {// Add Value setstringcell (cells, (string) dataentry to the column. getvalue ();} else if ("amount ". equals (dataentry. getkey () {// Add the floating point value setnumbercell (cells, double. parsedouble (string) dataentry. getvalue ();} else {// Add Value-Added setstringcell (cells, (string) dataentry to the column. getvalue () ;}} else {// Add Value-Added setstringcell (cells, (string) dataentry to the column. getvalue ();} cells ++;} rows ++;} exportxls (excelfile); Return excelfile ;}}

The following is a call:

String filename = "Ping An Bank (Pingan) Refund data"; List titlelist = new arraylist (); linkedhashmap titlemap = new linkedhashmap (); titlemap. put ("title1", "Order Number"); titlemap. put ("title2", "refund amount"); titlemap. put ("title3", "refund reason"); titlelist. add (0, titlemap); file; try {file = poiexcelutils. exportexcelfile (channelcode, filepath, titlelist, exportdata, filename); // download the downloadfile (response, filepath, file);} catch (exception e) {log. error ("Download failed", e );}

/*** Download file ** @ Param response * @ Param filepath file path * @ Param file * @ throws ioexception */Public void downloadfile (httpservletresponse response, string filepath, file) throws ioexception {string filename = file. getname (); // download the file filemanageutils. exportfile (response, filepath + filename, filename); // delete a single file filemanageutils. deletefile (filepath, filename );}

Package COM. saicfc. pmpf. internal. manage. utils; import Java. io. file; import Java. io. fileinputstream; import Java. io. filenotfoundexception; import Java. io. ioexception; import Java. io. inputstream; import Java. io. outputstream; import javax. servlet. HTTP. httpservletresponse;/*** file management ** @ author lizhiyong * @ version $ ID: filemanageutils. java, V 0.12014-September 11 9:37:47 exp $ */public class filemanageutils {/*** Download file * @ Param response * @ Param csvfilepath * file path * @ Param filename * file name * @ throws ioexception */public static void exportfile (httpservletresponse response, string csvfilepath, string filename) throws ioexception {response. setcontenttype ("application/CSV; charset = GBK"); response. setheader ("content-disposition", "attachment; filename =" + new string (filename. getbytes ("GBK"), "ISO8859-1"); // ur Lencoder. encode (filename, "GBK") inputstream in = NULL; try {In = new fileinputstream (csvfilepath); int Len = 0; byte [] buffer = new byte [1024]; response. setcharacterencoding ("GBK"); outputstream out = response. getoutputstream (); While (LEN = in. read (buffer)> 0) {// out. write (New byte [] {(byte) 0xef, (byte) 0xbb, (byte) 0xbf}); out. write (buffer, 0, Len) ;}} catch (filenotfoundexception e ){ System. Out. println (E);} finally {If (in! = NULL) {try {In. close () ;}catch (exception e) {Throw new runtimeexception (e );}}}} /*** delete all files under the filepath directory * @ Param filepath * file directory path */public static void deletefiles (string filepath) {file = new file (filepath ); if (file. exists () {file [] files = file. listfiles (); For (INT I = 0; I <files. length; I ++) {If (files [I]. isfile () {files [I]. delete () ;}}}/*** delete a single file * @ Param filepath * file directory path * @ Param filename * file name */public static void deletefile (string filepath, string filename) {file = new file (filepath); If (file. exists () {file [] files = file. listfiles (); For (INT I = 0; I <files. length; I ++) {If (files [I]. isfile () {If (files [I]. getname (). equals (filename) {files [I]. delete (); Return ;}}}}}}

Use poi to create and download Excel files