We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

DataLabels issue while exporting xlsx in PDF

Hello,


Yuu will find in attachment the same file generated once with the xlslx option and the other with de PDF option. As you can see, the data labels in the pdf overlap the top of the chart.

Any solution to this issue ?

Thanks,

Guillaume


By the way, I am using Aspose.Cell for .Net 7.5.0

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.2.2:
http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry579884.aspx I have tested your scenario using it, it works fine.


Thank you.

Yes, I’ve tryed but with no success.


You will find attached the code I’m using to generate the file.

Also I’ve joined another pdf file in which I uused this technique to move the data labels :

c.Calculate();
ChartPointCollection chartPoints = c.NSeries[0].Points;
//////Moves the labels
chartPoints[0].DataLabels.Y += 5;

But I have an akward result.

Thanks for your help

Hi Guillaume,


We are sorry to know that the latest release didn’t make any difference.

We have evaluated the case by exporting the provided spreadsheet “report.xlsx” to PDF format using the below provided code snippet. Attached is the resultant PDF file for your reference. You will notice that the data labels are placed correctly without overlapping as shown in your provided PDF file.

C#

Workbook book = new Workbook(filePath);
PdfSaveOptions options = new PdfSaveOptions();
book.Save(“output.pdf”, options);

Unfortunately, we were unable to execute your code because it has a dependency on a DataTable that you are importing in your code. We would request you to please share a standalone sample application by dynamically creating the DataTable in the application it self. This will expedite the investigation process, and we can quickly move towards the correction.

Thank you for your understanding & cooperation.

Thanks for your answer. The data are on the left in the xlsx file furnished. You can probably import them very easily in a DataTable, can’t you ?



Hi again,


Thank you for the pointer.

I have already tried as follow but the results are correct in this case as well. Please check the attached resultant PDF. Please execute the code on your end with Aspose.Cells for .NET 8.2.2 and share the resultant PDF file here.

C#

var source = new Workbook(“D:/temp/report.xlsx”);
var sheet = source.Worksheets[“Sheet1”];
DataTable table = sheet.Cells.ExportDataTable(3, 0, 64, 2);

Workbook workbook = new Workbook();
workbook.Worksheets.Add();
var ws = workbook.Worksheets[0];

ws.PageSetup.Orientation = PageOrientationType.Landscape;
// ws.PageSetup.Zoom = 50;
ws.PageSetup.FitToPagesTall = 1;
ws.PageSetup.FitToPagesWide = 1;
ws.PageSetup.PaperSize = PaperSizeType.PaperLegal;
ws.PageSetup.BottomMargin = 0.64;
ws.PageSetup.TopMargin = 0.64;
ws.PageSetup.LeftMargin = 0.64;
ws.PageSetup.RightMargin = 0.64;

Aspose.Cells.Style style_Main_Title = ws.Cells[“A1”].GetStyle();
Aspose.Cells.Style style_Grid_Title = ws.Cells[“A1”].GetStyle();
Aspose.Cells.Style style_Grid_Title_2 = ws.Cells[“A1”].GetStyle();
Aspose.Cells.Style style_Grid_Content = ws.Cells[“A1”].GetStyle();
Aspose.Cells.Style style_Grid_Total = ws.Cells[“A1”].GetStyle();
style_Grid_Content.Font.IsBold = false;
style_Grid_Content.Font.Size = 12;
ws.Cells[“A3”].SetStyle(style_Grid_Content);
ws.Cells[“B3”].SetStyle(style_Grid_Content);

ws.Cells.ImportDataTable(table, true, “A3”);

ws.Cells.Merge(0, 0, 1, 8);
Color blue = Color.FromArgb(79, 129, 189);
style_Main_Title.Font.Name = “Arial”;
style_Main_Title.Font.Size = 20;
style_Main_Title.Font.Color = blue;
style_Main_Title.Font.IsBold = true;
style_Grid_Title.Font.IsBold = true;
style_Grid_Title.Font.Size = 12;
style_Grid_Title_2.HorizontalAlignment = TextAlignmentType.Right;
style_Grid_Title_2.Font.IsBold = true;
style_Grid_Title_2.Font.Size = 12;
style_Grid_Total.Font.Name = “Arial”;
style_Grid_Total.Font.Size = 18;
style_Grid_Total.Font.Color = blue;
style_Grid_Total.Font.IsBold = true;
style_Grid_Total.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
style_Grid_Total.Borders[BorderType.TopBorder].Color = Color.Black;

