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

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

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

Introduction

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

Prototype

@Override
public HSSFCellStyle createCellStyle()

Source Link

Document

Create a new Cell style and add it to the workbook's style table.

Usage

From source file:com.esd.cs.common.PoiCreateExcel.java

License:Open Source License

/**
 * //  w ww . j  a va2s  .co  m
 * 
 * @param FilePath
 * @param companyList
 * @return
 */
public static boolean createRepeaExcel(String FilePath, List<ReportViewModel> companyList, ReportModel model) {
    // Excel Workbook,excel
    HSSFWorkbook wb = new HSSFWorkbook();
    // Excelsheet,exceltab
    HSSFSheet sheet = wb.createSheet("sheet1");
    // excel?
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 3500);

    // Excel?
    HSSFRow headRow0 = sheet.createRow(0);
    HSSFCell headCell = headRow0.createCell(0);
    // ??
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));// ???
    headCell = headRow0.createCell(0);
    // 
    headCell.setCellValue(model.getTitle());
    // ?
    HSSFCellStyle style = wb.createCellStyle();
    style.setFillBackgroundColor(HSSFColor.GREEN.index);
    style.setAlignment(CellStyle.ALIGN_CENTER);// 
    style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 
    // 
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 
    style.setFont(font);
    headCell.setCellStyle(style);

    // ? ??
    HSSFRow RowTow = sheet.createRow(1);
    HSSFCell CellTow = headRow0.createCell(1);
    // ??
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));// ???
    CellTow = RowTow.createCell(0);
    // 
    CellTow.setCellValue(model.getCreateCompany());

    // ? 
    // ??
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 13));// ???
    CellTow = RowTow.createCell(6);
    HSSFCellStyle style1 = wb.createCellStyle();
    style1.setFillBackgroundColor(HSSFColor.GREEN.index);
    style1.setAlignment(CellStyle.ALIGN_RIGHT);// ?
    CellTow.setCellStyle(style1);
    // 
    CellTow.setCellValue(model.getCreateData());

    // ?
    HSSFRow headRow = sheet.createRow(2);
    HSSFCell headell = headRow.createCell(2);
    // ???
    headell = headRow.createCell(0);
    headell.setCellValue(model.getType());

    headell = headRow.createCell(1);
    headell.setCellValue("??");

    headell = headRow.createCell(2);
    headell.setCellValue("??");
    sheet.setColumnWidth(2, 3000); // 

    headell = headRow.createCell(3);
    headell.setCellValue("???");
    sheet.setColumnWidth(3, 3000); // 

    headell = headRow.createCell(4);
    headell.setCellValue("???");
    sheet.setColumnWidth(4, 4000); // 

    headell = headRow.createCell(5);
    headell.setCellValue("???");
    sheet.setColumnWidth(5, 4000); // 

    headell = headRow.createCell(6);
    headell.setCellValue("???");
    sheet.setColumnWidth(6, 4500); // 

    headell = headRow.createCell(7);
    headell.setCellValue("");
    sheet.setColumnWidth(8, 4000);

    headell = headRow.createCell(8);
    headell.setCellValue("");
    sheet.setColumnWidth(8, 4000);

    headell = headRow.createCell(9);
    headell.setCellValue("");
    sheet.setColumnWidth(9, 4000);

    headell = headRow.createCell(10);
    headell.setCellValue("?");
    sheet.setColumnWidth(10, 4000);

    headell = headRow.createCell(11);
    headell.setCellValue("???");

    headell = headRow.createCell(12);
    headell.setCellValue("?");

    headell = headRow.createCell(13);
    headell.setCellValue("?");

    for (int i = 0; i < companyList.size(); i++) {
        ReportViewModel company = companyList.get(i);
        // Excel?
        HSSFRow row = sheet.createRow(i + 3);
        HSSFCell cell = row.createCell(i + 3);
        // ???
        // ????
        cell = row.createCell(0);
        cell.setCellValue(company.getReportName());
        // ??
        cell = row.createCell(1);
        cell.setCellValue(company.getUnitNum());
        // ??
        cell = row.createCell(2);
        cell.setCellValue(company.getEmpTotal());

        // ???
        cell = row.createCell(3);
        cell.setCellValue(company.getUnAudit());

        // ?, ???
        cell = row.createCell(4);
        cell.setCellValue(company.getUnReAudit());

        // ?, ??
        cell = row.createCell(5);
        cell.setCellValue(company.getAuditOk());

        // ?, ??
        cell = row.createCell(6);
        cell.setCellValue(company.getUnauditOk());

        // 
        cell = row.createCell(7);
        cell.setCellValue(company.getShouldTotal().toString());

        // ?
        cell = row.createCell(8);
        cell.setCellValue(company.getAlreadyTotal().toString());

        // 
        cell = row.createCell(9);
        cell.setCellValue(company.getLessTotal().toString());
        // ?
        cell = row.createCell(10);
        cell.setCellValue(company.getAmountPayable().toString());
        // ???
        cell = row.createCell(11);
        cell.setCellValue(company.getReductionAmount().toString());
        // ?
        cell = row.createCell(12);
        cell.setCellValue(company.getActualAmount().toString());
        // ?
        cell = row.createCell(13);
        cell.setCellValue(company.getAlreadyAmount().toString());
    }

    // ? 
    HSSFRow row = sheet.createRow(companyList.size() + 3);
    HSSFCell cell = row.createCell(companyList.size() + 3);
    // ???
    // ????
    sheet.addMergedRegion(new CellRangeAddress(companyList.size() + 3, companyList.size() + 3, 0, 13));// ???
    cell = row.createCell(0);
    // ?
    HSSFCellStyle styleFoot = wb.createCellStyle();
    styleFoot.setAlignment(CellStyle.ALIGN_RIGHT);// ?
    cell.setCellStyle(styleFoot);
    // 
    cell.setCellValue(model.getCreatePeople());

    try {
        FileOutputStream os = new FileOutputStream(FilePath);
        wb.write(os);
        os.flush();
        os.close();
        companyList.clear();
        companyList = null;
        os = null;
        wb = null;
        System.gc();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return true;
}

