Example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getRow

Introduction

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

Prototype

@Override
public XSSFRow getRow(int rownum)

Source Link

Document

Returns the logical row ( 0-based).

Usage

From source file:com.gtja.qh.TransferCtrl.TransferCtrl.java

private boolean transferToDbf(String inputFilePath, String outFileDir) throws DBFException {

    //DBF,?/*from w  ww. jav a2  s  . c o  m*/
    DBFField[] fields = new DBFField[6];

    fields[0] = new DBFField();
    fields[0].setName("ACCOUNTID");
    fields[0].setDataType(DBFField.FIELD_TYPE_C);
    fields[0].setFieldLength(6);

    fields[1] = new DBFField();
    fields[1].setName("PARTID");
    fields[1].setDataType(DBFField.FIELD_TYPE_C);
    fields[1].setFieldLength(4);

    fields[2] = new DBFField();
    fields[2].setName("CLIENTID");
    fields[2].setDataType(DBFField.FIELD_TYPE_C);
    fields[2].setFieldLength(8);

    fields[3] = new DBFField();
    fields[3].setName("AMOUNT");
    fields[3].setDataType(DBFField.FIELD_TYPE_N);
    fields[3].setFieldLength(23);
    fields[3].setDecimalCount(2);

    fields[4] = new DBFField();
    fields[4].setName("MONEYTYPE");
    fields[4].setDataType(DBFField.FIELD_TYPE_C);
    fields[4].setFieldLength(4);

    fields[5] = new DBFField();
    fields[5].setName("TYPEMEMO");
    fields[5].setDataType(DBFField.FIELD_TYPE_C);
    fields[5].setFieldLength(40);
    DBFWriter writer = new DBFWriter();
    try {
        writer.setFields(fields);
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
    //excel??
    File inputFile = new File(inputFilePath);
    String inputFileName = inputFile.getName();
    String extension = inputFileName.lastIndexOf(".") == -1 ? ""
            : inputFileName.substring(inputFileName.lastIndexOf(".") + 1);
    if ("xls".equals(extension)) {
        //JXL?excel 2003??xlsx?
        try {
            //?excel
            InputStream is = new FileInputStream(inputFilePath);
            jxl.Workbook rwb = jxl.Workbook.getWorkbook(is);
            jxl.Sheet rs = rwb.getSheet(0);
            int rsRows = rs.getRows();
            for (int i = 1; i < rsRows; i++) {
                if (rs.getCell(5, i).getContents().equals("")) {
                    Object[] rowData = new Object[6];
                    rowData[0] = "000101";
                    rowData[1] = "0001";
                    rowData[4] = "A999";
                    String tradeCode = rs.getCell(4, i).getContents();
                    Double amount = null;
                    if (rs.getCell(6, i).getType() == CellType.NUMBER) {
                        NumberCell numberCell = (NumberCell) rs.getCell(6, i);
                        amount = numberCell.getValue();
                    } else {
                        if (rs.getCell(6, i).getContents().length() == 0) {
                            amount = null;
                        } else {
                            amount = new DecimalFormat("0.00").parse(rs.getCell(6, i).getContents())
                                    .doubleValue(); //String?Double 
                        }

                    }
                    String typeMemo = rs.getCell(7, i).getContents();
                    rowData[2] = tradeCode;
                    rowData[3] = amount;
                    rowData[5] = typeMemo;
                    writer.addRecord(rowData);
                } else {
                    continue;
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    } else if ("xlsx".equals(extension)) {
        //POI ?excel 2007,??excel 2003
        try {
            InputStream fs = new FileInputStream(inputFilePath);
            XSSFWorkbook wb;
            wb = new XSSFWorkbook(fs);
            //wb = new XSSFWorkbook(inputFilePath);
            XSSFSheet sheet = wb.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rows; i++) {

                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (row.getCell(5).getStringCellValue().equals("")) {
                    Object[] rowData = new Object[6];
                    rowData[0] = "000101";
                    rowData[1] = "0001";
                    rowData[4] = "A999";
                    if (row.getCell(4) == null) {
                        row.createCell(4);
                        row.getCell(4).setCellValue("");
                    }
                    if (row.getCell(6) == null) {
                        row.createCell(6);
                        row.getCell(6).setCellValue("");
                    }
                    if (row.getCell(7) == null) {
                        row.createCell(7);
                        row.getCell(7).setCellValue("");
                    }
                    String tradeCode = row.getCell(4).getStringCellValue();
                    Double amount = null;
                    if (row.getCell(6).getCellType() == CELL_TYPE_NUMERIC) {
                        amount = row.getCell(6).getNumericCellValue();
                    } else {
                        row.getCell(6).setCellType(CELL_TYPE_STRING);
                        if (row.getCell(6).getStringCellValue().length() == 0) {
                            amount = null;
                        } else {
                            amount = new DecimalFormat("0.00").parse(row.getCell(6).getStringCellValue())
                                    .doubleValue();
                        }
                    }
                    String typeMemo = row.getCell(7).getStringCellValue();
                    rowData[2] = tradeCode;
                    rowData[3] = amount;
                    rowData[5] = typeMemo;
                    writer.addRecord(rowData);
                } else {
                    continue;
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    try {
        //
        String fileName = "0001_SG01_" + frame.getDate().getText() + "_1_ClientCapitalDetail";
        String outFile = outFileDir + "\\" + fileName + ".dbf";
        File file = new File(outFile);
        if (!file.exists()) {
            file.createNewFile();
        }
        OutputStream os = new FileOutputStream(file);
        writer.write(os); //? 
        os.close();
        return true;
    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, "???",
                "?", JOptionPane.ERROR_MESSAGE);
        return false;
    }
}

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

private void exportFile(List<String> list, String rootPath, HttpServletResponse response,
        List<List<Object>> bodyList) throws FileNotFoundException, IOException {
    String importFileName = rootPath + "WEB-INF" + File.separator + "xlsx" + File.separator + "order.xlsx";
    String tmpDir = rootPath + "tmpDir" + File.separator;
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(importFileName));
    this.styles = createStyles(wb);
    //?excel?  /*from   w w w  .ja va  2  s  . c om*/
    //??sheet  
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFCell cell1 = sheet.getRow(1).getCell(0);
    String cell1str = cell1.getStringCellValue();
    //????
    cell1str = cell1str.replace("X", list.get(0));
    cell1.setCellValue(cell1str);
    XSSFCell cell2 = sheet.getRow(2).getCell(0);
    String cell2str = cell2.getStringCellValue();
    //??
    cell2str = cell2str.replace("X", list.get(1));
    //?
    cell2str = cell2str.replace("Y", list.get(2));
    //???
    cell2str = cell2str.replace("Z", list.get(3));
    cell2.setCellValue(cell2str);
    XSSFCell cell3 = sheet.getRow(3).getCell(12);
    String cell3str = cell3.getStringCellValue();
    cell3str = cell3str.replace("XX", list.get(4));
    cell3.setCellValue(cell3str);

    XSSFCell cell4 = sheet.getRow(sheet.getLastRowNum() - 1).getCell(0);
    String cell4str = cell4.getStringCellValue();
    cell4str = cell4str.replace("X", list.get(5));
    cell4.setCellValue(cell4str);

    XSSFCell cell5 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(12);
    XSSFCell cell6 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(13);
    String str1 = String.valueOf(list.get(6));
    String str2 = String.valueOf(list.get(7));
    str1 = str1.substring(0, str1.indexOf("."));
    str2 = str2.substring(0, str2.indexOf("."));
    cell5.setCellValue(str1);

    cell6.setCellValue(str2);

    XSSFCell cell7 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(12);
    XSSFCell cell8 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(13);
    cell7.setCellValue(Double.parseDouble(list.get(6)) / 10);
    cell8.setCellValue(Double.parseDouble(list.get(7)) / 10);
    //                 int columnNum =sheet.getRow(3).getLastCellNum();
    //                 int column =0;
    //                 for(int i=0;i<columnNum;i++)
    //                 {
    //                    System.out.println("==="+sheet.getRow(3).getCell(i).getStringCellValue());
    //                    column =i;
    //                 }

    int num = 5;
    for (int i = 0; i < bodyList.size(); i++) {
        List<Object> strList = bodyList.get(i);
        sheet.shiftRows(num, bodyList.size() + num, 1, true, false);
        sheet.createRow(num);

        for (int j = 0; j < strList.size(); j++) {
            Row row = sheet.getRow(num);
            addCell(row, j, strList.get(j));
            //                       Cell cell = row.createCell(j);
            //                       cell.setCellValue(strList.get(j));
        }
        num++;
    }

    //??  
    String tmpName = tmpDir + "tmp_hmd.xlsx";
    File dirFile = new File(tmpDir);
    if (!dirFile.exists()) {
        dirFile.mkdir();
    }
    File tmpFile = new File(tmpName);
    FileOutputStream fos = new FileOutputStream(tmpFile);
    wb.write(fos);

    XSSFWorkbook wb1 = new XSSFWorkbook(new FileInputStream(tmpName));
    response.reset();
    response.setContentType("application/octet-stream; charset=utf-8");
    response.setHeader("Content-Disposition",
            "attachment; filename=" + Encodes.urlEncode("?" + list.get(0) + "?.xlsx"));
    wb1.write(response.getOutputStream());

    tmpFile.delete();
}

From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java

License:MIT License

/**
 * Utility function to update a scatterplot line's data series.
 * @param data the datagraph data.//from w  ww  . jav  a  2  s .  co  m
 * @param sheet the Excel sheet which contains corresponding data from the scatterplot data series.
 * @param seriesIdx the index of the data in the dategraph data.
 * @param series the XML object representing the series in the chart.
 */
private static void updateCTScatterSer(final DategraphData data, final XSSFSheet sheet, final int seriesIdx,
        final CTScatterSer series) {
    final String sheetName = sheet.getSheetName();

    // the series idx starts from 0
    final DategraphData.Row row = data.getRows().get(seriesIdx);
    final String title = row.getLabel();
    final Color color = Color.decode(row.getColor());

    series.getOrder().setVal(seriesIdx);
    series.getIdx().setVal(seriesIdx);

    final CTSolidColorFillProperties fill = series.getSpPr().getLn().getSolidFill();

    // We have to set any possible colour type, PowerPoint throws an error if there's multiple fills, and we don't
    //   know what colour type the user may have used in their template slide.
    if (fill.getSchemeClr() != null) {
        fill.unsetSchemeClr();
    }
    if (fill.getSrgbClr() != null) {
        fill.unsetSrgbClr();
    }
    if (fill.getHslClr() != null) {
        fill.unsetHslClr();
    }
    if (fill.getPrstClr() != null) {
        fill.unsetPrstClr();
    }
    if (fill.getScrgbClr() != null) {
        fill.unsetScrgbClr();
    }
    if (fill.getSysClr() != null) {
        fill.unsetSysClr();
    }

    final CTSRgbColor fillClr = fill.addNewSrgbClr();
    final byte[] colorBytes = { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() };
    fillClr.setVal(colorBytes);

    final CTMarker marker = series.getMarker();

    if (marker != null) {
        final CTShapeProperties markerSpPr = marker.getSpPr();
        unsetSpPrFills(markerSpPr);
        markerSpPr.addNewSolidFill().addNewSrgbClr().setVal(colorBytes);

        final CTLineProperties markerLn = markerSpPr.getLn();
        if (markerLn != null) {
            unsetLineFills(markerLn);
            markerLn.addNewSolidFill().addNewSrgbClr().setVal(colorBytes);
        }
    }

    final CTStrRef strRef = series.getTx().getStrRef();
    strRef.getStrCache().getPtArray()[0].setV(title);

    strRef.setF(new CellReference(sheetName, 0, seriesIdx + 1, true, true).formatAsString());

    final long[] timestamps = data.getTimestamps();
    {
        final CTNumRef timestampCatNumRef = series.getXVal().getNumRef();
        timestampCatNumRef.setF(new AreaReference(new CellReference(sheetName, 1, 0, true, true),
                new CellReference(sheetName, 1 + timestamps.length, 0, true, true)).formatAsString());

        final CTNumData timeStampCatNumCache = timestampCatNumRef.getNumCache();
        timeStampCatNumCache.getPtCount().setVal(timestamps.length);
        timeStampCatNumCache.setPtArray(null);

        for (int ii = 0; ii < timestamps.length; ++ii) {
            final CTNumVal pt = timeStampCatNumCache.addNewPt();
            pt.setIdx(ii);
            pt.setV(sheet.getRow(1 + ii).getCell(0).getRawValue());
        }
    }

    {
        final double[] seriesData = row.getValues();

        final CTNumRef valuesNumRef = series.getYVal().getNumRef();
        valuesNumRef.setF(new AreaReference(new CellReference(sheetName, 1, seriesIdx + 1, true, true),
                new CellReference(sheetName, 1 + timestamps.length, seriesIdx + 1, true, true))
                        .formatAsString());

        final CTNumData valuesNumCache = valuesNumRef.getNumCache();
        valuesNumCache.getPtCount().setVal(timestamps.length);
        valuesNumCache.setPtArray(null);

        for (int ii = 0; ii < timestamps.length; ++ii) {
            final CTNumVal pt = valuesNumCache.addNewPt();
            pt.setIdx(ii);
            pt.setV(Double.toString(seriesData[ii]));
        }
    }
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * Generate the preview of the xls grid/* w ww  .ja v  a2s . c o m*/
 * @param filename
 * @param sheetNo
 * @return
 * @throws FileNotFoundException
 * @throws IOException
 * @throws JSONException
 */
public static JSONObject parseXLSX(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    FileInputStream fs = new FileInputStream(filename);
    XSSFWorkbook wb = new XSSFWorkbook(fs);
    XSSFSheet sheet = wb.getSheetAt(sheetNo);
    //DateFormat sdf = new SimpleDateFormat(df);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }

    JSONArray jArr = new JSONArray();
    try {
        for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
            XSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i == 0) {
                maxCol = row.getLastCellNum();
            }
            for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                XSSFCell cell = row.getCell(cellcount);
                CellReference cref = new CellReference(i, cellcount);
                String colHeader = cref.getCellRefParts()[2];
                String val = null;

                if (cell != null) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            val = Long.toString(cell.getDateCellValue().getTime());
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                }

                if (i == 0) { // List of Headers (Consider first row as Headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val == null ? "" : val);
                        jtemp1.put("index", cellcount);
                        jobj.append("Header", jtemp1);
                    }
                }
                obj.put(colHeader, val);
            }
            //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
            jArr.put(obj);
            //                    }
        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLSX has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * @param filename/*from www  .ja v  a  2s .  c  o  m*/
 * @param sheetNo
 * @param startindex
 * @param importDao
 * @return
 * @throws ServiceException
 */
public static void dumpXLSXFileData(String filename, int sheetNo, int startindex, ImportDAO importDao,
        HibernateTransactionManager txnManager) throws ServiceException {
    boolean commitedEx = false;
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("import_Tx");
    def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
    TransactionStatus status = txnManager.getTransaction(def);
    Session session = txnManager.getSessionFactory().getCurrentSession();
    try {
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        FileInputStream fs = new FileInputStream(destinationDirectory + "/" + filename);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet = wb.getSheetAt(sheetNo);
        //DateFormat sdf = new SimpleDateFormat(df_full);
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        String tableName = importDao.getTableName(filename);
        int flushCounter = 0;
        for (int i = startindex; i <= maxRow; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum(); //Column Count
            }
            ArrayList<String> dataArray = new ArrayList<String>();
            JSONObject dataObj = new JSONObject();
            for (int j = 0; j < maxCol; j++) {
                XSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    dataArray.add(val);
                    continue;
                }
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        val = Long.toString(cell.getDateCellValue().getTime());
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case XSSFCell.CELL_TYPE_STRING:
                    val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                dataObj.put(colHeader, val);
                dataArray.add(val); //Collect row data
            }
            //Insert Query
            if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                importDao.dumpFileRow(tableName, dataArray.toArray());
                if (flushCounter % 30 == 0) {
                    session.flush();
                    session.clear();
                }
                flushCounter++;
            }

        }
        try {
            txnManager.commit(status);
        } catch (Exception ex) {
            commitedEx = true;
            throw ex;
        }
    } catch (IOException ex) {
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    } catch (Exception ex) {
        if (!commitedEx) { //if exception occurs during commit then dont call rollback
            txnManager.rollback(status);
        }
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    }
}

From source file:com.mycompany.fyp_emr.ExcelReaders.java

public static Diagnostic_Algorithm ReadFullGraph(String file) throws IOException {
    Diagnostic_Algorithm DA = new Diagnostic_Algorithm();

    FileInputStream fs = new FileInputStream(file);
    XSSFWorkbook wb = new XSSFWorkbook(fs);
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFRow row;/* w  ww  .ja v  a 2  s.c  o  m*/
    int id, id1, id2;
    String label, type, color, available, label_e;

    Node n;
    Edge e;
    LinkedList<Edge> edges = new LinkedList<Edge>();

    int rows = sheet.getPhysicalNumberOfRows();

    for (int r = 2; r < rows; r++) {
        row = sheet.getRow(r);
        if (row != null) {
            if (row.getCell(0) != null) {
                row.getCell(0).setCellType(Cell.CELL_TYPE_NUMERIC);
                id = (int) row.getCell(0).getNumericCellValue();
                if (id != 0) {
                    label = row.getCell(1).getStringCellValue();
                    type = row.getCell(2).getStringCellValue();
                    color = row.getCell(3).getStringCellValue();
                    available = row.getCell(4).getStringCellValue();

                    n = new Node();
                    n.setID(id);
                    n.setLabel(label);
                    n.setType(type);
                    n.setAvailable(available);
                    n.setColor(color);

                    DA.addNode(n);
                }
            }
            if (row.getCell(6) != null) {
                row.getCell(6).setCellType(Cell.CELL_TYPE_NUMERIC);
                row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC);
                id1 = (int) row.getCell(6).getNumericCellValue();
                id2 = (int) row.getCell(7).getNumericCellValue();
                label_e = row.getCell(8).getStringCellValue();

                e = new Edge(id1, id2);
                e.setLabel(label_e);
                edges.add(e);
            }
        }
    }
    for (int i = 0; i < edges.size(); i++)
        DA.addEdge(edges.get(i));

    return DA;
}

