Folks,
我们有一个要求,我们希望使用JAVA将两个excel表合并到一个工作簿中。
我们知道这可以通过Apache POI来实现。
但我们不想做以下事情。我们只想将这两个excel文件合并到一个工作簿中。
不要做:将两个Excel文件都加载到内存中。它们的内容巨大,超过10万行。
谢谢。
不能只将工作表复制到另一个工作簿。您需要创建一个新工作簿,然后创建一个工作表,并逐单元格添加工作表的内容。
我可以帮助你用两种方法复制行,和复制单元格和依赖的方法:
/**
* Copy row with style and merged region. This manage a shift in copy. You can copy row 1 to 10 and paste it between. this method can be use for copying in same sheet
* 21 and 30
* @param pSrcSheet source sheet
* @param pDestSheet dest sheet
* @param pSrcLine line to copy
* @param pDestLine line to paste
* @param pStyleMap just get a new HashMap<Integer, CellStyle> pStyleMap
* @param pLineShift the shift set 0 if you don't need to shift your row. Example copy row 1 to 10 and paste it between 21 and 30 you
* need set thhe shift value 20. The value could be negative to pick up the data
*/
public static void copyRow(Sheet pSrcSheet, Sheet pDestSheet, Row pSrcLine, Row pDestLine,
Map<Integer, CellStyle> pStyleMap, int pLineShift)
{
ArrayList<CellRangeAddress> mergedRegions = getMergedRegions(pDestSheet);
pDestLine.setHeight(pSrcLine.getHeight());
for (int j = pSrcLine.getFirstCellNum(); j < pSrcLine.getLastCellNum(); j++)
{
Cell oldCell = null;
Cell newCell = null;
// if no cell in row skip the iteration
if (j >= 0)
{
oldCell = pSrcLine.getCell(j);
newCell = pDestLine.getCell(j, Row.CREATE_NULL_AS_BLANK);
}
if (oldCell != null)
{
if (newCell == null)
{
newCell = pDestLine.createCell(j);
}
copyCell(oldCell, newCell, pStyleMap);
CellRangeAddress mergedRegion =
getCurrentMergedRegion(pSrcSheet, pSrcLine.getRowNum(), (short) oldCell.getColumnIndex());
if (mergedRegion != null)
{
CellRangeAddress newMergedRegion =
new CellRangeAddress(mergedRegion.getFirstRow() + pLineShift, mergedRegion.getLastRow()
+ pLineShift, mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
if (isNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.add(newMergedRegion);
pDestSheet.addMergedRegion(newMergedRegion);
}
}
}
}
}
/**
* Copy cell with style from same workbook or not
* @param pOldCell
* @param pNewCell
* @param pStyleMap
*/
public static void copyCell(Cell pOldCell, Cell pNewCell, Map<Integer, CellStyle> pStyleMap)
{
if (pStyleMap != null)
{
if (pOldCell.getSheet().getWorkbook() == pNewCell.getSheet().getWorkbook())
{
pNewCell.setCellStyle(pOldCell.getCellStyle());
}
else
{
int stHashCode = pOldCell.getCellStyle().hashCode();
CellStyle newCellStyle = pStyleMap.get(stHashCode);
if (newCellStyle == null)
{
newCellStyle = pNewCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.cloneStyleFrom(pOldCell.getCellStyle());
pStyleMap.put(stHashCode, newCellStyle);
}
pNewCell.setCellStyle(newCellStyle);
}
}
switch (pOldCell.getCellType())
{
case Cell.CELL_TYPE_STRING:
pNewCell.setCellValue(pOldCell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
pNewCell.setCellValue(pOldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
pNewCell.setCellType(Cell.CELL_TYPE_BLANK);
break;
case Cell.CELL_TYPE_BOOLEAN:
pNewCell.setCellValue(pOldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
pNewCell.setCellErrorValue(pOldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
pNewCell.setCellFormula(pOldCell.getCellFormula());
break;
default:
break;
}
}
/**
* return the cellRangeAdress of the merge region where the cell is. Null if the cell is not in merged region
* @param pSheet
* @param pRowNum
* @param pCellNum
* @return return the cellRangeAdress of the merge region where the cell is. Null if the cell is not in merged
* region
*/
public static CellRangeAddress getCurrentMergedRegion(Sheet pSheet, int pRowNum, int pCellNum)
{
for (int i = 0; i < pSheet.getNumMergedRegions(); i++)
{
CellRangeAddress merged = pSheet.getMergedRegion(i);
if (merged.isInRange(pRowNum, pCellNum))
{
return merged;
}
}
return null;
}
/**
* return the index of the merge region where the cell is. -1 if the cell is not in merged region
* @param pSheet
* @param pRowNum
* @param pCellNum
* @return return the index of the merge region where the cell is. -1 if the cell is not in merged region
*/
public static int getCurrentMergedRegionIndex(Sheet pSheet, int pRowNum, int pCellNum)
{
for (int i = 0; i < pSheet.getNumMergedRegions(); i++)
{
CellRangeAddress merged = pSheet.getMergedRegion(i);
if (merged.isInRange(pRowNum, pCellNum))
{
return i;
}
}
return -1;
}
/**
* return the cellRangeAdress of the merge region where the cell is. Null if the cell is not in merged region
* @param pSheet
* @param pRowNum
* @param pCellNum
* @return return the cellRangeAdress of the merge region where the cell is. Null if the cell is not in merged
* region
*/
public static ArrayList<CellRangeAddress> getMergedRegions(Sheet pSheet)
{
ArrayList<CellRangeAddress> mergedRegions = new ArrayList<CellRangeAddress>();
for (int i = 0; i < pSheet.getNumMergedRegions(); i++)
{
mergedRegions.add(pSheet.getMergedRegion(i));
}
return mergedRegions;
}
/**
* verify all boolean are true
* @param pValues
* @return
*/
public static boolean areAllTrue(boolean... pValues)
{
for (int i = 0; i < pValues.length; ++i)
{
if (pValues[i] != true)
{
return false;
}
}
return true;
}
/**
* Compare 2 region if they are the same return true
* @param pRegion region compare to pRegion2
* @param pRegion2 region compare to pRegion
* @return true if the region are the same, false if region are different
*/
public static boolean cellRangeAdressEquals(CellRangeAddress pRegion, CellRangeAddress pRegion2)
{
boolean r1 = (pRegion.getFirstRow() == pRegion2.getFirstRow());
boolean r2 = (pRegion.getLastRow() == pRegion2.getLastRow());
boolean c1 = (pRegion.getFirstColumn() == pRegion2.getFirstColumn());
boolean c2 = (pRegion.getLastColumn() == pRegion2.getLastColumn());
return areAllTrue(r1, r2, c1, c2);
}
/**
* Verify the it's a new region or existing
* @param pNewMergedRegion
* @param pMergedRegions
* @return
*/
public static boolean isNewMergedRegion(CellRangeAddress pNewMergedRegion,
ArrayList<CellRangeAddress> pMergedRegions)
{
boolean isNew = true;
int i = 0;
// we want to check if newMergedRegion is contained inside our collection
while (pMergedRegions != null && i < pMergedRegions.size() && isNew)
{
if (cellRangeAdressEquals(pMergedRegions.get(i), pNewMergedRegion))
{
isNew = false;
}
i++;
}
return isNew;
}
你可以在这个线程上找到一些帮助:http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-excel
如果你有更多关于你的问题的信息,我会尽我所能帮助你;(