// Ensure text boxes update their values and adjust size before conversion
foreach (Worksheet sheet in workbook.Worksheets)
{
foreach (Shape shape in sheet.Shapes)
{
if (shape.MsoDrawingType == MsoDrawingType.TextBox)
{
// Ensure text wrapping is enabled
shape.IsTextWrapped = true;
// Adjust shape height dynamically
int rowIndex = shape.UpperLeftRow;
shape.Height = sheet.Cells.GetRowHeightPixel(rowIndex) * 3; // Scale height based on row height
}
}
sheet.Shapes.UpdateSelectedValue(); // Force update text values
}
// Recalculate formulas to ensure data is fresh
workbook.CalculateFormula();
// Configure PDF save options
var pdfOptions = new PdfSaveOptions
{
OnePagePerSheet = false, // Allows natural page breaks
AllColumnsInOnePagePerSheet = true, // Keeps text aligned properly
DefaultFont = "Arial" // Ensures consistent font rendering
};
// Save the workbook as a PDF
workbook.Save(outputFilePath, pdfOptions);
}
catch (Exception ex)
{
results = "ConvertExcel ERROR: " + ex.ToString();
}
Console.WriteLine(results);
return results;
}
I have attached an XLSX file and the resulting PDF. You can see the missing data in the textboxes on the page. It includes some of the text in these areas but not all. It also places a text box in the wrong location in this particular example.
Do you see anything in my code that potentially is causing it or any changes I could make to resolve the problem.
I am using version Aspose.Cells 25.1.2.
thank you in advance for any assistance you can provide in the future.
It seems you are experiencing issues with converting XLSX and XLSM files containing text boxes to PDF using Aspose.Cells. Based on your provided code and the description of the problem, here are a few suggestions that might help resolve the issues with missing data in text boxes and incorrect positioning:
Ensure Text Box Properties are Set Correctly: You are already enabling text wrapping and adjusting the height of the text boxes. However, you might want to ensure that the text box’s width is also set appropriately to accommodate the text. You can adjust the width based on the content or a fixed value.
Update Shape Values: You are calling sheet.Shapes.UpdateSelectedValue() to force an update of text values. Make sure this is being executed after all modifications to the shapes are done. This ensures that the latest values are reflected before the conversion.
Check for Overlapping Shapes: If there are multiple shapes in the same area, they might overlap, causing some text to be hidden. Ensure that the shapes are positioned correctly and do not overlap.
Use PdfSaveOptions.IgnoreError: If there are any errors during the conversion process that might be causing data loss, consider using the PdfSaveOptions.IgnoreError property. This allows the conversion to complete even if there are issues, but be cautious as it may lead to data loss in some cases.
Recalculate Formulas: You are already recalculating formulas before saving. Ensure that all necessary data is updated before the conversion.
Here’s a modified version of your code snippet that incorporates some of these suggestions:
static public string ConvertExcel(string inputFilePath = "", string outputFilePath = "")
{
string results = "SUCCESS";
try
{
// Load the workbook
Workbook workbook = new Workbook(inputFilePath);
// Ensure text boxes update their values and adjust size before conversion
foreach (Worksheet sheet in workbook.Worksheets)
{
foreach (Shape shape in sheet.Shapes)
{
if (shape.MsoDrawingType == MsoDrawingType.TextBox)
{
// Ensure text wrapping is enabled
shape.IsTextWrapped = true;
// Adjust shape height and width dynamically
int rowIndex = shape.UpperLeftRow;
shape.Height = sheet.Cells.GetRowHeightPixel(rowIndex) * 3; // Scale height based on row height
shape.Width = 200; // Set a fixed width or calculate based on content
}
}
sheet.Shapes.UpdateSelectedValue(); // Force update text values
}
// Recalculate formulas to ensure data is fresh
workbook.CalculateFormula();
// Configure PDF save options
var pdfOptions = new PdfSaveOptions
{
OnePagePerSheet = false,
AllColumnsInOnePagePerSheet = true,
DefaultFont = "Arial",
IgnoreError = true // Ignore errors during conversion
};
// Save the workbook as a PDF
workbook.Save(outputFilePath, pdfOptions);
}
catch (Exception ex)
{
results = "ConvertExcel ERROR: " + ex.ToString();
}
Console.WriteLine(results);
return results;
}
Make sure to test the modified code with your specific files to see if it resolves the issues. If problems persist, consider checking the Aspose.Cells documentation for any updates or known issues related to text box rendering in PDFs.
If you continue to face challenges, feel free to provide more details or specific examples, and I would be happy to assist further!
@david_j_meyer2_boeing_com
Through studying your sample file, we found that the text box that was not output to PDF is outside the printing area. If you need to display it, please set the printing area to an empty string. In addition, there are some comments. If you need to display them, please refer to the sample code to set the visible attribute of the comment to true and the placement position. Please refer to the attachment. out_net.zip (117.2 KB)
The sample code as follows:
static public string ConvertExcel(string inputFilePath = "", string outputFilePath = "")
{
string results = "SUCCESS";
try
{
// Load the workbook
Workbook workbook = new Workbook(inputFilePath);
// Ensure text boxes update their values and adjust size before conversion
foreach (Worksheet sheet in workbook.Worksheets)
{
// setting print area and comment placement position
sheet.PageSetup.PrintArea = "";
sheet.PageSetup.PrintComments = PrintCommentsType.PrintInPlace;
// show comment
foreach (Comment comment in sheet.Comments)
{
comment.IsVisible = true;
}
foreach (Shape shape in sheet.Shapes)
{
if (shape.MsoDrawingType == MsoDrawingType.TextBox)
{
// Ensure text wrapping is enabled
shape.IsTextWrapped = true;
// Adjust shape height dynamically
int rowIndex = shape.UpperLeftRow;
shape.Height = sheet.Cells.GetRowHeightPixel(rowIndex)* 3; // Scale height based on row height
}
}
sheet.Shapes.UpdateSelectedValue(); // Force update text values
}
// Recalculate formulas to ensure data is fresh
workbook.CalculateFormula();
// Configure PDF save options
PdfSaveOptions pdfOptions = new PdfSaveOptions
{
OnePagePerSheet = false, // Allows natural page breaks
AllColumnsInOnePagePerSheet = true, // Keeps text aligned properly
DefaultFont = "Arial" // Ensures consistent font rendering
};
// Save the workbook as a PDF
workbook.Save(outputFilePath, pdfOptions);
}
catch (Exception ex)
{
results = "ConvertExcel ERROR: " + ex.ToString();
}
Console.WriteLine(results);
return results;
}
@david_j_meyer2_boeing_com
By testing on the latest version v25.5 using sample files and code, we can reproduce the issue. The comment arrow position was incorrect after converting the file to PDF.
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.
Issue ID(s): CELLSNET-58392
You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.
Regarding the issue of data loss in the text box, please comment out the code that changes the height of the text box. Please refer to the attachment. out_net2.zip (82.0 KB)
static public string ConvertExcel(string inputFilePath = "", string outputFilePath = "")
{
string results = "SUCCESS";
try
{
// Load the workbook
Workbook workbook = new Workbook(inputFilePath);
// Ensure text boxes update their values and adjust size before conversion
foreach (Worksheet sheet in workbook.Worksheets)
{
sheet.PageSetup.PrintArea = "";
foreach (Shape shape in sheet.Shapes)
{
if (shape.MsoDrawingType == MsoDrawingType.TextBox)
{
// Ensure text wrapping is enabled
shape.IsTextWrapped = true;
// Adjust shape height dynamically
//int rowIndex = shape.UpperLeftRow;
//shape.Height = sheet.Cells.GetRowHeightPixel(rowIndex) * 3; // Scale height based on row height
}
}
sheet.Shapes.UpdateSelectedValue(); // Force update text values
}
// Recalculate formulas to ensure data is fresh
workbook.CalculateFormula();
// Configure PDF save options
PdfSaveOptions pdfOptions = new PdfSaveOptions
{
OnePagePerSheet = false, // Allows natural page breaks
AllColumnsInOnePagePerSheet = true, // Keeps text aligned properly
DefaultFont = "Arial" // Ensures consistent font rendering
};
// Save the workbook as a PDF
workbook.Save(outputFilePath, pdfOptions);
}
catch (Exception ex)
{
results = "ConvertExcel ERROR: " + ex.ToString();
}
Console.WriteLine(results);
return results;
}
The code provided worked, all the content in the text boxes in the defined printed area where included. We do not want the colored text boxes that are outside the printed arae included in the pdf.
I just want to clarify what I’m reading in the responses to make sure I understand. There is a bug that a ticket has been submitted for?
If the suggested code segment meets your needs, you can continue using it as it effectively serves the purpose.
We discovered a bug affecting the positioning of comment arrows/lines when rendering PDFs with inline comments. We’ve logged an internal ticket and are working on a solution. Since you’re not concerned with these comment arrows/lines and only wish to render your specified range or printed area, you can disregard this issue.
Just to inform you that the issue (logged earlier as “CELLSNET-58392”) has been resolved. The fix/enhancement will be included in the upcoming release (Aspose.Cells v25.6) that we plan to release in the first half of June 2025. Since the suggested code segment is already functioning well for your needs. However, the decision to upgrade to the next version is entirely up to you. But it is always good to try the latest version which will have more enhancements and fixes.
The issues you have found earlier (filed as CELLSNET-58392) have been fixed in this update. This message was posted using Bugs notification tool by leoluo