Example usage for org.apache.poi.xssf.usermodel XSSFRow createCell

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow createCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. createCell

Introduction

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

Prototype

@Override
public XSSFCell createCell(int columnIndex)

Source Link

Document

Use this to create new cells within the row and return it.

Usage

From source file:StatusUpdater.java

static boolean updateStatus(String path, String username, String task, int optionChosen) {
    File myFile = new File(path);
    FileInputStream fis = null;//from   w  w w  .  j av  a 2s  .c  om
    try {
        fis = new FileInputStream(myFile);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    XSSFWorkbook workbook = null;
    try {
        workbook = new XSSFWorkbook(fis);
    } catch (IOException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    XSSFSheet sheet = workbook.getSheetAt(0);
    if (sheet == null) {
        return false;
    }
    Iterator ite1 = sheet.rowIterator();
    if (ite1 == null) {
        return false;
    }
    XSSFRow myRow = null;
    DateFormat df = new SimpleDateFormat("dd/MM/yy HH:mm:ss");
    Date dateobj = new Date();
    df.format(dateobj);
    if (ite1.hasNext()) {
        ite1.next();
    }
    while (ite1.hasNext()) {
        myRow = (XSSFRow) ite1.next();
        XSSFCell usernameCell = myRow.getCell(0);
        String sheet_userid = null;
        if (usernameCell.getStringCellValue() != null) {
            sheet_userid = usernameCell.getStringCellValue();
        } else {
            return false;
        }
        System.out.println("sheet_userid=" + sheet_userid);
        XSSFCell taskCell = myRow.getCell(1);
        if (taskCell == null) {
            return false;
        }
        String sheet_task = taskCell.getStringCellValue();
        System.out.println("sheet_task=" + sheet_task);
        if (sheet_task == null) {
            return false;
        }
        if (sheet_userid.equals(username) && sheet_task.equals(task)) {
            break;
        }
    }
    if (optionChosen == 1) { //Resume is pressed.
        XSSFCell statusCell = myRow.getCell(2);
        String status = null;
        if (statusCell != null) {
            status = statusCell.getStringCellValue();
            if (status.equalsIgnoreCase("Paused") || status.equalsIgnoreCase("Deferred")) {
                XSSFCell timestampCell = myRow.getCell(3);
                timestampCell.setCellValue(df.format(dateobj));
                XSSFCell status_cell = myRow.getCell(2);
                status_cell.setCellValue("In-Progress");
            } else if (status.equalsIgnoreCase("In-Progress")) //trying to Resume an in-progress task.
            {
                return true;
            } else { //trying to resume a finished task or invalid status task.
                return false;
            }
        } else {
            return false;
        }
    } else if (optionChosen == 2) { //Pause is pressed
        XSSFCell statusCell = myRow.getCell(2);
        if (statusCell != null) {
            String status = statusCell.getStringCellValue();
            if (status != null) {

                if (status.equalsIgnoreCase("Paused"))
                    return true;
                else if (status.equalsIgnoreCase("In-Progress")) {
                    XSSFCell timestampCell = myRow.getCell(3);
                    String dateInString = timestampCell.getStringCellValue();
                    Date date_obj = null;
                    try {
                        date_obj = df.parse(dateInString);
                    } catch (ParseException ex) {
                        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    System.out.println("date value of sheet in pause button=" + dateobj.toString());

                    Date obj = new Date();
                    df.format(obj);
                    long diff = date_obj.getTime() - obj.getTime();
                    long divisor = 60 * 60 * 1000;
                    double diffHours = ((double) diff / (double) divisor);
                    //XSSFCell cell2=myRow.getCell(4);
                    XSSFCell totalTimeCell = null;
                    if (myRow.getCell(4) == null) {
                        totalTimeCell = myRow.createCell(4);
                        totalTimeCell.setCellValue(Double.toString(diffHours));
                    } else {
                        totalTimeCell = myRow.getCell(4);
                        double timeSpent = Double.parseDouble(totalTimeCell.getStringCellValue());
                        timeSpent += diffHours;
                        totalTimeCell.setCellValue(String.valueOf(timeSpent));
                    }
                    statusCell.setCellValue("Paused");
                } else if (status.equalsIgnoreCase("Deferred")) {
                    statusCell.setCellValue("Paused");
                } else
                    return false;
            } else
                return false;
        } else {
            return false;
        }
    } else if (optionChosen == 3) { //Stop is pressed
        XSSFCell statusCell = myRow.getCell(2);
        if (statusCell != null) {
            String status = statusCell.getStringCellValue();
            if (status != null) {

                if (status.equalsIgnoreCase("Paused"))
                    return true;
                else if (status.equalsIgnoreCase("In-Progress")) {
                    XSSFCell timestampCell = myRow.getCell(3);
                    String dateInString = timestampCell.getStringCellValue();
                    Date date_obj = null;
                    try {
                        date_obj = df.parse(dateInString);
                    } catch (ParseException ex) {
                        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
                    }
                    System.out.println("date value of sheet in pause button=" + dateobj.toString());

                    Date obj = new Date();
                    df.format(obj);
                    long diff = date_obj.getTime() - obj.getTime();
                    long divisor = 60 * 60 * 1000;
                    double diffHours = ((double) diff / (double) divisor);
                    XSSFCell totalTimeCell = null;
                    if (myRow.getCell(4) == null) {
                        totalTimeCell = myRow.createCell(4);
                        totalTimeCell.setCellValue(Double.toString(diffHours));
                    } else {
                        totalTimeCell = myRow.getCell(4);
                        double timeSpent = Double.parseDouble(totalTimeCell.getStringCellValue());
                        timeSpent += diffHours;
                        totalTimeCell.setCellValue(String.valueOf(timeSpent));
                    }
                    statusCell.setCellValue("Deferred");
                } else if (status.equalsIgnoreCase("Paused")) {
                    statusCell.setCellValue("Deferred");
                } else {
                    return false;
                }
            } else {
                return false;
            }
        } else {
            return false;
        }
    } else if (optionChosen == 4) {
        XSSFCell status_cell = myRow.getCell(2);
        if (status_cell.getStringCellValue() == "In-Progress") //logic to calculate the time taken if the task was in-process so far
        {
            XSSFCell timestampCell = myRow.getCell(3);
            String dateInString = timestampCell.getStringCellValue();
            Date date_obj = null;
            try {
                date_obj = df.parse(dateInString);
            } catch (ParseException ex) {
                Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
            }
            Date obj = new Date();
            df.format(obj);
            long fv = date_obj.getTime();
            long sv = obj.getTime();
            long diff = sv - fv;
            long divisor = 60 * 60 * 1000;
            double diffHours = ((double) diff / (double) divisor);

            XSSFCell cell2 = null;
            if (myRow.getCell(4) == null) {
                cell2 = myRow.createCell(4);
                cell2.setCellValue(Double.toString(diffHours));
            } else {
                cell2 = myRow.getCell(4);
                double timeSpent = Double.parseDouble(cell2.getStringCellValue());
                timeSpent += diffHours;

                cell2.setCellValue(String.valueOf(timeSpent));
            }
        }
        status_cell.setCellValue("Completed");

    } else {
        System.out.println("Invalid value for optionChosen");
    }
    try {
        fis.close();
    } catch (IOException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Debug one");
    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(myFile);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Debug two");
    try {
        workbook.write(fileOut);
    } catch (IOException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    try {
        fileOut.close();
    } catch (IOException ex) {
        Logger.getLogger(StatusUpdater.class.getName()).log(Level.SEVERE, null, ex);
    }
    return true;
}

From source file:Viewsale.java

private void writeToExcel() throws FileNotFoundException, IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();//w  ww  .  j  a va2s  . c  o  m
    TreeMap<String, Object[]> data = new TreeMap<>();
    data.put("-1",
            new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2), dm.getColumnName(3),
                    dm.getColumnName(4), dm.getColumnName(5), dm.getColumnName(6), dm.getColumnName(7),
                    dm.getColumnName(8), dm.getColumnName(9), dm.getColumnName(10), dm.getColumnName(11),
                    dm.getColumnName(12), dm.getColumnName(13), dm.getColumnName(14), dm.getColumnName(15) });
    for (int i = 0; i < dm.getRowCount(); i++) {

        data.put(Integer.toString(i),
                new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2), getCellValue(i, 3),
                        getCellValue(i, 4), getCellValue(i, 5), getCellValue(i, 6), getCellValue(i, 7),
                        getCellValue(i, 8), getCellValue(i, 9), getCellValue(i, 10), getCellValue(i, 11),
                        getCellValue(i, 12), getCellValue(i, 13), getCellValue(i, 14), getCellValue(i, 15) });

    }
    Set<String> ids = data.keySet();

    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);
        Object[] values = data.get(key);
        int cellID = 0;
        for (Object o : values) {
            XSSFCell cell = row.createCell(cellID++);
            cell.setCellValue(o.toString());
        }
    }

    FileOutputStream fos = new FileOutputStream(new File("D:/motors/saleview.xlsx"));
    wb.write(fos);
    fos.close();

}

