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

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

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

Introduction

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

Prototype

@Override
public XSSFSheet createSheet()

Source Link

Document

Create an XSSFSheet for this workbook, adds it to the sheets and returns the high level representation.

Usage

From source file:chocanproject.SummaryReports.java

private void writeToExcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();
    //Load data to Treemap
    TreeMap<String, Object[]> data = new TreeMap<>();
    //Add column headers
    data.put("-1", new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2) });
    //Add rows and cells
    for (int i = 0; i < dm.getRowCount(); i++) {
        data.put(Integer.toString(i),
                new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2) });
    }/*from  ww w.j ava2s  .  c o m*/
    //write to excel file
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);
        //get data as per key
        Object[] values = data.get(key);
        int cellID = 0;
        for (Object o : values) {
            Cell cell = row.createCell(cellID++);
            cell.setCellValue(o.toString());
        }
    }
    //write to filesystem
    try {
        DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY");
        Date date = new Date();
        String Cdate;
        Cdate = (dateFormat.format(date));
        FileOutputStream fos = new FileOutputStream(
                new File("D:/Excel/SummaryReport-ToBePaid" + Cdate + ".xlsx"));
        wb.write(fos);
        fos.close();
    } catch (FileNotFoundException ex) {
        ex.printStackTrace();
        // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        ex.printStackTrace();
        //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex);

    }
}

From source file:chocanproject.TotalSummaryReport.java

private void writeToExcel() {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet ws = wb.createSheet();
    //Load data to Treemap
    TreeMap<String, Object[]> data = new TreeMap<>();
    //Add column headers
    data.put("-1", new Object[] { dm.getColumnName(0), dm.getColumnName(1), dm.getColumnName(2) });
    //Add rows and cells
    for (int i = 0; i < dm.getRowCount(); i++) {
        data.put(Integer.toString(i),
                new Object[] { getCellValue(i, 0), getCellValue(i, 1), getCellValue(i, 2) });
    }//from  ww w  .ja  v a  2 s.  c om
    //write to excel file
    Set<String> ids = data.keySet();
    XSSFRow row;
    int rowID = 0;
    for (String key : ids) {
        row = ws.createRow(rowID++);
        //get data as per key
        Object[] values = data.get(key);
        int cellID = 0;
        for (Object o : values) {
            Cell cell = row.createCell(cellID++);
            cell.setCellValue(o.toString());
        }
    }
    //write to filesystem
    try {
        DateFormat dateFormat = new SimpleDateFormat("MM-dd-YYYY");
        Date date = new Date();
        String Cdate;
        Cdate = (dateFormat.format(date));
        FileOutputStream fos = new FileOutputStream(new File("D:/Excel/TotalSummaryReport" + Cdate + ".xlsx"));
        wb.write(fos);
        fos.close();
    } catch (FileNotFoundException ex) {
        ex.printStackTrace();
        // Logger.getLogger(WorkBookNSheet.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        ex.printStackTrace();
        //Logger.getLogger(MemberReportGUI.class.getName()).log(Level.SEVERE, null, ex);

    }
}

From source file:com.frameworkset.platform.sanylog.common.POIExcelUtil2007.java

License:Open Source License

/**
 * Excel Workbook?./*from  w w  w  .ja  v a  2 s  . c o m*/
 * 
 * @param colDesc 1717?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException 
 * @throws IllegalAccessException 
 * @throws IllegalArgumentException 
 */
public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {
    //???1717
    //TODO: ?
    List<String> colTitleList = getColumnTitleList(colDesc);
    List<String> colFieldList = getColumnFieldList(colDesc);

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

    XSSFFont font = getBaseFont(wb);
    XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    //?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    //CellStyle strCellStyle = getStringCellStyle(wb);

    //??1717
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    //
    XSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < colTitleList.size(); i++) {
        XSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(colTitleList.get(i));
    }

    //?1717
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            XSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            Object value = BeanConvertUtil.getProperty(obj, fieldName);
            //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            //??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue((Date) value);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    //??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    //
    for (int i = 0; i < colTitleList.size(); i++) {
        //??
        //sheet.autoSizeColumn(i);
    }

    return wb;
}

