java poi给Excel设置密码 java导出excel加密

文章目录

• 1.前言
• 2.准备工作说明
• 3.导出功能所依赖的jar包
• 4.撸代码异常状态码类(ErrorCode)
• 5.撸代码异常类(DCPException类)
• 6.返回前端提示类(ReturnValue)
• 7.撸代码控制器层（Controller类）
• 8.文件资源存放地址类（FileResource）
• 9.撸代码逻辑层（TestExport）
• 10.前端收到成功后拿到返回值文件名称
• 11.文件下载控制器(DownloadController)
• 12.文件下载
• 13.结尾了...

1.前言

在写系统软件里, 系统要导出数据是一个很频繁，也很普遍的需求.所以我特此记录下，前几天我写的导出，方便以后用到，或者能帮助到有需要的人。 感谢.

我这里只能用于少量数据导出（大概五万条,最多十万，具体没去测试…)，大数据到的话 可能造成内存 “爆炸…”, 你要大数据导出也可以，我的想法是牺牲性能，分页导出。循环调用分页查询接口然后再将查询出的数据导出…,具体请根据自己需求。

前段时间公司忙,一直有开发任务在身上，所以没写博客，最近有时间写个博客好好沉淀一下 哈哈.也方便日后拿起就用…

2.准备工作说明

我这里虽然是springboot，maven.但mapper.xml, mapper, service, serviceImpl我就不写了，只写关键部分.

思路说明：
1.连接数据库
2.点击查询
3.将查询出的数据调用生成excel各列
4.生成文件
5.返回给前端文件名称
6.前端收到请求成功后拿到文件名称
7.根据文件名称去目录下下载

3.导出功能所依赖的jar包

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.15</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>

4.撸代码异常状态码类(ErrorCode)

package com.test.error;

public enum ErrorCode {
	ERROR_SUCCESS(0, "成功"),						
	ERROR_SERVER_ERROR(-500, "服务内部错误"),		
	ERROR_BUSY(-429, "系统繁忙，请稍后重试"),
	ERROR_NOT_FOUND(-1, "未找到或不存在"),								
	ERROR_HANDLE_TIMEOUT(-2, "超时"),	
	ERROR_INVALID_PARAM(-3, "无效参数"),			
	ERROR_CHECK_CODE(-4, "验证码错误或已失效"),
	ERROR_MEMORY_OUT(-5, "内存溢出"),		
	ERROR_OBJECT_EXIST(-6, "对象已存在"),
	ERROR_USER_PASSWORD(-7, "用户名或密码不正确"),
	ERROR_DELETE_FAIL(-8, "删除对象失败"),
	ERROR_INVALID_ARRAY(-9, "无效列表"),
	ERROR_EXIST_TIMESPAN(-10, "该时间段包含已有时间"),
	ERROR_NOT_LOGIN(-10, "未登录或会话过期"),
	ERROR_NOT_SUPPORT(-11, "接口不支持"),
	ERROR_NO_RIGHT(-12, "权限不足"),
	ERROR_NOT_AVAILABLE(-13, "服务暂不可用"),
	ERROR_USER_HAS_LOGIN(-14, "用户已登录"),
	ERROR_WRITE_FAILED(-41, "写文件失败"),
	ERROR_READ_FAILED(-42, "读文件失败"),
	ERROR_UPLOAD_FAILED(-43, "上传文件失败"),
	ERROR_UNKNOW(-50, "未知错误"),
	ERROR_REGION_LIMIT(-51, "区域已经达到最大个数")
	;		
	
	private Integer error;	
	private String description;	
	
	private ErrorCode(Integer error, String description){
		this.error = error;
		this.description = description;
	}
	
