java 完成excel 路径下载

package cn.youthwechat.utils.excel;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.lang.reflect.Field;
import java.util.*;
public class ExcelUtil {
private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
//记录表头占几行
private int headDeep;
/**
* 导出excel
* @param dataList 即将导出的数据列表
* @param propertyToDescription 数据列表实体bean中属性与excel表头文字的对应关系
* @param 
* @return
*/
public static synchronized Workbook export(List dataList, Map propertyToDescription) throws Exception {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet hssfSheet = workbook.createSheet("sheet1");
HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
hssfCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
ExcelTreeBodyStructure structure = parseHeaderTree(propertyToDescription);
printExcelHead(structure.getTreeNodeList(),hssfSheet,hssfCellStyle);
printExcelBody(dataList, structure.getHeadPropertyTreeLeafList(),hssfSheet,null,getTreeHight(propertyToDescription),0);
return workbook;
}catch(Exception e){
e.printStackTrace();
logger.error(e.toString());
throw new Exception("导出信息失败！");
}
}
/**
* 根据表头单元格描述，构造表头
* @param nodeList 表头单元格描述节点
* @param sheet excel表单
* @param style 样式对象
*/
private static synchronized void printExcelHead(List nodeList,HSSFSheet sheet, HSSFCellStyle style){
Map rowNum2Obj = new HashMap<>();
HSSFRow row = null;
HSSFCell cell = null;
for (ExcelHeadNode node:nodeList){
if(rowNum2Obj.get(node.getRowNum()) == null){
row = sheet.createRow(node.getRowNum());
rowNum2Obj.put(node.getRowNum(), row);
}else{
row = rowNum2Obj.get(node.getRowNum());
}
//节点占用的单元格数量大于1，则需要合并单元格
if(node.getRowCount() > 1 || node.getColCount() > 1){
sheet.addMergedRegion(new CellRangeAddress(node.getRowNum(), node.getRowNum()+node.getRowCount()-1, node.getColNum(), node.getColNum()+node.getColCount()-1));
}
cell = row.createCell(node.getColNum());
cell.setCellValue(node.getContent());
if(style != null){
cell.setCellStyle(style);
}
}
}
private static synchronized void printExcelBody(List dataList, List headPropertyTreeLeafList, HSSFSheet sheet, HSSFCellStyle style, int beginRow, int beginColumn) throws NoSuchFieldException, IllegalAccessException {
HSSFRow row = null;
HSSFCell cell = null;
int columnIndex;
for (T dataBean:dataList){
row = sheet.createRow(beginRow++);
columnIndex = beginColumn;
for (String property : headPropertyTreeLeafList){
Field declaredField = dataBean.getClass().getDeclaredField(property);
cell = row.createCell(columnIndex++);
if (declaredField == null) {
cell.setCellValue("");
logger.warn("需要导出的列，在实体类中未找到！！！");
} else {
declaredField.setAccessible(true);
cell.setCellValue(String.valueOf(declaredField.get(dataBean)));
if(style != null){
cell.setCellStyle(style);
}
}
}
}
}
/**
* 根据实体bean属性与excel表头文字的对应关系获取表头结构
* @param propertyToDescription 原始的bean实体属性和excel表头显示文字的对应关系，key的类型限定为String和Map
* @return
*/
private static synchronized ExcelTreeBodyStructure parseHeaderTree(Map propertyToDescription) throws Exception {
ExcelTreeBodyStructure structure = new ExcelTreeBodyStructure();
ArrayList nodeList = new ArrayList<>();
ArrayList headPropertyTreeLeafList = new ArrayList<>();
parseHeaderTree(propertyToDescription, getTreeHight(propertyToDescription), 0,0, nodeList, headPropertyTreeLeafList);
structure.setHeadPropertyTreeLeafList(headPropertyTreeLeafList);
structure.setTreeNodeList(nodeList);
return structure;
}
/**
* 构造表头树的节点
* @param propertyToDescription 原始的bean实体属性和excel表头显示文字的对应关系，key的类型限定为String和Map
* @param totleLevel propertyToDescription包含了几层map(即是Excel表头将会有几行)
* @param currentLevel 当前计算的第几层
* @param currentColumn 当前计算的层从第几列开始
* @param nodeList 计算后生成的node都放在该list中，生成表头时使用
* @param headPropertyTreeLeafList 将bean属性名按顺序放到list中，导出数据时使用
* @throws Exception
*/
private static synchronized void parseHeaderTree(Map propertyToDescription, int totleLevel, int currentLevel,int currentColumn, List nodeList, List headPropertyTreeLeafList) throws Exception {
int columnOffset = 0;
Iterator iterator = propertyToDescription.keySet().iterator();
while(iterator.hasNext()){
Object next = iterator.next();
if(next instanceof String){
//构建节点
ExcelHeadNode node = new ExcelHeadNode();
node.setNodeType(ExcelNodeType.STRING);
node.setContent(propertyToDescription.get(next));
node.setProperty((String)next);
node.setRowNum(currentLevel);
node.setRowCount(totleLevel-currentLevel);
node.setColNum(currentColumn+columnOffset);
node.setColCount(1);
//将节点放到节点列表中
nodeList.add(node);
//将属性名放到表头属性列表中
headPropertyTreeLeafList.add((String)next);
columnOffset++;
}else if(next instanceof Map){
//构建节点
ExcelHeadNode node = new ExcelHeadNode();
node.setNodeType(ExcelNodeType.MAP);
node.setNode(next);
node.setContent(propertyToDescription.get(next));
node.setRowNum(currentLevel);
node.setRowCount(1);
node.setColNum(currentColumn+columnOffset);
int cellWidth = getTreeWidth((Map) next);
node.setColCount(cellWidth);
//将节点放到节点列表中
nodeList.add(node);
//递归调用
parseHeaderTree((Map) next,totleLevel,currentLevel+1,currentColumn+columnOffset,nodeList,headPropertyTreeLeafList);
columnOffset+=cellWidth;
}else{
throw new Exception("表头容器包含无效类型");
}
}
}
/**
* 计算表头共几行(表头树的高度)，递归算法
* @param propertyToDescription
* @return
* @throws Exception
*/
public static synchronized int getTreeHight(Map propertyToDescription) throws Exception {
if(propertyToDescription == null || propertyToDescription.size() < 1){
throw new Exception("表头容器中至少需要包含一个元素");
}
int treeHight = 1;
Iterator iterator = propertyToDescription.keySet().iterator();
while(iterator.hasNext()){
Object next = iterator.next();
if(next instanceof Map){
int h = getTreeHight((Map) next)+1;
if(h>treeHight){
treeHight = h;
}
}else if(next instanceof String){
}else{
throw new Exception("表头容器包含无效类型");
}
}
return treeHight;
}
/**
* 计算表头的列数(表头树的宽度)，递归算法
* @param propertyToDescription
* @return
*/
private static synchronized int getTreeWidth(Map propertyToDescription) throws Exception {
int treeWidth = 0;
if(propertyToDescription == null || propertyToDescription.size() < 1){
throw new Exception("表头容器中至少需要包含一个元素");
}
Iterator iterator = propertyToDescription.keySet().iterator();
while(iterator.hasNext()){
Object next = iterator.next();
if(next instanceof Map){
treeWidth += getTreeWidth((Map) next);
}else if(next instanceof String){
treeWidth ++;
}else{
throw new Exception("表头容器包含无效类型");
}
}
return treeWidth;
}
/**
* 例子
* @param response
*/
/*public void testExcel(HttpServletResponse response) {
ServletOutputStream out = null;
try {
out = response.getOutputStream();
String name = "专家回复统计" + new Date().getTime();
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(name + ".xls", "UTF-8"));
//构建表头结构，
// key只能是String类型和Map类型数据，String类型数据实体对象中的属性名，map类型是上层结构的递归(嵌套表头形式，用于多行的复杂表头)
// value只能是String类型数据，Excel表头中显示的文字
//具体看下面的例子
LinkedHashMap beanPropertyToExcelColumn = new LinkedHashMap<>();
beanPropertyToExcelColumn.put("username", "专家");
beanPropertyToExcelColumn.put("replyUserCount", "回复用户数");
//嵌套表头
LinkedHashMap head1 = new LinkedHashMap<>();
head1.put("username", "专家");
head1.put("replyUserCount", "用户数");
head1.put("replyMsgCount", "次数");
beanPropertyToExcelColumn.put(head1, "专家信息");
//以上的结构生成的excel表头是这样的：
//|-------------------------------------------------------|
//| | | 专家信息 |
//| 专家 | 回复用户数 |-------------------------------|
//| | | 专家 | 用户数 | 次数 |
//|-------------------------------------------------------|
//ReplyStatistics是个普通的javabean，只包含了三个属性(username，replyUserCount,replyMsgCount)和他们的getter/setter方法
//ReplyStatistics只允许包含java的基本类型(基本类型包含String等)，该工具类暂时不支持嵌套解析数据(即解析不了复杂结构的javabean)
//构建表头时，map中的key对应的都是ReplyStatistics的属性名
List replyStatistics = replyMapper.selectReplyStatistics(startTime, endTime, null,null);
Workbook workbook = ExcelUtil.export(replyStatistics, beanPropertyToExcelColumn);
workbook.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}*/
}