From source file:CreateTable.java

License:Apache License

public static void main(String[] args) throws FileNotFoundException, IOException {

    Workbook wb = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet) wb.createSheet();

    //Create /*  w ww .j  a va 2s.c o m*/
    XSSFTable table = sheet.createTable();
    table.setDisplayName("Test");
    CTTable cttable = table.getCTTable();

    //Style configurations
    CTTableStyleInfo style = cttable.addNewTableStyleInfo();
    style.setName("TableStyleMedium2");
    style.setShowColumnStripes(false);
    style.setShowRowStripes(true);

    //Set which area the table should be placed in
    AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(3, 3));
    cttable.setRef(reference.formatAsString());
    cttable.setId(1);
    cttable.setName("Test");
    cttable.setTotalsRowCount(1);

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);
    CTTableColumn column;
    XSSFRow row;
    XSSFCell cell;
    for (int i = 0; i < 3; i++) {
        //Create column
        column = columns.addNewTableColumn();
        column.setName("Column");
        column.setId(i + 1);
        //Create row
        row = sheet.createRow(i);
        for (int j = 0; j < 3; j++) {
            //Create cell
            cell = row.createCell(j);
            if (i == 0) {
                cell.setCellValue("Column" + j);
            } else {
                cell.setCellValue(i + j + 0.0);
            }
        }
    }

    FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:spareexcel.java

