We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Problem with Cells in converting to PDF

Aspose.Cells Product Family
#1

I’d posted this in the PDF forum and they sent me to the Cells forum. Here’s a link to that thread:

https://forum.aspose.com/t/130649

Essentially the problem is that when using an Excel template (with Smart Markers) to perform a merge, and then converting to PDF, the bottom of the PDF does not contain all of the information that was in the Excel template. Adeel indicates in the thread above that the information is missing from the XML, and therefore something must be up with Cells. Would you kindly take a look at these? I’ve attached the template and the output.

Thanks!
Mike

Hi again, Adeel.

I’ve put together a more complete test and I’m able to reproduce the problem
every time. Would you mind taking a look at this?

protected void Button1_Click(object sender, EventArgs e)
{
    // Create a dataset
    DataSet ds = new DataSet();

    // Create a datatable
    DataTable dt = new DataTable("Master");
    dt.Columns.Add(new DataColumn("LEAD_FULLNAME", Type.GetType("System.String")));
    dt.Columns.Add(new DataColumn("ACCOUNT_NAME", Type.GetType("System.String")));
    dt.Columns.Add(new DataColumn("CONTACT_SUPPORT_END_DATE", Type.GetType("System.String")));
    DataRow dr = dt.NewRow();
    dr["LEAD_FULLNAME"] = "David Copperfield";
    dr["ACCOUNT_NAME"] = "ABC Bank";
    dr["CONTACT_SUPPORT_END_DATE"] = "10 - 03 - 2007";
    dt.Rows.Add(dr);
    ds.Tables.Add(dt);

    // Create another data table
    DataTable reportdata4 = new DataTable("ReportData4");
    reportdata4.Columns.Add(new DataColumn("Asset_Name", Type.GetType("System.String")));
    reportdata4.Columns.Add(new DataColumn("Latest_Release_Version", Type.GetType("System.String")));
    dr = reportdata4.NewRow();
    dr["Asset_Name"] = "ABC";
    dr["Latest_Release_Version"] = "1.2";
    reportdata4.Rows.Add(dr);
    dr = reportdata4.NewRow();
    dr["Asset_Name"] = "DEF";
    dr["Latest_Release_Version"] = "1.4";
    reportdata4.Rows.Add(dr);
    ds.Tables.Add(reportdata4);

    // Merge the table into the Excel template
    WorkbookDesigner designer = new WorkbookDesigner();

    // Open a designer spreadsheet containing smart markers
    designer.Open(@"c:\Drew\ExcelTemplate2.xls");

    // Set the data source for the designer spreadsheet
    designer.SetDataSource(ds);

    // Process the smart markers
    designer.Process(false);

    // Convert to PDF
    // Save the word document to a memory stream
    MemoryStream stream = new MemoryStream();
    designer.Workbook.CalculateFormula(true);
    designer.Workbook.Save(stream, FileFormatType.AsposePdf);
    stream.Seek(0, SeekOrigin.Begin); // Reset the pointer to the beginning of the stream

    // Load the document as raw XML
    XmlDocument xmlDoc = new XmlDocument();
    xmlDoc.Load(stream);

    // Open in Aspose PDF
    Aspose.Pdf.Pdf pdf = new Aspose.Pdf.Pdf();
    pdf.BindXML(xmlDoc, null);

    // Output the PDF to the browser
    pdf.Save(@"output.pdf", Aspose.Pdf.SaveType.OpenInAcrobat, Response);
}

I’ve attached the template.

#2

Hi Mike,

Thanks for the template file.

Yes, we found the problem, We will fix this issue in the next week.

Thanks for being patient!

#3

Thank you, Amjad.

Did you all happen to notice the other problem mentioned in the listing from the PDF forum? With this same template and conversion to PDF, the date in cell J16 appears correctly in the Excel output, but shows as “#VALUE” in the PDF.

Is this an issue with Cells or with PDF?

#4
Hi,

I sorted it out, I think the problem is you have set the printable area "A1:H40" and in your template file, C40 is a merged cell (C40:H40). So, I think you can extend your printable area of the worksheet to resolve both your issues. Either you may set the Print area in MS Excel manually i.e. File|Page Setup... and click Sheet tab to set print area Or you may use Aspose.Cells APIs to set the print area.

E.g.,

I embed a line of code in your code snippet and attached is output .pdf file.

// Set the data source for the designer spreadsheet
designer.SetDataSource(ds);
// Process the smart markers
designer.Process(false);

designer.Workbook.Worksheets[0].PageSetup.PrintArea = "A1:M41";

// Convert to PDF
// Save the word document to a memory stream
MemoryStream stream = new MemoryStream();
designer.Workbook.CalculateFormula(true);
designer.Workbook.Save(stream, FileFormatType.AsposePdf);

Thank you.

#5

Hi Amjad,

Unfortunately your idea won’t work because the printable area settings are fundamental to the worksheet. Since it’s not possible to wrap an Excel function around a Smart Marker field, I have to merge the data into a non-printable area and then use the formula in C40 to convert the data to the appropriate format.

It may be possible to move the Smart Markers to a completely different sheet, rather than using the non-printable area, but it would be better if it worked without doing any special techniques.

