Getting error when saving the excel

Aspose.Cells Product Family
1

Hi,

We are using Aspose.Cells to create some charts. We are getting following error when using the Save method:

workbook.Save(excelFileName, SaveFormat.Xlsx);



Error:
System.Xml.XmlException: ‘’, hexadecimal value 0x02, is an invalid character. Line 1, position 2.

at Aspose.Cells.Workbook.Save(String fileName, SaveOptions saveOptions)

at Aspose.Cells.Workbook.Save(String fileName, SaveFormat saveFormat)
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version:
Aspose.Cells for .NET (Latest Version) .It should fix this error.

If problem still occurs, then we will need your source xls/xlsx file(s) and the sample test code replicating this issue with the latest version.

We will look into it and help you asap.

3

Sample code:


Code:<o:p></o:p>

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Aspose.Cells;

using Aspose.Cells.Charts;

using Aspose.Cells.Drawing;

using System.Drawing;

namespace AsposeSampleProject

{

class Program

{

static void Main(string[] args)

{

Workbook wb = new Workbook(@"C:\Working\1\Book1.xlsx");

Worksheet wrktest = wb.Worksheets["__ifnet"];

//Adding a new worksheet to the Workbook object

int sheetIndex = wb.Worksheets.Add(SheetType.Chart);

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = wb.Worksheets[sheetIndex];

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 1, 1, 25, 30);

//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];

Cells cell = wrktest.Cells;

//Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B4"

chart.NSeries.Add("__ifnet!B2:I10", true);

chart.NSeries.CategoryData = "__ifnet!A2:A10";

chart.NSeries.SecondCatergoryData = "__ifnet!A2:A10";

////Setting the chart type of 2nd NSeries to display as line chart

int i = 0;

for (i = 0; i < 7; i++)

{

string index = ((char)(66 + i)).ToString() + "1";

chart.NSeries[i].Name = cell[index].Value.ToString();

chart.NSeries[i].Type = Aspose.Cells.Charts.ChartType.Line;

chart.NSeries[i].Line.Weight = WeightType.HairLine;

DataLabels datalabels = chart.NSeries[i].DataLabels;

datalabels.ShowValue = false;

}

string cellindex = ((char)(66 + i)).ToString() + "1";

chart.NSeries[i].Name = cell[cellindex].Value.ToString();

chart.NSeries[i].Type = Aspose.Cells.Charts.ChartType.Line;

chart.NSeries[i].PlotOnSecondAxis = true;

chart.NSeries[i].Line.IsVisible = true;

chart.SecondCategoryAxis.IsVisible = true;

Console.WriteLine("Applying Formatting ...");

//Formatting chart area

chart.PlotArea.Area.BackgroundColor = Color.White;

chart.ChartArea.Area.BackgroundColor = Color.White;

chart.PlotArea.Area.ForegroundColor = Color.White;

chart.ChartArea.Area.ForegroundColor = Color.White;

chart.Name = "Sample Sheet";

chart.Title.Text = "Sample Chart";

chart.SizeWithWindow = true;

chart.ValueAxis.IsAutomaticMajorUnit = true;

chart.SecondValueAxis.IsAutomaticMajorUnit = true;

chart.SecondValueAxis.IsVisible = true;

chart.ValueAxis.IsAutomaticMaxValue = true;

chart.SecondValueAxis.IsAutomaticMaxValue = true;

chart.Calculate();

wb.Save(@"C:\Working\1\abc.xlsx");

}

}


Chart not getting generated, though excel is saved. Attached sample excel

4

Hi,


Thanks for providing us the template file and sharing the sample code.

I can find MS Excel displays error message: “Excel found unreadable…” when opening the saved file (that contains a chart by Aspose.Cells APIs) into MS Excel 2007. The chart is also not rendered at all.

I have logged a ticket with an id: CELLSNET-41118 for your issue. We will look into it soon to figure it out.

Thank you.
5

Hi,

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

6

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
7

The issue still seems to exists while using the DLL mentioned.


Regards
Arihant
8

Hi,

I have tested your issue using our latest version/fix:Aspose.Cells for .NET (Latest Version) and it works fine. I used the following code and my output file is opened fine.

Sample code:

Workbook wb = new Workbook(@“e:\test2\Book1.xlsx”);

Worksheet wrktest = wb.Worksheets["__ifnet"];

//Adding a new worksheet to the Workbook object

int sheetIndex = wb.Worksheets.Add(SheetType.Chart);

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = wb.Worksheets[sheetIndex];

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 1, 1, 25, 30);

//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];

Cells cell = wrktest.Cells;

//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B4”

chart.NSeries.Add("__ifnet!B2:I10", true);

chart.NSeries.CategoryData = “__ifnet!A2:A10”;