From source file:com.mycompany.owl.fxml.FXMLFilterController.java

@FXML
public void saveFileTransformed() throws FileNotFoundException, IOException {
    FileInputStream fileInputStream = new FileInputStream(file);
    XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbookToModify.getSheetAt(0);
    XSSFRow row;//from  w  w  w  . jav a2  s  . c  o m
    String atcMask = getATCMask();
    ArrayList<String> firstRowCells = new ArrayList<>();
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            System.out.print(row.getCell(j).getRawValue() + "\t");
            if (i == 0)
                firstRowCells.add(row.getCell(j).getRawValue());
        }
        System.out.println("");
    }

    XSSFWorkbook transformedWB = new XSSFWorkbook();
    transformedWB.createSheet();
    XSSFSheet transformedS = transformedWB.getSheetAt(0);
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        transformedS.createRow(i);
    }
    /*
    elkezdnk vgigmenni az alap sheeten
    ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre
    */
    ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells);
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int sum = 0;
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (i != 0 && j != 0) {
                for (int index : matchingIndexes) {
                    if (index == j) {
                        sum += Integer.valueOf(row.getCell(j).getRawValue());
                    }
                }
            }
        }
        System.out.println(sum);
        if (i > 0) {
            row = sheet.getRow(i);
            row.createCell(row.getLastCellNum()).setCellValue(sum);
        }
    }
    for (int index : matchingIndexes) {
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            row.getCell(index).setCellValue(3.14159);
        }
        /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
        if(i == index){
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                row = sheet.getRow(j);
                transformedS.getRow(j).createCell(transformedColumnCount).setCellValue(
                        row.getCell(i).getRawValue()
                );
            }
            transformedColumnCount++;
        }
        }*/
    }
    int columnsInTransformed = 0;
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            String cellValue = row.getCell(j).getRawValue();
            if (!cellValue.equals("3.14159")) {
                transformedS.getRow(i).createCell(columnsInTransformed);
                transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue);
                columnsInTransformed++;
            }
        }
        columnsInTransformed = 0;
    }

    row = transformedS.getRow(0);
    row.createCell(row.getLastCellNum()).setCellValue(atcMask);

    File file = fileChooser.showSaveDialog(new Stage());
    if (file != null) {
        try {
            FileOutputStream fop = new FileOutputStream(file);
            transformedWB.write(fop);
            fop.close();
        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
    }

}

