Issues related with the output generated the excel template

Aspose.Cells Product Family
1

Hi all,

We are using Aspose to merge data with the pre-defined excel template. We found the following issues in the output:

1. There was pre-defined colors on the worksheet tabs, but the tab color was missing in the generated output.

2. The digital signature was lost in the output and hence the macros did not run automatically with the default macro security setting.

3. If the data source is empty, the output file still shows the smart markers instead of the empty worksheet.

Attached please find one of the template files we are using as an example.

Looking forward to your answer.

Thanks,
Michelle

2

Hi Michelle,

Thank you for considering Aspose.

MichelleJi:

  1. There was pre-defined colors on the worksheet tabs, but the tab color was missing in the generated output.

  2. The digital signature was lost in the output and hence the macros did not run automatically with the default macro security setting.

I am afraid; currently, Aspose.Cells for Java does not support these features for 2007 file format. We will look into these features and check whether we can support them soon.

MichelleJi:

  1. If the data source is empty, the output file still shows the smart markers instead of the empty worksheet.

You can invoke WorkbookDesigner.process(false) to remove Smart Markers when the Dataset is empty.

Thank You & Best Regards,

3

Hi Michelle,

Thank you for considering Aspose.

Please try the attached latest fix of Aspose.Cells. We have fixed your mentioned issues (Issue: 1 & 2) .

Thank You & Best Regards,

4

Hi Nausherwan,

Thanks for the quick turnaround. I tried the attached new library. The 1st issue (tab color) was fixed. However, the pre-defined digital signature was not carried over in the output. Can you confirm if you missed anything in the code?

Thanks,
Michelle

5

Hi Michelle,

Thank you for considering Aspose.

I tried to generate a file using your template file to check your digital signature issue with the latest version of Aspose.Cells and it works fine. Please see the attached output file. Can you please provide us more details like what kind of processing are you doing on your template file when you get this issue. May be you can share your code and generated file which will help us figure out the issue soon.

Thank You & Best Regards,

6

Hi Nausherwan,

Below is the code to merge the data with template.

public static byte[] createRDFWorkbookByAspose(ParamBean paramBean, RDFBean rdfBean,
List queryResults)
throws ExcelTooManyRowsException {

ByteArrayOutputStream out = new ByteArrayOutputStream();

WorkbookDesigner designer = new WorkbookDesigner();
Workbook wb = designer.getWorkbook();

try {
wb.open(new ByteArrayInputStream(rdfBean.rdfTemplateBean.content),
rdfBean.isReportInExcel2007() ?
FileFormatType.EXCEL2007XLTM :
FileFormatType.EXCEL2003);
} catch (IOException ioe) {
log.error(“Error caught when opening RDF template.”, ioe);
}

designer.setDataSource(“COMPANY_NAME”, paramBean.getCompanyName());

String timeCreated = "Report Generated: " + ClientDateFormat.formatLong(new Date(),
paramBean.timeZone, paramBean.locale);
designer.setDataSource(“TIME_CREATED”, timeCreated);

List queries = rdfBean.queries;

if (queries != null) {
for (int i = 0; i < queries.size(); i++) {
QueryDefBean queryDef = (QueryDefBean) queries.get(i);
QueryResultBean queryResult = (QueryResultBean) queryResults.get(i);

log.info("queryResult size is " + queryResult.dataRows.size());
ensureQueryExcelCapable(queryDef, queryResult, rdfBean.isReportInExcel2007());

AsposeDataTable dataTable = new QueryResultDataTable(queryResult);
String smartMarkerName = StringUtils.replace(queryDef.name, " ", “”);
designer.setDataSource(smartMarkerName, dataTable);
}
}

//— Process the template to populate it with the Data Source.
designer.process(false);

//— Stream the processed document to the client.
try {
designer.save(out, rdfBean.isReportInExcel2007() ?
FileFormatType.EXCEL2007XLSM : FileFormatType.EXCEL2003);
} catch (IOException ioe) {
log.error(“Error caught when saving excel output.”, ioe);
}

return out.toByteArray();
}

The generated output file is attached also.

Thanks for the help.

-Michelle

7

Hi Michelle,

Well, we have created a test case to simulate your code (please find attached the zip file containing the test code and the generated file). Actually our latest fix (attached) works fine, please try it now.

Kindly let us know if it works fine. If you still have such problem, please give us a simple sample project to reproduce your issue, we will check it soon.

Thank you.