Example usage for org.apache.poi.ss.usermodel Workbook getSheetAt

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheetAt

Introduction

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

Prototype

Sheet getSheetAt(int index);

Source Link

Document

Get the Sheet object at the given index.

Usage

From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataTableWriterExcelTest.java

License:Apache License

@Test
public void test() throws Exception {
    RecordDef recordDef = createRecordDef();
    DataTable dataSet = new DataTable(recordDef);

    // Add a record to the dataset
    Record record = new Record(recordDef);
    for (FieldDef fieldDef : recordDef) {
        switch (fieldDef.getType()) {
        case STRING:
            record.setFieldValue(fieldDef.getName(), fieldDef.getName() + " test value");
            break;
        case HYPERLINK:
            HyperlinkFieldValue hyperlink = new HyperlinkFieldValue("http://www.google.com",
                    "hyperlink display text");
            record.setFieldValue(fieldDef.getName(), hyperlink);
            break;
        default://from   w  w w . j a va 2 s.com
            break;
        }
    }
    dataSet.add(record);

    // Add a second record
    record = new Record(recordDef);
    for (FieldDef fieldDef : recordDef) {
        switch (fieldDef.getType()) {
        case STRING:
            record.setFieldValue(fieldDef.getName(), fieldDef.getName() + " test value2");
            break;
        case HYPERLINK:
            HyperlinkFieldValue hyperlink = new HyperlinkFieldValue("http://www.blackducksoftware.com",
                    "hyperlink display text2");
            record.setFieldValue(fieldDef.getName(), hyperlink);
            break;
        default:
            break;
        }
    }
    dataSet.add(record);

    DataSetWriterExcel writer = new DataSetWriterExcel();
    writer.write(dataSet);
    Workbook wb = writer.getWorkbook();
    assertEquals(2, wb.getSheetAt(0).getLastRowNum());
    assertEquals("hyperlink display text", wb.getSheetAt(0).getRow(1).getCell(6).getStringCellValue());
    assertEquals("hyperlink display text2", wb.getSheetAt(0).getRow(2).getCell(6).getStringCellValue());
    assertEquals("http://www.blackducksoftware.com",
            wb.getSheetAt(0).getRow(2).getCell(6).getHyperlink().getAddress());
}

From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReaderTest.java

License:Apache License

/**
 * Test the "no copy" usage that SCM Connector uses. Generate a workbook
 * from a template file, and populate the template map.
 *
 * @throws Exception//  w ww .j  a v a 2 s.  co m
 */
@Test
public void testNoCopyUsage() throws Exception {
    Properties props = new Properties();
    props.setProperty("protex.server.name", "not used");
    props.setProperty("protex.user.name", "not used");
    props.setProperty("protex.password", "not used");
    ConfigurationManager config = new TestProtexConfigurationManager(props);

    // Generate a workbook from a template file, and populate the template
    // map.
    TemplateReader templateReader = new TemplateReader(config);
    Workbook wb = TemplateReader
            .generateWorkBookFromFile(new File("src/test/resources/test_excel_template.xlsx"));
    templateReader.setTemplateBook(wb);
    templateReader.populateTemplateMap();

    // Check the resulting workbook
    Assert.assertEquals("test_sheet", wb.getSheetAt(0).getSheetName());

    Assert.assertEquals("ColumnA", wb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());
    Assert.assertEquals("ColumnB", wb.getSheetAt(0).getRow(0).getCell(1).getStringCellValue());
    Assert.assertEquals("ColumnC", wb.getSheetAt(0).getRow(0).getCell(2).getStringCellValue());

    Assert.assertEquals("tmp", wb.getSheetAt(0).getRow(1).getCell(0).getStringCellValue());
    Assert.assertEquals("tmp", wb.getSheetAt(0).getRow(1).getCell(1).getStringCellValue());
    Assert.assertEquals(1, wb.getSheetAt(0).getRow(1).getCell(2).getNumericCellValue(), 0.01);

    Assert.assertEquals("test_sheet_2", wb.getSheetAt(1).getSheetName());

    Assert.assertEquals("ColumnA_Sheet2", wb.getSheetAt(1).getRow(0).getCell(0).getStringCellValue());
    Assert.assertEquals("ColumnB_Sheet2", wb.getSheetAt(1).getRow(0).getCell(1).getStringCellValue());

    Assert.assertEquals("tmp", wb.getSheetAt(1).getRow(1).getCell(0).getStringCellValue());
    Assert.assertEquals("tmp", wb.getSheetAt(1).getRow(1).getCell(1).getStringCellValue());
}

