Using Apache POI to create an Excel .xlsx spreadsheet

[Phillip Powell]

I am trying to write a Java program that will convert database table data to an Excel "*.xlsx" spreadsheet, but I have run into nothing but problems.

Lots of problems.

I apparently am running into an endless array of jar dependencies that poi-5.0.0.jar, poi-ooxml-full.jar, ooxml-schemas-1.4.jar, XMLBeans, Saxon, Log4J, etc., can't solve.

my error:

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/POIXMLTypeLoader
at org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook$Factory.newInstance(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onWorkbookCreate(XSSFWorkbook.java:452)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:255)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:249)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:237)
at com.dandylabs.DandyLabsGalleryViewsXLSGenerator.createHeader(DandyLabsGalleryViewsXLSGenerator.java:126)
at com.dandylabs.DandyLabsGalleryViewsXLSGenerator.toXLS(DandyLabsGalleryViewsXLSGenerator.java:324)
at com.dandylabs.DandyLabsGalleryViewsXLSGenerator.perform(DandyLabsGalleryViewsXLSGenerator.java:259)
at com.dandylabs.DandyLabsGalleryViewsXLSGenerator.main(DandyLabsGalleryViewsXLSGenerator.java:359)
Caused by: java.lang.ClassNotFoundException: org.apache.poi.POIXMLTypeLoader
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:602)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
... 9 more

It's just a simple method so far to create the spreadsheet workbook, sheet, and header, but it fails:

private void createHeader() throws EncryptedDocumentException, IOException, Exception {
		this.workbook = new XSSFWorkbook();
		
		this.sheet = this.workbook.createSheet(DandyLabsGalleryViewsXLSGenerator.SHEET_NAME);
		this.sheet.setColumnWidth(0, 6000);
		this.sheet.setColumnWidth(1, 4000);
		
		Row header = this.sheet.createRow(0);
		CellStyle headerStyle = this.workbook.createCellStyle();
		headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
		headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	
		Font font = this.workbook.createFont();
		font.setFontName(DandyLabsGalleryViewsXLSGenerator.FONT);
		font.setFontHeightInPoints((short) 16);
		font.setBold(true);
		headerStyle.setFont(font);

		Cell headerCell = header.createCell(0);
		headerCell.setCellValue(DandyLabsGalleryViewsXLSGenerator.HEADERS[0]);
		headerCell.setCellStyle(headerStyle);

		headerCell = header.createCell(1);
		headerCell.setCellValue(DandyLabsGalleryViewsXLSGenerator.HEADERS[1]);
		headerCell.setCellStyle(headerStyle);
		
		headerCell = header.createCell(2);
		headerCell.setCellValue(DandyLabsGalleryViewsXLSGenerator.HEADERS[2]);
		headerCell.setCellStyle(headerStyle);
	}

Should I give up and revert to an ".xls" Excel 97 format instead? Or not bother trying at all?

Thanks

[Carey Brown]

This worked for me so I suspect DandyLabs. What is that?

import java.io.File;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Phillip
{
	XSSFWorkbook workbook;

	public static void main( String[] args ) throws Exception
	{
		new Phillip().createHeader();
	}
	
	private void createHeader() throws Exception
	{
		workbook = new XSSFWorkbook();

		var sheet = this.workbook.createSheet( "A_SHEET" );
		sheet.setColumnWidth( 0, 6000 );
		sheet.setColumnWidth( 1, 4000 );

		Row header = sheet.createRow( 0 );
		XSSFCellStyle headerStyle = this.workbook.createCellStyle();
		headerStyle.setFillForegroundColor( IndexedColors.LIGHT_BLUE.getIndex() );
		headerStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );

		XSSFFont font = workbook.createFont();
		font.setFontName( XSSFFont.DEFAULT_FONT_NAME );
		font.setFontHeightInPoints( (short)16 );
		font.setBold( true );
		headerStyle.setFont( font );

		Cell headerCell = header.createCell( 0 );
		headerCell.setCellValue( "AA" );
		headerCell.setCellStyle( headerStyle );

		headerCell = header.createCell( 1 );
		headerCell.setCellValue( "BB" );
		headerCell.setCellStyle( headerStyle );

		headerCell = header.createCell( 2 );
		headerCell.setCellValue( "CC" );
		headerCell.setCellStyle( headerStyle );
		
		FileOutputStream	os = new FileOutputStream( new File("Phillip.xlsx" ) );
		workbook.write( os );
		os.close();
	}
}

[Phillip Powell]

Carey Brown wrote:This worked for me so I suspect DandyLabs. What is that?

What jar files were you obligated to include in your classpath?

[Phillip Powell]

