合并Excel

把两个excel合并成一个(我这里是合并两个多个自己修改处理)
合并代码

package com.baic.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 合并多个excel为一个文件
 * 
 * @author wuyahui
 *
 */
public class ExcelMergeUtils {

	private static Logger logger = LoggerFactory.getLogger(ExcelMergeUtils.class);

	/**
	 * 两个excel合并成一个
	 * 
	 * @param filePath1
	 *            第一个excel
	 * @param filePath2
	 *            第二个excel
	 * @param filePath3
	 *            合并后的excel
	 * @return
	 */
	public static void excels2One(String filePath1, String filePath2, String filePath3) {
		try {
			// 第一个文件
			InputStream in = new FileInputStream(filePath1);
			XSSFWorkbook firstExcel = new XSSFWorkbook(in);
			// 第二个文件
			FileInputStream in2 = new FileInputStream(filePath2);
			XSSFWorkbook secondExcel2 = new XSSFWorkbook(in2);
			// 获取第二个文件的sheet个数
			int numberOfSheets = secondExcel2.getNumberOfSheets();
			// 循环复制第二个文件的sheet到第一个文件中
			for (int i = 0; i < numberOfSheets; i++) {
				// 往第一个文件中创建新的sheet
				XSSFSheet firstSheet = firstExcel.createSheet(secondExcel2.getSheetName(i));
				// 获取第二文件的sheet
				XSSFSheet secondSheet = secondExcel2.getSheetAt(i);
				// 复制第二个文件的内容及格式到第一个文件中
				CopySheetUtil.copySheets(firstSheet, secondSheet, true);
			}
			// 删除第一个文件
			new File(filePath1).delete();
			// 删除第二个文件
			new File(filePath2).delete();
			// 定义新生成的xlx表格文件
			String allFileName = filePath3;
			FileOutputStream fileOut = new FileOutputStream(allFileName);
			// 写出文件
			firstExcel.write(fileOut);
			// 关闭流
			fileOut.close();
		} catch (FileNotFoundException e) {
			logger.error("合并excel", e);
		} catch (IOException e) {
			logger.error("合并excel", e);
		}
	}
}

处理格式的代码

package com.baic.util;

import org.apache.poi.ss.util.CellRangeAddress;
/**
 * 
 * @author wyh
 *
 */
public class CellRangeAddressWrapper implements Comparable<CellRangeAddressWrapper> {

    public CellRangeAddress range;

    public CellRangeAddressWrapper(CellRangeAddress theRange) {
        this.range = theRange;
    }

    public int compareTo(CellRangeAddressWrapper craw) {
        if (range.getFirstColumn() < craw.range.getFirstColumn()
                && range.getFirstRow() < craw.range.getFirstRow()) {
            return -1;
        } else if (range.getFirstColumn() == craw.range.getFirstColumn()
                && range.getFirstRow() == craw.range.getFirstRow()) {
            return 0;
        } else {
            return 1;
        }
    }

}


package com.baic.util;

import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

public class CopySheetUtil {

	public CopySheetUtil() {
    }

    public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet) {
        copySheets(newSheet, sheet, true);
    }

    public static void copySheets(XSSFSheet newSheet, XSSFSheet oldSheet,
                                  boolean copyStyle) {
        int maxColumnNum = 0;
        Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>()
                : null;
        for (int i = oldSheet.getFirstRowNum(); i <= oldSheet.getLastRowNum(); i++) {
            // 获取行
            XSSFRow oldRow = oldSheet.getRow(i);
            // 创建新行
            XSSFRow newRow = newSheet.createRow(i);
            if (oldRow != null) {
                CopySheetUtil.copyRow(oldSheet, newSheet, oldRow, newRow,
                        styleMap);
                if (oldRow.getLastCellNum() > maxColumnNum) {
                    maxColumnNum = oldRow.getLastCellNum();
                }
            }
        }
        for (int i = 0; i <= maxColumnNum; i++) {
            // 设置列宽
            newSheet.setColumnWidth(i, oldSheet.getColumnWidth(i));
        }
    }

    /**
     * 复制并合并单元格
     * @param
     * @param
     * @param
     *
     */
    public static void copyRow(XSSFSheet oldSheet, XSSFSheet newSheet,
                               XSSFRow oldRow, XSSFRow newRow,
                               Map<Integer, XSSFCellStyle> styleMap) {
        Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
        // 设置行高
        newRow.setHeight(oldRow.getHeight());
        // 如果copy到另一个sheet的起始行数不同
        int deltaRows = newRow.getRowNum() - oldRow.getRowNum();
        for (int j = oldRow.getFirstCellNum(); j < oldRow.getLastCellNum(); j++) {
            // old cell
            XSSFCell oldCell = oldRow.getCell(j);
            // new cell
            XSSFCell newCell = newRow.getCell(j);
            if (oldCell != null) {
                if (newCell == null) {
                    newCell = newRow.createCell(j);
                }
                copyCell(oldCell, newCell, styleMap);
                CellRangeAddress mergedRegion = getMergedRegion(oldSheet,
                        oldRow.getRowNum(), (short) oldCell.getColumnIndex());
                if (mergedRegion != null) {
                    CellRangeAddress newMergedRegion = new CellRangeAddress(
                            mergedRegion.getFirstRow() + deltaRows,
                            mergedRegion.getLastRow() + deltaRows, mergedRegion
                            .getFirstColumn(), mergedRegion
                            .getLastColumn());
                    CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(
                            newMergedRegion);
                    if (isNewMergedRegion(wrapper, mergedRegions)) {
                        mergedRegions.add(wrapper);
                        newSheet.addMergedRegion(wrapper.range);
                    }
                }
            }
        }
    }

    /**
     * 把原来的Sheet中cell（列）的样式和数据类型复制到新的sheet的cell（列）中
     *  @param oldCell
     * @param newCell
     * @param styleMap
     */
    public static void copyCell(XSSFCell oldCell, XSSFCell newCell,
                                Map<Integer, XSSFCellStyle> styleMap) {
        if (styleMap != null) {
            if (oldCell.getSheet().getWorkbook() == newCell.getSheet()
                    .getWorkbook()) {
                newCell.setCellStyle(oldCell.getCellStyle());
            } else {
                int stHashCode = oldCell.getCellStyle().hashCode();
                XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
                if (newCellStyle == null) {
                    newCellStyle = newCell.getSheet().getWorkbook()
                            .createCellStyle();
                    newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                    styleMap.put(stHashCode, newCellStyle);
                }
                newCell.setCellStyle(newCellStyle);
            }
        }
        switch (oldCell.getCellType()) {
            case XSSFCell.CELL_TYPE_STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_BLANK:
                newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                break;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(oldCell.getErrorCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            default:
                break;
        }

    }

    // 获取merge对象
    public static CellRangeAddress getMergedRegion(XSSFSheet sheet, int rowNum,
                                                   short cellNum) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress merged = sheet.getMergedRegion(i);
            if (merged.isInRange(rowNum, cellNum)) {
                return merged;
            }
        }
        return null;
    }

    private static boolean isNewMergedRegion(
            CellRangeAddressWrapper newMergedRegion,
            Set<CellRangeAddressWrapper> mergedRegions) {
        boolean bool = mergedRegions.contains(newMergedRegion);
        return !bool;
    }
}