java导入excel....-优快云博客

jsp.....
<%@page contentType="text/html; charset=GBK"%>
<%@taglib uri="/WEB-INF/struts-logic.tld" prefix="logic"%>
<%@taglib uri="/WEB-INF/struts-bean.tld" prefix="bean"%>
<%@taglib uri="/WEB-INF/struts-html.tld" prefix="html"%>
<%@taglib uri="/WEB-INF/tag.tld" prefix="tag"%>

<%@page import="com.post.inspect.duty.vo.*"%>
<%@page import="java.util.*"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<base href="<%=basePath%>">
<html>
<head>
<title>排班表.列表</title>

<script type="text/javascript" type="text/javascript" >
//var flag="${flag}";

function bb(){
// alert(document.all('ad'));
var flag="${flag}";
if(flag=='0'){
document.all('ad').style.display="none";
document.all('de').style.display="none";
}
}

</script>
<script language="javascript" type="text/javascript">
function getForm(){
return BasicForm;
}
//返回
function goback(){
history.back();
}
function click_HideImp(name){
var tmp=document.all(name);
var search = document.all('search');
search.style.display = "none";
if(tmp.style.display=="none"){
tmp.style.display="";
}else{
tmp.style.display="none";
}
return;
}

function myClick_HideSearch(name){
var tmp=document.all(name);
var excelImp = document.all('excelImp');
excelImp.style.display = "none";

var imp = document.all("excelFile");
imp.outerHTML += '';

if(tmp.style.display=="none"){
tmp.style.display="";
}else{
tmp.style.display="none";
}
return;
}

function row_selectesd(row,tablename,oldrowindex,oldrowcolor,keyvalue)
{
var orgId=row.cells(1).innerText;
document.all("orgId").value=orgId;

// var dutyDate= row.cells(5).innerText;
// document.all("dutyDate").value=dutyDate;
var i=0;
//alert("ok");
var rowindex=document.all(oldrowindex).value;
var rowcolor=document.all(oldrowcolor).value;
if (parseInt(oldrowindex)!=row.rowIndex)
{
if (rowindex!='')
{
for(i=0;i<document.all(tablename).rows(parseInt(rowindex)).cells.length;i++)
{
document.all(tablename).rows(parseInt(rowindex)).cells(i).style.backgroundColor=rowcolor;
}
}
document.all(oldrowindex).value=row.rowIndex;
document.all(oldrowcolor).value=row.cells(0).bgColor;
if (keyvalue!='' && row.all.item('rowkeyvalue')!=null){
// var orgId=row.cells(2).innerText;
// document.all(keyvalue).value=row.all.item('rowkeyvalue').value;
document.all(keyvalue).value=row.cells(2).innerText;
}
// alert(row.cells(2).innerText);
for(i=0;i<row.cells.length;i++)
row.cells(i).style.backgroundColor="#FFECD9";
}
return;
}

//实现查询
function click_query(method){
if(method==null||method=="undefined"){
method = "list";
}
document.all('method').value=method;
var e = event.srcElement;
var orgId=document.all('Q_orgId').value;
if(e.tagName!="IMAGE"&&e.type!="image"){
if(orgId==''){
alert("请选择网点号");
}else{
BasicForm.submit( );
}
}
}

/** 导出 */
function click_goExport(){
if(document.all("keyvalue").value==""){
alert('请选择至少一条记录进行导出！ ');
return;
}
document.all('method').value='exportQues';
var ids = document.getElementsByName("ids");
var checkId="";
for(var i=0;i<ids.length;i++){
if(ids[i].checked == true){
checkId+=(ids[i].value+",");
}
}

//从hidden中取值放到参数hidden与中

var ordids=document.getElementById('ordids').value;
document.all('id_org').value=ordids;
document.getElementById("checkId").value=checkId;
BasicForm.submit();
}

</script>
<%@include file="/ui/jsp/initJs.jsp"%>
<%@include file="/ui/jsp/initCss.jsp"%>
</head>
<body οnlοad="initpage(),bb()" >

