文章目錄

• java操作Excel數據
• • 使用場景
  • excel 03 和 07的區別
  • POI
  • easyExcel
  • 解析excel表中的對象
  • POI使用步驟
  • POI 寫數據
  • POI 讀數據
  • 計算公式
  • easyExcel讀寫數據
  • • ·寫數據
    • ·讀數據

java操作Excel數據

在 平時 可以使用IO流對Excle進行操作

但是現在使用更加方便的第三方組件來實現

使用場景

1、將用戶信息導出為Excel表格，導入數據

2、將Excel表中的數據錄入到網站數據庫 （習題上傳）?減輕網站的錄入量

3、開發中經常會設計到Excel的處理，導入Excel到數據庫中

目前最流行的是 Apache POI以及阿里巴巴easyExcel

excel 03 和 07的區別

HSSF 對應excel中的03版本 該版本要求excel中最多只能寫65536行

? 后綴名為?03.xls

XSSF 對應excel中的07版本 該版本對于行數沒有要求

? 后綴名為?07.xlsx

POI

Apache提供的，會比較麻煩，比較原生

開放源碼函式庫，POI提供API給java程序對Office格式檔案讀和寫的功能

但是存在內存問題 => POI將數據會先寫入內存中，一旦寫入的內容過多時會產生OOM，也叫做內存溢出

easyExcel

https://github.com/alibaba/easyexcel

對POI進行了一些優化，可以使開發者更加簡單，讀和寫代碼只需要1行

存在時間的問題 => easyExcel在寫數據時是一行一行往磁盤中寫，所以解決了POI的內存問題，但是帶來了時間問題

解析excel表中的對象

由于java中萬物皆對象，所以需要先觀察一張excel表中有哪些對象~

POI使用步驟

第一步：創建Maven項目

第二步：導入依賴

<dependencies>
        <!-- xls 03-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- xlsx 07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- 日期格式化工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>
        <!--測試-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
</dependencies>

POI 工具類

