Examples with XSSFWorkbook org.apache.poi.xssf.usermodel.XSSFWorkbook used on opensource projects

Example 1 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project textdb by TextDB.

the class ExcelSink method open.

@Override
public void open() throws TextDBException {
    if (cursor != CLOSED) {
        return;
    }
    inputOperator.open();
    inputSchema = inputOperator.getOutputSchema();
    outputSchema = new Schema(inputSchema.getAttributes().stream().filter(attr -> !attr.getAttributeName().equalsIgnoreCase(SchemaConstants._ID)).filter(attr -> !attr.getAttributeName().equalsIgnoreCase(SchemaConstants.PAYLOAD)).filter(attr -> !attr.getAttributeType().equals(AttributeType.LIST)).toArray(Attribute[]::new));
    wb = new XSSFWorkbook();
    DateFormat df = new SimpleDateFormat("yyyyMMdd-HHmmss");
    fileName = df.format(new Date()) + ".xlsx";
    try {
        if (Files.notExists(Paths.get(excelIndexDirectory))) {
            Files.createDirectories(Paths.get(excelIndexDirectory));
        }
        fileOut = new FileOutputStream(Paths.get(excelIndexDirectory, fileName).toString());
    } catch (IOException e) {
        throw new DataFlowException(e);
    }
    sheet = wb.createSheet("new sheet");
    Row row = sheet.createRow(0);
    List<String> attributeNames = outputSchema.getAttributeNames();
    for (int i = 0; i < attributeNames.size(); i++) {
        String attributeName = attributeNames.get(i);
        row.createCell(i).setCellValue(attributeName);
    }
    cursor = OPENED;
}

Example 2 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project cubrid-manager by CUBRID.

the class Export method exportXlsx.

/**
	 * export all data in Query Editor result table cache as xlsx
	 *
	 * @param monitor IProgressMonitor
	 * @throws IOException if failed
	 */
private void exportXlsx(final IProgressMonitor monitor) throws IOException {
    // FIXME move this logic to core module
    // 1048576: limit xlsx row number except for column row.
    final int rowLimit = ImportFileConstants.XLSX_ROW_LIMIT - 1;
    // 16384: limit xlsx column number.
    final int columnLimit = ImportFileConstants.XLSX_COLUMN_LIMIT;
    final int cellCharacterLimit = ImportFileConstants.XLSX_CELL_CHAR_LIMIT;
    //		//create dateformat
    //		SimpleDateFormat datetimeSdf = new SimpleDateFormat(
    //				"yyyy-MM-dd HH:mm:ss.SSS", Locale.getDefault());
    //		SimpleDateFormat timestampSdf = new SimpleDateFormat(
    //				"yyyy-MM-dd HH:mm:ss", Locale.getDefault());
    //		SimpleDateFormat dateSdf = new SimpleDateFormat("yyyy-MM-dd",
    //				Locale.getDefault());
    //		SimpleDateFormat timeSdf = new SimpleDateFormat("HH:mm:ss",
    //				Locale.getDefault());
    //		Date date = null;
    //create memory workbook
    XlsxWriterHelper xlsxWriterhelper = new XlsxWriterHelper();
    XSSFWorkbook workbook = new XSSFWorkbook();
    //		Calendar cal = Calendar.getInstance();
    //		int datetimeStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(
    //				workbook).get("datetime")).getIndex();
    //		int timestampStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(
    //				workbook).get("timestamp")).getIndex();
    //		int dateStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(
    //				workbook).get("date")).getIndex();
    //		int timeStyleIndex = ((XSSFCellStyle) xlsxWriterhelper.getStyles(
    //				workbook).get("time")).getIndex();
    Map<String, File> fileMap = new HashMap<String, File>();
    try {
        for (int k = 0; k < resultDataList.size(); k++) {
            List<ColumnInfo> columnList = resultColsList.get(k);
            List<Map<String, String>> dataList = resultDataList.get(k);
            int colCount = columnList.size();
            int itemCount = dataList.size();
            if (colCount > columnLimit) {
                if (!CommonUITool.openConfirmBox(Messages.columnCountOver)) {
                    return;
                }
                colCount = columnLimit;
            }
            XlsxWriterHelper.SpreadsheetWriter sheetWriter = null;
            try {
                sheetWriter = createSheetWriter(workbook, "Sheet " + (k + 1), fileMap);
                //export columns
                exportColumnsForXLSX(sheetWriter, k, columnLimit);
                int sheetNum = 0;
                for (int i = 0, xssfRowNum = 1; i < itemCount; i++) {
                    sheetWriter.insertRow(xssfRowNum);
                    for (int j = 0; j < colCount; j++) {
                        String colType = columnList.get(j).getType();
                        String colIndex = columnList.get(j).getIndex();
                        String cellValue = dataList.get(i).get(colIndex);
                        int cellType = FieldHandlerUtils.getCellType(colType, cellValue);
                        switch(cellType) {
                            case -1:
                                sheetWriter.createCell(j, DataType.NULL_EXPORT_FORMAT);
                                break;
                            case 0:
                                sheetWriter.createCell(j, Long.parseLong(cellValue));
                                break;
                            case 1:
                                sheetWriter.createCell(j, Double.parseDouble(cellValue));
                                break;
                            //								break;
                            case 2:
                            default:
                                String cellStr = cellValue.toString().length() > cellCharacterLimit ? cellValue.toString().substring(0, cellCharacterLimit) : cellValue.toString();
                                sheetWriter.createCell(j, covertXMLString(cellStr));
                                break;
                        }
                    }
                    sheetWriter.endRow();
                    xssfRowNum++;
                    if (((i + 1) % rowLimit) == 0 && (i + 1) < itemCount) {
                        sheetNum++;
                        try {
                            XlsxWriterHelper.writeSheetWriter(sheetWriter);
                        } catch (IOException e) {
                            sheetWriter = null;
                            throw e;
                        }
                        sheetWriter = createSheetWriter(workbook, "Sheet " + (k + 1) + "_" + sheetNum, fileMap);
                        exportColumnsForXLSX(sheetWriter, k, columnLimit);
                        xssfRowNum = 1;
                    }
                    exportedCount++;
                    monitor.subTask(Messages.bind(com.cubrid.common.ui.cubrid.table.Messages.msgExportDataRow, exportedCount));
                }
            } finally {
                try {
                    XlsxWriterHelper.writeSheetWriter(sheetWriter);
                } catch (IOException e) {
                    sheetWriter = null;
                    throw e;
                }
            }
        }
    } finally {
        XlsxWriterHelper.writeWorkbook(workbook, xlsxWriterhelper, fileMap, file);
    }
}

