Forum Discussion
Aaronliu
12 years agoFrequent Contributor
here is a way which using script to read data from xls and write data to xls:
ReadExcel:
//Need to import jxl.jar to %SOAP UI%\ext folder first
WriteExcel:
ReadExcel:
//Need to import jxl.jar to %SOAP UI%\ext folder first
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public static String readExcel(File file) {
StringBuffer sb = new StringBuffer();
Workbook wb = null;
try {
wb = Workbook.getWorkbook(file);
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
if (wb == null){
return null;
}
Sheet[] sheet = wb.getSheets();
if (sheet != null && sheet.length>0){
for (int i = 0; i<sheet.length;i++){
int rowNum = sheet[i].getRows();
for (int j=0;j<rowNum;j++){
Cell[] cells = sheet[i].getRow(j);
if (cells != null && cells.length>0){
for (int k = 0;k<cells.length;k++){
String cellValueString = cells[k].getContents();
sb.append(cellValueString+"\t");
}
}
sb.append("\r\n");
}
sb.append("\r\n");
}
}
wb.close();
return sb.toString();
}
def file = new File ("C:"+File.separator+"hello.xls")
log.info readExcel(file)
WriteExcel:
import jxl.Workbook;
import jxl.write.WritableWorkbook;
import jxl.write.WritableSheet;
import jxl.write.Label;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public static void writeExcel(String fileName){
WritableWorkbook wwb = null;
try {
//declare object of Workbook
wwb = Workbook.createWorkbook(new File(fileName));
} catch (IOException e) {
e.printStackTrace();
}
if(wwb!=null){
WritableSheet ws = wwb.createSheet("sheet1", 0);
for(int i=0;i<10;i++){
for(int j=0;j<5;j++){
Label labelC = new Label(j, i, "This is"+(i+1)+"Row,"+(j+1)+"Column");
try {
ws.addCell(labelC);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
try {
wwb.write();
wwb.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
def file = new File ("C:"+File.separator+"hello.xls")
log.info writeExcel("C:/hello.xls")
Related Content
- 12 months agoMW_Didata
- 2 years agoarmygrad
- 2 years agolalit_singh
Recent Discussions
- 2 days agoruchisingh