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:com.accounting.accountMBean.DifferentAccReports.java

public void simplePostProcessXLSX(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    XSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);/* www . j  a  v  a 2  s . c  o  m*/
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("SALES AGEING REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    XSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSXOfStock(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    XSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);//from  ww  w .j  a va 2s.  c o  m
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("STOCK VALUE WITH VALUES");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    XSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLSX(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    XSSFSheet sheet = wb.getSheetAt(0);
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);//from   ww w . j  av a 2  s .co  m
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(pageName);
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    XSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
}

From source file:com.adobe.acs.commons.data.SpreadsheetTest.java

License:Apache License

private static XSSFRow createRow(XSSFSheet sheet, String... values) {
    int rowNum = sheet.getPhysicalNumberOfRows();
    XSSFRow row = sheet.createRow(rowNum);
    for (int i = 0; i < values.length; i++) {
        if (values[i] == null) {
            row.createCell(i);/*from   ww w  . j  a v  a 2 s. c o  m*/
        } else {
            row.createCell(i).setCellValue(values[i]);
        }
    }
    return row;
}

From source file:com.adobe.acs.commons.mcp.impl.GenericReportExcelServlet.java

License:Apache License

private Workbook createSpreadsheet(GenericReport report) {
    Workbook wb = new XSSFWorkbook();

    String name = report.getName();
    for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) {
        name = StringUtils.remove(name, ch);
    }/*from w w  w  .  j  a  v  a 2  s  . co m*/
    XSSFSheet sheet = (XSSFSheet) wb.createSheet(name);

    XSSFRow headerRow = sheet.createRow(0);

    for (int c = 0; c < report.getColumnNames().size(); c++) {
        XSSFCell headerCell = headerRow.createCell(c);
        headerCell.setCellValue(report.getColumnNames().get(c));
    }

    List<ValueMap> rows = report.getRows();
    //make rows, don't forget the header row
    for (int r = 0; r < rows.size(); r++) {
        XSSFRow row = sheet.createRow(r + 1);

        //make columns
        for (int c = 0; c < report.getColumns().size(); c++) {
            String col = report.getColumns().get(c);
            XSSFCell cell = row.createCell(c);

            if (rows.get(r).containsKey(col)) {
                Object val = rows.get(r).get(col);
                if (val instanceof Number) {
                    Number n = (Number) val;
                    cell.setCellValue(((Number) val).doubleValue());
                } else {
                    cell.setCellValue(String.valueOf(val));
                }
            }
        }
    }
    return wb;
}

From source file:com.adobe.acs.commons.mcp.util.SpreadsheetTest.java

License:Apache License

static private XSSFRow createRow(XSSFSheet sheet, String... values) {
    int rowNum = sheet.getPhysicalNumberOfRows();
    XSSFRow row = sheet.createRow(rowNum);
    for (int i = 0; i < values.length; i++) {
        row.createCell(i).setCellValue(values[i]);
    }/*from   ww  w  .j  av a2  s  .com*/
    return row;
}

From source file:com.anritsu.mcrepositorymanager.utils.GenerateRSS.java

