Using POI to create XLS from a C app

[Rob Y.]

I have a system written in C/C++ that produces PDF's (using ClibPDF)
from data in a special 'flagged CSV' file format. The flags provide
formatting info that allows me to generate pretty nice looking
tablular reports. There's also a control file that tells me how to
format report headers, insert logos, do pagination, etc.

Now a big client wants the data in excel files with all the formatting
intact (or as much as possible). I already support export as plain
CSV with all the formatting ripped out, but for some reason he prefers
his reports in Excel instead of as PDF's.

It seems like the only mature library around to produce XLS files on a
unix server is the jakarta-poi stuff. Okay, so I finally have a
reason to learn Java. So far, so good. But my problem is that I
already have a ton of code that knows how to decode and interpret the
source data for these reports, and that code is in C. Does anybody
have any good ideas on how to leverage that stuff?

Ideally, I'd structure the thing so that my existing C code controls
things and I'd replace all the calls to ClibPDF with appropriate calls
to POI, but I assume there's no easy way to do that (would a JNI work
in a case where both sides need to keep their data structures active
simultaneously?)

I considered somehow launching an external java app that would somehow
open a pipe (can you do that in Java?) and accept commands from the C
caller, which could then control it that way.

Similarly, I guess, I could produce a text file with a stream of
commands to do spreadsheet'y things, and then launch a java/poi app to
use that as its input. Don't know whether that would be easier or
harder than just biting the bullet and re-implementing my PDF
generator and 'flagged CSV' library in Java. But I need to keep the
PDF stuff too, and a rewrite would require maintaining two code bases.

Am I missing something, or am I just out of luck in wanting to use a
Java library from C?

Anybody know of a good C/C++ equivalent of POI for creating XLS's?

 

[Bo Vance]

I have a system written in C/C++ that produces PDF's (using ClibPDF)
from data in a special 'flagged CSV' file format. The flags provide
formatting info that allows me to generate pretty nice looking
tablular reports. There's also a control file that tells me how to
format report headers, insert logos, do pagination, etc.

Now a big client wants the data in excel files with all the formatting
intact (or as much as possible). I already support export as plain
CSV with all the formatting ripped out, but for some reason he prefers
his reports in Excel instead of as PDF's.

It seems like the only mature library around to produce XLS files on a
unix server is the jakarta-poi stuff. Okay, so I finally have a
reason to learn Java. So far, so good. But my problem is that I
already have a ton of code that knows how to decode and interpret the
source data for these reports, and that code is in C. Does anybody
have any good ideas on how to leverage that stuff?

Ideally, I'd structure the thing so that my existing C code controls
things and I'd replace all the calls to ClibPDF with appropriate calls
to POI, but I assume there's no easy way to do that (would a JNI work
in a case where both sides need to keep their data structures active
simultaneously?)

I considered somehow launching an external java app that would somehow
open a pipe (can you do that in Java?) and accept commands from the C
caller, which could then control it that way.

Similarly, I guess, I could produce a text file with a stream of
commands to do spreadsheet'y things, and then launch a java/poi app to
use that as its input. Don't know whether that would be easier or
harder than just biting the bullet and re-implementing my PDF
generator and 'flagged CSV' library in Java. But I need to keep the
PDF stuff too, and a rewrite would require maintaining two code bases.

Am I missing something, or am I just out of luck in wanting to use a
Java library from C?

Anybody know of a good C/C++ equivalent of POI for creating XLS's?

I'm not sure why so many people want to hand code this type of
application when there is Open Office, CrossOver, Wine etc.

 

[Bo Vance]

I'm not sure why so many people want to hand code this type of
application when there is Open Office, CrossOver, Wine etc.

OpenOffice SDK Examples: Java, C++, Basic, etc.
<http://api.openoffice.org/nonav/source/browse/~checkout~/api/odk/examples/examples.html>

iText for Java PDF probably.

 

[Arne Vajhøj]

I have a system written in C/C++ that produces PDF's (using ClibPDF)
from data in a special 'flagged CSV' file format. The flags provide
formatting info that allows me to generate pretty nice looking
tablular reports. There's also a control file that tells me how to
format report headers, insert logos, do pagination, etc.

Now a big client wants the data in excel files with all the formatting
intact (or as much as possible). I already support export as plain
CSV with all the formatting ripped out, but for some reason he prefers
his reports in Excel instead of as PDF's.

It seems like the only mature library around to produce XLS files on a
unix server is the jakarta-poi stuff. Okay, so I finally have a
reason to learn Java. So far, so good. But my problem is that I
already have a ton of code that knows how to decode and interpret the
source data for these reports, and that code is in C. Does anybody
have any good ideas on how to leverage that stuff?

