在java中将两个excel文件合并为一个工作簿中的两张工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21575759/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-13 09:27:52 来源:igfitidea点击:
Merging Two excel files as two sheets in one workbook in java
提问by user3274923
I have two xlsx files at folder C:\DemoFilesExcel\demo1.xlsx and C:\DemoFilesExcel\demo2.xlsx.
我在文件夹 C:\DemoFilesExcel\demo1.xlsx 和 C:\DemoFilesExcel\demo2.xlsx 中有两个 xlsx 文件。
I want to create a new xlsx C:\DemoFilesExcel\merged.xlsx that will have these two files as two sheets in merged.xlsx workbook.
我想创建一个新的 xlsx C:\DemoFilesExcel\merged.xlsx，它将这两个文件作为merged.xlsx 工作簿中的两个工作表。
This is to be done using apache POI in java. Any idea how to do
这是在java中使用apache POI来完成的。知道怎么做
回答by Zi Kai
//Open the first excel file.
Workbook SourceBook1 = new Workbook("F:\Downloads\charts.xlsx");
//Define the second source book.
//Open the second excel file.
Workbook SourceBook2 = new Workbook("F:\Downloads\picture.xlsx");
//Combining the two workbooks
SourceBook1.combine(SourceBook2);
//Save the target book file.
SourceBook1.save("F:\Downloads\combined.xlsx");
http://www.aspose.com/docs/display/cellsjava/Combine+Multiple+Workbooks+into+a+Single+Workbook
http://www.aspose.com/docs/display/cellsjava/Combine+Multiple+Workbooks+into+a+Single+Workbook
回答by Akshoy
This below piece of code can be used to merge two excel files as two sheets in one workbook in java. This will copy both the excel files with the same structure.
下面这段代码可用于将两个excel文件合并为java中一个工作簿中的两个工作表。这将复制具有相同结构的两个 excel 文件。
It has worked for me.
它对我有用。
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class MergeMultipleXlsFilesInDifferentSheet{
public static void mergeExcelFiles(File file) throws IOException {
HSSFWorkbook book = new HSSFWorkbook();
System.out.println(file.getName());
String directoryName = "C:\MergingXlsDirectory";
File directory = new File(directoryName);
//get all the files from a directory
File[] fList = directory.listFiles();
for (File file1 : fList){
if (file1.isFile()){
String ParticularFile = file1.getName();
FileInputStream fin = new FileInputStream(new File(directoryName+"\"+ParticularFile));
HSSFWorkbook b = new HSSFWorkbook(fin);
for (int i = 0; i < b.getNumberOfSheets(); i++) {
HSSFSheet sheet = book.createSheet(b.getSheetName(i));
copySheets(book, sheet, b.getSheetAt(i));
System.out.println("Copying..");
}
}
try {
writeFile(book, file);
}catch(Exception e) {
e.printStackTrace();
}
}
}
protected static void writeFile(HSSFWorkbook book, File file) throws Exception {
FileOutputStream out = new FileOutputStream(file);
book.write(out);
out.close();
}
private static void copySheets(HSSFWorkbook newWorkbook, HSSFSheet newSheet, HSSFSheet sheet){
copySheets(newWorkbook, newSheet, sheet, true);
}
private static void copySheets(HSSFWorkbook newWorkbook, HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle){
int newRownumber = newSheet.getLastRowNum();
int maxColumnNum = 0;
Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
HSSFRow srcRow = sheet.getRow(i);
HSSFRow destRow = newSheet.createRow(i + newRownumber);
if (srcRow != null) {
copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public static void copyRow(HSSFWorkbook newWorkbook, HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) {
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
HSSFCell oldCell = srcRow.getCell(j);
HSSFCell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(newWorkbook, oldCell, newCell, styleMap);
}
}
}
public static void copyCell(HSSFWorkbook newWorkbook, HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) {
if(styleMap != null) {
int stHashCode = oldCell.getCellStyle().hashCode();
HSSFCellStyle newCellStyle = styleMap.get(stHashCode);
if(newCellStyle == null){
newCellStyle = newWorkbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
switch(oldCell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
newCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
public static void main(String[] args) {
try {
mergeExcelFiles(new File("C:\NewXlsfileWhereDataWillBeMerged.xls"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
回答by connectoram
My original idea was to use the cloneSheet method of XSSFWorkbook to do the trick, I was successful in copying the Relations and Drawings however failed to copy the data itself. Why? Because the XSSFSheet's method write and read are protected, I did come up with my own version of XSSFSheet by extending it and making the two methods write and read public, but that would mean I will have to copy and edit every source file using XSSFSheet replacing it with my version of XSSFSheet that would mean too much coding I didn't have that kind of time for the project.
我最初的想法是使用 XSSFWorkbook 的 cloneSheet 方法来实现这一点，我成功复制了关系和绘图，但未能复制数据本身。为什么？因为 XSSFSheet 的方法 write 和 read 是受保护的，我确实想出了我自己的 XSSFSheet 版本，通过扩展它并使这两种方法读写公开，但这意味着我将不得不使用 XSSFSheet 替换来复制和编辑每个源文件它与我的 XSSFSheet 版本一起意味着太多的编码，我没有那种时间来做这个项目。
The original answer to this question was posted on:
这个问题的原始答案发布在：
How to copy a sheet between Excel workbooks in Java
However, my project requirement were different, so I improvised the answer.
但是，我的项目要求不同，所以我即兴回答。
I added a method called combine which accepts target workbook, and source workbook.
我添加了一个名为 combine 的方法，它接受目标工作簿和源工作簿。
The methods loops through all the worksheets in the source workbook and adds them to target workbook. The original answer lacked copying of relations and drawing.
这些方法循环遍历源工作簿中的所有工作表并将它们添加到目标工作簿。原始答案缺少关系和绘图的复制。
Also the workbooks I was to copy from lacked Footer, neede Gridlines to be turned off, and fit to page. So here is my solution:
还有我要从缺少页脚复制的工作簿，需要关闭网格线并适合页面。所以这是我的解决方案：
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.POIXMLDocumentPart.RelationPart;
import org.apache.poi.POIXMLException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.PackageRelationship;
import org.apache.poi.openxml4j.opc.TargetMode;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.POILogger;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRelation;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import com.hcsc.eas.framework.services.logging.Logger;
public class XSSFWorkbookHelper {
static protected Logger logger = Logger.getLogger(XSSFWorkbookHelper.class.getName());
public static void combine(XSSFWorkbook tgt, XSSFWorkbook src) throws InvalidFormatException {
// begin sheets loop
boolean first=true;
String firstSheetName = null;
XSSFSheet tgtSheet = null;
for (int i = 0; i < src.getNumberOfSheets(); i++) {
XSSFSheet srcSheet = src.getSheetAt(i);
String sheetName = srcSheet.getSheetName().replaceAll("_", "-");
if (first) {
firstSheetName = srcSheet.getSheetName();
if (sheetName.equals(firstSheetName)) sheetName = sheetName + "_";
first = false;
}
tgtSheet = tgt.createSheet(sheetName);
copyRelations(tgtSheet, srcSheet);
copySheets(tgtSheet, srcSheet);
} // end sheets loop
tgtSheet = tgt.getSheet(firstSheetName);
if(tgtSheet != null) {
tgt.removeSheetAt(tgt.getSheetIndex(tgtSheet));
}
}
private static void copyRelations(XSSFSheet tgtSheet,XSSFSheet srcSheet) {
// copy sheet's relations
List<RelationPart> rels = srcSheet.getRelationParts();
// if the sheet being cloned has a drawing then rememebr it and re-create it too
XSSFDrawing dg = null;
for(RelationPart rp : rels) {
POIXMLDocumentPart r = rp.getDocumentPart();
// do not copy the drawing relationship, it will be re-created
if(r instanceof XSSFDrawing) {
dg = (XSSFDrawing)r;
continue;
}
addRelation(rp, tgtSheet);
}
try {
for(PackageRelationship pr : srcSheet.getPackagePart().getRelationships()) {
if (pr.getTargetMode() == TargetMode.EXTERNAL) {
tgtSheet.getPackagePart().addExternalRelationship
(pr.getTargetURI().toASCIIString(), pr.getRelationshipType(), pr.getId());
}
}
} catch (InvalidFormatException e) {
throw new POIXMLException("Failed to clone sheet", e);
}
CTWorksheet ct = tgtSheet.getCTWorksheet();
if(ct.isSetLegacyDrawing()) {
logger.warn(POILogger.WARN + "Cloning sheets with comments is not yet supported.");
ct.unsetLegacyDrawing();
}
if (ct.isSetPageSetup()) {
logger.warn(POILogger.WARN + "Cloning sheets with page setup is not yet supported.");
ct.unsetPageSetup();
}
tgtSheet.setSelected(false);
// clone the sheet drawing alongs with its relationships
if (dg != null) {
if(ct.isSetDrawing()) {
// unset the existing reference to the drawing,
// so that subsequent call of tgtSheet.createDrawingPatriarch() will create a new one
ct.unsetDrawing();
}
XSSFDrawing clonedDg = tgtSheet.createDrawingPatriarch();
// copy drawing contents
clonedDg.getCTDrawing().set(dg.getCTDrawing());
clonedDg = tgtSheet.createDrawingPatriarch();
// Clone drawing relations
List<RelationPart> srcRels = srcSheet.createDrawingPatriarch().getRelationParts();
for (RelationPart rp : srcRels) {
addRelation(rp, clonedDg);
}
}
}
private static void addRelation(RelationPart rp, POIXMLDocumentPart target) {
PackageRelationship rel = rp.getRelationship();
if (rel.getTargetMode() == TargetMode.EXTERNAL) {
target.getPackagePart().addRelationship(rel.getTargetURI(), rel.getTargetMode(), rel.getRelationshipType(),
rel.getId());
} else {
XSSFRelation xssfRel = XSSFRelation.getInstance(rel.getRelationshipType());
if (xssfRel == null) {
// Don't copy all relations blindly, but only the ones we know
// about
throw new POIXMLException(
"Can't clone sheet - unknown relation type found: " + rel.getRelationshipType());
}
target.addRelation(rel.getId(), xssfRel, rp.getDocumentPart());
}
}
/**
* @param newSheet
* the sheet to create from the copy.
* @param sheet
* the sheet to copy.
*/
public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet) {
copySheets(newSheet, sheet, true);
}
/**
* @param newSheet
* the sheet to create from the copy.
* @param sheet
* the sheet to copy.
* @param copyStyle
* true copy the style.
*/
public static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) {
setupSheet(newSheet);
int maxColumnNum = 0;
Map<Integer, CellStyle> styleMap = (copyStyle) ? new HashMap<Integer, CellStyle>() : null;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
XSSFRow srcRow = sheet.getRow(i);
XSSFRow destRow = newSheet.createRow(i);
if (srcRow != null) {
copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public static void setupSheet(XSSFSheet newSheet) {
newSheet.getFooter().setCenter("&7 _x0000_ A Division of Health Care Service Corporation, a Mutual Legal Reserve Company, \n _x0000_ an Independent Licensee of the Blue Cross and Blue Shield Association");
newSheet.setDisplayGridlines(false);
newSheet.setMargin(Sheet.RightMargin, 0.5 /* inches */ );
newSheet.setMargin(Sheet.LeftMargin, 0.5 /* inches */ );
newSheet.setMargin(Sheet.TopMargin, 0.5 /* inches */ );
newSheet.setMargin(Sheet.BottomMargin, 0.5 /* inches */ );
newSheet.setFitToPage(true);
}
/**
* @param srcSheet
* the sheet to copy.
* @param destSheet
* the sheet to create.
* @param srcRow
* the row to copy.
* @param destRow
* the row to create.
* @param styleMap
* -
*/
public static void copyRow(XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow,
Map<Integer, CellStyle> styleMap) {
try {
// manage a list of merged zone in order to not insert two times a
// merged zone
Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
destRow.setHeight(srcRow.getHeight());
// reckoning delta rows
int deltaRows = destRow.getRowNum() - srcRow.getRowNum();
// pour chaque row
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
XSSFCell oldCell = srcRow.getCell(j); // ancienne cell
XSSFCell newCell = destRow.getCell(j); // new cell
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
// copy chaque cell
copyCell(oldCell, newCell, styleMap);
// copy les informations de fusion entre les cellules
// System.out.println("row num: " + srcRow.getRowNum() + " ,
// col: " + (short)oldCell.getColumnIndex());
CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(),
(short) oldCell.getColumnIndex());
if (mergedRegion != null) {
// System.out.println("Selected merged region: " +
// mergedRegion.toString());
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow() + deltaRows,
mergedRegion.getLastRow() + deltaRows, mergedRegion.getFirstColumn(),
mergedRegion.getLastColumn());
// System.out.println("New merged region: " +
// newMergedRegion.toString());
CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
if (isNewMergedRegion(wrapper, mergedRegions)) {
mergedRegions.add(wrapper);
destSheet.addMergedRegion(wrapper.range);
}
}
}
}
} catch (Exception e) {
//e.printStackTrace();
logger.warn(POILogger.WARN + "merge area failure, happens when a merge area overlaps.");
}
}
/**
* @param oldCell
* @param newCell
* @param styleMap
*/
public static void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, CellStyle> styleMap) {
if (styleMap != null) {
if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
newCell.setCellStyle(oldCell.getCellStyle());
} else {
int stHashCode = oldCell.getCellStyle().hashCode();
CellStyle newCellStyle = styleMap.get(stHashCode);
if (newCellStyle == null) {
newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
}
switch (oldCell.getCellType()) {
case XSSFCell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case XSSFCell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_BLANK:
newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case XSSFCell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
default:
break;
}
}
/**
* Retrieves cell merge information in the source sheet
* to apply them to the destination sheet ... Get all zones
* merged in the source sheet and look for each of them if she
* find in the current row that we are dealing. If yes, return the object
* CellRangeAddress.
*
* @param sheet
* the sheet containing the data.
* @param rowNum
* the num of the row to copy.
* @param cellNum
* the num of the cell to copy.
* @return the CellRangeAddress created.
*/
public static CellRangeAddress getMergedRegion(XSSFSheet sheet, int rowNum, short cellNum) {
for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
CellRangeAddress merged = sheet.getMergedRegion(i);
if (merged.isInRange(rowNum, cellNum)) {
return merged;
}
}
return null;
}
/**
* Check that the merged region has been created in the destination sheet.
*
* @param newMergedRegion
* the merged region to copy or not in the destination sheet.
* @param mergedRegions
* the list containing all the merged region.
* @return true if the merged region is already in the list or not.
*/
private static boolean isNewMergedRegion(CellRangeAddressWrapper newMergedRegion,
Set<CellRangeAddressWrapper> mergedRegions) {
return !mergedRegions.contains(newMergedRegion);
}
}
class CellRangeAddressWrapper implements Comparable<CellRangeAddressWrapper> {
public CellRangeAddress range;
/**
* @param theRange
* the CellRangeAddress object to wrap.
*/
public CellRangeAddressWrapper(CellRangeAddress theRange) {
this.range = theRange;
}
/**
* @param o
* the object to compare.
* @return -1 the current instance is prior to the object in parameter, 0:
* equal, 1: after...
*/
public int compareTo(CellRangeAddressWrapper o) {
if (range.getFirstColumn() < o.range.getFirstColumn() && range.getFirstRow() < o.range.getFirstRow()) {
return -1;
} else if (range.getFirstColumn() == o.range.getFirstColumn() && range.getFirstRow() == o.range.getFirstRow()) {
return 0;
} else {
return 1;
}
}
}
回答by Taran
This code can be used for merging two excels. It worked for me:
此代码可用于合并两个 excel。它对我有用：
public static void mergeExcels(Workbook sourceExcel, Workbook destExcel) {
for(int sheetIndex = 0; sheetIndex < sourceExcel.getNumberOfSheets(); ++sheetIndex) {
Sheet sheet = sourceExcel.getSheetAt(sheetIndex);
if (destExcel.getSheet(sheet.getSheetName()) != null) {
destExcel.removeSheetAt(destExcel.getSheetIndex(sheet.getSheetName()));
}
Sheet outputSheet = destExcel.createSheet(sheet.getSheetName());
copySheets(outputSheet, sheet, true);
}
}
public static void copySheets(Sheet newSheet, Sheet sheet, boolean copyStyle) {
int maxColumnNum = 0;
Map<Integer, CellStyle> styleMap = copyStyle ? new HashMap() : null;
int i;
List<CellRangeAddress> mergedRegions = new ArrayList<CellRangeAddress>();
for(i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); ++i) {
Row srcRow = sheet.getRow(i);
Row destRow = newSheet.createRow(i);
if (srcRow != null) {
copyRow(sheet, newSheet, srcRow, destRow, styleMap, mergedRegions);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for(i = 0; i <= maxColumnNum; ++i) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public static void copyRow(Sheet srcSheet, Sheet destSheet, Row srcRow, Row destRow, Map<Integer, CellStyle> styleMap, List<CellRangeAddress> mergedRegions ) {
destRow.setHeight(srcRow.getHeight());
for(int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); ++j) {
if (j >= 0) {
Cell oldCell = srcRow.getCell(j);
Cell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
copyCell(oldCell, newCell, styleMap);
CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short)oldCell.getColumnIndex());
if (mergedRegion != null) {
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
mergedRegions.add(newMergedRegion);
destSheet.addMergedRegion(newMergedRegion);
}
}
}
}
}
}
public static void copyCell(Cell oldCell, Cell newCell, Map<Integer, CellStyle> styleMap) {
if (styleMap != null) {
if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
newCell.setCellStyle(oldCell.getCellStyle());
} else {
int stHashCode = oldCell.getCellStyle().hashCode();
CellStyle newCellStyle = (CellStyle)styleMap.get(stHashCode);
if (newCellStyle == null) {
newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
styleMap.put(stHashCode, newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
}
switch(oldCell.getCellType()) {
case 0:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case 1:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case 2:
newCell.setCellFormula(oldCell.getCellFormula());
break;
case 3:
newCell.setCellType(3);
break;
case 4:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case 5:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
}
}
public static CellRangeAddress getMergedRegion(Sheet sheet, int rowNum, short cellNum) {
for(int i = 0; i < sheet.getNumMergedRegions(); ++i) {
CellRangeAddress merged = sheet.getMergedRegion(i);
if (merged.isInRange(rowNum, cellNum)) {
return merged;
}
}
return null;
}
private static boolean areAllTrue(boolean... values) {
for(int i = 0; i < values.length; ++i) {
if (!values[i]) {
return false;
}
}
return true;
}
private static boolean isNewMergedRegion(CellRangeAddress newMergedRegion, Collection<CellRangeAddress> mergedRegions) {
boolean isNew = true;
Iterator i$ = mergedRegions.iterator();
while(i$.hasNext()) {
CellRangeAddress add = (CellRangeAddress)i$.next();
boolean r1 = add.getFirstRow() == newMergedRegion.getFirstRow();
boolean r2 = add.getLastRow() == newMergedRegion.getLastRow();
boolean c1 = add.getFirstColumn() == newMergedRegion.getFirstColumn();
boolean c2 = add.getLastColumn() == newMergedRegion.getLastColumn();
if (areAllTrue(r1, r2, c1, c2)) {
isNew = false;
}
}
return isNew;
}
