List of usage examples for org.apache.poi.ss.usermodel Workbook getSheetAt
Sheet getSheetAt(int index);
From source file:cn.mypandora.util.MyExcelUtil.java
License:Apache License
/** * ??ExcelTitle//from w w w .ja v a2 s .com * * @param excelFile * @param sheetName sheet??? * @return */ public static List<String> scanExcelTitles(File excelFile, String... sheetName) { List<String> titles = new ArrayList<>(); try { Workbook workbook = WorkbookFactory.create(new FileInputStream(excelFile)); Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } Row row = sheet.getRow(0); if (row != null) { int i = 0; while (true) { Cell cell = row.getCell(i); if (cell == null) { break; } titles.add(cell.getStringCellValue()); i++; } } } catch (Exception e) { logger.debug("Scan Excel [" + excelFile.getPath() + excelFile.getName() + "] Error"); throw new RuntimeException(e); } return titles; }
From source file:cn.mypandora.util.MyExcelUtil.java
License:Apache License
/** * @param workbook /*from w w w. ja v a2 s. c o m*/ * @param fieldNames ?? * @param sheetName ??? * @return */ private static List<Map<String, String>> execRead(Workbook workbook, String fieldNames, String... sheetName) { String[] strKey = fieldNames.split(","); List<Map<String, String>> listMap = new ArrayList<>(); int i = 1; try { Sheet sheet; if (sheetName.length == 0) { sheet = workbook.getSheetAt(0); } else { sheet = workbook.getSheet(sheetName[0]); } while (true) { Row row = sheet.getRow(i); if (row == null) { break; } Map<String, String> map = new HashMap<String, String>(); map.put("rowid", String.valueOf(row.getRowNum())); for (int keyIndex = 0; keyIndex < strKey.length; keyIndex++) { Cell cell; cell = row.getCell(keyIndex); String cellValue = ""; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: { // ?cell?Date if (DateUtil.isCellDateFormatted(cell)) { // Date?CellDate SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); cellValue = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())); } // else { // ??Cell Integer num = new Integer((int) cell.getNumericCellValue()); cellValue = String.valueOf(num); } break; } case Cell.CELL_TYPE_STRING: cellValue = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_BOOLEAN: System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: cellValue = " "; } } map.put(strKey[keyIndex], cellValue); } listMap.add(map); i++; } } catch (Exception e) { logger.debug("?" + i + "??"); throw new RuntimeException(e); } return listMap; }
From source file:cn.poi.api.example.ExcelExample.java
License:Open Source License
public static void ReadExcel(String excel, String brandcode) throws EncryptedDocumentException, InvalidFormatException, IOException { List<BrandConfigCommand> list = new ArrayList<>(); InputStream inp = resourceLoader.getResource(excel).getInputStream(); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(4); Row tempRow = null;// w w w. j a v a 2 s .c o m for (int i = 1; i < sheet.getLastRowNum(); i++) { BrandConfigCommand brandConfigCommand = new BrandConfigCommand(); String[] array = new String[5]; Row row = sheet.getRow(i); if (StringUtils.isEmpty(row.getCell(4).toString())) { continue; } for (int j = 0; j < row.getLastCellNum(); j++) { if (j == 0 && StringUtils.isEmpty(row.getCell(j).toString())) { System.out.print(tempRow.getCell(j).getStringCellValue() + " "); array[j] = tempRow.getCell(0).getStringCellValue(); continue; } else if (j == 0) { tempRow = row; } if (j == 1 && StringUtils.isEmpty(row.getCell(j).toString())) { System.out.print(tempRow.getCell(j).getStringCellValue() + " "); array[j] = tempRow.getCell(j).getStringCellValue(); continue; } array[j] = row.getCell(j).getStringCellValue(); System.out.print(row.getCell(j).toString() + " "); } brandConfigCommand.setStoreCode(array[0]); brandConfigCommand.setStoreDate(array[1]); brandConfigCommand.setProvice(array[2]); brandConfigCommand.setCity(array[3]); brandConfigCommand.setArea(array[4]); brandConfigCommand.setArea_name(array[2] + array[3] + array[4]); list.add(brandConfigCommand); System.out.println(); } List<String> listStr = new ArrayList<>(); for (BrandConfigCommand brandConfigCommand : list) { String str = sqlStart + brandConfigCommand.getArea_name() + sqlEnd + provice + brandConfigCommand.getProvice() + city + brandConfigCommand.getCity() + area + brandConfigCommand.getArea() + code + brandConfigCommand.getStoreCode() + brand_code + brandcode + "'"; listStr.add(str); } System.out.println(JSON.toJSONString(listStr)); }
From source file:cn.study.innerclass.Test.java
License:Open Source License
public static void main(String[] args) throws EncryptedDocumentException, InvalidFormatException, IOException { /*Exsample ex = new Exsample(); Exsample.PublicInnerClass pub = ex.new PublicInnerClass(); pub.setPuAge(111);// w w w . ja v a2 s. co m pub.test();*/ /*Workbook book = new HSSFWorkbook(); Cell cell = book.createSheet().createRow(1).createCell(1); cell.setCellValue(5l); System.out.println(cell.getCellType());*/ FileInputStream fin = new FileInputStream("E:/test.xlsx"); Workbook book = WorkbookFactory.create(fin); Sheet sheet = book.getSheetAt(0); ; PoiUtil.getCellData(sheet.getRow(0).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(0).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(0).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(1).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(1).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(1).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(2).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(2).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(2).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(3).getCell(0), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(3).getCell(1), new XSSFFormulaEvaluator((XSSFWorkbook) book)); PoiUtil.getCellData(sheet.getRow(3).getCell(2), new XSSFFormulaEvaluator((XSSFWorkbook) book)); }
From source file:CODIGOS.EditaPlanilha.java
public static void edita(String diretorio, String arquivo, int linha, int coluna, String texto) { try {//from ww w .j a v a 2 s . co m File dir = new File(diretorio); File file = new File(dir, arquivo + ".xlsx"); InputStream inp = new FileInputStream(file); try { Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(linha);//LINHA Cell cell = row.getCell(0); cell = row.createCell(coluna);//COLUNA cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(texto); try (FileOutputStream fileOut = new FileOutputStream(file)) { wb.write(fileOut); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(EditaPlanilha.class.getName()).log(Level.SEVERE, null, ex); } } catch (FileNotFoundException ex) { Logger.getLogger(EditaPlanilha.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.abixen.platform.service.businessintelligence.multivisualisation.application.service.file.reader.ExcelReaderService.java
License:Open Source License
public DataFileDto read(final MultipartFile multipartFile, final Boolean readFirstColumnAsColumnName, final FileParserMessage msg) { try {//from w ww. j a v a 2s . com final Workbook workbook = openFileAsWorkBook(multipartFile, msg); return parseWorkbook(workbook.getSheetAt(FIRST_SHEET_INDEX), msg, readFirstColumnAsColumnName); } catch (IOException e) { msg.addFileParseError(new FileParseError("Can't read file.")); } return null; }
From source file:com.abixen.platform.service.businessintelligence.multivisualisation.service.impl.parser.ExcelParserServiceImpl.java
License:Open Source License
@Override public FileParserMessage<DataFileColumn> parseFile(MultipartFile multipartFile, Boolean readFirstColumnAsColumnName) { FileParserMessage<DataFileColumn> msg = new FileParserMessage<>(); try {/*from w ww .j av a 2 s .com*/ Workbook workbook = openFileAsWorkBook(multipartFile, msg); parseWorkbook(workbook.getSheetAt(firstSheetIndex), msg, readFirstColumnAsColumnName); } catch (IOException e) { msg.addFileParseError(new FileParseError("Can't read file.")); } return msg; }
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); 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); }/*from w w w . j ava2s. c om*/ } } } } 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.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServletTest.java
License:Apache License
/** * Generate a worksheet, just confirming that there are no blatant runtime errors and the data was recorded in the output *//*from ww w .j a v a 2s . co m*/ @Test public void createSpreadsheet() throws Exception { addFailure("path1", "error1", "stacktrace1"); addFailure("path2", "error2", "stacktrace2"); addFailure("path3", "error3", "stacktrace3"); addFailure("path4", "error4", "stacktrace4"); Workbook wb = servlet.createSpreadsheet(process); assertNotNull("Created workbook", wb); assertEquals("Created one sheet", 1, wb.getNumberOfSheets()); assertEquals("Created correct number of rows", 5, wb.getSheetAt(0).getPhysicalNumberOfRows()); }
From source file:com.adobe.acs.commons.mcp.impl.TestGenericReportExcelServlet.java
License:Apache License
@Test public void testReport() throws Exception { int numRows = 10; String reportPath = "/var/acs-commons/mcp/instances/junit/jcr:content/report"; ResourceBuilder rb = slingContext.build() .resource(reportPath, "columns", new String[] { "ColumnA", "ColumnB" }, "name", "report", "sling:resourceType", "acs-commons/components/utilities/process-instance/process-generic-report") .resource("rows"); rb.siblingsMode();/*from w ww. j a v a 2 s . c o m*/ for (int i = 1; i <= numRows; i++) { rb.resource("row-" + i, "ColumnA", "abcdef-" + i, "ColumnB", "qwerty-" + i); } MockSlingHttpServletRequest request = slingContext.request(); request.setResource(slingContext.resourceResolver().getResource(reportPath)); MockSlingHttpServletResponse response = slingContext.response(); slingContext.addModelsForClasses(GenericReport.class); GenericReportExcelServlet servlet = new GenericReportExcelServlet(); servlet.doGet(request, response); assertEquals("application/vnd.ms-excel", response.getContentType()); Workbook wb = WorkbookFactory.create(new ByteArrayInputStream(response.getOutput())); Sheet sh = wb.getSheetAt(0); assertEquals(numRows, sh.getLastRowNum()); Row header = sh.getRow(0); assertEquals("Column A", header.getCell(0).getStringCellValue()); assertEquals("Column B", header.getCell(1).getStringCellValue()); for (int i = 1; i <= numRows; i++) { Row row = sh.getRow(i); assertEquals("abcdef-" + i, row.getCell(0).getStringCellValue()); assertEquals("qwerty-" + i, row.getCell(1).getStringCellValue()); } }