IO流相关基础
导出Excel文件
思维步骤：
Controller获取bootstrap-table传递过来的所有复选框的id组成的字符串
解析出所有的id
调用Service层的
exportExcel()方法获取对应的Excel文件对象使用
ResponseUtils（使用IOUtils）读取Excel文件，写入响应流response.getOutputStream()
代码实现：
Controller
/**
* 导出选中数据信息到excel
* @author 刘子龙
* @param ids 所有复选框的id通过","组成的字符串
*/
@ResponseBody
@RequiresPermissions(value = {"stpExpert:view","stpExpert:view"},logical = Logical.OR)
@RequestMapping("/exportExcel")
public void exportExcel(String ids, HttpServletResponse response, HttpServletRequest request) {
String[] split = StringUtils.split(ids, ",");
if (split==null||split.length<1){
return;
}
File file = stpExpertServiceImpl.exportExcel(split);
ResponseUtils.response(file, file.getName(),request, response);
}
stpExpertServiceImpl.exportExcel(split);
/**
* 导出专家信息
* @param ids
* @return
*/
public File exportExcel(String[] ids) {
String excelName = "专家信息表";
List StpExpertList = new ArrayList();
int i = 1;
for (String id : ids) {
StpExpert stpExpert = new StpExpert();
stpExpert.setId(id);
List<StpExpert> list = selectList(stpExpert);
StpExpert initStpExpert = list.get(0);
//获取当前专家的实时的年龄
initStpExpert.setAge(stpExpertMapper.getRealAge(initStpExpert.getUserId()));
i++;
StpExpertList.add(initStpExpert);
}
//初始化 映射map
LinkedHashMap<String, String> alias = new LinkedHashMap<>();
// alias.put("id", "编号");
alias.put("expertName", "专家姓名");
alias.put("expertSex", "专家性别");
alias.put("expertPosition", "专家职务");
alias.put("expertMajor", "专家专业");
alias.put("expertProfessionalTitle", "专家职称");
alias.put("expertPhone", "专家手机号码");
// alias.put("userId", "用户id");
alias.put("expertJob", "专家从事职业");
alias.put("idCard", "身份证号");
alias.put("employer", "工作单位");
alias.put("age", "年龄");
alias.put("eduBackground", "学历");
alias.put("nationality", "民族");
alias.put("politicsStatus", "政治面貌");
alias.put("email", "邮箱");
// alias.put("enableStatus", "启用状态");
//如果目录不存在，创建目录
File filePath = new File(exportExcelPath);//配置文件： exportExcelPath="D:/uploadFile/excel/"
if (!filePath.exists()) {
filePath.mkdirs();
}
//创建excel文件
File excel = new File(exportExcelPath + excelName + DateUtils.dateTimeNow() + ".xlsx");
try {
if (!excel.exists()) {
excel.createNewFile();
} else {
excel.delete();
excel.createNewFile();
}
//获取输出流
FileOutputStream outputStream = new FileOutputStream(excel);
//项目列表转excel
ExcelPoiUtil.pojo2Excel(StpExpertList, outputStream, alias, excelName);
// System.out.println();
return excel;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
相关工具类：
ExcelPoiUtil(核心)
传入对应id数组，获取对应数据的Excel文件对象
package com.liu.archetype.framework.utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.text.DecimalFormat;
import java.util.*;
import java.util.Map.Entry;
public class ExcelPoiUtil {
private static final Logger logger = LoggerFactory.getLogger(ExcelPoiUtil.class);
/**
* 将对象数组转换成excel
*
* @param pojoList 对象数组
* @param out 输出流
* @param alias 指定对象属性别名，生成列名和列顺序Map<"类属性名","列名">
* @param headLine 表标题
* @throws Exception
*/
public static <T> void pojo2Excel(List<T> pojoList, OutputStream out, LinkedHashMap<String, String> alias, String headLine) throws Exception {
//创建一个工作簿
XSSFWorkbook wb = new XSSFWorkbook();
//创建一个表
XSSFSheet sheet = wb.createSheet();
//创建第一行，作为表名
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue(headLine);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
//在第一行插入列名
insertColumnName(1, sheet, alias);
//*从第2行开始插入数据
insertColumnDate(2, pojoList, sheet, alias);
//输出表格文件
try {
wb.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
out.close();
}
}
public static <T> void map2Excel(List<Map<String, Object>> pojoList, OutputStream out, LinkedHashMap<String, String> alias, String headLine) throws Exception {
//创建一个工作簿
XSSFWorkbook wb = new XSSFWorkbook();
//创建一个表
XSSFSheet sheet = wb.createSheet();
//创建第一行，作为表名
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue(headLine);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
//在第一行插入列名
insertColumnName(1, sheet, alias);
//*从第2行开始插入数据
insertColumnDateByMap(2, pojoList, sheet, alias);
//输出表格文件
try {
wb.write(out);
} catch (IOException e) {
e.printStackTrace();
} finally {
out.close();
}
}
/**
* 将对象数组转换成excel
*
* @param pojoList 对象数组
* @param out 输出流
* @param alias 指定对象属性别名，生成列名和列顺序
* @throws Exception
*/
public static <T> void pojo2Excel(List<T> pojoList, OutputStream out, LinkedHashMap<String, String> alias) throws Exception {
//获取类名作为标题
String headLine = "";
if (pojoList.size() > 0) {
Object pojo = pojoList.get(0);
Class<? extends Object> claz = pojo.getClass();
headLine = claz.getName();
pojo2Excel(pojoList, out, alias, headLine);
}
}
/**
* 将对象数组转换成excel,列名为对象属性名
*
* @param pojoList 对象数组
* @param out 输出流
* @param headLine 表标题
* @throws Exception
*/
public static <T> void pojo2Excel(List<T> pojoList, OutputStream out, String headLine) throws Exception {
//获取类的属性作为列名
LinkedHashMap<String, String> alias = new LinkedHashMap<String, String>();
if (pojoList.size() > 0) {
Object pojo = pojoList.get(0);
Field[] fields = pojo.getClass().getDeclaredFields();
String[] name = new String[fields.length];
Field.setAccessible(fields, true);
for (int i = 0; i < name.length; i++) {
name[i] = fields[i].getName();
alias.put(isNull(name[i]).toString(), isNull(name[i]).toString());
}
pojo2Excel(pojoList, out, alias, headLine);
}
}
/**
* 将对象数组转换成excel，列名默认为对象属性名，标题为类名
*
* @param pojoList 对象数组
* @param out 输出流
* @throws Exception
*/
public static <T> void pojo2Excel(List<T> pojoList, OutputStream out) throws Exception {
//获取类的属性作为列名
LinkedHashMap<String, String> alias = new LinkedHashMap<String, String>();
//获取类名作为标题
String headLine = "";
if (pojoList.size() > 0) {
Object pojo = pojoList.get(0);
Class<? extends Object> claz = pojo.getClass();
headLine = claz.getName();
Field[] fields = claz.getDeclaredFields();
String[] name = new String[fields.length];
Field.setAccessible(fields, true);
for (int i = 0; i < name.length; i++) {
name[i] = fields[i].getName();
alias.put(isNull(name[i]).toString(), isNull(name[i]).toString());
}
pojo2Excel(pojoList, out, alias, headLine);
}
}
/**
* 此方法作用是创建表头的列名
*
* @param alias 要创建的表的列名与实体类的属性名的映射集合
* @return
*/
private static void insertColumnName(int rowNum, XSSFSheet sheet, Map<String, String> alias) {
XSSFRow row = sheet.createRow(rowNum);
//列的数量
int columnCount = 0;
Set<Entry<String, String>> entrySet = alias.entrySet();
for (Entry<String, String> entry : entrySet) {
//创建第一行的第columnCount个格子
XSSFCell cell = row.createCell(columnCount++);
//将此格子的值设置为alias中的键名
cell.setCellValue(isNull(entry.getValue()).toString());
}
}
/**
* 从指定行开始插入数据
*
* @param beginRowNum 开始行
* @param models 对象数组
* @param sheet 表
* @param alias 列别名
* @throws Exception
*/
private static <T> void insertColumnDate(int beginRowNum, List<T> models, XSSFSheet sheet, Map<String, String> alias) throws Exception {
for (T model : models) {
//创建新的一行
XSSFRow rowTemp = sheet.createRow(beginRowNum++);
logger.info("创建了第:{}行", beginRowNum);
//获取列的迭代
Set<Entry<String, String>> entrySet = alias.entrySet();
//从第0个格子开始创建
int columnNum = 0;
for (Entry<String, String> entry : entrySet) {
//获取属性值
String property = BeanUtils.getProperty(model, entry.getKey());
if ((entry.getKey().contains("Time") && property != null && !property.equals(""))) {
//property = DateUtils.formatDate(new Date(property), "yyyy-MM-dd");
//获取类对象
Class clazz = model.getClass();
//获取属性对象
Field field = null;
try {
field = clazz.getDeclaredField(entry.getKey());
} catch (NoSuchFieldException e1) {
//e1.printStackTrace();
try {
field = clazz.getSuperclass().getDeclaredField(entry.getKey());
} catch (NoSuchFieldException e2) {
//e2.printStackTrace();
field = null;
}
}
if (field != null) {
//设置修改权限(private)
field.setAccessible(true);
Object fieldValue = field.get(model);
if (fieldValue instanceof Date) {
property = DateUtils.formatDate((Date) fieldValue, "yyyy-MM-dd");
}
System.out.println(property);
}
}
if (((entry.getKey().equals("auditStatus") && property != null && !property.equals("")))) {
switch (property) {
case "0":
property = "草稿";
break;
case "1":
property = "待审核";
break;
case "2":
property = "区审核通过";
break;
case "3":
property = "市审核通过";
break;
case "4":
property = "省审核通过";
break;
case "5":
property = "审核未通过";
break;
default:
property = "";
break;
}
}
if (((entry.getKey().equals("status") && property != null && !property.equals("")))) {
switch (property) {
case "0":
property = "草稿";
break;
case "1":
property = "正常";
break;
default:
property = "";
break;
}
}
if (((entry.getKey().equals("subsidyFundApplyFlag") && property != null && !property.equals("")))) {
switch (property) {
case "0":
property = "否";
break;
case "1":
property = "是";
break;
default:
property = "";
break;
}
}
if (((entry.getKey().equals("subsidyFundCheckbox") && property != null && !property.equals("")))) {
property = property.replaceAll("1", "中央财政");
property = property.replaceAll("2", "中央发改");
property = property.replaceAll("99", "其他");
}
if (((entry.getKey().equals("planInvestmentCheckbox") && property != null && !property.equals("")))) {
property = property.replaceAll("1", "居民");
property = property.replaceAll("2", "单位");
property = property.replaceAll("3", "社会");
property = property.replaceAll("4", "中央财政");
property = property.replaceAll("5", "中央发改");
property = property.replaceAll("6", "地方财政");
property = property.replaceAll("99", "其他");
}
//创建一个格子
XSSFCell cell = rowTemp.createCell(columnNum++);
cell.setCellValue(property);
}
}
}
private static <T> void insertColumnDateByMap(int beginRowNum, List<Map<String, Object>> maps, XSSFSheet sheet, Map<String, String> alias) throws Exception {
for (Map<String, Object> map : maps) {
//创建新的一行
XSSFRow rowTemp = sheet.createRow(beginRowNum++);
logger.info("创建了第:{}行", beginRowNum);
//获取列的迭代
Set<Entry<String, String>> entrySet = alias.entrySet();
//从第0个格子开始创建
int columnNum = 0;
for (Entry<String, String> entry : entrySet) {
//获取属性值
String property = (String) map.get(entry.getKey());
//创建一个格子
XSSFCell cell = rowTemp.createCell(columnNum++);
cell.setCellValue(property);
}
}
}
//判断是否为空，若为空设为""
private static Object isNull(Object object) {
if (object != null) {
return object;
} else {
return "";
}
}
/**
* 将excel表转换成指定类型的对象数组
*
* @param claz 类型
* @param alias 列别名,格式要求：Map<"列名","类属性名">
* @return
* @throws IOException
* @throws IllegalArgumentException
* @throws IllegalAccessException
* @throws SecurityException
* @throws NoSuchFieldException
* @throws InstantiationException
* @throws InvocationTargetException
*/
public static <T> List<T> excel2Pojo(InputStream inputStream, Class<T> claz, LinkedHashMap<String, String> alias, int rows) throws IOException {
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
try {
XSSFSheet sheet = wb.getSheetAt(0);
//生成属性和列对应关系的map，Map<类属性名，对应一行的第几列>
Map<String, Integer> propertyMap = generateColumnPropertyMap(sheet, alias);
//根据指定的映射关系进行转换
List<T> list = generateList(sheet, propertyMap, claz, rows);
return list;
} catch (Exception e) {
e.printStackTrace();
return null;
} finally {
inputStream.close();
}
}
/**
* 生成一个属性-列的对应关系的map
*
* @param sheet 表
* @param alias 别名
* @return
*/
private static Map<String, Integer> generateColumnPropertyMap(XSSFSheet sheet, LinkedHashMap<String, String> alias) {
Map<String, Integer> propertyMap = new HashMap<>();
XSSFRow propertyRow = sheet.getRow(1);
short firstCellNum = propertyRow.getFirstCellNum();
short lastCellNum = propertyRow.getLastCellNum();
for (int i = firstCellNum; i < lastCellNum; i++) {
Cell cell = propertyRow.getCell(i);
if (cell == null) {
continue;
}
//获取单元格类型
int cellType = cell.getCellType();
//列名
String cellValue;
//若是数字类型
if (cellType == 0) {
double numericCellValue = cell.getNumericCellValue();
cellValue = Double.toString(numericCellValue);
} else {
cellValue = cell.getStringCellValue();
}
//对应属性名
String propertyName = alias.get(cellValue);
propertyMap.put(propertyName, i);
}
return propertyMap;
}
/**
* 根据指定关系将表数据转换成对象数组
*
* @param sheet 表
* @param propertyMap 属性映射关系Map<"属性名",一行第几列>
* @param claz 类类型
* @param rows 要忽略的行数
* @return
* @throws InstantiationException
* @throws IllegalAccessException
* @throws InvocationTargetException
*/
private static <T> List<T> generateList(XSSFSheet sheet, Map<String, Integer> propertyMap, Class<T> claz, int rows) throws InstantiationException, IllegalAccessException, InvocationTargetException {
//cell
Map<String, Object> cellMap = new HashMap<>();
//空单元格所在 行数列表
List<Integer> nullRowList = new ArrayList<>();
//对象数组
List<T> pojoList = new ArrayList<>();
//创建空单元格行数量和对比数。
int nullCount = 0;
int compareCount = -1;
for (Row row : sheet) {
//跳过前两行标题和列名
if (row.getRowNum() < rows) {
continue;
}
T instance = claz.newInstance();
Set<Entry<String, Integer>> entrySet = propertyMap.entrySet();
for (Entry<String, Integer> entry : entrySet) {
//获取此行指定列的值,即为属性对应的值
String property = null;
Cell cell = row.getCell(entry.getValue());
//获取单元格类型
int cellType = cell.getCellType();
//如果单元格为日期格式
if (entry.getKey().indexOf("Date") > 1) {
Date dateCellValue = cell.getDateCellValue();
BeanUtils.setProperty(instance, entry.getKey(), dateCellValue);
} else {
//若是数字类型
if (cellType == 0) {
DecimalFormat df = new DecimalFormat("0");
property = df.format(cell.getNumericCellValue());
} else {
property = row.getCell(entry.getValue()).getStringCellValue().toString();
}
// //如果单元格为空或者""，将行数放入空行列表
// int columnIndex = cell.getColumnIndex();
// if ((property == null || property.equals("")) && (columnIndex != 5 && columnIndex != 6)) {
// nullRowList.add(row.getRowNum() + 1);
// nullCount++;
// break;
// }
BeanUtils.setProperty(instance, entry.getKey(), property);
}
}
pojoList.add(instance);
}
return pojoList;
}
/**
* 将excel表转换成指定类型的对象数组，列名即作为对象属性
*
* @param claz 类型
* @return
* @throws IOException
* @throws InstantiationException
* @throws SecurityException
* @throws NoSuchFieldException
* @throws IllegalAccessException
* @throws IllegalArgumentException
* @throws InvocationTargetException
*/
public static <T> List<T> excel2Pojo(InputStream inputStream, Class<T> claz, int rows) throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException, InstantiationException, IOException, InvocationTargetException {
LinkedHashMap<String, String> alias = new LinkedHashMap<String, String>();
Field[] fields = claz.getDeclaredFields();
for (Field field : fields) {
alias.put(field.getName(), field.getName());
}
List<T> list = excel2Pojo(inputStream, claz, alias, rows);
return list;
}
public static void main(String[] args) {
Date date = new Date(39510);
}
/**
* @return java.io.File
* @Description 选择字段导出excel
* @Author liangchen
* @Date 2019/11/14 10:12
* @Param [fieldArr：所选字段list, excelName：生成Excel名称,
* alias：映射关系（对象属性：字段名）, exportExcelPath：导出路径, list：对象列表]
**/
public static File exportChangeExcel(List<String> fieldArr, String excelName, String exportExcelPath,
LinkedHashMap<String, String> alias, List pojoList) {
//遍历 映射map，如果key不在选中字段列表中，移除该key。
Iterator<Entry<String, String>> iterator = alias.entrySet().iterator();
while (iterator.hasNext()) {
Entry<String, String> entry = iterator.next();
String key = entry.getKey();
if (!fieldArr.contains(key)) {
iterator.remove();
}
}
//创建导出文件，获取输出流
//如果目录不存在，创建目录
File filePath = new File(exportExcelPath);
if (!filePath.exists()) {
filePath.mkdirs();
}
//创建excel文件
File excel = new File(exportExcelPath + excelName + StringUtils.uuid() + ".xlsx");
try {
if (!excel.exists()) {
excel.createNewFile();
} else {
excel.delete();
excel.createNewFile();
}
//获取输出流
FileOutputStream outputStream = new FileOutputStream(excel);
//项目列表转excel
ExcelPoiUtil.pojo2Excel(pojoList, outputStream, alias, excelName);
} catch (IOException e) {
e.printStackTrace();
//logger.error(DateUtils.getDateTime() + "----" + e.getMessage());
} catch (Exception e) {
e.printStackTrace();
//logger.error(DateUtils.getDateTime() + "----" + e.getMessage());
}
return excel;
}
}
ResponseUtls
向请求端响应文件信息流 (
IOUtils.copy(文件读取流，响应流))
package com.lidachu.archetype.framework.utils;
import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
public class ResponseUtils {
private static final Logger log = LoggerFactory.getLogger(HttpUtils.class);
/**
*<ul>
* <li>功能：向前端响应普通文本数据</li>
* <li>流程：</li>
* @param content
* @param response
*/
public static void response(String content, HttpServletResponse response) {
try {
response.setHeader("Content-type", "application/json;charset=UTF-8");
response.setCharacterEncoding("UTF-8");
response.getWriter().write(content);
response.getWriter().flush();
} catch (IOException e) {
// TODO Auto-generated catch block
log.error("ResponseUtils.response", e);
}
}
/**
*<ul>
* <li>功能：向请求端响应文件信息流</li>
* <li>流程：</li>
* @param file 响应的文件对象
* @param fileName
* @param response
*</ul>
*/
public static void response(File file, String fileName, HttpServletRequest request, HttpServletResponse response) {
try {
//浏览器设置
String userAgent = request.getHeader("User-Agent");
if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
//IE浏览器处理
fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
} else {
// 非IE浏览器的处理：
fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
}
// 设置文件头：最后一个参数是设置下载文件名
response.setHeader("Content-Disposition", "attachment;fileName="+fileName);
// 设置文件ContentType类型，这样设置，会自动判断下载文件类型
response.setContentType("application/octet-stream;charset=utf-8");
IOUtils.copy(new FileInputStream(file), response.getOutputStream());
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
log.error(e.getMessage());
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
log.error(e.getMessage());
} catch (IOException e) {
// TODO Auto-generated catch block
log.error(e.getMessage());
}
}
}
616
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
