// 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();
}
}
/**
* @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, XSSFCellStyle> styleMap) {
Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
destRow.setHeight(srcRow.getHeight());
for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
XSSFCell oldCell = srcRow.getCell(j);
XSSFCell 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.getFirstColumn(),
mergedRegion.getLastRow(),
mergedRegion.getLastColumn());
CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
if (isNewMergedRegion(wrapper, mergedRegions)) {
mergedRegions.add(wrapper);
destSheet.addMergedRegion(wrapper.range);
}
}
}
}
}
public void testModifyArrayCells_removeRow() {
Workbook workbook = _testDataProvider.createWorkbook();
Sheet sheet = workbook.createSheet();
// single-cell array formulas behave just like normal cells
CellRangeAddress cra = CellRangeAddress.valueOf("B5");
CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
Cell scell = srange.getTopLeftCell();
assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
Row srow = scell.getRow();
assertSame(srow, sheet.getRow(cra.getFirstRow()));
sheet.removeRow(srow);
assertNull(sheet.getRow(cra.getFirstRow()));
// re-create the removed row and cell
scell = sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn());
assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
assertFalse(scell.isPartOfArrayFormulaGroup());
// we cannot remove rows with cells included in a multi-cell array formula
CellRange<? extends Cell> mrange =
sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
for (Cell mcell : mrange) {
int columnIndex = mcell.getColumnIndex();
Row mrow = mcell.getRow();
try {
sheet.removeRow(mrow);
fail("expected exception");
} catch (IllegalStateException e) {
String msg =
"Row[rownum="
+ mrow.getRowNum()
+ "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
assertEquals(msg, e.getMessage());
}
// a failed invocation of Row.removeCell leaves the row
// in the state that it was in prior to the invocation
assertSame(mrow, sheet.getRow(mrow.getRowNum()));
assertSame(mcell, mrow.getCell(columnIndex));
assertTrue(mcell.isPartOfArrayFormulaGroup());
assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
}
}
private void processCell(Element tr, XSSFCell cell) {
int num = cell.getSheet().getNumMergedRegions();
// System.out.println(cell.getCTCell());
for (int i = 0; i < num; i++) {
CellRangeAddress c = cell.getSheet().getMergedRegion(i);
System.out.println(c.getFirstColumn());
;
System.out.println(c.getLastColumn());
System.out.println(c.getFirstRow());
System.out.println(c.getLastRow());
System.out.println();
System.out.println(cell.getRowIndex());
System.out.println(cell.getColumnIndex());
System.out.println("\n\n\n");
// System.out.println(cra);
}
// System.exit(0);
Element td = htmlDocumentFacade.createTableCell();
Object value;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
value = "\u00a0";
break;
case Cell.CELL_TYPE_NUMERIC:
value = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
value = cell.getNumericCellValue();
break;
default:
value = cell.getRichStringCellValue();
break;
}
if (value instanceof XSSFRichTextString) {
processCellStyle(td, cell.getCellStyle(), (XSSFRichTextString) value);
td.setTextContent(value.toString());
} else {
processCellStyle(td, cell.getCellStyle(), null);
td.setTextContent(value.toString());
}
// System.err.println(value);
tr.appendChild(td);
}
/**
* @param
* @return void
*/
private void fillMergedRegion(HSSFSheet sheet, CellRangeAddress address, HSSFCellStyle style) {
for (int i = address.getFirstRow(); i <= address.getLastRow(); i++) {
HSSFRow row = sheet.getRow(i);
if (row == null) row = sheet.createRow(i);
for (int j = address.getFirstColumn(); j <= address.getLastColumn(); j++) {
HSSFCell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
if (style != null) cell.setCellStyle(style);
}
}
}
}
/**
* 取单元格所在的合并区域，如果返回空，则说明没有在合并区域
*
* @param cell -- 指定的单元格
* @return
*/
private static CellRangeAddress getMergedRegion(HSSFCell cell) {
HSSFSheet sheet = cell.getSheet();
CellRangeAddress range = null;
int mergedNum = sheet.getNumMergedRegions();
for (int i = 0; i < mergedNum; i++) {
range = sheet.getMergedRegion(i);
if (range.getFirstColumn() == cell.getColumnIndex()
&& range.getFirstRow() == cell.getRowIndex()) {
return range;
}
}
return null;
}
/**
* 输出图片到指定的单元格，参考POI例子中的ReportImageUtil类。
*
* @param cell -- 单元格
* @param bytes -- 图片内容
*/
public static void addImageToSheet(HSSFCell cell, byte[] bytes) {
if (cell == null) {
_log.showError("-----insertImageToSheet: cell is null!");
return;
}
if (bytes == null || bytes.length == 0) {
_log.showError("-----insertImageToSheet: bytes is null!");
return;
}
// 取所在表单对象
HSSFSheet sheet = cell.getSheet();
// 取图片输出行与列
int firstRow = cell.getRowIndex();
int lastRow = cell.getRowIndex();
int firstCol = cell.getColumnIndex();
int lastCol = cell.getColumnIndex();
// 取单元格所在的区域
CellRangeAddress range = getMergedRegion(cell);
if (range != null) {
firstRow = range.getFirstRow();
lastRow = range.getLastRow();
firstCol = range.getFirstColumn();
lastCol = range.getLastColumn();
}
_log.showDebug(
"---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]");
// 图片输出要比单元格的高与宽偏5个值，保留单元的边框，宽度1023表示填充满，高度255表示填充满
HSSFClientAnchor anchor =
new HSSFClientAnchor(5, 5, 1023, 255, (short) firstCol, firstRow, (short) lastCol, lastRow);
anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE);
// 取图片管理器，如果没有则创建
HSSFPatriarch draw = sheet.getDrawingPatriarch();
if (draw == null) {
draw = sheet.createDrawingPatriarch();
}
// 插入新图片，返回的新图片序号无效
sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
// 上面代码中新建图片的序号没有考虑原有图片数量，所以取原图片数量+1作为新图片的序号
List<HSSFPicture> lsPicture = getAllPicture(sheet);
int index = lsPicture.size() + 1;
_log.showDebug("---------new image index=" + index);
draw.createPicture(anchor, index);
}
public static void copyBlock(
Sheet sheet,
int startRow,
int startCol,
int endRow,
int endCol,
boolean copyStyle,
int rowOffset,
int colOffset,
List<CellRangeAddress> mergedRegions) {
for (int row = startRow; row <= endRow; row++) {
Row oldRow = sheet.getRow(row);
if (oldRow == null) continue;
Row newRow = sheet.getRow(row + rowOffset);
if (newRow == null) newRow = sheet.createRow(row + rowOffset);
if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
if (logger.isDebugEnabled()) {
logger.debug("copy row {} to {}", row, row + rowOffset);
logger.debug("Set row height :{}", newRow.getHeightInPoints());
}
for (int col = startCol; col <= endCol; col++) {
Cell oldCell = oldRow.getCell(col);
if (oldCell == null) continue;
Cell newCell = newRow.getCell(col + colOffset);
if (newCell == null) newCell = newRow.createCell(col + colOffset);
copyCell(oldCell, newCell, copyStyle, rowOffset, colOffset);
}
}
for (int col = startCol; col <= endCol; col++) {
if (sheet.getColumnWidth(col) >= 0)
sheet.setColumnWidth(col + colOffset, sheet.getColumnWidth(col));
}
if (mergedRegions != null) {
for (CellRangeAddress cra : mergedRegions) {
CellRangeAddress craNew =
new CellRangeAddress(
cra.getFirstRow() + rowOffset,
cra.getLastRow() + rowOffset,
cra.getFirstColumn() + colOffset,
cra.getLastColumn() + colOffset);
sheet.addMergedRegion(craNew);
}
}
}
/**
* 添加临时表格内容
*
* @param mainSheet -- 原表单内容
* @param subSheet -- 临时表单内容
* @return
*/
public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) {
if (mainSheet == null || subSheet == null) return null;
// 判断报表是否允许输出
if (!isAllowOut(mainSheet)) return mainSheet;
// 原报表的最后一行
int endRowNum = mainSheet.getPhysicalNumberOfRows();
HSSFRow sourow = null, descrow = null;
HSSFCell sourcell = null, descell = null, orgcell = null;
int i = 0, offsetcnt = 0;
// 复制表格中的图片
copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook());
// 设置以合并的单元格
CellRangeAddress range = null;
int mergedNum = subSheet.getNumMergedRegions();
for (i = 0; i < mergedNum; i++) {
range = subSheet.getMergedRegion(i);
range.setFirstRow(range.getFirstRow() + endRowNum);
range.setLastRow(range.getLastRow() + endRowNum);
mainSheet.addMergedRegion(range);
}
range = null;
// int k = 0;
// 设置相关参数
mainSheet.setAlternativeExpression(subSheet.getAlternateExpression());
mainSheet.setAlternativeFormula(subSheet.getAlternateFormula());
mainSheet.setAutobreaks(subSheet.getAutobreaks());
mainSheet.setDialog(subSheet.getDialog());
mainSheet.setDisplayGuts(subSheet.getDisplayGuts());
mainSheet.setFitToPage(subSheet.getFitToPage());
for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) {
sourow = (HSSFRow) iterow.next();
offsetcnt = sourow.getRowNum() + endRowNum;
descrow = mainSheet.createRow(offsetcnt);
descrow.setHeight(sourow.getHeight());
descrow.setHeightInPoints(sourow.getHeightInPoints());
java.util.Iterator<Cell> iter = sourow.cellIterator();
while (iter.hasNext()) {
sourcell = (HSSFCell) iter.next();
int column = sourcell.getColumnIndex();
descell = descrow.createCell(column);
// 取模板中的单元格，与来源表单位置相同
int row = sourcell.getRowIndex();
orgcell = mainSheet.getRow(row).getCell(column);
if (orgcell != null) {
// 取模板中的类型赋值
descell.setCellType(orgcell.getCellType());
// 取模板中的样式赋值
descell.setCellStyle(orgcell.getCellStyle());
} else {
_log.showWarn("module xls [{0}, {1}] cell is null!", row, column);
}
if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING)
descell.setCellValue(sourcell.getStringCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
descell.setCellValue(sourcell.getNumericCellValue());
else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ;
}
sourow = null;
sourcell = null;
descrow = null;
orgcell = null;
}
return mainSheet;
}
/**
* Собирает информацию о некоторой области из указанного листа шаблона excel.
*
* @param sheet шаблон листа отчета в котором находятся данные для данной области.
* @param top номер верхней строки (начиная с 0) относящейся к указанной области.
* @param height количество строк в области. Должно быть как минимум 1.
* @param palette реестр всех стилей используемых в данном отчете.
*/
public Area(final HSSFSheet sheet, final int top, final int height, final StylePalette palette) {
if (sheet == null || top < 0 || height < 1)
throw new IllegalArgumentException("Illegal area arguments");
rows = new ArrayList<Row>();
regions = new ArrayList<Region>();
final int bottom = top + height - 1;
int lastColumn = 0;
for (int i = top; i <= bottom; i++) {
final Row rm = new Row();
rows.add(rm);
final HSSFRow row = sheet.getRow(i);
if (row == null) {
rm.setHeight(sheet.getDefaultRowHeight());
continue;
}
rm.setHeight(row.getHeight());
rm.setHidden(row.getZeroHeight());
lastColumn = Math.max(lastColumn, row.getLastCellNum());
for (int j = 0; j <= row.getLastCellNum(); j++) {
final HSSFCell cell = row.getCell(j);
if (cell == null) {
rm.getCells().add(null);
} else {
rm.getCells().add(new Cell(cell, palette));
}
}
}
this.columnsCount = lastColumn + 1;
final int regcount = sheet.getNumMergedRegions();
for (int i = 0; i < regcount; i++) {
final CellRangeAddress src = sheet.getMergedRegion(i);
if (src.getFirstRow() >= top && src.getLastRow() <= bottom) {
final Region dst =
new Region(
this,
src.getFirstColumn(),
src.getFirstRow() - top,
src.getLastColumn(),
src.getLastRow() - top);
regions.add(dst);
} else if (src.getFirstRow() < top && src.getLastRow() >= top)
throw new IllegalArgumentException(
"Illegal region {top:"
+ top
+ ", height:"
+ height
+ "} bounds: conflict with region {top:"
+ src.getFirstRow()
+ ", bottom:"
+ src.getLastRow()
+ "}");
if (src.getFirstRow() >= top && src.getFirstRow() <= bottom && src.getLastRow() > bottom)
throw new IllegalArgumentException(
"Illegal region {top:"
+ top
+ ", height:"
+ height
+ "} bounds: conflict with region {top:"
+ src.getFirstRow()
+ ", bottom:"
+ src.getLastRow()
+ "}");
}
this.hidden = false;
}
/** @return the 0-based row of the first cell that contains this hyperlink */
public int getFirstRow() {
return _range.getFirstRow();
}
/** Adds in a Row to the given Sheet */
public Row addRow(
Workbook wb,
SheetToAdd sheetToAdd,
RowToAdd rowToAdd,
int rowIndex,
ReportData reportData,
ReportDesign design,
Map<String, String> repeatSections) {
// Create a new row and copy over style attributes from the row to add
Row newRow = sheetToAdd.getSheet().createRow(rowIndex);
Row rowToClone = rowToAdd.getRowToClone();
try {
CellStyle rowStyle = rowToClone.getRowStyle();
if (rowStyle != null) {
newRow.setRowStyle(rowStyle);
}
} catch (Exception e) {
// No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the
// rowStyle. Mysteries of POI
}
newRow.setHeight(rowToClone.getHeight());
// Iterate across all of the cells in the row, and configure all those that need to be
// added/cloned
List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>();
int totalCells = rowToClone.getPhysicalNumberOfCells();
int cellsFound = 0;
for (int cellNum = 0; cellsFound < totalCells; cellNum++) {
Cell currentCell = rowToClone.getCell(cellNum);
log.debug("Handling cell: " + currentCell);
if (currentCell != null) {
cellsFound++;
}
// If we find that the cell that we are on is a repeating cell, then add the appropriate
// number of cells to clone
String repeatingColumnProperty =
getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections);
if (repeatingColumnProperty != null) {
String[] dataSetSpanSplit = repeatingColumnProperty.split(",");
String dataSetName = dataSetSpanSplit[0];
DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData());
int numCellsToRepeat = 1;
if (dataSetSpanSplit.length == 2) {
numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]);
}
log.debug(
"Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat);
int repeatNum = 0;
for (DataSetRow dataSetRow : dataSet) {
repeatNum++;
for (int i = 0; i < numCellsToRepeat; i++) {
Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i));
if (repeatNum == 1 && cell != null && cell != currentCell) {
cellsFound++;
}
Map<String, Object> newReplacements =
getReplacementData(
rowToAdd.getReplacementData(),
reportData,
design,
dataSetName,
dataSetRow,
repeatNum);
cellsToAdd.add(new CellToAdd(cell, newReplacements));
log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow);
}
}
cellNum += numCellsToRepeat;
} else {
cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData()));
log.debug("Adding " + currentCell);
}
}
// Now, go through all of the collected cells, and add them back in
ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb);
String prefix = getExpressionPrefix(design);
String suffix = getExpressionSuffix(design);
for (int i = 0; i < cellsToAdd.size(); i++) {
CellToAdd cellToAdd = cellsToAdd.get(i);
Cell newCell = newRow.createCell(i);
Cell cellToClone = cellToAdd.getCellToClone();
if (cellToClone != null) {
String contents = ExcelUtil.getCellContentsAsString(cellToClone);
newCell.setCellStyle(cellToClone.getCellStyle());
try {
newCell.setCellFormula(cellToClone.getCellFormula());
} catch (Exception e) {
// Do nothing here. I don't know why POI throw exceptions here when the cell is not a
// formula, but this suppresses them...
}
int numFormattings =
sheetToAdd.getSheet().getSheetConditionalFormatting().getNumConditionalFormattings();
for (int n = 0; n < numFormattings; n++) {
ConditionalFormatting f =
sheetToAdd.getSheet().getSheetConditionalFormatting().getConditionalFormattingAt(n);
for (CellRangeAddress add : f.getFormattingRanges()) {
if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum()
&& add.getLastRow() == rowToClone.getRowNum()) {
if (add.getFirstColumn() == cellToClone.getColumnIndex()
&& add.getLastColumn() == cellToClone.getColumnIndex()) {
ConditionalFormattingRule[] rules =
new ConditionalFormattingRule[f.getNumberOfRules()];
for (int j = 0; j < f.getNumberOfRules(); j++) {
rules[j] = f.getRule(j);
}
CellRangeAddress[] cellRange = new CellRangeAddress[1];
cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i);
sheetToAdd
.getSheet()
.getSheetConditionalFormatting()
.addConditionalFormatting(cellRange, rules);
}
}
}
}
if (ObjectUtil.notNull(contents)) {
Object newContents =
EvaluationUtil.evaluateExpression(
contents, cellToAdd.getReplacementData(), prefix, suffix);
ExcelUtil.setCellContents(styleHelper, newCell, newContents);
}
}
}
return newRow;
}
