java中用POI方式实现EXCEL导入/导出

java中用POI方式实现EXCEL导入/导出

JAR

<!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17-beta1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.xmlbeans</groupId>
            <artifactId>xmlbeans</artifactId>
            <version>2.4.0</version>
        </dependency>

工具类

package com.gt.hydrologicdata.utils;

import com.gt.hydrologicdata.enmus.ResultEnum;
import com.gt.hydrologicdata.entity.TableItemIndex;
import com.gt.hydrologicdata.handlers.CustomException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @program: HydrologicData
 * @description:文件导入
 * @author: KJH
 * @create: 2019-09-16 11:36
 */
public class FileUtils {


    public static ArrayList<Map<String, Object>> fileImport(MultipartFile file) throws CustomException {
        ArrayList<Map<String, Object>> data = new ArrayList<>(); // 数据存储
        if (file.isEmpty()) {
            throw new CustomException(ResultEnum.FILE_NULL_ERROR);
        }
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if (!fileName.endsWith("xls") && !fileName.endsWith("xlsx")) {
            throw new CustomException(ResultEnum.FILE_NOT_EXCEL);
        }
        Workbook workBook = getWorkBook(file);
        if (null != workBook) {
//            int numberOfSheets = workBook.getNumberOfSheets();//有多少张表
//            if(numberOfSheets != 1) throw new CustomException(ResultEnum.FILE_EXCEL_NO_ONE_TABLE);
            Sheet sheet = workBook.getSheetAt(0);
            int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//行数

            if (physicalNumberOfRows > 0) {
                //整合表格数据
                Row rowHead = sheet.getRow(0);//第一行
                int colums = rowHead.getPhysicalNumberOfCells();//列数
                List<String> headList = new ArrayList<>();
                for (int k = 0; k < colums; k++) {
                    String head = rowHead.getCell(k).getStringCellValue().toUpperCase();
                    //非法字符處理
                    headList.add(head);
                }
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    if (j == 0) continue;//标题行
                    Map<String, Object> map = new HashMap<>();
                    Row row = sheet.getRow(j);
                    for (int k = 0; k < colums; k++) {
                        Cell cell = row.getCell(k);
                        String value = getStringValueFromCell(cell);
                        map.put(headList.get(k), value);
                    }
                    data.add(map);
                }

            }
        }
        return data;

    }

    public static Workbook getWorkBook(MultipartFile file) throws CustomException {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;

        //获取excel文件的io流
        InputStream is = null;
        try {
            is = file.getInputStream();
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if (fileName.endsWith("xls")) {
                //2003
                workbook = new HSSFWorkbook(is);
            } else if (fileName.endsWith("xlsx")) {
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw new CustomException(ResultEnum.FILE_NOT_getWorkBook);
        }
        return workbook;
    }

    //表格单元格数据
    private static String getStringValueFromCell(Cell cell) {
        SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
        DecimalFormat decimalFormat = new DecimalFormat("#.########");
        String cellValue = null;
        if (cell == null) {
            return cellValue;
        }
        switch (cell.getCellTypeEnum()) {
            case STRING:  //String
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN://Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC://数值类型 - 整数，小数，时间
                double v = cell.getNumericCellValue();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {//时间类型
                    Date date = HSSFDateUtil.getJavaDate(v);
                    cellValue = sFormat.format(date);
                } else {
                    cellValue = decimalFormat.format(v);
                }
                break;
            case BLANK://空白
                cellValue = null;
                break;
            case _NONE://未知类型
                cellValue = "";
                break;
            case ERROR://错误单元格
                cellValue = "";
                break;
            case FORMULA://公式
                cellValue = "";
                break;
        }
        return cellValue;
    }


    /**
     * 文件导出
     */
    public static void fileExport(HttpServletResponse response, List<Map<String, Object>> headerAndDataList,
                                  Date startTm, Date endTm) throws Exception {
        OutputStream os = null;
        SXSSFWorkbook workBook = null;
        if (null != headerAndDataList && headerAndDataList.size() > 0) {
            try {
                // 声明一个工作薄
                // XSSFWorkbook和SXSSFWorkbook 当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap
                // space;内存溢出错误。这时应该用SXSSFworkbook
                workBook = new SXSSFWorkbook();
                // 生成一个表格
                SXSSFSheet sheet = workBook.createSheet();
                sheet.createFreezePane(0, 1, 0, 1);
                workBook.setSheetName(0, "sheet1");
                CellStyle style = workBook.createCellStyle();
                Font font = workBook.getFontAt((short) 0);
                font.setCharSet(HSSFFont.DEFAULT_CHARSET);
                font.setFontHeightInPoints((short) 12);//更改默认字体大小
                font.setFontName("宋体");//
                style.setFont(font);
                int dataListNum = 1;
//            int iNum = 0;
                SXSSFRow titleRow = sheet.createRow(0);
                //获取第一行的信息，并填充到excel第一行
                List<TableItemIndex> dataListOne = (List<TableItemIndex>) headerAndDataList.get(0).get("header");
                titleRow.createCell(0).setCellValue(dataListOne.get(0).getCtname());
                titleRow.createCell(1).setCellValue(dataListOne.get(0).getTname());
                titleRow = sheet.createRow(1);
                //把查询到的数据填充到excel
                for (int i = 0; i < headerAndDataList.size(); i++) {
                    Map<String, Object> maps = headerAndDataList.get(i);
                    List<TableItemIndex> headerList = (List<TableItemIndex>) maps.get("header");//表的表头数据集
                    List<Map<String, Object>> dataList = (List<Map<String, Object>>) maps.get("data");//表的数据集
                    // 创建表格标题行 第一行已存在，所以从第二行开始
                    if (i > 0) {
//                    iNum = i;
                        //记录上一个dataList数据的size
                        List<Map<String, Object>> dataListPrior =
                                (List<Map<String, Object>>) headerAndDataList.get(i - 1).get("data");
                        dataListNum = dataListPrior.size() + dataListNum + 2;//2等价于1+1，前一个1代表空一行，后一个代表新添加一行中文表名和英文表名
                        titleRow = sheet.createRow(dataListNum);
                        titleRow.createCell(0).setCellValue(headerList.get(0).getCtname());
                        titleRow.createCell(1).setCellValue(headerList.get(0).getTname());
                        //新添加一行表头
                        dataListNum = dataListNum + 1;
                        titleRow = sheet.createRow(dataListNum);
                    }
                    //添加表头
                    for (int j = 0; j < headerList.size(); j++) {
                        TableItemIndex tableItemIndex = headerList.get(j);
                        titleRow.createCell(j).setCellValue(tableItemIndex.getCfname());
                    }
                    //添加数据
                    for (int m = 0; m < dataList.size(); m++) {
                        int rows = i == 0 ? m + 1 : m;
                        if (i != 0) {
                            rows = dataListNum + rows;
                        }
                        Map<String, Object> map = dataList.get(m);
                        SXSSFRow titleRowNext = sheet.createRow(rows + 1);
                        for (int n = 0; n < headerList.size(); n++) {
                            String fname = headerList.get(n).getFname();
                            Object o = map.get(fname);//获取表字段英文名称
                            //如果o是空，调用大写获取表字段英文名称(数据库字段可能存在大小写问题)
                            if (ObjectUtils.isEmpty(o)) {
                                o = map.get(fname.toUpperCase());
                            }
                            titleRowNext.createCell(n).setCellValue(String.valueOf(o));
                        }
                    }
                }
                os = response.getOutputStream();
                String fileName = DateUtils.getCustomDateChangeString(startTm, "yyyy年MM月dd日") + "_"
                        + DateUtils.getCustomDateChangeString(endTm, "yyyy年MM月dd日");
                response.reset();//清除首部的空白行
                response.addHeader("Content-Disposition",
                        "attachment;filename=" + new String((fileName + ".xlsx").getBytes(
                                "UTF-8"), "ISO-8859-1"));
                workBook.write(os);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (os != null) {
                        os.flush();
                        os.close();
                    }
                } catch (IOException e) {
                    throw new RuntimeException("导出报表异常");
                }
            }
        }else {
            String fileName ="DATA_IS_NULL";
            response.reset();//清除首部的空白行
            response.addHeader("Content-Disposition",
                    "attachment;filename=" + new String((fileName + ".xlsx").getBytes(
                            "UTF-8"), "ISO-8859-1"));
        }

    }
}

表头字段可以自己后台自定义维护，也可以放到excel中自己获取

调用文件工具

 public void fileLoad(MultipartFile file) throws Exception {
  List<Map<String, Object>> mapList = FileUtils.fileImport(file,tname);
  数据处理（批量/单个添加数据库）
 }

版权声明：本文为博主原创文章，转载请附上博文链接！
https://blog.csdn.net/qq_29379559/article/details/105492507