一、POI动态字段导出Excel-导入Excel
1.直接导出Excel
public static void main(String[] args) throws IOException {
// String filePath = "E:\\测试POI生成文件\\测试POI生成文件01.xls";
// HSSFWorkbook workbook = new HSSFWorkbook();
// HSSFSheet sheet = workbook.createSheet();
// sheet = workbook.createSheet("测试POI生成文件01");
// FileOutputStream out = new FileOutputStream(filePath);
// workbook.write(out);
// out.close();
// 升级到 POI 3.5
// TestController.test01();
// TestController.test02();o
String jsonString = "{\n" +
" \"title\":\"查询表格\",\n" +
" \"columns\":[\n" +
" [\n" +
" {\n" +
" \"title\":\"序号\",\n" +
" \"field\":\"SEQ\"\n" +
" },\n" +
" {\n" +
" \"title\":\"当前状态\",\n" +
" \"field\":\"CURRENT_STATUS\"\n" +
" }\n" +
" ]\n" +
" ]\n" +
" }";
Map<String, Object> headerMap = JSON.parseObject(jsonString);
List<Map<String, Object>> list = new ArrayList<>();
for (int i = 1; i < 65534; i++) {
Map<String, Object> map = new HashMap<>();
map.put("SEQ", "seq" + i);
map.put("CURRENT_STATUS", "CURRENT_STATUS" + i);
list.add(map);
}
HSSFWorkbook workbook = TestController.test03(headerMap, list);
FileOutputStream os = new FileOutputStream("E:\\\\测试POI生成文件\\测试poi生成文件03.xls");
workbook.write(os);
}
2.响应式导出Excel
/**
*
* @param head 列表标题行，例：{"head": {"title": "接口异常统计数据","columns": [[{"title": "接口名称","field": "requestUrl"}, {"title": "接口类型","field": "interfaceType"}]]}}
* @param param 查询列表数据参数 {"code":"c_test_select","initSql":"AA==","init":[],"query":[{"name":"XM","value":"","type":"2"}]}
* @param request
* @param response
* @return
* @throws Exception
*/
@ApiOperation("2.导出列表数据")
@GetMapping(value = "exportQueryData")
public void exportQueryExcel(@RequestParam(value = "head") String head,
@RequestParam(value = "param" ) String param,
@RequestParam(value = "pager.pageNo" , defaultValue = "1") String pageNo,
@RequestParam(value = "pager.pageSize" , defaultValue = "0") String pageSize,
@RequestParam(value = "sort" , defaultValue = "") String sort,
@RequestParam(value = "direction" , defaultValue = "") String direction,
HttpServletRequest request,
HttpServletResponse response) throws Exception{
queryService.exportQueryExcel(head, param, pageNo, pageSize, sort, direction, request, response);
/**
*
* @param head 列表标题行，例：{"head": {"title": "接口异常统计数据","columns": [[{"title": "接口名称","field": "requestUrl"}, {"title": "接口类型","field": "interfaceType"}]]}}
* @param param 查询列表数据参数 {"code":"c_test_select","initSql":"AA==","init":[],"query":[{"name":"XM","value":"","type":"2"}]}
* @param request
* @param response
* @return
* @throws Exception
*/
void exportQueryExcel(String head, String param, String pageNo, String pageSize, String sort, String direction, HttpServletRequest request, HttpServletResponse response) throws Exception;
@Override
public void exportQueryExcel(String head, String param, String pageNo, String pageSize, String sort, String direction, HttpServletRequest request, HttpServletResponse response) throws Exception {
log.info("-------------------------------开始导出数据------------------------------------------------------");
String deStringParam = ""; // 解密后 param
String deStringHead = ""; // 解密后 head
try {
// 解密（前端加密2次，）
deStringParam = URLDecoder.decode(param, "UTF-8");
log.info("解密后param：" + deStringParam);
deStringHead = URLDecoder.decode(head, "UTF-8");
log.info("解密后head：" + deStringHead);
}catch (Exception e){
log.error("解密数据异常");
e.printStackTrace();
}
Map<String, Object> paramMap = JSON.parseObject(deStringParam, Map.class);
Map<String, Object> headMap = new HashMap<>();
List outTitleAndColumnsList = new ArrayList<>();
List<Map<String, String>> inTitleAndColumnsList = new ArrayList<>();
String fileTitle = "";
if (paramMap!= null){
// 获取列表的查询标识
String queryCode = (String)paramMap.get("code");
Query query = queryDao.get(queryCode);
// 导出文件的标题
fileTitle = query.getQueryName();
String[] codes = new String[]{queryCode};
List<Field> fList = queryDao.getField(codes);
// 获取到导出文件的表头
for (Field field : fList) {
// List fieldList = new ArrayList();
String[] inSort = deStringHead.split(",");
for (String inS : inSort) {
String fieSort = String.valueOf(field.getSort().toString());
if (fieSort.equals(inS)){
Map<String, String> fieldMap = new HashMap<>();
String tittle = field.getTittle();
String fieldName = field.getFieldName();
fieldMap.put("title", tittle);
fieldMap.put("field", fieldName);
inTitleAndColumnsList.add(fieldMap);
}
}
}
}
// List nullList = new ArrayList<>();
// nullList.add(inTitleAndColumnsList);
// outTitleAndColumnsList.add(nullList);
outTitleAndColumnsList.add(inTitleAndColumnsList);
Map map = new HashMap();
map.put("title", fileTitle);
map.put("columns", outTitleAndColumnsList);
headMap.put("head",map);
deStringHead = JSON.toJSONString(headMap);
log.info("-----------------------------------" + deStringHead);
// 获取需要导出的数据
Map<String, String> inVo = new HashMap<>();
inVo.put("param", deStringParam);
inVo.put("pager.pageNo", pageNo);
// 暂时限制最多导出 1w 条数据
inVo.put("pager.pageSize", (StringUtils.isBlank(pageSize) || Integer.valueOf(pageSize) > 10000) ? "10000" : pageSize);
inVo.put("sort", sort);
inVo.put("direction", direction);
log.info("导出：查询数据，请求地址为:{},请求参数为：{}", commonQueryLoadDataUrl, JSON.toJSONString(inVo));
String result = HttpUtils.postString(queryDataUrl, inVo);
log.info("请求返回结果为：{}", result);
Map<String, Object> resultMap = JSON.parseObject(result, Map.class);
if (null == resultMap || !resultMap.containsKey("rows") || CollectionUtils.isEmpty((List)resultMap.get("rows"))){
throw new Exception("未查询到数据,请稍后再试！");
}
// 列表数据 集合(需要导出的数据)
List rows = (List)resultMap.get("rows");
List<Map<String, Object>> dataList = ExportUtils.formatList(rows);
log.info("导出数据量为" + dataList.size());
Map<String,Object> parameterMap = new HashMap<String, Object>();
parameterMap.put("map",deStringParam);
Map<String, Object> headerMap = ExportUtils.buildMap(deStringHead,"head");// 列表标题行
// Excel的head 和 查询到的数据字段名会不一致，将导出的Excel的head名转换为数据字段名
if (!CollectionUtils.isEmpty(dataList)){
Map<String, Object> dataMap = dataList.get(0); // 数据的字段名 值
List<Map<String,String>> columns = (List<Map<String,String>>)((List) headerMap.get("columns")).get(0); // 传入的需要导出的标题 字段名
for (String key : dataMap.keySet()) {
for (Map<String, String> columnsMap : columns){
if (key.equalsIgnoreCase(columnsMap.get("field"))){
columnsMap.put("field", key);
}
}
}
}
//生成数据
HSSFWorkbook workbook = ExportUtils.createExcel(headerMap, dataList);
//输出
Writer.exportExcel(response, workbook, ExportUtils.getTitle(headerMap));
log.info("-------------------------------结束导出数据------------------------------------------------------");
}3.ExportUtils工具类
import com.alibaba.fastjson.JSON;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
public class ExportUtils {
// 通用Excel导出
public static HSSFWorkbook createExcel(Map<String, Object> headerMap,
List<Map<String, Object>> dataList) {
// 第三行开始写表头
int headNum = 2;
// 表头行数
int headSize = 0;
// 数据列数
int columnLength = 0;
int[][] place = null;
String[] field = null;
List<String> dataField = new ArrayList<String>();
// 创建excel工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建sheet
HSSFSheet sheet = workbook.createSheet();
// 单元格样式
HSSFCellStyle style = workbook.createCellStyle(); // 样式对象
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
// 字体样式
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 20);// 设置字体大小
style.setFont(font);
// 单元格样式
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 样式对象
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
HSSFRow row1 = sheet.createRow(0);
row1.setHeight((short) 500);
HSSFCell cell1 = row1.createCell((short) 0);
//sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 10));
cell1.setCellStyle(style);
//-------------cell1.setEncoding(HSSFCell.ENCODING_UTF_16);
// 写Excel文件名称
cell1.setCellValue(headerMap.get("title").toString());
// 获取表头数据
List<Map<String, String>> headerList = (List<Map<String, String>>) headerMap
.get("columns");
headSize = headerList.size();
// 通过第一列表头，获取导出Excel列数
List<Map<String, String>> first = (List<Map<String, String>>) headerList
.get(0);
for (int i = 0; i < first.size(); i++) {
Map<String, String> m = first.get(i);
if (null != m.get("colspan")) {
Object value = m.get("colspan");
if (value instanceof Integer) {
columnLength = columnLength + (Integer) value;
}
} else {
columnLength = columnLength + 1;
}
}
// 初始化表头位置标志，用于判断单元格是否合并
place = new int[headSize][columnLength];
field = new String[columnLength];
System.out.println(" columnLength " + columnLength);
System.out.println(" headsize " + headSize);
// 在第三行开始创建表头
HSSFRow row = sheet.createRow(headNum);
// 设置每一列的宽度
sheet.setDefaultColumnWidth((short) 15);
sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) (columnLength-1)));
for (int i = 0; i < headerList.size(); i++) {
if (headerList.get(i) instanceof List) {
int k = 0, l = 0;
// 获取当前行的表头设置
List<Map<String, String>> header = (List<Map<String, String>>) headerList
.get(i);
for (; k < columnLength; k++) {
HSSFCell cellCheck = null;
HSSFCell cell = null;
int rowspan = 0;
int colspan = 0;
// 合并单元格直接跳过，进入下一列
if (place[headNum - 2][k] == 1) {
continue;
} else { // 否则创建单元格
cell = row.createCell((short) k);
}
// 获取当前单元格定义
Map<String, String> h = header.get(l);
// 获取跨行
if (null != h.get("rowspan")) {
Object value = h.get("rowspan");
if (value instanceof Integer) {
rowspan = (Integer) value;
}
}
// 获取跨列
if (null != h.get("colspan")) {
Object value = h.get("colspan");
if (value instanceof Integer) {
colspan = (Integer) value;
}
}
if (rowspan != 0 && colspan != 0) { // 跨行跨列
// 合并单元格
sheet.addMergedRegion(new Region(headNum, (short) k,
headNum + rowspan - 1,
(short) (k + colspan - 1)));
// 标记合并单元格
for (int m = headNum; m < headNum + rowspan; m++) {
for (int n = k; n < k + colspan; n++) {
place[m - 2][n] = 1;
}
}
k = k + colspan - 1;
} else if (rowspan != 0) { // 只跨行
// 合并单元格
sheet.addMergedRegion(new Region(headNum, (short) k,
headNum + rowspan - 1, (short) k));
// 标记合并单元格
for (int m = headNum; m < headNum + rowspan; m++) {
place[m - 2][k] = 1;
}
} else if (colspan != 0) { // 只跨列
// 合并单元格
sheet.addMergedRegion(new Region(headNum, (short) k,
headNum, (short) (k + colspan - 1)));
// 标记合并单元格
for (int n = k; n < k + colspan; n++) {
place[headNum - 2][n] = 1;
}
// 标记下一个未写列
k = k + colspan - 1;
}
// 下一个表头设置
l++;
// 标记字段值
if (null != h.get("field")
&& StringUtils.isNotBlank(h.get("field"))) {
field[k] = h.get("field");
dataField.add(h.get("field"));
}
// 设置单元格的样式
cell.setCellStyle(cellStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//-------------cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// 在单元格写内容
cell.setCellValue(h.get("title").toString());
cell.getStringCellValue();
}
// 下一行
headNum = headNum + 1;
row = sheet.createRow(headNum);
}
}
for (int i = 0; i < dataList.size(); i++) {
// 表头的下一行开始填充数据
HSSFRow row_value = sheet.createRow(headNum);
Map dataMap = dataList.get(i);
// System.out.println("------------------start数据"+ i + 1 +"------------------");
// String string = JSON.toJSONString(dataMap);
// System.out.println(string);
// System.out.println("------------------end数据"+ i + 1 +"------------------");
for (int n = 0; n < field.length; n++) {
// if (dataMap.get("CUST_CER_ID").equals("2134の1") && n == 9){
// System.out.println(1111);
// Object cust_cer_id = dataMap.get("CUST_CER_ID");
// System.out.println(cust_cer_id);
// }
HSSFCell cell = row_value.createCell((short) n);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
//-------------cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// cell.setCellValue(dataMap.get(field[n]) == null ? ""
// : dataMap.get(field[n]).toString());
if(dataMap.get(field[n]) == null || dataMap.get(field[n]).toString() == ""){
cell.setCellValue("");
}else{
Boolean strResult = dataMap.get(field[n]).toString().matches("-[0-9]+(.[0-9]+)?|[0-9]+(.[0-9]+)?");
Pattern p = Pattern.compile("[a-zA-z]");
/**判断是否含有英文单词*/
Boolean flag = p.matcher(dataMap.get(field[n]).toString()).find();
/**dataMap.get(field[n]).toString().indexOf("-")>0判断主要是区分字符串2017-10和数字-10*/
// if (!strResult||dataMap.get(field[n]).toString().indexOf("-")>0||flag){
// cell.setCellValue(dataMap.get(field[n]).toString());
// }else{
// if(dataMap.get(field[n]).toString().length()>11){
// //韶关现场 事项编码数字过长,导出时转换为String类型
// cell.setCellValue(dataMap.get(field[n]).toString());
// }else{
// // 存在特殊符号的时候转换成 Double类型会出错
// cell.setCellValue(Double.parseDouble(dataMap.get(field[n]).toString()));
// }
// }
cell.setCellValue(dataMap.get(field[n]).toString());
}
}
headNum = headNum + 1;
}
return workbook;
}
/**
* 根据前台参数 整理出表头数据
* @param parameterMap
* @return
*/
public static Map<String, Object> buildMap(Map parameterMap, String key){
JSONObject json = JSONObject.fromObject(parameterMap.get("map"));
Map<String, Object> param = json;
if (key!=null) {
Map<String, Object> keyMap = (Map<String, Object>) param.get(key);
return keyMap;
}
// 获取表头数据
return param;
}
/**
* 根据前台参数 整理出表头数据
* @param parameterMap json串
* @param key head
* @return
*/
public static Map<String, Object> buildMap(String parameterMap, String key){
Map<String, Object> param = JSON.parseObject(parameterMap, Map.class);
// Map<String, Object> param = json;
if (key!=null) {
Map<String, Object> keyMap = (Map<String, Object>) param.get(key);
return keyMap;
}
// 获取表头数据
return param;
}
public static Map<String, Object> buildHeadMap(Map parameterMap){
Map<String, Object> keyMap = (Map<String, Object>) parameterMap.get("head");
return keyMap;
}
/**
* 获取一个title
* @param headerMap
* @return
*/
public static String getTitle(Map<String, Object> headerMap){
return headerMap.get("title") == null ? "未命名" : headerMap.get("title").toString();
}
/**
* 根据 list<object> 生成 List<Map<String, Object>> 格式的数据
* @param <T>
* @param list
* @return
*/
@SuppressWarnings("unchecked")
public static <T> List<Map<String, Object>> formatList(List<T> list){
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
for(Object temp:list){
dataList.add(JSONObject.fromObject(temp));
}
return dataList;
}
/**
* 传进表头数据和列表数据 直接导出excel
* @param header
* @param list
* @param response
*/
public static<T> void exportWrite(String header, List<T> list, HttpServletResponse response){
Map<String, Object> parameterMap = new HashMap<String, Object>();
parameterMap.put("map", header);
List<Map<String, Object>> dataList = formatList(list);
//根据head生成表头数据
Map<String, Object> headerMap = buildMap(parameterMap,"head");
//生成数据
HSSFWorkbook workbook = createExcel(headerMap,dataList);
//输出
Writer.exportExcel(response, workbook, getTitle(headerMap));
}
}
4.Writer工具类
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
/**
* 文件名称：Writer.java
*
*
*/
public class Writer {
/**
* 输出不分页列表到前台
* @param response
* @param list
*/
public static void outPutList(HttpServletResponse response, List list){
try {
response.setContentType("text/html;charset=GBK");
PrintWriter out = response.getWriter();
out.print(JSONArray.fromObject(list));
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 输出普通对象到前台
* @param response
* @param list
*/
public static void outPutObject(HttpServletResponse response, Object object){
try {
response.setContentType("text/html;charset=GBK");
PrintWriter out = response.getWriter();
out.print(JSONObject.fromObject(object));
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 输出字符串到前台
* @param response
* @param list
*/
public static void outPutStr(HttpServletResponse response, String jsonStr){
try {
response.setContentType("text/html;charset=GBK");
PrintWriter out = response.getWriter();
out.print(jsonStr);
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 导出到excel
* @param response
* @param workbook
*/
public static void exportExcel(HttpServletResponse response, HSSFWorkbook workbook ){
try{
OutputStream out = response.getOutputStream();;
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition", "attachment; filename="
// + new String("所有办件统计".getBytes("utf-8"), "ISO8859-1")+".xls");
+ String.valueOf(URLEncoder.encode("所有办件统计", "UTF-8"))+".xls");
workbook.write(out);
out.flush();
out.close();
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 导出到excel，文件名可自定义
* @param response
* @param workbook
*/
public static void exportExcel(HttpServletResponse response, HSSFWorkbook workbook, String fileName ){
try{
OutputStream out = response.getOutputStream();;
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("utf-8");
/*response.setHeader("Content-disposition", "attachment; filename="
+ String.valueOf(URLEncoder.encode(fileName, "UTF-8"))+".xls");*/
/**ISO8859-1是页面上数据传输的格式*/
response.addHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes("GB2312"),"ISO-8859-1")+".xls");
workbook.write(out);
out.flush();
out.close();
}catch(Exception e){
e.printStackTrace();
}
}
/**
* 输出分页列表到前台供datagrid使用
* @param response
* @param list
*/
public static void outPutToDataGrid(HttpServletResponse response, List<Map<String, Object>> list){
try {
response.setContentType("text/html;charset=GBK");
PrintWriter out = response.getWriter();
DataGridObject dg = new DataGridObject();
dg.setRows(list);
dg.setTotal(PagerThreadLocal.getValue().getTotal()==0?list.size():PagerThreadLocal.getValue().getTotal());
JSONObject jsonObject = JSONObject.fromObject(dg);
out.print(jsonObject.toString());
out.flush();
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
二、解析加密数据再导出
2.1 ExcelUtil工具类：
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
/**
* 借鉴地址：https://blog.csdn.net/qq_37960007/article/details/84451534?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-14.control&dist_request_id=1328593.13383.16147799212719547&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-14.control
* java如何读取、写入Excel文件(将内容转成任意的bean对象)
*/
public class ExcelUtil {
public static <T> List<T> parseFromExcel(String path, Class<T> aimClass) {
return parseFromExcel(path, 0, aimClass);
}
/**
* 导入Excel文件，将其数据装换为对应实体bean
* @param path 文件路径
* @param firstIndex 从firstIndex行开始
* @param aimClass 对应的实体类
* @param <T>
* @return
*/
@SuppressWarnings("deprecation")
public static <T> List<T> parseFromExcel(String path, int firstIndex, Class<T> aimClass) {
List<T> result = new ArrayList<T>();
try {
FileInputStream fis = new FileInputStream(path);
Workbook workbook = WorkbookFactory.create(fis);
//对excel文档的第一页,即sheet1进行操作
Sheet sheet = workbook.getSheetAt(0);
int lastRaw = sheet.getLastRowNum();
for (int i = firstIndex; i <= lastRaw; i++) {
//第i行
Row row = sheet.getRow(i);
T parseObject = aimClass.newInstance();
Field[] fields = aimClass.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
field.setAccessible(true);
Class<?> type = field.getType();
//第j列
Cell cell = row.getCell(j);
if (cell == null)
continue;
//很重要的一行代码,如果不加,像12345这样的数字是不会给你转成String的,只会给你转成double,而且会导致cell.getStringCellValue()报错
cell.setCellType(Cell.CELL_TYPE_STRING);
String cellContent = cell.getStringCellValue();
cellContent = "".equals(cellContent) ? "0" : cellContent;
if (type.equals(String.class)) {
field.set(parseObject, cellContent);
} else if (type.equals(char.class) || type.equals(Character.class)) {
field.set(parseObject, cellContent.charAt(0));
} else if (type.equals(int.class) || type.equals(Integer.class)) {
field.set(parseObject, Integer.parseInt(cellContent));
} else if (type.equals(long.class) || type.equals(Long.class)) {
field.set(parseObject, Long.parseLong(cellContent));
} else if (type.equals(float.class) || type.equals(Float.class)) {
field.set(parseObject, Float.parseFloat(cellContent));
} else if (type.equals(double.class) || type.equals(Double.class)) {
field.set(parseObject, Double.parseDouble(cellContent));
} else if (type.equals(short.class) || type.equals(Short.class)) {
field.set(parseObject, Short.parseShort(cellContent));
} else if (type.equals(byte.class) || type.equals(Byte.class)) {
field.set(parseObject, Byte.parseByte(cellContent));
} else if (type.equals(boolean.class) || type.equals(Boolean.class)) {
field.set(parseObject, Boolean.parseBoolean(cellContent));
}
}
result.add(parseObject);
}
fis.close();
} catch (Exception e) {
e.printStackTrace();
System.err.println("An error occured when parsing object from Excel. at " );
}
return result;
}
// 带标题写入Excel
public static <T> void writeExcelWithTitle(List<T> beans, String path) {
writeExcel(beans,path,true);
}
// 仅把数据写入Excel
public static <T> void writeExcel(List<T> beans, String path) {
writeExcel(beans,path,false);
}
/**
*
* @param beans 需要写成Excel文件的集合数据
* @param path 写到的指定路径
* @param writeTitle 文件标题
* @param <T>
*/
private static <T> void writeExcel(List<T> beans, String path, boolean writeTitle) {
if(beans == null || beans.size() == 0){
return;
}
Workbook workbook = new HSSFWorkbook();
FileOutputStream fos = null;
int offset = writeTitle ? 1 : 0;
try {
Sheet sheet = workbook.createSheet();
for (int i = 0; i < beans.size() + offset; ++i) {
if(writeTitle && i == 0) {
createTitle(beans, sheet);
continue;
}
Row row = sheet.createRow(i);
T bean = beans.get(i - offset);
Field[] fields = bean.getClass().getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
Field field = fields[j];
field.setAccessible(true);
Cell cell = row.createCell(j);
//Date,Calender都可以 使用 +"" 操作转成字符串
cell.setCellValue(field.get(bean)+"");
}
}
fos = new FileOutputStream(path);
workbook.write(fos);
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
fos.close();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
private static <T> void createTitle(List<T> beans,Sheet sheet){
Row row = sheet.createRow(0);
T bean = beans.get(0);
Field[] fields = bean.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
field.setAccessible(true);
Cell cell = row.createCell(i);
cell.setCellValue(field.getName());
}
}
public static void main(String[] args) {
/**
* 1.将Excel文件读取到程序中
*/
//参数里的1表示有效行数从第1行开始
List<AESPublicUser> publicUserList = ExcelUtil.parseFromExcel("C:\\Users\\tission_01\\Desktop\\（解密前）.xlsx", 1,AESPublicUser.class);
System.out.println("数据量：-----------" + publicUserList.size());
for (AESPublicUser aesPublicUser : publicUserList) {
System.out.println(aesPublicUser.toString());
}
// 解密手机号码或IDcard号码
EncryptDesUtils des2 = new EncryptDesUtils("TESTTESTWIJDINVA");
for(AESPublicUser user : publicUserList){
try {
user.setCertificate_no(des2.decrypt(user.getCertificate_no()));
user.setContact_phone(des2.decrypt(user.getContact_phone()));
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 2.将数据写成Excel文件
*/
// // 带标题写入excel
// List scenics = Lists.newArrayList();
// ExcelUtil.writeExcelWithTitle(scenics,"C:\\Users\\unive\\Documents\\景点信息\\scenics.xlsx");
//
// // 不带标题写入excel
ExcelUtil.writeExcel(publicUserList,"C:\\Users\\tission_01\\Desktop\\(解密后).xlsx");
}
}2.2 Excel 对应的 实体类:
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class AESPublicUser {
/**
* 用户ID
*/
private String user_id;
/**
* 用户名
*/
// private String user_name;
/**
* 手机号码
*/
private String contact_phone;
/**
* 证件号码
*/
private String certificate_no;
}