"Springmvc+mybatis project" Jie Xin Business-25. Shipment Form Printing

Before we learned about POI technology, we can make use of POI for custom Excel file generation. We will then use this technology to achieve our shipping table printing.

Look back at our shipping list

We will use POI to construct such an Excel document and then build it.

Let's start with the analysis from the beginning, "August 2012 shipping List" is a title, and merged cells, what should we do?

The development steps of our shipping table are as follows
1. Get Data
2. Create an Excel file
3. Writing data to an Excel file

Start with "Get Data", our shipping list is shipped by month, so we have to write an interface, so that users on this page to select a few months to print the shipping table:

<%@ page language= "java" pageencoding= "UTF-8"%><%@ include file= ". /..    /base.jsp "%>
Then we need a controller to handle the date selected by the user, and then create the appropriate file:
Package Cn.hpu.jk.controller.cargo.outproduct;import Org.springframework.stereotype.controller;import Org.springframework.web.bind.annotation.requestmapping;import cn.hpu.jk.controller.basecontroller;@ Controllerpublic class Outproductcontroller extends basecontroller{//turns on page @requestmapping ("/cargo/outproduct/ Toedit.action ") Public String Toedit () {return"/cargo/outproduct/joutproduct.jsp ";} Print @requestmapping ("/cargo/outproduct/print.action") public void print (String inputdate) {  //inputdate format: Yyyy-mmsystem.out.println (inputdate);}}

Here, we'll print the inputdate and see if the value is coming.

We then add our entry to the left-hand menu bar on our page:
</div>       <div class= "Panel" >       <div class= "Panel_icon" ></div>       <div class=" Panel-header ">       <div class=" Panel-title "> Freight management </div>       <div class=" panel-content "><ul><li><a href=" ${ctx}/ Cargo/contract/list.action "onclick=" linkhighlighted (This) "target=" main "id=" aa_1 "> Purchase and Sale contract </a></li> <li><a href= "${ctx}/cargo/outproduct/toedit.action" onclick= "linkhighlighted (This)" target= "main" id= "Aa_ 1 "> Shipping list </a></li></ul>       </div>       </div></div>

Effect:


After restarting the server, click "Shipping List", you will see the following interface

Then select a date to click Print, and in the console you will see the date we selected

So our date can be passed on normally.

We are going to get the data on the shipping table, we have analyzed it before, and we have to check the contract and the goods table in the end.

Here is our SQL to get the data from the shipping table (take the November 2014 data for example)
Selectc.custom_name,c.contract_no,cp.product_no,cp.cnumber,cp.factory_name,cp.exts,c.delivery_period,c.ship_ Time,c.trade_termsfrom (select  contract_id,custom_name,contract_no,delivery_period,ship_time,trade_ Termsfrom contract_c) Cleft join (select Contract_id,product_no,cnumber| | Packing_unit as cnumber,factory_name,exts from Contract_product_c) cpon C.contract_id=cp.contract_idwhere To_char ( C.ship_time, ' yyyy-mm ') = ' 2014-11 '

We can see from the above shipping table information, we need to inquire about the "customer", "Order Number", "Number", "Quantity", "Factory", "Accessories", "Factory delivery", "schedule" and "Trade terms" these fields.
So we can customize a Vo object (including the above data), used to selectively use SQL to query out the required data and then encapsulate to our custom Vo object
Package Cn.hpu.jk.vo;public class Outproductvo {private string customname;private string Contract_no;private string Productno;private string Cnumber;private string factoryname;private string exts;private string delivery_preriod; private string Ship_time;private string Tradeterms;//get and set method omitted}

We now construct a shipping Table mapper mapping file Outproductmapper.xml, in which to add query statements, mainly to query the data we need to ship the table:
<?xml version= "1.0" encoding= "UTF-8"? ><! DOCTYPE mapperpublic "-//mybatis.org//dtd Mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd" >< Mapper namespace= "Cn.hpu.jk.mapper.OutProductMapper" ><resultmap type= "Cn.hpu.jk.vo.OutProductVO" id= " OUTPRODUCTRM "><result property=" Customname "column=" Custom_name "jdbctype=" VARCHAR "/><result property= "Contractno" column= "Contract_no" jdbctype= "VARCHAR"/><result property= "Productno" column= "PRODUCT_NO" jdbctype= "varchar"/><result property= "Cnumber" column= "cnumber" jdbctype= "varchar"/><result property= " Factoryname "column=" Factory_name "jdbctype=" VARCHAR "/><result property=" exts "column=" exts "jdbcType=" varchar "/><result property=" Delivery_preriod "column=" delivery_preriod "jdbctype=" varchar "/><result Property= "Ship_time" column= "Ship_time" jdbctype= "VARCHAR"/><result property= "tradeterms" column= "TRADE_ TERMS "jdbctype=" VARCHAR "/></resultmap><select id=" finD "parametertype=" string "resultmap=" OUTPRODUCTRM ">select c.custom_name,c.contract_no,cp.product_no,cp.cnumber , Cp.factory_name,cp.exts, To_char (c.delivery_period, ' yyyy-mm-dd ') as Delivery_period, To_char (C.ship_time, ' Yyyy-mm-dd ') as Ship_time,c.trade_termsfrom (select Contract_id,custom_name,contract_no,delivery_period,ship_time, Trade_termsfrom contract_c) Cleft join (select Contract_id,product_no,cnumber| | Packing_unit as cnumber,factory_name,exts from Contract_product_c) cpon C.contract_id=cp.contract_idwhere To_char ( C.ship_time, ' yyyy-mm ') = #{inputdate}</select></mapper>

Next we start writing our DAO Layer:
Outproductdao.java:
Package Cn.hpu.jk.dao;import Cn.hpu.jk.vo.outproductvo;public Interface Outproductdao extends basedao< outproductvo>{}

Outproductdaoimpl.java:
Package Cn.hpu.jk.dao.impl;import Org.springframework.stereotype.repository;import Cn.hpu.jk.dao.OutProductDao; Import Cn.hpu.jk.vo.OutProductVO; @Repository//For packet scanning this DAO is scanned to public class Outproductdaoimpl extends Basedaoimpl <OutProductVO> implements Outproductdao{public Outproductdaoimpl () {//Set namespace Super.setns (" Cn.hpu.jk.mapper.OutProductMapper ");} }

Next start writing the service layer:
Outproductservice.java:
Package Cn.hpu.jk.service;import Java.util.list;import Cn.hpu.jk.vo.outproductvo;public interface OutProductService {public list<outproductvo> find (String inputdate);}

Outproductserviceimpl.java:
Package Cn.hpu.jk.service.impl;import Java.util.hashmap;import Java.util.list;import java.util.map;import Javax.annotation.resource;import Cn.hpu.jk.dao.outproductdao;import Cn.hpu.jk.service.outproductservice;import Cn.hpu.jk.vo.outproductvo;public class Outproductserviceimpl implements outproductservice{@ResourceOutProductDao Outproductdao; @Overridepublic list<outproductvo> Find (String inputdate) {Map paramap=new HashMap ();p aramap.put ("Inputdate", inputdate); return Outproductdao.find (Paramap);}}

Inject our service into the Beans-service.xml:
<bean name= "Outproductservice" class= "Cn.hpu.jk.service.impl.OutProductServiceImpl"/>

Back to our previous controller class, we wrote the Print method
Package Cn.hpu.jk.controller.cargo.outproduct;import Java.util.list;import Javax.annotation.resource;import Org.springframework.stereotype.controller;import Org.springframework.web.bind.annotation.requestmapping;import Cn.hpu.jk.controller.basecontroller;import Cn.hpu.jk.service.outproductservice;import Cn.hpu.jk.vo.OutProductVO; @Controllerpublic class Outproductcontroller extends basecontroller{@ResourceOutProductService outproductservice;// Turn to Page @requestmapping ("/cargo/outproduct/toedit.action") public String Toedit () {return '/cargo/outproduct/ Joutproduct.jsp ";} Print @requestmapping ("/cargo/outproduct/print.action") public void print (String inputdate) {  //inputdate format: Yyyy-mmlist<outproductvo> Datalist=outproductservice.find (inputdate); System.out.println (Datalist.size ()); System.out.println (inputdate);}}

The November 2014 we took out of the database was 2 records.


We experimented, and found that we took out 2, explaining our Find method function implementation.



So then we're going to start writing the data we've got into Excel, and we'll add the following code to the Print method:
Print @requestmapping ("/cargo/outproduct/print.action") public void print (String inputdate) throws ioexception{// Inputdate format:yyyy-mmlist<outproductvo> datalist=outproductservice.find (inputdate);/*System.out.println ( Datalist.size ()); System.out.println (inputdate); */workbook wb=new Hssfworkbook (); Sheet Sheet=wb.createsheet (); Row Nrow=null;  Cell Ncell=null;int rowno=0; Line number int cellno=1;//column number rowno++;//configuration header row String [] title=new string[]{"Customer", "Order Number", "SKU", "Quantity", "Factory", "attachment", "Factory delivery", "schedule", "Trade terms" };nrow=sheet.createrow (rowno++); for (int i = 0; i < title.length; i++) {Ncell=nrow.createcell (i+1); Ncell.setcellvalue (Title[i]);} Processing data for (int i = 0; i < datalist.size (); i++) {Outproductvo op=datalist.get (i); Nrow=sheet.createrow (rowno++); Cellno =1;//column number initialization Ncell=nrow.createcell (cellno++); Ncell.setcellvalue (Op.getcustomname ()); Ncell=nrow.createcell (cellNo+ +); Ncell.setcellvalue (Op.getcontractno ()); Ncell=nrow.createcell (cellno++); Ncell.setcellvalue (Op.getProductNo () ); Ncell=nrow.createcell (cellno++); ncell.sEtcellvalue (Op.getcnumber ()); Ncell=nrow.createcell (cellno++); Ncell.setcellvalue (Op.getfactoryname ()); nCell= Nrow.createcell (cellno++); Ncell.setcellvalue (Op.getexts ()); Ncell=nrow.createcell (cellno++); NCell.setCellValue ( Op.getdelivery_preriod ()); Ncell=nrow.createcell (cellno++); Ncell.setcellvalue (Op.getship_time ()); nCell= Nrow.createcell (cellno++); Ncell.setcellvalue (Op.gettradeterms ());} OutputStream os=new FileOutputStream (New File ("F:\\outproduct.xls")); wb.write (OS); Os.close ();}

Then we went to the F-disk and saw that a new file was generated

Open this file and we can see that our shipping list has been printed out, as in the database.



But we also need to modify the table to look better and add the download link, which we'll continue to discuss in the next article.
Reprint Please specify source: http://blog.csdn.net/acmman/article/details/48710311
 
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
 
"Springmvc+mybatis project" Jie Xin Business-25. Shipment Form Printing