Examples with CellStyle org.apache.poi.ss.usermodel.CellStyle used on opensource projects

Example 31 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project OpenRefine by OpenRefine.

the class XlsExporter method export.

@Override
public void export(final Project project, Properties params, Engine engine, OutputStream outputStream) throws IOException {
    final Workbook wb = xml ? new XSSFWorkbook() : new HSSFWorkbook();
    TabularSerializer serializer = new TabularSerializer() {

        Sheet s;

        int rowCount = 0;

        CellStyle dateStyle;

        @Override
        public void startFile(JSONObject options) {
            s = wb.createSheet();
            String sheetName = WorkbookUtil.createSafeSheetName(ProjectManager.singleton.getProjectMetadata(project.id).getName());
            wb.setSheetName(0, sheetName);
            dateStyle = wb.createCellStyle();
            dateStyle.setDataFormat(// TODO what format here?
            wb.getCreationHelper().createDataFormat().getFormat("YYYY-MM-DD"));
        }

        @Override
        public void endFile() {
        }

        @Override
        public void addRow(List<CellData> cells, boolean isHeader) {
            Row r = s.createRow(rowCount++);
            for (int i = 0; i < cells.size(); i++) {
                Cell c = r.createCell(i);
                if (i == 255 && cells.size() > 256) {
                    c.setCellValue("ERROR: TOO MANY COLUMNS");
                    break;
                } else {
                    CellData cellData = cells.get(i);
                    if (cellData != null && cellData.text != null && cellData.value != null) {
                        Object v = cellData.value;
                        if (v instanceof Number) {
                            c.setCellValue(((Number) v).doubleValue());
                        } else if (v instanceof Boolean) {
                            c.setCellValue(((Boolean) v).booleanValue());
                        } else if (v instanceof Date) {
                            c.setCellValue((Date) v);
                            c.setCellStyle(dateStyle);
                        } else if (v instanceof Calendar) {
                            c.setCellValue((Calendar) v);
                            c.setCellStyle(dateStyle);
                        } else {
                            String s = cellData.text;
                            if (s.length() > 32767) {
                                // The maximum length of cell contents (text) is 32,767 characters
                                s = s.substring(0, 32767);
                            }
                            c.setCellValue(s);
                        }
                        if (cellData.link != null) {
                            HSSFHyperlink hl = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
                            hl.setLabel(cellData.text);
                            hl.setAddress(cellData.link);
                        }
                    }
                }
            }
        }
    };
    CustomizableTabularExporterUtilities.exportRows(project, engine, params, serializer);
    wb.write(outputStream);
    outputStream.flush();
}

Example 32 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class SSPerformanceTest method addContent.

private static void addContent(Workbook workBook, boolean isHType, int rows, int cols) {
    Map<String, CellStyle> styles = createStyles(workBook);
    Sheet sheet = workBook.createSheet("Main Sheet");
    Cell headerCell = sheet.createRow(0).createCell(0);
    headerCell.setCellValue("Header text is spanned across multiple cells");
    headerCell.setCellStyle(styles.get("header"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));
    int sheetNo = 0;
    int rowIndexInSheet = 1;
    double value = 0;
    Calendar calendar = Calendar.getInstance();
    for (int rowIndex = 0; rowIndex < rows; rowIndex++) {
        if (isHType && sheetNo != rowIndex / 0x10000) {
            sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo));
            headerCell.setCellValue("Header text is spanned across multiple cells");
            headerCell.setCellStyle(styles.get("header"));
            sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1"));
            rowIndexInSheet = 1;
        }
        Row row = sheet.createRow(rowIndexInSheet);
        for (int colIndex = 0; colIndex < cols; colIndex++) {
            value = populateCell(styles, value, calendar, rowIndex, row, colIndex);
        }
        rowIndexInSheet++;
    }
}

Example 33 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class ToHtml method printStyles.

public void printStyles() {
    ensureOut();
    // First, copy the base css
    BufferedReader in = null;
    try {
        in = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("excelStyle.css")));
        String line;
        while ((line = in.readLine()) != null) {
            out.format("%s%n", line);
        }
    } catch (IOException e) {
        throw new IllegalStateException("Reading standard css", e);
    } finally {
        IOUtils.closeQuietly(in);
    }
    // now add css for each used style
    Set<CellStyle> seen = new HashSet<CellStyle>();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        Iterator<Row> rows = sheet.rowIterator();
        while (rows.hasNext()) {
            Row row = rows.next();
            for (Cell cell : row) {
                CellStyle style = cell.getCellStyle();
                if (!seen.contains(style)) {
                    printStyle(style);
                    seen.add(style);
                }
            }
        }
    }
}

Example 34 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class CreateCell method main.

public static void main(String[] args) throws IOException {
    //or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    CreationHelper creationHelper = wb.getCreationHelper();
    Sheet sheet = wb.createSheet("new sheet");
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 0);
    // Create a cell and put a value in it.
    Cell cell = row.createCell((short) 0);
    cell.setCellValue(1);
    //numeric value
    row.createCell(1).setCellValue(1.2);
    //plain string value
    row.createCell(2).setCellValue("This is a string cell");
    //rich text string
    RichTextString str = creationHelper.createRichTextString("Apache");
    Font font = wb.createFont();
    font.setItalic(true);
    font.setUnderline(Font.U_SINGLE);
    str.applyFont(font);
    row.createCell(3).setCellValue(str);
    //boolean value
    row.createCell(4).setCellValue(true);
    //formula
    row.createCell(5).setCellFormula("SUM(A1:B1)");
    //date
    CellStyle style = wb.createCellStyle();
    style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm"));
    cell = row.createCell(6);
    cell.setCellValue(new Date());
    cell.setCellStyle(style);
    //hyperlink
    row.createCell(7).setCellFormula("SUM(A1:B1)");
    cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")");
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("ooxml-cell.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 35 with CellStyle

use of org.apache.poi.ss.usermodel.CellStyle in project poi by apache.

the class HyperlinkExample method main.

public static void main(String[] args) throws IOException {
    //or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    CellStyle hlink_style = wb.createCellStyle();
    Font hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(IndexedColors.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    Cell cell;
    Sheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell(0);
    cell.setCellValue("URL Link");
    Hyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //link to a file in the current directory
    cell = sheet.createRow(1).createCell(0);
    cell.setCellValue("File Link");
    link = createHelper.createHyperlink(HyperlinkType.FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //e-mail link
    cell = sheet.createRow(2).createCell(0);
    cell.setCellValue("Email Link");
    link = createHelper.createHyperlink(HyperlinkType.EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //link to a place in this workbook
    //create a target sheet and cell
    Sheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell(0).setCellValue("Target Cell");
    cell = sheet.createRow(3).createCell(0);
    cell.setCellValue("Worksheet Link");
    Hyperlink link2 = createHelper.createHyperlink(HyperlinkType.DOCUMENT);
    link2.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link2);
    cell.setCellStyle(hlink_style);
    FileOutputStream out = new FileOutputStream("hyperinks.xlsx");
    wb.write(out);
    out.close();
    wb.close();
}