List of usage examples for org.apache.poi.ss.usermodel Workbook close
@Override void close() throws IOException;
From source file:javaapp.ParseCreditorTransactionsDataEE.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "EE/GBRMECOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; String filter = ""; while (iterator.hasNext()) { rec = ""; pay = ""; Row nextRow = iterator.next();/*from ww w . j a v a 2 s.c om*/ Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } if (cell.getColumnIndex() == 23) { filter = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if ((rec.length() == 5 || rec.length() == 8) && (pay.length() == 5 || pay.length() == 8)) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.print(rpps); //System.out.println("--->"+tbl); if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) { if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled") || filter.equalsIgnoreCase("")) { continue; } } // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){ //System.out.println(rpps+"|"+dval+"|"+cval); //} //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.ParseDebtorData.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; while (iterator.hasNext()) { Row nextRow = iterator.next();//from w w w . j a v a2 s . c o m Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.print(rpps+"|"+dval+"|"+cval); //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.ParseDebtorTransactionsData.java
public static ArrayList<String> parseReport(String name, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "S9/GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheet(name); //String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Name : " + name + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; String filter = ""; while (iterator.hasNext()) { Row nextRow = iterator.next();/*from w w w .ja v a2 s. c om*/ Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == 23 || cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } if (cell.getColumnIndex() == 23) { filter = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; if (tbl.equalsIgnoreCase("open") || tbl.equalsIgnoreCase("close")) { if (filter.equalsIgnoreCase("Missing Invoice") || filter.equalsIgnoreCase("Unreconciled") || filter.equalsIgnoreCase("")) { continue; } } // if(name.equalsIgnoreCase("Settled Transactions-Funds-Paid")){ //System.out.println(rpps+"|"+dval+"|"+cval); //} //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:javaapp.SimpleExcelReaderExample.java
public static ArrayList<String> parseReport(int sno, int c1, int c2, int c3, int c4, int c5, int c6, String tbl) throws IOException { String excelFilePath = "GBRCNCOR.xlsx"; FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); ArrayList<String> ar = new ArrayList<String>(); String sqlstr = ""; Workbook workbook = new XSSFWorkbook(inputStream); //Sheet uninv_open = workbook.getSheetAt(sno); Sheet uninv_open = workbook.getSheetAt(sno); String sname = workbook.getSheetName(sno); System.out.println("Parsing Sheet Number ---> " + sno + "---> Name : " + sname + " ---> " + tbl); Iterator<Row> iterator = uninv_open.iterator(); String rec = ""; String pay = ""; String per = ""; String svc = ""; double dval = 0; double cval = 0; String rpps = ""; while (iterator.hasNext()) { Row nextRow = iterator.next();//from ww w . java 2s .c o m Iterator<Cell> cellIterator = nextRow.cellIterator(); dval = 0; cval = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getColumnIndex() == c1 || cell.getColumnIndex() == c2 || cell.getColumnIndex() == c3 || cell.getColumnIndex() == c4 || cell.getColumnIndex() == c5 || cell.getColumnIndex() == c6) { switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: if (cell.getColumnIndex() == c1) { rec = cell.getStringCellValue(); } if (cell.getColumnIndex() == c2) { pay = cell.getStringCellValue(); } if (cell.getColumnIndex() == c3) { svc = cell.getStringCellValue(); } if (cell.getColumnIndex() == c4) { per = cell.getStringCellValue(); } break; case Cell.CELL_TYPE_BOOLEAN: //System.out.print(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.print(cell.getNumericCellValue()); if (cell.getColumnIndex() == c5) { dval = cell.getNumericCellValue(); } if (cell.getColumnIndex() == c6) { cval = cell.getNumericCellValue(); } break; } } } if (rec.length() == 5 || rec.length() == 8) { rpps = rec + "-" + pay + "-" + per + "-" + svc; //System.out.print(rpps+"|"+dval+"|"+cval); //System.out.println("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); //System.out.println(); // ADD insert Query to Array sqlstr = "insert into " + tbl + " (rpps,sdrval) values(\"" + rpps + "\"," + dval + ")"; ar.add(sqlstr); //stmt.executeUpdate("insert into "+tbl+" (rpps,sdrval) values(\""+rpps+"\","+dval+")"); } } workbook.close(); inputStream.close(); return ar; }
From source file:kaflib.types.Matrix.java
License:Open Source License
/** * Writes the specified matrices to file as worksheets. Each object is * represented by its toString() value.//from ww w . j a va 2 s .c om * @param file * @param worksheets * @throws Exception */ @SafeVarargs public static <T> void toXLSX(final File file, final Pair<String, Matrix<T>>... worksheets) throws Exception { Workbook workbook = new XSSFWorkbook(); for (Pair<String, Matrix<T>> worksheet : worksheets) { Sheet sheet = workbook.createSheet(worksheet.getFirst()); Matrix<T> matrix = worksheet.getSecond(); int i = 0; if (matrix.getColumnLabels() != null && matrix.getColumnLabels().size() > 0) { Row row = sheet.createRow(i); int j = 0; for (String string : matrix.getColumnLabels()) { row.createCell(j).setCellValue(string); j++; } i++; } for (int mrow = 0; mrow < matrix.getRowCount(); mrow++) { Row row = sheet.createRow(i); int j = 0; for (T t : matrix.getRow(mrow)) { if (t != null) { row.createCell(j).setCellValue(t.toString()); } else { row.createCell(j).setCellValue(""); } j++; } i++; } } FileOutputStream stream = new FileOutputStream(file); workbook.write(stream); stream.close(); workbook.close(); }
From source file:kaflib.utils.FileUtils.java
License:Open Source License
/** * Reads a spreadsheet to a set of matrices (one per worksheet). * @param file// w ww .j av a 2 s . co m * @return * @throws Exception */ public static Map<String, Matrix<String>> readXLSX(final File file, final boolean columnTitles) throws Exception { Map<String, Matrix<String>> matrices = new HashMap<String, Matrix<String>>(); Workbook workbook = null; workbook = new XSSFWorkbook(new FileInputStream(file)); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { String name = workbook.getSheetName(i); Sheet sheet = workbook.getSheetAt(i); if (sheet == null) { continue; } Matrix<String> matrix = new Matrix<String>(); int start = 0; if (columnTitles) { Row row = sheet.getRow(0); if (row != null) { List<String> labels = new ArrayList<String>(); for (int k = 0; k < row.getLastCellNum(); k++) { labels.add(row.getCell(k).toString()); } matrix.setColumnLabels(labels); } start = 1; } for (int j = start; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row == null) { continue; } for (int k = 0; k <= row.getLastCellNum(); k++) { Cell cell = row.getCell(k); if (cell != null) { matrix.set(j - start, k, cell.toString()); } } } matrices.put(name, matrix); } workbook.close(); return matrices; }
From source file:massiveanalyserxryv2.ServiceTaskSearch.java
@Override protected Task createTask() { Task task;// w w w . j a v a2 s.com task = new Task() { @Override protected Object call() throws Exception { // rcupration de la liste des mots cls // si il s'agit d'une base de donne slectionn dans la liste String path; if (modelDataSearch.getAbosoluthPathDb() == null) { path = System.getProperty("user.dir"); path = path + "/db/"; path = path + modelDataSearch.getNameDb(); } else { // sinon on cre le path avec le chemin absolu (fichier import) path = modelDataSearch.getAbosoluthPathDb(); } ArrayList<String> keyWords = new ArrayList<String>(); keyWords.clear(); // lecture for (String line : Files.readAllLines(Paths.get(path))) { keyWords.add(line); } // rcupration de la liste des contents du tableau excel Workbook book = WorkbookFactory.create(modelDataSearch.getFile()); // rcupration du sheet Sheet sheet = book.getSheet(modelDataSearch.getNameSheet()); // rcupration de la colonne int top = sheet.getFirstRowNum(); int down = sheet.getLastRowNum(); Row row = sheet.getRow(top); // on parse les column jusqu'a ce que le nom soit le meme que celui dans le modele short start = row.getFirstCellNum(); short end = row.getLastCellNum(); int indiceColumn = -1; for (short i = start; i <= end; i++) { if (row.getCell(i).getStringCellValue().equals(modelDataSearch.getNameColumn())) { // on connait l'indice de column indiceColumn = i; break; } } // cration de la liste des contents ArrayList<DataContent> listContent = new ArrayList<DataContent>(); listContent.clear(); for (int j = top; j <= down; j++) { if (sheet.getRow(j) != null) { if ((sheet.getRow(j).getCell(indiceColumn).getCellType() == CellType.STRING.getCode())) { DataContent data = new DataContent(j, sheet.getRow(j).getCell(indiceColumn).getStringCellValue()); listContent.add(data); } } } // recherches for (DataContent content : listContent) { for (String key : keyWords) { if (key.isEmpty()) continue; int res = content.getContent().toLowerCase().indexOf(key.toLowerCase()); if (res != -1) { DataResultat data = new DataResultat(content.getNumRow() + 1, content.getContent(), key); // +1 car dans le fichie excel les row commence 1 et pas 0 ob.add(data); } } } // Fermeture du workbook if (book != null) book.close(); //tableauResultat.setItems(ob); return (Object) ob; } }; return task; }
From source file:naivebayesimplementation.NaiveBayesClassifier.java
License:Open Source License
public void ReadTrainingExamples(Map<String, String[]> trainingExamples) { String excelFilePath = "resources/topics/AssignedTopics.xlsx"; FileInputStream inputStream;/*from w w w . j av a 2s .c om*/ try { inputStream = new FileInputStream(new File(excelFilePath)); Workbook workbook = new XSSFWorkbook(inputStream); Sheet firstSheet = workbook.getSheetAt(0); String[] emtpyArray = new String[1]; emtpyArray[0] = " "; // Using for Topics that don't fit any of the topics //trainingExamples.put("Another Topic", emtpyArray ); for (int row = 0; row < 60; row++) { String topicName = firstSheet.getRow(row).getCell(1).toString(); String topicWords = firstSheet.getRow(row).getCell(2).toString(); String[] topicWordsArray = topicWords.split("\\s"); trainingExamples.put(topicName, topicWordsArray); } // Saving this topics for scoring allTrainingTopics = trainingExamples; workbook.close(); inputStream.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:nl.detoren.ijsco.io.ExcelImport.java
License:Open Source License
public GroepsUitslagen importeerUitslagen(File file) { GroepsUitslagen groepen = new GroepsUitslagen(); try {//from w ww .java 2 s . co m FileInputStream excelFile = new FileInputStream(file); Workbook workbook = new XSSFWorkbook(excelFile); String versie = "onbekend"; Sheet sheet = workbook.getSheet("Configuratie"); try { versie = sheet.getRow(0).getCell(1).getStringCellValue(); } catch (Exception ex) { logger.log(Level.INFO, "Indelings Excel versie ophalen mislukt"); } logger.log(Level.INFO, "Indelings Excel is versie " + versie); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { sheet = workbook.getSheetAt(i); if (sheet.getSheetName().startsWith("Groep ")) { logger.log(Level.INFO, "Importeer uitslag van groep : " + sheet.getSheetName()); Row row = sheet.getRow(0); if (row != null) { Cell cell = row.getCell(0); if (cell != null) { int groepsgrootte = (int) cell.getNumericCellValue(); logger.log(Level.INFO, "Groepsgrootte is " + groepsgrootte); switch (groepsgrootte) { case 4: groepen.Add(importeerGroep4(sheet)); break; case 6: groepen.Add(importeerGroep6(sheet)); break; case 8: groepen.Add(importeerGroep8(sheet)); break; case 10: groepen.Add(importeerGroep10(sheet)); break; default: logger.log(Level.WARNING, "Uitslagen verwerken voor groepsgrootte " + groepsgrootte + " niet ondersteund!"); break; } } } logger.log(Level.INFO, "next"); } } workbook.close(); } catch (Exception ex) { logger.log(Level.SEVERE, "Exception! Cause: " + ex.getCause() + ". Internal error is " + ex.getMessage() + ". Stracktrace: \r\n" + Utils.stackTraceToString(ex)); } // Print resultaat; logger.log(Level.INFO, groepen.toString()); return groepen; }
From source file:onl.netfishers.netshot.RestService.java
License:Open Source License
@GET @Path("reports/export") @RolesAllowed("readonly") @Produces({ "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" }) public Response getDataXLSX(@Context HttpServletRequest request, @DefaultValue("-1") @QueryParam("group") long group, @DefaultValue("false") @QueryParam("interfaces") boolean exportInterfaces, @DefaultValue("false") @QueryParam("inventory") boolean exportInventory, @DefaultValue("xlsx") @QueryParam("format") String fileFormat) throws WebApplicationException { logger.debug("REST request, export data."); User user = (User) request.getSession().getAttribute("user"); if (fileFormat.compareToIgnoreCase("xlsx") == 0) { String fileName = String.format("netshot-export_%s.xlsx", (new SimpleDateFormat("yyyyMMdd-HHmmss")).format(new Date())); Session session = Database.getSession(); try {//from www .ja va 2s. co m Workbook workBook = new XSSFWorkbook(); Row row; Cell cell; CreationHelper createHelper = workBook.getCreationHelper(); CellStyle datetimeCellStyle = workBook.createCellStyle(); datetimeCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm")); CellStyle dateCellStyle = workBook.createCellStyle(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd")); Sheet summarySheet = workBook.createSheet("Summary"); row = summarySheet.createRow(0); row.createCell(0).setCellValue("Netshot version"); row.createCell(1).setCellValue(Netshot.VERSION); row = summarySheet.createRow(1); row.createCell(0).setCellValue("Exported by"); row.createCell(1).setCellValue(user.getName()); row = summarySheet.createRow(2); row.createCell(0).setCellValue("Date and time"); cell = row.createCell(1); cell.setCellValue(new Date()); cell.setCellStyle(datetimeCellStyle); row = summarySheet.createRow(4); row.createCell(0).setCellValue("Selected Group"); Query query; if (group == -1) { query = session.createQuery("select d from Device d"); row.createCell(1).setCellValue("None"); } else { query = session.createQuery("select d from Device d join d.ownerGroups g where g.id = :id") .setLong("id", group); DeviceGroup deviceGroup = (DeviceGroup) session.get(DeviceGroup.class, group); row.createCell(1).setCellValue(deviceGroup.getName()); } Sheet deviceSheet = workBook.createSheet("Devices"); row = deviceSheet.createRow(0); row.createCell(0).setCellValue("ID"); row.createCell(1).setCellValue("Name"); row.createCell(2).setCellValue("Management IP"); row.createCell(3).setCellValue("Domain"); row.createCell(4).setCellValue("Network Class"); row.createCell(5).setCellValue("Family"); row.createCell(6).setCellValue("Creation"); row.createCell(7).setCellValue("Last Change"); row.createCell(8).setCellValue("Software"); row.createCell(9).setCellValue("End of Sale Date"); row.createCell(10).setCellValue("End Of Life Date"); int yDevice = 1; @SuppressWarnings("unchecked") List<Device> devices = query.list(); for (Device device : devices) { row = deviceSheet.createRow(yDevice++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(device.getName()); row.createCell(2).setCellValue(device.getMgmtAddress().getIp()); row.createCell(3).setCellValue(device.getMgmtDomain().getName()); row.createCell(4).setCellValue(device.getNetworkClass().toString()); row.createCell(5).setCellValue(device.getFamily()); cell = row.createCell(6); cell.setCellValue(device.getCreatedDate()); cell.setCellStyle(datetimeCellStyle); cell = row.createCell(7); cell.setCellValue(device.getChangeDate()); cell.setCellStyle(datetimeCellStyle); row.createCell(8).setCellValue(device.getSoftwareVersion()); if (device.getEosDate() != null) { cell = row.createCell(9); cell.setCellValue(device.getEosDate()); cell.setCellStyle(dateCellStyle); } if (device.getEolDate() != null) { cell = row.createCell(10); cell.setCellValue(device.getEolDate()); cell.setCellStyle(dateCellStyle); } } if (exportInterfaces) { Sheet interfaceSheet = workBook.createSheet("Interfaces"); row = interfaceSheet.createRow(0); row.createCell(0).setCellValue("Device ID"); row.createCell(1).setCellValue("Virtual Device"); row.createCell(2).setCellValue("Name"); row.createCell(3).setCellValue("Description"); row.createCell(4).setCellValue("VRF"); row.createCell(5).setCellValue("MAC Address"); row.createCell(6).setCellValue("Enabled"); row.createCell(7).setCellValue("Level 3"); row.createCell(8).setCellValue("IP Address"); row.createCell(9).setCellValue("Mask Length"); row.createCell(10).setCellValue("Usage"); int yInterface = 1; for (Device device : devices) { for (NetworkInterface networkInterface : device.getNetworkInterfaces()) { if (networkInterface.getIpAddresses().size() == 0) { row = interfaceSheet.createRow(yInterface++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(networkInterface.getVirtualDevice()); row.createCell(2).setCellValue(networkInterface.getInterfaceName()); row.createCell(3).setCellValue(networkInterface.getDescription()); row.createCell(4).setCellValue(networkInterface.getVrfInstance()); row.createCell(5).setCellValue(networkInterface.getMacAddress()); row.createCell(6).setCellValue(networkInterface.isEnabled()); row.createCell(7).setCellValue(networkInterface.isLevel3()); row.createCell(8).setCellValue(""); row.createCell(9).setCellValue(""); row.createCell(10).setCellValue(""); } for (NetworkAddress address : networkInterface.getIpAddresses()) { row = interfaceSheet.createRow(yInterface++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(networkInterface.getVirtualDevice()); row.createCell(2).setCellValue(networkInterface.getInterfaceName()); row.createCell(3).setCellValue(networkInterface.getDescription()); row.createCell(4).setCellValue(networkInterface.getVrfInstance()); row.createCell(5).setCellValue(networkInterface.getMacAddress()); row.createCell(6).setCellValue(networkInterface.isEnabled()); row.createCell(7).setCellValue(networkInterface.isLevel3()); row.createCell(8).setCellValue(address.getIp()); row.createCell(9).setCellValue(address.getPrefixLength()); row.createCell(10).setCellValue(address.getAddressUsage() == null ? "" : address.getAddressUsage().toString()); } } } } if (exportInventory) { Sheet inventorySheet = workBook.createSheet("Inventory"); row = inventorySheet.createRow(0); row.createCell(0).setCellValue("Device ID"); row.createCell(1).setCellValue("Slot"); row.createCell(2).setCellValue("Part Number"); row.createCell(3).setCellValue("Serial Number"); int yInventory = 1; for (Device device : devices) { for (Module module : device.getModules()) { row = inventorySheet.createRow(yInventory++); row.createCell(0).setCellValue(device.getId()); row.createCell(1).setCellValue(module.getSlot()); row.createCell(2).setCellValue(module.getPartNumber()); row.createCell(3).setCellValue(module.getSerialNumber()); } } } ByteArrayOutputStream output = new ByteArrayOutputStream(); workBook.write(output); workBook.close(); return Response.ok(output.toByteArray()) .header("Content-Disposition", "attachment; filename=" + fileName).build(); } catch (IOException e) { logger.error("Unable to write the resulting file.", e); throw new WebApplicationException("Unable to write the resulting file.", javax.ws.rs.core.Response.Status.INTERNAL_SERVER_ERROR); } catch (Exception e) { logger.error("Unable to generate the report.", e); throw new WebApplicationException("Unable to generate the report.", javax.ws.rs.core.Response.Status.INTERNAL_SERVER_ERROR); } finally { session.close(); } } logger.warn("Invalid requested file format."); throw new WebApplicationException("The requested file format is invalid or not supported.", javax.ws.rs.core.Response.Status.BAD_REQUEST); }