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:gov.nih.nci.ncicb.cadsr.cdebrowser.struts.actions.CompareCDEAction.java

License:BSD License

public ActionForward downloadToExcel(ActionMapping mapping, ActionForm form, HttpServletRequest request,
        HttpServletResponse response) throws IOException, ServletException {
    DynaActionForm hrefCRFForm = (DynaActionForm) form;

    CDECompareList cdeList = (CDECompareList) this.getSessionObject(request, CDE_COMPARE_LIST);

    // create a new excel workbook
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();/*from w w w  .  j a  v a  2s.  com*/
    short rowNumber = 0;
    short colNumber = 0;

    //create bold cell style
    HSSFCellStyle boldCellStyle = wb.createCellStyle();
    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    boldCellStyle.setFont(font);
    boldCellStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL);

    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row = sheet.createRow(rowNumber++);
    HSSFCell cell = row.createCell((short) 0);
    cell.setCellValue("Data Element");
    cell.setCellStyle(boldCellStyle);

    List cdeColl = cdeList.getCdeList();

    addNewRow(sheet, rowNumber++, "Public ID", boldCellStyle, cdeColl, "CDEId");
    addNewRow(sheet, rowNumber++, "Long Name", boldCellStyle, cdeColl, "longName");
    addNewRow(sheet, rowNumber++, "Document Text", boldCellStyle, cdeColl, "longCDEName");
    addNewRow(sheet, rowNumber++, "Definition", boldCellStyle, cdeColl, "preferredDefinition");
    addNewRow(sheet, rowNumber++, "Owned by Context", boldCellStyle, cdeColl, "contextName");
    addNewRow(sheet, rowNumber++, "Used by Context", boldCellStyle, cdeColl, "usingContexts");
    addNewRow(sheet, rowNumber++, "Origin", boldCellStyle, cdeColl, "origin");
    addNewRow(sheet, rowNumber++, "Workflow Status", boldCellStyle, cdeColl, "aslName");
    addNewRow(sheet, rowNumber++, "Registration Status", boldCellStyle, cdeColl, "registrationStatus");
    addNewRow(sheet, rowNumber++, "Short Name", boldCellStyle, cdeColl, "preferredName");
    addNewRow(sheet, rowNumber++, "Version", boldCellStyle, cdeColl, "version");

    row = sheet.createRow(rowNumber++);
    row = sheet.createRow(rowNumber++);
    cell = row.createCell((short) 0);
    cell.setCellValue("Data Element Concept");
    cell.setCellStyle(boldCellStyle);

    List cdeConceptList = new ArrayList();

    for (int i = 0; i < cdeColl.size(); i++)
        cdeConceptList.add(i, ((DataElement) cdeColl.get(i)).getDataElementConcept());

    addNewRow(sheet, rowNumber++, "Public ID", boldCellStyle, cdeConceptList, "publicId");
    addNewRow(sheet, rowNumber++, "Long Name", boldCellStyle, cdeConceptList, "longName");
    addNewRow(sheet, rowNumber++, "Short Name", boldCellStyle, cdeConceptList, "preferredName");
    addNewRow(sheet, rowNumber++, "Definition", boldCellStyle, cdeConceptList, "preferredDefinition");
    addNewRow(sheet, rowNumber++, "Context", boldCellStyle, cdeConceptList, "contextName");
    addNewRow(sheet, rowNumber++, "Conceptual Domain Short Name", boldCellStyle, cdeConceptList, "CDPrefName");
    addNewRow(sheet, rowNumber++, "Object Class Short Name", boldCellStyle, cdeConceptList,
            "objectClass.preferredName");
    addNewRow(sheet, rowNumber++, "Property Short Name", boldCellStyle, cdeConceptList,
            "property.preferredName");
    addNewRow(sheet, rowNumber++, "Origin", boldCellStyle, cdeConceptList, "origin");
    addNewRow(sheet, rowNumber++, "Workflow Status", boldCellStyle, cdeConceptList, "aslName");

    row = sheet.createRow(rowNumber++);
    row = sheet.createRow(rowNumber++);
    cell = row.createCell((short) 0);
    cell.setCellValue("Value Domain");
    cell.setCellStyle(boldCellStyle);

    addNewRow(sheet, rowNumber++, "Public ID", boldCellStyle, cdeColl, "valueDomain.publicId");
    addNewRow(sheet, rowNumber++, "Long Name", boldCellStyle, cdeColl, "valueDomain.longName");
    addNewRow(sheet, rowNumber++, "Short Name", boldCellStyle, cdeColl, "valueDomain.preferredName");
    addNewRow(sheet, rowNumber++, "Definition", boldCellStyle, cdeColl, "valueDomain.preferredDefinition");
    addNewRow(sheet, rowNumber++, "Data Type", boldCellStyle, cdeColl, "valueDomain.datatype");
    addNewRow(sheet, rowNumber++, "Unit of Measure", boldCellStyle, cdeColl, "valueDomain.unitOfMeasure");
    addNewRow(sheet, rowNumber++, "Display Format", boldCellStyle, cdeColl, "valueDomain.displayFormat");
    addNewRow(sheet, rowNumber++, "Maximum Length", boldCellStyle, cdeColl, "valueDomain.maxLength");
    addNewRow(sheet, rowNumber++, "Minimum Length", boldCellStyle, cdeColl, "valueDomain.minLength");
    addNewRow(sheet, rowNumber++, "Decimal Place", boldCellStyle, cdeColl, "valueDomain.decimalPlace");
    addNewRow(sheet, rowNumber++, "High Value", boldCellStyle, cdeColl, "valueDomain.highValue");
    addNewRow(sheet, rowNumber++, "Low Value", boldCellStyle, cdeColl, "valueDomain.lowValue");
    addNewRow(sheet, rowNumber++, "Value Domain Type", boldCellStyle, cdeColl, "valueDomain.VDType");
    addNewRow(sheet, rowNumber++, "Conceptual Domain Short Name", boldCellStyle, cdeColl,
            "valueDomain.CDPrefName");
    addNewRow(sheet, rowNumber++, "Representation", boldCellStyle, cdeColl,
            "valueDomain.representation.longName");
    addNewRow(sheet, rowNumber++, "Origin", boldCellStyle, cdeColl, "valueDomain.origin");
    addNewRow(sheet, rowNumber++, "Workflow Status", boldCellStyle, cdeColl, "valueDomain.aslName");
    addNewRow(sheet, rowNumber++, "Version", boldCellStyle, cdeColl, "valueDomain.version");

    List pvTitles = new ArrayList();
    pvTitles.add(0, "Value");
    pvTitles.add(1, "Value Meaning");
    pvTitles.add(2, "Description");
    List pvProperties = new ArrayList();
    pvProperties.add(0, "shortMeaningValue");
    pvProperties.add(1, "shortMeaning");
    pvProperties.add(2, "shortMeaningDescription");

    rowNumber += this.exportObjects(sheet, rowNumber, "Permissible Values", "valueDomain.validValues",
            boldCellStyle, cdeColl, pvProperties, pvTitles);

    List refDocPropertyTitles = new ArrayList();
    refDocPropertyTitles.add(0, "Document Name");
    refDocPropertyTitles.add(1, "Document Type");
    refDocPropertyTitles.add(2, "Document Text");
    List refDocProperties = new ArrayList();
    refDocProperties.add(0, "docName");
    refDocProperties.add(1, "docType");
    refDocProperties.add(2, "docText");

    rowNumber += this.exportObjects(sheet, rowNumber, "Reference Document", "refereceDocs", boldCellStyle,
            cdeColl, refDocProperties, refDocPropertyTitles);

    List csPropertyTitles = new ArrayList();
    csPropertyTitles.add(0, "CS* Short Name");
    csPropertyTitles.add(1, "CS* Definition");
    csPropertyTitles.add(2, "CSI* Name");
    csPropertyTitles.add(3, "CSI* Type");
    List csProperties = new ArrayList();
    csProperties.add(0, "classSchemeName");
    csProperties.add(1, "classSchemeDefinition");
    csProperties.add(2, "classSchemeItemName");
    csProperties.add(3, "classSchemeItemType");

    rowNumber += this.exportObjects(sheet, rowNumber, "Classifications", "classifications", boldCellStyle,
            cdeColl, csProperties, csPropertyTitles);

    row = sheet.createRow(rowNumber++);
    row = sheet.createRow(rowNumber++);
    cell = row.createCell((short) 0);
    cell.setCellValue("Data Element Derivation");
    cell.setCellStyle(boldCellStyle);

    addNewRow(sheet, rowNumber++, "Derivation Type", boldCellStyle, cdeColl, "derivedDataElement.type.name");
    addNewRow(sheet, rowNumber++, "Rule", boldCellStyle, cdeColl, "derivedDataElement.rule");
    addNewRow(sheet, rowNumber++, "Method", boldCellStyle, cdeColl, "derivedDataElement.methods");
    addNewRow(sheet, rowNumber++, "Concatenation Character", boldCellStyle, cdeColl,
            "derivedDataElement.concatenationCharacter");

    List dedPropertyTitles = new ArrayList();
    dedPropertyTitles.add(0, "Long Name");
    dedPropertyTitles.add(1, "Context");
    dedPropertyTitles.add(2, "Public ID");
    dedPropertyTitles.add(3, "Version");
    List dedProperties = new ArrayList();
    dedProperties.add(0, "longName");
    dedProperties.add(1, "contextName");
    dedProperties.add(2, "CDEId");
    dedProperties.add(3, "version");

    rowNumber += this.exportObjects(sheet, rowNumber, "Component Data Elements",
            "derivedDataElement.dataElementDerivation", boldCellStyle, cdeColl, dedProperties,
            dedPropertyTitles);

    CDEBrowserParams params = CDEBrowserParams.getInstance();
    String excelFilename = params.getXMLDownloadDir() + "compareCDEs" + ".xls";
    FileOutputStream fileOut = new FileOutputStream(excelFilename);
    wb.write(fileOut);
    fileOut.close();

    File f = new File(excelFilename);
    String ctype = ContentTypeHelper.getContentType(f.getName());

    response.setContentType(ctype);
    response.setContentLength((int) f.length());
    response.setHeader("Content-Disposition", "attachment;filename=\"" + f.getName() + "\"");
    response.setHeader("Pragma", "public");
    response.setHeader("Expires", "0");
    response.setHeader("Cache-Control", "max-age=0");

    try {
        // create buffer
        byte[] buffer = new byte[1024];

        int r = 0;
        // write out file
        FileInputStream fin = new FileInputStream(f);
        OutputStream out = response.getOutputStream();

        while ((r = fin.read(buffer, 0, buffer.length)) != -1) {
            out.write(buffer, 0, r);
        }

        try {
            fin.close();

            out.flush();
            out.close();
        } catch (Exception e) {
        }

        out = null;
        fin = null;
        buffer = null;
    } catch (Exception ex) {
        String msg = ex.getMessage();

        response.setContentType("text/html");
        response.setContentLength(msg.length());
        PrintWriter out = response.getWriter();
        out.println("Unexpected error");
        out.flush();
        out.close();
    }

    return null;
}

