We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Format chart x-axis

hi,

I want to format the x-axis of chart. My chart has lot of points for x-axis so I want to set the major unit to automatic. I tried this at the design time but does not help.

So I tried below code to make major and minor unit to automatic.

-----------------------------------------------------------------------------------------------

Workbook oWB = new Workbook(@"C:\Test.xls");

Cells oCells = oWB.Worksheets[0].Cells;

Worksheet oSheet = oWB.Worksheets[0];

DataTable dt = CreateDataTable();

oCells.ImportDataTable(dt, false, 2, 0, dt.Rows.Count, dt.Columns.Count, false, "MM/dd/yyyy", true);

Aspose.Cells.Charts.Chart oChart = oSheet.Charts["Chart 1"];

oChart.CategoryAxis.IsAutomaticMajorUnit = true;

oChart.CategoryAxis.IsAutomaticMinorUnit = true;

oChart.CategoryAxis.BaseUnitScale = Aspose.Cells.Charts.TimeUnit.Months;

oWB.Save(@"C:\TestOuput.xls");

private DataTable CreateDataTable()

{

DataTable dt = new DataTable("Test Data");

DataColumn dcDate = new DataColumn("Date", typeof(System.DateTime));

DataColumn dcV1 = new DataColumn("V1", typeof(System.Double));

DataColumn dcV2 = new DataColumn("V2", typeof(System.Double));

DataColumn dcV3 = new DataColumn("V3", typeof(System.Double));

DataColumn dcV4 = new DataColumn("V4", typeof(System.Double));

DataColumn dcV5 = new DataColumn("V5", typeof(System.Double));

dt.Columns.Add(dcDate);

dt.Columns.Add(dcVAMI1);

dt.Columns.Add(dcVAMI2);

dt.Columns.Add(dcVAMI3);

dt.Columns.Add(dcVAMI4);

dt.Columns.Add(dcVAMI5);

DateTime dtStartDate = new DateTime(2000, 1, 1);

for (int iCtr = 0; iCtr < 279; iCtr++)

{

DataRow dr = dt.NewRow();

dr[0] = dtStartDate.AddDays(iCtr);

dr[1] = Convert.ToDouble(string.Concat(iCtr + 1, ".", 1));

dr[2] = Convert.ToDouble(string.Concat(iCtr + 1, ".", 2));

dr[3] = Convert.ToDouble(string.Concat(iCtr + 1, ".", 3));

dr[4] = Convert.ToDouble(string.Concat(iCtr + 1, ".", 4));

dr[5] = Convert.ToDouble(string.Concat(iCtr + 1, ".", 5));

dt.Rows.Add(dr);

}

dt.AcceptChanges();

return dt;

}

-----------------------------------------------------------------------------------------------

Attached the template and the output file.

Hi,


Thank you for using Aspose.Cells.

Please provide us with your source Excel file used in this example. We will look into it and try to assist you further as soon as possible.

Just now attached in the first description

Hi,


I think you may try to add a line to your code, see the line in bold if it works for your needs:

Workbook oWB = new Workbook(@“C:\Test.xls”);

Cells oCells = oWB.Worksheets[0].Cells;

Worksheet oSheet = oWB.Worksheets[0];

DataTable dt = CreateDataTable();

oCells.ImportDataTable(dt, false, 2, 0, dt.Rows.Count, dt.Columns.Count, false, “MM/dd/yyyy”, true);

Aspose.Cells.Charts.Chart oChart = oSheet.Charts[“Chart 1”];

oChart.CategoryAxis.IsAutomaticMajorUnit = true;

oChart.CategoryAxis.IsAutomaticMinorUnit = true;

oChart.CategoryAxis.BaseUnitScale = Aspose.Cells.Charts.TimeUnit.Months;

oChart.CategoryAxis.CategoryType = CategoryType.TimeScale;

oWB.Save(@“C:\TestOuput.xls”);


private DataTable CreateDataTable()
{

DataTable dt = new DataTable(“Test Data”);

DataColumn dcDate = new DataColumn(“Date”, typeof(System.DateTime));

DataColumn dcV1 = new DataColumn(“V1”, typeof(System.Double));

DataColumn dcV2 = new DataColumn(“V2”, typeof(System.Double));

DataColumn dcV3 = new DataColumn(“V3”, typeof(System.Double));

DataColumn dcV4 = new DataColumn(“V4”, typeof(System.Double));

DataColumn dcV5 = new DataColumn(“V5”, typeof(System.Double));

dt.Columns.Add(dcDate);

dt.Columns.Add(dcV1);

dt.Columns.Add(dcV2);

dt.Columns.Add(dcV3);

dt.Columns.Add(dcV4);

dt.Columns.Add(dcV5);

DateTime dtStartDate = new DateTime(2000, 1, 1);

for (int iCtr = 0; iCtr < 279; iCtr++)
{

DataRow dr = dt.NewRow();

dr[0] = dtStartDate.AddDays(iCtr);

dr[1] = Convert.ToDouble(string.Concat(iCtr + 1, “.”, 1));

dr[2] = Convert.ToDouble(string.Concat(iCtr + 1, “.”, 2));

dr[3] = Convert.ToDouble(string.Concat(iCtr + 1, “.”, 3));

dr[4] = Convert.ToDouble(string.Concat(iCtr + 1, “.”, 4));

dr[5] = Convert.ToDouble(string.Concat(iCtr + 1, “.”, 5));

dt.Rows.Add(dr);

}

dt.AcceptChanges();

return dt;

}


