Show empty cells as gaps in graphs

[elabnextdeveloper]

Hi,

We use Spire.XLS to generate screenshots of excel files and I want to make improvements to the screenshot process.
Is it possible to set the empty cells behaviour?

I have an excel file with data and a graph where some values are empty/0, I would like to not render the line to zero.

I've attached 1 zip with 3 files, expected_result.jpg, current_result.jpg and graph.xlsx.

Please let me know how I can set Spire.XLS to handle gaps.

Thank you in advance.

Best,

Erick

[Amin.Gan]

Hello,

Thanks for your inquiry.You can refer to the following code to change the display of empty cells.If you have any other questions, please feel free to write to me.

Code: Select all

  Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"graph.xlsx");
            Worksheet sheet = workbook.Worksheets[1];
            //Specify the first chart in the workbook
            Chart chart = sheet.Charts[0];
            //Set empty cells to display as empty spaces
            chart.DisplayBlanksAs = ChartPlotEmptyType.NotPlotted;
            workbook.SaveToFile(@"out.xlsx", ExcelVersion.Version2010);

Sincerely,
Amin
E-iceblue support team

[elabnextdeveloper]

Hi Amin,

Thanks for you reply. I've implemented your suggested solution but I'm not seeing any difference in the output unfortunately. Can you double check the file I've attached and confirm? Good thing to point out is that we render the excel file to an image with the sheet.ToImage method.

Best,

Erick

[Amin.Gan]

Hello,

Thank you for your feedback.I have reconfirmed your Excel document and conducted further testing, and the results still show that everything is normal. I have attached my test project and output files for your reference.You can try running it on your side, If the results of your test still have issues, please provide us with your testing environment(E.g. Win10, 64 bit, .NET 8, Spire.XLS for .NET : 14.8.2) for investigation. Thank you in advance.

Sincerely,
Amin
E-iceblue support team

[elabnextdeveloper]

Hi Amin,

Thanks for testing, unfortunately I still have the same issue. The specs of our setup are as following;
- Win10, 64 bit
- .NET 6.4.2,
- Spire.XLS for .NET : 14.8.2

With the following code:

Code: Select all

using (Workbook workbook = new Workbook())
            {
                workbook.LoadFromFile(filePath);
                using (Worksheet sheet = workbook.Worksheets[workbook.ActiveSheetIndex])
                {
                    sheet.UseRangesCache = false;
                    sheet.PageSetup.IsPrintGridlines = true;
                    sheet.PageSetup.BottomMargin = 0;
                    sheet.PageSetup.LeftMargin = 0;
                    sheet.PageSetup.RightMargin = 0;
                    sheet.PageSetup.TopMargin = 0;

                    Chart chart = sheet.Charts[0];
                    chart.DisplayBlanksAs = ChartPlotEmptyType.NotPlotted;
                   
                    using (var image = sheet.ToImage(1, 1, 30, 15)) {
                        image.Save(stream, ImageFormat.Png);
                    }
                }
            }


[Amin.Gan]

Hello,

Thank you for your feedback. I simulated your testing environment and used the code you provided, and the results showed that everything was normal. Please let us know the results of the test project I provided yesterday that you directly ran on your end? If only your project has issues, please provide us with your testing project for investigation. You can upload here or send it to us via email（ support@e-iceblue.com ). Thank you in advance.

Sincerely,
Amin
E-iceblue support team