java生成Excel表格并导出

小灰灰 2023-09-25 18:38 41阅读 0赞

1.导入相关的依赖

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.1.0</version>
            </dependency>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.5.2</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.47</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>

2.创建ExportExcel类

package www.gaozening.club.langfangdachenggaozhuanggerenxinxiguanlizhongxin.Export;
    
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import lombok.SneakyThrows;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.DateUtil;
    import org.springframework.stereotype.Controller;
    
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import java.io.IOException;
    import java.io.OutputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import static com.sun.xml.internal.ws.spi.db.BindingContextFactory.LOGGER;
    
    @Controller
    @Api(description = "导出Excel")
    public class ExportExcel {
    
        /**
         * 导出模板
         *
         * @param response
         * @param request
         * @throws IOException
         */
        @RequestMapping(value = "/download", produces = "text/html;charset=UTF-8")
        public void download(HttpServletResponse response, HttpServletRequest request) {
            //创建HSSFWorkbook对象
            try (HSSFWorkbook wb = new HSSFWorkbook()) {
                LOGGER.info("进入导出模板方法");
                //创建HSSFSheet对象
                HSSFSheet sheet = wb.createSheet("导出模板");
                //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
                HSSFRow row1 = sheet.createRow(0);
                //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
                HSSFCell cell = row1.createCell(0);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 11));
                //向合并单元格中输入字段
                cell.setCellValue("第一小区户口");
    
                //在sheet里创建第二行(下标为1)
                HSSFRow row2 = sheet.createRow(1);
                //创建单元格并设置单元格内容
                row2.createCell(0).setCellValue("ID");
                row2.createCell(1).setCellValue("户号");
                row2.createCell(2).setCellValue("与户主关系");
                row2.createCell(3).setCellValue("姓名");
                row2.createCell(4).setCellValue("性别");
                row2.createCell(5).setCellValue("年龄");
                row2.createCell(6).setCellValue("出生年月");
                row2.createCell(7).setCellValue("身份证");
                row2.createCell(8).setCellValue("手机号");
                row2.createCell(9).setCellValue("民族");
                row2.createCell(10).setCellValue("派出所");
                row2.createCell(11).setCellValue("村居委会");
    
    
                //输出Excel文件
                OutputStream output = response.getOutputStream();
                response.reset();
                //filename=后面跟文件名
                response.setHeader("Content-disposition", "attachment; filename=Community.xlsx");
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
                LOGGER.info("即将输出文件流");
                wb.write(output);
                output.flush();
                output.close();
            } catch (Exception e) {
    //            LOGGER.error("导出统计模板生成excel异常", e);
            } finally {
                Object RequestContextLocal = null;
    //            RequestContextLocal.bindUserOperation("下载模板", "客户支付流程跟踪导入和统计菜单");
            }
            LOGGER.info("方法运行结束");
        }
    
    }

3.工具类ExcelUtils

