自己写的POIUtil，主要解决从不同的HSSFWorkbook复制sheet以及根据单元格插入图片等
复制sheet的原始代码网上找的，但是小问题很多，然后自己动手改了一下；
根据单元格信息动态插入图片，如果单元格有文字，图片的位置会在文字之后，如果同样的位置已有图片则会往下插入。
import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import javax.imageio.ImageIO; import java.awt.image.BufferedImage; import java.io.*; import java.math.BigDecimal; import java.util.ArrayList; import java.util.List; /** * Created with IntelliJ IDEA. * Author: Duelsol * Date: 14-7-18 * Time: 下午4:05 */ public class POIUtil { private static Logger logger = Logger.getLogger(POIUtil.class); private POIUtil() {} /** * 复制工作表 * 此方法主要用于复制2个不同HSSFWorkbook间的工作表 */ public static void copySheet(HSSFWorkbook fromWorkbook, HSSFWorkbook toWorkbook, int fromSheetIndex, int toSheetIndex) { toWorkbook.setSheetName(toSheetIndex, fromWorkbook.getSheetName(fromSheetIndex)); HSSFSheet fromSheet = fromWorkbook.getSheetAt(fromSheetIndex); for (int i = fromSheet.getFirstRowNum(); i <= fromSheet.getLastRowNum(); i++) { copyRows(fromWorkbook, toWorkbook, fromSheetIndex, toSheetIndex, i, i, i); } } /** * 复制行 * 此方法主要用于复制2个不同HSSFWorkbook间的行 */ public static void copyRows(HSSFWorkbook fromWorkbook, HSSFWorkbook toWorkbook, int fromSheetIndex, int toSheetIndex, int startRow, int endRow, int position) { HSSFSheet fromSheet = fromWorkbook.getSheetAt(fromSheetIndex); HSSFSheet toSheet = toWorkbook.getSheetAt(toSheetIndex); int i; int j; if ((startRow == -1) || (endRow == -1)) { return; } List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>(); for (i = 0; i < fromSheet.getNumMergedRegions(); i++) { oldRanges.add(fromSheet.getMergedRegion(i)); } // 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式 for (CellRangeAddress oldRange : oldRanges) { CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(), oldRange.getFirstColumn(), oldRange.getLastColumn()); if (oldRange.getFirstRow() >= startRow && oldRange.getLastRow() <= endRow) { int targetRowFrom = oldRange.getFirstRow() - startRow + position; int targetRowTo = oldRange.getLastRow() - startRow + position; oldRange.setFirstRow(targetRowFrom); oldRange.setLastRow(targetRowTo); toSheet.addMergedRegion(oldRange); fromSheet.addMergedRegion(newRange); } } // 设置列宽 for (i = startRow; i <= endRow; i++) { HSSFRow fromRow = fromSheet.getRow(i); if (fromRow != null) { for (j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) { toSheet.setColumnWidth(j, fromSheet.getColumnWidth(j)); toSheet.setColumnHidden(j, false); } break; } } // 拷贝行并填充数据 for (; i <= endRow; i++) { HSSFRow fromRow = fromSheet.getRow(i); if (fromRow == null) { continue; } HSSFRow toRow = toSheet.createRow(i - startRow + position); toRow.setHeight(fromRow.getHeight()); for (j = fromRow.getFirstCellNum(); j <= fromRow.getPhysicalNumberOfCells(); j++) { HSSFCell fromCell = fromRow.getCell(j); if (fromCell == null) { continue; } HSSFCell toCell = toRow.createCell(j); HSSFCellStyle toStyle = toWorkbook.createCellStyle(); copyCellStyle(fromWorkbook, toWorkbook, fromCell.getCellStyle(), toStyle); toCell.setCellStyle(toStyle); int cType = fromCell.getCellType(); toCell.setCellType(cType); switch (cType) { case HSSFCell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); // System.out.println("--------TYPE_BOOLEAN:" + // targetCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: toCell.setCellErrorValue(fromCell.getErrorCellValue()); // System.out.println("--------TYPE_ERROR:" + // targetCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: toCell.setCellFormula(parseFormula(fromCell.getCellFormula())); // System.out.println("--------TYPE_FORMULA:" + // targetCell.getCellFormula()); break; case HSSFCell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); // System.out.println("--------TYPE_NUMERIC:" + // targetCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getRichStringCellValue()); // System.out.println("--------TYPE_STRING:" + i + // targetCell.getRichStringCellValue()); break; } } } } /** * 复制行 * 如果是同一个HSSFWorkbook中的行请用此方法 */ public static void copyRows(HSSFWorkbook workbook, int fromSheetIndex, int toSheetIndex, int startRow, int endRow, int position) { HSSFSheet fromSheet = workbook.getSheetAt(fromSheetIndex); HSSFSheet toSheet = workbook.getSheetAt(toSheetIndex); int i; int j; if ((startRow == -1) || (endRow == -1)) { return; } List<CellRangeAddress> oldRanges = new ArrayList<CellRangeAddress>(); for (i = 0; i < fromSheet.getNumMergedRegions(); i++) { oldRanges.add(fromSheet.getMergedRegion(i)); } // 拷贝合并的单元格。原理：复制当前合并单元格后，原位置的格式会移动到新位置，需在原位置生成旧格式 for (CellRangeAddress oldRange : oldRanges) { CellRangeAddress newRange = new CellRangeAddress(oldRange.getFirstRow(), oldRange.getLastRow(), oldRange.getFirstColumn(), oldRange.getLastColumn()); if (oldRange.getFirstRow() >= startRow && oldRange.getLastRow() <= endRow) { int targetRowFrom = oldRange.getFirstRow() - startRow + position; int targetRowTo = oldRange.getLastRow() - startRow + position; oldRange.setFirstRow(targetRowFrom); oldRange.setLastRow(targetRowTo); toSheet.addMergedRegion(oldRange); fromSheet.addMergedRegion(newRange); } } // 设置列宽 for (i = startRow; i <= endRow; i++) { HSSFRow fromRow = fromSheet.getRow(i); if (fromRow != null) { for (j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) { toSheet.setColumnWidth(j, fromSheet.getColumnWidth(j)); toSheet.setColumnHidden(j, false); } break; } } // 拷贝行并填充数据 for (; i <= endRow; i++) { HSSFRow fromRow = fromSheet.getRow(i); if (fromRow == null) { continue; } HSSFRow toRow = toSheet.createRow(i - startRow + position); toRow.setHeight(fromRow.getHeight()); for (j = fromRow.getFirstCellNum(); j <= fromRow.getPhysicalNumberOfCells(); j++) { HSSFCell fromCell = fromRow.getCell(j); if (fromCell == null) { continue; } HSSFCell toCell = toRow.createCell(j); toCell.setCellStyle(fromCell.getCellStyle()); int cType = fromCell.getCellType(); toCell.setCellType(cType); switch (cType) { case HSSFCell.CELL_TYPE_BOOLEAN: toCell.setCellValue(fromCell.getBooleanCellValue()); // System.out.println("--------TYPE_BOOLEAN:" + // targetCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: toCell.setCellErrorValue(fromCell.getErrorCellValue()); // System.out.println("--------TYPE_ERROR:" + // targetCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: toCell.setCellFormula(parseFormula(fromCell.getCellFormula())); // System.out.println("--------TYPE_FORMULA:" + // targetCell.getCellFormula()); break; case HSSFCell.CELL_TYPE_NUMERIC: toCell.setCellValue(fromCell.getNumericCellValue()); // System.out.println("--------TYPE_NUMERIC:" + // targetCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: toCell.setCellValue(fromCell.getRichStringCellValue()); // System.out.println("--------TYPE_STRING:" + i + // targetCell.getRichStringCellValue()); break; } } } } /** * 复制单元格样式 * 此方法主要用于复制2个不同HSSFWorkbook间的单元格样式 */ public static void copyCellStyle(HSSFWorkbook fromWorkbook, HSSFWorkbook toWorkbook, HSSFCellStyle fromStyle, HSSFCellStyle toStyle) { toStyle.setAlignment(fromStyle.getAlignment()); // 边框和边框颜色 toStyle.setBorderBottom(fromStyle.getBorderBottom()); toStyle.setBorderLeft(fromStyle.getBorderLeft()); toStyle.setBorderRight(fromStyle.getBorderRight()); toStyle.setBorderTop(fromStyle.getBorderTop()); toStyle.setTopBorderColor(fromStyle.getTopBorderColor()); toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor()); toStyle.setRightBorderColor(fromStyle.getRightBorderColor()); toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor()); // 字体 HSSFFont tofont = toWorkbook.createFont(); copyFont(fromStyle.getFont(fromWorkbook), tofont); toStyle.setFont(tofont); // 背景和前景 toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor()); toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor()); toStyle.setDataFormat(fromStyle.getDataFormat()); toStyle.setFillPattern(fromStyle.getFillPattern()); toStyle.setHidden(fromStyle.getHidden()); toStyle.setIndention(fromStyle.getIndention()); toStyle.setLocked(fromStyle.getLocked()); toStyle.setRotation(fromStyle.getRotation()); toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment()); toStyle.setWrapText(fromStyle.getWrapText()); } /** * 复制字体 * 此方法主要用于复制2个不同HSSFWorkbook间的字体 */ public static void copyFont(HSSFFont fromFont, HSSFFont toFont) { toFont.setBoldweight(fromFont.getBoldweight()); toFont.setCharSet(fromFont.getCharSet()); toFont.setColor(fromFont.getColor()); toFont.setFontHeight(fromFont.getFontHeight()); toFont.setFontHeightInPoints(fromFont.getFontHeightInPoints()); toFont.setFontName(fromFont.getFontName()); toFont.setItalic(fromFont.getItalic()); toFont.setStrikeout(fromFont.getStrikeout()); toFont.setTypeOffset(fromFont.getTypeOffset()); toFont.setUnderline(fromFont.getUnderline()); } private static String parseFormula(String pPOIFormula) { final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$ StringBuffer result; int index; result = new StringBuffer(); index = pPOIFormula.indexOf(cstReplaceString); if (index >= 0) { result.append(pPOIFormula.substring(0, index)); result.append(pPOIFormula.substring(index + cstReplaceString.length())); } else { result.append(pPOIFormula); } return result.toString(); } /** * 根据单元格信息动态插入图片，如果单元格有文字，图片的位置会在文字之后，如果同样的位置已有图片则会往下插入 * * @param workbook Excel * @param cell 单元格信息 * @param inputStream 图片输入流 * @param scale 图片缩放，传入null表示原始尺寸，其余表示图片高于行高的比（例如传入1.5，表示该图片占1.5个行高） */ public static void createPicture(HSSFWorkbook workbook, HSSFCell cell, InputStream inputStream, Double scale) { ByteArrayOutputStream byteArrayOut = null; try { byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(inputStream); ImageIO.write(bufferImg, "png", byteArrayOut); if (cell != null && (cell.getCellType() == HSSFCell.CELL_TYPE_STRING || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK)) { HSSFSheet sheet = cell.getSheet(); HSSFRow row = cell.getRow(); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); String cellValue = cell.getStringCellValue().contains("#{") ? cell.getStringCellValue().split("#\\{")[0] : cell.getStringCellValue(); int i = row.getRowNum(); short j = (short) cell.getColumnIndex(); int colWidth = sheet.getColumnWidth(cell.getColumnIndex()) / 32; // 单元格像素宽度 int wordWidth = cellValue.getBytes("GBK").length == 0 ? 0 : ((cellValue.getBytes("GBK").length + 2) * 8); // 单元格文本大致像素宽度 double pert = new BigDecimal(wordWidth).divide(new BigDecimal(colWidth), 10, BigDecimal.ROUND_HALF_UP).doubleValue(); int dx1 = new BigDecimal(pert * 1023).setScale(0, BigDecimal.ROUND_HALF_UP).intValue(); int dy1 = 0; List<HSSFShape> shapes = sheet.getDrawingPatriarch().getChildren(); for (HSSFShape shape : shapes) { HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor(); if (anchor.getRow1() == i && anchor.getCol1() == j && anchor.getDx1() == dx1 && anchor.getDy1() == dy1) { if (anchor.getDy2() >= 255) { i = anchor.getRow2() + 1; dy1 = 0; } else { i = anchor.getRow2(); dy1 = anchor.getDy2() + 1; } } } HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, 0, 0, j, i, j, i + 1); // 由于用了getPreferredSize所以dx2,dy2无效 anchor.setAnchorType(3); if (scale == null) { patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)).getPreferredSize(1.0); } else { double zoom = new BigDecimal(row.getHeight() / 15).divide(new BigDecimal(bufferImg.getHeight()), 10, BigDecimal.ROUND_HALF_UP).doubleValue(); // 行高像素与图片高度像素比例 patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG)).getPreferredSize(zoom * scale); } } } catch (IOException ioe) { logger.error("插入图片失败", ioe); } finally { if (byteArrayOut != null) { try { byteArrayOut.close(); } catch (IOException e) { logger.error("关闭ByteArrayOutputStream失败", e); } } } } }