	public Integer getError(){
		return this.error;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	public void setError(Integer error) {
		this.error = error;
	}
	
	static public String getDescription(Integer error) {
		String desc = "";
		if(ERROR_SUCCESS.getError() == error){
			desc = ERROR_SUCCESS.getDescription();
		}
		if(ERROR_SERVER_ERROR.getError() == error){
			desc = ERROR_SERVER_ERROR.getDescription();
		}
		if(ERROR_BUSY.getError() == error){
			desc = ERROR_BUSY.getDescription();
		}
		if(ERROR_NOT_FOUND.getError() == error){
			desc = ERROR_NOT_FOUND.getDescription();
		}
		if(ERROR_HANDLE_TIMEOUT.getError() == error){
			desc = ERROR_HANDLE_TIMEOUT.getDescription();
		}
		if(ERROR_INVALID_PARAM.getError() == error){
			desc = ERROR_INVALID_PARAM.getDescription();
		}								
		if(ERROR_CHECK_CODE.getError() == error){
			desc = ERROR_CHECK_CODE.getDescription();
		}								
		if(ERROR_MEMORY_OUT.getError() == error){
			desc = ERROR_MEMORY_OUT.getDescription();
		}								
		if(ERROR_OBJECT_EXIST.getError() == error){
			desc = ERROR_OBJECT_EXIST.getDescription();
		}
		if(ERROR_USER_PASSWORD.getError() == error){
			desc = ERROR_USER_PASSWORD.getDescription();
		}
		if(ERROR_DELETE_FAIL.getError() == error){
			desc = ERROR_DELETE_FAIL.getDescription();
		}
		if(ERROR_INVALID_ARRAY.getError() == error){
			desc = ERROR_INVALID_ARRAY.getDescription();
		}
		if(ERROR_EXIST_TIMESPAN.getError() == error){
			desc = ERROR_EXIST_TIMESPAN.getDescription();
		}
		if(ERROR_NOT_LOGIN.getError() == error){
			desc = ERROR_NOT_LOGIN.getDescription();
		}
		if(ERROR_NOT_SUPPORT.getError() == error){
			desc = ERROR_NOT_SUPPORT.getDescription();
		}
		if(ERROR_NO_RIGHT.getError() == error){
			desc = ERROR_NO_RIGHT.getDescription();
		}
		if(ERROR_NOT_AVAILABLE.getError() == error){
			desc = ERROR_NOT_AVAILABLE.getDescription();
		}
		return desc;
	}
}

5.撸代码异常类(DCPException类)

package com.test.exception;

import com.dondown.error.ErrorCode;

import lombok.Data;


@Data
public class DCPException extends Exception{

	private static final long serialVersionUID = 1L;
	private	ErrorCode errorCode;
	
	public DCPException(ErrorCode code,String  msg){
		super(msg);
		errorCode = code;
	}
}

6.返回前端提示类(ReturnValue)

package com.test.error;

import java.io.IOException;

import com.fasterxml.jackson.annotation.JsonInclude;
import com.fasterxml.jackson.annotation.JsonInclude.Include;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

@JsonInclude(Include.NON_NULL) 
public class ReturnValue<T> {
	private Integer error = 0;				// 错误
	private String description = "";		// 错误描述
	private T value;						// 返回值【当error为ERROR_NO_SUCCESS才有可能返回值-判断值是否为空】
	
	
	// 成功不带返回值
	public ReturnValue(){
		this.error = ErrorCode.ERROR_SUCCESS.getError();
		this.description = "成功";
	}
	
	// 成功带返回值
	public ReturnValue(T value){
		if(null == value){
			this.error = ErrorCode.ERROR_NOT_FOUND.getError();
			this.description = "没有找到你需要的资源";
		} else {
			this.error = ErrorCode.ERROR_SUCCESS.getError();
			this.description = "成功";
			this.value = value;
		}		
	}
	
	// 返回错误
	public ReturnValue(ErrorCode error){
		this.error = error.getError();
		this.description = error.getDescription();
	}
	
	// 返回错误--对错误描述进行更改
	public ReturnValue(ErrorCode error, String description){
		this.error = error.getError();
		this.description = description;
	}
	
	// 返回错误
	public ReturnValue(Integer error){
		this.error = error;
		this.description = ErrorCode.getDescription(error);
	}
	
	public ReturnValue(Integer error, String description){
		this.error = error;
		this.description = description;
	}
	
	public Integer getError() {
		return error;
	}
	
	public boolean success(){
		return error == ErrorCode.ERROR_SUCCESS.getError();
	}