private void writeToExcel() throws FileNotFoundException, IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();//from   w w w  . j  a  va 2 s.co  m
    TreeMap<String, Object[]> data = new TreeMap<>();
    data.put("-1", new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2),
            dm.getColumnName(3), dm.getColumnName(4), dm.getColumnName(5), dm.getColumnName(6) });
    for (int i = 0; i < dm.getRowCount(); i++) {

        data.put(Integer.toString(i), new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2),
                getCellValue(i, 3), getCellValue(i, 4), getCellValue(i, 5), getCellValue(i, 6) });

    }
    Set<String> ids = data.keySet();

    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);
        Object[] values = data.get(key);
        int cellID = 0;
        for (Object o : values) {
            XSSFCell cell = row.createCell(cellID++);
            cell.setCellValue(o.toString());
        }
    }

    FileOutputStream fos = new FileOutputStream(new File("D:/motors/sparexcel.xlsx"));
    wb.write(fos);
    fos.close();

}

From source file:Viewservice.java

private void writeToExcel() throws FileNotFoundException, IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();//  w  ww  .  j a v  a  2 s . c o  m
    TreeMap<String, Object[]> data = new TreeMap<>();
    data.put("-1",
            new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2), dm.getColumnName(3),
                    dm.getColumnName(4), dm.getColumnName(5), dm.getColumnName(6), dm.getColumnName(7),
                    dm.getColumnName(8), dm.getColumnName(9), dm.getColumnName(10), dm.getColumnName(11),
                    dm.getColumnName(12), dm.getColumnName(13), dm.getColumnName(14), dm.getColumnName(15),
                    dm.getColumnName(16), dm.getColumnName(17), dm.getColumnName(18) });
    for (int i = 0; i < dm.getRowCount(); i++) {

        data.put(Integer.toString(i),
                new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2), getCellValue(i, 3),
                        getCellValue(i, 4), getCellValue(i, 5), getCellValue(i, 6), getCellValue(i, 7),
                        getCellValue(i, 8), getCellValue(i, 9), getCellValue(i, 10), getCellValue(i, 11),
                        getCellValue(i, 12), getCellValue(i, 13), getCellValue(i, 14), getCellValue(i, 15),
                        getCellValue(i, 16), getCellValue(i, 17), getCellValue(i, 18) });

    }
    Set<String> ids = data.keySet();

    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);
        Object[] values = data.get(key);
        int cellID = 0;
        for (Object o : values) {
            XSSFCell cell = row.createCell(cellID++);
            cell.setCellValue(o.toString());
        }
    }

    FileOutputStream fos = new FileOutputStream(new File("D:/motors/serviceview.xlsx"));

    wb.write(fos);
    fos.close();

}

