Skip to content

Instantly share code, notes, and snippets.

@hakobera
Created March 5, 2012 14:07
Show Gist options
  • Save hakobera/1978452 to your computer and use it in GitHub Desktop.
Save hakobera/1978452 to your computer and use it in GitHub Desktop.
POIとjXLSでWorkbook間のシートコピー(Styleは無視して良い場合)
package util;
import java.io.InputStream;
import java.util.Collection;
import java.util.Iterator;
import java.util.Set;
import java.util.TreeSet;
import net.sf.jxls.util.Util;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* jxls 用のユーティリティです。
*/
public class ReportUtil {
public static InputStream loadTemplate(String templateName) {
ClassLoader cl = Thread.currentThread().getContextClassLoader();
return cl.getResourceAsStream(templateName);
}
public static void copySheets(Sheet newSheet, Sheet sheet) {
int maxColumnNum = 0;
for (int i = sheet.getFirstRowNum(), c = sheet.getLastRowNum(); i <= c; i++) {
org.apache.poi.ss.usermodel.Row srcRow = sheet.getRow(i);
org.apache.poi.ss.usermodel.Row destRow = newSheet.createRow(i);
if (srcRow != null) {
copyRow(sheet, newSheet, srcRow, destRow);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
private static void copyRow(Sheet srcSheet, Sheet destSheet, org.apache.poi.ss.usermodel.Row srcRow,
org.apache.poi.ss.usermodel.Row destRow) {
Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>();
if (srcRow.getHeight() >= 0) {
destRow.setHeight(srcRow.getHeight());
}
if (srcRow.getFirstCellNum() >= 0 && srcRow.getLastCellNum() >= 0) {
for (int j = srcRow.getFirstCellNum(), c = srcRow.getLastCellNum(); j <= c; j++) {
org.apache.poi.ss.usermodel.Cell oldCell = srcRow.getCell(j);
org.apache.poi.ss.usermodel.Cell newCell = destRow.getCell(j);
if (oldCell != null) {
if (newCell == null) {
newCell = destRow.createCell(j);
}
Util.copyCell(oldCell, newCell, false);
CellRangeAddress mergedRegion = Util.getMergedRegion(srcSheet, srcRow.getRowNum(),
oldCell.getColumnIndex());
if (mergedRegion != null) {
// Region newMergedRegion = new Region(
// destRow.getRowNum(),
// mergedRegion.getColumnFrom(),
// destRow.getRowNum() + mergedRegion.getRowTo() -
// mergedRegion.getRowFrom(), mergedRegion.getColumnTo()
// );
CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(),
mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
if (isNewMergedRegion(newMergedRegion, mergedRegions)) {
mergedRegions.add(newMergedRegion);
destSheet.addMergedRegion(newMergedRegion);
}
}
}
}
}
}
private static boolean isNewMergedRegion(CellRangeAddress region, Collection<CellRangeAddress> mergedRegions) {
for (Iterator<CellRangeAddress> iterator = mergedRegions.iterator(); iterator.hasNext();) {
CellRangeAddress cellRangeAddress = iterator.next();
if (Util.areRegionsEqual(cellRangeAddress, region)) {
return false;
}
}
return true;
}
}
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import util.ReportUtil;
public class Sample {
public static void main(String[] args) throws Exception {
Workbook srcBook = openBook("src.xls");
Workbook dstBook = openBook("dst.xls");
copyAllSheets(dstBook, srcBook);
dstBook.write(new FileOutputStream("result.xls"));
}
private static Workbook openBook(String filePath) throws InvalidFormatException, IOException {
InputStream is = new FileInputStream(filePath);
return WorkbookFactory.create(is);
}
private static void copyAllSheets(Workbook dstBook, Workbook srcBook) {
int numOfSheet = srcBook.getNumberOfSheets();
for (int i = 0; i < numOfSheet; ++i) {
Sheet src = srcBook.getSheetAt(i);
Sheet dst = dstBook.createSheet(src.getSheetName());
ReportUtil.copySheets(dst, src);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment