根据字段信息动态合并单元格 动态合计 自定义sheet样式风格
控制层
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
@SuppressWarnings("unchecked")
@RequestMapping(value="/exportExcelDay", method = RequestMethod.POST)
@ResponseBody
public Map<String,Object> exportExcelNew(String filter,HttpServletResponse response) throws IOException{
Map<String, Object> resultMap = new HashMap<String, Object>();
filter = InputInjectFilter.encodeInputString(filter);
Map<String, Object> params = StringUtil.formatParam(filter);
// 声明一个工作薄(构建工作簿、表格、样式)
HSSFWorkbook wb = new HSSFWorkbook();
BufferedOutputStream fos = null;
String name = DateUtil.getCurrentTime("yyyyMMddHHmmss")+ ".xls";
try {
List<DiaryInfoDetail> detailList = deductInfoService.queryDiaryInfoDetail(params);
List<DiaryDeductInfo> infoList = deductInfoService.diaryInfo(params,null);
List<String> listTile = new ArrayList<>();
listTile.add("业务");
listTile.add("类型");
listTile.add("项目");
listTile.add("数量");
listTile.add("金额");
listTile.add("现金");
listTile.add("销卡");
listTile.add("疗程");
listTile.add("其他");
List<String> listCloumn = new ArrayList<>();
listCloumn.add("businessType");
listCloumn.add("subclassType");
listCloumn.add("businessCode");
listCloumn.add("businessNum");
listCloumn.add("sumMoney");
listCloumn.add("cashCount");
listCloumn.add("savingCount");
listCloumn.add("courseCount");
listCloumn.add("sumElse");
String[] listTile1 = {" 日绩日期","总营业额","总客单数","男客单数","女客单数","总支出","结余","现金总额","微信总额","支付宝总额",
"银行卡总额","项目总业绩","产品总业绩","会员卡异动合计","销卡合计","经理签单合计"};
String[] listCloumn1 = {"diaryDate","sumTurnover","guestCount","countMan","countLady","sumExpend",
"endSurplus","sumCash","sumWechat","sumAilpay","sumBank","itemSumDeduct","productSumDeduct",
"cardOtherSum","cardAnnulCount","managerSumPay"};
if(detailList.size() != 0 && infoList.size() != 0) {
DiaryExcel export = new DiaryExcel();
export.exportExcel(wb,"日记账表","高新店",params,detailList, listTile, listCloumn, response);
DayDetailExport exportDay = new DayDetailExport();
exportDay.exportExcel(wb,"营业汇总表", "高新店", params, infoList, listTile1, listCloumn1, response);
response.reset();
response.setContentType("application/force-download; charset=utf-8");
String fileName = URLEncoder.encode(name, "UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1"));
fos = new BufferedOutputStream(response.getOutputStream());
wb.write(fos);
resultMap.put("errorCode", ErrorCodeContents.SUCCESS_CODE);
resultMap.put("mes", "导出成功");
}else {
resultMap.put("errorCode", SqlColumnCode.FAILED_CODE);
resultMap.put("mes", "请勿导出空数据表格");
}
}
catch(Exception e) {
e.printStackTrace();
resultMap.put("errorCode", ErrorCodeContents.FAILUE_CODE);
resultMap.put("mes", "导出失败");
}finally {
if (fos != null) {
fos.flush();
fos.close();
}
}
return resultMap;
}
使用工具类DayDetailExport（动态创建合计行)
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellUtil;
/**
* 表格数据导出至excel
*
* @date 2019/9/6
* @author vring
*/
public class DayDetailExport<T> {
/**
* 这个表格主要是动态得到合计数据
* @param title 表格标题
* @param store 门店
* @param timeMap 日期范围
* @param dtoList 要导出的目标集合
* @param listTile 中文表头
* @param listCloumn 匹配的实体字段
* @param response
* @throws IOException
*/
@SuppressWarnings({ "deprecation", "null" })
// public void exportExcel(String title,String store,Map<String,Object> timeMap, List<Object> dtoList, List<String> listTile,List<String> listCloumn,HttpServletResponse response)
// throws IOException {
public void exportExcel(HSSFWorkbook wb,String title,String store,Map<String,Object> timeMap, List<Object> dtoList, String[] listTile,String[] listCloumn,HttpServletResponse response)throws IOException {
//用于存储合计行的数据
int[] cloumn = new int[listCloumn.length-1];
for(int i:cloumn) {
i = 0;
}
//表格title
Map<Integer, String> listTileMap = new HashMap<>();
int key=0;
for (int i = 0; i < listTile.length; i++) {
if (!listTile[i].equals(null)) {
listTileMap.put(key, listTile[i]);
key++;
}
}
// 实体字段
Map<Integer, String> titleFieldMap = new HashMap<>();
int value = 0;
for (int i = 0; i < listCloumn.length; i++) {
if (!listCloumn[i].equals(null)) {
titleFieldMap.put(value, listCloumn[i]);
value++;
}
}
// 声明一个工作薄(构建工作簿、表格、样式)
// HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = null;
// BufferedOutputStream fos = null;
try {
sheet = wb.createSheet(title);
sheet.setDefaultColumnWidth(20);
// 生成一个标题样式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFFont fontStyle = wb.createFont();
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontStyle.setFontHeightInPoints((short)25); //设置标题字体大小
cellStyle.setFont(fontStyle);
//在第0行创建标题行 行和列都是从0开始
HSSFRow titlerow = sheet.createRow(0);
titlerow.setHeightInPoints(40);//行高
HSSFCell cellValue = titlerow.createCell(0);
cellValue.setCellValue(title);
cellValue.setCellStyle(cellStyle);
/**合并单元格
* CellRangeAddress(firstRow, lastRow, firstCol, lastCol)
*firstRow 起始行号
*lastRow 结束行号
*firstCol 起始列号
*lastCol 结束列号
**/
sheet.addMergedRegion(new CellRangeAddress(0,0,0,(listTile.length-1)));
/ 生成一个备注行样式
HSSFCellStyle remarkcellStyle = wb.createCellStyle();
remarkcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
remarkcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFFont remarkfontStyle = wb.createFont();
remarkfontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
remarkfontStyle.setFontHeightInPoints((short)15); //设置标题字体大小
remarkcellStyle.setFont(remarkfontStyle);
CellRangeAddress csRemark1 = new CellRangeAddress(1,1,0,2);
CellRangeAddress csRemark2 = new CellRangeAddress(1,1,2,4);
//在第一行创建备注行
HSSFRow ramrkrow = sheet.createRow(1);
ramrkrow.setHeightInPoints(20);//行高
HSSFCell cellstore = ramrkrow.createCell(0);
cellstore.setCellValue("查询门店："+store);
cellstore.setCellStyle(remarkcellStyle);
setRegionStyle( sheet, csRemark1, remarkcellStyle);
setRegionStyle( sheet, csRemark2, remarkcellStyle);
String time = "";
if(StringUtil.isEmpty(timeMap.get("endDate"))) {
time = (String) timeMap.get("openDate");
}else {
time = (String) timeMap.get("openDate")+"~"+(String) timeMap.get("endDate");
}
HSSFCell celltime = ramrkrow.createCell(4);
celltime.setCellValue("日期范围："+time);
celltime.setCellStyle(remarkcellStyle);
// 生成一个表头样式
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 15);
font.setColor(HSSFColor.DARK_RED.index);//设置字体颜色 (红色)
style.setFont(font);
// 在第二行创建表头行
HSSFRow row = sheet.createRow(2);
row.setHeightInPoints(25);//行高
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个数据单元格样式
HSSFCellStyle cellParamStyle = wb.createCellStyle();
HSSFFont ParamFontStyle = wb.createFont();
ParamFontStyle.setFontHeightInPoints((short) 12);
cellParamStyle.setFont(ParamFontStyle);
cellParamStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellParamStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFCell cell;//表头cell
Collection<String> title_value = listTileMap.values();//拿到表格所有标题的value的集合
Iterator<String> ititle = title_value.iterator();//表格标题的迭代器
//根据选择的字段生成表头
int size = 0;
while (ititle.hasNext()) {
cell = row.createCell(size);
cell.setCellValue(ititle.next().toString());
cell.setCellStyle(style);
size++;
}
//表格标题一行的字段的集合
Collection<String> titleColl = titleFieldMap.values();
Iterator<Object> iterator = dtoList.iterator();//总记录的迭代器
int rowNum = 2;//列序号
while (iterator.hasNext()) {//记录的迭代器，遍历总记录
int zdCell = 0;
rowNum++;
row = sheet.createRow(rowNum);
row.setHeightInPoints(15);//行高
@SuppressWarnings("unchecked")
T t = (T) iterator.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();//获得JavaBean全部属性
for (short i = 0; i < fields.length; i++) {//遍历属性，比对
Field field = fields[i];
String fieldName = field.getName(); //属性名
Iterator<String> columnIter = titleColl.iterator(); //一条字段的集合的迭代器
while (columnIter.hasNext()) { //遍历要导出的字段集合
if (columnIter.next().equals(fieldName)) {//比对JavaBean的属性名是否一致
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);//拿到属性的get方法
Class<? extends Object> dto = t.getClass();//拿到JavaBean对象
try {
Method getMethod = dto.getMethod(getMethodName, new Class[] {});//通过JavaBean对象拿到该属性的get方法
Object val = getMethod.invoke(t, new Object[] {});//操控该对象属性的get方法，拿到属性值
String textVal = null;
HSSFCell paramCell = row.createCell(zdCell);//单元格cell
if (val!= null) {
if(val instanceof Date ) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
val = sdf.format(val);
}
textVal = String.valueOf(val);
}else{
textVal = null;
}
paramCell.setCellValue(textVal);//写进excel对象
paramCell.setCellStyle(cellParamStyle);
zdCell++;
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
}
//放入合计行的数据
for(int j = 0 ; j < listCloumn.length-1 ; j++) { //x轴
for(int i = 3 ; i <= sheet.getLastRowNum(); i++) { //y轴
int cloumnValue = Integer.valueOf(sheet.getRow(i).getCell(j+1).getStringCellValue());
if(cloumn != null) {
cloumn[j] += cloumnValue;
}
}
}
//创建合计行
int lastRow = sheet.getLastRowNum() + 1;
row = sheet.createRow(lastRow);
row.createCell(0).setCellValue("合计");
row.getCell(0).setCellStyle(cellParamStyle);
row.setHeightInPoints(25);//行高
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
for(int i = 0 ; i < cloumn.length; i++) {
HSSFCell celllast = row.createCell(i+1);
celllast.setCellValue(String.valueOf(cloumn[i]));//给单元格赋值
celllast.setCellStyle(cellParamStyle);//设置样式
}
}catch(Throwable t){
t.printStackTrace();
}
}
/**
*
* @Description: TODO(合并单元格后边框不显示问题)
* @param @param sheet
* @param @param region
* @param @param cs
* @throws
*/
public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle cs) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
HSSFRow row = (HSSFRow) CellUtil.getRow(i, sheet);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
HSSFCell cell = (HSSFCell) CellUtil.getCell(row, (short) j);
cell.setCellStyle(cs);
}
}
}
}
使用工具类DiaryExcel（根据字段动态合并单元格）
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.util.CellUtil;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* 表格数据导出至excel
* 动态合并单元格
* @date 2019/9/6
* @author vring
*/
@SuppressWarnings("hiding")
public class DiaryExcel<T> {
/**
*
* @param title 标题
* @param store 门店
* @param timeMap 日期范围
* @param dtoList 要导出的目标集合
* @param listTile 中文表头
* @param listCloumn 匹配的实体字段
* @param response
* @throws IOException
*/
@SuppressWarnings("deprecation")
public void exportExcel(HSSFWorkbook wb,String title,String store,Map<String,Object> timeMap, List<Object> dtoList, List<String> listTile,List<String> listCloumn,HttpServletResponse response)
throws IOException {
//先根据合并字段进行排序
String [] sortNameArr = {"businessType","subclassType"};
boolean [] isAscArr = {true,true};
ListUtils.sort(dtoList,sortNameArr,isAscArr);
//表格title
Map<Integer, String> listTileMap = new HashMap<>();
int key=0;
for (int i = 0; i < listTile.size(); i++) {
if (!listTile.get(i).equals(null)) {
listTileMap.put(key, listTile.get(i));
key++;
}
}
// 实体字段
Map<Integer, String> titleFieldMap = new HashMap<>();
int value = 0;
for (int i = 0; i < listCloumn.size(); i++) {
if (!listCloumn.get(i).equals(null)) {
titleFieldMap.put(value, listCloumn.get(i));
value++;
}
}
HSSFSheet sheet = null;
try {
String name = DateUtil.getCurrentTime("yyyyMMddHHmmss")+ ".xls";
String fileName = URLEncoder.encode(name, "UTF-8");
sheet = wb.createSheet(title);
sheet.setDefaultColumnWidth(20);
// 生成一个标题样式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFFont fontStyle = wb.createFont();
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontStyle.setFontHeightInPoints((short)25); //设置标题字体大小
cellStyle.setFont(fontStyle);
//在第0行创建标题行
HSSFRow titlerow = sheet.createRow(0);
titlerow.setHeightInPoints(40);//行高
HSSFCell cellValue = titlerow.createCell(0);
cellValue.setCellValue(title);
cellValue.setCellStyle(cellStyle);
/**合并单元格
* CellRangeAddress(firstRow, lastRow, firstCol, lastCol)
*firstRow 起始行号
*lastRow 结束行号
*firstCol 起始列号
*lastCol 结束列号
**/
sheet.addMergedRegion(new CellRangeAddress(0,0,0,(listTile.size()-1)));
//在第2行创建备注行
HSSFCellStyle remarkcellStyle = wb.createCellStyle();
remarkcellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
remarkcellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
HSSFFont remarkfontStyle = wb.createFont();
remarkfontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
remarkfontStyle.setFontHeightInPoints((short)15); //设置标题字体大小
remarkcellStyle.setFont(remarkfontStyle);
CellRangeAddress csRemark1 = new CellRangeAddress(1,1,0,2);
CellRangeAddress csRemark2 = new CellRangeAddress(1,1,2,4);
//创建备注行
HSSFRow ramrkrow = sheet.createRow(1);
ramrkrow.setHeightInPoints(20);//行高
HSSFCell cellstore = ramrkrow.createCell(0);
cellstore.setCellValue("查询门店："+store);
cellstore.setCellStyle(remarkcellStyle);
setRegionStyle( sheet, csRemark1, remarkcellStyle);
setRegionStyle( sheet, csRemark2, remarkcellStyle);
String time = "";
if(StringUtil.isEmpty(timeMap.get("endDate"))) {
time = (String) timeMap.get("openDate");
}else {
time = (String) timeMap.get("openDate")+"~"+(String) timeMap.get("endDate");
}
HSSFCell celltime = ramrkrow.createCell(4);
celltime.setCellValue("日期范围："+time);
celltime.setCellStyle(remarkcellStyle);
// 生成一个表头样式
HSSFCellStyle style = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short) 15);
font.setColor(HSSFColor.DARK_RED.index);//设置字体颜色 (红色)
style.setFont(font);
// 创建表头行
HSSFRow row = sheet.createRow(2);
row.setHeightInPoints(25);//行高
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个数据单元格样式
HSSFCellStyle cellParamStyle = wb.createCellStyle();
HSSFFont ParamFontStyle = wb.createFont();
ParamFontStyle.setFontHeightInPoints((short) 12);
cellParamStyle.setFont(ParamFontStyle);
cellParamStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellParamStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFCell cell;//表头cell
Collection<String> title_value = listTileMap.values();//拿到表格所有标题的value的集合
Iterator<String> ititle = title_value.iterator();//表格标题的迭代器
//根据选择的字段生成表头
int size = 0;
while (ititle.hasNext()) {
cell = row.createCell(size);
cell.setCellValue(ititle.next().toString());
cell.setCellStyle(style);
size++;
}
//表格标题一行的字段的集合
Collection<String> titleColl = titleFieldMap.values();
Iterator<Object> iterator = dtoList.iterator();//总记录的迭代器
int rowNum = 2;//列序号
//合计变量
int businessNum = 0;
int sumMoney = 0;
int cashCount = 0;
int savingCount = 0;
int courseCount = 0;
int sumElse = 0;
List<Map<Integer,String>> resMaplist = new ArrayList<>();
while (iterator.hasNext()) {//记录的迭代器，遍历总记录
int zdCell = 0;
rowNum++;
row = sheet.createRow(rowNum);
row.setHeightInPoints(15);//行高
@SuppressWarnings("unchecked")
T t = (T) iterator.next();
Map<Integer,String> rowMap = new HashMap<>();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();//获得JavaBean全部属性
for (short i = 0; i < fields.length; i++) {//遍历属性，比对
Field field = fields[i];
String fieldName = field.getName(); //属性名
Iterator<String> columnIter = titleColl.iterator(); //一条字段的集合的迭代器
while (columnIter.hasNext()) { //遍历要导出的字段集合
if (columnIter.next().equals(fieldName)) {//比对JavaBean的属性名是否一致
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);//拿到属性的get方法
Class<? extends Object> dto = t.getClass();//拿到JavaBean对象
try {
Method getMethod = dto.getMethod(getMethodName, new Class[] {});//通过JavaBean对象拿到该属性的get方法
Object val = getMethod.invoke(t, new Object[] {});//操控该对象属性的get方法，拿到属性值
String textVal = null;
HSSFCell paramCell = row.createCell(zdCell);//单元格cell
if (val!= null) {
if(val instanceof Date ) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
val = sdf.format(val);
}
switch(fieldName) {
case "businessType":
if(Integer.parseInt(val.toString()) == 1) {
textVal = "项目";
}else if(Integer.parseInt(val.toString()) == 2) {
textVal = "产品";
}else if(Integer.parseInt(val.toString()) == 3) {
textVal = "疗程";
}else if(Integer.parseInt(val.toString()) == 4) {
textVal = "售卡";
}else if(Integer.parseInt(val.toString()) == 5) {
textVal = "充值";
}else if(Integer.parseInt(val.toString()) == 6) {
textVal = "兑换";
}
break;
case "subclassType":
if(Integer.parseInt(val.toString()) == 1) {
textVal = "美容服务类";
}else if(Integer.parseInt(val.toString()) == 2) {
textVal = "美发服务类";
}else if(Integer.parseInt(val.toString()) == 3) {
textVal = "会员卡开卡";
}else if(Integer.parseInt(val.toString()) == 4) {
textVal = "会员卡充值";
}else if(Integer.parseInt(val.toString()) == 5) {
textVal = "兑换";
}
break;
case "businessNum":
businessNum += StringUtil.isEmpty(val)?0:Integer.parseInt(val.toString());
textVal = String.valueOf(val);
break;
case "sumMoney":
sumMoney += StringUtil.isEmpty(val)?0:Integer.parseInt(val.toString());
textVal = String.valueOf(val);
break;
case "cashCount":
cashCount += StringUtil.isEmpty(val)?0:Integer.parseInt(val.toString());
textVal = String.valueOf(val);
break;
case "savingCount":
savingCount += StringUtil.isEmpty(val)?0:Integer.parseInt(val.toString());
textVal = String.valueOf(val);
break;
case "courseCount":
courseCount += StringUtil.isEmpty(val)?0:Integer.parseInt(val.toString());
textVal = String.valueOf(val);
break;
case "sumElse":
sumElse += StringUtil.isEmpty(val)?0:Integer.parseInt(val.toString());
textVal = String.valueOf(val);
break;
default :
textVal = String.valueOf(val);
}
rowMap.put(zdCell,textVal);
}else{
textVal = null;
}
paramCell.setCellValue(textVal);//写进excel对象
paramCell.setCellStyle(cellParamStyle);
zdCell++;
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
resMaplist.add(rowMap);
}
//创建合计行
int lastRow = sheet.getLastRowNum() + 1; //从0开始
Object[] lastList = {"合计","","",businessNum,sumMoney,cashCount,savingCount,courseCount,sumElse};
row = sheet.createRow(lastRow);
row.setHeightInPoints(25);//行高
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
for(int i = 0 ; i <lastList.length; i++) {
HSSFCell celllast = row.createCell(i);
celllast.setCellValue(lastList[i].toString());//给单元格赋值
celllast.setCellStyle(cellParamStyle);//设置样式
}
//开始合并
CellRangeAddress region = null; // 加_ 防止变量名重复
// 一列列遍历 就是需要先循环列 然后再 判断当前列与上一列是否重复 需要定义一个变量来记录这重复连续出现的长度
// 由于是先比较完一列再比较下一列 所以外面的循环是先遍历map(rowMap) 所以就要先得到第一个map值 也作为上一行
Map<Integer,String> first_map = resMaplist.get(0);// key 存放的是列顺序下标
Map<Integer,String> curr_map = null; // 当前行的map
int num = 0;
List<Integer> index_ = new ArrayList<>();
for(int x = 0 ; x < first_map.size() ; x++){
int len = 0;
int y_len = resMaplist.size();// 得到总列数
if(x > 1){
break;
}
for(int y = 1 ; y < y_len ; y ++){
first_map = resMaplist.get(y - 1);
curr_map = resMaplist.get(y);// 得到当前行
// 如果当前行的值与上一行的值重复就记录长度
if(x == 1 && num < index_.size() && index_.get(num) == y){
num++;
if(len != 0){
// 表示 之前有要合并的
int end = y - 1 + 3;//3是第三行的意思
region = new CellRangeAddress(end - len ,end,x,x);
sheet.addMergedRegion(region);
len = 0;
}
continue;
}
// 当前行的值与上一行的值重复
if(curr_map.get(x).toString().equals(first_map.get(x).toString())){
len++;
}else {//临界值
// 如果不为空 先判断是不是第一行的值 第一行不为空 就不记录合并
if(len != 0){
int end_len = y - 1 + 3 ; // 表示 之前有要合并的
region = new CellRangeAddress(end_len - len,end_len,x,x);// 结束的y坐标 减去 连续空长度
sheet.addMergedRegion(region);
if(x==0){
index_.add(y);
}
len = 0;
}
}
}
// 如果最后一行是需要合并的 就有可能 len 还有 长度
if(len != 0){
int end_len = y_len - 1 + 3;
region = new CellRangeAddress(end_len - len,end_len,x,x);
sheet.addMergedRegion(region);
}
}
}catch(Throwable t){
t.printStackTrace();
}
}
/**
*
* @Description: TODO(合并单元格后边框不显示问题)
* @param @param sheet
* @param @param region
* @param @param cs
* @throws
*/
public static void setRegionStyle(HSSFSheet sheet, CellRangeAddress region, HSSFCellStyle cs) {
for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
HSSFRow row = (HSSFRow) CellUtil.getRow(i, sheet);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
HSSFCell cell = (HSSFCell) CellUtil.getCell(row, (short) j);
cell.setCellStyle(cs);
}
}
}
}
前端JS
//好像只能用表单提交 所以我这里使用的js模拟表单提交 这里使用的layui弹窗
layer.confirm('请确认是否导出'+productName+'：'+title+" 的明细数据", function(index){
var url = basePath + "DeductInfo/exportExcelDay";
var form = $("<form>");
form.attr('style','display:none');
form.attr('target','');
form.attr('method','post');
form.attr('action',url);
var input1 = $('<input>');
input1.attr('type','hidden');
input1.attr('name','filter');
input1.attr('value',filter);
$('body').append(form);
form.append(input1);
form.submit();
setTimeout(function(){
layer.close(index);
}, 3000);
});
pom.xml
<!-- poi操作excel -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.8</version>
</dependency>
效果图
1340
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
