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");
}
}