Example usage for org.apache.poi.ss.usermodel Workbook close

List of usage examples for org.apache.poi.ss.usermodel Workbook close

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. close

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook close.

Prototype

@Override
void close() throws IOException;

Source Link

Document

Close the underlying input resource (File or Stream), from which the Workbook was read.

Usage

From source file:com.projectswg.tools.controllers.MainController.java

License:Open Source License

private void handlePopulateList(File source) throws IOException, InvalidFormatException {
    Workbook workbook = WorkbookFactory.create(source);
    outputSelectionList.clear();//from w  ww  . j a  v a 2s  .c o m
    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
        outputSelectionList.add(getFileItem(source.getAbsolutePath(), workbook.getSheetAt(i)));
    }
    workbook.close();
}

From source file:com.snapdeal.scm.web.core.utils.ExcelReader.java

License:Open Source License

public static FileValuesDTO readExcelFile(MultipartFile file) throws IOException, FileUploadException {

    FileValuesDTO fileValue = new FileValuesDTO();

    InputStream inputStream = file.getInputStream();

    Workbook workbook = getWorkbook(inputStream, file.getOriginalFilename());
    Sheet firstSheet = workbook.getSheetAt(0);

    Iterator<Row> iterator = firstSheet.iterator();

    if (!iterator.hasNext()) {
        throw new FileUploadException("Empty File Uploded");
    }/*from   ww  w  .ja v  a 2s.c  o m*/

    List<String> headers = new ArrayList<String>();
    iterator.next().forEach(cell -> headers.add(cell.getStringCellValue()));
    fileValue.setHeader(headers);
    List<Map<String, String>> headerDataMap = new ArrayList<Map<String, String>>();
    int size = fileValue.getHeader().size();

    while (iterator.hasNext()) {
        Map<String, String> dataMap = new HashMap<String, String>();
        Row row = iterator.next();
        if (row.getPhysicalNumberOfCells() == 0) {
            continue;
        }
        for (int i = 0; i < size; i++) {
            switch (row.getCell(i).getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                dataMap.put(fileValue.getHeader().get(i),
                        new Double(row.getCell(i).getNumericCellValue()).toString());
                break;
            case Cell.CELL_TYPE_STRING:
                dataMap.put(fileValue.getHeader().get(i), row.getCell(i).getStringCellValue());
                break;
            }
        }
        headerDataMap.add(dataMap);
    }

    if (CollectionUtils.isEmpty(headerDataMap)) {
        throw new FileUploadException("No date in Uploded File");
    }

    fileValue.setHeaderDataMap(headerDataMap);
    workbook.close();
    inputStream.close();

    return fileValue;
}

From source file:com.svi.uzabase.logic.ExtractData.java

private List<String> extractCompanyNames() {
    List<String> companyList = new ArrayList<>();
    try {//  w w  w.  jav a  2s  . co m
        FileInputStream inputStream = new FileInputStream(new File(COMPANY_EXCEL_PATH));
        Workbook workbook = new HSSFWorkbook(inputStream);
        Row row;
        Cell cell;
        Sheet sheet;
        sheet = workbook.getSheetAt(0);
        for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            row = sheet.getRow(rowIndex);
            for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                cell = row.getCell(colIndex);
                cell.setCellType(Cell.CELL_TYPE_STRING);
                if (colIndex == 1) {
                    companyList.add(cell.getStringCellValue().toUpperCase());
                }
            }
        }
        //Closes opened documents
        inputStream.close();
        workbook.close();
    } catch (IOException ex) {
        Logger.getLogger(ExtractData.class.getName()).log(Level.SEVERE, null, ex);
    }
    return companyList;
}

From source file:com.toba.bll.admin.ReportsDownloadServlet.java

