Java 导入Excel竖版表头/导入Zip包含多个Excel

表头/导入Zip包含多个Excel"/>

Java 导入Excel竖版表头/导入Zip包含多个Excel

工作中遇到了竖版的表头，像下面这样的。真·反人类！
主要还是几百个这样Excel打包成了一个ZIP包。
表头如果是在第一行的话。 一个Excel搞定。 通过EasyExcel或者EasyPOI简简单单处理。

下面代码直接拷走用就完了。

1、pom依赖

这里我用的是EasyExcel的依赖，内嵌了poi的依赖。版本如下图。

 <dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.7</version></dependency>

2、controller层

@PostMapping("/importFilePortrait.do")@ApiOperation(value = "poi导入文件(竖版模板)")public void importFilePortrait(MultipartFile file) {employeeService.importFilePortrait(file);}@PostMapping("/importZipFileContainsExcel.do")@ApiOperation(value = "poi导入zip 包含多个excel")public void importZipFileContainsExcel(MultipartFile file) {employeeService.importZipFileContainsExcel(file);}

3、service层

	@Overridepublic void importFilePortrait(MultipartFile file) {try {//获取上传文件的对象Workbook hssfWorkbook = WorkbookFactory.create(file.getInputStream());// Workbook hssfWorkbook = new HSSFWorkbook(inputStream);Map<String, String> map = readExcel(hssfWorkbook);System.out.println(map);}catch (Exception e) {log.error(e.getMessage());}}@Overridepublic void importZipFileContainsExcel(MultipartFile file) {try {InputStream inputStream = file.getInputStream();// 如果文件名称包含中文，需要加上Charset.forName("gbk")ZipInputStream zipInputStream = new ZipInputStream(inputStream, Charset.forName("gbk"));ZipEntry zipEntry = null;while ((zipEntry = zipInputStream.getNextEntry()) != null) {if (!zipEntry.isDirectory() && (zipEntry.getName().endsWith(".xlsx") || zipEntry.getName().endsWith(".xls"))) {// Read the Excel file from the Zip entryByteArrayOutputStream outputStream = new ByteArrayOutputStream();byte[] buffer = new byte[4096];int length = -1;while ((length = zipInputStream.read(buffer)) != -1) {outputStream.write(buffer, 0, length);}Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(outputStream.toByteArray()));Map<String, String> map = readExcel(workbook);System.out.println(map);zipInputStream.closeEntry();}}zipInputStream.close();} catch (Exception e) {log.error(e.getMessage());}}public Map<String, String> readExcel(Workbook  workbook) throws Exception {Map<String, String> hashMap = new HashMap<>();// 循环工作表Sheetfor (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++){Sheet hssfSheet = workbook.getSheetAt(numSheet);if (hssfSheet == null) {continue;}// 循环行Rowfor (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {Row hssfRow = hssfSheet.getRow(rowNum);if (hssfRow != null) {Cell key = hssfRow.getCell(0);Cell cell = hssfRow.getCell(1);//解析文件中各种类型的数据信息String stringCellValue = new DataFormatter().formatCellValue(cell);//key值为去空格的值hashMap.put(key.toString().trim(), stringCellValue);}}}workbook.close();return hashMap;}

参考文章：