Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed Aug 11, 2021 7:02 am

When switching row/column on an excel chart using C#,
I read the following,
Excel.ChartObject.SetSourceData(chartRange, Excel.xlRowCol.xlRows);

When I want to do the same thing using spire.xls, how can I do it ?

kurikabocya
 
Posts: 13
Joined: Mon Aug 09, 2021 2:21 am

Wed Aug 11, 2021 11:51 am

Hello,

Thanks for your inquiry.
I am sorry that our Spire.XLS does not support switching the row / column of the chart. But you can refer to the following code to achieve a similar result.
Code: Select all
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("chartAxis.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            Worksheet temp = workbook.Worksheets.Add("temp");
            CellRange range = sheet.Charts[0].DataRange;

            int rowStr = range.Row;
            int colStr = range.Column;
            int lastRow = range.LastRow;
            int lastCol = range.LastColumn;
           
            for (int i = rowStr; i <= lastRow; i++)
            {
                for (int j = colStr; j <= lastCol; j++)
                {
                    if (!sheet.Range[i, j].HasFormula)
                    {
                       
                        sheet.Copy(sheet.Range[i, j], temp.Range[j, i], true);
                    }
                    else
                    {
                       
                        temp.Range[j, i].Value2 = sheet.Range[i, j].FormulaValue;
                    }
                }
            }
            Chart chart = sheet.Charts[0];
            int height = range.Rows.Length;
            int width = range.Columns.Length;
           

            chart.DataRange = temp.Range[rowStr-1,colStr-1,rowStr+width,colStr+height];

            temp.Visibility = WorksheetVisibility.Hidden;
            workbook.SaveToFile("result.xlsx");


Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Tue Aug 31, 2021 10:42 am

Hello,

Greetings from E-iceblue!
Did the code we provided work for you? Any feedback will be greatly appreciated.

Sincerely,
Brian
E-iceblue support team
User avatar

Brian.Li
 
Posts: 1271
Joined: Mon Oct 19, 2020 3:04 am

Return to Spire.XLS