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

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

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

Introduction

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

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:aco.Utilities.java

License:Open Source License

static void writeParetoSet(ArrayList<Ant> bestSoFarPareto, int trial) {
    Row r;//from w ww.j  av a2  s .co m
    Cell c;
    int lineNumber = 0;

    //filePath1 += InOut.max_iterations + " iter (ACO MinMax_vers noua).xlsx";
    //System.out.println("file path=" + filePath1);

    //the file already exists; we should add a new row as the last one in the Excel file
    if (new File(filePath1).canRead()) {
        //System.out.println("File already exists..");
        try {
            FileInputStream file = new FileInputStream(new File(filePath1));

            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook1 = new XSSFWorkbook(file);

            //Get first/desired sheet from the workbook
            XSSFSheet sheet1 = workbook1.getSheetAt(trial);

            //write table header cells
            r = sheet1.getRow(lineNumber);
            if (r == null) {
                // First cell in the row, create
                r = sheet1.createRow(lineNumber);
            }
            c = r.getCell(0);
            if (c == null) {
                // New cell
                c = r.createCell(0);
            }
            c.setCellValue("Point #");
            c = r.getCell(1);
            if (c == null) {
                // New cell
                c = r.createCell(1);
            }
            c.setCellValue("Total tours length");
            c = r.getCell(2);
            if (c == null) {
                // New cell
                c = r.createCell(2);
            }
            c.setCellValue("Amplitude of tours");
            c = r.getCell(3);
            if (c == null) {
                // New cell
                c = r.createCell(3);
            }
            c.setCellValue("List with cost of subtours");

            lineNumber++;
            for (int i = 0; i < bestSoFarPareto.size(); i++) {
                r = sheet1.getRow(i + lineNumber);
                if (r == null) {
                    // First cell in the row, create
                    r = sheet1.createRow(i + lineNumber);
                }
                //write point id
                c = r.getCell(0);
                if (c == null) {
                    // New cell
                    c = r.createCell(0, Cell.CELL_TYPE_NUMERIC);
                }
                c.setCellValue(i + 1);
                //write total cost and amplitude
                for (int indexObj = 0; indexObj < 2; indexObj++) {
                    c = r.getCell(indexObj + 1);
                    if (c == null) {
                        // New cell
                        c = r.createCell(indexObj + 1, Cell.CELL_TYPE_NUMERIC);
                    }
                    c.setCellValue(bestSoFarPareto.get(i).costObjectives[indexObj]);
                }
                //write cost of each individual subtour
                for (int j = 0; j < bestSoFarPareto.get(i).tour_lengths.size(); j++) {
                    c = r.getCell(j + 3);
                    if (c == null) {
                        // New cell
                        c = r.createCell(j + 3);
                    }
                    c.setCellValue(bestSoFarPareto.get(i).tour_lengths.get(j));
                }
            }

            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File(filePath1));
            workbook1.write(out);
            out.close();

            //System.out.println("\nWritten Pareto front points successfully on disk.\n");
            int nrOfRun = trial + 1;
            System.out.println("\nRun #" + nrOfRun + " written Pareto front points successfully on disk.\n");
        } catch (Exception e) {
            e.printStackTrace();
        }

    } else {
        System.out.println(" File " + filePath1 + " doesn't exists");
    }

}

From source file:aco.Utilities.java

License:Open Source License

