English 中文(简体)
Programmatically generating Excel workbook with pivots and charts
原标题:
  • 时间:2009-11-10 06:03:58
  •  标签:
  • c#
  • excel
  • vsto

I need to create a workbook which has a raw data on a sheet and a pivot table on another sheet. The pivot is created from the raw data. Then I need to create a chart with this pivot on the same or new sheet.

How can I do this in C# - and is this possible using VSTO?

最佳回答

One option is to connect to database from Excel and refresh the "Raw data" sheet, via VBA or defining an SQL query in Pivot data source. This is not so great as the user who opens the file must be able to connect to the database.

The other option is to fill the "Raw data" sheet programatically via C#. There are numerous libraries that can help you with that, even some free ones, but you can also do it yourself by using the Excel XML format (SpreadsheetML). You can use the Excel 2003 XML format or the new Open XML Excel format. The latter is far more complicated, but with it you can also take advantage of the OpenXML SDK and the Excel Package API.

问题回答

Easiest way to do it?
Use Open XML SDK 2.0

  1. Build a pre-generated version of the workbook with raw data, pivottable and pivotchart already created.
  2. Install the OpenXML SDK 2.0 setup package
  3. Use the OpenXML SDK 2.0 productivity tool from this install to open the workbook
  4. Select the root element on the left hand pane, and then right click and select Reflect Code
  5. Done - on the left pane is a complete C# class with the necessary code to generate the file.
  6. Well not quite done as you still need to refactor the code to take into account dynamically adding the required data content, however this will get you 97% of the way if you already know C# fairly well.

I ve done something similar to this before, but I didn t do it using C#. I used VBA since the language is already built in to Excel.

My approach was this:

  • Create a worksheet called "RAW DATA." This worksheet has a QueryTable object in it that can be updated via code in VBA.
  • I manually created a pivot table based on the QueryTable in the RAW DATA worksheet.
  • I then added code in VBA so that after RAW DATA was updated, Pivot Table was refreshed with the new data.

This method works really well if the layout of your raw data and your pivot table stays the same. I have a workbook that I made for a cowork that updates multiple sheets with pivot tables based on one set of data. She really likes it because just by clicking one button, she has a refreshed view of all of her data.

If this approach works for you and you d like more details as to how to implement some of those methods, let me know more details of your situation and I can try to help you out.





相关问题
Anyone feel like passing it forward?

I m the only developer in my company, and am getting along well as an autodidact, but I know I m missing out on the education one gets from working with and having code reviewed by more senior devs. ...

NSArray s, Primitive types and Boxing Oh My!

I m pretty new to the Objective-C world and I have a long history with .net/C# so naturally I m inclined to use my C# wits. Now here s the question: I feel really inclined to create some type of ...

C# Marshal / Pinvoke CBitmap?

I cannot figure out how to marshal a C++ CBitmap to a C# Bitmap or Image class. My import looks like this: [DllImport(@"test.dll", CharSet = CharSet.Unicode)] public static extern IntPtr ...

How to Use Ghostscript DLL to convert PDF to PDF/A

How to user GhostScript DLL to convert PDF to PDF/A. I know I kind of have to call the exported function of gsdll32.dll whose name is gsapi_init_with_args, but how do i pass the right arguments? BTW, ...

Linqy no matchy

Maybe it s something I m doing wrong. I m just learning Linq because I m bored. And so far so good. I made a little program and it basically just outputs all matches (foreach) into a label control. ...

热门标签