Example usage for org.apache.poi.xssf.usermodel XSSFSheet 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:uk.co.spudsoft.birt.emitters.excel.tests.Issue55GroupsNotWorkingCorrectly.java

License:Open Source License

@Test
public void testFooterHierarchy() throws Exception {

    debug = false;/* w  ww  . j a va  2 s.  com*/
    groupSummaryHeader = false;
    InputStream inputStream = runAndRenderReport("Issue55GroupHierarchyBelow.rptdesign", "xlsx");
    assertNotNull(inputStream);
    try {
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        assertNotNull(workbook);

        assertEquals(1, workbook.getNumberOfSheets());
        assertEquals(!groupSummaryHeader, workbook.getSheetAt(0).getRowSumsBelow());

        XSSFSheet sheet0 = workbook.getSheetAt(0);

        assertEquals(!groupSummaryHeader, sheet0.getRowSumsBelow());

        /*         for( int i = 0; i < 64; ++i ) {
                    System.out.println( "assertEquals( " + sheet0.getRow( i ).getCTRow().getOutlineLevel() + ", sheet0.getRow( " + i + " ).getCTRow().getOutlineLevel() );" );
                 }
        */
        assertEquals(0, sheet0.getRow(0).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(1).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(2).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(3).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(4).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(5).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(6).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(7).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(8).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(9).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(10).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(11).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(12).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(13).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(14).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(15).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(16).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(17).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(18).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(19).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(20).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(21).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(22).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(23).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(24).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(25).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(26).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(27).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(28).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(29).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(30).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(31).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(32).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(33).getCTRow().getOutlineLevel());
        assertEquals(0, sheet0.getRow(34).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(35).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(36).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(37).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(38).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(39).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(40).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(41).getCTRow().getOutlineLevel());
        assertEquals(0, sheet0.getRow(42).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(43).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(44).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(45).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(46).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(47).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(48).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(49).getCTRow().getOutlineLevel());
        assertEquals(0, sheet0.getRow(50).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(51).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(52).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(53).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(54).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(55).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(56).getCTRow().getOutlineLevel());
        assertEquals(1, sheet0.getRow(57).getCTRow().getOutlineLevel());
        assertEquals(0, sheet0.getRow(58).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(59).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(60).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(61).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(62).getCTRow().getOutlineLevel());
        assertEquals(2, sheet0.getRow(63).getCTRow().getOutlineLevel());

    } finally {
        inputStream.close();
    }

}

---

From source file:update2viva.ConvertXLSX.java

static void readXlsx(File inputFile, String outputfile)
        throws FileNotFoundException, UnsupportedEncodingException, IOException {
    String[] args;//from  w w  w.  j  a  v a 2  s . co  m
    //File to store data in form of CSV
    File f = new File(outputfile + "\\out_.csv");

    OutputStream os = (OutputStream) new FileOutputStream(f);
    String encoding = "ISO-8859-1";
    OutputStreamWriter osw = new OutputStreamWriter(os, encoding);
    BufferedWriter bw = new BufferedWriter(osw);

    // Get the workbook instance for XLSX file
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));

    // Get first sheet from the workbook
    XSSFSheet sheet = wb.getSheetAt(0);

    Row row;
    Cell cell;

    String acrow = "";
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);

        for (int j = 0; j < row.getLastCellNum(); j++) {
            if (!(row.getCell(j) == null)) {
                switch (row.getCell(j).getCellType()) {

                case Cell.CELL_TYPE_BOOLEAN:
                    acrow = "" + row.getCell(j).getBooleanCellValue();
                    break;

                case Cell.CELL_TYPE_NUMERIC:
                    acrow = "" + row.getCell(j).getNumericCellValue();
                    break;

                case Cell.CELL_TYPE_STRING:
                    acrow = row.getCell(j).getStringCellValue();
                    break;

                case Cell.CELL_TYPE_BLANK:
                    System.out.println(" ");
                    break;
                }
                // acrow=row.getCell(j).getStringCellValue();
                if (acrow.contains("\n"))
                    acrow = acrow.replaceAll("\n", "");

                if (!(acrow.contains("\n")))

                {
                    bw.write(acrow + ";");
                }
            }
            if (row.getCell(j) == null) {
                bw.write(';');
            }
        }

        bw.newLine();
    }

    bw.flush();
    bw.close();
    inputFile.delete();
}

---

From source file:Utilities.BatchInDJMSHelper.java

