List of usage examples for org.apache.poi.ss.usermodel Sheet getColumnWidthInPixels
float getColumnWidthInPixels(int columnIndex);
Please note, that this method works correctly only for workbooks with the default font size (Arial 10pt for .xls and Calibri 11pt for .xlsx).
From source file:com.cn.led.DrawFromExcel.java
public static void drawExcelToPNG(String excelFilePath, String pngFilePath, int[] fromIndex, int[] toIndex) throws Exception { // ???/*w w w .ja v a2 s .co m*/ // 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 { // ???//from w w w . j av a2s .co 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!"); }
From source file:org.tiefaces.components.websheet.utility.PicturesUtility.java
License:MIT License
/** * Gets the anchor size./*from w ww . j ava 2s . co m*/ * * @param sheet1 * the sheet 1 * @param fcell * the fcell * @param cell * the cell * @param anchor * the anchor * @return the anchor size */ public static AnchorSize getAnchorSize(final Sheet sheet1, final FacesCell fcell, final Cell cell, final ClientAnchor anchor) { if (!(sheet1 instanceof XSSFSheet)) { return null; } double picWidth = 0.0; double picHeight = 0.0; int left = anchor.getDx1() / org.apache.poi.util.Units.EMU_PER_PIXEL; int top = (int) ((double) anchor.getDy1() / org.apache.poi.util.Units.EMU_PER_PIXEL / WebSheetUtility.PICTURE_HEIGHT_ADJUST); int right = anchor.getDx2() / org.apache.poi.util.Units.EMU_PER_PIXEL; int bottom = (int) ((double) anchor.getDy2() / org.apache.poi.util.Units.EMU_PER_PIXEL / WebSheetUtility.PICTURE_HEIGHT_ADJUST); double cellWidth = 0.0; double cellHeight = 0.0; if ((cell != null) && (fcell != null)) { for (int col = cell.getColumnIndex(); col < cell.getColumnIndex() + fcell.getColspan(); col++) { cellWidth += sheet1.getColumnWidthInPixels(col); } double lastCellWidth = sheet1.getColumnWidthInPixels(cell.getColumnIndex() + fcell.getColspan() - 1); for (int rowIndex = cell.getRowIndex(); rowIndex < cell.getRowIndex() + fcell.getRowspan(); rowIndex++) { cellHeight += WebSheetUtility.pointsToPixels(sheet1.getRow(rowIndex).getHeightInPoints()); } double lastCellHeight = WebSheetUtility .pointsToPixels(sheet1.getRow(cell.getRowIndex() + fcell.getRowspan() - 1).getHeightInPoints()); picWidth = cellWidth - lastCellWidth + right - left; picHeight = cellHeight - lastCellHeight + bottom - top; } else { for (short col = anchor.getCol1(); col < anchor.getCol2(); col++) { picWidth += sheet1.getColumnWidthInPixels(col); } for (int rowindex = anchor.getRow1(); rowindex < anchor.getRow2(); rowindex++) { Row row = sheet1.getRow(rowindex); if (row != null) { picHeight += WebSheetUtility.pointsToPixels(row.getHeightInPoints()); } } } return new AnchorSize(left, top, (int) picWidth, (int) picHeight, cellWidth, cellHeight); }