private void printSetup(Sheet oldSheet, Sheet newSheet) {
// print setup
PrintSetup oldPS = oldSheet.getPrintSetup();
PrintSetup newPS = newSheet.getPrintSetup();
newPS.setPaperSize(oldPS.getPaperSize());
newPS.setScale(oldPS.getScale());
newPS.setPageStart(oldPS.getPageStart());
newPS.setFitWidth(oldPS.getFitWidth());
newPS.setFitHeight(oldPS.getFitHeight());
newPS.setLeftToRight(oldPS.getLeftToRight());
newPS.setLandscape(oldPS.getLandscape());
newPS.setValidSettings(oldPS.getValidSettings());
newPS.setNoColor(oldPS.getNoColor());
newPS.setDraft(oldPS.getDraft());
newPS.setNotes(oldPS.getNotes());
newPS.setNoOrientation(oldPS.getNoOrientation());
newPS.setUsePage(oldPS.getUsePage());
newPS.setHResolution(oldPS.getHResolution());
newPS.setVResolution(oldPS.getVResolution());
newPS.setHeaderMargin(oldPS.getHeaderMargin());
newPS.setFooterMargin(oldPS.getFooterMargin());
newPS.setCopies(oldPS.getCopies());
newSheet.setRightToLeft(oldSheet.isRightToLeft());
newSheet.setHorizontallyCenter(oldSheet.getHorizontallyCenter());
newSheet.setVerticallyCenter(oldSheet.getVerticallyCenter());
newSheet.setAutobreaks(oldSheet.getAutobreaks());
newSheet.setDisplayZeros(oldSheet.isDisplayZeros());
newSheet.setFitToPage(oldSheet.getFitToPage());
newSheet.setAutobreaks(oldSheet.getAutobreaks());
newSheet.setPrintGridlines(oldSheet.isPrintGridlines());
newSheet.getHeader().setCenter(oldSheet.getHeader().getCenter());
newSheet.getHeader().setLeft(oldSheet.getHeader().getLeft());
newSheet.getHeader().setRight(oldSheet.getHeader().getRight());
newSheet.getFooter().setCenter(oldSheet.getFooter().getCenter());
newSheet.getFooter().setLeft(oldSheet.getFooter().getLeft());
newSheet.getFooter().setRight(oldSheet.getFooter().getRight());
newSheet.setMargin(Sheet.LeftMargin, oldSheet.getMargin(Sheet.LeftMargin));
newSheet.setMargin(Sheet.RightMargin, oldSheet.getMargin(Sheet.RightMargin));
newSheet.setMargin(Sheet.HeaderMargin, oldSheet.getMargin(Sheet.HeaderMargin));
newSheet.setMargin(Sheet.FooterMargin, oldSheet.getMargin(Sheet.FooterMargin));
newSheet.setMargin(Sheet.BottomMargin, oldSheet.getMargin(Sheet.BottomMargin));
newSheet.setMargin(Sheet.TopMargin, oldSheet.getMargin(Sheet.TopMargin));
}
// traversal cell
public void traversalCell(String filePath) {
try {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(filePath); // 支持2007
} catch (Exception ex) {
workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
}
// 获得Excel中工作表个数
System.out.println("工作表个数 :" + workBook.getNumberOfSheets());
// 循环每个工作表
for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
// 创建工作表
Sheet sheet = workBook.getSheetAt(i);
int rows = sheet.getPhysicalNumberOfRows(); // 获得行数
System.out.println(
"工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());
if (rows > 0) {
sheet.getMargin(Sheet.TopMargin);
for (int r = 0; r < rows; r++) { // 行循环
Row row = sheet.getRow(r);
if (row != null) {
int cells = row.getLastCellNum(); // 获得列数
for (short c = 0; c < cells; c++) { // 列循环
Cell cell = row.getCell(c);
if (cell != null) {
String value = getCellData(cell);
System.out.println("第" + r + "行 " + "第" + c + "列：" + value);
}
}
}
}
}
// 查询合并的单元格
for (i = 0; i < sheet.getNumMergedRegions(); i++) {
System.out.println("第" + i + "个合并单元格");
CellRangeAddress region = sheet.getMergedRegion(i);
int row = region.getLastRow() - region.getFirstRow() + 1;
int col = region.getLastColumn() - region.getFirstColumn() + 1;
System.out.println("起始行:" + region.getFirstRow());
System.out.println("起始列:" + region.getFirstColumn());
System.out.println("所占行:" + row);
System.out.println("所占列:" + col);
}
}
} catch (Exception ex) {
ex.printStackTrace();
}
}