From source file:com.mycompany.owl.fxml.FXMLFilterController.java

@FXML
public void saveFileFiltered() throws FileNotFoundException, IOException {
    FileInputStream fileInputStream = new FileInputStream(file);
    XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbookToModify.getSheetAt(0);
    XSSFRow row;/* w  w  w.  j ava 2s. c om*/
    String atcMask = getATCMask();
    ArrayList<String> firstRowCells = new ArrayList<>();
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            System.out.print(row.getCell(j).getRawValue() + "\t");
            if (i == 0)
                firstRowCells.add(row.getCell(j).getRawValue());
        }
        System.out.println("");
    }
    row = sheet.getRow(0);
    row.createCell(row.getLastCellNum()).setCellValue("ATC mask:");
    row.createCell(row.getLastCellNum()).setCellValue(atcMask);

    ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells);
    System.out.println("SUMS");
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int sum = 0;
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (i != 0 && j != 0) {
                for (int index : matchingIndexes) {
                    if (index == j) {
                        sum += Integer.valueOf(row.getCell(j).getRawValue());
                    }
                }
            }
        }
        System.out.println(sum);
        if (i > 0) {
            row = sheet.getRow(i);
            row.createCell(row.getLastCellNum() + 1).setCellValue(sum);
        }
    }

    File file = fileChooser.showSaveDialog(new Stage());
    if (file != null) {
        try {
            FileOutputStream fop = new FileOutputStream(file);
            workbookToModify.write(fop);
            fop.close();
        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
    }
}