From source file:com.blackducksoftware.tools.commonframework.standard.workbook.CsvWriter.java

License:Apache License

@Override
public void write(Workbook wb) throws IOException {

    int numSheets = wb.getNumberOfSheets();
    for (int i = 0; i < numSheets; i++) {

        File curOutputFile = getCurrentOutputFile(filePath, numSheets, i);

        CSVWriter pw = new CSVWriter(new OutputStreamWriter(new FileOutputStream(curOutputFile)),
                CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_QUOTE_CHARACTER, "\r\n");

        try {//  w  w  w.  j ava 2s.co m
            Sheet sheet = wb.getSheetAt(i);
            for (Row row : sheet) {
                List<String> cells = new ArrayList<String>();
                String cellValue = "";
                for (Cell cell : row) {
                    int cellType = cell.getCellType();
                    switch (cellType) {
                    case Cell.CELL_TYPE_BLANK:
                        cellValue = "";
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        boolean cellValueBoolean = cell.getBooleanCellValue();
                        cellValue = cellValueBoolean ? "true" : "false";
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cellValue = "<error: " + cell.getErrorCellValue() + ">";
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cellValue = cell.getCellFormula();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        double cellValueDouble = cell.getNumericCellValue();
                        cellValue = Double.toString(cellValueDouble);
                        break;
                    case Cell.CELL_TYPE_STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    default:
                        break;
                    }

                    cells.add(cellValue);
                }
                String[] typeExample = new String[cells.size()];
                String[] cellArray = cells.toArray(typeExample);
                pw.writeNext(cellArray);
            }
        } finally {
            pw.close();
        }
    }
}

From source file:com.blackducksoftware.tools.commonframework.test.TestUtils.java

License:Apache License

/**
 * Compare two reports, optionally forgiving some diffs (like dates, etc.).
 *
 * @param expectedReportFilename/*  ww  w .ja va  2  s.  co  m*/
 * @param actualReportFilename
 * @param firstDataRowOnly
 * @param beFlexible
 * @throws Exception
 */
public static void checkReport(String expectedReportFilename, String actualReportFilename,
        boolean firstDataRowOnly, boolean beFlexible) throws Exception {

    // System.out.println("Expected: " + expectedReportFilename +
    // "; Actual: " + actualReportFilename);
    Workbook expectedWorkbook = WorkbookFactory.create(new File(expectedReportFilename));
    Workbook actualWorkbook = WorkbookFactory.create(new File(actualReportFilename));

    for (int sheetIndex = 0; sheetIndex < expectedWorkbook.getNumberOfSheets(); sheetIndex++) {
        Sheet expectedSheet = expectedWorkbook.getSheetAt(sheetIndex);
        String sheetName = expectedSheet.getSheetName();
        Sheet actualSheet = actualWorkbook.getSheet(sheetName);

        assertNotNull(actualSheet);
        // System.out.println("Checking sheet " + sheetName);

        Iterator<Row> expectedRowIter = expectedSheet.iterator();
        Iterator<Row> actualRowIter = actualSheet.iterator();
        int rowIndex = 0;
        while (expectedRowIter.hasNext()) {

            if (firstDataRowOnly) {
                if (rowIndex > 1) {
                    break; // Just check the first data row
                }
            }
            Row expectedRow = expectedRowIter.next();
            Row actualRow = actualRowIter.next();

            Iterator<Cell> expectedCellIter = expectedRow.iterator();
            Iterator<Cell> actualCellIter = actualRow.iterator();

            int colIndex = 0;
            while (expectedCellIter.hasNext()) {
                compareCells(expectedCellIter, actualCellIter, colIndex++, beFlexible);
            }
            while (actualCellIter.hasNext()) {
                Cell actualCell = actualCellIter.next();
                String actualValue = getCellValueString(actualCell);
                if (actualValue.length() > 0) {
                    // System.out.println("Found extra value: " +
                    // actualValue);
                    fail("Actual report row has more values than expected report row");
                }
            }
            rowIndex++;
        }
    }
}

