【springmvc+mybatis项目实战】杰信商贸-29.购销合同技术难点分析

我们接下来要进行最复杂的一个打印，就是购销合同的打印---制式表单的打印，业界最复杂的报表打印。

a)分析技术难点：
1）插入图片，POI插入图片时，设定区域，自动缩放到这个区域中，图片要设置偏移量
HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); //add picture
poiUtil.setPicture(wb, patriarch, path+"make/xlsprint/logo.jpg", curRow, 2, curRow+4, 2);

2）模板，对于购销合同它不能直接做模板。代码中不能做这个功能。Excel支持区域复制。但是目前POI不直接支持区域复制。（合并单元格在sheet对象中、图片、线等）（基于用户使用系统，以及POI不直接支持区域复制，所以它不能直接使用模板）
A.只是改变列宽
B.画一个sheet，利用poi支持sheet的复制，保留样式。

3）插入一根线，设置两个坐标，它直接连线，可以是直线，可以是斜线，可以设置偏移量
poiUtil.setLine(wb, patriarch, curRow, 2, curRow, 8); //draw line

4）合并单元格，特点：合并单元格的边线样式，它实际上是在合并前的单元格上设置的。特殊的地方：在单元格如果有样式，合并单元格的这几个格子都必须设置边线样式。
CellRangeAddress region = null;
region = new CellRangeAddress(curRow-1, curRow-1, 1, 3); //纵向合并单元格

不需要设置内容，只设置样式，补齐线。
for(int j=2;j<9;j++){
nCell = nRow.createCell(j);
nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));
}

5）换行
curStyle.setWrapText(true);

6）设置人民币符号（前缀）
format.getFormat("\"￥\"#,###,###.00");

7）设置公式，乘法，合计公式
nCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
nCell.setCellFormula("F"+String.valueOf(curRow)+"*H"+String.valueOf(curRow));

8）实现审单人的人名等量空格替换，实现它后面的位置不变（工具类）
utilFuns.fixSpaceStr(contract.getCheckBy(),26)

9）单元格自适应（工具类）
float height = poiUtil.getCellAutoHeight(printMap.get("Request"), 12f); //自动高度

10）数据和业务分离
一页数据封装到一个map中，以业务字段名称定义KEY，以string定义内容
所有页面封装到一个arraylist。
Map<String,String> pageMap = null;
List<Map> pageList = new ArrayList(); //打印页



11）插入分页符
if(p>0){
sheet.setRowBreak(curRow++); //在第startRow行设置分页符
}

12）公用的工具类PoiUtil poiUtil = new PoiUtil();  样式，特殊内容封装

13）return format.getFormat("\"￥\"#,###,##0.00"); // 设置格式
0代表，这位值如果不为零，显示原来的内容，直接输出0
#代表，这位值如果存在，就直接显示，不存在，就不显示。

14）nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

购销合同最总打印样式:



实现代码:

[java]  view plain copy

