前言
hello，大家好，俗话说，上传下载是我们作为开发必不可少的业务，这里讲解比较常用的单纯上传下载功能，不涉及到很高级的比如幂等性、鉴权等业务
废话不多说，直接撸代码
一、上传
1. 接口
@RequestMapping(value = "/uploadFile.ajax", method = RequestMethod.POST)
@ResponseBody
public Result uploads(HttpServletRequest request, @RequestParam(required = true) MultipartFile file,
@ApiParam(value = "文件类型") @RequestParam(defaultValue = "1") Integer type) {
try {
if (file == null) {
throw new ServiceException("上传文件不能为空");
}
// 文件名校验
String fileName = file.getOriginalFilename();
if (!fileName.matches(GenConstants.LOWEXCEL) && !fileName.matches(GenConstants.HIGHEXCEL)) {
throw new ServiceException(ResultEnum.FILE_INCORRENT.getMessage());
}
// 文件大小校验
if (!(file.getSize() / CommonConstant.FILE_FORMAT_SIZE / CommonConstant.FILE_FORMAT_SIZE < 10)) {
throw new ServiceException("文件过大，不能超过10MB");
}
commonService.uploadFile(request, file, type);
} catch (Exception e) {
throw new ServiceException("上传失败，文件过大或格式有误");
}
return ResultGenerator.genSuccessResult("上传成功");
}
多个文件的话直接数组接收参数再循环即可
2. 逻辑
public void uploadFile(HttpServletRequest request, MultipartFile file, Integer type) throws Exception {
// 上传目录地址
String uploadDir = request.getSession().getServletContext().getRealPath("/") + "upload/type" + type + "/";
// 如果目录不存在，自动创建文件夹
File dir = new File(uploadDir);
if (!dir.exists()) {
dir.mkdir();
}
// 调用上传方法
HashMap<String, String> uploadInfo = executeUpload(uploadDir, file);
}
public HashMap<String, String> executeUpload(String uploadDir, MultipartFile file) throws Exception {
HashMap<String, String> upload = new HashMap<>();
// 文件后缀名
String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
String name = file.getOriginalFilename().substring(0, file.getOriginalFilename().lastIndexOf("."));
// 上传文件名
String fileName = name + suffix;
// 服务器端保存的文件对象
File serverFile = new File(uploadDir + fileName);
// 将上传的文件写入到服务器端文件内
FileUtils.copyInputStreamToFile(file.getInputStream(), serverFile);
long attachmentSize = file.getSize();
FTPClientUtils ftp = FTPClientUtils.init();
String contextPath = "qmp";
String result = "上传至Ftp完成";
boolean ret = ftp.put(contextPath + "/" + serverFile.getName(), serverFile.getAbsolutePath());
if (ret) {
String url = FTP_URL + "/" + contextPath + "/" + serverFile.getName();
upload.put("fileName", fileName);
upload.put("attachmentPath", url);
upload.put("attachmentSize", String.valueOf(attachmentSize));
} else {
result = ", 上传至Ftp失败";
}
upload.put("result", result);
return upload;
}
FTPClientUtils 工具
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.net.SocketException;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPReply;
import org.apache.log4j.Logger;
/**
* @author Terry FTP客户端
* @creation 2017年8月18日-下午7:25:32
*/
public class FTPClientUtils {
// ---------------------------------------------------------------------
// Instance data
// ---------------------------------------------------------------------
/** logger */
protected final Logger log = Logger.getLogger(getClass());
private ThreadLocal<FTPClient> ftpClientThreadLocal = new ThreadLocal<FTPClient>();
private String host;
private int port;
private String username;
private String password;
private boolean binaryTransfer = true;
private boolean passiveMode = true;
private String encoding = "UTF-8";
private int clientTimeout = 1000 * 30;
private int bufSize = 1024 * 1024 * 10;// 10M缓冲区10M
public String getHost() {
return host;
}
public void setHost(String host) {
this.host = host;
}
public int getPort() {
return port;
}
public void setPort(int port) {
this.port = port;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public boolean isBinaryTransfer() {
return binaryTransfer;
}
public void setBinaryTransfer(boolean binaryTransfer) {
this.binaryTransfer = binaryTransfer;
}
public boolean isPassiveMode() {
return passiveMode;
}
public void setPassiveMode(boolean passiveMode) {
this.passiveMode = passiveMode;
}
public String getEncoding() {
return encoding;
}
public void setEncoding(String encoding) {
this.encoding = encoding;
}
public int getClientTimeout() {
return clientTimeout;
}
public void setClientTimeout(int clientTimeout) {
this.clientTimeout = clientTimeout;
}
// ---------------------------------------------------------------------
// private method
// ---------------------------------------------------------------------
/**
* 返回一个FTPClient实例
*
* @throws Exception
*/
private FTPClient getFTPClient() {
if (ftpClientThreadLocal.get() != null && ftpClientThreadLocal.get().isConnected()) {
return ftpClientThreadLocal.get();
} else {
FTPClient ftpClient = new FTPClient(); // 构造一个FtpClient实例
ftpClient.setControlEncoding(encoding); // 设置字符集
ftpClient.setBufferSize(bufSize);
try {
// 连接到ftp服务器
connect(ftpClient);
} catch (Exception e1) {
e1.printStackTrace();
}
// 设置为passive模式
if (passiveMode) {
ftpClient.enterLocalPassiveMode();
}
try {
// 设置文件传输类型
setFileType(ftpClient);
} catch (Exception e1) {
e1.printStackTrace();
}
try {
ftpClient.setSoTimeout(clientTimeout);
} catch (SocketException e) {
e.printStackTrace();
}
ftpClientThreadLocal.set(ftpClient);
return ftpClient;
}
}
/**
* 设置文件传输类型
*
* @throws Exception
* @throws IOException
*/
private void setFileType(FTPClient ftpClient) throws Exception {
try {
if (binaryTransfer) {
ftpClient.setFileType(FTPClient.BINARY_FILE_TYPE);
} else {
ftpClient.setFileType(FTPClient.ASCII_FILE_TYPE);
}
} catch (IOException e) {
throw new Exception("Could not to set file type.", e);
}
}
/**
* 连接到ftp服务器
*
* @param ftpClient
* @return 连接成功返回true，否则返回false
* @throws Exception
*/
private boolean connect(FTPClient ftpClient) throws Exception {
try {
ftpClient.connect(host, port);
// 连接后检测返回码来校验连接是否成功
int reply = ftpClient.getReplyCode();
if (FTPReply.isPositiveCompletion(reply)) {
// 登陆到ftp服务器
if (ftpClient.login(username, password)) {
setFileType(ftpClient);
return true;
}
} else {
ftpClient.disconnect();
throw new Exception("FTP server refused connection.");
}
} catch (IOException e) {
if (ftpClient.isConnected()) {
try {
ftpClient.disconnect(); // 断开连接
} catch (IOException e1) {
throw new Exception("Could not disconnect from server.", e1);
}
}
throw new Exception("Could not connect to server.", e);
}
return false;
}
// ---------------------------------------------------------------------
// public method
// ---------------------------------------------------------------------
/**
* 断开ftp连接
*
* @throws Exception
*/
public void disconnect() throws Exception {
try {
FTPClient ftpClient = getFTPClient();
ftpClient.logout();
if (ftpClient.isConnected()) {
ftpClient.disconnect();
ftpClient = null;
}
log.info("连接关闭");
} catch (IOException e) {
throw new Exception("Could not disconnect from server.", e);
}
}
public boolean mkdir(String pathname) throws Exception {
return mkdir(pathname, null);
}
/**
* 在ftp服务器端创建目录（不支持一次创建多级目录）
*
* 该方法执行完后将自动关闭当前连接
*
* @param pathname
* @return
* @throws Exception
*/
public boolean mkdir(String pathname, String workingDirectory) throws Exception {
return mkdir(pathname, workingDirectory, true);
}
/**
* 在ftp服务器端创建目录（不支持一次创建多级目录）
*
* @param pathname
* @param autoClose
* 是否自动关闭当前连接
* @return
* @throws Exception
*/
public boolean mkdir(String pathname, String workingDirectory, boolean autoClose) throws Exception {
try {
getFTPClient().changeWorkingDirectory(workingDirectory);
return getFTPClient().makeDirectory(pathname);
} catch (IOException e) {
throw new Exception("Could not mkdir.", e);
} finally {
if (autoClose) {
disconnect(); // 断开连接
}
}
}
/**
* 上传一个本地文件到远程指定文件
*
* @param remoteAbsoluteFile
* 远程文件名(包括完整路径)
* @param localAbsoluteFile
* 本地文件名(包括完整路径)
* @return 成功时，返回true，失败返回false
* @throws Exception
*/
public boolean put(String remoteAbsoluteFile, String localAbsoluteFile) throws Exception {
return put(remoteAbsoluteFile, localAbsoluteFile, true);
}
/**
* 上传一个本地文件到远程指定文件
*
* @param remoteAbsoluteFile
* 远程文件名(包括完整路径)
* @param localAbsoluteFile
* 本地文件名(包括完整路径)
* @param autoClose
* 是否自动关闭当前连接
* @return 成功时，返回true，失败返回false
* @throws Exception
*/
public boolean put(String remoteAbsoluteFile, String localAbsoluteFile, boolean autoClose) throws Exception {
InputStream input = null;
try {
// 处理传输
input = new FileInputStream(localAbsoluteFile);
System.out.println("上传的文件为：" + localAbsoluteFile);
getFTPClient().storeFile(remoteAbsoluteFile, input);
log.debug("put " + localAbsoluteFile);
return true;
} catch (FileNotFoundException e) {
throw new Exception("local file not found.", e);
} catch (IOException e) {
throw new Exception("Could not put file to server.", e);
} finally {
try {
if (input != null) {
input.close();
}
} catch (Exception e) {
throw new Exception("Couldn't close FileInputStream.", e);
}
if (autoClose) {
disconnect(); // 断开连接
}
}
}
/**
* 下载一个远程文件到本地的指定文件
*
* @param remoteAbsoluteFile
* 远程文件名(包括完整路径)
* @param localAbsoluteFile
* 本地文件名(包括完整路径)
* @return 成功时，返回true，失败返回false
* @throws Exception
*/
public boolean get(String remoteAbsoluteFile, String localAbsoluteFile) throws Exception {
return get(remoteAbsoluteFile, localAbsoluteFile, true);
}
/**
* 下载一个远程文件到本地的指定文件
*
* @param remoteAbsoluteFile
* 远程文件名(包括完整路径)
* @param localAbsoluteFile
* 本地文件名(包括完整路径)
* @param autoClose
* 是否自动关闭当前连接
*
* @return 成功时，返回true，失败返回false
* @throws Exception
*/
public boolean get(String remoteAbsoluteFile, String localAbsoluteFile, boolean autoClose) throws Exception {
OutputStream output = null;
try {
output = new FileOutputStream(localAbsoluteFile);
return get(remoteAbsoluteFile, output, autoClose);
} catch (FileNotFoundException e) {
throw new Exception("local file not found.", e);
} finally {
try {
if (output != null) {
output.close();
}
} catch (IOException e) {
throw new Exception("Couldn't close FileOutputStream.", e);
}
}
}
/**
* 下载一个远程文件到指定的流 处理完后记得关闭流
*
* @param remoteAbsoluteFile
* @param output
* @return
* @throws Exception
*/
public boolean get(String remoteAbsoluteFile, OutputStream output) throws Exception {
return get(remoteAbsoluteFile, output, true);
}
/**
* 下载一个远程文件到指定的流 处理完后记得关闭流
*
* @param remoteAbsoluteFile
* @param output
* @param delFile
* @return
* @throws Exception
*/
public boolean get(String remoteAbsoluteFile, OutputStream output, boolean autoClose) throws Exception {
try {
FTPClient ftpClient = getFTPClient();
// 处理传输
return ftpClient.retrieveFile(remoteAbsoluteFile, output);
} catch (IOException e) {
throw new Exception("Couldn't get file from server.", e);
} finally {
if (autoClose) {
disconnect(); // 关闭链接
}
}
}
/**
* 从ftp服务器上删除一个文件 该方法将自动关闭当前连接
*
* @param delFile
* @return
* @throws Exception
*/
public boolean delete(String delFile) throws Exception {
return delete(delFile, true);
}
/**
* 从ftp服务器上删除一个文件
*
* @param delFile
* @param autoClose
* 是否自动关闭当前连接
*
* @return
* @throws Exception
*/
public boolean delete(String delFile, boolean autoClose) throws Exception {
try {
getFTPClient().deleteFile(delFile);
return true;
} catch (IOException e) {
throw new Exception("Couldn't delete file from server.", e);
} finally {
if (autoClose) {
disconnect(); // 关闭链接
}
}
}
/**
* 批量删除 该方法将自动关闭当前连接
*
* @param delFiles
* @return
* @throws Exception
*/
public boolean delete(String[] delFiles) throws Exception {
return delete(delFiles, true);
}
/**
* 批量删除
*
* @param delFiles
* @param autoClose
* 是否自动关闭当前连接
*
* @return
* @throws Exception
*/
public boolean delete(String[] delFiles, boolean autoClose) throws Exception {
try {
FTPClient ftpClient = getFTPClient();
for (String s : delFiles) {
ftpClient.deleteFile(s);
}
return true;
} catch (IOException e) {
throw new Exception("Couldn't delete file from server.", e);
} finally {
if (autoClose) {
disconnect(); // 关闭链接
}
}
}
/**
* 列出远程默认目录下所有的文件
*
* @return 远程默认目录下所有文件名的列表，目录不存在或者目录下没有文件时返回0长度的数组
* @throws Exception
*/
public String[] listNames() throws Exception {
return listNames(null, true);
}
public String[] listNames(boolean autoClose) throws Exception {
return listNames(null, autoClose);
}
/**
* 列出远程目录下所有的文件
*
* @param remotePath
* 远程目录名
* @param autoClose
* 是否自动关闭当前连接
*
* @return 远程目录下所有文件名的列表，目录不存在或者目录下没有文件时返回0长度的数组
* @throws Exception
*/
public String[] listNames(String remotePath, boolean autoClose) throws Exception {
try {
String[] listNames = getFTPClient().listNames(remotePath);
return listNames;
} catch (IOException e) {
throw new Exception("列出远程目录下所有的文件时出现异常", e);
} finally {
if (autoClose) {
disconnect(); // 关闭链接
}
}
}
public static FTPClientUtils init() {
FTPClientUtils ftp = new FTPClientUtils();
ftp.setHost("XXX");
ftp.setPort(2121);
ftp.setUsername("user");
ftp.setPassword("pass");
ftp.setBinaryTransfer(true);
ftp.setPassiveMode(false);
ftp.setEncoding("utf-8");
return ftp;
}
public static void main(String[] args) throws Exception, InterruptedException {
FTPClientUtils ftp = FTPClientUtils.init();
// ftp.mkdir("TBD");
System.out.print("开始上传...");
File file = new File("F:/test/");
for (File file2 : file.listFiles()) {
boolean ret = ftp.put(file2.getName(), file2.getAbsolutePath());
if (ret) {
String result = "上传完成";
System.out.println(file2.getName() + result);
} else {
String result = "上传失败";
System.out.println(file2.getName() + result);
}
}
System.out.println("上传全部完成");
}
}
二、下载
下载一般用在下载模板、下载固定文件，其实有好几种方案，这里一个个说
-
前端使用url下载
使用windows下载 -
前端js下载
这里就不讲这一个了，随便百度找下即可
– 下载静态文件
<el-link type="primary" target="_blank" :href="`${path}static/template/${tempName}`">
点击下载
</el-link>
data() {
return {
path: process.env.BASE_URL
}
},
- 后端下载
@PostMapping("/downTemplate.ajax")
@ApiOperation("下载模板")
public void exportItemCheck(HttpServletResponse response) {
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
try {
String filename = CustomFieldEnum.getByType(type).getName() + "模板.xls";
filename = new String(filename.getBytes("gb2312"), "ISO8859-1");
response.setHeader("Content-disposition", "attachment;filename=" + filename);
// 表头
HashMap<String, String> headers = new LinkedHashMap<>();
getExpostHeader(headers, type, true);
ExcelUtil.exportExcel(headers, new ArrayList<>(), response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
}
protected void getExpostHeader(Map<String, String> headers, Integer type,Boolean flagCreate){
CustomFieldReq customFieldReq = new CustomFieldReq ();
customFieldReq.setUserName(CookieUtils.getCurrentUser());
customFieldReq.setType(type);
List<DTO> dtos = 查询
for (DTO dto:dtos){
headers.put(req.getFieldKey(), fieldName);
}
}
ExcelUtil 工具
package com.qa.test.system.util.excelutil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ComparatorChain;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* The <code>ExcelUtil</code> 与 {@link ExcelCell}搭配使用
*
* @author sargeras.wang
* @version 1.0, Created at 2013年9月14日
*/
public class ExcelUtil {
private static Logger LG = LoggerFactory.getLogger(ExcelUtil.class);
/**
* 用来验证excel与Vo中的类型是否一致 <br>
* Map<栏位类型,只能是哪些Cell类型>
*/
private static Map<Class<?>, CellType[]> validateMap = new HashMap<>();
static {
validateMap.put(String[].class, new CellType[]{CellType.STRING});
validateMap.put(Double[].class, new CellType[]{CellType.NUMERIC});
validateMap.put(String.class, new CellType[]{CellType.STRING});
validateMap.put(Double.class, new CellType[]{CellType.NUMERIC});
validateMap.put(Date.class, new CellType[]{CellType.NUMERIC, CellType.STRING});
validateMap.put(Integer.class, new CellType[]{CellType.NUMERIC});
validateMap.put(Float.class, new CellType[]{CellType.NUMERIC});
validateMap.put(Long.class, new CellType[]{CellType.NUMERIC});
validateMap.put(Boolean.class, new CellType[]{CellType.BOOLEAN});
}
/**
* 获取cell类型的文字描述
*
* @param cellType <pre>
* CellType.BLANK
* CellType.BOOLEAN
* CellType.ERROR
* CellType.FORMULA
* CellType.NUMERIC
* CellType.STRING
* </pre>
* @return
*/
private static String getCellTypeByInt(CellType cellType) {
if(cellType == CellType.BLANK)
return "Null type";
else if(cellType == CellType.BOOLEAN)
return "Boolean type";
else if(cellType == CellType.ERROR)
return "Error type";
else if(cellType == CellType.FORMULA)
return "Formula type";
else if(cellType == CellType.NUMERIC)
return "Numeric type";
else if(cellType == CellType.STRING)
return "String type";
else
return "Unknown type";
}
/**
* 获取单元格值
*
* @param cell
* @return
*/
public static String getCellValue(Cell cell) {
if (cell == null
|| (cell.getCellTypeEnum() == CellType.STRING && StringUtils.isBlank(cell
.getStringCellValue()))) {
return null;
}
CellType cellType = cell.getCellTypeEnum();
if(cellType == CellType.BLANK)
return null;
else if(cellType == CellType.BOOLEAN)
return String.valueOf(cell.getBooleanCellValue());
else if(cellType == CellType.ERROR)
return String.valueOf(cell.getErrorCellValue());
else if(cellType == CellType.FORMULA) {
try {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return String.valueOf(cell.getDateCellValue());
} else {
return String.valueOf(cell.getNumericCellValue());
}
} catch (IllegalStateException e) {
return String.valueOf(cell.getRichStringCellValue());
}
}
else if(cellType == CellType.NUMERIC){
if (DateUtil.isCellDateFormatted(cell)) {
return String.valueOf(cell.getDateCellValue());
} else {
return String.valueOf(cell.getNumericCellValue());
}
}
else if(cellType == CellType.STRING)
return cell.getStringCellValue();
else
return null;
}
/**
* 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
* 用于单个sheet
*
* @param <T>
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
* @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
*/
public static <T> void exportExcel(Map<String,String> headers, Collection<T> dataset, OutputStream out) {
exportExcel(headers, dataset, out, null);
}
/**
* 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
* 用于单个sheet
*
* @param <T>
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
* @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("resource")
public static <T> void exportExcel(Map<String,String> headers, Collection<T> dataset, OutputStream out,
String pattern) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
write2Sheet(sheet, headers, dataset, pattern,workbook);
try {
workbook.write(out);
} catch (IOException e) {
LG.error(e.toString(), e);
}
}
public static List<Map<String,String>> getProjectKeyParamList(Workbook wb ,int colNum) {
Sheet sheet = null;
Row row = null;
String cellData = null;
//用来存放表中数据
List <Map<String,String>> list = new ArrayList<Map<String,String>>();
if(wb != null){
//获取第一个sheet
sheet = wb.getSheetAt(0);
int startRow =0;
if(sheet.getMergedRegion(0) != null){
//表头占得行数
startRow =sheet.getMergedRegion(0).getLastRow();
}
//效验表头模板
CellRangeAddress ca = sheet.getMergedRegion(0);
int heardMaxRow = 0;
if(ca != null){
heardMaxRow = ca.getLastRow();
}
//获取最大行数
int rowNum = sheet.getPhysicalNumberOfRows();
//获取第一行
row = sheet.getRow(0);
//获取最大列数
// int colNum = row.getLastCellNum();
//key是所在的行列，value是单元格的值
Map<String,String> map = null;
for (int i = 0; i < rowNum; i++) {
map = new LinkedHashMap<String,String>();
row = sheet.getRow(i);
if(row != null ){
//从第0列开始
for(int j = 0; j < (colNum+1); j++){
cellData = (String) getCellValue(row.getCell(j));
if(cellData==null || "".equals(cellData)){
continue;
}
map.put(i+"-"+j,cellData);
}
}
list.add(map);
}
}
return list;
}
@SuppressWarnings("resource")
public static void exportExcel(String[][] datalist, OutputStream out) {
try {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
for (int i = 0; i < datalist.length; i++) {
String[] r = datalist[i];
HSSFRow row = sheet.createRow(i);
for (int j = 0; j < r.length; j++) {
HSSFCell cell = row.createCell(j);
//cell max length 32767
if (r[j].length() > 32767) {
r[j] = "--此字段过长(超过32767),已被截断--" + r[j];
r[j] = r[j].substring(0, 32766);
}
cell.setCellValue(r[j]);
}
}
//自动列宽
if (datalist.length > 0) {
int colcount = datalist[0].length;
for (int i = 0; i < colcount; i++) {
sheet.autoSizeColumn(i);
}
}
workbook.write(out);
} catch (IOException e) {
LG.error(e.toString(), e);
}
}
/**
* 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
* 用于多个sheet
*
* @param <T>
* @param sheets {@link ExcelSheet}的集合
* @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
*/
public static <T> void exportExcel(List<ExcelSheet<T>> sheets, OutputStream out) {
exportExcel(sheets, out, null);
}
/**
* 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
* 用于多个sheet
*
* @param <T>
* @param sheets {@link ExcelSheet}的集合
* @param out 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
* @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
*/
@SuppressWarnings("resource")
public static <T> void exportExcel(List<ExcelSheet<T>> sheets, OutputStream out, String pattern) {
if (CollectionUtils.isEmpty(sheets)) {
return;
}
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
for (ExcelSheet<T> sheet : sheets) {
// 生成一个表格
HSSFSheet hssfSheet = workbook.createSheet(sheet.getSheetName());
write2Sheet(hssfSheet, sheet.getHeaders(), sheet.getDataset(), pattern,workbook);
}
try {
workbook.write(out);
} catch (IOException e) {
LG.error(e.toString(), e);
}
}
public static <T> void writeKeyParamTemplate(Map<String,Object> headers, List<HashMap<String,String>> data, OutputStream out) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
LinkedHashSet<String> headerIDList = getHeadData(headers, workbook, sheet, row);
sheet.setDefaultRowHeight((short)300.0);
sheet.setDefaultColumnWidth(20);
try {
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
LG.error(e.toString(), e);
}
}
public static <T> void writeKeyParamExcel(Map<String,Object> headers, List<HashMap<String,String>> data, OutputStream out) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
LinkedHashSet<String> headerIDList = getHeadData(headers, workbook, sheet, row);
int c;
HSSFRichTextString text;
HSSFCell cell;
c = 1;
//打印表格中的数据
if(headerIDList != null && !headerIDList.isEmpty()){
int rowNum = sheet.getLastRowNum();
for(HashMap<String,String> keyValue : data){
rowNum++;
row = sheet.createRow(rowNum);
c = 0;
//打印机型
if(keyValue.get("projectModel")!=null){
text = new HSSFRichTextString(keyValue.get("projectModel"));
cell = row.createCell(c);
cell.setCellValue(text);
c++;
}
if(keyValue.get("keyParam")==null){
continue;
}
//打印中间动态的数据
String[] rowObject = keyValue.get("keyParam").split(",");
for (String id :headerIDList) {
int flag =0 ;
for(String cellObject : rowObject){
String[] cellValue = cellObject.split(";");
if(cellValue[0].equals(id)){
flag =1;
text = new HSSFRichTextString(cellValue[1]);
cell = row.createCell(c);
cell.setCellValue(text);
c++;
break;
}
}
if(flag ==0){
cell = row.createCell(c);
cell.setCellValue("");
c++;
}
}
//打印后两列
if(keyValue.get("creator")!=null){
text = new HSSFRichTextString(keyValue.get("creator"));
cell = row.createCell(c);
cell.setCellValue(text);
c++;
}
if(keyValue.get("gmtCreate")!=null){
text = new HSSFRichTextString(keyValue.get("gmtCreate"));
cell = row.createCell(c);
cell.setCellValue(text);
c++;
}
}
}
sheet.setDefaultRowHeight((short)300.0);
sheet.setDefaultColumnWidth(20);
try {
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
LG.error(e.toString(), e);
}
}
private static LinkedHashSet<String> getHeadData(Map<String, Object> headers, HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row) {
HSSFFont titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 14);
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
// 标题行转中文
Set<String> keys = headers.keySet();
Iterator<String> it1 = keys.iterator();
String key = ""; //存放临时键变量
int c = 0; //标题列数
LinkedHashSet<String> headerIDList = new LinkedHashSet<>();
HSSFCell cell = null;
HSSFRichTextString text = null;
while (it1.hasNext()) {
key = it1.next();
//打印表头
if("array".equals(key)){
JSONArray array = (JSONArray) headers.get(key);
c = setKeyParamCellValue(0,c,row, cell, array,sheet,titleStyle,headerIDList);
}else {
text = new HSSFRichTextString(headers.get(key).toString());
cell = row.createCell(c);
cell.setCellValue(text);
cell.setCellStyle(titleStyle);
c++;
}
}
int startCol = 1;
int overCol = 1;
//合并表头单元格
JSONArray array = (JSONArray) headers.get("array");
mergedKeyParamCellValue(startCol,overCol,array,sheet);
return headerIDList;
}
private static void mergedKeyParamCellValue( int startCol,int overCol, JSONArray array,HSSFSheet sheet ) {
int maxColumn = 0;
for(int i=0;i<=sheet.getLastRowNum();i++){
int size = sheet.getRow(i).getLastCellNum();
if(size>maxColumn){
maxColumn=size;
}
}
//合并列
HashMap<Integer,Integer> map = new HashMap<>();
//合并行
for(int i = 0; i< maxColumn; i++) {
int startRow=0;
for(int j = 1; j< sheet.getLastRowNum()+1; j++){
Object cellValue = sheet.getRow(j).getCell(i);
if(cellValue != null && !"".equals(cellValue) || j==(sheet.getLastRowNum())){
if(cellValue == null){
if((j) > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, j, i,i));
map.put(i,j);
}
}
startRow = j;
}
}
}
for(int i = 0; i<= sheet.getLastRowNum(); i++) {
for(int j = 1; j< maxColumn-1; j++){
Object cellValue = sheet.getRow(i).getCell(j);
if(cellValue != null && !"".equals(cellValue) || j==(maxColumn)){
if((j-1) > startCol) {
if(map.containsKey(j-1)){
int val = map.get(j-1);
if(i==val){
startCol = j;
}else{
if((j-1) > startCol && cellValue == null) {
sheet.addMergedRegion(new CellRangeAddress(i, i, startCol, j -1));
}
else{
startCol = j;
}
}
}else{
if((j-1) > startCol && cellValue == null) {
sheet.addMergedRegion(new CellRangeAddress(i, i, startCol, j -1));
} else{
startCol = j;
}
}
}
startCol = j;
}
}
}
}
private static int setKeyParamCellValue(int rowNum,int c,HSSFRow row, HSSFCell cell, JSONArray array,HSSFSheet sheet,CellStyle titleStyle,LinkedHashSet<String> headerIDList ) {
HSSFRow rowTmp = sheet.getRow(rowNum);
if(rowTmp != null){
row = rowTmp;
}
else{
row = sheet.createRow(rowNum);
}
rowNum ++ ;
HSSFRichTextString text;
for(int i = 0; i< array.size(); i++){
JSONObject json = (JSONObject)array.get(i);
String fieldName = json.get("fieldName").toString();
text = new HSSFRichTextString(fieldName);
cell = row.createCell(c);
cell.setCellValue(text);
cell.setCellStyle(titleStyle);
JSONArray sendArray = (JSONArray)json.get("children");
if(sendArray == null){
headerIDList.add(json.get("id").toString());
c ++;
continue;
}
c = setKeyParamCellValue( rowNum,c,row,cell,sendArray,sheet,titleStyle,headerIDList);
}
return c;
}
public static <T> void writeProjectItemExcel(String projectModel,Map<String,String> headers, List<LinkedHashSet<JSONObject>> itemTotalList,
OutputStream out,Map<String,String> allResultHeaders,JSONArray allResultArray,String categoryID) throws FileNotFoundException {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(projectModel+"总检点结果");
write2Sheet(sheet, allResultHeaders, allResultArray, null,workbook);
String label = "";
for (LinkedHashSet<JSONObject> results : itemTotalList) {
for(JSONObject result : results){
label = String.valueOf(result.get("label"));
}
sheet = workbook.createSheet(label +"阶段检点结果");
//生成sheet内容
if(Objects.equals(CategoryEnum.Europe.getType(),Integer.valueOf(categoryID))){
getSheet(headers, results, 1, workbook, sheet);
}else{
getSheetCheckResult(headers, results, 1, workbook, sheet);
}
}
sheet.setDefaultRowHeight((short)300.0);
sheet.setDefaultColumnWidth(30);
try {
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
LG.error(e.toString(), e);
}
}
public static <T> void writeProjectItemCheckResultExcel(Map<String,String> headers, LinkedHashSet<JSONObject> results, OutputStream out) throws Exception {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("检点结果");
getSheetCheckResult(headers, results, 1, workbook, sheet);
sheet.setDefaultRowHeight((short)300.0);
sheet.setDefaultColumnWidth(30);
try {
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
LG.error(e.toString(), e);
}
}
public static <T> void getExportNetQualityTargetDetail(Map<String, String> headers,
List<NetQualityTargetDetailReq> templateReqList, OutputStream out) {
try {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet("质量目标");
HSSFFont titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short)14);
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
// 标题行转中文
Set<String> keys = headers.keySet();
Iterator<String> it1 = keys.iterator();
String key = ""; // 存放临时键变量
int c = 0; // 标题列数
while (it1.hasNext()) {
key = it1.next();
if (headers.containsKey(key)) {
HSSFCell cell = row.createCell(c);
cell.setCellStyle(titleStyle);
HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
cell.setCellValue(text);
c++;
}
}
HSSFCellStyle linkStyle = workbook.createCellStyle();
HSSFFont linkFont = workbook.createFont();
linkFont.setUnderline(HSSFFont.U_SINGLE);
linkFont.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
linkStyle.setFont(linkFont);
titleFont.setFontHeightInPoints((short)10);
titleStyle = workbook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
HSSFCellStyle rowStyle = workbook.createCellStyle();
// 遍历集合数据，产生数据行
Iterator<NetQualityTargetDetailReq> it = templateReqList.iterator();
HSSFCell cell = null;
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
NetQualityTargetDetailReq t = it.next();
try {
int cellNum = 0;
// 遍历列名
Iterator<String> it2 = keys.iterator();
while (it2.hasNext()) {
key = it2.next();
if (!headers.containsKey(key)) {
continue;
}
Object value = null;
if (QualityTemplateEnum.YEAR.getName().equals(key)) {
value = t.getYear().getLevelValue();
} else if (QualityTemplateEnum.FIRST.getName().equals(key)) {
value = t.getFirstLevel().getLevelValue();
if(StringUtils.isNotEmpty(String.valueOf(value))){
value = String.valueOf(value).split("_")[0];
}
} else if (QualityTemplateEnum.SECOND.getName().equals(key)) {
value = t.getSecondLevel().getLevelValue();
if(StringUtils.isNotEmpty(String.valueOf(value))){
value = String.valueOf(value).split("_")[0];
}
} else if (QualityTemplateEnum.THIRD.getName().equals(key)) {
value = t.getThirdLevel().getLevelValue();
if(StringUtils.isNotEmpty(String.valueOf(value))){
value = String.valueOf(value).split("_")[0];
}
} else if (QualityTemplateEnum.FOURTH.getName().equals(key)) {
value = t.getFourthLevel().getLevelValue();
if(StringUtils.isNotEmpty(String.valueOf(value))){
value = String.valueOf(value).split("_")[0];
}
} else if (QualityTemplateEnum.FIFTH.getName().equals(key)) {
value = t.getFifthLevel().getLevelValue();
if(StringUtils.isNotEmpty(String.valueOf(value))){
value = String.valueOf(value).split("_")[0];
}
} else if (QualityTemplateEnum.SIXTH.getName().equals(key)) {
value = t.getSixthLevel().getLevelValue();
if(StringUtils.isNotEmpty(String.valueOf(value))){
value = String.valueOf(value).split("_")[0];
}
} else if (QualityTemplateEnum.SEVENTH.getName().equals(key)) {
value = t.getSeventhLevel().getLevelValue();
if(StringUtils.isNotEmpty(String.valueOf(value))){
value = String.valueOf(value).split("_")[0];
}
} else if (QualityTemplateEnum.EIGHTH.getName().equals(key)) {
value = t.getEighthLevel().getLevelValue();
if(StringUtils.isNotEmpty(String.valueOf(value))){
value = String.valueOf(value).split("_")[0];
}
} else if (QualityTemplateEnum.PROJECT_NAME.getName().equals(key)) {
value = t.getProject().get("projectName");
} else if (QualityTemplateEnum.YEAR_TARGET_VALUE.getName().equals(key)) {
value = t.getYearTargetValue();
} else if (QualityTemplateEnum.Q1_TARGET_VALUE.getName().equals(key)) {
value = t.getQ1TargetValue();
} else if (QualityTemplateEnum.Q2_TARGET_VALUE.getName().equals(key)) {
value = t.getQ2TargetValue();
} else if (QualityTemplateEnum.Q3_TARGET_VALUE.getName().equals(key)) {
value = t.getQ3TargetValue();
} else if (QualityTemplateEnum.Q4_TARGET_VALUE.getName().equals(key)) {
value = t.getQ4TargetValue();
}
cell = row.createCell(cellNum);
cell.setCellStyle(titleStyle);
cellNum = setCellValue(cell, value, null, cellNum, null, row);
cellNum++;
}
} catch (Exception e) {
LG.error(e.toString(), e);
}
}
Iterator<String> it2 = keys.iterator();
index = 0;
while (it2.hasNext()) {
key = it2.next();
if (!headers.containsKey(key)) {
continue;
}
if (QualityTemplateEnum.YEAR.getName().equals(key)
||QualityTemplateEnum.PROJECT_NAME.getName().equals(key)
||QualityTemplateEnum.FIRST.getName().equals(key)
||QualityTemplateEnum.SECOND.getName().equals(key)
||QualityTemplateEnum.THIRD.getName().equals(key)
||QualityTemplateEnum.FOURTH.getName().equals(key)
||QualityTemplateEnum.FIFTH.getName().equals(key)
||QualityTemplateEnum.SIXTH.getName().equals(key)
||QualityTemplateEnum.SEVENTH.getName().equals(key)
) {
mergeSpecifiedColumn(sheet,index,rowStyle);
}
index++;
}
sheet.setDefaultColumnWidth(30);
try {
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
LG.error(e.toString(), e);
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void mergeSpecifiedColumn(HSSFSheet sheet, int column,HSSFCellStyle titleStyle) {
int totalRows = sheet.getLastRowNum(), firstRow = 0, lastRow = 0;
boolean isLastCompareSame = false;//上一次比较是否相同
//这里第一行是表头，从第三行开始判断是否相同
if (totalRows >= 2) {
for (int i = 2; i <= totalRows; i++) {
String lastRowCellContent = sheet.getRow(i - 1).getCell(column).getStringCellValue();
String curRowCellContent = sheet.getRow(i).getCell(column).getStringCellValue();
if(StringUtils.isEmpty(lastRowCellContent)&& StringUtils.isEmpty(curRowCellContent)){
continue;
}
if (curRowCellContent.equals(lastRowCellContent)) {
if (!isLastCompareSame) {
firstRow = i - 1;
}
lastRow = i;
isLastCompareSame = true;
} else {
isLastCompareSame = false;
if (lastRow > firstRow) {
// sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, column, column));
}
}
//最后一行时判断是否有需要合并的行
if ((i == totalRows) && (lastRow > firstRow)) {
sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, column, column));
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
sheet.getRow(firstRow).getCell(column).setCellStyle(titleStyle);
}
}
}
}
private static void getSheetCheckResult(Map<String, String> headers, LinkedHashSet<JSONObject> array,
int flag, HSSFWorkbook workbook, HSSFSheet sheet){
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
// 标题行转中文
Set<String> keys = headers.keySet();
Iterator<String> it1 = keys.iterator();
String key = ""; //存放临时键变量
int c= 0; //标题列数
while (it1.hasNext()){
key = it1.next();
if (headers.containsKey(key)) {
HSSFCell cell = row.createCell(c);
HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
cell.setCellValue(text);
c++;
}
}
// 遍历集合数据，产生数据行
int index = 0;
for(JSONObject jsonObject :array){
String label = String.valueOf(jsonObject.get("label"));
index++;
row = sheet.createRow(index);
int cellNum = 0;
//阶段
HSSFCell cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//获取阶段下的部门列表
LinkedHashSet<JSONObject> it2 =(LinkedHashSet<JSONObject>)jsonObject.get("children");
if(it2 == null || it2.isEmpty()){
return ;
}
int departmentIndex =0;
//往excel里写值
for (JSONObject department : it2){
//获取部门
label = String.valueOf(department.get("label"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
LinkedHashSet<JSONObject> centerList = (LinkedHashSet<JSONObject>)department.get("children");
if(centerList==null || centerList.isEmpty()){
index++;
cellNum = cellNum -1;
//row = sheet.createRow(index);
continue;
}
int centerIndex =0;
for (JSONObject center : centerList){
//获取中心
label = String.valueOf(center.get("label"));
cell = row.createCell(cellNum);
LinkedHashSet<JSONObject> groupList = (LinkedHashSet<JSONObject>)center.get("children");
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
if(groupList==null || groupList.isEmpty()){
index++;
cellNum = cellNum -1;
row = sheet.createRow(index);
continue;
}
int groupIndex =0;
for (JSONObject group : groupList){
//获取小组
label = String.valueOf(group.get("label"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
LinkedHashSet<JSONObject> itemList = (LinkedHashSet<JSONObject>)group.get("children");
if(itemList==null || itemList.isEmpty()){
index++;
cellNum = cellNum -1;
row = sheet.createRow(index);
continue;
}
int itemIndex =0;
for (JSONObject item : itemList){
//点检项次
label = String.valueOf(item.get("label"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//点检结果
label = String.valueOf(item.get("result"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//点检人
label = String.valueOf(item.get("responMan"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//附件
label = String.valueOf(item.get("attachmentName"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//备注
label = String.valueOf(item.get("resultComment"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
LinkedHashSet<JSONObject> midList = (LinkedHashSet<JSONObject>)item.get("children");
if(midList == null || midList.isEmpty()){
index++;
cellNum = cellNum -1;
row = sheet.createRow(index);
continue;
}
int midIndex = 0;
for(JSONObject mid : midList){
//不合格问题及跟进
label = String.valueOf(mid.get("problemFollow"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
LinkedHashSet<JSONObject> detailList = (LinkedHashSet<JSONObject>)mid.get("children");
if(detailList == null || detailList.isEmpty()){
index++;
cellNum = cellNum -1;
row = sheet.createRow(index);
continue;
}
int detailIndex = 0;
for(JSONObject detail : detailList){
//对应BUG/问题/风险链接
label = String.valueOf(detail.get("originProblemDesc"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//处理人
label = String.valueOf(detail.get("handlerName"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//测试工程师
label = String.valueOf(detail.get("engineerName"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//状态
label = String.valueOf(detail.get("status"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//优先级
label = String.valueOf(detail.get("priority"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//BPM文档链接
label = String.valueOf(detail.get("documentUrl"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum = cellNum -1;
cellNum = cellNum -1;
cellNum = cellNum -1;
cellNum = cellNum -1;
cellNum = cellNum -1;
if((detailList.size()-1)==detailIndex
&&(midList.size()-1)==midIndex
&&(itemList.size()-1)==itemIndex
&&(groupList.size()-1)==groupIndex
&&(centerList.size()-1)==centerIndex
&&(it2.size() -1)==departmentIndex){
continue;
}
detailIndex++;
index++;
row = sheet.createRow(index);
}
midIndex++;
cellNum = cellNum -1;
}
itemIndex++;
cellNum = cellNum -1;
cellNum = cellNum -1;
cellNum = cellNum -1;
cellNum = cellNum -1;
cellNum = cellNum -1;
}
groupIndex++;
cellNum = cellNum -1;
}
centerIndex++;
cellNum = cellNum -1;
}
departmentIndex++;
cellNum = cellNum -1;
}
}
Integer startRow = 1;
Integer overRow = 0;
int preRow = 1;
for(JSONObject jsonObject :array) {
int departmentSize = 0;
int centerSize = 0;
int groupSize = 0;
int itemSize = 0;
int midSize = 0;
int detailSize = 0;
Integer startCol = 0;
Integer overCol = 0;
//获取阶段下的部门列表
LinkedHashSet<JSONObject> it2 =(LinkedHashSet<JSONObject>)jsonObject.get("children");
if(it2 == null){
return ;
}
//合并单元格
//往excel里写值
for (JSONObject department : it2) {
LinkedHashSet<JSONObject> centerList = (LinkedHashSet<JSONObject>) department.get("children");
if (centerList == null || centerList.isEmpty()) {
detailSize++;
midSize++;
groupSize++;
itemSize++;
centerSize++;
departmentSize++;
continue;
}
for (JSONObject center : centerList) {
//获取小组
LinkedHashSet<JSONObject> groupList = (LinkedHashSet<JSONObject>) center.get("children");
if (groupList == null || groupList.isEmpty()) {
detailSize++;
midSize++;
groupSize++;
itemSize++;
continue;
}
for (JSONObject group : groupList) {
LinkedHashSet<JSONObject> itemList = (LinkedHashSet<JSONObject>) group.get("children");
if(itemList==null || itemList.isEmpty()){
detailSize++;
midSize++;
itemSize++;
continue;
}
for(JSONObject item : itemList){
LinkedHashSet<JSONObject> midList = (LinkedHashSet<JSONObject>) item.get("children");
if(midList == null || midList.isEmpty()){
detailSize++;
midSize++;
continue;
}
for(JSONObject mid : midList){
LinkedHashSet<JSONObject> detailList = (LinkedHashSet<JSONObject>) mid.get("children");
if(detailList == null || detailList.isEmpty()){
continue;
}
//对应BUG/问题/风险链接
if (detailList != null && !detailList.isEmpty()) {
for (JSONObject detail : detailList) {
detailSize++;
}
} else {
detailSize++;
}
//不合格问题及跟进
startRow = preRow + midSize;
overRow = preRow + detailSize-1;
startCol = 9;
overCol = 9;
if (overRow > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
midSize = detailSize;
}
startRow = preRow + itemSize;
overRow = preRow +detailSize-1;
if (overRow > startRow) {
//点检项次
startCol = 4;
overCol = 4;
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
//点检结果
startCol = 5;
overCol = 5;
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
//点检人
startCol = 6;
overCol = 6;
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
//附件
startCol = 7;
overCol = 7;
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
//备注
startCol = 8;
overCol = 8;
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
itemSize = detailSize;
}
//小组
startRow = preRow + groupSize;
overRow = preRow + detailSize-1;
startCol = 3;
overCol = 3;
if (overRow > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
groupSize = detailSize;
}
//中心
startRow = preRow + centerSize;
overRow = preRow +detailSize-1;
startCol = 2;
overCol = 2;
if (overRow > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
centerSize = detailSize;
}
//部门
startRow = preRow + departmentSize;
overRow = preRow + detailSize-1;
startCol = 1;
overCol = 1;
if (overRow > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
departmentSize = detailSize;
}
//阶段
startRow = preRow ;
overRow = preRow + detailSize-1;
startCol = 0;
overCol = 0;
if (overRow > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
preRow = preRow + detailSize;
startRow = preRow + detailSize-1;
}
}
public static <T> void writeItemExcel(Map<String,String> headers, LinkedHashSet<JSONObject> array, OutputStream out,int flag) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
//生成sheet内容
getSheet(headers, array, flag, workbook, sheet);
sheet.setDefaultRowHeight((short)300.0);
sheet.setDefaultColumnWidth(30);
try {
workbook.write(out);
out.flush();
out.close();
} catch (IOException e) {
LG.error(e.toString(), e);
}
}
private static void getSheet(Map<String, String> headers, LinkedHashSet<JSONObject> array, int flag, HSSFWorkbook workbook, HSSFSheet sheet) {
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
// 标题行转中文
Set<String> keys = headers.keySet();
Iterator<String> it1 = keys.iterator();
String key = ""; //存放临时键变量
int c= 0; //标题列数
while (it1.hasNext()){
key = it1.next();
if (headers.containsKey(key)) {
HSSFCell cell = row.createCell(c);
HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
cell.setCellValue(text);
c++;
}
}
// 遍历集合数据，产生数据行
int index = 0;
for(JSONObject jsonObject :array){
String label = String.valueOf(jsonObject.get("label"));
index++;
row = sheet.createRow(index);
int cellNum = 0;
//阶段
HSSFCell cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
//获取阶段下的部门列表
LinkedHashSet<JSONObject> it2 =(LinkedHashSet<JSONObject>)jsonObject.get("children");
if(it2 == null || it2.isEmpty()){
return ;
}
int departmentIndex =0;
//往excel里写值
for (JSONObject department : it2){
//获取部门
label = String.valueOf(department.get("label"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
LinkedHashSet<JSONObject> centerList = (LinkedHashSet<JSONObject>)department.get("children");
if(centerList==null || centerList.isEmpty()){
index++;
cellNum = cellNum -1;
//row = sheet.createRow(index);
continue;
}
int centerIndex =0;
for (JSONObject center : centerList){
//获取中心
label = String.valueOf(center.get("label"));
cell = row.createCell(cellNum);
LinkedHashSet<JSONObject> groupList = (LinkedHashSet<JSONObject>)center.get("children");
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
if(groupList==null || groupList.isEmpty()){
index++;
cellNum = cellNum -1;
row = sheet.createRow(index);
continue;
}
int groupIndex =0;
for (JSONObject group : groupList){
//获取小组
label = String.valueOf(group.get("label"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
LinkedHashSet<JSONObject> itemList = (LinkedHashSet<JSONObject>)group.get("children");
if(itemList==null || itemList.isEmpty()){
index++;
cellNum = cellNum -1;
row = sheet.createRow(index);
continue;
}
int itemIndex =0;
for (JSONObject item : itemList){
//获取项次
label = String.valueOf(item.get("label"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
if(flag == 0){
label = String.valueOf(item.get("comment"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
}
else if(flag == 1){
label = String.valueOf(item.get("result"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
label = String.valueOf(item.get("responMan"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
label = String.valueOf(item.get("attachmentName"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum++;
label = String.valueOf(item.get("resultComment"));
cell = row.createCell(cellNum);
cellNum = setCellValue(cell,label,null,cellNum,null,row);
cellNum = cellNum-1;
cellNum = cellNum-1;
cellNum = cellNum-1;
}
cellNum = cellNum-1;
if(itemIndex == (itemList.size()-1) && (groupList.size()-1) ==groupIndex
&& centerIndex== (centerList.size()-1)
&& departmentIndex == (it2.size() -1)){
continue;
}
itemIndex++;
index++;
row = sheet.createRow(index);
}
groupIndex++;
cellNum = cellNum -1;
}
centerIndex++;
cellNum = cellNum -1;
}
departmentIndex++;
cellNum = cellNum -1;
}
}
Integer startRow = 1;
Integer overRow = 0;
int preRow = 1;
for(JSONObject jsonObject :array) {
int departmentSize = 0;
int centerSize = 0;
int groupSize = 0;
//获取阶段下的部门列表
int itemSize = 0;
Integer startCol = 0;
Integer overCol = 0;
int emptyIndex = 0;
//获取阶段下的部门列表
LinkedHashSet<JSONObject> it2 =(LinkedHashSet<JSONObject>)jsonObject.get("children");
if(it2 == null){
return ;
}
//合并单元格
//往excel里写值
for (JSONObject department : it2) {
LinkedHashSet<JSONObject> centerList = (LinkedHashSet<JSONObject>) department.get("children");
if (centerList == null || centerList.isEmpty()) {
//emptyIndex ++;
groupSize++;
itemSize++;
centerSize++;
departmentSize++;
continue;
}
for (JSONObject center : centerList) {
//获取小组
LinkedHashSet<JSONObject> groupList = (LinkedHashSet<JSONObject>) center.get("children");
if (groupList == null || groupList.isEmpty()) {
//emptyIndex ++;
groupSize++;
itemSize++;
continue;
}
for (JSONObject group : groupList) {
LinkedHashSet<JSONObject> itemList = (LinkedHashSet<JSONObject>) group.get("children");
if(itemList==null || itemList.isEmpty()){
continue;
}
if (itemList != null && !itemList.isEmpty()) {
for (JSONObject item : itemList) {
itemSize++;
}
} else {
itemSize++;
}
startRow = preRow + groupSize;
overRow = preRow + itemSize-1;
startCol = 3;
overCol = 3;
if (overRow > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
groupSize = itemSize;
}
startRow = preRow + centerSize;
overRow = preRow +itemSize-1;
startCol = 2;
overCol = 2;
if (overRow > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
centerSize = itemSize;
}
startRow = preRow + departmentSize;
overRow = preRow + itemSize-1;
startCol = 1;
overCol = 1;
if (overRow > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
departmentSize = itemSize;
}
startRow = preRow ;
overRow = preRow + itemSize-1;
startCol = 0;
overCol = 0;
if (overRow > startRow) {
sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
}
preRow = preRow + itemSize;
startRow = preRow + itemSize-1;
}
}
/**
* 每个sheet的写入
*
* @param sheet 页签
* @param headers 表头
* @param dataset 数据集合
* @param pattern 日期格式
*/
private static <T> void write2Sheet(HSSFSheet sheet, Map<String,String> headers, Collection<T> dataset,
String pattern,HSSFWorkbook workbook) {
//时间格式默认"yyyy-MM-dd"
if (StringUtils.isEmpty(pattern)){
pattern = "yyyy-MM-dd";
}
HSSFFont titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 14);
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
// 标题行转中文
Set<String> keys = headers.keySet();
Iterator<String> it1 = keys.iterator();
String key = ""; //存放临时键变量
int c= 0; //标题列数
while (it1.hasNext()){
key = it1.next();
if (headers.containsKey(key)) {
HSSFCell cell = row.createCell(c);
cell.setCellStyle(titleStyle);
HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
if("问题来源".equals(headers.get(key))|| "专业组".equals(headers.get(key)) || "严重等级".equals(headers.get(key))
|| "问题状态".equals(headers.get(key))|| "当前处理人".equals(headers.get(key))|| "文档编号".equals(headers.get(key))
|| "问题重要度".equals(headers.get(key))
|| "文档状态".equals(headers.get(key)) || "项目编号".equals(headers.get(key))|| "故障类别".equals(headers.get(key))
|| "功能模块".equals(headers.get(key))){
sheet.setColumnWidth(c, (int)((50 + 0.72) * 60));
}
cell.setCellValue(text);
c++;
}
}
HSSFCellStyle hlink_style = workbook.createCellStyle();
HSSFFont hlink_font = workbook.createFont();
hlink_font.setUnderline(HSSFFont.U_SINGLE);
hlink_font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
hlink_style.setFont(hlink_font);
titleFont.setFontHeightInPoints((short) 10);
titleStyle = workbook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
CreationHelper createHelper = workbook.getCreationHelper();
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
HSSFCell cell = null;
Field field = null;
int index = 0;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = it.next();
try {
if (t instanceof Map) {
Map<String, Object> map = (Map<String, Object>) t;
int cellNum = 0;
//遍历列名
Iterator<String> it2 = keys.iterator();
while (it2.hasNext()){
key = it2.next();
if (!headers.containsKey(key)) {
LG.error("Map 中 不存在 key [" + key + "]");
continue;
}
Object value = map.get(key);
cell = row.createCell(cellNum);
cell.setCellStyle(titleStyle);
//文档编号加上链接
if("documentNum".equals(key)){
String[] strArray = String.valueOf(value).split("\\+++");
if(strArray.length>1){
org.apache.poi.hssf.usermodel.HSSFHyperlink link = (org.apache.poi.hssf.usermodel.HSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);
link.setAddress(strArray[1]);
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
}
if(strArray!=null && strArray.length>=1){
cell.setCellValue(strArray[0]);
}
else{
cell.setCellValue("");
}
}else{
cellNum = setCellValue(cell,value,pattern,cellNum,null,row);
}
cellNum++;
}
} else {
List<FieldForSortting> fields = sortFieldByAnno(t.getClass());
int cellNum = 0;
for (int i = 0; i < fields.size(); i++) {
cell = row.createCell(cellNum);
field = fields.get(i).getField();
field.setAccessible(true);
Object value = field.get(t);
cellNum = setCellValue(cell,value,pattern,cellNum,field,row);
cellNum++;
}
}
} catch (Exception e) {
LG.error(e.toString(), e);
}
}
sheet.setDefaultColumnWidth(30);
}
/**
* 每个sheet的写入
*
* @param sheet 页签
* @param headers 表头
* @param dataset 数据集合
* @param pattern 日期格式
*/
private static <T> void write4Sheet(HSSFSheet sheet, Map<String,String> headers, List<Map<String, Object>> dataset,
String pattern,HSSFWorkbook workbook,List<ProjectTypeStageAllOutVO> list,ProjectTypeStageInVO vo) throws ParseException {
//时间格式默认"yyyy-MM-dd"
if (StringUtils.isEmpty(pattern)){
pattern = "yyyy-MM-dd";
}
HSSFFont titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 14);
CellStyle titleStyle = workbook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
// 标题行转中文
Set<String> keys = headers.keySet();
Iterator<String> it1 = keys.iterator();
String key = ""; //存放临时键变量
int c= 0; //标题列数
while (it1.hasNext()){
key = it1.next();
if (headers.containsKey(key)) {
HSSFCell cell = row.createCell(c);
cell.setCellStyle(titleStyle);
HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
cell.setCellValue(text);
c++;
}
}
titleFont.setFontHeightInPoints((short) 10);
titleStyle = workbook.createCellStyle();
titleStyle.setFont(titleFont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
// 遍历集合数据，产生数据行
headers = headers;
Row row3;
int kk=0;
row3 = sheet.createRow((short) 0);
for (String key1 : headers.keySet()) {
row3.createCell((short) kk).setCellValue(String.valueOf(headers.get(key1)));
kk=kk+1;
}
for(int i=0;i<dataset.size();i++) {
row3 = sheet.createRow((short) i+1);
List rowData = new ArrayList();
Map<String, Object> hashMap = dataset.get(i);
kk=0;
for (String key1 : headers.keySet()) {
if (dataset.get(i).get(key1) == null) {
rowData.add("");
row3.createCell((short) kk).setCellValue("");
} else {
// rowData.add(dataset.get(i).get(key1));
String name = String.valueOf(hashMap.get(key1));
if(name!=null){
int as = 1;
}
if("项目编号".equals(key1)){
String[] names = (String[]) hashMap.get(key1);
String namez = "";
for(int m=0;m<names.length;m++){
namez = namez +","+names[m];
}
if("".equals(namez)){
}else{
namez = namez.substring(1);
}
row3.createCell((short) kk).setCellValue(namez);
}else{
row3.createCell((short) kk).setCellValue(name);
}
}
kk=kk+1;
}
}
sheet.setDefaultColumnWidth(30);
List<ProjectTypeStageAllOutVO> lists = list;
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date stDate = vo.getStartDate();
Date endDate = vo.getEndDate();
long from1 = stDate.getTime();
long to1 = endDate.getTime();
int days = (int) ((to1 - from1) / (1000 * 60 * 60 * 24));
//导出标题头 根据筛选条件循环复制标题头
LinkedHashMap<String, Integer> linkMap = new LinkedHashMap<>();
int num = 5;
for(int k=0;k<days;k++){
String kDate = "";
Calendar calendar = Calendar.getInstance();
calendar.setTime(stDate);
calendar.add(Calendar.DAY_OF_MONTH, +k);//+1今天的时间加一天
kDate = dateFormat.format(calendar.getTime());
headers.put(kDate, kDate);
linkMap.put(kDate,num+k);
}
List<Short> listColor = new ArrayList<>();
listColor.add(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.BRIGHT_GREEN.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.TURQUOISE.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.PLUM.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.ROSE.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.LIGHT_GREEN.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.CORNFLOWER_BLUE.getIndex());
listColor.add(HSSFColor.HSSFColorPredefined.LEMON_CHIFFON.getIndex());
for(int i=0;i<list.size();i++){
int rNum = i+1;
Row row2 = sheet.getRow(rNum);
CellStyle cellStyle = workbook.createCellStyle();
List<ProjectTypeStageOutVO> listInfoStage = list.get(i).getListInfoStage();
HashMap<String,Integer> numMap = new HashMap<>();
for(int m=0;m<listInfoStage.size();m++){
ProjectTypeStageOutVO voN = listInfoStage.get(m);
Date stDateN = dateFormat.parse(voN.getStartDate());
Date endDateN = dateFormat.parse(voN.getEndDate());
long from1N = stDateN.getTime();
long to1N = endDateN.getTime();
int daysN = (int) ((to1N - from1N) / (1000 * 60 * 60 * 24));
//根据日期 一个单元格一个单元格赋值,同时记录下位置
for(int k=0;k<daysN;k++){
String kDate = "";
Calendar calendar = Calendar.getInstance();
calendar.setTime(stDateN);
calendar.add(Calendar.DAY_OF_MONTH, +k);//+1今天的时间加一天
Integer startNum = linkMap.get(dateFormat.format(stDateN));
if(startNum==null){
kDate = dateFormat.format(stDate);
}else{
kDate = dateFormat.format(calendar.getTime());
}
Integer nums = linkMap.get(kDate);
if (k > listColor.size()) {
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex());
} else {
cellStyle.setFillForegroundColor(listColor.get(k));
}
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
if(nums!=null){
row2.getCell(nums).setCellStyle(cellStyle);
}
}
Integer startNum = linkMap.get(dateFormat.format(stDateN));
if(startNum==null){
startNum = 5;
}
Integer endNum = linkMap.get(dateFormat.format(endDateN));
if(endNum==null){
endNum = 5+linkMap.size()-1;
}
int maxNum = numMap.get("max")==null?0:numMap.get("max");
//如果有重合部分 则从前一个合并单元格的下一列开始
if(startNum<maxNum){
startNum = maxNum +1;
}
CellRangeAddress region = new CellRangeAddress(rNum, rNum, startNum, endNum);
if(startNum==endNum){
}else{
sheet.addMergedRegion(region);
}
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); //左右居中
/*var cell = sheet.getRow(rNum).getCell(index["firstCol"]);
cell.CellStyle = cellstyle;*/
if(endNum>maxNum){
maxNum=endNum;
}
numMap.put("max",maxNum);
}
}
}
private static int setCellValue(HSSFCell cell,Object value,String pattern,int cellNum,Field field,HSSFRow row){
String textValue = null;
if (value instanceof Integer) {
int intValue = (Integer) value;
cell.setCellValue(intValue);
} else if (value instanceof Float) {
float fValue = (Float) value;
cell.setCellValue(fValue);
} else if (value instanceof Double) {
double dValue = (Double) value;
cell.setCellValue(dValue);
} else if (value instanceof Long) {
long longValue = (Long) value;
cell.setCellValue(longValue);
} else if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
cell.setCellValue(bValue);
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(pattern);
textValue = sdf.format(date);
} else if (value instanceof String[]) {
String[] strArr = (String[]) value;
for (int j = 0; j < strArr.length; j++) {
String str = strArr[j];
cell.setCellValue(str);
if (j != strArr.length - 1) {
cellNum++;
cell = row.createCell(cellNum);
}
}
} else if (value instanceof Double[]) {
Double[] douArr = (Double[]) value;
for (int j = 0; j < douArr.length; j++) {
Double val = douArr[j];
// 值不为空则set Value
if (val != null) {
cell.setCellValue(val);
}
if (j != douArr.length - 1) {
cellNum++;
cell = row.createCell(cellNum);
}
}
} else {
// 其它数据类型都当作字符串简单处理
String empty = StringUtils.EMPTY;
if(field != null) {
ExcelCell anno = field.getAnnotation(ExcelCell.class);
if (anno != null) {
empty = anno.defaultValue();
}
}
textValue = value == null ? empty : value.toString();
}
if (textValue != null) {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
if("null".equals(textValue)){
cell.setCellValue("");
}
else{
cell.setCellValue(richString);
}
}
return cellNum;
}
/**
* 把Excel的数据封装成voList
*
* @param clazz vo的Class
* @param inputStream excel输入流
* @param pattern 如果有时间数据，设定输入格式。默认为"yyy-MM-dd"
* @param logs 错误log集合
* @param arrayCount 如果vo中有数组类型,那就按照index顺序,把数组应该有几个值写上.
* @return voList
* @throws RuntimeException
*/
@SuppressWarnings("unchecked")
public static <T> Collection<T> importExcel(Class<T> clazz, InputStream inputStream,
String pattern, ExcelLogs logs, Integer... arrayCount) {
Workbook workBook;
try {
workBook = WorkbookFactory.create(inputStream);
} catch (Exception e) {
LG.error("load excel file error",e);
return null;
}
List<T> list = new ArrayList<>();
Sheet sheet = workBook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
try {
List<ExcelLog> logList = new ArrayList<>();
// Map<title,index>
Map<String, Integer> titleMap = new HashMap<>();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
if (row.getRowNum() == 0) {
if (clazz == Map.class) {
// 解析map用的key,就是excel标题行
Iterator<Cell> cellIterator = row.cellIterator();
Integer index = 0;
while (cellIterator.hasNext()) {
String value = cellIterator.next().getStringCellValue();
titleMap.put(value, index);
index++;
}
}
continue;
}
// 整行都空，就跳过
boolean allRowIsNull = true;
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Object cellValue = getCellValue(cellIterator.next());
if (cellValue != null) {
allRowIsNull = false;
break;
}
}
if (allRowIsNull) {
LG.warn("Excel row " + row.getRowNum() + " all row value is null!");
continue;
}
StringBuilder log = new StringBuilder();
if (clazz == Map.class) {
Map<String, Object> map = new HashMap<>();
for (String k : titleMap.keySet()) {
Integer index = titleMap.get(k);
Cell cell = row.getCell(index);
// 判空
if (cell == null) {
map.put(k, null);
} else {
cell.setCellType(CellType.STRING);
String value = cell.getStringCellValue();
map.put(k, value);
}
}
list.add((T) map);
} else {
T t = clazz.newInstance();
int arrayIndex = 0;// 标识当前第几个数组了
int cellIndex = 0;// 标识当前读到这一行的第几个cell了
List<FieldForSortting> fields = sortFieldByAnno(clazz);
for (FieldForSortting ffs : fields) {
Field field = ffs.getField();
field.setAccessible(true);
if (field.getType().isArray()) {
Integer count = arrayCount[arrayIndex];
Object[] value;
if (field.getType().equals(String[].class)) {
value = new String[count];
} else {
// 目前只支持String[]和Double[]
value = new Double[count];
}
for (int i = 0; i < count; i++) {
Cell cell = row.getCell(cellIndex);
String errMsg = validateCell(cell, field, cellIndex);
if (StringUtils.isBlank(errMsg)) {
value[i] = getCellValue(cell);
} else {
log.append(errMsg);
log.append(";");
logs.setHasError(true);
}
cellIndex++;
}
field.set(t, value);
arrayIndex++;
} else {
Cell cell = row.getCell(cellIndex);
String errMsg = validateCell(cell, field, cellIndex);
if (StringUtils.isBlank(errMsg)) {
Object value = null;
// 处理特殊情况,Excel中的String,转换成Bean的Date
if (field.getType().equals(Date.class)
&& cell.getCellTypeEnum() == CellType.STRING) {
Object strDate = getCellValue(cell);
try {
value = new SimpleDateFormat(pattern).parse(strDate.toString());
} catch (ParseException e) {
errMsg =
MessageFormat.format("the cell [{0}] can not be converted to a date ",
CellReference.convertNumToColString(cell.getColumnIndex()));
}
} else {
value = getCellValue(cell);
// 处理特殊情况,excel的value为String,且bean中为其他,且defaultValue不为空,那就=defaultValue
ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
if (value instanceof String && !field.getType().equals(String.class)
&& StringUtils.isNotBlank(annoCell.defaultValue())) {
value = annoCell.defaultValue();
}
}
field.set(t, value);
}
if (StringUtils.isNotBlank(errMsg)) {
log.append(errMsg);
log.append(";");
logs.setHasError(true);
}
cellIndex++;
}
}
list.add(t);
logList.add(new ExcelLog(t, log.toString(), row.getRowNum() + 1));
}
}
logs.setLogList(logList);
} catch (InstantiationException e) {
throw new RuntimeException(MessageFormat.format("can not instance class:{0}",
clazz.getSimpleName()), e);
} catch (IllegalAccessException e) {
throw new RuntimeException(MessageFormat.format("can not instance class:{0}",
clazz.getSimpleName()), e);
}
return list;
}
/**
* 驗證Cell類型是否正確
*
* @param cell cell單元格
* @param field 欄位
* @param cellNum 第幾個欄位,用於errMsg
* @return
*/
private static String validateCell(Cell cell, Field field, int cellNum) {
String columnName = CellReference.convertNumToColString(cellNum);
String result = null;
CellType[] cellTypeArr = validateMap.get(field.getType());
if (cellTypeArr == null) {
result = MessageFormat.format("Unsupported type [{0}]", field.getType().getSimpleName());
return result;
}
ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
if (cell == null
|| (cell.getCellTypeEnum() == CellType.STRING && StringUtils.isBlank(cell
.getStringCellValue()))) {
if (annoCell != null && annoCell.valid().allowNull() == false) {
result = MessageFormat.format("the cell [{0}] can not null", columnName);
}
;
} else if (cell.getCellTypeEnum() == CellType.BLANK && annoCell.valid().allowNull()) {
return result;
} else {
List<CellType> cellTypes = Arrays.asList(cellTypeArr);
// 如果類型不在指定範圍內,並且沒有默認值
if (!(cellTypes.contains(cell.getCellTypeEnum()))
|| StringUtils.isNotBlank(annoCell.defaultValue())
&& cell.getCellTypeEnum() == CellType.STRING) {
StringBuilder strType = new StringBuilder();
for (int i = 0; i < cellTypes.size(); i++) {
CellType cellType = cellTypes.get(i);
strType.append(getCellTypeByInt(cellType));
if (i != cellTypes.size() - 1) {
strType.append(",");
}
}
result =
MessageFormat.format("the cell [{0}] type must [{1}]", columnName, strType.toString());
} else {
// 类型符合验证,但值不在要求范围内的
// String in
if (annoCell.valid().in().length != 0 && cell.getCellTypeEnum() == CellType.STRING) {
String[] in = annoCell.valid().in();
String cellValue = cell.getStringCellValue();
boolean isIn = false;
for (String str : in) {
if (str.equals(cellValue)) {
isIn = true;
}
}
if (!isIn) {
result = MessageFormat.format("the cell [{0}] value must in {1}", columnName, in);
}
}
// 数字型
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
double cellValue = cell.getNumericCellValue();
// 小于
if (!Double.isNaN(annoCell.valid().lt())) {
if (!(cellValue < annoCell.valid().lt())) {
result =
MessageFormat.format("the cell [{0}] value must less than [{1}]", columnName,
annoCell.valid().lt());
}
}
// 大于
if (!Double.isNaN(annoCell.valid().gt())) {
if (!(cellValue > annoCell.valid().gt())) {
result =
MessageFormat.format("the cell [{0}] value must greater than [{1}]", columnName,
annoCell.valid().gt());
}
}
// 小于等于
if (!Double.isNaN(annoCell.valid().le())) {
if (!(cellValue <= annoCell.valid().le())) {
result =
MessageFormat.format("the cell [{0}] value must less than or equal [{1}]",
columnName, annoCell.valid().le());
}
}
// 大于等于
if (!Double.isNaN(annoCell.valid().ge())) {
if (!(cellValue >= annoCell.valid().ge())) {
result =
MessageFormat.format("the cell [{0}] value must greater than or equal [{1}]",
columnName, annoCell.valid().ge());
}
}
}
}
}
return result;
}
/**
* 根据annotation的seq排序后的栏位
*
* @param clazz
* @return
*/
private static List<FieldForSortting> sortFieldByAnno(Class<?> clazz) {
Field[] fieldsArr = clazz.getDeclaredFields();
List<FieldForSortting> fields = new ArrayList<>();
List<FieldForSortting> annoNullFields = new ArrayList<>();
for (Field field : fieldsArr) {
ExcelCell ec = field.getAnnotation(ExcelCell.class);
if (ec == null) {
// 没有ExcelCell Annotation 视为不汇入
continue;
}
int id = ec.index();
fields.add(new FieldForSortting(field, id));
}
fields.addAll(annoNullFields);
sortByProperties(fields, true, false, "index");
return fields;
}
@SuppressWarnings({ "rawtypes", "unchecked" })
private static void sortByProperties(List<? extends Object> list, boolean isNullHigh,
boolean isReversed, String... props) {
if (CollectionUtils.isNotEmpty(list)) {
Comparator<?> typeComp = ComparableComparator.getInstance();
if (isNullHigh == true) {
typeComp = ComparatorUtils.nullHighComparator(typeComp);
} else {
typeComp = ComparatorUtils.nullLowComparator(typeComp);
}
if (isReversed) {
typeComp = ComparatorUtils.reversedComparator(typeComp);
}
List<Object> sortCols = new ArrayList<Object>();
if (props != null) {
for (String prop : props) {
sortCols.add(new BeanComparator(prop, typeComp));
}
}
if (sortCols.size() > 0) {
Comparator<Object> sortChain = new ComparatorChain(sortCols);
Collections.sort(list, sortChain);
}
}
}
}
三、导入
@PostMapping(value = "/import.ajax")
@ResponseBody
@ApiOperation("导入")
public Result importKeyParam(@RequestParam(required = true) MultipartFile file,
@RequestParam(required = true) Integer importType,
@RequestParam(required = true) Integer type) {
// 文件名校验
String fileName = file.getOriginalFilename();
if (!fileName.matches(GenConstants.LOWEXCEL) && !fileName.matches(GenConstants.HIGHEXCEL)) {
return ResultGenerator.genFailResult(ResultEnum.FILE_INCORRENT.getMessage());
}
// 文件大小校验
if (file.getSize() / CommonConstant.FILE_FORMAT_SIZE / CommonConstant.FILE_FORMAT_SIZE > 0.1) {
return ResultGenerator.genFailResult("文件过大，不能超过" + CommonConstant.BIG_EXCEL_SIZE + "KB");
}
Collection<Map> maps;
try {
maps = ExcelUtil.importExcel(Map.class, file.getInputStream(), "yyyy-MM-dd HH:mm:ss", new ExcelLogs(), 0);
} catch (Exception e) {
return ResultGenerator.genFailResult("请求或者文件有问题！");
}
if (maps == null || maps.size() == 0) {
return ResultGenerator.genFailResult(ResultEnum.EMPTY_EXCEL_DATA.getMessage());
}
if (CustomFieldEnum.getByType(type) != null) {
HashMap<String, String> headers = new LinkedHashMap<>();
getExpostHeader(headers, type, true);
try {
List<Map> mapList = Arrays.asList(maps.toArray(new Map[0]));
// 保存
HashMap<String, Object> ret = getInterComService(type).importData(headers, mapList, importType, CustomFieldEnum.getByType(type));
return ResultGenerator.genSuccessResult(ret);
} catch (Exception e) {
return ResultGenerator.genFailResult("导入失败：逻辑有误！");
}
} else {
return ResultGenerator.genFailResult("导入失败：模块类型有误！");
}
}