Ideally, I'd structure the thing so that my existing C code controls
things and I'd replace all the calls to ClibPDF with appropriate calls
to POI, but I assume there's no easy way to do that (would a JNI work
in a case where both sides need to keep their data structures active
simultaneously?)

You can call C->Java via JNI.

It is a bit of work, but not that much worse than what you have
already coded.

I considered somehow launching an external java app that would somehow
open a pipe (can you do that in Java?) and accept commands from the C
caller, which could then control it that way.

You could use a socket for communication.

But I would go JNI.

Similarly, I guess, I could produce a text file with a stream of
commands to do spreadsheet'y things, and then launch a java/poi app to
use that as its input.

Messy in my opinion.

Anybody know of a good C/C++ equivalent of POI for creating XLS's?

Not XLS.

But WKS format is relative easy to generate and if it provides
enough formatting capabilities, then generating that from C could
be an option.

Arne

 

[Bo Vance]

But WKS format is relative easy to generate and if it provides
enough formatting capabilities, then generating that from C could
be an option.

Future trouble for wks in MS Office.
<http://support.microsoft.com/kb/938810/en-us>

 

[Rob Y.]

You can call C->Java via JNI.

It is a bit of work, but not that much worse than what you have
already coded.

Sounds interesting. What I don't get is that, presumably POI needs to
be instantiated and remain so for the duration of the process. If I
had a C app calling into POI via JNI, would that be possible? I guess
I'm assuming JNI needs to embed the Java interpreter in your C app (or
dynamically link to it), and that each Java call would encapsulate a
complete instance of the Java environment, which would have to clean
itself up upon completion of the JNI call.

Maybe it's more flexible than that? (actually, I always thought JNI
was to allow Java to call out to C, so maybe there's a lot more to it
than I know about)

Rob

 

[Arne Vajhøj]

Sounds interesting. What I don't get is that, presumably POI needs to
be instantiated and remain so for the duration of the process. If I
had a C app calling into POI via JNI, would that be possible? I guess
I'm assuming JNI needs to embed the Java interpreter in your C app (or
dynamically link to it), and that each Java call would encapsulate a
complete instance of the Java environment, which would have to clean
itself up upon completion of the JNI call.

Maybe it's more flexible than that? (actually, I always thought JNI
was to allow Java to call out to C, so maybe there's a lot more to it
than I know about)

JNI can also be used to call Java from C.

You can start the JVM and create objects that you can keep
over multiple invocations.

I can create a simple example if needed.

Arne

 

[Rob Y.]

JNI can also be used to call Java from C.

You can start the JVM and create objects that you can keep
over multiple invocations.

I can create a simple example if needed.

Arne

That would be great if you wouldn't mind. I assume I'd just use a
fairly high-level JNI interface, with just a few functions:

1. Set up a document - with an output path.
2. Feed in a cell with text and properties to the document.
3. Finalize it - produce the actual .xls file.

Don't need the specifics, I guess. Just enough of a headstart for a
good C programmer, but Java novice to use the JNI mechanism to build
such a C API.

Thanks,
Rob

 

[Arne Vajhøj]

That would be great if you wouldn't mind. I assume I'd just use a
fairly high-level JNI interface, with just a few functions:

1. Set up a document - with an output path.
2. Feed in a cell with text and properties to the document.
3. Finalize it - produce the actual .xls file.

Don't need the specifics, I guess. Just enough of a headstart for a
good C programmer, but Java novice to use the JNI mechanism to build
such a C API.

Here it comes.

Java code:

public class J {
private int v;
public int getV() {
return v;
}
public void setV(int v) {
this.v = v;
}
}

Now we want to do:

J o = new J();
o.setV(123);
System.out.println(o.getV());

in C, which is super simple but do show that state can
be kept:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

#include <jni.h>

JavaVM* create_jvm(JNIEnv **env, int mb, char *cp)
{
JavaVMOption options[2];
JavaVMInitArgs vm_args;
JavaVM *jvm;
int res;
char maxmemopt[16];
char classpathopt[1024];
sprintf(maxmemopt,"-Xmx%dm", mb);
sprintf(classpathopt,"-Djava.class.path=%s", cp);
options[0].optionString = maxmemopt;
options[1].optionString = classpathopt;
vm_args.version = JNI_VERSION_1_4;
vm_args.nOptions = 2;
vm_args.options = options;
vm_args.ignoreUnrecognized = JNI_FALSE;
res = JNI_CreateJavaVM(&jvm,(void **)env,&vm_args);
if(res < 0)
{
return NULL;
};
return jvm;
}

