SpringMVC结合POI复杂报表打印

最新推荐文章于 2023-11-28 10:10:27 发布

奕起学习

最新推荐文章于 2023-11-28 10:10:27 发布

阅读量747

点赞数 2

分类专栏：工具类 web spring springmvc 框架文章标签： spring mvc 报表 poi

框架同时被 3 个专栏收录

18 篇文章 0 订阅

订阅专栏

web

14 篇文章 0 订阅

订阅专栏

工具类

9 篇文章 0 订阅

订阅专栏

package cn.itcast.jk.controller.cargo.contract;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import cn.itcast.jk.controller.BaseController;
import cn.itcast.jk.domain.Contract;
import cn.itcast.jk.print.ContractPrint;
import cn.itcast.jk.print.ContractPrintTemplate;
import cn.itcast.jk.service.ContractService;
import cn.itcast.jk.vo.ContractVO;

/**
 * @Description:
 * @Author: nutony
 * @Company:    http://java.itcast.cn
 * @CreateDate: 2014-3-26
 */
@Controller
public class ContractController extends BaseController {
    @Autowired
    ContractService contractService;

    @RequestMapping("/cargo/contract/list.action")
    public String list(Contract contract, Model model){
        List<Contract> dataList = contractService.find(contract);
        model.addAttribute("dataList", dataList);

        return "/cargo/contract/jContractList.jsp";
    }

    @RequestMapping("/cargo/contract/tocreate.action")
    public String tocreate(){
        return "/cargo/contract/jContractCreate.jsp";
    }

    @RequestMapping("/cargo/contract/insert.action")
    public String insert(Contract contract){
        contractService.insert(contract);
        return "redirect:/cargo/contract/list.action";
    }

    @RequestMapping("/cargo/contract/toupdate.action")
    public String toupdate(String id, Model model){
        Contract obj = contractService.get(id);
        model.addAttribute("obj", obj);

        return "/cargo/contract/jContractUpdate.jsp";
    }

    @RequestMapping("/cargo/contract/update.action")
    public String update(Contract contract){
        contractService.update(contract);
        return "redirect:/cargo/contract/list.action";
    }

    @RequestMapping("/cargo/contract/deleteBatch.action")
    public String deleteBatch(String id){
        contractService.delete(id.split(","));

        return "redirect:/cargo/contract/list.action";
    }   


    //上报
    @RequestMapping("/cargo/contract/submit.action")
    public String submit(String id){
        this.changeState(1, id.split(","));         //0草稿1上报

        return "redirect:/cargo/contract/list.action";
    }

    //取消
    @RequestMapping("/cargo/contract/cancel.action")
    public String cancel(String id){
        this.changeState(0, id.split(","));         //1启用0停用

        return "redirect:/cargo/contract/list.action";
    }

    //修改状态  1启用0停用
    private void changeState(Integer curSate, String[] ids){
        Map<String,Object> map = new HashMap<String,Object>();
        map.put("state", curSate);
        map.put("ids", ids);

        contractService.changeState(map);
    }

    //转向查看页面
    @RequestMapping("/cargo/contract/toview.action")
    public String toview(String id, Model model){
        ContractVO obj = contractService.view(id);
        model.addAttribute("obj", obj);

        return "/cargo/contract/jContractView.jsp";
    }

    //打印
    @RequestMapping("/cargo/contract/print.action")
    public void print(String id, HttpServletRequest request, HttpServletResponse response) throws Exception{
        ContractPrint cp = new ContractPrint();

        ContractVO obj = contractService.view(id);
        cp.print(obj, request.getSession().getServletContext().getRealPath("/"), response);
    }

    //合同模板打印
    @RequestMapping("/cargo/contract/printTemplate.action")
    public void printTemplate(String id, HttpServletRequest request, HttpServletResponse response) throws Exception{
        ContractPrintTemplate cp = new ContractPrintTemplate();

        ContractVO obj = contractService.view(id);
        cp.print(obj, request.getSession().getServletContext().getRealPath("/"), response);
        //cp.print();
    }
}

复杂报表打印类（无模板）

package cn.itcast.jk.print;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

import cn.itcast.jk.vo.ContractProductVO;
import cn.itcast.jk.vo.ContractVO;
import cn.itcast.util.DownloadUtil;
import cn.itcast.util.UtilFuns;
import cn.itcast.util.file.PoiUtil;

/**
 * @Description: 手工POI写excel文件
 * @Author: nutony
 * @Company:    http://java.itcast.cn
 * @CreateDate: 2014-4-2
 */
public class ContractPrint{



