Java – How to load old Microsoft Office XML file (Excel) using Java

exceljavams-officexml

I'm not able to load an Excel file in the older Office XML format (think Office 2002 or 2003 version) into Java. I tried JXL and Apache's POI (version 3.7). POI doesn't work since it appears to want the newer Office .xlsx format.

Here's an example of the older Office XML format.

One can generate a similar XML file from MS Excel 2010 by saving the workbook as the format "XML Spreadsheet 2003"?

Are there any open-source Java libraries that will load the XMLSS format? Otherwise I have no choice but to write a custom parser: read the XML file then interpret the cell tags to build out the cell matrix. In this XML format, any rows with empty cell values are skipped, the next cell with data positioned with an index attribute that acts like an offset in the columns, I assume to save space in the XML file.

Best Answer

The format is called SpreadsheetML (do not confuse with .xlsx which is also xml-based), a library called Xelem can handle it:

import nl.fountain.xelem.excel.Workbook;
import nl.fountain.xelem.lex.ExcelReader;
//...
ExcelReader reader = new ExcelReader();
Workbook xlWorkbook = reader.getWorkbook("c:\\my\\spreadsheet.xml");
System.out.println(xlWorkbook.getSheetNames());

Related Solutions

C# – How to create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office

You can use a library called ExcelLibrary. It's a free, open source library posted on Google Code:

ExcelLibrary

This looks to be a port of the PHP ExcelWriter that you mentioned above. It will not write to the new .xlsx format yet, but they are working on adding that functionality in.

It's very simple, small and easy to use. Plus it has a DataSetHelper that lets you use DataSets and DataTables to easily work with Excel data.

ExcelLibrary seems to still only work for the older Excel format (.xls files), but may be adding support in the future for newer 2007/2010 formats.

You can also use EPPlus, which works only for Excel 2007/2010 format files (.xlsx files). There's also NPOI which works with both.

There are a few known bugs with each library as noted in the comments. In all, EPPlus seems to be the best choice as time goes on. It seems to be more actively updated and documented as well.

Also, as noted by @АртёмЦарионов below, EPPlus has support for Pivot Tables and ExcelLibrary may have some support (Pivot table issue in ExcelLibrary)

Here are a couple links for quick reference:
ExcelLibrary - GNU Lesser GPL
EPPlus - GNU (LGPL) - No longer maintained
EPPlus 5 - Polyform Noncommercial - Starting May 2020
NPOI - Apache License

Here some example code for ExcelLibrary:

Here is an example taking data from a database and creating a workbook from it. Note that the ExcelLibrary code is the single line at the bottom:

//Create the data set and table
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");

//Set the locale for each
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;

//Open a DB connection (in this example with OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();

//Create a query and fill the data table with the data from the DB
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();

adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();

//Add the table to the data set
ds.Tables.Add(dt);

//Here's the easy part. Create the Excel worksheet from the data set
ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

Creating the Excel file is as easy as that. You can also manually create Excel files, but the above functionality is what really impressed me.

Java – How to create a Java string from the contents of a file

Read all text from a file

Java 11 added the readString() method to read small files as a String, preserving line terminators:

String content = Files.readString(path, StandardCharsets.US_ASCII);

For versions between Java 7 and 11, here's a compact, robust idiom, wrapped up in a utility method:

static String readFile(String path, Charset encoding)
  throws IOException
{
  byte[] encoded = Files.readAllBytes(Paths.get(path));
  return new String(encoded, encoding);
}

Read lines of text from a file

Java 7 added a convenience method to read a file as lines of text, represented as a List<String>. This approach is "lossy" because the line separators are stripped from the end of each line.

List<String> lines = Files.readAllLines(Paths.get(path), encoding);

Java 8 added the Files.lines() method to produce a Stream<String>. Again, this method is lossy because line separators are stripped. If an IOException is encountered while reading the file, it is wrapped in an UncheckedIOException, since Stream doesn't accept lambdas that throw checked exceptions.

try (Stream<String> lines = Files.lines(path, encoding)) {
  lines.forEach(System.out::println);
}

This Stream does need a close() call; this is poorly documented on the API, and I suspect many people don't even notice Stream has a close() method. Be sure to use an ARM-block as shown.

If you are working with a source other than a file, you can use the lines() method in BufferedReader instead.

Memory utilization

The first method, that preserves line breaks, can temporarily require memory several times the size of the file, because for a short time the raw file contents (a byte array), and the decoded characters (each of which is 16 bits even if encoded as 8 bits in the file) reside in memory at once. It is safest to apply to files that you know to be small relative to the available memory.

The second method, reading lines, is usually more memory efficient, because the input byte buffer for decoding doesn't need to contain the entire file. However, it's still not suitable for files that are very large relative to available memory.

For reading large files, you need a different design for your program, one that reads a chunk of text from a stream, processes it, and then moves on to the next, reusing the same fixed-sized memory block. Here, "large" depends on the computer specs. Nowadays, this threshold might be many gigabytes of RAM. The third method, using a Stream<String> is one way to do this, if your input "records" happen to be individual lines. (Using the readLine() method of BufferedReader is the procedural equivalent to this approach.)

Character encoding

One thing that is missing from the sample in the original post is the character encoding. There are some special cases where the platform default is what you want, but they are rare, and you should be able justify your choice.

The StandardCharsets class defines some constants for the encodings required of all Java runtimes:

String content = readFile("test.txt", StandardCharsets.UTF_8);

The platform default is available from the Charset class itself:

String content = readFile("test.txt", Charset.defaultCharset());

Note: This answer largely replaces my Java 6 version. The utility of Java 7 safely simplifies the code, and the old answer, which used a mapped byte buffer, prevented the file that was read from being deleted until the mapped buffer was garbage collected. You can view the old version via the "edited" link on this answer.