/**
 * Handles the HTTP <code>GET</code> method.
 *
 * @param request servlet request//from  w  w  w .j  a v a 2s  .  c o  m
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("Users Registered in the Past Month");
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("User Name");
    row.createCell(1).setCellValue("First Name");
    row.createCell(2).setCellValue("Last Name");
    row.createCell(3).setCellValue("Registration Date");

    Calendar calendar = Calendar.getInstance();
    calendar.add(Calendar.MONTH, -1);
    Date oneMonthAgo = calendar.getTime();

    List<User> usersRegisteredInThePastMonth = UserDB.selectUsersRegisteredAfter(oneMonthAgo);

    HSSFCellStyle dateCellStyle = (HSSFCellStyle) workbook.createCellStyle();
    short dateDataFormat = workbook.createDataFormat().getFormat("dd/MM/yyyy");
    dateCellStyle.setDataFormat(dateDataFormat);

    for (int i = 0; i < usersRegisteredInThePastMonth.size(); i++) {
        User user = usersRegisteredInThePastMonth.get(i);

        row = sheet.createRow(1 + i);
        row.createCell(0).setCellValue(user.getUserName());
        row.createCell(1).setCellValue(user.getFirstName());
        row.createCell(2).setCellValue(user.getLastName());
        row.createCell(3).setCellValue(user.getRegistrationDate());
        row.getCell(3).setCellStyle(dateCellStyle);
    }

    workbook.write(response.getOutputStream());
    workbook.close();

    response.setHeader("content-disposition", "attachment; filename=users.xls");
    response.setHeader("cache-control", "no-cache");
}

From source file:com.wuliu.biz.util.export.strategy.CarIndexExport.java

License:Open Source License

public String export(String folderPath, String templateName, List<WuliuMergedOrderModel> mergedOrders)
        throws Exception {

    List<List<WuliuMergedOrderModel>> mergedOrderLists = split(mergedOrders);

    if (CollectionUtils.isEmpty(mergedOrderLists)) {
        return null;
    }//from  www. j a v  a  2s  . co  m

    File template = new File(this.getClass().getClassLoader().getResource(templateName).getFile());
    InputStream inp = new FileInputStream(template);
    Workbook wb = WorkbookFactory.create(inp);
    Sheet sheet = wb.getSheetAt(0);
    fillSheet(sheet, mergedOrders);

    File file = new File(folderPath, getName(mergedOrders));
    try {
        FileOutputStream outputStream = new FileOutputStream(file);
        wb.write(outputStream);
        outputStream.flush();
        outputStream.close();
        wb.close();
        System.out.println("success");
    } catch (Exception e) {
        System.out.println("It cause Error on WRITTING excel workbook: ");
        e.printStackTrace();
    }
    return file.getAbsolutePath();
}

From source file:com.wuliu.biz.util.export.strategy.WholeOrderExport.java

License:Open Source License

public String export(String folderPath, String templateName, List<WuliuMergedOrderModel> mergedOrders)
        throws Exception {
    File folder = createFolder(folderPath);
    List<List<WuliuMergedOrderModel>> mergedOrderLists = split(mergedOrders);

    if (CollectionUtils.isEmpty(mergedOrderLists)) {
        return null;
    }//  w  ww .j a  v a  2 s  . c  o m

    for (List<WuliuMergedOrderModel> item : mergedOrderLists) {
        File template = new File(this.getClass().getClassLoader().getResource(templateName).getFile());
        InputStream inp = new FileInputStream(template);
        Workbook wb = WorkbookFactory.create(inp);
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        Sheet sheet = wb.getSheetAt(0);
        fillSheet(sheet, item);
        evaluate(sheet, evaluator);

        File file = new File(folder, getName(item));
        try {
            FileOutputStream outputStream = new FileOutputStream(file);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            wb.close();
            System.out.println("success");
        } catch (Exception e) {
            System.out.println("It cause Error on WRITTING excel workbook: ");
            e.printStackTrace();
        }
    }
    return folder.getAbsolutePath();
}

From source file:contestTabulation.Setup.java

License:Open Source License

@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    HttpTransport httpTransport = new NetHttpTransport();
    JacksonFactory jsonFactory = new JacksonFactory();
    DatastoreService datastore = DatastoreServiceFactory.getDatastoreService();

    Entity contestInfo = Retrieve.contestInfo();

    GoogleCredential credential = new GoogleCredential.Builder().setJsonFactory(jsonFactory)
            .setTransport(httpTransport)
            .setClientSecrets((String) contestInfo.getProperty("OAuth2ClientId"),
                    (String) contestInfo.getProperty("OAuth2ClientSecret"))
            .build().setFromTokenResponse(new JacksonFactory().fromString(
                    ((Text) contestInfo.getProperty("OAuth2Token")).getValue(), GoogleTokenResponse.class));

    String docName = null, docLevel = null;
    for (Level level : Level.values()) {
        docName = req.getParameter("doc" + level.getName());
        if (docName != null) {
            docLevel = level.toString();
            break;
        }//from w w w .j a  v  a 2s .c o  m
    }

    if (docLevel == null) {
        resp.sendError(HttpServletResponse.SC_BAD_REQUEST,
                "Spreadsheet creation request must have paramater document name parameter set");
        return;
    }

    Query query = new Query("registration")
            .setFilter(new FilterPredicate("schoolLevel", FilterOperator.EQUAL, docLevel))
            .addSort("schoolName", SortDirection.ASCENDING);
    List<Entity> registrations = datastore.prepare(query).asList(FetchOptions.Builder.withDefaults());

    Map<String, List<JSONObject>> studentData = new HashMap<String, List<JSONObject>>();
    for (Entity registration : registrations) {
        String regSchoolName = ((String) registration.getProperty("schoolName")).trim();
        String regStudentDataJSON = unescapeHtml4(((Text) registration.getProperty("studentData")).getValue());

        JSONArray regStudentData = null;
        try {
            regStudentData = new JSONArray(regStudentDataJSON);
        } catch (JSONException e) {
            e.printStackTrace();
            resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
            return;
        }

        for (int i = 0; i < regStudentData.length(); i++) {
            if (!studentData.containsKey(regSchoolName)) {
                studentData.put(regSchoolName, new ArrayList<JSONObject>());
            }
            try {
                studentData.get(regSchoolName).add(regStudentData.getJSONObject(i));
            } catch (JSONException e) {
                resp.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                e.printStackTrace();
                return;
            }
        }
    }

    for (List<JSONObject> students : studentData.values()) {
        Collections.sort(students, new Comparator<JSONObject>() {
            @Override
            public int compare(JSONObject a, JSONObject b) {
                try {
                    return a.getString("name").compareTo(b.getString("name"));
                } catch (JSONException e) {
                    e.printStackTrace();
                    return 0;
                }
            }
        });
    }

    Workbook workbook = new XSSFWorkbook();

    XSSFCellStyle boldStyle = (XSSFCellStyle) workbook.createCellStyle();
    Font boldFont = workbook.createFont();
    boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    boldStyle.setFont(boldFont);

    Map<Subject, XSSFCellStyle> subjectCellStyles = new HashMap<Subject, XSSFCellStyle>();
    for (Subject subject : Subject.values()) {
        final double ALPHA = .144;
        String colorStr = (String) contestInfo.getProperty("color" + subject.getName());
        byte[] backgroundColor = new byte[] { Integer.valueOf(colorStr.substring(1, 3), 16).byteValue(),
                Integer.valueOf(colorStr.substring(3, 5), 16).byteValue(),
                Integer.valueOf(colorStr.substring(5, 7), 16).byteValue() };
        // http://en.wikipedia.org/wiki/Alpha_compositing#Alpha_blending
        byte[] borderColor = new byte[] { (byte) ((backgroundColor[0] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[1] & 0xff) * (1 - ALPHA)),
                (byte) ((backgroundColor[2] & 0xff) * (1 - ALPHA)) };

        XSSFCellStyle style = (XSSFCellStyle) workbook.createCellStyle();
        style.setFillBackgroundColor(new XSSFColor(backgroundColor));
        style.setFillPattern(CellStyle.ALIGN_FILL);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(new XSSFColor(borderColor));
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(new XSSFColor(borderColor));
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(new XSSFColor(borderColor));
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(new XSSFColor(borderColor));
        subjectCellStyles.put(subject, style);
    }

    Entry<String, List<JSONObject>>[] studentDataEntries = studentData.entrySet().toArray(new Entry[] {});
    Arrays.sort(studentDataEntries, Collections.reverseOrder(new Comparator<Entry<String, List<JSONObject>>>() {
        @Override
        public int compare(Entry<String, List<JSONObject>> arg0, Entry<String, List<JSONObject>> arg1) {
            return Integer.compare(arg0.getValue().size(), arg1.getValue().size());
        }
    }));

    for (Entry<String, List<JSONObject>> studentDataEntry : studentDataEntries) {
        Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(studentDataEntry.getKey()));
        Row row = sheet.createRow((short) 0);

        String[] columnNames = { "Name", "Grade", "N", "C", "M", "S" };
        for (int i = 0; i < columnNames.length; i++) {
            String columnName = columnNames[i];
            Cell cell = row.createCell(i);
            cell.setCellValue(columnName);
            cell.setCellStyle(boldStyle);
            CellUtil.setAlignment(cell, workbook, CellStyle.ALIGN_CENTER);
        }

        int longestNameLength = 7;
        int rowNum = 1;
        for (JSONObject student : studentDataEntry.getValue()) {
            try {
                row = sheet.createRow((short) rowNum);
                row.createCell(0).setCellValue(student.getString("name"));
                row.createCell(1).setCellValue(student.getInt("grade"));

                for (Subject subject : Subject.values()) {
                    String value = student.getBoolean(subject.toString()) ? "" : "X";
                    Cell cell = row.createCell(Arrays.asList(columnNames).indexOf(subject.toString()));
                    cell.setCellValue(value);
                    cell.setCellStyle(subjectCellStyles.get(subject));
                }

                if (student.getString("name").length() > longestNameLength) {
                    longestNameLength = student.getString("name").length();
                }

                rowNum++;
            } catch (JSONException e) {
                e.printStackTrace();
                resp.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, e.toString());
                return;
            }
        }

        sheet.createFreezePane(0, 1, 0, 1);
        // sheet.autoSizeColumn((short) 0); Not supported by App Engine
        sheet.setColumnWidth((short) 0, (int) (256 * longestNameLength * 1.1));
    }

    Drive drive = new Drive.Builder(httpTransport, jsonFactory, credential)
            .setApplicationName("contestTabulation").build();

    File body = new File();
    body.setTitle(docName);
    body.setMimeType("application/vnd.google-apps.spreadsheet");

    ByteArrayOutputStream outStream = new ByteArrayOutputStream();
    workbook.write(outStream);
    ByteArrayInputStream inStream = new ByteArrayInputStream(outStream.toByteArray());
    InputStreamContent content = new InputStreamContent(
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", inStream);

    drive.files().insert(body, content).execute();
    workbook.close();
}

From source file:Controller.ThreadExcelImport.java

@Override
public void run() {

    //******/*from  w w  w  .j  ava 2  s  .com*/
    // CRIA STREAM DAS PLANILHAS
    // *******************

    // stream planilha 1
    InputStream stream1 = null;
    try {
        stream1 = new FileInputStream(new File(srcFileP1));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    Workbook workbook1 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
            .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
            .open(stream1);

    // stream planilha 2
    InputStream stream2 = null;
    try {
        stream2 = new FileInputStream(new File(srcFileP2));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    Workbook workbook2 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
            .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
            .open(stream2);

    //******
    // VERIFICA OS CABECALHOS
    // *******************

    // cabealhos da planilha 1
    Sheet sheet1 = null;
    sheet1 = workbook1.getSheetAt(0);

    // Pega de acordo com o cabealho as opes
    for (Row r : sheet1) {
        if (r.getRowNum() > 0)
            break;
        for (Integer i = 0; i < headerP1.size(); i++) {
            for (Cell c : r) {
                if (c.getStringCellValue().toLowerCase()
                        .equals(headerP1.get(i).getColumnName().toLowerCase())) {
                    // Adiciona o numero da coluna ao header
                    headerP1.get(i).setColumnNumber(c.getColumnIndex());
                    break;
                }
            }

            if (headerP1.get(i).getColumnNumber() == null) {
                // Alguma coluna do template est ausente
                JOptionPane.showMessageDialog(null, "A coluna " + headerP1.get(i).getColumnName().toLowerCase()
                        + " do template no existe como cabealho na planilha 1");
                System.exit(0);
            }

        }
    }
    // cabealhos da planilha 2
    Sheet sheet2 = null;
    sheet2 = workbook2.getSheetAt(0);

    // Pega de acordo com o cabealho as opes
    for (Row r : sheet2) {
        if (r.getRowNum() > 0)
            break;
        for (Integer i = 0; i < headerP2.size(); i++) {
            for (Cell c : r) {
                if (c.getStringCellValue().toLowerCase()
                        .equals(headerP2.get(i).getColumnName().toLowerCase())) {
                    // Adiciona o numero da coluna ao header
                    headerP2.get(i).setColumnNumber(c.getColumnIndex());
                    break;
                }
            }

            if (headerP2.get(i).getColumnNumber() == null) {
                // Alguma coluna do template est ausente
                JOptionPane.showMessageDialog(null, "A coluna " + headerP2.get(i).getColumnName().toLowerCase()
                        + " do template no existe como cabealho na planilha 2");
                System.exit(0);
            }

        }
    }

    //******
    // GRAVA EM MEMRIA A PLANILHA 2 PARA EVITAR O REABRIMENTO DA MESMA A CADA ITERAO DA PLANILHA 1
    // *******************
    stream2 = null;
    try {
        stream2 = new FileInputStream(new File(srcFileP2));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    workbook2 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
            .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
            .open(stream2);

    sheet2 = null;
    sheet2 = workbook2.getSheetAt(0);

    for (Row rowP2 : sheet2) {

        if (rowP2.getRowNum() > 0) {
            InterfaceMigracao objInterfaceP2 = Factory.getInstance(templateName);

            // calcula o hash
            String hashChaveP2 = "";
            for (String chaveP2 : colunaChave) {
                Integer columIndex = -1;
                for (Header he2 : headerP2) {
                    if (he2.getColumnName().equals(chaveP2)) {
                        columIndex = he2.getColumnNumber();
                        break;
                    }
                }

                if (columIndex > -1) {
                    Cell cell = null;
                    cell = rowP2.getCell(columIndex, Row.CREATE_NULL_AS_BLANK);
                    // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 );
                    hashChaveP2 = DigestUtils
                            .sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP2);
                }

            }

            for (Header he2 : headerP2) {

                Cell cell = rowP2.getCell(he2.getColumnNumber(), Row.CREATE_NULL_AS_BLANK);
                objInterfaceP2.setString(he2.getColumnName(), cell.getStringCellValue().trim().toLowerCase());
                objInterfaceP2.setExcelRowNumber((rowP2.getRowNum() + 1));
                //System.out.println("Novo loop HeaderP2 da linhaP2 " + String.valueOf(rowP2.getRowNum()) + " coluna " + he2.getColumnName() );
            }

            if (hashChaveP2.equals("")) {
                JOptionPane.showMessageDialog(null, "A linha " + String.valueOf((rowP2.getRowNum() + 1))
                        + " da planilha 2 tem as colunas chaves nula");
                System.exit(0);
            } else
                listaP2.put(hashChaveP2, objInterfaceP2);

        }
    }

    // limpa da memoria a workbook2
    try {
        if (workbook2 != null)
            workbook2.close();
    } catch (IOException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }

    // limpa da memoria o stream com workbook2
    if (stream2 != null)
        try {
            stream2.close();
        } catch (IOException ex) {
            Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
        }

    //******
    // FAZ A VALIDAO
    // OBSERVE QUE POR TER FEITO O FOREACH NOS PLANILHAS SE TORNA NECESS?RIO RECRIAR O STREAMING
    // *******************

    // Executa o loop nas linhas da planilha

    stream1 = null;
    try {
        stream1 = new FileInputStream(new File(srcFileP1));
    } catch (FileNotFoundException ex) {
        Logger.getLogger(ThreadExcelImport.class.getName()).log(Level.SEVERE, null, ex);
    }
    workbook1 = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10)
            .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024)
            .open(stream1);

    sheet1 = null;
    sheet1 = workbook1.getSheetAt(0);

    InterfaceMigracao objInterfaceP1 = null;

    for (Row rowP1 : sheet1) {

        // Pega o hash dos campos chaves da planilha 1 a fim de localizar na planilha 1
        String hashChaveP1 = "";
        for (String chaveP1 : colunaChave) {
            Integer columIndex = -1;
            for (Header he1 : headerP1) {
                if (he1.getColumnName().equals(chaveP1)) {
                    columIndex = he1.getColumnNumber();
                    break;
                }
            }

            if (columIndex > -1) {
                Cell cell = null;
                cell = rowP1.getCell(columIndex, Row.CREATE_NULL_AS_BLANK);
                // hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1 );
                hashChaveP1 = DigestUtils.sha1Hex(cell.getStringCellValue().trim().toLowerCase() + hashChaveP1);
            }

        }

        objInterfaceP1 = Factory.getInstance(templateName);
        // objInterfaceP2 = Factory.getInstance(templateName);

        objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1));
        Notify notify = new Notify();

        if (hashChaveP1.equals(""))
            notify.setLocalizadoP1(false);
        else {
            notify.setLocalizadoP1(true);
            //seta o numero da linha no excel

            // Preenche o objeto de interface da planilha 1 com seus respectivos dados
            for (Header he1 : headerP1) {

                Cell cell = null;
                cell = rowP1.getCell(he1.getColumnNumber(), Row.CREATE_NULL_AS_BLANK);
                objInterfaceP1.setString(he1.getColumnName(), cell.getStringCellValue().trim().toLowerCase());
            }

            boolean p2Localizado = false;

            // Preenche o objeto de interface da planilha 2 com seus respectivos dados
            if (rowP1.getRowNum() > 0) {
                InterfaceMigracao objInterfaceMigracaoP2 = listaP2.get(hashChaveP1);
                if (objInterfaceMigracaoP2 != null) {
                    p2Localizado = true;
                    notify.setEntidadeP2(objInterfaceMigracaoP2);
                }

            }
            notify.setLocalizadoP2(p2Localizado);

        }

        isRunning = true;

        objInterfaceP1.setExcelRowNumber((rowP1.getRowNum() + 1));
        notify.setEntidadeP1(objInterfaceP1);
        notify.setTotalRow((sheet1.getLastRowNum() + 1));

        notify.setRunning(isRunning);
        notify.setHeaderP1(headerP1);
        notify.setHeaderP2(headerP2);

        setChanged();
        notifyObservers(notify);

    }

    isRunning = false;
    // Notifica os observadores de que a execuo terminou
    Notify notify = new Notify();
    notify.setRunning(false);
    setChanged();
    notifyObservers(notify);
    listaP2 = null;

}

From source file:controllers.TargetController.java

License:Open Source License

private static void excelParser(File inputFile) throws Throwable {

    FileInputStream file = new FileInputStream(inputFile);

    //Create Workbook instance holding reference to .xls[x] file
    Workbook workbook = WorkbookFactory.create(file);

    //Get first/desired sheet from the workbook
    Sheet sheet = workbook.getSheetAt(0);

    // Check total row:
    if (sheet.getPhysicalNumberOfRows() <= 1) {
        throw new Exception("Sheet should have at least one row.");
    }/*from  w  ww . j a  v a  2  s  .c  o m*/
    Logger.debug("Sheet has " + sheet.getPhysicalNumberOfRows() + " rows.");

    //Iterate through each rows one by one
    Iterator<Row> rowIterator = sheet.iterator();

    // Header row:
    Row header = rowIterator.next();
    Logger.debug("HEADER: " + header);
    // TODO Check header row is right.

    // And the rest:
    StringBuilder sb = new StringBuilder();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        // Get
        Target target = new Target();
        target.title = row.getCell(0).getStringCellValue();
        target.fieldUrls = new ArrayList<FieldUrl>();
        // Check URL
        FieldUrl url = new FieldUrl(row.getCell(1).getStringCellValue());
        target.fieldUrls.add(url);
        FieldUrl existingFieldUrl = FieldUrl.findByUrl(url.url);
        if (existingFieldUrl != null) {
            String error = "Row # " + row.getRowNum() + ": CONFLICT - URL " + existingFieldUrl.url
                    + " is already part of target " + existingFieldUrl.target.id + "\n";
            Logger.debug(error);
            sb.append(error);
            continue;
        }
        //Collection c = new Collection();
        //c.name = 

        // 
        System.out.println(target);

        // TODO Merge with controllers.ApplicationController.bulkImport() code to avoid repetition.
        target.revision = Const.INITIAL_REVISION;
        target.active = true;

        target.selectionType = Const.SelectionType.SELECTION.name();

        if (target.noLdCriteriaMet == null) {
            target.noLdCriteriaMet = Boolean.FALSE;
        }

        if (target.keySite == null) {
            target.keySite = Boolean.FALSE;
        }

        if (target.ignoreRobotsTxt == null) {
            target.ignoreRobotsTxt = Boolean.FALSE;
        }

        // Save - disabled right now, as we do not want this live as yet.
        /*
        target.runChecks();
        target.save();
        */

        //
        System.out.println(target);
    }
    workbook.close();
    file.close();

    // And report errors
    if (sb.length() > 0) {
        throw (new Exception(sb.toString()));
    }
}

