Pages Navigation Menu

Coding is much easier than you think

Java code to convert xlsx & xls to csv

Posted by in CSV, Java, Java Excel

 

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

 

dwd2
Download It – ExcelToCSV

  Read More

Java Code to Convert XLSX to CSV Files

Posted by in CSV, Java, Java Excel | 6 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<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

Posted by in CSV, Java, Java Excel

 

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 + ",");
			}
		}
	}

	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

Java Code to Read XLS and XLSX Files

Posted by in Java Excel | 4 comments

 

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 ExcelReader
{
static void readXlsx(File inputFile)
{
try
{
	// 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:
			System.out.println(cell.getBooleanCellValue());
			break;

		case Cell.CELL_TYPE_NUMERIC:
			System.out.println(cell.getNumericCellValue());
			break;

		case Cell.CELL_TYPE_STRING:
			System.out.println(cell.getStringCellValue());
			break;

		case Cell.CELL_TYPE_BLANK:
			System.out.println(" ");
			break;

		default:
			System.out.println(cell);

		}
		}
	}
}
catch (Exception e)
{
	System.err.println("Exception :" + e.getMessage());
}
}

static void readXls(File inputFile)
{
try
{
	// 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:
			System.out.println(cell.getBooleanCellValue());
			break;

		case Cell.CELL_TYPE_NUMERIC:
			System.out.println(cell.getNumericCellValue());
			break;

		case Cell.CELL_TYPE_STRING:
			System.out.println(cell.getStringCellValue());
			break;

		case Cell.CELL_TYPE_BLANK:
			System.out.println(" ");
			break;

		default:
			System.out.println(cell);
		}
		}
	}

}

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 inputFile2 = new File("C:\input.xlsx");
	readXls(inputFile);
	readXlsx(inputFile2);
}
}

 

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

 

dwd2
Download It – ExcelReader

  Read More

Read Excel with Java (Jexcel-api)

Posted by in Java, Java Excel

 
This article demonstrate how to read Excel files with the Java Excel API.
 
ExcelApi – one of a number of open-source java Excel API, designed for reading, writing, and editing of dynamic excel. Among its features are:

  • Reading from the book Excel 95, 97, 2000, XP, and 2003
  • Reading and writing formulas
  • Creating a list in Excel 2000
  • Support for formatting fonts, numbers and dates
  • Support colors, borders, and colors of cells
  • Changing existing books excel
  • Localization support
  • Copying charts
  • Copying and pasting drawings
  • Logging events

 

dwd2
Download – Java Excel library

 

package com.simplecode.reader;

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

  public static void main(String[] args) throws IOException {
    ReadExcel test = new ReadExcel();
    test.setInputFile("c:/temp/output.xls");
    test.read();
  }

  private String inputFile;

  public void setInputFile(String inputFile) {
    this.inputFile = inputFile;
  }

  public void read() throws IOException  {
    File inputWorkbook = new File(inputFile);
    Workbook w;
    try {
      w = Workbook.getWorkbook(inputWorkbook);
      // Get the first sheet
      Sheet sheet = w.getSheet(0);
      // Loop over first 10 column and lines

      for (int j = 0; j < sheet.getColumns(); j++) {
        for (int i = 0; i < sheet.getRows(); i++) {
          Cell cell = sheet.getCell(j, i);
          CellType type = cell.getType();
          if (type == CellType.LABEL) {
            System.out.println("I got a label "
                + cell.getContents());
          }

          if (type == CellType.NUMBER) {
            System.out.println("I got a number "
                + cell.getContents());
          }

        }
      }
    } catch (BiffException e) {
      e.printStackTrace();
    }
  }
}
Read More

Write Excel with Java (Jexcel-api)

Posted by in Java, Java Excel

 
This article demonstrate how to create Excel files with the Java Excel API.
 