public String getRSS() {
    FileInputStream file = null;//from  w  w  w  .j  a va 2 s .  co  m
    String rssFileName = rssTemplateFileName.replaceAll("template", mcVersion);
    try {
        file = new FileInputStream(
                new File(Configuration.getInstance().getRssTemplatePath() + rssTemplateFileName));
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        workbook.setSheetName(workbook.getSheetIndex("MC X.X.X"), "MC " + mcVersion);
        XSSFSheet sheet = workbook.getSheet("MC " + mcVersion);
        CreationHelper createHelper = workbook.getCreationHelper();

        Cell cell = null;

        // Update the sheet title
        cell = sheet.getRow(0).getCell(0);
        cell.setCellValue(cell.getStringCellValue().replaceAll("template", mcVersion));

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        XSSFCellStyle hlinkstyle = workbook.createCellStyle();
        XSSFFont hlinkfont = workbook.createFont();
        hlinkfont.setUnderline(XSSFFont.U_SINGLE);
        hlinkfont.setColor(HSSFColor.BLUE.index);
        hlinkstyle.setFont(hlinkfont);
        hlinkstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        hlinkstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        XSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MMMM-yyyy"));
        dateCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
        dateCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);

        // Populate the table
        int rowCount = 1;
        for (RecommendedMcPackage rmcp : sortedMcPackages) {
            if (rmcp.getRecommendedVersion() != null && rmcp.isShowInTable()) {
                Row row = sheet.createRow(rowCount + 1);
                rowCount++;

                cell = row.createCell(0);
                cell.setCellValue(rmcp.getTier().replaceAll("Anritsu/MasterClaw/", ""));
                cell.setCellStyle(cellStyle);

                cell = row.createCell(1);
                cell.setCellValue(rmcp.getGroup());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(2);
                cell.setCellValue(rmcp.getPackageName());

                UrlValidator defaultValidator = new UrlValidator(UrlValidator.ALLOW_LOCAL_URLS);

                if (rmcp.getRecommendedVersion().getReleaseNote() != null
                        && defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())) {
                    XSSFHyperlink releaseNotelink = (XSSFHyperlink) createHelper
                            .createHyperlink(Hyperlink.LINK_URL);
                    releaseNotelink.setAddress(rmcp.getRecommendedVersion().getReleaseNote());
                    //System.out.println("Inside(if) RN: " + rmcp.getRecommendedVersion().getReleaseNote() + " Valid: " + defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote()));

                    cell.setHyperlink(releaseNotelink);
                }
                cell.setCellStyle(hlinkstyle);

                cell = row.createCell(3);
                cell.setCellValue(rmcp.getRecommendedVersion().getPackageVersion());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(4);
                cell.setCellValue(rmcp.getAvailability());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(5);
                String customers = Arrays.asList(rmcp.getRecommendedVersion().getCustomerList().toArray())
                        .toString();
                if (customers.equalsIgnoreCase("[All]")) {
                    customers = "";
                }
                cell.setCellValue(customers);
                cell.setCellStyle(cellStyle);

                cell = row.createCell(6);
                cell.setCellValue(rmcp.getRecommendedVersion().getRisk());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(7);
                cell.setCellValue(rmcp.getPackageName());
                XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL);
                link.setAddress(rmcp.getRecommendedVersion().getDownloadLinks().iterator().next());
                cell.setHyperlink((XSSFHyperlink) link);
                cell.setCellStyle(hlinkstyle);

                cell = row.createCell(8);
                cell.setCellValue((rmcp.getRecommendedVersion() != null
                        && rmcp.getRecommendedVersion().isLessRecommended()) ? "#" : "");
                cell.setCellStyle(cellStyle);

                cell = row.createCell(9);
                cell.setCellValue(rmcp.getRecommendedVersion().getNotes());
                cell.setCellStyle(cellStyle);

                StringBuilder newFeatures = new StringBuilder();
                for (MCPackageActivities mcpa : rmcp.getRecommendedVersion().getActivities()) {
                    if (!mcpa.getActivityType().equalsIgnoreCase("epr")) {
                        newFeatures.append(mcpa.getActivityType() + " " + mcpa.getActivityId() + "; ");
                    }
                }
                cell = row.createCell(10);
                cell.setCellValue(newFeatures.toString());
                cell.setCellStyle(cellStyle);

                cell = row.createCell(11);
                cell.setCellValue(rmcp.getRecommendedVersion().getReleaseDate());
                cell.setCellStyle(dateCellStyle);
            }
            sheet.autoSizeColumn(0);
            sheet.autoSizeColumn(1);
            sheet.autoSizeColumn(2);
            sheet.autoSizeColumn(3);
            sheet.autoSizeColumn(4);
            sheet.autoSizeColumn(6);
            sheet.autoSizeColumn(7);
            sheet.autoSizeColumn(8);
            sheet.autoSizeColumn(11);

        }

        FileOutputStream outFile = new FileOutputStream(
                new File(Configuration.getInstance().getRssTemplatePath() + rssFileName));
        workbook.write(outFile);
        outFile.close();
        return Configuration.getInstance().getRssTemplatePath() + rssFileName;

    } catch (FileNotFoundException ex) {
        Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            file.close();
        } catch (IOException ex) {
            Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    return "";
}

From source file:com.appdynamics.jrbronet.projectplan.ExcelManager.java

