Document Solutions for Excel, Java Edition | Document Solutions
Features / Chart / Chart Sheet
In This Topic
    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. User Shapes supported are shape, 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 user 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:

    Java
    Copy Code
    private static Workbook AddChartSheet() {
        // Initialize workbook
        Workbook workbook = new Workbook();
        // Fetch default worksheet
        IWorksheet worksheet = workbook.getWorksheets().get(0);
    
        worksheet.getRange("A1:E5")
                .setValue(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.getWorksheets().add(SheetType.Chart);
    
        // Add the main chart for the chart sheet
        IShape mainChart = chartSheet.getShapes().addChart(ChartType.ColumnClustered, 100, 100, 200, 200);
        mainChart.getChart().getChartTitle().setText("Sales 2018-2019");
        mainChart.getChart().getSeriesCollection().add(worksheet.getRange("A1:E5"));
    
        // Add a user shape for the main chart.
        IShape shape = mainChart.getChart().addShape(AutoShapeType.Rectangle, 50, 20, 100, 100);
        shape.getTextFrame().getTextRange()
                .add("This chart displays the regional quarterly sales for the year 2018-2019");
    
        // Saving workbook to Xlsx
        workbook.save("381-AddChartSheet.xlsx", SaveFileFormat.Xlsx);
        return workbook;

    Copy and Move Chart Sheet

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

    Java
    Copy Code
    private static void CopyMoveChartSheet() {
        Workbook workbook = AddChartSheet();
    
        // Add additional worksheets
        workbook.getWorksheets().add(SheetType.Worksheet);
        workbook.getWorksheets().add(SheetType.Worksheet);
    
        // Access ChartSheet
        IWorksheet chartSheet = workbook.getWorksheets().get(1);
    
        // Copies the chart sheet to the end of the workbook and save it
        chartSheet.copy();
        // Saving workbook to Xlsx
        workbook.save("382-CopyChartsheet.xlsx", SaveFileFormat.Xlsx);
    
        // Moves the chart sheet to the end of the workbook and save it
        chartSheet.move();
        // Saving workbook to Xlsx
        workbook.save("382-MoveChartsheet.xlsx", SaveFileFormat.Xlsx);

    Delete Chart Sheet

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

    Java
    Copy Code
    private static void DeleteChartSheet() {
        Workbook workbook = AddChartSheet();
    
        // Access ChartSheet
        IWorksheet chartSheet = workbook.getWorksheets().get(1);
    
        // Deletes the chart sheet
        chartSheet.delete();
    
        // Saving workbook to Xlsx
        workbook.save("384-NoChartsheet.xlsx", SaveFileFormat.Xlsx);