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

[elabnextdeveloper]

Sorry for the long delay. We investigated the issue further and it is slightly different than we thought.
The issue occurs when we use the `EnteredDirectlyValues`. The following code reproduces this:

Code: Select all

var workbook = new Workbook();
var worksheet = workbook.Worksheets[0];
var chart = worksheet.Charts.Add();
var serie = chart.Series.Add(ExcelChartType.Line);

chart.DisplayBlanksAs = ChartPlotEmptyType.NotPlotted;
serie.EnteredDirectlyValues = new object[] { 2000, "", 1000, 1000 };
serie.EnteredDirectlyCategoryLabels = new object[] { "one", "two", "three", "four" };

using (var image = worksheet.ToImage(0, 0, 30, 30))
{
    image.Save(@"C:\Temp\SpireIssue\result.png");
}

Console.WriteLine("DONE");



We expect the empty string to be not plotted instead of converted to 0.

[Amin.Gan]

Hello,

Thanks for your feedback. Please note that our product will parse empty strings ("") into the number 0, so even if 'chart.DisplayBlanksAs = ChartPlotEmptyType.NotPlotted' is set, it will not take effect. If the desired effect is to be achieved, the empty strings ("") should be replaced with 'null'. Additionally, I have recorded your requirement in our bug tracking system under the number SPIREXLS-5535. Our Dev team will investigate it further, once there is any update, we will let you know.

Sincerely,
Amin
E-iceblue support team

[elabnextdeveloper]

Ok, thanks.

I also tried null but then the graph starts after the null and doesn't show the values before the null.

Just to let you know: In the mean time we have a workaround for this issue so it doesn't have much priority at our side anymore.

Regards.

[Amin.Gan]

Hello,

Thanks for your feedback. I am glad that your issue has been resolved. In addition, our dev team has provided feedback that empty strings are also parsed as 0 in Microsoft Excel, so this issue will not be fixed as a bug. If you have any other questions, please feel free to write to me.

Sincerely,
Amin
E-iceblue support team