excel表格导入导出

1、easy-poi 将数据从数据库导出到excel表格

easy-poi 官方文档：http://easypoi.mydoc.io/#category_48830

在你将要导出的实体变量（即下面例子的BaseUser类中的变量）上加上easy-poi 的注解，

注解详见官方文档。如：@Excel(name = "姓名")，则“姓名”为导出的excle表中的列名

    @Override
    public String batchExport() throws Exception {
        String outPath = "D:\\员工导出.xls";// 导出到该路径下
        List<BaseUser> allUser = dao.findAll();// 从数据库中取出数据到List中

        Workbook workbook = ExcelExportUtil.exportExcel(
                new ExportParams("全体人员信息", "人员表"),
                BaseUser.class, allUser
        );
        if (workbook != null) {
            FileOutputStream outputStream = null;
            try {
                outputStream = new FileOutputStream(outPath);
                workbook.write(outputStream);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (outputStream != null) {
                        outputStream.close();
                    }
                    workbook.close();
                } catch (Exception e) {

                }
            }
            return outPath;
        } else {
            return "导出失败";
        }
    }

注：文件名后缀应为< .xls > 用xlsx 导出的文件打不开，暂时不知道是什么原因

2、HSSFWorkbook和XSSFWorkbook方法导出到excel表格

参考链接：https://blog.csdn.net/qq_28411869/article/details/82185016  Java 追加内容到Excel表格

• 在 maven项目的pom.xml 文件下导入依赖包 

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

---

• 例子，为外部调用

注：创建表格时会清空表格中原来的内容

package com.joysuch.locatecollector.service;

import org.apache.poi.hssf.usermodel.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class CreateExcel {
    private static String fileName = "E:/info.xls";
    private static int num = 1;
    /**
     * 创建excel表格
     */
    public static void createExcel() {
        if (num == 1) {
            FileOutputStream fos = null;
            try {
                fos = new FileOutputStream(fileName); // 若文件不存在则自动创建
                HSSFWorkbook workbook = new HSSFWorkbook();
                HSSFSheet sheet = workbook.createSheet("log");
                HSSFRow row0 = sheet.createRow(0);  // 创建行，第一行
                HSSFRow row1 = sheet.createRow(1);  // 第二行
                row0.createCell(0).setCellValue("定位分析");
//                row0.createCell(1).setCellValue("第二列");
                row1.createCell(0).setCellValue("定位时间");
                row1.createCell(1).setCellValue("定位坐标X");
                row1.createCell(2).setCellValue("定位坐标Y");
                row1.createCell(3).setCellValue("计算坐标X");
                row1.createCell(4).setCellValue("计算坐标Y");
                row1.createCell(5).setCellValue("距离");

                workbook.write(fos);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (fos != null) {
                        fos.close();
                    }
                } catch (IOException e) {
                    //ignore
                }
            }
        }
    }



    /** 
    * 在创建的excel 表格中追加数据
    */
    public static void appendToExcel(String time, int locatedX, int locatedY, double actualX, double actualY, double distance) {
        FileInputStream fis = null;
        FileOutputStream fos = null;
        try {
            fis = new FileInputStream(fileName);
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            HSSFSheet sheet = workbook.getSheet("log");
            if (sheet != null) {
                num ++;
                HSSFRow rowNum = sheet.createRow(num);    // 创建单元格
                rowNum.createCell(0).setCellValue(time);
                rowNum.createCell(1).setCellValue(locatedX);
                rowNum.createCell(2).setCellValue(locatedY);
                rowNum.createCell(3).setCellValue(actualX);
                rowNum.createCell(4).setCellValue(actualY);
                rowNum.createCell(5).setCellValue(distance);
            }
            fos = new FileOutputStream(fileName);
            workbook.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fis != null) {
                    fis.close();
                }
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                //ignore
            }
        }
    }

}

注：文件名后缀为  .xls  不能写为  .xlsx