Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook write

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. write

Introduction

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

Prototype

@SuppressWarnings("resource")
public final void write(OutputStream stream) throws IOException

Source Link

Document

Write out this document to an Outputstream.

Usage

From source file:org.alfresco.bm.api.v1.RestAPITest.java

License:Open Source License

/**
 * Extra data creation and export of XSLX Workbook
 * //from w  w w . ja  va 2 s  . c o m
 * @throws Exception
 */
@Test
public synchronized void testScenario11() throws Exception {
    // test definitions and extra data sheets
    String test = "T11";
    String run = "01";
    String driver = UUID.randomUUID().toString();
    String sheet1 = "extra.data.sheet1";
    String sheet2 = "extra.data.sheet2";
    String sheet3 = "extra.data.sheet3";
    String[] fieldNames = { "tf1", "tf2", "tf3", "tf4", "tf5" };
    String[] descriptions = { "Test Field 1", "Test Field 2", "Test Field 3", "Test Field 4", "Test Field 5" };
    String[] values = new String[5];

    executeTestRun(test, "A test for scenario 11.", run, "Scenario 11 - Run 01");

    // get the extra data report service
    DataReportService reportService = this.testRunServicesCache.getDataReportService(test, run);
    assertNotNull(reportService);

    // create a random value array
    for (int i = 0; i < 5; i++) {
        values[i] = UUID.randomUUID().toString();
    }

    // append some extra data and a description
    reportService.appendData(driver, test, run, sheet1, fieldNames, values);
    reportService.appendData(driver, test, run, sheet2, fieldNames, values);
    reportService.appendData(driver, test, run, sheet2, fieldNames, values);
    reportService.appendData(driver, test, run, sheet3, fieldNames, values);
    reportService.appendData(driver, test, run, sheet3, fieldNames, values);
    reportService.appendData(driver, test, run, sheet3, fieldNames, values);
    reportService.setDescription(driver, test, run, sheet3, fieldNames, descriptions);

    // create and download an XSLX
    ResultsRestAPI resultsAPI = api.getTestRunResultsAPI(test, run);
    StreamingOutput xlsxOutput = resultsAPI.getReportXLSX();
    ByteArrayOutputStream xlsxBos = new ByteArrayOutputStream();
    xlsxOutput.write(xlsxBos);
    xlsxBos.close();
    ByteArrayInputStream xlsxBis = new ByteArrayInputStream(xlsxBos.toByteArray());
    XSSFWorkbook xlsxWorkbook = new XSSFWorkbook(xlsxBis);
    xlsxBis.close();
    File xlsxFile = File.createTempFile(UUID.randomUUID().toString(), ".xlsx");
    FileOutputStream xlsxFos = new FileOutputStream(xlsxFile);
    xlsxWorkbook.write(xlsxFos);
    xlsxFos.close();

    // validate data in DB
    assertNull("Expected no description row for sheet 1, but found one?",
            reportService.getDescription(null, test, run, sheet1));
    assertNull("Expected no description row for sheet 2, but found one?",
            reportService.getDescription(null, test, run, sheet2));
    List<String> descList = reportService.getDescription(null, test, run, sheet3);
    assertNotNull("Expected a description row for sheet 3, but can't find any ...", descList);
    int count = 0;
    for (String desc : descList) {
        assertEquals(
                "Description of sheet 3 mismatch. Expected '" + descriptions[count] + "' found '" + desc + "'.",
                descriptions[count++], desc);
    }

    // validate number of sheets
    String[] sheetNames = reportService.getSheetNames(null, test, run);
    assertEquals("Expected 3 sheets of extra data, found: " + sheetNames.length, 3, sheetNames.length);

    // validate number of entries for each extra data
    int countTestEntries = 0;
    for (int j = 0; j < 3; j++) {
        DBCursor dbCursor = reportService.getData(null, test, run, sheetNames[j]);
        while (null != dbCursor && dbCursor.hasNext()) {
            List<String> valuesFromMongo = reportService.getNextValueRow(dbCursor);
            countTestEntries++;
            assertEquals("Expected 5 columns in extra data, found: " + valuesFromMongo.size(), 5,
                    valuesFromMongo.size());
        }
    }
    assertEquals("Expected 6 lines of extra data ... found: " + countTestEntries, 6, countTestEntries);

    // delete test
    api.deleteTest(test, true);

    // validate
    assertNull("Sheet names expected to be empty.", reportService.getSheetNames(null, test, run));
    assertNull("Description of sheet 3 expected to be empty.",
            reportService.getDescription(null, test, run, sheet3));

    // clean-up: delete XLSX file
    xlsxWorkbook.close();
    xlsxFile.delete();
}