From source file:gov.nih.nci.ncicb.cadsr.common.downloads.impl.GetExcelDownloadImpl.java

License:BSD License

private void generateExcelFile() throws Exception {
    Connection cn = null;//ww  w.  j  a v  a 2  s  .  com
    Statement st = null;
    ResultSet rs = null;
    FileOutputStream fileOut = null;

    try {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        int rowNumber = 0;

        HSSFCellStyle boldCellStyle = wb.createCellStyle();
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        boldCellStyle.setFont(font);
        boldCellStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL);

        // Create a row and put the column header in it
        HSSFRow row = sheet.createRow(rowNumber++);
        short col = 0;
        List colInfo = this.initColumnInfo(source);

        for (int i = 0; i < colInfo.size(); i++) {
            ColumnInfo currCol = (ColumnInfo) colInfo.get(i);

            if (currCol.type.indexOf("Array") >= 0) {
                for (int nestedI = 0; nestedI < currCol.nestedColumns.size(); nestedI++) {
                    ColumnInfo nestedCol = (ColumnInfo) currCol.nestedColumns.get(nestedI);

                    HSSFCell cell = row.createCell(col++);
                    cell.setCellValue(currCol.displayName + nestedCol.displayName);
                    cell.setCellStyle(boldCellStyle);
                }
            } else {
                HSSFCell cell = row.createCell(col++);

                cell.setCellValue(currCol.displayName);
                cell.setCellStyle(boldCellStyle);
            }
        }

        String sqlStmt = "SELECT * FROM DE_EXCEL_GENERATOR_VIEW " + "WHERE DE_IDSEQ IN " + " ( " + where
                + " )  ";

        ConnectionHelper connHelper = new ConnectionHelper(jndiName);
        cn = connHelper.getConnection();

        if (cn == null) {
            throw new Exception("Cannot get the connection for the JNDI name [" + jndiName + "]");
        }

        st = cn.createStatement();
        rs = st.executeQuery(sqlStmt);
        generateDataRow(rowNumber, sheet, colInfo, rs);
        String filename = getFileName();
        fileOut = new FileOutputStream(filename);
        wb.write(fileOut);

    } catch (SQLException e) {
        log.warn("Database error ", e);
    } catch (Exception ex) {
        log.error("Exception caught in Generate Excel File", ex);
        throw ex;
    } finally {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (cn != null) {
                cn.close();
            }
            if (fileOut != null) {
                fileOut.close();
            }
        } catch (Exception e) {
            log.debug("Unable to perform clean up due to the following error ", e);
        }
    }

}

