List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getActiveSheetIndex
@Override public int getActiveSheetIndex()
From source file:com.carlos.projects.billing.ExcelToMySQLImporter.java
License:Open Source License
public Long importData(MultipartFile excelFile) throws ImportException { XSSFWorkbook workbook; File componentsFile;// w w w. j a v a 2 s. c o m try { componentsFile = new File("components-" + new Date().getTime() + ".xlsx"); excelFile.transferTo(componentsFile); workbook = new XSSFWorkbook(componentsFile.getAbsolutePath()); } catch (IOException e) { throw new ImportException(messages.getProperty("import.error"), e); } workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK); Iterator<Row> rowIterator = workbook.getSheetAt(workbook.getActiveSheetIndex()).iterator(); Long numberOfImportedItems = 0L; log.info("Starting reading from file " + excelFile.getOriginalFilename() + " to import components to database"); while (rowIterator.hasNext()) { Row row = rowIterator.next(); String familyCode = row.getCell(FAMILY_CODE).getStringCellValue().trim(); //The first row of the excel file is the one with the titles if (row.getRowNum() != 0 && StringUtils.isNotBlank(familyCode)) { Family family = familyDAO.getById(Family.class, familyCode); boolean saveFamily = false; if (family == null) { family = createFamilyFromRow(row); saveFamily = true; } String componentCode = row.getCell(COMPONENT_CODE).getStringCellValue().trim(); Component component = componentDAO.getById(Component.class, componentCode); boolean addComponent = false; if (component == null) { addComponent = true; component = createComponent(row, family); numberOfImportedItems += 1L; } if (saveFamily) { if (addComponent) { family.addComponent(component); } familyDAO.save(family); log.info("Family " + family + " saved into the database"); } else { componentDAO.save(component); log.info("Component " + component + " saved into the database"); } } } closeAndDeleteTemporaryFiles(componentsFile); log.info("Components import to database finished"); return numberOfImportedItems; }
From source file:org.dhatim.fastexcel.Correctness.java
License:Apache License
@Test public void singleWorksheet() throws Exception { String sheetName = "Worksheet 1"; String stringValue = "Sample text with chars to escape : < > & \\ \" ' ~ "; Date dateValue = new Date(); LocalDateTime localDateTimeValue = LocalDateTime.now(); ZoneId timezone = ZoneId.of("Australia/Sydney"); ZonedDateTime zonedDateValue = ZonedDateTime.ofInstant(dateValue.toInstant(), timezone); double doubleValue = 1.234; int intValue = 2_016; long longValue = 2_016_000_000_000L; BigDecimal bigDecimalValue = BigDecimal.TEN; byte[] data = writeWorkbook(wb -> { Worksheet ws = wb.newWorksheet(sheetName); int i = 1; ws.value(i, i++, stringValue);/*from www. j av a 2s. c o m*/ ws.value(i, i++, dateValue); ws.value(i, i++, localDateTimeValue); ws.value(i, i++, zonedDateValue); ws.value(i, i++, doubleValue); ws.value(i, i++, intValue); ws.value(i, i++, longValue); ws.value(i, i++, bigDecimalValue); try { ws.finish(); } catch (IOException ex) { throw new RuntimeException(ex); } }); // Check generated workbook with Apache POI XSSFWorkbook xwb = new XSSFWorkbook(new ByteArrayInputStream(data)); assertThat(xwb.getActiveSheetIndex()).isEqualTo(0); assertThat(xwb.getNumberOfSheets()).isEqualTo(1); XSSFSheet xws = xwb.getSheet(sheetName); @SuppressWarnings("unchecked") Comparable<XSSFRow> row = (Comparable) xws.getRow(0); assertThat(row).isNull(); int i = 1; assertThat(xws.getRow(i).getCell(i++).getStringCellValue()).isEqualTo(stringValue); assertThat(xws.getRow(i).getCell(i++).getDateCellValue()).isEqualTo(dateValue); // Check zoned timestamps have the same textual representation as the Dates extracted from the workbook // (Excel date serial numbers do not carry timezone information) assertThat(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(ZonedDateTime .ofInstant(xws.getRow(i).getCell(i++).getDateCellValue().toInstant(), ZoneId.systemDefault()))) .isEqualTo(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(localDateTimeValue)); assertThat(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(ZonedDateTime .ofInstant(xws.getRow(i).getCell(i++).getDateCellValue().toInstant(), ZoneId.systemDefault()))) .isEqualTo(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(zonedDateValue)); assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(doubleValue); assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(intValue); assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(longValue); assertThat(new BigDecimal(xws.getRow(i).getCell(i++).getRawValue())).isEqualTo(bigDecimalValue); }
From source file:org.dhatim.fastexcel.Correctness.java
License:Apache License
@Test public void multipleWorksheets() throws Exception { int numWs = 10; int numRows = 5000; int numCols = 6; byte[] data = writeWorkbook(wb -> { @SuppressWarnings("unchecked") CompletableFuture<Void>[] cfs = new CompletableFuture[numWs]; for (int i = 0; i < cfs.length; ++i) { Worksheet ws = wb.newWorksheet("Sheet " + i); CompletableFuture<Void> cf = CompletableFuture.runAsync(() -> { for (int j = 0; j < numCols; ++j) { ws.value(0, j, "Column " + j); ws.style(0, j).bold().fontSize(12).fillColor(Color.GRAY2).set(); for (int k = 1; k <= numRows; ++k) { switch (j) { case 0: ws.value(k, j, "String value " + k); break; case 1: ws.value(k, j, 2); break; case 2: ws.value(k, j, 3L); break; case 3: ws.value(k, j, 0.123); break; case 4: ws.value(k, j, new Date()); ws.style(k, j).format("yyyy-MM-dd HH:mm:ss").set(); break; case 5: ws.value(k, j, LocalDate.now()); ws.style(k, j).format("yyyy-MM-dd").set(); break; default: throw new IllegalArgumentException(); }/*from w w w . jav a 2s. co m*/ } } ws.formula(numRows + 1, 1, "=SUM(" + ws.range(1, 1, numRows, 1).toString() + ")"); ws.formula(numRows + 1, 2, "=SUM(" + ws.range(1, 2, numRows, 2).toString() + ")"); ws.formula(numRows + 1, 3, "=SUM(" + ws.range(1, 3, numRows, 3).toString() + ")"); ws.formula(numRows + 1, 4, "=AVERAGE(" + ws.range(1, 4, numRows, 4).toString() + ")"); ws.style(numRows + 1, 4).format("yyyy-MM-dd HH:mm:ss").set(); ws.formula(numRows + 1, 5, "=AVERAGE(" + ws.range(1, 5, numRows, 5).toString() + ")"); ws.style(numRows + 1, 5).format("yyyy-MM-dd").bold().italic().fontColor(Color.RED) .fontName("Garamond").fontSize(new BigDecimal("14.5")).horizontalAlignment("center") .verticalAlignment("top").wrapText(true).set(); ws.range(1, 0, numRows, numCols - 1).style().borderColor(Color.RED).borderStyle("thick") .shadeAlternateRows(Color.RED).set(); }); cfs[i] = cf; } try { CompletableFuture.allOf(cfs).get(); } catch (InterruptedException | ExecutionException ex) { throw new RuntimeException(ex); } }); // Check generated workbook with Apache POI XSSFWorkbook xwb = new XSSFWorkbook(new ByteArrayInputStream(data)); assertThat(xwb.getActiveSheetIndex()).isEqualTo(0); assertThat(xwb.getNumberOfSheets()).isEqualTo(numWs); for (int i = 0; i < numWs; ++i) { assertThat(xwb.getSheetName(i)).isEqualTo("Sheet " + i); XSSFSheet xws = xwb.getSheetAt(i); assertThat(xws.getLastRowNum()).isEqualTo(numRows + 1); for (int j = 1; j <= numRows; ++j) { assertThat(xws.getRow(j).getCell(0).getStringCellValue()).isEqualTo("String value " + j); } } }