jsp:
<div style="height:390px;">
<div class="dialog-toolbar" >
<form id="upform" method="post" enctype="multipart/form-data" target="fileframe">
<a href="#" class="easyui-linkbutton" plain="true" iconCls="icon-download" onclick="downloadFile()">下载导入模板</a>
<input accept=".xls,xlsx" id="inputfile" class="easyui-filebox" name="file2" data-options="required:true,validType:'suffix[\'xls\']',onChange:function(){upForFile();},prompt:'请选择文件...',buttonText:'选择文件'" style="width:300px;height:26px;" />
<span id="fileText">
<span id="fileTextId">
<input id="fileTextName" class="easyui-combobox" name="fileTextName" style="width:120px;">
</span>
</span>
</form>
</div>
<div>
<table id="dg_f" class="easyui-datagrid"></table>
</div>
<input type="hidden" id='nowdygxsStr' value='${acctId}'/><%--有几个头可以选择--%>
<input type="hidden" id="fdygxs" /><%--对应关系--%>
<input type="hidden" id="fklxs" /><%--卡类型--%>
</div>
js:
<script type="text/javascript">
var columns = [
{
field : 'custMemberId',sortable:'true',
width : 100,
align : 'center',
title : '学工号',
fixed : true
},{
field : 'tranDt',sortable:'true',
width : 150,
align : 'center',
title : '考勤时间',
fixed : true,
formatter : function(value, row, index) {
return row.tranDtName;
}
},{
field : 'comments',sortable:'true',
width : 150,
align : 'center',
title : '备注',
fixed : true
}]
$('#dg_f').datagrid({
title:'考勤记录',
border: true,
collapsible : true,
cache : false,
fitColumns : true,remoteSort:false,rownumbers:true,
striped : true,
height : 350,
width : 'auto',
columns : [ columns ]
});
/*下载导入模板 */
function downloadFile() {
var url = $("#path").val()+"/attence/toDownloadAttenceManualRepairFile";
var form = $("<form></form>").attr("action", url).attr("method", "post");
form.appendTo('body').submit().remove();
}
/*显示遮罩层 */
function loadingdiv(){
var url =$("#path").val()+"/images/loading.gif";
$("body").append("<div class='cover' id='loading' style='position:fixed;top:0;left:0;right:0;z-index:9999;bottom:0;background-color:#fff;text-align:center;color: red;opacity:0.8;'><img src=\""+url+"\" style='margin-top:80px'/></div>");
}
/*移除遮罩层 */
function removediv(){
$("#loading").remove();
}
function forCancelDialogs() {
$('#ws').dialog('close');
}
var excelsheet;
function upForFile() {
$('#fileTextId').remove();
if($("#fileText").find("span").length == 0){
$("#fileText").append('<span id="fileTextId"><input id="fileTextName" class="easyui-combobox" name="fileTextName" style="width:120px;"></span>');
}
var path = $("#inputfile").filebox('getValue');
if (path == null || path == "") {
$.messager.alert("提示", "请首先选择上传文件!", "warning", null);
return;
}
var filename = path.split('.')[1].toString().toLowerCase();
if (filename != "xls" && filename != "xlsx") {
$.messager.alert("提示", "文件格式不正确,请重新选择!", "warning", null);
return;
}
fileUpload("");
getAllNumSheet();
}
function fileUpload(bzFlag){
$("#upform").form("submit", {
url : $("#path").val()+"/attence/toEditAttenceManualRepairFileUpload?bzFlag="+bzFlag,
onSubmit : function() {
loadingdiv();
return $(this).form('validate');
},success : function(data) {
var result = JSON.parse(data);
$('#dg_f').datagrid('loadData',result)
removediv();
}
});
}
function getAllNumSheet(){
//获取所有工作表
var isIE = navigator.userAgent.match(/MSIE/)!= null;
var isIE6 = navigator.userAgent.match(/MSIE 6.0/)!= null;
if(isIE) {
$("#upform").ajaxSubmit({
url:$("#path").val()+"/attence/showAttenceManualRepairFileUploadSheetName",
success:function(data0){
if(data0=="[]"){
$('#fileTextName').combobox({
panelHeight:'auto',
panelMaxHeight:'140px',
selected:true,
editable:false,
});
$.messager.alert("消息提示", "没有获取到工作表信息!请检查Excel文件", "info");
return;
}
var data0 = JSON.parse(data0);
$('#fileTextName').combobox({
valueField:'sheetId',
textField:'sheetName',
panelHeight:'auto',
panelMaxHeight:'140px',
selected:true,
data:data0,
editable:false,
value:"0",
onChange : function(){
fileUpload($('#fileTextName').combobox('getValue'));
}
});
}
});
return false;
}else{
var formData = new FormData($("#upform")[0]);
$.ajax({
type: "POST",
url:$("#path").val()+"/attence/showAttenceManualRepairFileUploadSheetName",
data: formData,// 序列化表单值
cache: false,
contentType: false,
processData: false,
error: function(request) {
alert("Connection error");
},
success: function(data0) {
if(data0=="[]"){
$('#fileTextName').combobox({
panelHeight:'auto',
panelMaxHeight:'140px',
selected:true,
editable:false,
});
$.messager.alert("消息提示", "没有获取到工作表信息!请选择别的表", "info");
return;
}
var data0 = JSON.parse(data0);
$('#fileTextName').combobox({
valueField:'sheetId',
textField:'sheetName',
panelHeight:'auto',
panelMaxHeight:'140px',
selected:true,
data:data0,
editable:false,
value:data0[0].sheetId,
onChange : function(){
fileUpload($('#fileTextName').combobox('getValue'));
}
});
}
});
}
}
function lenResult(result){
var numMax = 0;
for(var i=0;i<result.length;i++){
if(result[i].fnum>numMax){
numMax = result[i].fnum;
}
}
return numMax;
}
var nowvalue = ""; nowtext = "";
function getnowValue(num) {
nowvalue = $('#s_title_' + $('#fdygxss').val() + '_' + num).val();
nowtext = $('#s_title_' + $('#fdygxss').val() + '_' + num).find("option:selected").text();
}
function onselectChange(num) {
var selectcount = jQuery.parseJSON($("#nowdygxsStr").val()).length;
var now = $('#s_title_' + $('#fdygxss').val() + '_' + num).val();
var isgo = true;
for (var i = 0; i < selectcount; i++) {
if (num != i) {
if ($('#s_title_' + $('#fdygxss').val() + '_' + i).val() == now) {
$('#s_title_' + $('#fdygxss').val() + '_' + num).val(""); //?
isgo = false;
}
}
}
if (isgo) {
if (now != null && now != "") {
for (var i = 0; i < selectcount; i++) {
if (num != i) {
var se = document.getElementById('s_title_' + $('#fdygxss').val() + '_' + i);
for (var j = 0; j < se.options.length; j++) {
if (se.options[j].value == now) {
se.options.remove(j);
}
}
}
}
}
}
if (nowvalue != null && nowvalue != "") {
for (var i = 0; i < selectcount; i++) {
if (num != i) {
var se = document.getElementById('s_title_' + $('#fdygxss').val() + '_' + i);
se.options[se.length] = new Option(nowtext, nowvalue);
}
}
}
}
</script>
controller:
//对选中的文件进行处理
@RequiresPermissions(value={"attence:attenceManualRepair:upload"},logical=Logical.OR)
@ResponseBody
@RequestMapping("/attence/toEditAttenceManualRepairFileUpload")
public String toEditAttenceManualRepairFileUpload(MultipartFile file2,String bzFlag) throws IOException, EncryptedDocumentException, InvalidFormatException{
//定义excle表格的sheet,bzFlag为传入的sheet
int numSheetFlag = 0;
if(StringUtils.isEmpty(bzFlag))
numSheetFlag=0;
else
numSheetFlag = Integer.parseInt(bzFlag);
//创建封装数据结果集
List<AttenceBizAndMemberShow> result=null;
InputStream fileis = null;
fileis = file2.getInputStream();
//获取文件对象
Workbook hssfWorkbook = WorkbookFactory.create(fileis);
result = new ArrayList<AttenceBizAndMemberShow>();
//仅获取一个sheet
for (int numSheet = 0; numSheet < 1; numSheet++) {
//获取相应的sheet对象
Sheet sheetAt = hssfWorkbook.getSheetAt(numSheetFlag);
if (sheetAt == null) {
continue;
}
//获取当前sheet所有的行
for (int hssfRowNum = 1; hssfRowNum <sheetAt.getLastRowNum(); hssfRowNum++) {
List<String> list = new ArrayList<>();
//获取当前角标对象的行对象
Row hssfRow = sheetAt.getRow(hssfRowNum);
if(hssfRow==null){
break;
}
//获取当前行 的列数
int minColIx = 0;
int maxColIx = hssfRow.getLastCellNum();
//对当前行的所有列进行封装,封装结果为list
for (int colIx = minColIx; colIx < maxColIx; colIx++) {
Cell cell = hssfRow.getCell(colIx);
/*if (cell == null) {
cell =="";
}*/
if(colIx==1){
//对时间进行处理
String format = cell.getCellStyle().getDataFormatString();
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");
String dateString = sdf.format(date);
list.add(dateString);
}else{
list.add(ExcelUtils.getStringVal(cell));
}
}
//进行结果集的封装
AttenceBizAndMemberShow attenceBizAndMemberShow = new AttenceBizAndMemberShow();
if(list.size()>0){
attenceBizAndMemberShow.setCustMemberId(list.get(0));
}
if(list.size()>1){
attenceBizAndMemberShow.setTranDtName(list.get(1));
}
if(list.size()>2){
attenceBizAndMemberShow.setComments(list.get(2));
}
result.add(attenceBizAndMemberShow);
}
}
return StringUtils.toJson(result);
}
//获取表格所有的sheet
@RequiresPermissions(value={"attence:attenceManualRepair:upload"},logical=Logical.OR)
@ResponseBody
@RequestMapping("/attence/showAttenceManualRepairFileUploadSheetName")
public String showAttenceManualRepairFileUploadSheetName(MultipartFile file2) throws IOException, EncryptedDocumentException, InvalidFormatException{
InputStream fileis = null;
fileis = file2.getInputStream();
//获取表格对象
Workbook hssfWorkbook = WorkbookFactory.create(fileis);
//获取sheet的最大角标
int activeSheetIndex = hssfWorkbook.getNumberOfSheets()-1;
//创建表格sheet的集合
List<CustMemberExcelModel> result = new ArrayList<CustMemberExcelModel>();
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
// /获取相应的sheet对象
Sheet sheetAt = hssfWorkbook.getSheetAt(numSheet);
int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
if (physicalNumberOfRows==0) {
continue;
}
if((numSheet+"").equals(activeSheetIndex+"")){
CustMemberExcelModel model = new CustMemberExcelModel();
model.setSheetId(numSheet);
model.setSheetName(hssfWorkbook.getSheetName(numSheet));
result.add(model);
break ;
}
CustMemberExcelModel model = new CustMemberExcelModel();
model.setSheetId(numSheet);
model.setSheetName(hssfWorkbook.getSheetName(numSheet));
result.add(model);
}
return StringUtils.toJson(result);
}
ExcelUtils工具类:
package com.synjones.cloudcard.mng.gateway.web.shared.localService.utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.util.IOUtils;
import org.apache.shiro.authz.annotation.RequiresUser;
import org.springframework.web.bind.annotation.RequestMapping;
public class ExcelUtils {
private static final String EMPTY = "";
public static String getStringVal(Cell cell){
if(cell==null||"".equals(cell)){
return EMPTY;
}else{
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ?"TRUE":"FALSE";
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case Cell.CELL_TYPE_NUMERIC:
cell.setCellType(Cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
default:
return EMPTY;
}
}
}
public static String parseExcel(Cell cell) {
String result = new String();
if(cell==null||"".equals(cell)){
result = "";
return result;
}else{
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
Date date = cell.getDateCellValue();
result = sdf.format(date);
} else if (cell.getCellStyle().getDataFormat() == 58) {
// 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
result = sdf.format(date);
} else {
double value = cell.getNumericCellValue();
CellStyle style = cell.getCellStyle();
DecimalFormat format = new DecimalFormat();
String temp = style.getDataFormatString();
// 单元格设置成常规
if (temp.equals("General")) {
format.applyPattern("#");
}
result = format.format(value);
}
break;
case HSSFCell.CELL_TYPE_STRING:// String类型
result = cell.getRichStringCellValue().toString();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = "";
default:
result = "";
break;
}
return result;
}
}
}