Thank you.

I added the line you asked. This resolved the problem of showing Base unit in Months.

But Major and Minor unit are still showing as Fixed. By setting IsAutomaticMajorUnit and IsAutomaticMinorUnit to True it should set Major and Minor unit to automatic.

Hi,


Thanks for your feedback.

After further tests, you are right, I think it might be an issue with setting Major and Minor Units to Automatic. We need to investigate it thoroughly. I have logged a ticket with an id: CELLSNET-40882 We will look into it soon.

Thank you.

Hi,


Thank you for your feedback.

I can observe the issue as you mentioned. Despite setting the Major and Minor units to Automatic, when we open the output file, we see that these are again showing as Fixed. We will look into this issue and assist you further soon.

Hi,


After further evaluation of your issue, we come to know that you have to specify the XLSX file format before saving the file. The codes should be like as following:


<o:p></o:p>

//oChart.CategoryAxis.BaseUnitScale = Aspose.Cells.Charts.TimeUnit.Months;

oChart.CategoryAxis.CategoryType = CategoryType.TimeScale;

oWB.Save(Path.Combine(dir, "TestOutput.xlsx"));


Actually, if you could open your “TestOutput.xls” in MS Excel 2003, The values are automatic.


Thank you.

Hi,

I noticed that when I implement the above solution it does format axis to auto but here x-axis is in Date format and after making it Auto it removes the date formatting and shows x-axis in number format (converts date into number).

Also, We cannot open the file in 2003 because most of our users have 2007 and template is created in 2003 because Aspose has limitation for WordArt in 2007 excel.

So, the solution should be such that the template is in xls and the output file generated will also be in xls but it will be open in 2007 because most of the users has 2007 installed on their machine.

Thanks,

Hetal

Hi,


"Also, We cannot open the file in 2003 because most of our users have 2007 and template is created in 2003 because Aspose has limitation for WordArt in 2007 excel."

Well, WordArt feature is supported in Excel 2007 now, could you try our latest version:
Aspose.Cells for .NET (Latest Version)

Thank you.

hi,

I downloaded the latest 7.3.1.0 aspose.cells dll and tried generating report again. There is still small issues in wordart e.g. alignment for wordart is changed. its alignment is changed from middle to left.

Below is the code I used to insert WordArt. Let me know if I am missing here anything while inserting wordart. With the old version dll I was able to show proper wordart with middle alignment.

------------------------------------------------------------------------------

Aspose.Cells.Drawing.Shape oWordart = oSheet.Shapes.AddTextEffect(MsoPresetTextEffect.TextEffect1, sWatermarkText, sFontName, iFontSize, bFontBold, bFontItalic, iUpperLeftRow, iTop, iUpperLeftColumn, iLeft, iHeight, iWidth);

oWordart.RotationAngle = iRotationAngle;

MsoFillFormat oWordArtFormat = oWordart.FillFormat;

oWordArtFormat.ForeColor = oTextColor;

oWordArtFormat.Transparency = dblTransparency;

MsoLineFormat oLineFormat = oWordart.LineFormat;

oLineFormat.IsVisible = true;

oLineFormat.ForeColor = System.Drawing.Color.Black;

------------------------------------------------------------------------------

Thanks,

Hetal

Hi,


I am not sure about the alignment issue you have mentioned. You mean to say the vertical alignment of the textbox when you click on “Format Text Effects” option by right clicking on the Word Art. Please elaborate your issue, you may give us screen shots of the problem areas (encircling with red color) comparing the older file (by your older Aspose.Cells component) with new file (by v7.3.1). Also provide the output Excel files. We will check it soon. Also provide runnable code same as mine given below, so that we could get to know your original values for different variables and objects to accurately simulate the issue on our end. We will check your issue soon.

Sample code:
'Instantiate a new Workbook
Dim workbook As New Workbook()
'Get the first default sheet
Dim sheet As Worksheet = workbook.Worksheets(0)
'Add Watermark
Dim wordart As Aspose.Cells.Drawing.Shape = sheet.Shapes.AddTextEffect(MsoPresetTextEffect.TextEffect1, “CONFIDENTIAL”, “Arial Black”, 50, False, True, 18, 8, 1, 1, 130, 800)

'Set the rotation angle
wordart.RotationAngle = 3

'Get the fill format of the word art
Dim wordArtFormat As MsoFillFormat = wordart.FillFormat
'Set the color
wordArtFormat.ForeColor = System.Drawing.Color.Red
'Set the transparency
wordArtFormat.Transparency = 0.9
'Make the line invisible
Dim lineFormat As MsoLineFormat = wordart.LineFormat
lineFormat.IsVisible = False
'Save the file
workbook.Save(“e:\test2\My_Watermarkt_Test.xlsx”)