    public void print(ContractVO contract,String path, HttpServletResponse response) throws Exception{
        //相同厂家的信息一起打印
        List<ContractProductVO> oList = contract.getContractProducts();
        UtilFuns utilFuns = new UtilFuns();
        String tempXlsFile = path + "make/xlsprint/tCONTRACT.xls";      //获取模板文件

        //填写每页的内容，之后在循环每页读取打印
        Map<String,String> pageMap = null;
        List<Map> pageList = new ArrayList();           //打印页

        ContractProductVO oProduct = null;
        String stars = "";
        for(int j=0;j<contract.getImportNum();j++){     //重要程度
            stars += "★";
        }

        String oldFactory = "";
        for(int i=0;i<oList.size();i++){
            oProduct = oList.get(i);    //获得货物
            pageMap = new HashMap();    //每页的内容

            pageMap.put("Offeror", "收 购 方：" + contract.getOfferor());
            pageMap.put("Factory", "生产工厂：" + oProduct.getFactory().getFactoryName());
            pageMap.put("ContractNo", "合 同 号：" + contract.getContractNo());
            pageMap.put("Contractor", "联 系 人：" + oProduct.getFactory().getContractor());
            pageMap.put("SigningDate", "签单日期："+UtilFuns.formatDateTimeCN(UtilFuns.dateTimeFormat(contract.getSigningDate())));
            pageMap.put("Phone", "电    话：" + oProduct.getFactory().getPhone());
            pageMap.put("InputBy", "制单：" + contract.getInputBy());
            pageMap.put("CheckBy", "审单："+ utilFuns.fixSpaceStr(contract.getCheckBy(),26)+"验货员："+utilFuns.convertNull(contract.getInspector()));
            pageMap.put("Remark", "  "+contract.getRemark());
            pageMap.put("Request", "  "+contract.getCrequest());

            pageMap.put("ProductImage", oProduct.getProductImage());
            pageMap.put("ProductDesc", oProduct.getProductDesc());
            pageMap.put("Cnumber", String.valueOf(oProduct.getCnumber().doubleValue()));
            if(oProduct.getPackingUnit().equals("PCS")){
                pageMap.put("PackingUnit", "只");
            }else if(oProduct.getPackingUnit().equals("SETS")){
                pageMap.put("PackingUnit", "套");
            }
            pageMap.put("Price", String.valueOf(oProduct.getPrice().doubleValue()));
            pageMap.put("ProductNo", oProduct.getProductNo());

            oldFactory = oProduct.getFactory().getFactoryName();

            if(contract.getPrintStyle().equals("2")){
                i++;    //读取第二个货物信息
                if(i<oList.size()){
                    oProduct = oList.get(i);

                    if(oProduct.getFactory().getFactoryName().equals(oldFactory)){  //厂家不同另起新页打印，除去第一次的比较

                        pageMap.put("ProductImage2", oProduct.getProductImage());
                        pageMap.put("ProductDesc2", oProduct.getProductDesc());
                        pageMap.put("Cnumber2", String.valueOf(oProduct.getCnumber().doubleValue()));
                        if(oProduct.getPackingUnit().equals("PCS")){
                            pageMap.put("PackingUnit2", "只");
                        }else if(oProduct.getPackingUnit().equals("SETS")){
                            pageMap.put("PackingUnit2", "套");
                        }                       
                        pageMap.put("Price2", String.valueOf(oProduct.getPrice().doubleValue()));
                        //pageMap.put("Amount2", String.valueOf(oProduct.getAmount().doubleValue()));           //在excel中金额采用公式，所以无需准备数据
                        pageMap.put("ProductNo2", oProduct.getProductNo());
                    }else{
                        i--;    //tip:list退回
                    }
                }else{
                    pageMap.put("ProductNo2", null);    //后面依据此判断是否有第二个货物
                }
            }

            pageMap.put("ContractDesc", stars+" 货物描述");         //重要程度 + 货物描述

            pageList.add(pageMap);
        }

        int cellHeight = 96;    //一个货物的高度           用户需求，一个货物按192高度打印，后来又嫌难看，打印高度和2款高度一样。
//      if(contract.getPrintStyle().equals("2")){
//          cellHeight = 96;    //两个货物的高度
//      }

        PoiUtil poiUtil = new PoiUtil();
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(tempXlsFile));   //打开excel文件
        HSSFFont defaultFont10 = poiUtil.defaultFont10(wb);     //设置字体
        HSSFFont defaultFont12 = poiUtil.defaultFont12(wb);     //设置字体
        HSSFFont blackFont = poiUtil.blackFont12(wb);           //设置字体
        Short rmb2Format = poiUtil.rmb2Format(wb);              //设置格式
        Short rmb4Format = poiUtil.rmb4Format(wb);              //设置格式


        HSSFSheet sheet = wb.getSheetAt(0);             //选择第一个工作簿
        wb.setSheetName(0, "购销合同");                 //设置工作簿的名称


        //sheet.setDefaultColumnWidth((short) 20);      // 设置每列默认宽度

//      POI分页符有BUG，必须在模板文件中插入一个分页符，然后再此处删除预设的分页符；最后在下面重新设置分页符。
//      sheet.setAutobreaks(false);
//      int iRowBreaks[] = sheet.getRowBreaks();
//      sheet.removeRowBreak(3);
//      sheet.removeRowBreak(4);
//      sheet.removeRowBreak(5);
//      sheet.removeRowBreak(6);

        CellRangeAddress region = null;
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();       //add picture

        HSSFRow nRow = null;
        HSSFCell nCell   = null;
        int curRow = 0;