public void compareXlsxBatch(String xlsxFileName, Map<String, Map<String, String>> legitLvMap) {
    //Map<String, Map<String, String>> LegitLvMap = 
    File xlsxFile = new File(xlsxFileName);
    try {/*  w  w  w .  j av  a  2 s .c  o m*/
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        legendBuilder(myWorkBook);
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            Iterator<Cell> it = headerRow.cellIterator();
            int numCell = 0;
            // List<String> keyList = new ArrayList<>(); //keep track info of each column
            while (it.hasNext()) {
                //keyList.add(it.next().getStringCellValue());   
                it.next();
                numCell++;
            }

            if (numCell == GlobalVar.LEAVE_TITLES_V1.length || numCell == GlobalVar.LEAVE_TITLES_V2.length) { // correct xlsx file                 
                int rowNum = 1;
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    //row.getRowStyle();
                    rowNum++;
                }
                for (int i = 1; i < rowNum; i++) {
                    Row row = mySheet.getRow(i);
                    foregroundColorSetUp(row, myWorkBook, numCell, legitLvMap); //check each row and update foreground color
                }
                fis.close();
                FileOutputStream output;

                String targetFile = null;
                if (xlsxFileName.contains(".xlsx")) {
                    targetFile = xlsxFileName.replace(".xlsx", "COLORED.xlsx");
                } else {
                    targetFile = xlsxFileName + "COLORED.xlsx";
                }
                output = new FileOutputStream(targetFile);
                myWorkBook.write(output);
                output.close();

            } else {
                JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!");
            }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        JOptionPane.showMessageDialog(null,
                "The leave roster is colored successfully. Please check *COLORED.xlsx.\n");

        //                        case DUPLICATE_LV_ERR: return IndexedColors.YELLOW.getIndex();
        //            case INPROCESSING_ERR: return IndexedColors.PINK.getIndex();
        //            case AFTER_PCS_ERR: return IndexedColors.LAVENDER.getIndex();
        //            case PCS_ERR: return IndexedColors.BLUE.getIndex();
        //            case DUPLICATE_CTRL_NUM_ERR: return IndexedColors.DARK_YELLOW.getIndex();
        // finds the work book in stance for XLSX file
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!");
        Logger.getLogger(BatchInDJMS.class.getName()).log(Level.SEVERE, null, ex);
    }
}

---

From source file:Utilities.BatchInDJMSHelper.java

private void legendBuilder(XSSFWorkbook myWorkBook) {
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    Row row1;/*from www.j a v a2  s .  c om*/
    Row row2;
    Row row3;
    Row row4;
    System.out.println("BatchInDJMSHelper.java: Line number in xlsx" + mySheet.getPhysicalNumberOfRows());
    // Create a row and put some cells in it.
    if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y) {
        row1 = mySheet.getRow(GlobalVar.LEGEND_Y);
    } else {
        row1 = mySheet.createRow(GlobalVar.LEGEND_Y);
    }

    if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 1) {
        row2 = mySheet.getRow(GlobalVar.LEGEND_Y + 1);
    } else {
        row2 = mySheet.createRow(GlobalVar.LEGEND_Y + 1);
    }

    if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 2) {
        row3 = mySheet.getRow(GlobalVar.LEGEND_Y + 2);
    } else {
        row3 = mySheet.createRow(GlobalVar.LEGEND_Y + 2);
    }

    if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 3) {
        row4 = mySheet.getRow(GlobalVar.LEGEND_Y + 3);
    } else {
        row4 = mySheet.createRow(GlobalVar.LEGEND_Y + 3);
    }
    // Row row2 = mySheet.getRow(GlobalVar.LEGEND_Y + 1);
    //        Row row3 = mySheet.getRow(GlobalVar.LEGEND_Y + 2);
    //        Row row4 = mySheet.getRow(GlobalVar.LEGEND_Y + 3);

    int col1 = GlobalVar.LEGEND_X;
    int col2 = GlobalVar.LEGEND_X + 1;
    int col3 = GlobalVar.LEGEND_X + 2;
    int col4 = GlobalVar.LEGEND_X + 3;

    //// row 1
    CellStyle style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.ETS_STATUS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Cell cell = row1.createCell(col1);
    cell.setCellValue("ETS");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.BAD_STATUS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row1.createCell(col2);
    cell.setCellValue("Bad");
    cell.setCellStyle(style);
    style = myWorkBook.createCellStyle();

    style.setFillForegroundColor(GlobalVar.OVERLAP_LV_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row1.createCell(col3);
    cell.setCellValue("Overlap Lv");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.DUPLICATE_LV_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row1.createCell(col4);
    cell.setCellValue("Duplicate Lv");
    cell.setCellStyle(style);

    //            case INPROCESSING_ERR: return INPROCESSING_COLOR;
    //            case AFTER_PCS_ERR: return AFTER_PCS_COLOR;     
    //// row 2
    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.WRONG_SSN_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col1);
    cell.setCellValue("Wrong SSN");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.DUPLICATE_CTRL_NUM_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col2);
    cell.setCellValue("Duplicate CtrlNum");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.INPROCESSING_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col3);
    cell.setCellValue("Inprocessing");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.AFTER_PCS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col4);
    cell.setCellValue("After PCS");
    cell.setCellStyle(style);

    // row3
    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.IN_CYCLE_DUPLICATE_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row3.createCell(col1);
    cell.setCellValue("In-cycle duplicates/Invalid first five");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(GlobalVar.AUDITOR_DELETED_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row3.createCell(col2);
    cell.setCellValue("Auditor deleted");
    cell.setCellStyle(style);

}

