Friday, January 29, 2016

0

Convert Excel file data in to comma seperated text file

  • Friday, January 29, 2016


  • Below code is to convert excel file (name.xls) in to text files sepetated values by comma.
    In my name.xls file contains 6 columns and 20 rows (20 student informations) it has two different school details in sheet1 and sheet2


    package com.excel.upload;

    import java.io.BufferedWriter;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStreamWriter;
    import jxl.Cell;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.read.biff.BiffException;

    public class ExcelRead {

    public static void main(String[] args) {
           
           try {
               
                Workbook wrk1 =  Workbook.getWorkbook(new File("C:\\Users\\jayakumark\\Downloads\\test1.xlsx"));
               
               //i for number of row ,j for column , k for number of sheets in excel bassed on that you can change i, j, k values  here i am using 2 sheets
                int i,j,k;
                for(k=0;k<2;k++)
                {
               Sheet sheet1 = wrk1.getSheet(k);            
                      
               for (i=0;i<20;i++)
               {
                String [] crits = new String [6];
                for (j=0;j<=5;j++)
                {
                     Cell colArow1 = sheet1.getCell(j, i);      
             
                     String str_colArow1 = colArow1.getContents();
       
                     
                      crits[j] = str_colArow1;
                }
                   
       
         try{
                //to print those values in text file
                   OutputStreamWriter writer = new OutputStreamWriter(
                         new FileOutputStream("C:\\Users\\jayakumark\\Downloads\\DB_input_excel\\name.txt", true), "UTF-8");
                   BufferedWriter fbw = new BufferedWriter(writer);
                   fbw.write(crits[0]+"\t"+crits[1]+"\t"+crits[2]+"\t"+crits[3]+"\t"+crits[4]+"\t"+crits[5]");
                   fbw.newLine();
                   fbw.close();
               }catch (Exception e) {
                   System.out.println("Error: " + e.getMessage());
               }
               System.out.println("Contents of cell Col A Row 1: " +crits[1]);
           System.out.println("Done");
               }
                }
           } catch (BiffException e) {
               e.printStackTrace();
           } catch (IOException e) {
               e.printStackTrace();
           }
           

       }

    }

    here i -> number of rows
    j-> number of columns
    k -> number of sheets in excel
    if you have 5 sheets in your excel file you have to increase k value.
    Finally you will get all sheet values in a single text file .

    To use this code you should download and add below jar in your eclipse.
    jxl26jar

    Read more...

    Subscribe