        //打印每页
        Map<String,String> printMap = null;
        for(int p=0;p<pageList.size();p++){
            printMap = pageList.get(p);

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


            //设置logo图片
            poiUtil.setPicture(wb, patriarch, path+"make/xlsprint/logo.jpg", curRow, 2, curRow+4, 2);

            //header
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(21);

            nCell   = nRow.createCell((3));
            nCell.setCellValue("SHAANXI");
            nCell.setCellStyle(headStyle(wb));

            //header
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(41);

            nCell   = nRow.createCell((3));
            nCell.setCellValue("     JK INTERNATIONAL ");
            nCell.setCellStyle(tipStyle(wb));

            curRow++;

            //header
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(20);

            nCell   = nRow.createCell((1));
            nCell.setCellValue("                 西经济技术开发区西城一路27号无迪大厦19楼");
            nCell.setCellStyle(addressStyle(wb));

            //header
            nCell   = nRow.createCell((6));
            nCell.setCellValue(" CO., LTD.");
            nCell.setCellStyle(ltdStyle(wb));

            //header
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(15);

            nCell   = nRow.createCell((1));
            nCell.setCellValue("                   TEL: 0086-29-86339371  FAX: 0086-29-86303310               E-MAIL: ijackix@glass.cn");
            nCell.setCellStyle(telStyle(wb));

            //line
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(7);

            poiUtil.setLine(wb, patriarch, curRow, 2, curRow, 8);   //draw line

            //header
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(30);

            nCell   = nRow.createCell((4));
            nCell.setCellValue("    购   销   合   同");
            nCell.setCellStyle(titleStyle(wb));

            //Offeror
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(20);

            nCell   = nRow.createCell((1));
            nCell.setCellValue(printMap.get("Offeror"));
            nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));

            //Facotry
            nCell   = nRow.createCell((5));
            nCell.setCellValue(printMap.get("Factory"));
            nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));

            //ContractNo
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(20);

            nCell   = nRow.createCell(1);
            nCell.setCellValue(printMap.get("ContractNo"));
            nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));

            //Contractor
            nCell  = nRow.createCell(5);
            nCell.setCellValue(printMap.get("Contractor"));
            nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));

            //SigningDate
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(20);

            nCell = nRow.createCell(1);
            nCell.setCellValue(printMap.get("SigningDate"));
            nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));

            //Phone
            nCell = nRow.createCell(5);
            nCell.setCellValue(printMap.get("Phone"));
            nCell.setCellStyle(poiUtil.titlev12(wb, blackFont));

            //importNum
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(24);

            region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格 
            sheet.addMergedRegion(region);

            nCell = nRow.createCell(1);
            nCell.setCellValue("产品");
            nCell.setCellStyle(thStyle(wb));        

            nCell = nRow.createCell(2);
            nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));

            nCell = nRow.createCell(3);
            nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));

            nCell = nRow.createCell(4);
            nCell.setCellValue(printMap.get("ContractDesc"));
            nCell.setCellStyle(thStyle(wb));    

            region = new CellRangeAddress(curRow-1, curRow-1, 5, 6);    //纵向合并单元格 
            sheet.addMergedRegion(region);

            nCell = nRow.createCell(5);
            nCell.setCellValue("数量");
            nCell.setCellStyle(thStyle(wb));    

            nCell = nRow.createCell(6);
            nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));         

            nCell = nRow.createCell(7);
            nCell.setCellValue("单价");
            nCell.setCellStyle(thStyle(wb));                        

            nCell = nRow.createCell(8);
            nCell.setCellValue("总金额");
            nCell.setCellStyle(thStyle(wb));                        


            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(96);

            region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格 
            sheet.addMergedRegion(region);

            //插入产品图片
            if(UtilFuns.isNotEmpty(printMap.get("ProductImage"))){
                System.out.println(printMap.get("ProductImage"));
                poiUtil.setPicture(wb, patriarch, path+"ufiles/jquery/"+printMap.get("ProductImage"), curRow-1, 1, curRow, 3);
            }

            nCell = nRow.createCell(2);
            nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));

            nCell = nRow.createCell(3);
            nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));

            //ProductDesc
            region = new CellRangeAddress(curRow-1, curRow, 4, 4);  //纵向合并单元格 
            sheet.addMergedRegion(region);

            nCell = nRow.createCell(4);
            nCell.setCellValue(printMap.get("ProductDesc"));
            nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));     

            //Cnumber
            region = new CellRangeAddress(curRow-1, curRow, 5, 5);  //纵向合并单元格 
            sheet.addMergedRegion(region);

            nCell = nRow.createCell(5);
            nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            nCell.setCellValue(Double.parseDouble(printMap.get("Cnumber")));
            nCell.setCellStyle(poiUtil.numberrv10_BorderThin(wb, defaultFont10));   

            //Unit
            region = new CellRangeAddress(curRow-1, curRow, 6, 6);  //纵向合并单元格 
            sheet.addMergedRegion(region);

            nCell = nRow.createCell(6);
            nCell.setCellValue(printMap.get("PackingUnit"));
            nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));    

            //Price
            region = new CellRangeAddress(curRow-1, curRow, 7, 7);  //纵向合并单元格 
            sheet.addMergedRegion(region);

            nCell = nRow.createCell(7);
            nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            nCell.setCellValue(Double.parseDouble(printMap.get("Price")));
            nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));


            //Amount
            region = new CellRangeAddress(curRow-1, curRow, 8, 8);  //纵向合并单元格 
            sheet.addMergedRegion(region);

            nCell = nRow.createCell(8);
            if(UtilFuns.isNotEmpty(printMap.get("Cnumber")) && UtilFuns.isNotEmpty(printMap.get("Price"))){
                nCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                nCell.setCellFormula("F"+String.valueOf(curRow)+"*H"+String.valueOf(curRow));
            }
            nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));            

            curRow++;

            region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格 
            sheet.addMergedRegion(region);

            //ProductNo
            nRow = sheet.createRow(curRow-1);
            nRow.setHeightInPoints(24);

            nCell = nRow.createCell(1);
            nCell.setCellValue(printMap.get("ProductNo"));
            nCell.setCellStyle(poiUtil.notecv10_BorderThin(wb, defaultFont10));

            for(int j=2;j<9;j++){
                nCell = nRow.createCell(j);
                nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));
            }



            if(contract.getPrintStyle().equals("2") && UtilFuns.isNotEmpty(printMap.get("ProductNo2"))){
                nRow = sheet.createRow(curRow++);
                nRow.setHeightInPoints(96);

                region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格 
                sheet.addMergedRegion(region);

                //插入产品图片
                if(UtilFuns.isNotEmpty(printMap.get("ProductImage2"))){
                    System.out.println(printMap.get("ProductImage2"));
                    poiUtil.setPicture(wb, patriarch, path+"ufiles/jquery/"+printMap.get("ProductImage2"), curRow-1, 1, curRow, 3);
                }

                //ProductDesc
                region = new CellRangeAddress(curRow-1, curRow, 4, 4);  //纵向合并单元格 
                sheet.addMergedRegion(region);

                nCell = nRow.createCell(4);
                nCell.setCellValue(printMap.get("ProductDesc2"));
                nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));     

                //Cnumber
                region = new CellRangeAddress(curRow-1, curRow, 5, 5);  //纵向合并单元格 
                sheet.addMergedRegion(region);

                nCell = nRow.createCell(5);
                nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                nCell.setCellValue(Double.parseDouble(printMap.get("Cnumber2")));
                nCell.setCellStyle(poiUtil.numberrv10_BorderThin(wb, defaultFont10));   

                //Unit
                region = new CellRangeAddress(curRow-1, curRow, 6, 6);  //纵向合并单元格 
                sheet.addMergedRegion(region);

                nCell = nRow.createCell(6);
                nCell.setCellValue(printMap.get("PackingUnit2"));
                nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));    

                //Price
                region = new CellRangeAddress(curRow-1, curRow, 7, 7);  //纵向合并单元格 
                sheet.addMergedRegion(region);

                nCell = nRow.createCell(7);
                nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                nCell.setCellValue(Double.parseDouble(printMap.get("Price2")));
                nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));


                //Amount
                region = new CellRangeAddress(curRow-1, curRow, 8, 8);  //纵向合并单元格 
                sheet.addMergedRegion(region);

                nCell = nRow.createCell(8);
                if(UtilFuns.isNotEmpty(printMap.get("Cnumber2")) && UtilFuns.isNotEmpty(printMap.get("Price2"))){
                    nCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    nCell.setCellFormula("F"+String.valueOf(curRow)+"*H"+String.valueOf(curRow));
                }
                nCell.setCellStyle(poiUtil.moneyrv10_BorderThin(wb, defaultFont10, rmb4Format));        

                curRow++;

                region = new CellRangeAddress(curRow-1, curRow-1, 1, 3);    //纵向合并单元格
                sheet.addMergedRegion(region);

                nRow = sheet.createRow(curRow-1);
                nRow.setHeightInPoints(24);

                nCell = nRow.createCell(1);
                nCell.setCellValue(printMap.get("ProductNo2"));
                nCell.setCellStyle(poiUtil.notecv10_BorderThin(wb, defaultFont10));         

                //合并单元格画线
                for(int j=2;j<9;j++){
                    nCell = nRow.createCell(j);
                    nCell.setCellStyle(poiUtil.notehv10_BorderThin(wb, defaultFont10));
                }               
            }


            //InputBy
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(24);

            nCell = nRow.createCell(1);
            nCell.setCellValue(printMap.get("InputBy"));
            nCell.setCellStyle(poiUtil.bnormalv12(wb,defaultFont12));

            //CheckBy+inspector

            nCell = nRow.createCell(4);
            nCell.setCellValue(printMap.get("CheckBy"));
            nCell.setCellStyle(poiUtil.bnormalv12(wb,defaultFont12));

            //if(contract.getPrintStyle().equals("2") && UtilFuns.isNotEmpty(printMap.get("ProductNo2"))){

                nCell = nRow.createCell(7);
                nCell.setCellValue("总金额：");
                nCell.setCellStyle(bcv12(wb));

                //TotalAmount
                nRow = sheet.createRow(curRow-1);
                nRow.setHeightInPoints(24);
                if(UtilFuns.isNotEmpty(printMap.get("Cnumber"))&&UtilFuns.isNotEmpty(printMap.get("Price"))){
                    nCell  = nRow.createCell(8);
                    nCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                    nCell.setCellFormula("SUM(I"+String.valueOf(curRow-4)+":I"+String.valueOf(curRow-1)+")");
                    nCell.setCellStyle(poiUtil.moneyrv12_BorderThin(wb,defaultFont12,rmb2Format));      
                }
            //}


            //note
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(21);

            nCell = nRow.createCell(2);
            nCell.setCellValue(printMap.get("Remark"));
            nCell.setCellStyle(noteStyle(wb));          

            //Request
            region = new CellRangeAddress(curRow, curRow, 1, 8);    //指定合并区域 
            sheet.addMergedRegion(region);

            nRow = sheet.createRow(curRow++);
            float height = poiUtil.getCellAutoHeight(printMap.get("Request"), 12f);     //自动高度
            nRow.setHeightInPoints(height);

            nCell = nRow.createCell(1);
            nCell.setCellValue(printMap.get("Request"));
            nCell.setCellStyle(requestStyle(wb));

            //space line
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(20);

            //duty
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(32);

            nCell = nRow.createCell(1);
            nCell.setCellValue("未按以上要求出货而导致客人索赔，由供方承担。");
            nCell.setCellStyle(dutyStyle(wb));  

            //space line
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(32);

            //buyer
            nRow = sheet.createRow(curRow++);
            nRow.setHeightInPoints(25);

            nCell = nRow.createCell(1);
            nCell.setCellValue("    收购方负责人：");
            nCell.setCellStyle(dutyStyle(wb));              

            //seller
            nCell = nRow.createCell(5);
            nCell.setCellValue("供方负责人：");
            nCell.setCellStyle(dutyStyle(wb));  

            curRow++;

        }

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();          //生成流对象
        wb.write(byteArrayOutputStream);                                                    //将excel写入流

        //工具类，封装弹出下载框：      
        String outFile = "购销合同.xls";
        DownloadUtil down = new DownloadUtil();
        down.download(byteArrayOutputStream, response, outFile);

    }

    private HSSFCellStyle leftStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true);                         //换行   
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置
        //fTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //加粗
        curFont.setFontHeightInPoints((short)10);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);              //实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);                //实线右边框

        return curStyle;
    }  

    private HSSFCellStyle headStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("Comic Sans MS");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        curFont.setItalic(true);
        curFont.setFontHeightInPoints((short)16);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }  

    private HSSFCellStyle tipStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("Georgia");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
        curFont.setFontHeightInPoints((short)28);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }  

    private HSSFCellStyle addressStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("宋体");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        //fTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //加粗
        curFont.setFontHeightInPoints((short)10);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }  

    private HSSFCellStyle ltdStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("Times New Roman");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
        curFont.setItalic(true);
        curFont.setFontHeightInPoints((short)16);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }   

    private HSSFCellStyle telStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("宋体");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        //fTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);   //加粗
        curFont.setFontHeightInPoints((short)9);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }   

    private HSSFCellStyle titleStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("黑体");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
        curFont.setFontHeightInPoints((short)18);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }   

    private HSSFCellStyle requestStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true);                         //换行   
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("宋体");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        curFont.setFontHeightInPoints((short)10);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }   

    private HSSFCellStyle dutyStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("黑体");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
        curFont.setFontHeightInPoints((short)16);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }   

    private HSSFCellStyle noteStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("宋体");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
        curFont.setFontHeightInPoints((short)12);
        curStyle.setFont(curFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    } 

    public HSSFCellStyle thStyle(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                 //设置字体
        curFont.setFontName("宋体");
        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);    //加粗
        curFont.setFontHeightInPoints((short)12);
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);       //设置中文字体，那必须还要再对单元格进行编码设置

        curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);               //实线右边框
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);             //实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);            //实线右边框
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);              //实线右边框

        curStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }  

    public HSSFCellStyle bcv12(HSSFWorkbook wb){
        HSSFCellStyle curStyle = wb.createCellStyle();
        HSSFFont curFont = wb.createFont();                     //设置字体
        curFont.setFontName("Times New Roman");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET);           //设置中文字体，那必须还要再对单元格进行编码设置

        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);        //加粗
        curFont.setFontHeightInPoints((short)12);
        curStyle.setFont(curFont);

        curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);               //实线
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);         //粗实线
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);            //实线
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);              //实线

        curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }       

}