From source file:com.camel.action.location.CityAction.java

public void handleFileUpload(FileUploadEvent event) {
    try {//ww  w  .  j av  a  2s .  c o  m
        List<City> citiesList = new ArrayList<City>();

        //Create the input stream from the xlsx/xls file
        String fileName = event.getFile().getFileName();
        String cityCode = "";
        String cityName = "";
        String countryCode = "";
        String countryName = "";

        InputStream fis = event.getFile().getInputstream();

        //Create Workbook instance for xlsx/xls file input stream
        Workbook workbook = null;
        if (fileName.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(fis);
        } else if (fileName.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(fis);
        }

        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        Row row = null;
        Iterator<Cell> cellIterator = null;
        Cell cell = null;
        City city = null;
        while (rowIterator.hasNext()) {
            cityCode = "";
            cityName = "";
            countryCode = "";
            countryName = "";

            row = rowIterator.next();
            cellIterator = row.cellIterator();

            if (row.getRowNum() == 0)
                continue;

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (cityCode.equalsIgnoreCase("")) {
                        cityCode = cell.getStringCellValue().trim();
                    } else if (cityName.equalsIgnoreCase("")) {
                        cityName = cell.getStringCellValue().trim();
                    } else if (countryCode.equalsIgnoreCase("")) {
                        countryCode = cell.getStringCellValue().trim();
                    }
                    break;
                }
            } //end of cell iterator
            if (countryCode.equals("#N/A"))
                continue;

            country = findCountry(countryCode);

            if (country != null) {
                city = new City();
                city.setCityCode(cityCode);
                city.setCityName(cityName);
                city.setCountry(country);

                citiesList.add(city);
            }

        } //end of rows iterator

        //close file input stream
        fis.close();
        for (City c : citiesList) {
            super.setInstance(c);
            super.save();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }

    FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    FacesContext.getCurrentInstance().addMessage(null, message);
    country = null;
}

From source file:com.camel.action.location.CountryAction.java

public void handleFileUpload(FileUploadEvent event) {
    String errorMessage = "";
    try {//from   w w  w. java  2  s.co m
        List<Country> countriesList = new ArrayList<Country>();

        //Create the input stream from the xlsx/xls file
        String fileName = event.getFile().getFileName();
        String name = "";
        String shortCode = "";
        String cont = "";

        InputStream fis = event.getFile().getInputstream();

        //Create Workbook instance for xlsx/xls file input stream
        Workbook workbook = null;
        if (fileName.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(fis);
        } else if (fileName.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(fis);
        }

        Sheet sheet = workbook.getSheetAt(0);

        Iterator<Row> rowIterator = sheet.iterator();
        Row row = null;
        Iterator<Cell> cellIterator = null;
        Cell cell = null;
        Country country = null;
        while (rowIterator.hasNext()) {
            name = "";
            shortCode = "";
            cont = "";

            row = rowIterator.next();

            cellIterator = row.cellIterator();

            if (row.getRowNum() == 0) {
                continue;
            }

            while (cellIterator.hasNext()) {

                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (shortCode.equalsIgnoreCase("")) {
                        shortCode = cell.getStringCellValue().trim();
                    } else if (name.equalsIgnoreCase("")) {
                        name = cell.getStringCellValue().trim();
                    } else if (cont.equalsIgnoreCase("")) {
                        cont = cell.getStringCellValue().trim();
                    }
                    break;

                }

            } //end of cell iterator
            if (cont != null && cont.length() > 3) {
                country = new Country();
                country.setContinet(Continent.valueOf(cont));
                country.setCountryCode(shortCode);
                country.setCountryName(name);
                countriesList.add(country);
            }
        } //end of rows iterator

        fis.close();
        for (Country c : countriesList) {
            super.setInstance(c);
            super.save();
        }
    } catch (IOException e) {
        errorMessage = e.getMessage();
    }
    System.out.println("eerrromessage..:" + errorMessage);
    FacesMessage message = null;
    if (errorMessage != null && errorMessage.length() > 3) {
        message = new FacesMessage("ERROR..:", "Country dosn't uploaded![" + errorMessage + "]");
    } else {
        message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    }
    FacesContext.getCurrentInstance().addMessage(null, message);

}

