常用的注解
-
@Transient：一个从数据库中反射实体类中没有的字段可以用此注解
例如：
@Transient
private BigDecimal weight;//weight 数据库中没有此字段 -
@DataDict：
例如：
//民族 //数据有此字段
private String nationCode;
//民族 名称
@DataDict(source = “nationCode”, dict = “T3304”)
@Transient
private String nationName; -
@DataDictClass:将查询出来的list按某个类进行装换
例如：service层
@DataDictClass(model = Report.class)
public Map<String, Object> getUserAppraises(Map<String, Object> whereMap, String usercode) {
whereMap.put("orderBy", "t.CREATE_DATE desc");
whereMap.put("u.P_ID01", usercode);
String nameSpace = "com.kingosoft.web.dao.cem.AppraiseDAO";
String baseKey = "getUserAppraises";
String countKey = "getUserAppraisesCount";
Map<String, Object> dataMap = getWhereFromMap.getOracleListByPage(nameSpace, baseKey, countKey, whereMap);
return dataMap;
}
- @Api：
@Api(value="AppraiseController",description="课程评价相关接口")
- @ApiOperation
@ApiOperation(value = "查询用户可以评价的课程列表", notes = "查询用户可以评价的课程列表")
- @DateTimeFormat(pattern = “yyyy-MM-dd”)
@JsonFormat(pattern=“yyyy-MM-dd”,timezone=“GMT+8”)
例如：实体类中添加注解
/**
* 出生日期
*/
@DateTimeFormat(pattern = "yyyy-MM-dd")
@JsonFormat(pattern="yyyy-MM-dd",timezone="GMT+8")
private Date csrq;
Vue项目相关的操作
- 拖动排序：可以通过vue.draggable 进行拖动排序
- 页面保存值：
sessionStorage（临时存储） ：为每一个数据源维持一个存储区域，在浏览器打开期间存在，包括页面重新加载
localStorage（长期存储） ：与 sessionStorage 一样，但是浏览器关闭后，数据依然会一直存在
对表单默认提供一个空值即可
4、数据双向绑定-单击记录回填时，避免修改input的值改变表格中相应的字段的值的处理方式：
this.formData = JSON.parse(JSON.stringify(val))//val表示单击后当前记录的数据
页面
- width计算：width: calc(100% - 100px);
Java
java8 stream().map().collect()用法
//查询动态显示的列
List<Datacolumn> columnAndNames = datacolumnService.getColumnAndNamesByTableId(tableId);
List<String> columns = columnAndNames.stream().map(Datacolumn::getColumnid).collect(Collectors.toList());
String columnStr = String.join(",",columns);//列名
url解析并获取值
String menucode="",bylwgcxbs="";
try {
String Refer=Tools.getParamValue(filterParam, "Refer");
String params = Refer.substring(Refer.indexOf("?") + 1, Refer.length());
Map<String, String> split = Splitter.on("&").withKeyValueSeparator("=").split(params);
menucode=split.get("menucode");
}catch (Exception e){menucode="";}
if(menucode.equals("5302040607")){//通过menucode 区分过程性 还是非过程性 毕业论文使用的功能
//5302040101 过程性使用
bylwgcxbs="yes";
}
oracle
合并行
写法一：
select xh,listagg(xn||xq_m,',') within group(order by xn) from t_jw_studentstatus group by xh
写法二：
select xh,to_char(rtrim(xmlagg(xmlparse(content xn||xq_m||',' wellformed) order by xn).getclobval(), ',')) as xnxq_m from t_jw_studentstatus group by xh
分组排序
select t.*, row_number() over(partition by t.xn,tch.ssbm_m order by t.xn,tch.ssbm_m,t.kpdj,tch.gh)bmpm
from xxxxx t
创建表
--表结构
declare num number;
begin
select count(1) into num from user_tab_columns where table_name = upper('T_XT_QUERYLOG');
if num = 0 then
execute immediate 'create table T_XT_QUERYLOG
(
querylogid VARCHAR2(50) not null,
loginid VARCHAR2(100) not null,
username VARCHAR2(100) not null,
rolename VARCHAR2(1000) not null,
optime DATE not null,
opip VARCHAR2(100) not null,
menucode VARCHAR2(100) not null,
opcontent VARCHAR2(4000) not null,
optype VARCHAR2(20) not null,
constraint PRI_T_XT_QUERYLOG primary key (QUERYLOGID)
)';
end if;
end;
/
添加字段
declare num number;
begin
select count(1) into num from user_tab_columns where table_name = upper('T_CONSTRUCTBASEINFO')
and column_name = upper('jdyhdm');
if num = 0 then
execute immediate 'alter table T_CONSTRUCTBASEINFO add jdyhdm VARCHAR2(100)';
end if;
end;
/
设置不能为空的字段
declare num number;
begin
select count(1) into num from user_tab_columns where table_name = upper('T_CONSTRUCTBASEINFO')
and column_name = upper('jdyhdm') and nullable<>'N';
if num = 1 then
execute immediate 'alter table T_CONSTRUCTBASEINFO modify jdyhdm not null';
end if;
end;
/
备份表
declare isExisted number;
begin
select count(1) into isExisted from user_tables where table_name = upper('t_xs_student230213');
if isExisted = 0 then
execute immediate 'create table t_xs_student230213 as select * from t_xs_student ';
end if;
end;
/
存数字查询 主要是isnumeric函数
select max(t.gh) from t_jw_teacher t where isnumeric(t.gh)=1
迁移数据
exp 数据库用户名/密码 服务器信息 file=e:\.dmp tables=表名
数据迁移操作
SqlServer 到 oracle
--学分制收费-退费 T_JW_XFZSF_TUIFEI (教务管理V11表结构：t_xj_stud_jf_tuifei)
delete from openquery(LINK_DYZY,'select * from JWGL_DYZY.T_JW_XFZSF_TUIFEI')
go
insert into openquery(LINK_DYZY,'select xn,xh,cs,itemname,sfbzcount,tfcount,tftime,inputpeople,bz,fs_flag,printno,tfbz from JWGL_DYZY.T_JW_XFZSF_TUIFEI')
select top 5000 t.xn,t.xh,t.cs,t.itemname,sfbzcount,tfcount,tftime,inputpeople,bz,fs_flag,printno,tfbz from t_xj_stud_jf_tuifei t
left join(
select xn,xh,cs,itemname from openquery(LINK_DYZY,'select xn,xh,cs,itemname from JWGL_DYZY.T_JW_XFZSF_TUIFEI')
)s on s.xn=t.xn and s.xh=t.xh and s.cs=t.cs and s.itemname=t.itemname
where s.xn is null
go
oracle导出数据
exp JWGL_SDNZ/sa@jwgl file=C:/Users/Administrator/Desktop/jwgl_xfzsf210301.dmp tables=(t_jw_xfzsf_sfxm)
出现这种情况时使用下方代码进行检索
select b.dm,b.gh,b.xm, a.* from t_bylw_lrbylwtm_bylwtm a
inner join t_jw_teacher b on ','||a.zdjs ||',' like '%,'||b.dm||',%'
oracle 表数据误删除 闪回法
select * from t_rl_bm_department as of timestamp to_timestamp('2021-11-24 17:55:00', 'yyyy-mm-dd hh24:mi:ss')
redis及本地缓存操作
@RequestMapping("/simulatepush")
//@RequiresPermissions("generator:authverificationhistory:simulatepush")
public R simulatepush(HttpServletRequest request) {
String token=request.getHeader("token");
String key="generator:authverificationhistory:simulatepush:"+token;
if(StringUtils.isNotEmpty(cacheUtils.get(key))){//重复
return R.error("请求重复操作");
}else{//正常 过期时间内 只会第一次进来 才会缓存
//将 token 缓存起来
cacheUtils.set(key,token,2);//expire缓存时间
}
//获取当前考试计划
String examplan=getExamplan().getExamplan();
authVerificationHistoryService.simulatepush(examplan);
return R.ok();
}
package io.pixel.common.utils;
import java.util.List;
import java.util.Map;
public interface CacheUtils {
void set(String key, Object value);
void set(String key, Object value, long expire);
Long incr(String key);
Long incr(String key, long timeout);
Long incrBy(String key, long value);
Long incrBy(String key, long value, long timeout);
String get(String key);
<T> T get(String key, Class<T> clazz);
void delete(String key);
void hset(String key, Object hashKey, Object value);
Map<Object, Object> hgetall(String key);
List<Object> hvals(String key);
}
package io.pixel.common.utils;
import cn.hutool.cache.CacheUtil;
import cn.hutool.cache.impl.TimedCache;
import com.google.gson.Gson;
import org.apache.commons.lang.StringUtils;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Component;
import java.util.*;
@Component
@ConditionalOnProperty(name = "pixel.oss_type", havingValue = "2")
public class MyCacheUtils implements CacheUtils {
private static final Object incrLock = new Object();
private static final Object hashLock = new Object();
private final TimedCache<String, Object> timedCache;
private final Gson gson = new Gson();
private final Long DEFAULT_EXPIRE = 24 * 60 * 60L;
public MyCacheUtils() {
timedCache = CacheUtil.newTimedCache(DEFAULT_EXPIRE * 1000);
}
@Override
public void set(String key, Object value) {
set(key, value, DEFAULT_EXPIRE);
}
@Override
public void set(String key, Object value, long expire) {
timedCache.put(key, toJson(value), expire * 1000);
}
@Override
public Long incr(String key) {
return incr(key, DEFAULT_EXPIRE);
}
@Override
public Long incr(String key, long timeout) {
synchronized (incrLock) {
String strValue = get(key);
if (StringUtils.isBlank(strValue)) {
set(key, 1L);
// 与原RedisUtils的返回值保持一致，返回自增前的值
return 0L;
}
Long longValue = Long.valueOf(strValue);
longValue++;
set(key, longValue, timeout);
// 与原RedisUtils的返回值保持一致，返回自增前的值
longValue--;
return longValue;
}
}
@Override
public Long incrBy(String key, long value) {
return incrBy(key, value, DEFAULT_EXPIRE);
}
@Override
public Long incrBy(String key, long value, long timeout) {
synchronized (incrLock) {
String strValue = get(key);
if (StringUtils.isBlank(strValue)) {
set(key, value);
// 与原RedisUtils的返回值保持一致，返回自增前的值
return 0L;
}
Long longValue = Long.valueOf(strValue);
longValue += value;
set(key, longValue, timeout);
// 与原RedisUtils的返回值保持一致，返回自增前的值
longValue -= value;
return longValue;
}
}
@Override
public String get(String key) {
return (String) timedCache.get(key);
}
@Override
public <T> T get(String key, Class<T> clazz) {
return fromJson(get(key), clazz);
}
@Override
public void delete(String key) {
timedCache.remove(key);
}
@Override
public void hset(String key, Object hashKey, Object value) {
synchronized (hashLock) {
Map<Object, Object> map = (Map<Object, Object>) timedCache.get(key);
if (map == null) {
map = new HashMap<>();
map.put(hashKey, value);
timedCache.put(key, map);
} else {
map.put(hashKey, value);
timedCache.put(key, map);
}
}
}
@Override
public Map<Object, Object> hgetall(String key) {
return (Map<Object, Object>) timedCache.get(key);
}
@Override
public List<Object> hvals(String key) {
Map<Object, Object> map = (Map<Object, Object>) timedCache.get(key);
if (map == null) {
return new ArrayList<>();
} else {
return Arrays.asList(map.values().toArray());
}
}
/**
* Object转成JSON数据
*/
private String toJson(Object object) {
if (object instanceof Integer || object instanceof Long || object instanceof Float ||
object instanceof Double || object instanceof Boolean || object instanceof String) {
return String.valueOf(object);
}
return gson.toJson(object);
}
/**
* JSON数据，转成Object
*/
private <T> T fromJson(String json, Class<T> clazz) {
if (clazz == String.class) {
return (T) json;
}
return gson.fromJson(json, clazz);
}
}
/**
* Copyright (c) 2016-2019 All rights reserved.
*
* https://www.pixel.io
*
* 版权所有，侵权必究！
*/
package io.pixel.common.utils;
import com.google.gson.Gson;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.data.redis.core.*;
import org.springframework.data.redis.support.atomic.RedisAtomicLong;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;
/**
* Redis工具类
*
* @author Mark
*/
@Component
@ConditionalOnProperty(name = "pixel.oss_type", havingValue = "1")
public class RedisUtils implements CacheUtils {
@Autowired
private RedisTemplate<String, Object> redisTemplate;
@Autowired
private ValueOperations<String, String> valueOperations;
/** 默认过期时长，单位：秒 */
public final static long DEFAULT_EXPIRE = 60 * 60 * 24;
/** 不设置过期时长 */
public final static long NOT_EXPIRE = -1;
private final static Gson gson = new Gson();
public void set(String key, Object value, long expire){
valueOperations.set(key, toJson(value));
if(expire != NOT_EXPIRE){
redisTemplate.expire(key, expire, TimeUnit.SECONDS);
}
// if(expire != NOT_EXPIRE){
// long hour = expire/1000/3600 ;
// if(hour<24) {
// valueOperations.set(key, toJson(value), expire, TimeUnit.MILLISECONDS );
// }else{
// valueOperations.set(key, toJson(value), hour, TimeUnit.HOURS );
// }
// }else{
// valueOperations.set(key, toJson(value));
// }
}
public Long incrBy(String key ,long value){
return incrBy(key ,value ,DEFAULT_EXPIRE);
// return valueOperations.increment(key, value);
// RedisConnection rd= redisTemplate.getConnectionFactory().getConnection();
// Long v = rd.incrBy(key.getBytes(), value);
// rd.close();
// return v;
}
public Long incrBy(String key ,long value ,long timeout){
RedisAtomicLong entityIdCounter = new RedisAtomicLong(key, redisTemplate.getConnectionFactory());
Long increment = entityIdCounter.getAndAdd(value);
if ((null == increment || increment.longValue() == 0) && timeout > 0) {//初始设置过期时间
entityIdCounter.expire(timeout, TimeUnit.SECONDS);
}
return increment;
}
public Long incr(String key){
return incr(key ,DEFAULT_EXPIRE);
// RedisConnection rd= redisTemplate.getConnectionFactory().getConnection();
// Long v = rd.incr(key.getBytes());
// rd.close();
// return v;
}
public Long incr(String key ,long timeout){
RedisAtomicLong entityIdCounter = new RedisAtomicLong(key, redisTemplate.getConnectionFactory());
Long increment = entityIdCounter.getAndIncrement();
if ((null == increment || increment.longValue() == 0) && timeout > 0) {//初始设置过期时间
entityIdCounter.expire(timeout, TimeUnit.SECONDS);
}
return increment;
}
public void set(String key, Object value){
set(key, value, DEFAULT_EXPIRE);
}
public void setNoEXPIRE(String key, Object value){
set(key, value);
}
public <T> T get(String key, Class<T> clazz, long expire) {
String value = valueOperations.get(key);
if(expire != NOT_EXPIRE){
redisTemplate.expire(key, expire, TimeUnit.SECONDS);
}
return value == null ? null : fromJson(value, clazz);
}
public <T> T get(String key, Class<T> clazz) {
return get(key, clazz, NOT_EXPIRE);
}
public String get(String key, long expire) {
String value = valueOperations.get(key);
if(expire != NOT_EXPIRE){
redisTemplate.expire(key, expire, TimeUnit.SECONDS);
}
return value;
}
public String get(String key) {
return get(key, NOT_EXPIRE);
}
public void delete(String key) {
redisTemplate.delete(key);
}
@Override
public void hset(String key, Object hashKey, Object value) {
redisTemplate.opsForHash().put(key, hashKey, value);
}
@Override
public Map<Object, Object> hgetall(String key) {
return redisTemplate.opsForHash().entries(key);
}
@Override
public List<Object> hvals(String key) {
return redisTemplate.opsForHash().values(key);
}
/**
* Object转成JSON数据
*/
private String toJson(Object object){
if(object instanceof Integer || object instanceof Long || object instanceof Float ||
object instanceof Double || object instanceof Boolean || object instanceof String){
return String.valueOf(object);
}
return gson.toJson(object);
}
/**
* JSON数据，转成Object
*/
private <T> T fromJson(String json, Class<T> clazz){
if(clazz == String.class) {
return (T) json;
}
return gson.fromJson(json, clazz);
}
}
redis自定义注解
package io.renren.common.annotation;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 本注解实现采用aop,具体用法只要将注解放到方法上面即可
* 具体实现是 redis + 定时任务
* 在被注解方法返回数据之后，在aop内会把返回值放入 redis ,并用 定时任务定时刷新缓存数据
*
*/
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface RedisTaskCache {
/**
* 自定义名称
* redis key的名称
*/
String cacheName() default "";
/**
* 缓存时间
* redis 缓存时间
*/
String cacheTime() default "300";
/**
* 注意 缓存刷新时间一定要大于 cacheTime 参数设置的时间，否则会导致缓存失效
*
* 系统参数key
* 指定缓存刷新时间
*
*/
String paramKey();
}
package io.renren.common.aspect;
import io.renren.common.annotation.RedisTaskCache;
import io.renren.common.redis.RedisUtils;
import io.renren.common.utils.StringUtils;
import io.renren.modules.sys.service.SysParamsService;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.redisson.Redisson;
import org.redisson.api.RLock;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
import java.util.concurrent.*;
import java.util.stream.Collectors;
@Slf4j
@Component
@Aspect
public class RedisTaskCacheAspect {
private final ScheduledExecutorService threadPool = Executors.newScheduledThreadPool(5);
@Autowired
private SysParamsService sysParamsService;
@Autowired
private RedisUtils redisUtils;
@Autowired
private Redisson redisson;
@Pointcut("@annotation(io.renren.common.annotation.RedisTaskCache)")
public void logPointCut() {
}
@Around("logPointCut()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
Object[] args = joinPoint.getArgs();
List<String> objectList = Arrays.stream(args).map(Object::toString).collect(Collectors.toList());
String argString = String.join("_", objectList);
//获取注解信息
MethodSignature signature = (MethodSignature) joinPoint.getSignature();
Class<?> aClass = joinPoint.getTarget().getClass();
Method method = aClass.getDeclaredMethod(signature.getName(), signature.getParameterTypes());
RedisTaskCache annotation = method.getAnnotation(RedisTaskCache.class);
String cacheName = annotation.cacheName();
String redisKey = getRedisKey(joinPoint, argString, aClass, cacheName);
long cacheTime = Long.parseLong(annotation.cacheTime());
Object value = redisUtils.get(redisKey);
if (Objects.isNull(value)) {
//获取锁对象
RLock redissonLock = redisson.getLock("RedisLock:" +
redisKey + Thread.currentThread().getStackTrace()[1].getMethodName());
//加锁
redissonLock.lock();
try {
value = redisUtils.get(redisKey);
if (Objects.isNull(value)) {
value = joinPoint.proceed();
redisUtils.set(redisKey, value, cacheTime);
//执行定时任务
threadPool.schedule(task(joinPoint, redisKey, cacheTime, annotation.paramKey()),
getFlushTime(annotation.paramKey()), TimeUnit.SECONDS);
}
} finally {
if (redissonLock.isLocked() && redissonLock.isHeldByCurrentThread()) {
redissonLock.unlock();
}
}
}
//锁续命
redisUtils.expire(redisKey, cacheTime);
return value;
}
private Runnable task(ProceedingJoinPoint joinPoint, String redisKey, Long cacheTime, String paramKey) {
return () -> {
if (!Objects.isNull(redisUtils.get(redisKey))) {
Object result = null;
try {
result = joinPoint.proceed();
} catch (Throwable e) {
throw new RuntimeException(e);
}
redisUtils.set(redisKey, result, cacheTime);
threadPool.schedule(task(joinPoint, redisKey, cacheTime, paramKey),
getFlushTime(paramKey), TimeUnit.SECONDS);
}
};
}
/**
* 获取刷新时间，如果没有配置就默认30秒
*/
private long getFlushTime(String paramKey) {
if (StringUtils.isBlank(paramKey)) {
return 30L;
}
String value = Optional.ofNullable(sysParamsService.getValue(paramKey)).orElse("30");
long flushTime = Long.parseLong(value);
if (flushTime < 30) {
return flushTime;
} else {
return flushTime - ThreadLocalRandom.current().nextInt(5);
}
}
private String getRedisKey(ProceedingJoinPoint joinPoint, String argString, Class<?> aClass, String cacheName) {
String redisKey = null;
if (StringUtils.isNotBlank(cacheName)) {
redisKey = "RedisTaskCache:" + cacheName + ":" + argString;
} else {
String methodName = joinPoint.getSignature().getName();
redisKey = "RedisTaskCache:" + aClass + ":" + methodName + ":" + argString;
}
return redisKey;
}
}
@RedisTaskCache(flashInterval = "30",cacheName = "pingjuanyuan",durationTime = "600")
自定义excel导出
public void export(HttpServletResponse response, Map<String, Object> params){
XSSFWorkbook workbook = new XSSFWorkbook(); // 新建工作簿对象
XSSFSheet sheet = workbook.createSheet("List");// 创建sheet
List<Map<String,Object>> qulist=baseDao.getQuList(params);
int rowNum=0;
Row row = sheet.createRow(rowNum);// 创建第一行对象,设置表标题
Cell cell;
cell = row.createCell(0);
cell.setCellValue("试题简称");
cell = row.createCell(1);
cell.setCellValue("考生人数");
cell = row.createCell(2);
cell.setCellValue("分点号");
cell = row.createCell(3);
cell.setCellValue("评分细则");
cell = row.createCell(4);
cell.setCellValue("全市平均分");
int cellNum = 5;
if(qulist!=null && qulist.size()>0){
for(int i=0;i<qulist.size();i++){
Map<String,Object> item=qulist.get(i);
cell = row.createCell(cellNum);
cell.setCellValue(item.get("MC").toString());
cellNum++;
}
}
int rows = 1;
List<Map<String,Object>> list=dealList_new(params);
for (int x=0;x<list.size();x++){//遍历数据插入excel中
Map<String,Object> item=list.get(x);
row = sheet.createRow(rows);
row.createCell(0).setCellValue(item.get("tmbt").toString()+"");
row.createCell(1).setCellValue(item.get("zrs").toString()+"");
row.createCell(2).setCellValue(item.get("fdh").toString()+"");
row.createCell(3).setCellValue(item.get("pfxz").toString()+"");
row.createCell(4).setCellValue(item.get("qsavg").toString()+"");
int xxx=5;
if(qulist!=null && qulist.size()>0){
for(int i=0;i<qulist.size();i++){
Map<String,Object> xitem=qulist.get(i);
String qudm=xitem.get("DM")!=null?xitem.get("DM").toString():"";
row.createCell(xxx).setCellValue(item.get("DFSTR"+qudm).toString()+"");
xxx++;
}
}
rows++;
}
String fileName = "题目得分点平均分";
OutputStream out =null;
try {
try {
out = response.getOutputStream();
response.reset();
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
}finally {
out.flush();
out.close();
}
}catch (Exception e){
e.printStackTrace();
}
}
自定义导出 封装后的
/**
* Copyright (c) 2018 人人开源 All rights reserved.
* <p>
* https://www.renren.io
* <p>
* 版权所有，侵权必究！
*/
package io.pixel.common.utils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.bind.annotation.RequestParam;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;
/**
* excel工具类
*
* @author Mark sunlightcs@gmail.com
*/
public class ExcelUtils {
/**
* 自定义excel导出
*
* @param list 数据源
* @param colname 表头名称
* @param colkey 数据key值
* @param response
* @param filename 文件名称
* @param sfyxh 是否显示序号
*/
public static void zdyMapdataexcel(List<Map<String, Object>> list, String[] colname, String[] colkey, HttpServletResponse response, String filename, String sfyxh) {
//sfyxh 是否需要序号
//1 要 colname 第一个位置为 "序号" colkey 第一个位置为 "" 例如 colname={"序号","colname1"} colkey={"","colkey1"}
if (StringUtils.isEmpty(filename)) {
filename = DateUtils.format(new Date());
}
XSSFWorkbook workbook = new XSSFWorkbook(); // 新建工作簿对象
XSSFSheet sheet = workbook.createSheet("List");// 创建sheet
int rowNum = 0;
Row row = sheet.createRow(rowNum);// 创建第一行对象,设置表标题
Cell cell;
int colnamesize = colname.length;
int colkeysize = colkey.length;
for (int i = 0; i < colnamesize; i++) {
cell = row.createCell(i);
cell.setCellValue(colname[i]);
}
int rows = 1;
for (int x = 0; x < list.size(); x++) {//遍历数据插入excel中
Map<String, Object> item = list.get(x);
row = sheet.createRow(rows);
for (int k = 0; k < colkeysize; k++) {
if (k == 0 && sfyxh.equals("1")) {//序号
row.createCell(k).setCellValue(k + 1);
} else {
row.createCell(k).setCellValue(item.get(colkey[k]) + "");
}
}
rows++;
}
OutputStream out = null;
try {
try {
out = response.getOutputStream();
response.reset();
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8") + ".xls");
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.flush();
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 自定义excel导出 带样式
*
* @param list 数据源
* @param colname 表头名称 可带其他的参数 使用@符合进行拼接 标题名称@列宽度系数(1-100...) 例如 测试名称@200 “测试名称”为表头名称 “200”为宽度值
* @param colkey 数据key值
* @param response
* @param filename 文件名称
* @param sfyxh 是否显示序号
*/
public static void zdyMapdataexcel_xdys(List<Map<String, Object>> list, String[] colname, String[] colkey, HttpServletResponse response, String filename, String sfyxh) {
int w = 256;//单位/列宽
int wb = 15;//列宽系数
int rowhtable = 650;
int rowhdata = 450;
//sfyxh 是否需要序号
//1 要 colname 第一个位置为 "序号" colkey 第一个位置为 "" 例如 colname={"序号","colname1"} colkey={"","colkey1"}
if (StringUtils.isEmpty(filename)) {
filename = DateUtils.format(new Date());
}
XSSFWorkbook workbook = new XSSFWorkbook(); // 新建工作簿对象
XSSFSheet sheet = workbook.createSheet("List");// 创建sheet
//样式
XSSFCellStyle titleStyle = createTitleCellStyle(workbook);
XSSFCellStyle headerStyle = createHeadCellStyle(workbook);
XSSFCellStyle contentStyle = createContentCellStyle(workbook);
int colnamesize = colname.length;
int colkeysize = colkey.length;
int rows = 0;
//表头
XSSFRow row = sheet.createRow(rows);// 创建第一行对象,设置表标题
row.setHeight((short) rowhtable);
for (int i = 0; i < colnamesize; i++) {
String[] dqcolinfos = colname[i] != null ? colname[i].split("@", -1) : new String[]{"表头列" + i};
int dqcolwb = wb;//当前列宽系数
try {
dqcolwb = Integer.parseInt(dqcolinfos[1] != null ? dqcolinfos[1] : wb + "");
} catch (Exception e) {
dqcolwb = wb;
}
sheet.setColumnWidth(i, w * dqcolwb); //设置列宽度
XSSFCell cell = row.createCell(i);
cell.setCellValue(dqcolinfos[0]);
cell.setCellStyle(headerStyle);
}
//数据填充
rows++;
for (int x = 0; x < list.size(); x++) {//遍历数据插入excel中
Map<String, Object> item = list.get(x);
XSSFRow rowdata = sheet.createRow(rows);
rowdata.setHeight((short) rowhdata);//设置数据行高
for (int k = 0; k < colkeysize; k++) {
XSSFCell coldata = rowdata.createCell(k);
coldata.setCellStyle(contentStyle);
if (k == 0 && sfyxh.equals("1")) {//序号
coldata.setCellValue(k + 1);
} else {
coldata.setCellValue(item.get(colkey[k]) + "");
}
}
rows++;
}
OutputStream out = null;
try {
try {
out = response.getOutputStream();
response.reset();
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8") + ".xls");
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.flush();
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 多层次自定义导出 带样式 严格按要求写 表头可合并 但是 数据不进行合并
*
* @param list 数据源 如果是多层 则 需要包括 "列名(数据key值)+list"集合
* @param colname 表头名称 可无序 没格列名称 可带其他的参数 使用@符合进行拼接 标题名称@列宽度系数(1-100...) 例如 测试名称@200 “测试名称”为表头名称 “200”为宽度值 ...
* <p>
* 目前参数格式：标题名称(必填)@列宽度系数(1-100)(非必填)@行合并数(非必填)@列合并数(非必填)@colcc(必填)...
* <p>
* 例如 7日上午第1场语文@100@0@5@4-0
* 多层级 用对应层级数对应的集合进行
* 结构类似二维数组 例如 第1行第5列 需要如下结构 列与行 下标都从0开始
* 1 行 标题名称(7日上午第1场语文)
* 2 行 编排人数 已验证人数 验证数据推送人数 身份验证占已推送比例 上报完成
* 对应 colname=标题名称 colcc=主行值-主列值-当前行值-当前列值
* colname:7日上午第1场语文@100@0@5 colcc:0-4-0-0
* colname:编排人数@10@0@0 colcc:1-4-1-0 colname:已验证人数@10@0@0 colcc:1-4-1-1 colname:验证数据推送人数@10@0@0 colcc:1-4-1-2
* 如果还有第3行 层级 如下
* 1 行 标题名称(7日上午第1场语文)
* 2 行 编排人数 已验证人数 验证数据推送人数 身份验证占已推送比例 上报完成
* 3行 编排1 编排2 已验证人数1 已验证人数2
* 对应 colname=标题名称 colcc=主行值-主列值-当前行值-当前列值
* colname:7日上午第1场语文@100@0@5 colcc:0-4-0-0
* colname:编排人数@10@0@0 colcc:1-4-1-0 colname:已验证人数@10@0@0 colcc:1-4-1-1 colname:验证数据推送人数@10@0@0 colcc:1-4-1-2
* colname:编排人数1@10@0@0 colcc:2-4-2-0 colname:编排人数2@10@0@0 colcc:2-4-2-1 colname:已验证人数1@10@0@0 colcc:2-4-2-2 colname:已验证人数2@10@0@0 colcc:2-4-2-3
* 后续 多层级 一样
* @param colkey 数据key值 和 colname 对应结构顺序 保持一致
* 表头按上面所描述的 弄 对应列的key如下
* <p>
* 目前参数格式： 标题名称对应key(必填(除序号(第一个)))@colcc(必填)@子数据集合名称(非必填)@判断的key值(主)@判断的key值(子)@子数据集合遍历位置...
* <p>
* 数据集合名称 为空时直接主数据源取值
* 判断的key值=examplan|unit_id|exam_session_id 多个用|隔开
* 例如：
* 1 行 标题名称(7日上午第1场语文)
* 2 行 编排人数 已验证人数 验证数据推送人数 身份验证占已推送比例 上报完成
* colname:7日上午第1场语文@100@0@5 colcc:0-4-0-0
* colname:编排人数@10@0@0 colcc:1-4-1-0 colname:已验证人数@10@0@0 colcc:1-4-1-1 colname:验证数据推送人数@10@0@0 colcc:1-4-1-2
* 对应的key 对应最后面需要的列
* bprs@1-4-1-0@ccdatalist@examplan|unit_id|exam_session_id@examplan|unit_id|exam_session_id@0 yyzrs@1-4-1-1@ccdatalist@examplan|unit_id|exam_session_id@examplan|unit_id|exam_session_id@0 yzsjtsrs@1-4-1-1@ccdatalist@examplan|unit_id|exam_session_id@examplan|unit_id|exam_session_id@0
* @param response
* @param filename 文件名称
* @param sfyxh 是否显示序号
*/
public static void zdyMapdataexcel_dcc(List<Map<String, Object>> list, String[] colname, String[] colkey, HttpServletResponse response, String filename, String sfyxh) {
/**
* 合并的核心代码
* // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
* CellRangeAddress range = new CellRangeAddress(0, 0, 0, 5);
*/
int w = 256;//单位/列宽
int wb = 15;//列宽系数
int hbrows = 0;//行合并数
int hbcols = 1;//列合并数
int rowhtable = 650;
int rowhdata = 450;
//sfyxh 是否需要序号
//1 要 colname 第一个位置为 "序号" colkey 第一个位置为 "" 例如 colname={"序号","colname1"} colkey={"","colkey1"}
if (StringUtils.isEmpty(filename)) {
filename = DateUtils.format(new Date());
}
XSSFWorkbook workbook = new XSSFWorkbook(); // 新建工作簿对象
XSSFSheet sheet = workbook.createSheet("List");// 创建sheet
//样式
//XSSFCellStyle titleStyle = createTitleCellStyle(workbook);
//XSSFCellStyle headerStyle = createHeadCellStyle(workbook);
//XSSFCellStyle contentStyle = createContentCellStyle(workbook);
//居中
XSSFCellStyle centerstyle = workbook.createCellStyle();
centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//初始行
int inith = 2;
for (int ir = 0; ir < inith; ir++) {
XSSFRow xrow = sheet.getRow(ir) != null ? sheet.getRow(ir) : sheet.createRow(ir);
}
int rows = inith - 1;
//表头
int colnamesize = colname.length;
for (int cn = 0; cn < colnamesize; cn++) {
//获取当前的列名称信息并转换成数组
String[] dqcolnameinfoarr = colname[cn].split("@", -1);
String dqcolemc = dqcolnameinfoarr[0];//当前列名称
int dqcolwb = wb;//列宽度系数
try {
dqcolwb = Integer.parseInt(dqcolnameinfoarr[1]);
} catch (Exception e) {
dqcolwb = wb;
}
int dqhbrows = hbrows;//当前合并行数
try {
dqhbrows = Integer.parseInt(dqcolnameinfoarr[2]);
} catch (Exception e) {
dqhbrows = hbrows;
}
int dqhbcols = hbcols;//当前合并列数
try {
dqhbcols = Integer.parseInt(dqcolnameinfoarr[3]);
} catch (Exception e) {
dqhbcols = hbcols;
}
String dqcolcc = dqcolnameinfoarr[4];//当前colcc值 对应上面相应描述
String[] dqcolccarr = dqcolcc.split("-", -1);
int dqcczrow = Integer.parseInt(dqcolccarr[0]);//当前元素所在的主行
int dqcczcol = Integer.parseInt(dqcolccarr[1]);//当前元素所在的主列
int dqccrow = Integer.parseInt(dqcolccarr[2]);//当前元素所在的行
int dqcccol = Integer.parseInt(dqcolccarr[3]);//当前元素所在的列
//当前元素 在全局中所在的 列
int dqyyqjcol = dqcczcol + dqcccol;
int dqyyqjrow = dqccrow;
//创建几行几列的单元格
XSSFRow xrow = sheet.getRow(dqyyqjrow) != null ? sheet.getRow(dqyyqjrow) : sheet.createRow(dqyyqjrow);
XSSFCell xcoll = xrow.createCell(dqyyqjcol);
sheet.setColumnWidth(dqyyqjcol, w * dqcolwb); //设置列宽度
xrow.setHeight((short) rowhtable);
xcoll.setCellValue(dqcolemc);
xcoll.setCellStyle(centerstyle);
//合并 行 列 如果填写值是0 相当于不合并
// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
int hb_qs_row = dqyyqjrow;
int hb_js_row = dqyyqjrow + dqhbrows;
int hb_qs_col = dqyyqjcol;
int hb_js_col = dqyyqjcol + (dqhbcols - 1);
CellRangeAddress range = new CellRangeAddress(hb_qs_row < 0 ? 0 : hb_qs_row, hb_js_row < 0 ? 0 : hb_js_row, hb_qs_col < 0 ? 0 : hb_qs_col, hb_js_col < 0 ? 0 : hb_js_col);
sheet.addMergedRegion(range);
}
rows++;
//数据填充
int colkeysize = colkey.length;
for (int x = 0; x < list.size(); x++) {//遍历数据插入excel中
Map<String, Object> item = list.get(x);
for (int ck = 0; ck < colkeysize; ck++) {
//获取当前列key值相关信息并进行分割
String[] colkeyinfoarr = colkey[ck].split("@", -1);
String dqcolkeymc = colkeyinfoarr[0];//当前列key对应字段名称
String dqcolcc = colkeyinfoarr[1];//当前colcc值 对应上面相应描述
String[] dqcolccarr = dqcolcc.split("-", -1);
int dqcczrow = Integer.parseInt(dqcolccarr[0]);//当前元素所在的主行
int dqcczcol = Integer.parseInt(dqcolccarr[1]);//当前元素所在的主列
int dqccrow = Integer.parseInt(dqcolccarr[2]);//当前元素所在的行
int dqcccol = Integer.parseInt(dqcolccarr[3]);//当前元素所在的列
//当前元素 在全局中所在的 列
int dqyyqjrow = rows;
int dqyyqjcol = dqcczcol + dqcccol;
//创建几行几列的单元格 数据不需要进行合并 填充直接具体具体行列即可
XSSFRow xrow = sheet.getRow(dqyyqjrow) != null ? sheet.getRow(dqyyqjrow) : sheet.createRow(dqyyqjrow);
XSSFCell xcoll = xrow.createCell(dqyyqjcol);
xrow.setHeight((short) rowhdata);//设置数据行高
if (ck == 0 && sfyxh.equals("1")) {//序号
xcoll.setCellStyle(centerstyle);
xcoll.setCellValue(ck + 1);
} else {
String dqcolkeydylistmc = "";//当前列key对应字段名称对应的集合
try {
dqcolkeydylistmc = colkeyinfoarr[2];
} catch (Exception e) {
dqcolkeydylistmc = "";
}
String dqpdkeyPstr = "";
try {
dqpdkeyPstr = colkeyinfoarr[3];
} catch (Exception e) {
dqpdkeyPstr = "";
}
String[] dqpdkeyParr = StringUtils.isNotBlank(dqpdkeyPstr) ? dqpdkeyPstr.split("\\|", -1) : new String[]{};//当前列key对应字段 判断的key(主)
String dqpdPstr = "";//当前判断key对应的value值（主）
if (dqpdkeyParr != null && dqpdkeyParr.length > 0) {
for (int dp = 0; dp < dqpdkeyParr.length; dp++) {
String temp = item.get(dqpdkeyParr[dp]) != null ? item.get(dqpdkeyParr[dp]) + "" : "";
if (StringUtils.isNotBlank(temp)) {
if (StringUtils.isNotBlank(dqpdPstr)) {
dqpdPstr += "|" + temp;
} else dqpdPstr = temp;
}
}
}
String dqpdkeyZstr = "";
try {
dqpdkeyZstr = colkeyinfoarr[4];
} catch (Exception e) {
dqpdkeyZstr = "";
}
String[] dqpdkeyZarr = StringUtils.isNotBlank(dqpdkeyZstr) ? dqpdkeyZstr.split("\\|", -1) : new String[]{};//当前列key对应字段 判断的key(子)
String Zsjjhblwz = ""; //子数据集合遍历位置
try {
Zsjjhblwz = colkeyinfoarr[5];
} catch (Exception e) {
Zsjjhblwz = "";
}
String dqcolkeydyvaluestr = "";//当前对应的值
try {
if (StringUtils.isNotBlank(dqcolkeydylistmc)) {
List<Map<String, Object>> dqcolkeydylist = (List<Map<String, Object>>) item.get(dqcolkeydylistmc);//colkey对应的list集合
if (dqcolkeydylist != null && dqcolkeydylist.size() > 0) {
if (StringUtils.isBlank(dqpdkeyPstr)) {//如果主判断键都是空的 则按指定位置获取数据
try {
if (StringUtils.isNotBlank(Zsjjhblwz)) {
int zdtemp = Integer.parseInt(Zsjjhblwz);
Map<String, Object> dqzdmap = dqcolkeydylist.get(zdtemp);
dqcolkeydyvaluestr = dqzdmap.get(dqcolkeymc) != null ? dqzdmap.get(dqcolkeymc) + "" : "";
} else dqcolkeydyvaluestr = "";
} catch (Exception e) {
dqcolkeydyvaluestr = "";
}
} else {//按具体的条件进行判断遍历获取
for (int dckl = 0; dckl < dqcolkeydylist.size(); dckl++) {
Map<String, Object> dcklitem = dqcolkeydylist.get(dckl);
String dqpdZstr = "";//当前判断key对应的value值（子）
if (dqpdkeyZarr != null && dqpdkeyZarr.length > 0) {
for (int dp = 0; dp < dqpdkeyZarr.length; dp++) {
String temp = dcklitem.get(dqpdkeyZarr[dp]) != null ? item.get(dqpdkeyZarr[dp]) + "" : "";
if (StringUtils.isNotBlank(temp)) {
if (StringUtils.isNotBlank(dqpdZstr)) {
dqpdZstr += "|" + temp;
} else dqpdZstr = temp;
}
}
}
if (dqpdPstr.equals(dqpdkeyZarr)) {
dqcolkeydyvaluestr = dcklitem.get(dqcolkeymc) != null ? dcklitem.get(dqcolkeymc) + "" : "";
}
}
}
}
} else {
dqcolkeydyvaluestr = item.get(dqcolkeymc) != null ? item.get(dqcolkeymc) + "" : "";
}
} catch (Exception e) {
}
xcoll.setCellValue(dqcolkeydyvaluestr);
}
}
rows++;
}
OutputStream out = null;
try {
try {
out = response.getOutputStream();
response.reset();
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8") + ".xls");
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.flush();
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 创建标题样式
*
* @param wb
* @return
*/
private static XSSFCellStyle createTitleCellStyle(XSSFWorkbook wb) {
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());//背景颜色
XSSFFont headerFont1 = (XSSFFont) wb.createFont(); // 创建字体样式
headerFont1.setBold(true); //字体加粗
headerFont1.setFontName("黑体"); // 设置字体类型
headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
cellStyle.setFont(headerFont1); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建表头样式
*
* @param wb
* @return
*/
private static XSSFCellStyle createHeadCellStyle(XSSFWorkbook wb) {
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);// 设置自动换行
cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
cellStyle.setBorderRight(BorderStyle.THIN); //右边框
cellStyle.setBorderTop(BorderStyle.THIN); //上边框
XSSFFont headerFont = (XSSFFont) wb.createFont(); // 创建字体样式
headerFont.setBold(true); //字体加粗
headerFont.setFontName("黑体"); // 设置字体类型
headerFont.setFontHeightInPoints((short) 12); // 设置字体大小
cellStyle.setFont(headerFont); // 为标题样式设置字体样式
return cellStyle;
}
/**
* 创建内容样式
*
* @param wb
* @return
*/
private static XSSFCellStyle createContentCellStyle(XSSFWorkbook wb) {
XSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
cellStyle.setWrapText(true);// 设置自动换行
cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
cellStyle.setBorderRight(BorderStyle.THIN); //右边框
cellStyle.setBorderTop(BorderStyle.THIN); //上边框
// 生成12号字体
XSSFFont font = wb.createFont();
font.setColor((short) 8);
font.setFontHeightInPoints((short) 12);
cellStyle.setFont(font);
return cellStyle;
}
public static void exporttest(@RequestParam Map<String, Object> params, HttpServletResponse response) {
String filename = new Date().getTime() + "";
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFSheet sheet = workbook.createSheet("sheet");
// 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
//CellRangeAddress range = new CellRangeAddress(0, 0, 0, 5);
HSSFRow row0 = sheet.createRow(0);
HSSFCell cell_00 = row0.createCell(0);
cell_00.setCellStyle(style);
cell_00.setCellValue("AAA");
CellRangeAddress region = new CellRangeAddress(0, 1, 0, 1);
sheet.addMergedRegion(region);
HSSFCell cell_01 = row0.createCell(2);
cell_01.setCellStyle(style);
cell_01.setCellValue("BBB");
region = new CellRangeAddress(0, 0, 2, 2);
sheet.addMergedRegion(region);
HSSFCell cell_02 = row0.createCell(3);
cell_02.setCellStyle(style);
cell_02.setCellValue("CCC");
region = new CellRangeAddress(0, 0, 3, 4);
sheet.addMergedRegion(region);
OutputStream out = null;
try {
try {
out = response.getOutputStream();
response.reset();
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8") + ".xls");
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.flush();
out.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
调用方法
调用1：
public void exportwjksinfo(Map<String, Object> params, HttpServletResponse response) {
Map redisMap = paramsUtils.getRedis();
String ksdm = paramsUtils.noNullToString(redisMap.get("ksdm"));
String sjk = paramsUtils.noNullToString(redisMap.get("sjk"));
params.put("sfwgbs", "1");
params.put("bm", sjk);
params.put("ksdm", ksdm);
List<Map<String, Object>> list = baseDao.getksinfoList(params);
String[] colname = new String[]{"序号", "考生号", "姓名", "科目名称", "考场号", "座位号", "场次号", "考试时段", "考试开始时间", "考试结束时间", "证件号码", "违纪情况", "监考员", "监考员(组长)"};
String[] colkey = new String[]{"", "ksh", "ksxm", "kmmc", "kch", "zwh", "cch", "kssjduan", "kssj", "jssj", "sfzh", "illegal_reason", "jkymc", "jkyzzmc"};
String filename = "违纪管理_" + DateUtils.format(new Date(), DateUtils.DATE_TIME_PATTERN);
ExcelUtils.zdyMapdataexcel(list, colname, colkey, response, filename, "1");
}
多层次调用：
集合格式：
public List<Map<String, Object>> dealwithksdatayzdatatjlist(Map<String, Object> params) {
List<Map<String, Object>> datalist = new ArrayList<Map<String, Object>>();
params.put("cckey", "cckm");//默认到场次(科目)
//参数
String examplan_p = params.get("examplan") != null ? params.get("examplan") + "" : "";//考试计划
String unit_id_p = params.get("unit_id") != null ? params.get("unit_id") + "" : "";//考点单位代码
String exam_session_num_p = params.get("exam_session_num") != null ? params.get("exam_session_num") + "" : "";//场次代码
String unit_id_str_p = params.get("unit_id_str") != null ? params.get("unit_id_str") + "" : "";//考点单位代码字符串
QueryWrapper<AuthExamUnitEntity> authExamUnitEntityQueryWrapper = new QueryWrapper<AuthExamUnitEntity>();
authExamUnitEntityQueryWrapper.eq("examplan", examplan_p);
if (StringUtils.isNotBlank(unit_id_p)) {
authExamUnitEntityQueryWrapper.eq("unit_id", unit_id_p);
}
if (StringUtils.isNotBlank(unit_id_str_p)) {
authExamUnitEntityQueryWrapper.like("unit_id", unit_id_str_p);
}
List<AuthExamUnitEntity> list = authExamUnitDao.selectList(authExamUnitEntityQueryWrapper);
if (list != null && list.size() > 0) {
//cckey值:unit(单位),cckm(场次(科目)),kaoc(考场) 默认按考试计划
List<Map<String, Object>> ksbptjlist = authExamineeInfoService.getksbpinfotj(params);//考生编排统计数据 缓存
//获取当前考试计划下的场次科目
QueryWrapper<AuthExamSessionEntity> authExamSessionEntityQueryWrapper = new QueryWrapper<AuthExamSessionEntity>();
authExamSessionEntityQueryWrapper.eq("examplan", examplan_p);
if (StringUtils.isNotBlank(exam_session_num_p)) {
authExamSessionEntityQueryWrapper.eq("exam_session_num", exam_session_num_p);
}
List<AuthExamSessionEntity> authExamSessionEntities = authExamSessionDao.selectList(authExamSessionEntityQueryWrapper);
//获取当前考试计划下统计的数据
List<AuthExamStatisticEntity> authExamStatisticEntities = authExamStatisticDao.selectList(new QueryWrapper<AuthExamStatisticEntity>().eq("examplan", examplan_p));
for (int i = 0; i < list.size(); i++) {
AuthExamUnitEntity authExamUnitEntity = list.get(i);
Map<String, Object> item = MapUtils.objToMap(authExamUnitEntity, 0, 1);
String dq_examplan_str = item.get("examplan") != null ? item.get("examplan") + "" : "";
String dq_unit_id_str = item.get("unitId") != null ? item.get("unitId") + "" : "";
List<Map<String, Object>> ccinfolist = new ArrayList<Map<String, Object>>();
item.put("cclist", authExamStatisticEntities);//场次list
if (authExamSessionEntities != null && authExamSessionEntities.size() > 0) {
for (int c = 0; c < authExamSessionEntities.size(); c++) {
AuthExamSessionEntity authExamSessionEntity = authExamSessionEntities.get(c);
Map<String, Object> ccinfomap = MapUtils.objToMap(authExamSessionEntity, 0, 0);
String dq_exam_session_num_str = ccinfomap.get("examSessionNum") != null ? ccinfomap.get("examSessionNum") + "" : "";
String dq_cmpstr = dq_examplan_str + "@" + dq_unit_id_str + "@" + dq_exam_session_num_str;
//获取相关统计信息
//编排人数
Integer bprs = 0;
if (ksbptjlist != null && ksbptjlist.size() > 0) {
for (int x = 0; x < ksbptjlist.size(); x++) {
Map<String, Object> bpitem = ksbptjlist.get(x);
String bp_examplan_str = bpitem.get("examplan") != null ? bpitem.get("examplan") + "" : "";
String bp_unit_id_str = bpitem.get("unit_id") != null ? bpitem.get("unit_id") + "" : "";
String bp_exam_session_num_str = bpitem.get("exam_session_num") != null ? bpitem.get("exam_session_num") + "" : "";
String bp_cmpstr = bp_examplan_str + "@" + bp_unit_id_str + "@" + bp_exam_session_num_str;
String bp_bprs_str = bpitem.get("bprs") != null ? bpitem.get("bprs") + "" : "0";
if (dq_cmpstr.equals(bp_cmpstr)) {
bprs = Integer.parseInt(bp_bprs_str);
break;
}
}
}
ccinfomap.put("bprs", bprs);//编排人数
Integer yyzrs = 0, yzsjtsrs = 0;//已验证人数 验证数据推送人数
if (authExamStatisticEntities != null && authExamStatisticEntities.size() > 0) {
for (int t = 0; t < authExamStatisticEntities.size(); t++) {
AuthExamStatisticEntity authExamStatistic = authExamStatisticEntities.get(t);
String yz_cmpstr = authExamStatistic.getExamplan() + "@" + authExamStatistic.getUnitId() + "@" + authExamStatistic.getExamSessionId();
if (dq_cmpstr.equals(yz_cmpstr)) {
yyzrs = authExamStatistic.getYzzs() != null ? authExamStatistic.getYzzs() : 0;
yzsjtsrs = authExamStatistic.getYzytss() != null ? authExamStatistic.getYzytss() : 0;
break;
}
}
}
ccinfomap.put("yyzrs", yyzrs);//已验证人数
ccinfomap.put("yzsjtsrs", yzsjtsrs);//验证数据推送人数
//身份验证占已推送比例=(已推送人数/已验证人数)*100%
double sfyzbtsbl = yyzrs == 0 ? 0.00d : ((yzsjtsrs * 1.00d) / (yyzrs * 1.00d)) * 100.00d;
String sfyzbtsblstr = sfyzbtsbl + "";
ccinfomap.put("sfyzbtsbl", sfyzbtsblstr);
String sbwcstr = "否";
if (sfyzbtsbl > Double.parseDouble("95")) sbwcstr = "是";
ccinfomap.put("sbwc", sbwcstr);
ccinfolist.add(ccinfomap);
}
}
item.put("ccdatalist", ccinfolist);
item.put("ccdatasize", ccinfolist.size());
datalist.add(item);
}
}
return datalist;
}
参数调用：
public void exportksdatayzdatatj(Map<String, Object> params, HttpServletResponse response) {
String examplan_p = params.get("examplan") != null ? params.get("examplan") + "" : "";//考试计划
String exam_session_num_p = params.get("exam_session_num") != null ? params.get("exam_session_num") + "" : "";//场次代码
//获取当前考试计划下的场次科目
QueryWrapper<AuthExamSessionEntity> authExamSessionEntityQueryWrapper = new QueryWrapper<AuthExamSessionEntity>();
authExamSessionEntityQueryWrapper.eq("examplan", examplan_p);
if (StringUtils.isNotBlank(exam_session_num_p)) {
authExamSessionEntityQueryWrapper.eq("exam_session_num", exam_session_num_p);
}
List<AuthExamSessionEntity> authExamSessionEntities = authExamSessionDao.selectList(authExamSessionEntityQueryWrapper);
List<Map<String, Object>> list = dealwithksdatayzdatatjlist(params);
int zl = 0;//记录主列
//合并下标都是从0开始的
//colcc=主行值-主列值-当前行值-当前列值
//目前参数格式：标题名称(必填)@列宽度系数(1-100)(非必填)@行合并数(非必填)@列合并数(非必填)@colcc(必填)...
String colnamestr = "序号@@1@0@0-0-0-0,市@@1@@0-1-0-0,区(县)@@1@@0-2-0-0,考点编码@@1@@0-3-0-0,单位名称@25@1@@0-4-0-0";
//目前参数格式： 标题名称对应key(必填(除序号(第一个)))@colcc(必填)@子数据集合名称(非必填)@判断的key值(主)@判断的key值(子)@子数据集合遍历位置...
String colkeystr = "@0-0-0-0@@@@,cityName@0-1-0-0@@@@,areaName@0-2-0-0@@@@,unitId@0-3-0-0@@@@,unitName@0-4-0-0@@@@";
zl = 4 + 1;//目前位置 前面已经有5列了 从0开始 然后下一列 +1
if (authExamSessionEntities != null && authExamSessionEntities.size() > 0) {
int zgs = 5;//合并个数
for (int i = 0; i < authExamSessionEntities.size(); i++) {
AuthExamSessionEntity authExamSessionEntity = authExamSessionEntities.get(i);
int dqzl = zl + i * zgs;//当前主列
//列名称
//第0行
colnamestr += "," + authExamSessionEntity.getExamSessionName() + "@" + 15 * zgs + "@0@5@0-" + dqzl + "-0-0";//15为固定宽度系数 5为 其下有5列
//第1行
colnamestr += ",编排人数@@@@1-" + dqzl + "-1-0,已验证人数@@@@1-" + dqzl + "-1-1,验证数据推送人数@@@@1-" + dqzl + "-1-2,身份验证占已推送比例@@@@1-" + dqzl + "-1-3,上报完成@@@@1-" + dqzl + "-1-4";
//列数据
String ppkeystr = "";//主key判断值
String zpkeystr = "";//子key判断值
String gystr = "@ccdatalist@" + ppkeystr + "@" + zpkeystr + "@" + i;//公用串 //@子数据集合名称(非必填)@判断的key值(主)@判断的key值(子)@子数据集合遍历位置...
colkeystr += ",bprs@1-" + dqzl + "-1-0" + gystr + ",yyzrs@1-" + dqzl + "-1-1" + gystr + ",yzsjtsrs@1-" + dqzl + "-1-2" + gystr + ",sfyzbtsbl@1-" + dqzl + "-1-3" + gystr + ",sbwc@1-" + dqzl + "-1-4" + gystr;
}
}
String filename = "考点验证数据统计情况_" + DateUtils.format(new Date(), DateUtils.DATE_TIME_PATTERN_WFGF);
ExcelUtils.zdyMapdataexcel_dcc(list, colnamestr.split(",", -1), colkeystr.split(",", -1), response, filename, "1");
}
依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
Apache POI是画excel工具，大家在接收旧代码时会出现以下问题：
HSSFCellStyle.ALIGN_CENTER HSSFCellStyle.VERTICAL_CENTER 等爆红
原因是版本出错：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
以下是3.6及以下的版本显示：
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
更高版本是这样的：
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
扫一扫
29万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