From source file:org.alfresco.bm.report.XLSXReporter.java

License:Open Source License

@Override
public void export(OutputStream os) {
    XSSFWorkbook workbook = new XSSFWorkbook();
    // Set defaults
    workbook.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);

    // Write to the workbook
    try {/*from   w  w w  . j  a  v  a  2s .  c o m*/
        writeToWorkbook(workbook);
        workbook.write(os);
    } catch (Exception e) {
        throw new RuntimeException("Failed to write workbook: " + this, e);
    } finally {
        try {
            os.close();
        } catch (Throwable e) {
        }
    }
}

From source file:org.apache.commons.g.QueryStorePicUI.java

/**
 * ??//  www  .j a  va2  s  .c o  m
 * @param path
 * @throws Exception
 */
public void queryStorePic(String filePath, String outPath) throws Exception {

    final MyDialog dialog = new MyDialog(mContext, " ...");

    final List<String> noPics = new ArrayList<String>();
    final List<String> noStores = new ArrayList<String>();

    InputStream in = new FileInputStream(filePath);
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
    final XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    int rows = xssfSheet.getLastRowNum();
    if (rows > 100) {
        rows = 100;
    }

    System.out.println();
    System.out.println("  " + rows + "...");
    dialog.setWorkText("  " + rows + "...");

    HashMap<String, String> headers = new HashMap<String, String>();
    headers.put("Content-Type", "application/json");
    headers.put("Cookie", Config.JSESSIONID);
    headers.put("Referer", "https://homesis.homecredit.cn/hsis/index.html");

    final XSSFCell cell0 = xssfSheet.getRow(0).createCell(0);
    cell0.setCellValue("");

    System.out.println("?? |  | ? |    ??    | ??");
    for (int i = 1; i <= rows; i++) {
        final XSSFRow xssfRow = xssfSheet.getRow(i);

        if (xssfRow == null)
            continue;

        // ??
        String visitName = XssfUtil.getCellValue(xssfRow.getCell(0));
        if (TextUtils.isEmpty(visitName))
            continue;

        // ?
        final String storeCode = XssfUtil.getCellValue(xssfRow.getCell(3));
        final String data = "{\"code\":\"" + storeCode
                + "\",\"name\":null,\"licenseNumber\":null,\"salesDistrictId\":null,\"offset\":0,\"limit\":15}";

        // ??
        final String storeName = XssfUtil.getCellValue(xssfRow.getCell(10));

        final XSSFCell cell = xssfRow.createCell(0);

        String space2 = "     ";
        // ??2
        if (storeName.length() < 20)
            space2 += Util.getFormateSpace(20 - storeName.length());
        ;

        String space = "     ";
        // ??2
        if (visitName.length() == 3)
            space = space.substring(2);
        // ?????
        if (i < 10) {
            System.out.print(
                    "    " + i + "      " + visitName + space + storeCode + "        " + storeName + space2);
        } else {
            System.out.print(
                    "    " + i + "     " + visitName + space + storeCode + "        " + storeName + space2);
        }
        dialog.setWorkText(" " + storeCode + "-" + storeName);
        Thread.sleep(1234);

        String store = null;
        boolean run = true;
        while (run) {
            try {
                // ?
                HttpPost seachStore = mHttpUtil
                        .doPost("https://homesis.homecredit.cn/hsis/api/salesroom/search", headers, data);
                store = mHttpUtil.executeForBody(seachStore);
                //System.out.println(store);
                run = false;
            } catch (Exception e) {
                System.out.print("/");
            }
        }

        if (store.contains("HTTP/1.1 500 Internal Server Error")) {
            System.out.println("?(?)");
            break;
        }
        JSONObject json = null;
        try {
            json = JSONObject.parseObject(store);
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println(store);
            continue;
        }
        if (json == null) {
            System.out.println("?");
            continue;
        }

        JSONArray array = json.getJSONArray("data");
        if (array.size() == 0) {
            noStores.add(storeCode);
            cell.setCellValue("?");
            System.out.println("?");
            continue;
        }
        JSONObject obj = (JSONObject) array.get(0);

        //  ID
        int id = obj.getIntValue("id");
        //System.out.println(id);

        String storePic = null;
        run = true;
        while (run) {
            try {
                // 
                HttpGet seachPic = mHttpUtil
                        .doGet("https://homesis.homecredit.cn/hsis/api/document/salesroom/all/" + id, headers);
                storePic = mHttpUtil.executeForBody(seachPic);
                //                    System.out.println(storePic);
                run = false;
            } catch (Exception e) {
                System.out.print("/");
            }
        }

        json = JSONObject.parseObject(storePic);
        array = json.getJSONArray("documents");
        if (array.size() == 0) {
            noPics.add(storeCode);
            cell.setCellValue("");
            System.out.println("");
            continue;
        }

        boolean print = true;
        for (int j = 0; j < array.size(); j++) {
            obj = (JSONObject) array.get(j);
            //  ID
            int picid = obj.getIntValue("id");
            String fileName = obj.getString("fileName");
            if (fileName.contains("POSRepID"))
                continue;
            //                fileName = fileName.substring(0, fileName.indexOf("-"));//+fileName.substring(fileName.indexOf("."));
            run = true;
            while (run) {
                try {
                    // 
                    HttpGet down = mHttpUtil.doGet(
                            "https://homesis.homecredit.cn/hsis/api/document/download/" + picid, headers);
                    String result = mHttpUtil.downPic(down, Util
                            .mkdirsIfNeed(outPath + File.separator + visitName, storeCode + "-" + fileName));
                    if (print) {
                        print = false;
                        System.out.println(fileName.substring(0, fileName.indexOf("-")));
                    }
                    run = false;
                } catch (Exception e) {
                    System.out.println();
                    e.printStackTrace();
                    System.out.println();
                    System.out.println("?/");
                    System.out.println();
                }
            }
        }
    }
    xssfWorkbook.write(new FileOutputStream(filePath.replace(".xlsx", "_pic.xlsx")));
    xssfWorkbook.close();
    xssfWorkbook = null;

    in.close();
    in = null;

    if (noStores.size() > 0) {
        System.out.println();
        System.out.println();
        System.out.println("??");
        System.out.println(
                "");
        for (int k = 0; k < noStores.size(); k++) {
            System.out.println(noStores.get(k));
        }
    }

    if (noPics.size() > 0) {
        System.out.println();
        System.out.println();
        System.out.println("?");
        System.out.println(
                "");
        for (int k = 0; k < noPics.size(); k++) {
            System.out.println(noPics.get(k));
        }
    }

    dialog.dispose(2);
}

