Java基于POI4.1.2实现Excel的导入导出功能
**
## 1、导入依赖
**
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
```java
## **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;
public class ExcelUtils {
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));
}
}
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();
}
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);
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;
}
}
```java
## **3、测试**
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();
}
}
}