private static void copyRow(XSSFWorkbook workbook, XSSFSheet sourceWorksheet, int sourceRowNum,
        XSSFSheet destinationWorksheet, int destinationRowNum) {
    // Get the source / new row
    XSSFRow newRow = destinationWorksheet.getRow(destinationRowNum);
    XSSFRow sourceRow = sourceWorksheet.getRow(sourceRowNum);

    // If the row exist in destination, push down all rows by 1 else create a new row

    if (newRow != null) {
        destinationWorksheet.shiftRows(destinationRowNum, destinationWorksheet.getLastRowNum(), 1);
    } else {/*www . ja va2 s.c o  m*/
        newRow = destinationWorksheet.createRow(destinationRowNum);
    }

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        // Copy style from old cell and apply to new cell
        XSSFCellStyle newCellStyle = workbook.createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        ;
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        //newCell.setCellValue(oldCell.getRawValue());
        //newCell.setCellType(oldCell.getCellType());                        

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        }

    }

    // If there are are any merged regions in the source row, copy to new row
    /*
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
    CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
    if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
        CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                (newRow.getRowNum() +
                        (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                )),
                cellRangeAddress.getFirstColumn(),
                cellRangeAddress.getLastColumn());
        worksheet.addMergedRegion(newCellRangeAddress);
    }            
    }
    */
}

From source file:com.axelor.studio.service.data.validator.ValidatorService.java

License:Open Source License

public void addLog(String log, String sheetName, int rowNum) throws IOException {

    if (logFile == null) {
        logFile = File.createTempFile("ImportLog", ".xlsx");
        logBook = new XSSFWorkbook();
    }// w  w  w  .  ja  v a2s . c om

    XSSFSheet sheet = logBook.getSheet(sheetName);

    if (sheet == null) {
        sheet = logBook.createSheet(sheetName);
        XSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("Row Number");
        titleRow.createCell(1).setCellValue("Issues");
    }

    Iterator<Row> rowIterator = sheet.rowIterator();
    Row logRow = null;
    while (rowIterator.hasNext()) {
        Row sheetRow = rowIterator.next();
        Cell cell = sheetRow.getCell(0);
        if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            continue;
        }
        double value = cell.getNumericCellValue();
        if (value == rowNum + 1) {
            logRow = sheetRow;
            break;
        }
    }

    if (logRow == null) {
        logRow = sheet.createRow(sheet.getPhysicalNumberOfRows());
    }

    Cell cell = logRow.getCell(0);
    if (cell == null) {
        cell = logRow.createCell(0);
        cell.setCellValue(rowNum + 1);
    }
    cell = logRow.getCell(1);
    if (cell == null) {
        cell = logRow.createCell(1);
    }
    String oldValue = cell.getStringCellValue();
    if (oldValue == null) {
        cell.setCellValue(log);
    } else {
        cell.setCellValue(oldValue + "\n" + log);
    }

}

From source file:com.bc.util.XlsxExporter.java