From source file:gov.va.med.pharmacy.peps.presentation.common.displaytag.DefaultHssfExportView.java

License:Artistic License

/**
 * doExport creates excel file for download
 * @param out OutputStream/*  www  .ja  va 2s  . c  o m*/
 * @see org.displaytag.export.BinaryExportView#doExport(java.io.OutputStream)
 * @throws IOException IOException
 * @throws JspException JspException
 */
public void doExport(OutputStream out) throws IOException, JspException {
    try {
        HSSFWorkbook wb = new HSSFWorkbook();
        sheet = wb.createSheet("Table_Export");

        int rowNum = 0;
        int colNum = 0;

        //Create a header row
        HSSFRow xlsRow = sheet.createRow(rowNum++);

        HSSFCellStyle headerStyle = wb.createCellStyle();
        headerStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);
        headerStyle.setFillBackgroundColor(HSSFColor.BLUE_GREY.index);
        HSSFFont bold = wb.createFont();
        bold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        bold.setColor(HSSFColor.WHITE.index);
        headerStyle.setFont(bold);

        Iterator iterator = this.model.getHeaderCellList().iterator();

        while (iterator.hasNext()) {
            HeaderCell headerCell = (HeaderCell) iterator.next();

            String columnHeader = headerCell.getTitle();

            if (columnHeader == null) {
                columnHeader = StringUtils.capitalize(headerCell.getBeanPropertyName());
            }

            HSSFCell cell = xlsRow.createCell(colNum++);
            cell.setCellValue(columnHeader);
            cell.setCellStyle(headerStyle);
        }

        RowIterator rowIterator = this.model.getRowIterator(this.exportFull);

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            xlsRow = sheet.createRow(rowNum++);
            colNum = 0;

            // iterate on columns
            ColumnIterator columnIterator = row.getColumnIterator(this.model.getHeaderCellList());

            while (columnIterator.hasNext()) {
                Column column = columnIterator.nextColumn();

                Object value = column.getValue(this.decorated);

                HSSFCell cell = xlsRow.createCell(colNum++);

                if (value instanceof Number) {
                    Number num = (Number) value;
                    cell.setCellValue(num.doubleValue());
                } else if (value instanceof Date) {
                    cell.setCellValue((Date) value);
                } else if (value instanceof Calendar) {
                    cell.setCellValue((Calendar) value);
                } else {
                    cell.setCellValue(escapeColumnValue(value));
                }

            }
        }

        wb.write(out);

        //            new HssfTableWriter(wb).writeTable(this.model, "-1");            
        //            wb.write(out);
    } catch (Exception e) {
        throw new HssfGenerationException(e);
    }
}

