Excel Files, C, and misery

Alright, so, I haven't programmed a开发者_高级运维nything useful in ages - last time I did was a year ago and as you can imagine my knowledge of programming is seriously rusty. (last thing I 'programmed' was a ren'py game over the weekend. One can imagine the limited uses of this. The most advanced C program I wrote was a tic-tac-toe game a year ago. So yeah.)

Anyways, I've been given a job to write a program that takes two Excel files, both of which have a list of items, each associated with an ID. I need to write a program to search both files for IDs and if the IDs match, the program will need to create a new file with the matched IDs and items. This is insanely beyond my limited C capabilities.

If anyone could help, I would seriously appreciate it.

(also, if this is not possible with C, I'll do my best to work with any other languages)

Export the two files to .csv format and write a script to process the two files. For example, in PHP, you have built in csv read/write capabilities.

You can do this with VBA and create a Macro in one of the files which iterates over the cells in your column in file 1 and compares them to cells in file 2 and writes them to a new .xls file if they match.

Dana points out that the VLOOKUP function will do this quite easily.

1. Install GnuWin32
2. Output the excel files as text (csv for example)
3. sort each file with the -u option to remove duplicates if needed
4. mix and sort the 2 files
5. count unique IDs with uniq -c
6. filter out lines with a value of 1 for the count with grep
7. remove the count leaving the ID and whatever else you need with cut

If you know Java then you can use Apache POI for your project. You can use the examples given on the Apache POI website to accomplish your task.

Apache POI Excel Documentation: http://poi.apache.org/spreadsheet/quick-guide.html

If you absolutely have to do this on xls/xlsx file from a process, you probably need a copy of Excel controlled by COM automation. You can do this in BV/VBA/C#/C++ whatever, some easier than others. Google for 'Excel automation'.

Rgds, Martin

Not C, but you may be able to cobble something together very quickly using xlsperl.

It has come in handy for me in the past.