Reputation: 81

convert .xls to .csv java

I converted the .xls to .csv file,The code working fine.but the final output last comma will appear,I don't want to last comma in my code, for example my output look like ss,dd,ee, but I don't need last comma ,How do rectify it.

enter code here

 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 convert {

     static void convertToXls(File inputFile, File outputFile)
    {
      // For storing data into CSV files
       StringBuffer cellDData = new StringBuffer();
       String cellDDataString=null;
      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
              System.out.println("row"+row);

               Iterator<Cell> cellIterator = row.cellIterator();
               while (cellIterator.hasNext())
               {
                  cell = cellIterator.next();
                  System.out.println("cell" +cell);
                  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() + ",");
                   System.out.println(cell.getStringCellValue());
                   break;

                  case Cell.CELL_TYPE_BLANK:
                  cellDData.append("" + ",");
                   break;

                 default:
                cellDData.append(cell + ",");

                }
           }
       cellDData.append("\n");
        }
        //cellDData.append("\n");


//String out=cellDData.toString();
//System.out.println("res"+out);

//String o = out.substring(0, out.lastIndexOf(","));
//System.out.println("final"+o);
fos.write(cellDData.toString().getBytes());
//fos.write(cellDDataString.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("Book1.xls");

    File outputFile = new File("output1.csv");

    convertToXls(inputFile, outputFile);

    }
    }

Upvotes: 0

Answers (6)

Bruna Raniqueli

Reputation: 21

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

import org.apache.commons.io.FilenameUtils;
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.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public 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);

            FileInputStream fis = new FileInputStream(inputFile);
            Workbook workbook = null;

            String ext = FilenameUtils.getExtension(inputFile.toString());

            if (ext.equalsIgnoreCase("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else if (ext.equalsIgnoreCase("xls")) {
                workbook = new HSSFWorkbook(fis);
            }

            int numberOfSheets = workbook.getNumberOfSheets();

            Row row;
            Cell cell;

            for (int i = 0; i < numberOfSheets; i++) {
                Sheet sheet = workbook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.iterator();

                while (rowIterator.hasNext()) {
                    row = rowIterator.next();

                    short firstCell = row.getFirstCellNum();
                    short lastCell = row.getLastCellNum();

                    String DELIMITER = "";

                    for(int j = firstCell; j<lastCell; j++){
                        cell = row.getCell(j);
                        if(cell==null){
                            data.append(DELIMITER);

                        }else {
                            switch (cell.getCellType()) {
                                case Cell.CELL_TYPE_BOOLEAN:
                                    data.append(DELIMITER + cell.getBooleanCellValue() );

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

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

                                case Cell.CELL_TYPE_BLANK:
                                    data.append(DELIMITER );
                                    break;

                                default:
                                    data.append(DELIMITER + cell);
                            }
                        }
                        DELIMITER=",";
                    }
                    data.append('\n'); //
                }
            }
            fos.write(data.toString().getBytes());
            fos.close();

        } catch (Exception ioe) {
            ioe.printStackTrace();
        }
    }
}

Upvotes: 2

Klementina Stojanovska Chirico

Reputation: 73

Inside of your second while loop you are always calling some form of cellDData.append(cell + ","); which is what causes the comma to always get added to the end.

You can do this before you append:

if(cellIterator.hasNext()) {
   cellDData.append(value + ",");
} else {
   cellDData.append(value);
}

Unfortunately, you would have to have this check every time you call cellDData.append inside your switch statements. A cleaner solution may be to store your value as an Object and then call cellDData.append after the switch statements. However, before you store your value as an Object, you should test to see if it would work as expected for your code.

Upvotes: 0

rajat bodankar

Reputation: 9

import java.io.*;
import jxl.*;//libary file jxl-2.6.jar need to download
import java.util.*;

class  ConvertCSV
{
  public static void main(String[] args) 
  {
    try
    {
      //File to store data in form of CSV
      File f = new File("E:\\rajju.csv");

      OutputStream os = (OutputStream)new FileOutputStream(f);
      String encoding = "UTF8";
      OutputStreamWriter osw = new OutputStreamWriter(os, encoding);
      BufferedWriter bw = new BufferedWriter(osw);

      //Excel document to be imported
      String filename = "D:\\RAJAT\\tds.xls";
      WorkbookSettings ws = new WorkbookSettings();
      ws.setLocale(new Locale("en", "EN"));
      Workbook w = Workbook.getWorkbook(new File(filename),ws);

      // Gets the sheets from workbook
      for (int sheet = 0; sheet < w.getNumberOfSheets(); sheet++)
      {
        Sheet s = w.getSheet(sheet);

        bw.write(s.getName());
        bw.newLine();

        Cell[] row = null;

        // Gets the cells from sheet
        for (int i = 0 ; i < s.getRows() ; i++)
        {
          row = s.getRow(i);

          if (row.length > 0)
          {
            bw.write(row[0].getContents());
            for (int j = 1; j < row.length; j++)
            {
              bw.write(',');
              bw.write(row[j].getContents());
            }
          }
          bw.newLine();
        }
      }
      bw.flush();
      bw.close();
    }
    catch (UnsupportedEncodingException e)
    {
      System.err.println(e.toString());
    }
    catch (IOException e)
    {
      System.err.println(e.toString());
    }
    catch (Exception e)
    {
      System.err.println(e.toString());
    }
  }
}

Upvotes: 0

Tanu Garg

Reputation: 3067

Try this

cellDData.replace(cellDData.length() - 1, cellDData.length(), "");

just before line

cellDData.append("\n");

Upvotes: 0

Claudio

Reputation: 69

Just do a a preconstruct for your comma and prepend it in state of append String szMyComma = ""; while (cellIterator.hasNext()) { cell = cellIterator.next(); .... cellDData.append(szMyComma + cell.getStringCellValue()); .... szMyComma = ","; }

Upvotes: 0

Scary Wombat

Reputation: 44854

only append a comma if cellIterator.hasNext() is true

Upvotes: 0

convert .xls to .csv java

Answers (6)

Related Questions