From source file:org.apache.fineract.accounting.closure.storeglaccountbalance.service.GLClosureJournalEntryBalanceReadPlatformServiceImpl.java

License:Apache License

/**
 * Create the excel file with the balance report data
 * /*from   w  w w .j a  va  2s .  c  om*/
 * @param reportDataList
 * @return {@link File} object
 */
@SuppressWarnings("unused")
private File createGLClosureAccountBalanceReportExcelFile(
        final Collection<GLClosureAccountBalanceReportData> reportDataList) {
    File file = null;

    try {
        if (reportDataList != null) {
            final String[] columnTitles = new String[13];

            columnTitles[0] = "AccountCostCentre";
            columnTitles[1] = "AccountDepartment";
            columnTitles[2] = "AccountNumber";
            columnTitles[3] = "TransactionType";
            columnTitles[4] = "TransactionDate";
            columnTitles[5] = "GoodsAmount";
            columnTitles[6] = "Reference";
            columnTitles[7] = "Narrative";
            columnTitles[8] = "UniqueReferenceNumber";
            columnTitles[9] = "UserNumber";
            columnTitles[10] = "Source";
            columnTitles[11] = "PostedDate";
            columnTitles[12] = "TransactionAnalysisCode";

            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet spreadsheet = workbook.createSheet(" nominaltransactions ");
            XSSFRow row;
            XSSFFont font;
            XSSFCellStyle style;
            XSSFDataFormat dataFormat;

            int rowId = 0;
            int cellId = 0;

            row = spreadsheet.createRow(rowId++);

            for (String columnTitle : columnTitles) {
                font = workbook.createFont();
                style = workbook.createCellStyle();

                font.setBold(true);
                font.setFontName("Arial");
                font.setFontHeightInPoints((short) 10);
                style.setFont(font);

                Cell cell = row.createCell(cellId++);

                cell.setCellValue(columnTitle);
                cell.setCellStyle(style);
            }

            for (GLClosureAccountBalanceReportData reportData : reportDataList) {
                row = spreadsheet.createRow(rowId++);
                font = workbook.createFont();
                dataFormat = workbook.createDataFormat();

                font.setFontName("Arial");
                font.setFontHeightInPoints((short) 10);
                font.setBold(false);

                // ====================================================
                Cell cell = row.createCell(2);
                style = workbook.createCellStyle();

                cell.setCellType(Cell.CELL_TYPE_STRING);
                style.setDataFormat(dataFormat.getFormat("@"));
                style.setFont(font);
                cell.setCellValue(reportData.getAccountNumber());
                cell.setCellStyle(style);
                // ====================================================

                // ====================================================
                if (reportData.getTransactionType() != null) {
                    cell = row.createCell(3);
                    style = workbook.createCellStyle();

                    style.setFont(font);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(reportData.getTransactionType().getValue());
                    cell.setCellStyle(style);
                }
                // ====================================================

                // ====================================================
                if (reportData.getTransactionDate() != null) {
                    cell = row.createCell(4);
                    style = workbook.createCellStyle();

                    Date transactionDate = reportData.getTransactionDate().toDate();

                    style.setDataFormat(dataFormat.getFormat("MM/DD/YY"));
                    style.setFont(font);
                    cell.setCellValue(transactionDate);
                    cell.setCellStyle(style);
                }
                // ====================================================

                // ====================================================
                if (reportData.getAmount() != null) {
                    cell = row.createCell(5);
                    style = workbook.createCellStyle();

                    Double amount = reportData.getAmount().doubleValue();

                    style.setDataFormat(dataFormat.getFormat("0.00"));
                    style.setFont(font);
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(amount);
                    cell.setCellStyle(style);
                }
                // ====================================================

                // ====================================================
                if (reportData.getReference() != null) {
                    cell = row.createCell(6);
                    style = workbook.createCellStyle();

                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    style.setDataFormat(dataFormat.getFormat("@"));
                    style.setFont(font);
                    cell.setCellValue(reportData.getReference());
                    cell.setCellStyle(style);
                }
                // ====================================================

                // ====================================================
                if (reportData.getPostedDate() != null) {
                    cell = row.createCell(11);
                    style = workbook.createCellStyle();

                    Date postedDate = reportData.getPostedDate().toDate();

                    style.setDataFormat(dataFormat.getFormat("MM/DD/YY"));
                    style.setFont(font);
                    cell.setCellValue(postedDate);
                    cell.setCellStyle(style);
                }
                // ====================================================
            }

            final String fileDirectory = FileSystemContentRepository.MIFOSX_BASE_DIR + File.separator + "";

            if (!new File(fileDirectory).isDirectory()) {
                new File(fileDirectory).mkdirs();
            }

            file = new File(fileDirectory + "gl_closure_account_balance_report.xls");

            FileOutputStream fileOutputStream = new FileOutputStream(file);

            workbook.write(fileOutputStream);

            fileOutputStream.close();
        }
    }

    catch (Exception exception) {
        logger.error(exception.getMessage(), exception);
    }

    return file;
}

