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());*/
}
}