java实现excel导入功能 博客分类： java 导入excel

方法一
/**
*使用Hibernate框架导入
* @throws IOException
* @throws BiffException
*/
public String imp() throws BiffException, IOException {
TYh user = (TYh) session.get(SSHConstant.ONLINE_USER);// 获取用户信息
//输入流  
InputStream fis = null;
try {
fis = new FileInputStream(excelName);
} catch (FileNotFoundException e) {
e.printStackTrace();
}  
//得到解析Excel的实体集合  
//打开文件
try {
Workbook book = Workbook.getWorkbook(fis);
//得到第一个工作表对象
Sheet sheet = book.getSheet(0);
//得到第一个工作表中的总行数
int rowCount = sheet.getRows();
//循环取出Excel中的内容
for (int i = 1; i < rowCount; i++) {
Cell[] cells = sheet.getRow(i);
YpLsgzry lsgz = new YpLsgzry();//new一个对象
lsgz.setLsgzryxm(cells[0].getContents());
lsgz.setLsgzryxb(cells[1].getContents());
lsgz.setLsgzrysfzh(cells[2].getContents());
lsgz.setLsgzryfl(cells[3].getContents());
lsgz.setLsgzryjf(cells[4].getContents());
lsgz.setLsgzrycphm(cells[5].getContents());
lsgz.setLsgzrygzyy(cells[6].getContents());
lsgz.setKsgzsj(cells[7].getContents());
lsgz.setJsgzsj(cells[8].getContents());
lsgz.setSqgzdw(cells[9].getContents());
lsgz.setSqrxm(cells[10].getContents());
lsgz.setSqrlxdh(cells[11].getContents());
lsgz.setJcf(cells[12].getContents());
lsgz.setGjf(cells[13].getContents());
lsgz.setLsgzryqttz(cells[14].getContents());
     service.saveOrUpdate(lsgz);
     logservice.savelogHadle(2, user.getYhm()+"导入", 1);//日志记录
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return "add";

方法二
/**
*使用jdbc架导入
* @throws IOException
* @throws BiffException
*/
public class ImportExcelLsgzry {

public static List readExcel(String excelFileName) throws BiffException,IOException {
List<String[]> list = new ArrayList();
Workbook rwb = null;
Cell cell = null;
InputStream stream = new FileInputStream(excelFileName);
rwb = Workbook.getWorkbook(stream);
Sheet sheet = rwb.getSheet(0);
// 行数(表头的目录不需要，从1开始)
for (int i = 1; i < sheet.getRows(); i++) {
String[] str = new String[sheet.getColumns()];
// 列数
for (int j = 0; j < sheet.getColumns(); j++) {
// 获取第i行，第j列的值
cell = sheet.getCell(j, i);
str[j] = cell.getContents();
}
// 把刚获取的列存入list
list.add(str);
}
return list;

}

public static void main(String[] args) throws BiffException, IOException{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement ps = null;
String url = "jdbc:oracle:thin:@10.1.7.79:1521:tjkf";
String username = "ypxx";
String password = "ypxx";
try {
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn = DriverManager.getConnection(url, username, password);

} catch (SQLException e) {
e.printStackTrace();
}
List l = ImportExcelLsgzry.readExcel("D:\\lsgz.xls");
try {
//TYh user = (TYh) session.get(SSHConstant.ONLINE_USER);// 获取用户信息
PreparedStatement prt = conn
.prepareStatement("insert into yp_lsgzry(id,lsgzryxm,lsgzryxb,lsgzrysfzh,lsgzryfl,lsgzryjf,lsgzrycphm,lsgzrygzyy,ksgzsj,jsgzsj,sqgzdw,sqrxm,sqrlxdh,jcf,gjf,lsgzryqttz) values (seq_yp_lsgzry.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
for (int i = 0; i < l.size(); i++) {
String[] li = (String[]) l.get(i);
prt.setString(1, li[0]);
prt.setString(2, li[1]);
prt.setString(3, li[2]);
prt.setString(4, li[3]);
prt.setString(5, li[4]);
prt.setString(6, li[5]);
prt.setString(7, li[6]);
prt.setString(8, li[7]);
prt.setString(9, li[8]);
prt.setString(10, li[9]);
prt.setString(11, li[10]);
prt.setString(12, li[11]);
prt.setString(13, li[12]);
prt.setString(14, li[13]);
prt.setString(15, li[14]);
prt.addBatch();
if (i % 500 == 0) {
prt.executeBatch();
}
}
prt.executeBatch();
} catch (Exception e) {
e.printStackTrace();
}
}

}
以下是页面：
<script type="text/javascript">
function checkfile(){
        var FileType = "xls";
        if(uploadForm.excelName.value == ""){
alert("请点击浏览按钮，选择您要导入的文件！");
}else{
str = uploadForm.excelName.value;
strs = str.toLowerCase();
//lens = strs.length;
extname = strs.substring(strs.lastIndexOf('.')+1, strs.length).toLowerCase();
//alert(extname);
if(FileType.indexOf(extname) == -1){
alert("请选择excel文件！");
}else{
uploadForm.submit();
}
}
   }
</script>
<div id="import">
<s:form name="uploadForm" action="lsgzry!imp" method="post" onsubmit="return checkfile()" enctype="multipart/form-data" namespace="/background">
   &nbsp;&nbsp;<input id="excelName" name="excelName" type="file" value="上传文件"></input>&nbsp;&nbsp;
   <input type="button" onclick="checkfile();" value="开始导入" ></input>
   <div style="color: gray">
<ol title="导入说明"><span style="font-weight: 600">导入说明</span>
<li>第一步 选择文件(Excel文件)。下载模板<a href="${Context}/upload/model.xls"> model.xls</a></li>
<li>第二步 点击开始导入</li>
<li>文件必须是Excel文件</li>
</ol>
</div>
</s:form>
</div>