/** * 药师点评的导入 * @param request * @param response * @param f * @param tmallTcMessageImport * @return */ @RequestMapping(value="/importData") @ResponseBody public String importData(HttpServletRequest request,HttpServletResponse response,@RequestParam("uploadFile")MultipartFile f,PharmacistComment pharmacistComment) { String noGoods=""; Integer userId =Integer.parseInt( request.getSession().getAttribute("userId").toString()); try { InputStream is = f.getInputStream(); //文件流或文件未取到 if (StringUtil.isEmpty(pharmacistComment.getFileName())|| null==is){ return "error file"; } //文件名为.xls .xlsx以外 pharmacistComment.setFileName(URLDecoder.decode(pharmacistComment.getFileName(),"UTF-8")); if (pharmacistComment.getFileName().indexOf(".xls") == -1 && pharmacistComment.getFileName().indexOf(".xlsx") ==-1) { return "error file format"; } pharmacistComment.setAddTime(DateUtils.getCurrentDateString()); pharmacistComment.setAddUserId(userId); noGoods = pharmacistCommentService.savePharmacistCommentImport(pharmacistComment, is); } catch (Exception e) { e.printStackTrace(); return "system error"; } if(StringUtil.isNotEmpty(noGoods)){ return noGoods; } return "ok"; } ===================================================== /** * 导入文件 */ public String savePharmacistCommentImport( PharmacistComment pharmacistComment, InputStream is) throws Exception { String noGoods=""; String fieldValues = ""; pharmacistComment.setAddTime(pharmacistComment.getAddTime()); pharmacistComment.setAddUserId(pharmacistComment.getAddUserId()); // .xlsx的Excel文件 if (pharmacistComment.getFileName().indexOf(".xlsx") > -1) { XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); XSSFRow row; XSSFCell cell; // 获取第一个sheet XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); int rowNum = xssfSheet.getLastRowNum(); for (int i = 1; i <= rowNum; i++) { row = xssfSheet.getRow(i); // 商品编码 1 cell = row.getCell(0);// 【北京降压0号】 复方利血平氨苯蝶啶片 if (null != cell) { fieldValues = ""; if (cell.getCellType() == 0) { double cellValue = cell.getNumericCellValue(); fieldValues = new DecimalFormat("#").format(cellValue); } else if (cell.getCellType() == 1) { fieldValues = cell.getStringCellValue(); } } pharmacistComment .setGoodsNo((cell == null ? null : fieldValues)); //商品名称 2 cell = row.getCell(1);// 1115.0 if (null != cell) { fieldValues = ""; if (cell.getCellType() == 0) { double cellValue = cell.getNumericCellValue(); fieldValues = new DecimalFormat("#").format(cellValue); } else if (cell.getCellType() == 1) { fieldValues = cell.getStringCellValue(); } } pharmacistComment.setGoodsName(cell == null ? null : fieldValues); // 药师点评 3 cell = row.getCell(2); if (null != cell) { fieldValues = ""; if (cell.getCellType() == 0) { double cellValue = cell.getNumericCellValue(); fieldValues = new DecimalFormat("#").format(cellValue); } else if (cell.getCellType() == 1) { fieldValues = cell.getStringCellValue(); } } pharmacistComment .setPharmacistCommentMessage(cell == null ? null : fieldValues); //导入之前 先根据商品编码进行查询 PharmacistComment pharmacistComment2 = this .getGoodsByNo(pharmacistComment.getGoodsNo()); // 商品编码存在 if (pharmacistComment2 != null) { if (pharmacistComment2.getPharmacistCommentId() != null) { // 点评存在,做更新操作 pharmacistComment .setPharmacistCommentId(pharmacistComment2 .getPharmacistCommentId()); commonDao.update("PharmacistComment.update", pharmacistComment); } else { // 点评不存在,做新增操作 commonDao.save("PharmacistComment.save", pharmacistComment); } } else { // noGoods+=pharmacistComment.getGoodsNo()+","; // throw new Exception("GOODS_NO IS NOT EXISTS"); } } } else if (pharmacistComment.getFileName().indexOf(".xls") > -1) { XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); XSSFRow row; XSSFCell cell; // 获取第一个sheet XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0); int rowNum = xssfSheet.getLastRowNum(); for (int i = 1; i <= rowNum; i++) { row = xssfSheet.getRow(i); cell = row.getCell(1); if (null != cell) { fieldValues = ""; if (cell.getCellType() == 0) { double cellValue = cell.getNumericCellValue(); fieldValues = new DecimalFormat("#").format(cellValue); } else if (cell.getCellType() == 1) { fieldValues = cell.getStringCellValue(); } } pharmacistComment.setGoodsName(cell == null ? null : fieldValues); // 商品编码 cell = row.getCell(0); if (null != cell) { fieldValues = ""; if (cell.getCellType() == 0) { double cellValue = cell.getNumericCellValue(); fieldValues = new DecimalFormat("#").format(cellValue); } else if (cell.getCellType() == 1) { fieldValues = cell.getStringCellValue(); } } pharmacistComment .setGoodsNo((cell == null ? null : fieldValues)); // 药师点评 cell = row.getCell(2); if (null != cell) { fieldValues = ""; if (cell.getCellType() == 0) { double cellValue = cell.getNumericCellValue(); fieldValues = new DecimalFormat("#").format(cellValue); } else if (cell.getCellType() == 1) { fieldValues = cell.getStringCellValue(); } } pharmacistComment .setPharmacistCommentMessage(cell == null ? null : fieldValues); PharmacistComment pharmacistComment2 = this .getGoodsByNo(pharmacistComment.getGoodsNo()); // 商品编码存在 if (pharmacistComment2 != null) { if (pharmacistComment2.getPharmacistCommentId() != null) { // 点评存在,做更新操作 pharmacistComment .setPharmacistCommentId(pharmacistComment2 .getPharmacistCommentId()); commonDao.update("PharmacistComment.update", pharmacistComment); } else { // 点评不存在,做新增操作 commonDao.save("PharmacistComment.save", pharmacistComment); } } else { noGoods+=pharmacistComment.getGoodsNo()+","; } } } if(StringUtil.isNotEmpty(noGoods)){ noGoods = noGoods.substring(0,noGoods.lastIndexOf(',')); } return noGoods; }
=====================================================================
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <c:set var="ctx" value="${pageContext.request.contextPath}" /> <jsp:include page="/jsp/common/header.jsp"></jsp:include> <jsp:include page="/jsp/common/common_upload.jsp"></jsp:include> <script src="${ctx }/js/lib/ckeditor/ckeditor.js"></script> <script src="${ctx }/js/pharmacistComment/pharmacistComment.js"></script> <!-- 查询条件 --> <table class="queryTable"> <tr> <td class="queryTitle" width="80px">商品编号</td> <td class="queryContent"><input class="inputText" type="text" id="goodsNo" /></td> <td class="queryTitle" width="80px">商品名称</td> <td class="queryContent"><input class="inputText" type="text" id="goodsName" /></td> <td class="queryTitle" width="80px">状态</td> <td class="queryContent"> <select id="isOnline" class="easyui-combobox" style="width: 100px" panelHeight="auto"> <option value="">请选择</option> <option value="Y">开启</option> <option value="N">关闭</option> </select> </td> <td class="queryBtnTd"><a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-search" onclick="doQuery()">查 询</a> </td> </tr> </table> <!-- 上传 --> <tr style="border: 0"> <td> <div id="fsUploadProgress" width="100%" ></div> <div id="divStatus" style="display: none;"></div> <input id="btnCancel" type="button"disabled="disabled" style="display: none" /> </td> </tr> <div id="rslt" style="color:#F00;"></div> <div region="south" border="false" style="text-align:center;height:30px;line-height:30px;"> <span id="pharmacistCommentBtn"></span> </div> <table id="deliveryGrid"></table> <!-- 编辑页面 --> <div id="opreateHtml" class="easyui-window" title="" iconCls="icon-edit" style="width:600px; height:200px;text-align:center; background: #fafafa;"> <div class="easyui-layout" fit="true"> <div region="center" border="false" style="background:#fff;border:1px solid #ccc;"> <form id='addPharmacistComment'> <input type="hidden" id="idHidden" name="idHidden" value="" /> <table> <tr> <td class="queryTitle" width="80px">商品编码</td> <td class="queryContent" ><input class="inputText" type="text" id="addgoodsNo" /></td> <!-- <td class="queryBtnTd" > --> <!-- <a id="queryGoods" href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-search" οnclick="goodsQuery()">查 询</a> --> <!-- </td> --> <td class="queryBtnTd"> <lable id="queryGoods" ><a href="javascript:void(0)" class="easyui-linkbutton" iconCls="icon-search" onclick="goodsQuery()">查 询</a> </lable> </td> </tr> <tr> <td class="queryTitle" width="80px">商品名称</td> <td class="queryContent" ><input class="inputText" type="text" id="addgoodsName" readonly="true" /></td> </tr> <tr> <td class="queryTitle" width="80px">商品图片</td> <td class="queryContent" style="width: 100px;"> <img id="uploadImg" src="${ctx }/images/common/emptyImg.jpg" " style="border: solid 1px #ccc; padding: 3px; width: 250px; height: 136px; cursor: hand" /> </td> </tr> <tr> <td class="queryTitle">药师点评</td> <td class="queryContent" colspan="40" style="width: 765px;height:70px;"> <textarea id="addpharmacistCommentMessage" class="ckeditor" > </textarea> </td> </tr> </table> </form> </div> <div region="south" border="false" style="text-align:center;height:30px;line-height:30px;"> <a class="easyui-linkbutton" iconCls="icon-ok" href="javascript:void(0);" onclick="save();">提交</a> <a class="easyui-linkbutton" iconCls="icon-cancel" href="javascript:void(0);" onclick="closeWin();">取消</a> </div> </div> </div> ========================================================
$(function() { $("#opreateHtml").window("close"); $("#opreateHtmlGoods").window("close"); $("#deliveryGrid") .datagrid( { url : appPath + "/page/pharmacistCommentAct/getPage", height : "full", striped : true, remoteSort : false, pagination : true, rownumbers : true, singleSelect : false, queryParams : getQueryParam(), frozenColumns : [ [ { field : "ck", checkbox : true }, { field : "opt", title : "操作", width : 80, align : "center", formatter : function(value, rowData, rowIndex) { var html = ''; html += "<img class='op-enable' src='"+ appPath+ "/js/lib/jquery-easyui/themes/icons/pencil.png' onClick='showWin(" + rowData.pharmacistCommentId+ ")' title='编辑'/> "; html += "<img class='op-enable' src='"+ appPath+ "/js/lib/jquery-easyui/themes/icons/cancel.png' onClick='deletes(" + rowData.pharmacistCommentId+ ")' title='删除'/> "; if (rowData.isOnline == 'N') { html += "<a href='javascript:void(0);' οnclick='isOpen(" + rowData.pharmacistCommentId + ")'>关闭 </a>"; } else { html += "<a href='javascript:void(0);' οnclick='isClose(" + rowData.pharmacistCommentId + ")'>开启</a>"; } return html; } } ] ], columns : [ [ { field : 'goodsNo', title : '商品编号', width : 100, align : 'left', sortable : true }, { field : 'goodsName', title : '商品名称', width : 200, align : 'left', sortable : true }, { field : 'pharmacistCommentMessage', title : '药师点评', width : 250, align : 'left', sortable : true }, { field : 'editUserName', title : '操作人', width : 100, align : 'left', sortable : true } ] ], toolbar : [ { id : 'btnExcel', text : '导出文件', iconCls : 'icon-exportExcel', handler : function() { exportExcel(); } }, "-", { id : 'btnPass', text : '批量删除', iconCls : 'icon-cancel', handler : function() { deletes(''); } }, "-", { id : 'btnOpen', text : '批量启动', iconCls : 'icon-ok', handler : function() { isOpen(''); } }, "-", { id : 'btnClose', text : '批量关闭', iconCls : 'icon-cancel', handler : function() { isClose(''); } }, "-", { id : 'btnMessage', text : '药师点评设置', iconCls : 'icon-search', handler : function() { showWin(null); } } ] }); // 初始化SWFUpload上传按钮 initSWFUpload({ upload_url : appPath + "/page/pharmacistCommentAct/importData", file_dialog_start_handler : fileDialogStartFn, upload_start_handler : uploadStartFn, post_params : {}, file_types : "*.xls;*.xlsx", file_types_description : "Excel Files", button_placeholder_id : "pharmacistCommentBtn", upload_success_handler : importOk, debug : false, // 设置上传队列里最多能同时存放多少个文件 file_queue_limit : 1, custom_settings : { progressTarget : "fsUploadProgress", cancelButtonId : "btnCancel" } }) }); // 查询条件参数 function getQueryParam() { var pharmacistComment = new Object(); pharmacistComment.goodsNo = $.trim($("#goodsNo").val()); pharmacistComment.goodsName = $.trim($("#goodsName").val()); pharmacistComment.isOnline = $('#isOnline').combobox("getValue"); return pharmacistComment; } function doQuery() { $("#deliveryGrid").datagrid('load', getQueryParam()); } // 导出文件 function exportExcel() { $.messager .confirm( '提示信息', '您确定要导出到Excel?', function(r) { if (r) { var columns = $("#deliveryGrid") .datagrid("options").columns[0]; var stringTitle = ""; var stringFields = ""; for (var i = 0; i < columns.length; i++) { stringTitle = stringTitle + columns[i].title + ","; if (columns[i].title == "状态") { stringFields = stringFields + "isOnline,"; } else { stringFields = stringFields + columns[i].field + ","; } } var queryParams = $("#deliveryGrid").datagrid( "options").queryParams; queryParams.gridTitle = stringTitle; queryParams.gridField = stringFields; queryParams.moduleName = "药师点评.xls"; var formObj = $("<form></form>") .attr("method", "post") .attr( "action", appPath + "/page/pharmacistCommentAct/exportExcel"); formObj .append( "<input type='text' name='gridTitle'>") .append( "<input type='text' name='gridField'>") .append( "<input type='text' name='moduleName'>") .append( "<input type='text' name='isOnline'>") .append( "<input type='text' name='pharmacistCommentMessage'>") .append( "<input type='text' name='goodsNo'>") .append( "<input type='text' name='goodsName'>") .append( "<input type='text' name='editUserName'>") .append( "<input type='text' name='pharmacistCommentId'>") .css('display', 'none').appendTo("body"); formObj.form("load", queryParams); formObj.submit(); formObj.remove(); } }); } /** * 删除 */ function deletes(id) { var ids = ''; if (id == null || id == '') { var selections = $("#deliveryGrid").datagrid('getSelections'); if (selections == null || selections == '') { $.messager.alert('提示信息', '请选操作的记录', ''); return; } for (var i = 0; i < selections.length; i++) { ids += selections[i].pharmacistCommentId + ','; } } else { ids = id; } $.messager.confirm('提示信息', "您确定要删除吗？", function(r) { if (r) { $.post(appPath + "/page/pharmacistCommentAct/deleteIdsLogic", { "ids" : ids }, function(data) { $.messager.alert('提示信息', '删除成功', ''); doQuery(); }); } }); } /** * 批量启动 */ function isOpen(id) { var ids = ''; if (id == null || id == '') { var selections = $("#deliveryGrid").datagrid('getSelections'); if (selections == null || selections == '') { $.messager.alert('提示信息', '请选操作的记录', ''); return; } for (var i = 0; i < selections.length; i++) { ids += selections[i].pharmacistCommentId + ','; } } else { ids = id; } $.messager.confirm('提示信息', "您确定开启记录？", function(r) { if (r) { $.post(appPath + "/page/pharmacistCommentAct/isOpen", { "ids" : ids }, function(data) { $.messager.alert('提示信息', '操作数据成功', ''); doQuery(); }); } }); } /** * 批量关闭 */ function isClose(id) { var ids = ''; if (id == null || id == '') { var selections = $("#deliveryGrid").datagrid('getSelections'); if (selections == null || selections == '') { $.messager.alert('提示信息', '请选操作的记录', ''); return; } for (var i = 0; i < selections.length; i++) { ids += selections[i].pharmacistCommentId + ','; } } else { ids = id; } $.messager.confirm('提示信息', "您确定批量关闭记录？", function(r) { if (r) { $.post(appPath + "/page/pharmacistCommentAct/isClose", { "ids" : ids }, function(data) { $.messager.alert('提示信息', '操作数据成功', ''); doQuery(); }); } }); } /** * 编辑 * @param id */ function showWin(id) { $("#idHidden").val(""); CKEDITOR.instances.addpharmacistCommentMessage.setData(''); $("#addgoodsNo").val(""); $("#addgoodsName").val(""); $("#uploadImg").attr("src", ''); var iconStr = "icon-add"; var title = "添加药师点评设置"; if (id != null) { iconStr = "icon-edit"; title = "编辑药师点评设置"; $.post(appPath + "/page/pharmacistCommentAct/getBean/" + id, {}, function(data) { $("#idHidden").val(data.pharmacistCommentId); CKEDITOR.instances.addpharmacistCommentMessage .setData(data.pharmacistCommentMessage); $("#addgoodsNo").val(data.goodsNo); $("#addgoodsName").val(data.goodsName); $("#uploadImg").attr("src", data.imgUrl); }, "json"); $('#queryGoods').css('display', 'none'); } else { $('#queryGoods').css('display', 'block'); } $('#opreateHtml').window({ title : title, iconCls : iconStr, width : 800, height : 600, left : 200, modal : true, shadow : true, collapsible : false, minimizable : false, maximizable : false }); $('#opreateHtml').window('move', { top : 0 }); $('#opreateHtml').window('open'); } function closeWin(id) { $('#opreateHtml').window('close'); } function save() { var pharmacistComment = new Object(); var addpharmacistCommentMessage = CKEDITOR.instances.addpharmacistCommentMessage .getData(); if ($("#idHidden").val() != null && $("#idHidden").val() != "") { pharmacistComment.pharmacistCommentId = $("#idHidden").val(); } if ($("#addgoodsNo").val() == null || $("#addgoodsNo").val() == "") { $.messager.alert('提示', "商品编号不能为空", 'info'); return; } if (addpharmacistCommentMessage == null || addpharmacistCommentMessage == "") { $.messager.alert('提示', "药师点评不能为空", 'info'); return; } if(addpharmacistCommentMessage.length>300){ $.messager.alert('提示', "药师点评不能超过300字", 'info'); return; } pharmacistComment.goodsNo = $("#addgoodsNo").val(); pharmacistComment.pharmacistCommentId = $("#idHidden").val(); // 取值 赋值 pharmacistComment.pharmacistCommentMessage = addpharmacistCommentMessage; doAjax({ url : appPath + '/page/pharmacistCommentAct/saveOrUpdate', type : 'post', data : pharmacistComment, success : function(data) { if (data == 'ok') { $.messager.alert('提示信息', '操作成功', 'info'); $('#opreateHtml').window('close'); doQuery(); } else { $.messager.alert('提示信息', '系统错误，请联系管理员!', 'info'); return; } } }); } /** * 根据商品编码查询商品 */ function goodsQuery() { var obj = new Object(); obj.goodsNo = $("#addgoodsNo").val(); doAjax({ url : appPath + '/page/pharmacistCommentAct/getGoodsByNo', type : 'post', data : obj, success : function(data) { if (data != null && data != '') { $("#addgoodsNo").val(data.goodsNo); $("#addgoodsName").val(data.goodsName); $("#idHidden").val(data.pharmacistCommentId); CKEDITOR.instances.addpharmacistCommentMessage .setData(data.pharmacistCommentMessage); $("#uploadImg").attr("src", data.imgUrl); } else { $.messager.alert('提示信息', '没有该商品!', 'info'); return; } } }); } /** * 上传文件 * */ function uploadStartFn(file) { //向后台传参数 $("#fileName").val(file.name); var postobj = { "fileName":encodeURIComponent(file.name)}; swfUploadObj.setPostParams(postobj); // 开始提交 swfUploadObj.startUpload(); } /******************************************************************************* * 上传完成，后台返回结果回调方法 */ function importOk(file, serverData) { var serverDataObj = eval("(" + serverData + ")"); if (typeof (serverDataObj) != "undefined") { var progress = new FileProgress(file, swfUploadObj.customSettings.progressTarget); progress.setComplete(); progress.setStatus("完成."); progress.toggleCancel(false); if (serverDataObj == "ok") { doQuery(); return; }else if (serverDataObj == "system error") { $("#rslt").html("内部错误，请联系管理员!"); return; }else{ $("#rslt").html("下列商品编码不存在:<br/>"+serverDataObj); doQuery(); } } } /*** * 在文件选取窗口将要弹出时触发 */ function fileDialogStartFn(){ //清空结果 $("#rslt").html(""); //清空文件名 $("#fileName").val(''); }
package com.founder.ec.common.service.impl; import java.io.OutputStream; import java.lang.reflect.Method; import java.math.BigDecimal; import java.math.RoundingMode; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import jxl.Workbook; import jxl.write.WritableFont; import jxl.write.WritableWorkbook; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.founder.ec.admin.model.ExportExcelColumn; import com.founder.ec.base.service.impl.BaseServiceImpl; import com.founder.ec.common.dao.CommonDao; import com.founder.ec.common.service.ExportService; import com.founder.ec.order.model.OrderChannelRelInfo; import com.founder.ec.promote.model.EcCoupons; @Service public class ExportServiceImpl extends BaseServiceImpl implements ExportService { @Autowired private CommonDao commonDao; public String exportExcelFile(HttpServletRequest request,HttpServletResponse response, Object obj, String sqlMap) throws Exception { String fileChName = request.getParameter("moduleName"); String gridTitle = request.getParameter("gridTitle"); String gridField = request.getParameter("gridField"); response.reset(); response.setCharacterEncoding("ISO8859-1"); response.setHeader("Content-Disposition", "attachment; filename="+new String(fileChName.getBytes("GBK"), "ISO8859-1"));//fileChName为下载时用户看到的文件名 response.setHeader("Connection", "close"); response.setHeader("Content-Type", "application/octet-stream"); OutputStream out = response.getOutputStream(); Map map = new HashMap(); List<Object> paymentInfoList = commonDao.queryForList(sqlMap, obj); if(paymentInfoList.size()>10000){ WritableWorkbook wbook = Workbook.createWorkbook(out);//直接写入内存，不要存放到硬盘中 jxl.write.WritableSheet wsheet = wbook.createSheet("Sheet1", 0);//定义sheet的名称 jxl.write.WritableFont wfont = null; // 字体 jxl.write.WritableCellFormat wcfFC = null; // 字体格式 jxl.write.Label wlabelString = null; // Excel表格的Cell,文本格式 for(int i=0;i<1;i++){ wsheet.setColumnView(i, 40);//设置列宽 } // 设置excel标题字体 wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); wcfFC.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式 // 添加excel标题 jxl.write.Label wlabel1 = new jxl.write.Label(5, 0,"导出提示",wcfFC); wsheet.addCell(wlabel1); // 设置正文字体 wfont = new jxl.write.WritableFont(WritableFont.TIMES, 12, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); wcfFC.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式 //往Excel输出数据 wlabelString = new jxl.write.Label(5, 1, "导出数据超过一万条，系统不支持，请分批导出",wcfFC); wsheet.addCell(wlabelString); wbook.write(); // 写入文件 wbook.close(); out.flush(); out.close(); return "ok"; } String[] title = gridTitle.split(","); String[] propery = gridField.split(","); Integer titleNum = title.length;//Excel中字段的个数 String FileTitle = "";//Excel中正文标题 //--建立EXCEL索引、字段名、字段值的关系，存放到map中 for(int i=0;i<title.length;i++){ String PojoPropery = propery[i];//grid中title和field是一一对应的，所以可以这么写 String toUpp = PojoPropery.replaceFirst(PojoPropery.substring(0,1), PojoPropery.substring(0,1).toUpperCase());//把首字母转换为大写 String methodName = "get"+toUpp;//拼成pojo类中getXXX的方法名称 map.put(PojoPropery, new ExportExcelColumn(i,title[i],methodName)); } this.export(out, FileTitle, titleNum,paymentInfoList, map); return "ok"; } /** * 优惠券报表专用 add by lk 2014-10-9 */ public String exportExcelFile1(HttpServletRequest request,HttpServletResponse response, Object obj, String sqlMap) throws Exception { String fileChName = request.getParameter("moduleName"); String gridTitle = request.getParameter("gridTitle"); String gridField = request.getParameter("gridField"); response.reset(); response.setCharacterEncoding("ISO8859-1"); response.setHeader("Content-Disposition", "attachment; filename="+new String(fileChName.getBytes("GBK"), "ISO8859-1"));//fileChName为下载时用户看到的文件名 response.setHeader("Connection", "close"); response.setHeader("Content-Type", "application/octet-stream"); OutputStream out = response.getOutputStream(); Map map = new HashMap(); List<Object> paymentInfoList = commonDao.queryForList(sqlMap, obj); for(int i=0;i<paymentInfoList.size();i++){ EcCoupons ec = (EcCoupons) paymentInfoList.get(i); //ec.setNvlOrder(((new BigDecimal(ec.getSumOrderFee()).divide(new BigDecimal(ec.getCountMember()),2,RoundingMode.HALF_UP))).toString()); //ec.setOrderRate(((new BigDecimal(ec.getSumOrderFee()).subtract(new BigDecimal(ec.getSumCostFee())).divide(new BigDecimal(ec.getCountMember()),2,RoundingMode.HALF_UP))).toString()); } String[] title = gridTitle.split(","); String[] propery = gridField.split(","); Integer titleNum = title.length;//Excel中字段的个数 String FileTitle = "";//Excel中正文标题 //--建立EXCEL索引、字段名、字段值的关系，存放到map中 for(int i=0;i<title.length;i++){ String PojoPropery = propery[i];//grid中title和field是一一对应的，所以可以这么写 String toUpp = PojoPropery.replaceFirst(PojoPropery.substring(0,1), PojoPropery.substring(0,1).toUpperCase());//把首字母转换为大写 String methodName = "get"+toUpp;//拼成pojo类中getXXX的方法名称 map.put(PojoPropery, new ExportExcelColumn(i,title[i],methodName)); } this.export(out, FileTitle, titleNum,paymentInfoList, map); return "ok"; } /** * 订单渠道站点统计定制报表 * @param request * @param response * @param obj * @param sqlMap * @return * @throws Exception */ public String exportExcelFileColandRowSpan(HttpServletRequest request,HttpServletResponse response, Object obj, List<OrderChannelRelInfo> objList) throws Exception { String fileChName = request.getParameter("moduleName"); String gridTitle1 = request.getParameter("gridTitle1"); String gridField1 = request.getParameter("gridField1"); String gridTitle2 = request.getParameter("gridTitle2"); String gridField2 = request.getParameter("gridField2"); response.reset(); response.setCharacterEncoding("ISO8859-1"); response.setHeader("Content-Disposition", "attachment; filename="+new String(fileChName.getBytes("GBK"), "ISO8859-1"));//fileChName为下载时用户看到的文件名 response.setHeader("Connection", "close"); response.setHeader("Content-Type", "application/octet-stream"); OutputStream out = response.getOutputStream(); Map map= new HashMap(); //标题第一行 String[] title1 = gridTitle1.split(","); //标题第二行 String[] title2 = gridTitle2.split(","); String[] propery2 = gridField2.split(","); Integer titleNum = title2.length;//Excel中字段的个数 String FileTitle = "";//Excel中正文标题 for(int i=0;i<title2.length;i++){ String PojoPropery = propery2[i];//grid中title和field是一一对应的，所以可以这么写 String toUpp = PojoPropery.replaceFirst(PojoPropery.substring(0,1), PojoPropery.substring(0,1).toUpperCase());//把首字母转换为大写 String methodName = "get"+toUpp;//拼成pojo类中getXXX的方法名称 map.put(PojoPropery, new ExportExcelColumn(i,title2[i],methodName)); } OrderChannelRelInfo ocri = (OrderChannelRelInfo) obj; this.export1(out, FileTitle, titleNum , objList, map ,title1 ,ocri); return "ok"; } /** * 根据用户所选择的字段，动态导出成excel表格(通用方法，调用即可) * @author wudekai * os为输出流， * title为EXCEL正文标题， * titleNum为EXCEL标题字段总数， * dataList为要导出的数据， * map为列索引、标题、数据间的映射关系 */ @SuppressWarnings("unchecked") public void export1(OutputStream os,String title,Integer titleNum,List<OrderChannelRelInfo> objList,Map map,String [] str,OrderChannelRelInfo ocri) throws Exception{ WritableWorkbook wbook = Workbook.createWorkbook(os);//直接写入内存，不要存放到硬盘中 jxl.write.WritableSheet wsheet = wbook.createSheet("Sheet1", 0);//定义sheet的名称 jxl.write.WritableFont wfont = null; // 字体 jxl.write.WritableCellFormat wcfFC = null; // 字体格式 jxl.write.Label wlabelString = null; // Excel表格的Cell,文本格式 jxl.write.Number wlabelNumber=null; //Excel表格的Cell,数字格式 // 设置excel标题字体 wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); wcfFC.setAlignment(jxl.format.Alignment.CENTRE); // 设置对齐方式 // 添加excel标题 jxl.write.Label wlabel1 = new jxl.write.Label(5, 0, title,wcfFC); wsheet.addCell(wlabel1); // 设置列名字体 // 如果有标题的话，要设置一下偏移 int offset = 2; if(title == null || title.trim().equals("")){ offset = 0; }else{ wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); } if("Y".equals(ocri.getOrderBy())){ wsheet.mergeCells(0, 0, 0, 1);// 3(左列，左行，右列，右行) wsheet.mergeCells(1, 0, 1, 1); wsheet.mergeCells(2, 0, 2, 1); // 1 wsheet.mergeCells(3, 0, 7, 0); // 14 for (int i = 8; i <= 35; i = i + 2) { wsheet.mergeCells(i, 0, i + 1, 0);// } // 添加excel表头前三格 for (int i = 0; i < 3; i++) { jxl.write.Label lable1 = new jxl.write.Label(i, 0, str[i],wcfFC); wsheet.addCell(lable1); } // 添加第四个 jxl.write.Label lablet = new jxl.write.Label(3, 0, str[3], wcfFC); wsheet.addCell(lablet); int four = 4; for (int i = 8; i <= 35; i = i + 2) { jxl.write.Label lablef = new jxl.write.Label(i, 0, str[four], wcfFC); wsheet.addCell(lablef); four++; } }else{ wsheet.mergeCells(0, 0, 0, 1);// 3(左列，左行，右列，右行) wsheet.mergeCells(1, 0, 1, 1); wsheet.mergeCells(2, 0, 2, 1); wsheet.mergeCells(3, 0, 3, 1); // 1 wsheet.mergeCells(4, 0, 8, 0); // 14 for (int i = 9; i <= 36; i = i + 2) { wsheet.mergeCells(i, 0, i + 1, 0);// } //添加excel表头前四格 for(int i = 0 ; i < 4 ; i ++){ jxl.write.Label lable1 = new jxl.write.Label(i, 0, str[i],wcfFC); wsheet.addCell(lable1); } //添加第五个 jxl.write.Label lablet = new jxl.write.Label(4, 0, str[4],wcfFC); wsheet.addCell(lablet); int four = 5; for(int i = 9 ; i <= 36 ;i = i+2){ jxl.write.Label lablef = new jxl.write.Label(i, 0, str[four],wcfFC); wsheet.addCell(lablef); four++; } } @SuppressWarnings("rawtypes") Collection array1 = map.values(); @SuppressWarnings("rawtypes") Iterator it1 = array1.iterator(); while(it1.hasNext()){ ExportExcelColumn col = (ExportExcelColumn)it1.next(); if(col.getIndex()>2){ wlabelString = new jxl.write.Label(col.getIndex(), 1, col.getTitle(),wcfFC); wsheet.addCell(wlabelString); } } // 设置正文字体 wfont = new jxl.write.WritableFont(WritableFont.TIMES, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); //往Excel输出数据 int rowIndex = 2 + offset; @SuppressWarnings("rawtypes") Collection array = map.values(); for(Object obj:objList){//循环待导出的list数据集 @SuppressWarnings("rawtypes") Iterator it = array.iterator(); while(it.hasNext()){ ExportExcelColumn col = (ExportExcelColumn)it.next(); String value = ""; try { value = String.valueOf(invokeNoArgMethod(obj, col.getMethodName()));//利用反射机制，动态执行pojo类中get方法，获取属性值 if("null".equals(value)){ value = ""; } if(isNum(value)){ wlabelNumber = new jxl.write.Number(col.getIndex(), rowIndex,Double.parseDouble(value)); wsheet.addCell(wlabelNumber); }else{ wlabelString = new jxl.write.Label(col.getIndex(), rowIndex, value); wsheet.addCell(wlabelString); } } catch (Exception e) { e.printStackTrace(); } } rowIndex++; } wbook.write(); // 写入文件 wbook.close(); os.flush(); os.close(); } @Override public String exportExcelFile(HttpServletResponse response, Map<String,String> paramMap, List<Object> objList)throws Exception { String fileChName = paramMap.get("moduleName"); String gridTitle = paramMap.get("gridTitle"); String gridField = paramMap.get("gridField"); response.reset(); response.setCharacterEncoding("ISO8859-1"); response.setHeader("Content-Disposition", "attachment; filename="+new String(fileChName.getBytes("GBK"), "ISO8859-1"));//fileChName为下载时用户看到的文件名 response.setHeader("Connection", "close"); response.setHeader("Content-Type", "application/octet-stream"); OutputStream out = response.getOutputStream(); Map map = new HashMap(); String[] title = gridTitle.split(","); String[] propery = gridField.split(","); Integer titleNum = title.length;//Excel中字段的个数 String FileTitle = "";//Excel中正文标题 //--建立EXCEL索引、字段名、字段值的关系，存放到map中 for(int i=0;i<title.length;i++){ String PojoPropery = propery[i];//grid中title和field是一一对应的，所以可以这么写 String toUpp = PojoPropery.replaceFirst(PojoPropery.substring(0,1), PojoPropery.substring(0,1).toUpperCase());//把首字母转换为大写 String methodName = "get"+toUpp;//拼成pojo类中getXXX的方法名称 map.put(PojoPropery, new ExportExcelColumn(i,title[i],methodName)); } this.export(out, FileTitle, titleNum,objList, map); return "ok"; } /** * 根据用户所选择的字段，动态导出成excel表格(通用方法，调用即可) * @author wudekai * os为输出流， * title为EXCEL正文标题， * titleNum为EXCEL标题字段总数， * dataList为要导出的数据， * map为列索引、标题、数据间的映射关系 */ @SuppressWarnings("unchecked") public void export(OutputStream os,String title,Integer titleNum,List<Object> dataList,Map map) throws Exception{ WritableWorkbook wbook = Workbook.createWorkbook(os);//直接写入内存，不要存放到硬盘中 jxl.write.WritableSheet wsheet = wbook.createSheet("Sheet1", 0);//定义sheet的名称 jxl.write.WritableFont wfont = null; // 字体 jxl.write.WritableCellFormat wcfFC = null; // 字体格式 jxl.write.Label wlabelString = null; // Excel表格的Cell,文本格式 jxl.write.Number wlabelNumber=null; //Excel表格的Cell,数字格式 for(int i=0;i<titleNum;i++){ wsheet.setColumnView(i, 20);//设置列宽 } // 设置excel标题字体 wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); wcfFC.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式 // 添加excel标题 jxl.write.Label wlabel1 = new jxl.write.Label(5, 0, title,wcfFC); wsheet.addCell(wlabel1); // 设置列名字体 // 如果有标题的话，要设置一下偏移 int offset = 2; if(title == null || title.trim().equals("")){ offset = 0; }else{ wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); } //添加excel表头 @SuppressWarnings("rawtypes") Collection array1 = map.values(); @SuppressWarnings("rawtypes") Iterator it1 = array1.iterator(); while(it1.hasNext()){ ExportExcelColumn col = (ExportExcelColumn)it1.next(); wlabelString = new jxl.write.Label(col.getIndex(), offset, col.getTitle(),wcfFC); wsheet.addCell(wlabelString); } // 设置正文字体 wfont = new jxl.write.WritableFont(WritableFont.TIMES, 9, WritableFont.NO_BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); wcfFC = new jxl.write.WritableCellFormat(wfont); wcfFC.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式 //往Excel输出数据 int rowIndex = 1 + offset; @SuppressWarnings("rawtypes") Collection array = map.values(); for(Object obj:dataList){//循环待导出的list数据集 @SuppressWarnings("rawtypes") Iterator it = array.iterator(); while(it.hasNext()){ ExportExcelColumn col = (ExportExcelColumn)it.next(); String value = ""; try { value = String.valueOf(invokeNoArgMethod(obj, col.getMethodName()));//利用反射机制，动态执行pojo类中get方法，获取属性值 if("null".equals(value)){ value = ""; } if(isNum(value)){ wlabelNumber = new jxl.write.Number(col.getIndex(), rowIndex,Double.parseDouble(value),wcfFC); wsheet.addCell(wlabelNumber); }else{ wlabelString = new jxl.write.Label(col.getIndex(), rowIndex, value,wcfFC); wsheet.addCell(wlabelString); } } catch (Exception e) { e.printStackTrace(); } } rowIndex++; } wbook.write(); // 写入文件 wbook.close(); os.flush(); os.close(); } /** * (通用方法，调用即可) * 使用反射机制，动态执行方法（无参方法） * @author chen_zhenyu */ @SuppressWarnings("unchecked") public Object invokeNoArgMethod(Object owner,String methodName)throws Exception{ @SuppressWarnings("rawtypes") Class cls=owner.getClass(); Method method=cls.getMethod(methodName); return method.invoke(owner); } /** * 判断导出的单元格中是否是数值型值（大于十一位的数字，excel不支持，只能以字符串形式显示） * @param str * @return */ private boolean isNum(String str) { try { if(str.length()>11){ return false; } new BigDecimal(str); return true; } catch (Exception e) { return false; } } }
1830
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
