Hello Aspose team,
I have a template named excelTemplate.xlsx which i have attached in this comment. In Display1 sheet you can see there are equal grid area 4 in each 9 grid rows. Hence, there are altogether 9*4=36 grid areas. Each of the grid areas are named as 1,2,3,4…36. For example, I need to insert a chart in grid 4. For that I need to extract the area for grid 4 and insert the image in that grid using aspose java.
Can you please provide us the workable code example for such scenarios ?
excelTemplate.zip (295.6 KB)
@niteshregmi1234,
Thanks for the template file and details.
See the following sample on how to get the fourth shape in the sheet and gets its location details for your reference:
e.g
Sample code:
Workbook workbook = new Workbook("f:\\files\\excelTemplate.xlsx");
Worksheet worksheet = workbook.getWorksheets().get("Display1");
int lowerRightRow, lowerRightCol, upperLeftRow, upperLeftCol;
//get the location of the shape (grid) 4
Shape shape = worksheet.getShapes().get("Section4_1x1");
upperLeftRow = shape.getUpperLeftRow();
upperLeftCol = shape.getUpperLeftColumn();
lowerRightRow = shape.getLowerRightRow();
lowerRightCol = shape.getLowerRightColumn();
int left = shape.getLeft();
int top = shape.getTop();
System.out.println("Left row: " + upperLeftRow + " , Left column: " + upperLeftCol + ", Lower right row: " + lowerRightRow + ", Lower right column: " + lowerRightCol);
Please refer to the code segment and write your own code and logic by yourself to insert the picture accordingly.
Hope, this helps a bit.
@Amjad_Sahi
Thanks, for the workable code amjad.
I am able to place the widgets in the respective position of the grid using the excel template attached above but while generating the pdf the charts are distorted. Can you please look into this.
I have attached the excel template containing the widgets with this comment.
resource.zip (564.8 KB)
code snippet
Workbook workbook = new Workbook(io);
workbook.calculateFormula();
workbook.save(“myresult.pdf”); //used Display1 sheet
@niteshregmi1234,
Thanks for the template file and sample code segment.
Which charts you are talking about? I do not see any chart in the sheet when opening your file into MS Excel manually, see the screenshot for your reference:
Even I do not find charts in other worksheets too. Please elaborate your issue and provide some screenshots while comparing the shapes (displayed) in MS Excel Vs output PDF (by Aspose.Cells), we will check it soon.
@Amjad_Sahi
Sorry amjad, When i open it from linux libre office then the widgets are correctly placed in the required places but when seen from MS excel these widgets are distorted. Can you please help us providing the solution for placing the charts in the grid position exactly in the template. When i used the below logic of code then the widgets are not correctly positioned in the template in MS excel . My code is
FileInputStream io = new FileInputStream(new File(“DashboardTemplate.xlsx”));
Workbook workbook = new Workbook(io);
int i = ws.getPictures().add(0,0 , “chart.png”);
Picture picture = ws.getPictures()[i]
for (im; im < 3; im++) {
if (im == 1) {
dx = 1.93
dy = 0
picture.setHeightInch(1.04)
picture.setWidthInch(1.9) //for placing the widget in second grid of 0th row
} else if (im == 2) {
dx = 1.93+1.9
dy = 0
picture.setHeightInch(1.04)
picture.setWidthInch(1.9) //for placing widget in third grid of 0th row
}
}
picture.setLeftInch(dx)
picture.setTopInch(dy);
When you see in the template the first grid is of resolution of 1.93/1.04 in inch whereas the following grid are of resolution of 1.9/1.04 in inch.
This causes the chart to appear distorted. What is the solution for placing the widget successfully in the correct grid without distortion.
Please use the chart and template attached in above comments.
@niteshregmi1234,
It seems that you want to get/set resolution as the widgets are not distorted in MS Excel fine. Could, you provide a workable sample code (runnable) with the three pictures that he need to insert and to show the issue, we will check it soon. Moreover, you may also attach some screenshots (for input and output file) taken on linux libre office and sample files for reference.
@Amjad_Sahi,
ok i will provide all the required information mentioned by you. But is there any method in aspose api which allow to insert image inside the shape. I want to insert the image on specific shape of worksheet ??
@niteshregmi1234,
No there is no specific method to insert image into shape as you have to write your own code to do that (after finding the shape coordinates) accordingly.
@Amjad_Sahi
ok Amjad,
But if my co-ordinate measurement unit is in inch which is in decimal format then how to set image in that co-ordinate shape ?
@niteshregmi1234,
Please try the following code to add picture at the location of desired shape and share your feedback.
Workbook wb = new Workbook("excelTemplate.xlsx");
Worksheet sheet = wb.getWorksheets().get("Display1");
for(int i = 1; i <= 36; i++)
{
Shape shape = sheet.getShapes().get("Section" + i + "_1x1");
int pictureIndex = sheet.getPictures().add(shape.getUpperLeftRow(), shape.getUpperLeftColumn(), BUG_DIR + "tempBug/image3.png");
Picture picture = sheet.getPictures().get(pictureIndex);
picture.setLeft(shape.getLeft());;
picture.setTop(shape.getTop());;
picture.setWidth(shape.getWidth());;
picture.setHeight(shape.getHeight());;
picture.setPlacement(shape.getPlacement());;
}
wb.save("outfile.xlsx");
@ahsaniqbalsidiqui and @Amjad_Sahi
Bravooo that is what we exactly wanted. Above solution have resolved almost all of our problems. Thanks a lot for the help from both of you.
If any problem arises then we will update in this mail . For now this helps alot.
@niteshregmi1234,
Good to know that your issue is sorted out by the suggested sample code. Feel free to contact us at any time if you need further help or have some other issue or queries, we will be happy to assist you soon.