时间:2019-11-13  来源:简书  作者:听他忽悠  阅读:3

使用poi自带加密始终有问题,采用了jxcell方式加密

该方式只支持excel2003版本(后缀为.xls)

不支持excel2007版本(后缀为.xlsx),导出excel2007版本加密暂时未找到解决方案

jar包下载:

链接:https://pan.baidu.com/s/1112MNxRpw7JyoWsaJIFdzQ

提取码:b3kj

1.导入jar

com

jxcell

1.0

system

${basedir}/src/lib/jxcell.jar

jar包位置.png

2.ExcelUtil.java

package cn.indusec.sd.api.utils;

import cn.indusec.sd.annotation.ExcelSort;

import cn.indusec.sd.annotation.ExcelSortField;

import com.jxcell.View;

import org.apache.poi.hssf.usermodel.*;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.lang.reflect.Field;

import java.net.URLEncoder;

import java.text.SimpleDateFormat;

import java.util.*;

/**

* @author tong

* @date 2019/3/27

* @desc 导入导出Excel

*/

public class ExcelUtil {

public void exportExcel(String sheetName, String[] columnNames, Collection dataset, Class clazz, String sheetPwd, HttpServletResponse response) {

Workbook workbook = exportExcel2003(sheetName, columnNames, dataset, clazz, sheetPwd, "yyyy-MM-dd HH:mm:ss");

ByteArrayOutputStream baos = null;

ByteArrayOutputStream baos2 = null;

OutputStream out = null;

try {

baos = new ByteArrayOutputStream();

workbook.write(baos);// 临时存储流到内存

baos.flush();

ByteArrayInputStream workbookInput = new ByteArrayInputStream(baos.toByteArray());

baos2 = new ByteArrayOutputStream();

View view = new View();

view.read(workbookInput);

view.write(baos2, sheetPwd);

setResponseHeader(response, "log");

out = response.getOutputStream();

out.write(baos2.toByteArray());

out.flush();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

out.close();

baos2.close();

baos.close();

workbook.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

//发送响应流方法

private void setResponseHeader(HttpServletResponse response, String fileName) {

try {

fileName = URLEncoder.encode(fileName, "ISO-8859-1");

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");

} catch (Exception ex) {

ex.printStackTrace();

}

}

/**

* 通用Excel导出方法,利用反射机制遍历对象的所有字段,将数据写入Excel文件中

* 此方法生成2003版本的excel,文件名后缀:xls

*

* @param sheetName 表格标题名

* @param columnNames 表格头部标题集合

* @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的

* JavaBean属性的数据类型有基本数据类型及String,Date

* @param pattern 如果有时间数据,设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"

*/

@SuppressWarnings({"unchecked", "rawtypes"})

public HSSFWorkbook exportExcel2003(String sheetName, String[] columnNames, Collection dataset, Class clazz, String sheetPwd, String pattern) {

// 声明一个工作薄

HSSFWorkbook workbook = new HSSFWorkbook();

// 生成一个表格

HSSFSheet sheet = workbook.createSheet(sheetName);

//设置表格不可编辑

sheet.protectSheet(sheetPwd);

// 设置表格默认列宽度为15个字节

sheet.setDefaultColumnWidth(100);

for (int i = 0; i < 20; i++) {

//设置列宽

sheet.setColumnWidth(i, 30 * 256);

}

// 生成一个样式

HSSFCellStyle style = workbook.createCellStyle();

style.setLocked(true);

// 设置样式

style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());

style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

style.setBorderBottom(BorderStyle.THIN);

style.setBorderLeft(BorderStyle.THIN);

style.setBorderRight(BorderStyle.THIN);

style.setBorderTop(BorderStyle.THIN);

style.setAlignment(HorizontalAlignment.CENTER);

style.setVerticalAlignment(VerticalAlignment.CENTER);

// 生成一个字体

HSSFFont font = workbook.createFont();

font.setFontName("宋体");

font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());

font.setFontHeightInPoints((short) 11);

style.setFont(font);

// 生成并设置另一个样式

HSSFCellStyle style2 = workbook.createCellStyle();

//单元格锁定的样式

style2.setLocked(true);

style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());

style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);

style2.setBorderBottom(BorderStyle.THIN);

style2.setBorderLeft(BorderStyle.THIN);

style2.setBorderRight(BorderStyle.THIN);

style2.setBorderTop(BorderStyle.THIN);

style2.setAlignment(HorizontalAlignment.CENTER);

style2.setVerticalAlignment(VerticalAlignment.CENTER);

// 生成另一个字体

HSSFFont font2 = workbook.createFont();

// 把字体应用到当前的样式

style2.setFont(font2);

// 产生表格标题行

HSSFRow row = sheet.createRow(0);

HSSFCell cell;

for (int i = 0; i < columnNames.length; i++) {

cell = row.createCell(i);

cell.setCellStyle(style);

cell.setCellValue(columnNames[i]);

}

//字段排序

List list = new ArrayList<>();

Field[] fields = clazz.getDeclaredFields();

for (Field f : fields) {

ExcelSort excelSort = f.getAnnotation(ExcelSort.class);

ExcelSortField excelSortField = new ExcelSortField(f, excelSort);

if (excelSort != null) {

list.add(excelSortField);

}

}

Collections.sort(list, (o1, o2) -> {

return o1.getSort().value() - o2.getSort().value();

});

// 遍历集合数据,产生数据行

Iterator it = dataset.iterator();

int index = 0;

T t;

String fieldName;

String getMethodName;

Object value;

SimpleDateFormat sdf = new SimpleDateFormat(pattern);

