• 导出Excel


    导出Excel

     HSSFWorkbook与XSSFWorkbook,前一个可以用来解析以.xls结尾的excel,后一个可以用来解析.xlsx结尾的excel

    <!-- HSSF 导出Execel -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.0</version>
            </dependency>
    @Override
        public void exportExcel(HttpServletRequest request, HttpServletResponse response,StatisticalPageDto statisticalPageDto) {
            String indexCode = commonService.getIndexCodeByRegionCode(statisticalPageDto.getRegionCode());
            Set<String> communityCodes = commonService.getCommunityCodeByIndexCode(indexCode);
            List<PersonVehicleStatistical> statisticalList = new ArrayList<>();
            Wrapper<PersonVehicleStatistical> wrapper = new EntityWrapper<>();
            if (!ObjectUtils.isEmpty(communityCodes) && communityCodes.size() > 0){
                wrapper.in("community_code",communityCodes);
            }
            if (statisticalPageDto.getOrderType().equals("1")){
                wrapper.orderBy("snap_person_count",false);
            }else if (statisticalPageDto.getOrderType().equals("2")){
                wrapper.orderBy("snap_vehicle_count",false);
            }else if (statisticalPageDto.getOrderType().equals("4")){
                wrapper.orderBy("vehicle_count",false);
            }else if (statisticalPageDto.getOrderType().equals("3")){
                wrapper.orderBy("person_count",false);
            }
            if (statisticalPageDto.getExportType().equals("1")){
                Page<PersonVehicleStatistical> page = new Page<>();
                page.setCurrent(statisticalPageDto.getPageNo());
                page.setSize(statisticalPageDto.getPageSize());
                Page<PersonVehicleStatistical> statisticalPage = statisticalService.selectPage(page,wrapper);
                statisticalList = statisticalPage.getRecords();
            }else {
                statisticalList = statisticalService.selectList(wrapper);
            }
            export(PersonVehicleStatistical.class,"statistical_export",statisticalList,request,response);
        }
    /**
         * 导出数据
         * @param clazz  数据模型
         * @param data  数据
         * @param response 相应
         * @param <T>  数据的泛型
         */
        private static<T> void export(Class<T> clazz, String fileName, List<T> data, HttpServletRequest request, HttpServletResponse response){
            try {
                List<PersonVehicleStatistical> personVehicleStatisticalList = (List<PersonVehicleStatistical>) data;
    
                List<List<String>> excelData = new ArrayList<>();
                List<String> head = new ArrayList<>();
                head.add("XXXX");
                head.add("XXXX");
                head.add("XXXX");
                head.add("XXXX");
                head.add("XXXX");
                head.add("XXXX");
                head.add("XXXX");
                head.add("XXXX");
                excelData.add(head);
                for (PersonVehicleStatistical statistical:personVehicleStatisticalList) {
                    List<String> content = new ArrayList<>();
                    content.add(statistical.getCommunityCode());
                    content.add(statistical.getCommunityName());
                    content.add(statistical.getCommunityArea());
                    content.add( statistical.getCommunityAreaName());
                    content.add(String.valueOf(statistical.getSnapPersonCount()));
                    content.add(String.valueOf(statistical.getSnapVehicleCount()));
                    content.add(String.valueOf(statistical.getPersonCount()));
                    content.add(String.valueOf(statistical.getVehicleCount()));
                    excelData.add(content);
                }
                fileName = fileName + "_" + System.currentTimeMillis() + ".xls";
    
                preDownLoad(response,excelData,"统计",fileName);
            } catch (Exception e) {
                logger.error("export error: ",e);
            }
        }
    /**
         * Excel表格导出
         * @param response HttpServletResponse对象
         * @param excelData Excel表格的数据,封装为List<List<String>>
         * @param sheetName sheet的名字
         * @param fileName 导出Excel的文件名
         * @throws IOException 抛IO异常
         */
        public static void preDownLoad(HttpServletResponse response,List<List<String>> excelData, String sheetName,String fileName) throws IOException {
            //声明一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //生成一个表格,设置表格名称
            HSSFSheet sheet = workbook.createSheet(sheetName);
            //设置表格列宽度
            sheet.setDefaultColumnWidth(20);
            //写入List<List<String>>中的数据
            int rowIndex = 0;
            HSSFCellStyle styleTitle = ExcelUtils.getColumnTopStyle(workbook);
            HSSFCellStyle styleContent = ExcelUtils.getStyle(workbook);
            for (int t=0;t<excelData.size();t++) {
                List<String> data = excelData.get(t);
                //创建一个row行,然后自增1
                HSSFRow row = sheet.createRow(rowIndex++);
                //遍历添加本行数据
                for (int i = 0; i < data.size(); i++) {
                    //创建一个单元格
                    HSSFCell cell = row.createCell(i);
                    //创建一个内容对象
                    HSSFRichTextString text = new HSSFRichTextString(data.get(i));
                    //将内容对象的文字内容写入到单元格中
                    cell.setCellValue(text);
                    if (t == 0){
                        cell.setCellStyle(styleTitle);
                    }else {
                        cell.setCellStyle(styleContent);
                    }
                }
            }
            //准备将Excel的输出流通过response输出到页面下载
            //八进制输出流
            response.setContentType("application/octet-stream");
            //设置导出Excel的名称
            response.setHeader("Content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
            //刷新缓冲
            response.flushBuffer();
            //workbook将Excel写入到response的输出流中,供页面下载该Excel文件
            workbook.write(response.getOutputStream());
            //关闭workbook
            workbook.close();
        }
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    
    /**
     * @Auther:
     * @Date: 2020/9/9 11:54
     * @Description:
     */
    public class ExcelUtils {
        /*
         * 列头单元格样式
         */
        public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
            // 设置字体
            HSSFFont font = workbook.createFont();
            // 设置字体大小
            font.setFontHeightInPoints((short) 11);
            // 字体加粗
            font.setBold(true);
            // 设置字体名字
            font.setFontName("Courier New");
            // 设置样式
            HSSFCellStyle style = workbook.createCellStyle();
            // 设置低边框
            style.setBorderBottom(BorderStyle.THIN);
            // 设置低边框颜色
            style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // 设置右边框
            style.setBorderRight(BorderStyle.THIN);
            // 设置顶边框
            style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // 设置顶边框颜色
            style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // 在样式中应用设置的字体
            style.setFont(font);
            // 设置自动换行
            style.setWrapText(false);
            // 设置水平对齐的样式为居中对齐;
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            return style;
        }
    
        public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
            // 设置字体
            HSSFFont font = workbook.createFont();
            // 设置字体大小
            font.setFontHeightInPoints((short) 10);
            // 字体加粗
            //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 设置字体名字
            font.setFontName("Courier New");
            // 设置样式;
            HSSFCellStyle style = workbook.createCellStyle();
            // 设置底边框;
            style.setBorderBottom(BorderStyle.THIN);
            // 设置底边框颜色;
            style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // 设置左边框;
            style.setBorderLeft(BorderStyle.THIN);
            // 设置左边框颜色;
            style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // 设置右边框;
            style.setBorderRight(BorderStyle.THIN);
            // 设置右边框颜色;
            style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // 设置顶边框;
            style.setBorderTop(BorderStyle.THIN);
            // 设置顶边框颜色;
            style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // 在样式用应用设置的字体;
            style.setFont(font);
            // 设置自动换行;
            style.setWrapText(false);
            // 设置水平对齐的样式为居中对齐;
            style.setAlignment(HorizontalAlignment.CENTER);
            // 设置垂直对齐的样式为居中对齐;
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            return style;
        }
    }
  • 相关阅读:
    linux部署nuxt.js项目
    vue---el-table设置表头居中,内容列居中/左对齐/右对齐
    偶然看到的jquery选择器性能问题
    关于js中的回调函数问题
    html5图片上传(搬砖)
    css上传文件样式元素样式美化
    小记--转自张鑫旭的css命名规则
    关于nodeJS 在webstorm中的服务器配置
    关于window上的github 上传本地文件--傻瓜式教程
    关于PS的基本操作
  • 原文地址:https://www.cnblogs.com/lijianda/p/14100995.html
Copyright © 2020-2023  润新知