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层
@Override
public 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());
}
}
@Override
public 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 entry
ByteArrayOutputStream 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<>();
// 循环工作表Sheet
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++)
{
Sheet hssfSheet = workbook.getSheetAt(numSheet);
if (hssfSheet == null)
{
continue;
}
// 循环行Row
for (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;
}
参考文章:https://blog.csdn.net/weixin_42906244/article/details/105944860