Java代码笔记——上传excel文件

excel文件自带很多格式，所以处理excel上传的文件，比csv要复杂一些。本文提供了xls和xlsx格式的excel文件上传。数据结果最终存储在List<List<String>>中。

import com.google.common.collect.Lists;
import com.me.handler.MsgRuntimeException;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
    
public class ExcelUploadUtil {
    private final static String EXCEL2003L = ".xls";    //2003- 版本的excel

    private final static String EXCEL2007U = ".xlsx";   //2007+ 版本的excel

    private static final DateFormat dateFormat = new SimpleDateFormat("yyyy年MM月dd日");

    private static Workbook getWorkbook(MultipartFile multipartFile) throws Exception{
        String filename = multipartFile.getOriginalFilename();
        String fileType = filename.substring(filename.lastIndexOf("."));

        Workbook workbook;
        if(EXCEL2003L.equals(fileType)){
            workbook = new HSSFWorkbook(multipartFile.getInputStream());
        }else if(EXCEL2007U.equals(fileType)){
            workbook = new XSSFWorkbook(multipartFile.getInputStream());
        }else{
            throw new MsgRuntimeException("文件格式错误");
        }

        return workbook;
    }

    /**
     * 上传xls、xlsx等excel文件，把单元格内的数据按照字符串存储，每行存为一个List<String>，整个sheet存为List<List<String>>
     * @param multipartFile
     * @return
     * @throws Exception
     */
    public static List<List<String>> getDataFromExcelFile(MultipartFile multipartFile) throws Exception{
        Workbook workbook = getWorkbook(multipartFile);
        Sheet sheet = workbook.getSheetAt(0); //只处理sheet0
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();

        List<List<String>> data = Lists.newArrayListWithCapacity(lastRowNum - firstRowNum);
        for(int i = firstRowNum + 1; i <= lastRowNum; i++){
            Row row = sheet.getRow(i);
            if(row == null){
                continue;
            }
            int rowLength = row.getLastCellNum();
            List<String> rowData = Lists.newArrayListWithCapacity(rowLength);
            for(int j = 0; j < rowLength; j++){
                Cell cell = row.getCell(j);
                if(cell != null) {
                    switch (cell.getCellType()) {
                        case XSSFCell.CELL_TYPE_STRING: //单元格内是字符串，比如字母、汉字等，英文单引号引导的一串数字也是字符串
                            rowData.add(cell.getStringCellValue());
                            break;
                        case XSSFCell.CELL_TYPE_NUMERIC://无单引号引导的整数、小数，excel自带日期格式的日期也被当作数字处理
                            //单元格格式是excel内置日期格式的日期单元格，可以转换为自定义日期格式的字符串，自定义日期格式单元格不会被这样处理
                            if(DateUtil.isCellDateFormatted(cell)){
                                Date date = cell.getDateCellValue();
                                String dateStr = dateFormat.format(date);
                                rowData.add(dateStr);
                            }else{
                                Double cellNumber = cell.getNumericCellValue();
                                if(cellNumber.intValue() == cellNumber){ //解决整数被当作小数记录的问题，比如10被记为10.0
                                    rowData.add(String.valueOf(cellNumber.intValue()));
                                }else{
                                    rowData.add(String.valueOf(cellNumber));
                                }
                            }
                            break;
                        default:
                            rowData.add("");
                    }
                }else{ //空单元格
                    rowData.add("");
                }
            }

            int lastNotEmptyIndex = -1;
            for(int k = rowData.size() - 1; k >= 0; k--){
                if(StringUtils.isNotEmpty(rowData.get(k))){
                    lastNotEmptyIndex = k;
                    break;
                }
            }
            if(lastNotEmptyIndex > 0){ //去掉全空行和非全空行的末尾的空字符串元素
                List<String> list = rowData.subList(0, lastNotEmptyIndex + 1);
                data.add(list);
            }
        }
        return data;
    }

}