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

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

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

Introduction

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

Prototype


@Override
public HSSFFont createFont()

Source Link

Document

create a new Font and add it to the workbook's font table

Usage

From source file:org.sharegov.cirm.utils.ExcelExportUtil.java

License:Apache License

public void exportData(OutputStream out, Json allData) throws IOException {
    //Set the filename
    Date dt = new Date();
    SimpleDateFormat fmt = new SimpleDateFormat("MM-dd-yyyy");
    String filename = fmt.format(dt);

    // Create Excel Workbook and Sheet
    HSSFWorkbook wb = new HSSFWorkbook();
    sheet = wb.createSheet(filename);/*from   ww  w .  ja  v a2s.  co  m*/
    HSSFHeader header = sheet.getHeader();
    header.setCenter(filename);

    HSSFFont boldFont = wb.createFont();
    boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle boldStyle = wb.createCellStyle();
    boldStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    boldStyle.setFont(boldFont);
    boldStyle.setWrapText(true);

    //Start : populate the spreadsheet
    int rowCounter = 0;
    rowCounter = searchCriteriaRows(allData, boldStyle);
    rowCounter = headerRow(allData, boldStyle, rowCounter);
    int headingsRowSplitter = rowCounter;
    rowCounter = dataRows(allData, rowCounter);
    //end : populate the spreadsheet

    // Freeze Panes on Header Row
    sheet.createFreezePane(0, headingsRowSplitter);
    // Row 1 Repeats on each page
    wb.setRepeatingRowsAndColumns(0, 0, 0, 0, headingsRowSplitter);

    // Set Print Area, Footer
    int colCount = allData.at("metaData").at("columns").asInteger();
    wb.setPrintArea(0, 0, colCount, 0, rowCounter);
    HSSFFooter footer = sheet.getFooter();
    footer.setCenter("Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages());
    // Fit Sheet to 1 page wide but very long
    sheet.setAutobreaks(true);
    HSSFPrintSetup ps = sheet.getPrintSetup();
    ps.setFitWidth((short) 1);
    ps.setFitHeight((short) 9999);
    sheet.setGridsPrinted(true);
    sheet.setHorizontallyCenter(true);
    ps.setPaperSize(HSSFPrintSetup.LETTER_PAPERSIZE);
    if (colCount > 5) {
        ps.setLandscape(true);
    }
    if (colCount > 10) {
        ps.setPaperSize(HSSFPrintSetup.LEGAL_PAPERSIZE);
    }
    if (colCount > 14) {
        ps.setPaperSize(HSSFPrintSetup.EXECUTIVE_PAPERSIZE);
    }
    // Set Margins
    ps.setHeaderMargin((double) .35);
    ps.setFooterMargin((double) .35);
    sheet.setMargin(HSSFSheet.TopMargin, (double) .50);
    sheet.setMargin(HSSFSheet.BottomMargin, (double) .50);
    sheet.setMargin(HSSFSheet.LeftMargin, (double) .50);
    sheet.setMargin(HSSFSheet.RightMargin, (double) .50);

    // Write out the spreadsheet
    wb.write(out);
    out.close();
}

From source file:org.sourcecodemetrics.report.generators.ReportGeneratorImpl.java

License:Open Source License

@Override
public void generateReport(IProject project, String filePath) {
    try {/*from   w  w w  .  ja v a2  s . co m*/
        FileOutputStream fileOut = new FileOutputStream(filePath);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.createSheet("Source Code Metrics");

        // creation of the header row
        whiteStyle = workbook.createCellStyle();
        whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        whiteStyle.setFillBackgroundColor(HSSFColor.WHITE.index);
        whiteStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        dottedBlueStyle = workbook.createCellStyle();
        dottedBlueStyle.setFillForegroundColor(HSSFColor.BLACK.index);
        dottedBlueStyle.setFillBackgroundColor(HSSFColor.AQUA.index);
        dottedBlueStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);

        blueStyle = workbook.createCellStyle();
        blueStyle.setFillForegroundColor(HSSFColor.AQUA.index);
        blueStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.RED.index);
        redBlueStyle = workbook.createCellStyle();
        redBlueStyle.setFillForegroundColor(HSSFColor.AQUA.index);
        redBlueStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        redBlueStyle.setFont(font);

        dottedYellowStyle = workbook.createCellStyle();
        dottedYellowStyle.setFillForegroundColor(HSSFColor.BLACK.index);
        dottedYellowStyle.setFillBackgroundColor(HSSFColor.YELLOW.index);
        dottedYellowStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);

        yellowStyle = workbook.createCellStyle();
        yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
        yellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        redYellowStyle = workbook.createCellStyle();
        redYellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
        redYellowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        redYellowStyle.setFont(font);

        dottedStyle = workbook.createCellStyle();
        dottedStyle.setFillForegroundColor(HSSFColor.BLACK.index);
        dottedStyle.setFillBackgroundColor(HSSFColor.WHITE.index);
        dottedStyle.setFillPattern(HSSFCellStyle.SPARSE_DOTS);

        redWhiteStyle = workbook.createCellStyle();
        redWhiteStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        redWhiteStyle.setFillBackgroundColor(HSSFColor.WHITE.index);
        redWhiteStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        redWhiteStyle.setFont(font);

        HSSFRow headerRow = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
        for (int i = 0; i < headings.size(); i++) {
            String title = headings.get(i);
            HSSFCell cell = headerRow.createCell(i);
            cell.setCellValue(title);
            cell.setCellStyle(whiteStyle);
        }

        for (IPackage ip : project.getPackages()) {
            if (!ip.isTests() && !ip.getSourceFiles().isEmpty()) {
                PackageGenerator.generate(ip, worksheet, workbook);
            }
        }

        // adjusting first three columns
        try {
            for (int i = 0; i < 3; i++) {
                worksheet.autoSizeColumn(i);
            }
        } catch (ArrayIndexOutOfBoundsException e) {
            logger.log(Level.SEVERE, "Exception when trying to adjust columns", e);
        }

        // setting constant width to other columns
        for (int i = 3; i < headings.size(); i++) {
            worksheet.setColumnWidth(i, 2000);
        }

        // writing out the statistics to the report
        StatisticsGenerator.generateStatistics(project, workbook);

        // writing the raw data to the report
        RawDataGenerator.generateRawData(project, workbook);

        worksheet.createFreezePane(3, 1);

        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException ex) {
        Exceptions.printStackTrace(ex);
    } catch (IOException ex) {
        Exceptions.printStackTrace(ex);
    } catch (Exception ex) {
        Exceptions.printStackTrace(ex);
    }
}

