Java code to convert xlsx & xls to csv
Note : This Program uses XSSFWorkbook for xlsx and HSSFWorkbook for xlx.
package com.simplecode.excel;
import java.io.*;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 ExcelToCSV {
static void convertToXlsx(File inputFile, File outputFile)
{
// For storing data into CSV files
StringBuffer cellValue = new StringBuffer();
try
{
FileOutputStream fos = new FileOutputStream(outputFile);
// Get the workbook instance for XLSX file
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));
// Get first sheet from the workbook
XSSFSheet sheet = wb.getSheetAt(0);
Row row;
Cell cell;
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
cell = cellIterator.next();
switch (cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
cellValue.append(cell.getBooleanCellValue() + ",");
break;
case Cell.CELL_TYPE_NUMERIC:
cellValue.append(cell.getNumericCellValue() + ",");
break;
case Cell.CELL_TYPE_STRING:
cellValue.append(cell.getStringCellValue() + ",");
break;
case Cell.CELL_TYPE_BLANK:
cellValue.append("" + ",");
break;
default:
cellValue.append(cell + ",");
}
}
}
fos.write(cellValue.toString().getBytes());
fos.close();
}
catch (Exception e)
{
System.err.println("Exception :" + e.getMessage());
}
}
static void convertToXls(File inputFile, File outputFile)
{
// For storing data into CSV files
StringBuffer cellDData = new StringBuffer();
try
{
FileOutputStream fos = new FileOutputStream(outputFile);
// Get the workbook instance for XLS file
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
// Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
Cell cell;
Row row;
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext())
{
cell = cellIterator.next();
switch (cell.getCellType())
{
case Cell.CELL_TYPE_BOOLEAN:
cellDData.append(cell.getBooleanCellValue() + ",");
break;
case Cell.CELL_TYPE_NUMERIC:
cellDData.append(cell.getNumericCellValue() + ",");
break;
case Cell.CELL_TYPE_STRING:
cellDData.append(cell.getStringCellValue() + ",");
break;
case Cell.CELL_TYPE_BLANK:
cellDData.append("" + ",");
break;
default:
cellDData.append(cell + ",");
}
}
}
fos.write(cellDData.toString().getBytes());
fos.close();
}
catch (FileNotFoundException e)
{
System.err.println("Exception" + e.getMessage());
}
catch (IOException e)
{
System.err.println("Exception" + e.getMessage());
}
}
public static void main(String[] args)
{
File inputFile = new File("C:\input.xls");
File outputFile = new File("C:\output1.csv");
File inputFile2 = new File("C:\input.xlsx");
File outputFile2 = new File("C:\output2.csv");
convertToXls(inputFile, outputFile);
convertToXlsx(inputFile2, outputFile2);
}
}
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
 |