static void writeExcelFinalSolution(int trial, double scalledValue) {
    Row r;//from   w  w  w.java  2  s. c  o  m
    Cell c;
    int index1 = 0;

    //the file already exists; we should add a new row as the last one in the Excel file
    if (new File(filePath5).canRead()) {
        //System.out.println("File already exists..");
        try {
            FileInputStream file = new FileInputStream(new File(filePath5));

            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook1 = new XSSFWorkbook(file);

            //Get desired sheet from the workbook
            XSSFSheet sheet1 = workbook1.getSheetAt(0);

            //define a cell style for bold font
            CellStyle style = workbook1.createCellStyle();
            Font font = workbook1.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            style.setFont(font);

            //define style with bold font and blue color for font
            CellStyle styleBoldBlue = workbook1.createCellStyle();
            font = workbook1.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            font.setColor(IndexedColors.BLUE.index);
            styleBoldBlue.setFont(font);

            index1 = 8; //8  //26

            index1 = index1 + trial;
            r = sheet1.getRow(index1);
            if (r == null) {
                // First cell in the row, create
                //System.out.println("Empty row, create new one");
                r = sheet1.createRow(index1);
            }

            int nrOfRun = trial + 1;
            //write trial number (Run #)
            c = r.getCell(15);
            if (c == null) {
                // New cell
                //System.out.println("Empty cell, create new one");
                c = r.createCell(15);
            }
            c.setCellValue(nrOfRun);

            //write number of used vehicles
            c = r.getCell(16);
            if (c == null) {
                // New cell
                //System.out.println("Empty cell, create new one");
                c = r.createCell(16);
            }
            c.setCellValue(Ants.best_so_far_ant.usedVehicles);

            //write total traveled distance
            c = r.getCell(17);
            if (c == null) {
                // New cell
                //System.out.println("Empty cell, create new one");
                c = r.createCell(17);
            }
            c.setCellValue(scalledValue);

            //write the total number of feasible solutions
            c = r.getCell(18);
            if (c == null) {
                // New cell
                //System.out.println("Empty cell, create new one");
                c = r.createCell(18);
            }
            c.setCellValue(InOut.noSolutions);

            //Write the workbook in file system
            FileOutputStream out = new FileOutputStream(new File(filePath5));
            workbook1.write(out);
            out.close();

            System.out.println("\nRun #" + nrOfRun + " written successfully on disk.\n");
        }

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

    } else {
        //Blank workbook
        System.out.println("File " + filePath5 + " doesn't exists..");

    }
}

From source file:Algorithm.Method1.java

public String[] MethodTest(String Path) {
    StringBuffer keyword1 = new StringBuffer();
    try {/*www  .j a  va2  s  .  c o m*/
        Hashtable hash = new Hashtable();

        FileInputStream file = new FileInputStream(new File(Path));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet1 = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet1.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //Row rowNew =sheetNew.createRow(rowNumNew++);
            //For each row, iterate through all the columns
            Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                org.apache.poi.ss.usermodel.Cell cell = cellIterator.next();
                // Cell cellNew =rowNew.createCell(cellNumNew++);
                //Check the cell type and format accordingly
                switch (cell.getCellType()) {
                case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
                    break;
                case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING:
                    String result = cell.getStringCellValue();
                    if (hash.containsKey(result)) {
                        int f = Integer.parseInt(hash.get(result).toString());
                        f++;
                        hash.put(result, f);
                    } else {
                        hash.put(result, 1);
                    }

                }
            }

            //System.out.println("");
        }

        XSSFWorkbook workbookNew = new XSSFWorkbook();
        XSSFSheet sheetNew = workbookNew.createSheet("test");
        int rowNum = 0;

        Set s = hash.keySet();
        String key = new String();
        int t = 0;
        for (Iterator<String> i = s.iterator(); i.hasNext();) {
            key = i.next();
            Row rowNew = sheetNew.createRow(rowNum);
            org.apache.poi.ss.usermodel.Cell cellNew = rowNew.createCell(0);
            cellNew.setCellValue(key);
            keyword1.append(key + " ");
            org.apache.poi.ss.usermodel.Cell cellNew2 = rowNew.createCell(1);
            cellNew2.setCellValue(hash.get(key).toString());
            rowNum++;
            //sheet2.addCell(new Label(0,t , key));
            //System.out.println(hash.get(key));
            //sheet2.addCell(new Label(1,t , hash.get(key).toString()));
            t++;
        }

        FileOutputStream fileOut = new FileOutputStream(new File(Path.replace("???.xlsx", "method1.xlsx")));//new file
        workbookNew.write(fileOut);
        fileOut.close();

        file.close();

        // Workbook book = Workbook.getWorkbook(new File("n.xls"));

        //WritableWorkbook book2 = Workbook.createWorkbook(new File("method1.xls"));
        //
        // WritableSheet sheet2 = book2.createSheet("num1", 0);

        // 
        //Sheet sheet = book.getSheet(0);         
        //int rownum = sheet.getRows();// 
        /**
        Cell cell;
        for(int i = 0;i<rownum;i++){
           cell = sheet.getCell(0,i);
           String result = cell.getContents();
           if(hash.containsKey(result)){
          int f = Integer.parseInt(hash.get(result).toString());
          f++;
          hash.put(result, f);
           }else{
          hash.put(result, 1);
           }
        }
        */
        //??
        /*
        Set s=hash.keySet();
        String key = new String();
        int t = 0;
        for(Iterator<String> i=s.iterator();i.hasNext();){
           key = i.next();
           sheet2.addCell(new Label(0,t , key));
        //System.out.println(hash.get(key));
        sheet2.addCell(new Label(1,t , hash.get(key).toString()));
        t++;
        }
                
        book2.write();
        book2.close();
        */
        // book.close();
        System.out.print("method1");
    } catch (Exception e) {
        System.out.println(e);
    }
    return keyword1.toString().split(" ");
}

