以下代码为程序自动读取excel的表格数据,生成sql脚本,便于执行
目前只支持读取excel2003版本,更高级的版本可以另存为2003版本
package com.hundsun.front.compet.action;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.UnknownHostException;
import jxl.Sheet;
import jxl.Workbook;
/**
* @author liusn19096
*
*/
public class ReadExcel {
/**
* 文件编码
*/
private static final String ENCODE = "GBK";
/**
* @param args
* @throws UnknownHostException
*/
public static void main(String[] args) throws UnknownHostException {
// 读的文件路径,
String readFilePath = "C:\\Users\\liusn19096\\Desktop\\333.xls";
try {
Workbook readwb = null;
// 每次都重新读文件
InputStream instream = new FileInputStream(readFilePath);
readwb = Workbook.getWorkbook(instream);
//Sheet的下标是从0开始
//获取第一张Sheet表
Sheet readsheet = readwb.getSheet(0);
//获取Sheet表中所包含的总列数
int rsColumns = readsheet.getColumns();
//获取Sheet表中所包含的总行数
int rsRows = readsheet.getRows();
//获取指定单元格的对象引用
String url = null;
String name = null;
String param = null;
for (int i1 = 0; i1 < rsRows; i1++) {
url = readsheet.getCell(0, i1).getContents();
name = readsheet.getCell(1, i1).getContents();
param = readsheet.getCell(2, i1).getContents();
System.out.println("INSERT INTO `cache_rule` (serial_no,`cache_name`, `cache_uri`, `cache_prefix`, `cache_parameter`, `cache_timeout`, `cache_status`, `need_login`, `remark`) VALUES ("
+ (39 + i1) + ",'" + name + "', '" + url + "', 'compet:" + url + "', '" + param + "', '600', '1', '0', ' ');");
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (NumberFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}