Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt.

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:org.tiefaces.components.websheet.utility.ChartUtility.java

License:MIT License

/**
 * retrieve anchor information from draw.xml for all the charts in the
 * workbook. then save them to anchors map.
 *
 * @param wb/*from  w w  w  .  j a  v a2  s .c  o m*/
 *            workbook.
 * @param charsData
 *            the chars data
 */
public static void initXSSFAnchorsMap(final XSSFWorkbook wb, final ChartsData charsData) {

    Map<String, ClientAnchor> anchortMap = charsData.getChartAnchorsMap();
    Map<String, String> positionMap = charsData.getChartPositionMap();
    anchortMap.clear();
    positionMap.clear();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        initXSSFAnchorsMapForSheet(anchortMap, positionMap, wb.getSheetAt(i));
    }
}

From source file:org.tiefaces.components.websheet.utility.PicturesUtility.java

License:MIT License

/**
 * Gets the XSSF pictrues map./*from   w w  w .  ja  v a  2  s .c  om*/
 *
 * @param wb
 *            the wb
 * @param picMap
 *            the pic map
 */
private static void getXSSFPictruesMap(final XSSFWorkbook wb, final Map<String, Picture> picMap) {

    picMap.clear();
    List<XSSFPictureData> pictures = wb.getAllPictures();
    if (pictures.isEmpty()) {
        return;
    }
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        XSSFSheet sheet = wb.getSheetAt(i);
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            try {
                indexPictureInMap(picMap, sheet, dr);
            } catch (Exception ex) {
                LOG.log(Level.SEVERE, "Load Picture error = " + ex.getLocalizedMessage(), ex);
            }
        }
    }

    return;

}

From source file:org.tinywind.springi18nconverter.converter.ExcelConverter.java

License:Open Source License

@Override
@SuppressWarnings("unchecked")
public void decode(File sourceFile, String targetDir, String targetEncoding, Boolean describeByNative) {
    final String sourceFileName = sourceFile.getName().toLowerCase();
    if (Arrays.stream(JS_POSTFIX_ARRAY).filter(
            postfix -> sourceFileName.lastIndexOf(postfix) == sourceFileName.length() - postfix.length())
            .count() == 0)/*w w  w.j a v a  2  s  .  co  m*/
        return;

    try {
        final Map<String, List<String>> stringListMap = new HashMap<>();
        final FileInputStream file = new FileInputStream(sourceFile);
        Iterator<Row> rowIterator;
        try {
            final XSSFWorkbook workbook = new XSSFWorkbook(file);
            final XSSFSheet sheet = workbook.getSheetAt(0);
            rowIterator = sheet.iterator();
        } catch (OfficeXmlFileException e) {
            System.err.println(" exception:" + e.getMessage());
            final HSSFWorkbook workbook = new HSSFWorkbook(file);
            final HSSFSheet sheet = workbook.getSheetAt(0);
            rowIterator = sheet.iterator();
        }

        while (rowIterator.hasNext()) {
            final Row row = rowIterator.next();
            final String key = row.getCell(COLUMN_KEY).getStringCellValue();
            final String language = row.getCell(COLUMN_LANG).getStringCellValue();
            final String value = row.getCell(COLUMN_VALUE).getStringCellValue().trim();
            if (StringUtils.isEmpty(key) || StringUtils.isEmpty(language) || StringUtils.isEmpty(value))
                continue;

            List<String> stringList = stringListMap.get(language);
            if (stringList == null) {
                stringList = new ArrayList<>();
                stringListMap.put(language, stringList);
            }

            final String newLine = "\\\n";
            String lastValue = "", token;
            final BufferedReader reader = new BufferedReader(new StringReader(value));
            while ((token = reader.readLine()) != null)
                lastValue += token + newLine;
            reader.close();
            if (lastValue.lastIndexOf(newLine) == lastValue.length() - newLine.length())
                lastValue = lastValue.substring(0, lastValue.length() - newLine.length());

            addProperty(stringList, key, lastValue, describeByNative);
        }

        for (String language : stringListMap.keySet()) {
            Files.write(Paths.get(new File(targetDir, messagesPropertiesFileName(language)).toURI()),
                    stringListMap.get(language), Charset.forName(targetEncoding), StandardOpenOption.CREATE,
                    StandardOpenOption.TRUNCATE_EXISTING, StandardOpenOption.WRITE);
        }
    } catch (Exception e) {
        System.err.println(" FAIL to convert: " + sourceFile.getAbsolutePath());
        e.printStackTrace();
    }
}

