Chart Sheet | Document Solutions for Excel, .NET Edition

Chart Sheet

In This Topic

Sometimes, users find it difficult to accommodate both data and charts in the same worksheet. For this reason, DsExcel now lets users add the chart to a separate sheet, called the 'Chart sheet'. Unlike Worksheets, Chart sheets can contain only the chart. This helps avoid the usual clutter of data and embedded charts in the same worksheet. Also, using chart sheets, users will be able to read the chart in detail and change the sheet page orientation while printing.

A Chart sheet can be created in a Workbook using the IWorksheets.Add(SheetType.Chart) method. Further, you can add a chart to the Chart sheet by using IShapes.AddChart method. The user may note that each chart sheet should have a chart, else it can throw an exception while saving the file.

The methods and properties associated with chart sheets in DsExcel are listed in the table below:

Methods/Properties	Description
Add(SheetType.Chart)	The Add method in IWorksheets interface has an overload with 'SheetType'. Hence, for adding a Chart sheet, you need to use SheetType.Chart.
AddChart	The AddChart method in IShapes interface adds a chart for the Chart sheet. Note: Each Chart sheet should have a chart. Otherwise, it will throw an exception while saving the file.
AddShape	The AddShape method in IChart interface adds multiple shapes for the Chart sheet. Supported shapes are chart, picture, connector etc. In this case, the first chart is the main Chart, and the other shapes will be discarded when saving the file.
SheetType	The SheetType Property in IWorksheet interface gets the type of current sheet (Worksheet or Chart sheet).
Delete	The Delete method in IShape interface deletes the Chart from the Chart sheet, or deletes the shape from the Chart.
Copy	The Copy method in IWorksheet interface copies a new Chart sheet.
Move	The Move method in IWorksheet interface moves the chart sheet to a new location in the current workbook or a new workbook.

The following sections discuss in detail about chart sheet operations in a workbook.

Add Chart Sheet

To add a chart sheet, refer the following code:

C#	Copy Code
public Workbook AddChartSheet() { Workbook workbook = new Workbook(); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.Range["A1:E5"].Value = new object[,] { {"Region", "Q1", "Q2","Q3","Q4"}, {"North", 100, 300, 200, 600}, {"East", 400, 200, 500, 800}, {"South", 300, 500, 100, 400}, {"West", 400, 200, 600, 100}, }; //Add a Chart Sheet IWorksheet chartSheet = workbook.Worksheets.Add(SheetType.Chart); //Add the main chart for the chart sheet IShape mainChart = chartSheet.Shapes.AddChart(ChartType.ColumnClustered, 100, 100, 200, 200); mainChart.Chart.ChartTitle.Text = "Sales 2018-2019"; mainChart.Chart.SeriesCollection.Add(worksheet.Range["A1:E5"]); //Add a user shape for the main chart. IShape shape = mainChart.Chart.AddShape(AutoShapeType.Rectangle, 50, 20, 100, 100); shape.TextFrame.TextRange.Add("This chart displays the regional quarterly sales for the year 2018-2019"); //Save Workbook workbook.Save("Chartsheet.xlsx"); return workbook; }

Copy Code

public Workbook AddChartSheet()
{
    Workbook workbook = new Workbook();
    IWorksheet worksheet = workbook.Worksheets[0];

    worksheet.Range["A1:E5"].Value = new object[,]
    {
        {"Region", "Q1", "Q2","Q3","Q4"},
        {"North", 100, 300, 200, 600},
        {"East", 400, 200, 500, 800},
        {"South", 300, 500, 100, 400},
        {"West", 400, 200, 600, 100},
    };

    //Add a Chart Sheet
    IWorksheet chartSheet = workbook.Worksheets.Add(SheetType.Chart);

    //Add the main chart for the chart sheet
    IShape mainChart = chartSheet.Shapes.AddChart(ChartType.ColumnClustered, 100, 100, 200, 200);
    mainChart.Chart.ChartTitle.Text = "Sales 2018-2019";
    mainChart.Chart.SeriesCollection.Add(worksheet.Range["A1:E5"]);

    //Add a user shape for the main chart.
    IShape shape = mainChart.Chart.AddShape(AutoShapeType.Rectangle, 50, 20, 100, 100);
    shape.TextFrame.TextRange.Add("This chart displays the regional quarterly sales for the year 2018-2019");

    //Save Workbook
    workbook.Save("Chartsheet.xlsx");

    return workbook;
}

Copy and Move Chart Sheet

To copy and move a chart sheet, refer the following example code:

C#	Copy Code
public void CopyMoveChartSheet() { Workbook workbook = AddChartSheet(); //Add additional worksheets workbook.Worksheets.Add(SheetType.Worksheet); workbook.Worksheets.Add(SheetType.Worksheet); //Access ChartSheet IWorksheet chartSheet = workbook.Worksheets[1]; //Copies the chart sheet to the end of the workbook and save it. chartSheet.Copy(); workbook.Save("CopyChartsheet.xlsx"); //Moves the chart sheet to the end of the workbook and save it. chartSheet.Move(); workbook.Save("MoveChartsheet.xlsx"); }

Copy Code

public void CopyMoveChartSheet()
{
    Workbook workbook = AddChartSheet();

    //Add additional worksheets
    workbook.Worksheets.Add(SheetType.Worksheet);
    workbook.Worksheets.Add(SheetType.Worksheet);

    //Access ChartSheet
    IWorksheet chartSheet = workbook.Worksheets[1];

    //Copies the chart sheet to the end of the workbook and save it.
    chartSheet.Copy();
    workbook.Save("CopyChartsheet.xlsx");

    //Moves the chart sheet to the end of the workbook and save it.
    chartSheet.Move();
    workbook.Save("MoveChartsheet.xlsx");
}

Delete Chart Sheet

To delete a chart sheet, refer the following example code:

C#	Copy Code
public void DeleteChartSheet() { Workbook workbook = AddChartSheet(); //Access ChartSheet IWorksheet chartSheet = workbook.Worksheets[1]; //Deletes the chart sheet chartSheet.Delete(); //Save Workbook workbook.Save("NoChartsheet.xlsx"); }

Copy Code

public void DeleteChartSheet()
{
    Workbook workbook = AddChartSheet();

    //Access ChartSheet
    IWorksheet chartSheet = workbook.Worksheets[1];

    //Deletes the chart sheet
    chartSheet.Delete();

    //Save Workbook
    workbook.Save("NoChartsheet.xlsx");
}