Phillip Powell wrote:

Carey Brown wrote:This worked for me so I suspect DandyLabs. What is that?

What jar files were you obligated to include in your classpath?

Because I am including a plethora of Apache POI jar files, and it still never seems enough.  If I use HSSFWorkbook instead of XSSFWorkbook, things work better, but otherwise I get this error, and I have this class in my poi-5.0.0.jar file:

Caused by: java.lang.ClassNotFoundException: org.apache.poi.POIXMLTypeLoader
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:602)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
... 8 more

[Paul Clapham]

One post I found elsewhere said

Stack Overflow wrote:... it seems that in new versions of Apache POI, a reference to org.apache.poi.POIXMLTypeLoader persists, in new versions this class was moved to org.apache.poi.ooxml.POIXMLTypeLoader...

However, if you're using an IDE then usually it will tell you that "POIXMLTypeLoader" isn't imported (remove its import statement) and you can click something to have it find the correct package from the classpath.

[Carey Brown]

commons-collections4-4.1.jar
poi-3.17.jar
poi-3.8-20120326.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-5.1.3.jar

granted these are somewhat dated

[Phillip Powell]

Carey Brown wrote:commons-collections4-4.1.jar
poi-3.17.jar
poi-3.8-20120326.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-5.1.3.jar

granted these are somewhat dated

The exact same jar files (mine being newer versions, but might scale back to older versions), and yet I am riddled with NoClassDefFoundError for everything from com.zaxxer.sparsebits.SparseBitSet to POIXMLTypeLoader to whatever happens next..

[Phillip Powell]

Paul Clapham wrote:One post I found elsewhere said

Stack Overflow wrote:... it seems that in new versions of Apache POI, a reference to org.apache.poi.POIXMLTypeLoader persists, in new versions this class was moved to org.apache.poi.ooxml.POIXMLTypeLoader...

However, if you're using an IDE then usually it will tell you that "POIXMLTypeLoader" isn't imported (remove its import statement) and you can click something to have it find the correct package from the classpath.

I gave up for now and reverted to using HSSFWorkbook and "*.xls", because I will never master this. And now I have this

class org.apache.poi.hssf.usermodel.HSSFPatriarch cannot be cast to class org.apache.poi.xssf.usermodel.XSSFDrawing (org.apache.poi.hssf.usermodel.HSSFPatriarch and org.apache.poi.xssf.usermodel.XSSFDrawing are in unnamed module of loader 'app')

[Carey Brown]

I did swap in "DEFAULT_TYPE_NAME". Don't know what difference that would have made.

[Phillip Powell]

Carey Brown wrote:I did swap in "DEFAULT_TYPE_NAME". Don't know what difference that would have made.

In my case it made no difference; no columns were resized, and I had to comment out large blocks of code to get it to work at all:

	public void toXLS() throws EncryptedDocumentException, IOException, Exception {
		this.workbook = new HSSFWorkbook();
		this.sheet = this.workbook.createSheet(DandyLabsGalleryViewsXLSGenerator.SHEET_NAME);
		this.sheet.setColumnWidth(0, 3000);
		//this.sheet.setColumnWidth(1, 26000);
		
		this.createHeader();
		
		CellStyle style = this.workbook.createCellStyle();
		style.setWrapText(true);
		Font font = this.workbook.createFont();
		//font.setFontName(DandyLabsGalleryViewsXLSGenerator.FONT_NAME);
		font.setFontName(XSSFFont.DEFAULT_FONT_NAME);
		font.setFontHeightInPoints((short) 11);
		style.setFont(font);
		Row row;
		Cell cell;
		GalleryViewBean bean;

		for (int i = 0; i < this.size(); i++) {
			bean = this.get(i);
			row = this.sheet.createRow(i + 1);
			
			// IMAGE
			cell = row.createCell(0);
			cell.setCellValue("");
			cell.setCellStyle(style);
			//this.addImage(bean.getFile());

			// IMAGE URL
			cell = row.createCell(1);
			cell.setCellFormula(DandyLabsGalleryViewsXLSGenerator.generateURLString(bean.getUrl()));
			cell.setCellStyle(style);
			
			// NUMBER OF VIEWS
			cell = row.createCell(2);
			cell.setCellValue(bean.getCount());
			cell.setCellStyle(style);
		}
		
		for (int i = 0; i < DandyLabsGalleryViewsXLSGenerator.HEADERS.length; i++) {
			this.sheet.autoSizeColumn(i);
		}
	}

[Paul Clapham]

Phillip Powell wrote:... and yet I am riddled with NoClassDefFoundError for everything from com.zaxxer.sparsebits.SparseBitSet to POIXMLTypeLoader to whatever happens next..