1. package cn.itcast.jk.print;  
2.   
3. import java.io.ByteArrayOutputStream;  
4. import java.io.FileInputStream;  
5. import java.util.ArrayList;  
6. import java.util.HashMap;  
7. import java.util.List;  
8. import java.util.Map;  
9.   
10. import javax.servlet.http.HttpServletResponse;  
11.   
12. import org.apache.poi.hssf.usermodel.HSSFCell;  
13. import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
14. import org.apache.poi.hssf.usermodel.HSSFFont;  
15. import org.apache.poi.hssf.usermodel.HSSFPatriarch;  
16. import org.apache.poi.hssf.usermodel.HSSFRow;  
17. import org.apache.poi.hssf.usermodel.HSSFSheet;  
18. import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
19. import org.apache.poi.hssf.util.CellRangeAddress;  
20.   
21.   
22. import cn.itcast.jk.vo.ContractProductVO;  
23. import cn.itcast.jk.vo.ContractVO;  
24. import cn.itcast.util.DownloadUtil;  
25. import cn.itcast.util.UtilFuns;  
26. import cn.itcast.util.file.PoiUtil;  
27.   
28.   
29. //手工POI写excel文件  
30. public class ContractPrint{  
31.       
32.       
33.       
34.     public void print(ContractVO contract,String path, HttpServletResponse response) throws Exception{  
35.         //相同厂家的信息一起打印  
36.         List<ContractProductVO> oList = contract.getContractProducts();  
37.         UtilFuns utilFuns = new UtilFuns();  
38.         String tempXlsFile = path + "make/xlsprint/tCONTRACT.xls";      //获取模板文件  
39.           
40.         //填写每页的内容，之后在循环每页读取打印  
41.         Map<String,String> pageMap = null;  
42.         List<Map> pageList = new ArrayList();         //打印页  
43.           
44.         ContractProductVO oProduct = null;  
45.         String stars = "";  
46.         for(int j=0;j<contract.getImportNum();j++){      //重要程度  
47.             stars += "★";  
48.         }  
49.           
50.         String oldFactory = "";  
51.         for(int i=0;i<oList.size();i++){  
52.             oProduct = oList.get(i);    //获得货物  
53.             pageMap = new HashMap();    //每页的内容  
54.               
55.             pageMap.put("Offeror", "收 购 方：" + contract.getOfferor());  
56.             pageMap.put("Factory", "生产工厂：" + oProduct.getFactory().getFactoryName());  
57.             pageMap.put("ContractNo", "合 同 号：" + contract.getContractNo());  
58.             pageMap.put("Contractor", "联 系 人：" + oProduct.getFactory().getContractor());  
59.             pageMap.put("SigningDate", "签单日期："+UtilFuns.formatDateTimeCN(UtilFuns.dateTimeFormat(contract.getSigningDate())));  
60.             pageMap.put("Phone", "电    话：" + oProduct.getFactory().getPhone());  
61.             pageMap.put("InputBy", "制单：" + contract.getInputBy());  
62.             pageMap.put("CheckBy", "审单："+ utilFuns.fixSpaceStr(contract.getCheckBy(),26)+"验货员："+utilFuns.convertNull(contract.getInspector()));  
63.             pageMap.put("Remark", "  "+contract.getRemark());  
64.             pageMap.put("Request", "  "+contract.getCrequest());  
65.               
66.             pageMap.put("ProductImage", oProduct.getProductImage());  
67.             pageMap.put("ProductDesc", oProduct.getProductDesc());  
68.             pageMap.put("Cnumber", String.valueOf(oProduct.getCnumber().doubleValue()));  
69.             if(oProduct.getPackingUnit().equals("PCS")){  
70.                 pageMap.put("PackingUnit", "只");  
71.             }else if(oProduct.getPackingUnit().equals("SETS")){  
72.                 pageMap.put("PackingUnit", "套");  
73.             }  
74.             pageMap.put("Price", String.valueOf(oProduct.getPrice().doubleValue()));  
75.             pageMap.put("ProductNo", oProduct.getProductNo());  
76.               
77.             oldFactory = oProduct.getFactory().getFactoryName();  
78.               
79.             if(contract.getPrintStyle().equals("2")){  
80.                 i++;    //读取第二个货物信息  
81.                 if(i<oList.size()){  
82.                     oProduct = oList.get(i);  
83.                       
84.                     if(oProduct.getFactory().getFactoryName().equals(oldFactory)){  //厂家不同另起新页打印，除去第一次的比较  
85.                           
86.                         pageMap.put("ProductImage2", oProduct.getProductImage());  
87.                         pageMap.put("ProductDesc2", oProduct.getProductDesc());  
88.                         pageMap.put("Cnumber2", String.valueOf(oProduct.getCnumber().doubleValue()));  
89.                         if(oProduct.getPackingUnit().equals("PCS")){  
90.                             pageMap.put("PackingUnit2", "只");  
91.                         }else if(oProduct.getPackingUnit().equals("SETS")){  
92.                             pageMap.put("PackingUnit2", "套");  
93.                         }                         
94.                         pageMap.put("Price2", String.valueOf(oProduct.getPrice().doubleValue()));  
95.                         //pageMap.put("Amount2", String.valueOf(oProduct.getAmount().doubleValue()));           //在excel中金额采用公式，所以无需准备数据  
96.                         pageMap.put("ProductNo2", oProduct.getProductNo());  
97.                     }else{  
98.                         i--;    //tip:list退回  
99.                     }  
100.                 }else{  
101.                     pageMap.put("ProductNo2", null);    //后面依据此判断是否有第二个货物  
102.                 }  
103.             }  
104.               
105.             pageMap.put("ContractDesc", stars+" 货物描述");         //重要程度 + 货物描述  
106.               
107.             pageList.add(pageMap);  
108.         }  
109.           
110.         int cellHeight = 96;    //一个货物的高度           用户需求，一个货物按192高度打印，后来又嫌难看，打印高度和2款高度一样。  
111. //      if(contract.getPrintStyle().equals("2")){  
112. //          cellHeight = 96;    //两个货物的高度  
113. //      }  
114.           
115.         PoiUtil poiUtil = new PoiUtil();  
116.         HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(tempXlsFile));   //打开excel文件  
117.         HSSFFont defaultFont10 = poiUtil.defaultFont10(wb);     //设置字体  
118.         HSSFFont defaultFont12 = poiUtil.defaultFont12(wb);     //设置字体  
119.         HSSFFont blackFont = poiUtil.blackFont12(wb);           //设置字体  
120.         Short rmb2Format = poiUtil.rmb2Format(wb);              //设置格式  
121.         Short rmb4Format = poiUtil.rmb4Format(wb);              //设置格式  
122.           
123.   
124.   
125.         HSSFSheet sheet = wb.getSheetAt(0);             //选择第一个工作簿  
126.         wb.setSheetName(0, "购销合同");                 //设置工作簿的名称  
127.   
128.   
129.   
130.   
131.         //sheet.setDefaultColumnWidth((short) 20);      // 设置每列默认宽度  
132.           
133. //      POI分页符有BUG，必须在模板文件中插入一个分页符，然后再此处删除预设的分页符；最后在下面重新设置分页符。  
134. //      sheet.setAutobreaks(false);  
135. //      int iRowBreaks[] = sheet.getRowBreaks();  
136. //      sheet.removeRowBreak(3);  
137. //      sheet.removeRowBreak(4);  
138. //      sheet.removeRowBreak(5);  
139. //      sheet.removeRowBreak(6);  
140.           
141.         CellRangeAddress region = null;  
142.         HSSFPatriarch patriarch = sheet.createDrawingPatriarch();       //add picture  
143.   
144.   
145.         HSSFRow nRow = null;  
146.         HSSFCell nCell   = null;  
147.         int curRow = 0;  
148.           
149.         //打印每页  
150.         Map<String,String> printMap = null;  
151.         for(int p=0;p<pageList.size();p++){  
152.             printMap = pageList.get(p);  
153.               
154.             if(p>0){  
155.                 sheet.setRowBreak(curRow++);    //在第startRow行设置分页符  
156.             }  
157.               
158.               
159.             //设置logo图片  
160.             poiUtil.setPicture(wb, patriarch, path+"make/xlsprint/logo.jpg", curRow, 2, curRow+4, 2);  
161.               
162.             //header  
163.             nRow = sheet.createRow(curRow++);  
164.             nRow.setHeightInPoints(21);  
165.               
166.             nCell   = nRow.createCell((3));  
167.             nCell.setCellValue("SHAANXI");  
168.             nCell.setCellStyle(headStyle(wb));  
169.   
170.   
171.             //header  
172.             nRow = sheet.createRow(curRow++);  
173.             nRow.setHeightInPoints(41);  
174.               
175.             nCell   = nRow.createCell((3));  
176.             nCell.setCellValue("     JK INTERNATIONAL ");  
177.             nCell.setCellStyle(tipStyle(wb));  
178.   
179.   
180.             curRow++;  
181.               
182.             //header  
183.             nRow = sheet.createRow(curRow++);  
184.             nRow.setHeightInPoints(20);  
185.               
186.             nCell   = nRow.createCell((1));  
187.             nCell.setCellValue("                 西经济技术开发区西城一路27号无迪大厦19楼");  
188.             nCell.setCellStyle(addressStyle(wb));  
189.               
190.             //header  
191.             nCell   = nRow.createCell((6));  
192.             nCell.setCellValue(" CO., LTD.");  
193.             nCell.setCellStyle(ltdStyle(wb));  
194.   
195.   
196.             //header  
197.             nRow = sheet.createRow(curRow++);  
198.             nRow.setHeightInPoints(15);  
199.               
200.             nCell   = nRow.createCell((1));  
201.             nCell.setCellValue("                   TEL: 0086-29-86339371  FAX: 0086-29-86303310               E-MAIL: ijackix@glass.cn");  
202.             nCell.setCellStyle(telStyle(wb));  
203.               
204.             //line  
205.             nRow = sheet.createRow(curRow++);  
206.             nRow.setHeightInPoints(7);  
207.               
208.             poiUtil.setLine(wb, patriarch, curRow, 2, curRow, 8);   //draw line  
209.   
210.   
211.             //header  
212.             nRow = sheet.createRow(curRow++);  
213.             nRow.setHeightInPoints(30);  
214.               
215.             nCell   = nRow.createCell((4));  
216.             nCell.setCellValue("    购   销   合   同");  
217.             nCell.setCellStyle(titleStyle(wb));  
218.               
219.             //Offeror  
220.             nRow = sheet.createRow(curRow++);  
221.             nRow.setHeightInPoints(20);  
222.               
223.             nCell   = nRow.createCell((1));  
224.             nCell.setCellValue(printMap.get("Offeror"));  
225.             nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));  
226.   
227.   
228.             //Facotry  
229.             nCell   = nRow.createCell((5));  
230.             nCell.setCellValue(printMap.get("Factory"));  
231.             nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));  
232.               
233.             //ContractNo  
234.             nRow = sheet.createRow(curRow++);  
235.             nRow.setHeightInPoints(20);  
236.               
237.             nCell   = nRow.createCell(1);  
238.             nCell.setCellValue(printMap.get("ContractNo"));  
239.             nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));  
240.               
241.             //Contractor  
242.             nCell  = nRow.createCell(5);  
243.             nCell.setCellValue(printMap.get("Contractor"));  
244.             nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));  
245.               
246.             //SigningDate  
247.             nRow = sheet.createRow(curRow++);  
248.             nRow.setHeightInPoints(20);  
249.               
250.             nCell = nRow.createCell(1);  
251.             nCell.setCellValue(printMap.get("SigningDate"));  
252.             nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));  
253.               
254.             //Phone  
255.             nCell = nRow.createCell(5);  
256.             nCell.setCellValue(printMap.get("Phone"));  
257.             nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));  
258.               
259.             //importNum  
260.             nRow = sheet.createRow(curRow++);  
261.             nRow.setHeightInPoints(24);  
262.               
263.             region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格   
264.             sheet.addMergedRegion(region);  
265.               
266.             nCell = nRow.createCell(1);  
267.             nCell.setCellValue("产品");  
268.             nCell.setCellStyle(thStyle(wb));          
269.               
270.             nCell = nRow.createCell(2);  
271.             nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));  
272.               
273.             nCell = nRow.createCell(3);  
274.             nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));  
275.               
276.             nCell = nRow.createCell(4);  
277.             nCell.setCellValue(printMap.get("ContractDesc"));  
278.             nCell.setCellStyle(thStyle(wb));      
279.               
280.             region = new CellRangeAddress(curRow-1, curRow-1, 5, 6);    //纵向合并单元格   
281.             sheet.addMergedRegion(region);  
282.               
283.             nCell = nRow.createCell(5);  
284.             nCell.setCellValue("数量");  
285.             nCell.setCellStyle(thStyle(wb));      
286.               
287.             nCell = nRow.createCell(6);  
288.             nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));           
289.               
290.             nCell = nRow.createCell(7);  
291.             nCell.setCellValue("单价");  
292.             nCell.setCellStyle(thStyle(wb));                          
293.               
294.             nCell = nRow.createCell(8);  
295.             nCell.setCellValue("总金额");  
296.             nCell.setCellStyle(thStyle(wb));                          
297.   
298.   
299.   
300.   
301.             nRow = sheet.createRow(curRow++);  
302.             nRow.setHeightInPoints(96);  
303.               
304.             region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格   
305.             sheet.addMergedRegion(region);  
306.               
307.             //插入产品图片  
308.             if(UtilFuns.isNotEmpty(printMap.get("ProductImage"))){  
309.                 System.out.println(printMap.get("ProductImage"));  
310.                 poiUtil.setPicture(wb, patriarch, path+"ufiles/jquery/"+printMap.get("ProductImage"), curRow-1, 1, curRow, 3);  
311.             }  
312.               
313.             nCell = nRow.createCell(2);  
314.             nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));  
315.               
316.             nCell = nRow.createCell(3);  
317.             nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));  
318.               
319.             //ProductDesc  
320.             region = new CellRangeAddress(curRow-1, curRow, 4, 4);  //纵向合并单元格   
321.             sheet.addMergedRegion(region);  
322.               
323.             nCell = nRow.createCell(4);  
324.             nCell.setCellValue(printMap.get("ProductDesc"));  
325.             nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));       
326.               
327.             //Cnumber  
328.             region = new CellRangeAddress(curRow-1, curRow, 5, 5);  //纵向合并单元格   
329.             sheet.addMergedRegion(region);  
330.               
331.             nCell = nRow.createCell(5);  
332.             nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);  
333.             nCell.setCellValue(Double.parseDouble(printMap.get("Cnumber")));  
334.             nCell.setCellStyle(poiUtil.numberrv10_BorderThin(wb, defaultFont10));     
335.               
336.             //Unit  
337.             region = new CellRangeAddress(curRow-1, curRow, 6, 6);  //纵向合并单元格   
338.             sheet.addMergedRegion(region);  
339.               
340.             nCell = nRow.createCell(6);  
341.             nCell.setCellValue(printMap.get("PackingUnit"));  
342.             nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));      
343.               
344.             //Price  
345.             region = new CellRangeAddress(curRow-1, curRow, 7, 7);  //纵向合并单元格   
346.             sheet.addMergedRegion(region);  
347.               
348.             nCell = nRow.createCell(7);  
349.             nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);  
350.             nCell.setCellValue(Double.parseDouble(printMap.get("Price")));  
351.             nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));  
352.               
353.               
354.             //Amount  
355.             region = new CellRangeAddress(curRow-1, curRow, 8, 8);  //纵向合并单元格   
356.             sheet.addMergedRegion(region);  
357.               
358.             nCell = nRow.createCell(8);  
359.             if(UtilFuns.isNotEmpty(printMap.get("Cnumber")) && UtilFuns.isNotEmpty(printMap.get("Price"))){  
360.                 nCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);  
361.                 nCell.setCellFormula("F"+String.valueOf(curRow)+"*H"+String.valueOf(curRow));  
362.             }  
363.             nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));              
364.   
365.   
366.             curRow++;  
367.               
368.             region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格   
369.             sheet.addMergedRegion(region);  
370.               
371.             //ProductNo  
372.             nRow = sheet.createRow(curRow-1);  
373.             nRow.setHeightInPoints(24);  
374.               
375.             nCell = nRow.createCell(1);  
376.             nCell.setCellValue(printMap.get("ProductNo"));  
377.             nCell.setCellStyle(poiUtil.notecv10_BorderThin(wb, defaultFont10));  
378.               
379.             for(int j=2;j<9;j++){  
380.                 nCell = nRow.createCell(j);  
381.                 nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));  
382.             }  
383.               
384.               
385.               
386.             if(contract.getPrintStyle().equals("2") && UtilFuns.isNotEmpty(printMap.get("ProductNo2"))){  
387.                 nRow = sheet.createRow(curRow++);  
388.                 nRow.setHeightInPoints(96);  
389.                   
390.                 region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格   
391.                 sheet.addMergedRegion(region);  
392.                   
393.                 //插入产品图片  
394.                 if(UtilFuns.isNotEmpty(printMap.get("ProductImage2"))){  
395.                     System.out.println(printMap.get("ProductImage2"));  
396.                     poiUtil.setPicture(wb, patriarch, path+"ufiles/jquery/"+printMap.get("ProductImage2"), curRow-1, 1, curRow, 3);  
397.                 }  
398.                   
399.                 //ProductDesc  
400.                 region = new CellRangeAddress(curRow-1, curRow, 4, 4);  //纵向合并单元格   
401.                 sheet.addMergedRegion(region);  
402.                   
403.                 nCell = nRow.createCell(4);  
404.                 nCell.setCellValue(printMap.get("ProductDesc2"));  
405.                 nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));       
406.                   
407.                 //Cnumber  
408.                 region = new CellRangeAddress(curRow-1, curRow, 5, 5);  //纵向合并单元格   
409.                 sheet.addMergedRegion(region);  
410.                   
411.                 nCell = nRow.createCell(5);  
412.                 nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);  
413.                 nCell.setCellValue(Double.parseDouble(printMap.get("Cnumber2")));  
414.                 nCell.setCellStyle(poiUtil.numberrv10_BorderThin(wb, defaultFont10));     
415.                   
416.                 //Unit  
417.                 region = new CellRangeAddress(curRow-1, curRow, 6, 6);  //纵向合并单元格   
418.                 sheet.addMergedRegion(region);  
419.                   
420.                 nCell = nRow.createCell(6);  
421.                 nCell.setCellValue(printMap.get("PackingUnit2"));  
422.                 nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));      
423.                   
424.                 //Price  
425.                 region = new CellRangeAddress(curRow-1, curRow, 7, 7);  //纵向合并单元格   
426.                 sheet.addMergedRegion(region);  
427.                   
428.                 nCell = nRow.createCell(7);  
429.                 nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);  
430.                 nCell.setCellValue(Double.parseDouble(printMap.get("Price2")));  
431.                 nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));  
432.                   
433.                   
434.                 //Amount  
435.                 region = new CellRangeAddress(curRow-1, curRow, 8, 8);  //纵向合并单元格   
436.                 sheet.addMergedRegion(region);  
437.                   
438.                 nCell = nRow.createCell(8);  
439.                 if(UtilFuns.isNotEmpty(printMap.get("Cnumber2")) && UtilFuns.isNotEmpty(printMap.get("Price2"))){  
440.                     nCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);  
441.                     nCell.setCellFormula("F"+String.valueOf(curRow)+"*H"+String.valueOf(curRow));  
442.                 }  
443.                 nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));          
444.                   
445.                 curRow++;  
446.                   
447.                 region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格  
448.                 sheet.addMergedRegion(region);  
449.                   
450.                 nRow = sheet.createRow(curRow-1);  
451.                 nRow.setHeightInPoints(24);  
452.                   
453.                 nCell = nRow.createCell(1);  
454.                 nCell.setCellValue(printMap.get("ProductNo2"));  
455.                 nCell.setCellStyle(poiUtil.notecv10_BorderThin(wb, defaultFont10));           
456.                   
457.                 //合并单元格画线  
458.                 for(int j=2;j<9;j++){  
459.                     nCell = nRow.createCell(j);  
460.                     nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));  
461.                 }                 
462.             }  
463.               
464.               
465.             //InputBy  
466.             nRow = sheet.createRow(curRow++);  
467.             nRow.setHeightInPoints(24);  
468.               
469.             nCell = nRow.createCell(1);  
470.             nCell.setCellValue(printMap.get("InputBy"));  
471.             nCell.setCellStyle(poiUtil.bnormalv12(wb,defaultFont12));  
472.               
473.             //CheckBy+inspector  
474.               
475.             nCell = nRow.createCell(4);  
476.             nCell.setCellValue(printMap.get("CheckBy"));  
477.             nCell.setCellStyle(poiUtil.bnormalv12(wb,defaultFont12));  
478.               
479.             //if(contract.getPrintStyle().equals("2") && UtilFuns.isNotEmpty(printMap.get("ProductNo2"))){  
480.                   
481.                 nCell = nRow.createCell(7);  
482.                 nCell.setCellValue("总金额：");  
483.                 nCell.setCellStyle(bcv12(wb));  
484.                   
485.                 //TotalAmount  
486.                 nRow = sheet.createRow(curRow-1);  
487.                 nRow.setHeightInPoints(24);  
488.                 if(UtilFuns.isNotEmpty(printMap.get("Cnumber"))&&UtilFuns.isNotEmpty(printMap.get("Price"))){  
489.                     nCell  = nRow.createCell(8);  
490.                     nCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);  
491.                     nCell.setCellFormula("SUM(I"+String.valueOf(curRow-4)+":I"+String.valueOf(curRow-1)+")");  
492.                     nCell.setCellStyle(poiUtil.moneyrv12_BorderThin(wb,defaultFont12,rmb2Format));        
493.                 }  
494.             //}  
495.               
496.               
497.             //note  
498.             nRow = sheet.createRow(curRow++);  
499.             nRow.setHeightInPoints(21);  
500.               
501.             nCell = nRow.createCell(2);  
502.             nCell.setCellValue(printMap.get("Remark"));  
503.             nCell.setCellStyle(noteStyle(wb));            
504.               
505.             //Request  
506.             region = new CellRangeAddress(curRow, curRow, 1, 8);    //指定合并区域   
507.             sheet.addMergedRegion(region);  
508.               
509.             nRow = sheet.createRow(curRow++);  
510.             float height = poiUtil.getCellAutoHeight(printMap.get("Request"), 12f);     //自动高度  
511.             nRow.setHeightInPoints(height);  
512.               
513.             nCell = nRow.createCell(1);  
514.             nCell.setCellValue(printMap.get("Request"));  
515.             nCell.setCellStyle(requestStyle(wb));  
516.               
517.             //space line  
518.             nRow = sheet.createRow(curRow++);  
519.             nRow.setHeightInPoints(20);  
520.               
521.             //duty  
522.             nRow = sheet.createRow(curRow++);  
523.             nRow.setHeightInPoints(32);  
524.               
525.             nCell = nRow.createCell(1);  
526.             nCell.setCellValue("未按以上要求出货而导致客人索赔，由供方承担。");  
527.             nCell.setCellStyle(dutyStyle(wb));    
528.               
529.             //space line  
530.             nRow = sheet.createRow(curRow++);  
531.             nRow.setHeightInPoints(32);  
532.               
533.             //buyer  
534.             nRow = sheet.createRow(curRow++);  
535.             nRow.setHeightInPoints(25);  
536.               
537.             nCell = nRow.createCell(1);  
538.             nCell.setCellValue("    收购方负责人：");  
539.             nCell.setCellStyle(dutyStyle(wb));                
540.               
541.             //seller  
542.             nCell = nRow.createCell(5);  
543.             nCell.setCellValue("供方负责人：");  
544.             nCell.setCellStyle(dutyStyle(wb));    
545.               
546.             curRow++;  
547.   
548.   
549.         }  
550.   
551.   
552.         ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();          //生成流对象  
553.         wb.write(byteArrayOutputStream);                                                    //将excel写入流  
554.   
555.   
556.         //工具类，封装弹出下载框：        
557.         String outFile = "购销合同.xls";  
558.         DownloadUtil down = new DownloadUtil();  
559.         down.download(byteArrayOutputStream, response, outFile);  
560.   
561.   
562.     }  
563.       
564.     private HSSFCellStyle leftStyle(HSSFWorkbook wb){  
565.         HSSFCellStyle curStyle = wb.createCellStyle();  
566.         curStyle.setWrapText(true);                         //换行     
567.         HSSFFont curFont = wb.createFont();                 //设置字体  
568.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
569.         //fTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //加粗  
570.         curFont.setFontHeightInPoints((short)10);  
571.         curStyle.setFont(curFont);  
572.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
573.           
574.         curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);              //实线右边框  
575.         curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);                //实线右边框  
576.           
577.         return curStyle;  
578.     }    
579.       
580.     private HSSFCellStyle headStyle(HSSFWorkbook wb){  
581.         HSSFCellStyle curStyle = wb.createCellStyle();  
582.         HSSFFont curFont = wb.createFont();                 //设置字体  
583.         curFont.setFontName("Comic Sans MS");  
584.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
585.           
586.         curFont.setItalic(true);  
587.         curFont.setFontHeightInPoints((short)16);  
588.         curStyle.setFont(curFont);  
589.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
590.           
591.         return curStyle;  
592.     }    
593.       
594.     private HSSFCellStyle tipStyle(HSSFWorkbook wb){  
595.         HSSFCellStyle curStyle = wb.createCellStyle();  
596.         HSSFFont curFont = wb.createFont();                 //设置字体  
597.         curFont.setFontName("Georgia");  
598.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
599.           
600.         curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗  
601.         curFont.setFontHeightInPoints((short)28);  
602.         curStyle.setFont(curFont);  
603.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
604.           
605.         return curStyle;  
606.     }    
607.       
608.     private HSSFCellStyle addressStyle(HSSFWorkbook wb){  
609.         HSSFCellStyle curStyle = wb.createCellStyle();  
610.         HSSFFont curFont = wb.createFont();                 //设置字体  
611.         curFont.setFontName("宋体");  
612.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
613.           
614.         //fTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //加粗  
615.         curFont.setFontHeightInPoints((short)10);  
616.         curStyle.setFont(curFont);  
617.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
618.           
619.         return curStyle;  
620.     }    
621.       
622.     private HSSFCellStyle ltdStyle(HSSFWorkbook wb){  
623.         HSSFCellStyle curStyle = wb.createCellStyle();  
624.         HSSFFont curFont = wb.createFont();                 //设置字体  
625.         curFont.setFontName("Times New Roman");  
626.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
627.           
628.         curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗  
629.         curFont.setItalic(true);  
630.         curFont.setFontHeightInPoints((short)16);  
631.         curStyle.setFont(curFont);  
632.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
633.           
634.         return curStyle;  
635.     }     
636.       
637.     private HSSFCellStyle telStyle(HSSFWorkbook wb){  
638.         HSSFCellStyle curStyle = wb.createCellStyle();  
639.         HSSFFont curFont = wb.createFont();                 //设置字体  
640.         curFont.setFontName("宋体");  
641.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
642.           
643.         //fTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //加粗  
644.         curFont.setFontHeightInPoints((short)9);  
645.         curStyle.setFont(curFont);  
646.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
647.           
648.         return curStyle;  
649.     }     
650.       
651.     private HSSFCellStyle titleStyle(HSSFWorkbook wb){  
652.         HSSFCellStyle curStyle = wb.createCellStyle();  
653.         HSSFFont curFont = wb.createFont();                 //设置字体  
654.         curFont.setFontName("黑体");  
655.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
656.           
657.         curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗  
658.         curFont.setFontHeightInPoints((short)18);  
659.         curStyle.setFont(curFont);  
660.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
661.           
662.         return curStyle;  
663.     }     
664.       
665.     private HSSFCellStyle requestStyle(HSSFWorkbook wb){  
666.         HSSFCellStyle curStyle = wb.createCellStyle();  
667.         curStyle.setWrapText(true);                         //换行     
668.         HSSFFont curFont = wb.createFont();                 //设置字体  
669.         curFont.setFontName("宋体");  
670.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
671.           
672.         curFont.setFontHeightInPoints((short)10);  
673.         curStyle.setFont(curFont);  
674.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
675.           
676.         return curStyle;  
677.     }     
678.       
679.     private HSSFCellStyle dutyStyle(HSSFWorkbook wb){  
680.         HSSFCellStyle curStyle = wb.createCellStyle();  
681.         HSSFFont curFont = wb.createFont();                 //设置字体  
682.         curFont.setFontName("黑体");  
683.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
684.           
685.         curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗  
686.         curFont.setFontHeightInPoints((short)16);  
687.         curStyle.setFont(curFont);  
688.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
689.           
690.         return curStyle;  
691.     }     
692.       
693.     private HSSFCellStyle noteStyle(HSSFWorkbook wb){  
694.         HSSFCellStyle curStyle = wb.createCellStyle();  
695.         HSSFFont curFont = wb.createFont();                 //设置字体  
696.         curFont.setFontName("宋体");  
697.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
698.           
699.         curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗  
700.         curFont.setFontHeightInPoints((short)12);  
701.         curStyle.setFont(curFont);  
702.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
703.           
704.         return curStyle;  
705.     }   
706.       
707.     public HSSFCellStyle thStyle(HSSFWorkbook wb){  
708.         HSSFCellStyle curStyle = wb.createCellStyle();  
709.         HSSFFont curFont = wb.createFont();                 //设置字体  
710.         curFont.setFontName("宋体");  
711.         curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗  
712.         curFont.setFontHeightInPoints((short)12);  
713.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置  
714.           
715.         curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);               //实线右边框  
716.         curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);             //实线右边框  
717.         curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);            //实线右边框  
718.         curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);              //实线右边框  
719.           
720.         curStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
721.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
722.           
723.         return curStyle;  
724.     }    
725.       
726.     public HSSFCellStyle bcv12(HSSFWorkbook wb){  
727.         HSSFCellStyle curStyle = wb.createCellStyle();  
728.         HSSFFont curFont = wb.createFont();                     //设置字体  
729.         curFont.setFontName("Times New Roman");  
730.         curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);           //设置中文字体，那必须还要再对单元格进行编码设置  
731.           
732.         curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        //加粗  
733.         curFont.setFontHeightInPoints((short)12);  
734.         curStyle.setFont(curFont);  
735.           
736.         curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);               //实线  
737.         curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);         //粗实线  
738.         curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);            //实线  
739.         curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);              //实线  
740.           
741.         curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);  
742.         curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中  
743.           
744.         return curStyle;  
745.     }         
746.       
747. }  

打印出来的和前面的样板一样，至此我们完成购销合同的打印工作。