Java Background POI export Excel based on template

Source: Internet
Author: User

 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.