ExcelApi – one of a number of open-source java Excel API, designed for reading, writing, and editing of dynamic excel. Among its features are:

  • Reading from the book Excel 95, 97, 2000, XP, and 2003
  • Reading and writing formulas
  • Creating a list in Excel 2000
  • Support for formatting fonts, numbers and dates
  • Support colors, borders, and colors of cells
  • Changing existing books excel
  • Localization support
  • Copying charts
  • Copying and pasting drawings
  • Logging events

 

dwd2
Download – Java Excel library

 
package com.simplecode.writer;

import java.io.File;
import java.io.IOException;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class WriteExcel {

	public static void main(String[] args) throws WriteException, IOException {
		WriteExcel test = new WriteExcel();
		test.setOutputFile("c:\temp\input.xls");
		test.write();
		System.out.println("Please check the result file under c:\temp\input.xls ");
	}

	private WritableCellFormat timesBoldUnderline;
	private WritableCellFormat times;
	private String inputFile;

	public void setOutputFile(String inputFile) {
		this.inputFile = inputFile;
	}

	public void write() throws IOException, WriteException {
		File file = new File(inputFile);
		WorkbookSettings wbSettings = new WorkbookSettings();

		wbSettings.setLocale(new Locale("en", "EN"));

		WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
		workbook.createSheet("Report", 0);
		WritableSheet excelSheet = workbook.getSheet(0);
		createLabel(excelSheet);
		createContent(excelSheet);

		workbook.write();
		workbook.close();
	}

	private void createLabel(WritableSheet sheet) throws WriteException {
		// Lets create a times font
		WritableFont times10pt = new WritableFont(WritableFont.TIMES, 10);
		// Define the cell format
		times = new WritableCellFormat(times10pt);
		// Lets automatically wrap the cells
		times.setWrap(true);

		// Create create a bold font with under lines
		WritableFont times10ptBoldUnderline = new WritableFont(
				WritableFont.TIMES, 10, WritableFont.BOLD, false,
				UnderlineStyle.SINGLE);
		timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
		// Lets automatically wrap the cells
		timesBoldUnderline.setWrap(true);

		CellView cv = new CellView();
		cv.setFormat(times);
		cv.setFormat(timesBoldUnderline);
		cv.setAutosize(true);

		// Write a few headers
		addCaption(sheet, 0, 0, "Header 1");
		addCaption(sheet, 1, 0, "This is another header");

	}

	private void createContent(WritableSheet sheet) throws WriteException,
			RowsExceededException {
		// Write a few number
		for (int i = 1; i < 10; i++) {
			// First column
			addNumber(sheet, 0, i, i + 10);
			// Second column
			addNumber(sheet, 1, i, i * i);
		}
		// Lets calculate the sum of it
		StringBuffer buf = new StringBuffer();
		buf.append("SUM(A2:A10)");
		Formula f = new Formula(0, 10, buf.toString());
		sheet.addCell(f);
		buf = new StringBuffer();
		buf.append("SUM(B2:B10)");
		f = new Formula(1, 10, buf.toString());
		sheet.addCell(f);

		// Now a bit of text
		for (int i = 12; i < 20; i++) {
			// First column
			addLabel(sheet, 0, i, "Boring text " + i);
			// Second column
			addLabel(sheet, 1, i, "Another text");
		}
	}

	private void addCaption(WritableSheet sheet, int column, int row, String s)
			throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, timesBoldUnderline);
		sheet.addCell(label);
	}

	private void addNumber(WritableSheet sheet, int column, int row,
			Integer integer) throws WriteException, RowsExceededException {
		Number number;
		number = new Number(column, row, integer, times);
		sheet.addCell(number);
	}

	private void addLabel(WritableSheet sheet, int column, int row, String s)
			throws WriteException, RowsExceededException {
		Label label;
		label = new Label(column, row, s, times);
		sheet.addCell(label);
	}
}

Let me know if you face any issues.
Happy learning :)

Read More
Page 1 of 212
SimpleCodeStuffs located at , India, Chennai . Reviewed by rated: 8.8 / 10