From source file:org.sourcecodemetrics.report.generators.StatisticsGenerator.java

License:Open Source License

public static void generateStatistics(IProject project, HSSFWorkbook workbook) {

    if (blueStyle == null) {
        blueStyle = workbook.createCellStyle();
        blueStyle.setFillForegroundColor(HSSFColor.AQUA.index);
        blueStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    }/*from   ww  w . ja v  a2s  .c om*/

    HSSFSheet worksheet = workbook.createSheet("Statistics");

    // creation of the header row
    HSSFRow headerRow = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    HSSFFont font = workbook.createFont();
    font.setColor(HSSFColor.WHITE.index);
    for (int i = 0; i < headings.size(); i++) {
        String title = headings.get(i);
        HSSFCell cell = headerRow.createCell(i);
        cell.setCellValue(title);
        cell.setCellStyle(blueStyle);
    }

    // writing out all of the values of the metrics
    HSSFRow row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    HSSFCell cell = row.createCell(0);
    cell.setCellValue("A");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Abstractness");
    cell = row.createCell(3);
    if (project.getPackageAMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageAMin());
    }
    cell = row.createCell(4);
    if (project.getPackageAMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageAMax());
    }
    cell = row.createCell(5);
    if (project.getPackageAAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageAAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("AC");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Afferent Coupling");
    cell = row.createCell(3);
    if (project.getPackageACMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageACMin());
    }
    cell = row.createCell(4);
    if (project.getPackageACMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageACMax());
    }
    cell = row.createCell(5);
    if (project.getPackageACAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageACAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("C");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Coverage");
    cell = row.createCell(3);
    if (project.getPackageCoverageMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageCoverageMin());
    }
    cell = row.createCell(4);
    if (project.getPackageCoverageMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageCoverageMax());
    }
    cell = row.createCell(5);
    if (project.getPackageCoverageAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageCoverageAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("D");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Distance");
    cell = row.createCell(3);
    if (project.getPackageDMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageDMin());
    }
    cell = row.createCell(4);
    if (project.getPackageDMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageDMax());
    }
    cell = row.createCell(5);
    if (project.getPackageDAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageDAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("EC");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Efferent Coupling");
    cell = row.createCell(3);
    if (project.getPackageECMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageECMin());
    }
    cell = row.createCell(4);
    if (project.getPackageECMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageECMax());
    }
    cell = row.createCell(5);
    if (project.getPackageECAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageECAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("I");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Instability");
    cell = row.createCell(3);
    if (project.getPackageIMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageIMin());
    }
    cell = row.createCell(4);
    if (project.getPackageIMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageIMax());
    }
    cell = row.createCell(5);
    if (project.getPackageIAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageIAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LOC");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Lines Of Code");
    cell = row.createCell(3);
    if (project.getPackageLocMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageLocMin());
    }
    cell = row.createCell(4);
    if (project.getPackageLocMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageLocMax());
    }
    cell = row.createCell(6);
    if (project.getLOC() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getLOC());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LOCm");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Lines Of Comments");
    cell = row.createCell(3);
    if (project.getPackageLocmMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageLocmMin());
    }
    cell = row.createCell(4);
    if (project.getPackageLocmMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageLocmMax());
    }
    cell = row.createCell(6);
    if (project.getLOCm() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getLOCm());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NCP");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Number Of Classes in Package");
    cell = row.createCell(3);
    if (project.getPackageNCPMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageNCPMin());
    }
    cell = row.createCell(4);
    if (project.getPackageNCPMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageNCPMax());
    }
    cell = row.createCell(6);
    if (project.getPackageNCPSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageNCPSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NIP");
    cell = row.createCell(1);
    cell.setCellValue("package");
    cell = row.createCell(2);
    cell.setCellValue("Number Of Interfaces in Package");
    cell = row.createCell(3);
    if (project.getPackageNIPMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageNIPMin());
    }
    cell = row.createCell(4);
    if (project.getPackageNIPMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageNIPMax());
    }
    cell = row.createCell(6);
    if (project.getPackageNIPSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getPackageNIPSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LCC");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Loose Class Coupling");
    cell = row.createCell(3);
    if (project.getClassLCCMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCCMax());
    }
    cell = row.createCell(4);
    if (project.getClassLCCMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCCMin());
    }
    cell = row.createCell(5);
    if (project.getClassLCCAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCCAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LCOM1");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Lack Of Cohesion in Methods 1");
    cell = row.createCell(3);
    if (project.getClassLCOM1Min() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM1Min());
    }
    cell = row.createCell(4);
    if (project.getClassLCOM1Max() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM1Max());
    }
    cell = row.createCell(5);
    if (project.getClassLCOM1Avg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM1Avg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LCOM2");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Lack Of Cohesion in Methods 2");
    cell = row.createCell(3);
    if (project.getClassLCOM2Min() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM2Min());
    }
    cell = row.createCell(4);
    if (project.getClassLCOM2Max() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM2Max());
    }
    cell = row.createCell(5);
    if (project.getClassLCOM2Avg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM2Avg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LCOM3");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Lack Of Cohesion in Methods 3");
    cell = row.createCell(3);
    if (project.getClassLCOM3Min() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM3Min());
    }
    cell = row.createCell(4);
    if (project.getClassLCOM3Max() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM3Max());
    }
    cell = row.createCell(5);
    if (project.getClassLCOM3Avg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM3Avg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LCOM4");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Lack Of Cohesion in Methods 4");
    cell = row.createCell(3);
    if (project.getClassLCOM4Min() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM4Min());
    }
    cell = row.createCell(4);
    if (project.getClassLCOM4Max() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM4Max());
    }
    cell = row.createCell(5);
    if (project.getClassLCOM4Avg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM4Avg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LCOM5");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Lack Of Cohesion in Methods 5");
    cell = row.createCell(3);
    if (project.getClassLCOM5Min() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM5Min());
    }
    cell = row.createCell(4);
    if (project.getClassLCOM5Max() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM5Max());
    }
    cell = row.createCell(5);
    if (project.getClassLCOM5Avg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLCOM5Avg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LOC");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Lines Of Code");
    cell = row.createCell(3);
    if (project.getClassLOCMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLOCMin());
    }
    cell = row.createCell(4);
    if (project.getClassLOCMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLOCMax());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LOCm");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Lines Of Comments");
    cell = row.createCell(3);
    if (project.getClassLOCmMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLOCmMin());
    }
    cell = row.createCell(4);
    if (project.getClassLOCmMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassLOCmMax());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NAK");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Number of Assertions per KLOC");
    cell = row.createCell(3);
    if (project.getClassNAKMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNAKMin());
    }
    cell = row.createCell(4);
    if (project.getClassNAKMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNAKMax());
    }
    cell = row.createCell(5);
    if (project.getClassNAKAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNAKAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NOC");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Number Of Children");
    cell = row.createCell(3);
    if (project.getClassNOCMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOCMin());
    }
    cell = row.createCell(4);
    if (project.getClassNOCMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOCMax());
    }
    cell = row.createCell(6);
    if (project.getClassNOCSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOCSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NOF");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Number Of Fields");
    cell = row.createCell(3);
    if (project.getClassNOFMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOFMin());
    }
    cell = row.createCell(4);
    if (project.getClassNOFMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOFMax());
    }
    cell = row.createCell(6);
    if (project.getClassNOFSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOFSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NOM");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Number Of Methods");
    cell = row.createCell(3);
    if (project.getClassNOMMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOMMin());
    }
    cell = row.createCell(4);
    if (project.getClassNOMMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOMMax());
    }
    cell = row.createCell(6);
    if (project.getClassNOMSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOMSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NOSF");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Number Of Static Fields");
    cell = row.createCell(3);
    if (project.getClassNOSFMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOSFMin());
    }
    cell = row.createCell(4);
    if (project.getClassNOSFMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOSFMax());
    }
    cell = row.createCell(6);
    if (project.getClassNOSFSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOSFSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NOSM");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Number Of Static Methods");
    cell = row.createCell(3);
    if (project.getClassNOSMMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOSMMin());
    }
    cell = row.createCell(4);
    if (project.getClassNOSMMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOSMMax());
    }
    cell = row.createCell(6);
    if (project.getClassNOSMSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNOSMSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NTM");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Number of Test Methods");
    cell = row.createCell(3);
    if (project.getClassNTMMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNTMMin());
    }
    cell = row.createCell(4);
    if (project.getClassNTMMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNTMMax());
    }
    cell = row.createCell(6);
    if (project.getClassNTMSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassNTMSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("TCC");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Tight Class Coupling");
    cell = row.createCell(3);
    if (project.getClassTCCMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassTCCMin());
    }
    cell = row.createCell(4);
    if (project.getClassTCCMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassTCCMax());
    }
    cell = row.createCell(5);
    if (project.getClassTCCAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassTCCAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("WMC");
    cell = row.createCell(1);
    cell.setCellValue("class");
    cell = row.createCell(2);
    cell.setCellValue("Weighted Method Count");
    cell = row.createCell(3);
    if (project.getClassWMCMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassWMCMin());
    }
    cell = row.createCell(4);
    if (project.getClassWMCMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassWMCMax());
    }
    cell = row.createCell(6);
    if (project.getClassWMCSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getClassWMCSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LOC");
    cell = row.createCell(1);
    cell.setCellValue("method");
    cell = row.createCell(2);
    cell.setCellValue("Lines Of Code");
    cell = row.createCell(3);
    if (project.getMethodLOCMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodLOCMin());
    }
    cell = row.createCell(4);
    if (project.getMethodLOCMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodLOCMax());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("LOCm");
    cell = row.createCell(1);
    cell.setCellValue("method");
    cell = row.createCell(2);
    cell.setCellValue("Lines Of Comments");
    cell = row.createCell(3);
    if (project.getMethodLOCmMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodLOCmMin());
    }
    cell = row.createCell(4);
    if (project.getMethodLOCmMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodLOCmMax());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NBD");
    cell = row.createCell(1);
    cell.setCellValue("method");
    cell = row.createCell(2);
    cell.setCellValue("Nested Block Depth");
    cell = row.createCell(3);
    if (project.getMethodNBDMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodNBDMin());
    }
    cell = row.createCell(4);
    if (project.getMethodNBDMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodNBDMax());
    }
    cell = row.createCell(5);
    if (project.getMethodNBDAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodNBDAvg());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("NOP");
    cell = row.createCell(1);
    cell.setCellValue("method");
    cell = row.createCell(2);
    cell.setCellValue("Number Of Parameters");
    cell = row.createCell(3);
    if (project.getMethodNOPMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodNOPMin());
    }
    cell = row.createCell(4);
    if (project.getMethodNOPMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodNOPMax());
    }
    cell = row.createCell(6);
    if (project.getMethodNOPSum() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodNOPSum());
    }

    row = worksheet.createRow(worksheet.getPhysicalNumberOfRows());
    cell = row.createCell(0);
    cell.setCellValue("VG");
    cell = row.createCell(1);
    cell.setCellValue("method");
    cell = row.createCell(2);
    cell.setCellValue("McGabe's Cyclomatic Complexity");
    cell = row.createCell(3);
    if (project.getMethodVGMin() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodVGMin());
    }
    cell = row.createCell(4);
    if (project.getMethodVGMax() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodVGMax());
    }
    cell = row.createCell(5);
    if (project.getMethodVGAvg() == null) {
        cell.setCellValue("-");
    } else {
        cell.setCellValue(project.getMethodVGAvg());
    }

    // adjusting first three columns
    try {
        for (int i = 0; i < 7; i++) {
            worksheet.autoSizeColumn(i);
        }
    } catch (ArrayIndexOutOfBoundsException e) {
        logger.log(Level.SEVERE, "Exception when trying to adjust columns", e);
    }

    // setting constant width to other columns
    for (int i = 3; i < 7; i++) {
        worksheet.setColumnWidth(i, 2700);
    }
}

