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

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