From source file:Algorithm.Method2.java

public String[] MethodTest(String Path) {
    StringBuffer keyword1 = new StringBuffer();
    try {// w w w . ja v  a 2 s .  c  om
        /**
        * ??excel
        * src
        * map?
        * result
        */

        String srcFilePath = Path;
        String mapFilePath = "??.xlsx";
        String newFilePath = Path.replace("???.xlsx", "method2.xlsx");

        FileInputStream srcFile = new FileInputStream(new File(srcFilePath));
        FileInputStream mapFile = new FileInputStream(new File(mapFilePath));

        XSSFWorkbook srcWorkbook = new XSSFWorkbook(srcFile);
        XSSFWorkbook mapWorkbook = new XSSFWorkbook(mapFile);
        XSSFWorkbook newWorkbook = new XSSFWorkbook();

        XSSFSheet srcSheet = srcWorkbook.getSheetAt(0);
        XSSFSheet mapSheet = mapWorkbook.getSheetAt(0);
        XSSFSheet newSheet = newWorkbook.createSheet("test");

        Iterator<Row> srcRowIterator = srcSheet.iterator();
        Iterator<Row> mapRowIterator = mapSheet.iterator();

        //map.xlst???mapList
        ArrayList mapList = new ArrayList();
        while (mapRowIterator.hasNext()) {
            Row row = mapRowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String test = cell.getStringCellValue();
                mapList.add(test);
            }
        }

        //???mapArray,?
        int mapListLength = mapList.size();
        String[] mapArray = (String[]) mapList.toArray(new String[mapListLength]);

        //??mapResult,mapArray?src
        //,mapArray[i]src,mapResult[i]1?0
        int[] mapResult = new int[mapListLength];
        for (int i = 0; i < mapListLength; i++) {
            mapResult[i] = 0;
        }

        //?src.xlsx,?mapArray
        while (srcRowIterator.hasNext()) {
            Row row = srcRowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                String test = cell.getStringCellValue().trim();

                for (int i = 0; i < mapListLength; i++) {
                    if (test.equals(mapArray[i]))
                        mapResult[i] = 1;
                }
            }
        }

        //?mapResult,mapArrayresult
        int newRowNum = 0;
        for (int i = mapListLength - 1; i >= 0; i--) {
            if (mapResult[i] == 1) {
                Row newRow = newSheet.createRow(newRowNum++);
                Cell newCell = newRow.createCell(0);
                String test = mapArray[i];
                keyword1.append(test + " ");
                newCell.setCellValue(test);
            }
        }

        srcFile.close();
        mapFile.close();

        //FileOutputStream fileOut = new FileOutputStream(new File(newFilePath));
        // newWorkbook.write(fileOut);
        // fileOut.close();
        // System.out.print("method2");
    } catch (Exception e) {
        System.out.println(e);
    }

    return keyword1.toString().split(" ");
}

From source file:Algorithm.SegmentationAndNounFilter.java