From source file:com.camel.action.location.PortAction.java

public void handleFileUpload(FileUploadEvent event) {
    try {/*  w  w  w . j ava  2  s. co  m*/
        List<Port> portsList = new ArrayList<Port>();

        //Create the input stream from the xlsx/xls file
        String fileName = event.getFile().getFileName();
        String portCode = "";
        String portName = "";
        String cityCode = "";
        String cityName = "";
        String countryCode = "";
        String countryName = "";
        InputStream fis = event.getFile().getInputstream();

        //Create Workbook instance for xlsx/xls file input stream
        Workbook workbook = null;
        if (fileName.toLowerCase().endsWith("xlsx")) {
            workbook = new XSSFWorkbook(fis);
        } else if (fileName.toLowerCase().endsWith("xls")) {
            workbook = new HSSFWorkbook(fis);
        }

        Sheet sheet = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        Row row = null;
        Iterator<Cell> cellIterator = null;
        Cell cell = null;
        City city = null;
        Port port = null;
        while (rowIterator.hasNext()) {
            portCode = "";
            portName = "";
            cityCode = "";
            cityName = "";
            countryCode = "";
            countryName = "";

            row = rowIterator.next();
            cellIterator = row.cellIterator();

            if (row.getRowNum() == 0)
                continue;

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    if (portCode.equalsIgnoreCase("")) {
                        portCode = cell.getStringCellValue().trim();
                    } else if (portName.equalsIgnoreCase("")) {
                        portName = cell.getStringCellValue().trim();
                    } else if (cityCode.equalsIgnoreCase("")) {
                        cityCode = cell.getStringCellValue().trim();
                    } else if (cityName.equalsIgnoreCase("")) {
                        cityName = cell.getStringCellValue().trim();
                    } else if (countryCode.equalsIgnoreCase("")) {
                        countryCode = cell.getStringCellValue().trim();
                    } else if (countryName.equalsIgnoreCase("")) {
                        countryName = cell.getStringCellValue().trim();
                    }
                    break;
                }
            } //end of cell iterator
            if (countryCode.equals("#N/A"))
                continue;

            country = findCountry(countryCode);
            city = findCity(cityCode);
            if (country != null && city != null) {
                port = new Port();
                port.setPortType(PortType.SEAPORT);
                port.setPortCode(portCode);
                port.setPortName(portName);
                port.setCity(city);
                port.setCountry(country);
                portsList.add(port);
            }

        } //end of rows iterator

        //close file input stream
        fis.close();
        for (Port c : portsList) {
            super.setInstance(c);
            super.save();
        }
    } catch (IOException e) {
        e.printStackTrace();
    }

    FacesMessage message = new FacesMessage("Succesful", event.getFile().getFileName() + " is uploaded.");
    FacesContext.getCurrentInstance().addMessage(null, message);

    country = null;

}

From source file:com.canoo.webtest.plugins.exceltest.ExcelStructureFilter.java

License:Open Source License

