文件上传，下载，预览，删除（File），分页接口
我们的业务场景：上传文件之类的东西，并展示；包括CRUD的功能与一体；1.2 分页接口1.3公共实体类1.4 公共的 mapper.java/xml（都放在一起）1.4.1FileMapper .java1.4.2FileMapper .xml1.5 Dao层1.6 调用常理1.7数据库执行语句2.0 文件上传代码 （上面有公共的下面提供Controller与ServiceImpl接口）3.0 文
·
文件上传，下载，预览，删除（File）
我们的业务场景：上传文件之类的东西，并展示；包括CRUD的功能与一体；
1.公共参数方法
1.1公共返回类型定义
package com.ces.yun.framework.base.vo;
import java.io.Serializable;
import java.util.HashMap;
import java.util.Map;
import com.ces.yun.framework.base.TokenHolder;
/**
* 定义前后端交互对象具体属性
*
* @version 2019年4月17日
*/
public class MsgContext implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 服务代码
*/
private String code = MSG_CODE_SUCCESS;
/**
* 服务器server端返回成功
*/
public static final String MSG_CODE_SUCCESS = "1";
/**
* 服务器server端返回失败
*/
public static final String MSG_CODE_ERROR = "-1";
/**
* 通知消息
*/
private String msg;
/**
* 结果数据
*/
private Map<String, Object> result = new HashMap<String, Object>();
public MsgContext(){
this.setCode(MSG_CODE_SUCCESS);
TokenHolder.set(this);
}
/**
* 方便使用，添加返回结果
* @param key 值名称
* @param value 具体值
*/
public void addResult(String key, Object value) {
if (result == null) {
result = new HashMap<String, Object>();
}
result.put(key, value);
}
public static MsgContext createErrorContext(String msg) {
MsgContext mc = new MsgContext();
mc.setCode(MSG_CODE_ERROR);
mc.setMsg(msg);
return mc;
}
public static MsgContext createSuccessContext(String msg) {
MsgContext mc = new MsgContext();
mc.setCode(MSG_CODE_SUCCESS);
mc.setMsg(msg);
return mc;
}
public static MsgContext createSuccessContext(String msg, Long id) {
MsgContext mc = new MsgContext();
mc.setCode(MSG_CODE_SUCCESS);
mc.setMsg(msg);
mc.addResult("id", id);
return mc;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public Map<String, Object> getResult() {
return result;
}
public void setResult(Map<String, Object> result) {
this.result = result;
}
}
1.2 分页接口
package com.ces.yun.business.datasources.vo;
import java.io.Serializable;
import lombok.Data;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
@Data
public class MyPageInfoVo <T> implements Serializable{
private static final long serialVersionUID = 1L;
//当前页
private Integer pageNum;
//每页的数量
private Integer pageSize;
//当前页的数量
private int size;
//当前页面第一个元素在数据库中的行号
private int startRow;
//当前页面最后一个元素在数据库中的行号
private int endRow;
//总记录数
private int total;
//总页数
private int pages;
//结果集
private List<T> list;
//前一页
private int prePage;
//下一页
private int nextPage;
//是否为第一页
private boolean isFirstPage = false;
//是否为最后一页
private boolean isLastPage = false;
//是否有前一页
private boolean hasPreviousPage = false;
//是否有下一页
private boolean hasNextPage = false;
//导航页码数
private int navigatePages;
//所有导航页号
private int[] navigatepageNums;
//导航条上的第一页
private int navigateFirstPage;
//导航条上的最后一页
private int navigateLastPage;
public Integer getPageNum() {
return pageNum;
}
public void setPageNum(Integer pageNum) {
this.pageNum = pageNum;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public int getSize() {
return size;
}
public void setSize(int size) {
this.size = size;
}
public int getStartRow() {
return startRow;
}
public void setStartRow(int startRow) {
this.startRow = startRow;
}
public int getEndRow() {
return endRow;
}
public void setEndRow(int endRow) {
this.endRow = endRow;
}
public int getTotal() {
return total;
}
public void setTotal(int total) {
this.total = total;
}
public int getPages() {
return pages;
}
public void setPages(int pages) {
this.pages = pages;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
public int getPrePage() {
return prePage;
}
public void setPrePage(int prePage) {
this.prePage = prePage;
}
public int getNextPage() {
return nextPage;
}
public void setNextPage(int nextPage) {
this.nextPage = nextPage;
}
public boolean isFirstPage() {
return isFirstPage;
}
public void setFirstPage(boolean firstPage) {
isFirstPage = firstPage;
}
public boolean isLastPage() {
return isLastPage;
}
public void setLastPage(boolean lastPage) {
isLastPage = lastPage;
}
public boolean isHasPreviousPage() {
return hasPreviousPage;
}
public void setHasPreviousPage(boolean hasPreviousPage) {
this.hasPreviousPage = hasPreviousPage;
}
public boolean isHasNextPage() {
return hasNextPage;
}
public void setHasNextPage(boolean hasNextPage) {
this.hasNextPage = hasNextPage;
}
public int getNavigatePages() {
return navigatePages;
}
public void setNavigatePages(int navigatePages) {
this.navigatePages = navigatePages;
}
public int[] getNavigatepageNums() {
return navigatepageNums;
}
public void setNavigatepageNums(int[] navigatepageNums) {
this.navigatepageNums = navigatepageNums;
}
public int getNavigateFirstPage() {
return navigateFirstPage;
}
public void setNavigateFirstPage(int navigateFirstPage) {
this.navigateFirstPage = navigateFirstPage;
}
public int getNavigateLastPage() {
return navigateLastPage;
}
public void setNavigateLastPage(int navigateLastPage) {
this.navigateLastPage = navigateLastPage;
}
/**
* 对list集合进行分页
* @param list 需要分页的集合
* @param pageNum 当前页
* @param pageSize 每页的数量
*/
public MyPageInfoVo(List<T> list,Integer pageNum,Integer pageSize) {
this(list, 8, pageNum, pageSize);
}
/**
* 对list集合进行分页
* @param list 需要分页的集合
* @param navigatePages 导航页码数
* @param pageNum 当前页
* @param pageSize 每页的数量
*/
public MyPageInfoVo(List<T> list, int navigatePages,int pageNum,int pageSize) {
if (list instanceof Collection) {
// 总记录数
this.total = list.size();
// 为了跟pageHelper一致，当pageNum<1时,按第一页处理
int yourPageNum = pageNum;
pageNum= pageNum<1 ? 1:pageNum;
// 当前页码
this.pageNum = pageNum;
// 每页显示的记录数
this.pageSize = pageSize;
// 总页码数
this.pages = (int)Math.ceil(this.total*1.0/this.pageSize);
// 导航条页码数
this.navigatePages = navigatePages;
// 开始行号
this.startRow = this.pageNum * this.pageSize - (this.pageSize - 1);
// 结束行号
this.endRow = this.pageNum * this.pageSize;
// 当结束行号>总行数，结束行号=0
if(this.endRow > this.total){
if(this.startRow > this.total){
this.endRow = 0;
this.startRow = 0;
}else {
this.endRow =this.total;
}
}
//计算导航页
calcNavigatepageNums();
//计算前后页，第一页，最后一页
calcPage();
//判断页面边界
judgePageBoudary();
// 当pageNum超过最大页数，则size=0，list为空
if(this.pageNum > this.pages){
this.size = 0;
this.list = Collections.EMPTY_LIST;
}else {
this.list = list.subList(startRow - 1,endRow);
this.size = this.list.size();
}
this.pageNum = yourPageNum;
}
}
/**
* 计算导航页
*/
private void calcNavigatepageNums() {
//当总页数小于或等于导航页码数时
if (pages <= navigatePages) {
navigatepageNums = new int[pages];
for (int i = 0; i < pages; i++) {
navigatepageNums[i] = i + 1;
}
} else { //当总页数大于导航页码数时
navigatepageNums = new int[navigatePages];
int startNum = pageNum - navigatePages / 2;
int endNum = pageNum + navigatePages / 2;
if (startNum < 1) {
startNum = 1;
//(最前navigatePages页
for (int i = 0; i < navigatePages; i++) {
navigatepageNums[i] = startNum++;
}
} else if (endNum > pages) {
endNum = pages;
//最后navigatePages页
for (int i = navigatePages - 1; i >= 0; i--) {
navigatepageNums[i] = endNum--;
}
} else {
//所有中间页
for (int i = 0; i < navigatePages; i++) {
navigatepageNums[i] = startNum++;
}
}
}
}
/**
* 计算前后页，第一页，最后一页
*/
private void calcPage() {
if (navigatepageNums != null && navigatepageNums.length > 0) {
navigateFirstPage = navigatepageNums[0];
navigateLastPage = navigatepageNums[navigatepageNums.length - 1];
if (pageNum > 1) {
prePage = pageNum - 1;
}
if (pageNum < pages) {
nextPage = pageNum + 1;
}
}
}
/**
* 判定页面边界
*/
private void judgePageBoudary() {
isFirstPage = pageNum == 1;
isLastPage = pageNum == pages;
hasPreviousPage = pageNum > 1;
hasNextPage = pageNum < pages;
}
}
1.3公共实体类
package com.ces.yun.business.datasources.vo;
import com.ces.yun.framework.base.vo.BaseVO;
import org.hibernate.valyuntor.constraints.Length;
import java.util.Date;
public class FileDypeVo {
/**
* 主键
*/
private Long id;
/**
* 文件名称
*/
private String fileName;
/**
* 文件路径
*/
private String filePath;
/**
* 文件所属类型 状态(1:a文件 2:B文件 3:C文件)
*/
@Length(max = 1, message = "状态不得超过1个字符")
private String fileType;
/**
* 文件上传时间
*/
private String filePutDate;
/**
* 上传分页类型
*/
private String filePageType;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public String getFilePath() {
return filePath;
}
public void setFilePath(String filePath) {
this.filePath = filePath;
}
public String getFileType() {
return fileType;
}
public void setFileType(String fileType) {
this.fileType = fileType;
}
public String getFilePutDate() {
return filePutDate;
}
public void setFilePutDate(String filePutDate) {
this.filePutDate = filePutDate;
}
public String getFilePageType() {
return filePageType;
}
public void setFilePageType(String filePageType) {
this.filePageType = filePageType;
}
@Override
public String toString() {
return "FileDypeVo [id=" + id + ", fileName=" + fileName + ", filePath=" + filePath + ", fileType=" + fileType
+ ", filePutDate=" + filePutDate + ", filePageType=" + filePageType + "]";
}
}
1.4 公共的 mapper.java/xml（都放在一起）
1.4.1 FileMapper .java
package com.ces.yun.business.datasources.dao;
import org.apache.ibatis.annotations.Param;
import com.ces.yun.business.datasources.vo.FileDypeVo;
import java.util.List;
/**
* 上传文件
*/
public interface FileMapper {
int fileinsert(FileDypeVo fileDypeVo);
List<FileDypeVo> selectFile(@Param("filePageType")String filePageType);
FileDypeVo selectFileId(@Param("id")Long id);
}
1.4.2 FileMapper .xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.ces.yun.business.datasources.dao.FileMapper">
<resultMap id="FileResultMap" type="com.ces.yun.business.datasources.vo.FileDypeVo">
<id column="id" property="id" jdbcType="BIGINT" />
<result column="file_Name" property="fileName" jdbcType="VARCHAR" />
<result column="file_Path" property="filePath" jdbcType="VARCHAR" />
<result column="file_Type" property="fileType" jdbcType="VARCHAR" />
<result column="file_Put_Date" property="filePutDate" jdbcType="VARCHAR" />
<result column="file_page_Type" property="filePageType" jdbcType="VARCHAR" />
</resultMap>
<sql id="File_Column_List" >
id, file_Name, file_Path, file_Type, file_Put_Date, file_page_Type
</sql>
<insert id="fileinsert" parameterType="com.ces.yun.business.datasources.vo.FileDypeVo" >
insert into com_ds_file (id, file_Name, file_Path,
file_Type, file_Put_Date,file_page_Type
)
values (#{id,jdbcType=BIGINT}, #{fileName,jdbcType=VARCHAR}, #{filePath,jdbcType=VARCHAR},
#{fileType,jdbcType=VARCHAR}, #{filePutDate,jdbcType=VARCHAR},#{filePageType,jdbcType=VARCHAR} )
</insert>
<select id="selectFile" resultMap="FileResultMap" parameterType="com.ces.yun.business.datasources.vo.FileDypeVo">
select <include refid="File_Column_List"/>
from com_ds_file where file_page_Type=#{filePageType}
</select>
<select id="selectFileId" resultMap="FileResultMap" parameterType="com.ces.yun.business.datasources.vo.FileDypeVo">
select <include refid="File_Column_List"/>
from com_ds_file where id=#{id}
</select>
</mapper>
1.5 Dao层
package com.ces.yun.business.datasources.service;
import java.util.List;
import com.ces.yun.business.datasources.vo.DsDragVO;
import com.ces.yun.business.datasources.vo.DsTypeVO;
import com.ces.yun.framework.base.exception.ServiceException;
public interface DataSourcesService{
/**
* 上传文件
* @param request
* @param uploadFile
* @return
* @throws ServiceException
*/
Long updateFile(HttpServletRequest request, MultipartFile uploadFile,String fileType,String filePageType)throws ServiceException;
/**
* 查询
* @param pageNum
* @param pageSize
* @param filePageType
* @return
*/
MyPageInfoVo<FileDypeVo> selectFile(Integer pageNum,Integer pageSize,String filePageType);
/**
* 删除文件
* @param id
* @return
*/
Long delectFile(Long id);
/**
* 下载预览
* @param response
* @param id
* @param isOnLine
* @return
* @throws IOException
*/
FileDypeVo downFile(HttpServletResponse response,Long id,Boolean isOnLine) throws IOException;
}
1.6 调用常理
package com.ces.yun.framework.base;
/*
* 定义文件 使用常量 yan 2022/10/13
*
*/
public interface FileController {
/**
* 上传文件 页面1 文件管理
*/
String FILE_FIRSTLY = "10";
/**
* 上传文件 页面2 成果管理
*/
String FILE_SECOND = "20";
/**
* 上传文件 页面3 典型场景管理管理
*/
String FILE_THIRD = "30";
/**
* 页面下对应子模块
* 页面1
* 1.帮助文档
* 2.规范
* 3.设计小样
* 4.场景解说词
* 页面2
* 1.设计小样
* 2.场景解说词
* 3.第三方应用成功
*/
String FILE_ONE = "1";
String FILE_TWO = "2";
String FILE_THREE = "3";
String FILE_FOUR = "4";
/**
* 路径拼接 页面1
*
*/
String FILE_FIRSTLY_PATH = "//FIRSTLY//";
/**
* 路径拼接 页面2
*
*/
String FILE_SECOND_PATH = "//SECOND//";
/**
* 路径拼接 页面3
*
*/
String FILE_THIRD_PATH = "//THIRD//";
}
1.7 数据库执行语句
/*
Navicat Premium Data Transfer
Source Server : 可视化项目
Source Server Type : MySQL
Source Server Version : 50736
Source Host : localhost:3306
Source Schema : zhongdian
Target Server Type : MySQL
Target Server Version : 50736
File Encoding : 65001
Date: 14/10/2022 11:39:07
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for com_ds_file
-- ----------------------------
DROP TABLE IF EXISTS `com_ds_file`;
CREATE TABLE `com_ds_file` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`file_Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`file_Path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`file_Type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`file_Put_Date` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`file_page_Type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 59766911012865 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
2.0 文件上传代码 （上面有公共的下面提供Controller与ServiceImpl接口）
/**
* 上传接口文件
* @param multipartFile
* @param request
* @param fileType
* @param filePageType
* @return
*/
@RequestMapping(value ="/updateFile" ,method = RequestMethod.POST)
@ResponseBody
//@RequestParam()注解的参数要和前端代码name属性值对应
public MsgContext updateFile(@RequestParam("file") MultipartFile multipartFile,HttpServletRequest request,
@RequestParam("fileType") String fileType,
@RequestParam("filePageType") String filePageType){
try{
//判断文件是否为空 isEmpty
if (multipartFile == null){
return MsgContext.createErrorContext("上传文件为空");
}
MultipartFile uploadFile = getSingleFile(request);
Long id = dsService.updateFile(request, uploadFile,fileType,filePageType);
return MsgContext.createSuccessContext("新增成功", id);
}catch (Exception e) {
return MsgContext.createErrorContext("上传失败");
}
}
/**
* 上传文件
*
* @throws IOException
*/
@Override
@Transactional(readOnly = false)
public Long updateFile(HttpServletRequest request, MultipartFile multipartFile, String fileType,
String filePageType) throws ServiceException {
// 获取文件的原名称 getOriginalFilename 后缀
String OriginalFilename = multipartFile.getOriginalFilename();
if (!(OriginalFilename.endsWith(".xlsx") || OriginalFilename.endsWith(".xls")
|| OriginalFilename.endsWith(".txt"))) {
throw new ServiceException("导入文件格式错误，请导入xlsx/xls/txt格式的文件！");
}
String name = OriginalFilename.substring(0, OriginalFilename.indexOf("."));
// 获取时间戳和文件的扩展名，拼接成一个全新的文件名； 用时间戳来命名是为了避免文件名冲突
String fileName = name + "-" + System.currentTimeMillis() + "."
+ OriginalFilename.substring(OriginalFilename.lastIndexOf(".") + 1);
// 定义文件存放路径
String filePath = null;
if (filePageType.equals(FileController.FILE_FIRSTLY)) {
filePath = remoteBaseUrl + FileController.FILE_FIRSTLY_PATH;
} else if (filePageType.equals(FileController.FILE_THIRD)) {
filePath = remoteBaseUrl + FileController.FILE_THIRD_PATH;
} else {
filePath = remoteBaseUrl + FileController.FILE_SECOND_PATH;
}
// 不存在新增 一个目录（文件夹） 路径+文件名+后缀
File dest = new File(filePath + fileName);
// 判断filePath目录是否存在，如不存在，就新建一个
if (!dest.getParentFile().canExecute()) {
dest.getParentFile().mkdirs(); // 新建一个目录
}
FileDypeVo fileDypeVo = new FileDypeVo();
SimpleDateFormat sdf = new SimpleDateFormat();// 格式化时间
sdf.applyPattern("yyyy-MM-dd HH:mm:ss");
Date date = new Date();
fileDypeVo.setId(IDNexter.getInstance().next());
fileDypeVo.setFileName(fileName);
fileDypeVo.setFilePath(filePath);
fileDypeVo.setFileType(fileType);
fileDypeVo.setFilePageType(filePageType);
fileDypeVo.setFilePutDate(sdf.format(date));
LOGGER.info("保存数据" + JSON.toJSONString(fileDypeVo + "OriginalFilename" + OriginalFilename));
int bdCnt = fileMapper.fileinsert(Sutil.checkData(StringUtils.EMPTY, fileDypeVo));
if (bdCnt < 1) {
throw new ServiceException("导入文件失败");
}
try {
// 文件输出
multipartFile.transferTo(dest);
} catch (Exception e) {
e.printStackTrace();
// 拷贝失败要有提示
return fileDypeVo.getId();
}
return fileDypeVo.getId();
}
3.0 文件查询接口代码 （上面有公共的下面提供Controller与ServiceImpl接口）
/**
* 查询上传接口
* @param filePageType
* @param pageNum
* @param pageSize
* @return
*/
@RequestMapping(value ="/selectFile" ,method = RequestMethod.POST)
@ResponseBody
public MsgContext selectFile(@RequestParam("filePageType") String filePageType,
@RequestParam(value = "pageNum",name = "pageNum" ,required = false,defaultValue = "1")Integer pageNum,
@RequestParam(value = "pageSize",name = "pageSize",required = false,defaultValue = "1000")Integer pageSize){
try{
//判断文件是否为空 isEmpty
if (filePageType == null){
return MsgContext.createErrorContext("类型为空");
}
MsgContext mc = new MsgContext();
MyPageInfoVo<FileDypeVo> fInfoVo = dsService.selectFile(pageNum,pageSize,filePageType);
mc.addResult("data", fInfoVo);
mc.setMsg("查询成功");
return mc;
}catch (Exception e) {
return MsgContext.createErrorContext("上传失败,原因=》"+e.getMessage());
}
}
/**
* 查询文件
*
*/
@Override
public MyPageInfoVo<FileDypeVo> selectFile(Integer pageNum, Integer pageSize, String filePageType) {
List<FileDypeVo> fileDypeVos = fileMapper.selectFile(filePageType);
MyPageInfoVo<FileDypeVo> myPageInfo = new MyPageInfoVo<>(fileDypeVos, pageNum, pageSize);
// TODO Auto-generated method stub
return myPageInfo;
}
4.0 文件预览/下载接口代码 （上面有公共的下面提供Controller与ServiceImpl接口）
isOnLine 默认true在线打开 false下载
/**
* 文件预览以及下载接口
* @param response
* @param id
* @param isOnLine
* @return
*/
@RequestMapping(value ="/downFile" ,method = RequestMethod.POST)
@ResponseBody
public MsgContext downFile(HttpServletResponse response,@RequestParam("id") Long id,
@RequestParam(value = "isOnLine",name = "isOnLine" ,required = false,defaultValue = "true")Boolean isOnLine
){
//isOnLine 默认true在线打开 false下载
try{
//判断文件是否为空 isEmpty
if (id == null){
return MsgContext.createErrorContext("id不能为空");
}
MsgContext mc = new MsgContext();
FileDypeVo fInfoVo = dsService.downFile(response,id,isOnLine);
mc.addResult("data", fInfoVo);
mc.setMsg("查询成功");
return mc;
}catch (Exception e) {
return MsgContext.createErrorContext("下载/预览失败,原因=》"+e.getMessage());
}
}
@Override
public FileDypeVo downFile(HttpServletResponse response, Long id, Boolean isOnLine) throws IOException {
FileDypeVo fileDypeVos = fileMapper.selectFileId(id);
String path = fileDypeVos.getFilePath();
String name = fileDypeVos.getFileName();
File file = new File(path+name);
LOGGER.info("file==>"+file+"|file.exists()==>"+file.exists());
if (!file.exists()) {
throw new ServiceException("未在指定路径发现该文件"+file);
}
byte[] buf = new byte[1024];
int len = 0;
BufferedInputStream br = new BufferedInputStream(new FileInputStream(file));
response.reset(); // 非常重要
if (isOnLine) { // 在线打开方式
URL url = new URL("file:///" + path);
response.setContentType(url.openConnection().getContentType());
response.setHeader("Content-Disposition", "inline; filename=" + name);
// 文件名应该编码成UTF-8
} else { // 纯下载方式
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename=" + name);
}
OutputStream out = response.getOutputStream();
while ((len = br.read(buf)) > 0)
out.write(buf, 0, len);
br.close();
out.close();
// TODO Auto-generated method stub
return null;
}
4.1 下载接口代码 （上面有公共的下面提供Controller与ServiceImpl接口）
下载：Controller
/**
* 文件下载接口
* @param response
* @param id
* @param isOnLine
* @return
*/
@RequestMapping(value ="/printFile" ,method = RequestMethod.POST)
@ResponseBody
public MsgContext printFile(HttpServletResponse response,@RequestParam("id") Long id)
{
try{
//判断文件是否为空 isEmpty
if (id == null){
return MsgContext.createErrorContext("id不能为空");
}
MsgContext mc = new MsgContext();
String fInfoVo = dsService.printFile(id);
mc.addResult("data", fInfoVo);
mc.setMsg("查询成功");
return mc;
}catch (Exception e) {
return MsgContext.createErrorContext("预览失败,原因=》"+e.getMessage());
}
}
ServerImpl
@Override
public FileDypeVo downFile(HttpServletResponse response,Long id){
//根据查询获取地址和文件名
FileDypeVo fileDypeVos = fileMapper.selectFileId(id);
String path = fileDypeVos.getFilePath();
String name = fileDypeVos.getFileName();
File file = new File(path + name);
LOGGER.info("file==>" + file + "|file.exists()==>" + file.exists());
if (!file.exists()) {
throw new ServiceException("未在指定路径发现该文件" + file);
}
//效验格式后缀
if (!(name.endsWith(".xlsx") || name.endsWith(".xls")
|| name.endsWith(".txt") || name.endsWith(".pdf")
|| name.endsWith(".docx") || name.endsWith(".doc")
|| name.endsWith(".jpg") || name.endsWith(".png")
|| name.endsWith(".zip"))) {
throw new ServiceException("导入文件格式错误，请导入xlsx/xls/txt/png/doc/docx/pdf/jpg/zip格式的文件！");
}
OutputStream os = null;
BufferedOutputStream out = null;
BufferedInputStream is = null;
try {
os = response.getOutputStream();
response.reset();// 清空输出流
response.setContentType("application/x-msdownload");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
// response.setHeader("Content-disposition", "attachment; filename="+URLEncoder.encode(filename, "UTF-8")+"."+subfix);// 设定输出文件头
//开始下载 name 是有后缀的 ab.pdf
response.setHeader("Content-disposition", "attachment; filename="+URLEncoder.encode(name, "UTF-8"));// 设定输出文件头
is = new BufferedInputStream(new FileInputStream(new File(path+name)));
out = new BufferedOutputStream(os);
byte[] buff = new byte[1024];
int len = 0;
while ((len = is.read(buff, 0, buff.length)) != -1) {
out.write(buff, 0, len);
}
out.flush();
} catch (IOException e) {
e.printStackTrace();
throw new ServiceException("下载失败");
} finally {
IOUtils.closeQuietly(out);
IOUtils.closeQuietly(is);
IOUtils.closeQuietly(os);
}
return fileDypeVos;
}
前段Vue：
/sys/comp/downLoad 接口路径
row 传的id的值进行查询相关路径
token 我们有项目验证看项目情况
window.location.href = `${ipConfig.SERVER_IP}/sys/comp/downLoad?id=${row}&token=${token}`
4.2 下载接口代码 （上面有公共的下面提供Controller与ServiceImpl接口）
控制台代码Controller
/**
* 文件预览接口
* @param response
* @param id
* @param isOnLine
* @return
*/
@RequestMapping(value ="/printFile" ,method = RequestMethod.POST)
@ResponseBody
public MsgContext printFile(HttpServletResponse response,@RequestParam("id") Long id)
{
try{
//判断文件是否为空 isEmpty
if (id == null){
return MsgContext.createErrorContext("id不能为空");
}
MsgContext mc = new MsgContext();
String fInfoVo = dsService.printFile(id);
mc.addResult("data", fInfoVo);
mc.setMsg("查询成功");
return mc;
}catch (Exception e) {
return MsgContext.createErrorContext("预览失败,原因=》"+e.getMessage());
}
}
Impl代码 采用转换base64的方式
/**
* 预览
*/
@Override
public String printFile(Long id) throws IOException {
try {
FileDypeVo fileDypeVos = fileMapper.selectFileId(id);
String path = fileDypeVos.getFilePath();
String name = fileDypeVos.getFileName();
File file = new File(path+name);
FileInputStream inputFile = new FileInputStream(file);
byte[] buffer = new byte[(int) file.length()];
inputFile.read(buffer);
inputFile.close();
System.out.println(new BASE64Encoder().encode(buffer));
return new BASE64Encoder().encode(buffer);
} catch (Exception e) {
throw new ServiceException("预览失败" + e.getMessage());
// TODO: handle exception
}
}
前段代码：
npm install --save vue-pdf //下载插件
<pdf :style="{ width: pdfWidth + '%',top:'20px' }" v-for="i in numPages" :key="i" :src="src" :page="i" ref="myPdfComponent"></pdf>
import pdf from "vue-pdf";
import CMapReaderFactory from "vue-pdf/src/CMapReaderFactory.js";
components: {
pdf,
},
previewFile({ id: fileId }).then(res => {
if (res.code == 1) {
let da = res.result.data
this.isShowOk = true
let datas = "data:application/pdf;base64," + da;
this.src = pdf.createLoadingTask({ url: datas, CMapReaderFactory });
console.log(this.src)
this.src.promise.then(pdf => {
this.numPages = pdf.numPages;
});
} else {
this.$message.error(res.msg)
}
})
前段相关模块；
4.3 Base64转换demo
package com.example.democrud.democurd.controller;
import sun.misc.BASE64Encoder;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
public class TestDemo2 {
public static void main(String[] args) throws Exception {
String path="G:\\可视化工具\\XXX\\前端\\client05\\public\\file\\FIRSTLY\\a.pdf";
TestDemo2.encodeBase64File(path);
}
/*
*actions: 将文件转成base64 字符串
*path：文件路径
*/
public static String encodeBase64File(String path) throws Exception {
File file = new File(path);
FileInputStream inputFile = new FileInputStream(file);
byte[] buffer = new byte[(int) file.length()];
inputFile.read(buffer);
inputFile.close();
System.out.println(new BASE64Encoder().encode(buffer));
return new BASE64Encoder().encode(buffer);
}
/*
*actions: 将base64字符保存文本文件
*targetPath：文件路径
*base64Code: base64字符串
*/
public void toFile(String base64Code, String targetPath) throws Exception {
byte[] buffer = base64Code.getBytes();
FileOutputStream out = new FileOutputStream(targetPath);
out.write(buffer);
out.close();
}
}
5.0 文件删除接口代码 （上面有公共的下面提供Controller与ServiceImpl接口）
@RequestMapping(value ="/delectFile" ,method = RequestMethod.POST)
@ResponseBody
public MsgContext delectFile(@RequestParam("id") Long id){
try{
//判断文件是否为空 isEmpty
if (id == null){
return MsgContext.createErrorContext("请选择需要删除文件,文件不能为空");
}
MsgContext mc = new MsgContext();
Long fInfoVo = dsService.delectFile(id);
if (fInfoVo>1) {
mc.addResult("data", "删除成功");
}
return mc;
}catch (Exception e) {
return MsgContext.createErrorContext("删除失败,原因=》"+e.getMessage());
}
}
@Override
public Long delectFile(Long id) {
if (id==null) {
throw new ServiceException("删除id不容许为空");
}
FileDypeVo fileDypeVos = fileMapper.selectFileId(id);
String path = fileDypeVos.getFilePath();
String name = fileDypeVos.getFileName();
File file = new File(path+name);
LOGGER.info("file==>"+file+"|file.exists()==>"+file.exists());
if (!file.exists()) {
throw new ServiceException("未在指定路径发现该文件"+file);
}
boolean lFile=file.delete();
Long le=null;
if (lFile) {
le=1L;
}
// TODO Auto-generated method stub
return le;
}
大家根据自己的业务场景去设计相关接口需求；上面只供参考；有问题或者疑问请提出；
6.0 网上的（四种） Java下载文件的四种方式详细代码
1.以流的方式下载
public HttpServletResponse download(String path, HttpServletResponse response) {
try {
// path是指欲下载的文件的路径。
File file = new File(path);
// 取得文件名。
String filename = file.getName();
// 取得文件的后缀名。
String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/octet-stream");
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
}
return response;
}
2.下载本地文件
public void downloadLocal(HttpServletResponse response) throws FileNotFoundException {
// 下载本地文件
String fileName = "Operator.doc".toString(); // 文件的默认保存名
// 读到流中
InputStream inStream = new FileInputStream("c:/Operator.doc");// 文件的存放路径
// 设置输出的格式
response.reset();
response.setContentType("bin");
response.addHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
// 循环取出流中的数据
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();
}
}
3.支持在线打开文件/下载啊的一种方式
public class FileDownloadUtil {
/**
* 支持在线打开文件的一种方式
* @param filePath
* @param response
* @param isOnLine
* @throws Exception
*/
public void downLoad(String filePath, HttpServletResponse response, boolean isOnLine) throws Exception {
File f = new File(filePath);
if (!f.exists()) {
response.sendError(404, "File not found!");
return;
}
BufferedInputStream br = new BufferedInputStream(new FileInputStream(f));
byte[] buf = new byte[8192];
int len = 0;
response.reset(); // 非常重要
if (isOnLine) { // 在线打开方式
URL u = new URL("file:///" + filePath);
response.setContentType(u.openConnection().getContentType());
response.setHeader("Content-Disposition", "inline; filename=" + f.getName());
// 文件名应该编码成UTF-8
} else { // 纯下载方式
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename=" + f.getName());
}
OutputStream out = response.getOutputStream();
while ((len = br.read(buf)) > 0){
out.write(buf, 0, len);
}
br.close();
out.close();
}
}
4.0 网络url下载，并写入浏览器
public class FileDownloadUtil {
/**
* 网络url下载
* @param request
* @param response
*/
public void downloadFileURL(HttpServletRequest request, HttpServletResponse response) {
String fileUrl = "http://oss.aliyun.cn/pro/2021/06/30/2635a51b-3338-478c-9e46-0e7834afabbb.pdf";
try {
URL url = new URL(fileUrl);
URLConnection conn = url.openConnection();
InputStream inStream = conn.getInputStream();
//对文件名进行编码防止中文乱码
String filename = FileDownloadUtil.encodeDownloadFilename("文件.pdf", request);
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment; filename=" + filename);
OutputStream out = response.getOutputStream();
byte[] buffer = new byte[8192];
int length;
while ((length = inStream.read(buffer)) != -1) {
out.write(buffer, 0, length);
}
inStream.close();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public class FileDownloadUtil {
/**
* 下载文件时，针对不同浏览器，进行附件名的编码
*
* @param filename 载文件名
* @param request 请求request
* @return 编码后的下载附件名
* @throws IOException
*/
public static String encodeDownloadFilename(String filename, HttpServletRequest request)
throws IOException {
String agent = request.getHeader("user-agent");//获得游览器
if (agent.contains("Firefox")) { // 火狐浏览器
filename = "=?UTF-8?B?"
+ Base64.getEncoder().encode(filename.getBytes("utf-8"))
+ "?=";
filename = filename.replaceAll("\r\n", "");
} else { // IE及其他浏览器
filename = URLEncoder.encode(filename, "utf-8");
filename = filename.replace("+"," ");
}
return filename;
}
}
7.0 导出excel(xls)
7.1工具类
ExcelUtils
package com.ces.yun.framework.util.excel;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
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.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.SAXException;
import com.ces.yun.framework.base.yunConstants;
import com.ces.yun.framework.base.exception.ServiceException;
import com.ces.yun.framework.util.DateUtils;
/**
* 数据转excel和excel转数据
*
*/
public class ExcelUtils {
/**
* 日志对象
*/
private static final Logger LOGGER = Logger.getLogger(ExcelUtils.class);
/**
*
* 把java数据转成excel对象 调用方保证表头和数据的顺序对应，本方法不做检查
*
* @param datas
* 数据列表
* @param titles
* 表头 --单行
* @return excel对象
*/
public static HSSFWorkbook data2Excel(List<List<Object>> datas, List<String> titles, String sheetName) {
HSSFWorkbook book = new HSSFWorkbook();
HSSFCellStyle style = getCellStyle(book);
HSSFFont font = getFont(book);
// 把字体应用到样式
style.setFont(font);
// 创建sheet
HSSFSheet sheet = book.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
// sheet.setDefaultColumnWidth((short)15);
// 设置表头--单行
HSSFRow titleRow = sheet.createRow(0);
// 创建标题行
for (int i = 0; i < titles.size(); i++) {
HSSFCell cell = titleRow.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(titles.get(i));
}
// 写入数据
for (int i = 0; i < datas.size(); i++) {
// 创建数据行
HSSFRow dataRow = sheet.createRow(i + 1);
List<Object> rowDatas = datas.get(i);
if (CollectionUtils.isEmpty(rowDatas)) {
continue;
}
for (int j = 0; j < rowDatas.size(); j++) {
HSSFCell cell = dataRow.createCell(j);
setCellValue(cell, rowDatas.get(j));
}
}
return book;
}
public static HSSFWorkbook createWorkbook() {
HSSFWorkbook book = new HSSFWorkbook();
return book;
}
public static void addNewSheet(HSSFWorkbook book, List<List<Object>> datas, List<String> titles, String sheetName) {
HSSFCellStyle style = getCellStyle(book);
HSSFFont font = getFont(book);
// 把字体应用到样式
style.setFont(font);
// 创建sheet
HSSFSheet sheet = book.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
// sheet.setDefaultColumnWidth((short)15);
// 设置表头--单行
HSSFRow titleRow = sheet.createRow(0);
// 创建标题行
for (int i = 0; i < titles.size(); i++) {
HSSFCell cell = titleRow.createCell(i);
cell.setCellStyle(style);
cell.setCellValue(titles.get(i));
}
// 写入数据
for (int i = 0; i < datas.size(); i++) {
// 创建数据行
HSSFRow dataRow = sheet.createRow(i + 1);
List<Object> rowDatas = datas.get(i);
if (CollectionUtils.isEmpty(rowDatas)) {
continue;
}
for (int j = 0; j < rowDatas.size(); j++) {
HSSFCell cell = dataRow.createCell(j);
setCellValue(cell, rowDatas.get(j));
}
}
}
// 表格样式
private static HSSFCellStyle getCellStyle(HSSFWorkbook book) {
HSSFCellStyle style = book.createCellStyle();
// 设置样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
return style;
}
// 设置字体
private static HSSFFont getFont(HSSFWorkbook book) {
HSSFFont font = book.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
return font;
}
// 单元格填充数据
private static void setCellValue(HSSFCell cell, Object value) {
if (null == value) {
return;
}
if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if (value instanceof Date) {
cell.setCellValue(DateUtils.dateToString((Date) value));
} else if (value instanceof Calendar) {
cell.setCellValue((Calendar) value);
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
} else if (value instanceof String) {
if (((String) value).length() > 32767) {
value = ((String) value).substring(0, 32767);
}
cell.setCellValue((String) value);
} else {
cell.setCellValue(value.toString());
}
}
public static ExcelVO byte2BigExcel(byte[] bytes, int type) throws IOException, OpenXML4JException, SAXException {
ExcelVO excelVO = null;
if (yunConstants.EXCEL_TYPE_XLS == type) { // 处理excel2003文件
// ExcelXlsReader excelXls = new ExcelXlsReader();
// excelXls.process(fileName);
// 暂时2003用老的方式处理
return byte2Excel(bytes, type);
} else if (yunConstants.EXCEL_TYPE_XLSX == type) {// 处理excel2007文件
ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader();
excelVO = excelXlsxReader.process(bytes);
} else {
return null;
}
return excelVO;
}
/**
*
* 将字节流转换为excel对象
*
* @param bytes
* 内容
* @param type
* 文件类型
* @return excel对象
*/
public static ExcelVO byte2Excel(byte[] bytes, int type) {
Workbook wb = null;
InputStream is = new ByteArrayInputStream(bytes);
if (yunConstants.EXCEL_TYPE_XLS == type) {
try {
wb = new HSSFWorkbook(is);
} catch (IOException e) {
LOGGER.error(e.getMessage());
return null;
}
} else if (yunConstants.EXCEL_TYPE_XLSX == type) {
try {
wb = new XSSFWorkbook(is);
} catch (IOException e) {
LOGGER.error(e.getMessage());
return null;
}
} else {
return null;
}
ExcelVO excelVO = new ExcelVO();
int sheetCount = wb.getNumberOfSheets();
if (sheetCount == 0) {
return excelVO;
}
List<SheetVO> sheets = new ArrayList<SheetVO>();
excelVO.setSheets(sheets);
// 计算公式
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (int i = 0; i < sheetCount; i++) {
SheetVO sheetVO = loadSheet(wb.getSheetAt(i), evaluator);
if (null == sheetVO || CollectionUtils.isEmpty(sheetVO.getTitles())) {
continue;
}
sheets.add(sheetVO);
}
return excelVO;
}
private static SheetVO loadSheet(Sheet sheet, FormulaEvaluator evaluator) {
SheetVO sheetVO = new SheetVO();
sheetVO.setName(sheet.getSheetName());
// 总行数
int rownum = sheet.getPhysicalNumberOfRows();
if (rownum == 0) {
return null;
}
// 获取第一行
Row titleRow = sheet.getRow(0);
// 列数
int coloumNum = sheet.getRow(0).getPhysicalNumberOfCells();
// 表头
List<String> titles = new ArrayList<>(coloumNum);
for (int i = 0; i < coloumNum; i++) {
String titleStr = titleRow.getCell(i).toString().trim();
// 处理特殊字符，全部替换为下划线，避免SQL报错,只保留汉字，字母，数字
String patten = "[^\u4e00-\u9fa5a-zA-Z0-9]";
titleStr = titleStr.replaceAll(patten, "_");
if (StringUtils.isBlank(titleStr)) {
throw new ServiceException("表格的第一行必须有值");
}
// 处理表头过长
if (titleStr.length() > 40) {
titleStr = titleStr.substring(0, 40);
}
titles.add(titleStr);
}
sheetVO.setTitles(titles);
// 只有表头，没有数据
if (rownum == 1) {
return sheetVO;
}
// 装数据
List<RowVO> datas = new ArrayList<RowVO>();
for (int i = 1; i < rownum; i++) {
Row dataRow = sheet.getRow(i);
if (null == dataRow) {
continue;
}
RowVO rowVO = new RowVO();
datas.add(rowVO);
List<CellVO> cells = new ArrayList<CellVO>();
rowVO.setCells(cells);
for (int j = 0; j < coloumNum; j++) {
String cellStr = "";
Cell cell = dataRow.getCell(j);
if (null == cell) {
cells.add(null);
continue;
}
CellVO cellVO = new CellVO();
int type = cell.getCellType();
if (Cell.CELL_TYPE_FORMULA == type) {
CellValue tempCellValue = evaluator.evaluate(cell);
double iCellValue = tempCellValue.getNumberValue();
cellStr = iCellValue + "";
}
if (Cell.CELL_TYPE_NUMERIC == type) {
if (DateUtil.isCellDateFormatted(cell)) {
cellVO.setDataType(CellVO.DATA_TYPE_DATE);
Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
cellStr = DateUtils.dateToString(date);
} else {
cellVO.setDataType(CellVO.DATA_TYPE_NUMERIC);
// 处理数字精度和科学计数法
BigDecimal bd = new BigDecimal(cell.toString());
cellStr = bd.toPlainString();
}
} else {
cellVO.setDataType(CellVO.DATA_TYPE_STRING);
cellStr = cell.toString();
}
cellVO.setValue(cellStr);
cells.add(cellVO);
}
}
sheetVO.setDatas(datas);
return sheetVO;
}
/**
*
* 获取excel表头和本地数据表字段名称映射
*
* @param sheetVO
* sheet页
* @return 映射关系 key:原始名称 value:本地存储表字段名称和数据类型
*/
public static Map<String, Map<String, String>> getColMapping(SheetVO sheetVO) {
List<String> titles = sheetVO.getTitles();
List<RowVO> datas = sheetVO.getDatas();
RowVO row = null;
if (CollectionUtils.isNotEmpty(datas)) {
row = datas.get(0);
}
Map<String, Map<String, String>> map = new HashMap<String, Map<String, String>>();
for (int i = 0; i < titles.size(); i++) {
Map<String, String> temMap = new HashMap<>();
String localCol = "col_" + i;
temMap.put("name", localCol);
String type = yunConstants.DATA_TYPE_VARCHAR;
if (null != row) {
CellVO cell = row.getCells().get(i);
if (null != cell) {
int dataType = cell.getDataType();
if (CellVO.DATA_TYPE_NUMERIC == dataType) {
type = yunConstants.DATA_TYPE_NUMERIC;
}
if (CellVO.DATA_TYPE_DATE == dataType) {
type = yunConstants.DATA_TYPE_TIMESTAMP;
}
}
}
temMap.put("type", type);
map.put(titles.get(i), temMap);
}
return map;
}
// public static void main(String[] args) {
// File f = new File("e://安徽业绩考核综合指标2.xlsx");
// FileInputStream fs;
// try {
// fs = new FileInputStream(f);
// ExcelVO excel = byte2BigExcel(IOUtils.toByteArray(fs),
// yunConstants.EXCEL_TYPE_XLSX);
// Map<String,Map<String,String>> da =
// getColMapping(excel.getSheets().get(0));
// System.out.println();
// } catch (Exception e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
// }
}
ExcelVO
package com.ces.yun.framework.util.excel;
import java.util.List;
import com.alibaba.fastjson.JSONObject;
/**
* excel 实体对象
*/
public class ExcelVO {
/**
* sheet页
*/
private List<SheetVO> sheets;
public List<SheetVO> getSheets() {
return sheets;
}
public void setSheets(List<SheetVO> sheets) {
this.sheets = sheets;
}
public String toString(){
JSONObject jobj = (JSONObject)JSONObject.toJSON(this);
return jobj.toJSONString();
}
}
ExcelXlsReader
package com.ces.yun.framework.util.excel;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder;
import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.StringRecord;
import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* @author
* @create 2018-01-19 14:18
* @desc 用于解决.xls2003版本大数据量问题
**/
public class ExcelXlsReader implements HSSFListener {
private int minColums = -1;
private POIFSFileSystem fs;
/**
* 总行数
*/
private int totalRows = 0;
/**
* 上一行row的序号
*/
private int lastRowNumber;
/**
* 上一单元格的序号
*/
private int lastColumnNumber;
/**
* 是否输出formula，还是它对应的值
*/
private boolean outputFormulaValues = true;
/**
* 用于转换formulas
*/
private EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener;
// excel2003工作簿
private HSSFWorkbook stubWorkbook;
private SSTRecord sstRecord;
private FormatTrackingHSSFListener formatListener;
private final HSSFDataFormatter formatter = new HSSFDataFormatter();
/**
* 文件的绝对路径
*/
private String filePath = "";
// 表索引
private int sheetIndex = 0;
private BoundSheetRecord[] orderedBSRs;
@SuppressWarnings("unchecked")
private ArrayList boundSheetRecords = new ArrayList();
private int nextRow;
private int nextColumn;
private boolean outputNextStringRecord;
// 当前行
private int curRow = 0;
// 存储一行记录所有单元格的容器
private List<String> cellList = new ArrayList<String>();
/**
* 判断整行是否为空行的标记
*/
private boolean flag = false;
@SuppressWarnings("unused")
private String sheetName;
/**
* 遍历excel下所有的sheet
*
* @param fileName
* @throws Exception
*/
public int process(String fileName)
throws Exception {
filePath = fileName;
this.fs = new POIFSFileSystem(new FileInputStream(fileName));
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
formatListener = new FormatTrackingHSSFListener(listener);
HSSFEventFactory factory = new HSSFEventFactory();
HSSFRequest request = new HSSFRequest();
if (outputFormulaValues) {
request.addListenerForAllRecords(formatListener);
} else {
workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);
request.addListenerForAllRecords(workbookBuildingListener);
}
factory.processWorkbookEvents(request, fs);
return totalRows; // 返回该excel文件的总行数，不包括首列和空行
}
/**
* HSSFListener 监听方法，处理Record
* 处理每个单元格
* @param record
*/
@SuppressWarnings("unchecked")
public void processRecord(Record record) {
int thisRow = -1;
int thisColumn = -1;
String thisStr = null;
String value = null;
switch (record.getSid()) {
case BoundSheetRecord.sid:
boundSheetRecords.add(record);
break;
case BOFRecord.sid: // 开始处理每个sheet
BOFRecord br = (BOFRecord)record;
if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
// 如果有需要，则建立子工作簿
if (workbookBuildingListener != null && stubWorkbook == null) {
stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
}
if (orderedBSRs == null) {
orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
}
sheetName = orderedBSRs[sheetIndex].getSheetname();
sheetIndex++ ;
}
break;
case SSTRecord.sid:
sstRecord = (SSTRecord)record;
break;
case BlankRecord.sid: // 单元格为空白
BlankRecord brec = (BlankRecord)record;
thisRow = brec.getRow();
thisColumn = brec.getColumn();
thisStr = "";
cellList.add(thisColumn, thisStr);
break;
case BoolErrRecord.sid: // 单元格为布尔类型
BoolErrRecord berec = (BoolErrRecord)record;
thisRow = berec.getRow();
thisColumn = berec.getColumn();
thisStr = berec.getBooleanValue() + "";
cellList.add(thisColumn, thisStr);
checkRowIsNull(thisStr); // 如果里面某个单元格含有值，则标识该行不为空行
break;
case FormulaRecord.sid:// 单元格为公式类型
FormulaRecord frec = (FormulaRecord)record;
thisRow = frec.getRow();
thisColumn = frec.getColumn();
if (outputFormulaValues) {
if (Double.isNaN(frec.getValue())) {
outputNextStringRecord = true;
nextRow = frec.getRow();
nextColumn = frec.getColumn();
} else {
thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
}
} else {
thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
}
cellList.add(thisColumn, thisStr);
checkRowIsNull(thisStr); // 如果里面某个单元格含有值，则标识该行不为空行
break;
case StringRecord.sid: // 单元格中公式的字符串
if (outputNextStringRecord) {
StringRecord srec = (StringRecord)record;
thisStr = srec.getString();
thisRow = nextRow;
thisColumn = nextColumn;
outputNextStringRecord = false;
}
break;
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord)record;
curRow = thisRow = lrec.getRow();
thisColumn = lrec.getColumn();
value = lrec.getValue().trim();
value = value.equals("") ? "" : value;
cellList.add(thisColumn, value);
checkRowIsNull(value); // 如果里面某个单元格含有值，则标识该行不为空行
break;
case LabelSSTRecord.sid: // 单元格为字符串类型
LabelSSTRecord lsrec = (LabelSSTRecord)record;
curRow = thisRow = lsrec.getRow();
thisColumn = lsrec.getColumn();
if (sstRecord == null) {
cellList.add(thisColumn, "");
} else {
value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
value = value.equals("") ? "" : value;
cellList.add(thisColumn, value);
checkRowIsNull(value); // 如果里面某个单元格含有值，则标识该行不为空行
}
break;
case NumberRecord.sid: // 单元格为数字类型
NumberRecord numrec = (NumberRecord)record;
curRow = thisRow = numrec.getRow();
thisColumn = numrec.getColumn();
// 第一种方式
// value = formatListener.formatNumberDateCell(numrec).trim();//这个被写死，采用的m/d/yy h:mm格式，不符合要求
// 第二种方式，参照formatNumberDateCell里面的实现方法编写
Double valueDouble = ((NumberRecord)numrec).getValue();
String formatString = formatListener.getFormatString(numrec);
if (formatString.contains("m/d/yy")) {
formatString = "yyyy-MM-dd hh:mm:ss";
}
int formatIndex = formatListener.getFormatIndex(numrec);
value = formatter.formatRawCellContents(valueDouble, formatIndex, formatString).trim();
value = value.equals("") ? "" : value;
// 向容器加入列值
cellList.add(thisColumn, value);
checkRowIsNull(value); // 如果里面某个单元格含有值，则标识该行不为空行
break;
default:
break;
}
// 遇到新行的操作
if (thisRow != -1 && thisRow != lastRowNumber) {
lastColumnNumber = -1;
}
// 空值的操作
if (record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord mc = (MissingCellDummyRecord)record;
curRow = thisRow = mc.getRow();
thisColumn = mc.getColumn();
cellList.add(thisColumn, "");
}
// 更新行和列的值
if (thisRow > -1) lastRowNumber = thisRow;
if (thisColumn > -1) lastColumnNumber = thisColumn;
// 行结束时的操作
if (record instanceof LastCellOfRowDummyRecord) {
if (minColums > 0) {
// 列值重新置空
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
}
lastColumnNumber = -1;
if (flag && curRow != 0) { // 该行不为空行且该行不是第一行，发送（第一行为列名，不需要）
// ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow + 1, cellList); //每行结束时，调用sendRows()方法
totalRows++ ;
}
// 清空容器
cellList.clear();
flag = false;
}
}
/**
* 如果里面某个单元格含有值，则标识该行不为空行
* @param value
*/
public void checkRowIsNull(String value) {
if (value != null && !"".equals(value)) {
flag = true;
}
}
}
ExcelXlsxReader
package com.ces.yun.framework.util.excel;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import com.ces.yun.framework.base.exception.ServiceException;
/**
* 处理大的excel表
*/
public class ExcelXlsxReader extends DefaultHandler {
/**
* 单元格中的数据可能的数据类型
*/
enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}
/**
* 共享字符串表
*/
private SharedStringsTable sst;
/**
* 上一次的索引值
*/
private String lastIndex;
/**
* sheet名
*/
private String sheetName = "";
/**
* 一行内cell集合
*/
private List<CellVO> cellList = new ArrayList<>();
/**
* 判断整行是否为空行的标记
*/
private boolean flag = false;
/**
* 当前行
*/
private int curRow = 1;
/**
* 当前列
*/
private int curCol = 0;
/**
* T元素标识
*/
private boolean isTElement;
/**
* 单元格数据类型，默认为字符串类型
*/
private CellDataType nextDataType = CellDataType.SSTINDEX;
/**
* 日期格式化
*/
private final DataFormatter formatter = new DataFormatter();
/**
* 单元格日期格式的索引
*/
private short formatIndex;
/**
* 日期格式字符串
*/
private String formatString;
/**
* 定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
*/
private String preRef = null;
/**
* 当前元素位置
*/
private String ref = null;
/**
* 定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
*/
private String maxRef = null;
/**
* 单元格
*/
private StylesTable stylesTable;
/**
* 当前sheet对象
*/
private SheetVO curSheetVO;
/**
* 当前字段的数据类型
*/
private int curDataType;
/**
* 遍历工作簿中所有的电子表格
* 并缓存在mySheetList中
*
* @param bytes excel字节流
* @throws IOException 异常
* @throws OpenXML4JException 异常
* @throws SAXException 异常
* @return excel对象
*/
public ExcelVO process(byte[] bytes)
throws IOException, OpenXML4JException, SAXException {
InputStream inputStream = new ByteArrayInputStream(bytes);
OPCPackage pkg = OPCPackage.open(inputStream);
XSSFReader xssfReader = new XSSFReader(pkg);
stylesTable = xssfReader.getStylesTable();
SharedStringsTable sst = xssfReader.getSharedStringsTable();
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
parser.setFeature("http://apache.org/xml/features/disallow-doctype-decl", true);
parser.setFeature("http://xml.org/sax/features/external-general-entities", false);
parser.setFeature("http://xml.org/sax/features/external-parameter-entities", false);
this.sst = sst;
parser.setContentHandler(this);
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)xssfReader.getSheetsData();
ExcelVO excelVO = new ExcelVO();
List<SheetVO> sheetList = new ArrayList<SheetVO>();
excelVO.setSheets(sheetList);
while (sheets.hasNext()) { // 遍历sheet
curSheetVO = new SheetVO();
List<RowVO> datas = new ArrayList<>();
curSheetVO.setDatas(datas);
curRow = 1; // 标记初始行为第一行
InputStream sheet = sheets.next(); // sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
sheetName = sheets.getSheetName();
curSheetVO.setName(sheetName);
sheetList.add(curSheetVO);
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource); // 解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
sheet.close();
}
return excelVO; // 返回该excel文件的总行数，不包括首列和空行
}
/**
* 第一个执行
*
*/
@Override
public void startElement(String uri, String localName, String name, Attributes attributes)
throws SAXException {
// c => 单元格
if ("c".equals(name)) {
//开关位置不对，则证明转换后的xml只有c为没有v <c r="XX" s="XX"></c> 这里需要自己补齐v的数据
//由于计算实际V比较困难，这里采用有C就先占位，然后有V就替换列表最后一个元素的方式来处理
CellVO cellVO = new CellVO();
cellVO.setValue("");
cellVO.setDataType(CellVO.DATA_TYPE_STRING);
cellList.add(curCol, cellVO);
curCol++ ;
// 前一个单元格的位置
if (preRef == null) {
preRef = attributes.getValue("r");
} else {
preRef = ref;
}
// 当前单元格的位置
ref = attributes.getValue("r");
//检查是否有空单元格需要补齐
int blankCount = countNullCell(ref, preRef);
if(blankCount > 0){
for (int i = 0; i < blankCount; i++ ) {
CellVO blankVO = new CellVO();
blankVO.setValue("");
blankVO.setDataType(CellVO.DATA_TYPE_STRING);
cellList.add(curCol, cellVO);
curCol++ ;
}
}
// 设定单元格类型
this.setNextDataType(attributes);
}
// 当元素为t时
if ("t".equals(name)) {
isTElement = true;
} else {
isTElement = false;
}
// 置空
lastIndex = "";
}
/**
* 第二个执行
* 得到单元格对应的索引值或是内容值
* 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
* 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值
*/
@Override
public void characters(char[] ch, int start, int length)
throws SAXException {
lastIndex += new String(ch, start, length);
}
/**
* 第三个执行
*
*/
@Override
public void endElement(String uri, String localName, String name)
throws SAXException {
// t元素也包含字符串
if (isTElement) {// 这个程序没经过
// 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
String value = lastIndex.trim();
CellVO cellVO = new CellVO();
cellVO.setValue(value);
cellVO.setDataType(curDataType);
cellList.set(curCol-1, cellVO);
isTElement = false;
// 如果里面某个单元格含有值，则标识该行不为空行
if (value != null && !"".equals(value)) {
flag = true;
}
} else if ("v".equals(name)) {
// v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
String value = this.getDataValue(lastIndex.trim(), "");// 根据索引值获取对应的单元格值
if(null != value){
value = value.trim();
}
CellVO cellVO = new CellVO();
cellVO.setValue(value);
cellVO.setDataType(curDataType);
cellList.set(curCol-1, cellVO);
// 如果里面某个单元格含有值，则标识该行不为空行
if (value != null && !"".equals(value)) {
flag = true;
}
} else {
// 如果标签名称为row，这说明已到行尾，调用optRows()方法
if ("row".equals(name)) {
// 默认第一行为表头，以该行单元格数目为最大数目
if (curRow == 1) {
maxRef = ref;
// 表头
List<String> titles = new ArrayList<>();
for (CellVO cell : cellList) {
String titleStr = cell.getValue().trim();
// 处理特殊字符，全部替换为下划线，避免SQL报错,只保留汉字，字母，数字
String patten = "[^\u4e00-\u9fa5a-zA-Z0-9]";
titleStr = titleStr.replaceAll(patten, "_");
if (StringUtils.isBlank(titleStr)) {
throw new ServiceException("表格的第一行必须有值");
}
// 处理表头过长
if (titleStr.length() > 40) {
titleStr = titleStr.substring(0, 40);
}
titles.add(titleStr);
}
curSheetVO.setTitles(titles);
}
// 补全一行尾部可能缺失的单元格
if (maxRef != null) {
//检查是否有空单元格需要补齐
int blankCount = countNullCell(maxRef, ref);
if(blankCount >= 0){
for (int i = 0; i <= blankCount; i++ ) {
CellVO blankVO = new CellVO();
blankVO.setValue("");
blankVO.setDataType(CellVO.DATA_TYPE_STRING);
cellList.add(curCol, blankVO);
curCol++ ;
}
}
}
if (flag && curRow != 1) { // 该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）
List<RowVO> datas = curSheetVO.getDatas();
RowVO rowVO = new RowVO();
datas.add(rowVO);
List<CellVO> cells = new ArrayList<>();
rowVO.setCells(cells);
for (CellVO cell : cellList) {
CellVO cellVO = new CellVO();
String value = cell.getValue();
if(null != value){
value = value.trim();
}
cellVO.setValue(value);
cellVO.setDataType(cell.getDataType());
cells.add(cellVO);
}
}
cellList.clear();
curRow++ ;
curCol = 0;
preRef = null;
ref = null;
flag = false;
}
}
}
/**
* 处理数据类型
*
* @param attributes 属性
*/
public void setNextDataType(Attributes attributes) {
nextDataType = CellDataType.NUMBER; // cellType为空，则表示该单元格类型为数字
formatIndex = -1;
formatString = null;
String cellType = attributes.getValue("t"); // 单元格类型
String cellStyleStr = attributes.getValue("s"); //
if ("b".equals(cellType)) { // 处理布尔值
nextDataType = CellDataType.BOOL;
} else if ("e".equals(cellType)) { // 处理错误
nextDataType = CellDataType.ERROR;
} else if ("inlineStr".equals(cellType)) {
nextDataType = CellDataType.INLINESTR;
} else if ("s".equals(cellType)) { // 处理字符串
nextDataType = CellDataType.SSTINDEX;
} else if ("str".equals(cellType)) {
nextDataType = CellDataType.FORMULA;
}
if (cellStyleStr != null) { // 处理日期
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
formatIndex = style.getDataFormat();
formatString = style.getDataFormatString();
if (formatString.contains("m/d/yy")) {
nextDataType = CellDataType.DATE;
formatString = "yyyy-MM-dd hh:mm:ss";
}
if (formatString == null) {
nextDataType = CellDataType.NULL;
formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
}
}
}
/**
* 对解析出来的数据进行类型处理
* @param value 单元格的值，
* value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
* SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
* @param thisStr 一个空字符串
* @return 单元格的值
*/
public String getDataValue(String value, String thisStr) {
curDataType = CellVO.DATA_TYPE_STRING;
switch (nextDataType) {
// 这几个的顺序不能随便交换，交换了很可能会导致数据错误
case BOOL: // 布尔值
char first = value.charAt(0);
thisStr = first == '0' ? "FALSE" : "TRUE";
break;
case ERROR: // 错误
thisStr = "\"ERROR:" + value.toString() + '"';
break;
case FORMULA: // 公式
thisStr = '"' + value.toString() + '"';
break;
case INLINESTR:
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
thisStr = rtsi.toString();
rtsi = null;
break;
case SSTINDEX: // 字符串
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));// 根据idx索引值获取内容值
thisStr = rtss.toString();
rtss = null;
} catch (NumberFormatException ex) {
thisStr = value.toString();
}
break;
case NUMBER: // 数字
curDataType = CellVO.DATA_TYPE_NUMERIC;
if (formatString != null) {
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
} else {
thisStr = value;
}
thisStr = thisStr.replace("_", "").trim();
break;
case DATE: // 日期
curDataType = CellVO.DATA_TYPE_DATE;
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
// 对日期字符串作特殊处理，去掉T
thisStr = thisStr.replace("T", " ");
break;
default:
thisStr = " ";
break;
}
return thisStr;
}
private int countNullCell(String ref, String preRef) {
// excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
String xfd = ref.replaceAll("\\d+", "");
String preXfd = preRef.replaceAll("\\d+", "");
xfd = fillChar(xfd, 3, '@', true);
preXfd = fillChar(preXfd, 3, '@', true);
char[] letter = xfd.toCharArray();
char[] preLetter = preXfd.toCharArray();
int res = (letter[0] - preLetter[0]) * 26 * 26 + (letter[1] - preLetter[1]) * 26 + (letter[2] - preLetter[2]);
return res - 1;
}
private String fillChar(String str, int len, char let, boolean isPre) {
int strLen = str.length();
if (strLen < len) {
if (isPre) {
for (int i = 0; i < (len - strLen); i++ ) {
str = let + str;
}
} else {
for (int i = 0; i < (len - strLen); i++ ) {
str = str + let;
}
}
}
return str;
}
}
SheetVO
package com.ces.yun.framework.util.excel;
import java.util.List;
/**
* excel的sheet实体对象
*
* @version 2019年5月17日
*/
public class SheetVO {
/**
* 名称
*/
private String name;
/**
* 表头列表--暂时只支持单表头，不支持跨行--约定为第一行
*/
private List<String> titles;
/**
* 数据内容--不含表头
*/
private List<RowVO> datas;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<String> getTitles() {
return titles;
}
public void setTitles(List<String> titles) {
this.titles = titles;
}
public List<RowVO> getDatas() {
return datas;
}
public void setDatas(List<RowVO> datas) {
this.datas = datas;
}
}
RowVO
package com.ces.yun.framework.util.excel;
import java.util.List;
/**
* excel行对象
*
* @version 2019年5月17日
*/
public class RowVO {
/**
* 单元格数据
*/
private List<CellVO> cells;
public List<CellVO> getCells() {
return cells;
}
public void setCells(List<CellVO> cells) {
this.cells = cells;
}
}
CellVO
/*
* 文件名：CellVO.java
* 版权：Copyright by cesit
* 描述：单元格对象
*/
package com.ces.yun.framework.util.excel;
/**
* 单元格实体
*
* @version 2019年5月17日
*/
public class CellVO {
/**
* 数字类型
*/
public static final int DATA_TYPE_NUMERIC = 1;
/**
* 字符串类型
*/
public static final int DATA_TYPE_STRING = 2;
/**
* 日期类型
*/
public static final int DATA_TYPE_DATE = 3;
/**
* 单元格的值
*/
private String value;
/**
* 值的类型
*/
private int dataType;
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public int getDataType() {
return dataType;
}
public void setDataType(int dataType) {
this.dataType = dataType;
}
}
控制台
@Override
public MsgContext exportSheetDatas(HttpServletResponse response,Long sheetId, int currentPageNo, int pageSize) {
BizIdxSheets sheetPO = idxSheetsMapper.selectByPrimaryKey(Sutil.checkData(StringUtils.EMPTY, sheetId));
if (null == sheetPO) {
throw new ServiceException("未找到该指标图层信息！");
}
IdxSqlHander idxSqlHander = SpringUtil.getBean("idxSqlHander", IdxSqlHander.class);
Map<String, Object> allMap=idxSqlHander.init(sheetId).queryData(currentPageNo, pageSize);
List<Map> list = (List<Map>) allMap.get("titles");
for (Map map : list) {
map.get("label");
LOGGER.info("===map=>"+map.get("label"));
}
// List<Map> listss1 = (List<Map>) allMap.get("datas");
//
// LOGGER.info("===listss=>"+listss1);
//
// listss1.forEach(li->{
// li.values().forEach(v->{
// LOGGER.info("=88=>"+v);
// });
// li.keySet().forEach(k->{
// LOGGER.info("=99=>"+k);
// });
// });
// ArrayList<Map<String, Object>> rows = (ArrayList<Map<String, Object>>)allMap.get("datas");
// List<Map<String, Object>> compDatas = (List<Map<String, Object>>)allMap.get("datas");
List<Map<String, String>> compTitles = (List<Map<String, String>>)MapUtils.getObject(allMap, "titles");
List<Map<String, Object>> compDatas = (List<Map<String, Object>>)MapUtils.getObject(allMap, "datas");
LOGGER.info("===compDatas=2>"+compDatas);
LOGGER.info("===compTitles=2>"+compTitles);
List<String> titles = new ArrayList<String>();
for(Map<String, String> map : compTitles){
titles.add(MapUtils.getString(map, "label"));
}
LOGGER.info("===titles=2>"+titles);
List<List<Object>> exportDatas = new ArrayList<List<Object>>(compDatas.size());
LOGGER.info("===exportDatas.size()==》"+exportDatas.size());
for (int i = 0; i < compDatas.size(); i++ ) {
List<Object> rowData = new ArrayList<Object>();
Map<String, Object> compData = compDatas.get(i);
for(String title : titles){
rowData.add(MapUtils.getString(compData, title, ""));
}
exportDatas.add(rowData);
}
LOGGER.info("===exportDatas=2>"+exportDatas);
LOGGER.info("===json=2>"+JSON.toJSONString(allMap.get("titles")));
// JSONArray ac=JSON.parseArray(JSON.toJSONString(allMap.get("titles")));
//
// LOGGER.info("===ac=2>"+ac);
//
//
//
// for (int i = 0; i < ac.size(); i++) {
// Map<String, Object> map = (Map<String, Object>) ac.get(i);
// Iterator iterator = map.keySet().iterator();
// while (iterator.hasNext()) {
// String string = (String) iterator.next();
// LOGGER.info("===map.get(string)>"+map.get(string));
// }
//
// }
//
// JSONArray ab=JSON.parseArray(JSON.toJSONString(allMap.get("datas")));
//
//
// for (int i = 0; i < ab.size(); i++) {
// Map<String, Object> map = (Map<String, Object>) ac.get(i);
// Iterator iterator = map.keySet().iterator();
// while (iterator.hasNext()) {
// String string = (String) iterator.next();
// LOGGER.info("===map.get(string)>"+map.get(string));
// }
//
// }
//
HSSFWorkbook book= ExcelUtils.data2Excel(exportDatas, titles, "sheet");
// String sceName = MapUtils.getString(allMap, "sceName");
String sceName = "ExportXls";
OutputStream os = null;
try {
os = response.getOutputStream();
response.reset();// 清空输出流
response.setContentType("application/x-msdownload");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
response.setHeader("Content-disposition", "attachment; filename="+URLEncoder.encode(sceName, "UTF-8")+".xls");// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
book.write(os);
LOGGER.info("导出完成关闭流");
} catch (IOException e) {
return MsgContext.createErrorContext("导出失败。");
} finally {
IOUtils.closeQuietly(os);
}
return null;
}
allMap 数据类型为
allMap==>{sortColName=, datas=[{number=100, name=电工, deptID=200, ID=1}, {number=150, name=水工, deptID=300, ID=2}, {number=200, name=领导, deptID=400, ID=3}, {number=5000, name=董事长, deptID=250, ID=4}], titles=[{sign=, label=number, type=1}, {sign=, label=name, type=1}, {sign=, label=deptID, type=2}, {sign=, label=ID, type=2}], totalCount=4}
欢迎加入北京社区
更多推荐
1
0- 0
已为社区贡献129条内容
所有评论(0)