chart.NSeries.SecondCatergoryData = “__ifnet!A2:A10”;

////Setting the chart type of 2nd NSeries to display as line chart

int i = 0;

for (i = 0; i < 7; i++)

{

string index = ((char)(66 + i)).ToString() + “1”;

chart.NSeries[i].Name = cell[index].Value.ToString();

//chart.NSeries[i].Name = cell[0,i+1].Value.ToString();

chart.NSeries[i].Type = Aspose.Cells.Charts.ChartType.Line;

chart.NSeries[i].Line.Weight = WeightType.HairLine;

DataLabels datalabels = chart.NSeries[i].DataLabels;

datalabels.ShowValue = false;

}

string cellindex = ((char)(66 + i)).ToString() + “1”;

chart.NSeries[i].Name = cell[cellindex].Value.ToString();

//chart.NSeries[i].Name = cell[0, i + 1].Value.ToString();

chart.NSeries[i].Type = Aspose.Cells.Charts.ChartType.Line;

chart.NSeries[i].PlotOnSecondAxis = true;

chart.NSeries[i].Line.IsVisible = true;

chart.SecondCategoryAxis.IsVisible = true;

Console.WriteLine(“Applying Formatting …”);

//Formatting chart area 

chart.PlotArea.Area.BackgroundColor = Color.White;

chart.ChartArea.Area.BackgroundColor = Color.White;

chart.PlotArea.Area.ForegroundColor = Color.White;

chart.ChartArea.Area.ForegroundColor = Color.White;

chart.Name = “Sample Sheet”;

chart.Title.Text = “Sample Chart”;

chart.SizeWithWindow = true;

chart.ValueAxis.IsAutomaticMajorUnit = true;

chart.SecondValueAxis.IsAutomaticMajorUnit = true;

chart.SecondValueAxis.IsVisible = true;

chart.ValueAxis.IsAutomaticMaxValue = true;

chart.SecondValueAxis.IsAutomaticMaxValue = true;

chart.Calculate();

wb.Save(@“e:\test2\myabc.xlsx”);
9

I tried the below code with latest DLL, though the excel is getting saved succeesfully, the chart is not getting genearted. Getting attached error:


using System;

using System.Collections.Generic;

using System.Drawing;

using System.Linq;

using System.Text;

using Aspose.Cells;

using Aspose.Cells.Charts;

using Aspose.Cells.Drawing;

namespace PerfomanceIssue {

class Program {

static void Main(string[] args) {

Workbook test = new Workbook(@“C:\bin\testbook1.xlsx”);

Worksheet wrktest = test.Worksheets["__testsheet"];


        <span style="color:green;">//Adding a new worksheet to the Workbook object</span>
        <span style="color:blue;">int</span> sheetIndex = test.Worksheets.Add(<span style="color:#2b91af;">SheetType</span>.Chart);

        <span style="color:green;">//Obtaining the reference of the newly added worksheet by passing its sheet index</span>
        <span style="color:#2b91af;">Worksheet</span> worksheet = test.Worksheets[sheetIndex];

        <span style="color:blue;">int</span> chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.<span style="color:#2b91af;">ChartType</span>.Line, 1, 1, 25, 30);

        <span style="color:green;">//Accessing the instance of the newly added chart</span>
        Aspose.Cells.Charts.<span style="color:#2b91af;">Chart</span> chart = worksheet.Charts[chartIndex];

        <span style="color:green;">//Adding NSeries (chart data source) to the chart ranging from "A1" cell to "B4"</span>
        chart.NSeries.Add(<span style="color:#a31515;">"__testsheet!B2:EG168"</span>, <span style="color:blue;">true</span>);

        chart.NSeries.CategoryData = <span style="color:#a31515;">"__testsheet!A1:A168"</span>;
        chart.NSeries.SecondCatergoryData = <span style="color:#a31515;">"__testsheet!A1:A168"</span>;

        <span style="color:green;">////Setting the chart type of 2nd NSeries to display as line chart</span>
        

        <span style="color:blue;">for</span> (<span style="color:blue;">int</span> i = 0; i < 50; i++) {
            chart.NSeries[i].Type = Aspose.Cells.Charts.<span style="color:#2b91af;">ChartType</span>.LineStacked;
            chart.NSeries[i].Line.Weight = <span style="color:#2b91af;">WeightType</span>.HairLine;
            chart.NSeries[i].Name = <span style="color:#a31515;">"®Ù"</span>;
            <span style="color:#2b91af;">DataLabels</span> datalabels = chart.NSeries[i].DataLabels;
            <span style="color:green;">//datalabels.Text = "®Ù";</span>
            datalabels.ShowValue = <span style="color:blue;">false</span>;
        }

