Generate Excel with chart and convert to PDF without dataset direct from C#

GemBox.Spreadsheet
1

Hi everyone,

I created an excel with a chart and converted it into a pdf
is it possible to remove the dataset from the pdf?
I would like to see only the chart

chart

here below the code that I’m using:

worksheet.Rows[0].Style.Font.Weight = ExcelFont.BoldWeight;
worksheet.Rows[0].Style.HorizontalAlignment = HorizontalAlignmentStyle.Center;
worksheet.Columns[0].SetWidth(3, LengthUnit.Centimeter);

var chart = worksheet.Charts.Add<ColumnChart>(ChartGrouping.Clustered, "B7", "O27");
chart.SelectData(worksheet.Cells.GetSubrangeAbsolute(0, 0, 4, numberOfYears));

chart.Title.Text = "Column Chart";

chart.Legend.IsVisible = true;
chart.Legend.Position = ChartLegendPosition.Right;

chart.Axes.Horizontal.Title.Text = "Years";
chart.Axes.Vertical.Title.Text = "Salaries";

var valueAxis = chart.Axes.VerticalValue;
valueAxis.Minimum = 0;
valueAxis.Maximum = 6000;
valueAxis.MajorUnit = 1000;
valueAxis.MinorUnit = 500;
valueAxis.MajorGridlines.IsVisible = true;
valueAxis.MinorGridlines.IsVisible = true;
valueAxis.MajorTickMarkType = TickMarkType.Outside;
valueAxis.MinorTickMarkType = TickMarkType.Cross;
  
var printOptions = worksheet.PrintOptions;
printOptions.HorizontalCentered = true;
printOptions.PrintHeadings = true;
printOptions.PrintGridlines = true;
printOptions.FitWorksheetWidthToPages = 1;
printOptions.FitWorksheetHeightToPages = 1;

var saveOptions = new GemBox.Spreadsheet.PdfSaveOptions();
saveOptions.SelectionType = SelectionType.EntireFile;

workbook.Save(path, GemBox.Spreadsheet.PdfSaveOptions.PdfDefault);
2

You could move that data into another worksheet, like this:

var otherWorksheet = workbook.Worksheets.Add("ChartData");
otherWorksheet.Visibility = SheetVisibility.Hidden;
otherWorksheet.Cells["A1"].Value = "Name";
otherWorksheet.Cells["B1"].Value = "2019";
otherWorksheet.Cells["C1"].Value = "2020";
otherWorksheet.Cells["D1"].Value = "2021";
otherWorksheet.Cells["E1"].Value = "2022";

// ...

chart.SelectData(otherWorksheet.Cells.GetSubrangeAbsolute(0, 0, 4, numberOfYears));

Or you could create a chart without it referencing cells, like this:

var chart = worksheet.Charts.Add<ColumnChart>(ChartGrouping.Clustered, "B7", "O27");

chart.SetCategoryLabels("Name", "2019", "2020", "2021", "2022");
chart.Series.Add("John Doe", 3990, 3368, 4047, 3781);
chart.Series.Add("Fred Nurk", 3571, 4592, 1884, 3050);
chart.Series.Add("Hans Meier", 3342, 4082, 1921, 3973);
chart.Series.Add("Ivan Horvat", 2904, 2477, 4026, 2127);
chart.Title.Text = "Column Chart";

chart.Legend.IsVisible = true;
chart.Legend.Position = ChartLegendPosition.Right;

chart.Axes.Horizontal.Title.Text = "Years";
chart.Axes.Vertical.Title.Text = "Salaries";

var valueAxis = chart.Axes.VerticalValue;
valueAxis.NumberFormat = "\"$\"#,##0";
valueAxis.Minimum = 0;
valueAxis.Maximum = 6000;
valueAxis.MajorUnit = 1000;
valueAxis.MinorUnit = 500;
valueAxis.MajorGridlines.IsVisible = true;
valueAxis.MinorGridlines.IsVisible = true;
valueAxis.MajorTickMarkType = TickMarkType.Outside;
valueAxis.MinorTickMarkType = TickMarkType.Cross;

I hope this helps.

Regards,
Mario

3

Thank you for your answer and your help

This is what I get:

image
image851×423 24.4 KB

but what I would need is to remove all or hide the cells, it should only show the graph with nothing around.

Regards,
Yuza

4

I see now, in that case, you can specify the desired print area to be only the area where the chart is placed, like this:

var chart = worksheet.Charts.Add<ColumnChart>(ChartGrouping.Clustered, "B7", "O27");
worksheet.NamedRanges.SetPrintArea(worksheet.Cells.GetSubrange("B7:O27"));
// ...

Or you can save the chart itself as a PDF file, like this:

chart.Format().Save("output.pdf");
5

It works, thanks!

After conversion can I edit the PDF by adding text or images?

I tried so:

double x = 50, y = 100;
var image = PdfImage.Load("FragonardReader.jpg");
page.Content.DrawImage(image, new PdfPoint(x, y));

but I get these 2 errors:

The name ‘PdfImage’ does not exist in the current context.
‘PdfContentGroup’ does not contain definition for ‘DrawImage’ and no extension method ‘DrawImage’ accepting a first argument of type ‘PdfContentGroup’ could be not found.

these are my using:

using GemBox.Pdf;
using GemBox.Pdf.Content;
using GemBox.Spreadsheet;
using GemBox.Spreadsheet.Charts;

Thanks

6

I’m not 100% sure, but I believe your problem could be that you’re using some older version of GemBox.Pdf.

Can you please try again with the current latest version:
https://www.nuget.org/packages/GemBox.Pdf/

If the problem remains, please send us a small Visual Studio project that reproduces your issue so that we can investigate it.

7

Thanks for your help!

Yuza