Java中数据做Execl表格jxl 和 poi 和easyPoi三种方式导出

墨蓝 2021-12-12 13:23 428阅读 0赞

# jxl方式 #

1.pom文件

<dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.12</version>
    </dependency>

引包注意,包比较多容易混淆  Label类包为   package jxl.write;

//导出execl
        public void  returnApplyExcelDerive(HttpServletResponse response ,List<String> memberIds) {
            //根据用户id查出需要导出的数据集合
            List<CouponMember> list=couponHistoryMapper.selectBymemberIds(memberIds);
            List<Label> labelList=new ArrayList<>();
            //要插入到的Excel表格的行号，默认从0开始 建表头
            Label memberId= new Label(0,0,"用户ID");
            Label phone= new Label(1, 0, "用户账号");//表示第
            Label username= new Label(2, 0, "用户昵称");
            Label levelName= new Label(3, 0, "会员等级");
            Label used= new Label(4, 0, "已使用");
            Label unused= new Label(5, 0, "未使用");
            Label expired= new Label(6, 0, "已过期");
            labelList.add(memberId);
            labelList.add(phone);
            labelList.add(username);
            labelList.add(levelName);
            labelList.add(used);
            labelList.add(unused);
            labelList.add(expired);
            if (TargetUtils.isEmpty(list)||TargetUtils.isEmpty(labelList)){
                throw new CouPonException("导出execl传入参数错误");
            }
            // 设置文件名
            long time=System.currentTimeMillis();
            StringBuffer stringBuffer=new StringBuffer(String.valueOf(time));
            String filename = stringBuffer.append("coupon.xls").toString();
            OutputStream out = null;
            try {
                //文件名防止中文乱码设置UTF-8
                response.setCharacterEncoding("UTF-8");
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("Content-disposition", "attachment; filename="
                        + new String(filename.getBytes("utf-8"), "ISO-8859-1"));
                out = response.getOutputStream();
                // 创建写工作簿对象，这里直接采用流输出，而不会再生成一个文件
                WritableWorkbook wwb = Workbook.createWorkbook(out);
                // 创建工作表
                WritableSheet ws = wwb.createSheet("优惠券查询", 0);
                // 设置字体;
                WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
                WritableCellFormat cellFormat = new WritableCellFormat(font);
                // 设置背景颜色;
                cellFormat.setBackground(Colour.WHITE);
                // 设置边框;
                cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);
                // 设置文字居中对齐方式;
                cellFormat.setAlignment(Alignment.CENTRE);
                // 设置垂直居中;
                cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
                // 分别给1,2,7列设置不同的宽度;
                ws.setColumnView(1, 20);
                ws.setColumnView(2, 30);
                ws.setColumnView(7, 30);
                // 给sheet电子版中所有的列设置默认的列的宽度;
                ws.getSettings().setDefaultColumnWidth(20);
                // 给sheet电子版中所有的行设置默认的高度，高度的单位是1/20个像素点,但设置这个貌似就不能自动换行了
                // sheet.getSettings().setDefaultRowHeight(30 * 20);
                // 设置自动换行;
                cellFormat.setWrap(true);
                //excel导入表头数据
                for(Label label:labelList){
                    ws.addCell(label);
                }
                // 给第二行设置背景、字体颜色、对齐方式等等;
                WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
                WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
                // 设置文字居中对齐方式;
                cellFormat2.setAlignment(Alignment.CENTRE);
                // 设置垂直居中;
                cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
                cellFormat2.setBackground(Colour.WHITE);
                cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
                cellFormat2.setWrap(true);
                String applyStatus="";
                //遍历导入数据
                for (int i = 0; i < list.size(); i++) {
                    Label memberId2= new Label(0, i+1, list.get(i).getMemberId()+"");
                    Label phone2= new Label(1, i+1, list.get(i).getPhone()+"");
                    Label username2= new Label(2, i+1, list.get(i).getUsername()+"");
                    Label levelName2= new Label(3, i+1, list.get(i).getLevelName()+"");
                    Label used2 = new Label(4, i + 1, list.get(i).getUsed() + "");
                    Label unused2= new Label(5, i+1, list.get(i).getUnused() + "");
                    Label expired2= new Label(6, i+1, list.get(i).getExpired()+"");
                    ws.addCell(memberId2);
                    ws.addCell(phone2);
                    ws.addCell(username2);
                    ws.addCell(levelName2);
                    ws.addCell(used2);
                    ws.addCell(unused2);
                    ws.addCell(expired2);
                }
                //写进文档
                wwb.write();
                // 关闭Excel工作簿对象
                out.flush();
                wwb.close();
                out.close();
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    
        }

# poi方式,execl文件写入硬盘+以流形式返回前端下载 #

### poi使用api教程 [https://blog.csdn.net/qq\_21137441/article/details/79226171][https_blog.csdn.net_qq_21137441_article_details_79226171] ###

1.pom.xml

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>

2.代码.