public void doExecute() throws Exception {
    final Workbook excelWorkbook = getExcelWorkbook();
    final DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
    final Document doc = builder.newDocument();
    final Element root = doc.createElement("excelWorkbook");
    doc.appendChild(root);/*from w  w  w  .ja va  2  s .c o  m*/
    //root.setAttribute("backup", String.valueOf(excelWorkbook.getBackupFlag()));
    root.setAttribute("backup", String.valueOf(Boolean.FALSE));
    root.setAttribute("numberOfFonts", String.valueOf(excelWorkbook.getNumberOfFonts()));
    root.setAttribute("numberOfCellStyles", String.valueOf(excelWorkbook.getNumCellStyles()));
    root.setAttribute("numberOfNames", String.valueOf(excelWorkbook.getNumberOfNames()));
    final Element sheets = doc.createElement("sheets");
    for (int i = 0; i < excelWorkbook.getNumberOfSheets(); i++) {
        final Sheet sheetAt = excelWorkbook.getSheetAt(i);
        final Element sheetElement = doc.createElement("sheet");
        sheetElement.setAttribute("index", String.valueOf(i));
        sheetElement.setAttribute("name", excelWorkbook.getSheetName(i));
        sheetElement.setAttribute("firstRow", String.valueOf(sheetAt.getFirstRowNum()));
        sheetElement.setAttribute("lastRow", String.valueOf(sheetAt.getLastRowNum()));
        sheetElement.setAttribute("physicalRows", String.valueOf(sheetAt.getPhysicalNumberOfRows()));
        sheetElement.setAttribute("defaultRowHeight", String.valueOf(sheetAt.getDefaultRowHeight()));
        sheetElement.setAttribute("defaultColumnWidth", String.valueOf(sheetAt.getDefaultColumnWidth()));
        sheetElement.setAttribute("fitToPage", String.valueOf(sheetAt.getFitToPage()));
        sheets.appendChild(sheetElement);
    }
    root.appendChild(sheets);
    final StringWriter sw = new StringWriter();
    writeXmlFile(doc, sw);
    ContextHelper.defineAsCurrentResponse(getContext(), sw.toString(), "text/xml", getClass());
}

From source file:com.cn.led.DrawFromExcel.java

