web项目和rest项目的导入和导出Excel
一、web项目的导入和导出Excel
1、web项目导入Excel--jsp页面
--js代码
function doUpload(){
if($("#file").val()==null || $("#file").val()==''){
SimplePop.alert('请选择文件');
return;
}
var upFileName = $("#file").val();
var index1=upFileName.lastIndexOf(".");
var index2=upFileName.length;
var suffix=upFileName.substring(index1+1,index2);//后缀名
if( suffix != "xls" && suffix !="xlsx"){
SimplePop.alert('文件格式不正确');
return;
}
//$("#importForm").submit();
$.ajaxFileUpload({
url:"importOrder",
type: 'post',
dataType: 'json',
data : {
id : $("#id").val(),
name: $("#name").val()
},
contentType:"multipart/form-data; text/xml;charset=utf-8",
secureuri:false,
cache: false,//防止缓存
fileElementId:'file',
success: function (data) {
if(data.status == 'SUCCESS'){
var msg = '导入数据完成！共导入数据：'+**+'条';
SimplePop.confirmS(msg+',点击确定刷新页面',{
confirm : function (){
window.location.reload();
}
});
}else{
SimplePop.confirmS(data.msg,{
confirm : function (){
window.location.reload();
}
});
}
},error: function (data, status, e){
SimplePop.alert("导入失败，***");
}
});
}
--html部分代码
<!--弹出导入窗口-->
<div class="md-modal md-effect-19" id="modal-8" style="width: 40%">
<div class="md-content">
<h3>订单导入</h3>
<div>
<!-- 关闭按钮 -->
<button class="md-close" id="close">X</button>
<form id="importForm" action="importOrder"
enctype="multipart/form-data" method="post">
<table class="table6">
<tr>
<td>ID：</td>
<td>
<select name="id" id="id">
<c:forEach items="${list}" varStatus="status" var="bean">
<option value="${bean.key}">${bean.id}</option>
</c:forEach>
</select>
</td>
<td>名称：</td>
<td>
<input id="name" name="name" value="${bean.name}">
</td>
</tr>
<tr>
<td colspan="2"><input type="file" id="file" name="file" style="width:100%"></td>
<td ></td>
<td ></td>
</tr>
</table>
<div class="btn-wrapper">
<a class="icon-search" id="" title="确定导入" href="javascript:doUpload()">确定导入</a>
</div>
</form>
</div>
</div>
</div>
--后台代码
--Controller.java
@Controller
public class OrderController {
@Autowired
private OrderConsumer orderConsumer;
@RequestMapping(value = { "/order/importOrder" }, method = { RequestMethod.POST })
@ResponseBody
public Map<String,Object> importOrder(@RequestParam("file") MultipartFile file,String name,String id){
Map<String,Object> result = new HashMap<String,Object>();
try {
result = orderConsumer.importFile(file,name,id);
} catch (ScnException e) {
result.put("status", "ERROR");
result.put("msg", "导入失败:" + e.getMessage().substring(e.getMessage().indexOf(":") + 1));
}
return result;
}
}
--Consumer.java
@Service("orderConsumer")
public class OrderConsumer {
private static final Logger log= LoggerFactory.getLogger(OrderConsumer.class);
@Autowired
private OrderService orderService;
@Transactional(readOnly = false,rollbackFor = Exception.class)
public Map<String,Object> importFile(MultipartFile file,String name,String id) throws ScnException{
Map<String,Object> result = new HashMap<String,Object>();
try {
//获取数据
List<Map<String, Object>> list = ExportExcelUtil.readExcel(file);
result = orderService.importFile(list,clientCode,orderType,whid,enterpriseid);
return result;
} catch (Exception e) {
log.error("-------------->>>>>>>>>>导入出错");
log.error(e.getMessage());
ScnException exp = new ScnException(e);
exp.setErrorCode(ItemContants.ERROR_CODE);
exp.setErrorMsg(ItemContants.ERROR_SELECT_MESSAGE);
throw exp;
}
}
}
--Service.java
public Map<String,Object> importFile(List<Map<String, Object>> list,String clientCode,String orderType,String whid,long enterpriseid) throws Exception{
Map<String,Object> result = new HashMap<String,Object>();
//如果目标表不存在则创建
if(!orderDao.checkIfExistsTable("temp_import_order")){
orderDao.createTableWhitTableName("temp_import_order");
}
//如果备份表不存在则创建
if(!orderDao.checkIfExistsTable("temp_import_order_bak")){
orderDao.createTableWhitTableName("temp_import_order_bak");
}
//导入前备份临时表数据
orderDao.backupData();
//导入前删除临时表数据
orderDao.deleteFromTemp();
//导入临时表
int linenumber = 1;
int handelNumber = 0;
int orderNumber = 0;
String lastOrderNumber = "";
String thisOrderNmuber = "";
//column12
for(Map<String, Object> map : list){
thisOrderNmuber = map.get("column0").toString();
if(thisOrderNmuber.equals(lastOrderNumber)){
linenumber++;
}else{
linenumber = 1;
orderNumber ++;
}
map.put("column12", linenumber);
map.put("id", id);
map.put("name", name);
***;
orderDao.insertIntoTemp(map);
lastOrderNumber = thisOrderNmuber;
handelNumber ++;
}
result.put("handelNumber", handelNumber);
result.put("orderNumber", orderNumber);
//判断文件中订单号重复数据
List<String> duplicaOrderInFile = orderDao.selectDuplicaOrderInFile();
if(!UtilHelper.isEmpty(duplicaOrderInFile)){
throw new Exception("文件中订单号:" + Arrays.toString(duplicaOrderInFile.toArray()) + "重复");
}
//获取导入重复数据
int duplicaNumber = orderDao.selectDuplicationData();
result.put("duplicaNumber", duplicaNumber);
if(duplicaNumber>0){
List<String> orders = orderDao.selectDuplicationOrder();
result.put("orders", Arrays.toString(orders.toArray()));
}
//删除重复数据
orderDao.deleteDuplicationData();
Map<String,Object> reqMap = new HashMap<String,Object>();
reqMap.put("id", id);
reqMap.put("name", name);
***;
result.put("status", "SUCCESS");
return result;
}
2、web项目导出Excel
--js代码
function exportOrderList(){
var whSear="${bean.id}";
var nameSear="${bean.namecode}";
**
if(!document.getElementsByName("radio").checked){//判断是否选中单选框
**
}
var names= "";
names+= $(".scrollContent input[type='radio'][name='radio']:checked").closest('tr').find('td:eq(3) a').map(function(){return this.innerHTML}).get().join();
postExport("exportOrderList", {"id":idSear,"names":names});
}
function postExport(URL, PARAMS) {
var temp = document.createElement("form");
temp.action = URL;
temp.method = "post";
temp.style.display = "none";
for (var x in PARAMS) {
var opt = document.createElement("textarea");
opt.name = x;
opt.value = PARAMS[x];
temp.appendChild(opt);
}
document.body.appendChild(temp);
temp.submit();
return temp;
}
--html部分代码
<!-- 表格数据 -->
<div class="page-content">
<!-- /.page-header -->
<div class="row">
<div class="col-xs-6" style="margin-top: 3px;">
<button class="btn_new btn-primary btn-default btn-bold" onclick="exportOrderList()">导出订单</button>
</div>
<div class="col-xs-6" style="margin-top: 5px; float: right;">
<div class="dataTables_paginate paging_simple_numbers"
id="sample-table-2_paginate">
<ul class="pagination">
<%@include file="orderPager.jsp"%>
</ul>
</div>
</div>
</div>
</div>
<div class="hr hr-18 dotted"></div>
<div class="row">
<div class="col-xs-12">
<div class="row">
<div class="col-xs-12">
<div id="chenkbox">
<table id="sample-table-2" class="table table-bordered">
<thead class="fixedHeader">
<tr>
<td class="tc min-5">选择</td>
<td class="tc min-5" sort="string"><span>序号</span>
<div class="ww"></div></td>
<td class="tc min-10" sort="string"><span>ID</span>
<div class="ww"></div></td>
<td class="tc min-10" sort="string"><span>名称</span>
<div class="ww"></div></td>
****
</tr>
</thead>
<tbody class="scrollContent" id="List">
<c:forEach items="${page.results}" varStatus="status"
var="order">
<tr>
<td class="min-5 td_center"><input type="radio"
value="${order.ordernumber}" name="radio" /></td>
<td class="min-5 td_center">${status.index+1}</td>
<td class="min-10 td_center">${order.id}</td>
<td class="min-10 td_center">${order.name}</td>
****
</tr>
</c:forEach>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
--后台代码
--Controller.java
@Controller
public class OrderController {
@Autowired
private OrderConsumer orderConsumer;
@RequestMapping(value="/order/exportOrderList", method = { RequestMethod.GET,RequestMethod.POST })
@ResponseBody
public String exportOrderList(OrderSearchDTO bean, HttpServletResponse response, String names) throws UnsupportedEncodingException {
if (bean == null) {
bean = new OrderSearchDTO();
}
if(!com.suneee.scn.common.util.StringUtils.isBlank(names)){
String[] nameArray = names.split(",");
List<String> nameList = new ArrayList<String>();
for(String temp : nameArray){
if(!com.suneee.scn.common.util.StringUtils.isBlank(temp.trim())){
nameList.add(temp.trim());
}
}
bean.setNameList(nameList);
}
Workbook workbook = null;
OutputStream os = null;
try {
workbook = orderConsumer.exportOrderList(bean);
String fileName="***表.xlsx";
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));
os = response.getOutputStream();
workbook.write(os);
}
catch (Exception e){
e.printStackTrace();
}
return null;
}
}
--Consumer.java
@Service("orderConsumer")
public class OrderConsumer {
private static final Logger log= LoggerFactory.getLogger(OrderConsumer.class);
@Autowired
private OrderService orderService;
@Transactional(readOnly=true)//, HttpServletResponse response
public Workbook exportOrderList(OrderSearchDTO bean){
Workbook wb = null;
com.suneee.scn.common.pageNew.Page<Map<String, Object>> page = new com.suneee.scn.common.pageNew.Page();
try {
List<Map<String, Object>> list = orderService.getexportOrderList(bean);
page.setResults(list);
ExcelBean bean2 = new ExcelBean();
bean2.setDataList(list);
String[] excelHeader = { "序号","ID","名称",***};
String[] keyArray = { "index","id","name",***};
bean2.setKeyArray(keyArray);
bean2.setExcelHeader(excelHeader);
bean2.setTitleCol(excelHeader.length-1);
bean2.setTableTitle("***表");
bean2.setAddTitle(true);// 第一次插入要设置标题
wb = ExportExcelUtil.createSimpleWorkbookNumToString(bean2);
} catch (Exception e) {
e.printStackTrace();
}
return wb;
}
}
--Service.java
public List<Map<String, Object>> getexportOrderList(OrderSearchDTO bean) {
com.suneee.scn.common.pageNew.Page<Map<String, Object>> pageNew = new com.suneee.scn.common.pageNew.Page<Map<String, Object>>();
//转换excel内容
pageNew.setPageSize(65535);
pageNew.setPageNo(1);
Map<String, Object> reqMap = new HashMap<String, Object>();
int pageNo = pageNew.getPageNo();
int pageSize = pageNew.getPageSize(); // 页大小
int start = (pageNo - 1) * pageSize;
reqMap.put("start", start);
reqMap.put("pageSize", pageSize);
reqMap.put("id", bean.getId());
reqMap.put("name", bean.getNameList());
int totalCount = orderDao.getOrderTotal(reqMap);
List<Map<String, Object>> list= orderDao.getOrderList(reqMap);
pageNew.setResults(list);
pageNew.setTotalCount(totalCount); // 总条数
pageNew.setPageCount(totalCount % pageSize == 0 ? totalCount / pageSize
: totalCount / pageSize + 1); // 总页数
return list;
}
一、rest项目的导入和导出Excel
1、rest项目导入Excel和rest项目导出Excel
--js代码
<script type="text/javascript">
var wmsUrl = '****';
var OrderConfig={};
//初始化列表
var _table;
$(function () {
/* var OrderConfig={}; */
OrderConfig.URL=wmsUrl; //正式URL前缀 carInspeStrUrl：本模块前缀路径
//列表
OrderConfig.listUrl=OrderConfig.URL+"orderRest/getOrderPage";
//导入订单
OrderConfig.importOrderUrl=OrderConfig.URL+"orderRest/importOrder";
//导出订单
OrderConfig.exportOrderUrl=OrderConfig.URL+"orderRest/exportOrder";
//表格的属性对象，用于初始化表格的设置
var option = {
plusBtn:[{
id:"wms-queryBtn", //按钮id
text:"查询", //显示的文字
clazz:"btn btn-primary", //按钮的class
iconClass:"fa fa-search" // 按钮的icon (使用的是 fontawesome 参考http://fontawesome.io/icons/)
},{
id:"wms-orderImportBtn", //按钮id
text:"导入订单", //显示的文字
clazz:"btn btn-primary", //按钮的class
iconClass:"fa fa-search" // 按钮的icon (使用的是 fontawesome 参考http://fontawesome.io/icons/)
},{
id:"wms-exportOrderBtn", //按钮id
text:"导出订单", //显示的文字
clazz:"btn btn-primary", //按钮的class
iconClass:"fa fa-search" // 按钮的icon (使用的是 fontawesome 参考http://fontawesome.io/icons/)
}],
onInit:function(){
// 表格初始化成功的回调,在这这里可以对自定义的东西进行一些初始化配置
// 在表格初始化成功后,再给按钮添加时间
//查询
$("#wms-queryBtn").on("click",function(){
selectline();
});
$("#wms-orderImportBtn").on("click",function(){
orderImpor();
});
$("#wms-exportOrderBtn").on("click",function(){
exportOrder();
});
},
handleCol: false,
radioCol: true,
seniorSearch: false,
search: true,
tools: true,
// 表格的头部，有多少列，就写多少
columns: [{
filed: "ID",
name: "id"
}, {
filed: "名称",
name: "name"
},***],
//渲染信息
columnDefs : [{
render : function(data, type, row) { // 格式化 列
return '<a href="javascript:detailList(\''+ row.id +'\',\''+ row.id +'\');">'+ data +'</a>';
},
targets : [3]
},***],
// 表格获取信息的后台服务器地址
url: OrderConfig.listUrl,
ajax: function (data, callback, settings) {
//ajax配置为function,手动调用异步查询
$.ajax({
type: "GET",
url: OrderConfig.listUrl,
data: {},
dataType: "json",
success: function (result) {
layer.msg("接口调用成功");
var finalData = result.data[0];
//封装返回数据
var returnData = {};
returnData.draw = data.draw;//自行返回draw参数,最好由后台返回
returnData.recordsTotal = finalData.totalCount;
returnData.recordsFiltered = finalData.pageSize;
returnData.data = finalData.results;
//调用DataTables提供的callback方法，代表数据已封装完成并传回DataTables进行渲染
//此时的数据需确保正确无误，异常判断应在执行此回调前自行处理完毕
callback(returnData);
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
layer.msg("查询失败");
}
});
}
};
var table = suneeeUI.seTable("wms-order-table", option);
//查询弹框
function selectline(){
layer.open({
title:"查询",
type: 1,
area: ['1005px', '230px'], //宽高
content: template('gc-bsearchSingTemp',{}),
success:function(){
//获取仓库下拉
getWhid();
getClient();
getStatus();
getCarrierCode();
initDatepicker();
//查询确定按钮
$("#gc-bsearchBtn").on("click",function(e){
//获取搜索框查询条件
var tempData = $('#gc-bsearchForm').serializeArray();
var formData = {};
for (var i = 0; i < tempData.length; i++) {
formData[tempData[i].name] = tempData[i].value;
}
var searchVal = [
{name:"id",value:formData.id},
{name:"name",value:formData.name},
***
];
//调用表格做高级查询
table.search(searchVal);
layer.close(layer.index); //关闭当前弹层
clearData("gc-bsearchForm"); //清空查询值
});
}
});
}
//导入
function orderImpor(){
var enterpriseInfoDetail = template("gc-orderImportTemp", {});
var index = layer.open({
type: 1,
title: "订单导入",
area: ['650px', '230px'], //宽高
content: enterpriseInfoDetail,
success: function () {
getWhid();
getClient();
},
btn: ["确定","取消"],
yes: function () {
if($("#file").val()==null || $("#file").val()==''){
layer.msg('请选择文件');
return;
}
var upFileName = $("#file").val();
var index1=upFileName.lastIndexOf(".");
var index2=upFileName.length;
var suffix=upFileName.substring(index1+1,index2);//后缀名
if( suffix != "xls" && suffix !="xlsx"){
layer.alert('文件格式不正确');
return;
}
var importData = new FormData($("#gc-importForm" )[0]);
importData.append("whid", $("#whid").val());
importData.append("name", $("#name").val());
$.ajax({
url:OrderConfig.importOrderUrl,
type: 'POST',
dataType: 'json',
data : importData,
cache: false,
contentType:false,
processData: false,
success: function (data) {
if(data.returnCode==1){
var msg = '导入数据完成！共导入数据：'+data.data[0].handelNumber+'条，总订单：'+data.data[0].orderNumber+'条，重复订单：'+data.data[0].duplicaNumber+'条';
if(data.data[0].duplicaNumber>0){
msg += '，重复订单号：'+data.data[0].orders;
}
layer.alert(msg);
layer.close(index);
table.reload();
/* SimplePop.confirmS(msg+',点击确定刷新页面',{
confirm : function (){
window.location.reload();
}
}); */
}else{
layer.alert(data.msg);
}
},error: function (XMLHttpRequest, textStatus, errorThrown){
layer.msg("导入出错，请联系管理员");
}
});
},
no:function () {
layer.close(editopen);
}
});
}
function downLoadTemplate(){
window.open(OrderConfig.downLoadTemplate);
}
// 导出
var exportCondition = '';
function exportOrder(){
var sessionId = GetQueryString("sessionId");
if(exportCondition==null || exportCondition==''){
exportCondition = 'null';
}
if(sessionId == null){
alert("cookie中无法获取sessionId");
return;
}
window.location.href = OrderConfig.exportOrderUrl + "/" + sessionId + "/" + exportCondition;
}
function GetQueryString(name)
{
var cookies = document.cookie.split(";");
for(var i=0; i<cookies.length; i++){
if(cookies[i].indexOf(name)!=-1){
return cookies[i].substring(cookies[i].indexOf("=")+1);
}
}
return null;
}
//重置条件按钮 pri-clearSearchBtn
$("#suneeeArea").on("click","#gc-bclearSearchBtn",function(e){
clearData("gc-bsearchForm");
});
/**
* 清空
* @param win 上级元素id
* @param classname 有这个class属性的不清空
*/
function clearData(win,classname) {
$("#" + win + " input[type='text']").each(function() {
if(classname!=undefined&&$(this).hasClass(classname))return true;
$(this).val('');
});
$("#" + win + " input[type='checkbox']").each(function() {
if(classname!=undefined&&$(this).hasClass(classname))return true;
$(this).attr("checked", false);
});
$("#" + win + " select").each(function() {
if(classname!=undefined&&$(this).hasClass(classname))return true;
$(this).val("");
});
$("#" + win + " textarea").each(function() {
if(classname!=undefined&&$(this).hasClass(classname))return true;
$(this).val("");
});
}
function getWhid(){
//发送请求
$.ajax({
url: OrderConfig.whid,
type: "GET",
dataType : "json",
async: false,
data: null,
success:function(data){
var result = eval(data);
var list = result.data[0];
if(data != null){
for(var i = 0; i < list.length; i++){
$("#whid").append("<option value='"+list[i].whid+"'>"
+ list[i].whname + "</option>")
}
}
}
});
}
function getClient(){
//发送请求
$.ajax({
url: OrderConfig.clientcode,
type: "GET",
dataType : "json",
async: false,
data: null,
success:function(data){
var result = eval(data);
var list = result.data[0];
if(data != null){
for(var i = 0; i < list.length; i++){
$("#clientcode").append("<option value='"+list[i].clientcode+"'>"
+ list[i].clientname + "</option>")
}
}
}
});
}
function getStatus(){
//发送请求
$.ajax({
url: OrderConfig.statusList,
type: "GET",
dataType : "json",
data: {"source":"sp_order_main","type":"STATUS"},
async: false,
success:function(data){
var result = eval(data);
var list = result.data[0];
if(data != null){
for(var i = 0; i < list.length; i++){
$("#status").append("<option value='"+list[i].text+"'>"
+ list[i].description + "</option>")
}
}
}
});
}
function getCarrierCode(){
//发送请求
$.ajax({
url: OrderConfig.carrierCodeList,
type: "GET",
dataType : "json",
data: null,
async: false,
success:function(data){
var result = eval(data);
var list = result.data[0];
if(data != null){
for(var i = 0; i < list.length; i++){
$("#carriercode").append("<option value='"+list[i].carriercode+"'>"
+ list[i].carriername + "</option>")
}
}
}
});
}
function initDatepicker(){
$('.input-datepicker').datepicker({
format: 'yyyy-mm-dd',
weekStart: 1,
todayBtn: 1,
autoclose: true,
language: 'zh-CN'
});
}
});
function detailList(whid,ordernumber){
var orderDetailTemp = template("gc-orderDetailTemp", {});
var index = layer.open({
type: 1,
title: "订单详情",
area: ['650px', '230px'], //宽高
content: orderDetailTemp,
success: function () {
$.ajax({
type: "GET",
url: OrderConfig.orderDetailListUrl,
data: {"ordernumber":ordernumber,"whid":whid},
dataType: "json",
success: function (result) {
if(result.returnCode==1){
var html = '';
for(var i = 0; i < result.data[0].length; i++){
html += '<tr style="word-break: keep-all;white-space:nowrap;">';
html += '<td>' + (i + 1) + '</td>';
html += '<td>' + (result.data[0][i].ordernumber == null ? '' : result.data[0][i].ordernumber) + '</td>';
html += '<td>' + (result.data[0][i].itemnumber == null ? '' : result.data[0][i].itemnumber) + '</td>';
html += '<td>' + (result.data[0][i].displayitemnumber == null ? '' : result.data[0][i].displayitemnumber) + '</td>';
html += '<td>' + (result.data[0][i].description == null ? '' : result.data[0][i].description) + '</td>';
html += '<td>' + (result.data[0][i].qty == null ? '' : result.data[0][i].qty) + '</td>';
html += '</tr>';
}
$("#orderDetail").append(html);
console.log(result.data);
console.log(result.data[1]);
}else{
layer.msg(result.msg);
}
},
error: function (XMLHttpRequest, textStatus, errorThrown) {
layer.msg("查询失败");
}
});
}
});
}
</script>
--html部分代码
--后台代码
--OrderRestServiceImpl.java
@Service
@Path("/orderRest")
public class OrderRestServiceImpl implements OrderRestService {
//定义日志输出工具
private static final Logger log = LoggerFactory.getLogger(Logger.class);
@Autowired
private OrderConsumer orderConsumer;
/**
* 导入订单
*/
@POST
@Path("/importOrder")
@Consumes(MediaType.MULTIPART_FORM_DATA)
@Produces("application/json;charset=utf-8;")
@Override
public JsonResult importOrder(MultipartFormDataInput file, @HeaderParam("sessionId") String sessionId) {
JsonResult result=JsonResult.createSuccess();
try {
List<Map<String, Object>> importList = null;
String whid = null;
String orderType = null;
String clientCode = null;
Map<String, List<InputPart>> uploadForm = file.getFormDataMap();
for (Entry<String, List<InputPart>> entry: uploadForm.entrySet()) {
List<InputPart> inputParts = entry.getValue();
if(entry.getKey().equals("file")){
String fileName = "";
MultivaluedMap<String, String> header = inputParts.get(0).getHeaders();
fileName = getFileName(header);
InputStream inputStream = inputParts.get(0).getBody(InputStream.class, null);
importList = ExportExcelUtil.readExcel(inputStream, fileName);
continue;
}
if(entry.getKey().equals("id")){
id = inputParts.get(0).getBody(String.class,null);
continue;
}
if(entry.getKey().equals("name")){
name= inputParts.get(0).getBody(String.class,null);
continue;
}
}
SystemUserInfoT u = CacheUtils.get(SystemUserInfoT.class, sessionId);
if (u == null || u.getAccount() == null) {
result=JsonResult.createSuccess();
result.setMsg("用户信息为空");
result.setReturnCode(-1);
result.addData("");
return result;
}
Map<String, Object> map = orderConsumer.importFile(importList,clientCode,orderType,whid,u.getEnterpriseid());
result.setMsg("导入成功");
result.addData(map);
return result;
} catch (Exception e) {
log.error(e.getMessage());
return JsonResult.createFalied("导入失败" + e.getMessage().substring(e.getMessage().indexOf(":") + 1));
}
}
private String getFileName(MultivaluedMap<String, String> header) {
String[] contentDisposition = header.getFirst("Content-Disposition").split(";");
for (String filename : contentDisposition) {
if ((filename.trim().startsWith("filename"))) {
String[] name = filename.split("=");
String finalFileName = name[1].trim().replaceAll("\"", "");
return finalFileName;
}
}
return "unknown";
}
/**
* 导出订单
*/
@GET
@Path("/exportOrder/{sessionId}/{exportCondition}")
@Produces("application/vnd.ms-excel; charset=UTF-8")
@Override
public Response exportOrder(@PathParam("exportCondition") String exportCondition,@PathParam("sessionId") String sessionId){
net.sf.json.JSONObject jsonObject = net.sf.json.JSONObject.fromObject(exportCondition);
OrderSearchDTO bean = new OrderSearchDTO();
SystemUserInfoT u = CacheUtils.get(SystemUserInfoT.class, sessionId);
ResponseBuilder response = null;
ByteArrayOutputStream baos = null;
try {
if(!"null".equals(jsonObject) && !UtilHelper.isEmpty(jsonObject)){
String id= jsonObject.getString("id");
bean.setId(id);
}
***
Workbook workbook = orderConsumer.exportOrderList(bean);
baos = new ByteArrayOutputStream();
workbook.write(baos);
response = Response.ok(baos.toByteArray());
String fileName = null;
fileName = new String(("***表.xls").getBytes("UTF-8"), "ISO-8859-1");
response.header("Content-Disposition","attachment; filename="+fileName);
response.encoding("UTF-8");
return response.build();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
log.error("UnsupportedEncodingException:"+e);
} catch (Exception ex) {
ex.printStackTrace();
log.error("导出订单表异常:"+ex);
}
return null;
}
}
--Consumer.java
--Consumer.java
@Service("orderConsumer")
public class OrderConsumer {
private static final Logger log= LoggerFactory.getLogger(OrderConsumer.class);
@Autowired
private OrderService orderService;
//导入
@Transactional(readOnly = false,rollbackFor = Exception.class)
public Map<String,Object> importFile(List<Map<String, Object>> list,String clientCode,String orderType,String whid,long enterpriseid) throws ScnException{
Map<String,Object> result = new HashMap<String,Object>();
try {
//获取数据
result = orderService.importFile(list,clientCode,orderType,whid,enterpriseid);
return result;
} catch (Exception e) {
log.error("-------------->>>>>>>>>>导入出错");
log.error(e.getMessage());
ScnException exp = new ScnException(e);
exp.setErrorCode(ItemContants.ERROR_CODE);
exp.setErrorMsg(ItemContants.ERROR_SELECT_MESSAGE);
throw exp;
}
}
//导出
@Transactional(readOnly=true)//, HttpServletResponse response
public Workbook exportOrderList(OrderSearchDTO bean){
Workbook wb = null;
com.suneee.scn.common.pageNew.Page<Map<String, Object>> page = new com.suneee.scn.common.pageNew.Page();
try {
List<Map<String, Object>> list = orderService.getexportOrderList(bean);
page.setResults(list);
ExcelBean bean2 = new ExcelBean();
bean2.setDataList(list);
String[] excelHeader = { "序号","ID","名称",***};
String[] keyArray = { "index","id","name",***};
bean2.setKeyArray(keyArray);
bean2.setExcelHeader(excelHeader);
bean2.setTitleCol(excelHeader.length-1);
bean2.setTableTitle("***表");
bean2.setAddTitle(true);// 第一次插入要设置标题
wb = ExportExcelUtil.createSimpleWorkbookNumToString(bean2);
} catch (Exception e) {
e.printStackTrace();
}
return wb;
}
}
}
--Service.java
public List<Map<String, Object>> getexportOrderList(OrderSearchDTO bean) {
com.suneee.scn.common.pageNew.Page<Map<String, Object>> pageNew = new com.suneee.scn.common.pageNew.Page<Map<String, Object>>();
//转换excel内容
pageNew.setPageSize(65535);
pageNew.setPageNo(1);
Map<String, Object> reqMap = new HashMap<String, Object>();
int pageNo = pageNew.getPageNo();
int pageSize = pageNew.getPageSize(); // 页大小
int start = (pageNo - 1) * pageSize;
reqMap.put("start", start);
reqMap.put("pageSize", pageSize);
reqMap.put("id", bean.getId());
reqMap.put("name", bean.getNameList());
int totalCount = orderDao.getOrderTotal(reqMap);
List<Map<String, Object>> list= orderDao.getOrderList(reqMap);
pageNew.setResults(list);
pageNew.setTotalCount(totalCount); // 总条数
pageNew.setPageCount(totalCount % pageSize == 0 ? totalCount / pageSize
: totalCount / pageSize + 1); // 总页数
return list;
}
浙公网安备 33010602011771号