Creating sparklines

Aspose.Cells Product Family
1

Hi,

back for version 4.9 I got this snippet from your dev. team regarding sparklines for excel 2010.

Code is not working any more. Can I get a new snippet which does the same?

regards

Rale

Here is the code:

Workbook wb = new Workbook(FileFormatType.Excel2007Xlsx); // specify the dest file type

wb.Worksheets.Clear();

Worksheet ws = wb.Worksheets.Add("New");

ws.Cells["A8"].PutValue(34);

ws.Cells["A9"].PutValue(50);

ws.Cells["A10"].PutValue(34);

CellArea ca = new CellArea();

ca.StartColumn = 0;

ca.EndColumn = 0;

ca.StartRow = 0;

ca.EndRow = 0;

int idx = ws.SparklineGroupCollection.Add(Aspose.Cells.Charts.SparklineType.Column, ws.Name + "!A8:A10", true, ca);

Aspose.Cells.Charts.SparklineGroup group = ws.SparklineGroupCollection[idx];

// change the color of the series if need

CellsColor clr = wb.CreateCellsColor();

clr.Color = Color.Orange;

group.SeriesColor = clr;

wb.Save(@"Result.xlsx");

2

Hi Ranko,

We are able to reproduce the issue. The issue has been logged into our Issue Tracking System with ID CELLSNET-19619. We will update you as soon as possible.

Thanks,

3

Hi Ranko,

We have looked into the issue in detail and investigated. The code is working. Sparkline is the feature of Excel 2010. We created an Xlsx file and tried to open it in Excel 2010. It all worked fine.

Thanks,

4

Hi Ranko,

Please try to open the output file in Excel 2010. If still there is any issue, please forward us your output file. We will investigate it.

Thanks,

5

Hi Salman

yes, the sample works fine, but the problem is if the worksheet name contains a space char (ie. New Sheet). To reproduce, please use the attached sample.

Regards

Rale

ps. following code will throw exception with msg "Invalid formula:"=!A1:A1"." when adding a sparkline group

Workbook wb = new Workbook(FileFormatType.Xlsx); // specify the dest file type

wb.Worksheets.Clear();

Worksheet ws = wb.Worksheets.Add("New Sheet");

ws.Cells["A8"].PutValue(34);

ws.Cells["B8"].PutValue(50);

ws.Cells["C8"].PutValue(34);

CellArea ca = new CellArea();

ca.StartColumn = 9;

ca.EndColumn = 9;

ca.StartRow = 6;

ca.EndRow = 6;

int idx = ws.SparklineGroupCollection.Add(Aspose.Cells.Charts.SparklineType.Column, ws.Name + "!A8:C8", false, ca);

Aspose.Cells.Charts.SparklineGroup group = ws.SparklineGroupCollection[idx];

// change the color of the series if need

CellsColor clr = wb.CreateCellsColor();

clr.Color = Color.Orange;

group.SeriesColor = clr;

wb.Save(@"Result.xlsx");

6

Hi Salman

could you give me a short status on this issue?

regards

Rale

7

Hi Ranko,

We are working on this issue. We will update you as soon as possible. The issue has been logged into our Issue Tracking System with ID CELLSNET-19722.
Thank you for your endurance.

Thanks,

8

Hi Ranko,

Please use the updated version of Aspose.Cells.dll v5.1.2.5 attached. Please verify that if the sheet name contains some special char such as space char, it should have quotes around it when setting the sparkline data range. For example:

int idx = ws.SparklineGroupCollection.Add(Aspose.Cells.Charts.SparklineType.Column,
"New Name" + "!A8:A10", true, ca); // invalid

int idx = ws.SparklineGroupCollection.Add(Aspose.Cells.Charts.SparklineType.Column,
"'" + "New Name" + "'!A8:A10", true, ca); // valid

Furthermore, the following function may help you to check if the sheet name needs quotes:

public static bool NeedQuote(string str)

{

for (int i = 0; i < str.Length; ++i)

{

switch (str[i])

{

case '\\':

case '!':

case '<':

case '>':

case ' ':

case '=':

case '{':

case '}':

case '&':

case '%':

case '$':

case '#':

case '~':

case '\'':

case '+':

case '-':

case ',':

case '|':

case '(':

case ')':

return true;

}

}

return false;

}

Thanks,

9

Hi Salman

just tryied out your change and it seems to be working. But I have another problem in case there has been insterted rows after group has been created. see code below for more details

regards

Rale

The sparkline group is not visible in this case

Workbook wb = new Workbook(FileFormatType.Xlsx); // specify the dest file type

wb.Worksheets.Clear();

Worksheet ws = wb.Worksheets.Add("New Sheet");

ws.Cells["A8"].PutValue(34);

ws.Cells["B8"].PutValue(50);

ws.Cells["C8"].PutValue(34);

CellArea ca = new CellArea();

ca.StartColumn = 9;

ca.EndColumn = 9;

ca.StartRow = 6;

ca.EndRow = 6;

int idx = ws.SparklineGroupCollection.Add(Aspose.Cells.Charts.SparklineType.Column, "'" + ws.Name + "'!A8:C8", false, ca);

Aspose.Cells.Charts.SparklineGroup group = ws.SparklineGroupCollection[idx];

// change the color of the series if need

CellsColor clr = wb.CreateCellsColor();

clr.Color = Color.Orange;

group.SeriesColor = clr;

ws.Cells.InsertRows(2, 17);

wb.Save(@"Result.xlsx");

10

Hi Salman

since my subsricption has expired does this mean that I won't get any response on this issue?

Regards

Rale

11

Hi Rale,

Please accept my apologies on behalf of Salman Shakeel for the delay. Support developers of Aspose.Cells team were on holidays for few days which caused this delay but they are back to work from today so, please expect a reply today from our support team.

Moreover, our forum support is absolutely free which means whether your subscription has expired or not, our support team will always help you and answer your inquiries.


12

The issues you have found earlier (filed as 19722) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
13

Hi Ranko,

Please use the updated version Aspose.Cells.dllv5.1.3.0.zip attached. The issue has been fixed. If still there is any problem, feel free to reach us.

Direct Link:

http://www.aspose.com/community/files/51/.net-components/aspose.cells-for-.net/entry259000.aspx

We appreciate your endurance.

Thanks,

14

Hi Salman

I ve got the fix but unfortunatelly I am unable to use it since my license has expired 10 days ago. Does this mean that I have to renew yearly subscription to get the bug fix?

thanks & regards

Rale

15

Hi Ranko,

You have to renew your yearly subcription in order to use the latest version.

Before renewing you can get a temporary 30 days license which will allow you to use the latest version with out any restrictions for 30 days free of charge. You can use this temporary license for testing purpose and once you have confirmation that the problem is fixed then you can renew your subscription.

Thanks,