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.
The system where this ran, supported Java 7, so have not used features in java 8, such as try with exceptions.
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
Post a Comment
Have some feedback, comment or question ?
Post it here in comments section.