From source file:com.frameworkset.platform.util.POIExcelUtil.java

License:Open Source License

/**
 * Excel Workbook?./* w w  w .j a  v a2  s.co m*/
 * 
 * @param colDesc
 *            "?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException
 * @throws IllegalAccessException
 * @throws IllegalArgumentException
 */
public static XSSFWorkbook createHSSFWorkbook(List<String> titlesList, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {

    // ???
    List<String> colFieldList = getColumnFieldList(titlesList);

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

    XSSFFont font = getBaseFont(wb);
    XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    // ?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    // ??1717
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

    // 
    XSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < titlesList.size(); i++) {
        XSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(titlesList.get(i));
    }
    ClassInfo classInfo = null;
    // ?
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        if (classInfo == null)
            classInfo = ClassUtil.getClassInfo(obj.getClass());
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            XSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            PropertieDescription reflexField = classInfo.getPropertyDescriptor(fieldName);
            Object value = reflexField.getValue(obj);

            // ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            // Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            // ??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date || value instanceof Timestamp) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue(sdf.format((Date) value));
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                // cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    // ??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    // 
    for (int i = 0; i < titlesList.size(); i++) {
        // ??
        sheet.autoSizeColumn(i);
    }

    return wb;
}

From source file:com.frameworkset.platform.util.POIExcelUtil2007.java

License:Open Source License

/**
 * Excel Workbook?./*w w  w  .  java  2 s. c  om*/
 * 
 * @param colDesc ?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException 
 * @throws IllegalAccessException 
 * @throws IllegalArgumentException 
 */
public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {
    //???
    //TODO: ?
    List<String> colTitleList = getColumnTitleList(colDesc);
    List<String> colFieldList = getColumnFieldList(colDesc);

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

    XSSFFont font = getBaseFont(wb);
    XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    //?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    //CellStyle strCellStyle = getStringCellStyle(wb);

    //??
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    //
    XSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < colTitleList.size(); i++) {
        XSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(colTitleList.get(i));
    }

    //?
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        XSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            XSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            Object value = BeanConvertUtil.getProperty(obj, fieldName);
            //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            //??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue((Date) value);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    //??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    //
    for (int i = 0; i < colTitleList.size(); i++) {
        //??
        //sheet.autoSizeColumn(i);
    }

    return wb;
}

From source file:com.griffinslogistics.document.excel.BookLabelGenerator.java

private static void generate(XSSFWorkbook workbook, BookLabelModel bookLabelModel) {
    Map<String, CellStyle> styles = createStyles(workbook);
    String title = bookLabelModel.getTitle().replace("/", "-");
    bookLabelModel.setTitle(title);//from www  .j  a  va 2 s.  com
    //        Sheet sheet = workbook.createSheet(bookLabelModel.getBookNumber() + " " + bookLabelModel.getTitle());
    Sheet sheet = workbook.createSheet();

    for (int i = 0; i < 20; i++) {
        sheet.createRow(i);
    }

    generateHeaders(sheet, styles);
    generateAddress(sheet, styles, bookLabelModel);
    generateClient(sheet, styles, bookLabelModel);
    generateTransportation(sheet, styles, bookLabelModel);
    generateTitle(sheet, styles, bookLabelModel);
    generateCountPerBox(sheet, styles, bookLabelModel);
    generateCountPerAddress(sheet, styles, bookLabelModel);

    sheet.setColumnWidth(1, 20000);
    sheet.setColumnWidth(4, 20000);
    sheet.autoSizeColumn(0, false);
    sheet.autoSizeColumn(3, false);
    sheet.getPrintSetup().setLandscape(true);
    sheet.getPrintSetup().setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
    sheet.setFitToPage(true);
}

From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java

License:MIT License

