Hi! @peyton.xu
I tried with the approach you specified.
But still I am not able to achieve my requirements.
And I want my cells to be auto fit according to the content of the cell.
Because for the web version of Powerpoint, Users will not be able to edit the Image in PPT.
Please could you let me know, if there is any other approach of handling it.
thanks!
Rohan
Is the text vertical alignment issue fixed after setting System.setProperty("Aspose.Cells.Disable", "EmfPlus");?
Do you mean that you need the code dataSheet.autoFitColumns(); to autofit column width according to cell content? If yes, we will open a ticket to trace the issue that column data is shown as "#######" after autofitting column width.
hi! @peyton.xu
No TextVerticalAlignment is not fixed by setting this property as well.
And I want to use the property autoFit columns as well for the datasheet
Please try latest version/fix: Aspose.Cells for Java v23.6.
If you still find the text alignment issue with Aspose.Cells for Java v23.6, kindly share your sample (runnable) code and sample files that you are using, we will check it soon.
Hi! @amjad.sahi
I tried updating the version and tried setting system property as well.
But it is not working.
I am adding the snippet code for the issue.
Please try to generate the excel with this snippet code to replicate the issue.
thanks!
public static void main(String[] args) throws Exception {
System.setProperty("Aspose.Cells.Disable", "EmfPlus");
String dataDir = "D://AsposeSlides//";
// Create a workbook
Workbook workbook = new Workbook();
// set visible rows and columns count
int chartRows = 25;
int chartCols = 11;
Worksheet dataSheet = workbook.getWorksheets().get(0);
com.aspose.cells.Cells cells = dataSheet.getCells();
// Adding a string value to the cell
com.aspose.cells.Cell cell = cells.get("A1");
cell.setValue("EMP ID");
Style style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());
cell.setStyle(style);
cell = cells.get("B1");
cell.setValue("CITY");
style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());
cell.setStyle(style);
cell = cells.get("C1");
cell.setValue("NAME");
style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());
cell.setStyle(style);
cell = cells.get("D1");
cell.setValue("LPA");
style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.RIGHT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, Color.getBlack());
cell.setStyle(style);
dataSheet.autoFitColumns();
// Set chart print area
dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
+ dataSheet.getCells().get(chartRows + 1, chartCols).getName());
// Set chart ole size
workbook.getWorksheets().setOleSize(0, chartRows, 0, chartCols);
// Get the chart worksheet as an image
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(ImageType.EMF);
imageOptions.setOnlyArea(true);
imageOptions.setOnePagePerSheet(true);
imageOptions.setAllColumnsInOnePagePerSheet(true);
SheetRender sheetRender = new SheetRender(dataSheet, imageOptions);
ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
sheetRender.toImage(0, imageStream);
byte[] imageByteArray = imageStream.toByteArray();
// Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.save(bout, com.aspose.cells.SaveFormat.XLSX);
// Create a presentation
Presentation pres = new Presentation();
ISlide sld = pres.getSlides().get_Item(0);
// Add the workbook to the slide
IOleEmbeddedDataInfo dataInfo = new OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(1f, 1f,
(float) pres.getSlideSize().getSize().getWidth() - 2,
(float) pres.getSlideSize().getSize().getHeight() - 2, dataInfo);
oof.getSubstitutePictureFormat().getPicture()
.setImage(pres.getImages().addImage(imageByteArray));
// Write the presentation to disk
pres.save(dataDir + "Example-out-dataSummary7.pptx", com.aspose.slides.SaveFormat.Pptx);
}
@Rohan_Wankar,
Please change the following code:
dataSheet.autoFitColumns();
To:
dataSheet.autoFitColumns();
dataSheet.autoFitRows(true);
After changing the code, we can obtain the correct results through testing. Please check the attachment. (31.8 KB).
Hi! @John.He
AutoFitRows resolved the issue for the row height.
But I have one more issue with respect to autofitColumns for the cell content.
Why we are seeing ####### for the cell content even if we do autoFitColumns()?
Could you please help me with this as well?
thanks!
Rohan
Regarding the data shown as "#######", please do not use the following line of code::
dataSheet.autoFitColumns();
Hi! @amjad.sahi
I tried removing dataSheet.autoFitColumns() as well
But still I am seeing the ##### symbol for the fact values.
Could you please help me with this issue?
thanks!
Rohan
Please share complete sample code and sample files to reproduce the issue, we will check it soon.
Hi! @amjad.sahi
I have used the snippet code which I have added early in the chat.
I just remove dataSheet.autoFitColumns() from the snippet code.
Please take a look at the same snippet code and let me know whether it is achievable.
thanks!
Rohan
I tested the following sample code and it works fine.
e.g.
Sample code:
System.setProperty("Aspose.Cells.Disable", "EmfPlus");
String dataDir1 = "f:\\files\\";
// Create a workbook
Workbook workbook = new Workbook();
// set visible rows and columns count
int chartRows = 25;
int chartCols = 11;
Worksheet dataSheet = workbook.getWorksheets().get(0);
com.aspose.cells.Cells cells = dataSheet.getCells();
// Adding a string value to the cell
com.aspose.cells.Cell cell = cells.get("A1");
cell.setValue("EMP ID");
Style style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
cell.setStyle(style);
cell = cells.get("B1");
cell.setValue("CITY");
style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
cell.setStyle(style);
cell = cells.get("C1");
cell.setValue("NAME");
style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
cell.setStyle(style);
cell = cells.get("D1");
cell.setValue("LPA");
style = cell.getStyle();
style.setVerticalAlignment(TextAlignmentType.TOP);
style.setHorizontalAlignment(TextAlignmentType.RIGHT);
style.getFont().setSize(13);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the left border
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
// Setting the line style of the right border
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, com.aspose.cells.Color.getBlack());
cell.setStyle(style);
dataSheet.autoFitColumns();
dataSheet.autoFitRows(true);
// Set chart print area
dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
+ dataSheet.getCells().get(chartRows + 1, chartCols).getName());
// Set chart ole size
workbook.getWorksheets().setOleSize(0, chartRows, 0, chartCols);
// Get the chart worksheet as an image
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(ImageType.EMF);
imageOptions.setOnlyArea(true);
imageOptions.setOnePagePerSheet(true);
imageOptions.setAllColumnsInOnePagePerSheet(true);
SheetRender sheetRender = new SheetRender(dataSheet, imageOptions);
ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
sheetRender.toImage(0, imageStream);
byte[] imageByteArray = imageStream.toByteArray();
// Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.save(bout, com.aspose.cells.SaveFormat.XLSX);
// Create a presentation
com.aspose.slides.Presentation pres = new com.aspose.slides.Presentation();
com.aspose.slides.ISlide sld = pres.getSlides().get_Item(0);
// Add the workbook to the slide
com.aspose.slides.IOleEmbeddedDataInfo dataInfo = new com.aspose.slides.OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
com.aspose.slides.IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(1f, 1f,
(float) pres.getSlideSize().getSize().getWidth() - 2,
(float) pres.getSlideSize().getSize().getHeight() - 2, dataInfo);
oof.getSubstitutePictureFormat().getPicture()
.setImage(pres.getImages().addImage(imageByteArray));
// Write the presentation to disk
pres.save(dataDir1 + "Example-out-dataSummary7.pptx", com.aspose.slides.SaveFormat.Pptx);
Please find attached the output PPTX file for your reference.
Example-out-dataSummary8.zip (30.8 KB)
If you still find the issue, kindly paste exact code and output PPTX file for reference, we will check it soon.
Hi! @amjad.sahi
I have attached the used sample excel and downloaded PPTX from the sample code mentioned below.
I have commented dataSheet.autoFitColumns() as well, but still seeing #### for the bigger currency values.
Please take a look.
thanks!
public static void main(String[] args) throws Exception {
String dataDir = "D://AsposeSlides//";
// Create a workbook
Workbook workbook = new Workbook(dataDir+"Example.xlsx");
// set visible rows and columns count
int chartRows = 25;
int chartCols = 11;
Worksheet dataSheet = workbook.getWorksheets().get(0);
//dataSheet.autoFitColumns();
// Set chart print area
dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
+ dataSheet.getCells().get(chartRows + 1, chartCols).getName());
// Set chart ole size
workbook.getWorksheets().setOleSize(0, chartRows, 0, chartCols);
// Get the chart worksheet as an image
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(ImageType.EMF);
imageOptions.setOnlyArea(true);
imageOptions.setOnePagePerSheet(true);
imageOptions.setAllColumnsInOnePagePerSheet(true);
SheetRender sheetRender = new SheetRender(dataSheet, imageOptions);
ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
sheetRender.toImage(0, imageStream);
byte[] imageByteArray = imageStream.toByteArray();
// Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.save(bout, com.aspose.cells.SaveFormat.XLSX);
// Create a presentation
Presentation pres = new Presentation();
ISlide sld = pres.getSlides().get_Item(0);
// Add the workbook to the slide
IOleEmbeddedDataInfo dataInfo = new OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(1f, 1f,
(float) pres.getSlideSize().getSize().getWidth() - 2,
(float) pres.getSlideSize().getSize().getHeight() - 2, dataInfo);
oof.getSubstitutePictureFormat().getPicture()
.setImage(pres.getImages().addImage(imageByteArray));
// Write the presentation to disk
pres.save(dataDir+"OLETest2.pptx", com.aspose.slides.SaveFormat.Pptx);
System.err.println("File Generated");
}OLESampleOutput.zip (58.6 KB)
@Rohan_Wankar
By using sample code and file for testing, we can reproduce the issue. I found that the bigger currency values were displayed as a string of # characters.
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): CELLSJAVA-45635
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.
When you use/enble the option imageOptions.setOnlyArea(true);, the result is closer to what you see in Excel Normal view with windows display 100% setting(see screentshot: Normal_view_vs_Print_view.png (77.0 KB)), the text in Cell E5 shows as #. When disable the option imageOptions.setOnlyArea(false);, the result is closer to what you see at Excel Printview.
So, please disable the option imageOptions.setOnlyArea(false); if you want to get the result as Excel Printview.
Hi @amjad.sahi ,
Please find attached herewith sample code for OLE object. As shown in below screenshot, numeric values are aligned to LEFT in last column, whereas text alignment given through code is RIGHT aligned.
image.png (109.7 KB)
{
String dataDir = "C:\\\\Temp\\\\";
// Create a workbook
Workbook workbook = new Workbook();
Cells cells = workbook.getWorksheets().get(0).getCells();
cells.get("A4").putValue("Emp id");
Cell cell1 = cells.get("A4");
Style style1 = cell1.getStyle();
style1.getFont().setBold(true);
style1.getFont().setSize(16);
style1.getFont().setName("Arial");
Color c = Color.getGray();
style1.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style1.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style1.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style1.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style1.setForegroundColor(c);
cell1.setStyle(style1);
cells.get("B4").putValue("City");
Cell cell2 = cells.get("B4");
Style style2 = cell2.getStyle();
style2.getFont().setBold(true);
style2.getFont().setSize(16);
style2.getFont().setName("Arial");
style2.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style2.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style2.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style2.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style2.setForegroundColor(c);
cell2.setStyle(style2);
cells.get("C4").putValue("Name");
Cell cell3 = cells.get("C4");
Style style3 = cell3.getStyle();
style3.getFont().setBold(true);
style3.getFont().setSize(16);
style3.getFont().setName("Arial");
style3.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style3.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style3.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style3.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style3.setForegroundColor(c);
cell3.setStyle(style3);
cells.get("D4").putValue("Last Name");
Cell cell4 = cells.get("D4");
Style style4 = cell4.getStyle();
style4.getFont().setBold(true);
style4.getFont().setSize(16);
style4.getFont().setName("Arial");
style4.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style4.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style4.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style4.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style4.setForegroundColor(c);
cell4.setStyle(style4);
cells.get("E4").putValue("$");
Cell cell5 = cells.get("E4");
Style style5 = cell5.getStyle();
style5.getFont().setBold(true);
style5.getFont().setSize(16);
style5.getFont().setName("Arial");
style5.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style5.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style5.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style5.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style5.setHorizontalAlignment(TextAlignmentType.RIGHT);
style5.setForegroundColor(c);
cell5.setStyle(style5);
cells.get("A5").putValue("Long Text needed");
cells.get("A6").putValue("Long Text needed");
cells.get("A7").putValue("ong Text needed");
cells.get("A8").putValue("Long Text needed");
cells.get("A9").putValue("Long Text needed");
cells.get("A10").putValue("Long Text needed");
cells.get("A11").putValue("Long Text needed");
cells.get("A12").putValue("Long Text needed");
cells.get("A13").putValue("Long Text needed");
cells.get("A14").putValue("Long Text needed");
cells.get("A15").putValue("Long Text needed");
cells.get("A16").putValue("Long Text needed");
cells.get("A17").putValue("Long Text needed");
cells.get("A18").putValue("Long Text needed");
cells.get("A19").putValue("Long Text needed");
cells.get("A20").putValue("Long Text needed");
cells.get("A21").putValue("Long Text neede7");
cells.get("A22").putValue("Long Text needed");
cells.get("A23").putValue("Long Text needed");
cells.get("A24").putValue("Long Text needed");
cells.get("A25").putValue("Long Text needed");
cells.get("A26").putValue("Long Text needed");
cells.get("A27").putValue("Long Text needed");
for(int i=1;i<=24;i++) {
Cell cell = cells.get("A"+(i+4));
Style style = cell.getStyle();
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setName("Arial");
style.getFont().setSize(11);
cell.setStyle(style);
}
cells.get("B5").putValue("Long Text needed");
cells.get("B6").putValue("Long Text needed");
cells.get("B7").putValue("Long Text needed");
cells.get("B8").putValue("Long Text needed");
cells.get("B9").putValue("Long Text needed");
cells.get("B10").putValue("Long Text needed");
cells.get("B11").putValue("Long Text needed");
cells.get("B12").putValue("Long Text needed");
cells.get("B13").putValue("Long Text needed");
cells.get("B14").putValue("Long Text needed");
cells.get("B15").putValue("Long Text needed");
cells.get("B16").putValue("Long Text needed");
cells.get("B17").putValue("Long Text needed");
cells.get("B18").putValue("Long Text needed");
cells.get("B19").putValue("Long Text needed");
cells.get("B20").putValue("Long Text needed");
cells.get("B21").putValue("Long Text needed");
cells.get("B22").putValue("Long Text needed");
cells.get("B23").putValue("Long Text needed");
cells.get("B24").putValue("Long Text needed");
cells.get("B25").putValue("Long Text needed");
cells.get("B26").putValue("Long Text needed");
cells.get("B27").putValue("Long Text needed");
for(int i=1;i<=24;i++) {
Cell cell = cells.get("B"+(i+4));
Style style = cell.getStyle();
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(11);
style.getFont().setName("Arial");
cell.setStyle(style);
}
cells.get("C5").putValue("Long Text needed");
cells.get("C6").putValue("Long Text needed");
cells.get("C7").putValue("c3");
cells.get("C8").putValue("Long Text needed");
cells.get("C9").putValue("e5");
cells.get("C10").putValue("f6");
cells.get("C11").putValue("Long Text needed");
cells.get("C12").putValue("h8");
cells.get("C13").putValue("i9");
cells.get("C14").putValue("Long Text needed");
cells.get("C15").putValue("k11");
cells.get("C16").putValue("l12");
cells.get("C17").putValue("Long Text needed");
cells.get("C18").putValue("n14");
cells.get("C19").putValue("o15");
cells.get("C20").putValue("o15s");
cells.get("C21").putValue("Long Text needed");
cells.get("C22").putValue("o1ssss52");
cells.get("C23").putValue("o1v545");
cells.get("C24").putValue("Long Text needed");
cells.get("C25").putValue("Long Text needed");
cells.get("C26").putValue("ot15");
cells.get("C27").putValue("Long Text needed");
for(int i=1;i<=24;i++) {
Cell cell = cells.get("C"+(i+4));
Style style = cell.getStyle();
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(11);
style.getFont().setName("Arial");
cell.setStyle(style);
}
cells.get("D5").putValue("Long Text needed");
cells.get("D6").putValue("Long Text needed");
cells.get("D7").putValue("Long Text needed");
cells.get("D8").putValue("r");
cells.get("D9").putValue("s");
cells.get("D10").putValue("Long Text needed");
cells.get("D11").putValue("u");
cells.get("D12").putValue("v");
cells.get("D13").putValue("Long Text needed");
cells.get("D14").putValue("x");
cells.get("D15").putValue("Long Text needed");
cells.get("D16").putValue("z");
cells.get("D17").putValue("ab");
cells.get("D18").putValue("ac");
cells.get("D19").putValue("a4d");
cells.get("D20").putValue("adh");
cells.get("D21").putValue("Long Text needed");
cells.get("D22").putValue("ad6");
cells.get("D23").putValue("ad4");
cells.get("D24").putValue("ad3");
cells.get("D25").putValue("ad2");
cells.get("D26").putValue("Long Text needed");
cells.get("D27").putValue("Long Text needed");
for(int i=1;i<=24;i++) {
Cell cell = cells.get("D"+(i+4));
Style style = cell.getStyle();
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style.setHorizontalAlignment(TextAlignmentType.LEFT);
style.getFont().setSize(11);
style.getFont().setName("Arial");
cell.setStyle(style);
}
cells.get("E5").putValue(9678988800L);
cells.get("E6").putValue(9678988800L);
cells.get("E7").putValue(9678988800L);
cells.get("E8").putValue(9678988800L);
cells.get("E9").putValue(9678988800L);
cells.get("E10").putValue(9678988800L);
cells.get("E11").putValue(9678988800L);
cells.get("E12").putValue(9678988800L);
cells.get("E13").putValue(9678988800L);
cells.get("E14").putValue(9678988800L);
cells.get("E15").putValue(9678988800L);
cells.get("E16").putValue(9678988800L);
cells.get("E17").putValue(8678988800L);
cells.get("E18").putValue(9678988800L);
cells.get("E19").putValue(9678988800L);
cells.get("E20").putValue(9678988800L);
cells.get("E21").putValue(9678988800L);
cells.get("E22").putValue(9678988800L);
cells.get("E23").putValue(9678988800L);
cells.get("E24").putValue(1000000000L);
cells.get("E25").putValue(1000000000L);
cells.get("E26").putValue(1000000000L);
cells.get("E27").putValue(1000000000L);
for(int i=1;i<=24;i++) {
Cell cell = cells.get("E"+(i+4));
Style style = cell.getStyle();
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THIN, c);
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THIN, c);
style.getFont().setSize(11);
style.getFont().setName("Arial");
style.setHorizontalAlignment(TextAlignmentType.RIGHT);
style.setVerticalAlignment(TextAlignmentType.TOP);
Shade shade = new Shade();
shade.setColor("#FDFDFD");
ColourProvider d1 = new ColourProvider();
Color d = d1.getColorFromShade(shade);
style.setForegroundColor(d);
style.setPattern(BackgroundType.SOLID);
style.setIndentLevel(0);
style.setCustom("$#,##0;$(#,##0)");
cell.setStyle(style);
}
// set visible rows and columns count
int chartRows = 25;
int chartCols = 11;
Worksheet dataSheet = workbook.getWorksheets().get(0);
dataSheet.autoFitColumns();
// Set chart print area
dataSheet.getPageSetup().setPrintArea(dataSheet.getCells().get(0, 0).getName() + ":"
+ dataSheet.getCells().get(chartRows + 1, chartCols).getName());
// Set chart ole size
workbook.getWorksheets().setOleSize(0, chartRows, 0, chartCols);
// Get the chart worksheet as an image
ImageOrPrintOptions imageOptions = new ImageOrPrintOptions();
imageOptions.setImageType(ImageType.EMF);
imageOptions.setOnlyArea(true);
imageOptions.setOnePagePerSheet(true);
imageOptions.setAllColumnsInOnePagePerSheet(true);
SheetRender sheetRender = new SheetRender(dataSheet, imageOptions);
ByteArrayOutputStream imageStream = new ByteArrayOutputStream();
sheetRender.toImage(0, imageStream);
byte[] imageByteArray = imageStream.toByteArray();
// Save the workbook to stream
ByteArrayOutputStream bout = new ByteArrayOutputStream();
workbook.save(bout, com.aspose.cells.SaveFormat.XLSX);
// Create a presentation
Presentation pres = new Presentation();
ISlide sld = pres.getSlides().get_Item(0);
// Add the workbook to the slide
IOleEmbeddedDataInfo dataInfo = new OleEmbeddedDataInfo(bout.toByteArray(), "xlsx");
IOleObjectFrame oof = sld.getShapes().addOleObjectFrame(1f, 1f,
(float) pres.getSlideSize().getSize().getWidth() - 2,
(float) pres.getSlideSize().getSize().getHeight() - 2, dataInfo);
oof.getSubstitutePictureFormat().getPicture()
.setImage(pres.getImages().addImage(imageByteArray));
// Write the presentation to disk
pres.save(dataDir+"Issue2.pptx", com.aspose.slides.SaveFormat.Pptx);
System.out.println("File Generated");
}
OLEIssue.zip (47.1 KB)
@jnachi16
After calling dataSheet.autoFitColumns(), the text width and column width are similar, which is not conducive to observation. By adding the following sample code to expand the column width, and then running the sample code you provided, we can observe that the alignment of text and numbers is correct. Please refer to the attachment (209.1 KB).
ColumnCollection columns = dataSheet.getCells().getColumns();
int colCount = columns.getCount();
for (int i = 0; i < colCount;i++)
{
Column col = columns.get(i);
col.setWidth(col.getWidth() + 20);
}
Hi @John.He /@amjad.sahi ,
Thanks for the solution.
But this approach won’t work for my requirement .
I might have any number of columns out of which I need to increase width for numeric columns only. Further 20 is static addition, which won’t suit all the use cases I’m having.
I need a generic solution for the addition of column width.
Based on numeric content length, perhaps I can decide with for that column but that would require iterating over all the rows impacting performance.
Kindly suggest some generic approach for handling this use case.
Thanks!
Thanks for your feedback.
We will check if we could find any better alternative. We will get back to you soon.
It seems that the EmfPlus records in the generated Emf image are not rendered well. Please use the following JVM argument to disable to generate EmfPlus records while generating Emf image.
-DAspose.Cells.Disable=EmfPlus
Here is the generated pptx file for your reference:
Issue2_withoutEmfPlus.zip (40.5 KB)