<html:form action='/inspect/duty/InspectDuty1.do' method="post" enctype="multipart/form-data">
<input type="hidden" name="method" value="<tag:attr source="method" />">
<input type="hidden" name="id" value="">
<input type="hidden" name="orderCol" value="<bean:write name="BasicForm" property="orderCol"/>">
<input type="hidden" name="orderMode" value="<bean:write name="BasicForm" property="orderMode"/>">
<input type="hidden" id="rowindex">
<input type="hidden" id="rowcolor">
<input type="hidden" name="keyvalue" id="keyvalue">
<input type="hidden" name="orgId" id="orgId">
<input type="hidden" name="flag" id="flag" value="${flag}">
<input type="hidden" name="checkId" value="">
<input type="hidden" name="id_org" value="">
<in
<div class="main_menu">
<ul id="toolbar">
<div id="ad" name="ad">
<li id="ads" name="ads"><a onClick="javascript:click_add()"><img src="images/new.gif" border="0">增加</a></li>
</div>



<div id="de" name="de">
<li><a onClick="javascript:click_del()"><img src="images/del.gif" border="0">删除</a></li>
</div>


<li><a onClick="javascript:myClick_HideSearch('search')"><img src="images/search.gif" border="0">查询</a></li>

<li><a onClick="javascript:click_HideImp('excelImp')"><img src="images/search.gif" border="0">导入</a></li>
<li><a onClick="javascript:click_goExport()"><img src="images/modify.gif" border="0">导出</a></li>
</ul>
</div>
<br/>
<%@ include file="/configure/common/messages.jsp" %>

<table id="excelImp" style="display:none" border="0" cellpadding="0" cellspacing = "0">
<tr>
<td>选择排班表：</td>
<td><input type="file" name="excelFile" id="excelFile"/>  </td>
<td>
<div class="buttons">
<button class="positive" name="qry" onClick="javascript:click_query('<tag:attr source="method" />')">
<img src="ui/images/search.gif" alt=""/>导入
</button>
</div>
</td>
</tr>
</table>

<table id="search" style="display:none" border="0" cellpadding="0" cellspacing="0">
<tr>

<td>值班日期：</td>
<td>
<input type="text" name="Q_startDate" οnkeydοwn="enterToTab();" οnfοcus="this.select()"
οnclick="WdatePicker({dateFmt:'yyyy-MM-dd'})" styleClass="text"
value='<bean:write name="BasicForm" property="Q_startDate"/>' class="Wdate">

</td>

<td>
<input type="text" name="Q_endDate" οnkeydοwn="enterToTab();" οnfοcus="this.select()"
οnclick="WdatePicker({dateFmt:'yyyy-MM-dd'})" styleClass="text"
value='<bean:write name="BasicForm" property="Q_endDate"/>' class="Wdate">

</td>

<td>网点号：</td>
<td>
<tag:userComboxTree name="Q_orgId" rootId="${deptid}" showType="children" selectType="dept"/>
</td>

<td>
<div class="buttons">
<button class="positive" name="qry" onClick="javascript:click_query('<tag:attr source="method" />')">
<img src="ui/images/search.gif" alt=""/>查询
</button>
</div>
</td>
</tr>
</table>

<table id="LodgeInfo" class="dataList" width="100%" cellspacing="0" border="0">

<thead>
<tr>
<th width="3%"><input type="checkbox" οnclick="selectAll()"></th>
<th style="cursor: hand" title="点击排序"
οnclick="orderBy('DUTY_DATE')">日期</th>
<logic:present name="taixiList">
<logic:iterate id="taixi" name="taixiList">
<th style="cursor: hand" title="点击排序"
οnclick="orderBy('<bean:write name="taixi"/>')"><bean:write
name="taixi" property="SITE_NAME" /></th>
</logic:iterate>
</logic:present>
</tr>
</thead>


<tbody>
<logic:present name="volist">
<bean:define id="list" name="volist" />
<logic:iterate id="li" name="list">
<bean:define id="ub" name="li" />