From source file:org.apache.fineract.infrastructure.dataexport.helper.XlsFileHelper.java

License:Apache License

public static void createFile(final SqlRowSet sqlRowSet, final File file,
        final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap,
        final DataExportCoreTable coreTable) {
    try {/*from   ww  w . j ava  2  s  .  c  o m*/
        final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData();
        final int columnCount = sqlRowSetMetaData.getColumnCount();

        // Create a new spreadsheet workbook
        XSSFWorkbook workbook = new XSSFWorkbook();
        // Create a blank sheet for the workbook
        XSSFSheet sheet = workbook.createSheet();
        // create a new cell style object
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        // create a new data format object 
        XSSFDataFormat dataFormat = workbook.createDataFormat();

        int rowIndex = 0;
        int columnIndex = 0;
        Row row = sheet.createRow(rowIndex++);

        for (int i = 1; i <= columnCount; i++) {
            // create a new cell for each columns for the header row
            Cell cell = row.createCell(columnIndex++);
            // get the column label of the dataset
            String columnLabel = DataExportUtils
                    .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable);
            // set the value of the cell
            cell.setCellValue(WordUtils.capitalize(columnLabel));
        }

        while (sqlRowSet.next()) {
            columnIndex = 0;
            row = sheet.createRow(rowIndex++);

            for (int i = 1; i <= columnCount; i++) {
                Cell cell = row.createCell(columnIndex++);
                String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i);
                MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName);
                String columnValue = sqlRowSet.getString(i);
                String columnName = sqlRowSetMetaData.getColumnName(i);

                // replace code value id with the code value name
                AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils
                        .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                // replace app user id with respective username
                columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName,
                        columnValue, mysqlDataType);

                // update the column value
                columnValue = columnValueDataType.getKey();

                // update the data type
                mysqlDataType = columnValueDataType.getValue();

                if (columnValue != null) {
                    switch (mysqlDataType.getCategory()) {
                    case NUMERIC:
                        // TINYINT(1) is also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit
                        // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212
                        if (mysqlDataType.equals(MysqlDataType.TINYINT)
                                && sqlRowSetMetaData.getPrecision(i) == 1
                                && (columnValue.equals("true") || columnValue.equals("false"))) {
                            // Handle the cell as string, it is already a casted boolean:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            cell.setCellValue(columnValue);

                        } else {
                            double numberAsDouble = Double.parseDouble(columnValue);

                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(numberAsDouble);
                        }
                        break;

                    case DATE_TIME:
                        DateFormat dateFormat;
                        Date date;

                        switch (mysqlDataType) {
                        case DATE:
                        case DATETIME:
                            String mysqlDateFormat = "yyyy-MM-dd";
                            String excelDateFormat = "MM/DD/YYYY";

                            if (mysqlDataType.equals(MysqlDataType.DATETIME)) {
                                mysqlDateFormat = "yyyy-MM-dd HH:mm:ss";
                                excelDateFormat = "MM/DD/YYYY HH:MM:SS";
                            }

                            dateFormat = new SimpleDateFormat(mysqlDateFormat);
                            date = dateFormat.parse(columnValue);

                            cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat));

                            cell.setCellValue(date);
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellStyle(cellStyle);
                            break;

                        default:
                            cell.setCellValue(columnValue);
                            break;
                        }
                        break;

                    default:
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        cell.setCellValue(columnValue);
                        break;
                    }

                } else {
                    cell.setCellValue(columnValue);
                }
            }
        }

        //Write the workbook in file system
        FileOutputStream fileOutputStream = new FileOutputStream(file);

        workbook.write(fileOutputStream);
        fileOutputStream.close();

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