        chart.NSeries[10].Name = <span style="color:#a31515;">"Area Two"</span>;
        chart.NSeries[10].Type = Aspose.Cells.Charts.<span style="color:#2b91af;">ChartType</span>.Line;
        chart.NSeries[10].PlotOnSecondAxis = <span style="color:blue;">true</span>;
        chart.NSeries[10].Line.IsVisible = <span style="color:blue;">true</span>;
        chart.SecondCategoryAxis.IsVisible = <span style="color:blue;">true</span>;

        <span style="color:#2b91af;">Console</span>.WriteLine(<span style="color:#a31515;">"Applying Formatting ..."</span>);
        <span style="color:green;">//Formatting chart area                    </span>

        chart.PlotArea.Area.BackgroundColor = <span style="color:#2b91af;">Color</span>.White;
        chart.ChartArea.Area.BackgroundColor = <span style="color:#2b91af;">Color</span>.White;
        chart.PlotArea.Area.ForegroundColor = <span style="color:#2b91af;">Color</span>.White;
        chart.ChartArea.Area.ForegroundColor = <span style="color:#2b91af;">Color</span>.White;
        chart.Name = <span style="color:#a31515;">"Sample Sheet"</span>;
        chart.Title.Text = <span style="color:#a31515;">"Sample Chart"</span>;
        chart.SizeWithWindow = <span style="color:blue;">true</span>;
        chart.ValueAxis.IsAutomaticMajorUnit = <span style="color:blue;">true</span>;
        chart.SecondValueAxis.IsAutomatic</pre><pre style="font-family: Consolas; font-size: 13px; background-color: white; background-position: initial initial; background-repeat: initial initial;">MajorUnit = <span style="color:blue;">true</span>;
        chart.SecondValueAxis.IsVisible = <span style="color:blue;">true</span>;
        chart.ValueAxis.IsAutomaticMaxValue = <span style="color:blue;">true</span>;
        chart.SecondValueAxis.IsAutomaticMaxValue = <span style="color:blue;">true</span>;


        chart.Calculate();

        <span style="color:green;">////Saving the Excel file</span>
        test.Save(<span style="color:#a31515;">@"c:\bin\test2.xlsx"</span>, <span style="color:#2b91af;">SaveFormat</span>.Xlsx);
    }
}



}



Please check again…
10

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

I tried to run your code but I could not check this issue properly because you have not attached the source file (testbook1.xlsx) used in your code.

I am afraid, the Book1.xlsx does not work with your code. Kindly attach your source file to look into this issue further

C#

string filePath = @“F:\Shak-Data-RW\Downloads\Book1.xlsx”;


Workbook test = new Workbook(filePath);

Worksheet wrktest = test.Worksheets["__testsheet"];


//Adding a new worksheet to the Workbook object

int sheetIndex = test.Worksheets.Add(SheetType.Chart);


//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = test.Worksheets[sheetIndex];


int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 1, 1, 25, 30);


//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];


//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B4”

chart.NSeries.Add("__testsheet!B2:EG168", true);


chart.NSeries.CategoryData = “__testsheet!A1:A168”;

chart.NSeries.SecondCatergoryData = “__testsheet!A1:A168”;


////Setting the chart type of 2nd NSeries to display as line chart



for (int i = 0; i < 50; i++) {

chart.NSeries[i].Type = Aspose.Cells.Charts.ChartType.LineStacked;

chart.NSeries[i].Line.Weight = WeightType.HairLine;

chart.NSeries[i].Name = “®Ù”;

DataLabels datalabels = chart.NSeries[i].DataLabels;

//datalabels.Text = “®Ù”;

datalabels.ShowValue = false;

}


chart.NSeries[10].Name = “Area Two”;

chart.NSeries[10].Type = Aspose.Cells.Charts.ChartType.Line;

chart.NSeries[10].PlotOnSecondAxis = true;

chart.NSeries[10].Line.IsVisible = true;

chart.SecondCategoryAxis.IsVisible = true;


Console.WriteLine(“Applying Formatting …”);

//Formatting chart area 


chart.PlotArea.Area.BackgroundColor = Color.White;

chart.ChartArea.Area.BackgroundColor = Color.White;

chart.PlotArea.Area.ForegroundColor = Color.White;

chart.ChartArea.Area.ForegroundColor = Color.White;

chart.Name = “Sample Sheet”;

chart.Title.Text = “Sample Chart”;

chart.SizeWithWindow = true;

chart.ValueAxis.IsAutomaticMajorUnit = true;

chart.SecondValueAxis.IsAutomaticMajorUnit = true;

chart.SecondValueAxis.IsVisible = true;

chart.ValueAxis.IsAutomaticMaxValue = true;

chart.SecondValueAxis.IsAutomaticMaxValue = true;


chart.Calculate();