<tr class="bg"
onClick="row_selectesd(this,'LodgeInfo','rowindex','rowcolor','keyvalue','orgId');">
<td>
<input type="checkbox" name="ids" value="<bean:write name="ub" property="DUTY_DATE"/>">
<input type="hidden" id="rowkeyvalue" name="rowkeyvalue" value='<bean:write name="ub" property="DUTY_DATE"/>'>
<input type="hidden" id="ordids" name="ordids" value='<bean:write name="ub" property="ORG_ID"/>'>
</td>
<td><bean:write name="ub" property="DUTY_DATE"
format="yyyy-MM-dd" />
</td>
<logic:present name="taixiList">
<logic:iterate id="taixi" name="taixiList">
<bean:define id="test" name="taixi" property="SITE_NO" />
<%
String str = "A" + test;
%>

<td><a href="inspect/duty/InspectDuty1.do?method=updateTaixi&flag=${flag}
&commId=<bean:write name="ub" property="<%=str%>" />&dutyDate=<bean:write name="li" property="DUTY_DATE" />"> <bean:write name="ub" property="<%=str%>" /> </td>
</logic:iterate>
</logic:present>
</tr>
</logic:iterate>
</logic:present>
</tbody>



<tfoot>
<%@include file="/public/pubpage1.jsp"%>
</tfoot>

</table>
</html:form>
</body>
</html>

java代码
/**
* 分页列表
*/
public ActionForward list(ActionMapping mapping, ActionForm form, HttpServletRequest request,
HttpServletResponse response) throws Exception {
QueryForm qf = new QueryForm(request, InspectDuty.class);
PageInfo page = new PageInfo(request);
BasicActionForm baseForm = (BasicActionForm) form;
List<InspectDuty> volist = new ArrayList<InspectDuty>();
String message = "";
FormFile file = (FormFile) baseForm.get("excelFile"); //导入判断。。。
if (null != file && !COMMON.isEmpty(file.getFileName())) {
message = processExcelFile(volist, baseForm);
if (!COMMON.isEmpty(message)) {
this.setMessage(request, "导入失败:\n\r" + message.trim());
} else if (volist.size() == 0) {
this.setMessage(request, "导入失败:表格数据不能为空，请检查！");
} else {
// List list = inspectDutyService.list(qf, page);
inspectDuty1Service.batchSaveOrUpdateDuty(volist);
this.setMessage(request, "导入成功！");
// request.setAttribute("volist", list);
}
}

String currentOrgId = UserContext.getDept().getDeptCode(); //当前机构
// String orgId = inspectDuty1Service.getQueryValue(baseForm, "orgId"); //值班开始日期
String type = request.getParameter("type");

String orgId = null;
if (baseForm.get("Q_orgId") != null) {
orgId = (String) baseForm.get("Q_orgId");
}

String flag = "";

if (type != null && "1".equals(type)) { //从菜单进入的

if (currentOrgId.equals("440000888")) { //省里的人
flag = "0";
} else { //不是省里的人
flag = "1";
}

} else { //不是从菜单进入的

if (currentOrgId.equals("440000888")) { //省里的人
flag = "0";
} else { //不是省里的人在操作进入的

if (baseForm.get("Q_orgId") != null) { //上级处理下级数据处理查询后的操作
if (orgId.equals(currentOrgId)) { //只能处理自己的数据
flag = "1";
}
}

}

}

if (orgId == null && type == null) { //这是增加和修改后删除后返回的list页面一定不是省里的人可以进入
flag = "1";
}

if (request.getParameter("cs") != null) {
flag = "0";

}

request.setAttribute("flag", flag);

List taixiList = null;
if (orgId == null) {
taixiList = inspectDuty1Service.queryAllSiteNo(currentOrgId);
} else {
taixiList = inspectDuty1Service.queryAllSiteNo(orgId);
}
volist = inspectDuty1Service.listInspectData(baseForm, page);
if (taixiList.size() < 0 || taixiList == null) {
Map map = new HashMap();
map.put("SITE_NO", 000000);
map.put("SITE_NAME", "无数据展示");
super.setMessage(request, "无数据展示");
}
request.setAttribute("taixiList", taixiList);
request.setAttribute("volist", volist);

page.saveToRequest(request);
request.setAttribute("method", "list");
return LIST_FORWARD;
}

