Search code examples
excel-2010officewriter

After I bind a DataTable to an Excel template sheet, how can I build an Excel scatter chart on top of the DataTable placeholders?

Here my problem in short. I'm using a demo version of OfficeWriter to figure out whether it is suitable for our porpuses or not. In particular i've prepared an Excel template composed of 8 placeholders, representing the X-Y columns of 4 DataTables, and an Excel Scatter-Chart in which i add 4 series with reference to the respecive columns pairs. enter image description here In my code i copy this template several times according items in a list and naming those copied sheets after my items' names (i use wb.CopySheet(...) method for this). The main issue here is that when i call for "xlt.process()" method, it correctly updates the Y-range datamarkers with the right sheet name, but it doesn't seem to update the X-range datamarker. enter image description here Can someone give me a hand?

Solution

  • This appears to be a bug. When OfficeWriter is reading in the values for the chart, the property .ScatterValues should have been set to the correct range value (e.g. =Sheet2!$R$2:$R$2), and therefore causing CopySheet to fail. I have filed a bug for development to fix this bug.

    To work around this issue, you can set the .ScatterValues (see http://wiki.softartisans.com/display/EW8/Series.ScatterValues) property in the code above:

    string templatePath = "input.xlsx";

/* Open the template workbook */
ExcelApplication xla = new ExcelApplication();
var wb = xla.Open(templatePath);

/* Select the template worksheet to copy */
var origWS = wb.Worksheets["Sheet1"];

/* Make a copy of the worksheet with the given name */
var wsName = "Sheet2";
wb.Worksheets.CopySheet(origWS, 1, wsName);

/* For the new worksheet, update the ScatterValues to point to this sheet */
var newWS = wb.Worksheets[wsName];
newWS.Charts[0].SeriesCollection[0].ScatterValues = "=" + wsName + "!$B$27";

/* Create an instance of ExcelTemplate */
ExcelTemplate xlt = new ExcelTemplate();

/* Open the workbook from the ExcelApplication object above */
xlt.Open(xla, wb);

    Disclaimer: I work for SoftArtisans, makers of OfficeWriter.