From source file:org.talend.mdm.webapp.browserecords.server.servlet.ExportingServlet.java

License:Open Source License

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    org.apache.log4j.Logger.getLogger(this.getClass()).info("SERVLET exporting for excel "); //$NON-NLS-1$

    DateFormat df = new SimpleDateFormat("dd-MM-yyyy"); //$NON-NLS-1$
    response.reset();/*  ww w  .  ja  va  2s.  c o m*/
    response.setContentType("application/vnd.ms-excel"); //$NON-NLS-1$
    String theReportFile = "Reporting_" + df.format(new Date()) + ".xls"; //$NON-NLS-1$ //$NON-NLS-2$
    response.setHeader("Content-Disposition", "attachment; filename=\"" + theReportFile + "\""); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet"); //$NON-NLS-1$
    sheet.setDefaultColumnWidth((short) 20);

    String parametersValues = request.getParameter("params"); //$NON-NLS-1$
    if (parametersValues == null) {
        parametersValues = ""; //$NON-NLS-1$
    }

    org.apache.log4j.Logger.getLogger(this.getClass()).debug("params =" + parametersValues); //$NON-NLS-1$

    boolean splitEnd = false;
    String tmpSplit = parametersValues;
    Vector<String> paramVector = new Vector<String>();
    while (!splitEnd) {
        int indexMatch = tmpSplit.indexOf("###"); //$NON-NLS-1$
        if (indexMatch == -1) {
            paramVector.add(tmpSplit);
            splitEnd = true;
        } else {
            if (indexMatch > 0) {
                String tmpParam = tmpSplit.substring(0, indexMatch);
                paramVector.add(tmpParam);
            } else {
                paramVector.add(""); //$NON-NLS-1$
            }

            if (indexMatch + 3 >= tmpSplit.length()) {
                tmpSplit = ""; //$NON-NLS-1$
            } else {
                tmpSplit = tmpSplit.substring(indexMatch + 3);
            }
        }
    }

    // String []parameters = parametersValues.split("###");
    String[] parameters = new String[paramVector.size()];
    for (int i = 0; i < paramVector.size(); i++) {
        parameters[i] = paramVector.get(i);
    }

    org.apache.log4j.Logger.getLogger(this.getClass()).debug("nb params =" + parameters.length); //$NON-NLS-1$

    try {
        WSDataClusterPK wsDataClusterPK = new WSDataClusterPK();
        String entity = null;
        String contentWords = null;
        String keys = null;
        Long fromDate = new Long(-1);
        Long toDate = new Long(-1);
        String fkvalue = null;
        String dataObject = null;

        if (parametersValues != null && parametersValues.length() > 0) {
            JSONObject criteria = new JSONObject(parametersValues);

            Configuration configuration = Configuration.getConfiguration();
            wsDataClusterPK.setPk(configuration.getCluster());
            entity = !criteria.isNull("entity") ? (String) criteria.get("entity") : ""; //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
            keys = !criteria.isNull("key") && !"*".equals(criteria.get("key")) ? (String) criteria.get("key") //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$//$NON-NLS-4$
                    : ""; //$NON-NLS-1$
            fkvalue = !criteria.isNull("fkvalue") && !"*".equals(criteria.get("fkvalue")) //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
                    ? (String) criteria.get("fkvalue") //$NON-NLS-1$
                    : ""; //$NON-NLS-1$
            dataObject = !criteria.isNull("dataObject") && !"*".equals(criteria.get("dataObject")) //$NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
                    ? (String) criteria.get("dataObject") //$NON-NLS-1$
                    : ""; //$NON-NLS-1$
            contentWords = !criteria.isNull("keyWords") ? (String) criteria.get("keyWords") : ""; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

            if (!criteria.isNull("fromDate")) { //$NON-NLS-1$
                String startDate = (String) criteria.get("fromDate"); //$NON-NLS-1$
                SimpleDateFormat dataFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //$NON-NLS-1$
                java.util.Date date = dataFmt.parse(startDate);
                fromDate = date.getTime();
            }

            if (!criteria.isNull("toDate")) { //$NON-NLS-1$
                String endDate = (String) criteria.get("toDate"); //$NON-NLS-1$
                SimpleDateFormat dataFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //$NON-NLS-1$
                java.util.Date date = dataFmt.parse(endDate);
                toDate = date.getTime();
            }
        }

        BusinessConcept businessConcept = SchemaWebAgent.getInstance().getBusinessConcept(entity);
        Map<String, String> foreignKeyMap = businessConcept.getForeignKeyMap();
        Set<String> foreignKeyXpath = foreignKeyMap.keySet();
        Set<String> xpathes = new HashSet<String>();

        for (String path : foreignKeyXpath) {
            String dataObjectPath = foreignKeyMap.get(path);
            if (dataObjectPath.indexOf(dataObject) != -1) {
                xpathes.add(path.substring(1));
            }
        }

        List<String> types = SchemaWebAgent.getInstance().getBindingType(businessConcept.getE());
        for (String type : types) {
            List<ReusableType> subTypes = SchemaWebAgent.getInstance().getMySubtypes(type);
            for (ReusableType reusableType : subTypes) {
                Map<String, String> fks = SchemaWebAgent.getInstance().getReferenceEntities(reusableType,
                        dataObject);
                Collection<String> fkPaths = fks != null ? fks.keySet() : null;
                for (String fkpath : fkPaths) {
                    if (fks.get(fkpath).indexOf(dataObject) != -1) {
                        xpathes.add(fkpath);
                    }
                }
            }
        }

        Map<String, String> inheritanceForeignKeyMap = businessConcept.getInheritanceForeignKeyMap();
        for (Map.Entry<String, String> entry : inheritanceForeignKeyMap.entrySet()) {
            if (entry.getValue().indexOf(dataObject) != -1) {
                xpathes.add(entry.getKey().substring(1));
            }
        }

        StringBuilder keysb = new StringBuilder();
        keysb.append("$"); //$NON-NLS-1$
        keysb.append(joinSet(xpathes, ",")); //$NON-NLS-1$
        keysb.append("$"); //$NON-NLS-1$
        keysb.append(fkvalue);

        WSItemPKsByCriteriaResponse results = Util.getPort().getItemPKsByFullCriteria(
                new WSGetItemPKsByFullCriteria(new WSGetItemPKsByCriteria(wsDataClusterPK, entity, contentWords,
                        keysb.toString(), keys, fromDate, toDate, 0, Integer.MAX_VALUE), false));

        // create a cell style
        HSSFCellStyle cs = wb.createCellStyle();
        HSSFFont f = wb.createFont();
        f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        cs.setFont(f);
        HSSFRow row = sheet.createRow((short) 0);

        if (results.getResults().length > 0) {
            row.createCell((short) 0).setCellValue("date"); //$NON-NLS-1$
            row.createCell((short) 1).setCellValue("entity"); //$NON-NLS-1$
            row.createCell((short) 2).setCellValue("key"); //$NON-NLS-1$
        }

        // set a style for these cells
        for (int i = 0; i < 3; i++) {
            row.getCell((short) i).setCellStyle(cs);
        }

        for (int i = 0; i < results.getResults().length; i++) {
            WSItemPKsByCriteriaResponseResults result = results.getResults()[i];
            if (i == 0) {
                continue;
            }

            row = sheet.createRow((short) i);
            SimpleDateFormat dataFmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); //$NON-NLS-1$
            String date = dataFmt.format(result.getDate());
            row.createCell((short) 0).setCellValue(date);
            row.createCell((short) 1).setCellValue(result.getWsItemPK().getConceptName());
            String[] ids = result.getWsItemPK().getIds();
            StringBuilder sb = new StringBuilder();

            if (ids != null) {
                for (String id : ids) {
                    sb.append(id);
                }
            }

            row.createCell((short) 2).setCellValue(sb.toString());
        }
    } catch (RemoteException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }

    // Write the output
    OutputStream out = response.getOutputStream();
    wb.write(out);
    out.close();
}

