Pages Navigation Menu

Coding is much easier than you think

Java Code to Convert XLSX to CSV Files

Posted by in CSV, Java, Java Excel | 19 comments

 

Note : xlsx file should contain only data in cells. No image and any other media element. This program has been developed only for cell data

 


package com.simplecode.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class XlsxtoCSV {

	static void xlsx(File inputFile, File outputFile) {
		// For storing data into CSV files
		StringBuffer data = new StringBuffer();
		try {
			FileOutputStream fos = new FileOutputStream(outputFile);

			// Get the workbook object for XLSX file
	       XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));

			// Get first sheet from the workbook
			XSSFSheet sheet = wBook.getSheetAt(0);
			Row row;
			Cell cell;

			// Iterate through each rows from first sheet
			Iterator rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {
				row = rowIterator.next();

				// For each row, iterate through each columns
				Iterator cellIterator = row.cellIterator();
				while (cellIterator.hasNext()) {

					cell = cellIterator.next();

					switch (cell.getCellType()) {
					case Cell.CELL_TYPE_BOOLEAN:
						data.append(cell.getBooleanCellValue() + ",");

						break;
					case Cell.CELL_TYPE_NUMERIC:
						data.append(cell.getNumericCellValue() + ",");

						break;
					case Cell.CELL_TYPE_STRING:
						data.append(cell.getStringCellValue() + ",");
						break;

					case Cell.CELL_TYPE_BLANK:
						data.append("" + ",");
						break;
					default:
						data.append(cell + ",");

					}
				}
			}

			fos.write(data.toString().getBytes());
			fos.close();

		} catch (Exception ioe) {
			ioe.printStackTrace();
		}
	}
	public static void main(String[] args) {
		
		File inputFile = new File("C:\\test.xlsx");
		File outputFile = new File("C:\\output.csv");
		xlsx(inputFile, outputFile);
	}
}
To execute the above code you must have the following libraries
  • dom4j-1.1.jar
  • poi-3.7-20101029.jar
  • poi-ooxml-3.7-20101029.jar
  • poi-ooxml-schemas-3.7-20101029.jar
  • xmlbeans-2.3.0.jar

Let me know if you face any issues.

