Chart Sheet | Document Solutions for Excel, Java 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. 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;

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);

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);

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);