From source file:org.tentackle.ui.FormTableUtilityPopup.java

License:Open Source License

/**
 * Converts the table to an excel spreadsheet.
 * @param file the output file//from  www. j  a v a 2s .  c om
 * @param onlySelected true if export only selected rows
 * @throws IOException if export failed
 */
public void excel(File file, boolean onlySelected) throws IOException {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();

    TableModel model = table.getModel();
    TableColumnModel columnModel = table.getColumnModel();

    int[] selectedRows = onlySelected ? table.getSelectedRows() : new int[] {};

    int rows = onlySelected ? selectedRows.length : model.getRowCount(); // number of data rows
    int cols = columnModel.getColumnCount(); // number of data columns

    short srow = 0; // current spreadsheet row

    // local copies cause might be changed
    String xTitle = this.title;
    String xIntro = this.intro;

    if (xTitle == null) {
        // get default from window title
        Window parent = FormHelper.getParentWindow(table);
        try {
            // paint page-title
            xTitle = ((FormWindow) parent).getTitle();
        } catch (Exception e) {
            xTitle = null;
        }
    }
    if (xTitle != null) {
        HSSFRow row = sheet.createRow(srow);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs.setFont(font);
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(cs);
        cell.setCellValue(new HSSFRichTextString(xTitle));
        // region rowFrom, colFrom, rowTo, colTo
        sheet.addMergedRegion(new CellRangeAddress(0, srow, 0, cols - 1));
        srow++;
    }

    if (xIntro != null || onlySelected) {
        HSSFRow row = sheet.createRow(srow);
        HSSFCell cell = row.createCell(0);
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cs.setWrapText(true);
        cell.setCellStyle(cs);
        if (onlySelected) {
            if (xIntro == null) {
                xIntro = "";
            } else {
                xIntro += ", ";
            }
            xIntro += Locales.bundle.getString("<nur_selektierte_Zeilen>");
        }
        cell.setCellValue(new HSSFRichTextString(xIntro));
        sheet.addMergedRegion(new CellRangeAddress(srow, srow + 2, 0, cols - 1));
        srow += 3;
    }

    // column headers
    boolean isAbstractFormTableModel = model instanceof AbstractFormTableModel;
    srow++; // always skip one line
    HSSFRow row = sheet.createRow(srow);
    HSSFFont font = wb.createFont();
    font.setItalic(true);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle cs = wb.createCellStyle();
    cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    cs.setFont(font);
    for (int c = 0; c < cols; c++) {
        HSSFCell cell = row.createCell(c);
        cell.setCellValue(new HSSFRichTextString(isAbstractFormTableModel
                ? ((AbstractFormTableModel) model)
                        .getDisplayedColumnName(columnModel.getColumn(c).getModelIndex())
                : model.getColumnName(columnModel.getColumn(c).getModelIndex())));
        cell.setCellStyle(cs);
    }
    srow++;

    // default cell-style for date
    HSSFCellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

    // cellstyles for numbers
    List<HSSFCellStyle> numberStyles = new ArrayList<HSSFCellStyle>();
    HSSFDataFormat format = wb.createDataFormat();

    for (int r = 0; r < rows; r++) {

        int modelRow = onlySelected ? selectedRows[r] : r;

        row = sheet.createRow(srow + (short) r);

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

            int c = columnModel.getColumn(i).getModelIndex();

            Object value = model.getValueAt(modelRow, c);

            HSSFCell cell = row.createCell(i);

            if (value instanceof Boolean) {
                cell.setCellValue(((Boolean) value).booleanValue());
            } else if (value instanceof BMoney) {
                BMoney money = (BMoney) value;
                cell.setCellValue(money.doubleValue());

                String fmt = "#,##0";
                if (money.scale() > 0) {
                    fmt += ".";
                    for (int j = 0; j < money.scale(); j++) {
                        fmt += "0";
                    }
                }
                // create format
                short fmtIndex = format.getFormat(fmt);

                // check if there is already a cellstyle with this scale
                Iterator<HSSFCellStyle> iter = numberStyles.iterator();
                boolean found = false;
                while (iter.hasNext()) {
                    cs = iter.next();
                    if (cs.getDataFormat() == fmtIndex) {
                        // reuse that
                        found = true;
                        break;
                    }
                }
                if (!found) {
                    // create a new style
                    cs = wb.createCellStyle();
                    cs.setDataFormat(fmtIndex);
                    numberStyles.add(cs);
                }
                cell.setCellStyle(cs);
            } else if (value instanceof Number) {
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellValue((Date) value);
                cell.setCellStyle(dateStyle);
            } else if (value instanceof GregorianCalendar) {
                cell.setCellValue((GregorianCalendar) value);
                cell.setCellStyle(dateStyle);
            } else if (value != null) {
                cell.setCellValue(new HSSFRichTextString(value.toString()));
            }
        }
    }

    // set the width for each column
    for (int c = 0; c < cols; c++) {
        short width = (short) (columnModel.getColumn(c).getWidth() * 45); // is a reasonable value
        sheet.setColumnWidth(c, width);
    }

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream(file);
    wb.write(fileOut);
    fileOut.close();

    // open Excel
    URLHelper.openURL(file.getPath());
}