From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java

License:Open Source License

private static CellStyle getDateTimeCellStyle(HSSFWorkbook wb) {//
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    return cellStyle;
}

From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java

License:Open Source License

@SuppressWarnings("unused")
private static CellStyle getStringCellStyle(HSSFWorkbook wb) {//
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@"));
    return cellStyle;
}

From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java

License:Open Source License

private static HSSFCellStyle getHeadCellStyle(HSSFWorkbook wb, HSSFFont font) {//
    HSSFCellStyle headCellStyle = wb.createCellStyle();
    headCellStyle.setFont(font);/*  w  ww. j  a  v a2 s .c  om*/
    headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headCellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    headCellStyle.setWrapText(false);
    headCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    return headCellStyle;
}

From source file:com.fufang.bi.controllers.ChainReportController.java

/***
 * ?excel//  w  ww. j a va 2s  .  co m
 * @param orderType
 * @param list
 * @param inputStream
 * @return
 */
public HSSFWorkbook createUploadSplitExcel(List<?> list, InputStream inputStream, Integer select, Object obj) {
    try {
        HSSFWorkbook workbook = null;
        workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = null;//
        sheet = workbook.getSheetAt(0);//

        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setLocked(false);
        HSSFRow row = null;
        HSSFCell cell = null;
        if (list != null && list.size() > 0) {
            for (int i = 0, size = list.size(); i < size; i++) {
                HSSFRow rows = null;
                rows = sheet.createRow(i + 5);
                if (select == 3) {
                    row = sheet.createRow(i + 3);
                } else {
                    row = sheet.createRow(i + 2);
                }

                if (select == 1) {
                    StorageTotal data = (StorageTotal) list.get(i);
                    Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle);
                    Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle);
                    Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle);
                    Converter.setCellText(row, cell, 3, Converter.toBlank(data.getQckc()), cellStyle);
                    Converter.setCellText(row, cell, 4, Converter.toBlank(data.getQckcje()), cellStyle);
                    Converter.setCellText(row, cell, 5, Converter.toBlank(data.getQjrk()), cellStyle);
                    Converter.setCellText(row, cell, 6, Converter.toBlank(data.getQjrkje()), cellStyle);
                    Converter.setCellText(row, cell, 7, Converter.toBlank(data.getQjck()), cellStyle);
                    Converter.setCellText(row, cell, 8, Converter.toBlank(data.getQjckje()), cellStyle);
                    Converter.setCellText(row, cell, 9, Converter.toBlank(data.getQmkc()), cellStyle);
                    Converter.setCellText(row, cell, 10, Converter.toBlank(data.getQmkcje()), cellStyle);
                }
                if (select == 2) {
                    StorageMilde data = (StorageMilde) list.get(i);
                    Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle);
                    Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle);
                    Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle);
                    Converter.setCellText(row, cell, 3, Converter.toBlank(data.getMatcode()), cellStyle);
                    Converter.setCellText(row, cell, 4, Converter.toBlank(data.getBarcode()), cellStyle);
                    Converter.setCellText(row, cell, 5, Converter.toBlank(data.getMatname()), cellStyle);
                    Converter.setCellText(row, cell, 6, Converter.toBlank(data.getCommonname()), cellStyle);
                    Converter.setCellText(row, cell, 7, Converter.toBlank(data.getDosage()), cellStyle);
                    Converter.setCellText(row, cell, 8, Converter.toBlank(data.getSpec()), cellStyle);
                    Converter.setCellText(row, cell, 9, Converter.toBlank(data.getUnitname()), cellStyle);
                    Converter.setCellText(row, cell, 10, Converter.toBlank(data.getManufname()), cellStyle);
                    Converter.setCellText(row, cell, 11, Converter.toBlank(data.getProductarea()), cellStyle);
                    Converter.setCellText(row, cell, 12, Converter.toBlank(data.getLicensenum()), cellStyle);
                    Converter.setCellText(row, cell, 13, Converter.toBlank(data.getRetail()), cellStyle);

                    Converter.setCellText(row, cell, 14, Converter.toBlank(data.getQckc()), cellStyle);
                    Converter.setCellText(row, cell, 15, Converter.toBlank(data.getQckcje()), cellStyle);
                    Converter.setCellText(row, cell, 16, Converter.toBlank(data.getQjrk()), cellStyle);
                    Converter.setCellText(row, cell, 17, Converter.toBlank(data.getQjrkje()), cellStyle);
                    Converter.setCellText(row, cell, 18, Converter.toBlank(data.getQjck()), cellStyle);
                    Converter.setCellText(row, cell, 19, Converter.toBlank(data.getQjckje()), cellStyle);
                    Converter.setCellText(row, cell, 20, Converter.toBlank(data.getQmkc()), cellStyle);
                    Converter.setCellText(row, cell, 21, Converter.toBlank(data.getQmkcje()), cellStyle);

                }
                if (select == 3) {
                    StorageDetail data = (StorageDetail) list.get(i);
                    Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle);
                    Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle);
                    Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle);
                    Converter.setCellText(row, cell, 3, Converter.toBlank(data.getMatcode()), cellStyle);
                    Converter.setCellText(row, cell, 4, Converter.toBlank(data.getBarcode()), cellStyle);
                    Converter.setCellText(row, cell, 5, Converter.toBlank(data.getMatname()), cellStyle);
                    Converter.setCellText(row, cell, 6, Converter.toBlank(data.getCommonname()), cellStyle);
                    Converter.setCellText(row, cell, 7, Converter.toBlank(data.getDosage()), cellStyle);
                    Converter.setCellText(row, cell, 8, Converter.toBlank(data.getSpec()), cellStyle);
                    Converter.setCellText(row, cell, 9, Converter.toBlank(data.getUnitname()), cellStyle);
                    Converter.setCellText(row, cell, 10, Converter.toBlank(data.getManufname()), cellStyle);
                    Converter.setCellText(row, cell, 11, Converter.toBlank(data.getProductarea()), cellStyle);
                    Converter.setCellText(row, cell, 12, Converter.toBlank(data.getLicensenum()), cellStyle);
                    Converter.setCellText(row, cell, 13, Converter.toBlank(data.getRetail()), cellStyle);

                    Converter.setCellText(row, cell, 14, Converter.toBlank(data.getQckc()), cellStyle);
                    Converter.setCellText(row, cell, 15, Converter.toBlank(data.getQckcje()), cellStyle);
                    Converter.setCellText(row, cell, 16, Converter.toBlank(data.getCg()), cellStyle);
                    Converter.setCellText(row, cell, 17, Converter.toBlank(data.getCgje()), cellStyle);
                    Converter.setCellText(row, cell, 18, Converter.toBlank(data.getPyrk()), cellStyle);
                    Converter.setCellText(row, cell, 19, Converter.toBlank(data.getPyrkje()), cellStyle);
                    Converter.setCellText(row, cell, 20, Converter.toBlank(data.getQtrk()), cellStyle);
                    Converter.setCellText(row, cell, 21, Converter.toBlank(data.getQtrkje()), cellStyle);
                    Converter.setCellText(row, cell, 22, Converter.toBlank(data.getClrk()), cellStyle);
                    Converter.setCellText(row, cell, 23, Converter.toBlank(data.getClrkje()), cellStyle);
                    Converter.setCellText(row, cell, 24, Converter.toBlank(data.getPsrk()), cellStyle);
                    Converter.setCellText(row, cell, 25, Converter.toBlank(data.getPsrkje()), cellStyle);
                    Converter.setCellText(row, cell, 26, Converter.toBlank(data.getQcrk()), cellStyle);
                    Converter.setCellText(row, cell, 27, Converter.toBlank(data.getQcrkje()), cellStyle);

                    Converter.setCellText(row, cell, 28, Converter.toBlank(data.getXsck()), cellStyle);
                    Converter.setCellText(row, cell, 29, Converter.toBlank(data.getXsckje()), cellStyle);
                    Converter.setCellText(row, cell, 30, Converter.toBlank(data.getPkck()), cellStyle);
                    Converter.setCellText(row, cell, 31, Converter.toBlank(data.getPkckje()), cellStyle);
                    Converter.setCellText(row, cell, 32, Converter.toBlank(data.getQtck()), cellStyle);
                    Converter.setCellText(row, cell, 33, Converter.toBlank(data.getQtckje()), cellStyle);
                    Converter.setCellText(row, cell, 34, Converter.toBlank(data.getClck()), cellStyle);
                    Converter.setCellText(row, cell, 35, Converter.toBlank(data.getClckje()), cellStyle);
                    Converter.setCellText(row, cell, 36, Converter.toBlank(data.getPsck()), cellStyle);
                    Converter.setCellText(row, cell, 37, Converter.toBlank(data.getPsckje()), cellStyle);
                    Converter.setCellText(row, cell, 38, Converter.toBlank(data.getQdck()), cellStyle);
                    Converter.setCellText(row, cell, 39, Converter.toBlank(data.getQdckje()), cellStyle);
                    Converter.setCellText(row, cell, 40, Converter.toBlank(data.getXhck()), cellStyle);
                    Converter.setCellText(row, cell, 41, Converter.toBlank(data.getXhckje()), cellStyle);

                    Converter.setCellText(row, cell, 42, Converter.toBlank(data.getQmkc()), cellStyle);
                    Converter.setCellText(row, cell, 43, Converter.toBlank(data.getQmkcje()), cellStyle);
                }

                if (i == size - 1) {
                    if (select == 1) {
                        StorageTotal sum = (StorageTotal) obj;

                        Converter.setCellText(rows, cell, 0, "? ", cellStyle);
                        Converter.setCellText(rows, cell, 3, Converter.toBlank(sum.getQckc()), cellStyle);
                        Converter.setCellText(rows, cell, 4, Converter.toBlank(sum.getQckcje()), cellStyle);
                        Converter.setCellText(rows, cell, 5, Converter.toBlank(sum.getQjrk()), cellStyle);
                        Converter.setCellText(rows, cell, 6, Converter.toBlank(sum.getQjrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 7, Converter.toBlank(sum.getQjck()), cellStyle);
                        Converter.setCellText(rows, cell, 8, Converter.toBlank(sum.getQjckje()), cellStyle);
                        Converter.setCellText(rows, cell, 9, Converter.toBlank(sum.getQmkc()), cellStyle);
                        Converter.setCellText(rows, cell, 10, Converter.toBlank(sum.getQmkcje()), cellStyle);
                    }
                    if (select == 2) {
                        StorageMilde sum = (StorageMilde) obj;
                        Converter.setCellText(rows, cell, 0, Converter.toBlank("? "), cellStyle);
                        Converter.setCellText(rows, cell, 14, Converter.toBlank(sum.getQckc()), cellStyle);
                        Converter.setCellText(rows, cell, 15, Converter.toBlank(sum.getQckcje()), cellStyle);
                        Converter.setCellText(rows, cell, 16, Converter.toBlank(sum.getQjrk()), cellStyle);
                        Converter.setCellText(rows, cell, 17, Converter.toBlank(sum.getQjrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 18, Converter.toBlank(sum.getQjck()), cellStyle);
                        Converter.setCellText(rows, cell, 19, Converter.toBlank(sum.getQjckje()), cellStyle);
                        Converter.setCellText(rows, cell, 20, Converter.toBlank(sum.getQmkc()), cellStyle);
                        Converter.setCellText(rows, cell, 21, Converter.toBlank(sum.getQmkcje()), cellStyle);
                    }
                    if (select == 3) {
                        StorageDetail sum = (StorageDetail) obj;
                        Converter.setCellText(rows, cell, 0, Converter.toBlank("? "), cellStyle);
                        //                     Converter.setCellText(rows, cell, 1,Converter.toBlank(sum.getPharmacycode()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 2,Converter.toBlank(sum.getName()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 3,Converter.toBlank(sum.getMatcode()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 4,Converter.toBlank(sum.getBarcode()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 5,Converter.toBlank(sum.getMatname()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 6,Converter.toBlank(sum.getCommonname()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 7,Converter.toBlank(sum.getDosage()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 8,Converter.toBlank(sum.getSpec()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 9,Converter.toBlank(sum.getUnitname()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 10,Converter.toBlank(sum.getManufname()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 11,Converter.toBlank(sum.getProductarea()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 12,Converter.toBlank(sum.getLicensenum()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 13,Converter.toBlank(sum.getRetail()), cellStyle);

                        Converter.setCellText(rows, cell, 14, Converter.toBlank(sum.getQckc()), cellStyle);
                        Converter.setCellText(rows, cell, 15, Converter.toBlank(sum.getQckcje()), cellStyle);
                        Converter.setCellText(rows, cell, 16, Converter.toBlank(sum.getCg()), cellStyle);
                        Converter.setCellText(rows, cell, 17, Converter.toBlank(sum.getCgje()), cellStyle);
                        Converter.setCellText(rows, cell, 18, Converter.toBlank(sum.getPyrk()), cellStyle);
                        Converter.setCellText(rows, cell, 19, Converter.toBlank(sum.getPyrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 20, Converter.toBlank(sum.getQtrk()), cellStyle);
                        Converter.setCellText(rows, cell, 21, Converter.toBlank(sum.getQtrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 22, Converter.toBlank(sum.getClrk()), cellStyle);
                        Converter.setCellText(rows, cell, 23, Converter.toBlank(sum.getClrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 24, Converter.toBlank(sum.getPsrk()), cellStyle);
                        Converter.setCellText(rows, cell, 25, Converter.toBlank(sum.getPsrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 26, Converter.toBlank(sum.getQcrk()), cellStyle);
                        Converter.setCellText(rows, cell, 27, Converter.toBlank(sum.getQcrkje()), cellStyle);

                        Converter.setCellText(rows, cell, 28, Converter.toBlank(sum.getXsck()), cellStyle);
                        Converter.setCellText(rows, cell, 29, Converter.toBlank(sum.getXsckje()), cellStyle);
                        Converter.setCellText(rows, cell, 30, Converter.toBlank(sum.getPkck()), cellStyle);
                        Converter.setCellText(rows, cell, 31, Converter.toBlank(sum.getPkckje()), cellStyle);
                        Converter.setCellText(rows, cell, 32, Converter.toBlank(sum.getQtck()), cellStyle);
                        Converter.setCellText(rows, cell, 33, Converter.toBlank(sum.getQtckje()), cellStyle);
                        Converter.setCellText(rows, cell, 34, Converter.toBlank(sum.getClck()), cellStyle);
                        Converter.setCellText(rows, cell, 35, Converter.toBlank(sum.getClckje()), cellStyle);
                        Converter.setCellText(rows, cell, 36, Converter.toBlank(sum.getPsck()), cellStyle);
                        Converter.setCellText(rows, cell, 37, Converter.toBlank(sum.getPsckje()), cellStyle);
                        Converter.setCellText(rows, cell, 38, Converter.toBlank(sum.getQdck()), cellStyle);
                        Converter.setCellText(rows, cell, 39, Converter.toBlank(sum.getQdckje()), cellStyle);
                        Converter.setCellText(rows, cell, 40, Converter.toBlank(sum.getXhck()), cellStyle);
                        Converter.setCellText(rows, cell, 41, Converter.toBlank(sum.getXhckje()), cellStyle);

                        Converter.setCellText(rows, cell, 42, Converter.toBlank(sum.getQmkc()), cellStyle);
                        Converter.setCellText(rows, cell, 43, Converter.toBlank(sum.getQmkcje()), cellStyle);
                    }

                }
            }
        }
        return workbook;
    } catch (Exception e) {

        e.printStackTrace();
        return null;
    }

}

From source file:com.github.gaborfeher.grantmaster.framework.base.ExcelExporter.java

License:Open Source License

private void setExcelCell(HSSFWorkbook workbook, Object cellValue, Cell excelCell) {
    if (cellValue instanceof BigDecimal) {
        double doubleValue = ((BigDecimal) cellValue).doubleValue();
        excelCell.setCellValue(doubleValue);
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFDataFormat hssfDataFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.00"));
        excelCell.setCellStyle(cellStyle);
        excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (cellValue instanceof LocalDate) {
        LocalDate localDate = (LocalDate) cellValue;
        Calendar calendar = Calendar.getInstance();
        calendar.set(localDate.getYear(), localDate.getMonthValue() - 1, localDate.getDayOfMonth());
        excelCell.setCellValue(calendar);

        String excelFormatPattern = DateFormatConverter.convert(Locale.US, "yyyy-MM-DD");
        CellStyle cellStyle = workbook.createCellStyle();
        DataFormat poiFormat = workbook.createDataFormat();
        cellStyle.setDataFormat(poiFormat.getFormat(excelFormatPattern));
        excelCell.setCellStyle(cellStyle);
    } else if (cellValue != null) {
        excelCell.setCellValue(cellValue.toString());
    }//from  ww w .jav a  2  s .co m
}

From source file:com.github.gujou.deerbelling.sonarqube.service.XlsTasksGenerator.java

License:Open Source License

public static File generateFile(Project sonarProject, FileSystem sonarFileSystem, String sonarUrl,
        String sonarLogin, String sonarPassword) {

    short formatIndex;
    HSSFDataFormat dataFormat = null;// ww w.j  av a 2  s  . co m
    FileOutputStream out = null;
    HSSFWorkbook workbook = null;

    String filePath = sonarFileSystem.workDir().getAbsolutePath() + File.separator + "tasks_report_"
            + sonarProject.getEffectiveKey().replace(':', '-') + "."
            + ReportsKeys.TASKS_REPORT_TYPE_XLS_EXTENSION;

    File resultFile = new File(filePath);

    try {
        out = new FileOutputStream(resultFile);

        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Tasks list");

        // Date format.
        dataFormat = workbook.createDataFormat();
        formatIndex = dataFormat.getFormat("yyyy-MM-ddTHH:mm:ss");
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(formatIndex);

        Issues rootIssue = IssueGateway.getOpenIssues(sonarProject.getEffectiveKey(), sonarUrl, sonarLogin,
                sonarPassword);

        if (rootIssue == null) {
            return null;
        }

        DataValidationHelper validationHelper = new HSSFDataValidationHelper(sheet);
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(
                new String[] { "OPENED", "CONFIRMED", "REOPENED", "RESOLVED", "CLOSE" });
        CellRangeAddressList addressList = new CellRangeAddressList(1, rootIssue.getIssues().size() + 1,
                STATUS_COLUMN_INDEX, STATUS_COLUMN_INDEX);
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setSuppressDropDownArrow(false);
        sheet.addValidationData(dataValidation);

        int rownum = 0;

        Row row = sheet.createRow(rownum++);
        row.createCell(STATUS_COLUMN_INDEX).setCellValue("Status");
        row.createCell(SEVERITY_COLUMN_INDEX).setCellValue("Severity");
        row.createCell(COMPONENT_COLUMN_INDEX).setCellValue("Component");
        row.createCell(LINE_COLUMN_INDEX).setCellValue("Line");
        row.createCell(MESSAGE_COLUMN_INDEX).setCellValue("Message");
        row.createCell(AUTHOR_COLUMN_INDEX).setCellValue("Author");
        row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue("Assigned");
        row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue("CreationDate");
        row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue("UpdateDate");
        row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue("Path");

        for (Issue issue : rootIssue.getIssues()) {
            if (issue != null) {
                row = sheet.createRow(rownum++);
                int componentIndex = 0;
                if (issue.getComponent() != null) {
                    componentIndex = issue.getComponent().lastIndexOf('/');
                }
                String component;
                String path;
                if (componentIndex > 0) {
                    component = issue.getComponent().substring(componentIndex + 1);
                    path = issue.getComponent().substring(0, componentIndex);
                } else {
                    component = issue.getComponent();
                    path = "";
                }

                // Set values.
                row.createCell(STATUS_COLUMN_INDEX).setCellValue(issue.getStatus());
                row.createCell(SEVERITY_COLUMN_INDEX).setCellValue(issue.getSeverity());
                row.createCell(COMPONENT_COLUMN_INDEX).setCellValue(component);
                row.createCell(LINE_COLUMN_INDEX).setCellValue(issue.getLine());
                row.createCell(MESSAGE_COLUMN_INDEX).setCellValue(issue.getMessage());
                row.createCell(AUTHOR_COLUMN_INDEX).setCellValue(issue.getAuthor());
                row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue(issue.getAssignee());
                row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue(issue.getCreationDate());
                row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue(issue.getUpdateDate());
                row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue(path);

                // Set date style to date column.
                row.getCell(CREATION_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
                row.getCell(UPDATE_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
            }
        }

        // Auto-size sheet columns.
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_COLUMN_INDEX);
        sheet.autoSizeColumn(LINE_COLUMN_INDEX);
        sheet.autoSizeColumn(MESSAGE_COLUMN_INDEX);
        sheet.autoSizeColumn(AUTHOR_COLUMN_INDEX);
        sheet.autoSizeColumn(ASSIGNED_COLUMN_INDEX);
        sheet.autoSizeColumn(CREATION_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(UPDATE_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_PATH_COLUMN_INDEX);

        workbook.write(out);

    } catch (FileNotFoundException e) {

        // TODO manage error.
        e.printStackTrace();
    } catch (IOException e) {

        // TODO manage error.
        e.printStackTrace();
    } finally {
        IOUtils.closeQuietly(workbook);
        IOUtils.closeQuietly(out);
    }

    return resultFile;
}

From source file:com.haulmont.mp2xls.writer.LocalizationBatchExcelWriter.java

License:Apache License

public static void exportToXls(LocalizationsBatch localizations, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet worksheet = workbook.createSheet("localizations");

    HSSFCellStyle systemStyle = workbook.createCellStyle();
    systemStyle.setFillForegroundColor(HSSFColor.RED.index);
    systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

    HSSFRow row = worksheet.createRow(0);
    row.createCell(0).setCellValue("Project path");
    row.createCell(1).setCellValue(localizations.getProjectDirectory());

    HSSFRow headLine = worksheet.createRow(5);
    headLine.createCell(0).setCellValue("Path to File");
    headLine.createCell(1).setCellValue("Parameter Name");

    Map<String, Integer> localeColumn = new HashMap<>();
    int colCount = 1;
    for (String localeId : localizations.getLocalizationIds()) {
        if (localizations.getScanLocalizationIds().contains(localeId == null ? "en" : localeId)) {
            String id = localeId == null ? "default" : localeId;
            headLine.createCell(++colCount).setCellValue(id);
            localeColumn.put(localeId, colCount);
        }//from  w ww  . j  a  va2 s.c o  m
    }

    Integer currentRow = headLine.getRowNum();
    for (String folder : localizations.getMessagesLocalizations().keySet()) {
        Set<MessagesLocalization> locales = localizations.getMessagesLocalizations().get(folder);

        Set<String> parameters = new HashSet<>();
        for (MessagesLocalization locale : locales) {
            if (localizations.getScanLocalizationIds()
                    .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId()))
                parameters.addAll(locale.getMessages().keySet());
        }

        for (String parameter : parameters) {
            row = worksheet.createRow(++currentRow);
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(folder);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
                row.setZeroHeight(true);
            }

            cell = row.createCell(1);
            cell.setCellValue(parameter);
            if (MessagesFolderReader.systemKeys.contains(parameter)) {
                cell.setCellStyle(systemStyle);
            }

            for (MessagesLocalization locale : locales) {
                if (localizations.getScanLocalizationIds()
                        .contains(locale.getLocaleId() == null ? "en" : locale.getLocaleId())) {
                    Integer columnNum = localeColumn.get(locale.getLocaleId());
                    cell = row.createCell(columnNum);
                    cell.setCellValue(locale.getMessages().get(parameter));
                }
            }

        }
    }

    worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, colCount));
    worksheet.createFreezePane(0, headLine.getRowNum() + 1);
    /*
            for (int i = 0; i < colCount; i++){
    worksheet.autoSizeColumn(i);
    worksheet.setColumnWidth(i, worksheet.getColumnWidth(i) + 100);
            }
    */
    workbook.write(fileOut);
    fileOut.flush();
    fileOut.close();
}

From source file:com.haulmont.mp2xls.writer.LocalizationLogExcelWriter.java

License:Apache License

public static void exportToXls(List<LocalizationLog> differences, String outputXls) throws IOException {
    FileOutputStream fileOut = new FileOutputStream(outputXls);
    HSSFWorkbook workbook = new HSSFWorkbook();
    try {/* ww  w .j av a 2s  .  co  m*/
        HSSFSheet worksheet = workbook.createSheet("localizations");

        HSSFCellStyle systemStyle = workbook.createCellStyle();
        systemStyle.setFillForegroundColor(HSSFColor.RED.index);
        systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        Integer currentRow = 0;
        HSSFRow headLine = worksheet.createRow(++currentRow);
        headLine.createCell(0).setCellValue("File");
        headLine.createCell(1).setCellValue("Property");
        headLine.createCell(2).setCellValue("Source Value");
        headLine.createCell(3).setCellValue("Excel Value");

        HSSFRow row;

        for (LocalizationLog.Type type : LocalizationLog.Type.values()) {
            List<LocalizationLog> logs = getLogsByType(differences, type);
            if (logs.size() > 0) {
                row = worksheet.createRow(++currentRow);
                HSSFCell cell = row.createCell(0);
                cell.setCellValue(LogHelper.getMessageByType(type));
                cell.setCellStyle(LogHelper.getStyleByType(workbook, type));
                for (int i = 1; i < 4; i++) {
                    row.createCell(i);
                }

                CellRangeAddress region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);

                for (LocalizationLog log : logs) {
                    createNewLogRow(worksheet, ++currentRow, log);
                }

                row = worksheet.createRow(++currentRow);
                for (int i = 0; i < 4; i++) {
                    row.createCell(i);
                }

                region = new CellRangeAddress(currentRow, currentRow, 0, 3);
                worksheet.addMergedRegion(region);
            }
        }

        worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, 3));
        worksheet.createFreezePane(0, headLine.getRowNum() + 1);

        for (int i = 0; i < worksheet.getLastRowNum(); i++) {
            worksheet.autoSizeColumn(i);
        }
    } finally {
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java

License:Apache License

@Override
public void apply() {
    for (DataObject dataObject : data) {
        HSSFCell templateCell = dataObject.templateCell;
        HSSFCell resultCell = dataObject.resultCell;
        BandData bandData = dataObject.bandData;

        HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook();
        HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook();

        String templateCellValue = templateCell.getStringCellValue();

        Matcher matcher = pattern.matcher(templateCellValue);
        if (matcher.find()) {
            String paramName = matcher.group(1);
            String styleName = (String) bandData.getParameterValue(paramName);
            if (styleName == null)
                continue;

            HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName);
            if (cellStyle == null)
                continue;

            HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle);

            if (resultStyle == null) {
                HSSFCellStyle newStyle = resultWorkbook.createCellStyle();
                // color
                newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor());
                newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor());
                newStyle.setFillPattern(cellStyle.getFillPattern());

                // borders
                newStyle.setBorderLeft(cellStyle.getBorderLeft());
                newStyle.setBorderRight(cellStyle.getBorderRight());
                newStyle.setBorderTop(cellStyle.getBorderTop());
                newStyle.setBorderBottom(cellStyle.getBorderBottom());

                // border colors
                newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor());
                newStyle.setRightBorderColor(cellStyle.getRightBorderColor());
                newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor());
                newStyle.setTopBorderColor(cellStyle.getTopBorderColor());

                // alignment
                newStyle.setAlignment(cellStyle.getAlignment());
                newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment());
                // misc
                DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat();
                newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString()));
                newStyle.setHidden(cellStyle.getHidden());
                newStyle.setLocked(cellStyle.getLocked());
                newStyle.setIndention(cellStyle.getIndention());
                newStyle.setRotation(cellStyle.getRotation());
                newStyle.setWrapText(cellStyle.getWrapText());
                // font
                HSSFFont cellFont = cellStyle.getFont(templateWorkbook);
                HSSFFont newFont = fontCache.getFontByTemplate(cellFont);

                if (newFont == null) {
                    newFont = resultWorkbook.createFont();

                    newFont.setFontName(cellFont.getFontName());
                    newFont.setItalic(cellFont.getItalic());
                    newFont.setStrikeout(cellFont.getStrikeout());
                    newFont.setTypeOffset(cellFont.getTypeOffset());
                    newFont.setBoldweight(cellFont.getBoldweight());
                    newFont.setCharSet(cellFont.getCharSet());
                    newFont.setColor(cellFont.getColor());
                    newFont.setUnderline(cellFont.getUnderline());
                    newFont.setFontHeight(cellFont.getFontHeight());
                    newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints());
                    fontCache.addCachedFont(cellFont, newFont);
                }// w w w  .j av a  2s  .  co  m
                newStyle.setFont(newFont);

                resultStyle = newStyle;
                styleCache.addCachedNamedStyle(cellStyle, resultStyle);
            }

            fixNeighbourCellBorders(cellStyle, resultCell);

            resultCell.setCellStyle(resultStyle);

            Sheet sheet = resultCell.getSheet();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) {

                    int firstRow = mergedRegion.getFirstRow();
                    int lastRow = mergedRegion.getLastRow();
                    int firstCol = mergedRegion.getFirstColumn();
                    int lastCol = mergedRegion.getLastColumn();

                    for (int row = firstRow; row <= lastRow; row++)
                        for (int col = firstCol; col <= lastCol; col++)
                            sheet.getRow(row).getCell(col).setCellStyle(resultStyle);

                    // cell includes only in one merged region
                    break;
                }
            }
        }
    }
}