Java Learning--spring MVC Create and download Excel from scratch using POI

Source: Internet
Author: User

This function description, click on the Download button on the front page, start to download the required data, as follows:

Google Chrome works as follows:

Open Excel as follows:

The Firefox browser works as follows:

First to import the POI jar package, my project uses MAVEN management, so add is relatively simple, the following code

Find the project's Pom.xml file to join in <dependencies>

<Dependency>    <groupId>Org.apache.poi</groupId>    <Artifactid>Poi</Artifactid>    <version>3.11</version></Dependency>

If you need a manual import can go to the official website to download: https://poi.apache.org/

Then you can start writing the code, and I'll write the backend code, the local environment test:

//List Partial referencesImportOrg.apache.poi.hssf.usermodel.HSSFCell;ImportOrg.apache.poi.hssf.usermodel.HSSFCellStyle;ImportOrg.apache.poi.hssf.usermodel.HSSFRow;ImportOrg.apache.poi.hssf.usermodel.HSSFSheet;ImportOrg.apache.poi.hssf.usermodel.HSSFWorkbook;Importorg.apache.poi.hssf.util.CellRangeAddress; String TimeStamp=NewSimpleDateFormat ("Yyyy-mm-dd"). Format (NewDate ()); //Create worksheets, sheet objects, rows, and cells. Hssfworkbook wb=NewHssfworkbook (); Hssfsheet sheet=wb.createsheet ("History"); Hssfrow Row=sheet.createrow (0); //Font CenteredHssfcellstyle style =Wb.createcellstyle ();        Style.setalignment (Hssfcellstyle.align_center); Hssfcell Cell=row.createcell (0);        Cell.setcellstyle (style); Cell.setcellvalue ("Validatorhistory" +TimeStamp); Sheet.addmergedregion (NewCellrangeaddress (0,0,0,5));//Merge cell parameter is start row, up to row, start column, up to column//Create a second row in sheetHssfrow Row2=sheet.createrow (1); //create cells and set cell contentsRow2.createcell (0). Setcellvalue ("Name1"); Row2.createcell (1). Setcellvalue ("Name2"); Row2.createcell (2). Setcellvalue ("Time1"); Row2.createcell (3). Setcellvalue ("Time2"); Row2.createcell (4). Setcellvalue ("Person1"); Row2.createcell (5). Setcellvalue ("Iscomplete"); //Adjust column widthsSheet.setcolumnwidth (0, ( Short) 5500); Sheet.setcolumnwidth (1, ( Short) 5500); Sheet.setcolumnwidth (2, ( Short) 5500); Sheet.setcolumnwidth (3, ( Short) 5500); Sheet.setcolumnwidth (4, ( Short) 6000); Sheet.setcolumnwidth (5, ( Short) 4000); //remove the values from the database into the table        intRowNum = 2; List< > history=*****repository.get*****;//extracting data from a database        if(history!=NULL&& history.size () >0){             for(Validatortrigger vt:history) {hssfrow Vtrow=Sheet.createrow (RowNum); Vtrow.createcell (0). Setcellvalue (Vt.getname1 ()); Vtrow.createcell (1). Setcellvalue (Vt.getname2 ()); Vtrow.createcell (2). Setcellvalue (Vt.gettime1 ()); Vtrow.createcell (3). Setcellvalue (Vt.gettime2 ()); Vtrow.createcell (4). Setcellvalue (Vt.getperson1 ()); Vtrow.createcell (5). Setcellvalue (Vt.iscomplete ()); RowNum++; }        }                //Output Excel FileFileOutputStream output=NewFileOutputStream ("D:\\workbook.xls");            Wb.write (out);                    Out.flush (); 

After running, you can see the Workbook.xls file on the D drive.

Test poi is available, start the overall write, first the front-end code, the front-end uses bootstrap:

// just posted the relevant code <div class= "down col-xs-6 col-md-offset-3" >            <a id= "Download" href= "#" class= "btn Btn-info btn-sm "> <span class=" Glyphicon glyphicon-download-alt "></span>DownloadHistory</a> </div>$ (' #download '). Click (function() {    var name1=$ ("select[id= NAME1] "). Val ();     var name2=$ ("select[id=name2]"). Val ();     Location.href= "/history/download?name1=" +name1+ "&name2=" +name2;  });

Here is a pit of the place, I used the Ajax way, no matter how I debug background are not downloaded shadow, then I finally have a suspicion of my front end, the online check, found that the AJAX request is just a "character" request, that the content of the request is stored in text type. This link:71520390 is therefore used Location.href method (specially selected with parameters)

The

is then the controller part: Receive parameters, call service

  @ResponseBody @RequestMapping (value  = "/history/download", Method = Requestmethod.get)  public  void  Download (httpservletrequest request, httpservletresponse response) throws  Span style= "color: #000000;"        > ioexception{String name1  =request.getparameter ("name1" );        String name2  =request.getparameter ("name2" );  Request.setcharacterencoding ( "UTF-8"); //  response.setcharacterencoding ("UTF-8"          );  service.downloadhistory (    Name1,name2,request,response); }

The service code is as follows:

 Public voidDownloadhistory (String name1, string name2,httpservletrequest request, httpservletresponse response)throwsioexception{OutputStream out=NULL; String TimeStamp=NewSimpleDateFormat ("Yyyy-mm-dd"). Format (NewDate ()); //Create worksheets, sheet objects, rows, and cells. //Create a second row in sheet//create cells and set cell contents//Adjust column widths//remove the values from the database into the table//The above part of the code is the same as before, in the output Excel file start different, as follows//Output Excel File        Try{response.reset (); Response.setcontenttype ("Application/x-download"); Response.setcharacterencoding ("Utf-8"); String fname= "History" + TimeStamp;//Excel file nameResponse.setheader ("Content-disposition", "attachment; Filename= "+fname+". xls "); out=Response.getoutputstream ();            Wb.write (out);        Out.flush (); }Catch(IOException e) {e.printstacktrace (); }finally{out.close (); }    }

And then you're done.

If you want to know more, you can refer to:

65435181

78663076

72778348?locationnum=5&fps=1

53491258

53432364

78810622

54375399

Java Learning--spring MVC Create and download Excel from scratch using POI

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.