	public void setError(Integer error) {
		this.error = error;
	}

	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}

	public T getValue() {
		return value;
	}

	public void setValue(T value) {
		this.value = value;
	}
	
	/**
	 * 将字符串转为json对象
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public void fromJsonString(String json){
		try {
			ObjectMapper mapper = new ObjectMapper();
			ReturnValue<T> value = mapper.readValue(json, this.getClass());
			this.setError(value.getError());
			this.setDescription(value.getDescription());
			this.setValue(value.getValue());
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 将本对象转为json字符串
	 * @return
	 */
	public String toJsonString(){
		String json = "";
		try {
			ObjectMapper mapper = new ObjectMapper();
			json = mapper.writeValueAsString(this);
		} catch (JsonProcessingException e) {
			e.printStackTrace();
		}
		return json;
	}
}

7.撸代码控制器层（Controller类）

注意看代码注释

@Slf4j
@RequestMapping("/test")
public class testExportController {	
	@Autowired
	private TestService testService;
	@PostMapping("/testDataExport ")
	public ReturnValue<String> testDataExport (
				@RequestParam(name = "excelName", required = true) String excelName) {
		try {
				// CheckUtil类我就不贴出来了，就是检测对象是否为空
				// 我这里假设已经有service并且已经查询成功有数据
				List<Test> rows = testService.findByAttributes(exceName); // 这只是一句假设语句
				if (CheckUtil.isNull(rows)) {
					return new ReturnValue<>(ErrorCode.ERROR_NOT_FOUND, "无数据导出");
				}
				String fileName = TestExport.export(rows, excelName);
				return new ReturnValue<>(fileName);
			} catch (Exception e) {
				log.error(e.getMessage());
				return new ReturnValue<>(ErrorCode.ERROR_SERVER_ERROR);
			}
		}
	}

8.文件资源存放地址类（FileResource）

package com.test.export;
import java.io.File;
import java.io.FileNotFoundException;

import org.springframework.util.ResourceUtils;

public class FileResource {

	public static String xls= "";
	
