Message-ID: <673127298.8713.1711657332439.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_8712_899307612.1711657332439" ------=_Part_8712_899307612.1711657332439 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html Charts in ExcelApplication

Charts in ExcelApplication

ExcelApplication supports all Excel chart types, and allows you to = insert a chart in a worksheet, create a chart sheet (a worksheet that only = contains a chart).

If you open an existing Excel file with ExcelApplication.Open, you can u= se ExcelWriter's charting objects to modify charts in the spreadsheet.

To create a simple column chart:

  1. Get a Charts collection:

    =20
    ExcelApplication xla =3D new ExcelApplication();
    Workbook wb =3D xla.Create();
    Worksheet ws =3D wb.Worksheets[0];
    Charts chrts =3D ws.Charts;
    
    =20

    The Charts collection contains all charts in a specified worksheet= .

  2. Create an anchor in the worksheet:

    =20
    Anchor anch =3D ws.CreateAnchor(7, 4, 0, 50);
    
    =20

    An anchor represents the position of a floating object (e.g., a ch= art) within a worksheet. The chart's top left corner will be placed at the = anchor.

  3. Create a blank chart of a specified type, at the anchor you created:=

    =20
    Chart chrt =3D chrts.CreateChart(ChartType.Column.Clustered, anc=
    h);
    
    =20

    The ChartType class contains all available chart types (e.g., Colu= mn) and sub-types (e.g., Clustered).

  4. Return a SeriesCollection object representing the set of data series= in the chart:

    =20
    SeriesCollection sc =3D chrt.SeriesCollection;
    =20
  5. Set the range of category (x) axis values:

    =20
    sc.CategoryData =3D "Sheet1!A3:C3";
    =20
  6. Add a data series to the chart:

    =20
    Series s =3D sc.CreateSeries("Sheet1!A25:C25");
    = =20

    The formula passed to CreateSeries represents cells that contain t= he source values for the new data series.

Example: Creatin= g a Chart

=20
using System;
using SoftArtisans.OfficeWriter.ExcelWriter;

class ChartDemo : System.Web.UI.Page
{
     protected void Page_Load(object sender, System.EventArgs e)
     {
          //--- Create ExcelApplication, a Workbook, and a Worksheet
          ExcelApplication xla =3D new ExcelApplication();
          Workbook wb =3D xla.Create();
          Worksheet ws =3D wb.Worksheets[0];

          //--- Put some values into the cells that the
          //--- chart will reference
          System.Random rand =3D new System.Random();

          //--- Headers
          ws.Cells[0, 0].Value =3D "Month";
          ws.Cells[0, 1].Value =3D "Sales";
=20
          //--- Month and sales
          ws.Cells[1, 0].Value =3D "Jan";
          ws.Cells[1, 1].Value =3D rand.Next(1000);
          ws.Cells[2, 0].Value =3D "Feb";
          ws.Cells[2, 1].Value =3D rand.Next(1000);
          ws.Cells[3, 0].Value =3D "Mar";
          ws.Cells[3, 1].Value =3D rand.Next(1000);

          //--- Create an Anchor on Cell B6
          Anchor anch =3D ws.CreateAnchor(4, 2, 0, 0);

          //--- Create the chart
          Chart chrt =3D ws.Charts.CreateChart(ChartType.Column.Clustered, =
anch);

          //--- Set series collection
          string seriesFormula =3D
               String.Format("=3D{0}!{1}:{2}", ws.Name,
               ws.Cells[1, 1].Name, ws.Cells[3, 1].Name);
          Series srs =3D chrt.SeriesCollection.CreateSeries(seriesFormula);
          srs.Name =3D "Sales";

          //--- Set category data
          chrt.SeriesCollection.CategoryData =3D
               String.Format("{0}!{1}:{2}", ws.Name,
               ws.Cells[1, 0].Name, ws.Cells[3, 0].Name);

          xla.Save(wb, Page.Response, "Charts.xls", false);
     }
}
=20

Modifying an E= xisting Chart

Cod= e sample: Modifying an Existing Chart

When you use ExcelApplication to open a spreadsheet, you can access char= ts through their names or titles. Chart.getTitle returns a ChartText object= representing the title region. ChartText.getText returns the title text. T= he following function takes a chart's title and returns a Chart object repr= esenting the chart. This code can be useful if you want to locate a specifi= c chart in a worksheet but only know its title text:

=20
private Chart FindChart(string title)
{
     for (int iChart =3D 0; iChart < ws.Charts.Count; iChart++)
     {
          Chart chrt =3D ws.Charts[iChart];
          if (chrt.Title.Text =3D=3D title)
               return chrt;
     }
     return null;
}
=20

The following example uses the FindChart code to locate a specific chart= in a worksheet. The chart series collection is then cleared and re-set to = the data imported from a database:

=20
private void PopulateMainChart()
{
     //--- Get the data from the database.
     DataTable[] dtArr =3D GetCategoryQuarterlySales();

     //--- Find the main chart based on its title text, and get
     //--- a reference to it.
     Chart mainChart =3D FindChart("Quarterly Sales 2003");

     //--- Clear all the existing series objects from the collection=20
     SeriesCollection seriesCol =3D mainChart.SeriesCollection;
     while (seriesCol.Count > 0)
          seriesCol.Remove(0);

     //--- Each DataTable has a single row of data.
     int iRow =3D 29;
     for (int i =3D 0; i < dtArr.Length; i++)
     {
          DataTable dt =3D dtArr[i];

          //--- Import data from the DataTable.=20
          ws.ImportData(dt, ws[iRow, 1]);

          //--- Add the imported data as a new Series object
          //--- in the chart's collection.  There will be a variable
          //--- number of series objects depending on which categories
          //--- were selected for display.
          Area a =3D ws.CreateArea(iRow, 2, 1, 4);
          Series srs =3D seriesCol.CreateSeries(a);
          srs.NameFormula =3D ws[iRow, 1].Name;

          iRow++;
     }

     //--- Re-set the category data.  The size will vary
     //--- depending on the selected categories.
     seriesCol.CategoryData =3D
          ws.CreateArea(29, 1, dtArr.Length, 1).Dimensions;

     //--- Add a legend to the chart if desired=20
     if (bLegend)
     {
          mainChart.Legend.Visible =3D true;
          mainChart.Legend.Location =3D Legend.LegendLocation.Top;
     }
     else
     {
          //--- The legend is hidden in the template workbook.
          //--- Hide it again in case it's made visible.
          mainChart.Legend.Visible =3D false;
     }
}
=20
------=_Part_8712_899307612.1711657332439--