Example usage for org.apache.poi.ss.usermodel Sheet getSheetName

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getSheetName.

Prototype

String getSheetName();

Source Link

Document

Returns the name of this sheet

Usage

From source file:cherry.goods.excel.ExcelFactoryTest.java

License:Apache License

@Test
public void testOpen_XLSX() throws Exception {
    // /*from w w w. j  a  v a 2s.  co m*/
    File file = File.createTempFile(getClass().getName(), ".xlsx", new File("."));
    file.deleteOnExit();
    try (OutputStream out = new FileOutputStream(file);
            Workbook workbook = ExcelFactory.createBlankXlsx("OPEN_XLSX")) {
        workbook.write(out);
    }
    // 
    try (InputStream in = new FileInputStream(file); Workbook workbook = ExcelFactory.open(in)) {
        Sheet sheet = workbook.getSheetAt(0);
        assertNotNull(sheet);
        assertEquals("OPEN_XLSX", sheet.getSheetName());
    }
}

---

From source file:cherry.goods.excel.ExcelFactoryTest.java

License:Apache License

@Test
public void testLoad_XLS() throws Exception {
    // //  w ww . j a v a 2 s .  com
    File file = File.createTempFile(getClass().getName(), ".xls", new File("."));
    file.deleteOnExit();
    try (OutputStream out = new FileOutputStream(file);
            Workbook workbook = ExcelFactory.createBlankXls("LOAD_XLS")) {
        workbook.write(out);
    }
    // 
    try (Workbook workbook = ExcelFactory.load(file)) {
        Sheet sheet = workbook.getSheetAt(0);
        assertNotNull(sheet);
        assertEquals("LOAD_XLS", sheet.getSheetName());
    }
}

---

From source file:cherry.goods.excel.ExcelFactoryTest.java

License:Apache License

@Test
public void testLoad_XLSX() throws Exception {
    // /*  w  w  w .  j av  a2s. co  m*/
    File file = File.createTempFile(getClass().getName(), ".xlsx", new File("."));
    file.deleteOnExit();
    try (OutputStream out = new FileOutputStream(file);
            Workbook workbook = ExcelFactory.createBlankXlsx("LOAD_XLSX")) {
        workbook.write(out);
    }
    // 
    try (Workbook workbook = ExcelFactory.load(file)) {
        Sheet sheet = workbook.getSheetAt(0);
        assertNotNull(sheet);
        assertEquals("LOAD_XLSX", sheet.getSheetName());
    }
}

---

From source file:cherry.goods.excel.ExcelFactoryTest.java

License:Apache License

@Test
public void testCreateBlankXls_WITH_NAME() throws Exception {
    // //from   w ww.  j av  a  2s .com
    File file = File.createTempFile(getClass().getName(), ".xls", new File("."));
    file.deleteOnExit();
    // 
    try (OutputStream out = new FileOutputStream(file);
            Workbook workbook = ExcelFactory.createBlankXls("CREATE_BLANK_XLS")) {
        workbook.write(out);
    }
    // 
    try (Workbook workbook = ExcelFactory.load(file); ExcelReader reader = new ExcelReader(workbook)) {
        Sheet sheet = workbook.getSheetAt(0);
        assertNotNull(sheet);
        assertEquals("CREATE_BLANK_XLS", sheet.getSheetName());
        String[] r0 = reader.read();
        assertNull(r0);
    }
}

---

From source file:cherry.goods.excel.ExcelFactoryTest.java

License:Apache License

@Test
public void testCreateBlankXlsx_WITH_NAME() throws Exception {
    // /*from   www. j  ava  2 s . co m*/
    File file = File.createTempFile(getClass().getName(), ".xlsx", new File("."));
    file.deleteOnExit();
    // 
    try (OutputStream out = new FileOutputStream(file);
            Workbook workbook = ExcelFactory.createBlankXlsx("CREATE_BLANK_XLSX")) {
        workbook.write(out);
    }
    // 
    try (Workbook workbook = ExcelFactory.load(file); ExcelReader reader = new ExcelReader(workbook)) {
        Sheet sheet = workbook.getSheetAt(0);
        assertNotNull(sheet);
        assertEquals("CREATE_BLANK_XLSX", sheet.getSheetName());
        String[] r0 = reader.read();
        assertNull(r0);
    }
}