But this just sounds like your classpath hasn't been set correctly (or at all).

[Phillip Powell]

Paul Clapham wrote:

Phillip Powell wrote:... and yet I am riddled with NoClassDefFoundError for everything from com.zaxxer.sparsebits.SparseBitSet to POIXMLTypeLoader to whatever happens next..

But this just sounds like your classpath hasn't been set correctly (or at all).

I am using Eclipse, and my classpath has the following:

commons-collections4-4.3.jar
commons-compress-1.26.1.jar
dom4j-1.6.jar
HikariCP-5.1.0.jar
poi-5.0.0.jar
poi-ooxml-5.0.0.jar
poi-ooxml-schemas-3.6.jar
slf4j-api-1.7.36.jar
SparseBitSet-1.3.jar
stax-api-1.0.1.jar
xml-apis-1.4.01.wso2v1.jar
xmlbeans-5.2.0.jar

I don't know what I'm missing

[Paul Clapham]

Phillip Powell wrote:I am using Eclipse

Good. Then delete all of the import statements from your code. This will produce red lines under all of those POI classes. Hovering over the red lines will produce a drop-down which may include the option to add an import statement for the class. If there are two such options then you may have too much stuff in your classpath, and if there are none then your classpath needs the jar file which has that class. Or else its name is misspelled.

[Phillip Powell]

Good. Then delete all of the import statements from your code. This will produce red lines under all of those POI classes. Hovering over the red lines will produce a drop-down which may include the option to add an import statement for the class. If there are two such options then you may have too much stuff in your classpath, and if there are none then your classpath needs the jar file which has that class. Or else its name is misspelled.

Did all of that:

Deleted all import statements

Hovered over red lines to obtain required imports

Paired down all jar files in classpath to the bare minimum required

At this point it will compile, however, it still fails when attempting to do simply this:

this.workbook = new XSSFWorkbook();

Caused by: java.lang.ClassNotFoundException: org.apache.poi.POIXMLTypeLoader
at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:602)
at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
... 8 more

[Paul Clapham]

Stack Overflow wrote:... it seems that in new versions of Apache POI, a reference to org.apache.poi.POIXMLTypeLoader persists, in new versions this class was moved to org.apache.poi.ooxml.POIXMLTypeLoader...

So it looks like that could be a problem in the POI jars themselves. I'm using poi-4.1.2.jar and poi-ooxml-4.1.2.jar and poi-ooxml-schemas-4.1.2.jar myself, installed in my system in March 2020, with no problems. Maybe you could try those?