public static void drawExcelToPNG(String excelFilePath, String pngFilePath, int[] fromIndex, int[] toIndex)
        throws Exception {
    // ???// ww  w  .  j  av a2s  .c  om
    //        int[] fromIndex = {0, 0};
    //        int[] toIndex = {1, 5};

    int imageWidth = 0;
    int imageHeight = 0;

    File file = new File(excelFilePath);
    Workbook wb = WorkbookFactory.create(file);
    Sheet sheet = wb.getSheetAt(0);
    List<CellRangeAddress> rangeAddress = sheet.getMergedRegions(); // ?sheet????

    // ????
    int rowSum = sheet.getPhysicalNumberOfRows();
    int colSum = sheet.getRow(0).getPhysicalNumberOfCells();
    if (fromIndex[0] > rowSum || fromIndex[0] > toIndex[0] || toIndex[0] > rowSum) {
        throw new Exception("the rowIndex of the area is wrong!");
    }
    if (fromIndex[1] > colSum || fromIndex[1] > toIndex[1] || toIndex[1] > colSum) {
        throw new Exception("the colIndex of the area is wrong!");
    }

    // ?Cell???
    int rowSize = toIndex[0] + 1;
    int colSize = toIndex[1] + 1;

    // ?????
    UserCell[][] cells = new UserCell[rowSize][colSize];
    int[] rowPixPos = new int[rowSize + 1];
    rowPixPos[0] = 0;
    int[] colPixPos = new int[colSize + 1];
    colPixPos[0] = 0;
    for (int i = 0; i < rowSize; i++) {

        for (int j = 0; j < colSize; j++) {

            cells[i][j] = new UserCell();
            cells[i][j].setCell(sheet.getRow(i).getCell(j));
            cells[i][j].setRow(i);
            cells[i][j].setCol(j);
            boolean ifShow = (i >= fromIndex[0]) && (j >= fromIndex[1]); //?
            ifShow = ifShow && !(sheet.isColumnHidden(j) || sheet.getRow(i).getZeroHeight()); //????
            cells[i][j].setShow(ifShow);

            // 
            float widthPix = (!ifShow ? 0 : sheet.getColumnWidthInPixels(j)); // ???0
            if (i == fromIndex[0]) {
                imageWidth += widthPix;
            }
            colPixPos[j + 1] = (int) (widthPix + colPixPos[j]);

        }

        // 
        boolean ifShow = (i >= fromIndex[0]); //?
        ifShow = ifShow && !sheet.getRow(i).getZeroHeight(); //????
        float heightPoint = !ifShow ? 0 : sheet.getRow(i).getHeightInPoints(); // ???0
        imageHeight += heightPoint;
        rowPixPos[i + 1] = (int) (heightPoint * 96 / 80) + rowPixPos[i];
    }

    imageHeight = imageHeight * 96 / 80 + 2;

    wb.close();

    List<Grid> grids = new ArrayList<>();
    for (int i = 0; i < rowSize; i++) {
        for (int j = 0; j < colSize; j++) {
            Grid grid = new Grid();
            // ??
            grid.setX(colPixPos[j]);
            grid.setY(rowPixPos[i]);
            grid.setWidth(colPixPos[j + 1] - colPixPos[j]);
            grid.setHeight(rowPixPos[i + 1] - rowPixPos[i]);
            grid.setRow(cells[i][j].getRow());
            grid.setCol(cells[i][j].getCol());
            grid.setShow(cells[i][j].isShow());

            // ???
            int[] isInMergedStatus = isInMerged(grid.getRow(), grid.getCol(), rangeAddress);

            if (isInMergedStatus[0] == 0 && isInMergedStatus[1] == 0) {
                // ???????
                continue;
            } else if (isInMergedStatus[0] != -1 && isInMergedStatus[1] != -1) {
                // ??????                 
                int lastRowPos = isInMergedStatus[0] > rowSize - 1 ? rowSize - 1 : isInMergedStatus[0];
                int lastColPos = isInMergedStatus[1] > colSize - 1 ? colSize - 1 : isInMergedStatus[1];

                grid.setWidth(colPixPos[lastColPos + 1] - colPixPos[j]);
                grid.setHeight(rowPixPos[lastRowPos + 1] - rowPixPos[i]);

            }

            // ?
            CellStyle cs = cells[i][j].getCell().getCellStyle();
            if (cs.getFillPattern() == CellStyle.SOLID_FOREGROUND) {
                grid.setBgColor(cells[i][j].getCell().getCellStyle().getFillForegroundColorColor());
            }

            // 
            String strCell = "";
            switch (cells[i][j].getCell().getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cells[i][j].getCell().getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cells[i][j].getCell().getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                } catch (IllegalStateException e) {
                    strCell = String.valueOf(cells[i][j].getCell().getRichStringCellValue());
                }
                break;
            default:
                strCell = "";
            }
            //                System.out.println("strCell:" + strCell);

            if (cells[i][j].getCell().getCellStyle().getDataFormatString().contains("0.00%")) {
                try {
                    double dbCell = Double.valueOf(strCell);
                    strCell = new DecimalFormat("#.00").format(dbCell * 100) + "%";
                } catch (NumberFormatException e) {
                }
            }

            grid.setText(strCell.matches("\\w*\\.0") ? strCell.substring(0, strCell.length() - 2) : strCell);

            grids.add(grid);
        }
    }

    BufferedImage image = new BufferedImage(imageWidth, imageHeight + 1, BufferedImage.TYPE_INT_RGB);
    Graphics2D g2d = image.createGraphics();
    // 
    //g2d.setRenderingHint(SunHints.KEY_ANTIALIASING, SunHints.VALUE_ANTIALIAS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIASING, SunHints.VALUE_TEXT_ANTIALIAS_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_STROKE_CONTROL, SunHints.VALUE_STROKE_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIAS_LCD_CONTRAST, 140);
    //g2d.setRenderingHint(SunHints.KEY_FRACTIONALMETRICS, SunHints.VALUE_FRACTIONALMETRICS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_RENDERING, SunHints.VALUE_RENDER_DEFAULT);

    g2d.setColor(Color.white);
    g2d.fillRect(0, 0, imageWidth, imageHeight + 1);

    // 
    Iterator<Grid> iterable = grids.iterator();
    while (iterable.hasNext()) {
        Grid g = iterable.next();
        if (!g.isShow()) {
            continue;
        }

        // 
        g2d.setColor(g.getBgColor() == null ? Color.black : g.getBgColor());
        g2d.fillRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // 
        g2d.setColor(Color.red);
        g2d.setStroke(new BasicStroke(1));
        g2d.drawRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // ,
        g2d.setColor(g.getFtColor());

        Font font = g.getFont();
        FontMetrics fm = g2d.getFontMetrics(font);
        int strWidth = fm.stringWidth(g.getText());// ??
        g2d.setFont(font);

        g2d.drawString(g.getText(), g.getX() + (g.getWidth() - strWidth) / 2,
                g.getY() + (g.getHeight() - font.getSize()) / 2 + font.getSize());
    }

    g2d.dispose();
    ImageIO.write(image, "png", new File(pngFilePath));
    //        BMPWriter.write(image, new File(pngFilePath));
    System.out.println("Output to png file Success!");
}

