【java学习】excel读写

1，com.alibaba.easyexcel

1）Demo

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

public class ExcelListener extends AnalysisEventListener<ExcelData> {

    List<ExcelData> dataList = new ArrayList<>();
    @Override
    public void invoke(ExcelData excelData, AnalysisContext analysisContext) {        
        dataList.add(networkExcelData);
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("读取完毕！");
    }

    public List<ExcelData> getDataList(){
        return  this.dataList;
    }
}

@Data
public class ExcelData {

    @ExcelProperty(value = "序号", index = 0)
    private String id;

    @ExcelProperty(value = "名称", index = 1)
    private String name;

    @ExcelProperty(value = "省份", index = 2)
    private String province;
}

        ExcelListener listener = new  ExcelListener();
        EasyExcel.read(EXCEL_PATH, ExcelData.class, listener).sheet(EXCEL_SHEET_NAME).doRead();
        List<ExcelData> dataList = listener.getDataList();

2）Apache POI如何获取Excel合并单元格的值

	/**
	 * 获取合并单元格的值
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public String getMergedRegionValue(Sheet sheet ,int row , int column){
		int sheetMergeCount = sheet.getNumMergedRegions();
		
		for(int i = 0 ; i < sheetMergeCount ; i++){
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			
			if(row >= firstRow && row <= lastRow){
				
				if(column >= firstColumn && column <= lastColumn){
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					
					return getCellValue(fCell) ;
				}
			}
		}
		
		return null ;
	}
	
	/**
	 * 判断指定的单元格是否是合并单元格
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public boolean isMergedRegion(Sheet sheet , int row , int column){
		int sheetMergeCount = sheet.getNumMergedRegions();
		
		for(int i = 0 ; i < sheetMergeCount ; i++ ){
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();
			
			if(row >= firstRow && row <= lastRow){
				if(column >= firstColumn && column <= lastColumn){
					
					return true ;
				}
			}
		}
		
		return false ;
	}
	
	/**
	 * 获取单元格的值
	 * @param cell
	 * @return
	 */
	public String getCellValue(Cell cell){
		
		if(cell == null) return "";
		
		if(cell.getCellType() == Cell.CELL_TYPE_STRING){
			
			return cell.getStringCellValue();
			
		}else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
			
			return String.valueOf(cell.getBooleanCellValue());
			
		}else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
			
			return cell.getCellFormula() ;
			
		}else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			
			return String.valueOf(cell.getNumericCellValue());
			
		}
		
		return "";
	}

2，org.apache.poi

1，poi读取文件的时候可能导致OOM

1. excel文件其实非常大；
   xlsx是一个若干个XML格式的纯文本文件的压缩文件，Excel就是读取这些压缩文件最后展现一个完全图形的电子表格。可以修改后缀为.zip或.rar解压。
2. XSSFWorkbook、HSSFWorkbook在处理excel过程中会将整个excel都加载到内存中；
3. SXSSFWorkbook是流式处理，将数据写入临时文件，减少内存消耗，适合大型数据集处理。

2，使用

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

package com.trans;

import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ReadExcel {

    private File file;
    //k-colIndex Object-value
    private List<Map<Integer, Object>> valueMaps = new ArrayList<>();

    public ReadExcel(File file) {
        this.file = file;
    }
    public ReadExcel read(){
        try {
            FileInputStream inputStream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet xssfSheet = workbook.getSheetAt(0);
            //不获取表头数据
            for (int rowIndex=Constant.TABLE_NUM; rowIndex <= xssfSheet.getLastRowNum(); rowIndex++){
                getDataFromRow(xssfSheet.getRow(rowIndex));
            }
        }catch (Exception e){
            //文件错误
        }
        return this;
    }
    private void getDataFromRow(XSSFRow row){
        if (isRowEmpty(row)){
            return;
        }
        Map<Integer, Object> valueMap = new HashMap<>();
        for(int i=0; i<row.getLastCellNum(); i++) {
            if (!isCellEmpty(row.getCell(i))) {
                valueMap.put(i, getLabelCellValue(row.getCell(i)));
            }
        }
        valueMaps.add(valueMap);
    }

    private boolean isRowEmpty(XSSFRow row) {
        if (null == row) {
            return true;
        }
        for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
            XSSFCell cell = row.getCell(i);
            if (null != cell && cell.getCellTypeEnum() != CellType.BLANK) {
                return false;
            }
        }
        return true;
    }

    private boolean isCellEmpty(XSSFCell cell) {
        return null == cell || cell.toString().length() == 0;
    }

    public List<Map<Integer, Object>> getValueMaps() {
        return valueMaps;
    }

    private String getLabelCellValue(XSSFCell xssfCell) {
        String cellValue;
        if (null != xssfCell) {
            //判断cell类型
            if (xssfCell.getCellTypeEnum() == CellType.NUMERIC || xssfCell.getCellTypeEnum() == CellType.FORMULA) {
                if (new Double(xssfCell.getNumericCellValue()).intValue() == new Double(xssfCell.getNumericCellValue())) {
                    cellValue = String.valueOf(new Double(xssfCell.getNumericCellValue()).intValue());
                } else {
                    cellValue = xssfCell.getNumericCellValue().toString();
                }
            } else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {
                cellValue = String.valueOf(xssfCell.getBooleanCellValue());
            } else if (xssfCell.getCellTypeEnum() == CellType.STRING) {
                cellValue = xssfCell.getStringCellValue();
            } else {
                cellValue = xssfCell.toString();
            }

        } else {
            cellValue = "";
        }
        return cellValue.trim();
    }
}