---

From source file:utilities.DebtMgmtBatchInDJMS.java

public void compareXlsxBatch(String xlsxFileName) {
    File xlsxFile = new File(xlsxFileName);
    try {/*from w  w  w  .ja v  a2s  .  c  o m*/
        FileInputStream fis = new FileInputStream(xlsxFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);

        //Return first sheet from the XLSX workbook
        XSSFSheet mySheet = myWorkBook.getSheetAt(0);
        legendBuilder(myWorkBook);
        //Get iterator to all the rows in current sheet
        Iterator<Row> rowIterator = mySheet.iterator();
        // Traversing over each row of XLSX file
        if (rowIterator.hasNext()) {
            Row headerRow = rowIterator.next(); //skip the header row
            //                Iterator<Cell> it = headerRow.cellIterator();
            //                int numCell = 0;
            //               // List<String> keyList = new ArrayList<>(); //keep track info of each column
            //                while(it.hasNext()){
            //                    //keyList.add(it.next().getStringCellValue());   
            //                    it.next();
            //                    numCell++;
            //                }

            //  if (numCell == GlobalVar.LEAVE_TITLES_V1.length || numCell == GlobalVar.LEAVE_TITLES_V2.length){  // correct xlsx file                 
            int rowNum = 1;
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                //row.getRowStyle();
                rowNum++;
            }
            for (int i = 0; i < rowNum; i++) {
                Row row = mySheet.getRow(i);
                foregroundColorSetUp(row, myWorkBook); //check each row and update foreground color
            }

            fis.close();
            FileOutputStream output;

            String targetFile = null;
            if (xlsxFileName.contains(".xlsx")) {
                targetFile = xlsxFileName.replace(".xlsx", "COLORED.xlsx");
            } else {
                targetFile = xlsxFileName + "COLORED.xlsx";
            }
            output = new FileOutputStream(targetFile);
            myWorkBook.write(output);
            output.close();
            //
            //                } else {
            //                     JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!");
            //                }
        } else {
            JOptionPane.showMessageDialog(null, "XLSX file is empty!");
            System.out.println("The xlsx file is empty!");
        }
        JOptionPane.showMessageDialog(null,
                "The leave roster is colored successfully. Please check *COLORED.xlsx.\n");

        //                        case DUPLICATE_LV_ERR: return IndexedColors.YELLOW.getIndex();
        //            case INPROCESSING_ERR: return IndexedColors.PINK.getIndex();
        //            case AFTER_PCS_ERR: return IndexedColors.LAVENDER.getIndex();
        //            case PCS_ERR: return IndexedColors.BLUE.getIndex();
        //            case DUPLICATE_CTRL_NUM_ERR: return IndexedColors.DARK_YELLOW.getIndex();
        // finds the work book in stance for XLSX file
    } catch (FileNotFoundException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!");
    } catch (IOException ex) {
        JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!");
        Logger.getLogger(DebtMgmtBatchInDJMS.class.getName()).log(Level.SEVERE, null, ex);
    }
}

---

From source file:utilities.DebtMgmtBatchInDJMS.java