From source file:org.wandora.application.tools.extractors.excel.ExcelAdjacencyListExtractor.java

License:Open Source License

@Override
public void processWorkbook(XSSFWorkbook workbook, TopicMap topicMap) {
    int numberOfSheets = workbook.getNumberOfSheets();
    for (int i = 0; i < numberOfSheets && !forceStop(); i++) {
        XSSFSheet sheet = workbook.getSheetAt(i);
        processSheet(sheet, topicMap);//from  w ww.  java  2  s . co m
    }
}

From source file:org.wise.portal.presentation.web.controllers.run.MergeSpreadsheetsController.java

License:Open Source License

@RequestMapping(method = RequestMethod.POST)
protected ModelAndView onSubmit(@RequestParam("uploadFile") MultipartFile uploadFile,
        @RequestParam("mergeColumnTitle") String mergeColumnTitle, HttpServletResponse response)
        throws Exception {

    // TODO: this line is saving uploadFile to home directory. Can we do without saving to home directory?
    File file = multipartToFile(uploadFile);
    String mergedResultFileName = "merged_" + file.getName();
    FileInputStream fis = new FileInputStream(file);

    // Finds the workbook instance of XLSX file
    XSSFWorkbook workbook = new XSSFWorkbook(fis);
    DataFormatter objDefaultFormat = new DataFormatter();
    FormulaEvaluator objFormulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);

    // number of sheets in the workbook
    int numberOfSheets = workbook.getNumberOfSheets();

    // contains all values of the merge column across all sheets
    ArrayList<String> mergeColumnValues = new ArrayList<String>();

    // maps mergeColumn value to a Map<SheetIndex, ArrayList<Row>>
    HashMap<String, HashMap<Integer, ArrayList<Row>>> mergeColumnValueToSheetRows = new HashMap<String, HashMap<Integer, ArrayList<Row>>>();

    // maps sheet index to the headers in that sheet
    HashMap<Integer, ArrayList<String>> sheetIndexToSheetColumnHeaders = new HashMap<Integer, ArrayList<String>>();

    // how many copies of headers need to be created for each sheet
    HashMap<Integer, Integer> sheetIndexToMaxSheetRowCount = new HashMap<Integer, Integer>();

    // loop through the sheets in the workbook and populate the variables
    for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);

        int mergeColumnIndex = -1; // index of the merge column in this sheet
        int rowIteratorIndex = 0; // index of current row iteration

        // collect all of the merge column rows in each sheet
        Iterator<Row> rowIterator = sheet.rowIterator();

        int maxSheetRowCountForCurrentSheet = 0;
        while (rowIterator.hasNext()) {
            Row row = (Row) rowIterator.next();
            if (rowIteratorIndex == 0) {
                // for the very first row in this sheet, go through all the cells in the top row and add to sheetColumnHeaders
                // and add it to sheetIndexToSheetColumnHeaders
                ArrayList<String> sheetColumnHeaders = new ArrayList<String>();

                int rowCellIteratorIndex = 0;
                Iterator<Cell> topRowCellIterator = row.cellIterator();
                while (topRowCellIterator.hasNext()) {
                    Cell topRowCell = topRowCellIterator.next();
                    String topRowCellString = topRowCell.toString();
                    if (!topRowCellString.isEmpty()) {
                        sheetColumnHeaders.add(topRowCellString);
                    }/* w ww .  j  av  a 2 s.c  o m*/
                    if (!topRowCellString.isEmpty() && topRowCellString.equals(mergeColumnTitle)) {
                        // this is the mergeColumn. Remember the column index
                        if (mergeColumnIndex == -1) {
                            mergeColumnIndex = rowCellIteratorIndex;
                        } else {
                            // there are multiple mergeColumnTitles in this sheet. Let the user know and exit
                            ModelAndView mav = new ModelAndView("/admin/run/mergespreadsheets");
                            mav.addObject("errorMsg", "You have multiple columns titled \"" + mergeColumnTitle
                                    + "\" in worksheet #" + (sheetIndex + 1)
                                    + ". You can have only one merge column per worksheet. Please fix and try again.");
                            return mav;
                        }
                    }
                    rowCellIteratorIndex++;
                }
                sheetIndexToSheetColumnHeaders.put(sheetIndex, sheetColumnHeaders);
            } else {
                // for rows that are not the top row (header)
                // 1. get all the mergeColumnValues
                // 2. populate mergeColumnValueToSheetRows
                // 3. calculate sheetIndexToMaxSheetRowCount
                Cell mergeColumnValueCell = row.getCell(mergeColumnIndex);
                if (mergeColumnValueCell != null && !mergeColumnValueCell.toString().isEmpty()) {

                    objFormulaEvaluator.evaluate(mergeColumnValueCell);
                    String mergeColumnValueString = objDefaultFormat.formatCellValue(mergeColumnValueCell,
                            objFormulaEvaluator);

                    HashMap<Integer, ArrayList<Row>> sheetIndexToSheetRows = mergeColumnValueToSheetRows
                            .get(mergeColumnValueString);
                    if (sheetIndexToSheetRows == null) {
                        sheetIndexToSheetRows = new HashMap<Integer, ArrayList<Row>>();
                        mergeColumnValueToSheetRows.put(mergeColumnValueString, sheetIndexToSheetRows);
                    }
                    ArrayList<Row> sheetRows = sheetIndexToSheetRows.get(sheetIndex);
                    if (sheetRows == null) {
                        sheetRows = new ArrayList<>();
                        sheetIndexToSheetRows.put(sheetIndex, sheetRows);
                    }
                    sheetRows.add(row);
                    if (sheetRows.size() > maxSheetRowCountForCurrentSheet) {
                        maxSheetRowCountForCurrentSheet = sheetRows.size();
                    }

                    Iterator<Cell> rowCellIterator = row.cellIterator();
                    int rowCellIteratorIndex = 0;
                    while (rowCellIterator.hasNext()) {
                        Cell rowCell = rowCellIterator.next();
                        if (rowCellIteratorIndex == mergeColumnIndex) {
                            // this is a merge column cell, so add its value to mergeColumnValues
                            if (!rowCell.toString().isEmpty()) {
                                objFormulaEvaluator.evaluate(rowCell);
                                String rowCellValueString = objDefaultFormat.formatCellValue(rowCell,
                                        objFormulaEvaluator);
                                if (!mergeColumnValues.contains(rowCellValueString)) {
                                    mergeColumnValues.add(rowCellValueString);
                                }
                            }
                        }
                        rowCellIteratorIndex++;
                    }
                }
            }
            rowIteratorIndex++;
        }
        sheetIndexToMaxSheetRowCount.put(sheetIndex, maxSheetRowCountForCurrentSheet);
    }

    // Now we are ready to make the merge sheet. We will be writing one row at a time.

    Workbook wb = new XSSFWorkbook(); // new output workbook
    Sheet mergedSheet = wb.createSheet("merged"); // output merged result in "merged" sheet

    // make the header row
    Row headerRow = mergedSheet.createRow(0);

    // (0,0) will be the merge cell header. Column 0 will contain mergeColumnValues.
    Cell mergeColumnHeaderCell = headerRow.createCell(0);
    mergeColumnHeaderCell.setCellValue(mergeColumnTitle);

    // current column index "cursor" where we will be writing to
    int cellIndexWithoutMergeColumn = 1;

    // make the header row
    for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {

        Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex);
        ArrayList<String> sheetColumnHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex);
        XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        String sheetName = sheet.getSheetName();

        for (int i = 0; i < maxSheetRowCount; i++) {
            for (int sheetColumnHeaderIndex = 0; sheetColumnHeaderIndex < sheetColumnHeaders
                    .size(); sheetColumnHeaderIndex++) {
                String sheetColumnHeader = sheetColumnHeaders.get(sheetColumnHeaderIndex);
                if (!sheetColumnHeader.isEmpty() && !sheetColumnHeader.equals(mergeColumnTitle)) {
                    String newSheetColumnHeader = sheetColumnHeader + " ( " + sheetName + " " + (i + 1) + " ) ";
                    Cell headerCell = headerRow.createCell(cellIndexWithoutMergeColumn);
                    headerCell.setCellValue(newSheetColumnHeader);
                    cellIndexWithoutMergeColumn++;
                }
            }
        }
    }

    // now make all the non-header rows
    for (int mergeColumnValueIndex = 0; mergeColumnValueIndex < mergeColumnValues
            .size(); mergeColumnValueIndex++) {
        String mergeColumnValue = mergeColumnValues.get(mergeColumnValueIndex);
        HashMap<Integer, ArrayList<Row>> mergeColumnValueSheetRow = mergeColumnValueToSheetRows
                .get(mergeColumnValue);
        if (mergeColumnValueSheetRow == null) {
            System.out.println("Null mergeColumnValueSheetRow, continuing. mergeColumnValueIndex: "
                    + mergeColumnValueIndex + " mergeColumnValue: " + mergeColumnValue);
            continue;
        }

        Row row = mergedSheet.createRow(mergeColumnValueIndex + 1); // + 1 is to account for the header row;

        // reset current cursor as we make each row
        cellIndexWithoutMergeColumn = 0;

        // first column will be the merge column value
        Cell mergeColumnCell = row.createCell(0);
        mergeColumnCell.setCellValue(mergeColumnValue);
        cellIndexWithoutMergeColumn++;

        for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex++) {
            ArrayList<Row> sheetRows = mergeColumnValueSheetRow.get(sheetIndex);
            int currentSheetSheetRowIndex = 0;
            ArrayList<String> sheetHeaders = sheetIndexToSheetColumnHeaders.get(sheetIndex);

            if (sheetRows != null) {
                for (int sheetRowIndex = 0; sheetRowIndex < sheetRows.size(); sheetRowIndex++) {
                    Row sheetRow = sheetRows.get(sheetRowIndex);
                    for (int sheetHeaderIndex = 0; sheetHeaderIndex < sheetHeaders.size(); sheetHeaderIndex++) {
                        String sheetHeader = sheetHeaders.get(sheetHeaderIndex);
                        if (!sheetHeader.equals(mergeColumnTitle)) {
                            Cell cell = sheetRow.getCell(sheetHeaderIndex);
                            Cell exportCell = row.createCell(cellIndexWithoutMergeColumn);
                            objFormulaEvaluator.evaluate(cell);
                            String cellString = objDefaultFormat.formatCellValue(cell, objFormulaEvaluator);
                            exportCell.setCellValue(cellString);
                            cellIndexWithoutMergeColumn++;
                        }
                    }
                    currentSheetSheetRowIndex++;
                }
            }

            // some columns do not have any values to populate, so populate them with empty cells
            Integer maxSheetRowCount = sheetIndexToMaxSheetRowCount.get(sheetIndex);
            while (currentSheetSheetRowIndex < maxSheetRowCount) {
                for (int i = 0; i < sheetHeaders.size(); i++) {
                    String sheetHeader = sheetHeaders.get(i);
                    if (!sheetHeader.isEmpty() && !sheetHeader.equals(mergeColumnTitle)) {
                        Cell exportCell = row.createCell(cellIndexWithoutMergeColumn);
                        exportCell.setCellValue("");
                        cellIndexWithoutMergeColumn++;
                    }
                }
                currentSheetSheetRowIndex++;
            }
        }
    }

    // write to response output
    response.setHeader("Content-Disposition", "attachment; filename=\"" + mergedResultFileName + "\"");
    ServletOutputStream outputStream = response.getOutputStream();
    wb.write(outputStream);
    fis.close();

    return null;
}

