Search in sources :

Example 1 with CTTableStyleInfo

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo in project poi by apache.

the class CreateTable method main.

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet) wb.createSheet();
    //Create 
    XSSFTable table = sheet.createTable();
    table.setDisplayName("Test");
    CTTable cttable = table.getCTTable();
    //Style configurations
    CTTableStyleInfo style = cttable.addNewTableStyleInfo();
    style.setName("TableStyleMedium2");
    style.setShowColumnStripes(false);
    style.setShowRowStripes(true);
    //Set which area the table should be placed in
    AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(2, 2));
    cttable.setRef(reference.formatAsString());
    cttable.setId(1);
    cttable.setName("Test");
    cttable.setTotalsRowCount(1);
    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);
    CTTableColumn column;
    XSSFRow row;
    XSSFCell cell;
    for (int i = 0; i < 3; i++) {
        //Create column
        column = columns.addNewTableColumn();
        column.setName("Column");
        column.setId(i + 1);
        //Create row
        row = sheet.createRow(i);
        for (int j = 0; j < 3; j++) {
            //Create cell
            cell = row.createCell(j);
            if (i == 0) {
                cell.setCellValue("Column" + j);
            } else {
                cell.setCellValue("0");
            }
        }
    }
    FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}
Also used : AreaReference(org.apache.poi.ss.util.AreaReference) CTTableColumns(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumns) CellReference(org.apache.poi.ss.util.CellReference) XSSFTable(org.apache.poi.xssf.usermodel.XSSFTable) CTTableColumn(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) Workbook(org.apache.poi.ss.usermodel.Workbook) XSSFSheet(org.apache.poi.xssf.usermodel.XSSFSheet) XSSFRow(org.apache.poi.xssf.usermodel.XSSFRow) CTTableStyleInfo(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo) FileOutputStream(java.io.FileOutputStream) CTTable(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable) XSSFWorkbook(org.apache.poi.xssf.usermodel.XSSFWorkbook) XSSFCell(org.apache.poi.xssf.usermodel.XSSFCell)

Example 2 with CTTableStyleInfo

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo 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");
}
Also used : org.apache.poi.ss.usermodel(org.apache.poi.ss.usermodel) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress) org.apache.poi.xssf.usermodel(org.apache.poi.xssf.usermodel) AreaReference(org.apache.poi.ss.util.AreaReference) java.io(java.io) org.openxmlformats.schemas.spreadsheetml.x2006.main(org.openxmlformats.schemas.spreadsheetml.x2006.main) CellReference(org.apache.poi.ss.util.CellReference) ArrayList(java.util.ArrayList) ArrayList(java.util.ArrayList) CellReference(org.apache.poi.ss.util.CellReference) AreaReference(org.apache.poi.ss.util.AreaReference) CellRangeAddress(org.apache.poi.ss.util.CellRangeAddress)

Example 3 with CTTableStyleInfo

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo in project poi by apache.

the class TestXSSFTable method testCTTableStyleInfo.

@Test
public void testCTTableStyleInfo() throws IOException {
    XSSFWorkbook outputWorkbook = new XSSFWorkbook();
    XSSFSheet sheet = outputWorkbook.createSheet();
    //Create
    XSSFTable outputTable = sheet.createTable();
    outputTable.setDisplayName("Test");
    CTTable outputCTTable = outputTable.getCTTable();
    //Style configurations
    CTTableStyleInfo outputStyleInfo = outputCTTable.addNewTableStyleInfo();
    outputStyleInfo.setName("TableStyleLight1");
    outputStyleInfo.setShowColumnStripes(false);
    outputStyleInfo.setShowRowStripes(true);
    XSSFWorkbook inputWorkbook = XSSFTestDataSamples.writeOutAndReadBack(outputWorkbook);
    List<XSSFTable> tables = inputWorkbook.getSheetAt(0).getTables();
    assertEquals("Tables number", 1, tables.size());
    XSSFTable inputTable = tables.get(0);
    assertEquals("Table display name", outputTable.getDisplayName(), inputTable.getDisplayName());
    CTTableStyleInfo inputStyleInfo = inputTable.getCTTable().getTableStyleInfo();
    assertEquals("Style name", outputStyleInfo.getName(), inputStyleInfo.getName());
    assertEquals("Show column stripes", outputStyleInfo.getShowColumnStripes(), inputStyleInfo.getShowColumnStripes());
    assertEquals("Show row stripes", outputStyleInfo.getShowRowStripes(), inputStyleInfo.getShowRowStripes());
    inputWorkbook.close();
    outputWorkbook.close();
}
Also used : CTTableStyleInfo(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo) CTTable(org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable) SXSSFWorkbook(org.apache.poi.xssf.streaming.SXSSFWorkbook) Test(org.junit.Test)

Aggregations

AreaReference (org.apache.poi.ss.util.AreaReference)2 CellReference (org.apache.poi.ss.util.CellReference)2 CTTable (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTable)2 CTTableStyleInfo (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableStyleInfo)2 java.io (java.io)1 FileOutputStream (java.io.FileOutputStream)1 ArrayList (java.util.ArrayList)1 org.apache.poi.ss.usermodel (org.apache.poi.ss.usermodel)1 Workbook (org.apache.poi.ss.usermodel.Workbook)1 CellRangeAddress (org.apache.poi.ss.util.CellRangeAddress)1 SXSSFWorkbook (org.apache.poi.xssf.streaming.SXSSFWorkbook)1 org.apache.poi.xssf.usermodel (org.apache.poi.xssf.usermodel)1 XSSFCell (org.apache.poi.xssf.usermodel.XSSFCell)1 XSSFRow (org.apache.poi.xssf.usermodel.XSSFRow)1 XSSFSheet (org.apache.poi.xssf.usermodel.XSSFSheet)1 XSSFTable (org.apache.poi.xssf.usermodel.XSSFTable)1 XSSFWorkbook (org.apache.poi.xssf.usermodel.XSSFWorkbook)1 Test (org.junit.Test)1 org.openxmlformats.schemas.spreadsheetml.x2006.main (org.openxmlformats.schemas.spreadsheetml.x2006.main)1 CTTableColumn (org.openxmlformats.schemas.spreadsheetml.x2006.main.CTTableColumn)1