From source file:hornet.framework.web.service.export.AbstractTableExportService.java

License:CeCILL license

@Override
public HSSFWorkbook construireXlsModel(final T toExport) {

    // Blank workbook
    final HSSFWorkbook workbook = new HSSFWorkbook();

    // Create a blank sheet
    final HSSFSheet sheet = workbook.createSheet();
    int rownum = 0;

    // Style pour la bordure des cellules
    final CellStyle styleBordure = workbook.createCellStyle();
    styleBordure.setBorderBottom(BorderStyle.THIN);
    styleBordure.setBorderTop(BorderStyle.THIN);
    styleBordure.setBorderRight(BorderStyle.THIN);
    styleBordure.setBorderLeft(BorderStyle.THIN);

    final CellStyle styleEntete = workbook.createCellStyle();
    styleEntete.cloneStyleFrom(styleBordure);
    styleEntete.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
    styleEntete.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    // Rcupration du Table VO
    final TableVO tableVo = construireTableauExport(toExport);

    final Collection<String> colTitles = tableVo.getColumnsTitles();
    final Iterator<String> itTitles = colTitles.iterator();

    // Titre des colonnes
    int cellnum = 0;
    final Row xlsRow = sheet.createRow(rownum++);
    while (itTitles.hasNext()) {
        final String title = itTitles.next();
        final Cell cell = xlsRow.createCell(cellnum++);
        cell.setCellValue(title);/*from  ww w  .java  2 s .  c o m*/
        cell.setCellStyle(styleEntete);
    }

    if (tableVo.getRows() != null) {
        final List<RowVO> rows = tableVo.getRows();
        final Iterator<RowVO> itRows = rows.iterator();
        // Lignes
        while (itRows.hasNext()) {
            this.exporteLigne(itRows, rownum, sheet, styleBordure);
            rownum++;
        }

        for (int i = 0; i < cellnum; i++) {
            sheet.autoSizeColumn(i);
        }
    }

    return workbook;
}

