Message-ID: <1925138896.10233.1711718614326.JavaMail.web05$@web05> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_10232_1251141639.1711718614326" ------=_Part_10232_1251141639.1711718614326 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
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:
Get a Charts collection:
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= .
Create an anchor in the worksheet:
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.
Create a blank chart of a specified type, at the anchor you created:=
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).
Return a SeriesCollection object representing the set of data series= in the chart:
SeriesCollection sc =3D chrt.SeriesCollection;=20
Set the range of category (x) axis values:
sc.CategoryData =3D "Sheet1!A3:C3";=20
Add a data series to the chart:
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.
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
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:
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:
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