Java Background POI export Excel based on template

 Public classExcelutils {Private Static FinalString Inspectionrecord_surface_templet_path = "Download\\template\\materiallist.xlsx"; Private StaticXssfcellstyle CellStyle =NULL;  Public Static voidExportbom (HttpServletRequest request, httpservletresponse response, map map)throwsIOException {//The path to the template, which is easy to mistake in your own project, the relative position must be written right AhString psth = Request.getrealpath ("/") +Inspectionrecord_surface_templet_path; Workbook WebBook=Readexcel (PSTH); Sheet Sheet= Webbook.getsheetat (0); //Calling Style methodsCellStyle =Createcellstyle (WebBook); Sheet.setcolumnwidth (0, 3766);//Column WidthSheet.setcolumnwidth (1, 4766);//Column Width//start the action template, find a row of a column (a cell), you need to note that there is a hole, row and column count is starting from 0//the location of the data insertion is not right, don't lose heart, try a few more times, if you can read my code below, where the data is inserted, you understand//Print Time        introws = 0; Row Row=sheet.getrow (rows); //call method, assign value and give style (when writing style, this tool class can only use XSSF and cannot use HSSF, otherwise the style will not respond)Setcellstylewithvalue (Row.createcell (0), (String) map.get ("title")); Rows= 2; Row=sheet.getrow (rows); Row.createcell (1). Setcellvalue (String) map.get ("date"));//Setcellstylewithvalue (Row.createcell (1), (String) map.get ("date"); //principalrows = 3; Row=sheet.getrow (rows); Row.createcell (1). Setcellvalue (String) map.get ("leader"));//Setcellstylewithvalue (Row.createcell (1), (String) Map.get ("Leader")); //Auditor.rows = 4; Row=sheet.getrow (rows); Row.createcell (1). Setcellvalue (String) map.get ("Auditleader"));//Setcellstylewithvalue (Row.createcell (1), (String) map.get ("Auditleader")); //When invoking a template, data insertions cannot be applied directly to the template line, or the data will be overwritten .rows = 7; Row=sheet.createrow (rows); Row.createcell (0). Setcellvalue ("number")); Row.createcell (1). Setcellvalue ("name")); Row.createcell (2). Setcellvalue ("Specs")); Row.createcell (3). Setcellvalue ("Quantity")); Row.createcell (4). Setcellvalue ("Benefit value"));        Row.setrowstyle (CellStyle); List<map<string, object>> list = (list<map<string, object>>) map.get ("Resultlist");  for(inti = 0; I < list.size (); i++) {row= Sheet.createrow (rows++);/*Row.createcell (0). Setcellvalue (Stringutils.objtostr (List.get (i). Get ("Item number"));            Row.createcell (1). Setcellvalue (Stringutils.objtostr (List.get (i). Get ("Item name"));            Row.createcell (2). Setcellvalue (Stringutils.objtostr (List.get (i). Get ("Material Specification")));            Row.createcell (3). Setcellvalue (Double.parsedouble (Stringutils.objtostr (List.get (i). Get ("Item consumption Quantity"))); Row.createcell (4). Setcellvalue (Float.parsefloat (Stringutils.objtostr (List.get (i). Get ("Material consumption profit and loss value" )));*/Setcellstylewithvalue (Row.createcell (0), Stringutils.objtostr (List.get (i). Get ("Item number"))); Setcellstylewithvalue (Row.createcell (1), Stringutils.objtostr (List.get (i). Get ("Item name"))); Setcellstylewithvalue (Row.createcell (2), Stringutils.objtostr (List.get (i). Get ("Item Specification"))); Setcellstylewithvalue (Row.createcell (3), Double.parsedouble (Stringutils.objtostr (List.get (i). Get ("Item consumption quantity")))); Setcellstylewithvalue (Row.createcell (4), Float.parsefloat (Stringutils.objtostr (List.get (i). Get (item consumption profit and loss value))))); } writeexcel (Response, WebBook, (String) Map.get ("Title")); }   Private Staticxssfworkbook readexcel (String filePath) {InputStream in=NULL; Xssfworkbook Work=NULL; Try{ in=NewFileInputStream (FilePath); work=NewXssfworkbook (in); } Catch(FileNotFoundException e) {System.out.println ("File path Error");        E.printstacktrace (); } Catch(IOException e) {System.out.println ("File input stream error");        E.printstacktrace (); }        returnWork ; }    Private Static voidWriteexcel (httpservletresponse response, Workbook work, String fileName)throwsIOException {outputstream out=NULL; Try{ out=Response.getoutputstream (); Response.setcontenttype ("Application/ms-excel;charset=utf-8"); Response.setheader ("Content-disposition", "Attachment;filename=". Concat (string.valueof (Urlencoder.encode (FileName+ ". xls", "UTF-8"))));        Work.write (out); } Catch(IOException e) {System.out.println ("Output stream Error");        E.printstacktrace (); } finally{out.close (); }    }    Private Staticcell Setcellstylewithstyleandvalue (cellstyle style, cell cell, String value) {Cell.setcellstyle (style);        Cell.setcellvalue (value); returncell; }    Private Staticcell Setcellstylewithstyleandvalue (cellstyle style, cell cell, Double value) {Cell.setcellstyle (style);        Cell.setcellvalue (value); returncell; }    Private Staticcell setcellstylewithvalue (cell cell, String value) {Cell.setcellstyle (CellStyle);        Cell.setcellvalue (value); returncell; }    Private Staticcell Setcellstylewithstyleandvalue (cellstyle style, cell cell, richtextstring value) {Cell.setcellstyle (style        );        Cell.setcellvalue (value); returncell; }    Private StaticCell Setcellstylewithvalue (cell cell,intvalue)        {Cell.setcellstyle (CellStyle);        Cell.setcellvalue (value); returncell; }    Private StaticCell Setcellstylewithvalue (cell cell,Doublevalue)        {Cell.setcellstyle (CellStyle);        Cell.setcellvalue (value); returncell; }    Private StaticXssfcellstyle Createcellstyle (Workbook wb) {CellStyle=(Xssfcellstyle) Wb.createcellstyle (); Cellstyle.setalignment (xssfcellstyle.align_center);//Cellstyle.setborderbottom (Xssfcellstyle.border_thin);//Cellstyle.setborderleft (Xssfcellstyle.border_thin);//cellstyle.setborderright (Xssfcellstyle.border_thin);//cellstyle.setbordertop (Xssfcellstyle.border_thin);cellstyle.setverticalalignment (Xssfcellstyle.vertical_center); returnCellStyle; }}

My own empty template, the figure of the data is not posted out!

Java Background POI export Excel based on template