0

I'm creating an excel file with many tabs. Each tab has a table and, manually, is very easy to create a graph from it. The tables contain the cell range and headings needed for the graphs, which varies from one to another. I've been searching to do this programmatically and found nothing. Do I have to create it from scratch or is there a simpler way of creating a graph form a table?

Thanks for your help

Additional info:

I'm not saying that the graphs cannot be create using the tables but I was looking for a way where the information from the table could be used for the graph. Anyway, I've realised that the tables are the same throughout the sheets.

Code from the method that generates the tabs and adds the tables

       ...
            XSSFSheet sheet = workbook.createSheet(kind);
            addTable(sheet, years, count++);

            XSSFRow row;
            XSSFCell cell;
            for (String var : yearMapSorted.keySet()) {

                List<Double> weights = yearMap.get(var);

                row = sheet.createRow(rowNum++);
                cell = row.createCell(0);
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(weights.get(FIRST_YEAR));
                row.createCell(1).setCellValue(weights.get(SECOND_YEAR));
                row.createCell(2).setCellValue(var);
            }

            // add formula to calculate the sum of the columns
            addTotalColumns(sheet);
            addCalculatedColumns(sheet);
        ...

Method that adds the table 

private void addTable(XSSFSheet sheet, List<Integer> years, Long count) {

    String tableName = "Table_" + count;

    XSSFRow row = sheet.createRow(0);
    row.createCell(2).setCellValue("Variety");
    row.createCell(3).setCellValue(years.get(FIRST_YEAR).toString());
    row.createCell(4).setCellValue(years.get(SECOND_YEAR).toString());

    XSSFTable table = sheet.createTable();
    CTTable cttable = table.getCTTable();
    cttable.setDisplayName(tableName);
    cttable.setId(count);
    cttable.setName(tableName);
    cttable.setRef("C1:E11");
    cttable.setTotalsRowShown(false);

    CTTableStyleInfo styleInfo = cttable.addNewTableStyleInfo();
    styleInfo.setName("TableStyleMedium2");
    styleInfo.setShowColumnStripes(false);
    styleInfo.setShowRowStripes(true);

    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);
    for (int i = 1; i <= 3; i++) {
        CTTableColumn column = columns.addNewTableColumn();
        column.setId(i);
        column.setName("Column"+i);
    }
}
Nils
  • 117
  • 12
  • Have any some code as your try? – I'm Limit Aug 29 '19 at 07:40
  • errr.... create graph in excel file? – I'm Limit Aug 29 '19 at 07:40
  • Possible duplicate of [Create Bar Chart in Excel with Apache POI](https://stackoverflow.com/questions/38913412/create-bar-chart-in-excel-with-apache-poi) – I'm Limit Aug 29 '19 at 07:43
  • 1
    For creating charts there is now a better `API`. See examples here: https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/ – Axel Richter Aug 29 '19 at 08:48
  • @I'mLimit I've check that before. I'm not sure if I'm not explaining myself. The table contains the information I need for the graph, cell range, heading... I can't add any code because I don't have any. I'm trying to see if there is a way of using the table. Thanks – Nils Aug 29 '19 at 09:05
  • @AxelRichter thanks for this, if there is no way to use a table and I decide to do it, then I use this. – Nils Aug 29 '19 at 09:09

0 Answers0