From source file:org.wise.vle.web.VLEGetXLS.java

License:Open Source License

/**
 * Set all the student data, each row represents one workgroup
 * /*from  w  ww  .java2s. com*/
 * @param workbook the excel workbook
 * @param nodeIdToNodeTitlesMap a mapping of node id to node titles
 * @param workgroupIds a vector of workgroup ids 
 * @param nodeIdList a list of node ids
 * @param runId the run id
 * @param nodeIdToNodeContent a mapping of node id to node content
 * @param workgroupIdToPeriodId a mapping of workgroup id to period id
 * @param teacherWorkgroupIds a list of teacher workgroup ids
 */
private void setGetLatestStudentWorkStudentRows(XSSFWorkbook workbook,
        HashMap<String, String> nodeIdToNodeTitlesMap, Vector<String> workgroupIds, List<String> nodeIdList,
        String runId, HashMap<String, JSONObject> nodeIdToNode, HashMap<String, JSONObject> nodeIdToNodeContent,
        HashMap<Integer, Integer> workgroupIdToPeriodId, List<String> teacherWorkgroupIds) {

    XSSFSheet mainSheet = null;
    int rowCounter = 0;

    if (workbook != null) {
        //workbook is not null which means we are generating an xls file

        //get the sheet
        mainSheet = workbook.getSheetAt(0);

        //get the next empty row
        rowCounter = mainSheet.getLastRowNum() + 1;
    }

    //loop through the workgroup ids
    for (int x = 0; x < workgroupIds.size(); x++) {
        //create a row for this workgroup
        Row rowForWorkgroupId = createRow(mainSheet, x + rowCounter);
        Vector<String> rowForWorkgroupIdVector = createRowVector();

        int workgroupColumnCounter = 0;

        //get a workgroup id
        String userId = workgroupIds.get(x);

        int periodId = workgroupIdToPeriodId.get(Integer.parseInt(userId));

        /*
         * create the row that will display the user data such as the actual values
         * for workgroup id, student login, teacher login, period name, etc.
         */
        workgroupColumnCounter = createUserDataRow(workgroupColumnCounter, rowForWorkgroupId,
                rowForWorkgroupIdVector, userId, true, true, null);

        /*
         * increment the column counter to create an empty column under the header column
         * that contains Step Title, Step Type, Step Prompt, Node Id, Step Extra
         */
        workgroupColumnCounter++;
        addEmptyElementsToVector(rowForWorkgroupIdVector, 1);

        //get the UserInfo object for the workgroup id
        UserInfo userInfo = vleService.getUserInfoByWorkgroupId(Long.parseLong(userId));

        //get all the work for a workgroup id
        List<StepWork> stepWorksForWorkgroupId = vleService.getStepWorksByUserInfo(userInfo);

        //loop through all the node ids which are ordered
        for (int y = 0; y < nodeIdList.size(); y++) {
            //get a node id
            String nodeId = nodeIdList.get(y);

            //get the content for the node
            JSONObject nodeContent = nodeIdToNodeContent.get(nodeId);

            JSONObject nodeJSONObject = nodeIdToNode.get(nodeId);

            /*
             * set the review cells if applicable to this step, this means filling in the
             * cells that specify the associated workgroup id and associated work and only
             * applies for review type steps. if the current step/node is not a review cell
             * this function call will not need to do much besides fill in N/A values
             * or nothing at all depending on whether we are getting "latestStudentWork"
             * or "allStudentWork"
             */
            workgroupColumnCounter = setGetLatestStudentWorkReviewCells(teacherWorkgroupIds,
                    stepWorksForWorkgroupId, runId, periodId, userInfo, nodeJSONObject, nodeContent,
                    rowForWorkgroupId, rowForWorkgroupIdVector, workgroupColumnCounter, "latestStudentWork");

            //get all the step works for this node id
            List<StepWork> stepWorksForNodeId = getStepWorksForNodeId(stepWorksForWorkgroupId, nodeId);

            //get the latest step work that contains a response
            StepWork latestStepWorkWithResponse = getLatestStepWorkWithResponse(stepWorksForNodeId);

            //set the step work data into the row in the given column
            workgroupColumnCounter = setStepWorkResponse(rowForWorkgroupId, rowForWorkgroupIdVector,
                    workgroupColumnCounter, latestStepWorkWithResponse, nodeId);

            if (isAutoGraded(nodeContent)) {
                //set the auto graded values
                workgroupColumnCounter = setLatestAutoScoreValues(stepWorksForNodeId, rowForWorkgroupId,
                        rowForWorkgroupIdVector, workgroupColumnCounter);
            }

            //set the latest annotation score and timestamp from any of the teachers
            workgroupColumnCounter = setLatestAnnotationScore(stepWorksForNodeId, rowForWorkgroupId,
                    rowForWorkgroupIdVector, workgroupColumnCounter, nodeId);

            //set the latest annotation comment and timestamp from any of the teachers
            workgroupColumnCounter = setLatestAnnotationComment(stepWorksForNodeId, rowForWorkgroupId,
                    rowForWorkgroupIdVector, workgroupColumnCounter);
        }

        //write the csv row if we are generating a csv file
        writeCSV(rowForWorkgroupIdVector);
    }
}

