3

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?

Tom
  • 33
  • 3

4 Answers4

2

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.

No One in Particular
  • 2,846
  • 4
  • 27
  • 32
1

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 :-))

Doc Brown
  • 19,739
  • 7
  • 52
  • 88
  • The data comes from a couple of programs that the engineering department has created. We use CA’s workload automation to create job control applications. I was planning to email the zipped output file and have the user manually perform the Excel import procedure. Between the fact that the users don’t like the process and the file is often too large to email, I want a solution where I can just send the chart worksheet from the workbook. The macro would make it easier for the user but it doesn’t address the large files. Thanks for your suggestion. – Tom Mar 26 '10 at 18:42
0

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.

Community
  • 1
  • 1
Leniel Maccaferri
  • 100,159
  • 46
  • 371
  • 480
  • Thanks for the suggestion but I don’t have a .NET development environment and like most businesses these days I’d be hard pressed to get them to spend money on new software licenses. – Tom Mar 26 '10 at 18:44
0

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.

Matthew
  • 26
  • 1