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

80 阅读 0 评论 53 点赞

我是靠谱客的博主动听花瓣，最近开发中收集的这篇文章主要介绍java中使用POI和JXL工具合并多个Excel文件，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

在项目开发过程中，我们经常都会遇到对excel文件的相关操作，本文主要讲解的是对多个excel文件进行合并的操作。

在讲解之前，有必要说明这两种工具的使用场景和区别。jxl目前仅支持后缀格式为xls的excel文件，像Excel2007版本后缀为xlsx的文件暂时还不支持。poi目前可以支持excel后缀格式为xls与xlsx的文件。其他的区别有兴趣可以自己去网络上搜索下。

一. 使用jxl工具合并多个后缀为xls的excel文件，直接贴上代码。

package com.bacs.buz.util;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import com.xingyi.bacs.util.LogUtil;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class JXLMergerWriteExcelUtil {
private WritableCellFormat times;
private String createFilePath;//合成文件存放路径
private int beginMergerColumnIndex=0;//开始合并的列
private int endMerGerColumnIndex;//结束的合并的列
//private long headRowIndex;//列头所在位置
private int beginMergerRow=1;//开始合并的行标
private List<String> columns=null;
private String dirPath;
private int mergerRowBeginIndex=0;
public JXLMergerWriteExcelUtil(String createFilePath,String path){
this.createFilePath = createFilePath;
this.dirPath=path;
}
public JXLMergerWriteExcelUtil(String createFilePath,String path,int beginMergerRow){
this.createFilePath = createFilePath;
this.dirPath=path;
this.beginMergerRow=beginMergerRow;
}
public JXLMergerWriteExcelUtil(String createFilePath,String path,int beginMergerColumnIndex,int endMerGerColumnIndex,int beginMergerRow){
this.createFilePath = createFilePath;
this.dirPath=path;
this.beginMergerColumnIndex=beginMergerColumnIndex;
this.endMerGerColumnIndex=endMerGerColumnIndex;
this.beginMergerRow=beginMergerRow;
}
/*******
* 写文件
* @throws IOException
* @throws WriteException
* @throws Exception
*/
public String mergerExcel() {
LogUtil.info(JXLMergerWriteExcelUtil.class, "开始合并文件");
WritableWorkbook workbook=null;
Workbook book=null;
try{
if(createFilePath==null){
return "请输入创建文件路径";
}
if(dirPath==null){
return "请输入被合并文件夹的路径";
}
File file = new File(createFilePath);
LogUtil.info(JXLMergerWriteExcelUtil.class, "主文件名路径为："+createFilePath);
book=Workbook.getWorkbook(file);
workbook=Workbook.createWorkbook(file, book);
WritableSheet excelSheet = workbook.getSheet(0);
this.beginMergerColumn(excelSheet);
workbook.write();
LogUtil.info(JXLMergerWriteExcelUtil.class, "结束合并文件");
return "0";
}catch(Exception e){
e.printStackTrace();
return "1";
}finally{
if(workbook!=null){
try {
workbook.close();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
if(book!=null){
book.close();
}
}
}
/*****
* 开始合并文件
* @param excelSheet
* @param dirPath
*/
private void beginMergerColumn(WritableSheet excelSheet ){
File srcFile = new File(dirPath);
LogUtil.info(JXLMergerWriteExcelUtil.class, "需要合并的文件名路径为："+dirPath);
String path= srcFile.getAbsolutePath();
LogUtil.info(JXLMergerWriteExcelUtil.class, "====合并excel文件----请稍等====");
try {
this.readSingleExcel(excelSheet,path);
} catch (Exception e) {
e.printStackTrace();
}
}
/*****
* 读取单个文件
* @param excelSheet 主excel
* @param singFilePath 需要合并的文件路径
*/
public void readSingleExcel(WritableSheet excelSheet,String singFilePath){
File inputWorkbook = new File(singFilePath);
Workbook w = null;
try {
w = Workbook.getWorkbook(inputWorkbook);
Sheet sheet = w.getSheet(0);
mergerRowBeginIndex=excelSheet.getRows();
LogUtil.info(JXLMergerWriteExcelUtil.class, "主文件名开始合并开始值为："+mergerRowBeginIndex);
endMerGerColumnIndex=excelSheet.getColumns();
LogUtil.info(JXLMergerWriteExcelUtil.class, "需要合并的数量为："+(sheet.getRows()-1));
for (int i = beginMergerRow; i < sheet.getRows(); i++) {
for (int j = beginMergerColumnIndex; j < endMerGerColumnIndex; j++) {
Cell cell = sheet.getCell(j, i);
String cell_value=cell.getContents();
/*if(j==0){
if(cell_value!=""){
cell_value=String.valueOf(Integer.parseInt(cell_value)+mergerRowBeginIndex);
}
}*/
this.addLabel(excelSheet, j, mergerRowBeginIndex, cell_value);
}
mergerRowBeginIndex++;
}
} catch (Exception e) {
e.printStackTrace();
}finally{
if(w!=null){
w.close();
}
}
}
/*****
* 创建表头
* @param excelSheet
* @param firstFilePath
*/
private void createHeader(WritableSheet excelSheet,String firstFilePath){
int column=0;
try{
this.readFirstFileGetHeaders(firstFilePath);
for(int i=beginMergerColumnIndex;i<endMerGerColumnIndex;i++){
this.addLabel(excelSheet, column++, 0, columns.get(i));
}
this.addLabel(excelSheet, excelSheet.getColumns()-1, 0, "来源文件名称");
}catch(Exception e){
e.printStackTrace();
}
}
/*****
* 读取单个文件获取文件的表头信息
* @param filePath
*/
private void readFirstFileGetHeaders(String filePath){
File inputWorkbook = new File(filePath);
Workbook w = null;
try {
w = Workbook.getWorkbook(inputWorkbook);
// Get the first sheet
Sheet sheet = w.getSheet(0);
// Loop over first 10 column and lines
columns=new ArrayList<String>();
for (int i = 0; i < sheet.getRows(); i++) {
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
String cell_value=cell.getContents();
columns.add(cell_value);
}
endMerGerColumnIndex=sheet.getColumns();
break;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(w!=null){
w.close();
}
}
}
/*****
* 添加信息到excel中
* @param sheet
* @param column
* @param row
* @param s
* @throws WriteException
* @throws RowsExceededException
*/
private void addLabel(WritableSheet sheet, int column, int row, String s)
throws WriteException, RowsExceededException {
Label label;
// Lets create a times font
WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
// Define the cell format
times = new WritableCellFormat(times10pt);
label = new Label(column, row, s, times);
sheet.addCell(label);
}
public static void main(String[] args) throws Exception {
// for(int i=17;i<29;i++){
// JXLWriteExcel mergerExcel = new JXLWriteExcel("d:/合并文件/merger_"+i+".xls","D:/scopus20111124/"+i,2);
// String inf=mergerExcel.mergerExcel();
// System.out.println("第"+i+"批次文件"+inf);
// }
File source=new File("D://cs//MPOSJLTF_T1_20170103_01.xls");
File srcFile=new File("D://cs//MPOSJLTF_T1_20170103.xls");
JXLMergerWriteExcelUtil mergerExcel = new JXLMergerWriteExcelUtil("D://cs//MPOSJLTF_T1_20170103.xls","D://cs//MPOSJLTF_T1_20170103_02.xls",1);
String inf=mergerExcel.mergerExcel();
// mergerExcel.fileChannelCopy(source, srcFile);
}
}

二. 使用POI工具合并多个后缀为xls与xlsx的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<String> 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<columns.size();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<sourceSheet.getLastRowNum()+1;i++){
Row createRow=sheet.createRow(i);
//获取第一份文件的一行
Row sourceRow=sourceSheet.getRow(i);
for(int j=0;j<columns.size();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<sheet.getLastRowNum()+1;i++){
Row createRow=excelSheet.createRow(mergerRowBeginIndex);
//获取第一份文件的一行
Row sourceRow=sheet.getRow(i);
for(int j=0;j<columns.size();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<String>();
Row row=sheet.getRow(0);
for(int i=0;i<row.getPhysicalNumberOfCells();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<XRow> xRows = reader.getListXRow();
System.out.println(xRows.size());*/
}
}