excel工具类

excel工具类

基于poi实现的excel工具类，功能包括：

功能一：从excel读取数据，数据输入 readExcel(String filePath,String columns[])

功能二：将数据写入excel,数据输出 writeExcel(String title, String[] rowName, List<Object[]> dataList, OutputStream out)

package com.pzc.opendataplatform.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * @Author: pzc
 * @Date: 2020-12
 * @Description: Excel工具类
 */
public class ExcelUtils {
   

    // 导出时excel的扩展名
    public static final String EXTENSION_NAME = ".xlsx";
    // 03版excel扩展名
    private static final String XLS = ".xls";
    // 07版excel扩展名
    private static final String XLSX = ".xlsx";

    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    public static List<Map<String, String>> readExcel(String filePath,String columns[]) throws FileNotFoundException {
   
        Workbook wb =null;
        Sheet sheet = null;
        Row row = null;
        List<Map<String,String>> list = null;
        String cellData = null;
        wb = readExcel(filePath);
        if(wb != null){
   
            //用来存放表中数据
            list = new ArrayList<Map<String,String>>();
            //获取第一个sheet
            sheet = wb.getSheetAt(0);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            for (int i = 1; i<rownum; i++) {
   
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(row !=null){
   
                    for (int j=0;j<colnum;j++){
   
                        cellData = (String) getCellFormatValue(row.getCell(j));
                        map.put(columns[j], cellData);
                    }
                }else{
   
                    break;
                }
                list.add(map);
            }
        }
        return list;
    }
    //读取excel
    public static Workbook readExcel(String filePath) throws FileNotFoundException {
   
        Workbook wb = null;
        if(filePath==null){
   
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
   
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
   
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
   
                return wb = null;
            }else{
   
                return wb = null;
            }

        } catch (FileNotFoundException e) {
   
            e.printStackTrace();
        } catch (IOException e) {
   
            e.printStackTrace();
        }
        return wb;
    }
    public static Object getCellFormatValue(Cell cell){
   
        Object cellValue = null;
        if(cell!=null){
   
            //判断cell类型
            switch(cell.getCellType()){
   
                case  NUMERIC:{
   
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case FORMULA:{
   
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
   
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    }else{
   
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case STRING:{
   
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
   
            cellValue = "";
        }
        return cellValue;
    }


    /**
     * 导入Excel到数据库
     *
     * @param filePath 导入的excel文件所在的绝对路径
     * @param startRow 开始解析的行数
     * @param startCol 开始解析的列数
     * @param sheetNum 开始解析的sheet序号，如果不指定，默认传值为-1，则会解析所有sheet
     * @return  {Row2-Col1=编号, Row2-Col2=名称, Row2-Col3=地址}
     */
    public static List<List<Map<String, Object>>> readExcel(String filePath, int startRow, int startCol, int sheetNum) {
   
        logger.info