From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java

License:Apache License

public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) {
    report.println();/*from w w  w .  j  a  v a  2  s .co  m*/
    report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale),
            InterfaceReport.FORMAT_NOTE);
    FileOutputStream fos = null;
    XSSFCreationHelper factory = workbook.getCreationHelper();
    XSSFFont boldFont = workbook.createFont();
    boldFont.setFontName("Arial");
    boldFont.setBold(true);
    boldFont.setCharSet(134);
    boldFont.setFontHeightInPoints((short) 9);
    XSSFFont plainFont = workbook.createFont();
    plainFont.setFontName("Arial");
    plainFont.setCharSet(134);
    plainFont.setFontHeightInPoints((short) 9);

    XSSFSheet errorSheet = null;
    if (errorMessages.keySet().size() > 0) {
        String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors";
        errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName);
        errorSheet = workbook.createSheet(errorSheetName);
        workbook.setSheetOrder(errorSheetName, 0);
        workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName));
        XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch();
        if (drawingPatriarch == null) {
            drawingPatriarch = errorSheet.createDrawingPatriarch();
        }
        for (int i = 0; i <= getHeaderRowNo(); i++) {
            XSSFRow newRow = errorSheet.createRow(i);
            XSSFRow row = sheet.getRow(i);
            newRow.setHeight(row.getHeight());
            copyRow(row, newRow, factory, drawingPatriarch);
        }

        // copy merged regions
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            if (mergedRegion.getFirstRow() < getHeaderRowNo()) {
                errorSheet.addMergedRegion(mergedRegion);
            }
        }

        // copy images
        List<XSSFPictureData> pics = workbook.getAllPictures();
        List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes();
        for (int i = 0; i < shapes.size(); i++) {
            XSSFShape shape = shapes.get(i);
            XSSFAnchor anchor = shape.getAnchor();
            if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) {
                XSSFPicture pic = (XSSFPicture) shape;
                XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor;
                if (clientAnchor.getRow1() < getHeaderRowNo()) {
                    for (int j = 0; j < pics.size(); j++) {
                        XSSFPictureData picture = pics.get(j);
                        if (picture.getPackagePart().getPartName()
                                .equals(pic.getPictureData().getPackagePart().getPartName())) {
                            drawingPatriarch.createPicture(clientAnchor, j);
                        }
                    }
                }
            }
        }
    }

    try {
        // set comments in the original sheet
        XSSFDrawing patriarch = sheet.getDrawingPatriarch();
        for (CellReference cell : errorMessages.keySet()) {
            if (cell != null && errorMessages.get(cell) != null) {
                XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol()));
                boolean isNewComment = false;
                if (comment == null) {
                    XSSFClientAnchor anchor = factory.createClientAnchor();
                    anchor.setDx1(100);
                    anchor.setDx2(100);
                    anchor.setDy1(100);
                    anchor.setDy2(100);
                    anchor.setCol1(cell.getCol());
                    anchor.setCol2(cell.getCol() + 4);
                    anchor.setRow1(cell.getRow());
                    anchor.setRow2(cell.getRow() + 4);
                    anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);

                    comment = patriarch.createCellComment(anchor);
                    isNewComment = true;
                }
                XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n");
                rts.applyFont(boldFont);
                rts.append(errorMessages.get(cell), plainFont);
                comment.setString(rts);
                comment.setAuthor("Apache OFBiz PriCat");
                if (isNewComment) {
                    sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment);
                    OFBizPricatUtil.formatCommentShape(sheet, cell);
                }
            }
        }

        // set comments in the new error sheet
        XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch();
        int newRowNum = getHeaderRowNo() + 1;
        Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>();
        for (CellReference cell : errorMessages.keySet()) {
            if (cell != null && errorMessages.get(cell) != null) {
                XSSFRow row = sheet.getRow(cell.getRow());
                Integer rowNum = Integer.valueOf(row.getRowNum());
                int errorRow = newRowNum;
                if (rowMapping.containsKey(rowNum)) {
                    errorRow = rowMapping.get(rowNum).intValue();
                } else {
                    XSSFRow newRow = errorSheet.getRow(errorRow);
                    if (newRow == null) {
                        newRow = errorSheet.createRow(errorRow);
                    }
                    rowMapping.put(rowNum, Integer.valueOf(errorRow));
                    newRow.setHeight(row.getHeight());
                    copyRow(row, newRow, factory, errorPatriarch);
                    newRowNum++;
                }
            }
        }

        // write to file
        if (sequenceNum > 0L) {
            File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx");
            fos = new FileOutputStream(commentedExcel);
            workbook.write(fos);
        } else {
            fos = new FileOutputStream(pricatFile);
            workbook.write(fos);
        }
        fos.flush();
        fos.close();
        workbook.close();
    } catch (FileNotFoundException e) {
        report.println(e);
        Debug.logError(e, module);
    } catch (IOException e) {
        report.println(e);
        Debug.logError(e, module);
    } finally {
        if (fos != null) {
            try {
                fos.close();
            } catch (IOException e) {
                Debug.logError(e, module);
            }
        }
        if (workbook != null) {
            try {
                workbook.close();
            } catch (IOException e) {
                Debug.logError(e, module);
            }
        }
    }
    report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK);
    report.println();
}