From source file:org.waterforpeople.mapping.dataexport.SurveyFormExporter.java

License:Open Source License

/**
 * Writes the survey as an XLS document/*  w  w  w .  j  a  v  a 2 s.c  om*/
 */
private void writeSurvey(String title, File fileName, List<QuestionGroupDto> groupList,
        Map<QuestionGroupDto, List<QuestionDto>> questions) throws Exception {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();

    sheet.setColumnWidth(0, COL_WIDTH);
    sheet.setColumnWidth(1, COL_WIDTH);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont headerFont = wb.createFont();
    headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);

    HSSFCellStyle questionStyle = wb.createCellStyle();
    questionStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
    questionStyle.setWrapText(true);

    HSSFCellStyle depStyle = wb.createCellStyle();
    depStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    HSSFFont depFont = wb.createFont();
    depFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    depFont.setItalic(true);
    depStyle.setFont(depFont);

    int curRow = 0;
    HSSFRow row = sheet.createRow(curRow++);
    sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1));
    createCell(row, 0, title, headerStyle);
    row = sheet.createRow(curRow++);
    createCell(row, 0, QUESTION_HEADER, headerStyle);
    createCell(row, 1, RESPONSE_HEADER, headerStyle);

    Long count = 1L;
    if (questions != null) {
        for (int i = 0; i < groupList.size(); i++) {
            HSSFRow groupHeaderRow = sheet.createRow(curRow++);
            sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1));
            createCell(groupHeaderRow, 0, groupList.get(i).getDisplayName(), headerStyle);

            for (QuestionDto q : questions.get(groupList.get(i))) {
                int questionStartRow = curRow;
                HSSFRow tempRow = sheet.createRow(curRow++);
                if (q.getQuestionDependency() != null) {
                    // if there is a dependency, add a row about not
                    // answering unless the dependency is satisfied
                    sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 1));
                    Long qNum = idToNumberMap.get(q.getQuestionDependency().getQuestionId());
                    createCell(tempRow, 0,
                            DEP_HEAD + q.getQuestionDependency().getAnswerValue() + DEP_HEAD_TO + "Q" + qNum,
                            depStyle);
                    tempRow = sheet.createRow(curRow++);
                    questionStartRow = curRow;
                }
                createCell(tempRow, 0, (count++) + ". " + formText(q.getText(), q.getTranslationMap()),
                        questionStyle);
                if (q.getOptionContainerDto() != null && q.getOptionContainerDto().getOptionsList() != null) {
                    for (QuestionOptionDto opt : q.getOptionContainerDto().getOptionsList()) {
                        tempRow = sheet.createRow(curRow++);
                        createCell(tempRow, 1, formText(opt.getText(), opt.getTranslationMap()) + SMALL_BLANK,
                                null);
                    }
                    sheet.addMergedRegion(new CellRangeAddress(questionStartRow, curRow - 1, 0, 0));
                } else {
                    createCell(tempRow, 1, BLANK, null);
                }
            }
        }
    }

    FileOutputStream fileOut = new FileOutputStream(fileName);
    wb.write(fileOut);
    fileOut.close();
}