ws.Cells[“A1”].SetStyle(style_Main_Title);
ws.Cells[“A3”].SetStyle(style_Grid_Title);
ws.Cells[“B3”].SetStyle(style_Grid_Title_2);
ws.Cells[“A4”].SetStyle(style_Grid_Content);
ws.Cells[“A4”].SetStyle(style_Grid_Content);
ws.Cells[“A68”].SetStyle(style_Grid_Total);
ws.Cells[“B68”].SetStyle(style_Grid_Total);
ws.Cells[“A68”].PutValue(“Total”);
ws.Cells[“B68”].Formula = “=SUM(B4:B67)”;

int chartIndex = ws.Charts.Add(ChartType.Cylinder, 2, 3, 67, 24);
Chart c = ws.Charts[chartIndex];
c.ChartObject.Width = 1800;
c.ChartObject.Height = 1150;
c.NSeries.Add(“B4:B67”, true);
c.NSeries.CategoryData = “A4:A67”;
c.CategoryAxis.TickLabelSpacing = 1;
c.CategoryAxis.TickLabels.AutoScaleFont = true;
c.CategoryAxis.TickLabels.RotationAngle = 90;
c.CategoryAxis.TickLabels.Font.Size = 8;
c.CategoryAxis.TickLabels.Font.Name = “Candara”;
c.CategoryAxis.MinorUnit = 2.0;

DataLabels lbls = c.NSeries[0].DataLabels;
lbls.Position = LabelPositionType.Above;
lbls.ShowValue = true;

c.PlotArea.Area.BackgroundColor = Color.White;
c.ChartArea.Area.BackgroundColor = Color.White;
c.PlotArea.Area.ForegroundColor = Color.White;
c.ChartArea.Area.ForegroundColor = Color.White;
c.Walls.ForegroundColor = Color.White;
c.Walls.BackgroundColor = Color.White;
c.Walls.Transparency = 1.0;
c.ChartDataTable.BackgroundMode = BackgroundMode.Automatic;
c.Floor.BackgroundColor = Color.White;
c.NSeries[0].LegendEntry.IsDeleted = true;
c.NSeries[0].Area.ForegroundColor = Color.FromArgb(79, 129, 189);
c.NSeries[0].Area.BackgroundColor = Color.White;
c.NSeries[0].GapWidth = 150;
c.ValueAxis.MajorGridLines.IsVisible = true;
c.ValueAxis.MajorGridLines.Color = Color.Gray;
c.CategoryAxis.MinorGridLines.IsVisible = true;
c.CategoryAxis.MinorGridLines.Color = Color.Gray;
c.ValueAxis.IsAutomaticMaxValue = true;
c.ValueAxis.IsAutomaticMinValue = true;
c.ValueAxis.MinValue = 0;

ws.Cells[“A1”].PutValue(String.Format(“Number of active employees with a SCV - {0}”, DateTime.Now.ToString(“dd”) + “/” +
ws.Cells[“A3”].PutValue(“Processing Center”);

String title = “Numbers of active employees with a SCV by Processing Center - {0}”;
string date = DateTime.Now.ToString(“Y”);
string formatedDate = char.ToUpper(date[0]) + date.Substring(1);

c.Title.Text = String.Format(title, formatedDate);
c.Title.Font.Size = 18;

workbook.Save(“D:/temp/output.xlsx”, SaveFormat.Xlsx);
workbook.Save(“D:/temp/output.pdf”, SaveFormat.Pdf);

Yes it works ! Thank you !


Can please point what’s the difference between your code and mine ? It could be useful for later !

Thanks !

Hi,


It’s good to know that you are up and running again.

There are two differences as listed below.

  • I have commented out the statement to set the CultureInfo for the CurrentThread. We would suggest you to use the WorkbookSettings.CultureInfo property to set the same. I have tested on my end that adding statement workbook.Settings.CultureInfo = new CultureInfo(“en-us”) to the above code produces the correct results.
  • Unlike you original application where you could be importing the data directly from a database, I am importing it from a spreadsheet so there could be change in data representation/format thus triggering the aforesaid problem.

You can evaluate both above stated cases individually to isolate the problem cause on your end. Please feel free to contact us in case you have further questions or concerns.