|
Read More
Java Code to Convert XLSX to CSV Files
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<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> 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. Read More
Converting XLS to CSV files Using Java
package com.simplecode.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
class XlstoCSV
{
static void xls(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 XLS file
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
// Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
Cell cell;
Row row;
// Iterate through each rows from first sheet
Iterator<Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// For each row, iterate through each columns
Iterator<Cell> 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 + ",");
}
data.append('\n');
}
}
fos.write(data.toString().getBytes());
fos.close();
}
catch (FileNotFoundException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}
public static void main(String[] args)
{
File inputFile = new File("C:\test.xls");
File outputFile = new File("C:\output.csv");
xls(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
Read More
Read / Write CSV file in Java using opencsv library
We often need to read data from a CSV file to do some manipulation. Most of the examples I have seen uses StringTokenizer to read the CSV file but that has certain limitations. It cannot read a CSV file properly if the data present in the CSV has a comma in them. We can avoid this issue by using a open source project called Opencsv
You can use this library to Create, Read and write CSV files. The best part of OpenCSV parser is, it takes a CSV file and map the result data to a Java Bean object.
Please download and add Open Csv.jar in your class path.
Let’s get started.
1. Reading CSV file in Java
We will use following CSV sample file for this example:
File: Book.csv
Product No:, Product Name :,Price :,Quantity : 1,"Core Java, A cumulative approach",300 $,30 2,Complete reference,200 $,30 3,Struts 2 Black Book,85 $,30
File : ParseCSVFile.java
package com.simplecode.csv;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import au.com.bytecode.opencsv.CSVReader;
public class ParseCSVFileReadLineByLine
{
public static void main(String[] args)
{
String filename = "C:\Book1.csv";
ParseCSVFileReadLineByLine parseCSVFile = new ParseCSVFileReadLineByLine();
System.out.println("Starting to parse CSV file using opencsv");
parseCSVFile.parseUsingOpenCSV(filename);
}
private void parseUsingOpenCSV(String filename)
{
CSVReader reader;
try
{
reader = new CSVReader(new FileReader(filename));
String[] row;
while ((row = reader.readNext()) != null)
{
for (int i = 0; i < row.length; i++)
{
// display CSV values
System.out.println("Cell column index: " + i);
System.out.println("Cell Value: " + row[i]);
System.out.println("-------------");
}
}
}
catch (FileNotFoundException e)
{
System.err.println(e.getMessage());
}
catch (IOException e)
{
System.err.println(e.getMessage());
}
}
}
In above code, the readNext() method of CSVReader class to read CSV file line by line. It returns a String array for each value in row.
Recommended reading:
It is also possible to read full CSV file entierly by using readAll() method.
package com.simplecode.csv;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.List;
import au.com.bytecode.opencsv.CSVReader;
public class ParseCSVFileReadAll
{
public static void main(String[] args)
{
String filename = "C:\Book.csv";
ParseCSVFileReadAll parseCSVFile = new ParseCSVFileReadAll();
System.out.println("Starting to parse CSV file using opencsv");
parseCSVFile.parseUsingOpenCSV(filename);
}
private void parseUsingOpenCSV(String filename)
{
CSVReader reader;
try
{
reader = new CSVReader(new FileReader(filename));
String[] row;
List<?> content = reader.readAll();
for (Object object : content)
{
row = (String[]) object;
for (int i = 0; i < row.length; i++)
{
// display CSV values
System.out.println("Cell column index: " + i);
System.out.println("Cell Value: " + row[i]);
System.out.println("-------------");
}
}
}
catch (FileNotFoundException e)
{
System.err.println(e.getMessage());
}
catch (IOException e)
{
System.err.println(e.getMessage());
}
}
}
The readAll() method returns a List of String[] for given CSV file.
Also Read:
2. Mapping CSV with Java beans
OpenCSV parser take a CSV file and map the result data to a Java Bean object. For example we created a Java bean to store Product information.
File : ProductDetail.java
package com.simplecode.csv;
public class ProductDetail
{
String productNumber = null;
String productName = null;
String price = null;
String quantity = null;
public String getProductNumber()
{
return productNumber;
}
public void setProductNumber(String productNumber)
{
this.productNumber = productNumber;
}
public String getProductName()
{
return productName;
}
public void setProductName(String productName)
{
this.productName = productName;
}
public String getPrice()
{
return price;
}
public void setPrice(String price)
{
this.price = price;
}
public String getQuantity()
{
return quantity;
}
public void setQuantity(String quantity)
{
this.quantity = quantity;
}
}
Now we can map this bean with Opencsv and read the CSV file. Check out below example file:
File : ParseCSV2Bean.java
package com.simplecode.csv;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.util.List;
import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.bean.ColumnPositionMappingStrategy;
import au.com.bytecode.opencsv.bean.CsvToBean;
public class ParseCSV2Bean
{
public static void main(String[] args)
{
String filename = "C:\Book1.csv";
ParseCSV2Bean parseCSVFile = new ParseCSV2Bean();
System.out.println("Starting to parse CSV file and map to Java Bean");
parseCSVFile.parseCSVtoBean(filename);
}
private void parseCSVtoBean(String filename)
{
try
{
// To ignore Processing of 1st row
CSVReader reader = new CSVReader(new FileReader(filename), ',', '\"', 1);
ColumnPositionMappingStrategy<ProductDetail> mappingStrategy
= new ColumnPositionMappingStrategy<ProductDetail>();
mappingStrategy.setType(ProductDetail.class);
// the fields to bind do in your JavaBean
String[] columns = new String[] {"productNumber","productName","price","quantity"};
mappingStrategy.setColumnMapping(columns);
CsvToBean<ProductDetail> csv = new CsvToBean<ProductDetail>();
List<ProductDetail> productList = csv.parse(mappingStrategy, reader);
for (int i = 0; i < productList.size(); i++)
{
ProductDetail productDetail = productList.get(i);
// display CSV values
System.out.println("Product No : " + productDetail.getProductNumber());
System.out.println("Product Name : " + productDetail.getProductName());
System.out.println("Price: " + productDetail.getPrice());
System.out.println("Quandity: " + productDetail.getQuantity());
System.out.println("------------------------------");
}
}
catch (FileNotFoundException e)
{
System.err.println(e.getMessage());
}
}
}
The methodsetColumnMapping is used to map individual property of Java bean to the CSV position. In this example we map first CSV value to productNumber attribute and next to productName, and 3rd and 4th as price and quantity.
3. Writing CSV file in Java
Simple example to write one line in CSV file.
String csv = "C:\output.csv";
CSVWriter writer = new CSVWriter(new FileWriter(csv));
String [] fruits= "Apple,Orange,PineApple".split(",");
writer.writeNext(fruits);
writer.close();
We created object of class CSVWriter and called its writeNext() method. The writeNext() methods takes String [] as argument.
You can also write a List of String[] to CSV entirely. Following is code snippet for that.
String csv = "C:\output.csv";
CSVWriter writer = new CSVWriter(new FileWriter(csv));
List<String[]> database = new ArrayList<String[]>();
database.add(new String[] {"DB2", "Enterprise Database"});
database.add(new String[] {"PostgreSQL", "Open Source Enterprise Database"});
database.add(new String[] {"MySQL", "Open Source Database"});
writer.writeAll(database);
writer.close();
We used writeAll() method of class CSVWriter to write a List of String[] as CSV file.
4. Dumping SQL Table as CSV
OpenCSV also provides support to dump data from SQL table directly to CSV. For this we need ResultSet object. Following API can be used to write data to CSV from ResultSet.
java.sql.ResultSet rSet = getResultSet(); writer.writeAll(rSet, includeTableName);
The writeAll(ResultSet, boolean) method is utilized for this. The first argument is the ResultSet which you want to write to CSV file. And the second argument is boolean which represents whether you want to write table column names to file or not.
5. Reference