public class POIUtils {
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";
    private final static String DATE_FORMAT = "yyyy/MM/dd";
    /**
     * 讀入excel文件，解析后返回
     * @param file
     * @throws IOException
     */
    public static List<String[]> readExcel(MultipartFile file) throws IOException {
        //檢查文件
        checkFile(file);
        //獲得Workbook工作薄對象
        Workbook workbook = getWorkBook(file);
        //創建返回對象，把每行中的值作為一個數組，所有行作為一個集合返回
        List<String[]> list = new ArrayList<String[]>();
        if(workbook != null){
            for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
                //獲得當前sheet工作表
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if(sheet == null){
                    continue;
                }
                //獲得當前sheet的開始行
                int firstRowNum  = sheet.getFirstRowNum();
                //獲得當前sheet的結束行
                int lastRowNum = sheet.getLastRowNum();
                //循環除了第一行的所有行
                for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
                    //獲得當前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
                    //獲得當前行的開始列
                    int firstCellNum = row.getFirstCellNum();
                    //獲得當前行的列數
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()];
                    //循環當前行
                    for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                    list.add(cells);
                }
            }
            workbook.close();
        }
        return list;
    }

    //校驗文件是否合法
    public static void checkFile(MultipartFile file) throws IOException{
        //判斷文件是否存在
        if(null == file){
            throw new FileNotFoundException("文件不存在！");
        }
        //獲得文件名
        String fileName = file.getOriginalFilename();
        //判斷文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
            throw new IOException(fileName + "不是excel文件");
        }
    }

    /**
     * 獲得Workbook工作簿對象
     * @param file
     * @return
     */
    public static Workbook getWorkBook(MultipartFile file) {
        //獲得文件名
        String fileName = file.getOriginalFilename();
        //創建Workbook工作薄對象，表示整個excel
        Workbook workbook = null;
        try {
            //獲取excel文件的io流
            InputStream is = file.getInputStream();
            //根據文件后綴名不同(xls和xlsx)獲得不同的Workbook實現類對象
            if(fileName.endsWith(xls)){
                //2003
                workbook = new HSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    /**
     * 獲取單元格的值
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        //如果當前單元格內容為日期類型，需要特殊處理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        if(dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat(DATE_FORMAT).format(cell.getDateCellValue());
            return cellValue;
        }
        //把數字當成String來讀，避免出現1讀成1.0的情況
        if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判斷數據的類型
        switch (cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC: //數字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知類型";
                break;
        }
        return cellValue;
    }
}

easyExcel

根據實體類自動生成表

第一步：導入依賴

該依賴中自帶了很多種依賴，如lombok、spring-boot等，需要我們在引入依賴時將自己已經導入的依賴刪除，不然會報依賴沖突的錯誤

<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>3.1.4</version>
</dependency>
<dependency>
	<groupId>org.projectlombok</groupId>
	<artifactId>lombok</artifactId>
	<version>1.18.8</version>
</dependency>
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>fastjson</artifactId>
	<version>1.2.75</version>
</dependency>

寫數據

第二步：創建excel表對應的實體類

@Data
public class DemoData {
    @ExcelProperty("字符串標題")
    private String string;
    @ExcelProperty("日期標題")
    private Date date;
    @ExcelProperty("數字標題")
    private Double aDouble;
    // 忽略該字段
    @ExcelIgnore
    private String ignore;
}

第四步：編寫測試類

使用鏈式編寫的方式

write(文件路徑，excel表對應的java類)

sheet(設置表名)

doWrite(數據)

 @Test
    public void test1(){
        String fileName ="d:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx";
        EasyExcel.write(fileName,DemoData.class).sheet("表1").doWrite(easyTest1());
    }

·讀數據

1、每執行一條excel表中的數據都會執行一次監聽文件中的invoke方法，所以如果需要修改可以修改invoke方法中的內容

2、DemoDataListener 不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構造方法傳進去

第二步：準備一個對應excel表中字段的類

與寫操作中使用同一個類

@Data
public class DemoData {
    @ExcelProperty("字符串標題")
    private String string;
    @ExcelProperty("日期標題")
    private Date date;
    @ExcelProperty("數字標題")
    private Double aDouble;
    // 忽略該字段
    @ExcelIgnore
    private String ignore;
}

@Data
public class DemoData {
    @ExcelProperty("字符串標題")
    private String string;
    @ExcelProperty("日期標題")
    private Date date;
    @ExcelProperty("數字標題")
    private Double aDouble;
    // 忽略該字段
    @ExcelIgnore
    private String ignore;
}

第三步：創建數據層 Mapper || Dao

public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,盡量別直接調用多次insert,自己寫一個mapper里面新增一個方法batchInsert,所有數據一次性插入
    }
}

第四步：創建監聽器

package excel.readEasy;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import excel.easy.DemoData;
import lombok.extern.slf4j.Slf4j;

import java.util.List;
import java.util.Map;

// 有個很重要的點 DemoDataListener 不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構造方法傳進去
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {
    //每隔5條存儲數據庫，實際使用中可以100條，然后清理list ，方便內存回收
    private static final int BATCH_COUNT = 100;
    //緩存的數據
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);


    //假設這個是一個DAO，當然有業務邏輯這個也可以是一個service。當然如果不用存儲這個對象沒用
    private DemoDAO demoDAO;

    // 這里是demo，所以隨便new一個。實際使用如果到了spring,請使用下面的有參構造函數
    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }

    //如果使用了spring,請使用這個構造方法。每次創建Listener的時候需要把spring管理的類傳進來
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }


    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {

    }

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        System.out.println("111");
    }

    //這個每一條數據解析都會來調用
    @Override
    public void invoke(DemoData data, AnalysisContext analysisContext) {
        System.out.println("2222");
        System.out.println(JSON.toJSONString(data));
        //  log.info("解析到一條數據:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 達到BATCH_COUNT了，需要去存儲一次數據庫，防止數據幾萬條數據在內存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存儲完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {

    }

    //所有數據解析完成了 都會來調用
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("所有數據解析完成！");
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return false;
    }

    /**
     * 加上存儲數據庫
     */
    private void saveData() {
        log.info("{}條數據，開始存儲數據庫！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存儲數據庫成功！");
    }
}

第五步：測試

@Test
    public void test3() {
        String fileName = "E:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx";
        EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
            for (DemoData demoData : dataList) {
                System.out.println(JSON.toJSONString(demoData));
            }
        })).sheet().doRead();
    }