java操作excel

java
java基础
发布日期:   2020-04-07
更新日期:   2020-06-02
文章字数:   2,515
阅读时长:   12 分
阅读次数:  

java操作excel

使用apache提供的工具POI操作Excel

1.Maven的依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>

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

也可以在网上下载其jar包(个人使用3.9)

下载的有三个包：poi、poi-ooxml、poi-ooxml-schemas，三个包下载的版本必须一致

POI基本功能介绍:

HSSF:操作excel，03版本

XSSF:操作excel07版本

HWPF:操作word格式的文档

HSLF:操作ppt文档

HDGF:操作visio文档

poi的jar包

poi-ooxml的jar包

poi-ooxml-schemas的jar包

简单使用1,创建一个工作簿：

public static void main(String[] args) {
        //新建一个工作簿
        Workbook wb = new HSSFWorkbook();
        try {
            //创建一个io流，通过io流将数据写入磁盘
            FileOutputStream fileout = new FileOutputStream("E:\\file\\java生成的工作簿.xls");
            wb.write(fileout);
            fileout.close();//关流

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

简单使用2,创建一个sheet：

public static void main(String[] args) {
        Workbook wb = new HSSFWorkbook();//定义新的工作簿
        wb.createSheet("第一个sheet页面");//定义第一个sheet
        wb.createSheet("第二个sheet页面");//定义第二个sheet
        FileOutputStream fileout = null;
        try {
            fileout = new FileOutputStream("E:\\file\\java生成的sheet.xls");
            wb.write(fileout);
            fileout.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

简单使用3,创建cell,根据行创建多个列并写入数据：

Workbook wb = new HSSFWorkbook();//定义新的工作簿
        Sheet sheet = wb.createSheet("第一个sheet");
        //创建行
        Row row = sheet.createRow(0);
        //创建一个列，根据行创建列
        Cell cell = row.createCell(0);
        cell.setCellValue(1);
        row.createCell(1).setCellValue(1.2);//创建第二列
        row.createCell(2).setCellValue("字符串类型");//创建第三列
        row.createCell(3).setCellValue(false);//创建第四列

        //使用流将创建的数据写到磁盘上
        FileOutputStream fileout = null;
        try {
            fileout = new FileOutputStream("E:\\file\\java生成的cell.xls");
            wb.write(fileout);
            fileout.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

简单使用4，某一列数据使用日期类型，对其进行格式化输出：

public static void main(String[] args) throws Exception {
        //定义一个新的工作簿
        Workbook wb = new HSSFWorkbook();
        //创建第一个sheet页
        Sheet sheet = wb.createSheet();
        //创建一个行
        Row row = sheet.createRow(0);
        //创建一个单元格
        Cell cell = row.createCell(0);
        //给第一列元素赋值
        cell.setCellValue(new Date());
        //给单元格设定值
        //row.createCell(1).setCellValue(new Date());

        //创建helper元素
        CreationHelper chelp = wb.getCreationHelper();
        //设置单元格样式
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(chelp.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss"));
        cell = row.createCell(1);
        cell.setCellValue(new Date());
            //将单元格设置为自己设置的样式
        cell.setCellStyle(cellStyle);

        //创建第三列并设置样式，使用Calendar类进行设置样式
        cell = row.createCell(2);
        cell.setCellValue(Calendar.getInstance());
        cell.setCellStyle(cellStyle);


        FileOutputStream fileout = new FileOutputStream("E:\\file\\java生成的style.xls");
        wb.write(fileout);
        fileout.close();
    }

简单使用5，读取excel文件内容：

public static void main(String[] args) throws Exception {
        //创建文件输入流
        FileInputStream filein = new FileInputStream("E:\\file\\java读取的文件.xls");
        //使用POIFSFileSystem类，传入数据流进行读取
        POIFSFileSystem fs = new POIFSFileSystem(filein);
        //使用poifs读取一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet hsheet = wb.getSheetAt(0);
        if(hsheet==null) {return ;}
        //遍历工作簿中的行
        for(int rowNum=0;rowNum<=hsheet.getLastRowNum();rowNum++) {
            HSSFRow hsrow = hsheet.getRow(rowNum);
            if(hsrow == null) {
                return;
            }
            //遍历一行中的列
            for(int cellRow=0;cellRow<hsrow.getLastCellNum();cellRow++) {
                HSSFCell hscell = hsrow.getCell(cellRow);
                if(hscell == null) {
                    return;
                }
                String ss = getValue(hscell);
                System.out.print(ss+" ");
            }
            System.out.println(" ");
        }
    }

    //将列中的不同的元素转换成String类型进行输出
    public static String getValue(HSSFCell hscell ) {
        //将布尔类型转换成字符串
        if(hscell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hscell.getBooleanCellValue());
            //将数字类型转换成字符串
        }else if(hscell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hscell.getNumericCellValue());
        }else {
            return String.valueOf(hscell.getStringCellValue());
        }
    }

使用工具类进行数据抽取(功能与上边读取一致)

public static void main(String[] args) throws Exception {
        FileInputStream filein = new FileInputStream("E:\\file\\java读取的文件.xls");
        //使用POIFSFileSystem类，传入数据流进行读取
        POIFSFileSystem fs = new POIFSFileSystem(filein);
        //使用poifs读取一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook(fs);

        ExcelExtractor excel = new ExcelExtractor(wb);
        excel.setIncludeSheetNames(false);
        System.out.println(excel.getText());

    }
大批量的导入

大文件写HSSF：(03版本)

缺点：最多只能处理65536，多出会出现异常

优点：过程写入缓存，不操作磁盘，最后一次性写入，速度快

 //批量操作
    @Test
    public void testWrite003() throws Exception {
        //时间
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个表
        HSSFSheet sheet = workbook.createSheet();

        //写入数据
        for(int num=0;num<65536;num++){
            HSSFRow row = sheet.createRow(num);
            for(int cellnum = 0;cellnum<10;cellnum++){
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(cellnum);
            }
        }

        //写入数据后，使用IO流将数据写入磁盘
        System.out.println("写入完成");
        FileOutputStream fileOutputStream = new FileOutputStream(path+"three.xls");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        long ll = end - begin;
        System.out.println(ll);
    }

大文件写XSSF：

缺点：写数据时，全部放入内存，会产生OOM，如100万条数据

优点：可以写较大数据量，如：20万

 @Test
    public void testWrite007() throws Exception {
        //时间
        long begin = System.currentTimeMillis();
        //创建一个工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建一个表
        XSSFSheet sheet = workbook.createSheet();

        //写入数据
        for(int num=0;num<65536;num++){
            XSSFRow row = sheet.createRow(num);
            for(int cellnum = 0;cellnum<10;cellnum++){
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(cellnum);
            }
        }
        //写入数据后，使用IO流将数据写入磁盘
        System.out.println("写入完成");
        FileOutputStream fileOutputStream = new FileOutputStream(path+"four.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        long ll = end - begin;
        System.out.println(ll);
    }

针对以上的大量数据导入的XSSF写入时比较慢，因此可以使用SXSSF

SXSSF优点：可以写入非常大量的数据，多余100万，写数据速度快，占用内存少，会产生一个临时文件。先将部分数据放入临时文件，超过一定数据量时，将临时文件中的数据写入磁盘。

 @Test
    public void testWrite007sxssf() throws Exception {
        //时间
        long begin = System.currentTimeMillis();
       //多态，父类引用指向子类对象
        Workbook work = new SXSSFWorkbook();

        Sheet sheet = work.createSheet("大批量的导入07版sheet");

        //写入数据
        for(int num=0;num<65536;num++){
            Row row = sheet.createRow(num);
            for(int cellnum = 0;cellnum<10;cellnum++){
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(cellnum);
            }
        }
        FileOutputStream out = new FileOutputStream(path+"five.xlsx");
        work.write(out);
        out.close();
        //清理临时文件
        ((SXSSFWorkbook)work).dispose();
        long end = System.currentTimeMillis();
        long ll = end - begin;
        System.out.println(ll);

    }
  @Test
    public void testReadCacle03() throws Exception {
        //获取文件输入流
        FileInputStream in = new FileInputStream(path+"first.xls");
        //创建一个工作簿
        Workbook workbook = new HSSFWorkbook(in);
        //创建一个sheet
        Sheet sheetAt = workbook.getSheetAt(0);
        //获取行
        Row row = sheetAt.getRow(7);
        Cell cell = row.getCell(0);
        //拿到计算公式
        FormulaEvaluator formulaEvaluator  = new HSSFFormulaEvaluator((HSSFWorkbook)workbook);

        int cellType = cell.getCellType();
        switch (cellType){
            case Cell.CELL_TYPE_FORMULA:
                String formula = cell.getCellFormula();
                System.out.println(formula);
                //使用公式进行计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String s = evaluate.formatAsString();
                System.out.println(s);
                break;
        }

        in.close();
    }

阿里的easyExcel

poi会消耗大的内存，使用easyExcel工具操作excel,首先导入依赖文件

    <!--导入依赖-->
    <dependencies>
        <!--导入日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>
    <!--阿里巴巴的easyexcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>

        <!--导入单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <scope>provided</scope>
        </dependency>

阿里excel操作文档：阿里excel文档

步骤1：创建一个和表相同列与属性的model实体

/**
 * @author xing
 * @create 2020/6/2-mavenStudy
 */
@Data
public class ExcelModel {
    @ExcelProperty("字符串标题")
    private String str;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;

    @ExcelIgnore
    private String ignore;
}

步骤2：读取文件

/**
 * @author xing
 * @create 2020/6/2-mavenStudy
 */
public class TestEasy {
    //存放文件的位置
   String path = "E:\\file\\";
    //创建一个存放model的集合
   public List<ExcelModel> data(){
       List list  = new ArrayList<ExcelModel>();
       for(int i=0;i<10;i++){
           ExcelModel excelModel = new ExcelModel();
           excelModel.setStr("星仔"+i);
           excelModel.setDate(new Date());
           excelModel.setDoubleData(0.56);
           list.add(excelModel);
       }
       return list;
   }

   //根据list写入excel
    @Test
    public void simpleExcel(){
       //文件名(文件位置+文件名称)
        String fileName = path+"easyTets.xlsx";
       //指定使用哪个类进行写操作，名字模板，文件流用完会自动关闭
        //write（参数1是文件名称，参数2格式类）
        //sheet（参数是sheet的名字）
        EasyExcel.write(fileName,ExcelModel.class).sheet("阿里sheet").doWrite(data());
    }
}

写excel步骤：(今本都是官方文档的代码，只需要修改自己的路径地址即可，根据工作需要进行学习)

还是之前的文档对象

创建一个DAO类

/**
 * @author xing
 * @create 2020/6/2-mavenStudy
 */
public class DemoDAO {
    public void save(List<ExcelModel> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

创建监听器：

/**
 * @author xing
 * @create 2020/6/2-mavenStudy
 */
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<ExcelModel> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<ExcelModel> list = new ArrayList<ExcelModel>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;
    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(ExcelModel data, AnalysisContext context) {
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        //再次将自己的数据输出到控制台
        System.out.println(JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
        System.out.println("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

注：前半部分是自己从博客中进行学习的，后半部分是在b站听狂神的课做的笔记和记录。

文章作者: it星
文章链接: https://github.com/fengyanxing/2020/04/07/java-cao-zuo-excel/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 it星 !
java
赏
 上一篇
JVM虚拟机学习 JVM虚拟机学习
it星
关于该学习笔记参考网上的jvm课程以及一些文章的讲解 JVM结构： 局部变量表存放了编译期可知的基本数据类型、对象引用和returnAddress类型，其所需的内存空间在编译期间完成分配，方法运行期间不会改变局部变量表的大小。 除了程序计
2020-04-10 java深入学习
jvm
下一篇 
千人千面推荐系统 千人千面推荐系统
需求分析(尚未完善) 业务需求BRD(Bussiness Requirement Document) 用户故事(User Story) 产品PRD(Product Requirement Document) 业务需求BRD 业务目的：仿
2020-04-01 java项目
java
  目录