Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook write

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. write

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook write.

Prototype

private void write(POIFSFileSystem fs) throws IOException

Source Link

Document

Writes the workbook out to a brand new, empty POIFS

Usage

From source file:com.selfsoft.business.service.impl.TbFixEntrustServiceImpl.java

public void printTbFixEntrustTemplate(OutputStream os, String tpl, Long tbFixEntrustId) {

    /*/* w w w.  j ava2 s  .co m*/
     * Workbook wb = null;
     * 
     * WritableWorkbook wwb = null;
     * 
     * WritableSheet ws = null;
     * 
     * try {
     * 
     * wb = Workbook.getWorkbook(this.getClass().getResourceAsStream(tpl));
     * 
     * wwb = Workbook.createWorkbook(os, wb);
     * 
     * ws = wwb.getSheet(0);
     * 
     * WritableCellFormat wcf = new WritableCellFormat();
     * 
     * wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
     * 
     * wcf.setAlignment(Alignment.CENTRE);
     * 
     * wcf.setWrap(true);
     * 
     * TbFixEntrust tbFixEntrust = this.findById(tbFixEntrustId);
     * 
     * TbCustomer tbCustomer =
     * tbCustomerService.findById(tbFixEntrust.getTbCustomer().getId());
     * 
     * TbCarInfo tbCarInfo =
     * tbCarInfoService.findById(tbFixEntrust.getTbCarInfo().getId());
     * 
     * Label label = null;
     * 
     * label = new Label(0, 10, tbFixEntrust.getEntrustCode(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(8, 10, tbCarInfo.getPurchaseDate()== null ? "" :
     * CommonMethod.parseDateToString(tbCarInfo.getPurchaseDate(),
     * "yyyy-MM-dd"), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(14, 10, tbFixEntrust.getTmUser().getUserRealName(),
     * wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(20, 10, tbCarInfo.getLicenseCode(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(27, 10,
     * tbCarInfo.getTmCarModelType().getModelName(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(32, 10, tbFixEntrust.getEnterStationKilo() == null
     * ? "" : tbFixEntrust.getEnterStationKilo().toString(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(41, 10, tbCarInfo.getColor() == null ? "" :
     * tbCarInfo.getColor().toString(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(45, 10, tbFixEntrust.getFixDate() == null ? "" :
     * CommonMethod.parseDateToString(tbFixEntrust.getFixDate(),
     * "yyyy-MM-dd HH:mm:ss"), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(0, 15, tbCustomer.getCustomerName() == null ? "" :
     * tbCustomer.getCustomerName(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(8, 15, tbCustomer.getTelephone() == null ? "" :
     * tbCustomer.getTelephone(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(14, 15, tbCustomer.getPhone() == null ? "" :
     * tbCustomer.getPhone(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(20, 15, tbCarInfo.getChassisCode() == null ? "" :
     * tbCarInfo.getChassisCode(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(32, 15, tbCarInfo.getEngineCode() == null ? "" :
     * tbCarInfo.getEngineCode(),wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(45, 15, tbFixEntrust.getEstimateDate() == null ? ""
     * : CommonMethod.parseDateToString(tbFixEntrust.getEstimateDate(),
     * "yyyy-MM-dd HH:mm:ss"),wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(8, 18, tbCustomer.getCustomerName() == null ? "" :
     * tbCustomer.getCustomerName(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(0, 22, tbCustomer.getAddress() == null ? "" :
     * tbCustomer.getAddress(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(8, 25, tbCustomer.getZipCode() == null ? "" :
     * tbCustomer.getZipCode(), wcf);
     * 
     * ws.addCell(label);
     * 
     * label = new Label(20, 20, (tbFixEntrust.getWrongDescribe() ==
     * null?"":tbFixEntrust.getWrongDescribe()) +";" +
     * (tbFixEntrust.getBeforeFixState
     * ()==null?"":tbFixEntrust.getBeforeFixState()) + ";" +
     * (tbFixEntrust.getCheckResult
     * ()==null?"":tbFixEntrust.getCheckResult()) + ";" +
     * (tbFixEntrust.getRemark()==null?"":tbFixEntrust.getRemark()), wcf);
     * 
     * ws.addCell(label);
     * 
     * wwb.write();
     * 
     * } catch (BiffException e) {
     * 
     * e.printStackTrace(); } catch (IOException e) {
     * 
     * e.printStackTrace(); } catch (RowsExceededException e) { // TODO
     * Auto-generated catch block e.printStackTrace(); } catch
     * (WriteException e) { // TODO Auto-generated catch block
     * e.printStackTrace(); } finally {
     * 
     * if (null != wb) {
     * 
     * wb.close();
     * 
     * }
     * 
     * if (null != wwb) {
     * 
     * try { wwb.close(); } catch (WriteException e) {
     * 
     * e.printStackTrace(); } catch (IOException e) {
     * 
     * e.printStackTrace(); } }
     * 
     * }
     */

    try {

        TbFixEntrust tbFixEntrust = this.findById(tbFixEntrustId);

        TbCustomer tbCustomer = tbCustomerService.findById(tbFixEntrust.getTbCustomer().getId());

        TbCarInfo tbCarInfo = tbCarInfoService.findById(tbFixEntrust.getTbCarInfo().getId());

        List<TbFixEntrustContent> tbFixEntrustContentList = tbFixEntrustContentService
                .findTbFixEnTrustContentListByTbFixEntrustId(tbFixEntrustId);

        /*
         * List<TbMaintainPartContent> maintainList =
         * tbMaintainPartContentService
         * .getViewEntrustMaintianContent(tbFixEntrustId);
         * 
         * 
         * List<TmStockOutDetVo> solePartList =
         * tmStockOutService.getSellByEntrustCode
         * (tbFixEntrust.getEntrustCode());
         */

        /**
         * ??
         */
        List<TbMaintianVo> maintianvos = tbMaintainPartContentService
                .getTbMaintianDetailVosByEntrustId(tbFixEntrust.getId(), Constants.BALANCE_ALL);

        /**
         * ?
         */
        List<TmStockOutDetVo> tmStockOutDetVos = tmStockOutService
                .getSellDetailByEntrustCode(tbFixEntrust.getEntrustCode(), Constants.BALANCE_ALL);

        /**
         * ??
         */

        List<TbMaintianVo> partAll = new ArrayList<TbMaintianVo>();

        if (null != maintianvos && maintianvos.size() > 0) {

            for (TbMaintianVo tbMaintianVo : maintianvos) {

                partAll.add(tbMaintianVo);

            }
        }

        if (null != tmStockOutDetVos && tmStockOutDetVos.size() > 0) {

            for (TmStockOutDetVo tmStockOutDetVo : tmStockOutDetVos) {

                TbMaintianVo tbMaintianVo = new TbMaintianVo();

                tbMaintianVo.setPartId(tmStockOutDetVo.getPartinfoId());

                tbMaintianVo.setHouseName(tmStockOutDetVo.getHouseName());

                tbMaintianVo.setPartCode(tmStockOutDetVo.getPartCode());

                tbMaintianVo.setPartName(tmStockOutDetVo.getPartName());

                tbMaintianVo.setUnitName(tmStockOutDetVo.getUnitName());

                tbMaintianVo.setPrice(tmStockOutDetVo.getPrice());

                tbMaintianVo.setPartQuantity(tmStockOutDetVo.getQuantity());

                tbMaintianVo.setTotal(tmStockOutDetVo.getTotal());

                tbMaintianVo.setIsFree(tmStockOutDetVo.getIsFree());

                tbMaintianVo.setProjectType(tmStockOutDetVo.getProjectType());

                tbMaintianVo.setZl(tmStockOutDetVo.getZl());

                tbMaintianVo.setXmlx(tmStockOutDetVo.getXmlx());

                partAll.add(tbMaintianVo);
            }
        }

        int fixSize = (tbFixEntrustContentList == null ? 0 : tbFixEntrustContentList.size());

        int maintainSize = (maintianvos == null ? 0 : maintianvos.size());

        int solePartSize = (tmStockOutDetVos == null ? 0 : tmStockOutDetVos.size());

        int partAllSize = (partAll == null ? 0 : partAll.size());

        HSSFWorkbook workbook = new HSSFWorkbook(this.getClass().getResourceAsStream(tpl));

        HSSFSheet sheet = workbook.getSheetAt(0);

        sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.5);

        sheet.setMargin(HSSFSheet.RightMargin, (double) 0.5);

        HSSFCellStyle style = workbook.createCellStyle();

        style.setWrapText(true);

        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont font = workbook.createFont();

        font.setFontName("");

        font.setFontHeightInPoints((short) 9);

        style.setFont(font);

        HSSFRow row = null;

        HSSFCell cell = null;

        ByteArrayOutputStream byteArrayOutImgLion = new ByteArrayOutputStream();

        ByteArrayOutputStream byteArrayOutDBZF = new ByteArrayOutputStream();

        BufferedImage bufferImgLion = ImageIO.read(this.getClass().getResourceAsStream("/lion.png"));

        BufferedImage bufferImgDFBZ = ImageIO.read(this.getClass().getResourceAsStream("/dfbz.png"));

        ImageIO.write(bufferImgLion, "png", byteArrayOutImgLion);

        ImageIO.write(bufferImgDFBZ, "png", byteArrayOutDBZF);

        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

        HSSFClientAnchor anchorLion = new HSSFClientAnchor(0, 0, 1023, 200, (short) 48, 0, (short) 53, 7);

        HSSFClientAnchor anchorDBZF = new HSSFClientAnchor(0, 0, 1023, 200, (short) 0, 0, (short) 8, 7);

        patriarch.createPicture(anchorLion,
                workbook.addPicture(byteArrayOutImgLion.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

        patriarch.createPicture(anchorDBZF,
                workbook.addPicture(byteArrayOutDBZF.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

        row = sheet.getRow(10);

        cell = row.getCell(0);

        String entrustCode = tbFixEntrust.getEntrustCode();

        String[] es = entrustCode.split("-");

        String newCode = "RO" + es[0].substring(2, 6) + es[1];

        // cell.setCellValue(tbFixEntrust.getEntrustCode());

        cell.setCellValue(newCode);

        row = sheet.getRow(10);

        cell = row.getCell(8);

        cell.setCellValue(tbCarInfo.getPurchaseDate() == null ? ""
                : CommonMethod.parseDateToString(tbCarInfo.getPurchaseDate(), "yyyy-MM-dd"));

        row = sheet.getRow(10);

        cell = row.getCell(14);

        cell.setCellValue(
                tbFixEntrust.getTmUser().getUserRealName() == null ? tbFixEntrust.getTmUser().getUserName()
                        : tbFixEntrust.getTmUser().getUserRealName());

        row = sheet.getRow(10);

        cell = row.getCell(19);

        cell.setCellValue(tbCarInfo.getLicenseCode());

        row = sheet.getRow(10);

        cell = row.getCell(27);

        cell.setCellStyle(style);

        cell.setCellValue(tbCarInfo.getTmCarModelType().getModelName());

        row = sheet.getRow(10);

        cell = row.getCell(32);

        cell.setCellValue(
                tbFixEntrust.getEnterStationKilo() == null ? ""
                        : new BigDecimal(tbFixEntrust.getEnterStationKilo().toString())
                                .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString()
                                + "   Km");

        row = sheet.getRow(10);

        cell = row.getCell(41);

        cell.setCellValue(tbCarInfo.getColor() == null ? "" : tbCarInfo.getColor().toString());

        row = sheet.getRow(10);

        cell = row.getCell(45);

        cell.setCellValue(tbFixEntrust.getFixDate() == null ? ""
                : CommonMethod.parseDateToString(tbFixEntrust.getFixDate(), Constants.TIMEFORMATOFMINUTE));

        row = sheet.getRow(15);

        cell = row.getCell(0);

        cell.setCellValue(tbCustomer.getCustomerName() == null ? "" : tbCustomer.getContractPerson());

        row = sheet.getRow(15);

        cell = row.getCell(8);

        cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone());

        row = sheet.getRow(15);

        cell = row.getCell(14);

        cell.setCellValue(tbCustomer.getPhone() == null ? "" : tbCustomer.getPhone());

        row = sheet.getRow(15);

        cell = row.getCell(20);

        cell.setCellValue(tbCarInfo.getChassisCode() == null ? "" : tbCarInfo.getChassisCode());

        row = sheet.getRow(15);

        cell = row.getCell(32);

        // cell.setCellStyle(style);

        cell.setCellValue(tbCarInfo.getEngineCode() == null ? "" : tbCarInfo.getEngineCode());

        row = sheet.getRow(15);

        cell = row.getCell(45);

        // cell.setCellStyle(style);

        cell.setCellValue(tbFixEntrust.getEstimateDate() == null ? ""
                : CommonMethod.parseDateToString(tbFixEntrust.getEstimateDate(), Constants.TIMEFORMATOFMINUTE));

        row = sheet.getRow(18);

        cell = row.getCell(8);

        cell.setCellValue(tbCustomer.getCustomerName() == null ? "" : tbCustomer.getCustomerName());

        row = sheet.getRow(20);

        cell = row.getCell(8);

        cell.setCellStyle(style);

        cell.setCellValue(tbCustomer.getAddress() == null ? "" : tbCustomer.getAddress());

        row = sheet.getRow(25);

        cell = row.getCell(8);

        cell.setCellValue(tbCustomer.getZipCode() == null ? "" : tbCustomer.getZipCode());

        row = sheet.getRow(20);

        cell = row.getCell(23);

        cell.setCellStyle(style);

        cell.setCellValue((tbFixEntrust.getWrongDescribe() == null || "".equals(tbFixEntrust.getWrongDescribe())
                ? ""
                : tbFixEntrust.getWrongDescribe() + ";")
                + (tbFixEntrust.getBeforeFixState() == null || "".equals(tbFixEntrust.getBeforeFixState()) ? ""
                        : tbFixEntrust.getBeforeFixState() + ";"));

        Double fixCount = tbFixEntrustContentService.countTbFixEnTrustContentByTbFixEntrustId(tbFixEntrustId);

        row = sheet.getRow(70);

        cell = row.getCell(32);

        cell.setCellStyle(style);

        cell.setCellValue(new BigDecimal(fixCount).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP)
                .toString());

        row = sheet.getRow(70);

        cell = row.getCell(48);

        cell.setCellStyle(style);

        cell.setCellValue("0.00");

        BigDecimal partPriceAll = new BigDecimal("0.00");

        if (partAllSize > 0) {

            for (int i = 0; i < partAllSize; i++) {

                TbMaintianVo tbMaintianVo = partAll.get(i);

                BigDecimal total = new BigDecimal(tbMaintianVo.getPrice())
                        .multiply(new BigDecimal(tbMaintianVo.getPartQuantity()));

                partPriceAll = partPriceAll.add(total);

            }

        }

        row = sheet.getRow(73);

        cell = row.getCell(33);

        cell.setCellStyle(style);

        cell.setCellValue(partPriceAll.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

        BigDecimal total = new BigDecimal(fixCount).add(partPriceAll).divide(new BigDecimal("1.00"), 2,
                BigDecimal.ROUND_HALF_UP);

        row = sheet.getRow(76);

        cell = row.getCell(34);

        cell.setCellStyle(style);

        cell.setCellValue(total.toString());

        int page = 1;

        if (fixSize / 5 >= (maintainSize + solePartSize) / 13) {

            page = fixSize / 5;

        }

        else {

            page = (maintainSize + solePartSize) / 13;

        }

        for (int i = 0; i < page; i++) {

            int p = 0;

            int k = 0;

            HSSFSheet sheetClone = workbook.cloneSheet(0);

            byteArrayOutImgLion = new ByteArrayOutputStream();

            byteArrayOutDBZF = new ByteArrayOutputStream();

            bufferImgLion = ImageIO.read(this.getClass().getResourceAsStream("/lion.png"));

            bufferImgDFBZ = ImageIO.read(this.getClass().getResourceAsStream("/dfbz.png"));

            ImageIO.write(bufferImgLion, "png", byteArrayOutImgLion);

            ImageIO.write(bufferImgDFBZ, "png", byteArrayOutDBZF);

            patriarch = sheetClone.createDrawingPatriarch();

            anchorLion = new HSSFClientAnchor(0, 0, 1023, 200, (short) 48, 0, (short) 53, 7);

            anchorDBZF = new HSSFClientAnchor(0, 0, 1023, 200, (short) 0, 0, (short) 8, 7);

            patriarch.createPicture(anchorLion,
                    workbook.addPicture(byteArrayOutImgLion.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

            patriarch.createPicture(anchorDBZF,
                    workbook.addPicture(byteArrayOutDBZF.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

            if (fixSize > 5) {

                int printFixSize = (fixSize > (i + 2) * 5 ? (i + 2) * 5 : fixSize);

                for (int j = 5 * (i + 1); j < printFixSize; j++) {

                    TbFixEntrustContent content = tbFixEntrustContentList.get(j);

                    List<TbFixShare> tbFixShareList = tbFixShareService
                            .findTbFixShareListByTbFixEntrustContentId(content.getId());

                    String fixPersons = "";

                    if (null != tbFixShareList && tbFixShareList.size() > 0) {

                        for (TbFixShare tbFixShare : tbFixShareList) {

                            if (null != tbFixShare.getTmUser()) {

                                TmUser tmUser = tmUserService.findById(tbFixShare.getTmUser().getId());

                                fixPersons += (tmUser.getUserRealName() == null
                                        || "".equals(tmUser.getUserRealName()) ? tmUser.getUserName()
                                                : tmUser.getUserRealName())
                                        + " ";
                            }

                        }
                    }

                    row = sheetClone.getRow(31 + p * 6);

                    cell = row.getCell(0);

                    cell.setCellStyle(style);

                    cell.setCellValue(content.getStationCode() + "    " + content.getStationName());

                    cell = row.getCell(20);

                    cell.setCellStyle(style);

                    cell.setCellValue(content.getXmlx() == null ? "" : content.getXmlx());

                    cell = row.getCell(26);

                    cell.setCellStyle(style);

                    cell.setCellValue(fixPersons);

                    p++;

                }

            }

            if (partAllSize > 13) {

                int prinPartSize = (partAllSize > (i + 2) * 13 ? (i + 2) * 13 : partAllSize);

                for (int j = 13 * (i + 1); j < prinPartSize; j++) {

                    TbMaintianVo t = partAll.get(j);

                    row = sheetClone.getRow(31 + k * 3);

                    cell = row.getCell(32);

                    cell.setCellStyle(style);

                    cell.setCellValue(t.getPartName());

                    cell = row.getCell(40);

                    cell.setCellStyle(style);

                    cell.setCellValue(t.getPartQuantity());

                    cell = row.getCell(44);

                    cell.setCellStyle(style);

                    cell.setCellValue(new BigDecimal(t.getPrice())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    k++;

                }
            }

        }

        if (fixSize > 0) {

            int printFixSize = (fixSize > 5 ? 5 : fixSize);

            for (int j = 0; j < printFixSize; j++) {

                TbFixEntrustContent content = tbFixEntrustContentList.get(j);

                List<TbFixShare> tbFixShareList = tbFixShareService
                        .findTbFixShareListByTbFixEntrustContentId(content.getId());

                String fixPersons = "";

                if (null != tbFixShareList && tbFixShareList.size() > 0) {

                    for (TbFixShare tbFixShare : tbFixShareList) {

                        if (null != tbFixShare.getTmUser()) {

                            TmUser tmUser = tmUserService.findById(tbFixShare.getTmUser().getId());

                            fixPersons += (tmUser.getUserRealName() == null
                                    || "".equals(tmUser.getUserRealName()) ? tmUser.getUserName()
                                            : tmUser.getUserRealName())
                                    + " ";
                        }

                    }
                }

                row = sheet.getRow(31 + j * 6);

                cell = row.getCell(0);

                cell.setCellStyle(style);

                cell.setCellValue(content.getStationCode() + "    " + content.getStationName());

                cell = row.getCell(20);

                cell.setCellStyle(style);

                cell.setCellValue(content.getXmlx() == null ? "" : content.getXmlx());

                cell = row.getCell(26);

                cell.setCellStyle(style);

                cell.setCellValue(fixPersons);

            }

        }

        if (partAllSize > 0) {

            int printPartSize = (partAllSize > 13 ? 13 : partAllSize);

            for (int j = 0; j < printPartSize; j++) {

                TbMaintianVo t = partAll.get(j);

                row = sheet.getRow(31 + j * 3);

                cell = row.getCell(32);

                cell.setCellStyle(style);

                cell.setCellValue(t.getPartName());

                cell = row.getCell(40);

                cell.setCellStyle(style);

                cell.setCellValue(t.getPartQuantity());

                cell = row.getCell(44);

                cell.setCellStyle(style);

                cell.setCellValue(new BigDecimal(t.getPrice())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

            }

        }

        workbook.write(os);

    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.selfsoft.business.service.impl.TbFixEntrustServiceImpl.java

public void printTbFixEntrustTemplateBlank(OutputStream os, String tpl, Long tbFixEntrustId) {

    try {//from   w  ww . j a va  2s .  c om

        TbFixEntrust tbFixEntrust = this.findById(tbFixEntrustId);

        TbCustomer tbCustomer = tbCustomerService.findById(tbFixEntrust.getTbCustomer().getId());

        TbCarInfo tbCarInfo = tbCarInfoService.findById(tbFixEntrust.getTbCarInfo().getId());

        List<TbFixEntrustContent> tbFixEntrustContentList = tbFixEntrustContentService
                .findTbFixEnTrustContentListByTbFixEntrustId(tbFixEntrustId);

        /*
         * List<TbMaintainPartContent> maintainList =
         * tbMaintainPartContentService
         * .getViewEntrustMaintianContent(tbFixEntrustId);
         * 
         * 
         * List<TmStockOutDetVo> solePartList =
         * tmStockOutService.getSellByEntrustCode
         * (tbFixEntrust.getEntrustCode());
         */

        /**
         * ??
         */
        List<TbMaintianVo> maintianvos = tbMaintainPartContentService
                .getTbMaintianDetailVosByEntrustId(tbFixEntrust.getId(), Constants.BALANCE_ALL);

        /**
         * ?
         */
        List<TmStockOutDetVo> tmStockOutDetVos = tmStockOutService
                .getSellDetailByEntrustCode(tbFixEntrust.getEntrustCode(), Constants.BALANCE_ALL);

        /**
         * ??
         */

        List<TbMaintianVo> partAll = new ArrayList<TbMaintianVo>();

        if (null != maintianvos && maintianvos.size() > 0) {

            for (TbMaintianVo tbMaintianVo : maintianvos) {

                partAll.add(tbMaintianVo);

            }
        }

        if (null != tmStockOutDetVos && tmStockOutDetVos.size() > 0) {

            for (TmStockOutDetVo tmStockOutDetVo : tmStockOutDetVos) {

                TbMaintianVo tbMaintianVo = new TbMaintianVo();

                tbMaintianVo.setPartId(tmStockOutDetVo.getPartinfoId());

                tbMaintianVo.setHouseName(tmStockOutDetVo.getHouseName());

                tbMaintianVo.setPartCode(tmStockOutDetVo.getPartCode());

                tbMaintianVo.setPartName(tmStockOutDetVo.getPartName());

                tbMaintianVo.setUnitName(tmStockOutDetVo.getUnitName());

                tbMaintianVo.setPrice(tmStockOutDetVo.getPrice());

                tbMaintianVo.setPartQuantity(tmStockOutDetVo.getQuantity());

                tbMaintianVo.setTotal(tmStockOutDetVo.getTotal());

                tbMaintianVo.setIsFree(tmStockOutDetVo.getIsFree());

                tbMaintianVo.setProjectType(tmStockOutDetVo.getProjectType());

                tbMaintianVo.setZl(tmStockOutDetVo.getZl());

                tbMaintianVo.setXmlx(tmStockOutDetVo.getXmlx());

                partAll.add(tbMaintianVo);
            }
        }

        int fixSize = (tbFixEntrustContentList == null ? 0 : tbFixEntrustContentList.size());

        int maintainSize = (maintianvos == null ? 0 : maintianvos.size());

        int solePartSize = (tmStockOutDetVos == null ? 0 : tmStockOutDetVos.size());

        int partAllSize = (partAll == null ? 0 : partAll.size());

        HSSFWorkbook workbook = new HSSFWorkbook(this.getClass().getResourceAsStream(tpl));

        HSSFSheet sheet = workbook.getSheetAt(0);

        HSSFCellStyle style = workbook.createCellStyle();

        style.setWrapText(true);

        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont font = workbook.createFont();

        font.setFontName("");

        font.setFontHeightInPoints((short) 9);

        // style.setFont(font);

        HSSFCellStyle style11 = workbook.createCellStyle();

        style11.setWrapText(true);

        style11.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        style11.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont font11 = workbook.createFont();

        font11.setFontName("");

        font11.setFontHeightInPoints((short) 11);

        // style11.setFont(font11);

        HSSFCellStyle style10 = workbook.createCellStyle();

        style10.setWrapText(true);

        style10.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        style10.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont font10 = workbook.createFont();

        font10.setFontName("");

        font10.setFontHeightInPoints((short) 10);

        // style10.setFont(font10);

        HSSFRow row = null;

        HSSFCell cell = null;

        row = sheet.getRow(10);

        cell = row.getCell(0);

        String entrustCode = tbFixEntrust.getEntrustCode();

        String[] es = entrustCode.split("-");

        String newCode = "RO" + es[0].substring(2, 6) + es[1];

        // cell.setCellValue(tbFixEntrust.getEntrustCode());

        cell.setCellValue(newCode);

        row = sheet.getRow(10);

        cell = row.getCell(8);

        cell.setCellValue(tbCarInfo.getPurchaseDate() == null ? ""
                : CommonMethod.parseDateToString(tbCarInfo.getPurchaseDate(), "yyyy-MM-dd"));

        row = sheet.getRow(10);

        cell = row.getCell(14);

        cell.setCellValue(
                tbFixEntrust.getTmUser().getUserRealName() == null ? tbFixEntrust.getTmUser().getUserName()
                        : tbFixEntrust.getTmUser().getUserRealName());

        row = sheet.getRow(10);

        cell = row.getCell(19);

        cell.setCellValue(tbCarInfo.getLicenseCode());

        row = sheet.getRow(10);

        cell = row.getCell(27);

        // cell.setCellStyle(style);

        cell.setCellValue(tbCarInfo.getTmCarModelType().getModelName());

        row = sheet.getRow(10);

        cell = row.getCell(32);

        cell.setCellValue(
                tbFixEntrust.getEnterStationKilo() == null ? ""
                        : new BigDecimal(tbFixEntrust.getEnterStationKilo().toString())
                                .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString()
                                + "   Km");

        row = sheet.getRow(10);

        cell = row.getCell(41);

        cell.setCellValue(tbCarInfo.getColor() == null ? "" : tbCarInfo.getColor().toString());

        row = sheet.getRow(10);

        cell = row.getCell(45);

        cell.setCellValue(tbFixEntrust.getFixDate() == null ? ""
                : CommonMethod.parseDateToString(tbFixEntrust.getFixDate(), Constants.TIMEFORMATOFMINUTE));

        row = sheet.getRow(15);

        cell = row.getCell(0);

        cell.setCellValue(tbCustomer.getCustomerName() == null ? "" : tbCustomer.getContractPerson());

        row = sheet.getRow(15);

        cell = row.getCell(8);

        cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone());

        row = sheet.getRow(15);

        cell = row.getCell(14);

        cell.setCellValue(tbCustomer.getPhone() == null ? "" : tbCustomer.getPhone());

        row = sheet.getRow(15);

        cell = row.getCell(20);

        cell.setCellValue(tbCarInfo.getChassisCode() == null ? "" : tbCarInfo.getChassisCode());

        row = sheet.getRow(15);

        cell = row.getCell(32);

        // cell.setCellStyle(style);

        cell.setCellValue(tbCarInfo.getEngineCode() == null ? "" : tbCarInfo.getEngineCode());

        row = sheet.getRow(15);

        cell = row.getCell(45);

        // cell.setCellStyle(style);

        cell.setCellValue(tbFixEntrust.getEstimateDate() == null ? ""
                : CommonMethod.parseDateToString(tbFixEntrust.getEstimateDate(), Constants.TIMEFORMATOFMINUTE));

        row = sheet.getRow(18);

        cell = row.getCell(8);

        cell.setCellValue(tbCustomer.getCustomerName() == null ? "" : tbCustomer.getCustomerName());

        row = sheet.getRow(20);

        cell = row.getCell(8);

        // cell.setCellStyle(style);

        cell.setCellValue(tbCustomer.getAddress() == null ? "" : tbCustomer.getAddress());

        row = sheet.getRow(25);

        cell = row.getCell(8);

        cell.setCellValue(tbCustomer.getZipCode() == null ? "" : tbCustomer.getZipCode());

        row = sheet.getRow(20);

        cell = row.getCell(23);

        // cell.setCellStyle(style);

        cell.setCellValue((tbFixEntrust.getWrongDescribe() == null || "".equals(tbFixEntrust.getWrongDescribe())
                ? ""
                : tbFixEntrust.getWrongDescribe() + ";")
                + (tbFixEntrust.getBeforeFixState() == null || "".equals(tbFixEntrust.getBeforeFixState()) ? ""
                        : tbFixEntrust.getBeforeFixState() + ";"));

        Double fixCount = tbFixEntrustContentService.countTbFixEnTrustContentByTbFixEntrustId(tbFixEntrustId);

        row = sheet.getRow(70);

        cell = row.getCell(32);

        // cell.setCellStyle(style);

        cell.setCellValue(new BigDecimal(fixCount).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP)
                .toString());

        row = sheet.getRow(70);

        cell = row.getCell(48);

        // cell.setCellStyle(style);

        cell.setCellValue("0.00");

        BigDecimal partPriceAll = new BigDecimal("0.00");

        if (partAllSize > 0) {

            for (int i = 0; i < partAllSize; i++) {

                TbMaintianVo tbMaintianVo = partAll.get(i);

                BigDecimal total = new BigDecimal(tbMaintianVo.getPrice())
                        .multiply(new BigDecimal(tbMaintianVo.getPartQuantity()));

                partPriceAll = partPriceAll.add(total);

            }

        }

        row = sheet.getRow(73);

        cell = row.getCell(34);

        // cell.setCellStyle(style);

        cell.setCellValue(partPriceAll.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

        BigDecimal total = new BigDecimal(fixCount).add(partPriceAll).divide(new BigDecimal("1.00"), 2,
                BigDecimal.ROUND_HALF_UP);

        row = sheet.getRow(76);

        cell = row.getCell(35);

        // cell.setCellStyle(style);

        cell.setCellValue(total.toString());

        int page = 1;

        if (fixSize / 5 >= (maintainSize + solePartSize) / 13) {

            page = fixSize / 5;

        }

        else {

            page = (maintainSize + solePartSize) / 13;

        }

        for (int i = 0; i < page; i++) {

            int p = 0;

            int k = 0;

            HSSFSheet sheetClone = workbook.cloneSheet(0);

            if (fixSize > 5) {

                int printFixSize = (fixSize > (i + 2) * 5 ? (i + 2) * 5 : fixSize);

                for (int j = 5 * (i + 1); j < printFixSize; j++) {

                    TbFixEntrustContent content = tbFixEntrustContentList.get(j);

                    List<TbFixShare> tbFixShareList = tbFixShareService
                            .findTbFixShareListByTbFixEntrustContentId(content.getId());

                    String fixPersons = "";

                    if (null != tbFixShareList && tbFixShareList.size() > 0) {

                        for (TbFixShare tbFixShare : tbFixShareList) {

                            if (null != tbFixShare.getTmUser()) {

                                TmUser tmUser = tmUserService.findById(tbFixShare.getTmUser().getId());

                                fixPersons += (tmUser.getUserRealName() == null
                                        || "".equals(tmUser.getUserRealName()) ? tmUser.getUserName()
                                                : tmUser.getUserRealName())
                                        + " ";
                            }

                        }
                    }

                    row = sheetClone.getRow(31 + p * 6);

                    cell = row.getCell(0);

                    // cell.setCellStyle(style11);

                    cell.setCellValue(content.getStationCode() + "    " + content.getStationName());

                    cell = row.getCell(20);

                    // cell.setCellStyle(style11);

                    cell.setCellValue(content.getWxlx() == null ? "" : content.getWxlx());

                    cell = row.getCell(26);

                    // cell.setCellStyle(style11);

                    cell.setCellValue(fixPersons);

                    p++;

                }

            }

            if (partAllSize > 13) {

                int prinPartSize = (partAllSize > (i + 2) * 13 ? (i + 2) * 13 : partAllSize);

                for (int j = 13 * (i + 1); j < prinPartSize; j++) {

                    TbMaintianVo t = partAll.get(j);

                    row = sheetClone.getRow(31 + k * 3);

                    cell = row.getCell(32);

                    // cell.setCellStyle(style10);

                    cell.setCellValue(t.getPartName());

                    cell = row.getCell(40);

                    // cell.setCellStyle(style10);

                    cell.setCellValue(new BigDecimal(t.getPartQuantity())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(44);

                    // cell.setCellStyle(style10);

                    cell.setCellValue(new BigDecimal(t.getPrice())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    k++;

                }
            }

        }

        if (fixSize > 0) {

            int printFixSize = (fixSize > 5 ? 5 : fixSize);

            for (int j = 0; j < printFixSize; j++) {

                TbFixEntrustContent content = tbFixEntrustContentList.get(j);

                List<TbFixShare> tbFixShareList = tbFixShareService
                        .findTbFixShareListByTbFixEntrustContentId(content.getId());

                String fixPersons = "";

                if (null != tbFixShareList && tbFixShareList.size() > 0) {

                    for (TbFixShare tbFixShare : tbFixShareList) {

                        if (null != tbFixShare.getTmUser()) {

                            TmUser tmUser = tmUserService.findById(tbFixShare.getTmUser().getId());

                            fixPersons += (tmUser.getUserRealName() == null
                                    || "".equals(tmUser.getUserRealName()) ? tmUser.getUserName()
                                            : tmUser.getUserRealName())
                                    + " ";
                        }

                    }
                }

                row = sheet.getRow(31 + j * 6);

                cell = row.getCell(0);

                // cell.setCellStyle(style11);

                cell.setCellValue(content.getStationCode() + "    " + content.getStationName());

                cell = row.getCell(20);

                // cell.setCellStyle(style11);

                cell.setCellValue(content.getWxlx() == null ? "" : content.getWxlx());

                cell = row.getCell(26);

                // cell.setCellStyle(style11);

                cell.setCellValue(fixPersons);

            }

        }

        if (partAllSize > 0) {

            int printPartSize = (partAllSize > 13 ? 13 : partAllSize);

            for (int j = 0; j < printPartSize; j++) {

                TbMaintianVo t = partAll.get(j);

                row = sheet.getRow(31 + j * 3);

                cell = row.getCell(32);

                // cell.setCellStyle(style10);

                cell.setCellValue(t.getPartName());

                cell = row.getCell(40);

                // cell.setCellStyle(style10);

                cell.setCellValue(new BigDecimal(t.getPartQuantity())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(44);

                // cell.setCellStyle(style10);

                cell.setCellValue(new BigDecimal(t.getPrice())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

            }

        }

        workbook.write(os);

    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.selfsoft.business.service.impl.TbFixEntrustServiceImpl.java

public void printTbFixEntrustTemplateXTL(OutputStream os, String tpl, Long tbFixEntrustId) {

    try {/*from  w ww .j  av a 2s  .  co m*/

        TbFixEntrust tbFixEntrust = this.findById(tbFixEntrustId);

        TbCustomer tbCustomer = tbCustomerService.findById(tbFixEntrust.getTbCustomer().getId());

        TbCarInfo tbCarInfo = tbCarInfoService.findById(tbFixEntrust.getTbCarInfo().getId());

        List<TbFixEntrustContent> tbFixEntrustContentList = this.composeContent(
                tbFixEntrustContentService.findTbFixEnTrustContentListByTbFixEntrustId(tbFixEntrustId));

        // ??
        TmCompany tmCompany = tmCompanyService.acquireUniqueTmCompany();

        /*
         * List<TbMaintainPartContent> maintainList =
         * tbMaintainPartContentService
         * .getViewEntrustMaintianContent(tbFixEntrustId);
         * 
         * 
         * List<TmStockOutDetVo> solePartList =
         * tmStockOutService.getSellByEntrustCode
         * (tbFixEntrust.getEntrustCode());
         */

        /**
         * ??
         */
        List<TbMaintianVo> maintianvos = tbMaintainPartContentService
                .getTbMaintianDetailVosByEntrustId(tbFixEntrust.getId(), Constants.BALANCE_ALL);

        /**
         * ?
         */
        List<TmStockOutDetVo> tmStockOutDetVos = tmStockOutService
                .getSellDetailByEntrustCode(tbFixEntrust.getEntrustCode(), Constants.BALANCE_ALL);

        /**
         * ??
         */

        List<TbMaintianVo> partAll = new ArrayList<TbMaintianVo>();

        if (null != maintianvos && maintianvos.size() > 0) {

            for (TbMaintianVo tbMaintianVo : maintianvos) {

                partAll.add(tbMaintianVo);

            }
        }

        if (null != tmStockOutDetVos && tmStockOutDetVos.size() > 0) {

            for (TmStockOutDetVo tmStockOutDetVo : tmStockOutDetVos) {

                TbMaintianVo tbMaintianVo = new TbMaintianVo();

                tbMaintianVo.setPartId(tmStockOutDetVo.getPartinfoId());

                tbMaintianVo.setHouseName(tmStockOutDetVo.getHouseName());

                tbMaintianVo.setPartCode(tmStockOutDetVo.getPartCode());

                tbMaintianVo.setPartName(tmStockOutDetVo.getPartName());

                tbMaintianVo.setUnitName(tmStockOutDetVo.getUnitName());

                tbMaintianVo.setPrice(tmStockOutDetVo.getPrice());

                tbMaintianVo.setPartQuantity(tmStockOutDetVo.getQuantity());

                tbMaintianVo.setTotal(tmStockOutDetVo.getTotal());

                tbMaintianVo.setIsFree(tmStockOutDetVo.getIsFree());

                tbMaintianVo.setProjectType(tmStockOutDetVo.getProjectType());

                tbMaintianVo.setZl(tmStockOutDetVo.getZl());

                tbMaintianVo.setXmlx(tmStockOutDetVo.getXmlx());

                partAll.add(tbMaintianVo);
            }
        }

        partAll = this.composePart(partAll);

        int fixSize = (tbFixEntrustContentList == null ? 0 : tbFixEntrustContentList.size());

        int maintainSize = (maintianvos == null ? 0 : maintianvos.size());

        int solePartSize = (tmStockOutDetVos == null ? 0 : tmStockOutDetVos.size());

        int partAllSize = (partAll == null ? 0 : partAll.size());

        HSSFWorkbook workbook = new HSSFWorkbook(this.getClass().getResourceAsStream(tpl));

        HSSFSheet sheet = workbook.getSheetAt(0);

        HSSFCellStyle style = workbook.createCellStyle();

        style.setWrapText(true);

        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont font = workbook.createFont();

        font.setFontName("");

        font.setFontHeightInPoints((short) 9);

        style.setFont(font);

        HSSFRow row = null;

        HSSFCell cell = null;

        row = sheet.getRow(3);

        cell = row.getCell(0);

        cell.setCellValue("  ??:" + "?" + " (?:70691M)  ?:"
                + tmCompany.getCompanyAddress() + "  ?: "
                + (tmCompany.getCompanyPhone() == null ? "" : tmCompany.getCompanyPhone()));

        row = sheet.getRow(0);

        cell = row.getCell(31);

        cell.setCellStyle(style);

        cell.setCellValue("*?:" + tbCustomer.getCustomerCode() + "\n" + "??:");

        row = sheet.getRow(6);

        cell = row.getCell(4);

        cell.setCellValue(tbCustomer.getCustomerName() == null ? "" : tbCustomer.getCustomerName());

        row = sheet.getRow(6);

        cell = row.getCell(24);

        cell.setCellStyle(style);

        cell.setCellValue(tbCustomer.getAddress() == null ? "" : tbCustomer.getAddress());

        row = sheet.getRow(6);

        cell = row.getCell(41);

        cell.setCellValue(tbCustomer.getContractPerson() == null ? "" : tbCustomer.getContractPerson());

        row = sheet.getRow(8);

        cell = row.getCell(41);

        cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone());

        row = sheet.getRow(8);

        cell = row.getCell(4);

        cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone());

        row = sheet.getRow(12);

        cell = row.getCell(0);

        String entrustCode = tbFixEntrust.getEntrustCode();

        String[] es = entrustCode.split("-");

        String newCode = "RO" + es[0].substring(2, 6) + es[1];

        cell.setCellValue(/* tbFixEntrust.getEntrustCode() */newCode);

        row = sheet.getRow(12);

        cell = row.getCell(9);

        cell.setCellValue(CommonMethod.parseDateToString(tbFixEntrust.getFixDate(), "yyyy-MM-dd HH:mm"));

        row = sheet.getRow(12);

        cell = row.getCell(18);

        cell.setCellValue(tbCarInfo.getLicenseCode());

        row = sheet.getRow(12);

        cell = row.getCell(26);

        cell.setCellStyle(style);

        cell.setCellValue(tbCarInfo.getTmCarModelType().getModelName());

        row = sheet.getRow(12);

        cell = row.getCell(34);

        cell.setCellValue(tbCarInfo.getChassisCode());

        // ?
        row = sheet.getRow(16);

        cell = row.getCell(0);

        cell.setCellValue("");

        row = sheet.getRow(16);

        cell = row.getCell(9);

        cell.setCellValue(CommonMethod.parseDateToString(tbCarInfo.getPurchaseDate(), "yyyy-MM-dd"));

        // ?
        row = sheet.getRow(16);

        cell = row.getCell(18);

        cell.setCellValue("");

        row = sheet.getRow(16);

        cell = row.getCell(26);

        cell.setCellValue(tbFixEntrust.getEnterStationKilo() == null ? ""
                : new BigDecimal(tbFixEntrust.getEnterStationKilo().toString())
                        .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString());

        row = sheet.getRow(16);

        cell = row.getCell(34);

        cell.setCellValue(CommonMethod.parseDateToString(tbFixEntrust.getEstimateDate(), "yyyy-MM-dd HH:mm"));

        Double fixCount = tbFixEntrustContentService.countTbFixEnTrustContentByTbFixEntrustId(tbFixEntrustId);

        row = sheet.getRow(75);

        cell = row.getCell(30);

        cell.setCellValue(new BigDecimal(fixCount).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP)
                .toString());

        BigDecimal partPriceAll = new BigDecimal("0.00");

        if (partAllSize > 0) {

            for (int i = 0; i < partAllSize; i++) {

                TbMaintianVo tbMaintianVo = partAll.get(i);

                BigDecimal total = new BigDecimal(tbMaintianVo.getPrice())
                        .multiply(new BigDecimal(tbMaintianVo.getPartQuantity()));

                partPriceAll = partPriceAll.add(total);

            }

        }

        row = sheet.getRow(77);

        cell = row.getCell(30);

        cell.setCellValue(partPriceAll.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

        BigDecimal total = new BigDecimal(fixCount).add(partPriceAll).divide(new BigDecimal("1.00"), 2,
                BigDecimal.ROUND_HALF_UP);

        row = sheet.getRow(81);

        cell = row.getCell(30);

        cell.setCellValue(total.toString());

        row = sheet.getRow(79);

        cell = row.getCell(30);

        cell.setCellValue("0.00");

        ByteArrayOutputStream byteArrayOutImgLion = new ByteArrayOutputStream();

        ByteArrayOutputStream byteArrayOutDBZF = new ByteArrayOutputStream();

        BufferedImage bufferImgLion = ImageIO.read(this.getClass().getResourceAsStream("/xtl.png"));

        BufferedImage bufferImgDFBZ = ImageIO.read(this.getClass().getResourceAsStream("/xtl_logo.png"));

        ImageIO.write(bufferImgLion, "png", byteArrayOutImgLion);

        ImageIO.write(bufferImgDFBZ, "png", byteArrayOutDBZF);

        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

        HSSFClientAnchor anchorLion = new HSSFClientAnchor(0, 0, 1023, 200, (short) 40, 0, (short) 47, 2);

        HSSFClientAnchor anchorDBZF = new HSSFClientAnchor(0, 0, 1023, 100, (short) 0, 0, (short) 3, 3);

        patriarch.createPicture(anchorLion,
                workbook.addPicture(byteArrayOutImgLion.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

        patriarch.createPicture(anchorDBZF,
                workbook.addPicture(byteArrayOutDBZF.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

        int page = 1;

        if (fixSize / 8 >= (maintainSize + solePartSize) / 12) {

            page = fixSize / 8;

        }

        else {

            page = (maintainSize + solePartSize) / 12;

        }

        for (int i = 0; i < page; i++) {

            int p = 0;

            int k = 0;

            HSSFSheet sheetClone = workbook.cloneSheet(0);

            byteArrayOutImgLion = new ByteArrayOutputStream();

            byteArrayOutDBZF = new ByteArrayOutputStream();

            bufferImgLion = ImageIO.read(this.getClass().getResourceAsStream("/xtl.png"));

            bufferImgDFBZ = ImageIO.read(this.getClass().getResourceAsStream("/xtl_logo.png"));

            ImageIO.write(bufferImgLion, "png", byteArrayOutImgLion);

            ImageIO.write(bufferImgDFBZ, "png", byteArrayOutDBZF);

            patriarch = sheetClone.createDrawingPatriarch();

            anchorLion = new HSSFClientAnchor(0, 0, 1023, 200, (short) 40, 0, (short) 47, 2);

            anchorDBZF = new HSSFClientAnchor(0, 0, 1023, 200, (short) 0, 0, (short) 3, 3);

            patriarch.createPicture(anchorLion,
                    workbook.addPicture(byteArrayOutImgLion.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

            patriarch.createPicture(anchorDBZF,
                    workbook.addPicture(byteArrayOutDBZF.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));

            if (fixSize > 8) {

                int printFixSize = (fixSize > (i + 2) * 8 ? (i + 2) * 8 : fixSize);

                for (int j = 8 * (i + 1); j < printFixSize; j++) {

                    TbFixEntrustContent content = tbFixEntrustContentList.get(j);

                    List<TbFixShare> tbFixShareList = tbFixShareService
                            .findTbFixShareListByTbFixEntrustContentId(content.getId());

                    String fixPersons = "";

                    String workTypes = "";

                    if (null != tbFixShareList && tbFixShareList.size() > 0) {

                        for (TbFixShare tbFixShare : tbFixShareList) {

                            if (null != tbFixShare.getTmUser()) {

                                TmUser tmUser = tmUserService.findById(tbFixShare.getTmUser().getId());

                                fixPersons += (tmUser.getUserRealName() == null
                                        || "".equals(tmUser.getUserRealName()) ? tmUser.getUserName()
                                                : tmUser.getUserRealName())
                                        + " ";

                                workTypes += (tmUser.getTmWorkType() == null ? ""
                                        : tmUser.getTmWorkType().getWorkName()) + " ";
                            }

                        }
                    }

                    row = sheetClone.getRow(23 + p * 2);

                    cell = row.getCell(2);

                    cell.setCellStyle(style);

                    cell.setCellValue(content.getStationName());

                    cell = row.getCell(20);

                    cell.setCellStyle(style);

                    cell.setCellValue(new BigDecimal(content.getFixHour())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(24);

                    cell.setCellStyle(style);

                    cell.setCellValue(new BigDecimal(content.getWorkingHourPrice())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(28);

                    cell.setCellStyle(style);

                    cell.setCellValue(new BigDecimal(content.getFixHourAll())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(34);

                    cell.setCellStyle(style);

                    cell.setCellValue(content.getProjectType() == null ? "" : content.getProjectType());

                    cell = row.getCell(40);

                    cell.setCellStyle(style);

                    cell.setCellValue(workTypes);

                    cell = row.getCell(45);

                    cell.setCellStyle(style);

                    cell.setCellValue(fixPersons);

                    p++;

                }

            }

            if (partAllSize > 12) {

                int prinPartSize = (partAllSize > (i + 2) * 12 ? (i + 2) * 12 : partAllSize);

                for (int j = 12 * (i + 1); j < prinPartSize; j++) {

                    TbMaintianVo t = partAll.get(j);

                    row = sheetClone.getRow(41 + k * 2);

                    cell = row.getCell(2);

                    cell.setCellStyle(style);

                    cell.setCellValue(t.getPartCode());

                    cell = row.getCell(11);

                    cell.setCellStyle(style);

                    cell.setCellValue(t.getPartName());

                    cell = row.getCell(20);

                    cell.setCellStyle(style);

                    cell.setCellValue(new BigDecimal(t.getPartQuantity())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(24);

                    cell.setCellStyle(style);

                    cell.setCellValue(new BigDecimal(t.getPrice())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(28);

                    cell.setCellStyle(style);

                    cell.setCellValue(new BigDecimal(t.getPartQuantity()).multiply(new BigDecimal(t.getPrice()))
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(34);

                    cell.setCellStyle(style);

                    cell.setCellValue(t.getProjectType() == null ? "" : t.getProjectType());

                    // 
                    cell = row.getCell(40);

                    cell.setCellStyle(style);

                    cell.setCellValue("");

                    k++;

                }
            }

        }

        if (fixSize > 0) {

            int printFixSize = (fixSize > 8 ? 8 : fixSize);

            for (int j = 0; j < printFixSize; j++) {

                TbFixEntrustContent content = tbFixEntrustContentList.get(j);

                List<TbFixShare> tbFixShareList = tbFixShareService
                        .findTbFixShareListByTbFixEntrustContentId(content.getId());

                String fixPersons = "";

                String workTypes = "";

                if (null != tbFixShareList && tbFixShareList.size() > 0) {

                    for (TbFixShare tbFixShare : tbFixShareList) {

                        if (null != tbFixShare.getTmUser()) {

                            TmUser tmUser = tmUserService.findById(tbFixShare.getTmUser().getId());

                            fixPersons += (tmUser.getUserRealName() == null
                                    || "".equals(tmUser.getUserRealName()) ? tmUser.getUserName()
                                            : tmUser.getUserRealName())
                                    + " ";

                            workTypes += (tmUser.getTmWorkType() == null ? ""
                                    : tmUser.getTmWorkType().getWorkName()) + " ";
                        }

                    }
                }

                row = sheet.getRow(23 + j * 2);

                cell = row.getCell(2);

                cell.setCellStyle(style);

                cell.setCellValue(content.getStationName());

                cell = row.getCell(20);

                cell.setCellStyle(style);

                cell.setCellValue(new BigDecimal(content.getFixHour())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(24);

                cell.setCellStyle(style);

                cell.setCellValue(new BigDecimal(content.getWorkingHourPrice())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(28);

                cell.setCellStyle(style);

                cell.setCellValue(new BigDecimal(content.getFixHourAll())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(34);

                cell.setCellStyle(style);

                cell.setCellValue(content.getProjectType() == null ? "" : content.getProjectType());

                cell = row.getCell(40);

                cell.setCellStyle(style);

                cell.setCellValue(workTypes);

                cell = row.getCell(45);

                cell.setCellStyle(style);

                cell.setCellValue(fixPersons);

            }

        }

        if (partAllSize > 0) {

            int printPartSize = (partAllSize > 12 ? 12 : partAllSize);

            for (int j = 0; j < printPartSize; j++) {

                TbMaintianVo t = partAll.get(j);

                row = sheet.getRow(41 + j * 2);

                cell = row.getCell(2);

                cell.setCellStyle(style);

                cell.setCellValue(t.getPartCode());

                cell = row.getCell(11);

                cell.setCellStyle(style);

                cell.setCellValue(t.getPartName());

                cell = row.getCell(20);

                cell.setCellStyle(style);

                cell.setCellValue(new BigDecimal(t.getPartQuantity())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(24);

                cell.setCellStyle(style);

                cell.setCellValue(new BigDecimal(t.getPrice())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(28);

                cell.setCellStyle(style);

                cell.setCellValue(new BigDecimal(t.getPartQuantity()).multiply(new BigDecimal(t.getPrice()))
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(34);

                cell.setCellStyle(style);

                cell.setCellValue(t.getProjectType() == null ? "" : t.getProjectType());

                // 
                cell = row.getCell(40);

                cell.setCellStyle(style);

                cell.setCellValue("");

            }

        }

        workbook.write(os);

    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

From source file:com.selfsoft.business.service.impl.TbFixEntrustServiceImpl.java

public void printTbFixEntrustTemplateBlankXTL(OutputStream os, String tpl, Long tbFixEntrustId) {

    try {/*from  www. j  ava  2 s  .  com*/

        TbFixEntrust tbFixEntrust = this.findById(tbFixEntrustId);

        TbCustomer tbCustomer = tbCustomerService.findById(tbFixEntrust.getTbCustomer().getId());

        TbCarInfo tbCarInfo = tbCarInfoService.findById(tbFixEntrust.getTbCarInfo().getId());

        List<TbFixEntrustContent> tbFixEntrustContentList = tbFixEntrustContentService
                .findTbFixEnTrustContentListByTbFixEntrustId(tbFixEntrustId);

        // ??
        TmCompany tmCompany = tmCompanyService.acquireUniqueTmCompany();

        /*
         * List<TbMaintainPartContent> maintainList =
         * tbMaintainPartContentService
         * .getViewEntrustMaintianContent(tbFixEntrustId);
         * 
         * 
         * List<TmStockOutDetVo> solePartList =
         * tmStockOutService.getSellByEntrustCode
         * (tbFixEntrust.getEntrustCode());
         */

        /**
         * ??
         */
        List<TbMaintianVo> maintianvos = tbMaintainPartContentService
                .getTbMaintianDetailVosByEntrustId(tbFixEntrust.getId(), Constants.BALANCE_ALL);

        /**
         * ?
         */
        List<TmStockOutDetVo> tmStockOutDetVos = tmStockOutService
                .getSellDetailByEntrustCode(tbFixEntrust.getEntrustCode(), Constants.BALANCE_ALL);

        /**
         * ??
         */

        List<TbMaintianVo> partAll = new ArrayList<TbMaintianVo>();

        if (null != maintianvos && maintianvos.size() > 0) {

            for (TbMaintianVo tbMaintianVo : maintianvos) {

                partAll.add(tbMaintianVo);

            }
        }

        if (null != tmStockOutDetVos && tmStockOutDetVos.size() > 0) {

            for (TmStockOutDetVo tmStockOutDetVo : tmStockOutDetVos) {

                TbMaintianVo tbMaintianVo = new TbMaintianVo();

                tbMaintianVo.setPartId(tmStockOutDetVo.getPartinfoId());

                tbMaintianVo.setHouseName(tmStockOutDetVo.getHouseName());

                tbMaintianVo.setPartCode(tmStockOutDetVo.getPartCode());

                tbMaintianVo.setPartName(tmStockOutDetVo.getPartName());

                tbMaintianVo.setUnitName(tmStockOutDetVo.getUnitName());

                tbMaintianVo.setPrice(tmStockOutDetVo.getPrice());

                tbMaintianVo.setPartQuantity(tmStockOutDetVo.getQuantity());

                tbMaintianVo.setTotal(tmStockOutDetVo.getTotal());

                tbMaintianVo.setIsFree(tmStockOutDetVo.getIsFree());

                tbMaintianVo.setProjectType(tmStockOutDetVo.getProjectType());

                tbMaintianVo.setZl(tmStockOutDetVo.getZl());

                tbMaintianVo.setXmlx(tmStockOutDetVo.getXmlx());

                partAll.add(tbMaintianVo);
            }
        }

        int fixSize = (tbFixEntrustContentList == null ? 0 : tbFixEntrustContentList.size());

        int maintainSize = (maintianvos == null ? 0 : maintianvos.size());

        int solePartSize = (tmStockOutDetVos == null ? 0 : tmStockOutDetVos.size());

        int partAllSize = (partAll == null ? 0 : partAll.size());

        int pz = (fixSize / 8 > partAllSize / 12 ? (fixSize / 8 + 1) : (partAllSize / 12 + 1));

        HSSFWorkbook workbook = new HSSFWorkbook(this.getClass().getResourceAsStream(tpl));

        HSSFSheet sheet = workbook.getSheetAt(0);

        HSSFCellStyle style = workbook.createCellStyle();

        style.setWrapText(true);

        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont font = workbook.createFont();

        font.setFontName("");

        font.setFontHeightInPoints((short) 9);

        style.setFont(font);

        HSSFCellStyle style10_5 = workbook.createCellStyle();

        style10_5.setWrapText(true);

        style10_5.setAlignment(HSSFCellStyle.ALIGN_LEFT);

        style10_5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFFont font10_5 = workbook.createFont();

        font10_5.setFontName("");

        font10_5.setFontHeightInPoints((short) 11);

        style10_5.setFont(font10_5);

        HSSFRow row = null;

        HSSFCell cell = null;

        row = sheet.getRow(3);

        cell = row.getCell(0);

        cell.setCellValue("  ??:" + "?" + " (?:70691M)  ?:"
                + tmCompany.getCompanyAddress() + "  ?: "
                + (tmCompany.getCompanyPhone() == null ? "" : tmCompany.getCompanyPhone()));

        row = sheet.getRow(0);

        cell = row.getCell(40);

        // cell.setCellStyle(style);

        cell.setCellValue("*?:" + tbCustomer.getCustomerCode() + "\n" + "??:");

        row = sheet.getRow(6);

        cell = row.getCell(4);

        cell.setCellValue(tbCustomer.getCustomerName() == null ? "" : tbCustomer.getCustomerName());

        row = sheet.getRow(6);

        cell = row.getCell(24);

        // cell.setCellStyle(style);

        cell.setCellValue(tbCustomer.getAddress() == null ? "" : tbCustomer.getAddress());

        row = sheet.getRow(6);

        cell = row.getCell(41);

        cell.setCellValue(tbCustomer.getContractPerson() == null ? "" : tbCustomer.getContractPerson());

        row = sheet.getRow(8);

        cell = row.getCell(41);

        cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone());

        row = sheet.getRow(8);

        cell = row.getCell(4);

        cell.setCellValue(tbCustomer.getTelephone() == null ? "" : tbCustomer.getTelephone());

        row = sheet.getRow(12);

        cell = row.getCell(0);

        String entrustCode = tbFixEntrust.getEntrustCode();

        String[] es = entrustCode.split("-");

        String newCode = "RO" + es[0].substring(2, 6) + es[1];

        cell.setCellValue(/* tbFixEntrust.getEntrustCode() */newCode);

        row = sheet.getRow(12);

        cell = row.getCell(9);

        cell.setCellValue(CommonMethod.parseDateToString(tbFixEntrust.getFixDate(), "yyyy-MM-dd HH:mm"));

        row = sheet.getRow(12);

        cell = row.getCell(18);

        cell.setCellValue(tbCarInfo.getLicenseCode());

        row = sheet.getRow(12);

        cell = row.getCell(26);

        // cell.setCellStyle(style);

        cell.setCellValue(tbCarInfo.getTmCarModelType().getModelName());

        row = sheet.getRow(12);

        cell = row.getCell(34);

        cell.setCellValue(tbCarInfo.getChassisCode());

        // ?
        row = sheet.getRow(16);

        cell = row.getCell(0);

        cell.setCellValue(tbFixEntrust.getBjzzh() == null ? "" : tbFixEntrust.getBjzzh());

        row = sheet.getRow(16);

        cell = row.getCell(9);

        cell.setCellValue(CommonMethod.parseDateToString(tbCarInfo.getPurchaseDate(), "yyyy-MM-dd"));

        // ?
        row = sheet.getRow(16);

        cell = row.getCell(18);

        cell.setCellValue(tbFixEntrust.getSbrq() == null ? "" : tbFixEntrust.getSbrq());

        row = sheet.getRow(16);

        cell = row.getCell(26);

        cell.setCellValue(tbFixEntrust.getEnterStationKilo() == null ? ""
                : new BigDecimal(tbFixEntrust.getEnterStationKilo().toString())
                        .divide(new BigDecimal("1.00"), 0, BigDecimal.ROUND_HALF_UP).toString());

        row = sheet.getRow(16);

        cell = row.getCell(34);

        cell.setCellValue(CommonMethod.parseDateToString(tbFixEntrust.getEstimateDate(), "yyyy-MM-dd HH:mm"));

        Double fixCount = tbFixEntrustContentService.countTbFixEnTrustContentByTbFixEntrustId(tbFixEntrustId);

        row = sheet.getRow(75);

        cell = row.getCell(30);

        cell.setCellValue(new BigDecimal(fixCount).divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP)
                .toString());

        BigDecimal partPriceAll = new BigDecimal("0.00");

        if (partAllSize > 0) {

            for (int i = 0; i < partAllSize; i++) {

                TbMaintianVo tbMaintianVo = partAll.get(i);

                BigDecimal total = new BigDecimal(tbMaintianVo.getPrice())
                        .multiply(new BigDecimal(tbMaintianVo.getPartQuantity()));

                partPriceAll = partPriceAll.add(total);

            }

        }

        row = sheet.getRow(77);

        cell = row.getCell(30);

        cell.setCellValue(partPriceAll.divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

        BigDecimal total = new BigDecimal(fixCount).add(partPriceAll).divide(new BigDecimal("1.00"), 2,
                BigDecimal.ROUND_HALF_UP);

        row = sheet.getRow(81);

        cell = row.getCell(30);

        cell.setCellValue(total.toString());

        row = sheet.getRow(79);

        cell = row.getCell(30);

        cell.setCellValue("0.00");

        row = sheet.getRow(89);

        cell = row.getCell(6);

        cell.setCellValue("1");

        row = sheet.getRow(89);

        cell = row.getCell(1);

        cell.setCellValue(pz + "");

        int page = 1;

        if (fixSize / 8 >= (maintainSize + solePartSize) / 12) {

            page = fixSize / 8;

        }

        else {

            page = (maintainSize + solePartSize) / 12;

        }

        for (int i = 0; i < page; i++) {

            int p = 0;

            int k = 0;

            HSSFSheet sheetClone = workbook.cloneSheet(0);

            row = sheetClone.getRow(89);

            cell = row.getCell(1);

            cell.setCellValue((i + 1) + "");

            row = sheetClone.getRow(89);

            cell = row.getCell(6);

            cell.setCellValue(pz + "");

            if (fixSize > 8) {

                int printFixSize = (fixSize > (i + 2) * 8 ? (i + 2) * 8 : fixSize);

                for (int j = 8 * (i + 1); j < printFixSize; j++) {

                    TbFixEntrustContent content = tbFixEntrustContentList.get(j);

                    List<TbFixShare> tbFixShareList = tbFixShareService
                            .findTbFixShareListByTbFixEntrustContentId(content.getId());

                    String fixPersons = "";

                    String workTypes = "";

                    if (null != tbFixShareList && tbFixShareList.size() > 0) {

                        for (TbFixShare tbFixShare : tbFixShareList) {

                            if (null != tbFixShare.getTmUser()) {

                                TmUser tmUser = tmUserService.findById(tbFixShare.getTmUser().getId());

                                fixPersons += (tmUser.getUserRealName() == null
                                        || "".equals(tmUser.getUserRealName()) ? tmUser.getUserName()
                                                : tmUser.getUserRealName())
                                        + " ";

                                workTypes += (tmUser.getTmWorkType() == null ? ""
                                        : tmUser.getTmWorkType().getWorkName()) + " ";
                            }

                        }
                    }

                    row = sheetClone.getRow(23 + p * 2);

                    cell = row.getCell(2);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(content.getStationName());

                    cell = row.getCell(20);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(new BigDecimal(content.getFixHour())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(24);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(new BigDecimal(content.getWorkingHourPrice())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(28);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(new BigDecimal(content.getFixHourAll())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(34);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(content.getProjectType() == null ? "" : content.getProjectType());

                    cell = row.getCell(40);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(workTypes);

                    cell = row.getCell(45);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(fixPersons);

                    p++;

                }

            }

            if (partAllSize > 12) {

                int prinPartSize = (partAllSize > (i + 2) * 12 ? (i + 2) * 12 : partAllSize);

                for (int j = 12 * (i + 1); j < prinPartSize; j++) {

                    TbMaintianVo t = partAll.get(j);

                    row = sheetClone.getRow(41 + k * 2);

                    cell = row.getCell(2);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(t.getPartCode());

                    cell = row.getCell(11);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(t.getPartName());

                    cell = row.getCell(20);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(new BigDecimal(t.getPartQuantity())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(24);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(new BigDecimal(t.getPrice())
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(28);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(new BigDecimal(t.getPartQuantity()).multiply(new BigDecimal(t.getPrice()))
                            .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                    cell = row.getCell(34);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue(t.getProjectType() == null ? "" : t.getProjectType());

                    // 
                    cell = row.getCell(40);

                    // cell.setCellStyle(style10_5);

                    cell.setCellValue("");

                    k++;

                }
            }

        }

        if (fixSize > 0) {

            int printFixSize = (fixSize > 8 ? 8 : fixSize);

            for (int j = 0; j < printFixSize; j++) {

                TbFixEntrustContent content = tbFixEntrustContentList.get(j);

                List<TbFixShare> tbFixShareList = tbFixShareService
                        .findTbFixShareListByTbFixEntrustContentId(content.getId());

                String fixPersons = "";

                String workTypes = "";

                if (null != tbFixShareList && tbFixShareList.size() > 0) {

                    for (TbFixShare tbFixShare : tbFixShareList) {

                        if (null != tbFixShare.getTmUser()) {

                            TmUser tmUser = tmUserService.findById(tbFixShare.getTmUser().getId());

                            fixPersons += (tmUser.getUserRealName() == null
                                    || "".equals(tmUser.getUserRealName()) ? tmUser.getUserName()
                                            : tmUser.getUserRealName())
                                    + " ";

                            workTypes += (tmUser.getTmWorkType() == null ? ""
                                    : tmUser.getTmWorkType().getWorkName()) + " ";
                        }

                    }
                }

                row = sheet.getRow(23 + j * 2);

                cell = row.getCell(2);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(content.getStationName());

                cell = row.getCell(20);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(new BigDecimal(content.getFixHour())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(24);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(new BigDecimal(content.getWorkingHourPrice())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(28);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(new BigDecimal(content.getFixHourAll())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(34);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(content.getProjectType() == null ? "" : content.getProjectType());

                cell = row.getCell(40);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(workTypes);

                cell = row.getCell(45);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(fixPersons);

            }

        }

        if (partAllSize > 0) {

            int printPartSize = (partAllSize > 12 ? 12 : partAllSize);

            for (int j = 0; j < printPartSize; j++) {

                TbMaintianVo t = partAll.get(j);

                row = sheet.getRow(41 + j * 2);

                cell = row.getCell(2);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(t.getPartCode());

                cell = row.getCell(11);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(t.getPartName());

                cell = row.getCell(20);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(new BigDecimal(t.getPartQuantity())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(24);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(new BigDecimal(t.getPrice())
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(28);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(new BigDecimal(t.getPartQuantity()).multiply(new BigDecimal(t.getPrice()))
                        .divide(new BigDecimal("1.00"), 2, BigDecimal.ROUND_HALF_UP).toString());

                cell = row.getCell(34);

                // cell.setCellStyle(style10_5);

                cell.setCellValue(t.getProjectType() == null ? "" : t.getProjectType());

                // 
                cell = row.getCell(40);

                // cell.setCellStyle(style10_5);

                cell.setCellValue("");

            }

        }

        workbook.write(os);

    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.Servlet.SaveServlet.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    empId = request.getParameter("employeeid");
    empName = request.getParameter("employeename");
    teamname = request.getParameter("projectteam");
    tcat = request.getParameter("trainingcategory");
    tname = request.getParameter("nameoftrainer");
    thours = request.getParameter("hoursoftraining");
    ttopic = request.getParameter("topic");
    sdate = request.getParameter("startdate");
    cdate = request.getParameter("completiondate");
    request.setAttribute("uname", empName);
    request.setAttribute("emId", empId);
    request.setAttribute("teName", teamname);

    try {/*from  w w  w  . ja  v a 2 s. co m*/

        FileInputStream file = new FileInputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls"));

        HSSFWorkbook workbook = new HSSFWorkbook(file);

        HSSFSheet sheet = workbook.getSheetAt(0);

        Map<String, Object[]> data = new HashMap<>();
        //data.put("1", new Object[] {"Employee_Name", "Email"});
        data.put("2", new Object[] { empId, empName, teamname, tcat, tname, thours, ttopic, sdate, cdate });

        counter = sheet.getPhysicalNumberOfRows();
        Set<String> keyset = data.keySet();
        int rownum = counter;
        for (String key : keyset) {
            HSSFRow row = sheet.createRow(rownum++);
            Object[] objArr = data.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                if (obj instanceof Date)
                    cell.setCellValue((Date) obj);
                else if (obj instanceof Boolean)
                    cell.setCellValue((Boolean) obj);
                else if (obj instanceof String)
                    cell.setCellValue((String) obj);
                else if (obj instanceof Double)
                    cell.setCellValue((Double) obj);
            }
        }

        file.close();
        FileOutputStream out = new FileOutputStream(new File("C:\\Users\\mc13082\\Desktop\\form\\new.xls"));
        workbook.write(out);
        out.close();

        System.out.println("Excel written successfully..");
        counter++;
        System.out.println(counter);

        RequestDispatcher rd = request.getRequestDispatcher("success.jsp");
        rd.forward(request, response);

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

}

From source file:com.servoy.extensions.plugins.excelxport.Enabler.java

License:Open Source License

/**
 * @clonedesc js_excelExport(IFoundSet, String[])
 * @sampleas js_excelExport(IFoundSet, String[])
 * @param foundSet the foundset on which to export
 * @param dataProviderIds the ids of the dataproviders
 * @param templateXLS the xls template to export in
 * @param sheetName the name of the worksheet 
 * @param outputColumnNames is used to set the column headers independently from the dataprovider names
 * @param startRow row in the foundset at which to start the export
 * @param startColumn column in the foundset at which to start the export
 *//*from  w w  w .j a va2 s.  c o m*/
public byte[] js_excelExport(IFoundSet foundSet, String[] dataProviderIds, byte[] templateXLS, String sheetName,
        String[] outputColumnNames, Number startRow, Number startColumn) throws IOException {
    int _startRow = (startRow == null ? 1 : startRow.intValue());
    int _startColumn = (startColumn == null ? 1 : startColumn.intValue());

    if (foundSet != null && dataProviderIds != null && dataProviderIds.length > 0) {
        ByteArrayOutputStream buffer = new ByteArrayOutputStream();
        HSSFWorkbook wb = ExportSpecifyFilePanel.populateWb(foundSet, dataProviderIds, templateXLS,
                outputColumnNames, sheetName, _startRow - 1, _startColumn - 1);
        wb.write(buffer);
        return buffer.toByteArray();
    }
    return null;
}

From source file:com.sevenorcas.openstyle.app.service.spreadsheet.SpreadSheetServiceImp.java

/**
 * Generate the spreadsheet file.//from   w  ww . j  av a2  s .co m
 * TODO: Do code to make password optional  
 * @param object to be exported to spreadsheet
 * @param output filename
 * @param language
 * @param spreadsheet password (optional)
 * @throws Exception
 */
@SuppressWarnings("rawtypes")
private void generateSpreadSheetFile(final Object exportObject, String outFilename, String language,
        final String password) throws Exception {

    //Get necessary master-data
    final Hashtable<String, Object> mdata = new Hashtable<>();
    mdata.put("Language", languageService.getLanguage(language));

    //Set parameters
    HSSFWorkbook wb = new HSSFWorkbook();

    Object header = null;

    if (exportObject instanceof List) {
        List list = (List) exportObject;
        if (list.size() == 0) {
            throw new Exception("Emtpy List");
        }
        header = list.get(0);
    } else {
        header = exportObject;
    }

    Def def = getDefinition(header, true);
    List<SpreadSheet> sheets = new ArrayList<>();
    Colors cc = new Colors();

    if (exportObject instanceof FreeFormSpreadsSheetI) {
        sheets = ((FreeFormSpreadsSheetI) exportObject).getSpreadSheetList();
    } else if (exportObject instanceof List) {
        sheets.add(exportList((List) exportObject, new SpreadSheet(def.entity, "", "", cc)));
    } else {
        sheets.add(setValues(def, exportObject, new SpreadSheet(def.entity, "", "", cc)));
    }

    exportSpreadSheet(sheets, wb);

    //Write file
    File file = new File(outFilename);
    File dirs = new File(file.getParent());
    dirs.mkdirs();
    FileOutputStream fileOut = new FileOutputStream(outFilename);
    wb.write(fileOut);
    fileOut.close();

}

From source file:com.sfy.controller.SiteManageController.java

@RequestMapping(value = "/exportExcel", method = RequestMethod.POST)
public void exportExcelForPage(HttpServletRequest request, HttpServletResponse response) {
    SiteBaseInfo siteBaseInfo;//  ww w . j  av  a  2 s  .c o m
    OutputStream outputStream = null;
    try {
        response.setContentType("application/msexcel;charset=GBK");
        response.setHeader("Content-Disposition", "attachment;filename="
                .concat(String.valueOf(URLEncoder.encode("JDHMS-EXPORT-SITE.xls", "UTF-8"))));
        response.setHeader("Connection", "close");
        response.setHeader("Content-Type", "application/vnd.ms-excel");
        siteBaseInfo = assumeSiteBaseInfoEntity(request);
        siteBaseInfo.setErp(request.getParameter("loginUser"));
        siteBaseInfo = changeParamForMutilOrg(siteBaseInfo); //
        Result<List<SiteBaseInfo>> result = storeService.querySiteBaseInfoForPageByMultiOrg(siteBaseInfo);
        if (result.isSuccess()) {
            List<SiteBaseInfo> siteBaseInfoList = result.getResult();
            if (siteBaseInfoList.size() > 0) {
                /* ?? */
                List<String> querySyncList = new ArrayList<String>();
                for (SiteBaseInfo siteBaseInfoQuery : siteBaseInfoList) {
                    querySyncList.add(String.valueOf(siteBaseInfoQuery.getSiteNo()));
                }
                Map<String, List<SyncSiteInfoMq>> compositeMap = storeService
                        .queryBunchSyncSiteInfo(querySyncList);
                /* EXCEL */
                HSSFWorkbook workbook = new HSSFWorkbook();
                List<String> listCol = new ArrayList<String>();
                listCol.add("");
                listCol.add("??");
                listCol.add("??");
                listCol.add("?");
                listCol.add("");
                listCol.add("?");
                listCol.add("");
                listCol.add("");
                listCol.add("");
                listCol.add("?");
                listCol.add("??");
                listCol.add("?");
                listCol.add("");
                listCol.add("");
                listCol.add("?");
                listCol.add("");
                listCol.add("?");
                listCol.add("");
                listCol.add("");
                listCol.add("??");
                listCol.add("??");
                listCol.add("?");
                HSSFSheet sheet = workbook.createSheet();
                HSSFRow headRow = sheet.createRow(0);
                for (int j = 0; j < listCol.size(); j++) {
                    HSSFCell cell = headRow.createCell(j);
                    cell.setCellValue(listCol.get(j));
                }
                for (int i = 1; i <= siteBaseInfoList.size(); i++) {
                    HSSFRow row = sheet.createRow(i);
                    for (int j = 0; j < listCol.size(); j++) {
                        HSSFCell cell = row.createCell(j);
                        if (j == 0) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getOrgName());
                        } else if (j == 1) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getDistributeName());
                        } else if (j == 2) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteName());
                        } else if (j == 3) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getBussinessSiteNo());
                        } else if (j == 4) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteType());
                        } else if (j == 5) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getProvinceName());
                        } else if (j == 6) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getCityName());
                        } else if (j == 7) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getCountryName());
                        } else if (j == 8) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getTownName());
                        } else if (j == 9) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getAddress());
                        } else if (j == 10) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getGpsLongitude());
                        } else if (j == 11) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getGpsLatitude());
                        } else if (j == 12) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getManagerName());
                        } else if (j == 13) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteManager());
                        } else if (j == 14) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getJdAccount());
                        } else if (j == 15) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteLevel());
                        } else if (j == 16) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getSiteStatus());
                        } else if (j == 17) {
                            Date openTime = siteBaseInfoList.get(i - 1).getOpenTime();
                            if (openTime != null) {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String formater = sdf.format(openTime);
                                cell.setCellValue(formater);
                            } else {
                                cell.setCellValue("");
                            }
                        } else if (j == 18) {
                            Date createTime = siteBaseInfoList.get(i - 1).getCreateTime();
                            if (createTime != null) {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                String formater = sdf.format(createTime);
                                cell.setCellValue(formater);
                            } else {
                                cell.setCellValue("");
                            }
                        } else if (j == 19) {
                            cell.setCellValue(siteBaseInfoList.get(i - 1).getTelephone());
                        } else if (j == 20) {
                            if (compositeMap
                                    .containsKey(String.valueOf(siteBaseInfoList.get(i - 1).getSiteNo()))) {
                                List<SyncSiteInfoMq> syncSiteInfoMqList = compositeMap
                                        .get(String.valueOf(siteBaseInfoList.get(i - 1).getSiteNo()));
                                StringBuilder sb = new StringBuilder();
                                for (int ii = 0; ii < syncSiteInfoMqList.size(); ii++) {
                                    int systemSource = syncSiteInfoMqList.get(ii).getSystemSource();
                                    if (systemSource == 0) {
                                        sb.append(syncSiteInfoMqList.get(ii).getWebsiteNo());
                                        sb.append(",");
                                    }
                                }
                                String[] splitArray = sb.toString().split(",");
                                String str = "";
                                for (int jj = 0; jj < splitArray.length; jj++) {
                                    str = str + splitArray[jj];
                                    if (jj + 1 != splitArray.length) {
                                        str = str + ",";
                                    }
                                }
                                cell.setCellValue(str);
                            }
                        } else if (j == 21) {
                            if (compositeMap
                                    .containsKey(String.valueOf(siteBaseInfoList.get(i - 1).getSiteNo()))) {
                                List<SyncSiteInfoMq> syncSiteInfoMqList = compositeMap
                                        .get(String.valueOf(siteBaseInfoList.get(i - 1).getSiteNo()));
                                StringBuilder sb = new StringBuilder();
                                for (int ii = 0; ii < syncSiteInfoMqList.size(); ii++) {
                                    int systemSource = syncSiteInfoMqList.get(ii).getSystemSource();
                                    if (systemSource == 1) {
                                        sb.append(syncSiteInfoMqList.get(ii).getWebsiteNo());
                                        sb.append(",");
                                    }
                                }
                                String[] splitArray = sb.toString().split(",");
                                String str = "";
                                for (int jj = 0; jj < splitArray.length; jj++) {
                                    str = str + splitArray[jj];
                                    if (jj + 1 != splitArray.length) {
                                        str = str + ",";
                                    }
                                }
                                cell.setCellValue(str);
                            }
                        }
                    }
                }
                outputStream = response.getOutputStream();
                workbook.write(outputStream);
                /* EXCEL? */
            }
        }
    } catch (Exception e) {
        logger.error(e.toString());
    } finally {
        try {
            if (outputStream != null) {
                outputStream.flush();
                outputStream.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

From source file:com.siva.javamultithreading.MultiThreadExecutor.java

protected static void writeFile(HSSFWorkbook book, FileOutputStream out) throws Exception {
    // FileOutputStream out = new FileOutputStream(file);
    book.write(out);
    out.close();/*from  w  w  w .  j a v a  2 s .c o  m*/
}

From source file:com.smi.travel.datalayer.view.dao.impl.APNirvanaImpl.java

private String genReport(List<APNirvana> apDataList, String fullFileName, List<APNirvana> APList) {
    SimpleDateFormat df = new SimpleDateFormat();
    df.applyPattern("dd/MM/yyyy");
    String status = "";
    UtilityFunction util = new UtilityFunction();
    try {//from w  w w  .  ja va2 s.c o  m
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        int rownum = 0;
        for (APNirvana ap : apDataList) {
            HSSFRow dataRow = sheet.createRow(rownum++);
            int cellnum = 0;
            HSSFCell cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getRefinvoiceno());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getIntreference());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVendorid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVendorname());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getDivisionid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getProjectid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getTranscode());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getTransdate() == null ? ""
                    : util.ConvertString(
                            df.format(util.convertStringToDate(String.valueOf(ap.getTransdate())))));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getDuedate() == null ? ""
                    : util.ConvertString(df.format(util.convertStringToDate(String.valueOf(ap.getDuedate())))));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getCurrencyid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getHomerate()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getForeignrate()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getBasevatamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getBasevathmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getVatamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getVathmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getTransamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getTranshmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVatflag());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVatid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getWhtflag());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getWhtid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getBasewhtamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getBasewhthmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getWhtamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getWhthmamt()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getYear() == null ? "" : ap.getYear().toString());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPeriod() == null ? "" : ap.getPeriod().toString());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getNote());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount1());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision1());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject1());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt1()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt1()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount2());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision2());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject2());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt2()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt2()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount3());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision3());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject3());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt3()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt3()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount4());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision4());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject4());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt4()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt4()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount5());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision5());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject5());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt5()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt5()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount6());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision6());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject6());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt6()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt6()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount7());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision7());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject7());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt7()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt7()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount8());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision8());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject8());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt8()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt8()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount9());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision9());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject9());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt9()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt9()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPuraccount10());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurdivision10());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPurproject10());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPuramt10()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(UtilityFunction.getObjectString(ap.getPurhmamt10()));
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getService());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getApaccount());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getPrefix());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVoucherno());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getTaxid());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getVendor_branch() == null ? "0" : ap.getVendor_branch().toString());
            cell = dataRow.createCell(cellnum++);
            cell.setCellValue(ap.getCompany_branch());
            //                for(int j =0;j<100;j++){
            //                    sheet.autoSizeColumn(j);
            //                }
        }

        FileOutputStream out = new FileOutputStream(new File(fullFileName + ".xls"));
        workbook.write(out);
        out.close();
        status = "success";
    } catch (Exception e) {
        e.printStackTrace();
        for (APNirvana ap : APList) {
            if (!"".equals(status)) {
                status += ", ";
            }
            status += ap.getPayment_detail_id();
        }
    }
    return status;
}