投稿
  1. 首页
  2. java

Java开发中的POI与easyExcel对Excel文件的读与写

code • 2022-4-25 • java • 阅读 64

Java开发中的POI与easyExcel对Excel文件的读与写,第1张

Java POI与easyExcel POI

Apache POI是用Java编写的免费开源跨平台的Java API,Apache POI提供API给Java对Microsoft Office格式档案都和写的功能.POI为Poor Obfuscation Implementation的首字母缩写,意为简洁版的模糊实现

easyExcel

easyExcel是阿里巴巴开源的一个excel处理框架,以使用简单、节省内存著称.

excel中的对象:
  • 工作簿
  • 工作表
  • 行
  • 列
导入Maven poi依赖: 
 
        org.apache.poi
        poi
        3.9
    
    
        org.apache.poi
        poi-ooxml
        3.9
导入Maven 时间插件依赖: 
    
        joda-time
        joda-time
        2.10.1
excel分为03和07两种版本: 基本写入

03版本写入代码:

    public void testWrite03() throws Exception
    {
         String PATH="路径";
        //创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //创建一个工作表
        Sheet sheet=workbook.createSheet("sheet名");
        //创建一行
        Row row1=sheet.createRow(0);
        //创建单元格
        Cell cell1= row1.createCell(0);
        cell1.setCellValue("值");
        Cell cell2=row1.createCell(1);
        cell2.setCellValue(666);

        //第二行
        Row row2=sheet.createRow(1);
        Cell cell21=row2.createCell(0);
        cell21.setCellValue("统计时间");
        Cell cell22=row2.createCell(1);
        cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        //生成一张表
        FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        System.out.println("finish");
    }

07版本写入代码:

        public void testWrite07() throws Exception
        {
             String PATH="路径";
            //创建一个工作簿
            Workbook workbook = new XSSFWorkbook();
            //创建一个工作表
            Sheet sheet=workbook.createSheet("统计表");
            //创建一行
            Row row1=sheet.createRow(0);
            //创建单元格
            Cell cell1= row1.createCell(0);
            cell1.setCellValue("今日新增");
            Cell cell2=row1.createCell(1);
            cell2.setCellValue(666);

            //第二行
            Row row2=sheet.createRow(1);
            Cell cell21=row2.createCell(0);
            cell21.setCellValue("统计时间");
            Cell cell22=row2.createCell(1);
            cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

            //生成一张表
            FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xlsx");
            workbook.write(fileOutputStream);
            fileOutputStream.close();
            System.out.println("finish");
    }

二者的区别只有调用对象和生成文件拓展名有些区别,03对象为HSSFWorkbook(),拓展名为.xls,07对象为XSSFWorkbook,拓展名为.xlsx
03最大只能到65536行

数据量大写入

03大数据量写入:

    public void testWrite03BigData() throws Exception
    {
         String PATH="路径";
        //时间
        long begin=System.currentTimeMillis();
        Workbook workbook=new HSSFWorkbook();
        Sheet sheet=workbook.createSheet();
        for(int rowNum=0;rowNum<65536;rowNum++)
        {
            Row row=sheet.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++)
            {
                Cell cell=row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

07大数据量写入:

    public void testWrite07BigData() throws Exception
    {
         String PATH="路径";
        //时间
        long begin=System.currentTimeMillis();
        Workbook workbook=new XSSFWorkbook();
        Sheet sheet=workbook.createSheet();
        for(int rowNum=0;rowNum<65536;rowNum++)
        {
            Row row=sheet.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++)
            {
                Cell cell=row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }

07版写入速度较慢,所以引入S方法(使用缓存)(SXSSF方法):

    public void testWrite07BigDataS() throws Exception
    {
         String PATH="路径";
        //时间
        long begin=System.currentTimeMillis();
        Workbook workbook=new SXSSFWorkbook();
        Sheet sheet=workbook.createSheet();
        for(int rowNum=0;rowNum<65536;rowNum++)
        {
            Row row=sheet.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++)
            {
                Cell cell=row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        System.out.println("over");
        FileOutputStream fileOutputStream=new FileOutputStream(PATH+"文件名.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        long end=System.currentTimeMillis();
        System.out.println((double) (end-begin)/1000);
    }
读取

03:

public class ExcelReadTest {
    String PATH="路径";
    public void testRead03() throws Exception
    {
        FileInputStream inputStream=new FileInputStream(PATH+"testWrite03BigData.xls");
        //创建一个工作簿
        Workbook workbook=new HSSFWorkbook(inputStream);
        //得到表
        Sheet sheet=workbook.getSheetAt(0);
        Row row=sheet.getRow(0);
        Cell cell=row.getCell(0);
        //读取值需要类型判断
        System.out.println(cell.getNumericCellValue());
        inputStream.close();
    }

07:

    public  void testRead07() throws Exception
    {
         String PATH="路径";
        FileInputStream inputStream=new FileInputStream(PATH+"文件名.xlsx");
        //创建一个工作簿
        Workbook workbook=new XSSFWorkbook(inputStream);
        //得到表
        Sheet sheet=workbook.getSheetAt(0);
        Row row=sheet.getRow(0);
        Cell cell=row.getCell(0);
        //读取值需要类型判断
        System.out.println(cell.getNumericCellValue());
        inputStream.close();
    }
判断表格内容类型:

03:

public void testCellType() throws Exception
    {
        String PATH="路径";
        FileInputStream fileInputStream=new FileInputStream(PATH+"文件名.xls");
        Workbook workbook=new HSSFWorkbook(fileInputStream);
        Sheet sheet=workbook.getSheetAt(0);
        Row rowTitle=sheet.getRow(0);
        if(rowTitle!=null)
        {
            int cellCount=rowTitle.getPhysicalNumberOfCells();
            for(int cellNum=0;cellNum
easyExcel 导入依赖: 
    
        com.alibaba
        easyexcel
        3.0.5
    
    
        org.apache.poi
        poi
        4.1.2
    
    
        org.projectlombok
        lombok
        1.18.16
    
    
        org.slf4j
        slf4j-simple
        1.7.25
pojo: 
@Data
public class Dd {
    @ExcelProperty("发货日期")
    private String fhrq;
    @ExcelProperty("合同号")
    private String hth;

}
写入类: 
public class Easy {
    private List
 data()
    {
        List
 list=new ArrayList
();
        Dd data=new Dd();
        data.setFhrq("2022/3/1");
        data.setHth("CYZL-KJ-A-2021013-D003");
        list.add(data);
        return list;
    }
    public void simpleWrite()
    {
        String PATH="D:\\SourceCodes\\Temp\\poitest\\result\\";
        String fileName=PATH+"EasyTest.xlsx";
        EasyExcel.write(fileName,Dd.class).sheet("模板").doWrite(data());
    }
}

EasyExcel利用实体类直接写入.

欢迎分享，转载请注明来源：内存溢出

原文地址: https://www.outofmemory.cn/langs/719851.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
code code 管理员组
0 0
上一篇 2022-04-25
下一篇 2022-04-25

发表评论

登录后才能评论

评论列表（0条）

保存