Aspose Java - Support multiple record set / charts side by side

Hello,

Can we have multiple results sets configured side by side.
Say I have R1 and R2 result sets from which R1 has 3 columns (columns are fixed and rows are dynamic from result set) displayed in Col A,B & C having 25 rows. I also have R2 has two more columns which needs to be displayed in Col E & F with 5rows.

I also want to plot a chart from R2 result set data in the same sheet. Is it feasible doing with Aspose Cells all side by side in Horizantal way like the enclosed screenshot.

I could able to configure them one below the other but not side by side as I shown.

Hi Victor,


Thank you for considering Aspose products, and welcome to Aspose.Cells support forum.

You may add multiple data series for one chart with Aspose.Cells for Java API. Please check the below provided code snippet for your kind reference.

Java

//Load the spreadsheet containing the data
Workbook workbook = new Workbook(myDir + “book1.xls”);
//Get the instance of first worksheet having data
Worksheet sheet = workbook.getWorksheets().get(0);
//Store worksheet name for future reference
String sheetName = sheet.getName();
//Add another worksheet to hold the chart
int index = workbook.getWorksheets().add();
//Get the reference of newly added worksheet
Worksheet chartSheet = workbook.getWorksheets().get(index);
//Add a line chart at specified location of newly added worksheet
index = chartSheet.getCharts().add(ChartType.LINE, 5, 0, 15, 5);
//Get the reference of newly added chart
Chart chart = chartSheet.getCharts().get(index);
//Get the SeriesCollection of chart
SeriesCollection serieses = chart.getNSeries();
//Add series to the collection referencing the data source
serieses.add("=" + sheetName + “!$B$2:$F$14”, true);
//Add another series to the collection referencing different data source
serieses.add("=" + sheetName + “!$H$2:$L$14”, true);
//Save workbook
workbook.save(myDir + “output.xlsx”);

Please note, while using the above approach you can set a discontinues data source/range to the chart therefore its is advised that both data sources should be similar otherwise chart may not render according to your expectation.

If we have misunderstood your requirements then we will request you to provide us your desired results which you may create manually with MS Excel application. After reviewing your provided spreadsheet we will try to mimic the functionality with Aspose.Cells for Java API.

No that wasn’t what I am looking for. The issue got miss interpreted.


The problem is we couldn’t display multiple result sets both (R1 & R2) side by side. When I configure both R1 & R2 horizontally, R1 data is displaying in the next row where R2 data is completed writing . It isn’t starting from the beginning. The below screenshot may help you understand what issues we are having in displaying result sets. I have highlighted the rows in yellow where it is pushing down untill R2 data is done. It is blocking the row for 1 result set. The second resultset starts only in the new row even though it is configured side by side. Looking Forward for a solution if Aspose can supports this.

We can leave charts for now and look after when the result sets problem is solved.

Your help is much appreciated.

Hi again,


Sorry for the misunderstanding.

It seems that the problem could be in the logic how you are inserting the data in the spreadsheet. Please share your current code snippet and your desired results here for reference.
There will be multiple sheets in the workbook with multiple result sets in each sheet.

for (int sheetIndex = 0; sheetIndex < collection.getCount(); sheetIndex++) {
TreeMap currentSheetRowNumResultSetIndex = new TreeMap();
Worksheet sheet = collection.get(sheetIndex);
Cells cells = sheet.getCells();
FindOptions options = new FindOptions();
options.setLookInType(LookInType.VALUES_EXCLUDE_FORMULA_CELL);
options.setLookAtType(LookAtType.START_WITH);
Cell cell = null;
while ((cell = cells.find("&=", cell, options)) != null) {
String value = cell.getStringValue();
Matcher matcher = resultSetAndFieldPattern.matcher(value);
if (matcher.matches()) {
String resultSetName = matcher.group(1) != null ? matcher
.group(1) : matcher.group(2);
String resultField = matcher.group(3) != null ? matcher
.group(3) : matcher.group(4);
int useColumn = cell.getColumn() + 1;
int useRow = cell.getRow() + 1;
int resultSetIndex = bidirResultSetNameResultSetIndex
.intValue(resultSetName);
if (resultSetIndex >= 0) {
if (resultField.equals("*")) {
expandWildcardFields(resultSetName, cell, cells);
return false;
} else {
currentSheetRowNumResultSetIndex.put(useRow,
resultSetIndex);
BiIntMonoStr sheetIndexResultSetIndexField = new BiIntMonoStr(
sheetIndex, resultSetIndex, resultField);
BiInt columnRow = new BiInt(useColumn, useRow);
sheetIndexResultSetIndexFieldColumnRow.put(
sheetIndexResultSetIndexField, columnRow);
}
} else {
if (value.equals("&=
  • .
  • ")) {
  • resultSetName = dataSetNameResultSet.keySet().toArray(new String[0])[0];
    expandWildcardFields(resultSetName, cell, cells);
    return false;
    } else {
    System.out
    .println("WARNING: invalid recordset used in template: "
    + value);
    }
    }
    }
    }
    sheetIndexRowNumResultSetIndex
    .add(currentSheetRowNumResultSetIndex);
    }

    private void expandWildcardFields(String dataSetName, Cell cell, Cells cells) {
    try {
    ResultSet resultSet = dataSetNameResultSet.get(dataSetName);
    ResultSetMetaData metaData = resultSet.getMetaData();
    int wildRow = cell.getRow();
    int wildColumn = cell.getColumn();
    for (int fieldIndex = metaData.getColumnCount(); fieldIndex > 0; fieldIndex--) {
    String fieldName = metaData.getColumnLabel(fieldIndex);
    Cell header = cells.get(wildRow, wildColumn - 1 + fieldIndex);
    header.setValue(fieldName);
    Cell dataRef = cells.get(wildRow + 1, wildColumn - 1
    + fieldIndex);
    dataRef.setValue("&=[" + dataSetName + "].[" + fieldName + "]");
    }
    return;
    } catch (SQLException e) {
    e.printStackTrace();
    try {
    connection.rollback();
    } catch (SQLException e1) {
    ;
    }
    updateQueue("Failure");
    System.exit(1);
    }
    }

    This is the expected output. Having Result sets side by side with out any empty rows. R1 is starting only after R2 rows are written as highlighted in yellow in earlier pic.

    Hi,


    Thank you for the code.

    By looking at your provided snippet I have got the idea your are creating a Designer spreadsheet containing Smart Markers. You must be further processing the designer to get the final result as shown in Aspose1.png. Please save the designer on disk and provide here along with code snippets used to fill the data.

    Please do give a try to the latest version of Aspose.Cells for Java 8.0.0.1 with your actual application to see how it produces the results.