Skip to main content

How to convert a CSV file to XLSX

I wrote this utility for a friend of mine who wanted to convert large (~50MB) CSV files to XLSX.
The CSV files had comma "," as a delimiter.

I have used apache poi utility in this program.
https://www.apache.org/dyn/closer.lua/poi/release/RELEASE-NOTES.txt

This code uses SXSSFWorkbook class. The SXSSFWorkbook class uses "BigGridDemo" strategy, where only portions being processed are kept in memory. There are temporary files created which store the rest of the temporary data.

setCompressTempFiles() method allows these temp files to be compressed. The size of these files can get quite large.

The data to be converted was UTF-8 encoded data. So, we are using OutputStreamWriter, where we specify the encoding of the data. If you do not need the encoding, just remove that parameter from the constructor call of OutputStreamWriter.

The system where this ran, supported Java 7, so have not used features in java 8, such as try with exceptions.

import java.io.*;

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CSV_XLS {

 public static void main(String[] args){
  if(args==null || args.length<2){
   printMsg();
   System.exit(0);
  }
  String sourceFileName  = args[0];
  String targetFileName  = args[1];
  csvToXLSX(sourceFileName, targetFileName);
 }

 public static void printMsg(){
  System.out.println("---------0-----------------0---------");
  System.out.println("-----------------||------------------");
  System.out.println("---CSV To XLSX converter utility---");
  System.out.println("-------------------------------------");
  System.out.println("Please provide following 2 arguments : sourceFileName , targetFileName");
  System.out.println("-------------------------------------");
  System.out.println("-------------------------------------");

 }

 public static void csvToXLSX(String sourceFileName, String targetFileName) {
  BufferedReader br=null;
  SXSSFWorkbook wb = null;
  FileOutputStream fileOutputStream  =null;
  OutputStreamWriter osw = null;
  PrintWriter printWriter = null;
  Reader reader = null;
  try {
   String csvFileAddress = sourceFileName;
   String xlsxFileAddress = targetFileName;
   wb = new SXSSFWorkbook(100); 
   wb.setCompressTempFiles(true); // temp files will be gzipped
   Sheet sheet = wb.createSheet("sheet1");
   String currentLine=null;
   int RowNum=0;
   
   reader = new InputStreamReader(new FileInputStream(csvFileAddress), "utf-8");
   br = new BufferedReader(reader);

   while ((currentLine = br.readLine()) != null) {
    String str[] = currentLine.split("\",\"");
    Row currentRow=sheet.createRow(RowNum);
    for(int i=0;i<str.length;i++){
     if (str[i].startsWith("=")) {
                        currentRow.createCell(i).
setCellType(currentRow.createCell(i).CELL_TYPE_STRING);
                        str[i] = str[i].replaceAll("\"", "");
                        str[i] = str[i].replaceAll("=", "");
                        currentRow.createCell(i).setCellValue(str[i]);
                    } else if (str[i].startsWith("\"")) {
                        str[i] = str[i].replaceAll("\"", "");
                        currentRow.createCell(i).
setCellType(currentRow.createCell(i).CELL_TYPE_STRING);
                        currentRow.createCell(i).setCellValue(str[i]);
                    } else {
                        str[i] = str[i].replaceAll("\"", "");
                        currentRow.createCell(i).
setCellType(currentRow.createCell(i).CELL_TYPE_NUMERIC);
                        currentRow.createCell(i).setCellValue(str[i]);
                    }     
    }
    RowNum++;
   }

   fileOutputStream  =  new FileOutputStream(xlsxFileAddress);
   wb.write(fileOutputStream);
   osw = new OutputStreamWriter(fileOutputStream , "UTF-8");
            printWriter = new PrintWriter(osw);

   System.out.println("Done");
  } catch (Exception ex) {
   System.out.println(ex.getMessage()+"Exception in try");
  }
  finally{ 
   try {
    if(br!=null)
    br.close();
    if(reader!=null)
    reader.close();
    if(fileOutputStream!=null)
    fileOutputStream.close(); 
    if(osw!=null)
    osw.close();
       if(printWriter!=null)
    printWriter.close();
   } catch (IOException e) {
    System.out.println
("Error trying to close resources.."+e.getMessage());
   }
  }
 }
}



Comments

Popular posts from this blog

How to remotly debug java program using Eclipse

Eclipse provides a facility to debug your java programs remotely. To demonstrate how you can remotely debug java programs remotely, I will do the following: Create a .bat file that calls the java program Configure the arguments to the JVM so that the JVM will be capable of being remotely debugged Configure Eclipse to connect to the remote JVM 1. Create a bat file calling the java program I create a java class given below: public class RemoteDebugDemo { public static void main(String[] args) { System.out.println("STEP 1"); System.out.println("STEP 2"); System.out.println("STEP 3"); System.out.println("STEP 4"); System.out.println("STEP 5"); System.out.println("STEP 6"); } } Now I wish to run this program in batch mode.So I write a file called RemoteDebugDemo.bat file with following content. java -jar RemoteDebugDemo.jar I create a jar file named RemoteDebugDe

How to detect browser closing events and warn user before closing

Recently I came across this problem.I was creating a web application where users were supposed to save their work before logging out.While testing, it turned out that many users closed their browsers instead of logging out. To solve this, I googled the problem and found out that this was a very common problem. I just used below java script code in my webpage's header. Q. How to detect browser closing event and save your data Ans: The onunload event gets fired when the browser is closed or refreshed(using F5 or the refresh button). So now It turns out that the data was getting saved unnecessarily on refreshing the page. For that I stumbled across a forum where I found the solution.(It works for IE only) Q.How to disable function key F5? Ans: document.attachEvent("onkeydown", my_onkeydown_handler); function my_onkeydown_handler() { switch (event.keyCode) { case 116 : // 'F5' event.retur