Java基于POI实现Excel的导入导出功能【utils封装】

港控/mmm° 2022-11-30 01:35 144阅读 0赞

> 文章优先发表在个人公众号：[https://www.xdx97.com/article/747444695356932096][https_www.xdx97.com_article_747444695356932096]

## 一、代码 ##

### 1-1、导入依赖 ###

<dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml-schemas</artifactId>
          <version>4.0.0</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>4.0.0</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>4.0.0</version>
      </dependency>

### 1-2、ExcelUtils ###

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.poifs.filesystem.POIFSFileSystem;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    
    /** * Excel导入导出 * * @author 小道仙 * @date 2020年8月22日 */
    public class ExcelUtils { 
    
        /** * Excel 导出 * @param response HttpServletResponse * @param header 表头 * @param keys map的key值 * @param content 内容数据 * @param title 表格名字 * @param sheetName sheet名 */
        public static void exportExcel(HttpServletResponse response, String[] header,String[] keys, List<Map<String, Object>> content,String title,String sheetName) throws Exception{ 
            title = title + ".xlsx";
            Workbook wb = new SXSSFWorkbook(1000);
            Sheet sheet = wb.createSheet(sheetName);
            Row row = sheet.createRow( 0);
            // 行高
            row.setHeight((short) 700);
            // 列宽
            for (int i = 0; i < header.length; i++) { 
                sheet.setColumnWidth(i, 20 * 256);
            }
            for (int i = 0; i < header.length; i++) { 
                Cell cell = row.createCell(i);
                cell.setCellValue(header[i]);
                cell.setCellStyle(HeaderStyle(wb));
            }
            for (int i = 0; i < content.size(); i++) { 
                Map<String, Object> map = content.get(i);
                row = sheet.createRow((int) i + 1);
                row.setHeight((short) 500);
                for (int j = 0; j < keys.length; j++){ 
                    Cell cell = row.createCell(j);
                    cell.setCellValue(map.get(keys[j]) == null ? "" : map.get(keys[j]).toString());
                    cell.setCellStyle(contentStyle(wb));
                }
            }
    // if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) { 
    // title = new String(title.getBytes("UTF-8"), "ISO8859-1"); // firefox浏览器
    // } else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) { 
    // title = URLEncoder.encode(title, "UTF-8");// IE浏览器
    // } else if (request.getHeader("User-Agent").toUpperCase().indexOf("CHROME") > 0) { 
    // title = new String(title.getBytes("UTF-8"), "ISO8859-1");// 谷歌
    // }
            title = new String(title.getBytes("UTF-8"), "ISO8859-1");
            response.reset();
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Content-Disposition", "attachment; filename=" + title);
            wb.write(response.getOutputStream());
            response.getOutputStream().close();
        }
    
        /** * Excel 导入 * @param file 文件 * @param keys 数据顺序 */
        public static  List<Map<String, Object>>  importExcel(MultipartFile file,String[] keys) throws Exception{ 
            Workbook wb = null;
            String fileName = file.getOriginalFilename();
            if (fileName.endsWith("xls")) { 
                POIFSFileSystem pois = new POIFSFileSystem(file.getInputStream());
                wb = new HSSFWorkbook(pois);
            } else if (fileName.endsWith("xlsx")) { 
                wb = new XSSFWorkbook(file.getInputStream());
            }
            Sheet sheet = wb.getSheetAt(0);
            int rowCount = sheet.getPhysicalNumberOfRows();
            if (sheet.getRow( 1).getPhysicalNumberOfCells() != keys.length){ 
                throw new RuntimeException("导入的Excel和模板的列不匹配");
            }
            List<Map<String,Object>> result = new ArrayList<>();
            for (int i = 0; i < rowCount - 1; i++) { 
                Row row = sheet.getRow(i + 1);
                Map<String,Object> tmp = new HashMap<>();
                for (int j = 0;j < keys.length; j++){ 
                    Cell cell = row.getCell(j);
                    // 把类型转行Spring
    // cell.setCellType(CellType.STRING);
                    tmp.put(keys[j], cell.getStringCellValue());
                }
                result.add(tmp);
            }
            return result;
        }
    
        /** * 表头样式 */
        private static CellStyle HeaderStyle(Workbook wb){ 
            Font font = wb.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 11);
            CellStyle cellStyle = commonStyle(wb);
            cellStyle.setFont(font);
            return cellStyle;
        }
    
        /** * 内容样式 */
        private static CellStyle contentStyle(Workbook wb){ 
            Font font = wb.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 10);
            CellStyle cellStyle = commonStyle(wb);
            cellStyle.setFont(font);
            return cellStyle;
        }
    
        /** * 公共样式 */
        private static CellStyle commonStyle(Workbook wb){ 
            CellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setWrapText(true);// 自动换行
            return style;
        }
    }

## 二、测试 ##

**直接用导出的Excel去测试就好了**

import com.xdx97.test.utils.ExcelUtils;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    @RestController
    public class TestController { 
    
        private String[] excelHeader = {  "姓名", "年纪" };
    
        private String[] excelHeaderKey = {  "name", "age" };
    
    
        @GetMapping("/xdx/excelExport")
        public void excelExport(HttpServletResponse response) throws Exception{ 
    
            List<Map<String,Object>> list = new ArrayList<>();
            Map<String,Object> tmp = new HashMap<>();
            tmp.put("name","张三");
            tmp.put("age","18");
            list.add(tmp);
    
            Map<String,Object> tmp1 = new HashMap<>();
            tmp1.put("name","李四");
            tmp1.put("age","20");
            list.add(tmp1);
    
            ExcelUtils.exportExcel(response, excelHeader, excelHeaderKey,list,"统计表格","表1");
        }
    
        @PostMapping("/xdx/import")
        public void excelImport(MultipartFile file)throws Exception{ 
            List<Map<String, Object>> list = ExcelUtils.importExcel(file,excelHeaderKey);
            for (Map<String, Object> item : list){ 
                for (String key : item.keySet()){ 
                    System.out.print(item.get(key) + " ");
                }
                System.out.println();
            }
        }
    
    }

#### 视频讲解地址 ####

[https://www.bilibili.com/video/BV1kf4y1i761?p=5][https_www.bilibili.com_video_BV1kf4y1i761_p_5]

[https_www.xdx97.com_article_747444695356932096]: https://www.xdx97.com/article/747444695356932096
[https_www.bilibili.com_video_BV1kf4y1i761_p_5]: https://www.bilibili.com/video/BV1kf4y1i761?p=5