1.js使用datagrid时:
rownumbers:true 显示序号
frozenColumns:[[]] 冻结某些列
例如:
var $oneKeyAlarmHistorygrid;
$(function () {
$oneKeyAlarmHistorygrid = $("#oneKeyAlarmHistory-dg").datagrid({
method: "GET",
url: "${pageContext.request.contextPath}/oneKeyAlarmHistory/oneKeyAlarmHistoryList",
width: '100%',
pagination: true,
rownumbers: false,
border: true,
striped: true,
singleSelect: true,
pageList: [10, 20, 30, 40, 50],
frozenColumns:[[
{field: 'companyName2', title: '操作', width: '13%', align: 'center', sortable: false, formatter: oneKeyAlarmHistoryOptionButton},
]],
columns: [[{field: 'id', title: 'id', width: '30%', sortable: false, hidden: true},
{field: 'alarmType', title: '报警类型', width: '5%', sortable: false},
{field: 'customerPhone', title: '乘客手机号', width: '8%', sortable: false},
{field: 'carUserPhone', title: '司机手机号', width: '8%', sortable: false},
{field: 'carUserName', title: '司机姓名', width: '5%', sortable: false},
{field: 'orderId', title: '订单号', width: '16%', sortable: false},
{field: 'alarmAddress', title: '报警时地址', width: '25%', sortable: false},
{field: 'alarmMsg', title: '报警短信内容', width: '25%', sortable: false},
{field: 'alarmDate', title: '报警时间', width: '14%', sortable: false},
{field: 'isHandle', title: '状态', width: '5%', sortable: false,formatter:function(value){
if(value==0||value=="0"){
return "未处理";
}else if(value==1||value=="1"){
return "已处理";
}
}},
{field: 'updateName', title: '处理人', width: '5%', sortable: false},
{field: 'remarks', title: '处理意见', width: '10%', sortable: false},
{field: 'updateTime', title: '处理时间', width: '14%', sortable: false}
]], toolbar: '#oneKeyAlarmHistorytb',
onHeaderContextMenu: function (e, field) {
e.preventDefault();
if (!oneKeyAlarmHistoryCmenu) {
oneKeyAlarmHistoryCreateColumnMenu();
}
oneKeyAlarmHistoryCmenu.menu('show', {
left: e.pageX,
top: e.pageY
});
},
onLoadSuccess: function (data) {
$oneKeyAlarmHistorygrid.datagrid('resize', {
height: $(window).height()-$("#oneKeyAlarmHistory-search").height()-105
});
$('.button-delete').linkbutton({});
$('.button-edit').linkbutton({});
}
});
});
2.导入excel表格
html文件:
<div class="l-text">
<a id="xlsExcel" href="${pageContext.request.contextPath}/static/files/customerImport.xlsx" style="display: none;color: blue;text-decoration: underline;">《手机号信息模板下载》</a>
<td >
<a id="avatsel1"><input id="uploadToImportFile" name="uploadToImportFile" style="width: 190px;margin-top: 5px;" buttonText="选择文件" class="easyui-filebox"/></a>
<a href="javascript:void(0)" class="easyui-linkbutton" style="height: 27px;margin:6px 0px 0px 0px;" onclick="contactAjaxXlsUpload()" >开始导入</a>
<input type="hidden" id="xlsInfo"/>
</td>
</div>
js文件:
/*点击上传*/
function cilckImport() {
$("#avatsel1").click(function(){
$("#uploadToImportFile").trigger('click');
});
$("#uploadToImportFile").change(function(){
$("#uploadToImportFile").val($(this).val());
});
}
/*上传excel文件*/
var AllXlsExt=".xls|.xlsx|"//全部图片格式类型
function contactAjaxXlsUpload(){
var fileName=$("#uploadToImportFile").filebox('getValue');
var FileExt=fileName.substr(fileName.lastIndexOf(".")).toLowerCase();
if(fileName.trim()==""){
alert("请选择excel文件");
return;
}
if(AllXlsExt.indexOf(FileExt+"|") ==-1){
alert("请选择xls,xlsx格式的文件");
return;
}
//当文件正在上传时，弹出正在加载的弹出框，防止多次上传图片
load();
$.ajaxFileUpload({
url:"${pageContext.request.contextPath}/couponsSend/uploadXls2.action",
secureuri:false, //是否启用安全提交,默认为false
fileElementId:"avatsel1", //文件选择框的id属性
dataType:'text', //服务器返回的格式,可以是json或xml等
success:function(result){ //服务器响应成功时的处理函数
var result = eval('(' + result + ')');
if(result.status=="true"){
$.messager.alert('提示',"成功导入 "+result.data.successNum+" 条数据");
$("#customerPhones").textbox("setValue", result.data.msg);
}else {
$("#importFailedTable").html("<tr><td width=130px;>失败序号：</td><td width=130px;>失败原因</td></tr>");
uploadExcelStatus = 1;
if (result.data.numsList != null) {
$("#ifShow").val(1);
$.each(result.data.numsList, function (commentIndex, comment) {
var importFailedTable = $("#importFailedTable");
var tr = "<tr><td>" + commentIndex + "</td><td>" + comment + "</td></tr>";
importFailedTable.append(tr);
});
$("#couponSend-dd").dialog({
modal: true,
minimizable: false,//最小化，默认false
maximizable: false, //最大化，默认false
collapsible: false, //可折叠，默认false
resizable: true //可缩放，即可以通脱拖拉改变大小，默认false
}).dialog("open").dialog('setTitle', '导入失败详情');
$("#couponSend-dd").show();
couponsSendDialog.dialog('close');
}
}
}
});
}
jQuery.extend({
createUploadIframe: function(id, uri) {
//create frame
var frameId = 'jUploadFrame' + id;
var iframeHtml = '<iframe id="' + frameId + '" name="' + frameId + '" style="position:absolute; top:-9999px; left:-9999px"';
if(window.ActiveXObject){
if(typeof uri== 'boolean'){
iframeHtml += ' src="' + 'javascript:false' + '"';
}
else if(typeof uri== 'string'){
iframeHtml += ' src="' + uri + '"';
}
}
iframeHtml += ' />';
jQuery(iframeHtml).appendTo(document.body);
return jQuery('#' + frameId).get(0);
},
createUploadForm: function(id, fileElementId, data) {
//create form
var formId = 'jUploadForm' + id;
var fileId = 'jUploadFile' + id;
var form = jQuery('<form action="" method="POST" name="' + formId + '" id="' + formId + '" enctype="multipart/form-data"></form>');
if(data){
for(var i in data){
jQuery('<input type="hidden" name="' + i + '" value="' + data[i] + '" />').appendTo(form);
}
}
var oldElement = jQuery('#' + fileElementId);
var newElement = jQuery(oldElement).clone();
jQuery(oldElement).attr('id', fileId);
jQuery(oldElement).before(newElement);
jQuery(oldElement).appendTo(form);
//set attributes
jQuery(form).css('position', 'absolute');
jQuery(form).css('top', '-1200px');
jQuery(form).css('left', '-1200px');
jQuery(form).appendTo('body');
return form;
},
ajaxFileUpload: function(s) {
// TODO introduce global settings, allowing the client to modify them for all requests, not only timeout
s = jQuery.extend({}, jQuery.ajaxSettings, s);
var id = new Date().getTime()
var form = jQuery.createUploadForm(id, s.fileElementId, (typeof(s.data)=='undefined'?false:s.data));
var io = jQuery.createUploadIframe(id, s.secureuri);
var frameId = 'jUploadFrame' + id;
var formId = 'jUploadForm' + id;
// Watch for a new set of requests
if ( s.global && ! jQuery.active++ ) {
jQuery.event.trigger( "ajaxStart" );
}
var requestDone = false;
// Create the request object
var xml = {}
if ( s.global )
jQuery.event.trigger("ajaxSend", [xml, s]);
// Wait for a response to come back
var uploadCallback = function(isTimeout) {
var io = document.getElementById(frameId);
try{
if(io.contentWindow) {
xml.responseText = io.contentWindow.document.body?io.contentWindow.document.body.innerHTML:null;
xml.responseXML = io.contentWindow.document.XMLDocument?io.contentWindow.document.XMLDocument:io.contentWindow.document;
}else if(io.contentDocument){
xml.responseText = io.contentDocument.document.body?io.contentDocument.document.body.innerHTML:null;
xml.responseXML = io.contentDocument.document.XMLDocument?io.contentDocument.document.XMLDocument:io.contentDocument.document;
}
}catch(e){
jQuery.handleError(s, xml, null, e);
}
if ( xml || isTimeout == "timeout"){
requestDone = true;
var status;
try {
status = isTimeout != "timeout" ? "success" : "error";
// Make sure that the request was successful or notmodified
if ( status != "error" ){
// process the data (runs the xml through httpData regardless of callback)
var data = jQuery.uploadHttpData( xml, s.dataType );
// If a local callback was specified, fire it and pass it the data
if ( s.success )
s.success( data, status );
// Fire the global callback
if( s.global )
jQuery.event.trigger( "ajaxSuccess", [xml, s] );
} else
jQuery.handleError(s, xml, status);
} catch(e){
status = "error";
jQuery.handleError(s, xml, status, e);
}
// The request was completed
if( s.global )
jQuery.event.trigger( "ajaxComplete", [xml, s] );
// Handle the global AJAX counter
if ( s.global && ! --jQuery.active )
jQuery.event.trigger( "ajaxStop" );
// Process result
if ( s.complete )
s.complete(xml, status);
jQuery(io).unbind()
setTimeout(function(){
try{
jQuery(io).remove();
jQuery(form).remove();
} catch(e) {
jQuery.handleError(s, xml, null, e);
}
}, 100)
xml = null
}
}
// Timeout checker
if ( s.timeout > 0 ) {
setTimeout(function(){
// Check to see if the request is still happening
if( !requestDone ) uploadCallback( "timeout" );
}, s.timeout);
}
try {
var form = jQuery('#' + formId);
jQuery(form).attr('action', s.url);
jQuery(form).attr('method', 'POST');
jQuery(form).attr('target', frameId);
if(form.encoding){
jQuery(form).attr('encoding', 'multipart/form-data');
}else{
jQuery(form).attr('enctype', 'multipart/form-data');
}
jQuery(form).submit();
} catch(e){
jQuery.handleError(s, xml, null, e);
}
jQuery('#' + frameId).load(uploadCallback );
return {abort: function () {}};
}
})
工具实体类UploadXlsVo :
public class UploadXlsVo {
private String msg;
private List<Integer> nums;
private Map<Integer,String> numsList;
private Integer successNum;
public Integer getSuccessNum() {
return successNum;
}
public void setSuccessNum(Integer successNum) {
this.successNum = successNum;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public List<Integer> getNums() {
return nums;
}
public void setNums(List<Integer> nums) {
this.nums = nums;
}
public Map<Integer, String> getNumsList() {
return numsList;
}
public void setNumsList(Map<Integer, String> numsList) {
this.numsList = numsList;
}
}
控制器controller:
/**
* poi操作excel
* @param request
* @param response
* @return
* @throws Exception
*/
@RequestMapping(value = "/uploadXls2")
@ResponseBody
public Result uploadXls2(HttpServletRequest request, HttpServletResponse response) throws Exception{
Result result = new Result();
UploadXlsVo uploadXlsVo = couponsSendService.uploadXls(request,response);
if (uploadXlsVo.getNumsList()!=null) {
result.setStatus("false");
result.setData(uploadXlsVo);
return result;
}else {
result.setData(uploadXlsVo);
result.setStatus("true");
return result;
}
}
业务层:
/**
* 上传excel文件
* @param request
* @param response
* @return
*/
public UploadXlsVo uploadXls(HttpServletRequest request, HttpServletResponse response) {
UploadXlsVo uploadXlsVo = new UploadXlsVo();
Map<Integer, String> param = new HashMap<>();
List<Integer> nums = new ArrayList<>();
XSSFWorkbook wb;
XSSFSheet sheet;
XSSFRow row;
String str = "";
try {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile myfile = multipartRequest.getFile("uploadToImportFile");
DecimalFormat df = new DecimalFormat("#");
//将文件上传到微软云,返回url
multipartRequest.setCharacterEncoding("UTF-8");
response.setContentType("application/json;charset=UTF-8");
if (myfile.isEmpty()) {
System.out.println("文件未上传");
} else {
System.out.println("文件长度: " + myfile.getSize());
System.out.println("文件类型: " + myfile.getContentType());
System.out.println("文件名称: " + myfile.getName());
System.out.println("文件原名: " + myfile.getOriginalFilename());
System.out.println("========================================");
//如果用的是Tomcat服务器，则文件会上传到\\%TOMCAT_HOME%\\webapps\\YourWebProject\\WEB-INF\\upload\\文件夹中
String realPath = request.getSession().getServletContext().getRealPath("/WEB-INF/upload");
//这里不必处理IO流关闭的问题，因为FileUtils.copyInputStreamToFile()方法内部会自动把用到的IO流关掉，我是看它的源码才知道的
//将文件保存在upload文件夹中
FileUtils.copyInputStreamToFile(myfile.getInputStream(), new File(realPath, System.currentTimeMillis() + myfile.getOriginalFilename()));
InputStream is = myfile.getInputStream();
try {
wb = new XSSFWorkbook(is);
sheet = wb.getSheetAt(0);
// 得到总行数
int rowNum = sheet.getLastRowNum();
row = sheet.getRow(0);
int colNum = row.getPhysicalNumberOfCells();
int successCount = 0;
int errorCount = 0;
// 正文内容应该从第二行开始,第一行为表头的标题
for (int i = 1; i <= rowNum; i++) {
int j = 0;
row = sheet.getRow(i);
XSSFCell cell1 = row.getCell(j);
if(!String.valueOf(cell1).startsWith("1")||String.valueOf(cell1).length()!=15){
param.put(i+1,"该手机号码格式不正确！");
errorCount++;
}else {
while (j < colNum) {
//获得cell具体类型值的方式
if (row.getCell((short) j) != null) {
if (row.getCell((short) j).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
XSSFCell cell = row.getCell(j);
Map<String, Object> map = Maps.newHashMap();
map.put("phone", df.format(cell.getNumericCellValue()));
Customer authList = customerMapper.selectByPrimaryKey(df.format(cell.getNumericCellValue()).toString());
if (authList==null) {
param.put(i + 1, "该手机号码不存在");
nums.add(i + 1);
errorCount++;
} else {
if (str.indexOf(df.format(cell.getNumericCellValue())) != -1) {
param.put(i + 1, "该手机号码重复导入");
} else {
str = str + df.format(cell.getNumericCellValue()) + ",";
successCount++;
}
}
}
j++;
}
}
}
}
if (errorCount==0){
uploadXlsVo.setSuccessNum(successCount);
uploadXlsVo.setMsg(str);
return uploadXlsVo;
}
} catch (IOException e) {
e.printStackTrace();
log.error("Coupons-uploadXls2", e.getMessage());
}
}
}catch (Exception e){
log.error("Coupons-uploadXls2", e.getMessage());
}
uploadXlsVo.setNumsList(param);
uploadXlsVo.setMsg(str);
return uploadXlsVo;
}
3.导出excel表格:
js文件:
/**
* 导出报表
*/
function oneKeyAlarmHistoryExportExcel(){
//导出报表给出一个正在加载中的提示，防止用户多次点击导出按钮
window.location="${pageContext.request.contextPath}/oneKeyAlarmHistory/exportoneKeyAlarmHistory?customerPhone="+$("#oneKeyAlarmHistoryList-customerPhone").textbox('getValue')
+"&carUserPhone="+$("#oneKeyAlarmHistoryList-carUserPhone").textbox('getValue') +"&carUserName="+$("#oneKeyAlarmHistoryList-carUserName").textbox('getValue')
+"&orderId="+$("#oneKeyAlarmHistoryList-orderId").textbox('getValue')+"&isHandle="+$("#oneKeyAlarmHistoryList-isHandle").combobox('getValue')
+"&startTime="+$('#oneKeyAlarmHistoryList-startTime').datebox('getValue')+"&endTime="+$('#oneKeyAlarmHistoryList-endTime').datebox('getValue');
}
代码实现:
/**
* 导出数据
* @param request
* @param response
*/
public void exportOneKeyAlarmHistory(HttpServletRequest request, HttpServletResponse response) {
String orderId = request.getParameter("orderId");//类型
String customerPhone = request.getParameter("customerPhone");
String carUserPhone = request.getParameter("carUserPhone");
String carUserName = request.getParameter("carUserName");//
String isHandle = request.getParameter("isHandle");
String startTime = request.getParameter("startTime");
String endTime = request.getParameter("endTime");
Map<String, Object> params = new HashMap<String, Object>();
if(StringUtils.isNotBlank(orderId)){
params.put("orderId", orderId);
}
if(StringUtils.isNotBlank(customerPhone)){
params.put("customerPhone", customerPhone);
}
if(StringUtils.isNotBlank(carUserPhone)){
params.put("carUserPhone", carUserPhone);
}
if(StringUtils.isNotBlank(carUserName)){
params.put("carUserName", carUserName);
}
if(StringUtils.isNotBlank(isHandle)){
params.put("isHandle",isHandle);
}
if(StringUtils.isNotBlank(startTime)){
params.put("startTime", startTime);
}
if(StringUtils.isNotBlank(endTime)){
params.put("endTime", endTime);
}
//调用对应的service取出要导出的发票信息
List<Map<String, Object>> queryResults = oneKeyAlarmHistoryMapper.exportOneKeyAlarmHistory(params);
String excelName = "一键报警记录" + UtilDate.getDate() + ".xlsx";// 导出报表文件名;
String sheetName = "一键报警记录";
String[] title = { "序号", "报警类型","乘客手机号","司机手机号","司机姓名","报警时间","订单号","报警时地址","报警短信内容","状态","处理人","处理意见","处理时间"};// 表头（列名）
List<Object[]> dataList = new ArrayList<Object[]>();
if (queryResults != null && queryResults.size() > 0) {
int i = 1;//用于导出excel时，记录序号
for (Map<String, Object> hashMap : queryResults) {
if (hashMap != null && hashMap.size() > 0) {
Object[] strList = new Object[title.length];
strList[0] = i;
strList[1] = hashMap.get("ALARM_TYPE");
strList[2] = hashMap.get("CUSTOMER_PHONE");
strList[3] = hashMap.get("CAR_USER_PHONE");
strList[4] = hashMap.get("CAR_USER_NAME");
strList[5] = hashMap.get("ALARM_DATE")!=null?new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(hashMap.get("ALARM_DATE")):"";
strList[6] = hashMap.get("ORDER_ID");
strList[7] = hashMap.get("ALARM_ADDRESS");
strList[8] = hashMap.get("ALARM_MSG");
strList[9] = hashMap.get("IS_HANDLE");
strList[10] = hashMap.get("UPDATE_NAME");
strList[11] = hashMap.get("REMARKS");
strList[12] = hashMap.get("UPDATE_TIME")!=null?new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(hashMap.get("UPDATE_TIME")):"";
dataList.add(strList);
}
i++;
}
}
PoiExportExcel.expExcelSheet(excelName, sheetName, title, dataList, response);// 调用导出方法类
}
PoiExportExcel类:
/**
* excel导出
*
* @param excelName excel表的名称
* @param sheetName sheet名称
* @param title 列名称数组
* @param dataList 主数据
* @param response
*/
public static void expExcelSheet(String excelName, String sheetName, String[] title, List<Object[]> dataList, HttpServletResponse response){
try {
XSSFWorkbook workbook = new XSSFWorkbook();// 创建Excel工作簿
XSSFSheet sheet = workbook.createSheet(sheetName);
sheet.createFreezePane(0, 1, 0, 1);// 冻结前1行
sheet.setDefaultColumnWidth((short) 30);// 设置表格默认列宽度为15个字节
XSSFCellStyle style = workbook.createCellStyle();// 标题行样式
XSSFFont font = workbook.createFont();// 生成一个字体
font.setFontHeightInPoints((short) 11);
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗
style.setFont(font);// 把字体应用到当前的样式
XSSFRow row = sheet.createRow(0);// 创建第一行（列名）
XSSFCell cell = null;
if (title != null && title.length > 0) {
for (int i = 0; i < title.length; i++) {
//sheet.autoSizeColumn(i);//设置列宽自适应
cell = row.createCell(i);
if (title[i] != null) {
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
}
}
// 遍历主数据
if (dataList != null && dataList.size() > 0) {
Iterator<Object[]> it = dataList.iterator();
int index = 0;
while (it.hasNext()) {
index++;
Row nextrow = sheet.createRow(index);
Object[] obj = it.next();
if (obj != null) {
int objLen = obj.length;
for (int i = 0; i < objLen; i++) {
Cell cell2 = null;
cell2 = nextrow.createCell(i);
if (obj[i] != null) {
cell2.setCellValue(obj[i].toString());
}
}
}
}
}
// 输出
OutputStream outputStream = response.getOutputStream();
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(excelName,"UTF8"));
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