From source file:das.pf.io.IOExcel.java

License:Open Source License

public boolean processFile(Path input, boolean openFile) {
    boolean result = false;
    int endRow = 0;

    try {/*from   ww  w  .  j  a v a 2 s  . c  o  m*/
        updateMessages(String.format("Inicializando el documento: %s", input.toString()));
        Path copy = createCopy(input);

        if (copy != null && Files.exists(copy, LinkOption.NOFOLLOW_LINKS)) {
            Workbook workbook = WorkbookFactory.create(copy.toFile());
            Sheet sheet = workbook.getSheetAt(0);
            Sheet newSheet = workbook.createSheet("Procesado");

            workbook.setSheetName(0, "Crudo");

            endRow = getLasRow(sheet);

            // seccion para la creacion de los encabezados
            updateMessages("Creando la cabecera de los datos");
            createHeaderData(newSheet, getCellStyleHeaderData(workbook));

            // seccion para los values USD
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MTH),
                    11, 35, 14);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.QRT),
                    35, 49, 38);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.YTD),
                    49, 54, 52);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values USD' %s", TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.VALUES, TypeUnits.MAT),
                    54, 59, 57);

            // seccion para los values units
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MTH),
                    59, 83, 63);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.QRT),
                    83, 97, 87);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.YTD),
                    97, 102, 101);
            updateMessages(
                    String.format("Creando la cabecera para los: 'values Units' %s", TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.UNITS, TypeUnits.MAT),
                    102, 107, 106);
            //            
            //            // seccion para los values units standars
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.MTH.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MTH),
                    107, 131, 112);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.QRT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.QRT),
                    131, 145, 136);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.YTD.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.YTD),
                    145, 150, 150);
            updateMessages(String.format("Creando la cabecera para los: 'values Standard Units' %s",
                    TypeUnits.MAT.name()));
            createHeaderValues(newSheet, sheet, getCellStyleValues(workbook, TypeValues.U_E, TypeUnits.MAT),
                    150, 155, 155);

            // fin de la seccion para la creacion de los encabezados

            // seccion para escribir los CT
            updateMessages("Escribiendo las clases terampeuticas...");
            writeCT(newSheet, sheet, 13, endRow);

            // seccion para escribir los productos
            updateMessages("Escribiendo los productos...");
            writeProducts(newSheet, sheet, 14);

            // seccion para escribir los otros valores
            updateMessages("Escribiendo datos en general...");
            writerOthersValues(newSheet, sheet, 15);

            // seccion para escribir los key competitors
            updateMessages("Escribiendo los Key Competitors...");
            writeKeyCompetitors(newSheet, 3, endRow, 9, 5);

            // seccion para escribir el pais
            XmlContry contry = writeContries(newSheet, 3, 0, input);

            // seccion para escribir la region
            writeRegions(contry, newSheet, 3, 1);

            for (int i = 0; i < 155; i++)
                newSheet.autoSizeColumn(i);

            newSheet.setAutoFilter(CellRangeAddress.valueOf(String.format("A3:K%d", newSheet.getLastRowNum())));

            String pathOutput = "DAS PF - " + input.getFileName().toString();

            try (FileOutputStream fos = new FileOutputStream(
                    Paths.get(this.out.toString(), pathOutput).toFile())) {

                updateMessages(String.format("Guadando el trabajo en la ruta: '%s'",
                        Paths.get(this.out.toString(), pathOutput)));

                workbook.write(fos);
            } catch (IOException ex) {
                Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
                workbook.close();
            }

            if (openFile && Desktop.isDesktopSupported()
                    && Desktop.getDesktop().isSupported(Desktop.Action.OPEN))
                Desktop.getDesktop().open(Paths.get(this.out.toString(), pathOutput).toFile());

            result = true;

            newSheet = null;
            sheet = null;
            workbook = null;

            Files.delete(copy);
        }
    } catch (IOException | InvalidFormatException ex) {
        Logger.getLogger(IOExcel.class.getName()).log(Level.SEVERE, null, ex);

        Util.showException("No se pudo guardar el archivo", ex);
    }

    return result;
}