poi操作excel，复制sheet，复制行，复制单元格 .

http://blog.csdn.net/wutbiao/article/details/8696446

项目中，我们经常使用Poi来操作excel，但是经常碰到一个不方便的地方，不如最简单常用的，在两个excel之间复制sheet，复制行，复制单元格等。

我这里是最近刚做的一个简单封装。不是很好，必须始终传过去一个“目标workbook“的引用，留下个mark！

源码如下：

[java] view plain copy print ?

1. public class POIUtils {  
2. //  /**   
3. //   * 把一个excel中的cellstyletable复制到另一个excel，这里会报错，不能用这种方法，不明白呀？？？？？  
4. //   * @param fromBook   
5. //   * @param toBook   
6. //   */   
7. //  public static void copyBookCellStyle(HSSFWorkbook fromBook,HSSFWorkbook toBook){  
8. //      for(short i=0;i<fromBook.getNumCellStyles();i++){  
9. //          HSSFCellStyle fromStyle=fromBook.getCellStyleAt(i);  
10. //          HSSFCellStyle toStyle=toBook.getCellStyleAt(i);  
11. //          if(toStyle==null){  
12. //              toStyle=toBook.createCellStyle();  
13. //          }   
14. //          copyCellStyle(fromStyle,toStyle);  
15. //      }   
16. //  }   
17.     /** 
18.      * 复制一个单元格样式到目的单元格样式 
19.      * @param fromStyle 
20.      * @param toStyle 
21.      */  
22.     public static void copyCellStyle(HSSFCellStyle fromStyle,  
23.             HSSFCellStyle toStyle) {  
24.         toStyle.setAlignment(fromStyle.getAlignment());  
25.         //边框和边框颜色   
26.         toStyle.setBorderBottom(fromStyle.getBorderBottom());  
27.         toStyle.setBorderLeft(fromStyle.getBorderLeft());  
28.         toStyle.setBorderRight(fromStyle.getBorderRight());  
29.         toStyle.setBorderTop(fromStyle.getBorderTop());  
30.         toStyle.setTopBorderColor(fromStyle.getTopBorderColor());  
31.         toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());  
32.         toStyle.setRightBorderColor(fromStyle.getRightBorderColor());  
33.         toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());  
34.           
35.         //背景和前景   
36.         toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());  
37.         toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());  
38.           
39.         toStyle.setDataFormat(fromStyle.getDataFormat());  
40.         toStyle.setFillPattern(fromStyle.getFillPattern());  
41. //      toStyle.setFont(fromStyle.getFont(null));  
42.         toStyle.setHidden(fromStyle.getHidden());  
43.         toStyle.setIndention(fromStyle.getIndention());//首行缩进  
44.         toStyle.setLocked(fromStyle.getLocked());  
45.         toStyle.setRotation(fromStyle.getRotation());//旋转  
46.         toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());  
47.         toStyle.setWrapText(fromStyle.getWrapText());  
48.           
49.     }  
50.     /** 
51.      * Sheet复制 
52.      * @param fromSheet 
53.      * @param toSheet 
54.      * @param copyValueFlag 
55.      */  
56.     public static void copySheet(HSSFWorkbook wb,HSSFSheet fromSheet, HSSFSheet toSheet,  
57.             boolean copyValueFlag) {  
58.         //合并区域处理   
59.         mergerRegion(fromSheet, toSheet);  
60.         for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {  
61.             HSSFRow tmpRow = (HSSFRow) rowIt.next();  
62.             HSSFRow newRow = toSheet.createRow(tmpRow.getRowNum());  
63.             //行复制   
64.             copyRow(wb,tmpRow,newRow,copyValueFlag);  
65.         }  
66.     }  
67.     /** 
68.      * 行复制功能 
69.      * @param fromRow 
70.      * @param toRow 
71.      */  
72.     public static void copyRow(HSSFWorkbook wb,HSSFRow fromRow,HSSFRow toRow,boolean copyValueFlag){  
73.         for (Iterator cellIt = fromRow.cellIterator(); cellIt.hasNext();) {  
74.             HSSFCell tmpCell = (HSSFCell) cellIt.next();  
75.             HSSFCell newCell = toRow.createCell(tmpCell.getCellNum());  
76.             copyCell(wb,tmpCell, newCell, copyValueFlag);  
77.         }  
78.     }  
79.     /** 
80.     * 复制原有sheet的合并单元格到新创建的sheet 
81.     *  
82.     * @param sheetCreat 新创建sheet 
83.     * @param sheet      原有的sheet 
84.     */  
85.     public static void mergerRegion(HSSFSheet fromSheet, HSSFSheet toSheet) {  
86.        int sheetMergerCount = fromSheet.getNumMergedRegions();  
87.        for (int i = 0; i < sheetMergerCount; i++) {  
88.         Region mergedRegionAt = fromSheet.getMergedRegionAt(i);  
89.         toSheet.addMergedRegion(mergedRegionAt);  
90.        }  
91.     }  
92.     /** 
93.      * 复制单元格 
94.      *  
95.      * @param srcCell 
96.      * @param distCell 
97.      * @param copyValueFlag 
98.      *            true则连同cell的内容一起复制 
99.      */  
100.     public static void copyCell(HSSFWorkbook wb,HSSFCell srcCell, HSSFCell distCell,  
101.             boolean copyValueFlag) {  
102.         HSSFCellStyle newstyle=wb.createCellStyle();  
103.         copyCellStyle(srcCell.getCellStyle(), newstyle);  
104.         distCell.setEncoding(srcCell.getEncoding());  
105.         //样式   
106.         distCell.setCellStyle(newstyle);  
107.         //评论   
108.         if (srcCell.getCellComment() != null) {  
109.             distCell.setCellComment(srcCell.getCellComment());  
110.         }  
111.         // 不同数据类型处理   
112.         int srcCellType = srcCell.getCellType();  
113.         distCell.setCellType(srcCellType);  
114.         if (copyValueFlag) {  
115.             if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {  
116.                 if (HSSFDateUtil.isCellDateFormatted(srcCell)) {  
117.                     distCell.setCellValue(srcCell.getDateCellValue());  
118.                 } else {  
119.                     distCell.setCellValue(srcCell.getNumericCellValue());  
120.                 }  
121.             } else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {  
122.                 distCell.setCellValue(srcCell.getRichStringCellValue());  
123.             } else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {  
124.                 // nothing21   
125.             } else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {  
126.                 distCell.setCellValue(srcCell.getBooleanCellValue());  
127.             } else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {  
128.                 distCell.setCellErrorValue(srcCell.getErrorCellValue());  
129.             } else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {  
130.                 distCell.setCellFormula(srcCell.getCellFormula());  
131.             } else { // nothing29  
132.             }  
133.         }  
134.     }  
135. }  

 

 

-------------------------------------------------

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