（亲测可用）java实现excel导入 导出

参照博客 :https://blog.csdn.net/HaHa_Sir/article/details/105231975
和https://www.cnblogs.com/weiyi1314/p/11686018.html

导入Excel

可以直接使用WorkbookFactory创建对象（但是超多6万多行会报错，没测试）

Workbook workbook = WorkbookFactory.create(inputStream);

导入的类

package com.orient.tdm201.vehicleCheck.business;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
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.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author xks
 * @date 2020/5/22 9:01
 * @description :
 */
public class ExcelImport {
    private ExcelImport INSTANSE = new ExcelImport();

    /**
     * excel 2003 suffix
     */
    private static final String EXCEL_XLS_SUFFIX = ".xls" ;

    /**
     * excel 2007 或以上 suffix
     */
    private static final String EXCEL_XLSX_SUFFIX = ".xlsx";

    /**
     * 分隔符 "."
     */
    public static final String POINT = ".";

    /**
     * description: 读取excel数据
     * @param file
     * @return List<List<Object>>
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午3:36:39
     */
    public static List<List<List<Object>>> importFile (File file) throws Exception{
        if(file == null) {
            return null ;
        }
        return readXls2(new FileInputStream(file));
//        if(file.getName().endsWith(EXCEL_XLS_SUFFIX)) {
//            return readXls(new FileInputStream(file));
//        }
//        if(file.getName().endsWith(EXCEL_XLSX_SUFFIX)) {
//            return readXlsx(new FileInputStream(file));
//        }
//
//        throw new RuntimeException("文件不对,必须是excel文件，后缀名以："+EXCEL_XLS_SUFFIX + " 或者 "+ EXCEL_XLSX_SUFFIX);
    }

    /**
     * description: 导入excel --- 支持web
     * @param
     * @param
     * @throws Exception
     * @return List<List<Object>>
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午4:51:01
     */
    public static List<List<List<Object>>> importFile (MultipartFile multipartFile) throws Exception{
        if(multipartFile == null) {
            return null ;
        }
        /*if(multipartFile.getOriginalFilename().endsWith(EXCEL_XLS_SUFFIX)) {
            return readXls(multipartFile.getInputStream());
        }
        if(multipartFile.getOriginalFilename().endsWith(EXCEL_XLSX_SUFFIX)) {
            return readXlsx(multipartFile.getInputStream());
        }*/
        return readXls2(multipartFile.getInputStream());
//        throw new RuntimeException("文件不对,必须是excel文件，后缀名以："+EXCEL_XLS_SUFFIX + " 或者 "+ EXCEL_XLSX_SUFFIX);
    }

    /**
     * 测试 workBookFactory  读取excel
     * @param inputStream
     * @return
     * @throws Exception
     */
    private static List<List<List<Object>>> readXls2(InputStream inputStream) throws Exception {
        List<List<List<Object>>> list = new ArrayList<>();
        // 读取excel
//        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        Workbook workbook = WorkbookFactory.create(inputStream);
        // 获取sheet 页数量
        int sheets = workbook.getNumberOfSheets();
        for(int num = 0 ; num < sheets ; num++ ) {
            Sheet sheet = workbook.getSheetAt(num);
            List<List<Object>> list1= new ArrayList<>();

            if(null == sheet) {
                continue ;
            }
            // sheet 页的总行数
            int rows = sheet.getLastRowNum();
            // startRow 开始读取的行数 --- 第二行开始读
            for( int startRow = 1 ;startRow <= rows  ; startRow ++) {
                Row row = sheet.getRow(startRow);
                List<Object> rowList = new ArrayList<>();
                if(null != row) {
                    // row 行中的 单元格总个数
                    short cells = row.getLastCellNum();
                    for(int x = 0 ; x <= cells ; x++) {
                        Cell cell = row.getCell(x);
                        if(null == cell) {
                            rowList.add("");
                        }else {
                            rowList.add(getXlsxValue2(cell));
                        }
                    }
                    list1.add(rowList);
                }
            }
            list.add(list1);
        }
        return list;
    }

