/**
* 导出文件
*/
public static void exportToNewFile() {
FileOutputStream out;
try {
out = new FileOutputStream(desXlsPath);
wb2.write(out);
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 功能:拷贝sheet
* 实际调用 copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true)
* @param targetSheet
* @param sourceSheet
* @param targetWork
* @param sourceWork
*/
public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet,
HSSFWorkbook targetWork, HSSFWorkbook sourceWork) throws Exception{
if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){
throw new IllegalArgumentException("targetSheet、sourceSheet、targetWork、sourceWork都不能为空,故抛出该异常!");
}
copySheet(targetSheet, sourceSheet, targetWork, sourceWork, true);
}
/**
* 功能:拷贝sheet
* @param targetSheet
* @param sourceSheet
* @param targetWork
* @param sourceWork
* @param copyStyle boolean 是否拷贝样式
*/
public static void copySheet(HSSFSheet targetSheet, HSSFSheet sourceSheet,
HSSFWorkbook targetWork, HSSFWorkbook sourceWork, boolean copyStyle)throws Exception {
if(targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null){
throw new IllegalArgumentException("targetSheet、sourceSheet、targetWork、sourceWork都不能为空,故抛出该异常!");
}
//复制源表中的行
int maxColumnNum = 0;
Map styleMap = (copyStyle) ? new HashMap() : null;
HSSFPatriarch patriarch = targetSheet.createDrawingPatriarch(); //用于复制注释
for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) {
HSSFRow sourceRow = sourceSheet.getRow(i);
HSSFRow targetRow = targetSheet.createRow(i);
if (sourceRow != null) {
copyRow(targetRow, sourceRow,
targetWork, sourceWork,patriarch, styleMap);
if (sourceRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = sourceRow.getLastCellNum();
}
}
}
//复制源表中的合并单元格
mergerRegion(targetSheet, sourceSheet);
//设置目标sheet的列宽
for (int i = 0; i <= maxColumnNum; i++) {
targetSheet.setColumnWidth(i, sourceSheet.getColumnWidth(i));
}
}
/**
* 功能:拷贝row
* @param targetRow
* @param sourceRow
* @param styleMap
* @param targetWork
* @param sourceWork
* @param targetPatriarch
*/
public static void copyRow(HSSFRow targetRow, HSSFRow sourceRow,
HSSFWorkbook targetWork, HSSFWorkbook sourceWork,HSSFPatriarch targetPatriarch, Map styleMap) throws Exception {
if(targetRow == null || sourceRow == null || targetWork == null || sourceWork == null || targetPatriarch == null){
throw new IllegalArgumentException("targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空,故抛出该异常!");
}
//设置行高
targetRow.setHeight(sourceRow.getHeight());
for (int i = sourceRow.getFirstCellNum(); i <= sourceRow.getLastCellNum(); i++) {
HSSFCell sourceCell = sourceRow.getCell(i);
HSSFCell targetCell = targetRow.getCell(i);
if (sourceCell != null) {
if (targetCell == null) {
targetCell = targetRow.createCell(i);
}
//拷贝单元格,包括内容和样式
copyCell(targetCell, sourceCell, targetWork, sourceWork, styleMap);
//拷贝单元格注释
copyComment(targetCell,sourceCell,targetPatriarch);
}
}
}
/**
* 功能:拷贝cell,依据styleMap是否为空判断是否拷贝单元格样式
* @param targetCell 不能为空
* @param sourceCell 不能为空
* @param targetWork 不能为空
* @param sourceWork 不能为空
* @param styleMap 可以为空
*/
public static void copyCell(HSSFCell targetCell, HSSFCell sourceCell, HSSFWorkbook targetWork, HSSFWorkbook sourceWork,Map styleMap) {
if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){
throw new IllegalArgumentException("targetCell、sourceCell、targetWork、sourceWork都不能为空,故抛出该异常!");
}
//处理单元格样式
if(styleMap != null){
HSSFCellStyle newstyle=targetWork.createCellStyle();
copyCellStyle(sourceCell.getCellStyle(), newstyle, targetWork, sourceWork);
//distCell.setEncoding(srcCell.getEncoding());
//样式
targetCell.setCellStyle(newstyle);
//targetCell.setCellStyle(sourceCell.getCellStyle());
/*if (targetWork == sourceWork) {
targetCell.setCellStyle(sourceCell.getCellStyle());
} else {
String stHashCode = "" + sourceCell.getCellStyle().hashCode();
HSSFCellStyle targetCellStyle = (HSSFCellStyle) styleMap
.get(stHashCode);
if (targetCellStyle == null) {
targetCellStyle = targetWork.createCellStyle();
targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
styleMap.put(stHashCode, targetCellStyle);
}
targetCell.setCellStyle(targetCellStyle);
}*/
//copyCellStyle(sourceCell.getCellStyle(), targetCell.getCellStyle());
//targetCell.setCellStyle(targetCell.getCellStyle());
}
//处理单元格内容
switch (sourceCell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
targetCell.setCellValue(sourceCell.getRichStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
targetCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
targetCell.setCellFormula(sourceCell.getCellFormula());
break;
default:
break;
}
}
/**
* 复制一个单元格样式到目的单元格样式
* @param fromStyle
* @param toStyle
*/
public static void copyCellStyle(HSSFCellStyle fromStyle,
HSSFCellStyle toStyle,HSSFWorkbook targetWork, HSSFWorkbook sourceWork) {
toStyle.setAlignment(fromStyle.getAlignment());
//边框和边框颜色
toStyle.setBorderBottom(fromStyle.getBorderBottom());
toStyle.setBorderLeft(fromStyle.getBorderLeft());
toStyle.setBorderRight(fromStyle.getBorderRight());
toStyle.setBorderTop(fromStyle.getBorderTop());
toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());
//背景和前景
toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());
toStyle.setDataFormat(fromStyle.getDataFormat());
toStyle.setFillPattern(fromStyle.getFillPattern());
toStyle.setHidden(fromStyle.getHidden());
toStyle.setIndention(fromStyle.getIndention());//首行缩进
toStyle.setLocked(fromStyle.getLocked());
toStyle.setRotation(fromStyle.getRotation());//旋转
toStyle.setVerticalAlignment(fromStyle.getVerticalAlignment());
toStyle.setWrapText(fromStyle.getWrapText());
HSSFFont fromFont = fromStyle.getFont(sourceWork);
HSSFFont toFont = targetWork.createFont();
toFont.setFontName(fromFont.getFontName());
toFont.setBoldweight(fromFont.getBoldweight());
toFont.setColor(fromFont.getColor());
toStyle.setFont(toFont);
}
/**
* 功能:拷贝comment
* @param targetCell
* @param sourceCell
* @param targetPatriarch
*/
public static void copyComment(HSSFCell targetCell,HSSFCell sourceCell,HSSFPatriarch targetPatriarch)throws Exception{
if(targetCell == null || sourceCell == null || targetPatriarch == null){
throw new IllegalArgumentException("targetCell、sourceCell、targetPatriarch都不能为空,故抛出该异常!");
}
//处理单元格注释
HSSFComment comment = sourceCell.getCellComment();
if(comment != null){
HSSFComment newComment = targetPatriarch.createComment(new HSSFClientAnchor());
newComment.setAuthor(comment.getAuthor());
newComment.setColumn(comment.getColumn());
newComment.setFillColor(comment.getFillColor());
newComment.setHorizontalAlignment(comment.getHorizontalAlignment());
newComment.setLineStyle(comment.getLineStyle());
newComment.setLineStyleColor(comment.getLineStyleColor());
newComment.setLineWidth(comment.getLineWidth());
newComment.setMarginBottom(comment.getMarginBottom());
newComment.setMarginLeft(comment.getMarginLeft());
newComment.setMarginTop(comment.getMarginTop());
newComment.setMarginRight(comment.getMarginRight());
newComment.setNoFill(comment.isNoFill());
newComment.setRow(comment.getRow());
newComment.setShapeType(comment.getShapeType());
newComment.setString(comment.getString());
newComment.setVerticalAlignment(comment.getVerticalAlignment());
newComment.setVisible(comment.isVisible());
targetCell.setCellComment(newComment);
}
}
/**
* 功能:复制原有sheet的合并单元格到新创建的sheet
*
* @param sheetCreat
* @param sourceSheet
*/
public static void mergerRegion(HSSFSheet targetSheet, HSSFSheet sourceSheet)throws Exception {
if(targetSheet == null || sourceSheet == null){
throw new IllegalArgumentException("targetSheet或者sourceSheet不能为空,故抛出该异常!");
}
for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
CellRangeAddress oldRange = sourceSheet.getMergedRegion(i);
CellRangeAddress newRange = new CellRangeAddress(
oldRange.getFirstRow(), oldRange.getLastRow(),
oldRange.getFirstColumn(), oldRange.getLastColumn());
targetSheet.addMergedRegion(newRange);
}
}