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

Example 6 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 7 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 8 with XSSFWorkbook

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

the class TestXLSXResponseWriter method getWSResultForQuery.

private XSSFSheet getWSResultForQuery(SolrQueryRequest req, SolrQueryResponse rsp) throws IOException, Exception {
    ByteArrayOutputStream xmlBout = new ByteArrayOutputStream();
    writerXlsx.write(xmlBout, req, rsp);
    XSSFWorkbook output = new XSSFWorkbook(new ByteArrayInputStream(xmlBout.toByteArray()));
    XSSFSheet sheet = output.getSheetAt(0);
    req.close();
    output.close();
    return sheet;
}

Example 9 with XSSFWorkbook

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

the class PieChartDemo method main.

public static void main(String[] args) throws Exception {
    if (args.length < 2) {
        usage();
        return;
    }
    BufferedReader modelReader = new BufferedReader(new FileReader(args[1]));
    XMLSlideShow pptx = null;
    try {
        // first line is chart title
        String chartTitle = modelReader.readLine();
        pptx = new XMLSlideShow(new FileInputStream(args[0]));
        XSLFSlide slide = pptx.getSlides().get(0);
        // find chart in the slide
        XSLFChart chart = null;
        for (POIXMLDocumentPart part : slide.getRelations()) {
            if (part instanceof XSLFChart) {
                chart = (XSLFChart) part;
                break;
            }
        }
        if (chart == null)
            throw new IllegalStateException("chart not found in the template");
        // embedded Excel workbook that holds the chart data
        POIXMLDocumentPart xlsPart = chart.getRelations().get(0);
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            XSSFSheet sheet = wb.createSheet();
            CTChart ctChart = chart.getCTChart();
            CTPlotArea plotArea = ctChart.getPlotArea();
            CTPieChart pieChart = plotArea.getPieChartArray(0);
            //Pie Chart Series
            CTPieSer ser = pieChart.getSerArray(0);
            // Series Text
            CTSerTx tx = ser.getTx();
            tx.getStrRef().getStrCache().getPtArray(0).setV(chartTitle);
            sheet.createRow(0).createCell(1).setCellValue(chartTitle);
            String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString();
            tx.getStrRef().setF(titleRef);
            // Category Axis Data
            CTAxDataSource cat = ser.getCat();
            CTStrData strData = cat.getStrRef().getStrCache();
            // Values
            CTNumDataSource val = ser.getVal();
            CTNumData numData = val.getNumRef().getNumCache();
            // unset old axis text
            strData.setPtArray(null);
            // unset old values
            numData.setPtArray(null);
            // set model
            int idx = 0;
            int rownum = 1;
            String ln;
            while ((ln = modelReader.readLine()) != null) {
                String[] vals = ln.split("\\s+");
                CTNumVal numVal = numData.addNewPt();
                numVal.setIdx(idx);
                numVal.setV(vals[1]);
                CTStrVal sVal = strData.addNewPt();
                sVal.setIdx(idx);
                sVal.setV(vals[0]);
                idx++;
                XSSFRow row = sheet.createRow(rownum++);
                row.createCell(0).setCellValue(vals[0]);
                row.createCell(1).setCellValue(Double.valueOf(vals[1]));
            }
            numData.getPtCount().setVal(idx);
            strData.getPtCount().setVal(idx);
            String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
            val.getNumRef().setF(numDataRange);
            String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true);
            cat.getStrRef().setF(axisDataRange);
            // updated the embedded workbook with the data
            OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream();
            try {
                wb.write(xlsOut);
            } finally {
                xlsOut.close();
            }
            // save the result
            OutputStream out = new FileOutputStream("pie-chart-demo-output.pptx");
            try {
                pptx.write(out);
            } finally {
                out.close();
            }
        } finally {
            wb.close();
        }
    } finally {
        if (pptx != null)
            pptx.close();
        modelReader.close();
    }
}

Example 10 with XSSFWorkbook

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

the class SettingExternalFunction method main.

public static void main(String[] args) throws IOException {
    // or new HSSFWorkbook()
    Workbook wb = new XSSFWorkbook();
    // register the add-in
    wb.addToolPack(new BloombergAddIn());
    Sheet sheet = wb.createSheet();
    Row row = sheet.createRow(0);
    row.createCell(0).setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100");
    row.createCell(1).setCellFormula("BDH(\"goog us equity\",\"EBIT\",\"1/1/2005\",\"12/31/2009\",\"per=cy\",\"curr=USD\") ");
    row.createCell(2).setCellFormula("BDS(\"goog us equity\",\"top_20_holders_public_filings\") ");
    FileOutputStream out = new FileOutputStream("bloomberg-demo.xlsx");
    wb.write(out);
    out.close();
    wb.close();
}