    /**
     * 测试 workBookFactory  获得cell的值
     * @param cell
     * @return
     */
    private static Object getXlsxValue2(Cell cell) {
        Object cellValue = null;
        if (cell != null) {
            //判断cell类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_FORMULA: {
                    //判断cell是否为日期格式
                    if (DateUtil.isCellDateFormatted(cell)) {
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    } else {
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        } else {
            cellValue = "";
        }
        return cellValue;
    }


    /**
     * description: 读取03版excel
     * @param
     * @return List<List<Object>>
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午3:38:44
     */
    private static List<List<Object>> readXls(InputStream inputStream) throws Exception {
        List<List<Object>> list = new ArrayList<>();
        // 读取excel
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        // 获取sheet 页数量
        int sheets = workbook.getNumberOfSheets();
        for(int num = 0 ; num < sheets ; num++ ) {
            HSSFSheet sheet = workbook.getSheetAt(num);
            if(null == sheet) {
                continue ;
            }
            // sheet 页的总行数
            int rows = sheet.getLastRowNum();
            // startRow 开始读取的行数 --- 第二行开始读
            for( int startRow = 1 ;startRow <= rows  ; startRow ++) {
                HSSFRow row = sheet.getRow(startRow);
                List<Object> rowList = new ArrayList<>();
                if(null != row) {
                    // row 行中的 单元格总个数
                    short cells = row.getLastCellNum();
                    for(int x = 0 ; x <= cells ; x++) {
                        HSSFCell cell = row.getCell(x);
                        if(null == cell) {
                            rowList.add("");
                        }else {
                            rowList.add(getXlsValue(cell));
                        }
                    }
                    list.add(rowList);
                }
            }
        }
        return list;
    }

    /**
     * description: 获取 03 版 excel数据
     * @param cell
     * @return String
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午3:54:14
     */
    private static String getXlsValue(HSSFCell cell) {
        if ( cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            if(HSSFDateUtil.isCellDateFormatted(cell)){
                Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
                cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date);
            }else{
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(cell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT)+1,cellValue.length());
                if(strArr.equals("00")){
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            // 其他类型的值，统一设置为 string
            // http://blog.csdn.net/ysughw/article/details/9288307
            cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        }
    }

    /**
     * description: 读取07或以上版本的 excel
     * @param
     * @throws Exception
     * @return List<List<Object>>
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午4:01:25
     */
    private static List<List<Object>> readXlsx(InputStream inputStream) throws Exception {
        List<List<Object>> list = new ArrayList<>();
        // 读取excel ，封装到 XSSFWorkbook 对象
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        int sheets = workbook.getNumberOfSheets();
        for(int num = 0 ;num < sheets ; num++) {
            XSSFSheet sheet = workbook.getSheetAt(num);
            if(null == sheet) {
                continue ;
            }
            // 获取sheet页的总行数
            int rows = sheet.getLastRowNum();
            for(int startRow = 1 ; startRow <= rows ; startRow++ ) {
                // startRow 开始读取的行数， 从第二行开始读取
                XSSFRow row = sheet.getRow(startRow);
                List<Object> rowList = new ArrayList<>();
                if(null != row) {
                    // 获取行总单元格个数
                    short cells = row.getLastCellNum();
                    for(int x = 0 ; x < cells ; x++) {
                        XSSFCell cell = row.getCell(x);
                        if(cell == null) {
                            rowList.add("");
                        }else {
                            rowList.add(getXlsxValue(cell));
                        }
                    }
                    list.add(rowList);
                }
            }
        }
        return list;
    }

    /**
     * description: 获取07或以上版本 excel 数据
     * @param cell
     * @return Object
     * @version v1.0
     * @author w
     * @date 2020年3月31日 下午4:09:03
     */
    private static Object getXlsxValue(XSSFCell cell) {
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            String cellValue = "";
            if (DateUtil.isCellDateFormatted(cell)) {
                Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                cellValue = new SimpleDateFormat("yyyy/MM/dd").format(date);
            } else {
                DecimalFormat df = new DecimalFormat("#.##");
                cellValue = df.format(cell.getNumericCellValue());
                String strArr = cellValue.substring(cellValue.lastIndexOf(POINT) + 1, cellValue.length());
                if (strArr.equals("00")) {
                    cellValue = cellValue.substring(0, cellValue.lastIndexOf(POINT));
                }
            }
            return cellValue;
        } else {
            // 其他类型的值，统一设置为 string
            // http://blog.csdn.net/ysughw/article/details/9288307
            //cell.setCellType(Cell.CELL_TYPE_STRING);
            return String.valueOf(cell.getStringCellValue());
        }
    }

}

