List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getColumnIndex
@Override
public int getColumnIndex()
From source file:blueprint.sdk.experimental.util.XlsReader.java
License:Open Source License
/** * create error message for cell//from w w w .j a va 2s. c o m * * @param cell target cell * @return error message */ protected String createTypeErrMsg(final HSSFCell cell) { return createTypeErrMsg(wbook.getSheetIndex(cell.getSheet()), cell.getRowIndex(), cell.getColumnIndex()); }
From source file:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
private Boolean isCellInConditionalFormat(HSSFSheetConditionalFormatting cfms, HSSFCell cell) { if ((cell != null) && (cfms != null)) { int rowIdx = cell.getRowIndex(); int columnIdx = cell.getColumnIndex(); if (cfms.getNumConditionalFormattings() > 0) { int i = 0; for (int len = cfms.getNumConditionalFormattings(); i < len; ++i) { if (cfms.getConditionalFormattingAt(i).getNumberOfRules() <= 0) { continue; }/*from www . ja va 2 s . co m*/ for (int k = 0; k < cfms.getConditionalFormattingAt(i).getFormattingRanges().length; ++k) { if (!(cfms.getConditionalFormattingAt(i).getFormattingRanges()[k].isInRange(rowIdx, columnIdx))) { continue; } for (int j = 0, size = cfms.getConditionalFormattingAt(i) .getNumberOfRules(); j < size; ++j) { HSSFConditionalFormattingRule rule = cfms.getConditionalFormattingAt(i).getRule(j); handleContionalFormatCell(rule, cell); } return Boolean.valueOf(true); } } } } return Boolean.valueOf(false); }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ???????Exception??//from w ww . j av a2 s. c om * @param cell ? * @param msg * @return Exception? */ private String createExceptionMsg(HSSFCell cell, String msg) { int col = cell.getColumnIndex(); int rownum = cell.getRowIndex(); String sheetName = cell.getSheet().getSheetName(); String fmt = "%s, filename = %s, sheet = %s, row = %d, col = %d"; String ret = String.format(fmt, msg, filename, sheetName, rownum + 1, col + 1); return ret; }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ?????????Exception???// ww w . j a v a 2 s. c o m * @param cell * @param expect ?? * @return Exception? */ private String createCellTypeMismatchExceptionMsg(HSSFCell cell, String expect) { int col = cell.getColumnIndex(); int rownum = cell.getRowIndex(); String sheetName = cell.getSheet().getSheetName(); String actual; switch (cell.getCellType()) { case Cell.CELL_TYPE_BLANK: actual = ""; break; case Cell.CELL_TYPE_BOOLEAN: actual = "?"; break; case Cell.CELL_TYPE_ERROR: actual = ""; break; case Cell.CELL_TYPE_FORMULA: actual = "?"; break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { actual = ""; } else { actual = ""; } break; case Cell.CELL_TYPE_STRING: actual = ""; break; default: actual = "?"; break; } String fmt = "Excel CELL??????????" + "expect = %s, actual = %s, filename = %s, sheet = %s, row = %d, col = %d"; String ret = String.format(fmt, expect, actual, filename, sheetName, rownum + 1, col + 1); return ret; }
From source file:com.b510.excel.client.HSSFReadWrite.java
License:Apache License
/** * Method main/* w w w. java2s .co m*/ * * Given 1 argument takes that as the filename, inputs it and dumps the cell * values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet see * {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second an * output filename (not write), attempts to fully read in the spreadsheet * and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read * in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col * 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1". * If you take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { String[] name = new String[2]; name[0] = "HSSFReadWrite.xlsx"; name[1] = "write"; if (name.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = name[0]; try { if (name.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } else if (name.length == 2) { if (name[1].toLowerCase().equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(name[1]); wb.write(stream); stream.close(); } } else if (name.length == 3 && name[2].toLowerCase().equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to // string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(name[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.cms.utils.ExcelReader.java
public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>(); 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); }//from w ww .j ava 2 s .c o m 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()); if (isNewMergedRegion(newMergedRegion, mergedRegions)) { mergedRegions.add(newMergedRegion); destSheet.addMergedRegion(newMergedRegion); } } } } }
From source file:com.elbeesee.poink.transreptor.HSSFCellToXML.java
License:Open Source License
public void onTransrept(INKFRequestContext aContext) throws Exception { IHSSFCellRepresentation aIHSSFCellRepresentation = (IHSSFCellRepresentation) aContext .sourcePrimary(IHSSFCellRepresentation.class); HSSFCell vCell = aIHSSFCellRepresentation.getCellReadOnly(); String vSheetName = vCell.getSheet().getSheetName(); StringBuilder vCellXML = new StringBuilder(); vCellXML.append("<cell columnIndex=\""); vCellXML.append(vCell.getColumnIndex()); vCellXML.append("\" rowIndex=\""); vCellXML.append(vCell.getRow().getRowNum()); vCellXML.append("\" sheetIndex=\""); vCellXML.append(vCell.getSheet().getWorkbook().getSheetIndex(vSheetName)); vCellXML.append("\">"); int vCellType = vCell.getCellType(); if (vCellType == Cell.CELL_TYPE_FORMULA) { vCellType = vCell.getCachedFormulaResultType(); }/*from w w w. ja va2s . c o m*/ if (vCellType == Cell.CELL_TYPE_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(vCell)) { vCellXML.append(vCell.getDateCellValue()); } else { vCellXML.append(vCell.getNumericCellValue()); } } else if (vCellType == Cell.CELL_TYPE_STRING) { vCellXML.append(XMLUtils.escape(vCell.getStringCellValue())); } else if (vCellType == Cell.CELL_TYPE_BOOLEAN) { vCellXML.append(vCell.getBooleanCellValue()); } else if (vCellType == Cell.CELL_TYPE_BLANK) { } else if (vCellType == Cell.CELL_TYPE_ERROR) { vCellXML.append(vCell.getErrorCellValue()); } // vCellXML.append("</cell>"); INKFResponse vResponse = aContext.createResponseFrom(vCellXML.toString()); vResponse.setExpiry(INKFResponse.EXPIRY_DEPENDENT); }
From source file:com.haulmont.yarg.formatters.impl.inline.AbstractInliner.java
License:Apache License
@Override public void inlineToXls(HSSFPatriarch patriarch, HSSFCell resultCell, Object paramValue, Matcher paramsMatcher) {//from w w w .j a v a 2 s.c o m try { Image image = new Image(paramValue, paramsMatcher); if (image.isValid()) { HSSFSheet sheet = resultCell.getSheet(); HSSFWorkbook workbook = sheet.getWorkbook(); int pictureIdx = workbook.addPicture(image.imageContent, Workbook.PICTURE_TYPE_JPEG); CreationHelper helper = workbook.getCreationHelper(); ClientAnchor anchor = helper.createClientAnchor(); anchor.setCol1(resultCell.getColumnIndex()); anchor.setRow1(resultCell.getRowIndex()); anchor.setCol2(resultCell.getColumnIndex()); anchor.setRow2(resultCell.getRowIndex()); if (patriarch == null) { throw new IllegalArgumentException(String.format( "No HSSFPatriarch object provided. Charts on this sheet could cause this effect. Please check sheet %s", resultCell.getSheet().getSheetName())); } HSSFPicture picture = patriarch.createPicture(anchor, pictureIdx); Dimension size = ImageUtils.getDimensionFromAnchor(picture); double actualHeight = size.getHeight() / EMU_PER_PIXEL; double actualWidth = size.getWidth() / EMU_PER_PIXEL; picture.resize((double) image.width / actualWidth, (double) image.height / actualHeight); } } catch (IllegalArgumentException e) { throw new ReportFormattingException("An error occurred while inserting bitmap to xls file", e); } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
@Override public void apply() { for (DataObject dataObject : data) { HSSFCell templateCell = dataObject.templateCell; HSSFCell resultCell = dataObject.resultCell; BandData bandData = dataObject.bandData; HSSFWorkbook resultWorkbook = resultCell.getSheet().getWorkbook(); HSSFWorkbook templateWorkbook = templateCell.getSheet().getWorkbook(); String templateCellValue = templateCell.getStringCellValue(); Matcher matcher = pattern.matcher(templateCellValue); if (matcher.find()) { String paramName = matcher.group(1); String styleName = (String) bandData.getParameterValue(paramName); if (styleName == null) continue; HSSFCellStyle cellStyle = styleCache.getStyleByName(styleName); if (cellStyle == null) continue; HSSFCellStyle resultStyle = styleCache.getNamedCachedStyle(cellStyle); if (resultStyle == null) { HSSFCellStyle newStyle = resultWorkbook.createCellStyle(); // color newStyle.setFillBackgroundColor(cellStyle.getFillBackgroundColor()); newStyle.setFillForegroundColor(cellStyle.getFillForegroundColor()); newStyle.setFillPattern(cellStyle.getFillPattern()); // borders newStyle.setBorderLeft(cellStyle.getBorderLeft()); newStyle.setBorderRight(cellStyle.getBorderRight()); newStyle.setBorderTop(cellStyle.getBorderTop()); newStyle.setBorderBottom(cellStyle.getBorderBottom()); // border colors newStyle.setLeftBorderColor(cellStyle.getLeftBorderColor()); newStyle.setRightBorderColor(cellStyle.getRightBorderColor()); newStyle.setBottomBorderColor(cellStyle.getBottomBorderColor()); newStyle.setTopBorderColor(cellStyle.getTopBorderColor()); // alignment newStyle.setAlignment(cellStyle.getAlignment()); newStyle.setVerticalAlignment(cellStyle.getVerticalAlignment()); // misc DataFormat dataFormat = resultWorkbook.getCreationHelper().createDataFormat(); newStyle.setDataFormat(dataFormat.getFormat(cellStyle.getDataFormatString())); newStyle.setHidden(cellStyle.getHidden()); newStyle.setLocked(cellStyle.getLocked()); newStyle.setIndention(cellStyle.getIndention()); newStyle.setRotation(cellStyle.getRotation()); newStyle.setWrapText(cellStyle.getWrapText()); // font HSSFFont cellFont = cellStyle.getFont(templateWorkbook); HSSFFont newFont = fontCache.getFontByTemplate(cellFont); if (newFont == null) { newFont = resultWorkbook.createFont(); newFont.setFontName(cellFont.getFontName()); newFont.setItalic(cellFont.getItalic()); newFont.setStrikeout(cellFont.getStrikeout()); newFont.setTypeOffset(cellFont.getTypeOffset()); newFont.setBoldweight(cellFont.getBoldweight()); newFont.setCharSet(cellFont.getCharSet()); newFont.setColor(cellFont.getColor()); newFont.setUnderline(cellFont.getUnderline()); newFont.setFontHeight(cellFont.getFontHeight()); newFont.setFontHeightInPoints(cellFont.getFontHeightInPoints()); fontCache.addCachedFont(cellFont, newFont); }// w w w. ja v a 2 s .co m newStyle.setFont(newFont); resultStyle = newStyle; styleCache.addCachedNamedStyle(cellStyle, resultStyle); } fixNeighbourCellBorders(cellStyle, resultCell); resultCell.setCellStyle(resultStyle); Sheet sheet = resultCell.getSheet(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.isInRange(resultCell.getRowIndex(), resultCell.getColumnIndex())) { int firstRow = mergedRegion.getFirstRow(); int lastRow = mergedRegion.getLastRow(); int firstCol = mergedRegion.getFirstColumn(); int lastCol = mergedRegion.getLastColumn(); for (int row = firstRow; row <= lastRow; row++) for (int col = firstCol; col <= lastCol; col++) sheet.getRow(row).getCell(col).setCellStyle(resultStyle); // cell includes only in one merged region break; } } } } }
From source file:com.haulmont.yarg.formatters.impl.xls.hints.CustomCellStyleHint.java
License:Apache License
private void fixNeighbourCellBorders(HSSFCellStyle cellStyle, HSSFCell resultCell) { HSSFSheet sheet = resultCell.getRow().getSheet(); // disable neighboring cells border int columnIndex = resultCell.getColumnIndex(); int rowIndex = resultCell.getRowIndex(); // fix left border fixLeftBorder(cellStyle, sheet, columnIndex, resultCell); // fix right border fixRightBorder(cellStyle, sheet, columnIndex, resultCell); // fix up border fixUpBorder(cellStyle, sheet, columnIndex, rowIndex, resultCell); // fix down border fixDownBorder(cellStyle, sheet, columnIndex, rowIndex, resultCell); }