---

From source file:cherry.parser.worksheet.RowBasedParser.java

License:Apache License

private List<TypeDef> parseSheet(Sheet sheet) {

    boolean configured = false;
    int coldefFirstCellNum = -1;
    Map<Integer, String> coldef = new TreeMap<Integer, String>();

    Map<String, TypeDef> map = new LinkedHashMap<String, TypeDef>();

    TypeDef typeDef = null;/*from ww  w .ja  va  2s.  c om*/
    for (Row row : sheet) {

        int firstCellNum = row.getFirstCellNum();
        if (firstCellNum < 0) {
            continue;
        }

        if (!configured) {

            String directive = getCellValueAsString(row.getCell(firstCellNum));
            if ("##COLDEF".equals(directive)) {

                for (Cell cell : row) {
                    if (cell.getColumnIndex() == firstCellNum) {
                        continue;
                    }
                    if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                        continue;
                    }
                    coldef.put(cell.getColumnIndex(), getCellValueAsString(cell));
                }

                coldefFirstCellNum = firstCellNum;
                configured = true;
            } else {
                // IGNORE UNKNOWN DIRECTIVES
            }
        } else {

            ItemDef item = null;
            for (Cell cell : row) {

                if (cell.getColumnIndex() == coldefFirstCellNum) {
                    item = new ItemDef();
                    continue;
                }
                if (item == null) {
                    continue;
                }
                String key = coldef.get(cell.getColumnIndex());
                if (key == null) {
                    continue;
                }

                String value = getCellValueAsString(cell);
                if (value != null) {
                    item.put(key, value);
                }
            }

            if (item != null) {
                String fqcn = item.get(TypeDef.FULLY_QUALIFIED_CLASS_NAME);
                if (fqcn != null) {
                    TypeDef td = map.get(fqcn);
                    if (td != null) {
                        typeDef = td;
                    } else {
                        typeDef = new TypeDef();
                        typeDef.setSheetName(sheet.getSheetName());
                        map.put(fqcn, typeDef);
                    }
                }
                if (typeDef != null) {
                    typeDef.getItemDef().add(item);
                }
            }
        }
    }

    return new ArrayList<TypeDef>(map.values());
}

---

From source file:cherry.parser.worksheet.SheetBasedParser.java

License:Apache License

private TypeDef parseSheet(Sheet sheet) {

    State state = State.HEAD;
    int coldefFirstCellNum = -1;
    Map<Integer, String> coldef = new TreeMap<Integer, String>();

    TypeDef typeDef = new TypeDef();
    typeDef.setSheetName(sheet.getSheetName());
    for (Row row : sheet) {

        int firstCellNum = row.getFirstCellNum();
        if (firstCellNum < 0) {
            continue;
        }//from  w w w .  j a va 2s . c om

        if (state == State.HEAD) {

            String directive = getCellValueAsString(row.getCell(firstCellNum));
            if ("##FQCN".equals(directive)) {

                String fqcn = getCellValueAsString(row.getCell(firstCellNum + 1));
                typeDef.setFullyQualifiedClassName(fqcn);
            } else if ("##ATTR".equals(directive)) {

                String key = getCellValueAsString(row.getCell(firstCellNum + 1));
                String value = getCellValueAsString(row.getCell(firstCellNum + 2));
                typeDef.put(key, value);
            } else if ("##COLDEF".equals(directive)) {

                for (Cell cell : row) {
                    if (cell.getColumnIndex() == firstCellNum) {
                        continue;
                    }
                    if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
                        continue;
                    }
                    coldef.put(cell.getColumnIndex(), getCellValueAsString(cell));
                }

                coldefFirstCellNum = firstCellNum;
                state = State.ITEM;
            } else {
                // IGNORE UNKNOWN DIRECTIVES
            }
        } else if (state == State.ITEM) {

            ItemDef item = null;
            for (Cell cell : row) {

                if (cell.getColumnIndex() == coldefFirstCellNum) {
                    item = new ItemDef();
                    continue;
                }
                if (item == null) {
                    continue;
                }
                String key = coldef.get(cell.getColumnIndex());
                if (key == null) {
                    continue;
                }

                String value = getCellValueAsString(cell);
                if (value != null) {
                    item.put(key, value);
                }
            }

            if (item != null) {
                typeDef.getItemDef().add(item);
            }
        } else {
            // IGNORE UNKNOWN STATE
        }
    }

    return typeDef;
}

