Problems with Charts and Hyperlinks

Hi,

I am evaluating your Aspose.Excel. I have a Excel File and I'm trying to modify the value ranges for the charts and save it with a new name. I'm not able to modify the embedded chart in the sheet "Data". I have also problems to modify the chart on sheet "Chart_1". In the source excel file I have included a background on sheet "Data", but it will not appear in the saved file.

I've attached the Excel File, the questions are in the code below:

Excel my_excel = new Excel();

my_excel.Open("Z:\\PCSE07\\Aspose\\test_006.xls");

// ****************************************************** //
// Insert Values in Cells B5 to E5 //
// Works fine! //
// ****************************************************** //

Worksheet my_sheet = my_excel.Worksheets["Data"];
my_sheet.Cells["B5"].PutValue(32456);
my_sheet.Cells["C5"].PutValue(21637);
my_sheet.Cells["D5"].PutValue(1356);
my_sheet.Cells["E5"].PutValue(322);

// Cell F5 may be Zero and is not required!
// Delete Value F5 ==> Clear Cell F4
// Clear Cell F5
// Set new Border

my_sheet.Cells.ClearContents(3,5,4,5);

my_sheet.Cells["F4"].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.None;
my_sheet.Cells["F4"].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.None;
my_sheet.Cells["F4"].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.None;

my_sheet.Cells["F5"].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.None;
my_sheet.Cells["F5"].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.None;
my_sheet.Cells["F5"].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.None;

my_sheet.Cells["E4"].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
my_sheet.Cells["E5"].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;

// ***************************************************** //
// Delete value "F5" from sheet "Chart_1"
// ***************************************************** //
my_sheet = my_excel.Worksheets["Chart_1"];
Chart my_chart = my_sheet.Charts[0];

int my_count_NSeries = my_chart.NSeries.Count;

for (int i = 1; i <= my_count_NSeries; i++)
{
if (my_chart.NSeries[i-1].Values == "Data!F5")
{
my_chart.NSeries.RemoveAt(i-1);
}
}
// **************************//
// To prove the "Nseries.RemoveAt"
// **************************//

my_count_NSeries = my_chart.NSeries.Count;

for (int i = 1; i <= my_count_NSeries; i++)
{
Console.WriteLine("{0}", my_chart.NSeries[i-1].Values);
// Display: Data!B5
// Data!C5
// Data!D5
// Data!E5
//
//
// ==> F5 should be deleted, but is still shown in the new excel file!
// Why?
}

// ****************************************************** //
// Delete value "F5" from chart 1 on sheet "Data"
// ****************************************************** //
my_sheet = my_excel.Worksheets["Data"];

// Count the charts

int my_count_charts = my_sheet.DesignCharts.Count;
Console.WriteLine("No. of DesignCharts = {0}", my_count_charts);
// Display: 2

my_chart = my_sheet.DesignCharts[0];

// my_chart.NSeries.Count returns Zero?
my_count_NSeries = my_chart.NSeries.Count;
Console.WriteLine("No. of NSeries = {0}", my_count_NSeries);
// Display: 0
// Why?

for (int c = 1; c <= my_count_NSeries; c++)
{
Console.WriteLine("never reached");
}

// ***************************************************** //
// Insert Values in Cells B28 to F28
// ***************************************************** //

my_sheet = my_excel.Worksheets["Data"];
my_sheet.Cells["B28"].PutValue(5221);
my_sheet.Cells["C28"].PutValue(3211);
my_sheet.Cells["D28"].PutValue(2110);
my_sheet.Cells["E28"].PutValue(1229);
my_sheet.Cells["F28"].PutValue(4872);

// Need another Cell
my_sheet.Cells["G27"].PutValue("Value 6");
my_sheet.Cells["G28"].PutValue(2879);

my_sheet.Cells["F27"].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.None;
my_sheet.Cells["F28"].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.None;

my_sheet.Cells["G27"].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
my_sheet.Cells["G27"].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thick;
my_sheet.Cells["G27"].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
my_sheet.Cells["G27"].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

my_sheet.Cells["G28"].Style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
my_sheet.Cells["G28"].Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
my_sheet.Cells["G28"].Style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thick;
my_sheet.Cells["G28"].Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thick;

// *************************************************** //
// Add value "G28" to sheet "Chart_2"
// ************************************************** //

my_sheet = my_excel.Worksheets["Chart_2"];
my_chart = my_sheet.Charts[0];