/**
 * Internal implementation to add a sunburst chart (actually a doughnut chart) to a slide, based on a template.
 * @param template the parsed template information.
 * @param slide the slide to add to.// w w w .  j  a v a  2 s  .c o  m
 * @param anchor optional bounding rectangle to draw onto, in PowerPoint coordinates.
 *               If null, we'll use the bounds from the original template chart.
 * @param data the sunburst data.
 * @param shapeId the slide shape ID, should be unique within the slide.
 * @param relId the relation ID to the chart data.
 * @throws TemplateLoadException if we can't create the sunburst; most likely due to an invalid template.
 */
private static void addSunburst(final SlideShowTemplate template, final XSLFSlide slide,
        final Rectangle2D.Double anchor, final SunburstData data, final int shapeId, final String relId)
        throws TemplateLoadException {
    final String[] categories = data.getCategories();
    final double[] values = data.getValues();
    final String title = data.getTitle();

    slide.getXmlObject().getCSld().getSpTree().addNewGraphicFrame()
            .set(template.getDoughnutChartShapeXML(relId, shapeId, "chart" + shapeId, anchor));

    final XSSFWorkbook workbook = new XSSFWorkbook();
    final XSSFSheet sheet = workbook.createSheet();

    final XSLFChart baseChart = template.getDoughnutChart();

    final CTChartSpace chartSpace = (CTChartSpace) baseChart.getCTChartSpace().copy();
    final CTChart ctChart = chartSpace.getChart();
    final CTPlotArea plotArea = ctChart.getPlotArea();

    if (StringUtils.isEmpty(title)) {
        if (ctChart.getAutoTitleDeleted() != null) {
            ctChart.getAutoTitleDeleted().setVal(true);
        }

        ctChart.unsetTitle();
    }

    final CTDoughnutChart donutChart = plotArea.getDoughnutChartArray(0);

    final CTPieSer series = donutChart.getSerArray(0);

    final CTStrRef strRef = series.getTx().getStrRef();
    strRef.getStrCache().getPtArray(0).setV(title);
    sheet.createRow(0).createCell(1).setCellValue(title);
    strRef.setF(new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString());

    final CTStrRef categoryRef = series.getCat().getStrRef();
    final CTStrData categoryData = categoryRef.getStrCache();
    final CTNumRef numRef = series.getVal().getNumRef();
    final CTNumData numericData = numRef.getNumCache();

    final String[] fillColors = data.getColors();
    final String[] strokeColors = data.getStrokeColors();
    final boolean overrideFill = ArrayUtils.isNotEmpty(fillColors);
    final boolean overrideStroke = ArrayUtils.isNotEmpty(strokeColors);
    final boolean overrideColors = overrideFill || overrideStroke;
    final List<CTDPt> dPtList = series.getDPtList();
    final CTDPt templatePt = (CTDPt) dPtList.get(0).copy();
    if (overrideColors) {
        dPtList.clear();

        final CTShapeProperties spPr = templatePt.getSpPr();
        final CTLineProperties ln = spPr.getLn();

        // We need to unset any styles on the existing template
        if (overrideFill) {
            unsetSpPrFills(spPr);
        }

        if (overrideStroke) {
            unsetLineFills(ln);
        }
    }

    categoryData.setPtArray(null);
    numericData.setPtArray(null);

    CTLegend legend = null;
    final int[] showInLegend = data.getShowInLegend();
    int nextLegendToShow = 0, nextLegendToShowIdx = -1;
    if (showInLegend != null) {
        // We need to write legendEntry elements to hide the legend for chart series we don't want.
        // Note this only works in PowerPoint, and not OpenOffice.
        legend = ctChart.isSetLegend() ? ctChart.getLegend() : ctChart.addNewLegend();
        Arrays.sort(showInLegend);
        nextLegendToShow = showInLegend[++nextLegendToShowIdx];
    }

    for (int idx = 0; idx < values.length; ++idx) {
        final CTStrVal categoryPoint = categoryData.addNewPt();
        categoryPoint.setIdx(idx);
        categoryPoint.setV(categories[idx]);

        final CTNumVal numericPoint = numericData.addNewPt();
        numericPoint.setIdx(idx);
        numericPoint.setV(Double.toString(values[idx]));

        if (overrideColors) {
            final CTDPt copiedPt = (CTDPt) templatePt.copy();
            copiedPt.getIdx().setVal(idx);

            if (overrideFill) {
                final Color color = Color.decode(fillColors[idx % fillColors.length]);
                final CTSolidColorFillProperties fillClr = copiedPt.getSpPr().addNewSolidFill();
                fillClr.addNewSrgbClr().setVal(
                        new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() });
            }

            if (overrideStroke) {
                final Color strokeColor = Color.decode(strokeColors[idx % strokeColors.length]);
                final CTSolidColorFillProperties strokeClr = copiedPt.getSpPr().getLn().addNewSolidFill();
                strokeClr.addNewSrgbClr().setVal(new byte[] { (byte) strokeColor.getRed(),
                        (byte) strokeColor.getGreen(), (byte) strokeColor.getBlue() });
            }

            dPtList.add(copiedPt);
        }

        if (legend != null) {
            // We're hiding some legend elements. Should we show this index?
            if (nextLegendToShow == idx) {
                // We show this index, find the next one to show.
                ++nextLegendToShowIdx;
                if (nextLegendToShowIdx < showInLegend.length) {
                    nextLegendToShow = showInLegend[nextLegendToShowIdx];
                }
            } else {
                // We hide this index. If there's already a matching legend entry in the XML, update it,
                //   otherwise we create a new legend entry.
                boolean found = false;
                for (int ii = 0, max = legend.sizeOfLegendEntryArray(); ii < max; ++ii) {
                    final CTLegendEntry legendEntry = legend.getLegendEntryArray(ii);
                    final CTUnsignedInt idxLegend = legendEntry.getIdx();
                    if (idxLegend != null && idxLegend.getVal() == idx) {
                        found = true;
                        if (legendEntry.isSetDelete()) {
                            legendEntry.getDelete().setVal(true);
                        } else {
                            legendEntry.addNewDelete().setVal(true);
                        }
                    }
                }

                if (!found) {
                    final CTLegendEntry idxLegend = legend.addNewLegendEntry();
                    idxLegend.addNewIdx().setVal(idx);
                    idxLegend.addNewDelete().setVal(true);
                }
            }
        }

        XSSFRow row = sheet.createRow(idx + 1);
        row.createCell(0).setCellValue(categories[idx]);
        row.createCell(1).setCellValue(values[idx]);
    }
    categoryData.getPtCount().setVal(categories.length);
    numericData.getPtCount().setVal(values.length);

    categoryRef.setF(new CellRangeAddress(1, values.length, 0, 0).formatAsString(sheet.getSheetName(), true));
    numRef.setF(new CellRangeAddress(1, values.length, 1, 1).formatAsString(sheet.getSheetName(), true));

    try {
        writeChart(template.getSlideShow(), slide, baseChart, chartSpace, workbook, relId);
    } catch (IOException | InvalidFormatException e) {
        throw new TemplateLoadException("Error writing chart in loaded template", e);
    }
}

