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);
}
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.
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);
// 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);
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.
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();
// 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.
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.