/**
* 药师点评的导入
* @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" onclick="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);' onclick='isOpen("
+ rowData.pharmacistCommentId
+ ")'>关闭 </a>";
} else {
html += "<a href='javascript:void(0);' onclick='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);
wsheet.mergeCells(3, 0, 7, 0);
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);
wsheet.mergeCells(4, 0, 8, 0);
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;
}
}
}