Examples with CTAutoFilter org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter used on opensource projects

Example 1 with CTAutoFilter

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter in project SoftUni by kostovhg.

the class p14_ExportToExcel method main.

public static void main(String[] args) throws IOException {
    ArrayList<Object[]> allLines = new ArrayList<>();
    allLines.add(new Object[] { "FN", "First name", "Last Name", "Email", "Age", "Group", "Grade1", "Grade2", "Grade3", "Grade4", "Phones" });
    new BufferedReader(new FileReader("StudentsData.txt")).lines().map(x -> x.split("\\t")).filter(x -> !x[0].equals("FN")).forEach(allLines::add);
    // Create workbook and worksheet object
    int rowStart = 2;
    int columnStart = 0;
    int totalRows = allLines.size();
    int totalCols = allLines.get(0).length;
    int rowEnd = totalRows + rowStart - 1;
    int colEnd = totalCols + columnStart - 1;
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("SoftUniOOPCourseResults");
    CellStyle style1 = workbook.createCellStyle();
    style1.setAlignment(HorizontalAlignment.RIGHT);
    XSSFDataFormat intFormat = workbook.createDataFormat();
    style1.setDataFormat(intFormat.getFormat("0"));
    CellStyle style2 = workbook.createCellStyle();
    XSSFDataFormat strFormat = workbook.createDataFormat();
    style2.setDataFormat(strFormat.getFormat("General"));
    style2.setAlignment(HorizontalAlignment.LEFT);
    XSSFRow row;
    XSSFCell cell;
    // Create an object of type XSSFTable
    XSSFTable myTable = sheet.createTable();
    // Get CTTable object
    CTTable cttable = myTable.getCTTable();
    cttable.setTotalsRowShown(false);
    // Define the required style1 for the table
    CTTableStyleInfo table_style = cttable.addNewTableStyleInfo();
    table_style.setName("TableStyleLight14");
    // Set table style option
    table_style.setShowColumnStripes(false);
    table_style.setShowRowStripes(true);
    // define the data range including headers
    AreaReference my_data_range = new AreaReference(new CellReference(rowStart, columnStart), new CellReference(rowEnd, colEnd));
    // Set range to the table
    cttable.setRef(my_data_range.formatAsString());
    cttable.setDisplayName("Students");
    cttable.setName("Students");
    cttable.setId(1L);
    CTTableColumns columns = cttable.addNewTableColumns();
    CTAutoFilter autoFilter = cttable.addNewAutoFilter();
    columns.setCount(totalCols);
    // Define Header Information for the table
    for (int i = columnStart; i <= colEnd; i++) {
        CTTableColumn column = columns.addNewTableColumn();
        column.setName(allLines.get(0)[i].toString());
        column.setId(i + 1);
    }
    sheet.setAutoFilter(new CellRangeAddress(rowStart, rowStart, columnStart, colEnd));
    // Add remaining Table data
    row = sheet.createRow((short) 0);
    cell = row.createCell((short) 0);
    sheet.addMergedRegion(new CellRangeAddress(0, rowStart - 1, columnStart, colEnd));
    cell.setCellValue("SoftUni OOP Course Results");
    CellStyle bolded = workbook.createCellStyle();
    bolded.setAlignment(HorizontalAlignment.CENTER);
    XSSFFont myFont = workbook.createFont();
    myFont.setBold(true);
    myFont.setFontHeightInPoints((short) 30);
    bolded.setFont(myFont);
    cell.setCellStyle(bolded);
    int rowNum = rowStart;
    for (Object[] datatype : allLines) {
        XSSFRow inRow = sheet.createRow(rowNum++);
        int colNum = columnStart;
        for (Object field : datatype) {
            XSSFCell inCell = inRow.createCell(colNum++);
            if (isInt(field)) {
                inCell.setCellStyle(style1);
                inCell.setCellType(CellType.NUMERIC);
                inCell.setCellValue((Double) field);
            } else {
                inCell.setCellStyle(style2);
                inCell.setCellType(CellType.STRING);
                inCell.setCellValue((String) field);
            }
        }
    }
    for (int i = 1; i <= 11; i++) {
        sheet.autoSizeColumn(i);
    }
    try {
        FileOutputStream outputStream = new FileOutputStream(FILE_NAME);
        workbook.write(outputStream);
        workbook.close();
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    System.out.println("Done");
}

Example 2 with CTAutoFilter

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter in project org.hl7.fhir.core by hapifhir.

the class StructureDefinitionSpreadsheetGenerator method configureSheet.

public void configureSheet(Sheet sheet, StructureDefinition sd) throws IOException {
    for (int i = 0; i < 34; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.setColumnHidden(2, true);
    sheet.setColumnHidden(3, true);
    sheet.setColumnHidden(30, true);
    sheet.setColumnHidden(31, true);
    sheet.setColumnHidden(32, true);
    sheet.setColumnWidth(9, columnPixels(20));
    sheet.setColumnWidth(11, columnPixels(100));
    sheet.setColumnWidth(12, columnPixels(100));
    sheet.setColumnWidth(13, columnPixels(100));
    sheet.setColumnWidth(15, columnPixels(20));
    sheet.setColumnWidth(16, columnPixels(20));
    sheet.setColumnWidth(17, columnPixels(20));
    sheet.setColumnWidth(18, columnPixels(20));
    sheet.setColumnWidth(34, columnPixels(100));
    int i = titles.length - 1;
    for (StructureDefinitionMappingComponent map : sd.getMapping()) {
        i++;
        sheet.setColumnWidth(i, columnPixels(50));
        sheet.autoSizeColumn(i);
    // sheet.setColumnHidden(i,  true);
    }
    sheet.createFreezePane(2, 1);
    if (hideMustSupportFalse) {
        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
        String address = "A2:AI" + Math.max(Integer.valueOf(sheet.getLastRowNum()), 2);
        CellRangeAddress[] regions = { CellRangeAddress.valueOf(address) };
        ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$G2<>\"Y\"");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
        ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("$Q2<>\"\"");
        FontFormatting font = rule2.createFontFormatting();
        font.setFontColorIndex(IndexedColors.GREY_25_PERCENT.index);
        font.setFontStyle(true, false);
        sheetCF.addConditionalFormatting(regions, rule1, rule2);
        sheet.setAutoFilter(new CellRangeAddress(0, sheet.getLastRowNum(), 0, titles.length + sd.getMapping().size() - 1));
        XSSFSheet xSheet = (XSSFSheet) sheet;
        CTAutoFilter sheetFilter = xSheet.getCTWorksheet().getAutoFilter();
        CTFilterColumn filterColumn1 = sheetFilter.addNewFilterColumn();
        filterColumn1.setColId(6);
        CTCustomFilters filters = filterColumn1.addNewCustomFilters();
        CTCustomFilter filter1 = filters.addNewCustomFilter();
        filter1.setOperator(STFilterOperator.NOT_EQUAL);
        filter1.setVal(" ");
        CTFilterColumn filterColumn2 = sheetFilter.addNewFilterColumn();
        filterColumn2.setColId(26);
        CTFilters filters2 = filterColumn2.addNewFilters();
        filters2.setBlank(true);
        // We have to apply the filter ourselves by hiding the rows:
        for (Row row : sheet) {
            if (row.getRowNum() > 0 && (!row.getCell(6).getStringCellValue().equals("Y") || !row.getCell(26).getStringCellValue().isEmpty())) {
                ((XSSFRow) row).getCTRow().setHidden(true);
            }
        }
    }
    sheet.setActiveCell(new CellAddress(sheet.getRow(1).getCell(0)));
}

Example 3 with CTAutoFilter

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter in project org.hl7.fhir.core by hapifhir.

the class StructureDefinitionSpreadsheetGenerator method configureSheet.

public void configureSheet(Sheet sheet, StructureDefinition sd) throws IOException {
    for (int i = 0; i < 34; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.setColumnHidden(2, true);
    sheet.setColumnHidden(3, true);
    sheet.setColumnHidden(30, true);
    sheet.setColumnHidden(31, true);
    sheet.setColumnHidden(32, true);
    sheet.setColumnWidth(9, columnPixels(20));
    sheet.setColumnWidth(11, columnPixels(100));
    sheet.setColumnWidth(12, columnPixels(100));
    sheet.setColumnWidth(13, columnPixels(100));
    sheet.setColumnWidth(15, columnPixels(20));
    sheet.setColumnWidth(16, columnPixels(20));
    sheet.setColumnWidth(17, columnPixels(20));
    sheet.setColumnWidth(18, columnPixels(20));
    sheet.setColumnWidth(34, columnPixels(100));
    int i = titles.length - 1;
    for (StructureDefinitionMappingComponent map : sd.getMapping()) {
        i++;
        sheet.setColumnWidth(i, columnPixels(50));
        sheet.autoSizeColumn(i);
    // sheet.setColumnHidden(i,  true);
    }
    sheet.createFreezePane(2, 1);
    if (hideMustSupportFalse) {
        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
        String address = "A2:AI" + Math.max(Integer.valueOf(sheet.getLastRowNum()), 2);
        CellRangeAddress[] regions = { CellRangeAddress.valueOf(address) };
        ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$G2<>\"Y\"");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
        ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("$Q2<>\"\"");
        FontFormatting font = rule2.createFontFormatting();
        font.setFontColorIndex(IndexedColors.GREY_25_PERCENT.index);
        font.setFontStyle(true, false);
        sheetCF.addConditionalFormatting(regions, rule1, rule2);
        sheet.setAutoFilter(new CellRangeAddress(0, sheet.getLastRowNum(), 0, titles.length + sd.getMapping().size() - 1));
        XSSFSheet xSheet = (XSSFSheet) sheet;
        CTAutoFilter sheetFilter = xSheet.getCTWorksheet().getAutoFilter();
        CTFilterColumn filterColumn1 = sheetFilter.addNewFilterColumn();
        filterColumn1.setColId(6);
        CTCustomFilters filters = filterColumn1.addNewCustomFilters();
        CTCustomFilter filter1 = filters.addNewCustomFilter();
        filter1.setOperator(STFilterOperator.NOT_EQUAL);
        filter1.setVal(" ");
        CTFilterColumn filterColumn2 = sheetFilter.addNewFilterColumn();
        filterColumn2.setColId(26);
        CTFilters filters2 = filterColumn2.addNewFilters();
        filters2.setBlank(true);
        // We have to apply the filter ourselves by hiding the rows:
        for (Row row : sheet) {
            if (row.getRowNum() > 0 && (!row.getCell(6).getStringCellValue().equals("Y") || !row.getCell(26).getStringCellValue().isEmpty())) {
                ((XSSFRow) row).getCTRow().setHidden(true);
            }
        }
    }
    sheet.setActiveCell(new CellAddress(sheet.getRow(1).getCell(0)));
}

Example 4 with CTAutoFilter

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter in project org.hl7.fhir.core by hapifhir.

the class XLSXWriter method dump.

public void dump() throws IOException {
    for (int i = 0; i < 34; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.setColumnHidden(2, true);
    sheet.setColumnHidden(3, true);
    sheet.setColumnHidden(30, true);
    sheet.setColumnHidden(31, true);
    sheet.setColumnHidden(32, true);
    sheet.setColumnWidth(9, columnPixels(20));
    sheet.setColumnWidth(11, columnPixels(100));
    sheet.setColumnWidth(12, columnPixels(100));
    sheet.setColumnWidth(13, columnPixels(100));
    sheet.setColumnWidth(15, columnPixels(20));
    sheet.setColumnWidth(16, columnPixels(20));
    sheet.setColumnWidth(17, columnPixels(20));
    sheet.setColumnWidth(18, columnPixels(20));
    sheet.setColumnWidth(34, columnPixels(100));
    int i = titles.length - 1;
    for (StructureDefinitionMappingComponent map : def.getMapping()) {
        i++;
        sheet.setColumnWidth(i, columnPixels(50));
        sheet.autoSizeColumn(i);
    // sheet.setColumnHidden(i,  true);
    }
    sheet.createFreezePane(2, 1);
    if (hideMustSupportFalse) {
        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
        String address = "A2:AI" + Math.max(Integer.valueOf(sheet.getLastRowNum()), 2);
        CellRangeAddress[] regions = { CellRangeAddress.valueOf(address) };
        ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$G2<>\"Y\"");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
        ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("$Q2<>\"\"");
        FontFormatting font = rule2.createFontFormatting();
        font.setFontColorIndex(IndexedColors.GREY_25_PERCENT.index);
        font.setFontStyle(true, false);
        sheetCF.addConditionalFormatting(regions, rule1, rule2);
        sheet.setAutoFilter(new CellRangeAddress(0, sheet.getLastRowNum(), 0, titles.length + def.getMapping().size() - 1));
        XSSFSheet xSheet = (XSSFSheet) sheet;
        CTAutoFilter sheetFilter = xSheet.getCTWorksheet().getAutoFilter();
        CTFilterColumn filterColumn1 = sheetFilter.addNewFilterColumn();
        filterColumn1.setColId(6);
        CTCustomFilters filters = filterColumn1.addNewCustomFilters();
        CTCustomFilter filter1 = filters.addNewCustomFilter();
        filter1.setOperator(STFilterOperator.NOT_EQUAL);
        filter1.setVal(" ");
        CTFilterColumn filterColumn2 = sheetFilter.addNewFilterColumn();
        filterColumn2.setColId(26);
        CTFilters filters2 = filterColumn2.addNewFilters();
        filters2.setBlank(true);
        // We have to apply the filter ourselves by hiding the rows:
        for (Row row : sheet) {
            if (row.getRowNum() > 0 && (!row.getCell(6).getStringCellValue().equals("Y") || !row.getCell(26).getStringCellValue().isEmpty())) {
                ((XSSFRow) row).getCTRow().setHidden(true);
            }
        }
    }
    sheet.setActiveCell(new CellAddress(sheet.getRow(1).getCell(0)));
    wb.write(outStream);
    flush();
    close();
}

Example 5 with CTAutoFilter

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTAutoFilter in project org.hl7.fhir.core by hapifhir.

the class XLSXWriter method dump.

public void dump() throws IOException {
    for (int i = 0; i < 34; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.setColumnHidden(2, true);
    sheet.setColumnHidden(3, true);
    sheet.setColumnHidden(30, true);
    sheet.setColumnHidden(31, true);
    sheet.setColumnHidden(32, true);
    sheet.setColumnWidth(9, columnPixels(20));
    sheet.setColumnWidth(11, columnPixels(100));
    sheet.setColumnWidth(12, columnPixels(100));
    sheet.setColumnWidth(13, columnPixels(100));
    sheet.setColumnWidth(15, columnPixels(20));
    sheet.setColumnWidth(16, columnPixels(20));
    sheet.setColumnWidth(17, columnPixels(20));
    sheet.setColumnWidth(18, columnPixels(20));
    sheet.setColumnWidth(34, columnPixels(100));
    int i = titles.length - 1;
    for (StructureDefinitionMappingComponent map : def.getMapping()) {
        i++;
        sheet.setColumnWidth(i, columnPixels(50));
        sheet.autoSizeColumn(i);
    // sheet.setColumnHidden(i,  true);
    }
    sheet.createFreezePane(2, 1);
    if (hideMustSupportFalse) {
        SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
        String address = "A2:AI" + Math.max(Integer.valueOf(sheet.getLastRowNum()), 2);
        CellRangeAddress[] regions = { CellRangeAddress.valueOf(address) };
        ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$G2<>\"Y\"");
        PatternFormatting fill1 = rule1.createPatternFormatting();
        fill1.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
        fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
        ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("$Q2<>\"\"");
        FontFormatting font = rule2.createFontFormatting();
        font.setFontColorIndex(IndexedColors.GREY_25_PERCENT.index);
        font.setFontStyle(true, false);
        sheetCF.addConditionalFormatting(regions, rule1, rule2);
        sheet.setAutoFilter(new CellRangeAddress(0, sheet.getLastRowNum(), 0, titles.length + def.getMapping().size() - 1));
        XSSFSheet xSheet = (XSSFSheet) sheet;
        CTAutoFilter sheetFilter = xSheet.getCTWorksheet().getAutoFilter();
        CTFilterColumn filterColumn1 = sheetFilter.addNewFilterColumn();
        filterColumn1.setColId(6);
        CTCustomFilters filters = filterColumn1.addNewCustomFilters();
        CTCustomFilter filter1 = filters.addNewCustomFilter();
        filter1.setOperator(STFilterOperator.NOT_EQUAL);
        filter1.setVal(" ");
        CTFilterColumn filterColumn2 = sheetFilter.addNewFilterColumn();
        filterColumn2.setColId(26);
        CTFilters filters2 = filterColumn2.addNewFilters();
        filters2.setBlank(true);
        // We have to apply the filter ourselves by hiding the rows:
        for (Row row : sheet) {
            if (row.getRowNum() > 0 && (!row.getCell(6).getStringCellValue().equals("Y") || !row.getCell(26).getStringCellValue().isEmpty())) {
                ((XSSFRow) row).getCTRow().setHidden(true);
            }
        }
    }
    sheet.setActiveCell(new CellAddress(sheet.getRow(1).getCell(0)));
    wb.write(outStream);
    flush();
    close();
}