复制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);
}
}
}
} }