From source file:com.niles.excel2json.objects.ExcelFile.java

License:Open Source License

public void process() throws Exception {
    File excel = new File(path);
    FileInputStream fis = SystemTools.getFileInputStream(excel);

    XSSFWorkbook wb = null;/*from  w w w.j a  va  2 s.  co  m*/

    try {
        wb = new XSSFWorkbook(fis);
    } catch (IOException ex) {
        logger.error("Unable to process file [{}]\r\n{}", path, ex.getMessage());
        return;
    }

    this.fileName = excel.getName().replace(StringConstants.XLSX, "");
    this.folderPath = fileName + " Files";

    folderPath = SystemTools.createFolder(folderPath);

    int sheetCount = wb.getNumberOfSheets();

    int currentSheetNumber = 0;
    int rowCount = 0;
    int columnCount = 0;

    HashMap<Integer, String> headers = new HashMap<Integer, String>();

    /*
     * Itterate through the Excel sheets here and convert them to JSON
     */
    while (currentSheetNumber < sheetCount) {
        XSSFSheet current = wb.getSheetAt(currentSheetNumber);
        sheetName = current.getSheetName();

        // System.out.println(sheetName);
        if (current.getRow(0) == null) {
            // logger.error("[{}] Sheet contains no data", sheetName);
        } else {
            //logger.info("[{}] Processing sheet", sheetName);
            rowCount = current.getLastRowNum() + 1;
            columnCount = current.getRow(0).getPhysicalNumberOfCells();

            // System.out.println("Col Count: " + columnCount);
            // System.out.println("Row Count: " + rowCount);
            JSONArray myJSONArray = new JSONArray();
            JSONObject currentJSONObject = null;
            for (int a = 0; a < rowCount; a++) {
                XSSFRow currentRow = current.getRow(a);

                if (a == 0) {
                    logger.info("[{}] Loading header information", sheetName);
                    for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) {
                        XSSFCell currentCell = currentRow.getCell(currentCellNumber);

                        String header = "Header" + currentCellNumber;

                        if (currentCell != null) {
                            if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                header = currentCell.getStringCellValue();
                            }

                            if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                header = currentCell.getNumericCellValue() + "";
                            }
                        }

                        headers.put(currentCellNumber, header.replaceAll(" ", ""));
                    }
                } else {
                    currentJSONObject = new JSONObject();

                    for (int currentCellNumber = 0; currentCellNumber < columnCount; currentCellNumber++) {
                        XSSFCell currentCell = currentRow.getCell(currentCellNumber);

                        String value = "";

                        if (currentCell != null) {
                            if (currentCell.getCellType() != XSSFCell.CELL_TYPE_ERROR
                                    && currentCell.getCellType() != XSSFCell.CELL_TYPE_FORMULA) {
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                                    value = currentCell.getStringCellValue();
                                }
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                                    value = currentCell.getNumericCellValue() + "";
                                }
                                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
                                    value = currentCell.getBooleanCellValue() + "";
                                }

                                if (value == null) {
                                    value = "";
                                }
                            }
                        }

                        currentJSONObject.put(headers.get(currentCellNumber), value);
                    }
                    myJSONArray.add(currentJSONObject);
                }
            }
            writeToJson(myJSONArray);
        }
        currentSheetNumber++;
    }
}

