一、代码
1.1 Controller
@GetMapping("/export")
@ApiOperation(value="导出excel")
public void export(QuerySurveyAnswerVO vo, HttpServletResponse response) {
Survey survey = surveyService.get(vo.getSurveyId());
String filename = survey.getSurveyName()+"(答卷汇总)"+".xlsx";
List<LinkedHashMap<String, String>> datas = surveyAnswerService.listExportDatas(vo);
ExcelWriter writer = ExcelUtil.getWriter(true);
//设置列宽
writer.setColumnWidth(-1, 18);
writer.setOnlyAlias(true);
writer.write(datas, true);
super.exportExcel(response, writer, filename);
}
1.2 查询参数VO
@Data
@NoArgsConstructor
@AllArgsConstructor
@SuppressWarnings("serial")
@ApiModel(value = "QuerySurveyAnswerVO")
public class QuerySurveyAnswerVO {
@ApiModelProperty(value = "调查问卷ID")
private Long surveyId;
@ApiModelProperty(value = "账号")
private String markId;
@ApiModelProperty(value = "昵称")
private String nickName;
@ApiModelProperty(value = "手机号码")
private String mobile;
@ApiModelProperty(value = "状态(0=全部；1=登录用户；2=游客)")
private Integer anonymousState;
}
1.3 service
public interface SurveyAnswerService {
/**
* 导出数据列表
* @param vo
* @return
*/
List<LinkedHashMap<String, String>> listExportDatas(QuerySurveyAnswerVO vo);
}
@Override
public List<LinkedHashMap<String, String>> listExportDatas(QuerySurveyAnswerVO vo) {
//获取动态拼接sql
String dynamicQuestions = mapper.dynamicQuestions(vo.getSurveyId());
log.info("dynamicQuestions:"+dynamicQuestions);
String markIdSql = "";
String nickNameSql = "";
String mobileSql = "";
String anonymousStateSql = "";
if (StringUtil.isNotEmpty(vo.getMarkId())) {
markIdSql = " and a.mark_id like '%" + vo.getMarkId() + "%'";
}
if (StringUtil.isNotEmpty(vo.getNickName())) {
nickNameSql = " and a.nick_name like '%" + vo.getNickName() + "%'";
}
if (StringUtil.isNotEmpty(vo.getMobile())) {
mobileSql = " and a.mobile like '%" + vo.getMobile() + "%'";
}
if (vo.getAnonymousState() != null) {
if (vo.getAnonymousState() == 1) {
anonymousStateSql = " and a.member_id is not null ";
}
if (vo.getAnonymousState() == 2) {
anonymousStateSql = " and a.member_id is null ";
}
}
//手动与固定列拼接
String sql = "select a.mark_id as 用户ID," +
"if(a.mark_id='',\"游客\",a.nick_name) as 用户昵称," +
"a.mobile as 手机号," +
"case a.sex" +
" when 'MEN' then '男' " +
" when 'WOMEN' then '女' " +
" when 'UNKNOW' then '保密' " +
" end as 性别," +
"a.answer_time as 参与时间," +
dynamicQuestions +
" from t_survey_answer_item ai JOIN t_survey_answer a on ai.survey_answer_id = a.id " +
" where ai.deleted=0 and a.deleted=0 and a.survey_id='" + vo.getSurveyId() + "'" +
markIdSql +
nickNameSql +
mobileSql +
anonymousStateSql +
" group by a.mark_id,a.nick_name,a.mobile,a.sex,a.answer_time" +
" order by a.answer_time desc";
log.info("export sql :"+sql);
//查询结果
List<LinkedHashMap<String, String>> mapList = mapper.listExportDatas(sql);
return mapList;
}
1.4 mybaits-mapper
public interface SurveyAnswerMapper extends BaseMapper<SurveyAnswer> {
/**
* 题目集合（动态）
* @return
*/
String dynamicQuestions(@Param("surveyId")Long surveyId);
/**
* 导出数据列表
* @param sql
* @return
*/
List<LinkedHashMap<String, String>> listExportDatas(String sql);
}
1.5 mybaits.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.alanchen.mapper.SurveyAnswerMapper">
<select id="dynamicQuestions" resultType="java.lang.String">
SELECT
@sql :=GROUP_CONCAT(DISTINCT
CONCAT(
'Max(IF(ai.question_name = ''',
a.question_name,
''', ai.item_name, '''')) AS ''',
replace(a.question_name,'.',''), ''''
)
order by order_number asc
)
FROM (SELECT DISTINCT question_name,order_number FROM t_survey_question where deleted = 0 and survey_id=#{surveyId}) a,
(SELECT @sql:="") s
</select>
<select id="listExportDatas" parameterType="java.lang.String" resultType="java.util.LinkedHashMap">
${sql}
</select>
</mapper>
三、数据表结构
CREATE TABLE `t_survey` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`survey_no` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '问卷ID',
`survey_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '问卷标题',
`remark` varchar(200) NOT NULL DEFAULT '' COMMENT '问卷描述',
`publish_status` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'NOT_ENABLED' COMMENT '状态',
`active` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否立即启用',
`anonymous` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否匿名',
`date_start` datetime DEFAULT NULL COMMENT '开始日期',
`date_end` datetime DEFAULT NULL COMMENT '结束日期',
`publisher_id` bigint DEFAULT NULL COMMENT '发布者ID',
`publisher_name` varchar(50) DEFAULT '' COMMENT '发布者名字',
`join_number` int DEFAULT '0' COMMENT '参与人数',
`link_url` varchar(100) DEFAULT NULL COMMENT '访问链接',
`question_num` int DEFAULT '0' COMMENT '题目数',
`deleted` bit(1) NOT NULL DEFAULT b'0',
`modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=186831853387778 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='问卷调查'
CREATE TABLE `t_survey_answer` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`survey_id` bigint NOT NULL DEFAULT '0' COMMENT '问卷ID',
`member_id` bigint DEFAULT NULL COMMENT '会员ID',
`mobile` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '手机号码',
`nick_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '昵称',
`mark_id` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '账号',
`icon_url` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '用户头像地址',
`sex` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户性别 UNKNOW保密 MEN男 WOMEN女',
`answer_time` datetime DEFAULT NULL COMMENT '答题时间',
`deleted` bit(1) NOT NULL DEFAULT b'0',
`modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=189434090356738 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='用户答卷'
CREATE TABLE `t_survey_answer_item` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`survey_id` bigint NOT NULL DEFAULT '0' COMMENT '问卷ID',
`survey_answer_id` bigint NOT NULL DEFAULT '0' COMMENT '用户答卷ID',
`question_id` bigint NOT NULL DEFAULT '0' COMMENT '题目ID',
`question_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '题目名称',
`serial_number` int DEFAULT '0',
`question_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'SINGLE_CHOICE' COMMENT '题型',
`question_item_id` bigint DEFAULT NULL COMMENT '选项ID',
`item_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '选项内容',
`icon` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '图标',
`order_number` int DEFAULT '0',
`deleted` bit(1) NOT NULL DEFAULT b'0',
`modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=189434090356743 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='用户答卷明细'
CREATE TABLE `t_survey_question` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`survey_id` bigint NOT NULL DEFAULT '0' COMMENT '问卷ID',
`question_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '题目名称',
`question_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'SINGLE_CHOICE' COMMENT '题型',
`required` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否必填',
`least` int DEFAULT NULL COMMENT '最少选择',
`most` int DEFAULT NULL COMMENT '最多选择',
`order_number` int DEFAULT '0' COMMENT '排序序号',
`forward_order_number` int DEFAULT NULL COMMENT '跳题序号（多选题时）',
`forward_question_id` bigint DEFAULT NULL COMMENT '跳题ID（多选时）',
`serial_number` int DEFAULT '0',
`deleted` bit(1) NOT NULL DEFAULT b'0',
`modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=186887039942735 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='问卷调查题目'
CREATE TABLE `t_survey_question_item` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`survey_id` bigint NOT NULL DEFAULT '0' COMMENT '问卷ID',
`question_id` bigint NOT NULL DEFAULT '0' COMMENT '题目ID',
`item_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '选项内容',
`icon` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '图标',
`item_type` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'TEXT' COMMENT '类型',
`order_number` int DEFAULT '0' COMMENT '排序序号',
`forward_order_number` int DEFAULT NULL COMMENT '跳题序号',
`forward_question_id` bigint DEFAULT NULL COMMENT '跳题ID',
`deleted` bit(1) NOT NULL DEFAULT b'0',
`modify_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_deleted` (`deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=186887039942742 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci AVG_ROW_LENGTH=2048 COMMENT='题目选项'
四、MySQL GROUP_CONCAT函数长度限制
平常使用可能发现不了问题，在处理大数据的时候，会发现内容被截取了，其实MYSQL内部对这个是有设置的，默认不设置的长度是1024个字符，如果我们需要更大，就需要手工去修改配置。
1、查看当前mysql group_concat_max_len
show variables like 'group_concat_max_len'
2、如果不方便重启mysql 可以在mysql状态通过命令设置，如
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;
此种方式在mysql重启后会读取配置文件重新设置，会导致设置失效，所以建议依旧要修改配置文件。
3、修改配置文件：my.ini
在[mysqld]下新增配置：group_concat_max_len = 102400
重启，通过方式1查看即可。
4、不限制大小
有时我们并不知需要多大的字节才能满足需求，此种情况可以考虑不设置最大字节（即采用最大字节数）即在配置文件设置group_concat_max_len=-1
五、备注
1、导出用的是Hutool导出工具类
网友评论