测试导入类代码
package com.orient.tdm201.vehicleCheck.business;

import java.io.File;
import java.util.List;

/**

• @author xks

• @date 2020/5/22 9:06

• @description : 导入Excel c测试
  */
  public class TestImportExcel {
  public static void main(String[] args) {
  String path = “D:\test3.xlsx” ;
  File file = new File(path);
  try {
  List<List<List>> importFile = ExcelImport.importFile(file);
  System.out.println(importFile);
  } catch (Exception e) {
  e.printStackTrace();
  }

  }
  }

导出Excel

导出工具类

package com.orient.tdm201.vehicleCheck.business;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;

import java.io.OutputStream;
import java.util.List;

/**
 * @author xks
 * @date 2020/5/21 22:17
 * @description :
 */
public class ExportExcelUtilsTest {
    public void exportExcel(Workbook workbook, int sheetNum,
                            String sheetTitle, String[] headers, List<List<String>> result,
                            OutputStream out) throws Exception {
// 生成一个表格
        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
        CellStyle style = workbook.createCellStyle();
// 设置这些样式
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

        style.setFillBackgroundColor(HSSFColor.WHITE.index);
        style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);

// 生成一个字体
        Font font = workbook.createFont();
        font.setColor(HSSFColor.BLACK.index);
        font.setFontHeightInPoints((short) 12);

      /*  // 设置字体
       Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 20); // 字体高度
         font.setColor(Font.COLOR_RED); // 字体颜色
         font.setFontName("黑体"); // 字体
         font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 宽度
        font.setItalic(true); // 是否使用斜体*/
         // font.setStrikeout(true); //是否使用划线
// 把字体应用到当前的样式
        style.setFont(font);

// 指定当单元格内容显示不下时自动换行
        style.setWrapText(true);

// 产生表格标题行
        Row row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            Cell cell = row.createCell((short) i);

            cell.setCellStyle(style);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text.toString());
        }
// 遍历集合数据，产生数据行
        if (result != null) {
            int index = 1;
            for (List<String> m : result) {
                row = sheet.createRow(index);
                int cellIndex = 0;
                for (String str : m) {
                    Cell cell = row.createCell((short) cellIndex);
                    cell.setCellValue(str.toString());
                    cellIndex++;
                }
                index++;
            }
        }
    }
}

导出测试类

package com.orient.tdm201.vehicleCheck.business;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author xks
 * @date 2020/5/21 22:11
 * @description : 导出Excel 测试
 */
public class TestExcel {
    public static void main(String[] args) {
        try {
            File f = new File("d:" + File.separator + "test5.xls");
            OutputStream out = new FileOutputStream(f);//导出本地
//OutputStream out = response.getOutputStream();//输出客户端
            List<List<String>> data1 = new ArrayList<List<String>>();
            List<List<String>> data2 = new ArrayList<List<String>>();
            List<List<String>> data3 = new ArrayList<List<String>>();
            for (int i = 1; i < 5; i++) {
                List rowData = new ArrayList();
                rowData.add(String.valueOf(i));
                rowData.add("上海东霖柏鸿");
                data1.add(rowData);
            }
            for (int i = 1; i < 5; i++) {
                List rowData = new ArrayList();
                rowData.add(String.valueOf(i));
                rowData.add("深圳东霖柏鸿");
                data2.add(rowData);
            }
            for (int i = 1; i < 5; i++) {
                List rowData = new ArrayList();
                rowData.add(String.valueOf(i));
                rowData.add("广州东霖柏鸿");
                data3.add(rowData);
            }
            String[] headers1 = {"ID1", "用户名1"};
            String[] headers2 = {"ID2", "用户名2"};
            String[] headers3 = {"ID3", "用户名3"};
            ExportExcelUtilsTest eeu = new ExportExcelUtilsTest();//工具类写法在下面
            HSSFWorkbook workbook = new HSSFWorkbook();
            //使用WorkbookFactory  将excel也编译了  参考：https://blog.csdn.net/Chilies/article/details/80986322
//            Workboo/k workbook = WorkbookFactory.create()
            eeu.exportExcel(workbook, 0, "上海", headers1, data1, out);
            eeu.exportExcel(workbook, 1, "深圳", headers2, data2, out);
            eeu.exportExcel(workbook, 2, "广州", headers3, data3, out);
//原理就是将所有的数据一起写入，然后再关闭输入流。
            workbook.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}