public String[] SegmentationNounFilter(String filepath, String filename) {
    try {/*from   w  w  w .j a  v a 2  s  .co  m*/

        String mingciPath = filepath.replace(filename, "???.xlsx");
        ICTCLAS50 testICTCLAS50 = new ICTCLAS50();
        String argu = ".";
        if (testICTCLAS50.ICTCLAS_Init(argu.getBytes("GB2312")) == false) {
            System.out.println("Init Fail");
        } else {
            System.out.println("Init Succeed!");
        }

        StringBuffer input = new StringBuffer();
        FileInputStream file = new FileInputStream(new File(filepath));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet1 = workbook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet1.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //Row rowNew =sheetNew.createRow(rowNumNew++);
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                // Cell cellNew =rowNew.createCell(cellNumNew++);
                //Check the cell type and format accordingly
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    break;
                case Cell.CELL_TYPE_STRING:
                    input.append(cell.getStringCellValue());

                }
            }

            //System.out.println("");
        }
        //?
        byte nativeBytes[] = testICTCLAS50.ICTCLAS_ParagraphProcess(input.toString().getBytes("GB2312"), 0, 1);

        String nativeStr = new String(nativeBytes, 0, nativeBytes.length, "GB2312");

        //xlsx
        XSSFWorkbook workbookNew = new XSSFWorkbook();
        XSSFSheet sheetNew = workbookNew.createSheet("test");
        int rowNum = 0;

        // WritableWorkbook book = Workbook.createWorkbook(new File("n.xls"));
        // WritableSheet sheet = book.createSheet("num1", 0);
        Scanner in = new Scanner(nativeStr);
        int i = 0;//
        while (in.hasNext()) {
            String ss = in.next();

            Pattern pattern = Pattern.compile("(.+?)/n.*");
            Matcher matcher = pattern.matcher(ss);
            if (matcher.find() && matcher.group(1).length() > 1 && !isDigit(matcher.group(1))) {

                //label = new jxl.write.Label(0, i, matcher.group(1));//?
                //sheet.addCell(label);
                Row rowNew = sheetNew.createRow(rowNum++);
                Cell cellNew = rowNew.createCell(0);
                cellNew.setCellValue(matcher.group(1));
                //i++;

            }
        }

        //    book.write();
        //   book.close();
        FileOutputStream fileOut = new FileOutputStream(new File(mingciPath));//new file
        workbookNew.write(fileOut);
        fileOut.close();

        //??            

        file.close();

        testICTCLAS50.ICTCLAS_Exit();

    } catch (Exception ex) {

    }

    return null;

}

From source file:amazonreviews.AmazonReviews.java

public static void main(String[] args) throws IOException {
    // TODO Auto-generated method stub
    //new AmazonReviews.filea("B00I8BIBCW");
    String s1 = "B002RL9CYK";
    reviews = new ArrayList<String>();
    reviewsDate = new ArrayList<String>();
    reviewsStar = new ArrayList<String>();
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Employee Data");

    //This data needs to be written (Object[])
    Map<String, Object[]> data = new TreeMap<String, Object[]>();
    data.put("0", new Object[] { "Review Text", "Review Date", "Review Stars" });
    for (int i = 1; i <= 100; i++) {
        GetReviews(s1, Integer.toString(i));
    }//www  .j av  a  2 s. c  o  m
    for (int i = 0; i < reviews.size(); i++) {
        data.put(Integer.toString(i + 1),
                new Object[] { reviews.get(i), reviewsDate.get(i), reviewsStar.get(i) });
    }

    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        XSSFRow row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
        }
    }
    try {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(new File(name + ".xlsx"));
        workbook.write(out);
        out.close();
        System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:app.DCReportsTab.java

License:Open Source License

private void exportButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_exportButtonActionPerformed

    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Employee Data");

    //This data needs to be written (Object[])
    Map<String, Object[]> data = new TreeMap<String, Object[]>();

    PreparedStatement pst;//from   ww w  .j  av  a 2  s.c om
    Connection con;

    String drNumber = "";
    String dateToday = "";
    String outlet = "";
    String quantity = "";
    String deliveryDate = "";
    String totalAmount = "";

    TableModel model = deliveryReceiptsTable.getModel(); //Table model
    if (model.getRowCount() != 0) {
        Row row = sheet.createRow(2);

        Row headerRow = sheet.createRow(0); //Create row at line 0
        for (int headings = 0; headings < model.getColumnCount(); headings++) { //For each column
            headerRow.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name
        }

        for (int rows = 0; rows < model.getRowCount(); rows++) { //For each table row
            for (int cols = 0; cols < deliveryReceiptsTable.getColumnCount(); cols++) { //For each table column
                row.createCell(cols).setCellValue(model.getValueAt(rows, cols).toString()); //Write value
            }

            //Set the row to the next one in the sequence 
            row = sheet.createRow((rows + 3));
        }
        try {
            //Write the workbook in file system
            java.util.Date date = new java.util.Date();
            FileOutputStream out = new FileOutputStream(
                    new File("C:/Users/Jenelle/Desktop/Report" + date.getTime() + ".xlsx"));
            workbook.write(out);
            out.close();
            System.out.println("Written successfully on disk.");
            JOptionPane.showMessageDialog(null, "Export success!", "Success", JOptionPane.PLAIN_MESSAGE);
        } catch (Exception e) {
            e.printStackTrace();
        }
    } else {

        JOptionPane.showMessageDialog(null, "Please input data to table.", "Error", JOptionPane.ERROR_MESSAGE);
    }

}

