效果：为了实现，在页面上点击excel导入按钮，选择excel文件后，把文件显示到页面上
1、实体类
1.1 分摊实体的数据信息
package com.whir.muju; public class Fentan { private String xh; private String ftcbzx; private String gzno; private String xshtno; private String mjno; private String xmno; private String ftje; private String bz; public String getXh() { return xh; } public void setXh(String xh) { this.xh = xh; } public String getFtcbzx() { return ftcbzx; } public void setFtcbzx(String ftcbzx) { this.ftcbzx = ftcbzx; } public String getGzno() { return gzno; } public void setGzno(String gzno) { this.gzno = gzno; } public String getXshtno() { return xshtno; } public void setXshtno(String xshtno) { this.xshtno = xshtno; } public String getMjno() { return mjno; } public void setMjno(String mjno) { this.mjno = mjno; } public String getXmno() { return xmno; } public void setXmno(String xmno) { this.xmno = xmno; } public String getFtje() { return ftje; } public void setFtje(String ftje) { this.ftje = ftje; } public String getBz() { return bz; } public void setBz(String bz) { this.bz = bz; } @Override public String toString() { return "Fentan [xh=" + xh + ", ftcbzx=" + ftcbzx + ", gzno=" + gzno + ", xshtno=" + xshtno + ", mjno=" + mjno + ", xmno=" + xmno + ", ftje=" + ftje + ", bz=" + bz + "]"; } }
1.2 分摊的集合信息
package com.whir.muju; import java.util.List; public class PageFentan { private int datanum;//总的数据条数 private List<Fentan> data; public List<Fentan> getData() { return data; } public void setData(List<Fentan> data) { this.data = data; } public int getDatanum() { return datanum; } public void setDatanum(int datanum) { this.datanum = datanum; } }
2.工具类
package com.whir.muju; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.io.PrintWriter; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import javax.servlet.http.HttpServletResponse; import net.sf.json.JSONObject; import org.apache.commons.io.FileUtils; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.struts2.ServletActionContext; public class ExeclShow { private InputStream inputStream; //封装上传文件属性 private File attachments; //封装上传文件的类型 private String attachmentsContentType; //封装上传文件名称 private String attachmentsFileName; //得到上传的路径 public String getUploadPath(){ StringBuffer resultJson= new StringBuffer(""); String resultData=""; ////在实际开发中,需要判断一下是否上传了文件，即upload是否为空,不为空，才进行上传的操作。 if(attachments!=null){ //1.输出上传的类型 System.out.println("上传文件的名称用File取得:"+this.getAttachments().getName()); System.out.println("直接用属性取得名称："+this.getAttachmentsFileName()); System.out.println("文件上传的类型:"+this.getAttachmentsContentType()); //2.设置上传文件的放置位置，通常放在服务器下面的upload文件夹下. String path="D:/jboss/jboss-as/server/langchao/deploy/defaultroot.war/WEB-INF/mjfiles"; //3.这是一个目录，如果这个目录不存在，需要创建这个目录(包括其父目录) File srcFile=new File(path); if(!srcFile.exists()){ srcFile.mkdirs(); } //4.通常还需要对上传的文件名进行UUID的操作，使文件名不能重复。文件的返回路径是fileName. String fileName=getAnglePath(this.getAttachmentsFileName()); System.out.println("fileName:"+fileName); //5.调用FileUtils类执行上传的操作. try { //写复制方法 FileUtils.copyFile(attachments,new File(srcFile,fileName)); //将fileName进行相应的处理,去掉前面的一些无用的东西。 得到项目名 //String context=ServletActionContext.getRequest().getContextPath().substring(1); resultJson.append(fileName.substring(1)).append("|"); fileName=path+fileName; System.out.println("文件上传成功，fileName路径:"+fileName); System.out.println("======excelshow start======"); resultData=getDataFromExcel3(fileName); System.out.println(resultData.toString()); System.out.println("======excelshow end======"); } catch (IOException e) { e.printStackTrace(); } } HttpServletResponse response=ServletActionContext.getResponse(); response.setContentType("text/html;charset=utf-8"); try { PrintWriter out= response.getWriter(); out.println(resultData); out.flush(); out.close(); } catch (IOException e) { e.printStackTrace(); } return null; }
/** * 将一个文件名设置成唯一的文件名 * @param fileName 普通的文件名 * @return 返回一个唯一的文件名 */ public String getAnglePath(String fileName) { //不采用UUID的形式，可以自定义当前的时间字符串来处理. SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss"); String angle=sdf.format(new java.util.Date()); return File.separator+angle+"_"+fileName; } /** * 读取出filePath中的所有数据信息 * @param filePath excel文件的绝对路径 * */ public static String getDataFromExcel3(String filePath) { List<Fentan> list = new ArrayList<Fentan>(); //判断是否为excel类型文件 if(!filePath.endsWith(".xls")&&!filePath.endsWith(".xlsx")) { System.out.println("文件不是excel类型"); } FileInputStream fis =null; Workbook wookbook = null; int flag = 0; try { //获取一个绝对地址的流 fis = new FileInputStream(filePath); } catch(Exception e) { e.printStackTrace(); } try { //2007版本的excel，用.xlsx结尾 wookbook = new XSSFWorkbook(fis);//得到工作簿 } catch (Exception ex) { //ex.printStackTrace(); try { fis = new FileInputStream(filePath); //2003版本的excel，用.xls结尾 wookbook = (Workbook) new HSSFWorkbook(fis);//得到工作簿 } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //得到一个工作表 Sheet sheet = wookbook.getSheetAt(0); //获取sheet中第一行行号 int firstRowNum = sheet.getFirstRowNum(); //获取sheet中最后一行行号 int lastRowNum = sheet.getLastRowNum(); String str=""; try { //循环插入数据 for(int i = firstRowNum+2 ; i <= lastRowNum ; i++) { //获得第i行对象 Row row = sheet.getRow(i); Fentan ft=new Fentan(); Cell cell1=row.getCell(0); //序号 if(cell1!=null){ cell1.setCellType(Cell.CELL_TYPE_STRING); ft.setXh(cell1.getStringCellValue()); } Cell cell2=row.getCell(1); if(cell2!=null){ cell2.setCellType(Cell.CELL_TYPE_STRING); ft.setFtcbzx(cell2.getStringCellValue()); } Cell cell3=row.getCell(2); if(cell3!=null){ cell3.setCellType(Cell.CELL_TYPE_STRING); ft.setGzno(cell3.getStringCellValue()); } Cell cell4=row.getCell(3); if(cell4!=null){ cell4.setCellType(Cell.CELL_TYPE_STRING); ft.setXshtno(cell4.getStringCellValue()); } Cell cell5=row.getCell(4); if(cell5!=null){ cell5.setCellType(Cell.CELL_TYPE_STRING); ft.setMjno(cell5.getStringCellValue()); } Cell cell6=row.getCell(5); if(cell6!=null){ cell6.setCellType(Cell.CELL_TYPE_STRING); ft.setXmno(cell6.getStringCellValue()); } Cell cell7=row.getCell(6); if(cell7!=null){ cell7.setCellType(Cell.CELL_TYPE_STRING); ft.setFtje(cell7.getStringCellValue()); } Cell cell8=row.getCell(7); if(cell8!=null){ cell8.setCellType(Cell.CELL_TYPE_STRING); ft.setBz(cell8.getStringCellValue()); } list.add(ft); } if(list.size()>0){ PageFentan page=new PageFentan(); page.setData(list); page.setDatanum(list.size()); JSONObject jsonObj=JSONObject.fromObject(page); String jsonstr =jsonObj.toString(); System.out.println("excel文件解析出来的数据："+jsonstr); return jsonstr; } }catch (ClassCastException e) { e.printStackTrace(); //System.out.println("数据不全是数字或全部是文字!");//由于都是设置的字符串，所以不会报错 } return ""; } public static void main(String[] args){ getDataFromExcel3("E://123.xlsx"); } public InputStream getInputStream() { return inputStream; } public void setInputStream(InputStream inputStream) { this.inputStream = inputStream; } public File getAttachments() { return attachments; } public void setAttachments(File attachments) { this.attachments = attachments; } public String getAttachmentsContentType() { return attachmentsContentType; } public void setAttachmentsContentType(String attachmentsContentType) { this.attachmentsContentType = attachmentsContentType; } public String getAttachmentsFileName() { return attachmentsFileName; } public void setAttachmentsFileName(String attachmentsFileName) { this.attachmentsFileName = attachmentsFileName; } }
3.jsp页面代码(从上一个页面跳转到该页面时带了一些数据信息)
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="s" uri="/struts-tags" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>报销明细</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<!--这里可以追加导入模块内私有的js文件或css文件-->
</head>
<body>
<s:form name="queryFormdd" id="queryFormdd" action="Detail!fyftDetailMethod.action" method="post" theme="simple" enctype="multipart/form-data">
<div style="text-align:center;margin-top:20px;margin-right:10px;">
<div style="float: right;"> Excel 导入: <input type="file" name="attachments" id="attachments" onchange="return fileUpload()"/></div>
<input style=" float:right;" type="button"value="增加" onclick="addTable()">
<input style=" float:right" type="button" value="确认"onclick="resetForm();" >
<input type="hidden" value="<s:property value='#request.je'/>" name="moneys" id="moneys" />
<input type="hidden" value="<s:property value='#request.gsjc'/>" name="gs" id="gs" />
<input type="hidden" value="<s:property value='#request.fykm'/>" name="fykm" id="fykm" />
<input type="hidden" value="<s:property value='#request.fwlx'/>" name="fwlx" id="fwlx" />
<input type="hidden" id="mjtime" value="" name="mjtime" /><br/>
</div>
<table width="100%" border="0" cellpadding="1" cellspacing="1" class="listTable" id='viewTabs'>
<thead id="headerContainer">
<tr class="listTableHead">
<td>序号</td>
<td>成本中心</td>
<td>资产编号</td>
<td>合同号</td>
<td>模具号</td>
<td>项目名称</td>
<td>费用</td>
<td>备注</td>
<td>操作</td>
</tr>
</thead>
<tbody id="Contbody">
<tr>
<td><input type="text" class="inputText" id="xh" value="" name="xh" /></td>
<td><input type="text" class="inputText" id="ftcbzx" value="" name="ftcbzx" /></td>
<td><input type="text" class="inputText" id="gdzcno" value="" name="gdzcno" /></td>
<td><input type="text" class="inputText" id="xshtno" value="" name="xshtno" /></td>
<td><input type="text" class="inputText" id="mjno" value="" name="mjno" /></td>
<td><input type="text" class="inputText" id="xm" value="" name="xm" /></td>
<td><input type="text" class="inputText" id="ftje" value="" name="ftje" /></td>
<td><input type="text" class="inputText" id="bz" value="" name="bz" /></td>
<td><input type="button" value ="删除"onclick="del(this);"></td>
</tr>
</tbody>
</table>
</s:form>
</body>
<script type="text/javascript">
var tr="";
var objtr="";
window.onload = function () {
//添加时间
var currentdate = getNowFormatDate();
document.getElementById("mjtime").value=currentdate;
var tab = $("[id='viewTabs'] tr")[1];
$("input[name='ftcbzx']").after("<a href="javascript:void(0);" class="selectIco" onclick="selectcbzx(this)" style="margin-left: -30px;"></a>");
tr = $(tab).clone(true);
//序号编号
xhJudge();
};
//删除按钮
function del(_this){
var t=document.getElementById('viewTabs');
var $trNode = $(_this).parent().parent();
var flag = confirm("确定删除该行数据吗？");
if (flag) {
$trNode.remove();
xhJudge();
}
};
//文件上传
function fileUpload(){
// 创建formdata对象
//给formData对象添加<input>标签,注意与input标签的ID一致
// var file1 = document.getElementById('file').files[0];
var datanum=0;
$("#queryFormdd").ajaxSubmit({
type:'post',
url:'mjexcelshow!getUploadPath.action',
success: function(response,status,xhr) { //提交成功后自动执行的处理函数，参数data就是服务器返回的数据。
var jsonPage = eval('('+response+')');
datanum=jsonPage.datanum;//total number
$("#Contbody").empty();
if(datanum==0){
alert("选择的excel没有数据");
}
for(var i=0;i<datanum;i++){
$("#Contbody").append("<tr>"+
" <td><input type='text' class='inputText' id='xh' value='"+jsonPage.data[i].xh+"' name='xh' /></td>"+
" <td><input type='text' class='inputText' id='ftcbzx' value='"+jsonPage.data[i].ftcbzx+"' name='ftcbzx' /></td>"+
" <td><input type='text' class='inputText' id='gdzcno' value='"+jsonPage.data[i].gzno+"' name='gdzcno' /></td>"+
" <td><input type='text' class='inputText' id='xshtno' value='"+jsonPage.data[i].xshtno+"' name='xshtno' /></td>"+
" <td><input type='text' class='inputText' id='mjno' value='"+jsonPage.data[i].mjno+"' name='mjno' /></td>"+
" <td><input type='text' class='inputText' id='xm' value='"+jsonPage.data[i].xmno+"' name='xm' /></td>"+
" <td><input type='text' class='inputText' id='ftje' value='"+jsonPage.data[i].ftje+"' name='ftje' /></td>"+
" <td><input type='text' class='inputText' id='bz' value='"+jsonPage.data[i].bz+"' name='bz' /></td>"+
"<td><input type='button' value ='删除' onclick='del(this);'></td>"+
"</tr>");
}
},
error: function(data, status, e) { //提交失败自动执行的处理函数。
alert('上传失败,请检查上传文件格式和文件类型');
}
});
return false;//阻止页面跳转
}
//增加行
function addTable(){
var obj=addJudge();
// console.log(obj);
if(obj.zt=="1"){
var tr1 = tr.clone(true);
tr1.appendTo($("[id='viewTabs']"));
var tds=$("#viewTabs").find("tr");
var lengths=tds.length;
//$(tds[lengths-1]).find("[name='subject']").val("");
$(tds[lengths-1]).find("[name='ftcbzx']").val("");
$(tds[lengths-1]).find("[name='gdzcno']").val("");
$(tds[lengths-1]).find("[name='xshtno']").val("");
$(tds[lengths-1]).find("[name='mjno']").val("");
$(tds[lengths-1]).find("[name='xm']").val("");
$(tds[lengths-1]).find("[name='ftje']").val("");
$(tds[lengths-1]).find("[name='bz']").val("");
}else{
alert(obj.ts);
}
};
</script>
</html>