QuFileUtil
web_uploader.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>上传文件</title>
<link rel="stylesheet" href="../css/table.css">
<script src="../js/table.js"></script>
<script>
$(function(){
var uploader = WebUploader.create({
// swf文件路径
swf: '../webuploader/orther/Uploader.swf',
auto: false,
// 文件接收服务端。
server: '/webUploader/upload',
// 选择文件的按钮。可选。
// 内部根据当前运行是创建，可能是input元素，也可能是flash.
pick: {
id: '#picker',
name:"multiFile", //这个地方 name 没什么用，和fileVal 配合使用。
multiple:false //默认为true，true表示可以多选文件，HTML5的属性
},
// 不压缩image, 默认如果是jpeg，文件上传前会压缩一把再上传！
resize: false,
fileVal:'multiFile', //后台通过该属性获取
chunked: true, //分片处理
chunkSize: 1 * 1024 * 1024, //每片2M
chunkRetry:2, //如果失败，则不重试
threads:1, //上传并发数。允许同时最大上传进程数。
fileNumLimit:1 //上传的文件总数
});
// 当有文件被添加进队列的时候
uploader.on( 'fileQueued', function( file ) {
var $list = $("#thelist");
$list.append( '<div id="' + file.id + '" class="item">' +
'<h4 class="info">' + file.name + '</h4>' +
'<p class="state">等待上传...</p>' +
'</div>'
);
});
// 文件上传过程中创建进度条实时显示。
uploader.on( 'uploadProgress', function( file, percentage ) {
var $li = $( '#'+file.id ),
$percent = $li.find('.progress .progress-bar');
// 避免重复创建
if ( !$percent.length ) {
$percent = $('<div class="progress progress-striped active">' +
'<div class="progress-bar" role="progressbar" style="width: 0%">' +
'</div>' +
'</div>').appendTo( $li ).find('.progress-bar');
}
$li.find('p.state').text('上传中');
$percent.css( 'width', percentage * 100 + '%' );
});
//完成上传，不管成功或者失败，先把遮罩删除。
uploader.on('uploadComplete', function(file) {
enabledButton();
});
// 文件上传成功之后进行的处理。
uploader.on('uploadSuccess', function(file, response) {
var $li = $( '#'+file.id );
if(response.flag){
$li.find('p.state').html("<span style='font-weight: bold;font-size: 200%;color: #2ec08b'>上传成功!</span>");
}else{
$li.find('p.state').html("<span style='font-weight: bold;font-size: 200%;color: #fa190c'>上传失败：" + response.failedReason + "</span>");
}
});
// 文件上传失败，显示上传出错。这个目前没什么用，因为我把提示信息放在遮罩了，几乎是闪现不见，所以该方法可以再次修改
uploader.on('uploadError', function(file) {
var $li = $( '#'+file.id );
$li.find('p.state').html("<span style='font-weight: bold;font-size: 200%;color: #fa190c'>服务端异常，上传失败，请稍后再试！</span>");
});
$("#ctlBtn").on("click", function() {
disabledButton();
uploader.upload();
});
//禁用BTN
function disabledButton(){
$("#ctlBtn").attr("disabled",true).css({"cursor" : "not-allowed"});
//$("#ctlBtn").css("cursor","not-allowed");
$("#picker").find("input[type='file']").attr("disabled",true);
$("#picker").find("label").css({
"cursor" : "not-allowed",
"opacity" : "0.6"
});
}
//启用BTN
function enabledButton(){
$("#ctlBtn").attr("disabled",false).css({"cursor" : "pointer"});
$("#picker").find("input[type='file']").attr("disabled",false);
$("#picker").find("label").css({
"cursor" : "pointer",
"opacity" : "0"
});
}
});
</script>
</head>
<body>
<div id="uploader" class="wu-example">
<!--用来存放文件信息-->
<div id="thelist" class="uploader-list"></div>
<div class="btns">
<div id="picker">选择文件</div>
<button id="ctlBtn" class="btn btn-default">开始上传</button>
</div>
</div>
</body>
</html>
package springboot5.controller;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import springboot5.util.QuFileUtil;
@Controller
@RequestMapping("/webUploader")
public class WebUploaderController {
@RequestMapping("/go")
public String goWebuploader(){
return "web_uploader";
}
@RequestMapping("/upload")
@ResponseBody
public Map<String, Object> upload(MultipartHttpServletRequest request, HttpServletResponse response) {
String realPath = "E:\\test";
Map<String, Object> result = new HashMap<>();
try {
Integer chunks = request.getParameter("chunks") == null ? null : Integer.parseInt(request.getParameter("chunks"));
Integer chunk = request.getParameter("chunk") == null ? null : Integer.parseInt(request.getParameter("chunk"));
MultipartFile file = request.getFile("multiFile");
if (chunk == null) { //不分片的情况
QuFileUtil.saveFile(file, realPath);
} else { //分片的情况
QuFileUtil.piecewiseUpload(chunks, chunk, file, realPath);
}
result.put("flag", true);
} catch (Exception e) {
result.put("flag", false);
result.put("failedReason", e.getMessage());
e.printStackTrace();
}
return result;
}
}
--------------------------------------------------------------
package springboot5.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.channels.FileChannel;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.tomcat.util.http.fileupload.FileUtils;
import org.springframework.util.FileCopyUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;
/**
* @author qufeng
* 377600191
*/
public class QuFileUtil {
private final static String _STATIC = "static";
private final static String _DATEFORMAT = "yyyyMMddHHmmss";
/**
* 单文件分片上传
* @param chunks 分片个数
* @param chunk 分片索引
* @param file 分片
* @param path 目标位置
* @throws IOException
*/
public static void piecewiseUpload(int chunks, int chunk, MultipartFile file, String path) throws IOException {
// 获取分片输入流
InputStream partIn = file.getInputStream();
path = path.endsWith(File.separator) ? path : (path += File.separator);
// 存放分片的临时目录
String tempDir = path + File.separator + "chunks";
File temp = new File(tempDir);
if (!temp.exists()) {
temp.mkdirs();
}
//目标分片
File partFile = new File(tempDir + File.separator + chunk + ".part");
FileOutputStream partOut = new FileOutputStream(partFile);
//拷贝目标分片
FileCopyUtils.copy(partIn, partOut);
// 已经上传到最后一个分片，//开始合并
if (chunk == (chunks - 1)) {
//获取上传文件路径 和 上传文件路径+文件名称
Map<String, String> target = getTarget(file, path);
String targetDir = target.get("targetDir");
String targetFileName = target.get("targetFileName");
//目标文件目录
File dir = new File(targetDir);
if (!dir.exists()) {
dir.mkdirs();
}
//目标文件
File targetFile = new File(targetFileName);
//目标channel
@SuppressWarnings("resource")
FileChannel targetChannel = new FileOutputStream(targetFile, true).getChannel();
//循环合并分片到目标文件
for(int i = 0; i < chunks; i ++){
File part = new File(tempDir + File.separator + i + ".part");
if(!part.exists()){
throw new RuntimeException("No part was found when merging");
}
@SuppressWarnings("resource")
FileChannel partChannel = new FileInputStream(part).getChannel();
targetChannel.transferFrom(partChannel, targetChannel.size(), partChannel.size());
partChannel.close();
}
targetChannel.close();
// 删除临时目录中的分片文件
FileUtils.deleteDirectory(temp);
}
}
/**
* 上传文件到指定目录
* @param file 源文件
* @param path 目标路径
* @return 文件保存的路径
* @throws IOException
*/
public static String saveFile(MultipartFile file, String path) throws IOException{
//获取上传文件路径 ， 上传文件路径+文件名称，保存到数据库的文件路径
Map<String, String> target = getTarget(file, path);
String targetDir = target.get("targetDir");
String targetFileName = target.get("targetFileName");
String pathName = target.get("pathName");
//目标目录
File dir = new File(targetDir);
if(!dir.exists()){
dir.mkdirs();
}
//创建目标文件并copy
InputStream in = file.getInputStream();
File targetFile = new File(targetFileName);
FileOutputStream out = new FileOutputStream(targetFile);
FileCopyUtils.copy(in, out);
//返回需要保存的路径有可能是相对路径，也可能是绝对路径
return pathName;
}
/**
* 下载指定目录的文件
* @param pathName 源文件目录及名称
* @param targetName 目标文件名称
* @param response
* @throws IOException
*/
@SuppressWarnings("resource")
public static void downLoadFile(String pathName,String targetName,HttpServletResponse response) throws IOException{
response.setContentType("application/x-msdownload;");
response.setHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode(targetName, "UTF-8"));
File file = new File(pathName);
if(!file.exists()){
throw new IOException("file : " + pathName + "not found");
}
FileInputStream in = new FileInputStream(file);
ServletOutputStream out = response.getOutputStream();
byte[] b = new byte[1024];
int len;
while((len = in.read(b)) != -1){
out.write(b, 0, len);
}
out.flush();
out.close();
}
/**
* 删除文件
* @param pathname
*/
public static void deleteFile(String pathname) {
File file = new File(pathname);
if(file.exists()) file.delete();
}
/**
* 下载zip文件
* @param map 多个输入流的map
* @param response
* @throws Exception
*/
public static void downLoadZip(Map<String, InputStream> map, HttpServletResponse response) throws Exception{
//zip流
ZipOutputStream out = new ZipOutputStream(response.getOutputStream());
//设置压缩方式
out.setMethod(ZipOutputStream.DEFLATED);
//迭代map中的流并压缩进zip,map中key为zip中子文件的名称
Set<Entry<String, InputStream>> entrySet = map.entrySet();
for (Entry<String, InputStream> entry : entrySet) {
out.putNextEntry(new ZipEntry(entry.getKey()));
InputStream in = entry.getValue();
byte[] b = new byte[1024];
int len;
while((len = in.read(b)) != -1){
out.write(b, 0, len);
}
in.close();
}
out.flush();
out.close();
}
/**
* 判断path是绝对路径还是相对路径
* @param file
* @param path
* @return map 返回目标位置字符串和目标文件名称(带路径)
* @throws FileNotFoundException
*/
private static Map<String, String> getTarget(MultipartFile file, String path) throws FileNotFoundException {
String dateFlag = new SimpleDateFormat(_DATEFORMAT).format(new Date()) + Math.round(((Math.random() * 1000 + 1))) + "_"
+ file.getOriginalFilename().substring(0, file.getOriginalFilename().lastIndexOf("."));
// 返回一个路径字符串可能是相对路径，也可能是绝对路径
String pathName = "";
// 目标文件绝对路径
String targetDir = "";
// 目标文件绝对路径+名称
String targetFileName = "";
if (path.contains("/")) {
// 相对路径 pathName和targetPathName不同
path = path.endsWith("/") ? path : (path += "/");
path += dateFlag;
pathName = path + "/" + file.getOriginalFilename();
String _classpath = ResourceUtils.getURL(ResourceUtils.CLASSPATH_URL_PREFIX).getPath();
String _path = path.replaceAll("/", File.separator + File.separator);
// 目标绝对路径
targetDir = _classpath + File.separator + _STATIC + _path;
targetFileName = targetDir + File.separator + file.getOriginalFilename();
} else { // 绝对路径 pathName和targetPathName相同
path = path.endsWith(File.separator) ? path : (path += File.separator);
path += dateFlag;
targetDir = path;
pathName = path + File.separator + file.getOriginalFilename();
targetFileName = pathName;
}
HashMap<String, String> map = new HashMap<>();
map.put("targetDir", targetDir);
map.put("targetFileName", targetFileName);
map.put("pathName", pathName);
return map;
}
}
SpecialPlanController
package com.want.special.controller;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
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 org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.want.common.util.ExceptionUtil;
import com.want.special.po.CanalSpecialPlan;
import com.want.special.service.ISpecialPlanService;
import com.want.special.util.DateFormat;
import com.want.special.util.ExportSpecialPlanTools;
import com.want.sso.client.thread.UserThreadLocal;
@Controller
@RequestMapping(value = "specialplanimport")
public class SpecialPlanController {
private static final Logger LOGGER = Logger.getLogger(SpecialPlanController.class);
@Autowired
private ISpecialPlanService specialPlanService;
/**
* <p>Description: 初始化特陈年月</p>
* <p>Company: 上海旺旺集团有限公司</p>
* @author 00311332
* @date 2018年5月17日 上午9:44:48
*/
@RequestMapping(value = "initYearMonth")
@ResponseBody
public Map<String, Object> initYearMonth(String flag) {
Map<String, Object> map = new HashMap<String, Object>();
List<String> yearMonthList = null;
try {
if("1".equals(flag)){
yearMonthList = DateFormat.getNowAndNextDate();
}
else{
yearMonthList = DateFormat.getNearDate();
}
map.put("yearMonth", yearMonthList);
map.put("status", true);
} catch (Exception e) {
map.put("status", false);
LOGGER.error(ExceptionUtil.getTrace(e));
}
return map;
}
/**
* <p>Description: 初始化特陈年月</p>
* <p>Company: 上海旺旺集团有限公司</p>
* @author 00311332
* @throws Exception
* @date 2018年5月17日 上午9:44:48
*/
@RequestMapping(value = "importPlanTemplate")
@ResponseBody
public void importPlanTemplate(HttpServletRequest request,HttpServletResponse response) throws Exception {
// 下载本地文件
File file = new File(request.getServletContext().getRealPath("/") + "Template/现渠特陈计划导入模板.xlsx");
// 读到流中
InputStream inStream = new FileInputStream(file);// 文件的存放路径
// 设置输出的格式
response.reset();
response.setContentType("application/vnd.ms-excel; charset=utf-8");
response.addHeader("Content-Disposition", "attachment; filename=\"" + java.net.URLEncoder.encode(file.getName(), "UTF-8") + "\"");
// 循环取出流中的数据
byte[] b = new byte[100];
int len;
try {
while ((len = inStream.read(b)) > 0)
response.getOutputStream().write(b, 0, len);
inStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* <p>Description: 导入特陈计划</p>
* <p>Company: 上海旺旺集团有限公司</p>
* @author 00311332
* @date 2018年5月17日 下午1:16:55
*/
@RequestMapping(value="importSpecialPlan",method = RequestMethod.POST)
public void carPhotoUpload(@RequestParam("file") MultipartFile file,String company,HttpServletResponse response){
try {
List<CanalSpecialPlan> list = specialPlanService.createCanalSpecialPlan(file,company,UserThreadLocal.get().getAccount(),response);
if(list.size() > 0){
responseImportSpecialPlanErrorReport(response,list);
}
}catch (IOException e) {
e.printStackTrace();
}
}
/**
* <p>Description: 导出特陈计划</p>
* <p>Company: 上海旺旺集团有限公司</p>
* @author 00311332
* @date 2018年5月22日 下午1:30:12
*/
@RequestMapping(value = "exportSpecialPlan", method = RequestMethod.GET)
@ResponseBody
public void exportSpecialPlan(HttpServletRequest request, HttpServletResponse response,
String yearMonth,String companyId,String companyIds) throws Exception {
List<Map<String, Object>> list = specialPlanService.exportCanalSpecialPlan(yearMonth,companyId,companyIds);
HSSFWorkbook workbook = ExportSpecialPlanTools.buildExcelDocument(list);
response.setCharacterEncoding("utf-8");
response.setContentType("application/force-download");// 设置强制下载不打开
String fileName = "Canal_Special_Plan_Report.xls";
response.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
workbook.write(response.getOutputStream());
workbook.close();
}
/**
* <p>Description: 上传文件</p>
* <p>Company: 上海旺旺集团有限公司</p>
* @author 00311332
* @date 2018年5月17日 下午1:16:32
*/
public static void write(InputStream in, OutputStream out) throws IOException{
try{
byte[] buffer = new byte[1024];
int bytesRead = -1;
while ((bytesRead = in.read(buffer)) != -1) {
out.write(buffer, 0, bytesRead);
}
out.flush();
} finally {
try {
in.close();
}
catch (IOException ex) {
}
try {
out.close();
}
catch (IOException ex) {
}
}
}
/**
* <p>Description: 上传文件</p>
* <p>Company: 上海旺旺集团有限公司</p>
* @author 00311332
* @date 2018年5月17日 下午2:43:48
*/
public String uploadFile(HttpServletRequest request){
String path = "";
String uploadPath = request.getSession().getServletContext().getRealPath("/uploadTemplate");
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
SimpleDateFormat simple = new SimpleDateFormat("yyyyMMdd hhmmss");
Iterator<Map.Entry<String, MultipartFile>> it = fileMap.entrySet().iterator();
while(it.hasNext()){
Map.Entry<String, MultipartFile> entry = it.next();
MultipartFile mFile = entry.getValue();
path = uploadPath+"/"+simple.format(new Date())+mFile.getOriginalFilename();
File uploadFile = new File(path);
if(!uploadFile.exists()){
uploadFile.getParentFile().mkdirs();
}
if(mFile.getSize() != 0 && !"".equals(mFile.getName())){
try {
write(mFile.getInputStream(), new FileOutputStream(path));
}
catch (FileNotFoundException e) {
e.printStackTrace();
}
catch (IOException e) {
e.printStackTrace();
}
}
}
return path;
}
/**
* <p>Description: 导出错误日志信息</p>
* <p>Company: 上海旺旺集团有限公司</p>
* @author 00311332
* @date 2018年7月5日 下午2:04:24
*/
private void responseImportSpecialPlanErrorReport(HttpServletResponse response, List<CanalSpecialPlan> list) {
Workbook workbook = null;
try {
workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("new sheet");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue("序号");
cell = row.createCell(1);
cell.setCellValue("分公司代码");
cell = row.createCell(2);
cell.setCellValue("门店编码");
cell = row.createCell(3);
cell.setCellValue("系统编码");
cell = row.createCell(4);
cell.setCellValue("BPM促销号");
cell = row.createCell(5);
cell.setCellValue("特陈年月");
cell = row.createCell(6);
cell.setCellValue("档期开始时间");
cell = row.createCell(7);
cell.setCellValue("档期结束时间");
cell = row.createCell(8);
cell.setCellValue("线别代码");
cell = row.createCell(9);
cell.setCellValue("品项名称(计划)");
cell = row.createCell(10);
cell.setCellValue("特陈形式代码(计划)");
cell = row.createCell(11);
cell.setCellValue("特陈属性(计划)");
cell = row.createCell(12);
cell.setCellValue("错误原因");
for (int index = 0; index < list.size(); index++) {
row = sheet.createRow(index + 1);
CanalSpecialPlan item = list.get(index);
cell = row.createCell(0);
cell.setCellValue(item.getIndex() + 1);
cell = row.createCell(1);
cell.setCellValue(item.getCompanyId());
cell = row.createCell(2);
cell.setCellValue(item.getStoreId());
cell = row.createCell(3);
cell.setCellValue(item.getSysId());
cell = row.createCell(4);
cell.setCellValue(item.getBpmId());
cell = row.createCell(5);
cell.setCellValue(item.getYearMonth());
cell = row.createCell(6);
cell.setCellValue(item.getPromotionDateStart());
cell = row.createCell(7);
cell.setCellValue(item.getPromotionDateEnd());
cell = row.createCell(8);
cell.setCellValue(item.getDwLineStatus());
cell = row.createCell(9);
cell.setCellValue(item.getProdName());
cell = row.createCell(10);
cell.setCellValue(item.getDisplayId() == null ? null : item.getDisplayId().toString());
cell = row.createCell(11);
cell.setCellValue(item.getPropertyValue() == null ? null : item.getPropertyValue().toString());
cell = row.createCell(12);
cell.setCellValue(item.getError());
}
String headString = "attachment; filename=\"" + new String(("Special_Plan_Error_Report.xls").getBytes("UTF-8"), "UTF-8") + "\"";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headString);
workbook.write(response.getOutputStream());
}
catch (Exception e) {
}
finally {
if(workbook != null) {
try {
workbook.close();
}
catch (Exception e) {
}
}
}
}
}
SpecialPlanServiceImpl
package com.want.special.service.imp;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import com.want.special.mapper.CanalSpecialPlanMapper;
import com.want.special.po.CanalSpecialPlan;
import com.want.special.service.ISpecialPlanService;
import com.want.special.util.DateFormat;
import com.want.special.util.WriteExcel;
@Service
public class SpecialPlanServiceImpl implements ISpecialPlanService{
@Autowired
private CanalSpecialPlanMapper caSpecialPlanMapper;
private static final Logger LOGGER = Logger.getLogger(SpecialPlanServiceImpl.class);
@Override
public List<CanalSpecialPlan> createCanalSpecialPlan(MultipartFile file,String company,String account,HttpServletResponse response){
//返回错误列表
List<CanalSpecialPlan> list = new ArrayList<CanalSpecialPlan>();
String importSpecialPlanStatus = "false";
// 根据输入流中的模板创建新的文件
try{
Workbook workbook = null;
try {
workbook = WriteExcel.create(file.getInputStream());
}
catch (Exception e) {
e.printStackTrace();
}
// 得到第一个sheet
Sheet sheet = workbook.getSheetAt(0);
Row row = null;
int rowNum = sheet.getLastRowNum();
// 用于新增特陈计划
List<CanalSpecialPlan> caSpecialPlan = new ArrayList<CanalSpecialPlan>();
String yearMonths = "";
String nowDate = new SimpleDateFormat("yyyyMMdd").format(new Date());
// 内容
for (int i = 1; i <= rowNum; i++) {
String errors = "";
row = sheet.getRow(i);
if(!isRowEmpty(row)){
String companyId = "".equals(getCellString(row.getCell(0))) ? null : getCellString(row.getCell(0)).trim();
String storeId = "".equals(getCellString(row.getCell(2))) ? null : getCellString(row.getCell(2)).trim();
String bpmId = "".equals(getCellString(row.getCell(4))) ? null : getCellString(row.getCell(4)).trim();
String yearMonth = "".equals(getCellString(row.getCell(5))) ? null : StringUtils.removeEnd(getCellString(row.getCell(5)),".0").trim();
String start = "".equals(getCellString(row.getCell(6))) ? null : getCellString(row.getCell(6)).trim();
String end = "".equals(getCellString(row.getCell(7))) ? null : getCellString(row.getCell(7)).trim();
String dwLine = "".equals(getCellString(row.getCell(8))) ? null : StringUtils.removeEnd(getCellString(row.getCell(8)),".0").trim();
String prodName = "".equals(getCellString(row.getCell(10))) ? null : getCellString(row.getCell(10)).trim();
String displayId = "".equals(getCellString(row.getCell(11))) ? null : StringUtils.removeEnd(getCellString(row.getCell(11)),".0").trim();
String propertyId = "".equals(getCellString(row.getCell(13))) ? null : StringUtils.removeEnd(getCellString(row.getCell(13)),".0").trim();
if((!StringUtils.isBlank(yearMonth))
&& (!yearMonths.contains(yearMonth))){
yearMonths += yearMonth + ",";
}
if(StringUtils.isBlank(companyId)
|| StringUtils.isBlank(storeId)
|| StringUtils.isBlank(yearMonth)
|| StringUtils.isBlank(start)
|| StringUtils.isBlank(end)
|| StringUtils.isBlank(dwLine)
|| StringUtils.isBlank(prodName)
|| StringUtils.isBlank(displayId)){
errors += "分公司代码、门店编号、特陈年月、特陈档期开始时间、特陈档期结束时间、干湿别代码、品项名称(计划)和特陈形式代码(计划)不能为空；";
}
if((!StringUtils.isBlank(companyId))
&& (!caSpecialPlanMapper.isExistCompanyId(companyId))){
errors += "分公司代码不存在；";
}
if((!StringUtils.isBlank(companyId))
&& (!company.equals(companyId))){
errors += "选择分公司与导入分公司不一致";
}
if((!StringUtils.isBlank(storeId))
&& (!caSpecialPlanMapper.isExistStoreId(new BigDecimal(storeId).toPlainString()))){
errors += "门店编码不存在；";
}
if((!StringUtils.isBlank(displayId))
&& (!caSpecialPlanMapper.isExistDisplayId(new BigDecimal(displayId)))){
errors += "特陈形式编码不存在；";
}
if(((!StringUtils.isBlank(start))
&& (!StringUtils.isBlank(end)))
&& ((!DateFormat.isDAY(start))
|| (!DateFormat.isDAY(end)))){
errors += "特陈档期开始时间或者特陈档期结束时间格式应为YYYYMMDD；";
}
if((!StringUtils.isBlank(yearMonth))
&& (!DateFormat.isMonth(yearMonth))){
errors += "特陈年月格式应为YYYYMM；";
}
LOGGER.info("start="+start+"\n,end="+end);
if((!StringUtils.isBlank(start))
&& (!StringUtils.isBlank(end))
&& (DateFormat.compare_date(start,end) == 1)){
errors += "特陈档期结束时间需大于特陈档期开始时间；";
}
if((!StringUtils.isBlank(prodName))
&& DateFormat.length(prodName) > 400){
errors += "品项名称(计划)不能超过200个汉字；";
}
if((!StringUtils.isBlank(dwLine))
&& (!"0".equals(dwLine))
&& (!"1".equals(dwLine))){
errors += "干湿线别代码只能为0或1；";
}
if((!StringUtils.isBlank(yearMonth))
&& (!DateFormat.getPreMonth().equals(yearMonth))
&& (!DateFormat.getNowMonth().equals(yearMonth))){
errors += "特陈年月不规范，只能导入当月及下月的数据；";
}
if((!StringUtils.isBlank(start))
&& (!StringUtils.isBlank(yearMonth))
&& DateFormat.getNowMonth().equals(yearMonth)
&& DateFormat.compare_date(nowDate,start) == 1){
errors += "导入当月特陈计划，特陈档期开始时间需大于当前时间；";
}
if((!StringUtils.isBlank(end))
&& (!StringUtils.isBlank(yearMonth))
&& DateFormat.dateCompare(yearMonth,end.replace("-", "").substring(0,6)) == 1){
errors += "特陈档期结束时间需不能早于特陈年月；";
}
if((!StringUtils.isBlank(displayId))
&& (!BigDecimal.ZERO.setScale(1).equals(new BigDecimal(propertyId).remainder(new BigDecimal(0.5))))){
errors += "特陈属性只允许导入0.5以及0.5的倍数";
}
CanalSpecialPlan specialPlan = new CanalSpecialPlan();
specialPlan.setCompanyId(companyId);//分公司
specialPlan.setStoreId(StringUtils.isBlank(storeId) ? null : new BigDecimal(storeId).toPlainString());//门店编号
specialPlan.setSysId(caSpecialPlanMapper.getSysIdByStoreId(StringUtils.isBlank(storeId) ? null : new BigDecimal(storeId).toPlainString()));//系统编码
specialPlan.setBpmId(bpmId);//BPM促销单号
specialPlan.setYearMonth(yearMonth);//特陈年月
specialPlan.setPromotionDateStart(DateFormat.strToDateFormat(start));//档期开始日期
specialPlan.setPromotionDateEnd(DateFormat.strToDateFormat(end));//档期结束日期
specialPlan.setDwLineStatus(dwLine);//干湿线
specialPlan.setProdName(prodName);//品项名称
specialPlan.setDisplayId(StringUtils.isBlank(displayId) ? null : new BigDecimal(displayId));//特陈形式编码
specialPlan.setPropertyValue(StringUtils.isBlank(propertyId) ? null :new BigDecimal(propertyId));//特陈属性编码
if(!"".equals(errors)){
specialPlan.setIndex(i);
specialPlan.setError(errors);
list.add(specialPlan);
}
else{
specialPlan.setCreateUser(account);//创建人员
caSpecialPlan.add(specialPlan);
}
}
}
if(list.size() == 0){
importSpecialPlanStatus = "true";
if(yearMonths.contains(DateFormat.getPreMonth())){
//删除数据库已存在的下个月的数据
Map<String,String> map = new HashMap<>();
map.put("yearMonth", DateFormat.getPreMonth());
map.put("company", company);
caSpecialPlanMapper.delCaSpecialPlanByYearMonth(map);
}
caSpecialPlanMapper.insertCanalSpecialPlan(caSpecialPlan);
}
}catch(Exception e){
}finally{
Cookie status = new Cookie("importSpecialPlanStatus", importSpecialPlanStatus);
status.setPath("/buss-ka-special-web");
response.addCookie(status);
}
return list;
}
//把EXCEL Cell原有数据转换成String类型
private String getCellString(Cell cell) {
if(cell==null) return "";
String cellSring="";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellSring = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
if (DateUtil.isCellDateFormatted(cell)) {
cellSring = new SimpleDateFormat("yyyyMMdd").format(cell.getDateCellValue()); //日期型
}else {
cellSring = new DecimalFormat("0").format(cell.getNumericCellValue()); //数字型
}
//cellSring=String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellSring=String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellSring=String.valueOf(cell.getCellFormula());
break;
case HSSFCell.CELL_TYPE_BLANK: // 空值
cellSring="";
break;
case HSSFCell.CELL_TYPE_ERROR: // 故障
cellSring="";
break;
default:
cellSring="ERROR";
break;
}
return cellSring;
}
/**
* <p>Description: 检验excel是否包含空行</p>
* <p>Company: 上海旺旺集团有限公司</p>
* @author 00311332
* @date 2018年8月30日 下午1:38:22
*/
public boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
return false;
}
return true;
}
@Override
public List<Map<String, Object>> exportCanalSpecialPlan(String yearMonth,String companyId,String companyIds) {
Map<String,String> map = new HashMap<>();
map.put("yearMonth", yearMonth);
map.put("companyId", companyId);
map.put("companyIds", companyIds);
return caSpecialPlanMapper.exportCanalSpecialPlan(map);
}
}
ExportSpecialPlanTools
package com.want.special.util;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExportSpecialPlanTools {
private static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
public static HSSFWorkbook buildExcelDocument(List<Map<String,Object>> list) {
HSSFWorkbook workbook = new HSSFWorkbook();
// 设置页签名称
HSSFSheet sheet = workbook.createSheet("现渠特陈计划");
sheet.setDefaultColumnWidth(15);
sheet.setColumnWidth(8, 35 * 256); //设置列宽
// 顶部表格样式
HSSFCellStyle cellTableStyle = workbook.createCellStyle();
cellTableStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
cellTableStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
cellTableStyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框
cellTableStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
cellTableStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
cellTableStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边边框颜色
cellTableStyle.setBorderTop(CellStyle.BORDER_THIN); // 上边边框
cellTableStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边边框颜色
cellTableStyle.setWrapText(true);// 指定单元格自动换行
// 中间数据部分表格样式set
HSSFCellStyle centerCellTableStyle = workbook.createCellStyle();
centerCellTableStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
centerCellTableStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 底部边框颜色
centerCellTableStyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框
centerCellTableStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边边框颜色
centerCellTableStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
centerCellTableStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边边框颜色
centerCellTableStyle.setBorderTop(CellStyle.BORDER_THIN); // 上边边框
centerCellTableStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边边框颜色
centerCellTableStyle.setWrapText(true);// 指定单元格自动换行
// 标题样式
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
// 10号字体，正文适用
HSSFFont centerFont = workbook.createFont();
centerFont.setFontHeightInPoints((short)10);
centerCellTableStyle.setFont(centerFont);
centerCellTableStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
centerCellTableStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 指定单元格垂直居中对齐
// 表头样式
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short)13);
cellTableStyle.setFont(font);
cellTableStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellTableStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 指定单元格垂直居中对齐
// 15号字体，标题试用
HSSFFont titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short)15);
titleStyle.setFont(titleFont);
// 标题
HSSFRow row = sheet.createRow(0);
row.setHeight((short)500);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(titleStyle);
cell.setCellValue("现渠特陈计划");
CellRangeAddress thirdPartRangeRow10 = new CellRangeAddress((short)0, (short)0, (short)0, (short)10);
sheet.addMergedRegion(thirdPartRangeRow10);
// 设置顶部信息
setHead(sheet, cellTableStyle);
// 设置中部特陈计划与实际报表资料
setCenter(sheet, centerCellTableStyle,list);
return workbook;
}
/**
* 特陈计划与实际报表表头部分.
*
* @param sheet 表格页签
* @param cellTableStyle 单元格样式
* @return [返回类型说明]
* @exception/throws [违例类型] [违例说明]
* @see [类、类#方法、类#成员]
*/
public static void setHead(HSSFSheet sheet, HSSFCellStyle cellTableStyle) {
// 第二行
HSSFRow row1 = sheet.createRow(1);
row1.setHeight((short)650);
HSSFCell row1cell = row1.createCell(0);
row1cell.setCellValue("分公司代码");
row1cell.setCellStyle(cellTableStyle);
HSSFCell row1cell1 = row1.createCell(1);
row1cell1.setCellValue("分公司名称");
row1cell1.setCellStyle(cellTableStyle);
HSSFCell row1cell2 = row1.createCell(2);
row1cell2.setCellValue("门店编号");
row1cell2.setCellStyle(cellTableStyle);
HSSFCell row1cell3 = row1.createCell(3);
row1cell3.setCellValue("门店名称");
row1cell3.setCellStyle(cellTableStyle);
HSSFCell row1cell4 = row1.createCell(4);
row1cell4.setCellValue("门店地址");
row1cell4.setCellStyle(cellTableStyle);
HSSFCell row1cell5 = row1.createCell(5);
row1cell5.setCellValue("系统编码");
row1cell5.setCellStyle(cellTableStyle);
HSSFCell row1cell6 = row1.createCell(6);
row1cell6.setCellValue("系统名称");
row1cell6.setCellStyle(cellTableStyle);
HSSFCell row1cell7 = row1.createCell(7);
row1cell7.setCellValue("BPM促销号");
row1cell7.setCellStyle(cellTableStyle);
HSSFCell row1cell8 = row1.createCell(8);
row1cell8.setCellValue("特陈年月");
row1cell8.setCellStyle(cellTableStyle);
HSSFCell row1cell9 = row1.createCell(9);
row1cell9.setCellValue("档期开始时间");
row1cell9.setCellStyle(cellTableStyle);
HSSFCell row1cell10 = row1.createCell(10);
row1cell10.setCellValue("档期结束时间");
row1cell10.setCellStyle(cellTableStyle);
HSSFCell row1cell11 = row1.createCell(11);
row1cell11.setCellValue("线别代码(计划)");
row1cell11.setCellStyle(cellTableStyle);
HSSFCell row1cell12 = row1.createCell(12);
row1cell12.setCellValue("线别名称(计划)");
row1cell12.setCellStyle(cellTableStyle);
HSSFCell row1cell13 = row1.createCell(13);
row1cell13.setCellValue("品项名称(计划)");
row1cell13.setCellStyle(cellTableStyle);
HSSFCell row1cell14 = row1.createCell(14);
row1cell14.setCellValue("特陈形式代码(计划)");
row1cell14.setCellStyle(cellTableStyle);
HSSFCell row1cell15 = row1.createCell(15);
row1cell15.setCellValue("特陈形式名称(计划)");
row1cell15.setCellStyle(cellTableStyle);
HSSFCell row1cell16 = row1.createCell(16);
row1cell16.setCellValue("特陈属性(计划)");
row1cell16.setCellStyle(cellTableStyle);
HSSFCell row1cell17 = row1.createCell(17);
row1cell17.setCellValue("计划导入时间");
row1cell17.setCellStyle(cellTableStyle);
HSSFCell row1cell18 = row1.createCell(18);
row1cell18.setCellValue("计划导入人员工号");
row1cell18.setCellStyle(cellTableStyle);
HSSFCell row1cell19 = row1.createCell(19);
row1cell19.setCellValue("计划导入人员姓名");
row1cell19.setCellStyle(cellTableStyle);
}
/**
* 特陈计划与实际报表表body部分（特陈计划、业代、主任资料）.
*
* @param sheet execl表格页签
* @param cellTableStyle execl表格单元格样式
* @param helper 特陈计划、业代、主任资料List.
* @return [返回类型说明]
* @exception/throws [违例类型] [违例说明]
* @see [类、类#方法、类#成员]
*/
public static void setCenter(HSSFSheet sheet, HSSFCellStyle cellTableStyle,List<Map<String,Object>> list) {
for (int i = 0; i < list.size(); i++) {
Map<String, Object> map = list.get(i);
HSSFRow row2 = sheet.createRow(i + 2);
row2.setHeight((short)400);
HSSFCell row2cell = row2.createCell(0);
row2cell.setCellValue(map.get("COMPANY_ID") != null ? map.get("COMPANY_ID").toString() : "");
row2cell.setCellStyle(cellTableStyle);
HSSFCell row2cell1 = row2.createCell(1);
row2cell1.setCellValue(map.get("COMPANY_NAME") != null ? map.get("COMPANY_NAME").toString() : "");
row2cell1.setCellStyle(cellTableStyle);
HSSFCell row2cell2 = row2.createCell(2);
row2cell2.setCellValue(map.get("STORE_ID") != null ? map.get("STORE_ID").toString() : "");
row2cell2.setCellStyle(cellTableStyle);
HSSFCell row2cell3 = row2.createCell(3);
row2cell3.setCellValue(map.get("STORE_NAME") != null ? map.get("STORE_NAME").toString() : "");
row2cell3.setCellStyle(cellTableStyle);
HSSFCell row2cell4 = row2.createCell(4);
row2cell4.setCellValue(map.get("ADDRESS") != null ? map.get("ADDRESS").toString() : "");
row2cell4.setCellStyle(cellTableStyle);
HSSFCell row2cell5 = row2.createCell(5);
row2cell5.setCellValue(map.get("SYS_ID") != null ? map.get("SYS_ID").toString() : "");
row2cell5.setCellStyle(cellTableStyle);
HSSFCell row2cell6 = row2.createCell(6);
row2cell6.setCellValue(map.get("SYS_NAME") != null ? map.get("SYS_NAME").toString() : "");
row2cell6.setCellStyle(cellTableStyle);
HSSFCell row2cell7 = row2.createCell(7);
row2cell7.setCellValue(map.get("BPM_ID") != null ? map.get("BPM_ID").toString() : "");
row2cell7.setCellStyle(cellTableStyle);
HSSFCell row2cell8 = row2.createCell(8);
row2cell8.setCellValue(map.get("YEAR_MONTH") != null ? map.get("YEAR_MONTH").toString() : "");
row2cell8.setCellStyle(cellTableStyle);
HSSFCell row2cell9 = row2.createCell(9);
row2cell9.setCellValue(map.get("PROMOTION_DATE_START") != null ? map.get("PROMOTION_DATE_START").toString() : "");
row2cell9.setCellStyle(cellTableStyle);
HSSFCell row2cell10 = row2.createCell(10);
row2cell10.setCellValue(map.get("PROMOTION_DATE_END") != null ? map.get("PROMOTION_DATE_END").toString() : "");
row2cell10.setCellStyle(cellTableStyle);
HSSFCell row2cell11 = row2.createCell(11);
row2cell11.setCellValue(map.get("DW_LINE_STATUS") != null ? map.get("DW_LINE_STATUS").toString() : "");
row2cell11.setCellStyle(cellTableStyle);
HSSFCell row2cell12 = row2.createCell(12);
row2cell12.setCellValue(map.get("DW_LINE_NM") != null ? map.get("DW_LINE_NM").toString() : "");
row2cell12.setCellStyle(cellTableStyle);
HSSFCell row2cell13 = row2.createCell(13);
row2cell13.setCellValue(map.get("PROD_NAME") != null ? map.get("PROD_NAME").toString() : "");
row2cell13.setCellStyle(cellTableStyle);
HSSFCell row2cell14 = row2.createCell(14);
row2cell14.setCellValue(map.get("DISPLAY_ID") != null ? map.get("DISPLAY_ID").toString() : "");
row2cell14.setCellStyle(cellTableStyle);
HSSFCell row2cell15 = row2.createCell(15);
row2cell15.setCellValue(map.get("DISPLAY_NAME") != null ? map.get("DISPLAY_NAME").toString() : "");
row2cell15.setCellStyle(cellTableStyle);
HSSFCell row2cell16 = row2.createCell(16);
row2cell16.setCellValue(map.get("PROPERTY_VALUE") != null ? map.get("PROPERTY_VALUE").toString() : "");
row2cell16.setCellStyle(cellTableStyle);
HSSFCell row2cell17 = row2.createCell(17);
row2cell17.setCellValue(map.get("CREATE_DATE") != null ? format.format(map.get("CREATE_DATE")) : "");
row2cell17.setCellStyle(cellTableStyle);
HSSFCell row2cell18 = row2.createCell(18);
row2cell18.setCellValue(map.get("CREATE_USER") != null ? map.get("CREATE_USER").toString() : "");
row2cell18.setCellStyle(cellTableStyle);
HSSFCell row2cell19 = row2.createCell(19);
row2cell19.setCellValue(map.get("EMP_NAME") != null ? map.get("EMP_NAME").toString() : "");
row2cell19.setCellStyle(cellTableStyle);
}
}
}
180
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
