实现图，全场总电位第一级，其他 例行政部为第二层，若没有第三层的第二级如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; } }