jclass load_class(JNIEnv *env, char *clznam)
{
char clznam2[128];
int i;
strcpy(clznam2, clznam);
for(i = 0; i < strlen(clznam2); i++)
{
if(clznam2 == '.')
{
clznam2 = '/';
}
}
return (*env)->FindClass(env, clznam2);
}

int main(int argc,char *argv[])
{
JNIEnv *env;
JavaVM *jvm;
jclass classptr;
jmethodID constructor;
jmethodID setmethod;
jmethodID getmethod;
jobject obj;
int v;
jvm = create_jvm(&env, 128, ".");
classptr = load_class(env, "J");
constructor = (*env)->GetMethodID(env, classptr, "<init>", "()V");
setmethod = (*env)->GetMethodID(env, classptr, "setV", "(I)V");
getmethod = (*env)->GetMethodID(env, classptr, "getV", "()I");
obj = (*env)->NewObject(env, classptr, constructor);
(*env)->CallVoidMethod(env, obj, setmethod, 123);
v = (*env)->CallIntMethod(env, obj, getmethod);
printf("%d\n", v);
(*env)->DeleteLocalRef(env, obj);
(*jvm)->DestroyJavaVM(jvm);
return 0;
}

For production code you would add a ton of error handling code.

Arne

 

[Rob Y.]

Looks promising.

To get started, I'm trying to just run some sample code (the
CreateCells.java sample from the poi source distro), and can't get it
to run. I was able to compile it, though.

I've essentially added all the .jar's in the poi distro to my
CLASSPATH (used symlinks to avoid the version-specific names):

..:$POI/poi.jar:$POI/poi-contrib.jar:$POI/poi-scratchpad.jar:$POI/lib/
log4j.jar:$POI/lib/junit.jar:$POI/lib/commons-logging.jar

but when I try to run the CreateCells.class, I get a bunch of errors I
don't understand:

$ java CreateCells
Exception in thread "main" java.lang.NoClassDefFoundError: CreateCells
(wrong name: org/apache/poi/hssf/usermodel/examples/CreateCells)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$100(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)

the 'wrong name:...' error is listing the path to my class minus the
top-level directory to the example source. The full path is:
~/xls/poi-3.1-FINAL/src/examples/src/org/apache/poi/hssf/
usermodel/examples/CreateCells

Sorry for the newbie questions, but I'm sure once I figure out enough
java to get some basic stuff running, I'll be able to take it from
there.

In case it matters, I'm running on RHEL 3:
java -version
java version "1.4.2_07"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_07-b05)
Java HotSpot(TM) Client VM (build 1.4.2_07-b05, mixed mode)

Thanks,
Rob

 

[Arne Vajhøj]

but when I try to run the CreateCells.class, I get a bunch of errors I
don't understand:

$ java CreateCells
Exception in thread "main" java.lang.NoClassDefFoundError: CreateCells
(wrong name: org/apache/poi/hssf/usermodel/examples/CreateCells)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(Unknown Source)
at java.security.SecureClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.defineClass(Unknown Source)
at java.net.URLClassLoader.access$100(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)

Sorry for the newbie questions, but I'm sure once I figure out enough
java to get some basic stuff running, I'll be able to take it from
there.

You are trying to run a class CreateCells with no package name,
java do find CreateCells.class but apperently it do have a package.

Classname in command line, package in source code and location of
files need to be consistent.

Arne

 

[Rob Y.]

You are trying to run a class CreateCells with no package name,

java do find CreateCells.class but apperently it do have a package.

Classname in command line, package in source code and location of
files need to be consistent.

Arne

Yeah, I just figured that out. It works when I run it with the fully-
qualified class name:

org.apache.poi.hssf.usermodel.examples.CreateCells

Now I can really start playing with it.

Thanks again,
Rob

 

[Rob Y.]

The class was not findable at runtime.

The class name is 'org.apache.poi.hssf.usermodel.examples.CreateCells'.

You didn't specify that in the command line.

Yeah, I figured that one out. Also had to add the parent directory of
org/apache/poi... to my CLASSPATH to get it to work.

Now I have another problem, though. I built a test program to call
POI as a JNI using Arne's hints. I'm able to build it, but when I try
to run it, I get a segfault in JNI_CreateJavaVM(&jvm,(void
**)env,&vm_args), which is the first Java-specific call in the app.
The code looks right - now what? Could this be a version 1.4 problem
that an upgrade might fix?