(I update my OSS jars periodically, but not that often. I updated Commons CSV last year and that was a pain because they improved the design and I had to change my code to match their changes. And they didn't improve the documentation to match.)

[Phillip Powell]

Paul Clapham wrote:

Stack Overflow wrote:... it seems that in new versions of Apache POI, a reference to org.apache.poi.POIXMLTypeLoader persists, in new versions this class was moved to org.apache.poi.ooxml.POIXMLTypeLoader...

So it looks like that could be a problem in the POI jars themselves. I'm using poi-4.1.2.jar and poi-ooxml-4.1.2.jar and poi-ooxml-schemas-4.1.2.jar myself, installed in my system in March 2020, with no problems. Maybe you could try those?

(I update my OSS jars periodically, but not that often. I updated Commons CSV last year and that was a pain because they improved the design and I had to change my code to match their changes. And they didn't improve the documentation to match.)

I wound up scaling back to older versions of poi, poi-ooxml, poi-ooxml-schemas, and xmlbeans, and no more errors!  However, I am getting warnings, but it does successfully run:

WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by org.apache.poi.util.DocumentHelper (file:/C:/Users/phill/OneDrive/Desktop/jar/poi-ooxml-3.17.jar) to constructor com.sun.org.apache.xerces.internal.util.SecurityManager()
WARNING: Please consider reporting this to the maintainers of org.apache.poi.util.DocumentHelper
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release

I am using

poi-3.17.jar
poi-ooxml-3.17.jar
poi-ooxml-schemas-3.17.jar
xmlbeans-2.3.0.jar

Not sure what I need to do about the console warnings other than, for now, live with it.  I still have a lot of cleanup to do with the class file, but thanks

[Paul Clapham]

In Java 9 the "reflection" access methods were restricted as part of the "modularization" of Java. So far, this just produces warnings, which are designed to prompt you to fix your code to do reflection differently.

Java 9 Illegal Reflective Access Warning

It's not your code so you can't do anything , except to upgrade to the later version in which this issue has presumably been fixed. Catch-22! But your other option is to ignore the warnings.

[Tim Moores]

These days any large library will have numerous dependencies. Trying to handle those manually is a sizeable task (as you have discovered), and I would strongly recommend not to do that. Instead, use a build system like Gradle or Maven (or even Ant with Ivy, although that's seriously outdated) that can do it for you.

For POI specifically, mixing jar files from different versions will very likely lead to problems.

I have used many version of POI over the last 20+ years, and have found it to have excellent backward compatibility. So in general there's no reason not to use the latest release version.

[Phillip Powell]

Paul Clapham wrote:In Java 9 the "reflection" access methods were restricted as part of the "modularization" of Java. So far, this just produces warnings, which are designed to prompt you to fix your code to do reflection differently.

Java 9 Illegal Reflective Access Warning

It's not your code so you can't do anything , except to upgrade to the later version in which this issue has presumably been fixed. Catch-22! But your other option is to ignore the warnings.

I finally got it stable enough to not throw Runtime errors (just warnings), however, the sheet doesn't auto resize to accommodate for long data, and the images embedded are overlapping over each other and subsequent rows and columns.  In short, it's a mess.

       private void addImage(File file, int row, int col) throws IOException {
		if (!FileUtilities.isFile(file)) {
			return;
		}
		
		InputStream in = FileUtils.openInputStream(file);
		byte[] imageBytes = IOUtils.toByteArray(in);
		in.close();
		int id = this.workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
		CreationHelper helper = this.workbook.getCreationHelper();
		XSSFDrawing drawing = (XSSFDrawing) this.sheet.createDrawingPatriarch();
		XSSFClientAnchor anchor = (XSSFClientAnchor) helper.createClientAnchor();
		anchor.setDx1(DandyLabsGalleryViewsXLSGenerator.IMAGE_WIDTH / 2 * XSSFShape.EMU_PER_PIXEL);
		anchor.setDx2(DandyLabsGalleryViewsXLSGenerator.IMAGE_WIDTH / 2 * XSSFShape.EMU_PER_PIXEL);
		anchor.setDy1(DandyLabsGalleryViewsXLSGenerator.IMAGE_HEIGHT / 2 * XSSFShape.EMU_PER_PIXEL);
		anchor.setDy1(DandyLabsGalleryViewsXLSGenerator.IMAGE_HEIGHT / 2 * XSSFShape.EMU_PER_PIXEL);
		anchor.setRow1(row);
		anchor.setCol1(col);
		anchor.setRow2(row + 1);
		anchor.setCol2(col + 1);
		XSSFPicture picture = drawing.createPicture(anchor, id);
		picture.resize();
	}

       @Override
	public void toFile() throws IOException, Exception {
		FileOutputStream out = new FileOutputStream(DandyLabsGalleryViewsXLSGenerator.XLS_FILE);
		this.workbook.write(out);
		this.workbook.close();
		out.close();
	}

	@Override
	public void toXLS() throws EncryptedDocumentException, IOException, Exception {
		this.workbook = new XSSFWorkbook();
		this.sheet = (XSSFSheet) this.workbook.createSheet(DandyLabsGalleryViewsXLSGenerator.SHEET_NAME);
		//this.sheet.setColumnWidth(0, 3000);
		//this.sheet.setColumnWidth(1, 26000);
		
		this.createHeader();
		
		CellStyle style = (XSSFCellStyle) this.workbook.createCellStyle();
		style.setWrapText(false);
		Font font = (XSSFFont) this.workbook.createFont();
		font.setFontName(XSSFFont.DEFAULT_FONT_NAME);
		font.setFontHeightInPoints((short) 11);
		style.setFont(font);
		Row row;
		Cell cell;
		GalleryViewBean bean;

		for (int i = 0; i < this.size(); i++) {
			bean = this.get(i);
			row = (XSSFRow) this.sheet.createRow(i + 1);
			
			// IMAGE
			cell = (XSSFCell) row.createCell(0);
			cell.setCellStyle(style);
			this.addImage(bean.getFile(), i + 1, 0);

			// IMAGE URL
			cell = row.createCell(1);
			cell.setCellFormula(DandyLabsGalleryViewsXLSGenerator.generateURLString(bean.getUrl()));
			cell.setCellStyle(style);
			
			// NUMBER OF VIEWS
			cell = row.createCell(2);
			cell.setCellValue(bean.getCount());
			cell.setCellStyle(style);
			
			// ADD ONE MORE ROW TO MAKE ROOM FOR IMAGE
			this.sheet.createRow(i + 2);
			i++;
		}
		
		for (int i = 0; i < DandyLabsGalleryViewsXLSGenerator.HEADERS.length; i++) {
			this.sheet.autoSizeColumn(i);
		}
	}