From source file:com.mycompany.owl.fxml.FXMLFilterController.java

@FXML
public void saveFileTransformed() throws FileNotFoundException, IOException {
    FileInputStream fileInputStream = new FileInputStream(file);
    XSSFWorkbook workbookToModify = new XSSFWorkbook(fileInputStream);
    XSSFSheet sheet = workbookToModify.getSheetAt(0);
    XSSFRow row;//  w  w  w .ja v  a2s.  co  m
    String atcMask = getATCMask();
    ArrayList<String> firstRowCells = new ArrayList<>();
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            System.out.print(row.getCell(j).getRawValue() + "\t");
            if (i == 0)
                firstRowCells.add(row.getCell(j).getRawValue());
        }
        System.out.println("");
    }

    XSSFWorkbook transformedWB = new XSSFWorkbook();
    transformedWB.createSheet();
    XSSFSheet transformedS = transformedWB.getSheetAt(0);
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        transformedS.createRow(i);
    }
    /*
    elkezdnk vgigmenni az alap sheeten
    ha megvan az index, ahol van match, akkor createrow(0) s bele a tbbit 0. helyre
    */
    ArrayList<Integer> matchingIndexes = matchingIndexes(firstRowCells);
    for (int i = 0; i <= sheet.getLastRowNum(); i++) {
        row = sheet.getRow(i);
        int sum = 0;
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            if (i != 0 && j != 0) {
                for (int index : matchingIndexes) {
                    if (index == j) {
                        sum += Integer.valueOf(row.getCell(j).getRawValue());
                    }
                }
            }
        }
        System.out.println(sum);
        if (i > 0) {
            row = sheet.getRow(i);
            row.createCell(row.getLastCellNum()).setCellValue(sum);
        }
    }
    for (int index : matchingIndexes) {
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            row.getCell(index).setCellValue(3.14159);
        }
        /*for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
        if(i == index){
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                row = sheet.getRow(j);
                transformedS.getRow(j).createCell(transformedColumnCount).setCellValue(
                        row.getCell(i).getRawValue()
                );
            }
            transformedColumnCount++;
        }
        }*/
    }
    int columnsInTransformed = 0;
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        row = sheet.getRow(i);
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            String cellValue = row.getCell(j).getRawValue();
            if (!cellValue.equals("3.14159")) {
                transformedS.getRow(i).createCell(columnsInTransformed);
                transformedS.getRow(i).getCell(columnsInTransformed).setCellValue(cellValue);
                columnsInTransformed++;
            }
        }
        columnsInTransformed = 0;
    }

    row = transformedS.getRow(0);
    row.createCell(row.getLastCellNum()).setCellValue(atcMask);

    File file = fileChooser.showSaveDialog(new Stage());
    if (file != null) {
        try {
            FileOutputStream fop = new FileOutputStream(file);
            transformedWB.write(fop);
            fop.close();
        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
    }

}