////Saving the Excel file

test.Save( filePath + @".out.xlsx", SaveFormat.Xlsx);
11

Attached testbook1.xls

12

Hi,

Thanks for your source file.

I have tested your file with the following code and found that issue is not replicable using the latest version: Aspose.Cells for .NET (Latest Version) .

Are you using a Regular .NET Framework or ClientProfile Framework ? Because there are two separate dlls for each of them and the dll that belongs to one version might not work with other version.

I have attached the output xlsx file generated using your source file with the given code and screenshot for your reference.

C#

string filePath = @“F:\Shak-Data-RW\Downloads\testBook1.xlsx”;

Workbook test = new Workbook(filePath);

Worksheet wrktest = test.Worksheets["__testsheet"];

//Adding a new worksheet to the Workbook object

int sheetIndex = test.Worksheets.Add(SheetType.Chart);

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = test.Worksheets[sheetIndex];

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Line, 1, 1, 25, 30);

//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];

//Adding NSeries (chart data source) to the chart ranging from “A1” cell to “B4”

chart.NSeries.Add("__testsheet!B2:EG168", true);

chart.NSeries.CategoryData = “__testsheet!A1:A168”;

chart.NSeries.SecondCatergoryData = “__testsheet!A1:A168”;

////Setting the chart type of 2nd NSeries to display as line chart

for (int i = 0; i < 50; i++)

{

chart.NSeries[i].Type = Aspose.Cells.Charts.ChartType.LineStacked;

chart.NSeries[i].Line.Weight = WeightType.HairLine;

chart.NSeries[i].Name = “®Ù”;

DataLabels datalabels = chart.NSeries[i].DataLabels;

//datalabels.Text = “®Ù”;

datalabels.ShowValue = false;

}

chart.NSeries[10].Name = “Area Two”;

chart.NSeries[10].Type = Aspose.Cells.Charts.ChartType.Line;

chart.NSeries[10].PlotOnSecondAxis = true;

chart.NSeries[10].Line.IsVisible = true;

chart.SecondCategoryAxis.IsVisible = true;

Console.WriteLine(“Applying Formatting …”);

//Formatting chart area

chart.PlotArea.Area.BackgroundColor = Color.White;

chart.ChartArea.Area.BackgroundColor = Color.White;

chart.PlotArea.Area.ForegroundColor = Color.White;

chart.ChartArea.Area.ForegroundColor = Color.White;

chart.Name = “Sample Sheet”;

chart.Title.Text = “Sample Chart”;

chart.SizeWithWindow = true;

chart.ValueAxis.IsAutomaticMajorUnit = true;

chart.SecondValueAxis.IsAutomaticMajorUnit = true;

chart.SecondValueAxis.IsVisible = true;

chart.ValueAxis.IsAutomaticMaxValue = true;

chart.SecondValueAxis.IsAutomaticMaxValue = true;

chart.Calculate();

////Saving the Excel file

test.Save(filePath + @".out.xlsx", SaveFormat.Xlsx);

Screenshot:

13

Hi,

Using the same excel and same code with adding below code is not generating the chart:

chart.Name = “Area One®Ù”;
chart.Title.Text = “Area One®Ù”;

Please check and do the needful. This is important to be fixed ASAP

Regards
Arihant
14

Hi,

Thanks for your feedback.

We were able to reproduce this issue at our end. By adding your line, it makes the chart corrupts.

Below is a simplified sample code that reproduces your exception. I have attached the source used in this code and output xlsx file generated by it.

We have logged this issue in our database. We will look into this issue and fix it. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41240.

Please check the red lines below for a reference.

C#



string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.Worksheets[0];


Chart chart = worksheet.Charts[0];


//Adding these two lines makes the output file

//corrupt so it does not open in Ms-Excel

chart.Name = “Area One®Ù”;

chart.Title.Text = “Area One®Ù”;


////Saving the Excel file

workbook.Save(filePath + @".out.xlsx", SaveFormat.Xlsx);

15

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have looked into your issue further and found that such title could not be set in Ms-Excel.

Please create a template file with a chart having such title and post it here.We will have to find how to write such character in chart to xlsx file.

16

Attached sample file with chart title and name as special character

17

Attached is the excel template with special characters in chart name, title and sheet name…

18

Hi,


Thanks for providing the sample file.

I can notice the chart’s name set as “Area One®Ù”.

I have logged your file with comments to attach with your existing issue “CELLSNET-41240”. Our concerned developer will look into it. Once we have any update on it, we will let you know here.

Thank you.
19
aryhant:
Attached is the excel template with special characters in chart name, title and sheet name..
Hi,

Ms-Excel automatically removes '' if you enter it as the chart.name.

We do not know which character should be removed.

Please remove those characters by yourself.
20

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.