public static void WriteInvoiceToFile(File output, CustomerOrder order, ArrayList<CustomerOrderItem> items) {
    try {// w  w  w .  j  a v  a2 s.c o  m
        if (output.exists()) {
            log.info(output.getName() + " exists. Deleting");
            output.delete();
            log.info("Deleted " + output.getName());
        }

        log.info("Creating xlsx file...");

        FileOutputStream fos = new FileOutputStream(output);
        XSSFWorkbook workBook = new XSSFWorkbook();
        XSSFSheet sheet = workBook.createSheet("Order");
        CellStyle style = workBook.createCellStyle();
        style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.ALIGN_FILL);
        Font font = workBook.createFont();
        font.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(font);

        String[] columnHeaders = { "Invoice", "Salesman", "Customer Name", "Customer Code", "PO", "Ship Date",
                "Post Date", "ISBN", "ISBN13", "Title", "List Price", "Price", "Quantity", "Shipped",
                "Discount", "Extended Price" };

        log.info("Creating header row & columns");

        Row row = sheet.createRow(0);

        for (int i = 0; i < columnHeaders.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(columnHeaders[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 4500);
        }

        sheet.setColumnWidth(9, 13500);

        log.info("Writing " + items.size() + " records");

        XSSFDataFormat decimalFormat = workBook.createDataFormat();
        CellStyle dstyle = workBook.createCellStyle();
        dstyle.setDataFormat(decimalFormat.getFormat("0.00"));

        int i = 1;
        for (CustomerOrderItem orderItem : items) {
            Row drow = sheet.createRow(i++);
            Hibernate.initialize(order.getCustomerOrderItems());

            String strValue;
            Float floatValue;
            Integer intVal;

            Cell cInvoice = drow.createCell(0);
            strValue = order.getInvoiceNumber();
            if (strValue == null)
                strValue = "";
            cInvoice.setCellValue(order.getInvoiceNumber());

            Cell cSalesman = drow.createCell(1);
            strValue = order.getSalesman();
            if (strValue == null)
                strValue = "";
            cSalesman.setCellValue(strValue);

            Cell cCustomerName = drow.createCell(2);
            strValue = order.getCustomer().getCompanyName();
            if (strValue == null)
                strValue = "";
            cCustomerName.setCellValue(strValue);

            Cell cCustomerCode = drow.createCell(3);
            strValue = order.getCustomerCode();
            if (strValue == null)
                strValue = "";
            cCustomerCode.setCellValue(strValue);

            Cell cPo = drow.createCell(4);
            strValue = order.getPoNumber();
            if (strValue == null)
                strValue = "";
            cPo.setCellValue(strValue);

            Cell cShipDate = drow.createCell(5);
            Date d = order.getShipDate();
            if (d == null)
                cShipDate.setCellValue("");
            else
                cShipDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));

            Cell cPostDate = drow.createCell(6);
            d = order.getPostDate();
            if (d == null)
                cPostDate.setCellValue("");
            else
                cPostDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear()));
            Hibernate.initialize(orderItem.getInventoryItem());
            InventoryItem item = orderItem.getInventoryItem(); //orderItem.getInventoryItem();
            if (item != null) {
                Cell cIsbn = drow.createCell(7);
                strValue = item.getIsbn();
                if (strValue == null)
                    strValue = "";
                cIsbn.setCellValue(strValue);

                Cell cIsbn13 = drow.createCell(8);
                strValue = item.getIsbn13();
                if (strValue == null)
                    strValue = "";
                cIsbn13.setCellValue(strValue);

                Cell cTitle = drow.createCell(9);
                strValue = item.getTitle();
                if (strValue == null)
                    strValue = "";
                cTitle.setCellValue(strValue);

                Cell cListPrice = drow.createCell(10);
                floatValue = item.getListPrice();
                cListPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cListPrice.setCellValue(floatValue);

                Cell cPrice = drow.createCell(11);
                floatValue = item.getSellingPrice();
                cPrice.setCellStyle(dstyle);
                if (floatValue == null)
                    floatValue = 0.0f;
                cPrice.setCellValue(floatValue);
            }
            Cell cQuantity = drow.createCell(12);
            intVal = orderItem.getQuantity();
            log.info("Quantity : " + intVal);
            if (intVal == null)
                intVal = 0;
            cQuantity.setCellValue(intVal);

            Cell cShipped = drow.createCell(13);
            intVal = orderItem.getFilled();
            log.info("Shipped QTY : " + intVal);
            if (intVal == null)
                intVal = 0;
            cShipped.setCellValue(intVal);

            Cell cDiscount = drow.createCell(14);
            cDiscount.setCellStyle(dstyle);
            floatValue = orderItem.getDiscount();
            if (floatValue == null)
                floatValue = 0.0f;
            cDiscount.setCellValue(floatValue);

            Cell cExtendedPrice = drow.createCell(15);
            cExtendedPrice.setCellStyle(dstyle);
            BigDecimal dValue = orderItem.getTotalPrice();
            if (dValue == null)
                dValue = BigDecimal.ZERO;
            cExtendedPrice.setCellValue(dValue.doubleValue());

        }

        workBook.write(fos);
        log.info("Finished writing data, closing...");

        fos.close();
        log.info("Completed exporting data to " + output.getAbsolutePath());
    } catch (Exception ex) {
        Logger.getLogger(XlsxExporter.class.getName()).log(Level.SEVERE, null, ex);
    }

}