poi导出excel动态表头并合并
实现图，全场总电位第一级，其他 例行政部为第二层，若没有第三层的第二级如V线总电和污水处理站则合并两列，第三级 例生活区，此上数据为动态数据，根据不同用户不同数据动态变化表头
DEMO代码：
package com.springboot.util;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
public class DynamicExport {
@RequestMapping(value = "/export")
@ResponseBody
public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
// excel标题
String[] title = { "id", "姓名", "手机号", "城市", "县区", "房源", "地铁线", "地铁站", "售价", "面积", "房型", "用途", "权属", "楼层", "朝向",
"楼龄", "有无电梯", "装修", "咨询时间", "备注" };
// excel文件名
String fileName = "购房需求信息表" + System.currentTimeMillis() + ".xls";
// sheet名
String sheetName = "购房需求信息表";
String[][] content = {{ "id", "姓名", "手机号", "城市", "县区", "房源", "地铁线", "地铁站", "售价", "面积", "房型", "用途", "权属", "楼层", "朝向",
"楼龄", "有无电梯", "装修", "咨询时间", "备注" },{ "id", "姓名", "手机号", "城市", "县区", "房源", "地铁线", "地铁站", "售价", "面积", "房型", "用途", "权属", "楼层", "朝向",
"楼龄", "有无电梯", "装修", "咨询时间", "备注" }};
// 创建HSSFWorkbook
HSSFWorkbook wb = getHSSFWorkbook(sheetName, title, content, null);
// 响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb) {
// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = getColumnTopStyle(wb);
//垂直居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
// 声明列对象
HSSFCell cell = null;
CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,26);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(callRangeAddress);
cell = row.createCell(0);
cell.setCellValue("2019年11月用电统计表");
cell.setCellStyle(style);
List<Map<String,Object>> list = new ArrayList<>();
Map<String,Object> map = new HashMap<>();
map.put("id", "1");
map.put("pid", "0");
map.put("name", "全场总电");
list.add(map);
map = new HashMap<>();
map.put("id", "11");
map.put("pid", "1");
map.put("name", "二级");
list.add(map);
map = new HashMap<>();
map.put("id", "12");
map.put("pid", "1");
map.put("name", "二级2");
list.add(map);
map = new HashMap<>();
map.put("id", "13");
map.put("pid", "1");
map.put("name", "二级3");
list.add(map);
map = new HashMap<>();
map.put("id", "14");
map.put("pid", "1");
map.put("name", "二级4");
list.add(map);
map = new HashMap<>();
map.put("id", "131");
map.put("pid", "13");
map.put("name", "三级30");
list.add(map);
map = new HashMap<>();
map.put("id", "121");
map.put("pid", "12");
map.put("name", "三级20");
list.add(map);
map = new HashMap<>();
map.put("id", "122");
map.put("pid", "12");
map.put("name", "三级21");
list.add(map);
map = new HashMap<>();
map.put("id", "111");
map.put("pid", "11");
map.put("name", "三级0");
list.add(map);
map = new HashMap<>();
map.put("id", "112");
map.put("pid", "11");
map.put("name", "三级1");
list.add(map);
map = new HashMap<>();
map.put("id", "113");
map.put("pid", "11");
map.put("name", "三级2");
list.add(map);
map = new HashMap<>();
map.put("id", "114");
map.put("pid", "11");
map.put("name", "三级3");
list.add(map);
map = new HashMap<>();
map.put("id", "115");
map.put("pid", "11");
map.put("name", "三级4");
list.add(map);
HSSFCellStyle style2 = getStyle(wb);
HSSFRow row2 = sheet.createRow(1);// row(1)第二行
HSSFRow row3 = sheet.createRow(2);// row(2)第三行
int s = 3;
int e = 4;
int s2 = 3;
int s3 = 2;
int i = 1;
int i2 = 1;
for (Map<String, Object> maps : list) {
if (maps.get("pid").equals("0")) {
CellRangeAddress call = new CellRangeAddress(1,2,1,2);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(call);
cell = row2.createCell(1);// cell(2)第二列
cell.setCellValue(String.valueOf(maps.get("name")));
cell.setCellStyle(style2);
List<Map<String,Object>> list2 = treeResultData(list, String.valueOf(maps.get("id")));
for (Map<String, Object> map2 : list2) {
List<Map<String,Object>> list3 = treeResultData(list, String.valueOf(map2.get("id")));
if (i2!=1) {
s2=s3+1;
}
int cr = 1;
if (list3.size()==0) {
s3+=2;
cr = 2;
}else {
s3+=list3.size()*2+1;
}
CellRangeAddress call2 = new CellRangeAddress(1,cr,s2,s3);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(call2);
cell = row2.createCell(s);
cell.setCellValue(String.valueOf(map2.get("name")));
cell.setCellStyle(style2);
i2++;
for (Map<String, Object> map3 : list3) {
CellRangeAddress call5 = new CellRangeAddress(2,2,s,e);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(call5);
cell = row3.createCell(s);
cell.setCellValue(String.valueOf(map3.get("name")));
cell.setCellStyle(style2);
if (i==list3.size()) {
cell = row3.createCell(e+1);
cell.setCellValue("实际用电");
cell.setCellStyle(style2);
s++;
e++;
i=0;
}
i++;
s+=2;
e+=2;
}
}
}
}
return wb;
}
public static List<Map<String,Object>> treeResultData(List<Map<String, Object>> list, String id){
List<Map<String, Object>> resultList = new ArrayList<>();
for (Map<String, Object> maps : list) {
if (maps.get("pid").equals(id)) {
Map<String,Object> map = new HashMap<>();
map.put("id", maps.get("id"));
map.put("pid", maps.get("pid"));
map.put("name", maps.get("name"));
resultList.add(map);
}
}
return resultList;
}
/*
* 列头单元格样式
*/
public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 15);
// 字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置低边框
//style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置低边框颜色
//style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置右边框
//style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置顶边框
//style.setTopBorderColor(HSSFColor.BLACK.index);
// 设置顶边框颜色
//style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式中应用设置的字体
style.setFont(font);
// 设置自动换行
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐；
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 10);
// 字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
//style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
//style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
//style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
//style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
//style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
//style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
//style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
//style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
}
实际应用的代码：
package com.sgcc.epri.platform.util;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFBorderFormatting;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.bouncycastle.pqc.math.linearalgebra.BigIntUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import com.sgcc.epri.platform.mapper.ElectricityEvaluateMapper;
import com.sgcc.epri.platform.model.responseModel.ResponseParam;
import com.sgcc.epri.platform.service.TreeService;
@Controller
public class DynamicExport {
@Autowired
private TreeService treeService;
@Autowired
ElectricityEvaluateMapper electricityEvaluateMapper;
@RequestMapping(value = "/appSessionDemo")
@ResponseBody
public ResponseParam appSessionDemo(HttpServletRequest request, HttpServletResponse response) {
String sessionId = request.getSession().getId();
String sessionDemo = (String) request.getSession().getAttribute("demo");
System.err.println(sessionDemo);
String demo = request.getParameter("demo");
System.err.println(demo);
request.getSession().setAttribute("demo", demo);
ResponseParam r = new ResponseParam();
Map<String,Object> map = new HashMap<>();
map.put("returnData", sessionDemo);
map.put("sessionId", sessionId);
r.setResult(map);
return r;
}
@RequestMapping(value = "/export")
@ResponseBody
public void export(HttpServletRequest request, HttpServletResponse response) throws Exception {
String time = request.getParameter("time");
List<Map<String,Object>> treeData = treeService.getTreeData(UserUtil.getUserId(),"01");
// excel标题
String[] title = String.valueOf("1号,2号,3号,4号,5号,6号,7号,8号,9号,10号,11号,12号,13号,14号,15号,16号,17号,18号,19号,20号,21号,22号,23号,24号,25号,26号,27号,28号,29号,30号,31号,合计").split(",");
// excel文件名
String fileName = "用电详情报表" + System.currentTimeMillis() + ".xls";
// sheet名
String sheetName = "用电详情报表";
String[][] content = {};
// 创建HSSFWorkbook
HSSFWorkbook wb = getHSSFWorkbook(sheetName, title, content, null, treeData, time);
// 响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
public HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook wb, List<Map<String, Object>> treeData, String time) {
// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
if (wb == null) {
wb = new HSSFWorkbook();
}
// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = getColumnTopStyle(wb);
// 声明列对象
HSSFCell cell = null;
// 合并首行为标题使用
CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,30);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(callRangeAddress);
cell = row.createCell(0);
cell.setCellValue(""+time+"用电统计表");
cell.setCellStyle(style);
// 获取监测点数据列表
List<Map<String,Object>> list = treeData;
// 定义样式
HSSFCellStyle style2 = getStyle(wb);
HSSFRow row2 = sheet.createRow(1);// row(1)第二行
HSSFRow row3 = sheet.createRow(2);// row(2)第三行
HSSFRow row4 = sheet.createRow(3);// row(3)第四行
List<Map<String,Object>> countList = new ArrayList<>();// 存储监测点id和对应详情的位置
int s = 3;
int e = 4;
int s2 = 3;
int s3 = 2;
int i = 1;
int i2 = 1;
// 便利监测点数据写入表头
for (Map<String, Object> maps : list) {
if (maps.get("parentSturctId").equals("0")) {// 判断为总表（0）
// 合并总表位置并对合并位置赋值
CellRangeAddress call = new CellRangeAddress(1,2,1,2);// 起始行,结束行,起始列,结束列
sheet.addMergedRegion(call);
cell = row2.createCell(1);// cell(2)第二列
cell.setCellValue(String.valueOf(maps.get("unitName")));
cell.setCellStyle(style2);
Map<String,Object> countMap = new HashMap<>();
countMap.put("msId", maps.get("msId"));
countMap.put("shishuCell", 1);
countMap.put("valueCell", 2);
countList.add(countMap);
// 赋值对应位置的明细列头
cell = row4.createCell(0);
cell.setCellValue("日期");
cell.setCellStyle(style2);
cell = row4.createCell(1);
cell.setCellValue("示数");
cell.setCellStyle(style2);
cell = row4.createCell(2);
cell.setCellValue("电量");
cell.setCellStyle(style2);
List<Map<String,Object>> list2 = treeResultData(list, String.valueOf(maps.get("structDetId")));// 获取总表下的监测点信息
for (Map<String, Object> map2 : list2) {// 循环二级监测点信息
List<Map<String,Object>> list3 = treeResultData(list, String.valueOf(map2.get("structDetId")));// 获取三级监测点信息
if (i2!=1) {// 判断i2不等于1时则是已经循环过，新合并开始列需获取最后的列+1
s2=s3+1;// 获取最后一列并+1作为河北开始列
}
int cr = 1;// cr默认为1
if (list3.size()==0) {// 验证此2级节点无子级
s3+=2;// 合并列为两列
cr = 2;// 合并行为两行
// 给第三级合并标识+2告诉新的开始结束位置
s+=2;
e+=2;
}else {// 若有子级结束行则+=当前查询数据条数*2+1
s3+=list3.size()*2+1;
}
if (cr==2) {// 验证无子级 赋值对应位置的明细列头
countMap = new HashMap<>();
countMap.put("msId", map2.get("msId"));
countMap.put("shishuCell", s2);
countMap.put("valueCell", s3);
countList.add(countMap);
cell = row4.createCell(s2);
cell.setCellValue("示数");
cell.setCellStyle(style2);
cell = row4.createCell(s3);
cell.setCellValue("电量");
cell.setCellStyle(style2);
}
// 二级菜单合并赋值
CellRangeAddress call2 = new CellRangeAddress(1,cr,s2,s3);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(call2);
cell = row2.createCell(s2);
cell.setCellValue(String.valueOf(map2.get("unitName")));
cell.setCellStyle(style2);
i2++;// 标识++，说明已开始占位，别占重复位置
for (Map<String, Object> map3 : list3) {// 循环三级监测点数据
countMap = new HashMap<>();
countMap.put("msId", map3.get("msId"));
countMap.put("shishuCell", s);
countMap.put("valueCell", e);
countList.add(countMap);
// 赋值对应位置的明细列头
cell = row4.createCell(s);
cell.setCellValue("示数");
cell.setCellStyle(style2);
cell = row4.createCell(e);
cell.setCellValue("电量");
cell.setCellStyle(style2);
// 三级菜单合并赋值
CellRangeAddress call5 = new CellRangeAddress(2,2,s,e);//起始行,结束行,起始列,结束列
sheet.addMergedRegion(call5);
cell = row3.createCell(s);
cell.setCellValue(String.valueOf(map3.get("unitName")));
cell.setCellStyle(style2);
// 验证循环是否是最后一次
if (i==list3.size()) {// 循环最后一次 +实际用电和总电量的表头写入
cell = row3.createCell(e+1);
cell.setCellValue("实际用电");
cell.setCellStyle(style2);
cell = row4.createCell(e+1);
cell.setCellValue("总电量");
cell.setCellStyle(style2);
countMap = new HashMap<>();
countMap.put("msId", map2.get("msId"));
countMap.put("sumValue", e+1);
countList.add(countMap);
s++;
e++;
i=0;
}
i++;
s+=2;
e+=2;
}
}
}
}
// 获取左侧日期数据
for (int j = 0; j < title.length; j++) {
HSSFRow forRow = sheet.createRow(4 + j);// row 赋数据时的行
rowSetDate(countList,forRow,cell,style2,j,time,title.length,treeData);
cell = forRow.createCell(0);// createCell赋数据的列
cell.setCellValue(title[j]);
cell.setCellStyle(style2);
}
return wb;
}
/**
* an:因为createRow会覆盖同行之前的数据，所以createRow只能用一次，调此方法传递HSSFRow写入值
* @param countList
* @param forRow
* @param cell
* @param style2
* @param j2
* @param time
* @param length
* @param treeData
* @return
*/
public String rowSetDate(List<Map<String,Object>> countList, HSSFRow forRow, HSSFCell cell, HSSFCellStyle style2, int j2, String time, int length, List<Map<String, Object>> treeData) {
String result = "0";
String j = String.valueOf(j2+1);
List<Map<String,Object>> getList = getData(time,length,j2,treeData);
for (Map<String, Object> map : countList) {
for (Map<String, Object> map2 : getList) {
if (map.get("msId").equals(map2.get("msId"))) {
if (j.equals(String.valueOf(map2.get("time")))) {
if (!"null".equals(String.valueOf(map.get("sumValue")))) {
cell = forRow.createCell(Integer.valueOf(String.valueOf(map.get("sumValue"))));// createCell赋数据的列
cell.setCellValue(String.valueOf(map2.get("price")));
cell.setCellStyle(style2);
}else {
cell = forRow.createCell(Integer.valueOf(String.valueOf(map.get("shishuCell"))));// createCell赋数据的列
cell.setCellValue(String.valueOf(map2.get("shishu")));
cell.setCellStyle(style2);
cell = forRow.createCell(Integer.valueOf(String.valueOf(map.get("valueCell"))));// createCell赋数据的列
cell.setCellValue(String.valueOf(map2.get("price")));
cell.setCellStyle(style2);
}
}
// String sumValue = String.valueOf(map.get("sumValue"));
// if (sumValue!=null&&!"".equals(sumValue)&&!"null".equals(sumValue)) {
// cell = forRow.createCell(Integer.valueOf(String.valueOf(map.get("sumValue"))));// createCell赋数据的列
// cell.setCellValue(1);
// cell.setCellStyle(style2);
// }
}
}
}
return result;
}
/**
* an:模拟数据
* @param time
* @param j2
* @param length
* @param treeData
* @return
*/
public List<Map<String,Object>> getData(String time, int length, int j2, List<Map<String, Object>> treeData){
DecimalFormat dcf = new DecimalFormat("0.00");
List<Map<String,Object>> getList = electricityEvaluateMapper.queryTBMonthElecTricity(UserUtil.getUserId(), time+"-01", time+"-31");
List<Map<String,Object>> list = new ArrayList<>();
String msId = null;
double a = 0;
for (Map<String, Object> map : getList) {
Map<String,Object> setMap = new HashMap<>();
setMap.put("msId", map.get("MS_ID"));
setMap.put("shishu", map.get("SHISHU")==null?"-":map.get("SHISHU"));
setMap.put("price", map.get("ELECTRICITY")==null?"-":map.get("ELECTRICITY"));
setMap.put("time", Integer.valueOf(String.valueOf(map.get("DATA_DATE")).split("-")[2]));
list.add(setMap);
int i = 1;
for (Map<String, Object> map2 :treeData) {
if (map2.get("msId").equals(map.get("MS_ID"))) {
if (!String.valueOf(map.get("MS_ID")).equals(msId)) {
a = 0;
}
msId = String.valueOf(map.get("MS_ID"));
a += Double.parseDouble(String.valueOf(map.get("ELECTRICITY")==null?"0":map.get("ELECTRICITY")));
}
if (i==treeData.size()) {
Map<String,Object> setMap2 = new HashMap<>();
setMap2.put("msId", msId);
setMap2.put("shishu", "");
setMap2.put("price", dcf.format(a));
setMap2.put("time", length);
list.add(setMap2);
}
i++;
}
}
// List<Map<String,Object>> list = new ArrayList<>();
// for (int i = 1; i <= 12; i++) {
// Map<String,Object> map = new HashMap<>();
// map.put("time", i);
// map.put("price", "2"+i);
// map.put("shishu", "2"+i);
// map.put("msId", "1000010000232435");
// list.add(map);
// map = new HashMap<>();
// map.put("time", i);
// map.put("price", "3"+i);
// map.put("shishu", "3"+i);
// map.put("msId", "1000010000232447");
// list.add(map);
// }
// Map<String,Object> map = new HashMap<>();
// map.put("time", 13);
// map.put("price", "858");
// map.put("shishu", "");
// map.put("msId", "1000010000232435");
// list.add(map);
// map = new HashMap<>();
// map.put("time", 13);
// map.put("price", "1248");
// map.put("shishu", "");
// map.put("msId", "1000010000232447");
// list.add(map);
return list;
}
/**
* an:根据id获得子级数据
* @param list
* @param id
* @return
*/
public static List<Map<String,Object>> treeResultData(List<Map<String, Object>> list, String id){
List<Map<String, Object>> resultList = new ArrayList<>();
for (Map<String, Object> maps : list) {
if (maps.get("parentSturctId").equals(id)) {
Map<String,Object> map = new HashMap<>();
map.put("structDetId", maps.get("structDetId"));
map.put("parentSturctId", maps.get("parentSturctId"));
map.put("unitName", maps.get("unitName"));
map.put("msId", maps.get("msId"));
resultList.add(map);
}
}
return resultList;
}
/*
* 列头单元格样式
*/
public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 15);
// 字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置低边框
//style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置低边框颜色
//style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置右边框
//style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置顶边框
//style.setTopBorderColor(HSSFColor.BLACK.index);
// 设置顶边框颜色
//style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式中应用设置的字体
style.setFont(font);
// 设置自动换行
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐；
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 10);
// 字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
//style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
//style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
//style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
//style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
//style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
//style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
//style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
//style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
}