my_chart.NSeries[0].Values = "Data!B28:G28";

// That is OK!

// ************************************************* //
// Delete value "F5" from sheet "Chart_3"
// ************************************************** //

my_sheet = my_excel.Worksheets["Chart_3"];
my_chart = my_sheet.Charts[0];

my_chart.NSeries[0].Values = "Data!B5:E5";

// That's also OK!

// ************************************************* //
// Hypelinks
// ************************************************ //

my_sheet = my_excel.Worksheets["Hyperlinks"];

int my_count_hyperlinks = my_sheet.Hyperlinks.Count;

Console.WriteLine("Hyperlinks = {0}", my_count_hyperlinks);
// Display: 0
// Why?


// Save to new filename

my_excel.Save("Z:\\PCSE07\\Aspose\\test_006_new.xls");
}
}

Worksheet.DesignChart is used to change setting in the template. Only part of the properties can be changed, such as source data, title, etc.

And you only can use Chart.NSeries to set new data. It’s not used to access setting in the design chart.

Hyperlinks collection is also used to set hyperlinks at run time. It doesn’t return hyperlinks set at the designer file.

Hi Laurence,

thank you for your quick answer. I think, I have to seperate my problems into smaller steps.

First I’ll try to explain the requirement for charts.

A user creates a Excel file with a predefined chart and a table with one column of data for the chart. I need a program that opens the Excel file and, dependent on a separate data file, adds more columns of data. The columns can vary from 1 to over a hundred. Then I have to modify the predefined chart from the user to fit for the new range of data.

How can I do that?


Andreas

You can use Worksheet.DesignerCharts to get the chart in your designer file, and use Chart.NSeries.Add to change the data source.


Chart chart = excel.Worksheet["Data"].DesignerCharts[0];
chart.NSeries.Add("B5:E5");

Hi Laurence,

thanks for your hints. Now I'm able to modify existing charts. The main problem I got was

chart.NSeries.Add("B5", true);

produced an error "Invalid Series Data".

I have to add the sheet name in the NSeries

chart.NSeries.Add("Sheet1!B5", true);

This one works fine.


Now I have an other problem. I can't modify the name of a NSeries. I create a new excel file:

Excel excel = new Excel();
Worksheet sheet = excel.Worksheets[0];
sheet.Name = "Sheet1";

Cells cells = sheet.Cells;

// Values for the Chart
cells["B3"].PutValue("File 1");
cells["C3"].PutValue(10);
cells["B4"].PutValue("File 2");
cells["C4"].PutValue(20);

// Create new chart
Chart chart = sheet.Charts[sheet.Charts.Add(ChartType.Column3DClustered,15,1,38,10)];

// set NSeries
chart.NSeries.Add("C3", true);
chart.NSeries.Add("C4", true);

// set name for NSeries (shown in the legend of the chart)
chart.NSeries[0].Name = "=B3";
chart.NSeries[1].Name = "=B4";

excel.Save("Test_010.xls");

That works fine. Next I open the saved file and try to modify the chart:

Excel file_excel = new Excel();
file_excel.Open("Test_010.xls");

Worksheet file_sheet = file_excel.Worksheets["Sheet1"];

Cells file_cells = file_sheet.Cells;

// Add new values
file_cells["B5"].PutValue("File 3");
file_cells["C5"].PutValue(30);
file_cells["B6"].PutValue("File 4");
file_cells["C6"].PutValue(40);

Chart file_chart = file_sheet.DesignCharts[0];

// set NSeries to new range
file_chart.NSeries.Add("Sheet1!C3", true);
file_chart.NSeries.Add("Sheet1!C4", true);
file_chart.NSeries.Add("Sheet1!C5", true);
file_chart.NSeries.Add("Sheet1!C6", true);

// set new NSeries names
file_chart.NSeries[0].Name = "=B3";
file_chart.NSeries[1].Name = "=B4";
file_chart.NSeries[2].Name = "=B5";
file_chart.NSeries[3].Name = "=B6";

// save the modified file with new name
file_excel.Save("Test_010_new.xls");


The new data (C4 and C5) are shown in the chart, but the names aren't set proper. What's wrong on this one?


Andreas

Hi Andreas,

Currently series name in designer chart cannot be changed at run time. I will implement this feature in the future release.

Hi Laurence,

could you please tell me a rough date for the next release?


Andreas

Hi Andreas,

It will be available before the end of this month.