Creating Excel documents with LaTeX

Question

I've been using an own class with some commands for writing exams in my classes (I'm a teacher). In this, I use a rather complex point system (why I do this is kind of irrelevant) with 9 different kind of points. In order to show the students what kind of points they earned on a particular question, I've been using newfile to create a .xml file showing the question number and what kind of point that can be earned on that particular question, as shown in the picture (when opened in Excel)

When I enter the questions I put some point commands to that question (9 different) which each add a row to the .xml file. The class uses a load of counters and commands, and lots of text to produce the beginning and ending of the .xml file.

I've come to the conclusion that I really need a barchart, showing the points earned and not earned, but .xml in Excel wont give me any (well, it does but it can not be saved so I can't harvest the code by opening the file in a text editor).

So, this might be more Excel related than LaTeX, but since I use LaTeX to produce the file in question, I thought I could ask it here: Is there a way (or package) to create Excel files via LaTeX, that can add charts (in some way)? Or is there a better way than using newfile and .xml? Perhaps a different file format is needed?

Answer 1

Since Excel files are binary files (see below the update) it would very difficult to create an excel file directly from LaTeX, IMHO. LaTeX is more suitable for creating text files. This is what you will have to exploit.

You are already creating xml files, so file creation and writing mechanism should be an easy task for you.

Anyway, I think that creation of .CSV files will be your best bet.

For example, a part of your displayed file should look like,

Uppgift, EBP, CBP, ABP, EPM  
1_1, 1  
1_2, ,,,1

Save this file with .csv extension, it will be automatically treated by Excel as an Excel compatible file. I am not sure how you can create merged cells or cell boundaries (and cell backgrounds). These are something, which perhaps will have to be done manually once you open the file in Excel.

If you want to go for a more adventurous (and definitely more rewarding) path, you will want to use Java (Apache POI) or PHP (PHPExcel) to create Excel files from text files created via LaTeX.

Unless you are using the facility for a large number of files and for a general user base, perhaps the .CSV option is the most optimal one taking into account the cost-benefit (please read effort-benefit) analysis.

UPDATE Found (the hard way) that xlsx files are zipped bunch of xml files (thanks Juri Robl). But there so many files (at least eight) to be generated, perhaps the programming involved in LaTeX will not be worth the effort. And once these files are generated, you will have to zip them using some external application, not LaTeX.