From source file:com.photon.phresco.framework.commons.FrameworkUtil.java

License:Apache License

public boolean deleteTestCasesFromXLSX(String filePath, String testSuiteName, String testCaseId) {
    boolean hasTrue = false;
    List<TestCase> testCases = new ArrayList<TestCase>();
    TestCase tstCase = new TestCase();
    Iterator<Row> rowIterator;
    try {//w w  w .  ja  va2  s . co m
        FileInputStream myInput = new FileInputStream(filePath);

        OPCPackage opc = OPCPackage.open(myInput);

        XSSFWorkbook myWorkBook = new XSSFWorkbook(opc);
        int numberOfSheets = myWorkBook.getNumberOfSheets();
        for (int j = 0; j < numberOfSheets; j++) {
            XSSFSheet mySheet = myWorkBook.getSheetAt(j);
            if (mySheet.getSheetName().equals(testSuiteName)) {
                rowIterator = mySheet.rowIterator();
                for (int i = 0; i <= 23; i++) {
                    rowIterator.next();
                }
                while (rowIterator.hasNext()) {
                    Row next = rowIterator.next();
                    if (testCaseId.equalsIgnoreCase(getValue(next.getCell(3)))) {
                        mySheet.removeRow(next);
                        int rowNum = next.getRowNum();
                        int newNum = rowNum + 1;
                        XSSFRow row = mySheet.getRow(newNum);
                        if (row != null) {
                            mySheet.shiftRows(newNum, mySheet.getLastRowNum(), -1);
                        }
                        hasTrue = true;
                        break;
                    }
                }
            }
        }
        if (hasTrue) {
            for (int j = 0; j < numberOfSheets; j++) {
                XSSFSheet myXSSFSheet = myWorkBook.getSheetAt(j);
                if (myXSSFSheet.getSheetName().equals(testSuiteName)) {
                    rowIterator = myXSSFSheet.rowIterator();
                    for (int i = 0; i <= 23; i++) {
                        rowIterator.next();
                    }
                    while (rowIterator.hasNext()) {
                        Row next = rowIterator.next();
                        TestCase createObject = readTest(next);
                        testCases.add(createObject);
                    }
                    float totalPass = 0;
                    float totalFail = 0;
                    float totalNotApplicable = 0;
                    float totalBlocked = 0;
                    int totalTestCases = testCases.size();
                    for (TestCase testCase : testCases) {
                        String testCaseStatus = testCase.getStatus();
                        if (testCaseStatus.equalsIgnoreCase("success")) {
                            totalPass = totalPass + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("failure")) {
                            totalFail = totalFail + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("notApplicable")) {
                            totalNotApplicable = totalNotApplicable + 1;
                        } else if (testCaseStatus.equalsIgnoreCase("blocked")) {
                            totalBlocked = totalBlocked + 1;
                        }
                    }

                    if (tstCase.getStatus().equalsIgnoreCase("success")) {
                        totalPass = totalPass - 1;
                    } else if (tstCase.getStatus().equalsIgnoreCase("failure")) {
                        totalFail = totalFail - 1;
                    } else if (tstCase.getStatus().equalsIgnoreCase("notApplicable")) {
                        totalNotApplicable = totalNotApplicable - 1;
                    } else if (tstCase.getStatus().equalsIgnoreCase("blocked")) {
                        totalBlocked = totalBlocked - 1;
                    }

                    XSSFSheet mySheet1 = myWorkBook.getSheetAt(0);
                    rowIterator = mySheet1.rowIterator();
                    for (int i = 0; i <= 2; i++) {
                        rowIterator.next();
                    }
                    while (rowIterator.hasNext()) {
                        Row next1 = rowIterator.next();
                        if (StringUtils.isNotEmpty(getValue(next1.getCell(2)))
                                && !getValue(next1.getCell(2)).equalsIgnoreCase("Total")) {
                            TestSuite createObject = createObject(next1);
                            if (StringUtils.isNotEmpty(tstCase.getTestCaseId())
                                    && createObject.getName().equals(testSuiteName)) {
                                updateIndex(totalPass, totalFail, totalNotApplicable, totalBlocked, next1,
                                        totalTestCases, "delete");
                            }
                        }
                    }
                }
            }

            myInput.close();
            FileOutputStream outFile = new FileOutputStream(filePath);
            myWorkBook.write(outFile);
            outFile.close();
        }
    } catch (Exception e) {

    }

    return hasTrue;
}