I am converting an excel file to pdf. During conversion, empty rows and columns are removed and the row with the content is shifted at the top. The empty rows, columns had no content but the image. Since the content was not present, rows were deleted and content got shifted above. When I convert this excel to pdf, the existing image which was on top of empty rows is now overlapping with the content . Is there any option available to print the images at the end of the sheet, just like comments.
Quicker response is appreciated. Thanks in advance.
Can you please provide more details about the method you are using for converting the Excel file to PDF and the version of Aspose.Cells and Aspose.Pdf you are using?
@Professionalize.Discourse
I am using Aspose.Cells.PdfSaveOptions for customizing the pdf options and workbook.save() to convert to pdf. I am using 24.7.0 verison of Aspose.Cells and Aspose.Pdf
@shreyap
Could you provide code you’re using?
Based on your description this issue may be related more to Aspose.Cells department which has different branch on this forum but maybe I’m wrong
As far as I know, Aspose PDF is more focused on PDF to Excel conversion an this is not the scenario you are using
@ilyazhuykov
I am just using DeleteBlankRows of aspose.cells to delete any empty rows.
Do we have any option in pdf, similar to pageSetup class where I can allot the place of image.
@shreyap
Do I understand correctly that issues you’re facing happen after you use cells DeleteBlankRows option or you’re trying to do some operations with result PDF where image overlaps content and your goal is to place content after image?
@ilyazhuykov
EMpty row.zip (387.6 KB)
Converted files.zip (804.1 KB)
Empty row zip is the actual file. I am removing empty rows during conversion and the output pdf and excel are in converted zip. In converted pdf as you can observe content is being overlapped by image. I would like to have an option of setting the image after the sheetcontent is being printed i.e. at the end of the sheet.
@shreyap
Please refer to the following example code to adjust the position of the image. Please refer to the attachment. out_net.zip (843.1 KB)
Workbook wb = new Workbook(filePath + "EMpty row.xlsx");
wb.Worksheets[0].Cells.DeleteBlankRows();
Worksheet ws = wb.Worksheets[0];
ws.Shapes[0].UpperLeftRow = ws.Cells.MaxDataRow + 1;
wb.Save(filePath + "out_net.xlsx");
wb.Save(filePath + "out_net.pdf");
Hope helps a bit.
@John.He
Thank you for the solution. Can you let me know how to handle when I have n number of images
@shreyap
We think you may traverse all shapes in for loop one by one, and set the LowerRightRow of previous shape as the UpperLeftRow of the next shape.
Moreover, to ensure that multiple shapes or images are placed precisely in a worksheet without overlapping existing contents, you need to determine the positions of these shapes relative to the existing data and then adjust their positions accordingly. See the sample code snippet to demonstrate it for your reference.
e.g.,
Sample code:
// Load the workbook
Workbook wb = new Workbook(filePath + "Book1.xlsx");
Worksheet ws = wb.Worksheets[0];
// Delete any blank rows
ws.Cells.DeleteBlankRows();
// Find the last data row
int lastDataRow = ws.Cells.MaxDataRow;
// Place each image after last data row
foreach (Aspose.Cells.Drawing.Shape shape in ws.Shapes)
{
// Check if the shape is currently in a row after the last data row
if (shape.UpperLeftRow <= lastDataRow)
{
// Move shape to a new row after the last data row
shape.UpperLeftRow = lastDataRow + 1;
// Update the row for the next shape
lastDataRow = shape.UpperLeftRow + (shape.Height / ws.Cells.GetRowHeightPixel(shape.UpperLeftRow)) + 1;
}
}
// Save the workbook
wb.Save(filePath + "out_net1.xlsx");
wb.Save(filePath + "out_net1.pdf");
Hope, this helps a bit. You may write your code snippet accordingly for your needs.
@amjad.sahi , @John.He
var totalEmptyRowsToBeInserted = shape.LowerRightRow - shape.UpperLeftRow;
worksheet.Cells.InsertRows(shape.UpperLeftRow, totalEmptyRowsToBeInserted);
shape.UpperLeftRow = shape.UpperLeftRow - totalEmptyRowsToBeInserted;
shape.LowerRightRow = shape.LowerRightRow - totalEmptyRowsToBeInserted;
I am inserting back empty rows that I have deleted earlier and then placing the image on top of those empty rows.
Initially lowerRightRow = 17 and UpperLeftRow = 0
totalEmptyRowsToBeInserted = 17
after insertion of rows, UpperLeftRow = 17, lowerRightRow =34
I am trying to change UpperLeftRow, lowerRightRow to their initial values i.e. 0,17 respectively. But shape.LowerRightRow is having a value of 91 after subtraction.
May I know what is happening internally.
I tested your scenario/case a bit. It works fine with your provided Excel file. Here is the sample code (I included your lines of code) that I am using.
e.g.,
Sample code:
Workbook wb = new Workbook("e:\\test2\\EMpty row.xlsx");
wb.Worksheets[0].Cells.DeleteBlankRows();
Worksheet ws = wb.Worksheets[0];
Console.WriteLine(ws.Shapes[0].UpperLeftRow);
Console.WriteLine(ws.Shapes[0].LowerRightRow);
ws.Shapes[0].UpperLeftRow = ws.Cells.MaxDataRow + 1;
var totalEmptyRowsToBeInserted = ws.Shapes[0].LowerRightRow - ws.Shapes[0].UpperLeftRow;
ws.Cells.InsertRows(ws.Shapes[0].UpperLeftRow, totalEmptyRowsToBeInserted);
ws.Shapes[0].UpperLeftRow = ws.Shapes[0].UpperLeftRow - totalEmptyRowsToBeInserted;
ws.Shapes[0].LowerRightRow = ws.Shapes[0].LowerRightRow - totalEmptyRowsToBeInserted;
Console.WriteLine(ws.Shapes[0].UpperLeftRow);
Console.WriteLine(ws.Shapes[0].LowerRightRow);
output:
0
10
3
13
Please evaluate your logic with respect to your Excel file. In case you still find any issue, kindly do provide a standalone sample (runnable) code and template Excel file (please zip the resource files prior attaching here). We will check your issue soon.
@amjad.sahi ,
Can you please test with below attachment.
Test.zip (166.9 KB)
In the above provided solution by you,
ws.Shapes[0].UpperLeftRow = ws.Cells.MaxDataRow + 1; I am not sure why this operation is required.
I would like to place the image where ever it was before deletion of empty rows, not at the end of the file
Steps I am trying to follow:
-
Delete all the empty rows
-
Iterate through each sheet and get its shapes, if shapes type is picture or chart, I will get its coordinates.
-
I will insert empty rows in those coordinates . Since inserting empty rows, would shift the image by those number of rows, I am subtracting the number of empty rows value with the coordinates.
Which would make the image to get back to its original position. -
This would avoid overlapping of content and image
workbook.Worksheets.Cells.DeleteBlankRows();
foreach(var worksheet in workbook.Worksheets)
{
if(worksheet.Shapes.Count > 0)
{
foreach(Shape shape in worksheet.Shapes)
{
var typr = shape.GetType();
if (shape is Picture || shape is ChartShape)
{
if (shape is Picture)
{
if (((Picture)shape).Hyperlink != null || ((Picture)shape).IsLink)
{
continue;
}
}
var totalEmptyRowsToBeInserted = shape.LowerRightRow - shape.UpperLeftRow;
worksheet.Cells.InsertRows(shape.UpperLeftRow, totalEmptyRowsToBeInserted);
shape.UpperLeftRow = shape.UpperLeftRow - totalEmptyRowsToBeInserted;
shape.LowerRightRow = shape.LowerRightRow - totalEmptyRowsToBeInserted;
}
}
}
}
shape.LowerRightRow = shape.LowerRightRow - totalEmptyRowsToBeInserted;
Is behaving in-consistently.
@shreyap
After setting the UpperLeftRow property of the Shape, the LowerLightRow value will also change accordingly. Please refer to the following example code. Please refer to the attachment. out_net.zip (168.6 KB)
Workbook workbook = new Workbook(filePath + "Test.xlsx");
workbook.Worksheets[0].Cells.DeleteBlankRows();
foreach (var worksheet in workbook.Worksheets)
{
if (worksheet.Shapes.Count > 0)
{
foreach (Shape shape in worksheet.Shapes)
{
var typr = shape.GetType();
if (shape is Picture || shape is ChartShape)
{
if (shape is Picture)
{
if (((Picture)shape).Hyperlink != null || ((Picture)shape).IsLink)
{
continue;
}
}
var totalEmptyRowsToBeInserted = shape.LowerRightRow - shape.UpperLeftRow;
worksheet.Cells.InsertRows(shape.UpperLeftRow, totalEmptyRowsToBeInserted);
shape.UpperLeftRow = shape.UpperLeftRow - totalEmptyRowsToBeInserted;
//shape.LowerRightRow = shape.LowerRightRow - totalEmptyRowsToBeInserted;
}
}
}
}
workbook.Save(filePath + "out_net.xlsx");
@John.He ,
Thank you for the solution. It works.
There is original position of an image. Empty rows are deleted and bottom cell data is shifted to top. Since the position of the image is still present I am adding extra rows and placing the image on top of it. How do I verify that there is cell data behind the original position of image and so I need to add some extra rows and skip adding of rows when there is no data behind.
For reference consider sheet 1 and 2 of attached file where sheet 1 has no data and requires adding of empty rows. Sheet 2 image does not need adding of empty rows.
Testfile.zip (2.1 MB)
@shreyap
You can determine whether there is data in the position of the image by comparing Shape.UpperLeftRow and Worksheet.Cells.MaxDataRow.