欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-25 20:35:22
...
方法一
/**
*使用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>
相关标签: 导入excel

上一篇: MySQL的安装与配置教程

下一篇: