Best language to use when exporting an excel file

Question

I want to write a macro program that takes in data from a text file and then arranges it in a specific manner in an excel file. I don't know which language has the best features for dealing with Excel.

I prefer java, and I see someone made an api called JExcelApi, but I'm not sure about it's capabilities. I would like to be able to generate a graph automatically in excel based on the data in a certain column. Is this possible in any language? I would guess that Microsoft's VB or C# would have an advanced feature such as this, but I'm not sure. Thanks.

Answer 1

You have a few approaches that are possible. If you didn't need to create charts, I'd say to construct an XML document manually; you can see the structure needed by going to an existing Excel document that is like the one you want to create, and doing "save as XML". Then you can just replicate that with your code, in any language, and it'll open in excel correctly.

With charting or other advanced requirements, however, you might be better off using a full-fledged Excel library. The one from Aspose (Aspose.Cells) has worked great for me in the past, and has both .NET and Java versions.

Answer 2

I've used CarlosAg Excel Xml Writer Library, which did the trick for my (simple) needs.

Answer 3

Apache POI (free/open source) http://poi.apache.org/ works well for Java for reading/writing Excel files. You can arrange all of the data and then just open Excel and create the chart. You cannot create the charts directly from POI.

If you're looking for more commercial support, @Ian Varley mentioned Aspose, which is also a very good library (I'm not sure if it will let you create the charts directly).

If you're not tied to Java, it should be pretty easy to write this in VBA (yes, VBA) since it has tight Excel integration and you can create the charts directly from the script.

Answer 4

JExcel Api Gives you total control on what you want to write, it's powerful, fast and pure java.

I suggest it, here's a tutorial.

Answer 5

How about c# and Open XML SDK 2.0, have a look at http://openxmldeveloper.org/ for samples

Answer 6

SmartXLS also have both java and .Net version,it is efficient and have rich features.

Answer 7

Any reason not to use Excel VBA?