Manipulate Excel workbooks programmatically

I have an Excel workbook that I want to use as a template. It has several worksheets setup, one that produces the pretty graphs and summarizes the numbers. Sheet 1 needs to be populated with data that is generated by another program. The data comes in a tab delimited file.

Currently the user imports the tab delimited file into a new Workbook, selects all and copies. Then goes to the template and pastes the data into sheet1.

This is a large amount of data, 269 columns and over 135,000 rows. It’s a cumbersome process and the users are not experienced Excel users. All they 开发者_运维技巧really want is the pretty graphs.

I would like to add a step after the program that generates the data to programmatically automate the process the user currently must do manually.

Can anyone suggest the best method/programming language that could accomplish this?

POI is the answer. Look at the Apache website. You can use java to read the data and place it in cells. The examples are very easy.

You can can solve this, for example, by a simple VBA macro. Just use the macro recorder to record the steps the user does manually now, this will give you something to start with (you probably will have to add a function to let the user choose the import file).

You said you have some data generated by another program. What kind of program? A program that you have developed by yourself and where you can add the excel-import functionality? Or a third party program with a GUI that cannot be automated easily?

And if you really want to create an external program for this task - choose whatever programming lanuguage you like as long as it can use COM objects. In .NET, you have the option of using VSTO, but I would only suggest that for this task if you have already some experience with that (but than you would not ask this kind of question, I think :-))

Look here:

Create Excel (.XLS and .XLSX) file from C#

There's NPOI (.NET Framework version of POI) so that you can code in C# if you want.

If you use two workbooks - one for data and one for graphs - and don't update links automatically you can use a macro to get the data (maybe an ODBC connection if the file is in a format it can read - long shot) and then link the charts to the data workbook.

Use a macro to update the links and generate the charts and then send them out and hope no one updates the links.