From source file:org.wise.vle.web.VLEGetXLS.java

License:Open Source License

/**
 * Create the header rows in the sheet//ww w  .  java 2 s  .  c o m
 * Step Title
 * Step Type
 * Step Prompt
 * Node Id
 * Step Extra
 * 
 * @param workbook the excel work book
 * @param nodeIdList a list of nodeIds in the order they appear in the project
 * @param nodeIdToNodeTitlesMap a map of node id to node titles
 * @param nodeIdToNodeContent a map of node id to node content
 */
private void setGetLatestStudentWorkHeaderRows(XSSFWorkbook workbook, List<String> nodeIdList,
        HashMap<String, String> nodeIdToNodeTitlesMap, HashMap<String, JSONObject> nodeIdToNodeContent) {
    XSSFSheet mainSheet = null;

    if (workbook != null) {
        //the workbook is not null which means we are generating an xls file
        mainSheet = workbook.getSheetAt(0);
    }

    int rowCounter = 0;
    int headerColumn = 0;

    //create the step title row
    Row stepTitleRow = createRow(mainSheet, rowCounter++);
    Vector<String> stepTitleRowVector = createRowVector();

    //create the step type row
    Row stepTypeRow = createRow(mainSheet, rowCounter++);
    Vector<String> stepTypeRowVector = createRowVector();

    //create the step prompt row
    Row stepPromptRow = createRow(mainSheet, rowCounter++);
    Vector<String> stepPromptRowVector = createRowVector();

    //create the node id row
    Row nodeIdRow = createRow(mainSheet, rowCounter++);
    Vector<String> nodeIdRowVector = createRowVector();

    //create the step type row
    Row stepExtraRow = createRow(mainSheet, rowCounter++);
    Vector<String> stepExtraRowVector = createRowVector();

    //create 13 empty columns in each of the rows because the first 13 columns are for the user data columns
    for (int x = 0; x < 13; x++) {
        setCellValue(stepTitleRow, stepTitleRowVector, x, "");
        setCellValue(stepTypeRow, stepTypeRowVector, x, "");
        setCellValue(stepPromptRow, stepPromptRowVector, x, "");
        setCellValue(nodeIdRow, nodeIdRowVector, x, "");
        setCellValue(stepExtraRow, stepExtraRowVector, x, "");
    }

    //start on column 13 because the first 13 columns are for the user data columns
    int columnCounter = 13;

    //set the cells that describe each of the rows
    setCellValue(stepTitleRow, stepTitleRowVector, columnCounter, "Step Title");
    setCellValue(stepTypeRow, stepTypeRowVector, columnCounter, "Step Type");
    setCellValue(stepPromptRow, stepPromptRowVector, columnCounter, "Step Prompt");
    setCellValue(nodeIdRow, nodeIdRowVector, columnCounter, "Node Id");
    setCellValue(stepExtraRow, stepExtraRowVector, columnCounter, "Step Extra");

    /*
     * create and populate the row that contains the user data headers such as
     * WorkgroupId, Student Login 1, Student Login 2, etc.
     */
    Row userDataHeaderRow = createRow(mainSheet, rowCounter++);
    Vector<String> userDataHeaderRowVector = createRowVector();
    columnCounter = createUserDataHeaderRow(headerColumn, userDataHeaderRow, userDataHeaderRowVector, true,
            true);

    /*
     * increment the column counter so the student work begins on the next column
     * and not underneath the column that contains the cells above that contain
     * "Step Title", "Step Type", etc.
     */
    columnCounter++;

    /*
     * loop through the node ids to set the step titles, step types,
     * step prompts, node ids, and step extras
     */
    for (int nodeIndex = 0; nodeIndex < nodeIdList.size(); nodeIndex++) {
        //get the node id
        String nodeId = nodeIdList.get(nodeIndex);

        //set the header columns for getLatestWork
        columnCounter = setGetLatestStudentWorkHeaderColumn(stepTitleRow, stepTypeRow, stepPromptRow, nodeIdRow,
                stepExtraRow, stepTitleRowVector, stepTypeRowVector, stepPromptRowVector, nodeIdRowVector,
                stepExtraRowVector, columnCounter, workbook, nodeIdToNodeTitlesMap, nodeIdToNodeContent,
                nodeId);
    }

    //write all of the rows to the csv if we are generating a csv file
    writeCSV(stepTitleRowVector);
    writeCSV(stepTypeRowVector);
    writeCSV(stepPromptRowVector);
    writeCSV(nodeIdRowVector);
    writeCSV(stepExtraRowVector);
    writeCSV(userDataHeaderRowVector);
}