public void daochu(HttpServletResponse response) {
            OutputStream out = null;
            //模拟导出数据
            List<Card> cardList = new ArrayList<>();
            Card card;
            for (int i = 0; i <= 10; i++) {
                card = new Card("卡号" + i, "卡名称" + (i + 1));
                cardList.add(card);
            }
            //导出数据列名
            String[] strArray = {"储值卡id", "储值卡号"};
            File file = new File("/data/home/ceshi/");
            if (!file.exists()) {
                file.mkdirs();
            }
            SimpleDateFormat fmt = new SimpleDateFormat("yyyyMMddhhmmss");
            // 给要导出的文件起名为 "测试导出数据表_时间.xls"
            String fileName = fmt.format(new Date()) + ".xls";
            HSSFWorkbook wb = new HSSFWorkbook();
            // 第二步，创建工作表(Sheet)
            HSSFSheet sheet = wb.createSheet("sheet1");
            sheet.setDefaultColumnWidth(20);// 默认列宽
            // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            HSSFRow row = sheet.createRow(0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            // 创建一个居中格式
            style.setAlignment(HorizontalAlignment.CENTER);//水平居中
            // 第一行赋值表头标题
            HSSFCell cell;
            for (int i = 0; i < strArray.length; i++) {
                cell = row.createCell(i);
                cell.setCellValue(strArray[i]);
                cell.setCellStyle(style); //样式居中
            }
            // 第五步，将数据赋值到execl对象
            int i = 1; //行数从0开始创建 第0行字段描述 ,所以从1 第二行开始
            int z = 0; //循环取值,然后给execl每行赋值
            while (z < cardList.size()) {
                    row = sheet.createRow(i); //创建第i行
                    Card card1 = cardList.get(z); //取值第z组数据
                    row.createCell(0).setCellValue(card1.getCardId()); //第i行的第一列数据赋值
                    row.createCell(1).setCellValue(card1.getCardName()); //第i行的第二列数据赋值
                i++; //赋值完++换第二行
                z++; //取值完一组数据换下一组
            }
            // 第六步，将文件存到指定位置
            try {
                //文件写入位置名称
                FileOutputStream fout = new FileOutputStream("D://Config/" + fileName);
                wb.write(fout);
                fout.close();
                //文件以流形式返回前端下载
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
                response.setContentType("application/x-download");
                response.setCharacterEncoding("UTF-8");
                response.addHeader("Pargam", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
                response.flushBuffer();
                out = response.getOutputStream();
                wb.write(out);
                out.flush();
            } catch (Exception e) {
                e.printStackTrace();
            }
    
        }

# easyPOI #

1.依赖pom.xml

<dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>3.2.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>3.2.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>3.2.0</version>
            </dependency>

2.定义需要导出的实体

@Data
    public class SignRecordExcelModel implements Serializable {
    
        @Excel(name = "序号",orderNum = "0",width = 15)
        private Integer number;
    
        @Excel(name = "人员名称",orderNum = "1",width = 15)
        private String employeeName;
    
        @Excel(name = "座位号",orderNum = "2",width = 15)
        private Integer seatNum;
    
        @Excel(name = "签到状态",orderNum = "3",width = 15)
        private String signStatusStr;
    
        @Excel(name = "测试状态1",orderNum = "4",width = 15)
        private String demo1;
    
        @Excel(name = "测试状态2",orderNum = "5",width = 15)
        private String demo2;
    
        @Excel(name = "测试状态3",orderNum = "6",width = 15)
        private String demo3;
    
        //字段导出名称 , 导出位置第几个 ,占比行宽默认值10
        @Excel(name = "测试状态4",orderNum = "7",width = 15 )
        @ExcelIgnore //忽略此字段,不导出
        private String demo4;
    
        @Excel(name = "测试状态5",orderNum = "8",width = 15)
        private String demo5;
    
        @Excel(name = "测试状态6",orderNum = "9",width = 15)
        private String demo6;
    
        @Excel(name = "测试状态7",orderNum = "10",width = 15)
        private String demo7;
    }

3.测试导出

@PostMapping(value = "/export")
        @ApiOperation(value = "导出execl2")
        public void  easyExport(HttpServletResponse response){
            //测试数据 赋值处理
            List<SignRecordExcelModel> signRecordExcelModels = new ArrayList<>();
            SignRecordExcelModel excelModel;
            for (int i = 0; i < 10000; i++) {
                excelModel=new SignRecordExcelModel();
                excelModel.setEmployeeName("zhang"+i);
                excelModel.setNumber(i);
                excelModel.setSeatNum(i);
                excelModel.setSignStatusStr("sheng"+i);
                excelModel.setDemo1("demo1   "+i);
                excelModel.setDemo2("demo2   "+i);
                excelModel.setDemo3("demo3   "+i);
                excelModel.setDemo4("demo4   "+i);
                excelModel.setDemo5("demo5   "+i);
                excelModel.setDemo6("demo6   "+i);
                excelModel.setDemo7("demo7   "+i);
                signRecordExcelModels.add(excelModel);
            }
            //设置标题title ,和sheetName
            ExportParams exportParams = new ExportParams("标题", "sheet1");
            Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams, SignRecordExcelModel.class , signRecordExcelModels);
            ExcelExportUtil.closeExportBigExcel();
            downLoadExcel("海贼王.xls", response, workbook);
        }
    
        private void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
            try {
                response.setCharacterEncoding("UTF-8");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                response.setHeader("content-Type", "application/vnd.ms-excel");
                workbook.write(response.getOutputStream());
                workbook.close();
                out.flush();
                out.close();
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage());
            }
        }

4.测试数据

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70][]

[https_blog.csdn.net_qq_21137441_article_details_79226171]: https://blog.csdn.net/qq_21137441/article/details/79226171
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70]: /images/20211211/439e24a79cc144f78dbda91d84df805d.png