Thank you.

hi,

Below is the code to have watermark by adding wordart and this is working perfectly fine with 7.2.1.0 aspose.cells dll.

--------------------------------------------------------------------

oWordart.RotationAngle = iRotationAngle;

MsoFillFormat oWordArtFormat = oWordart.FillFormat;

oWordArtFormat.ForeColor = oTextColor;

oWordArtFormat.Transparency = dblTransparency;

MsoLineFormat oLineFormat = oWordart.LineFormat;

oLineFormat.IsVisible = true;

oLineFormat.ForeColor = System.Drawing.Color.Black;

--------------------------------------------------------------------

Attached the excel file named "Wordart_Before.xls" of correct wordart in file created using 7.2.1.0 dll and with xls file.

Now when I replace latest dll i.e. 7.3.1.0 and with file extn as xlsx (as mentioned in your previous solutions) it does not retain the alignment of the text inside wordart. Attached the excel file named "Wordart_After.xlsx" after replacing new dll.

Hi,

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

I was able to replicate this issue using the latest version:
Aspose.Cells for .NET (Latest Version) with the following code.

When I convert your source xls file into xlsx file, the Watermark alignment gets changed from middle to left.

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

This issue has been logged as CELLSNET-41021.

Also, when I ran the same code using Aspose.Cells for .NET v7.2.0.1, I do not get the expected output.

Please see the screenshot below for your reference. I have also attached the output file.

C#


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


Workbook workbook = new Workbook(filePath);


workbook.Save(filePath + “.out.xlsx”);

Screenshot:

Hi,

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

Please download and try the latest version:
Aspose.Cells for .NET (Latest Version)
It should fix your issue.

I have attached the output file generated by the above code using this latest fix and it now works fine.

Let us know if you still face any issue. We will help you asap.

Thanks team,

There are still some issues with wordart. As I said earlier the chart issue is resolved but the WordArt is gone for toss.

Attached the file Before and After for your reference. This is using the new DLL 7.3.1.2

Thanks,

Hetal

Hi,

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

Could you please provide us your complete test case replicating your issue with the latest version:
Aspose.Cells for .NET (Latest Version)
alongwith the source/template xls/xlsx files involved?

I have tested your file with the following code and found, issue does not exist with xlsx format but it exists when the file is saved back in xls format. Please see the details below.

Xls Output:

  • No issue exist if you open file in Excel 2003
  • Issue does exist if you open output file in Excel 2007 or Excel 2010

Xlsx Output:

  • No issue exist if you open file in Excel 2007 or Excel 2010

I have attached all the files and screenshot for your reference.

C#

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


//Create a workbook object from the template file

Workbook workbook = new Workbook(filePath);


workbook.Save(filePath + “.out.xls”, SaveFormat.Excel97To2003);

workbook.Save(filePath + “.out.xlsx”, SaveFormat.Xlsx);

Screenshot:

hi,

The entire code is available in this post. I have not changed anything in my code. Its same just replced the new DLL.

Also, for your information, I am using xlsx template and the saving file is also xlsx. So there is no xls file used for template as well as generated report.

Thanks,

Hetal

Hi,

Thanks for your input.

I have looked into your issue further and I am afraid, this issue is not replicable if you save it to xlsx format.

Please provide us your full runnable code to replicate this issue. The code you provided earlier was a partial, so it could not be used to look into this issue precisely.

Below is the code used by me to test this issue. I have attached the output file and the screenshot for your reference.

C#


Workbook book = new Workbook();


Worksheet oSheet = book.Worksheets[0];


string sWatermarkText = “FOR INTERNAL USES ONLY \r\nNot for Distribution to Clients or Managers”;

string sFontName = “Arial Black”;

int iFontSize = 19;

bool bFontBold = true;

bool bFontItalic = true;

int iUpperLeftRow = 1;

int iTop = 100;

int iUpperLeftColumn = 1;

int iLeft = 20;

int iHeight = 20;

int iWidth = 700;


int iRotationAngle = 3;


Aspose.Cells.Drawing.Shape oWordart = oSheet.Shapes.AddTextEffect(MsoPresetTextEffect.TextEffect1, sWatermarkText,

sFontName, iFontSize, bFontBold, bFontItalic,

iUpperLeftRow, iTop, iUpperLeftColumn, iLeft,

iHeight, iWidth);


oWordart.RotationAngle = iRotationAngle;


MsoFillFormat oWordArtFormat = oWordart.FillFormat;


oWordArtFormat.ForeColor = System.Drawing.Color.Blue;


oWordArtFormat.Transparency = 0.9;


MsoLineFormat oLineFormat = oWordart.LineFormat;


oLineFormat.IsVisible = true;


oLineFormat.ForeColor = System.Drawing.Color.Black;


book.Save(“output.xlsx”, SaveFormat.Xlsx);


Screenshot:

hi,

I was using xls only for template as well as for generated file but if you see this post chain, there was a suggestion to use xlsx to get correct output hence.

I changed from xlsx to xls and its working properly now.

Thanks so much,

Hetal