private void legendBuilder(XSSFWorkbook myWorkBook) {
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    // Create a row and put some cells in it.
    Row row1 = mySheet.getRow(LEGEND_Y);
    Row row2 = mySheet.getRow(LEGEND_Y + 1);
    Row row3 = mySheet.getRow(LEGEND_Y + 2);
    Row row4 = mySheet.getRow(LEGEND_Y + 3);

    int col1 = LEGEND_X;
    int col2 = LEGEND_X + 1;
    int col3 = LEGEND_X + 2;
    int col4 = LEGEND_X + 3;

    //// row 1/*from ww w . j  a va  2  s .co m*/
    CellStyle style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(ETS_STATUS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Cell cell = row1.createCell(col1);
    cell.setCellValue("ETS");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(BAD_STATUS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row1.createCell(col2);
    cell.setCellValue("Bad");
    cell.setCellStyle(style);
    style = myWorkBook.createCellStyle();

    style.setFillForegroundColor(SUSPENDED_STATUS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row1.createCell(col3);
    cell.setCellValue("Suspended");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(PRA_STATUS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row1.createCell(col4);
    cell.setCellValue("PRA");
    cell.setCellStyle(style);

    //// row 2
    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(FULLY_COLLRECTED_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col1);
    cell.setCellValue("Fully Collected");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(WOUNDED_WARRIOR_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col2);
    cell.setCellValue("WW");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(NEW_ENT_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col3);
    cell.setCellValue("New Entitlement");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(DEBT_LETTER_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row2.createCell(col4);
    cell.setCellValue("Debt Letter");
    cell.setCellStyle(style);

    //// row 3
    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(COLLECTING_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row3.createCell(col1);
    cell.setCellValue("Collecting");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(PCS_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row3.createCell(col2);
    cell.setCellValue("PCS");
    cell.setCellStyle(style);

    style = myWorkBook.createCellStyle();
    style.setFillForegroundColor(QUESTIONABLE_COLOR);
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    cell = row3.createCell(col3);
    cell.setCellValue("Questionable");
    cell.setCellStyle(style);
}

---

From source file:utilities.TableReader.java

private static String getUpdate(XSSFSheet sheet, int[] columns, ArrayList<TableMetaData> meta,
        ArrayList<String> errors, int col) {
    String update = Helper.process(meta.get(col - 1).getName()) + " = case "
            + Helper.process(meta.get(0).getIdentifier());

    for (int i = 1; i < sheet.getLastRowNum(); i++) {
        Row row = sheet.getRow(i);
        update += " WHEN '" + getCellContents(row.getCell(columns[0])) + "' THEN '"
                + getCellContents(row.getCell(columns[col])) + "'";
    }//from w  w w .  jav  a2s. c  om
    update += " ELSE " + Helper.process(meta.get(col - 1).getName()) + " END";

    return update;
}

---

From source file:utilities.TableReader.java

private static int[] getExcelColumns(XSSFSheet sheet, ArrayList<TableMetaData> meta) throws UploadException {
    int[] columns = Tools.defaultIntArray(meta.size() + 1);

    Row row = sheet.getRow(0);
    for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i);//from   w w  w.j a v  a  2  s . c o  m
        String colName = cell.getRichStringCellValue().toString();

        checkColumn(columns, colName, i, meta);
    }

    if (Tools.contains(columns, -1)) {
        throw new UploadException(UploadException.MISSING_COLUMNS);
    }

    return columns;
}

---

From source file:Utility.CSV_File_Generator.java

public static void Traffic_Data() {
    Create_Excel_File();//from   w  w  w  .j  ava 2 s .com
    File csv_file = new File(file_details("ML_CSV_File"));
    try {
        FileInputStream fis = new FileInputStream(new File(file_details("Excel_Traffic_Rows")));
        XSSFWorkbook workbook1 = new XSSFWorkbook(fis);
        XSSFSheet sheet1 = workbook1.getSheetAt(0);

        int last_row_index = sheet1.getLastRowNum();
        XSSFRow last_row = sheet1.getRow(last_row_index);

        ArrayList<String> data = new ArrayList<String>();
        String str = "";
        StringBuilder sb = new StringBuilder();
        Iterator<Cell> cellIterator = last_row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                str = cell.getNumericCellValue() + ",";
                break;
            case Cell.CELL_TYPE_STRING:
                str = cell.getStringCellValue() + ",";
                break;
            }
            sb.append(str);
        }
        data.add(sb.substring(0, sb.length() - 1));
        PrintWriter pw = new PrintWriter(csv_file);
        pw.write(data.get(0) + "\n");
        pw.close();
        fis.close();
        System.out.println("Data written in Sample File successfully.");

    } catch (FileNotFoundException ex) {
        Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(CSV_File_Generator.class.getName()).log(Level.SEVERE, null, ex);
    }

}

---

From source file:Utility.CSV_File_Generator.java

public static void compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {
    int firstRow1 = sheet1.getFirstRowNum();
    int lastRow1 = sheet1.getLastRowNum();
    boolean equalSheets = true;
    int i;//  www.ja  va2 s .  c  o  m
    for (i = firstRow1; i <= lastRow1; i++) {

        XSSFRow row1 = sheet1.getRow(i);
        XSSFRow row2 = sheet2.getRow(i);
        if (!compareTwoRows(row1, row2)) {
            equalSheets = false;
            break;
        }
    }
    if (!equalSheets) {
        write_single_row(sheet1, sheet2, i);
    }

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• »