From source file:piecework.export.concrete.ExportAsExcelWorkbookProviderTest.java

License:Educational Community License

@Test
public void test() throws IOException {
    List<Field> fields = new ArrayList<Field>();
    fields.add(new Field.Builder().name("TestValue1").label("Test Label 1").header("Test Header 1").build());
    fields.add(new Field.Builder().name("TestValue2").label("Test Label 2").build());

    Map<String, String> headerMap = ExportUtility.headerMap(fields);

    Date startTime = new Date(1391725603939l);
    Date endTime = new Date(1391725704039l);

    ProcessInstance instance = new ProcessInstance.Builder().processInstanceId("1234")
            .processInstanceLabel("A Simple Test Instance").formValue("TestValue1", "Something")
            .formValue("TestValue2", "Another").startTime(startTime).endTime(endTime).build();

    Pager<ProcessInstance> pager = new ProcessInstanceListPager(Collections.singletonList(instance));
    ExportAsExcelWorkbookProvider provider = new ExportAsExcelWorkbookProvider("Example", headerMap, pager);

    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    provider.write(outputStream);/*from w  w w . ja v a  2  s  .c om*/
    ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray());

    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row headerRow = sheet.getRow(0);

    String[] expectedCellValues = { "ID", "Title", "Test Header 1", "Test Label 2", "Submitted", "Completed" };
    int cellNumber = 0;
    for (String expectedCellValue : expectedCellValues) {
        Cell cell = headerRow.getCell(cellNumber);
        Assert.assertEquals(expectedCellValue, cell.getStringCellValue());
        cellNumber++;
    }

    Row dataRow = sheet.getRow(1);
    expectedCellValues = new String[] { "1234", "A Simple Test Instance", "Something", "Another",
            "Thu Feb 06 14:26:43 PST 2014", "Thu Feb 06 14:28:24 PST 2014" };
    cellNumber = 0;
    for (String expectedCellValue : expectedCellValues) {
        Cell cell = dataRow.getCell(cellNumber);
        Assert.assertEquals(expectedCellValue, cell.getStringCellValue());
        cellNumber++;
    }

}

