js引入
<script type="text/javascript" src="<%=basePath%>Modules/v2/Library/JQuery/ajaxfileupload.js"></script>
导入弹窗
<div id="importMadol" class="easyui-window" title="导入模板" style="width:600px;height:160px"
data-options="iconCls:'icon-save',modal:true,closed:true" >
<div class="modal-footer" >
<input id="wrmsdr" name="wrmsdr" class="easyui-filebox"style="width:550px;height:35px;margin-left: 5%" buttonText="导入文件">
</div>
<div class="modal-footer" style="margin-top: -1%">
<div class="btn_search" style="float: left;margin-left: 25%">
<a href="javascript:void(0)" id="importSureBtn">确定</a>
</div>
<div class="btn_search" style="float: left;margin-left:25%">
<a href="javascript:void(0)" id="deCloseBtn">关闭</a>
</div>
</div>
</div>
js调接口
$('#importMadol').window('open');
// 导入:“确定”按钮点击事件
$("#importSureBtn").click(function() {
importInfo();
});
function importInfo(){
var url=$("#wrmsdr").filebox('getValue');
if(url==""){
messager("提示","请选择文件");
return;
}
var wrmsdr= $("input[name='wrmsdr']").attr("id");
$.ajaxFileUpload({
url : getBasePath() + "/sjzb/importQyfxrgsjDr.do",
secureuri : false,
fileElementId : wrmsdr,
type : "post",
dataType : 'text',
data : {
ts:DateToString(new Date(),'yyyy-MM-dd'),
},
success : function(data) {
if(data=="1"){
$.messager.alert('提示',"导入失败");
return;
}
}
});
}
JAVA
@Controller
@RequestMapping("/sjzb")
public class SjzbController {
@ResponseBody
@RequestMapping("/importQyfxrgsjDr")
//数据定时服务方法
public String qyfxrgsjDr(HttpServletRequest request,
HttpServletResponse response,String ts) throws ParseException, IOException, DocumentException {
String filePath = FileUtil.fileUpload(request, response,
"");
String filePath1 = request.getSession().getServletContext()
.getRealPath("/")
+ filePath;
JSONArray array = new JSONArray();
JSONObject jsonObject = null;
String detestr="";
//List<ZbmsZ> retList = new ArrayList<ZbmsZ>();
// 将读取表格的类实例化
HSSFWorkbook rwb = null;
try {
// 根据表格路径读取表格数据
rwb = new HSSFWorkbook(new FileInputStream(filePath1));
} catch (IOException e) {
e.printStackTrace();
}
HSSFSheet sheet = rwb.getSheetAt(0);
String retState = "";
// 获取总列数
int columnNum = sheet.getRow(1).getPhysicalNumberOfCells();
//总行数
int rowsNum=sheet.getLastRowNum();
int offset = 1;// 偏移量:从第几行开始的数据
for (int j = offset; j <= sheet.getLastRowNum(); j++) {// j的值为导入表格第一行数据的行索引
HSSFRow row = sheet.getRow(j);// 获取表格中索引为J行的整行数据
jsonObject = new JSONObject();
String stcd = "";
String tm = "";
String bd = "";
String z = "";
HSSFCell cell = row.getCell(0);
String retCellStr1 = (j + 1) + "," + 1;
stcd = getCellValue1(cell);
if (stcd != null) {
if(stcd.equals("formula")) {
retState = retCellStr1 + "*" + "存在公式";
break;
}
jsonObject.put("STCD", stcd);
}
cell = row.getCell(1);
retCellStr1 = (j + 1) + "," +2;
tm = getCell(cell);
if(tm != null){
if(tm.equals("formula")) {
retState = retCellStr1 + "*" + "存在公式";
break;
}
tm=tm+" 08:00:00";
jsonObject.put("TM", tm);
}
cell = row.getCell(2);
retCellStr1 = (j + 1) + "," +3;
bd = getCellValue1(cell);
if(bd != null){
if(bd.equals("formula")) {
retState = retCellStr1 + "*" + "存在公式";
break;
}
jsonObject.put("BD", bd);
jsonObject.put("RMSY", "");
}
cell = row.getCell(3);
retCellStr1 = (j + 1) + "," +4;
z = getCellValue1(cell);
if(z != null){
if(z.equals("formula")) {
retState = retCellStr1 + "*" + "存在公式";
break;
}
jsonObject.put("Z", z);
}
jsonObject.put("TS", ts);
jsonObject.put("NT", "");
array.put(jsonObject);
detestr+=stcd+"_"+tm+",";
if(!retState.equals("")){
break;
}
}
String conQuery = array.toString();
retState = retState.equals("") ? conQuery+"="+detestr.substring(0,detestr.length()-1) : "1";
return retState ;
}
/**
* 根据单元格类型取值,转成String
*
* @param cell
* @return
*/
public static String getCellValue1(Cell cell) {
String retStr = "";
if (cell == null) {
retStr = null;
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { // 空白
retStr = null;
}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { // 布尔类型
retStr = String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { // 数字类型
// Double d = cell.getNumericCellValue();
// retStr = String.valueOf(d);
/* 解决读取excel单元格小数的长度问题(float转double产生的) */
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
retStr = cell.getStringCellValue();
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { // 公式类型
retStr = "formula";
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { // 错误类型
retStr = null;
} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { // String类型
retStr = String.valueOf(cell.getStringCellValue());
if (retStr.trim().equals("")) {
retStr = null;
}
}else if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
if(HSSFDateUtil.isCellDateFormatted(cell)){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
retStr= sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
}
}
return retStr;
}
}