package www.gaozening.club.langfangdachenggaozhuanggerenxinxiguanlizhongxin.Utils;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.multipart.MultipartFile;
    import www.gaozening.club.langfangdachenggaozhuanggerenxinxiguanlizhongxin.enity.Community;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    public class ExcelUtils {
    
    
        //总行数
        private static int totalRows = 12;
        //总条数
        private static int totalCells = 12;
        //错误信息接收器
        private static String errorMsg;
        /**
         * 验证EXCEL文件
         *
         * @param filePath
         * @return
         */
        public static boolean validateExcel(String filePath) {
            if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
                errorMsg = "文件名不是excel格式";
                return false;
            }
            return true;
        }
        /*
            excel2003版本的excel文件是.xls格式，excel2007及以上版本的excel的是.xlsx格式。
        */
        // @描述：是否是2003的excel，返回true是2003
        public static boolean isExcel2003(String filePath)  {
            return filePath.matches("^.+\\.(?i)(xls)$");
        }
        //@描述：是否是2007的excel，返回true是2007
        public static boolean isExcel2007(String filePath)  {
            return filePath.matches("^.+\\.(?i)(xlsx)$");
    
        }
        /**
         * 读取Excel里面客户的信息
         * @param wb
         * @return
         */
        private static List<Community> readExcelValue(Workbook wb) {
            //默认会跳过第一行标题
            // 得到第一个shell
            Sheet sheet = wb.getSheetAt(0);
            // 得到Excel的行数
            totalRows = sheet.getPhysicalNumberOfRows();
            // 得到Excel的列数(前提是有行数)
            if (totalRows > 1 && sheet.getRow(0) != null) {
                totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            }
            List<Community> manychoiceList = new ArrayList<Community>();
            // 循环Excel行数
            for (int r = 1; r < totalRows; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                Community community = new Community();
                // 循环Excel的列
                for (int c = 0; c < totalCells ; c++) {
                    Cell cell = row.getCell(c);
                    if (null != cell) {
                        if (c == 0) {            //第一列
                            //如果是纯数字,将单元格类型转为String
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                                double numericCellValue = cell.getNumericCellValue();
                                community.setID((int)numericCellValue);
                            }
    
                        } else if (c == 1) {
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                                cell.setCellType(CellType.STRING);
                            }
                            community.setNumber(cell.getStringCellValue());
                        } else if (c == 2) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setRelationship(stringCellValue);
                            }
    
                        } else if (c == 3) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setName(stringCellValue);
                            }
    
                        } else if (c == 4) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setGender(stringCellValue);
                            }
    
                        } else if (c == 5) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
    //                            double numericCellValue = cell.getNumericCellValue();
    //                            community.setAge(String.valueOf((int)numericCellValue));
                                String  numericCellValue = cell.getStringCellValue();
                                community.setAge(numericCellValue);
    
                            }
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
    //                            double numericCellValue = cell.getNumericCellValue();
    //                            community.setAge(String.valueOf((int)numericCellValue));
                                double  numericCellValue = cell.getNumericCellValue();
                                community.setAge(String.valueOf((int)numericCellValue));
    
                            }
                            if (cell.getCellTypeEnum() == CellType.FORMULA) {
    //                            double numericCellValue = cell.getNumericCellValue();
    //                            community.setAge(String.valueOf((int)numericCellValue));
                                double  numericCellValue = cell.getNumericCellValue();
                                community.setAge(String.valueOf((int)numericCellValue));
    
                            }
    
    
                        } else if (c == 6) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String  numericCellValue = cell.getStringCellValue();
                                community.setCard(numericCellValue);
                            }
    
                        } else if (c == 7) {
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                                cell.setCellType(CellType.STRING);
                            } String numericCellValue = cell.getStringCellValue();
                            community.setDate(numericCellValue);
    
                        } else if (c == 8) {
                            if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                                cell.setCellType(CellType.STRING);
                            }
                            String numericCellValue = cell.getStringCellValue();
                            community.setMobile(numericCellValue);
    
                        } else if (c == 9) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setNationality(stringCellValue);
                            }
    
                        } else if (c == 10) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setStation(stringCellValue);
                            }
    
                        } else if (c == 11) {
                            if (cell.getCellTypeEnum() == CellType.STRING) {
                                String stringCellValue = cell.getStringCellValue();
                                community.setCommittee(stringCellValue);
                            }
    
                            //score属性是int数据类型，转换成int
    //                        manychoice.setScore(Integer.valueOf(cell.getStringCellValue()));
                        }
                    }
                }
                //将excel解析出来的数据赋值给对象添加到list中
                manychoiceList.add(community);
            }
            return manychoiceList;
        }
    
        /**
         * 根据excel里面的内容读取客户信息
         * @param is 输入流
         * @param isExcel2003 excel是2003还是2007版本
         * @return
         * @throws IOException
         */
        public static List<Community> createExcel(InputStream is, boolean isExcel2003) {
            try{
                Workbook wb = null;
                if (isExcel2003) {
                    // 当excel是2003时,创建excel2003
                    wb = new HSSFWorkbook(is);
                } else {
                    // 当excel是2007时,创建excel2007
                    wb = new XSSFWorkbook(is);
                }
                // 读取Excel里面客户的信息
                List<Community> communityList = readExcelValue(wb);
                return communityList;
            } catch (IOException e) {
                e.printStackTrace();
            }
            return null;
        }
    
    
        /**
         * 读EXCEL文件，获取信息集合
         * @return
         */
        public static List<Community> getExcelInfo(MultipartFile file) {
            String files = file.getOriginalFilename();//获取文件名
            try {
                if (!validateExcel(files)) {// 验证文件名是否合格
                    return null;
                }
                boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
                if (isExcel2007(files)) {
                    isExcel2003 = false;
                }
                List<Community> communityList = createExcel(file.getInputStream(), isExcel2003);
                return communityList;
            } catch (Exception e) {
                e.printStackTrace();
            }
            return null;
        }
    
    }

4.实体类

package cn.java999.uploadandparseexcel.entity;
    
    
    import com.baomidou.mybatisplus.annotation.TableId;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import org.springframework.stereotype.Component;
    
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Component
    public class Community {
    
    
        @TableId
        private Integer ID;
        private String number;
        private String relationship;
        private String name;
        private String gender;
        private String age;
        private String Date;
        private String card;
        private String Mobile;
        private String nationality;
        private String station;
        private String committee;
    
    
    }

5.mapper

package cn.java999.uploadandparseexcel.mapper;
    
    import cn.java999.uploadandparseexcel.entity.Community;
    import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import org.apache.ibatis.annotations.Mapper;
    
    @Mapper
    public interface Communitymapper extends BaseMapper<Community> {
    
    }

![385d775226964c0197301e7e3e4170ab.png][]

6.导出流程

![c1a5ab7208944ecd8dbbeccc98b71992.png][]

7.开始导出下载

![6d7d3b17712e4d89aacc8e34ea1401fb.png][]

8.下载完成

![98d2a23bff614696bf6b8e876e83e7ba.png][]

9.导出效果

![22d65e6fb67f4e9183bdf3400b9a963e.png][]

总结：到这里我们用java生成Excel表格并导出就完成了，请各位老铁浏览时点个赞并收藏以免用到时找不到，如有不足的地方请评论在下方

[385d775226964c0197301e7e3e4170ab.png]: https://img-blog.csdnimg.cn/385d775226964c0197301e7e3e4170ab.png
[c1a5ab7208944ecd8dbbeccc98b71992.png]: https://img-blog.csdnimg.cn/c1a5ab7208944ecd8dbbeccc98b71992.png
[6d7d3b17712e4d89aacc8e34ea1401fb.png]: https://img-blog.csdnimg.cn/6d7d3b17712e4d89aacc8e34ea1401fb.png
[98d2a23bff614696bf6b8e876e83e7ba.png]: https://img-blog.csdnimg.cn/98d2a23bff614696bf6b8e876e83e7ba.png
[22d65e6fb67f4e9183bdf3400b9a963e.png]: https://img-blog.csdnimg.cn/22d65e6fb67f4e9183bdf3400b9a963e.png