From source file:org.webguitoolkit.ui.util.export.ExcelTableExport.java

License:Apache License

public void writeTo(Table table, OutputStream out) {
    TableExportOptions exportOptions = table.getExportOptions();

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();//from  ww  w. jav  a2  s.c o m
    HSSFFont fontbold = wb.createFont();
    fontbold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    HSSFCellStyle headstyle = wb.createCellStyle();
    headstyle.setFont(fontbold);
    setExcelheadstyle(headstyle);

    // create dateStyle
    HSSFCellStyle cellStyleDate = wb.createCellStyle();
    if (StringUtils.isNotEmpty(exportOptions.getExcelDateFormat())) {
        cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat(exportOptions.getExcelDateFormat()));
    } else {
        cellStyleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    }
    setExcelDateStyle(cellStyleDate);

    sheet = excelExport(table, sheet);
    String sheetName = exportOptions.getExcelSheetName();
    if (StringUtils.isEmpty(sheetName)) {
        sheetName = StringUtils.isNotEmpty(table.getTitle()) ? table.getTitle() : "sheet";
    }
    if (sheetName.length() > 30) {
        sheetName = sheetName.substring(0, 30);
    }

    // DM: 19.11.2010: Slashes in Sheetname are not allowed, e.g. "Planned deliveries / disposals" did throw
    // IllegalArgumentException.
    // --> catch Exception and set 'Sheet1' as default.
    try {
        wb.setSheetName(0, sheetName);
    } catch (IllegalArgumentException e) {
        logger.error("Sheetname is not valid:" + sheetName + " using Sheet1 as default.", e);
        wb.setSheetName(0, "Sheet1");
    }
    try {
        wb.write(out);
    } catch (IOException e) {
        logger.error(e);
    }
}

