java 合并两个excel_java中使用POI和JXL工具合并多个Excel文件

package com.bacs.buz.util;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.Font;

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.xssf.usermodel.XSSFWorkbook;

import com.xingyi.bacs.util.LogUtil;

/**

* POI合并excel工具类

* @author hhb

*

*/

public class POIMergeExcelUtil {

private List columns=null;//列名集合

/**

* 创建xls或者xlsx文件

* @param filePath

* @param sourceSheet 第一份文件的sheet

* @throws Exception

*/

public void createExcelFile(String filePath,Sheet sourceSheet) throws Exception{

LogUtil.info(POIMergeExcelUtil.class, "开始创建主文件，文件路径为："+filePath);

Workbook workbook = null;

File file=new File(filePath);

String status=filePath.substring(filePath.lastIndexOf("."));

//HSSFWorkbook针对xls

if(status.equals(".xls")){

workbook = new HSSFWorkbook();

}

//XSSFWorkbook针对xlsx

if(status.equals(".xlsx")){

workbook = new XSSFWorkbook();

}

//创建一个工作簿

Sheet sheet=workbook.createSheet();

//创建列头

Row firstRow=sheet.createRow(0);

for(int i=0;i

Cell cell=firstRow.createCell(i, Cell.CELL_TYPE_STRING);

cell.setCellStyle(getStyle(workbook));

cell.setCellValue(columns.get(i));

}

LogUtil.info(POIMergeExcelUtil.class, "正在将第一份文件内容合并到主文件中……");

//将第一份目标文件内容填充到新的文件中

for(int i=1;i

Row createRow=sheet.createRow(i);

//获取第一份文件的一行

Row sourceRow=sourceSheet.getRow(i);

for(int j=0;j

Cell cell=sourceRow.getCell(j);

String cellValue=getCellValue(cell);

Cell createCell=createRow.createCell(j);

createCell.setCellValue(cellValue);

}

}

FileOutputStream fos=new FileOutputStream(file);

workbook.write(fos);

fos.flush();

fos.close();

LogUtil.info(POIMergeExcelUtil.class, "主文件创建成功，文件路径为："+file.getAbsolutePath());

}

/**

* 获取单元格里面的值

* @param cell

* @return

*/

public  String getCellValue(Cell cell){

String cellValue = "";

if(cell == null){

return cellValue;

}

//把数字当成String来读，避免出现1读成1.0的情况

if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){

cell.setCellType(Cell.CELL_TYPE_STRING);

}

//判断数据的类型

switch (cell.getCellType()){

case Cell.CELL_TYPE_NUMERIC: //数字

cellValue = String.valueOf(cell.getNumericCellValue());

break;

case Cell.CELL_TYPE_STRING: //字符串

cellValue = String.valueOf(cell.getStringCellValue());

break;

case Cell.CELL_TYPE_BOOLEAN: //Boolean

cellValue = String.valueOf(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA: //公式

cellValue = String.valueOf(cell.getCellFormula());

break;

case Cell.CELL_TYPE_BLANK: //空值

cellValue = "";

break;

case Cell.CELL_TYPE_ERROR: //故障

cellValue = "非法字符";

break;

default:

cellValue = "未知类型";

break;

}

return cellValue;

}

/**

* 表格格式

* @param workbook

* @return

*/

private CellStyle getStyle(Workbook workbook){

CellStyle style = workbook.createCellStyle();

//style.setAlignment(CellStyle.ALIGN_CENTER);

// style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

// 设置单元格字体

Font headerFont = workbook.createFont(); // 字体

headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

headerFont.setColor(HSSFColor.BLACK.index);

//headerFont.setFontName("宋体");

style.setFont(headerFont);

//tyle.setWrapText(true);*/

return style;

}

/**

* 合并Excel

* @param srcPath 目标文件路径

* @param sourcePath 需要合并的文件路径

* @throws IOException

* @throws FileNotFoundException

*/

public void mergeExcel(String srcPath,String sourcePath) throws Exception{

File inputWorkbook = new File(srcPath);

Workbook  w = null;

String status=srcPath.substring(srcPath.lastIndexOf("."));

//HSSFWorkbook针对xls

if(status.equals(".xls")){

w = new HSSFWorkbook(new FileInputStream(inputWorkbook));

}

//XSSFWorkbook针对xlsx

if(status.equals(".xlsx")){

w = new XSSFWorkbook(new FileInputStream(inputWorkbook));

}

Sheet sheet = w.getSheetAt(0);

this.beginMergerColumn(sheet, sourcePath);

FileOutputStream fos=new FileOutputStream(inputWorkbook);

w.write(fos);

fos.flush();

fos.close();

LogUtil.info(POIMergeExcelUtil.class, "======Excel文件合并成功=====");

}

/*****

* 开始合并文件

* @param excelSheet

* @param dirPath

* @throws IOException

* @throws FileNotFoundException

*/

private void beginMergerColumn(Sheet excelSheet,String dirPath ) throws Exception{

File inputWorkbook = new File(dirPath);

LogUtil.info(POIMergeExcelUtil.class, "开始合并Excel文件："+dirPath);

Workbook w = null;

String status=dirPath.substring(dirPath.lastIndexOf("."));

//HSSFWorkbook针对xls

if(status.equals(".xls")){

w = new HSSFWorkbook(new FileInputStream(inputWorkbook));

}

//XSSFWorkbook针对xlsx

if(status.equals(".xlsx")){

w = new XSSFWorkbook(new FileInputStream(inputWorkbook));

}

//需要合并文件的sheet

Sheet sheet = w.getSheetAt(0);

//主文件从第几行开始追加内容

int mergerRowBeginIndex=excelSheet.getLastRowNum()+1;

LogUtil.info(POIMergeExcelUtil.class, "主文件开始合并的行号值为："+mergerRowBeginIndex);

LogUtil.info(POIMergeExcelUtil.class, "需要合并的数量为："+(sheet.getLastRowNum()));

for(int i=1;i

Row createRow=excelSheet.createRow(mergerRowBeginIndex);

//获取第一份文件的一行

Row sourceRow=sheet.getRow(i);

for(int j=0;j

Cell cell=sourceRow.getCell(j);

String cellValue=getCellValue(cell);

Cell createCell=createRow.createCell(j);

createCell.setCellValue(cellValue);

}

mergerRowBeginIndex++;

}

}

/**

* 获取列头名称,返回第一份文件的sheet

* @param filePath

* @throws IOException

* @throws FileNotFoundException

*/

public Sheet readFirstFileGetHeaders(String filePath) throws Exception{

LogUtil.info(POIMergeExcelUtil.class, "开始获取目标文件的列头,文件路径为："+filePath);

File inputWorkbook = new File(filePath);

Workbook  w = null;

Sheet sheet =null;

String status=filePath.substring(filePath.lastIndexOf("."));

//HSSFWorkbook针对xls

if(status.equals(".xls")){

w = new HSSFWorkbook(new FileInputStream(inputWorkbook));

}

//XSSFWorkbook针对xlsx

if(status.equals(".xlsx")){

w = new XSSFWorkbook(new FileInputStream(inputWorkbook));

}

sheet = w.getSheetAt(0);

columns=new ArrayList();

Row row=sheet.getRow(0);

for(int i=0;i

columns.add(row.getCell(i).toString());

}

LogUtil.info(POIMergeExcelUtil.class, "结束获取目标文件的列头，列头信息为："+columns.toString());

return sheet;

}

public static void main(String[] args) throws IOException {

POIMergeExcelUtil u=new POIMergeExcelUtil();

String srcPath="D://cs//test.xlsx";

try {

Sheet  sheet=u.readFirstFileGetHeaders("D://cs//MPOSJLTF_T1_20170103_01.xls");

u.createExcelFile(srcPath,sheet);

u.mergeExcel(srcPath, "D://cs//需要合并的文件.xls");

} catch (Exception e) {

e.printStackTrace();

File file=new File(srcPath);

if(file.exists()){

file.delete();

}

System.out.println("异常，文件删除");

}

/*    //File  file = new File("D://cs//MPOSJLTF_T1_20170104.xlsx");

File  file = new File("D://cs//test.xlsx");

ReadExcel reader = null;

//读EXCEL的第一页

try {

reader = new ReadExcel("D://cs//test.xlsx");

reader.processByRow(1);

reader.stop();

} catch (Exception e) {

e.printStackTrace();

}

//获取读取到的行对象集合

List xRows = reader.getListXRow();

System.out.println(xRows.size());*/

}

}