- 将一个excel内容以及样式合并到目标excel以存在的sheet页后面(不新开sheet页)
/**
* @param @param srcExcel .xls源文件
* @param @param destExcel .xls新文件
* @param @throws IOException
* @throws Exception
* @Title: 合并Excel文件
* @Description: 将srcExcel文件中的sheet全部合并到destExcel中
*/
public static void excelMerge(File srcExcel, File destExcel) {
XSSFWorkbook srcWB = null;
XSSFWorkbook destWB = null;
XSSFSheet destSheet = null;
XSSFSheet srcSheet = null;
OutputStream out = null;
InputStream in = null;
try {
if (!srcExcel.exists()) {
return;
}
in = new FileInputStream(srcExcel);
srcWB = new XSSFWorkbook(in);
in = new FileInputStream(destExcel);
destWB = new XSSFWorkbook(in);
for (int m = 0; m < srcWB.getNumberOfSheets(); m++) {
//以下写法不新增sheet,如需开启新的sheet页跟改为注释部分
srcSheet = srcWB.getSheetAt(m);
//destWB.createSheet("新的sheet页");
destSheet = destWB.getSheetAt(m);
//由于我的excel处理过60行就换行,所以在sheet页中追加计算起始位置,具体根据自己需求改写
int lastRowNum = destSheet.getLastRowNum();
if(lastRowNum<60){
lastRowNum=60;
}else{
int Remainder = lastRowNum % 60;
lastRowNum=lastRowNum+(60-Remainder);
}
int destflg = lastRowNum;
//合并单元格,被合并的excel中存在单元格合并,所以复制的时候需要对应合并单元格
MergerRegion(destSheet, srcSheet, destflg);
int firstRow = srcSheet.getFirstRowNum();
int lastRow = srcSheet.getLastRowNum();
for (int i = firstRow; i <= lastRow; i++) {
// 在原sheet页末尾追加数据
XSSFRow rowCreat = destSheet.createRow(destflg);
// 取得源有excel Sheet的行
XSSFRow row = srcSheet.getRow(i);
if (row == null)
continue;
// 单元格式样
int firstCell = row.getFirstCellNum();
int lastCell = row.getLastCellNum();
for (int j = firstCell; j < lastCell; j++) {
if (row.getCell(j) == null)
continue;
if (i == 0) {
// 隐藏单元格
destSheet.setColumnHidden(j, srcSheet.isColumnHidden(j));
// 设置宽度
destSheet.setColumnWidth(j, srcSheet.getColumnWidth(j));
}
rowCreat.createCell(j);
// 复制高度
rowCreat.getCell(j).getRow().setHeight(row.getCell(j).getRow().getHeight());
// 复制样式
XSSFCellStyle oldStely = row.getCell(j).getCellStyle();
XSSFCellStyle cellStyle = rowCreat.getCell(j).getRow().getSheet().getWorkbook().createCellStyle();
cellStyle.cloneStyleFrom(oldStely);
rowCreat.getCell(j).setCellStyle(cellStyle);
// 复制内容
rowCreat.getCell(j).setCellValue(copyfont(row.getCell(j)));
}
destflg = destflg + 1;
}
}
out = new FileOutputStream(destExcel);
destWB.write(out);
out.flush();
out.close();
in.close();
srcExcel.delete();
} catch (Exception e) {
logger.error("Excel合并错误:" + e);
}
}
private static XSSFRichTextString copyfont(XSSFCell oldCell) {
//获取单元格中的数据
oldCell.setCellType(CellType.STRING);
XSSFRichTextString rts = oldCell.getRichStringCellValue();
return rts;
}
//合并单元格
private static void MergerRegion(XSSFSheet sheetCreat, XSSFSheet sheet, int start) {
int sheetMergerCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergerCount; i++) {
CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
mergedRegion.setFirstRow(mergedRegion.getFirstRow() + start);
mergedRegion.setLastRow(mergedRegion.getLastRow() + start);
sheetCreat.addMergedRegion(mergedRegion);
}
}
- 在pdf文件中添加图片
/**
*pdf文件添加logo和章印
*fileName pdf文件名称
*savepath 添加图片后文件路径
*logoPath 图片路径
*zyPath 图片路径
*/
public static int addlogoAndzy(String fileName, String savepath,String logoPath,String zyPath)
{
// 文档总页数
int num = 0;
Document document = new Document();
try
{
PdfReader reader = new PdfReader(fileName);
num = reader.getNumberOfPages();
PdfCopy copy = new PdfCopy(document, new FileOutputStream(savepath));
document.open();
for (int i = 0; i < num;)
{
PdfImportedPage page = copy.getImportedPage(reader, ++i);
PdfCopy.PageStamp stamp = copy.createPageStamp(page);
// 页眉添加logo (图片页眉,居坐),页脚添加zy居右
Image img = Image.getInstance(logoPath);// 选择页眉图片
Image img1 = Image.getInstance(zyPath);// 选择页脚图片
img.setAlignment(1);
img.scaleAbsolute(400 / 2, 96 / 5);// 控制图片大小
img.setAbsolutePosition(50f, 800);// 控制图片位置
img1.setAlignment(2);
img1.scaleAbsolute(470 / 5, 170/ 5);// 控制图片大小
img1.setAbsolutePosition(420f, 30);// 控制图片位置
stamp.getUnderContent().addImage(img);
stamp.getUnderContent().addImage(img1);
stamp.alterContents();
copy.addPage(page);
}
}
catch (Exception e)
{
logger.error("PDF添加logo和章印失败");
return -1;
}
finally
{
if (null != document)
{
document.close();
}
}
logger.info("PDF page: "+num+"页");
return num;
}