Create an Excel file download using POI

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 Morning 9:28:30 EXP $ */public class Poiexcelutils {/** * Custom date format */private static String D    Ate_format = "Yyyy-mm-dd HH:mm:ss";    /** * Custom Floating-point format */private static String Number_format = "#,# #0.00";    /** * Custom percent format */private static String Precent_format = "0%"; 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 Files * @param file * @throws IOException */public static void Exportxls (file fil            E) 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 line number */public static void CreateRow (int IndeX) {row = Sheet.createrow (index); }/** * Sets the character value format of the cell * @param the index column number * @param value of cell padding */public static void Setstringcell (i        NT index, String value) {Hssfcell cell = Row.createcell (index);        Cell.setcellvalue (value);    Cell.setcelltype (hssfcell.cell_type_string); /** * Format Cell date * @param index column number * @param value cell padding value */public static void Setdatecell (int i        Ndex, 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 a custom date format Cellstyle.setdataformat (Format.getformat (date_format));        Center Cellstyle.setalignment (hssfcellstyle.align_center);    Sets the display format for the cell date Cell.setcellstyle (CellStyle); }/** * Set cell integer number format * @param index column # * @param value UnitLattice padding value */public static void Setintcell (int index, int value) {Hssfcell cell = Row.createcell (index);        Cell.setcelltype (Hssfcell.cell_type_numeric);    Cell.setcellvalue (value); /** * Set cell floating-point numeric format * @param index column number * @param value cell padding value */public static void Setnumbercell (i        NT 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 a custom floating-point number format Cellstyle.setdataformat (Format.getformat (Number_format));    Sets the display format of the cell floating-point number Cell.setcellstyle (CellStyle); /** * Set Cell percentage format * @param index column number * @param value cell padding value */public static void Setpercentcell (in        T 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.println ("Start export Excel file");        CreateRow (0);        Setstringcell (0, "number");        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 ("Export Excel file [success]"); } catch (IOException E1) {System.out.println ("Export Excel file [failed]");        E1.printstacktrace ();  }}/** * Generate an Excel file Poi * @param inputfile Input Template file path * @param outputFile input file is stored in the server path * @param DataList data to be exported * @throws Exception */@SuppressWarnings ("Rawtypes") public static File Exportexcelfile (St Ring Channelcode, String filePath, List titlelist, List dataList, string fileName) t        Hrows Exception {File File = new file (FilePath);            if (!file.exists ()) {File.mkdir ();        System.out.println ("directory created"); } if (ChannelCodeEnums.PINGAN.getChannelCode () equals (Channelcode)) {//Set column width SHEET.SETCOLUMNW            Idth (0, 5000);            Sheet.setcolumnwidth (1, 4000);        Sheet.setcolumnwidth (2, 8000);        }//define the file name format and create the files Excelfile = File.createtempfile (FileName, ". xls", new file (FilePath));        Add header information int row = 0; For (Iterator 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 the value Setstringcell (cell, (String) Titleentry.getvalue ()) to the column;            cell++;        } row++;        }//start by adding the cell information int rows = Titlelist.size (); for (Iterator Iterator = Datalist.iterator (); Iterator.hasnext ();)            {Linkedhashmap DataMap = (linkedhashmap) iterator.next ();            Add a row 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 (data Entry.getkey ())) {//Add value Setstringcell to the column (cells, (String) Dataentry.getvalue                    ()); } else if ("Amount". Equals (Dataentry.getkey ())) {//adds floating-point values to the column Setnumbercell                    (Cells, double.parsedouble (String) Dataentry.getvalue ()));                    } else {//Adds the value Setstringcell (cells, (String) Dataentry.getvalue ()) to the column; }} else {//Add value Setstringcell to the column (cells, (String) data                Entry.getvalue ());            } cells++;        } rows++;        } exportxls (Excelfile);    return excelfile; }}

Here is the 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 file; try {    file = Poiexcelutils.exportexcelfile (Channelcode, FilePath, Titlelist, ExportData,                    fileName);    Download file    downLoadFile (response, FilePath, file),  } catch (Exception e) {    log.error ("Download Failed", e);}

  /** *     Download file     * @param response     * @param filePath  file path     * @param files   file     * @throws ioexception< c8/>*/public    void DownLoadFile (httpservletresponse response, String filePath, file file)                                                                                      throws IOException {        String fileName = File.getname ();        Download 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.120      September 11, 14 morning 9:37:47 EXP $ */public class Filemanageutils {/** * Download file * @param response * @param csvfilepath * File path * @param filename * file name * @throws IOException */public static VO                                                                                                    ID 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 ")); UrlencOder.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 folder FilePath * @param filePath * File Folder path */PU        Blic static void Deletefiles (String filePath) {File 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 individual files * @param filePath * File Folder path * @param f Ilename * File name * * public static void DeleteFile (string filePath, string filename) {File File = N        EW File (FilePath);            if (file.exists ()) {file[] files = file.listfiles (); for (int i = 0; i < files.length; i++) {if (Files[i].isfile ()) {if (Files[i].getnam                        E (). Equals (FileName) {files[i].delete ();                    Return }                }            }        }    }}

Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.

Create an Excel file download using POI