复杂报表打印类（有模板）

package cn.itcast.jk.print;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;

import cn.itcast.jk.vo.ContractProductVO;
import cn.itcast.jk.vo.ContractVO;
import cn.itcast.util.DownloadUtil;
import cn.itcast.util.UtilFuns;

/**
 * @Description: 根据模板文件打印，复制sheet，然后写单元格值；最终删除模板sheet，不留痕迹；这样样式工作可视化，无需代码，同时也加快的展现效率；
 * @Author: nutony
 * @Company:    http://java.itcast.cn
 * @CreateDate: 2014-4-2
 */
public class ContractPrintTemplate {

    public void print(ContractVO contract, String path, HttpServletResponse response) throws ParseException, FileNotFoundException, IOException{
        /*
         * 1.获取数据
         * 2.封装每页数据
         * 3.打印
         */

        UtilFuns utilFuns = new UtilFuns();
        List<Map> pageList = new ArrayList<Map>();
        Map<String,String> pageMap = null;

        String _stars = "";
        for(int i=0;i<contract.getImportNum();i++){
            _stars += "★";
        }

        List<ContractProductVO> cpList = contract.getContractProducts();
        ContractProductVO cp = null;
        for(int i=0;i<cpList.size();i++){
            pageMap = new HashMap<String,String>();
            cp = cpList.get(i);

            pageMap.put("Offeror", "收 购 方："+contract.getOfferor());
            pageMap.put("ContractNo", "合 同 号："+contract.getContractNo());
            pageMap.put("SigningDate", "签单日期："+UtilFuns.formatDateTimeCN(UtilFuns.dateTimeFormat(contract.getSigningDate())));

            pageMap.put("Factory", "生产工厂："+cp.getFactory().getFullName());
            pageMap.put("Contractor", "联 系 人："+cp.getFactory().getContractor());
            pageMap.put("Phone", "电    话："+cp.getFactory().getPhone());

            pageMap.put("InputBy", "制单："+contract.getInputBy());
            pageMap.put("CheckBy", "审单："+utilFuns.fixSpaceStr(contract.getCheckBy(), 26) + "验货员："+contract.getInspector());

            pageMap.put("Crequest", contract.getCrequest());



            pageMap.put("ProductDescTitle", _stars + " 货物描述");

            pageMap.put("ProductImage", cp.getProductImage());
            pageMap.put("ProductDesc", cp.getProductDesc());
            pageMap.put("Cnumber", cp.getCnumber().toString());
            if(cp.getPackingUnit().equals("PCS")){
                pageMap.put("PackingUnit", "只");
            }else if(cp.getPackingUnit().equals("SETS")){
                pageMap.put("PackingUnit", "套");
            }
            pageMap.put("Price", cp.getPrice().toString());
            pageMap.put("ProductNo", cp.getProductNo());

            String fullName = cp.getFactory().getFullName();
            if(contract.getPrintStyle().equals("2")){       //按两款货物打印，才做
                //处理第二款货物
                i++;
                if(i<cpList.size()){            //判断第二款货物是否有
                    cp = cpList.get(i);
                    if(cp.getFactory().getFullName().equals(fullName)){
                        pageMap.put("ProductImage2", cp.getProductImage());
                        pageMap.put("ProductDesc2", cp.getProductDesc());
                        pageMap.put("Cnumber2", cp.getCnumber().toString());
                        if(cp.getPackingUnit().equals("PCS")){
                            pageMap.put("PackingUnit2", "只");
                        }else if(cp.getPackingUnit().equals("SETS")){
                            pageMap.put("PackingUnit2", "套");
                        }
                        pageMap.put("Price2", cp.getPrice().toString());
                        pageMap.put("ProductNo2", cp.getProductNo());
                    }
                }else{
                    i--;                        //如果第二款货物厂家不同，则必须新起一页
                }
            }

            pageList.add(pageMap);              //存储一页数据
        }

        /*
         * 打开模板，复制sheet，另存
         */
        Workbook wb = new HSSFWorkbook(new FileInputStream(new File("c:\\tCONTRACTVO.xls")));
        for(int i=0;i<pageList.size();i++){
            wb.cloneSheet(0);                               //复制工作簿
            wb.setSheetName(i+1, "C"+(i+1)+"");     //设置工作簿名称
        }


        //设置相同内容
        for(int i=0;i<pageList.size();i++){
            int rowNo = 6;
            int colNo = 0;
            Row nRow = null;
            Cell nCell = null;
            Map<String,String> printMap = pageList.get(i);

            Sheet sheet = wb.getSheetAt(i+1);                       //定位到当前工作表
            sheet.setForceFormulaRecalculation(true);               //强制公式自动计算，利用模板时，模板中的公式不会因值发生变化而自动计算。

            nRow = sheet.getRow(rowNo++);
            nCell = nRow.getCell(1);
            nCell.setCellValue(printMap.get("Offeror"));
            nCell = nRow.getCell(5);
            nCell.setCellValue(printMap.get("Factory"));

            nRow = sheet.getRow(rowNo++);
            nCell = nRow.getCell(1);
            nCell.setCellValue(printMap.get("ContractNo"));
            nCell = nRow.getCell(5);
            nCell.setCellValue(printMap.get("Contractor"));

            nRow = sheet.getRow(rowNo++);
            nCell = nRow.getCell(1);
            nCell.setCellValue(printMap.get("SigningDate"));
            nCell = nRow.getCell(5);
            nCell.setCellValue(printMap.get("Phone"));

            nRow = sheet.getRow(rowNo++);
            nCell = nRow.getCell(4);
            nCell.setCellValue(printMap.get("ProductDescTitle"));

            nRow = sheet.getRow(rowNo++);
            nCell = nRow.getCell(1);
            nCell.setCellValue(printMap.get("ProductImage"));
            if(UtilFuns.isNotEmpty(printMap.get("ProductImage"))){
                this.setPicture(path+"/ufiles/jquery/"+printMap.get("ProductImage"), sheet, rowNo-1, 1, rowNo, 3);      //插入产品图片
            }           

            nCell = nRow.getCell(4);
            nCell.setCellValue(printMap.get("ProductDesc"));
            nCell = nRow.getCell(5);
            nCell.setCellValue(Integer.parseInt(printMap.get("Cnumber")));
            nCell = nRow.getCell(6);
            nCell.setCellValue(printMap.get("PackingUnit"));
            nCell = nRow.getCell(7);
            nCell.setCellValue(Double.parseDouble(printMap.get("Price")));

            nRow = sheet.getRow(rowNo++);
            nCell = nRow.getCell(1);
            nCell.setCellValue(printMap.get("ProductNo"));


            if(printMap.get("ProductNo2")!=null){           //第二款货不存在
                nRow = sheet.getRow(rowNo++);
                nCell = nRow.getCell(1);
                nCell.setCellValue(printMap.get("ProductImage2"));
                if(UtilFuns.isNotEmpty(printMap.get("ProductImage2"))){
                    this.setPicture(path+"/ufiles/jquery/"+printMap.get("ProductImage2"), sheet, rowNo-1, 1, rowNo, 3);     //插入产品图片
                }

                nCell = nRow.getCell(4);
                nCell.setCellValue(printMap.get("ProductDesc2"));
                nCell = nRow.getCell(5);
                nCell.setCellValue(printMap.get("Cnumber2"));
                nCell = nRow.getCell(6);
                nCell.setCellValue(printMap.get("PackingUnit2"));
                nCell = nRow.getCell(7);
                nCell.setCellValue(printMap.get("Price2"));

                nRow = sheet.getRow(rowNo++);
                nCell = nRow.getCell(1);
                nCell.setCellValue(printMap.get("ProductNo2"));

            }else{              //没有第二款货物时空着
                rowNo++;
                rowNo++;
            }

            nRow = sheet.getRow(rowNo++);
            nCell = nRow.getCell(1);
            nCell.setCellValue(printMap.get("InputBy"));            
            nCell = nRow.getCell(4);
            nCell.setCellValue(printMap.get("CheckBy"));    

            rowNo++;
            nRow = sheet.getRow(rowNo++);
            nCell = nRow.getCell(1);
            nCell.setCellValue("  "+printMap.get("Crequest"));
        }

        wb.removeSheetAt(0);                    //删除模板sheet

        DownloadUtil du = new DownloadUtil();
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wb.write(bos);
        bos.close();
        du.download(bos, response, "购销合同.xls");
        wb.setFirstVisibleTab(1);
    }