public String processExcelFile(List<InspectDuty> volist, BasicActionForm baseForm) throws Exception {
StringBuffer message = new StringBuffer("");
FormFile importExcelFile = (FormFile) baseForm.get("excelFile"); //取得导入文件对象

if (importExcelFile.getFileSize() > 0) { //文件大小
String ext = FileUtil.getExtensionName(importExcelFile.getFileName());
if (!ext.toLowerCase().matches("^xls$")) {
message.append("导入文件格式不正确，请导入.xls格式的文件！<br/>");
return message.toString();
}
} else {
message.append("导入的文件不能为空，请检查后再导入！<br/>");
return message.toString();
}

HSSFWorkbook wb = null;

HSSFSheet sheet = null;

HSSFCell cell = null;

HSSFRow row = null;

wb = (HSSFWorkbook) new HSSFWorkbook(importExcelFile.getInputStream());

sheet = wb.getSheetAt(0);

// 行数(从0开始,相当于最后一行的索引),列数
int count_row = sheet.getLastRowNum() + 1;
int count_cell = sheet.getRow(5).getPhysicalNumberOfCells();

String[][] str = new String[count_row][count_cell];

for (int i = 0; i < count_row; i++) {
row = sheet.getRow(i);

if (null != row) {
String tempMsg = validateRow(i, row);

message.append(tempMsg);

count_cell = sheet.getRow(i).getPhysicalNumberOfCells();

for (int j = 0; j < count_cell; j++) {

cell = row.getCell((short) j);

int type = cell.getCellType(); // 得到单元格数据类型

String value = "";

switch (type) { // 判断数据类型
case HSSFCell.CELL_TYPE_BLANK:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = cell.getBooleanCellValue() + "";
break;
case HSSFCell.CELL_TYPE_ERROR:
value = cell.getErrorCellValue() + "";
break;
case HSSFCell.CELL_TYPE_FORMULA:
value = cell.getCellFormula();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
value = sdf.format(cell.getDateCellValue());
} else {
DecimalFormat df = new DecimalFormat("#.##");
value = df.format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
default:
break;
}
str[i][j] = value;
}
}
}

cell = null;

row = null;

sheet = null;

wb = null;

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");

if (str.length > 0) {
for (int i = 0; i < str.length; i++) {
String[] array = str[i];
if (i >= 5 && array.length > 0) {
for (int j = 2; j < array.length; j++) {
String value = array[j];
if (!COMMON.isEmpty(value)) {
InspectDuty vo = new InspectDuty();
vo.setDutyDate(sdf.parse(array[0]));
vo.setTellerNo(value);
vo.setSiteNo(str[3][j]);
vo.setSiteName(str[2][j]);
vo.setOrgId(str[i][1]);
volist.add(vo);
}
}
}
}
}

return message.toString();
}

public String validateRow(int rowIndex, HSSFRow row) {
StringBuffer message = new StringBuffer("");
HSSFCell cell = null;
if (null != row) {
int cellCount = row.getPhysicalNumberOfCells();

if (rowIndex == 3) {
for (int i = 0; i < cellCount; i++) {
cell = row.getCell((short) i);
if (i > 1 && "".equals(cell.getStringCellValue())) {
message.append("第 " + rowIndex + " 行台席 " + i + " 数据不能为空，请检查！<br/>");
}
}
}
if (rowIndex >= 5) {
for (int i = 0; i < cellCount; i++) {
cell = row.getCell((short) i);
int type = cell.getCellType();
if (i == 0 && type == HSSFCell.CELL_TYPE_BLANK) {
message.append("第 " + rowIndex + " 行日期列数据不能为空，请检查！<br/>");
}
}
}
}
return message.toString();
}