From source file:pl.exsio.ck.model.reader.XlsxEntryReaderImpl.java

License:Open Source License

private XSSFSheet openSheet(File file) throws IOException {
    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(file));
    XSSFSheet sheet = workbook.getSheetAt(0);
    return sheet;
}

From source file:PlacementFormatter.Controller.FileController.java

/**
 *
 * @param filepath//from   w  ww. java2 s . c o m
 * @throws IOException
 */
public static void formatFile(PlacementFile filepath) throws IOException {

    //Creates instance  for reading xls, workbook,sheet, FileInputStream
    InputStream ExcelFileToRead = new FileInputStream(filepath.getFilepath());
    XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);
    XSSFSheet sheet = wb.getSheetAt(0);

    //Creates instances for writing output to xls format.
    String sheetName = "Import";//name of sheet
    HSSFWorkbook outWorkbook = new HSSFWorkbook();
    HSSFSheet outSheet = outWorkbook.createSheet(sheetName);

    //Variables to hold the data without ' and r for the row counter
    String cellReplace;
    int r = 0;

    //Outer and Inner loop for iterating through the workbook
    for (Row row : sheet) {

        HSSFRow outRow = outSheet.createRow(r);

        for (int cn = 0; cn < row.getLastCellNum(); cn++) {
            // If the cell is missing from the file, generate a blank one
            // (Works by specifying a MissingCellPolicy)
            org.apache.poi.ss.usermodel.Cell cell = row.getCell(cn, Row.CREATE_NULL_AS_BLANK);

            //NumberToTextConverter nc = new NumberToTextConverter.toText();
            //or
            DataFormatter df = new DataFormatter();
            String dataCell = df.formatCellValue(cell);

            //Replaces the single dash located in the data

            cellReplace = dataCell.replace("'", "");

            HSSFCell outCell = outRow.createCell(cn);
            outCell.setCellValue(cellReplace);
            //System.out.println("CELL: " + cn + " --> " + cellReplace);

        } //ends inner loop
        r++;
    } //ends outer loop

    FileOutputStream fileOut = new FileOutputStream(filepath.getFilepath().replace("xlsx", "xls"));
    outWorkbook.write(fileOut);
    fileOut.flush();
    System.out.print("File Exported Correclty");

}