List of usage examples for org.apache.poi.ss.usermodel Row getFirstCellNum
short getFirstCellNum();
From source file:cn.bzvs.excel.export.template.ExcelExportOfTemplateUtil.java
License:Apache License
/** * ,??//from w w w .j av a 2 s. c o m * @param sheet * @param map * @throws Exception */ private void deleteCell(Sheet sheet, Map<String, Object> map) throws Exception { Row row = null; Cell cell = null; int index = 0; while (index <= sheet.getLastRowNum()) { row = sheet.getRow(index++); if (row == null) { continue; } for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { cell = row.getCell(i); if (row.getCell(i) != null && (cell.getCellType() == Cell.CELL_TYPE_STRING || cell.getCellType() == Cell.CELL_TYPE_NUMERIC)) { cell.setCellType(Cell.CELL_TYPE_STRING); String text = cell.getStringCellValue(); if (text.contains(IF_DELETE)) { if (Boolean.valueOf( eval(text.substring(text.indexOf(START_STR) + 2, text.indexOf(END_STR)).trim(), map) .toString())) { PoiSheetUtility.deleteColumn(sheet, i); } cell.setCellValue(""); } } } } }
From source file:cn.bzvs.excel.imports.ExcelImportServer.java
License:Apache License
/*** * ?List?/*from w w w . j a va2s . c o m*/ * * @param object * @param param * @param row * @param titlemap * @param targetId * @param pictures * @param params */ private void addListContinue(Object object, ExcelCollectionParams param, Row row, Map<Integer, String> titlemap, String targetId, Map<String, PictureData> pictures, ImportParams params) throws Exception { Collection collection = (Collection) PoiReflectorUtil.fromCache(object.getClass()).getValue(object, param.getName()); Object entity = PoiPublicUtil.createObject(param.getType(), targetId); String picId; boolean isUsed = false;// ?? for (int i = row.getFirstCellNum(); i < param.getExcelParams().size(); i++) { Cell cell = row.getCell(i); String titleString = (String) titlemap.get(i); if (param.getExcelParams().containsKey(titleString)) { if (param.getExcelParams().get(titleString).getType() == 2) { picId = row.getRowNum() + "_" + i; saveImage(object, picId, param.getExcelParams(), titleString, pictures, params); } else { saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row); } isUsed = true; } } if (isUsed) { collection.add(entity); } }
From source file:cn.bzvs.excel.imports.ExcelImportServer.java
License:Apache License
private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass, ImportParams params, Map<String, PictureData> pictures) throws Exception { List collection = new ArrayList(); Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>(); List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>(); String targetId = null;/*from ww w . j av a2s . c o m*/ if (!Map.class.equals(pojoClass)) { Field fileds[] = PoiPublicUtil.getClassFields(pojoClass); ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class); if (etarget != null) { targetId = etarget.value(); } getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null); } Iterator<Row> rows = sheet.rowIterator(); for (int j = 0; j < params.getTitleRows(); j++) { rows.next(); } Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection); checkIsValidTemplate(titlemap, excelParams, params, excelCollection); Row row = null; Object object = null; String picId; while (rows.hasNext() && (row == null || sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) { row = rows.next(); // ???,?,? // keyIndex ??,?? if (params.getKeyIndex() != null && (row.getCell(params.getKeyIndex()) == null || StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex())))) && object != null) { for (ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params); } } else { object = PoiPublicUtil.createObject(pojoClass, targetId); try { for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) { Cell cell = row.getCell(i); String titleString = (String) titlemap.get(i); if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) { if (excelParams.get(titleString) != null && excelParams.get(titleString).getType() == 2) { picId = row.getRowNum() + "_" + i; saveImage(object, picId, excelParams, titleString, pictures, params); } else { saveFieldValue(params, object, cell, excelParams, titleString, row); } } } for (ExcelCollectionParams param : excelCollection) { addListContinue(object, param, row, titlemap, targetId, pictures, params); } if (verifyingDataValidity(object, row, params, pojoClass)) { collection.add(object); } } catch (ExcelImportException e) { if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) { throw new ExcelImportException(e.getType(), e); } } } } return collection; }
From source file:cn.edu.zucc.chenxg.preview.ToHtml.java
License:Apache License
private void ensureColumnBounds(Sheet sheet) { if (gotBounds) return;// w w w. j a v a2s . c om Iterator<Row> iter = sheet.rowIterator(); firstColumn = (iter.hasNext() ? Integer.MAX_VALUE : 0); endColumn = 0; while (iter.hasNext()) { Row row = iter.next(); short firstCell = row.getFirstCellNum(); if (firstCell >= 0) { firstColumn = Math.min(firstColumn, firstCell); endColumn = Math.max(endColumn, row.getLastCellNum()); } } gotBounds = true; }
From source file:cn.edu.zucc.chenxg.preview.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { printColumnHeads();//from w w w .ja va2 s. c om out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null; if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content.equals("")) content = " "; } } out.format(" <td class=%s %s>%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:co.foldingmap.data.ExcelDataConnector.java
License:Open Source License
/** * Parses the cells from the workbook into a TabulaData class. * //from ww w . ja v a 2 s .c o m * @param workingSheet * @return */ private TabularData parseWorkbook() { ArrayList<ArrayList<DataCell>> rows; ArrayList<DataCell> cells; int columnIndex, currentCellColumnIndex, lastCellcolumnIndex; int numberOfCells, previousRowLength, rowIndex; TabularData dataFile; dataFile = new TabularData(); previousRowLength = 0; rows = new ArrayList<ArrayList<DataCell>>(); rowIndex = -1; try { for (Row row : workingSheet) { cells = new ArrayList<DataCell>(); columnIndex = row.getFirstCellNum(); lastCellcolumnIndex = -1; numberOfCells = row.getPhysicalNumberOfCells(); rowIndex++; //add blank cells for (int i = 0; i < columnIndex; i++) cells.add(new DataCell("")); for (Cell cell : row) { currentCellColumnIndex = cell.getColumnIndex(); if ((lastCellcolumnIndex + 1) == currentCellColumnIndex) { cells.add(getCellText(cell)); lastCellcolumnIndex = currentCellColumnIndex; } else { for (int i = (lastCellcolumnIndex + 1); i < currentCellColumnIndex; i++) { cells.add(new DataCell("")); } cells.add(getCellText(cell)); lastCellcolumnIndex = currentCellColumnIndex; } } //if this row does not match the length of the last one, add blank cells for (int i = (lastCellcolumnIndex + 1); i <= previousRowLength; i++) { cells.add(new DataCell("")); lastCellcolumnIndex = i; } previousRowLength = lastCellcolumnIndex; rows.add(cells); } } catch (Exception e) { Logger.log(Logger.ERR, "Error in ExcelDataConnector.parseWorkbook(Sheet) - " + e); } dataFile.loadData(rows); return dataFile; }
From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java
License:Open Source License
public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception { assert wb != null; assert reportFile != null; //Precompute formula FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i);//from ww w.j a va 2 s . co m for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { try { evaluator.evaluateFormulaCell(c); } catch (Exception e) { System.err.println(e); } } } } } File tmp = File.createTempFile("tmp_", ".xlsx"); try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) { wb.write(out); } //Find page orientation int maxColumnsGlobal = 0; for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) { Sheet sheet = wb.getSheetAt(sheetNo); for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) { Row row = rowIterator.next(); maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum()); } } Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate(); Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f); PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile)); addHeader(writer, header); pdfDocument.open(); //we have two columns in the Excel sheet, so we create a PDF table with two columns //Note: There are ways to make this dynamic in nature, if you want to. //Loop through sheets for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) { Sheet sheet = wb.getSheetAt(sheetNo); //Loop through rows, to find number of columns int minColumns = 1000; int maxColumns = 0; for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) { Row row = rowIterator.next(); if (row.getFirstCellNum() >= 0) minColumns = Math.min(minColumns, row.getFirstCellNum()); if (row.getLastCellNum() >= 0) maxColumns = Math.max(maxColumns, row.getLastCellNum()); } if (maxColumns == 0) continue; //Loop through first rows, to find relative width float[] widths = new float[maxColumns]; int totalWidth = 0; for (int c = 0; c < maxColumns; c++) { int w = sheet.getColumnWidth(c); widths[c] = w; totalWidth += w; } for (int c = 0; c < maxColumns; c++) { widths[c] /= totalWidth; } //Create new page and a new chapter with the sheet's name if (sheetNo > 0) pdfDocument.newPage(); Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1); PdfPTable pdfTable = null; PdfPCell pdfCell = null; boolean inTable = false; //Loop through cells, to create the content // boolean leftBorder = true; // boolean[] topBorder = new boolean[maxColumns+1]; for (int r = 0; r <= sheet.getLastRowNum(); r++) { Row row = sheet.getRow(r); //Check if we exited a table (empty line) if (row == null) { if (pdfTable != null) { addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable); pdfTable = null; } inTable = false; continue; } //Check if we start a table (>MIN_COL_IN_TABLE columns) if (row.getLastCellNum() >= MIN_COL_IN_TABLE) { inTable = true; } if (!inTable) { //Process the data outside table, just add the text boolean hasData = false; Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_BLANK) continue; Chunk chunk = getChunk(wb, cell); pdfSheet.add(chunk); pdfSheet.add(new Chunk(" ")); hasData = true; } if (hasData) pdfSheet.add(Chunk.NEWLINE); } else { //Process the data in table if (pdfTable == null) { //Create table pdfTable = new PdfPTable(maxColumns); pdfTable.setWidths(widths); // topBorder = new boolean[maxColumns+1]; } int cellNumber = minColumns; // leftBorder = false; Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); for (; cellNumber < cell.getColumnIndex(); cellNumber++) { pdfCell = new PdfPCell(); pdfCell.setBorder(0); pdfTable.addCell(pdfCell); } Chunk phrase = getChunk(wb, cell); pdfCell = new PdfPCell(new Phrase(phrase)); pdfCell.setFixedHeight(row.getHeightInPoints() - 3); pdfCell.setNoWrap(!cell.getCellStyle().getWrapText()); pdfCell.setPaddingLeft(1); pdfCell.setHorizontalAlignment( cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER : cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT ? PdfPCell.ALIGN_RIGHT : PdfPCell.ALIGN_LEFT); pdfCell.setUseBorderPadding(false); pdfCell.setUseVariableBorders(false); pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f); pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0 : cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f); pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0 : cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f); pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0 : cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f); String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null : ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex(); if (color != null) pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2)))); pdfTable.addCell(pdfCell); cellNumber++; } for (; cellNumber < maxColumns; cellNumber++) { pdfCell = new PdfPCell(); pdfCell.setBorder(0); pdfTable.addCell(pdfCell); } } //Custom code to add all images on the first sheet (works for reporting) if (sheetNo == 0 && row.getRowNum() == 0) { for (PictureData pd : wb.getAllPictures()) { try { Image pdfImg = Image.getInstance(pd.getData()); pdfImg.scaleToFit( pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft() - pageSize.getBorderWidthRight(), pageSize.getHeight() * .8f - pageSize.getBorderWidthTop() - pageSize.getBorderWidthBottom()); pdfSheet.add(pdfImg); } catch (Exception e) { e.printStackTrace(); } } } } if (pdfTable != null) { addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable); } pdfDocument.add(pdfSheet); } pdfDocument.close(); }
From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java
License:Open Source License
public static void autoSizeColumns(Sheet sheet, int maxColWidth, boolean resizeHeight) { ListHashMap<Integer, Integer> col2lens = new ListHashMap<>(); for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum(); row++) { Row r = sheet.getRow(row); if (r == null || r.getFirstCellNum() < 0) continue; short maxH = 0; for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) { Cell c = r.getCell(col);//from w w w .ja v a 2s. c om if (c == null || (c.getCellType() != Cell.CELL_TYPE_STRING && c.getCellType() != Cell.CELL_TYPE_NUMERIC)) continue; Font font = sheet.getWorkbook().getFontAt(c.getCellStyle().getFontIndex()); String s = c.getCellType() == Cell.CELL_TYPE_STRING ? c.getStringCellValue() : "" + c.getNumericCellValue(); String[] lines = MiscUtils.split(s, "\n"); int maxLen = 1; for (int i = 0; i < lines.length; i++) { maxLen = Math.max(lines[i].length(), maxLen); } if (font.getFontHeightInPoints() < 12) { col2lens.add(col, 700 + maxLen * (font.getFontHeightInPoints() + (font.getBoldweight() > 500 ? 1 : 0)) * 20); } maxH = (short) Math.max(maxH, 50 + lines.length * (font.getFontHeight() * 1.2)); } if (resizeHeight) r.setHeight(maxH); } for (int col : col2lens.keySet()) { List<Integer> lens = col2lens.get(col); Collections.sort(lens); int len = lens.get(lens.size() - 1); if (lens.size() > 10 && lens.get(lens.size() - 1) > 2 * lens.get(lens.size() - 2)) { len = lens.get(lens.size() - 2); } sheet.setColumnWidth(col, Math.max(Math.min((int) (len * 1.25), maxColWidth > 0 ? maxColWidth : 300000), 1500)); } }
From source file:com.avaya.plds.excel.ExcelRead.java
public List<String> getPoeticFeatureLoad(String value1, String value2, int sheetNo, int headers) { System.out.println(" Inside of getPoeticFeatureLoad method ..."); sheet = xssfWorkbook.getSheetAt(sheetNo); boolean read = false; List<String> dataList = new ArrayList<String>(); rowIterator = sheet.iterator();// ww w . j a v a 2 s . c o m while (rowIterator.hasNext()) { StringBuilder builder = new StringBuilder(); Row row = rowIterator.next(); int rowNumber = row.getRowNum(); if (row != null) { for (short i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { if (row.getCell(i) != null && (row.getCell(i).getCellType() == row.getCell(i).CELL_TYPE_STRING) && row.getCell(i).getStringCellValue().contains(value1) && i == 0) { read = true; break; //builder.append(value1).append("\t"); } else if (row.getCell(i) != null && (row.getCell(i).getCellType() == row.getCell(i).CELL_TYPE_STRING) && row.getCell(i).getStringCellValue().contains(value2)) { read = false; } else if (read) { // System.out.println("rowNumber "+ rowNumber); maxCellIndex = (row.getLastCellNum() > maxCellIndex && rowNumber > 0) ? row.getLastCellNum() : maxCellIndex; // System.out.println("maxCellIndex "+ maxCellIndex); Cell cell = row.getCell(i); if (cell != null) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { //if(i >0) builder.append( cell != null ? Double.valueOf(cell.getNumericCellValue()).longValue() : "") .append("\t"); } else { //if(i >0 ) builder.append(cell != null ? cell.getStringCellValue() : "").append("\t"); } } else { //if(i >0) builder.append("").append("\t"); } } if (headers == rowNumber) { //if(i>0) builder.append(row.getCell(i).getStringCellValue()).append("\t"); } } if (!builder.toString().equals("") && !builder.toString().matches("^ null.*")) dataList.add(builder.toString().replaceFirst(",", "")); } } return dataList; }
From source file:com.b2international.snowowl.snomed.core.refset.automap.XlsParser.java
License:Apache License
private void parse(Sheet sheet) throws SnowowlServiceException { int firstRowIndex = findFirstRow(sheet); if (firstRowIndex == -1) { return;/*from ww w.jav a 2s.com*/ } if (hasHeader) { header = collectRowValues(sheet.getRow(firstRowIndex)); firstRowIndex++; } else { final Row firstRow = sheet.getRow(firstRowIndex); Cell first = firstRow.getCell(firstRow.getFirstCellNum()); Cell second = firstRow.getCell(firstRow.getFirstCellNum() + 1); if (isNumeric(first) || isNumeric(second)) { header.add("ID"); } if (isString(first) || isString(second)) { header.add("Label"); } } for (int i = firstRowIndex; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); // totally empty row w/o any value if (row == null) { if (!skipEmptyRows) { content.add(Collections.<String>emptyList()); } continue; } if (row.getLastCellNum() > maxWidth) { maxWidth = row.getLastCellNum(); } List<String> rowValues = collectRowValues(row); if (rowValues.isEmpty()) { if (!skipEmptyRows) { content.add(Collections.<String>emptyList()); } continue; } content.add(rowValues); } }