From source file:org.appdynamics.licensecount.file.WriteExcelDoc.java

public void init() {
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet licenseSummary = workbook.createSheet(LicenseS.LICENSE_SUMMARY);
    XSSFSheet licenseTiers = workbook.createSheet(LicenseS.TIER_SUMMARY);
    XSSFSheet licenseHourlyTiers = workbook.createSheet(LicenseS.HOURLY_TIER_SUMMARY);
    XSSFSheet licenseNodeInfo = workbook.createSheet(LicenseS.NODE_INFO_SUMMARY);
    XSSFSheet licenseNoNodeTiers = workbook.createSheet(LicenseS.TIERS_WITH_NO_NODES);

    style = workbook.createCellStyle();/*ww w  . j a v a  2  s.c  om*/
    style.setAlignment(HorizontalAlignment.RIGHT);
    addNodeInfo(licenseNodeInfo);
    addTierWNoNodeInfo(licenseNoNodeTiers);
    // Lets create the first row which will be the header.
    int headerRowIndex = 0;
    Row headerRow = licenseSummary.createRow(headerRowIndex);
    Row tierRow = licenseTiers.createRow(headerRowIndex);
    Row hourlyTierRow = licenseHourlyTiers.createRow(headerRowIndex);

    int i = 0;
    Cell cell_1 = headerRow.createCell(i);
    cell_1.setCellValue(LicenseS.CUSTOMER_NAME);

    Cell cell_2 = tierRow.createCell(i);
    cell_2.setCellValue(LicenseS.APPLICATION_NAME);
    cell_2 = tierRow.createCell(i + 1);
    cell_2.setCellValue(LicenseS.TIER_NAME);

    Cell cell_3 = hourlyTierRow.createCell(i);
    cell_3.setCellValue(LicenseS.APPLICATION_NAME);
    cell_3 = hourlyTierRow.createCell(i + 1);
    cell_3.setCellValue(LicenseS.TIER_NAME);

    i += 2;

    int columnCount = 2;
    int columnCount1 = 3;
    // Create the date headers
    for (CustomerLicenseRange cRange : customer.getCustomerRangeValues()) {
        cell_1 = headerRow.createCell(columnCount);
        cell_1.setCellValue(cRange.getColumnName());
        cell_2 = tierRow.createCell(columnCount1);
        cell_2.setCellValue(cRange.getColumnName());
        columnCount++;
        columnCount1++;
    }

    i = addCustomer(licenseSummary, i);
    //logger.log(Level.INFO,"Next row " + ++i);
    headerRow = licenseSummary.createRow(++i);
    cell_1 = headerRow.createCell(0);
    cell_1.setCellValue(LicenseS.APPLICATION_NAME);
    i++;
    int tierRowCount = 2;
    int createdHourlyTierHeader = 0;
    columnCount1 = 3;

    //logger.log(Level.INFO,new StringBuilder().append("\n\n\tNumber of applications ").append(customer.getApplications().size()).toString());
    for (ApplicationLicenseCount app : customer.getApplications().values()) {
        i = addApplication(licenseSummary, i, app);
        int inCount = 0;
        for (TierLicenseCount tier : app.getTierLicenses().values()) {
            if (createdHourlyTierHeader == 0) {
                for (TierHourLicenseRange tr : tier.getTierHourLicenseRange()) {
                    cell_3 = hourlyTierRow.createCell(columnCount1);
                    cell_3.setCellValue(tr.getHourColumnName());
                    columnCount1++;
                }
                createdHourlyTierHeader = 1;
            }
            tierRowCount = addTier(licenseTiers, tierRowCount, tier, app.getApplicationName(), inCount);
            inCount++;
        }
        if (inCount != 0)
            tierRowCount++;
        i++;
    }

    tierRowCount = 2;
    for (ApplicationLicenseCount app : customer.getApplications().values()) {
        //i=addApplication(licenseSummary,i,app);
        int inCount = 0;
        tierRowCount = addHourlyApp(licenseHourlyTiers, tierRowCount, app, inCount);

        for (TierLicenseCount tier : app.getTierLicenses().values()) {
            tierRowCount = addHourlyTier(licenseHourlyTiers, tierRowCount, tier, app.getApplicationName(),
                    inCount);
            inCount++;
        }
        tierRowCount++;
        i++;
    }

    try {
        //Write the workbook in file system
        //String fileName=new StringBuilder().append("/Users/gilbert.solorzano/Documents/").append(customer.getName()).append("LicenseFile.xlsx").toString();

        FileOutputStream out = new FileOutputStream(new File(LicenseS.FILENAME_V));
        workbook.write(out);
        out.close();
        StringBuilder bud = new StringBuilder();
        bud.append("Completed writing the file: ").append(LicenseS.FILENAME_V).append(".");
        logger.log(Level.INFO, bud.toString());
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:org.appdynamics.utilreports.files.ProcessExcelFile.java

public void init() {

    XSSFWorkbook workbook = new XSSFWorkbook();
    //logger.log(Level.INFO,"Start excel sheet.");
    for (GatherLoadCheck glc : loadChecks) {
        //String app=glc.getLc().getApplication();
        //Create a blank sheet
        for (LoadCheck lc : glc.getLoadCheckList()) {
            //logger.log(Level.INFO,new StringBuilder().append("Sheet Name ").append(lc.getSheetName()).append(", ").append(lc.getAppName()).toString());
            XSSFSheet _sheet = workbook.createSheet(new StringBuilder().append(lc.getSheetName())
                    .append(AppDUtilReportS._U).append(lc.getAppId()).toString());
            processCheck(_sheet, lc);//w  w  w  . j  a  v  a2  s . co  m
        }

    }

    try {
        //Write the workbook in file system
        //String fileName=new StringBuilder().append("/Users/gilbert.solorzano/Documents/").append(customer.getName()).append("LicenseFile.xlsx").toString();

        FileOutputStream out = new FileOutputStream(fileName);
        workbook.write(out);
        out.close();

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

From source file:org.azkfw.doclet.jaxrs.writer.JAXRSXlsxDocletWriter.java

License:Apache License

public void write(final RootDoc root) {
    try {/*from   ww w.java2 s .c o m*/

        parser.addListener(new JAXRSDocletParserListener() {
            @Override
            public void jaxrsDocletParserFindMethodDoc(JAXRSDocletParserEvent event, ClassDoc classDoc,
                    MethodDoc methodDoc) {
            }

            @Override
            public void jaxrsDocletParserFindClassDoc(JAXRSDocletParserEvent event, ClassDoc classDoc) {
            }

            @Override
            public void jaxrsDocletParserFindAreas(JAXRSDocletParserEvent event, AreasModel model) {

            }

            @Override
            public void jaxrsDocletParserFindAPI(final JAXRSDocletParserEvent event, final APIModel model) {
                addApi(model);
            }
        });

        parser.parse(root);

        List<APIModel> bufApis = new ArrayList<>();
        for (String key : apis.keySet()) {
            bufApis.add(apis.get(key));
        }
        Collections.sort(bufApis, new Comparator<APIModel>() {
            @Override
            public int compare(APIModel o1, APIModel o2) {
                String s1 = o1.getPath();
                String s2 = o2.getPath();
                if (null == s1 && null == s2) {
                    return 0;
                } else if (null == s2) {
                    return 1;
                } else if (null == s1) {
                    return -1;
                } else {
                    return s1.compareTo(s2);
                }
            }
        });

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheetAPIList = wb.createSheet("IF");
        printSheet(wb, sheetAPIList, bufApis);

        for (APIModel api : bufApis) {
            if (null != api.getId() && 0 < api.getId().length()) {
                XSSFSheet sheetApi = wb.createSheet(api.getId());
                printSheet(wb, sheetApi, api);
            }
        }

        FileOutputStream out = new FileOutputStream(new File("sample.xlsx"));
        wb.write(out);
        out.close();

    } catch (DocletParserException ex) {
        ex.printStackTrace();
    } catch (IOException ex) {
        ex.printStackTrace();
    }
}

From source file:org.azkfw.document.database.xlsx.XLSXWriter.java

License:Apache License

public boolean write(final DatabaseModel database, final WriterOption option, final File destFile) {
    this.option = option;
    if (null == this.option) {
        this.option = new WriterOption();
        ;//from  w ww. j a  v  a 2s .  c o  m
    }

    XSSFWorkbook workbook = write(database);

    FileOutputStream stream = null;
    try {
        stream = new FileOutputStream(destFile);
        workbook.write(stream);
    } catch (IOException ex) {
        ex.printStackTrace();
    } finally {
        if (null != stream) {
            try {
                stream.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
    }
    return true;
}