Add a ChartSheet to excel file

Question

Hello,

I am trying to add a ChartSheet to an excel file.
I did this in code but it doesn't create a tab:

using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = document.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();

                WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet();

Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Chartsheet chartsheet = new Chartsheet()
                { };
                sheets.Append(chartsheet);
}

Answer 1

I reproduced your problem, but I haven't been able to solve it yet.

But I used the Open XML SDK 2.5 Productivity Tool to generate a very lengthy code based on an excel file with only a blank chartsheet.

I will provide it to you as an attachment, if you are interested, you can take a look.

Whether we can use other packages, I wrote a piece of code using Microsoft.Office.Interop.Excel, it is much simpler.

            Application xl = null;  
            _Workbook wb = null;  
            try  
            {  
                xl = new Application();  
                xl.Visible = true;  
                wb = (_Workbook)(xl.Workbooks.Add(XlWBATemplate.xlWBATWorksheet));  
                Worksheet worksheet = (Worksheet)wb.ActiveSheet;  
                var charts = worksheet.ChartObjects() as  
                    Microsoft.Office.Interop.Excel.ChartObjects;  
                var chartObject = charts.Add(60, 10, 300, 300) as  
                    Microsoft.Office.Interop.Excel.ChartObject;  
                var chart = chartObject.Chart;  
                chart.Location(XlChartLocation.xlLocationAsNewSheet, Type.Missing);  
            }  
            catch (Exception e)  
            {  
                Console.WriteLine(e.Message);  
            }  
            finally   
            {  
                wb.SaveAs(@"C:\xxx\1.xlsx");  
                wb.Close();  
                xl.Quit();  
            }  

136018-1.txt

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Spire.XLS for .NET is a .NET library for Excel which does not rely on MS Excel. You can install it via NuGet and use the following code to create a chart sheet.

using Spire.Xls;
using Spire.Xls.Charts;

namespace AddChartSheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a workbook
            Workbook wb = new Workbook();
            //Remove default worksheets
            wb.Worksheets.Clear();
            //Add a sheet to store data
            Worksheet dataSheet = wb.Worksheets.Add("Data Sheet");
            //Insert data
            dataSheet.Range["A1"].Value = "Country";
            dataSheet.Range["A1"].Style.Font.IsBold = true;
            dataSheet.Range["A2"].Value = "Cuba";
            dataSheet.Range["A3"].Value = "Mexico";
            dataSheet.Range["A4"].Value = "France";
            dataSheet.Range["A5"].Value = "German";
            dataSheet.Range["B1"].Value = "Sales";
            dataSheet.Range["B1"].Style.Font.IsBold = true;
            dataSheet.Range["B2"].NumberValue = 6000;
            dataSheet.Range["B3"].NumberValue = 8000;
            dataSheet.Range["B4"].NumberValue = 9000;
            dataSheet.Range["B5"].NumberValue = 8500;
            //Add a chart sheet
            Worksheet chartSheet = wb.Worksheets.Add("Chart Sheet");
            //Add a chart
            Chart chart = chartSheet.Charts.Add();
            chart.ChartType = ExcelChartType.Doughnut;
            chart.DataRange = dataSheet.Range["A1:B5"];
            chart.SeriesDataFromRange = false;
            //Set position of chart
            chart.LeftColumn = 1;
            chart.TopRow = 1;
            chart.RightColumn = 9;
            chart.BottomRow = 21;
            //Set chart title
            chart.ChartTitle = "Market share by country";
            chart.ChartTitleArea.IsBold = true;
            chart.ChartTitleArea.Size = 12;
            foreach (ChartSerie cs in chart.Series)
            {
                cs.DataPoints.DefaultDataPoint.DataLabels.HasPercentage = true;
            }

            chart.Legend.Position = LegendPositionType.Top;
            //Save to file
            wb.SaveToFile("DoughnutChart.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("DoughnutChart.xlsx");
        }
    }
}