    //处理图片，excel中图片是单独对象存放
    public void setPicture(String pic, Sheet sheet, int startRow, int startCol, int stopRow, int stopCol) throws IOException{
        File imageFile = new File(pic);
        if(imageFile.exists()){
            InputStream is = new FileInputStream(new File(pic));
            byte[] bytes = IOUtils.toByteArray(is);
            int pictureIdx = sheet.getWorkbook().addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);     //扩展名可为.jpg/.jpeg/.png
            is.close();

            Drawing drawing = sheet.createDrawingPatriarch();   // Create the drawing patriarch.  This is the top level container for all shapes.
            //前面四个参数是图片偏移量
            ClientAnchor anchor = new HSSFClientAnchor(20, 1, 1020, 0, (short)startCol, startRow, (short)stopCol, stopRow); //add a picture shape
            anchor.setRow1(startRow);                           //set position corner of the picture        
            anchor.setCol1(startCol);
            anchor.setRow2(stopRow);
            anchor.setCol2(stopCol);

            drawing.createPicture(anchor, pictureIdx);
        }
    }


    public void print() throws ParseException, FileNotFoundException, IOException{

        /*
         * 打开模板，复制sheet，另存
         */
        Workbook wb = new HSSFWorkbook(new FileInputStream(new File("c:\\tCONTRACTVO.xls")));
        for(int i=0;i<2;i++){
            wb.cloneSheet(0);                               //复制工作簿
            wb.setSheetName(i+1, "Sheet("+(i+1)+")");       //设置工作簿名称
        }

        //设置相同内容
        for(int i=1;i<wb.getNumberOfSheets();i++){
            Row nRow = null;
            Cell nCell = null;

            Sheet sheet = wb.getSheetAt(i);                     //定位到当前工作表
            System.out.println(sheet.getLastRowNum());

            nRow = sheet.getRow(6);
            nCell = nRow.getCell(1);
            nCell.setCellValue("Offeror");

            System.out.println("=================================="+i);
        }

        wb.write(new FileOutputStream("c:\\y.xls"));
    }   
}