Example 3 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project OpenRefine by OpenRefine.

the class ExcelImporter method createParserUIInitializationData.

@Override
public JSONObject createParserUIInitializationData(ImportingJob job, List<JSONObject> fileRecords, String format) {
    JSONObject options = super.createParserUIInitializationData(job, fileRecords, format);
    JSONArray sheetRecords = new JSONArray();
    JSONUtilities.safePut(options, "sheetRecords", sheetRecords);
    try {
        if (fileRecords.size() > 0) {
            JSONObject firstFileRecord = fileRecords.get(0);
            File file = ImportingUtilities.getFile(job, firstFileRecord);
            InputStream is = new FileInputStream(file);
            if (!is.markSupported()) {
                is = new PushbackInputStream(is, 8);
            }
            try {
                Workbook wb = POIXMLDocument.hasOOXMLHeader(is) ? new XSSFWorkbook(is) : new HSSFWorkbook(new POIFSFileSystem(is));
                int sheetCount = wb.getNumberOfSheets();
                boolean hasData = false;
                for (int i = 0; i < sheetCount; i++) {
                    Sheet sheet = wb.getSheetAt(i);
                    int rows = sheet.getLastRowNum() - sheet.getFirstRowNum() + 1;
                    JSONObject sheetRecord = new JSONObject();
                    JSONUtilities.safePut(sheetRecord, "name", sheet.getSheetName());
                    JSONUtilities.safePut(sheetRecord, "rows", rows);
                    if (hasData) {
                        JSONUtilities.safePut(sheetRecord, "selected", false);
                    } else if (rows > 1) {
                        JSONUtilities.safePut(sheetRecord, "selected", true);
                        hasData = true;
                    }
                    JSONUtilities.append(sheetRecords, sheetRecord);
                }
            } finally {
                is.close();
            }
        }
    } catch (IOException e) {
        logger.error("Error generating parser UI initialization data for Excel file", e);
    } catch (IllegalArgumentException e) {
        logger.error("Error generating parser UI initialization data for Excel file (only Excel 97 & later supported)", e);
    } catch (POIXMLException e) {
        logger.error("Error generating parser UI initialization data for Excel file - invalid XML", e);
    }
    return options;
}

Example 4 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project poi by apache.

the class XSSFSave method main.

public static void main(String[] args) throws Exception {
    for (int i = 0; i < args.length; i++) {
        OPCPackage pkg = OPCPackage.open(args[i]);
        XSSFWorkbook wb = new XSSFWorkbook(pkg);
        int sep = args[i].lastIndexOf('.');
        String outfile = args[i].substring(0, sep) + "-save.xls" + (wb.isMacroEnabled() ? "m" : "x");
        FileOutputStream out = new FileOutputStream(outfile);
        wb.write(out);
        out.close();
        pkg.close();
    }
}

Example 5 with XSSFWorkbook

use of org.apache.poi.xssf.usermodel.XSSFWorkbook in project poi by apache.

the class WorkingWithPictures method main.

public static void main(String[] args) throws IOException {
    //create a new workbook
    //or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    try {
        CreationHelper helper = wb.getCreationHelper();
        //add a picture in this workbook.
        InputStream is = new FileInputStream(args[0]);
        byte[] bytes = IOUtils.toByteArray(is);
        is.close();
        int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
        //create sheet
        Sheet sheet = wb.createSheet();
        //create drawing
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        //add a picture shape
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(1);
        anchor.setRow1(1);
        Picture pict = drawing.createPicture(anchor, pictureIdx);
        //auto-size picture
        pict.resize(2);
        //save workbook
        String file = "picture.xls";
        if (wb instanceof XSSFWorkbook) {
            // NOSONAR
            file += "x";
        }
        OutputStream fileOut = new FileOutputStream(file);
        try {
            wb.write(fileOut);
        } finally {
            fileOut.close();
        }
    } finally {
        wb.close();
    }
}