From source file:paysheets.PaySheetFormatter.java

public static void addTitleRow(HSSFWorkbook workbook) {
    workbook.createSheet("Sheet 1");
    // Each pay sheet only uses the first sheet
    HSSFSheet sheet = workbook.getSheetAt(0);
    setDefaultColumnWidth(sheet);/*from  ww w .  j  a  v a 2s  . c o  m*/
    HSSFRow row;
    HSSFCell cell;

    // Create a font and set its attributes
    Font font = workbook.createFont();
    font.setFontHeightInPoints((short) 11);
    // Set the color to black (constant COLOR_NORMAL)
    font.setColor(Font.COLOR_NORMAL);
    font.setBold(true);

    // Create a cell style and set its properties
    CellStyle cs = workbook.createCellStyle();
    // Set the data format to the built in text format
    cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
    // Set the cell style to use the font created previously
    cs.setFont(font);

    // Create the first title row
    row = sheet.createRow(0);
    // Use the default row height (-1) is sheet default
    row.setHeight((short) -1);

    // Add the first title row's 6 cells
    for (int cellNum = 0; cellNum < 6; cellNum++) {
        cell = row.createCell(cellNum);
        cell.setCellStyle(cs);
    }
    // Populate first row's values
    cell = row.getCell(PaySheet.DATE_INDEX);
    cell.setCellValue("DATE");
    cell = row.getCell(PaySheet.CUST_INDEX);
    cell.setCellValue("CUSTOMER");
    cell = row.getCell(PaySheet.PAY_INDEX);
    cell.setCellValue("PAY");
    cell = row.getCell(PaySheet.NONSERIAL_INDEX);
    cell.setCellValue("EQUIPMENT");
    cell = row.getCell(PaySheet.SERIAL_INDEX);
    cell.setCellValue("SERIALIZED");
    cell = row.getCell(PaySheet.SHS_INDEX);
    cell.setCellValue("SHS");

    // Create second title row
    row = sheet.createRow(1);
    row.setHeight((short) -1);
    // Add the cells to the row
    for (int cellNum = 0; cellNum < 3; cellNum++) {
        cell = row.createCell(cellNum);
        cell.setCellStyle(cs);
    }
    // Populate the second title row's values
    cell = row.getCell(PaySheet.WO_INDEX);
    cell.setCellValue("WORK ORDER");
    cell = row.getCell(PaySheet.TYPE_INDEX);
    cell.setCellValue("TYPE");
    cell = row.getCell(PaySheet.LEP_INDEX);
    cell.setCellValue("LEP");

    // Add thick border around title row
    addJobBorder(workbook, 0);
}

