Friday, January 29, 2016
0
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
Convert Excel file data in to comma seperated text file
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
Subscribe to:
Post Comments (Atom)
0 Responses to “Convert Excel file data in to comma seperated text file”
Post a Comment