From source file:com.cn.led.DrawFromExcel.java

public static void drawExcelToBMP(String excelFilePath, String bmpFilePath, int[] fromIndex, int[] toIndex)
        throws Exception {
    // ???/*  w  w w  .  j a v a2  s  . c  o m*/
    //        int[] fromIndex = {0, 0};
    //        int[] toIndex = {1, 5};

    int imageWidth = 0;
    int imageHeight = 0;

    Workbook wb = WorkbookFactory.create(new File(excelFilePath));
    Sheet sheet = wb.getSheetAt(0);
    List<CellRangeAddress> rangeAddress = sheet.getMergedRegions(); // ?sheet????

    // ????
    int rowSum = sheet.getPhysicalNumberOfRows();
    int colSum = sheet.getRow(0).getPhysicalNumberOfCells();
    if (fromIndex[0] > rowSum || fromIndex[0] > toIndex[0] || toIndex[0] > rowSum) {
        throw new Exception("the rowIndex of the area is wrong!");
    }
    if (fromIndex[1] > colSum || fromIndex[1] > toIndex[1] || toIndex[1] > colSum) {
        throw new Exception("the colIndex of the area is wrong!");
    }

    // ?Cell???
    int rowSize = toIndex[0] + 1;
    int colSize = toIndex[1] + 1;

    // ?????
    UserCell[][] cells = new UserCell[rowSize][colSize];
    int[] rowPixPos = new int[rowSize + 1];
    rowPixPos[0] = 0;
    int[] colPixPos = new int[colSize + 1];
    colPixPos[0] = 0;
    for (int i = 0; i < rowSize; i++) {

        for (int j = 0; j < colSize; j++) {

            cells[i][j] = new UserCell();
            cells[i][j].setCell(sheet.getRow(i).getCell(j));
            cells[i][j].setRow(i);
            cells[i][j].setCol(j);
            boolean ifShow = (i >= fromIndex[0]) && (j >= fromIndex[1]); //?
            ifShow = ifShow && !(sheet.isColumnHidden(j) || sheet.getRow(i).getZeroHeight()); //????
            cells[i][j].setShow(ifShow);

            // 
            float widthPix = (!ifShow ? 0 : sheet.getColumnWidthInPixels(j)); // ???0
            if (i == fromIndex[0]) {
                imageWidth += widthPix;
            }
            colPixPos[j + 1] = (int) (widthPix + colPixPos[j]);

        }

        // 
        boolean ifShow = (i >= fromIndex[0]); //?
        ifShow = ifShow && !sheet.getRow(i).getZeroHeight(); //????
        float heightPoint = !ifShow ? 0 : sheet.getRow(i).getHeightInPoints(); // ???0
        imageHeight += heightPoint;
        rowPixPos[i + 1] = (int) (heightPoint * 96 / 80) + rowPixPos[i];
    }

    imageHeight = imageHeight * 96 / 80 + 2;
    //        imageWidth = imageWidth;

    wb.close();

    List<Grid> grids = new ArrayList<>();
    for (int i = 0; i < rowSize; i++) {
        for (int j = 0; j < colSize; j++) {
            Grid grid = new Grid();
            // ??
            grid.setX(colPixPos[j]);
            grid.setY(rowPixPos[i]);
            grid.setWidth(colPixPos[j + 1] - colPixPos[j]);
            grid.setHeight(rowPixPos[i + 1] - rowPixPos[i]);
            grid.setRow(cells[i][j].getRow());
            grid.setCol(cells[i][j].getCol());
            grid.setShow(cells[i][j].isShow());

            // ???
            int[] isInMergedStatus = isInMerged(grid.getRow(), grid.getCol(), rangeAddress);

            if (isInMergedStatus[0] == 0 && isInMergedStatus[1] == 0) {
                // ???????
                continue;
            } else if (isInMergedStatus[0] != -1 && isInMergedStatus[1] != -1) {
                // ??????                 
                int lastRowPos = isInMergedStatus[0] > rowSize - 1 ? rowSize - 1 : isInMergedStatus[0];
                int lastColPos = isInMergedStatus[1] > colSize - 1 ? colSize - 1 : isInMergedStatus[1];

                grid.setWidth(colPixPos[lastColPos + 1] - colPixPos[j]);
                grid.setHeight(rowPixPos[lastRowPos + 1] - rowPixPos[i]);

            }

            // ?
            CellStyle cs = cells[i][j].getCell().getCellStyle();
            if (cs.getFillPattern() == CellStyle.SOLID_FOREGROUND) {
                grid.setBgColor(cells[i][j].getCell().getCellStyle().getFillForegroundColorColor());
            }

            // 
            String strCell = "";
            switch (cells[i][j].getCell().getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
                strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_STRING:
                strCell = cells[i][j].getCell().getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                strCell = String.valueOf(cells[i][j].getCell().getBooleanCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                try {
                    strCell = String.valueOf(cells[i][j].getCell().getNumericCellValue());
                } catch (IllegalStateException e) {
                    strCell = String.valueOf(cells[i][j].getCell().getRichStringCellValue());
                }
                break;
            default:
                strCell = "";
            }
            //                System.out.println("strCell:" + strCell);

            if (cells[i][j].getCell().getCellStyle().getDataFormatString().contains("0.00%")) {
                try {
                    double dbCell = Double.valueOf(strCell);
                    strCell = new DecimalFormat("#.00").format(dbCell * 100) + "%";
                } catch (NumberFormatException e) {
                }
            }

            grid.setText(strCell.matches("\\w*\\.0") ? strCell.substring(0, strCell.length() - 2) : strCell);

            grids.add(grid);
        }
    }

    BufferedImage image = new BufferedImage(imageWidth, imageHeight + 1, BufferedImage.TYPE_INT_RGB);
    Graphics2D g2d = image.createGraphics();
    // 
    //g2d.setRenderingHint(SunHints.KEY_ANTIALIASING, SunHints.VALUE_ANTIALIAS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIASING, SunHints.VALUE_TEXT_ANTIALIAS_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_STROKE_CONTROL, SunHints.VALUE_STROKE_DEFAULT);
    //g2d.setRenderingHint(SunHints.KEY_TEXT_ANTIALIAS_LCD_CONTRAST, 140);
    //g2d.setRenderingHint(SunHints.KEY_FRACTIONALMETRICS, SunHints.VALUE_FRACTIONALMETRICS_OFF);
    //g2d.setRenderingHint(SunHints.KEY_RENDERING, SunHints.VALUE_RENDER_DEFAULT);

    g2d.setColor(Color.white);
    g2d.fillRect(0, 0, imageWidth, imageHeight + 1);

    // 
    Iterator<Grid> iterable = grids.iterator();
    while (iterable.hasNext()) {
        Grid g = iterable.next();
        if (!g.isShow()) {
            continue;
        }

        // 
        g2d.setColor(g.getBgColor() == null ? Color.black : g.getBgColor());
        g2d.fillRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // 
        g2d.setColor(Color.red);
        g2d.setStroke(new BasicStroke(1));
        g2d.drawRect(g.getX(), g.getY(), g.getWidth(), g.getHeight());

        // ,
        g2d.setColor(g.getFtColor());

        Font font = g.getFont();
        FontMetrics fm = g2d.getFontMetrics(font);
        int strWidth = fm.stringWidth(g.getText());// ??
        g2d.setFont(font);

        g2d.drawString(g.getText(), g.getX() + (g.getWidth() - strWidth) / 2,
                g.getY() + (g.getHeight() - font.getSize()) / 2 + font.getSize());
    }

    g2d.dispose();
    BMPWriter.write(image, new File(bmpFilePath));
    System.out.println("Output to png file Success!");
}