From source file:hr.restart.swing.raExtendedTable.java

License:Apache License

public void exportToXLS(File output) {
    String fname = output.getName();
    if (!fname.endsWith("xls") && fname.indexOf('.') < 0)
        output = new File(output.getParentFile(), fname + ".xls");
    System.out.println("exporting to XLS");
    HSSFWorkbook wb = new HSSFWorkbook();

    HSSFDataFormat df = wb.createDataFormat();

    String fontFamily = frmParam.getParam("sisfun", "excelFont", "Arial", "Font za export u Excel", true);
    if (fontFamily == null || fontFamily.length() == 0)
        fontFamily = "Arial";

    int fontSize = 10;
    String fontSizeTx = frmParam.getParam("sisfun", "excelFontSize", "10",
            "Veliina fonta za export u Excel, u tokama", true);
    if (fontSizeTx != null && Aus.getNumber(fontSizeTx) >= 6 && Aus.getNumber(fontSizeTx) <= 72)
        fontSize = Aus.getNumber(fontSizeTx);

    HSSFFont font = wb.createFont();/*from w ww .  jav a 2  s.co m*/
    font.setFontName(fontFamily);
    font.setFontHeightInPoints((short) fontSize);

    HSSFFont fontTitle = wb.createFont();
    fontTitle.setFontName(fontFamily);
    fontTitle.setFontHeightInPoints((short) (fontSize * 1.8));

    HSSFFont fontSubtitle = wb.createFont();
    fontSubtitle.setFontName(fontFamily);
    fontSubtitle.setFontHeightInPoints((short) (fontSize * 1.5));

    HSSFCellStyle csHeader = wb.createCellStyle();
    csHeader.setFont(font);
    csHeader.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    csHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    csHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    csHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    csHeader.setDataFormat(df.getFormat("text"));

    HSSFCellStyle csFooter = wb.createCellStyle();
    csFooter.setFont(font);
    csFooter.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooter.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooter.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    csFooter.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    csFooter.setDataFormat(df.getFormat("text"));

    HSSFCellStyle csFooterNum2 = wb.createCellStyle();
    csFooterNum2.setFont(font);
    csFooterNum2.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooterNum2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooterNum2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    csFooterNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csFooterNum2.setDataFormat(df.getFormat("#,##0.00"));

    HSSFCellStyle csFooterNum = wb.createCellStyle();
    csFooterNum.setFont(font);
    csFooterNum.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooterNum.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
    csFooterNum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    csFooterNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csFooterNum.setDataFormat(df.getFormat("#"));

    HSSFCellStyle csDate = wb.createCellStyle();
    csDate.setFont(font);
    csDate.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    csDate.setDataFormat(df.getFormat("dd.mm.yyyy"));

    HSSFCellStyle csTitle = wb.createCellStyle();
    csTitle.setFont(fontTitle);
    csTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    csTitle.setDataFormat(df.getFormat("text"));

    HSSFCellStyle csSubtitle = wb.createCellStyle();
    csSubtitle.setFont(fontSubtitle);
    csSubtitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    csSubtitle.setDataFormat(df.getFormat("text"));

    HSSFCellStyle csNum2 = wb.createCellStyle();
    csNum2.setFont(font);
    csNum2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csNum2.setDataFormat(df.getFormat("#,##0.00"));

    HSSFCellStyle csNum3 = wb.createCellStyle();
    csNum3.setFont(font);
    csNum3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csNum3.setDataFormat(df.getFormat("#,##0.000"));

    HSSFCellStyle csNum = wb.createCellStyle();
    csNum.setFont(font);
    csNum.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csNum.setDataFormat(df.getFormat("#.#"));

    HSSFCellStyle csInt = wb.createCellStyle();
    csInt.setFont(font);
    csInt.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    csInt.setDataFormat(df.getFormat("#"));

    HSSFCellStyle csText = wb.createCellStyle();
    csText.setFont(font);
    csText.setAlignment(HSSFCellStyle.ALIGN_LEFT);
    csText.setDataFormat(df.getFormat("text"));

    repDynamicProvider dp = repDynamicProvider.getInstance();
    boolean sums = dp.hasSumRow();
    int cols = getColumnModel().getColumnCount();
    int rows = getRowCount() - (sums ? 1 : 0);

    HSSFSheet sheet = wb.createSheet();
    HSSFRow row;
    HSSFCell cell;

    short cRow = 0;

    // header and title
    row = sheet.createRow(cRow = 0);
    cell = row.createCell((short) 0);
    cell.setCellStyle(csText);
    if (cols > 1)
        sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1)));
    cell.setEncoding(wb.ENCODING_UTF_16);
    cell.setCellValue(repMemo.getrepMemo().getOneLine());

    row = sheet.createRow(++cRow);
    cell = row.createCell((short) 0);
    cell.setCellStyle(csTitle);
    if (cols > 1)
        sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1)));
    cell.setEncoding(wb.ENCODING_UTF_16);
    if (dp.getTitle().length() > 0)
        cell.setCellValue(dp.getTitle().substring(1));
    else
        cell.setCellValue("");

    if (dp.getSubtitle().length() > 0) {
        row = sheet.createRow(++cRow);
        cell = row.createCell((short) 0);
        cell.setCellStyle(csSubtitle);
        if (cols > 1)
            sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (cols - 1)));
        cell.setEncoding(wb.ENCODING_UTF_16);
        cell.setCellValue(dp.getSubtitle().substring(1));
    }

    for (short c = 0; c < cols; c++)
        sheet.setColumnWidth(c, (short) (getColumnModel().getColumn(c).getWidth() * 40));

    // sections
    row = sheet.createRow(++cRow);
    int secRow = 0, firstRow = 0;

    for (int r = 0; r < rows; r++) {
        if (r == 0) {
            row = sheet.createRow(++cRow);
            for (short c = 0; c < cols; c++) {
                cell = row.createCell(c);
                cell.setCellStyle(csHeader);
                cell.setEncoding(wb.ENCODING_UTF_16);
                cell.setCellValue(getColumnModel().getColumn(c).getHeaderValue().toString());
            }
            if (firstRow == 0)
                firstRow = cRow;
            secRow = cRow;
        }
        row = sheet.createRow(++cRow);
        for (short c = 0; c < cols; c++) {
            cell = row.createCell(c);
            Object o = getValueAt(r, c);
            if (o instanceof Number) {
                if (o instanceof BigDecimal) {
                    BigDecimal bd = (BigDecimal) o;
                    if (bd.scale() == 2)
                        cell.setCellStyle(csNum2);
                    else if (bd.scale() == 3)
                        cell.setCellStyle(csNum3);
                    else
                        cell.setCellStyle(csNum);
                    cell.setCellValue(bd.doubleValue());
                } else {
                    String t = dp.getValueAt(r, c);
                    if (Aus.isDigit(t)) {
                        cell.setCellStyle(csInt);
                        cell.setCellValue(((Number) o).doubleValue());
                    } else {
                        cell.setCellStyle(csText);
                        cell.setEncoding(wb.ENCODING_UTF_16);
                        cell.setCellValue(t);
                    }
                }
            } else if (o instanceof Date) {
                cell.setCellStyle(csDate);
                cell.setCellValue((Date) o);
            } else {
                cell.setCellStyle(csText);
                cell.setEncoding(wb.ENCODING_UTF_16);
                cell.setCellValue(dp.getValueAt(r, c));
            }
        }
    }
    System.out.println("sums " + sums);
    if (sums) {
        int non = 0;
        while (non < cols && dp.getValueAt(getRowCount() - 1, non).trim().length() == 0)
            ++non;
        if (non < cols) {
            System.out.println("creating row " + non);
            row = sheet.createRow(++cRow);

            if (non > 0) {
                cell = row.createCell((short) 0);
                cell.setCellStyle(csFooter);
                cell.setEncoding(wb.ENCODING_UTF_16);
                cell.setCellValue("U K U P N O");
                if (non > 1)
                    sheet.addMergedRegion(new Region(cRow, (short) 0, cRow, (short) (non - 1)));
            }
            for (short c = (short) non; c < cols; c++) {
                cell = row.createCell(c);
                Object o = getValueAt(rows - 1, c);
                if ((o instanceof BigDecimal) && ((BigDecimal) o).scale() == 2)
                    cell.setCellStyle(csFooterNum2);
                else
                    cell.setCellStyle(csFooterNum);
                if (dp.getValueAt(getRowCount() - 1, c).trim().length() != 0)
                    cell.setCellFormula("SUBTOTAL(9;" + xlsRange(firstRow + 1, cRow, c) + ")");
                else
                    cell.setCellValue("");
            }
        }
    }
    FileOutputStream out = null;

    try {
        out = new FileOutputStream(output);
        wb.write(out);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (out != null)
            try {
                out.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
}

From source file:include.excel_export.XlsWriter.java

License:Open Source License

private void initCellTyles(HSSFWorkbook wb) {
    HSSFDataFormat df = wb.createDataFormat();
    short doubleFormat = df.getFormat("#,##0.00");
    short intFormat = df.getFormat("0");
    short dateFormat = df.getFormat("yyyy-mm-dd");
    doubleCellStyle = wb.createCellStyle();//? 
    doubleCellStyle.setDataFormat(doubleFormat);
    intCellStyle = wb.createCellStyle();
    intCellStyle.setDataFormat(intFormat);
    dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat);
}

