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

jxl方式

1.pom文件

<!--execl导出-->
<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.youkuaiyun.com/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

<!--easypoi-->
        <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.测试数据