KM-Server-Milestone2/src/main/java/com/redxun/knowledge/analysis/utils/ExcelUtils.java

package com.redxun.knowledge.analysis.utils;

import com.redxun.common.tool.StringUtils;
import com.redxun.knowledge.analysis.entity.enums.DownloadNameEnum;
import com.redxun.knowledge.analysis.entity.enums.DownloadTypeEnum;
import com.redxun.knowledge.analysis.entity.vo.DownloadInfoVo;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 文件名: ExcelUtils
 * 作者: zizg
 * 时间: 2023/6/1
 * 描述:
 * 修改人:
 * 修改时间:
 * 修改内容:
 */
public class ExcelUtils {

    private ExcelUtils(){

    }

    /**
     * 导出Excel
     * @param list 导出数据
     * @param name 导出模块
     * @param type 导出类型(1 人员 2 部门)
     * @param response
     */
    public static void downloadExcel(List<DownloadInfoVo> list,Integer name,Integer type,String createTime,String endTime,String dimension, HttpServletResponse response){
        try {
            //创建一个空的工作薄
            XSSFWorkbook workbook = new XSSFWorkbook();
            //在工作薄中创建一个工作表
            XSSFSheet sheet = workbook.createSheet("sheet1");
            //设置列宽
            sheet.setColumnWidth(0, 20 * 256);
            sheet.setColumnWidth(1, 80 * 256);
            sheet.setColumnWidth(2, 20 * 256);
            //处理标题
            String[] titles = new String[]{"名称", "部门路径", "数量"};

            //创建标题行
            Row titleRow = sheet.createRow(0);
            Cell cell = null;
            for (int i = 0; i < titles.length; i++) {
                cell = titleRow.createCell(i);
                cell.setCellValue(titles[i]);
            }
            //处理内容
            int rowIndex = 1;
            Row row = null;
            if (!CollectionUtils.isEmpty(list)){
                for (DownloadInfoVo info : list) {
                    row = sheet.createRow(rowIndex);
                    cell = row.createCell(0);
                    cell.setCellValue(info.getPersonName());

                    cell = row.createCell(1);
                    cell.setCellValue(info.getDeptName());

                    cell = row.createCell(2);
                    cell.setCellValue(info.getNum());

                    rowIndex++;
                }
            }
            //设置文件的打开方式和mime类型
            String fileName = null;
            if (createTime != null && endTime != null){
                createTime = createTime.replaceAll("-","");
                endTime = endTime.replaceAll("-","");
                if (name == 1 || name == 5){
                    fileName = URLEncoder.encode(
                            DownloadNameEnum.getMessage(name) + "-" + DownloadTypeEnum.getMessage(type) + "访问量" + "-"
                                    + createTime + "-" + endTime, "UTF-8").replaceAll("\\+", "%20");
                } else {
                    fileName = URLEncoder.encode(
                            DownloadNameEnum.getMessage(name) + "-" + DownloadTypeEnum.getMessage(type) + "创建量" + "-"
                                    + createTime + "-" + endTime, "UTF-8").replaceAll("\\+", "%20");
                }
            } else {
                if (name == 1 || name == 5){
                    if (("total").equals(dimension)){
                        fileName = URLEncoder.encode(
                                DownloadNameEnum.getMessage(name) + "-" + DownloadTypeEnum.getMessage(type) + "访问量" + "-"
                                        + "全部", "UTF-8").replaceAll("\\+", "%20");
                    } else if (("year").equals(dimension)){
                        fileName = URLEncoder.encode(
                                DownloadNameEnum.getMessage(name) + "-" + DownloadTypeEnum.getMessage(type) + "访问量" + "-"
                                        + "年", "UTF-8").replaceAll("\\+", "%20");
                    } else if (("month").equals(dimension)){
                        fileName = URLEncoder.encode(
                                DownloadNameEnum.getMessage(name) + "-" + DownloadTypeEnum.getMessage(type) + "访问量" + "-"
                                        + "月", "UTF-8").replaceAll("\\+", "%20");
                    }

                } else {
                    if (("total").equals(dimension)){
                        fileName = URLEncoder.encode(
                                DownloadNameEnum.getMessage(name) + "-" + DownloadTypeEnum.getMessage(type) + "创建量" + "-"
                                        + "全部", "UTF-8").replaceAll("\\+", "%20");
                    } else if (("year").equals(dimension)){
                        fileName = URLEncoder.encode(
                                DownloadNameEnum.getMessage(name) + "-" + DownloadTypeEnum.getMessage(type) + "创建量" + "-"
                                        + "年", "UTF-8").replaceAll("\\+", "%20");
                    } else if (("month").equals(dimension)){
                        fileName = URLEncoder.encode(
                                DownloadNameEnum.getMessage(name) + "-" + DownloadTypeEnum.getMessage(type) + "创建量" + "-"
                                        + "月", "UTF-8").replaceAll("\\+", "%20");
                    }
                }
            }

            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.addHeader("blob","true");
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}