使用POI导出文件和使用EasyExcel导出数据

 使用POI的方式导出数据：

import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class ExportTest {

    public static final String TAB = "  ";

    @Test
    public void test() throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet();
        List<User> list = new ArrayList<>();
        User user3 = initData(list);
        list.add(user3);
        List<String> field = new ArrayList<>();
        field.add("id");
        field.add("name");
        field.add("age");
        field.add("sex");

        //设置导出格式
        XSSFCellStyle cellStyle = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeight((short) 16);
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);

        XSSFRow row = sheet.createRow(0);

        for (int i = 0; i < field.size(); i++) {
            row.createCell(i).setCellValue(field.get(i));
        }
        for (int i = 0; i < list.size(); i++) {
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(list.get(i).getId());
            row.createCell(1).setCellValue(list.get(i).getName());
            row.createCell(2).setCellValue(list.get(i).getAge());
            row.createCell(3).setCellValue(list.get(i).getSex());
        }


        OutputStream os = new FileOutputStream("output.xls");
        wb.write(os);


        //通过文件输入流读取数据 读取到的数据是字节型数据 没啥卵用。机器才看得懂
        FileInputStream is = new FileInputStream("output.xls");
        byte[] bytes = new byte[(int) new File("output.xls").length()];
        int len = 0;
        int temp = 0;
        StringBuilder sb = new StringBuilder();
        while((temp=is.read())!=-1){
            bytes[len] = (byte)temp;
            len++;
        }
        is.close();
        for (int i = 0; i < bytes.length; i++) {
            sb.append(bytes[i]);
        }
        System.out.println(sb.toString());


        //通过XSSFWorkbook获取到指定的sheet 然后读取我刚才插进去的数据 看是否正确
        XSSFSheet sheetAt = wb.getSheetAt(0);
        for (int i = 0; i < list.size(); i++) {
            row = sheetAt.getRow(i + 1);
            System.out.println(row.getCell(0).getNumericCellValue() + TAB + row.getCell(1).getStringCellValue() + TAB
                    + row.getCell(2).getNumericCellValue() + TAB + row.getCell(3).getStringCellValue());
        }

        os.close();
        wb.close();
    }

    private User initData(List<User> list) {
        User user = new User(1234, "小松", 24, "男");
        User user2 = new User(1224, "小松松", 27, "男");
        User user3 = new User();
        user3.setName("小李子");
        user3.setAge(20);
        list.add(user);
        list.add(user2);
        return user3;
    }
}

import lombok.Data;

@Data
public class User {
    private int id;
    private String name;
    private int age;
    private String sex;

    public User(){

    }

    public User(int id, String name, int age, String sex) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
    }
}

 

 使用EasyExecl导出数据：

    这种方式基于注解设置value值对应excel表中的字段值 index 对应位置 更加方便使用

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Font;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.metadata.TableStyle;
import com.alibaba.excel.support.ExcelTypeEnum;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelWriteTest {

    @Test
    public void writeWithHead() throws IOException {
        try (OutputStream out = new FileOutputStream("RepaireSettle.xls");) {
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX);
            Sheet sheet1 = new Sheet(1, 0, RepaireSettleVO.class);
            sheet1.setSheetName("sheet1");
            List<RepaireSettleVO> data = new ArrayList<>();
            initData(data);
            initStyle(sheet1);
            writer.write(data, sheet1);
            writer.finish();
        }
    }

    private void initStyle(Sheet sheet1) {
        sheet1.setAutoWidth(true);
        //使用这个之后可以保证居中显示
        TableStyle ts = new TableStyle();
        ts.setTableContentBackGroundColor(IndexedColors.WHITE);
        sheet1.setTableStyle(ts);

        //设置字体
        Font font = new Font();
        font.setBold(true);
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);
        ts.setTableContentFont(font);
    }

    private void initData(List<RepaireSettleVO> data) {
        RepaireSettleVO vo1 = new RepaireSettleVO(123, "小松", 23, "男", "上海", "暂无");
        RepaireSettleVO vo2 = new RepaireSettleVO();
        RepaireSettleVO vo3 = new RepaireSettleVO(1235, "小李", 24, "男", "上海", "暂无");
        RepaireSettleVO vo4 = new RepaireSettleVO();
        RepaireSettleVO vo5 = new RepaireSettleVO(1236, "小青", 25, "男", "上海", "暂无");
        RepaireSettleVO vo6 = new RepaireSettleVO(1237, "大壮", 21, "男", "上海", "暂无");
        RepaireSettleVO vo7 = new RepaireSettleVO(1238, "熊二", 22, "男", "上海", "暂无");
        vo2.setId(1234);
        vo4.setId(12310);
        vo2.setName("小可爱");
        vo4.setName("小精灵");
        data.add(vo1);
        data.add(vo2);
        data.add(vo3);
        data.add(vo4);
        data.add(vo5);
        data.add(vo6);
        data.add(vo7);
    }
}

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import lombok.Data;

/**
 * @author jinsong.Liang
 * @Description
 * @date 2019/4/16 16:54
 */
@Data
public class RepaireSettleVO extends BaseRowModel {
    @ExcelProperty(value = "证件号",index = 0)
    private int id;
    @ExcelProperty(value = "姓名",index = 1)
    private String name;
    @ExcelProperty(value = "年龄",index = 2)
    private int age;
    @ExcelProperty(value = "性别",index = 3)
    private String sex;
    @ExcelProperty(value = "地址",index = 4)
    private String address;
    @ExcelProperty(value = "评论",index = 5)
    private String remark;

    public RepaireSettleVO(){}

    public RepaireSettleVO(int id, String name, int age, String sex, String address, String remark) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.address = address;
        this.remark = remark;
    }
}