复杂报表打印类使用到的一些工具类

package cn.itcast.util.file;

import java.awt.image.BufferedImage;
import java.io.*;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;

import javax.imageio.ImageIO;

public class PoiUtil {
    private static final String ENFONT = "Times New Roman";

    public HSSFFont defaultFont10(HSSFWorkbook wb) {
        HSSFFont curFont = wb.createFont(); // 设置字体
        curFont.setFontName(this.ENFONT);
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
        curFont.setFontHeightInPoints((short) 10);

        return curFont;
    }

    public HSSFFont defaultFont10Blod(HSSFWorkbook wb) {
        HSSFFont curFont = wb.createFont(); // 设置字体
        curFont.setFontName(this.ENFONT);
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
        curFont.setFontHeightInPoints((short) 10);

        return curFont;
    }

    public HSSFFont defaultFont12(HSSFWorkbook wb) {
        HSSFFont curFont = wb.createFont(); // 设置字体
        curFont.setFontName(this.ENFONT);
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
        curFont.setFontHeightInPoints((short) 12);

        return curFont;
    }

    public HSSFFont blackFont12(HSSFWorkbook wb) {
        HSSFFont theFont = wb.createFont(); // 设置字体
        theFont.setFontName("黑体");
        theFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
        theFont.setFontHeightInPoints((short) 12);

        return theFont;
    }