From source file:com.netsteadfast.greenstep.bsc.command.KpiPeriodTrendsExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();

    this.putTables(wb, sh, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);//from ww w.  j  a  v  a2s .com
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "kpis-period-trends.xlsx");
    file = null;
    return oid;
}

From source file:com.netsteadfast.greenstep.bsc.command.KpiReportExcelCommand.java

License:Apache License

private String createExcel(Context context) throws Exception {
    String visionOid = (String) context.get("visionOid");
    VisionVO vision = null;//from  w  ww . j  ava2  s  . co m
    BscStructTreeObj treeObj = (BscStructTreeObj) this.getResult(context);
    for (VisionVO visionObj : treeObj.getVisions()) {
        if (visionObj.getOid().equals(visionOid)) {
            vision = visionObj;
        }
    }
    BscReportPropertyUtils.loadData();
    BscReportSupportUtils.loadExpression(); // 2015-04-18 add
    String fileName = SimpleUtils.getUUIDStr() + ".xlsx";
    String fileFullPath = Constants.getWorkTmpDir() + "/" + fileName;
    int row = 24;
    if (context.get("pieCanvasToData") == null || context.get("barCanvasToData") == null) {
        row = 0;
    }
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sh = wb.createSheet();

    row += this.createHead(wb, sh, row, vision);
    row = this.createMainBody(wb, sh, row, vision);

    row = row + 1; // 
    row = this.createDateRange(wb, sh, row, vision, context);
    if (context.get("pieCanvasToData") != null && context.get("barCanvasToData") != null) {
        this.putCharts(wb, sh, context);
    }
    this.putSignature(wb, sh, row + 1, context);

    FileOutputStream out = new FileOutputStream(fileFullPath);
    wb.write(out);
    out.close();
    wb = null;

    File file = new File(fileFullPath);
    String oid = UploadSupportUtils.create(Constants.getSystem(), UploadTypes.IS_TEMP, false, file,
            "kpi-report.xlsx");
    file = null;
    return oid;
}