http://blog.csdn.net/wutbiao/article/details/8696446
项目中,我们经常使用Poi来操作excel,但是经常碰到一个不方便的地方,不如最简单常用的,在两个excel之间复制sheet,复制行,复制单元格等。
我这里是最近刚做的一个简单封装。不是很好,必须始终传过去一个“目标workbook“的引用,留下个mark!
源码如下:
- public class POIUtils {
- // /**
- // * 把一个excel中的cellstyletable复制到另一个excel,这里会报错,不能用这种方法,不明白呀?????
- // * @param fromBook
- // * @param toBook
- // */
- // public static void copyBookCellStyle(HSSFWorkbook fromBook,HSSFWorkbook toBook){
- // for(short i=0;i<fromBook.getNumCellStyles();i++){
- // HSSFCellStyle fromStyle=fromBook.getCellStyleAt(i);
- // HSSFCellStyle toStyle=toBook.getCellStyleAt(i);
- // if(toStyle==null){
- // toStyle=toBook.createCellStyle();
- // }
- // copyCellStyle(fromStyle,toStyle);
- // }
- // }
- /**
- * 复制一个单元格样式到目的单元格样式
- * @param fromStyle
- * @param toStyle
- */
- public static void copyCellStyle(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());
- //背景和前景
- toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
- toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
- toStyle.setDataFormat(fromStyle.getDataFormat());
- toStyle.setFillPattern(fromStyle.getFillPattern());
- // toStyle.setFont(fromStyle.getFont(null));
- toStyle.setHidden(fromStyle.getHidden());
- toStyle.setIndention(fromStyle.getIndention());//首行缩进
- toStyle.setLocked(fromStyle.getLocked());
- toStyle.setRotation(fromStyle.getRotation());//旋转
- toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
- toStyle.setWrapText(fromStyle.getWrapText());
- }
- /**
- * Sheet复制
- * @param fromSheet
- * @param toSheet
- * @param copyValueFlag
- */
- public static void copySheet(HSSFWorkbook wb,HSSFSheet fromSheet, HSSFSheet toSheet,
- boolean copyValueFlag) {
- //合并区域处理
- mergerRegion(fromSheet, toSheet);
- for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {
- HSSFRow tmpRow = (HSSFRow) rowIt.next();
- HSSFRow newRow = toSheet.createRow(tmpRow.getRowNum());
- //行复制
- copyRow(wb,tmpRow,newRow,copyValueFlag);
- }
- }
- /**
- * 行复制功能
- * @param fromRow
- * @param toRow
- */
- public static void copyRow(HSSFWorkbook wb,HSSFRow fromRow,HSSFRow toRow,boolean copyValueFlag){
- for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext();) {
- HSSFCell tmpCell = (HSSFCell) cellIt.next();
- HSSFCell newCell = toRow.createCell(tmpCell.getCellNum());
- copyCell(wb,tmpCell, newCell, copyValueFlag);
- }
- }
- /**
- * 复制原有sheet的合并单元格到新创建的sheet
- *
- * @param sheetCreat 新创建sheet
- * @param sheet 原有的sheet
- */
- public static void mergerRegion(HSSFSheet fromSheet, HSSFSheet toSheet) {
- int sheetMergerCount = fromSheet.getNumMergedRegions();
- for (int i = 0; i < sheetMergerCount; i++) {
- Region mergedRegionAt = fromSheet.getMergedRegionAt(i);
- toSheet.addMergedRegion(mergedRegionAt);
- }
- }
- /**
- * 复制单元格
- *
- * @param srcCell
- * @param distCell
- * @param copyValueFlag
- * true则连同cell的内容一起复制
- */
- public static void copyCell(HSSFWorkbook wb,HSSFCell srcCell, HSSFCell distCell,
- boolean copyValueFlag) {
- HSSFCellStyle newstyle=wb.createCellStyle();
- copyCellStyle(srcCell.getCellStyle(), newstyle);
- distCell.setEncoding(srcCell.getEncoding());
- //样式
- distCell.setCellStyle(newstyle);
- //评论
- if (srcCell.getCellComment() != null) {
- distCell.setCellComment(srcCell.getCellComment());
- }
- // 不同数据类型处理
- int srcCellType = srcCell.getCellType();
- distCell.setCellType(srcCellType);
- if (copyValueFlag) {
- if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
- if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
- distCell.setCellValue(srcCell.getDateCellValue());
- } else {
- distCell.setCellValue(srcCell.getNumericCellValue());
- }
- } else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
- distCell.setCellValue(srcCell.getRichStringCellValue());
- } else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
- // nothing21
- } else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
- distCell.setCellValue(srcCell.getBooleanCellValue());
- } else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
- distCell.setCellErrorValue(srcCell.getErrorCellValue());
- } else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
- distCell.setCellFormula(srcCell.getCellFormula());
- } else { // nothing29
- }
- }
- }
- }
-------------------------------------------------
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class POIDemo {
public static void main(String[] args) throws InvalidFormatException, IOException {
Workbook wb = new HSSFWorkbook();
wb.createSheet();
InputStream input = new FileInputStream("filePath1");
tranferValue(input, wb);
input = new FileInputStream("filePath2");
tranferValue(input, wb);
FileOutputStream fileOut = new FileOutputStream("yourExcelName.xls");
try {
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
}finally{
fileOut.close();
}
}
private static void tranferValue(InputStream input, Workbook outwb) throws InvalidFormatException, IOException{
Sheet outSheet = outwb.getSheetAt(0);
int outLastRowNum = outSheet.getLastRowNum();
if(outLastRowNum>0)outLastRowNum++;
Workbook wb = WorkbookFactory.create(input);
int sheetNums = wb.getNumberOfSheets();
for(int n = 0; n < sheetNums; n++){
Sheet sheet = wb.getSheetAt(n);
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for(int i = firstRowNum; i <= lastRowNum; i++){
Row row = sheet.getRow(i);
if(row != null){
Row outRow = outSheet.createRow(outLastRowNum++);
int firstCellNum = row.getFirstCellNum();
int lastCellNum = row.getLastCellNum();
for(int j = firstCellNum; j < lastCellNum; j++){
Cell cell = row.getCell(j);
Cell outCell = outRow.createCell(j);
if(cell != null){
copyValue(cell, outCell);
copyCellStyle(cell, outCell, outwb);
}
}
}
}
}
}
private static void copyValue(Cell formCell, Cell toCell) {
switch(formCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
toCell.setCellValue(formCell.getRichStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if(DateUtil.isCellDateFormatted(formCell)) {
toCell.setCellValue(formCell.getDateCellValue());
} else {
toCell.setCellValue(formCell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
toCell.setCellValue(formCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
toCell.setCellValue(formCell.getCellFormula());
break;
default:
}
}
private static void copyCellStyle(Cell cell, Cell cellout, Workbook wbout) {
CellStyle cellStyleout = wbout.createCellStyle();
cellStyleout.cloneStyleFrom(cell.getCellStyle());
cellout.setCellStyle(cellStyleout);
}
}