    public HSSFFont songBoldFont16(HSSFWorkbook wb) {
        HSSFFont curFont = wb.createFont(); // 设置字体
        curFont.setFontName("宋体");
        curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); // 设置中文字体，那必须还要再对单元格进行编码设置
        curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
        curFont.setFontHeightInPoints((short) 16);

        return curFont;
    }

    public short money1Format(HSSFWorkbook wb) {
        HSSFDataFormat format = wb.createDataFormat();
        return format.getFormat("#,###,###.0"); // 设置格式
    }

    public short money2Format(HSSFWorkbook wb) {
        HSSFDataFormat format = wb.createDataFormat();
        return format.getFormat("#,###,###.00"); // 设置格式
    }

    public short rmb2Format(HSSFWorkbook wb) {
        HSSFDataFormat format = wb.createDataFormat();
        return format.getFormat("\"￥\"#,###,###.00"); // 设置格式
    }

    public short rmb4Format(HSSFWorkbook wb) {
        HSSFDataFormat format = wb.createDataFormat();
        return format.getFormat("\"￥\"#,###,##0.00"); // 设置格式
    }

    public short datevENFormat(HSSFWorkbook wb) {
        HSSFDataFormat format = wb.createDataFormat();
        return format.getBuiltinFormat("m/d/yy"); // 设置格式
    }

    // 指定图片类型为jpg
    public void setPicture(HSSFWorkbook wb, HSSFPatriarch patriarch, String pic, int iRow, int iCol) throws IOException {
        // 判断文件是否存在
        File imgFile = new File(pic);
        if (imgFile.exists()) {
            // 图片处理
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            BufferedImage bufferImg = ImageIO.read(imgFile);
            ImageIO.write(bufferImg, "jpg", byteArrayOut);

            HSSFClientAnchor anchor = new HSSFClientAnchor(190, 0, 1000, 0, (short) (iCol), iRow - 1, (short) (iCol + 1), iRow);
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        }
    }

    // 指定图片类型为jpg
    public void setPicture(HSSFWorkbook wb, HSSFPatriarch patriarch, String pic, int iRowStart, int iColStart, int iRowStop, int iColStop) throws IOException {
        // 判断文件是否存在
        File imgFile = new File(pic);
        if (imgFile.exists()) {
            // 图片处理
            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            BufferedImage bufferImg = ImageIO.read(imgFile);
            ImageIO.write(bufferImg, "jpg", byteArrayOut);

            // 左,上(0-255),右(0-1023),下
            HSSFClientAnchor anchor = new HSSFClientAnchor(20, 1, 1018, 0, (short) (iColStart), iRowStart, (short) (iColStop), iRowStop);
            patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
        }
    }

    // 画线
    public void setLine(HSSFWorkbook wb, HSSFPatriarch patriarch, int iRowStart, int iColStart, int iRowStop, int iColStop) {
        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 350, 0, (short) (iColStart), iRowStart, (short) (iColStop), iRowStop);
        HSSFSimpleShape lineShape = patriarch.createSimpleShape(anchor);
        lineShape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
    }

    // 计算行高度，实现行自动适应高度 defaultRowHeight = 12.00f; //每一行的高度指定   目前只实现根据回车多行来判断，不能根据单元格宽度自动回行来判断
    public float getCellAutoHeight(String str, float defaultRowHeight) {
        if (str == null) {
            return defaultRowHeight;
        }
        float height = 0.00f;
        int n = 0;
        if (str.endsWith("\n")) {
            n = str.split("\n").length; // 回车个数
        } else {
            n = str.split("\n").length + 1; // 回车个数
        }
        height = defaultRowHeight * n;

        return height; // 计算
    }

    //计算字符串高度
    public float getregex(String charStr) {
        if (charStr.equals(" ")) {
            return 0.5f;
        }
        if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
            return 0.5f;
        }
        // 判断是否为全角
        if (Pattern.compile("^[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {
            return 1.00f;
        }
        if (Pattern.compile("^x00-xff]+$").matcher(charStr).matches()) {
            return 1.00f;
        }
        return 0.5f;
    }

    public HSSFCellStyle titlev12(HSSFWorkbook wb, HSSFFont blackFont) {
        HSSFCellStyle curStyle = wb.createCellStyle();

        curStyle.setFont(blackFont);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle nobox(HSSFWorkbook wb) {
        HSSFCellStyle curStyle = wb.createCellStyle();

        curStyle.setBorderTop(HSSFCellStyle.BORDER_NONE); // 实线右边框
        curStyle.setBorderRight(HSSFCellStyle.BORDER_NONE); // 实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_NONE); // 实线右边框
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_NONE); // 实线右边框

        curStyle.setTopBorderColor((short) 0);

        return curStyle;
    }

    // 实现打印时为白框，目的就是实现涂去上行的下边框线 by tony 20110709
    public HSSFCellStyle whiteBox(HSSFWorkbook wb) {
        HSSFCellStyle curStyle = wb.createCellStyle();

        curStyle.setTopBorderColor(HSSFColor.WHITE.index);
        curStyle.setRightBorderColor(HSSFColor.WHITE.index);
        curStyle.setBottomBorderColor(HSSFColor.WHITE.index);
        curStyle.setLeftBorderColor(HSSFColor.WHITE.index);

        curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 实线右边框

        return curStyle;
    }

    public HSSFCellStyle normalv12(HSSFWorkbook wb, HSSFFont defaultFont12) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setFont(defaultFont12);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle normalv10(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setFont(defaultFont10);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle bnormalv12(HSSFWorkbook wb, HSSFFont defaultFont12) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setFont(defaultFont12);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle moneyrv10_BorderThin(HSSFWorkbook wb, HSSFFont defaultFont10, short rmb4Format) {
        HSSFCellStyle curStyle = wb.createCellStyle();

        curStyle.setFont(defaultFont10);
        curStyle.setDataFormat(rmb4Format);

        curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 实线右边框

        return curStyle;
    }

    public HSSFCellStyle numberrv10_BorderThin(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setFont(defaultFont10);

        curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 实线右边框

        return curStyle;
    }

    public HSSFCellStyle moneyrv12_BorderThin(HSSFWorkbook wb, HSSFFont defaultFont12, short rmb2Format) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setFont(defaultFont12);
        curStyle.setDataFormat(rmb2Format);

        curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 实线右边框

        return curStyle;
    }

    public HSSFCellStyle money1(HSSFWorkbook wb, HSSFFont defaultFont10, short money1Format) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setFont(defaultFont10);
        curStyle.setDataFormat(money1Format);

        curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle money2(HSSFWorkbook wb, HSSFFont defaultFont10, short money2Format) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setFont(defaultFont10);
        curStyle.setDataFormat(money2Format);

        curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle datevEN(HSSFWorkbook wb, HSSFFont defaultFont10, short datevENFormat) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setFont(defaultFont10);
        curStyle.setDataFormat(datevENFormat);

        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle notet10(HSSFWorkbook wb) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true); // 换行

        curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 实线右边框

        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle notevt10(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true); // 换行
        curStyle.setFont(defaultFont10);

        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle noterv10(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true); // 换行
        curStyle.setFont(defaultFont10);

        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle noterv10NoWrap(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(false);                        //换行   
        curStyle.setFont(defaultFont10);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);       //单元格垂直居中

        return curStyle;
    }   

    public HSSFCellStyle notehv10(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true);                                     // 换行
        curStyle.setFont(defaultFont10);

        curStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);   // 单元格垂直居中

        return curStyle;
    }

    // 横向居左，垂直居中
    public HSSFCellStyle notehlv10(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true); // 换行
        curStyle.setFont(defaultFont10);

        curStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        return curStyle;
    }

    // 横向居右，垂直居中
    public HSSFCellStyle notehrv10(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true);                                     // 换行
        curStyle.setFont(defaultFont10);

        curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);   // 单元格垂直居中

        return curStyle;
    }

    public HSSFCellStyle notehv10_BorderThin(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true); // 换行

        curStyle.setFont(defaultFont10);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 单元格垂直居中

        curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 实线右边框
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 实线右边框

        return curStyle;
    }

    public HSSFCellStyle notecv10_BorderThin(HSSFWorkbook wb, HSSFFont defaultFont10) {
        HSSFCellStyle curStyle = wb.createCellStyle();
        curStyle.setWrapText(true);                                     // 换行
        curStyle.setFont(defaultFont10);
        curStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);   // 单元格垂直居中

        curStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);               // 实线右边框
        curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);             // 实线右边框
        curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);            // 实线右边框
        curStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);              // 实线右边框

        return curStyle;
    }

}