From source file:achmad.rifai.admin.ui.Saver.java

private void title(int i, XSSFRow r1, String s) {
    org.apache.poi.xssf.usermodel.XSSFCell c = r1.createCell(i);
    c.setCellType(CellType.STRING);//from ww w .  jav a 2  s .c  om
    c.setCellValue(s);
    org.apache.poi.xssf.usermodel.XSSFCellStyle cs = c.getCellStyle();
    cs.setFillBackgroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.BLACK));
    cs.setFillForegroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.YELLOW));
    cs.setAlignment(HorizontalAlignment.CENTER);
    cs.setVerticalAlignment(VerticalAlignment.CENTER);
    cs.setBorderBottom(BorderStyle.DASHED);
    cs.setBorderTop(BorderStyle.DASHED);
    cs.setBorderLeft(BorderStyle.DASHED);
    cs.setBorderRight(BorderStyle.DASHED);
}

From source file:achmad.rifai.admin.ui.Saver.java

private void konten(int i, XSSFRow r, String s) {
    org.apache.poi.xssf.usermodel.XSSFCell c = r.createCell(i);
    c.setCellType(CellType.STRING);//from  w  ww.ja va  2 s .  co  m
    c.setCellValue(s);
    org.apache.poi.xssf.usermodel.XSSFCellStyle cs = c.getCellStyle();
    cs.setFillBackgroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.WHITE));
    cs.setFillForegroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.BLACK));
    cs.setAlignment(HorizontalAlignment.JUSTIFY);
    cs.setVerticalAlignment(VerticalAlignment.TOP);
    cs.setBorderBottom(BorderStyle.DASHED);
    cs.setBorderTop(BorderStyle.DASHED);
    cs.setBorderLeft(BorderStyle.DASHED);
    cs.setBorderRight(BorderStyle.DASHED);
}

From source file:achmad.rifai.admin.ui.Saver.java

private void konten1(int i, XSSFRow r, String s) {
    org.apache.poi.xssf.usermodel.XSSFCell c = r.createCell(i);
    c.setCellType(CellType.STRING);//from   w w w.  j a  v a 2s .  c  o m
    c.setCellValue(s);
    org.apache.poi.xssf.usermodel.XSSFCellStyle cs = c.getCellStyle();
    cs.setFillBackgroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.WHITE));
    cs.setFillForegroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.BLACK));
    cs.setAlignment(HorizontalAlignment.CENTER);
    cs.setVerticalAlignment(VerticalAlignment.CENTER);
    cs.setBorderBottom(BorderStyle.DASHED);
    cs.setBorderTop(BorderStyle.DASHED);
    cs.setBorderLeft(BorderStyle.DASHED);
    cs.setBorderRight(BorderStyle.DASHED);
}

From source file:achmad.rifai.admin.ui.Saver.java

private void title2(int i, XSSFRow r1, String s, XSSFSheet sh) {
    sh.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 1, i, i));
    org.apache.poi.xssf.usermodel.XSSFCell c = r1.createCell(i);
    c.setCellType(CellType.STRING);//  w  ww  .  j  av a  2  s  .c  om
    c.setCellValue(s);
    org.apache.poi.xssf.usermodel.XSSFCellStyle cs = c.getCellStyle();
    cs.setFillBackgroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.BLACK));
    cs.setFillForegroundColor(new org.apache.poi.xssf.usermodel.XSSFColor(Color.YELLOW));
    cs.setAlignment(HorizontalAlignment.CENTER);
    cs.setVerticalAlignment(VerticalAlignment.CENTER);
    cs.setBorderBottom(BorderStyle.DASHED);
    cs.setBorderTop(BorderStyle.DASHED);
    cs.setBorderLeft(BorderStyle.DASHED);
    cs.setBorderRight(BorderStyle.DASHED);
}

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));
    }//from   w  w w. j a  v 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();
    }
}