List of usage examples for org.apache.poi.openxml4j.opc OPCPackage open
public static OPCPackage open(File file, PackageAccess access) throws InvalidFormatException
From source file:packtest.XLSX2CSV.java
License:Apache License
public static void main(String[] args) throws Exception { if (args.length < 1) { System.err.println("Use:"); System.err.println(" XLSX2CSV <xlsx file> [min columns]"); return;/*from w w w. j a va 2 s . c om*/ } File xlsxFile = new File(args[0]); if (!xlsxFile.exists()) { System.err.println("Not found or not a file: " + xlsxFile.getPath()); return; } int minColumns = -1; if (args.length >= 2) minColumns = Integer.parseInt(args[1]); // The package open is instantaneous, as it should be. OPCPackage p = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ); XLSX2CSV xlsx2csv = new XLSX2CSV(p, System.out, minColumns); xlsx2csv.process(); p.close(); }
From source file:sailpoint.services.task.genericImport.ExcelSaxImport.java
License:Apache License
/** * Initiates the processing of the XLS workbook file to CSV. * * @throws IOException/*w ww.java 2 s .co m*/ * @throws OpenXML4JException * @throws ParserConfigurationException * @throws SAXException */ public void process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException { OPCPackage xlsxPackage = OPCPackage.open(xlFile.getPath(), PackageAccess.READ); ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(xlsxPackage); XSSFReader xssfReader = new XSSFReader(xlsxPackage); StylesTable styles = xssfReader.getStylesTable(); XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData(); int index = 0; while (iter.hasNext()) { InputStream stream = iter.next(); if (sheetName.equals(iter.getSheetName()) || sheetName == null) { if (log.isDebugEnabled()) log.debug("Sheet name: " + sheetName + " (" + index + ")"); processSheet(styles, strings, stream); } stream.close(); ++index; } xlsxPackage.close(); }
From source file:surveyKPI.AllAssignments.java
License:Open Source License
@POST @Path("/load") public Response loadResultsFromFile(@Context HttpServletRequest request) { Response response = null;/*from ww w . j a v a2s. c o m*/ log.info("Load results from file"); // Authorisation - Access Connection sd = SDDataSource.getConnection("surveyKPI-AllAssignments-LoadTasks From File"); a.isAuthorised(sd, request.getRemoteUser()); // End role based authorisation - Check access to the requested survey once the survey id has been extracted DiskFileItemFactory fileItemFactory = new DiskFileItemFactory(); fileItemFactory.setSizeThreshold(20 * 1024 * 1024); // 20 MB TODO handle this with exception and redirect to an error page ServletFileUpload uploadHandler = new ServletFileUpload(fileItemFactory); // SQL to get a column name from the survey String sqlGetCol = "select q_id, qname, column_name, qtype " + "from question " + "where f_id = ? " + "and lower(qname) = ? " + "and source is not null " + "and not soft_deleted"; PreparedStatement pstmtGetCol = null; // Alternate SQL for data downloaded from google sheets - This will have all underscores stripped out String sqlGetColGS = "select q_id, qname, column_name, qtype " + "from question " + "where f_id = ? " + "and replace(lower(qname), '_','') = ? " + "and source is not null " + "and not soft_deleted"; PreparedStatement pstmtGetColGS = null; // SQL to get choices for a select question String sqlGetChoices = "select o.ovalue, o.column_name from option o, question q where q.q_id = ? and o.l_id = q.l_id"; PreparedStatement pstmtGetChoices = null; PreparedStatement pstmtDeleteExisting = null; // SQL to clear entries in linked_forms that controls csv regeneration String sqlDelLinks = "delete from linked_forms where linked_s_id = ? "; PreparedStatement pstmtDelLinks = null; String uploadedFileName = null; String sourceFormName = null; String fileName = null; String filePath = null; File savedFile = null; // The uploaded file ArrayList<File> dataFiles = new ArrayList<File>(); // Uploaded data files - There may be multiple of these in a zip file File zipFolder = null; // Temporary folder created using the contents of a zip String contentType = null; String importSource = "file"; // default to file int sId = 0; int sourceSurveyId = 0; String sIdent = null; // Survey Ident String sName = null; // Survey Name ArrayList<MetaItem> preloads = null; boolean clear_existing = false; HashMap<String, File> mediaFiles = new HashMap<String, File>(); HashMap<String, File> formFileMap = null; ArrayList<String> responseMsg = new ArrayList<String>(); int recordsWritten = 0; String validateSurvey = null; Calendar cal = Calendar.getInstance(); Timestamp importTime = new Timestamp(cal.getTime().getTime()); Connection results = ResultsDataSource.getConnection("surveyKPI-AllAssignments-LoadTasks From File"); boolean superUser = false; ResourceBundle localisation = null; try { // Get the users locale Locale locale = new Locale(GeneralUtilityMethods.getUserLanguage(sd, request, request.getRemoteUser())); localisation = ResourceBundle.getBundle("org.smap.sdal.resources.SmapResources", locale); try { superUser = GeneralUtilityMethods.isSuperUser(sd, request.getRemoteUser()); } catch (Exception e) { } String tz = "UTC"; // get default timezone // Get the base path String basePath = GeneralUtilityMethods.getBasePath(request); // Get the items from the multi part mime List<?> items = uploadHandler.parseRequest(request); Iterator<?> itr = items.iterator(); while (itr.hasNext()) { FileItem item = (FileItem) itr.next(); if (item.isFormField()) { log.info("Form field:" + item.getFieldName() + " - " + item.getString()); if (item.getFieldName().equals("survey")) { sId = Integer.parseInt(item.getString()); if (sId > 0) { validateSurvey = "target"; a.isValidSurvey(sd, request.getRemoteUser(), sId, false, superUser); a.canLoadTasks(sd, sId); sIdent = GeneralUtilityMethods.getSurveyIdent(sd, sId); sName = GeneralUtilityMethods.getSurveyName(sd, sId); } preloads = GeneralUtilityMethods.getPreloads(sd, sId); } else if (item.getFieldName().equals("clear_existing")) { clear_existing = true; } else if (item.getFieldName().equals("import_source")) { importSource = item.getString(); } else if (item.getFieldName().equals("import_form")) { sourceSurveyId = Integer.parseInt(item.getString()); if (sourceSurveyId > 0) { validateSurvey = "source"; a.isValidSurvey(sd, request.getRemoteUser(), sourceSurveyId, false, superUser); sourceFormName = GeneralUtilityMethods.getSurveyName(sd, sourceSurveyId); } } } else if (!item.isFormField()) { // Handle Uploaded file log.info("Field Name = " + item.getFieldName() + ", File Name = " + item.getName() + ", Content type = " + item.getContentType() + ", File Size = " + item.getSize()); uploadedFileName = item.getName(); if (item.getSize() > 0) { contentType = item.getContentType(); String ext = ""; if (contentType.contains("zip")) { ext = ".zip"; } else if (contentType.contains("csv")) { ext = ".csv"; } else { ext = ".xlsx"; } fileName = String.valueOf(UUID.randomUUID()) + ext; filePath = basePath + "/temp/" + fileName; savedFile = new File(filePath); item.write(savedFile); } } } log.info("Content Type: " + contentType); if (importSource.equals("file") && contentType == null) { throw new Exception(localisation.getString("mf_mf")); } else if (importSource.equals("form") && sourceSurveyId < 1) { throw new Exception(localisation.getString("mf_ms")); } else if (importSource.equals("form") && sourceSurveyId > 0) { // download the survey String folderPath = basePath + "/temp/" + String.valueOf(UUID.randomUUID()); // Use a random sequence to keep survey name unique File folder = new File(folderPath); folder.mkdir(); /* * Save the XLS export into the folder */ ExchangeManager xm = new ExchangeManager(localisation, tz); ArrayList<FileDescription> files = xm.createExchangeFiles(sd, results, request.getRemoteUser(), sourceSurveyId, request, folderPath, superUser, true); fileName = String.valueOf(UUID.randomUUID()) + ".zip"; filePath = basePath + "/temp/" + fileName; savedFile = new File(filePath); GeneralUtilityMethods .writeFilesToZipOutputStream(new ZipOutputStream(new FileOutputStream(savedFile)), files); folder.delete(); // Clean up // Set the uploaded file name to the source form name uploadedFileName = sourceFormName; } /* * Get the forms for this survey */ ExchangeManager xm = new ExchangeManager(localisation, tz); ArrayList<FormDesc> formList = xm.getFormList(sd, sId); pstmtGetCol = sd.prepareStatement(sqlGetCol); // Prepare the statement to get the column names in the survey that are to be updated pstmtGetColGS = sd.prepareStatement(sqlGetColGS); pstmtGetChoices = sd.prepareStatement(sqlGetChoices); // Prepare the statement to get select choices // If this is a zip file extract the contents and set the path to the expanded data file that should be inside // Refer to http://www.mkyong.com/java/how-to-decompress-files-from-a-zip-file/ if (savedFile.getName().endsWith(".zip")) { String zipFolderPath = savedFile.getAbsolutePath() + ".dir"; zipFolder = new File(zipFolderPath); if (!zipFolder.exists()) { zipFolder.mkdir(); } ZipInputStream zis = new ZipInputStream(new FileInputStream(savedFile)); ZipEntry ze = null; byte[] buffer = new byte[1024]; while ((ze = zis.getNextEntry()) != null) { String zFileName = ze.getName(); if (!zFileName.startsWith("__MAC")) { // Files added by macintosh zip utility log.info("File in zip: " + ze.getName()); File zFile = new File(zipFolderPath + File.separator + zFileName); new File(zFile.getParent()).mkdirs(); // Make sure path is complete if (ze.isDirectory()) { zFile.mkdir(); } else { if ((zFileName.endsWith(".csv") || zFileName.endsWith(".xlsx")) && !zFileName.startsWith("~$")) { // Data file dataFiles.add(zFile); } else { // Media File. Save the filename and File for processing with each record of data // Remove the path from the filename - every file in the zip file must have a unique name int idx = zFileName.lastIndexOf('/'); if (idx > 0) { zFileName = zFileName.substring(idx + 1); } mediaFiles.put(zFileName, zFile); } // Write the file FileOutputStream fos = new FileOutputStream(zFile); int len; while ((len = zis.read(buffer)) > 0) { fos.write(buffer, 0, len); } fos.close(); } } zis.closeEntry(); } zis.close(); savedFile.delete(); // clean up } else { dataFiles.add(savedFile); } /* * Get a mapping between form name and file name * We need this as the data will need to be applied from parent form to child form in order rather than * in file order */ formFileMap = getFormFileMap(xm, dataFiles, formList); /* * Create the results tables for the survey if they do not exist */ UtilityMethods.createSurveyTables(sd, results, localisation, sId, formList, sIdent, tz); /* * Delete the existing data if requested */ results.setAutoCommit(false); if (clear_existing) { for (int i = 0; i < formList.size(); i++) { String sqlDeleteExisting = "truncate " + formList.get(i).table_name + ";"; if (pstmtDeleteExisting != null) try { pstmtDeleteExisting.close(); } catch (Exception e) { } pstmtDeleteExisting = results.prepareStatement(sqlDeleteExisting); log.info("Clearing results: " + pstmtDeleteExisting.toString()); pstmtDeleteExisting.executeUpdate(); } /* * Delete any attachments * TODO this will delete the attachments even if the new upload fails */ String fileFolder = basePath + "/attachments/" + sIdent; File folder = new File(fileFolder); try { log.info("Deleting attachments folder: " + fileFolder); FileUtils.deleteDirectory(folder); } catch (IOException e) { log.info("Error deleting attachments directory:" + fileFolder + " : " + e.getMessage()); } } /* * Process the data files * Identify forms * Identify columns in forms */ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); for (int formIdx = 0; formIdx < formList.size(); formIdx++) { FormDesc formDesc = formList.get(formIdx); File f = formFileMap.get(formDesc.name); if (f != null) { boolean isCSV = false; if (f.getName().endsWith(".csv")) { isCSV = true; } int count = 0; if (isCSV) { count = xm.loadFormDataFromCsvFile(results, pstmtGetCol, pstmtGetColGS, pstmtGetChoices, f, formDesc, sIdent, mediaFiles, responseMsg, basePath, localisation, preloads, uploadedFileName, importTime, request.getServerName(), sdf); } else { try (OPCPackage p = OPCPackage.open(f.getPath(), PackageAccess.READ)) { XLSXEventParser ep = new XLSXEventParser(p); count = ep.processSheet(results, pstmtGetCol, pstmtGetChoices, pstmtGetColGS, responseMsg, formDesc, preloads, xm, importSource, importTime, request.getServerName(), basePath, sIdent, mediaFiles, sdf); } } if (formIdx == 0) { recordsWritten = count; } } else { responseMsg.add(localisation.getString("imp_no_file") + ": " + formDesc.name); log.info("No file of data for form: " + formDesc.name); } } /* * Clear any entries in linked_forms for this survey so that CSV files will be regenerated */ pstmtDelLinks = sd.prepareStatement(sqlDelLinks); pstmtDelLinks.setInt(1, sId); pstmtDelLinks.executeUpdate(); results.commit(); String logMessage = null; if (importSource.equals("file")) { logMessage = localisation.getString("imp_file"); } else { logMessage = localisation.getString("imp_form"); } logMessage = logMessage.replace("%s1", String.valueOf(recordsWritten)); logMessage = logMessage.replace("%s2", uploadedFileName); logMessage = logMessage.replace("%s3", sName); logMessage += ". "; if (clear_existing) { logMessage += localisation.getString("imp_pr_del"); } else { logMessage += localisation.getString("imp_pr_pres"); } String tMessage = localisation.getString("imp_time"); tMessage = tMessage.replace("%s1", String.valueOf(importTime)); logMessage += ". " + tMessage; lm.writeLog(sd, sId, request.getRemoteUser(), "import data", logMessage); log.info("userevent: " + request.getRemoteUser() + " : loading file into survey: " + sId + " Previous contents are" + (clear_existing ? " deleted" : " preserved")); // Write user event in english only Gson gson = new GsonBuilder().disableHtmlEscaping().create(); /* * Remove any temporary files created */ for (File f : dataFiles) { f.delete(); } for (String path : mediaFiles.keySet()) { File f = mediaFiles.get(path); f.delete(); } if (zipFolder != null) { zipFolder.delete(); } /* * Return results */ responseMsg.add(localisation.getString("imp_c")); response = Response.status(Status.OK).entity(gson.toJson(responseMsg)).build(); } catch (AuthorisationException e) { log.log(Level.SEVERE, "", e); try { results.rollback(); } catch (Exception ex) { } String msg = ""; if (validateSurvey != null && validateSurvey.equals("target")) { msg = localisation.getString("msg_load_file"); msg = msg.replace("%s1", String.valueOf(sId)); } else { msg = localisation.getString("msg_load_form"); msg = msg.replace("%s1", String.valueOf(sourceSurveyId)); } response = Response.status(Status.FORBIDDEN).entity(msg).build(); } catch (NotFoundException e) { log.log(Level.SEVERE, "", e); try { results.rollback(); } catch (Exception ex) { } throw new NotFoundException(); } catch (Exception e) { String msg = e.getMessage(); if (msg != null && (msg.startsWith("org.postgresql.util.PSQLException: Zero bytes") || msg.equals("java.lang.reflect.InvocationTargetException"))) { msg = localisation.getString("msg_load_format"); } else { log.log(Level.SEVERE, "", e); } response = Response.status(Status.INTERNAL_SERVER_ERROR).entity(msg).build(); try { results.rollback(); } catch (Exception ex) { } } finally { try { if (pstmtGetCol != null) { pstmtGetCol.close(); } } catch (SQLException e) { } try { if (pstmtGetColGS != null) { pstmtGetColGS.close(); } } catch (SQLException e) { } try { if (pstmtGetChoices != null) { pstmtGetChoices.close(); } } catch (SQLException e) { } try { if (pstmtDeleteExisting != null) { pstmtDeleteExisting.close(); } } catch (SQLException e) { } try { if (pstmtDelLinks != null) { pstmtDelLinks.close(); } } catch (SQLException e) { } try { results.setAutoCommit(true); } catch (SQLException e) { } try { SDDataSource.closeConnection("surveyKPI-AllAssignments-LoadTasks From File", sd); } catch (Exception e) { } ; try { ResultsDataSource.closeConnection("surveyKPI-AllAssignments-LoadTasks From File", results); } catch (Exception e) { } ; } return response; }
From source file:surveyKPI.AllAssignments.java
License:Open Source License
private HashMap<String, File> getFormFileMap(ExchangeManager xm, ArrayList<File> files, ArrayList<FormDesc> forms) throws Exception { HashMap<String, File> formFileMap = new HashMap<String, File>(); /*//from w ww. j av a2 s . c o m * If there is only one csv file then associate it with the main form * This is to ensure backward compatability for versions prior to 16.12 which only allowed a single data file of any name to load the main form */ boolean allDone = false; if (files.size() == 1) { File file = files.get(0); if (file.getName().endsWith(".csv")) { formFileMap.put("main", file); allDone = true; } } /* * Otherwise associate forms with files */ if (!allDone) { for (int i = 0; i < files.size(); i++) { File file = files.get(i); String filename = file.getName(); if (filename.endsWith(".csv")) { int idx = filename.lastIndexOf('.'); String formName = filename.substring(0, idx); formFileMap.put(formName, file); } else { // The package open is instantaneous, as it should be. try (OPCPackage p = OPCPackage.open(file.getPath(), PackageAccess.READ)) { XLSXEventParser ep = new XLSXEventParser(p); ArrayList<String> formNames = ep.getSheetNames(); for (int j = 0; j < formNames.size(); j++) { formFileMap.put(formNames.get(j), file); } } } } } return formFileMap; }
From source file:xlsconv.XLSX2CSV.java
License:Apache License
/** * Creates a new XLS -> CSV converter * * @param filename//from w w w . j ava 2 s .co m * The file to process * @param minColumns * The minimum number of columns to output, or -1 for no minimum * @throws IOException * @throws FileNotFoundException * @throws InvalidFormatException */ public XLSX2CSV(String filename, int minColumns) throws IOException, FileNotFoundException, InvalidFormatException { this(OPCPackage.open(filename, PackageAccess.READ), minColumns); inputFile = filename; }