---

From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java

License:Open Source License

public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception {
    assert wb != null;
    assert reportFile != null;

    //Precompute formula
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);

        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    try {
                        evaluator.evaluateFormulaCell(c);
                    } catch (Exception e) {
                        System.err.println(e);
                    }//from   w ww  . j  a  v  a2  s . c om
                }
            }
        }
    }

    File tmp = File.createTempFile("tmp_", ".xlsx");
    try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) {
        wb.write(out);
    }

    //Find page orientation
    int maxColumnsGlobal = 0;
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum());
        }
    }

    Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate();
    Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f);

    PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile));
    addHeader(writer, header);
    pdfDocument.open();
    //we have two columns in the Excel sheet, so we create a PDF table with two columns
    //Note: There are ways to make this dynamic in nature, if you want to.
    //Loop through sheets
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);

        //Loop through rows, to find number of columns
        int minColumns = 1000;
        int maxColumns = 0;
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            if (row.getFirstCellNum() >= 0)
                minColumns = Math.min(minColumns, row.getFirstCellNum());
            if (row.getLastCellNum() >= 0)
                maxColumns = Math.max(maxColumns, row.getLastCellNum());
        }
        if (maxColumns == 0)
            continue;

        //Loop through first rows, to find relative width
        float[] widths = new float[maxColumns];
        int totalWidth = 0;
        for (int c = 0; c < maxColumns; c++) {
            int w = sheet.getColumnWidth(c);
            widths[c] = w;
            totalWidth += w;
        }

        for (int c = 0; c < maxColumns; c++) {
            widths[c] /= totalWidth;
        }

        //Create new page and a new chapter with the sheet's name
        if (sheetNo > 0)
            pdfDocument.newPage();
        Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1);

        PdfPTable pdfTable = null;
        PdfPCell pdfCell = null;
        boolean inTable = false;

        //Loop through cells, to create the content
        //         boolean leftBorder = true;
        //         boolean[] topBorder = new boolean[maxColumns+1];
        for (int r = 0; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);

            //Check if we exited a table (empty line)
            if (row == null) {
                if (pdfTable != null) {
                    addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
                    pdfTable = null;
                }
                inTable = false;
                continue;
            }

            //Check if we start a table (>MIN_COL_IN_TABLE columns)
            if (row.getLastCellNum() >= MIN_COL_IN_TABLE) {
                inTable = true;
            }

            if (!inTable) {
                //Process the data outside table, just add the text
                boolean hasData = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        continue;
                    Chunk chunk = getChunk(wb, cell);
                    pdfSheet.add(chunk);
                    pdfSheet.add(new Chunk(" "));
                    hasData = true;
                }
                if (hasData)
                    pdfSheet.add(Chunk.NEWLINE);

            } else {
                //Process the data in table
                if (pdfTable == null) {
                    //Create table
                    pdfTable = new PdfPTable(maxColumns);
                    pdfTable.setWidths(widths);
                    //                  topBorder = new boolean[maxColumns+1];
                }

                int cellNumber = minColumns;
                //               leftBorder = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    for (; cellNumber < cell.getColumnIndex(); cellNumber++) {
                        pdfCell = new PdfPCell();
                        pdfCell.setBorder(0);
                        pdfTable.addCell(pdfCell);
                    }

                    Chunk phrase = getChunk(wb, cell);
                    pdfCell = new PdfPCell(new Phrase(phrase));
                    pdfCell.setFixedHeight(row.getHeightInPoints() - 3);
                    pdfCell.setNoWrap(!cell.getCellStyle().getWrapText());
                    pdfCell.setPaddingLeft(1);
                    pdfCell.setHorizontalAlignment(
                            cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER
                                    : cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT
                                            ? PdfPCell.ALIGN_RIGHT
                                            : PdfPCell.ALIGN_LEFT);
                    pdfCell.setUseBorderPadding(false);
                    pdfCell.setUseVariableBorders(false);
                    pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f);
                    pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0
                            : cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0
                            : cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0
                            : cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f);
                    String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null
                            : ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex();
                    if (color != null)
                        pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2))));
                    pdfTable.addCell(pdfCell);
                    cellNumber++;
                }
                for (; cellNumber < maxColumns; cellNumber++) {
                    pdfCell = new PdfPCell();
                    pdfCell.setBorder(0);
                    pdfTable.addCell(pdfCell);
                }
            }

            //Custom code to add all images on the first sheet (works for reporting)
            if (sheetNo == 0 && row.getRowNum() == 0) {
                for (PictureData pd : wb.getAllPictures()) {
                    try {
                        Image pdfImg = Image.getInstance(pd.getData());
                        pdfImg.scaleToFit(
                                pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft()
                                        - pageSize.getBorderWidthRight(),
                                pageSize.getHeight() * .8f - pageSize.getBorderWidthTop()
                                        - pageSize.getBorderWidthBottom());
                        pdfSheet.add(pdfImg);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        if (pdfTable != null) {
            addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
        }

        pdfDocument.add(pdfSheet);
    }
    pdfDocument.close();

}

---

From source file:com.admin.poi.ExcelUtils.java

License:Apache License

/**
 *  excel// w  w w  . ja  v a  2 s .  c o m
 *
 * @param excelSheet   sheet ?
 * @param workbook     
 * @param outputStream ?
 */
private static void export(ExcelSheet excelSheet, Workbook workbook, OutputStream outputStream)
        throws IOException, InvocationTargetException, IllegalAccessException {

    Sheet sheet;
    sheet = workbook.createSheet();
    workbook.setSheetOrder(sheet.getSheetName(), excelSheet.getSheetIndex());

    // write head
    writeHead(excelSheet, sheet);
    // sheet
    int writeRowIndex = excelSheet.getStartRowIndex();
    if (excelSheet.getDataList() != null && !excelSheet.getDataList().isEmpty()) {
        for (Object rowData : excelSheet.getDataList()) {
            // proc row
            Row row = Optional.ofNullable(sheet.getRow(writeRowIndex)).orElse(sheet.createRow(writeRowIndex));

            writeRow(excelSheet, row, rowData);
            writeRowIndex++;
        }
    }
    workbook.write(outputStream);
}

---

From source file:com.b2international.snowowl.datastore.server.importer.AbstractTerminologyExcelImportJob.java

License:Apache License

protected void commitSheets(final String fileName, final Set<Sheet> sheets) throws Exception, CommitException {
    for (final Sheet sheet : sheets) {
        final String terminologyName = importTerminology(sheet.getSheetName());
        logImportActivity(MessageFormat.format("Processed excel sheet {0} for {1}", sheet.getSheetName(),
                getTerminologyName()));/*from w  w w .  ja  va2s  .  c  o m*/
        final CDOCommitInfo commitInfo = commitChanges(terminologyName, fileName);
        if (null != commitInfo) {
            latestSuccessfulCommitTime = commitInfo.getTimeStamp();
        }
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• »