we want to be able to have a sheetname containing the |-character and still reference to this sheet in a chart on another sheet.
In our Live-Code we actually had an Exception at the code-line currSeries.XValues = "Technisch27|ewttww|<ycxxyv|de7b!$D$7:$EU$7";
The Exception-message was:
Aspose.Cells.CellsException: ‘Invalid text for the defined name: | (Based on Chart at Technisch27|ewttww|<ycxxyv|de7b!A1)’
Surprisingly in the following example no exception is thrown, but the chartValues are not set correctly.
For example the sheet Test|Test contains the Data and with Test|Test!$A$1:$D$1 we want to reference to this sheet from another sheet.
Here would be an example-Code which illustrates the problem:
static void Main(string[] args)
{
// -> File1 will show the chart correctly
// -> File2 will not show the chart correctly
License license = new License();
license.SetLicense(new MemoryStream(Resource1.Aspose_Total_NET)); //TODO Set me to your license
string bookFilename1 = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "File1.xlsx"); //TODO Set me as you want
string bookFilename2 = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "File2.xlsx"); //TODO Set me as you want
writeBookToFilename(bookFilename1, "TestTest");
writeBookToFilename(bookFilename2, "Test|Test");
}
private static void writeBookToFilename(string fileName, string dataSheetName)
{
Workbook excelBook = new Workbook();
Worksheet dataSheet = excelBook.Worksheets.Add(dataSheetName);
Worksheet chartSheet = excelBook.Worksheets.Add("ChartSheet");
dataSheet.Cells["A1"].Value = "Val1";
dataSheet.Cells["B1"].Value = "Val2";
dataSheet.Cells["C1"].Value = "Val3";
dataSheet.Cells["D1"].Value = "Val4";
dataSheet.Cells["A2"].Value = 1;
dataSheet.Cells["B2"].Value = 2;
dataSheet.Cells["C2"].Value = 3;
dataSheet.Cells["D2"].Value = 4;
int chartIndex = chartSheet.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 10, 10, 20, 20);
Chart excelChart = chartSheet.Charts[chartIndex];
excelChart.Name = "Test";
SeriesCollection seriesColl = excelChart.NSeries;
string xValues = string.Concat(dataSheetName, "!$A$1:$D$1");
string yValues = string.Concat(dataSheetName, "!$A$2:$D$2");
int seriesIndex = seriesColl.Add(yValues, isVertical: false);
Series currSeries = excelChart.NSeries[seriesIndex];
currSeries.XValues = xValues;
currSeries.Values = yValues;
currSeries.Name = "SeriesXYZ";
excelBook.Save(fileName, SaveFormat.Xlsx);
}
The files, which are produced by this code are attached. (Additionally there is an example, that is created directly in Excel, which works also if the Sheetname is Test|Test ExcelFiles.zip (29.3 KB)
Since your worksheet name has special character in it, so you need to enclose the worksheet name in single quotes when adding chart’s data series. MS Excel does this automatically when a worksheet has space or other special chars but when using Aspose.Cells in code, you may simply do that in code. Please change the following lines of code:
After changing the code as you said I get a System.ArgumentException: ‘Illegal Character in the path’. IllegalCharacter.PNG (48.4 KB)
Because of this exception we had the reference to the sheet without the single quotationmarks in the first place.
Do you have some further advice for this issue?
It is strange as it works fine on my end after updating those two lines. Here is my complete (runnable) sample code for your reference:
e.g. Sample code:
Please find attached the output file for your reference. out1.zip (8.8 KB)
Could you please create a standalone console application with latest version of Aspose.Cells for .NET, zip the project and post us, we will check it soon.
Thank you for your answer. Your example works for me.
I realized my mistake:
string dataSheetName = “‘Test|Test’”; //note the single quotationmarks here
Worksheet dataSheet = excelBook.Worksheets.Add(dataSheetName); //-> mistake here; the sheetname should not have single quotationmarks here
…
xValues = string.concat(dataSheetName, “!$A$1:$D$1”);
The adjusted version of the code above would be:
string dataSheetName = “Test|Test”; //no(!) single quotationmarks here
Worksheet dataSheet = excelBook.Worksheets.Add(dataSheetName);
…
xValues = string.concat("’", dataSheetName, “’”, “!$A$1:$D$1”); //added the single quotationmarks here
I will test the adjustment in our live code and hope this solves the issue.
Thank you for your nice support