From source file:appform.Complaint.java

public static void updateComplaint(String complaint) {
    try {/*from  w ww . j  a  v  a 2s .c  o m*/
        FileInputStream file = new FileInputStream(new File("Complaint.xls"));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(0);
        HashMap data;
        data = new HashMap<>();
        data.put("7", new Object[] { complaint });
        Set keyset = data.keySet();
        int rownum = sheet.getLastRowNum() + 1;
        for (Object key : keyset) {
            Row row = sheet.createRow(rownum++);
            Object[] objArr = (Object[]) data.get(key);
            int cellnum = 0;
            for (Object obj : objArr) {
                Cell cell = row.createCell(cellnum++);
                if (obj instanceof String)
                    cell.setCellValue((String) obj);
                else if (obj instanceof Double)
                    cell.setCellValue((Double) obj);
            }
        }
        FileOutputStream out = new FileOutputStream(new File("Complaint.xls"));
        workbook.write(out);
        out.close();
        System.out.println("Complaint written successfully..");

        //</string></string,></string,>
    }

    catch (Exception e) {
    }
}

From source file:application.ExportTool.java

private void convertToExcel(ResultSet rs) throws SQLException, FileNotFoundException {

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("employe db");
    XSSFRow row = sheet.createRow(0);

    // XSSFCell cell;
    // rowhead.createCell((short) 0).setCellValue("Roll No");
    // rowhead.createCell((short) 0).setCellValue("Roll No");
    // PrintWriter csvWriter = new PrintWriter(new File("whatever.csv")) ;
    ResultSetMetaData meta = rs.getMetaData();
    int numberOfColumns = meta.getColumnCount();
    // String dataHeaders = "\"" + meta.getColumnName(1) + "\"" ;
    int i = 1;/*from w  ww .  j av a  2  s  .c  o m*/
    for (i = 0; i < numberOfColumns; i++) {
        System.out.println(meta.getColumnName(i + 1).toString());
        row.createCell(i).setCellValue(meta.getColumnName(i + 1).toString());
    }
    int index = 1;
    int j = 1;

    while (rs.next()) {

        row = sheet.createRow(index);
        for (j = 0; j < numberOfColumns; j++) {
            System.out.println(rs.getString(j + 1));
            row.createCell(j).setCellValue(rs.getString(j + 1));
        }
        j = 1;
        index++;
    }

    try {
        FileOutputStream out = new FileOutputStream(new File("exceldatabase.xlsx"));
        wb.write(out);
        out.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    System.out.println("Data is saved in excel file.");

}

From source file:application.ExportTool.java

public void convertToExcel(TableView tv, ObservableList<ObservableList> data, String filepath)
        throws IOException {

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet("employe db");
    XSSFRow row = sheet.createRow(0);

    int numberOfColumns = tv.getColumns().size();
    int numberOfRows = tv.getItems().size();

    TableColumn col;/* w w  w. ja  v  a2 s. c o  m*/

    for (int i = 0; i < numberOfColumns; i++) {
        col = (TableColumn) tv.getColumns().get(i);
        System.out.println("dddd" + col.getText() + "pppp");
        row.createCell(i).setCellValue(col.getText());
    }

    int index = 1;
    int j = 0;

    for (ObservableList<String> tmp : data) {

        row = sheet.createRow(index);

        for (String cell : tmp) {
            System.out.println("YYYYY" + cell.toString());
            //String callstring = new String(cell.toString());
            row.createCell(j).setCellValue(cell.toString());
            j++;
        }
        j = 0;

        index++;
    }

    FileOutputStream out = new FileOutputStream(new File(filepath));
    wb.write(out);
    out.close();

}