19 Comments

  1. yogesh sharma /

    Hi, i have try this and add data.append(“rn”); at the last when column are appended but last column added twice every time plz give some suggestion on this..

  2. SHIVANGI LAD /

    plz tell me location where i put my library’s jar file….

  3. ShantanuKamdi /

    Can we store data from excel(.xls) format to MS Access database directly???

    • Mohaideen Jamil /

      Fetch data from xls file into arrayList and load them into database via jdbc program

  4. anamika /

    Hello,
    Can we convert xlsm to xlsx using poi?

  5. Michael Schmidt /

    Hi Jamil,

    thanks for your work! I wanted to use your code but i receive the following exception:

    Exception in thread “AWT-EventQueue-0” java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet
    at gui.XlsxtoCSV.xlsx(Convert.java:23)
    at gui.Translator.jBtnSpeichernActionPerformed(Translator.java:155)
    at gui.Translator.access$1(Translator.java:139)
    at gui.Translator$3.actionPerformed(Translator.java:85)
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$400(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)
    Caused by: java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet
    at java.net.URLClassLoader$1.run(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)
    … 40 more

    I know that I might miss some library but I integrated all and also imported them in the code:
    [code]
    import java.awt.event.ActionEvent;
    import java.awt.event.ActionListener;
    import java.io.BufferedReader;
    import java.io.File;
    import java.io.FileReader;

    import javax.swing.JButton;
    import javax.swing.JFileChooser;
    import javax.swing.JLabel;
    import javax.swing.JTextField;
    import javax.swing.JTextPane;
    import javax.swing.WindowConstants;
    import javax.swing.SwingUtilities;
    import javax.swing.filechooser.FileNameExtensionFilter; [/code]

    Have you any idea what I am doing wrong?
    Thanks in advance!

    Michael

    • Michael Schmidt /

      Sry little mistake, this are the imported libraries:
      [code]
      import java.io.File;
      import java.io.FileInputStream;
      import java.io.FileOutputStream;
      import java.util.Iterator;

      import org.apache.poi.ss.usermodel.Cell;
      import org.apache.poi.ss.usermodel.Row;
      import org.apache.poi.xssf.usermodel.XSSFSheet;
      import org.apache.poi.xssf.usermodel.XSSFWorkbook;
      import org.apache.poi.xssf.usermodel.XSSFWorkbook; [/code]

      • Mohaideen Jamil /

        Add xmlbeans-2.3.0.jar file to your classpath.

        P.S, To execute the above code you must have the following libraries

        dom4j-1.1.jar
        poi-3.7-20101029.jar
        poi-ooxml-3.7-20101029.jar
        poi-ooxml-schemas-3.7-20101029.jar
        xmlbeans-2.3.0.jar

  6. Michael /

    Hello Jamil,
    thanks for your work. I tried to integrate this code but I receive an Exception:

    Exception in thread “AWT-EventQueue-0” java.lang.NoClassDefFoundError: org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet
    at gui.XlsxtoCSV.xlsx(Convert.java:23)

    It sounds like I am missing an library but I cannot solve the problem. Could you please help me?
    Thanks in advance.

    Michael

  7. Angel /

    thanks friend served me a lot

  8. saff /

    please can you help me i can’t find API form “import org.apache.poi.ss.usermodel.Cell;”

  9. sk /

    Hi Jamil,

    Thanks for reply. I followed step which you told. but getting same Error.

    • Jamil /

      Hi Sk,
      What is your system’s RAM size ?? .. for the above program the maximum amount of memory required is 1gb i.e for 65 thousand records.

      I guess your system has low RAM memory.

      Please check your RAM size, if its about 2gb then try to set it as -Xms64m – Xms1024m and execute the program.

      And after modifying the values , please restart the system.

  10. sk /

    Hello Jamil ,

    The above code working fine when xlsx is small file. but when xlsx is large that is 90MB it is giving following Exception:
    Exception in thread “main” java.lang.OutOfMemoryError: Java heap space
    at java.util.Arrays.copyOf(Unknown Source)
    at java.io.ByteArrayOutputStream.grow(Unknown Source)
    at java.io.ByteArrayOutputStream.ensureCapacity(Unknown Source)
    at java.io.ByteArrayOutputStream.write(Unknown Source)
    at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource$FakeZipEntry.(ZipInputStreamZipEntrySource.java:115)
    at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.(ZipInputStreamZipEntrySource.java:55)
    at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:82)
    at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:220)
    at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39)
    at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:186)
    at exceltocsv.xlsx(exceltocsv.java:20)
    at exceltocsv.main(exceltocsv.java:72)

    please can you help me on this .

    thank you

    • Jamil /

      Hi Sk,
      If Java program requires a large amount of memory, then JVM will begin to throw OutOfMemoryError.

      To avoid this error you have to increase the heap size of JVM.
      The details about Increase JVM heap size is available in this post

      Hope it helps you:)

      • sk /

        Hi Jamil,
        thanks for reply. when i am giving the size -Xms1024m -Xmx2000m then it is giving me following error:

        Error:Could not create the Java Virtual Machine.
        Error:A fatal exception has occurres.Program will exit.

        So i changed size to -Xms512m -Xmx1024m then getting same error as previous that is:

        Exception in thread “main” java.lang.OutOfMemoryError: Java heap space
        at java.util.Arrays.copyOf(Unknown Source)
        at java.io.ByteArrayOutputStream.toByteArray(Unknown Source)
        at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource$FakeZipEntry.(ZipInputStreamZipEntrySource.java:118)
        at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.(ZipInputStreamZipEntrySource.java:55)
        at org.apache.poi.openxml4j.opc.ZipPackage.(ZipPackage.java:82)
        at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:220)
        at org.apache.poi.util.PackageHelper.open(PackageHelper.java:39)
        at org.apache.poi.xssf.usermodel.XSSFWorkbook.(XSSFWorkbook.java:186)
        at exceltocsv.xlsx(exceltocsv.java:20)
        at exceltocsv.main(exceltocsv.java:72)

        So not getting how to assign size so that it will run without any exception.

        thank you.

        • Jamil /

          Hi Sk,

          I have updated the solution in the following post-Solution for “Could not create the Java Virtual Machine” issue

          For your note:

          • Do not set -Xmx to too small value
          • Set -Xms to a small value

          Setting -Xmx to small value mostly leads to OutOfMemoryErrors, because this is the maximum amount of memory you are allocating for Java and it cannot utilize memory beyond the set value.

          Also If you set -Xms to higher value you might run out of memory. So try to keep it to a small value like -Xms16m.