while (it.hasNext()) {

index++;

row = sheet.createRow(index);

t = (T) it.next();

for (int i = 0; i < list.size(); i++) {

cell = row.createCell(i);

cell.setCellStyle(style2);

fieldName = list.get(i).getField().getName();

getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()

+ fieldName.substring(1);

try {

value = t.getClass().getMethod(getMethodName, new Class[]{}).invoke(t, new Object[]{});

if (value instanceof Boolean) {

cell.setCellValue((Boolean) value ? "是" : "否");

} else if (value instanceof Date) {

cell.setCellValue(sdf.format((Date) value));

} else {

cell.setCellValue(value.toString());

}

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

workbook.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

}

return workbook;

}

}

自定义注解,对excel展示字段排序

package cn.indusec.sd.annotation;

import java.lang.annotation.ElementType;

import java.lang.annotation.Retention;

import java.lang.annotation.RetentionPolicy;

import java.lang.annotation.Target;

/**

* @author tong

* @date 2019/4/2

* @desc

*/

@Retention(value = RetentionPolicy.RUNTIME)

@Target(value = {ElementType.FIELD})

public @interface ExcelSort {

int value();

}

package cn.indusec.sd.annotation;

import lombok.AllArgsConstructor;

import lombok.Data;

import lombok.NoArgsConstructor;

import java.lang.reflect.Field;

/**

* @author tong

* @date 2019/4/2

* @desc

*/

@Data

@NoArgsConstructor

@AllArgsConstructor

public class ExcelSortField {

private Field field;

private ExcelSort sort;

}

3.打成jar包,关键最后一个resource

src/main/webapp

src/main/resources

src/main/java

**/*.xml

src/lib

BOOT-INF/lib/

**/*.jar

org.springframework.boot

spring-boot-maven-plugin

repackage

true

另外附上导出excel2007表格

/**

* 通用Excel导出方法,利用反射机制遍历对象的所有字段,将数据写入Excel文件中

* 此版本生成2007以上版本的文件 (文件后缀:xlsx)

*

* @param sheetName 表格标题名

* @param columnNames 表格头部标题集合

* @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的

* JavaBean属性的数据类型有基本数据类型及String,Date

* @param pattern 如果有时间数据,设定输出格式。默认为"yyyy-MM-dd hh:mm:ss"

*/

@SuppressWarnings({"unchecked", "rawtypes"})

public XSSFWorkbook exportExcel2007(String sheetName, String[] columnNames, Collection dataset, Class clazz, String sheetPwd, String pattern) {

// 声明一个工作薄

XSSFWorkbook workbook = new XSSFWorkbook();

// 生成一个表格

XSSFSheet sheet = workbook.createSheet(sheetName);

//设置表格不可编辑

sheet.protectSheet(sheetPwd);

// 设置表格默认列宽度为15个字节

sheet.setDefaultColumnWidth(20);

// 生成一个样式

XSSFCellStyle style = workbook.createCellStyle();

//单元格锁定的样式

style.setLocked(true);

// 设置这些样式

style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());

style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

style.setBorderBottom(BorderStyle.THIN);

style.setBorderLeft(BorderStyle.THIN);

style.setBorderRight(BorderStyle.THIN);

style.setBorderTop(BorderStyle.THIN);

style.setAlignment(HorizontalAlignment.CENTER);

// 生成一个字体

XSSFFont font = workbook.createFont();

font.setFontName("宋体");

font.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());

font.setFontHeightInPoints((short) 11);

// 把字体应用到当前的样式

style.setFont(font);

// 生成并设置另一个样式

XSSFCellStyle style2 = workbook.createCellStyle();

//单元格锁定的样式

style2.setLocked(true);

style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());

style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);

style2.setBorderBottom(BorderStyle.THIN);

style2.setBorderLeft(BorderStyle.THIN);

style2.setBorderRight(BorderStyle.THIN);

style2.setBorderTop(BorderStyle.THIN);

style2.setAlignment(HorizontalAlignment.CENTER);

style2.setVerticalAlignment(VerticalAlignment.CENTER);

// 生成另一个字体

XSSFFont font2 = workbook.createFont();

// 把字体应用到当前的样式

style2.setFont(font2);

// 产生表格标题行

XSSFRow row = sheet.createRow(0);

XSSFCell cell;

for (int i = 0; i < columnNames.length; i++) {

cell = row.createCell(i);

cell.setCellStyle(style);

cell.setCellValue(columnNames[i]);

}

//字段排序

List list = new ArrayList<>();

Field[] fields = clazz.getDeclaredFields();

for (Field f : fields) {

ExcelSort excelSort = f.getAnnotation(ExcelSort.class);

ExcelSortField excelSortField = new ExcelSortField(f, excelSort);

if (excelSort != null) {

list.add(excelSortField);

}

}

Collections.sort(list, (o1, o2) -> {

return o1.getSort().value() - o2.getSort().value();

});

// 遍历集合数据,产生数据行

Iterator it = dataset.iterator();

int index = 0;

T t;

String fieldName;

String getMethodName;

Object value;

SimpleDateFormat sdf = new SimpleDateFormat(pattern);

while (it.hasNext()) {

index++;

row = sheet.createRow(index);

t = (T) it.next();

for (int i = 0; i < list.size(); i++) {

cell = row.createCell(i);

cell.setCellStyle(style2);

fieldName = list.get(i).getField().getName();

getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()

+ fieldName.substring(1);

try {

value = t.getClass().getMethod(getMethodName, new Class[]{}).invoke(t, new Object[]{});

if (value instanceof Boolean) {

cell.setCellValue((Boolean) value ? "是" : "否");

} else if (value instanceof Date) {

cell.setCellValue(sdf.format((Date) value));

} else {

cell.setCellValue(value.toString());

}

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

workbook.close();

} catch (IOException e) {

e.printStackTrace();

}

}

}

}

return workbook;

}

Top