	static{
		try {
			
			File path = new File(ResourceUtils.getURL("classpath:").getPath());
			// 如果通过jar允许，则使用当前jar包所在路径
			if (!path.exists())
				path = new File("");
			
			path = new File(path.getAbsolutePath(), "static"+File.separator+"xls");
			if (!path.exists())
				path.mkdirs();

			xls = path.getAbsolutePath();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

9.撸代码逻辑层（TestExport）

public class TestExport {
	public static String export(List<Object> rows, String excelName) throws DCPException {
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("poi导出");
		sheet.setDefaultRowHeight((short) (20 * 20));
		HSSFRow row = null;
		row = sheet.createRow(0);

		// 假设你的查询数据里有表头这些字段
		// 表头
		row.createCell(0).setCellValue("企业名称");
		row.createCell(1).setCellValue("联系电话");
		row.createCell(2).setCellValue("用电量");
		row.createCell(3).setCellValue("镇街");
		row.createCell(4).setCellValue("地址");
		row.createCell(5).setCellValue("用电计费起始日期");
		row.createCell(6).setCellValue("用电计费终止日期");

		JSONArray array = JSONArray.parseArray(rows);

		// 数据
		for (int i = 0; i < array.size(); i++) {
			JSONObject jsonObj = array.getJSONObject(i);
			row = sheet.createRow(i + 1);

			row.createCell(0).setCellValue(jsonObj.getString("企业名称"));
			row.createCell(1).setCellValue(jsonObj.getString("联系电话"));
			row.createCell(2).setCellValue(jsonObj.getString("用电量"));
			row.createCell(3).setCellValue(jsonObj.getString("镇街"));
			row.createCell(4).setCellValue(jsonObj.getString("地址"));
			row.createCell(5).setCellValue(jsonObj.getString("用电计费起始日期"));
			row.createCell(6).setCellValue(jsonObj.getString("用电计费终止日期"));
		}

		sheet.setColumnWidth(0, 450 * 20);
		sheet.setColumnWidth(1, 300 * 20);
		sheet.setColumnWidth(2, 150 * 20);
		sheet.setColumnWidth(3, 150 * 20);
		sheet.setColumnWidth(4, 150 * 20);
		sheet.setColumnWidth(5, 150 * 20);
		sheet.setColumnWidth(6, 150 * 20);
		
		String fileName = createFile(wb, excelName);
		return fileName;
	}
	
	private String createFile (HSSFWorkbook wb, String name) throws DCPException {
		String filePath = FileResource.xls;
	    // 生成文件
		DateFormat fm = new SimpleDateFormat("yyyMMddhhmmss");
	      String fileName = "自定义查询-"+fm.format(new Date())+".xls";
	      FileOutputStream fos=null;
			try {
				File file = new File(filePath+File.separator+fileName);
				if(!file.exists()) {
					file.createNewFile();
				}
				// 保存此XSSFWorkbook对象为xlsx文件
				workbook.write(new FileOutputStream(file));

				POIFSFileSystem fs = new POIFSFileSystem();
				EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);
				Encryptor enc = info.getEncryptor();
				// 设置密码
				enc.confirmPassword("123456");
				// 加密文件
				OPCPackage opc = OPCPackage.open(file, PackageAccess.READ_WRITE);
				java.io.OutputStream os = enc.getDataStream(fs);
				opc.save(os);
				opc.close();

				//把加密后的文件写回到流
				fos = new FileOutputStream(file);
				fs.writeFilesystem(fos);
				
				fos.flush();
			} catch (IOException e) {
				e.printStackTrace();
				throw new DCPException(ErrorCode.ERROR_SERVER_ERROR,"导出失败");
			} catch (InvalidFormatException e) {
				e.printStackTrace();
			} catch (GeneralSecurityException e) {
				e.printStackTrace();
			} finally {
				 try {
					fos.close();
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
					throw new DCPException(ErrorCode.ERROR_SERVER_ERROR,"导出失败");
				}
			}

	      return fileName;
	   }
}

10.前端收到成功后拿到返回值文件名称

我这里只写前端部分代码，理解意思就好.

this.$div.find('.export-btn').on('click', function () {
    let url = `http://127.0.0.1:8080/test/testExport`;
    window.ajaxAction.post(url, null, function (data) {
      window.open("http://127.0.0.1:8080/test/download/"+ data.value);
      alert('导出成功');
    }, function (data) {
      alert('导出失败'+data.description);
    });
  });

11.文件下载控制器(DownloadController)

@Slf4j
@RestController
@RequestMapping("/test")
public class TestDownload {
@GetMapping("/download/{fileName}")
    public void download(HttpServletRequest request,HttpServletResponse response, 
    @PathVariable(name="fileName", required=true) String fileName) {
 		   FileDownload.download(request,response,FileResource.xls, fileName);
    }
 }

12.文件下载

package com.test.export;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.util.ResourceUtils;

public class FileDownload {

	/*
	 *   下载文件，下载之后删除文件
	 */
	static public void download(HttpServletRequest request, HttpServletResponse response,String filePath,String fileName){
		try {
			  // 解决文件名中文乱码问题
			  String userAgent = request.getHeader("User-Agent");
			  String formFileName = fileName;
			  if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
	               formFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
	           } else {
	               // 非IE浏览器的处理：
	               formFileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
	           }
				
			   response.setContentType("multipart/form-data");
			   response.setHeader("Content-Disposition", "attachment;filename="+formFileName);
			   
			   File file = ResourceUtils.getFile(filePath+File.separator+fileName);
			   OutputStream out = response.getOutputStream();
			   InputStream in = new FileInputStream(file);
			   int b;
			   while((b=in.read())!=-1){
			       out.write(b);
			   }
			   in.close();      
			   out.close();  
			   file.delete();
			} catch (IOException e) {
				e.printStackTrace();
		}
	}
}

13.结尾了…

好久没写博客了，结尾都不知道写啥，哎 反正最近发际线是有点后退了。 另外，祝各位兄弟姐妹们牛逼，为什么牛逼？，为什么都牛逼！ 点个赞啊。