From source file:paysheets.PaySheetFormatter.java

public static void addJobFormatting(HSSFWorkbook workbook, int rowIndex) {
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFRow row;/*from   w  ww.  j  a  v  a2  s  .  c  o  m*/
    HSSFCell cell;

    Font font = workbook.createFont();
    font.setBold(false);
    font.setFontHeightInPoints((short) 10);
    font.setColor(Font.COLOR_NORMAL);

    // Create a cell style for general text
    CellStyle generalStyle = workbook.createCellStyle();
    generalStyle.setFont(font);
    generalStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));

    // Create a cell style for dates
    CellStyle dateStyle = workbook.createCellStyle();
    dateStyle.setFont(font);
    // Set the cell data format to date (0xe) is the built in format
    dateStyle.setDataFormat((short) 0xe);
    dateStyle.setAlignment(CellStyle.ALIGN_LEFT);

    // Create a new row at the given index
    row = sheet.createRow(rowIndex);
    // Format the first row for the new job
    for (int cellNum = 0; cellNum < 6; cellNum++) {
        cell = row.createCell(cellNum);
        // Only the first cell uses the date style
        if (cellNum > 0) {
            cell.setCellStyle(generalStyle);
        } else {
            cell.setCellStyle(dateStyle);
        }
    }
    // Create second row for the new Job at rowIndex + 1
    row = sheet.createRow(rowIndex + 1);
    for (int cellNum = 0; cellNum < 3; cellNum++) {
        cell = row.createCell(cellNum);
        cell.setCellStyle(generalStyle);
    }
}

From source file:pe.gob.mef.gescon.web.ui.BaseLegalMB.java

public void postProcessXLS(Object document) {
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFSheet sheet = wb.getSheetAt(0);/*  w w w.j  av a2  s.  c o m*/

    //Para los datos
    HSSFCellStyle centerStyle = wb.createCellStyle();
    centerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFCellStyle centerGrayStyle = wb.createCellStyle();
    centerGrayStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    centerGrayStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    centerGrayStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    HSSFCellStyle grayBG = wb.createCellStyle();
    grayBG.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    grayBG.setFillPattern(CellStyle.SOLID_FOREGROUND);
    int i = 1;
    for (BaseLegal b : this.getListaBaseLegal()) {
        HSSFRow row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            HSSFCell cell = row.getCell(j);
            if (i % 2 == 0) {
                if (j > 0) {
                    cell.setCellStyle(centerGrayStyle);
                } else {
                    cell.setCellStyle(grayBG);
                    cell.setCellValue(b.getVnumero());
                }
            } else {
                if (j > 0) {
                    cell.setCellStyle(centerStyle);
                } else {
                    cell.setCellValue(b.getVnumero());
                }
            }
        }
        i++;
    }

    // Para la cabecera
    HSSFRow header = sheet.getRow(0);
    HSSFCellStyle headerStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    headerStyle.setFont(font);

    for (int j = 0; j < header.getPhysicalNumberOfCells(); j++) {
        HSSFCell cell = header.getCell(j);
        cell.setCellStyle(headerStyle);
        sheet.autoSizeColumn(j);
    }
}