From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java

License:Apache License

private static HSSFCellStyle createHeaderCellStyle(final HSSFWorkbook workbook) {
    final HSSFCellStyle cellStyle = workbook.createCellStyle();
    final HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setFontName("Arial");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    cellStyle.setFont(font);// www  .j  a v a 2s. c  o m
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    cellStyle.setVerticalAlignment((short) 3);
    // styleEntete.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
    // headerCellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
    cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
    return cellStyle;
}

From source file:io.vertigo.dynamo.plugins.export.xls.XLSExporter.java

License:Apache License

private static HSSFCellStyle createRowCellStyle(final HSSFWorkbook workbook, final boolean odd) {
    final HSSFCellStyle cellStyle = workbook.createCellStyle();
    final HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setFontName("Arial");
    // font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    cellStyle.setFont(font);//  w w w  .  j  av a2  s.  c  o  m
    cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
    cellStyle.setBorderTop(CellStyle.BORDER_THIN);
    cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
    cellStyle.setBorderRight(CellStyle.BORDER_THIN);
    // cellStyle.setVerticalAlignment((short)3);
    // styleEntete.setFillPattern(HSSFCellStyle.SPARSE_DOTS);
    cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    cellStyle.setFillForegroundColor(odd ? HSSFColor.WHITE.index : HSSFColor.GREY_25_PERCENT.index);
    // headerCellStyle.setFillBackgroundColor(HSSFColor.LIGHT_BLUE.index);
    // cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // cellStyle.setWrapText(true);

    return cellStyle;
}

From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java

License:Apache License

private static HSSFCellStyle createHeaderCellStyle(final HSSFWorkbook workbook) {
    final HSSFCellStyle cellStyle = workbook.createCellStyle();
    final HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setFontName("Arial");
    font.setBold(true);//www  .j a v a2 s  . c o  m
    cellStyle.setFont(font);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setVerticalAlignment(VerticalAlignment.JUSTIFY);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    cellStyle.setFillForegroundColor(HSSFColorPredefined.GREY_40_PERCENT.getIndex());
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    return cellStyle;
}

From source file:io.vertigo.quarto.plugins.export.xls.XLSExporter.java

License:Apache License

private static HSSFCellStyle createRowCellStyle(final HSSFWorkbook workbook, final boolean odd) {
    final HSSFCellStyle cellStyle = workbook.createCellStyle();
    final HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints((short) 10);
    font.setFontName("Arial");
    cellStyle.setFont(font);/*from ww w.j  a v  a2s  .  c  o m*/
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

    cellStyle.setFillForegroundColor(
            odd ? HSSFColorPredefined.WHITE.getIndex() : HSSFColorPredefined.GREY_25_PERCENT.getIndex());

    return cellStyle;
}