精华内容
下载资源
问答
  • springboot导出excel操作

    2021-01-28 11:05:20
    展示主要跟导出功能相关的代码,主要流程:获取业务数据,形成excel数据流,导出excel @PostMapping(value = "/v1/analysis/mission/readrecordExport") public void readrecordExport(@RequestBody ...

    业务场景:

    后台需要提供一个导出所有数据的接口,将导出的excel表格返回给前端展示

    代码层面:

    展示主要跟导出功能相关的代码,主要流程:获取业务数据,形成excel数据流,导出excel

     @PostMapping(value = "/v1/analysis/mission/readrecordExport")
        public void readrecordExport(@RequestBody MissionReadRecordRequest missionReadRecordRequest,HttpServletResponse response) throws IOException {
            missionReadRecordRequest.setPage(1);
            missionReadRecordRequest.setSize(99999);
            if(missionReadRecordRequest.getBeginTime()==null){
                missionReadRecordRequest.setBeginTime(DateUtils.getDate(-30));
            }
            if(missionReadRecordRequest.getEndTime()==null){
                missionReadRecordRequest.setEndTime(new Date());
            }else{
                missionReadRecordRequest.setEndTime(DateUtils.getDateAfter(missionReadRecordRequest.getEndTime(),1));
            }
            List<MissionReadRecord> list = missionReadRecordService.getMemberMissionReadRecordList(missionReadRecordRequest);
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd-HHmmss");
            String title = simpleDateFormat.format(new Date()) + ".xls";
            //使用流将数据导出
            OutputStream out = null;
            try {
                out = response.getOutputStream();
                //防止中文乱码
                response.setHeader("Content-Type", "application/x-xls");
                response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
                response.setHeader("Content-Disposition", "attachment;fileName=" + new String(title.getBytes(), "UTF-8"));
    
                Map<String, String> headers = new LinkedHashMap<>();
                headers.put("createTime", "创建时间");
                headers.put("jobType", "任务类型");
                headers.put("readContentType", "阅读内容类型");
                headers.put("title", "内容标题");
                headers.put("objectId", "内容id");
                headers.put("coinAmount", "奖励金币数");
                headers.put("readTime", "阅读时长");
                headers.put("todayCount", "当日同类奖励计数");
                List<Map<String, Object>> dataset = list.stream()
                        .map(dto -> {
                            Map<String, Object> map = new HashMap<>();
                            map.put("createTime", dto.getCreateTime());
                            map.put("jobType", dto.getJobType());
                            map.put("readContentType", dto.getReadContentType());
                            map.put("title", dto.getTitle());
                            map.put("objectId", dto.getObjectId());
                            map.put("coinAmount", dto.getCoinAmount());
                            map.put("readTime", dto.getReadTime());
                            map.put("todayCount", dto.getTodayCount());
                            return map;
                        })
                        .collect(Collectors.toList());
                ExcelUtil.exportExcelwithTitle(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss");
            } finally {
                if (out != null) {
                    out.flush();
                }
                if (out != null) {
                    out.close();
                }
            }
        }
    
    package com.mongcent.core.commons.ui.ExcelUtil;
    
    import org.apache.commons.beanutils.BeanComparator;
    import org.apache.commons.collections.CollectionUtils;
    import org.apache.commons.collections.ComparatorUtils;
    import org.apache.commons.collections.comparators.ComparableComparator;
    import org.apache.commons.collections.comparators.ComparatorChain;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.apache.poi.ss.util.CellReference;
    import org.apache.poi.ss.util.RegionUtil;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import javax.servlet.http.HttpServletResponse;
    import java.beans.PropertyDescriptor;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.text.MessageFormat;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     */
    public class ExcelUtil {
    
        private static Logger LG = LoggerFactory.getLogger(ExcelUtil.class);
    
        /**
         * 用来验证excel与Vo中的类型是否一致 <br>
         * Map<栏位类型,只能是哪些Cell类型>
         */
        private static Map<Class<?>, CellType[]> validateMap = new HashMap<>();
    
        static {
            validateMap.put(String[].class, new CellType[]{CellType.STRING});
            validateMap.put(Double[].class, new CellType[]{CellType.NUMERIC});
            validateMap.put(String.class, new CellType[]{CellType.STRING});
            validateMap.put(Double.class, new CellType[]{CellType.NUMERIC});
            validateMap.put(Date.class, new CellType[]{CellType.NUMERIC, CellType.STRING});
            validateMap.put(Integer.class, new CellType[]{CellType.NUMERIC});
            validateMap.put(Float.class, new CellType[]{CellType.NUMERIC});
            validateMap.put(Long.class, new CellType[]{CellType.NUMERIC});
            validateMap.put(Boolean.class, new CellType[]{CellType.BOOLEAN});
        }
    
        /**
         * 利用JAVA的反射机制,将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
         * 用于单个sheet
         *
         * @param <T>
         * @param headers 表格属性列名数组
         * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
         *                javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
         * @param out     与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
         * @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd"
         */
        public static <T> void exportExcelwithTitle(String title, Map<String,String> headers, List<T> dataset, OutputStream out,
                                           String pattern) {
            // 声明一个工作薄
            HSSFWorkbook workbook = exportExcelwithTitle(title,headers,dataset,pattern);
            try {
                workbook.write(out);
            } catch (IOException e) {
                LG.error(e.toString(), e);
            }
        }
    
        /**
         * 利用JAVA的反射机制,将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
         * 用于单个sheet
         *
         * @param <T>
         * @param headers 表格属性列名数组
         * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
         *                javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
         * @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd"
         */
        public static <T> HSSFWorkbook exportExcelwithTitle(String title, Map<String,String> headers, List<T> dataset,
                                                    String pattern) {
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
    
            if(dataset.size()>65536) {
                int i = 1;
                while (dataset.size()>0) {
                    List<T> subList = dataset.subList(0, Math.min(60000,dataset.size()));
                    if(dataset.size()>60000) {
                        dataset = dataset.subList(60000,dataset.size());
                    } else {
                        dataset = Collections.emptyList();
                    }
                    // 生成一个表格
                    HSSFSheet sheet = workbook.createSheet(title + "-" + i);
                    write2Sheet(sheet, headers, subList, pattern,workbook);
                    i++;
                }
            } else {
                // 生成一个表格
                HSSFSheet sheet = workbook.createSheet(title);
                write2Sheet(sheet, headers, dataset, pattern,workbook);
            }
            return workbook;
        }
    
    
        /**
         * 每个sheet的写入
         *
         * @param sheet   页签
         * @param headers 表头
         * @param dataset 数据集合
         * @param pattern 日期格式
         */
        private static <T> void write2Sheet(HSSFSheet sheet, Map<String,String> headers, Collection<T> dataset,
                                            String pattern,HSSFWorkbook workbook ) {
            //时间格式默认"yyyy-MM-dd"
            if (isBlank(pattern)){
                pattern = "yyyy-MM-dd";
            }
    //        Map<String, CellStyle> styles = createStyles(workbook);
            // 产生表格标题行
            HSSFRow row = sheet.createRow(0);
            // 标题行转中文
            Set<String> keys = headers.keySet();
            Iterator<String> it1 = keys.iterator();
            //存放临时键变量
            String key;
            //标题列数
            int c= 0;
            while (it1.hasNext()){
                key = it1.next();
                if (headers.containsKey(key)) {
                    HSSFCell cell = row.createCell(c);
                    HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
                    cell.setCellValue(text);
                    c++;
                }
            }
    
            // 遍历集合数据,产生数据行
            Iterator<T> it = dataset.iterator();
            int index = 0;
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                T t = it.next();
                try {
                    if (t instanceof Map) {
                        @SuppressWarnings("unchecked")
                        Map<String, Object> map = (Map<String, Object>) t;
                        int cellNum = 0;
                        //遍历列名
                        Iterator<String> it2 = keys.iterator();
                        while (it2.hasNext()){
                            key = it2.next();
                            if (!headers.containsKey(key)) {
                                continue;
                            }
                            Object value = map.get(key);
                            HSSFCell cell = row.createCell(cellNum);
                            cellNum = setCellValue(cell,value,pattern,cellNum,null,row);
                            cellNum++;
                        }
                    } else {
                        List<FieldForSortting> fields = sortFieldByAnno(t.getClass());
                        int cellNum = 0;
                        for (int i = 0; i < fields.size(); i++) {
                            HSSFCell cell = row.createCell(cellNum);
                            Field field = fields.get(i).getField();
                            field.setAccessible(true);
                            Object value = field.get(t);
                            cellNum = setCellValue(cell,value,pattern,cellNum,field,row);
                            cellNum++;
                        }
                    }
                } catch (Exception e) {
                    LG.error(e.toString(), e);
                }
            }
        }
    
        private static int setCellValue(HSSFCell cell,Object value,String pattern,int cellNum,Field field,HSSFRow row){
            String textValue = null;
            if (value instanceof Integer) {
                int intValue = (Integer) value;
                cell.setCellValue(intValue);
            } else if (value instanceof Float) {
                float fValue = (Float) value;
                cell.setCellValue(fValue);
            } else if (value instanceof Double) {
                double dValue = (Double) value;
                cell.setCellValue(dValue);
            } else if (value instanceof Long) {
                long longValue = (Long) value;
                cell.setCellValue(longValue);
            } else if (value instanceof Boolean) {
                boolean bValue = (Boolean) value;
                cell.setCellValue(bValue);
            } else if (value instanceof Date) {
                Date date = (Date) value;
                SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                textValue = sdf.format(date);
            } else if (value instanceof String[]) {
                String[] strArr = (String[]) value;
                for (int j = 0; j < strArr.length; j++) {
                    String str = strArr[j];
                    cell.setCellValue(str);
                    if (j != strArr.length - 1) {
                        cellNum++;
                        cell = row.createCell(cellNum);
                    }
                }
            } else if (value instanceof Double[]) {
                Double[] douArr = (Double[]) value;
                for (int j = 0; j < douArr.length; j++) {
                    Double val = douArr[j];
                    // 值不为空则set Value
                    if (val != null) {
                        cell.setCellValue(val);
                    }
    
                    if (j != douArr.length - 1) {
                        cellNum++;
                        cell = row.createCell(cellNum);
                    }
                }
            } else {
                // 其它数据类型都当作字符串简单处理
                String empty = "";
                if(field != null) {
                    ExcelCell anno = field.getAnnotation(ExcelCell.class);
                    if (anno != null) {
                        empty = anno.defaultValue();
                    }
                }
                textValue = value == null ? empty : value.toString();
            }
            if (textValue != null) {
                HSSFRichTextString richString = new HSSFRichTextString(textValue);
                cell.setCellValue(richString);
            }
            return cellNum;
        }
    
        /**
         * 根据annotation的seq排序后的栏位
         *
         * @param clazz
         * @return
         */
        private static List<FieldForSortting> sortFieldByAnno(Class<?> clazz) {
            Field[] fieldsArr = clazz.getDeclaredFields();
            List<FieldForSortting> fields = new ArrayList<>();
            List<FieldForSortting> annoNullFields = new ArrayList<>();
            for (Field field : fieldsArr) {
                ExcelCell ec = field.getAnnotation(ExcelCell.class);
                if (ec == null) {
                    // 没有ExcelCell Annotation 视为不汇入
                    continue;
                }
                int id = ec.index();
                fields.add(new FieldForSortting(field, id));
            }
            fields.addAll(annoNullFields);
            sortByProperties(fields, true, false, "index");
            return fields;
        }
    
        private static void sortByProperties(List<? extends Object> list, boolean isNullHigh,
                                             boolean isReversed, String... props) {
            if (CollectionUtils.isNotEmpty(list)) {
                Comparator<?> typeComp = ComparableComparator.getInstance();
                if (isNullHigh == true) {
                    typeComp = ComparatorUtils.nullHighComparator(typeComp);
                } else {
                    typeComp = ComparatorUtils.nullLowComparator(typeComp);
                }
                if (isReversed) {
                    typeComp = ComparatorUtils.reversedComparator(typeComp);
                }
    
                List<Object> sortCols = new ArrayList<Object>();
    
                if (props != null) {
                    for (String prop : props) {
                        sortCols.add(new BeanComparator(prop, typeComp));
                    }
                }
                if (sortCols.size() > 0) {
                    Comparator<Object> sortChain = new ComparatorChain(sortCols);
                    Collections.sort(list, sortChain);
                }
            }
        }
    
        private static boolean isBlank(String str){
            if(str == null){
                return true;
            }
            return str.length() == 0;
        }
    
        protected static boolean isNotBlank(String str){
            return !isBlank(str);
        }
    
        /**
         * 创建表格样式
         *
         * @param wb 工作薄对象
         * @return 样式列表
         */
        private static Map<String, CellStyle> createStyles(Workbook wb) {
            Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    
            CellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            Font titleFont = wb.createFont();
            titleFont.setFontName("Arial");
            titleFont.setFontHeightInPoints((short) 16);
            titleFont.setBold(true);
            style.setFont(titleFont);
            styles.put("title", style);
    
            style = wb.createCellStyle();
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setBorderRight(BorderStyle.THIN);
            style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderLeft(BorderStyle.THIN);
            style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderTop(BorderStyle.THIN);
            style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderBottom(BorderStyle.THIN);
            style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            Font dataFont = wb.createFont();
            dataFont.setFontName("Arial");
            dataFont.setFontHeightInPoints((short) 10);
            style.setFont(dataFont);
            styles.put("data", style);
    
            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(HorizontalAlignment.LEFT);
            styles.put("data1", style);
    
            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(HorizontalAlignment.CENTER);
            styles.put("data2", style);
    
            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(HorizontalAlignment.RIGHT);
            styles.put("data3", style);
    
            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
    
            style.setAlignment(HorizontalAlignment.CENTER);
            //style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            Font headerFont = wb.createFont();
            headerFont.setFontName("Arial");
            headerFont.setFontHeightInPoints((short) 10);
            headerFont.setBold(true);
            //headerFont.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(headerFont);
            styles.put("header", style);
    
            return styles;
        }
    }
    

     

    展开全文
  • SpringBoot 导出 Excel

    2019-11-27 10:22:44
    1.导出Excel 到本地 / 浏览器 2.导出包含多个 Sheet 的 Excel 到本地 / 浏览器 package com.geotmt.billingcenter.common.utils; import com.alibaba.fastjson.JSONArray; import ...

    添加 maven 依赖

            <dependency>
                <groupId>commons-fileupload</groupId>
                <artifactId>commons-fileupload</artifactId>
                <version>1.3.1</version>
            </dependency>
            <dependency>
                <groupId>commons-io</groupId>
                <artifactId>commons-io</artifactId>
                <version>2.4</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.1.0</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.0</version>
            </dependency>

    编写工具类

    • 可以实现 
      • 1.导出Excel 到本地 / 浏览器
      • 2.导出包含多个 Sheet 的 Excel 到本地 / 浏览器
    package com.geotmt.billingcenter.common.utils;
    
    import com.alibaba.fastjson.JSONArray;
    import com.alibaba.fastjson.JSONObject;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.*;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.util.StringUtils;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.BufferedOutputStream;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.util.List;
    import java.util.Map;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    
    /**
     * @Descriptinon: Excel 导出类
     * @Author:       yanghanwei
     * @Mail:         yanghanwei@geotmt.com
     * @CreateDate:   2018/12/3 17:15
     * @Version:      v1
     */
    public class ExportExcelUtils {
    
        private static final String ATTR_ARR_STR = "attrArr";
        private static final String DATA_LIST_STR = "dataList";
        private static final String TITLE_ARR_STR = "titleArr";
        private static final String SHEET_NAME = "Sheet";
        private static final String SHEET_NAME_STR = "sheetName";
        /**
         * 默认单元格宽度
         */
        private static final Integer DEFAULT_CELL_WIDTH = 3000;
    
        private static FileOutputStream fout = null;
        private static OutputStream out = null;
    
        private static OutputStream setResponseHeaderFileName(HttpServletResponse response, String filename) throws IOException {
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename+".xls","UTF-8"));
            OutputStream out = new BufferedOutputStream(response.getOutputStream());
            return out;
        }
    
        private static final Logger logger = LoggerFactory.getLogger(ExportExcelUtils.class);
    
        /**
         * 导出 exccel到本地 工具类(多个sheet导出)
         * @param mapList
         * @param filePath 本地路径
         * sheetName:   sheet名字
         * titleArr:    表头中文名数组
         * attrArr:     数据对应的实体类属性数组(与表头一一对应)
         * dataList:    到处的数据list
         */
        public static void buildExcelTemplate( List<Map<String, Object>> mapList,String filePath, Integer cellWidth) {
            HSSFWorkbook wb = new HSSFWorkbook();
            FileOutputStream fout = null;
            try{
                // 将文件存到指定位置
                fout = new FileOutputStream(filePath);
                if (!mapList.isEmpty()) {
                    for (int a = 0; a < mapList.size(); a++) {
                        String sheetName = "Sheet1";
                        if(!StringUtils.isEmpty(mapList.get(a).get(SHEET_NAME_STR))){
                            sheetName = String.valueOf(mapList.get(a).get(SHEET_NAME_STR));
                        }
                        String[] titleArr = (String[]) mapList.get(a).get("titleArr");
                        String[] attrArr = (String[]) mapList.get(a).get("attrArr");
                        Object dataList = mapList.get(a).get("dataList");
                        // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
                        HSSFSheet sheetMonitor = wb.createSheet(sheetName);
                        // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                        HSSFRow rowMonitor = sheetMonitor.createRow(0);
                        // 第四步,创建单元格,并设置值表头 设置表头居中
                        HSSFCellStyle style = ExportExcelUtils.getColumnTopStyle(wb);
    
                        HSSFCell cellMonitor = rowMonitor.createCell(0);
                        for (int i = 0; i < titleArr.length; i++) {
                            cellMonitor.setCellValue(titleArr[i]);
                            cellMonitor.setCellStyle(style);
                            cellMonitor = rowMonitor.createCell((i + 1));
                        }
                        String jsonString = JSONObject.toJSONString(dataList);
                        JSONArray jsonArray = JSONObject.parseArray(jsonString);
                        if (jsonArray != null && !jsonArray.isEmpty()) {
                            for (int i = 0; i < jsonArray.size(); i++) {
                                sheetMonitor.setColumnWidth(i, null == cellWidth ? DEFAULT_CELL_WIDTH : cellWidth);
                                Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);
                                if(null != map){
                                    for (int j = 0; j < titleArr.length; j++) {
                                        rowMonitor = sheetMonitor.createRow(i + 1);
                                        // 第五步,写入实体数据 实际应用中这些数据从数据库得到,
                                        for (int k = 0; k < attrArr.length; k++) {
                                            if (map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {
                                                // 第四步,创建单元格,并设置值
                                                if(!StringUtils.isEmpty(map.get(attrArr[k]))){
                                                    String value = String.valueOf(map.get(attrArr[k]));
                                                    rowMonitor.createCell(k).setCellValue(value);
                                                }else{
                                                    rowMonitor.createCell(k).setCellValue("");
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                wb.write(fout);
            }catch (Exception e){
                logger.error("导出Excel失败,Exception:" + e);
                logger.info("导出Excel失败,Exception:{0}", e);
            }finally {
                try {
                    if(null != fout){
                        fout.close();
                    }
                }catch (Exception e){
                    logger.error("关闭流异常:[{}]",e);
                }
            }
        }
    
        /**
         * 导出 exccel 到浏览器工具类(1个sheet导出)
         * @param dataListForMap
         * sheetName:       sheet名字
         * titleArr:        表头中文名数组
         * attrArr:         数据对应的实体类属性数组(与表头一一对应)
         * dataListForMap:  导出的数据list
         */
        public static void buildExcelTemplate(Map<String, Object> dataListForMap, HttpServletResponse response, String fileName, Integer cellWidth) {
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
            Object sheetName = dataListForMap.get("sheetName");
            HSSFSheet sheetMonitor = wb.createSheet(!StringUtils.isEmpty(sheetName) ? sheetName + "" : SHEET_NAME);
            // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow rowMonitor = sheetMonitor.createRow(0);
            try{
                out = setResponseHeaderFileName(response,fileName);
                //创建excel标题,设置列宽
                createTitle(wb, rowMonitor, dataListForMap, sheetMonitor, cellWidth);
                //写入数据
                writeData(sheetMonitor,1,dataListForMap);
                wb.write(out);
            }catch (Exception e){
                logger.info("导出Excel失败,Exception:{}",e);
            }finally {
                try {
                    out.close();
                }catch (Exception e){
                    logger.error("关闭流异常:[{}]",e);
                }
            }
        }
    
    
        /**
         * 导出多个 sheet 到浏览器
         * @param mapList
         *      * sheetName:   sheet名字
         *      * titleArr:    表头中文名数组
         *      * attrArr:     数据对应的实体类属性数组(与表头一一对应)
         *      * dataList:    到处的数据list
         * @param response
         * @param fileName      文件名称
         */
        public static void buildExcelTemplate(List<Map<String, Object>> mapList, HttpServletResponse response, String fileName, Integer cellWidth) {
            HSSFWorkbook wb = new HSSFWorkbook();
            if(null != mapList && !mapList.isEmpty()){
                try{
                    for (int i=0; i<mapList.size(); i++) {
                        Map<String, Object> dataListForMap = mapList.get(i);
                        // 第二步,在webbook中添加一个sheet,对应Excel文件中的sheet
                        Object sheetName = dataListForMap.get(SHEET_NAME_STR);
                        String sheetNameRt = !StringUtils.isEmpty(sheetName) ? sheetName + "" : SHEET_NAME + (i+1);
                        HSSFSheet sheetMonitor = wb.createSheet(sheetNameRt);
                        // 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
                        HSSFRow rowMonitor = sheetMonitor.createRow(0);
                        out = setResponseHeaderFileName(response,fileName);
                        //创建excel标题
                        createTitle(wb, rowMonitor, dataListForMap, sheetMonitor, cellWidth);
                        //写入数据
                        writeData(sheetMonitor,1,dataListForMap);
                    }
                    wb.write(out);
                }catch (IOException e){
                    e.printStackTrace();
                    logger.info("导出Excel失败,Exception:{}",e);
                }catch (Exception e) {
                    logger.info("导出Excel失败,Exception:{}",e);
                }finally {
                    try {
                        out.close();
                    }catch (Exception e){
                        logger.error("关闭流异常:[{}]",e);
                    }
                }
            }
        }
    
        /**
         * 创建excel标题
         * @param dataListForMap
         */
        private static void createTitle(HSSFWorkbook wb, HSSFRow rowMonitor, Map<String, Object> dataListForMap, HSSFSheet sheetMonitor, Integer cellWidth){
            String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);
    
            //居中样式
            HSSFCellStyle centerStyle = ExportExcelUtils.getColumnTopStyle(wb);
    
            for (int i = 0; i < titleArr.length; i++) {
                // 设置表格宽度(自适应)
                sheetMonitor.setColumnWidth(i, null == cellWidth ? DEFAULT_CELL_WIDTH : cellWidth);
                HSSFCell cellMonitor = rowMonitor.createCell(i);
                cellMonitor.setCellStyle(centerStyle);
                cellMonitor.setCellValue(titleArr[i]);
            }
        }
    
        /**
         * 写入数据
         * @param sheetMonitor
         * @param startRow
         * @param dataListForMap
         */
        public static void writeData(HSSFSheet sheetMonitor, Integer startRow, Map<String, Object> dataListForMap){
            String[] titleArr = (String[]) dataListForMap.get(TITLE_ARR_STR);
            String[] attrArr = (String[]) dataListForMap.get(ATTR_ARR_STR);
            Object dataList = dataListForMap.get(DATA_LIST_STR);
    
            String jsonString = JSONObject.toJSONString(dataList);
            JSONArray jsonArray = JSONObject.parseArray(jsonString);
            if (jsonArray != null && !jsonArray.isEmpty()) {
                for (int i = 0; i < jsonArray.size(); i++) {
                    Map<String, Object> map = JSONObject.parseObject(JSONObject.toJSONString(jsonArray.get(i)), Map.class);
                    for (int j = 0; j < titleArr.length; j++) {
                        HSSFRow rowMonitor = sheetMonitor.createRow( i + startRow);
                        // 第五步,写入实体数据 实际应用中这些数据从数据库得到,
                        for (int k = 0; k < attrArr.length; k++) {
                            if (map != null && attrArr[k] != null && map.containsKey(attrArr[k]) && !StringUtils.isEmpty(map.get(attrArr[k]))) {
                                // 第四步,创建单元格,并设置值
                                if(!StringUtils.isEmpty(map.get(attrArr[k]))){
                                    String value = String.valueOf(map.get(attrArr[k]));
                                    rowMonitor.createCell(k).setCellValue(value);
                                }else{
                                    rowMonitor.createCell(k).setCellValue("");
                                }
                            }
                        }
                    }
                }
            }
        }
    
        /**
         * 列头单元格样式
         * @param workbook
         * @return
         */
        private static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
    
            // 设置字体
            HSSFFont font = workbook.createFont();
            //设置字体大小
            font.setFontHeightInPoints((short)12);
            //字体加粗
            font.setBold(true);
            //设置字体名字
            font.setFontName("Courier New");
            //设置样式;
            HSSFCellStyle style = workbook.createCellStyle();
            //设置底边框;
            style.setBorderBottom(BorderStyle.THIN);
            //设置底边框颜色;
            style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
            //设置左边框;
            style.setBorderLeft(BorderStyle.THIN);
            //设置左边框颜色;
            style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
            //设置右边框;
            style.setBorderRight(BorderStyle.THIN);
            //设置右边框颜色;
            style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
            //设置顶边框;
            style.setBorderTop(BorderStyle.THIN);
            //设置顶边框颜色;
            style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
    
            style.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
            style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
            //在样式用应用设置的字体;
            style.setFont(font);
            //设置自动换行;
            style.setWrapText(false);
            //设置水平对齐的样式为居中对齐;
            style.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直对齐的样式为居中对齐;
            style.setVerticalAlignment(VerticalAlignment.CENTER);
    
            return style;
    
        }
    
        /**
         * 列数据信息单元格样式
         * @param workbook
         * @return
         */
        private HSSFCellStyle getStyle(HSSFWorkbook workbook) {
            // 设置字体
            HSSFFont font = workbook.createFont();
            //设置字体大小
            font.setFontHeightInPoints((short)12);
            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.setFillBackgroundColor(HSSFColor.HSSFColorPredefined.BRIGHT_GREEN.getIndex());
            style.setFont(font);
            //设置自动换行;
            style.setWrapText(false);
            //设置水平对齐的样式为居中对齐;
            style.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直对齐的样式为居中对齐;
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            return style;
    
        }
    
        /**
         * 判断是否是整数
         * @param str
         * @return
         */
        private static Pattern pattern = Pattern.compile("^\\d+$");
        public static boolean isIntNum(String str) {
            Matcher isIntNum = pattern.matcher(str);
            if (!isIntNum.matches()) {
                return false;
            }
            return true;
        }
    
        /**
         * 判断是否是浮点数
         * @param str
         * @return
         */
        private static Pattern pattern2 = Pattern.compile("\\d+\\.\\d+$|-\\d+\\.\\d+$");
        public static boolean isDoubleNum(String str) {
            Matcher isDoubleNum = pattern2.matcher(str);
            if (!isDoubleNum.matches()) {
                return false;
            }
            return true;
        }
    }
    

    简单使用

    @ApiOperation(value = "导出成本统计")
        @RequestMapping(value = "/exportList",method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_UTF8_VALUE)
        public void ExportConsumeStatis(@RequestBody ParamsCost obj,
                                        HttpServletResponse response){
            logger.info("导出成本统计,ParamsConsume:{}",obj);
            try{
                if(null == obj.getEffectStartTime() || null == obj.getEffectEndTime()){
                    throw new MyException("开始时间和结束时间不能为空!", HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
                }
                String startStr = GeoDateUtils.getDateStr(obj.getEffectStartTime(), GeoDateUtils.DATE_FORMAT2);
                String endStr = GeoDateUtils.getDateStr(obj.getEffectEndTime(), GeoDateUtils.DATE_FORMAT2);
                String fileName = startStr + "-" + endStr + "(成本统计)";
                List<CostStatisVo> consumeStatisVos = costStatisService.costStatisList(obj);
                if (null != consumeStatisVos) {
                    consumeStatisVos.add(costStatisService.costStatisTotal(obj));
                }
                
                // 拼接 Excel 数据
                Map<String,Object> map = new HashMap<>(3);
                String[] titleArr = {"日期","产品","客户","用户","用户账号","用户ID","运营商","内部接口","外部接口","计费数","金额"};
                String[] attrArr = {"dayFlag","productName","companyName","cuserName","account","cuserId","isp","interfaceName","outInterfaceName","countCharging","cost"};
                map.put("titleArr",titleArr);
                map.put("attrArr",attrArr);
                map.put("dataList", consumeStatisVos);
                ExportExcelUtils.buildExcelTemplate(map, response, fileName,4000);
            }catch (Exception e) {
                e.printStackTrace();
                logger.error("导出消耗统计,Exception:",e);
            }
        }

     

    展开全文
  • Springboot导出Excel表格

    2020-07-17 19:04:14
    @ApiOperation("后台管理-导出开奖信息") @IgnoreAuth @GetMapping("exportPrizeExchangeInfo") public void exportPrizeExchangeInfo(BevActivitySettingIdReq req){ response.setCharacterEncoding("utf-8");...

    前言:

    项目需求:客户点击 “导出” 按钮将下载一个excel表格到本地电脑。
    需求分析:前端传一个id,后台根据id查询出数据列表并导出;

    实现效果:

    在这里插入图片描述

    代码实现过程:

    一.引入依赖:

            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-spring-boot-starter</artifactId>
                <version>4.1.3</version>
            </de
    展开全文
  • 导出Excel文件是Web系统中极为常见的一个操作,今天我们来学习使用easypoi+SpringBoot来实现Excel文件的导出功能。

    导出Excel文件是Web系统中极为常见的一个操作,今天我们来学习使用easypoi+SpringBoot来实现Excel文件的导出功能。

    引入依赖

    该项目既然是结合SpringBoot来进行开发的,那么我们直接导出easypoi的SpringBoot的启动器依赖,至于SpringBoot的其他依赖,可以参考我以前的SpringBoot项目依赖。

    <!--excel操作-->
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-spring-boot-starter</artifactId>
        <version>3.3.0</version>
    </dependency>
    

    注解实体类

    我们可以使用@Excel注解来标注实体类属性,同时配置实体类属性的导出参数。

    @Data
    @TableName("user")
    public class User implements Serializable {
        @TableId(type = IdType.AUTO)
        @Excel(name = "ID", orderNum = "0", width = 15)
        private Integer id;
        @Excel(name = "用户名", orderNum = "1", width = 15)
        private String username;
        @Excel(name = "姓名", orderNum = "2", width = 15)
        private String name;
    	@Excel(name = "性别", orderNum = "3", width = 15)
        private Integer gender;
        @Excel(name = "出生日期", orderNum = "4", width = 30, format = "yyyy-MM-dd")
        private Date birthday;
        @Excel(name = "手机号码", orderNum = "5", width = 15)
        private String phone;
    }
    
    

    ExcelUtils

    /**
     * @description: Excel导入导出工具类
     */
    @Component
    public class ExcelUtils {
    
        /**
         * 对外的导出excel方法,exportExcel重载方法
         * 该方法通过传入ExportParams对象参数指定标题和表名称
         *
         * @param fileName     文件名称
         * @param exportParams ExportParams对象,导出参数
         * @param exportClass  导出对象的字节码对象
         * @param exportData   导出的数据
         * @param response     HttpServletResponse对象
         * @throws IOException IO异常
         */
        public static void exportExcel(String fileName, ExportParams exportParams, Class<?> exportClass, List<?> exportData, HttpServletResponse response) throws IOException {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, exportClass, exportData);
            downloadExcel(fileName, workbook, response);
        }
    
        /**
         * 对外的导出excel方法,exportExcel重载方法
         * 该方法可以指定文件名称、标题名称、表名称
         *
         * @param fileName    文件名称
         * @param title       excel标题
         * @param sheetName   excel表名
         * @param exportClass 导出对象的字节码对象
         * @param exportData  导出的数据
         * @param response    HttpServletResponse对象
         * @throws IOException IO异常
         */
        public static void exportExcel(String fileName, String title, String sheetName, Class<?> exportClass, List<?> exportData, HttpServletResponse response) throws IOException {
            ExportParams exportParams = new ExportParams();
            exportParams.setTitle(title);
            exportParams.setCreateHeadRows(true);
            exportParams.setSheetName(sheetName);
            exportParams.setType(ExcelType.XSSF);
            defaultExport(fileName, exportParams, exportClass, exportData, response);
        }
    
        /**
         * 对外的导出excel方法,exportExcel重载方法(简化方法)
         * 该方法可以指定文件名称,默认标题和表名称为文件名称
         *
         * @param fileName    文件名称
         * @param exportClass 导出对象的字节码对象
         * @param exportData  导出的数据
         * @param response    HttpServletResponse对象
         * @throws IOException IO异常
         */
        public static void exportExcel(String fileName, Class<?> exportClass, List<?> exportData, HttpServletResponse response) throws IOException {
            ExportParams exportParams = new ExportParams();
            exportParams.setTitle(fileName);
            exportParams.setCreateHeadRows(true);
            exportParams.setSheetName(fileName);
            exportParams.setType(ExcelType.XSSF);
            defaultExport(fileName, exportParams, exportClass, exportData, response);
        }
    
        /**
         * 默认导出方法,私有
         *
         * @param fileName     文件名称
         * @param exportParams ExportParams对象,导出参数
         * @param exportClass  导出对象的字节码对象
         * @param exportData   导出的数据
         * @param response     HttpServletResponse对象
         * @throws IOException IO异常
         */
        private static void defaultExport(String fileName, ExportParams exportParams, Class<?> exportClass, List<?> exportData, HttpServletResponse response) throws IOException {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, exportClass, exportData);
            downloadExcel(fileName, workbook, response);
        }
    
    
        /**
         * 下载Excel,私有
         *
         * @param fileName 文件名称
         * @param workbook Workbook对象
         * @param response HttpServletResponse对象
         * @throws IOException IO异常
         */
        private static void downloadExcel(String fileName, Workbook workbook, HttpServletResponse response) throws IOException {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        }
    }
    

    导出Excel

    @GetMapping("export")
    public ResponseEntity<Void> export(HttpServletResponse response) throws Exception {
        List<User> exportData = userService.findList();
        ExcelUtils.exportExcel("用户列表",User.class,exportData,response);
        return ResponseEntity.ok().build();
    }
    

    该ExcelUtils导出工具类,导出的是.xlsx后缀类型的Excel文件,如果需要导出.xls后缀类型的文件,需要将工具类中response设置的Content-Disposition属性修改为application/vnd.ms-excel即可。

    展开全文
  • springboot导出excel

    千次阅读 2019-01-19 17:59:12
    //生成本地 /*File f = new File("c:/test.xlsx"); FileOutputStream out = new FileOutputStream(f); ExportExcelUtils.exportExcel(data, out); out.close();*/ ExportExcelUtils.exportExcel(response,...
  • springmvc/springboot导出excel

    千次阅读 2018-01-08 14:46:32
    1、pom引用POI组件 org.apache.poi ...2、自定义导出excel数据格式 package com.springcloud; import java.io.Serializable; import java.util.List; public class ExcelData implements Serializa
  • SpringBoot导出Excel表格

    2018-08-21 10:36:51
    7、编写Excel工具类(设置你要导出Excel表格样式,基本不需要修改) import javax .servlet .http .HttpServletResponse ; import java .io .OutputStream ; import java .util .List ; import org .apache...
  • --excel工具--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency> <dep
  • SpringBoot导出Excel,Apache-POI插件实现

    千次阅读 2019-05-16 15:42:08
    Java无模板导出Excel,Apache-POI插件实现开发环境Maven依赖工具类接口层和业务层其他区相关代码ServiceMapperMapper.xml视图其它效果展示预留占位 开发环境 jdk 1.8 Maven 3.6 Tomcat 8.5 SpringBoot ...
  • } //这一步是保存到本地, EasyExcel.write(fileName, Form.class).sheet("课程评价").doWrite(forms); //从本地下载文件 download(fileName,response,name); return "success"; } //下载excel文件 public void ...
  • Springboot Excel 导出

    2019-11-26 16:22:45
    springboot 实现导出Excel 在开发项目中需要实现一个Excel导出所有的数据,保存在本地,这里说一下自己的实现思路,需要的同学可以借鉴一下,不对的地方还请大佬指点出来,改进一下自己的不足: 建一个controller层...
  • 功能说明:读取指定路径excel文件模板并写入数据 生成文件另存为指定路径 生成实体类存入数据库(可根据自己的字段修改实体类即可) 需修改地方:数据库配置 到处excel模板及字段 项目下载地址:...
  • Spring Boot导出Excel文件理解导出的业务如何显示在Excel中使用Apache POI如何编写代码问题一,为什么使用数据流不使用文件流?问题二:2003版本Excel和2007版本的Excel?问题三:这个时候抛出一个问题就是,如果...
  • SpringBoot 数据导出excel

    千次阅读 2019-06-05 16:38:47
    SpringBoot导出数据的思路是自定义xls视图解析器 1、maven <!-- Apache POI Microsoft Documents API --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-...
  • SpringBoot实现excel表格导出...--导出excel--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>1.1.2-beta5</version>
  • springboot数据导出excel自定义封装工具 实例 public class Client { private Integer id; private String bianhao; private String name; private String phone; private String remark; private ...
  • SpringBoot 导入导出Excel

    2021-03-31 14:09:41
    简介: Java解析、生成Excel比较有名的...easyexcel重写了poi对07版Excel的解析,能够原本一个3M的excel用POI sax依然需要100M左右内存降低几M,并且再大的excel不会出现内存溢出,03版依赖POI的sax模式。在上层.
  • 提到Excel导出功能,可能很多人都使用springmvc框架做过,笔者今天要给大家分享的是基于springBoot开发Excel复杂模板导出功能(所谓复杂模板指在模板里的特定表头里有不同的单元格合并以及背景色,字体颜色的填充,...
  • springboot poi 导出excel

    千次阅读 2018-01-20 16:26:17
    至于poi的用法就不多说了,网上多得很,但是发现spring boot结合poi的就不多了,而且大多也有各种各样的问题。 ... ...public class ExcelData implements Serializable { private static final lo
  • SpringBoot导出export

    2021-02-27 10:43:13
    SpringBoot导出export导入依赖(poi实现excel导入导出)Controller层Service层ServiceImpl层FileUtils上传工具类 导入依赖(poi实现excel导入导出) <!--poi实现excel导入导出--> <dependency> <...
  • Springboot项目导出excel表功能

    千次阅读 2018-09-17 16:13:19
    修改list内容即可 ... import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import java.io.ByteArrayInputStream;...import java.io.ByteArrayOutputStream;...//这行是直接写到本地的 ...
  • SpringBoot Excel导出

    2018-11-09 15:33:32
    public R export( List&amp;amp;amp;lt;ClinicHistoryEntity&amp;amp;... clinicHistoryEntityList= clinicHistoryService.... //excel标题 String[] title = {&amp;amp;quot;姓名&amp;amp;quot
  • 画不多说先上成功后导出的pdf文件 2.maven依赖 <dependency> <groupId>com.itextpdf</groupId> <artifactId>itextpdf</artifactId> <version>5.4.4</version> ...
  • springboot poi导出excel表格

    千次阅读 2018-07-12 23:20:23
    导致小白会觉得有点画蛇添足,我们先考虑完成导出excel表这个需求才能考虑继续深造。 构造一个表从两方面考虑,一方面是结构,一方面是数据。 下面这个工具栏提供的方法很简单,就是传入一个excel表名,excel每...
  • SpringBoot实现Excel读取

    千次阅读 多人点赞 2020-12-20 11:34:51
    SpringBoot实现Excel读取 SpringBoot实现Excel写入在另一篇文章 文章地址: 。。。 这是本人写的一个SpringBootExcel读取的方法,实测能用,待提升的地方有很多,有不足之处请多多指点。 Excel2003版(后缀为....
  • Springboot项目导出Excel静态模板

    千次阅读 2019-03-08 11:02:31
    在给客户开发的企业系统中需要统计一些东西,这样就需要填写不同的Excel表格,为了不让用户填写的Excel表格五花八门给开发人员造成困扰则将已经定义好的Excel模板放在系统里 好了,直接贴代码 模板放置的位置 ...
  • 后台管理系统经常会用到表格来展示数据,如用户基本信息,若管理员需要将用户信息保存到本地,则需要对用户信息进行导出导出的文件大多采用excel。 java中对于excel的读写可以采用POI 一、POI简介 Apache POI是...
  • 使用SpringBoot导出excel模板文件,防止用户导入错误。 先看是否需要在下载,功能演示都放在这篇文章了:https://truedei.blog.csdn.net/article/details/105325018 下载地址:点我下载 项目结构 controller层 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 842
精华内容 336
关键字:

springboot导出excel到本地

spring 订阅