Here's a gdb session showing me stepping up to the JNI_CreateJavaVM
call and all the info the jvm prints out after the segfault:

(gdb)
26 vm_args.version = JNI_VERSION_1_4;
(gdb)
27 vm_args.nOptions = 2;
(gdb)
28 vm_args.options = options;
(gdb)
29 vm_args.ignoreUnrecognized = JNI_FALSE;
gdb) print options[0]
$1 = {optionString = 0xbfffc4f0 "-Xmx128m", extraInfo = 0x0}
gdb) print options[1]
$2 = {optionString = 0xbfffc0f0 "-Djava.class.path=/home/rob/xls/poi/
poi.jar:/home/rob/xls/poi/src/examples/src", extraInfo = 0x0}
((gdb)
30 res = JNI_CreateJavaVM(&jvm,(void **)env,&vm_args);
(gdb) n
[New Thread -1497216080 (LWP 12182)]
[New Thread -1497744464 (LWP 12183)]
[New Thread -1498272848 (LWP 12184)]

Program received signal SIGSEGV, Segmentation fault.
0xb2d96ff6 in ?? ()
(gdb)(gdb) c
Continuing.

Program received signal SIGSEGV, Segmentation fault.
0xb2d96ff6 in ?? ()
(gdb) c
Continuing.
[New Thread -1500898384 (LWP 12188)]
[New Thread -1501426768 (LWP 12189)]
[New Thread -1503659088 (LWP 12190)]
[New Thread -1505760336 (LWP 12191)]
[New Thread -1507861584 (LWP 12192)]
[New Thread -1509962832 (LWP 12193)]

Program received signal SIGSEGV, Segmentation fault.
0xb729c706 in alloc_object () from /usr/java/j2sdk1.4.2_07/jre/lib/
i386/server/libjvm.so
(gdb) c
Continuing.

Unexpected Signal : 11 occurred at PC=0xB729C706
Function=(null)
Library=/usr/java/j2sdk1.4.2_07/jre/lib/i386/server/libjvm.so

NOTE: We are unable to locate the function name symbol for the error
just occurred. Please refer to release documentation for
possible
reason and solutions.


Current Java thread:

Dynamic libraries:
08048000-08049000 r-xp 00000000 08:05 472697 /home/rob/xls/poi-3.1-
FINAL/jni/poijni
08049000-0804a000 rw-p 00000000 08:05 472697 /home/rob/xls/poi-3.1-
FINAL/jni/poijni
a5d4b000-a5e94000 r--s 00000000 08:05 1580234 /home/rob/xls/poi-3.1-
FINAL/poi-3.1-FINAL-20080629.jar
a5e94000-a5eb0000 r--s 00000000 08:08 51981 /usr/java/
j2sdk1.4.2_07/jre/lib/ext/sunjce_provider.jar
a5eb0000-a5f6c000 r--s 00000000 08:08 51982 /usr/java/
j2sdk1.4.2_07/jre/lib/ext/localedata.jar
a5f6c000-a5f79000 r--s 00000000 08:08 51979 /usr/java/
j2sdk1.4.2_07/jre/lib/ext/ldapsec.jar
a5f79000-a5f7c000 r--s 00000000 08:08 51978 /usr/java/
j2sdk1.4.2_07/jre/lib/ext/dnsns.jar
a68a2000-a6aa2000 r--p 00000000 08:08 311437 /usr/lib/locale/
locale-archive
b4d8f000-b52e8000 r--s 00000000 08:08 265009 /usr/java/
j2sdk1.4.2_07/jre/lib/charsets.jar
b52e8000-b52f9000 r--s 00000000 08:08 265008 /usr/java/
j2sdk1.4.2_07/jre/lib/jce.jar
b52f9000-b53d6000 r--s 00000000 08:08 265016 /usr/java/
j2sdk1.4.2_07/jre/lib/jsse.jar
b53d6000-b53ec000 r--s 00000000 08:08 265017 /usr/java/
j2sdk1.4.2_07/jre/lib/sunrsasign.jar
b5436000-b6ddf000 r--s 00000000 08:08 265018 /usr/java/
j2sdk1.4.2_07/jre/lib/rt.jar
b6ddf000-b6df3000 r-xp 00000000 08:08 183077 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/libzip.so
b6df3000-b6df6000 rw-p 00013000 08:08 183077 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/libzip.so
b6df6000-b6e16000 r-xp 00000000 08:08 183062 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/libjava.so
b6e16000-b6e18000 rw-p 0001f000 08:08 183062 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/libjava.so
b6e18000-b6e28000 r-xp 00000000 08:08 183076 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/libverify.so
b6e28000-b6e2a000 rw-p 0000f000 08:08 183076 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/libverify.so
b6e2a000-b6e2e000 rw-s 00000000 08:07 163842 /tmp/hsperfdata_rob/
12181
b6e2e000-b6e39000 r-xp 00000000 08:02 46957 /lib/
libnss_files-2.3.2.so
b6e39000-b6e3a000 rw-p 0000a000 08:02 46957 /lib/
libnss_files-2.3.2.so
b6e3a000-b6e42000 r-xp 00000000 08:08 494348 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/native_threads/libhpi.so
b6e42000-b6e43000 rw-p 00007000 08:08 494348 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/native_threads/libhpi.so
b6e44000-b6e51000 r-xp 00000000 08:02 81606 /lib/tls/
libpthread-0.60.so
b6e51000-b6e52000 rw-p 0000c000 08:02 81606 /lib/tls/
libpthread-0.60.so
b6e55000-b6e57000 r-xp 00000000 08:02 46937 /lib/libdl-2.3.2.so
b6e57000-b6e58000 rw-p 00001000 08:02 46937 /lib/libdl-2.3.2.so
b6e58000-b6e79000 r-xp 00000000 08:02 81604 /lib/tls/
libm-2.3.2.so
b6e79000-b6e7a000 rw-p 00020000 08:02 81604 /lib/tls/
libm-2.3.2.so
b6e7a000-b6e8c000 r-xp 00000000 08:02 46941 /lib/libnsl-2.3.2.so
b6e8c000-b6e8d000 rw-p 00011000 08:02 46941 /lib/libnsl-2.3.2.so
b6e8f000-b6fc0000 r-xp 00000000 08:02 81602 /lib/tls/
libc-2.3.2.so
b6fc0000-b6fc3000 rw-p 00130000 08:02 81602 /lib/tls/
libc-2.3.2.so
b6fc6000-b757a000 r-xp 00000000 08:08 297742 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/server/libjvm.so
b757a000-b75d4000 rw-p 005b3000 08:08 297742 /usr/java/
j2sdk1.4.2_07/jre/lib/i386/server/libjvm.so
b75e7000-b75e8000 r-xp 00000000 08:02 32792 /etc/libcwait.so
b75e8000-b75e9000 rw-p 00000000 08:02 32792 /etc/libcwait.so
b75eb000-b7600000 r-xp 00000000 08:02 46924 /lib/ld-2.3.2.so
b7600000-b7601000 rw-p 00015000 08:02 46924 /lib/ld-2.3.2.so

Heap at VM Abort:
Heap
def new generation total 576K, used 133K [0xa6ce0000, 0xa6d80000,
0xa7b10000)
eden space 512K, 26% used [0xa6ce0000, 0xa6d017f0, 0xa6d60000)
from space 64K, 0% used [0xa6d60000, 0xa6d60000, 0xa6d70000)
to space 64K, 0% used [0xa6d70000, 0xa6d70000, 0xa6d80000)
tenured generation total 1408K, used 0K [0xa7b10000, 0xa7c70000,
0xaece0000)
the space 1408K, 0% used [0xa7b10000, 0xa7b10000, 0xa7b10200,
0xa7c70000)
compacting perm gen total 16384K, used 965K [0xaece0000, 0xafce0000,
0xb2ce0000)
the space 16384K, 5% used [0xaece0000, 0xaedd1408, 0xaedd1600,
0xafce0000)

Local Time = Tue Oct 14 14:13:44 2008
Elapsed Time = 192
#
# HotSpot Virtual Machine Error : 11
# Error ID : 4F530E43505002EF
# Please report this error at
# http://java.sun.com/cgi-bin/bugreport.cgi
#
# Java VM: Java HotSpot(TM) Server VM (1.4.2_07-b05 mixed mode)
#
# An error report file has been saved as hs_err_pid12181.log.
# Please refer to the file for further information.
#

Program received signal SIGABRT, Aborted.
0xb75ebc32 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2
(gdb)

 

[Rob Y.]

Never mind. After much digging, I found out that SIGSEGV's are normal
in the JVM. Because I tried to run under gdb, and the SIGSEGV's
started happening when I stepped into JNI_CreateJavaVM, I figured
that's where it was blowing up, but that wasn't it.

After telling gdb to ignore the SIGSEGV's, I proceeded and found out
that I my load_class call was failing, and I hadn't put in an error
check yet. So on to normal debugging.

Sorry about that.