Excel读取与写入

一、简介

 适用于操作Excel2007及以上的Excel

一、依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>20.0</version>
        </dependency>

二、工具类

package com.example.javabase.excel;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
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 org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;

import java.io.*;
import java.util.*;

public class ExcelTools {
    private static Logger logger = LoggerFactory.getLogger(ExcelTools.class);

    /**
     * 获取文件输入流
     * @param path
     * @return
     */
    public static InputStream getInputStream(String path){
        try{
            ClassPathResource classPathResource = new ClassPathResource(path);
            InputStream inputStream = classPathResource.getInputStream();
            logger.info("文件: {} 加载完成",path);
            return inputStream;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取文件输出流
     * @param path
     * @return
     */
    public static OutputStream getOutputStream(String path){
        logger.info("创建Excel文件  {}",path);
        String relativePath = System.getProperty("user.dir")+"\\src\\main\\resources\\"+path;
        try{
            File file = new File(relativePath);

            if(file.exists()){
                file.delete();
                logger.info("删除旧文件");
            }
            //创建输出流
            OutputStream outputStream = new FileOutputStream(relativePath);
            return outputStream;
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 获取Excel工作簿
     * @param inputStream
     * @return
     */
    public static XSSFWorkbook getXSSFWorkbook(InputStream inputStream){
        XSSFWorkbook xssfWorkbook = null;
        try {
            xssfWorkbook = new XSSFWorkbook(inputStream);
            logger.info("创建工作簿对象");
        }catch (IOException e){
            logger.error(e.getMessage());
            e.printStackTrace();
        }
        return xssfWorkbook;
    }

    /**
     * 获取Sheet名称列表
     * @param xssfWorkbook
     * @return
     */
    public static List<String> getSheetNames(XSSFWorkbook xssfWorkbook){
        if(xssfWorkbook == null){
            return Lists.newArrayList();
        }
        List<String> list = Lists.newLinkedList();
        for(int i = 0;i < xssfWorkbook.getNumberOfSheets(); i++){
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(i);
            String sheetName = xssfSheet.getSheetName();
            list.add(sheetName);
        }
        return list;
    }

    /**
     *  读取指定工作表中的内容
     * @param xssfWorkbook
     * @param sheetName
     * @return
     */
    public static List<Map<String,String>> readSheet(XSSFWorkbook xssfWorkbook,String sheetName){
        XSSFSheet xssfSheet;
        if("".equals(sheetName)){
            xssfSheet = xssfWorkbook.getSheetAt(0);
        }else{
            xssfSheet = xssfWorkbook.getSheet(sheetName);
        }
        List<Map<String,String>> list = new ArrayList<>();
        XSSFRow titileRow = xssfSheet.getRow(0);
        for(int rowIndex = 1;rowIndex <xssfSheet.getPhysicalNumberOfRows();rowIndex++){
            XSSFRow xssfRow = xssfSheet.getRow(rowIndex);
            if(xssfRow == null){
                continue;
            }
            Map<String,String> map = Maps.newLinkedHashMap();
            for(int cellIndex = 0; cellIndex < xssfRow.getPhysicalNumberOfCells(); cellIndex++){
                XSSFCell titleCell = titileRow.getCell(cellIndex);
                XSSFCell xssfCell = xssfRow.getCell(cellIndex);
                try{
                    if(xssfCell.getCellType() != 3 && !"".equals(getString(xssfCell))){
                        map.put(getString(titleCell),getString(xssfCell));
                    }
                }catch (NullPointerException e){
                    logger.error("第 {} 行, {}为空",rowIndex,titleCell);
                }
            }
            list.add(map);
        }
        return list;
    }

    /**
     * 写入Excel
     *
     * @param list
     * @param outputStream
     */
    public static void writeExcel(List<List> list,OutputStream outputStream){
        //创建工作簿
        XSSFWorkbook xssfWorkbook = null;
        xssfWorkbook = new XSSFWorkbook();

        //创建工作表
        XSSFSheet xssfSheet;
        xssfSheet = xssfWorkbook.createSheet();

        //创建行
        XSSFRow xssfRow;
        //创建列,即单个cell
        XSSFCell xssfCell;

        //创建标题行
        XSSFRow titleRow = xssfSheet.createRow(0);
        titleRow.createCell(0).setCellValue("第一列");
        titleRow.createCell(1).setCellValue("第二列");
        titleRow.createCell(2).setCellValue("第三列");
        titleRow.createCell(3).setCellValue("第四列");
        //把List里面的数据写到excel中
        for(List subList: list){
            int lastRowNum = xssfSheet.getLastRowNum();
            //从第二行开始写入(非标题行)
            xssfRow = xssfSheet.createRow(lastRowNum+1);
            //创建每个单元格Cell,即列的数据
            for(int j =0 ;j<subList.size();j++){
                //创建单元格
                xssfCell = xssfRow.createCell(j);
                //设置单元格内容
                xssfCell.setCellValue((String)subList.get(j));
            }
        }

        //用输入流写到Excel
        try{
            xssfWorkbook.write(outputStream);
        }catch (IOException e){
            e.printStackTrace();
        }
    }

    /**
     * 将单元格内容转换为字符串
     * 0-数值
     * 1-字符串
     * 2-公式
     * 3-空值
     * 4-布尔
     * 5-错误
     * @param xssfCell
     * @return
     */
    private static String getString(XSSFCell xssfCell){
        if(xssfCell == null){
            return "";
        }
        int type = xssfCell.getCellType();
        if(type == 0){
            return String.valueOf(xssfCell.getNumericCellValue());
        }else if(type == 4){
            return String.valueOf(xssfCell.getBooleanCellValue());
        }else {
            return xssfCell.getStringCellValue();
        }
    }

    /**
     * 拆分map键和值
     * @param map
     * @return
     */
    public static List<List> convertMapToList(Map map){
        List<List> list = Lists.newArrayList();
        List<String> keyList = Lists.newLinkedList();
        List<String> valueList = Lists.newLinkedList();
        Set<Map.Entry<String,String>> set = map.entrySet();
        Iterator<Map.Entry<String,String>> iterator = set.iterator();
        while(iterator.hasNext()){
            keyList.add(iterator.next().getKey());
        }
        list.add(keyList);
        Collection<String> value = map.values();
        Iterator<String> iterator1 = value.iterator();
        while(iterator1.hasNext()){
            valueList.add(iterator1.next());
        }
        list.add(valueList);
        return list;
    }


}