Also, the programmatic technique also doesn’t help since my application is generic and can’t specifiy a print range.

Thanks for looking into this though. Please let me know if you have other thoughts on this. Also, looking forward to the fix that corrects the main problem.

Mike

#6
Hi Mike,

I think you may try to change the code to set the dynamic printable area before saving to .xml file (Aspose.Pdf Integrated).

i.e.,

int maxrow = workbook.Worksheets[0].Cells.MaxDataRow +1;
int maxcol = workbook.Worksheets[0].Cells.MaxDataColumn+1;
workbook.Worksheets[0].PageSetup.PrintArea = "A1" + ":" + CellsHelper.ColumnIndexToName(maxcol).ToString()+ CellsHelper.RowIndexToName(maxrow).ToString();

Thank you.

#7

I’ll give that a shot… many thanks.

#8

Hi Amjad,

Just wondering how the fix to PDF is coming regarding the original part of this message thread.

Thanks for letting me know.

Mike

#9

Looks like this was fixed in the PDF 3.6.1.0 build. I’ll try it out.

#10

Hi guys –

The new version of PDF 3.6.1.0 definitely fixed the main problem of the Excel sheet getting cut-off when converting to PDF. That’s great.

The secondary issue dealing with the date value in cell D16 is still an issue. At first, it was suggested that I change the printable area to include a referenced cell, but that doesn’t seem to help.

When the code below outputs an Excel file (after merging, see line 68-70), the value of D16 appears as a date. (It works whether I use =Date() or =DateValue()). However, if I comment out the Excel output and go straight to PDF, the value in D16 shows as “!VALUE” in the output PDF.

Would you please revisit this question?

Here’s my code (mostly the same as the prior version):
protected void Button1_Click(object sender, EventArgs e)
{
// Create a dataset
DataSet ds = new DataSet();

// Create a datatable
DataTable dt = new DataTable(“Master”);
dt.Columns.Add(new DataColumn(“LEAD_FULLNAME”, Type.GetType(“System.String”)));
dt.Columns.Add(new DataColumn(“ACCOUNT_NAME”, Type.GetType(“System.String”)));
dt.Columns.Add(new DataColumn(“CONTACT_SUPPORT_END_DATE”, Type.GetType(“System.String”)));
DataRow dr = dt.NewRow();
dr[“LEAD_FULLNAME”] = “David Copperfield”;
dr[“ACCOUNT_NAME”] = “ABC Bank”;
dr[“CONTACT_SUPPORT_END_DATE”] = “2007-09-28”;
dt.Rows.Add(dr);
ds.Tables.Add(dt);

// Create another data table
DataTable reportdata4 = new DataTable(“ReportData4”);
reportdata4.Columns.Add(new DataColumn(“Asset_Name”, Type.GetType(“System.String”)));
reportdata4.Columns.Add(new DataColumn(“Latest_Release_Version”, Type.GetType(“System.String”)));
dr = reportdata4.NewRow();
dr[“Asset_Name”] = “ABC”;
dr[“Latest_Release_Version”] = “1.2”;
reportdata4.Rows.Add(dr);
dr = reportdata4.NewRow();
dr[“Asset_Name”] = “DEF”;
dr[“Latest_Release_Version”] = “1.4”;
reportdata4.Rows.Add(dr);
ds.Tables.Add(reportdata4);

// Merge the table into the Excel template
WorkbookDesigner designer = new WorkbookDesigner();

// Open a designer spreadsheet containing smart markers
designer.Open(@“c:\Drew\ExcelTemplate2.xls”);

// Set the data source for the designer spreadsheet
designer.SetDataSource(ds);

// Process the smart markers
designer.Workbook.CalculateFormula(true);
designer.Process(false);

//// Output the Excel file
// designer.Save(“output.xls”, Aspose.Cells.SaveType.OpenInExcel, FileFormatType.Default, Response);
//return;


// Convert to PDF
// Save the word document to a memory stream
MemoryStream stream = new MemoryStream();
designer.Workbook.CalculateFormula(true);
designer.Workbook.Save(stream, FileFormatType.AsposePdf);
stream.Seek(0, SeekOrigin.Begin); // Reset the pointer to the beginning of the stream

// Load the document as raw XML
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(stream);

// Open in Aspose PDF
Aspose.Pdf.Pdf pdf = new Aspose.Pdf.Pdf();
pdf.BindXML(xmlDoc, null);

// Output the PDF to the browser
pdf.Save(@“output.pdf”, Aspose.Pdf.SaveType.OpenInAcrobat, Response);
}

Attached is the latest version of the Excel template.

Thanks much.

#11

Hi,

Please try this fix. It's caused by calculating formulas.

#12

Hello Warren,

That definitely fixed the problem, thank you. I noticed that using =DateValue( ) still doesn’t work and that =DateValue isn’t in the list of functions supported in .NET Cells. Will that function (and all the others supported in Java) be supported in the future?

Again, my thanks for your help. You guys always do a great job.

Mike

#13

Hi Mike,

After testing DateValue funtion, we find .Net version have supported this function. We will update the wiki.

#14

Excellent. Thank you! Looking forward to more functions being supported in future versions.

Mike