excel文件导出相应数据统计内容

这次完成了一个以excel文件导出相应数据统计内容的功能,选取其中的一个放在下面:

1.首先在资源文件夹下创建一个excel的package,并创建一个xlsx文件

创建的时候要注意版本兼容问题,2007和2003

2.chatStics.ftl

在前端页面添加导出:

<a href="javascript:void(0);" onclick="javascript:exportData();" class="easyui-linkbutton" iconCls="icon-search">导出</a>

添加js事件:

    function exportData() {
        $('#queryForm').form('submit', {
            url:'${request.contextPath}/examine/exportChatStaticsList',
            success:function () {
                console.log("success");
            }
        })
    }

3.StatisticsController

 @RequestMapping(value = "/exportChatStaticsList")
    public void exportChatStaticsList(@RequestParam(value = "date") String date, HttpServletResponse response)throws IOException {
        statisticsService.exportChatStaticsList(date,response);
    }
}

4.StatisticsService

public void  exportChatStaticsList(String date, HttpServletResponse response) throws IOException {
        String tempFilePath = StatisticsService.class.getResource("/excel/chat.xlsx").getPath();
        String filename="chat-"+date;
        List<Map<Integer, Object>> dataList = new ArrayList<>();
        List<TChatStatistics> list = chatStatisticsMapper.selectChatStics(date);
        for (TChatStatistics chatDTO : list){
            Map<Integer,Object> data = new HashMap<Integer, Object>();
            data.put(1,chatDTO.getUid());
            data.put(2,chatDTO.getNickname());
            data.put(3,chatDTO.getTotalNum());
            data.put(4,chatDTO.getConnectNum());
            double value =new BigDecimal((Double.valueOf(chatDTO.getConnectNum())/Double.valueOf(chatDTO.getTotalNum()))*100).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue();
            data.put(5,value);
            Format format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String time = ((SimpleDateFormat) format).format(chatDTO.getDate());
            data.put(6,time);
            dataList.add(data);
        }
        String[] heads = new String[]{"A2","B2","C2","D2","E2","F2"};
        ExcelUtil.writeDateList(tempFilePath,heads,dataList,0);

        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        if("2007".equals("2007")){
            response.setHeader("Content-Disposition", "inline;fileName="+filename+".xlsx");
        }else{
            response.setHeader("Content-Disposition", "inline;fileName="+filename+".xls");
        }
        OutputStream out = response.getOutputStream();
        ExcelUtil.writeAndClose(tempFilePath, out);
        out.close();

5.TChatStaticsMapper接口

public interface TChatStatisticsMapper extends BaseMapper<TChatStatistics> {

    /**
     *
     * @param startTime
     * @param endTime
     * @return
     */
    List<TChatRecord> selectChatRecordDate(@Param(value = "startTime")String startTime, @Param(value = "endTime")String endTime);

    List<TChatStatistics> selectChatStics(@Param(value = "date")String date);

}

6.TChatStatisticsMapper.xml

查询语句:

    <select id="selectChatStics" resultMap="BaseResultMap">
        select cs.*,ai.nickname from t_chat_statistics cs LEFT JOIN t_anchor_info ai on cs.uid=ai.uid where cs.date=#{date} and ai.type=3 and ai.status=0 ORDER BY cs.connect_num desc
    </select>