利用poi可以实现excel文件导入和导出功能 本例子结合了springboot做demo
pom文件
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>1.5.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-freemarker -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
<version>1.5.4.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.16</version>
</dependency>
</dependencies>
一个demo的controller类
@RestController
public class DemoController {
private final Logger LOGGER = LoggerFactory.getLogger(DemoController.class);
@RequestMapping("upload")
public Object upload(@PathVariable("file") MultipartFile file) throws Exception {
if (file == null || file.getInputStream() == null) {
LOGGER.error("文件上传失败");
return "文件上传失败";
}
if (file.getSize() > 5 * 1024 * 1024) {
LOGGER.error("文件太大，上传失败");
return "文件太大，上传失败";
}
FileTypeUtil.FileType fileType = FileTypeUtil.getFileType(file.getInputStream());
//XLS_DOC --》2007之前的excel类型 XLSX_DOCX--》 2007之后的excel类型
if (fileType == FileTypeUtil.FileType.XLS_DOC) {
LOGGER.info("开始解析XLS_DOC文件");
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(file.getInputStream());
List<User> getData = readOldExcel(hssfWorkbook);
if (getData == null) {
LOGGER.error("解析失败...");
return "文件数据解析失败";
}
LOGGER.info("数据解析成功");
file.getInputStream().close();
return getData;
} else if (fileType == FileTypeUtil.FileType.XLSX_DOCX) {
LOGGER.info("开始解析XLSX_DOCX文件");
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
List<User> getData = readExcel(xssfWorkbook);
if (getData == null) {
LOGGER.error("解析失败...");
return "文件数据解析失败";
}
LOGGER.info("数据解析成功");
file.getInputStream().close();
return getData;
} else {
LOGGER.error("上传文件类型不正确");
return "文件类型不正确";
}
}
@RequestMapping("download")
public void download(HttpServletResponse response) throws Exception {
//获取模板的输入流
InputStream inputStream = UploadController.class.getClassLoader().getResourceAsStream("templates" + File.separator + "xx.xlsx");
if (inputStream == null) {
LOGGER.error("模板文件的路径不正确");
}
//测试数据
List<User> users = new ArrayList<User>();
for (int i = 0; i < 10; i++) {
User user = new User();
user.setId(i + 1);
user.setUsername("张三" + i + 1);
user.setPassword("123" + i + 1);
users.add(user);
}
//获取模板的工作薄
XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
XSSFSheet sheetAt = workbook.getSheetAt(0);
XSSFRow row = sheetAt.getRow(0);
short rowHeight = row.getHeight();
XSSFRow row1 = null;
XSSFCell cell = null;
for (int j = 0; j < users.size(); j++) {
User user = users.get(j);
row1 = sheetAt.createRow(j + 1);
row1.setHeight(rowHeight);
cell = row1.createCell(0);
cell.setCellValue(user.getId());
cell = row1.createCell(1);
cell.setCellValue(user.getUsername());
cell = row1.createCell(2);
cell.setCellValue(user.getPassword());
}
String filename = new String("文件".getBytes("UTF-8"),"ISO8859-1");
LOGGER.info("文件名"+filename);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-disposition", "attachment;filename="+filename+".xlsx");
workbook.write(response.getOutputStream());
LOGGER.info("文件下载成功！！");
response.getOutputStream().flush();
response.getOutputStream().close();
workbook.close();
}
//处理2007之前的excel
private List<User> readOldExcel(HSSFWorkbook hssfWorkbook) {
List<User> users = new ArrayList<User>();
HSSFSheet sheetAt = hssfWorkbook.getSheetAt(0);
HSSFCell cell = null;
HSSFRow row = null;
for (int i = sheetAt.getFirstRowNum(); i < sheetAt.getPhysicalNumberOfRows(); i++) {
row = sheetAt.getRow(i);
if (row == null) {
LOGGER.warn("获取到一个空行-------》》》》》》" + i + "行");
continue;
}
Object[] objects = new Object[row.getLastCellNum()];
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
cell = row.getCell(j);
switch (cell.getCellTypeEnum()) {
case STRING:
objects[j] = cell.getStringCellValue();
System.out.println(cell.getStringCellValue());
break;
case _NONE:
objects[j] = "";
break;
case BOOLEAN:
objects[j] = cell.getBooleanCellValue();
System.out.println(cell.getBooleanCellValue());
break;
case NUMERIC:
//处理double类型的 1.0===》1
DecimalFormat df = new DecimalFormat("0");
String s = df.format(cell.getNumericCellValue());
objects[j] = s;
System.out.println(s);
break;
default:
objects[j] = cell.toString();
}
}
//处理数据
if (objects != null) {
User user = new User();
user.setId(Integer.parseInt(objects[0].toString()));
user.setUsername((String) objects[1]);
user.setPassword(objects[2].toString());
users.add(user);
}
}
return users;
}
//处理2007之后的excel
private List<User> readExcel(XSSFWorkbook xssfWorkbook) {
List<User> users = new ArrayList<User>();
//获得excel第一个工作薄
XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
//行
XSSFRow row = null;
//列
XSSFCell cell = null;
for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) {
//获取每一行
row = sheet.getRow(i);
//判断是否出现空行
if (row == null) {
LOGGER.warn("获取到一个空行-------》》》》》》" + i + "行");
continue;
}
Object[] objects = new Object[row.getLastCellNum()];
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
cell = row.getCell(j);
if (cell == null) {
LOGGER.warn("获取到一个空列------------》》》》》》" + j + "列");
continue;
}
//第一行数据
switch (cell.getCellTypeEnum()) {
case STRING:
objects[j] = cell.getStringCellValue();
System.out.println(cell.getStringCellValue());
break;
case _NONE:
objects[j] = "";
break;
case BOOLEAN:
objects[j] = cell.getBooleanCellValue();
System.out.println(cell.getBooleanCellValue());
break;
case NUMERIC:
//处理double类型的 1.0===》1
DecimalFormat df = new DecimalFormat("0");
String s = df.format(cell.getNumericCellValue());
objects[j] = s;
System.out.println(s);
break;
default:
objects[j] = cell.toString();
}
}
//处理数据
if (objects != null) {
User user = new User();
user.setId(Integer.parseInt(objects[0].toString()));
user.setUsername((String) objects[1]);
user.setPassword(objects[2].toString());
users.add(user);
}
}
return users;
}
//处理excel中的图片 2007之后 ---》获取 String里面保存着 这个图片的坐标
private Map<String, PictureData> getExcelPic(XSSFWorkbook xssfWorkbook, XSSFSheet xssfSheet) {
Map<String, PictureData> map = new HashMap<String, PictureData>();
for (POIXMLDocumentPart poixmlDocument : xssfWorkbook.getRelations()) {
if (poixmlDocument instanceof XSSFDrawing) {
//拿到 drawing
XSSFDrawing xssfDrawing = (XSSFDrawing) poixmlDocument;
//拿到所有的图形
List<XSSFShape> shapes = xssfDrawing.getShapes();
for (XSSFShape xssfShape : shapes) {
if (xssfShape instanceof XSSFPicture) {
//拿到picture
XSSFPicture xssfPicture = (XSSFPicture) xssfShape;
//拿到anchor 这里存在 图片的坐标
XSSFClientAnchor size = xssfPicture.getPreferredSize();
int row1 = size.getRow1();
short col1 = size.getCol1();
//自定义一个坐标
String xy = String.valueOf(row1) + "_" + String.valueOf(col1);
map.put(xy, (PictureData) xssfPicture.getPictureData());
}
}
}
}
return map;
}
//处理excel中的图片 2007之前
private Map<String, PictureData> getOldExcelPic(HSSFWorkbook hssfWorkbook, HSSFSheet hssfSheet) {
Map<String, PictureData> map = new HashMap<String, PictureData>();
//需要先获取 所有的图片 通过 workbook
List<HSSFPictureData> pictures = hssfWorkbook.getAllPictures();
if (pictures.size() > 0) {
//通过sheet 获得sheet里面所有的 shape
List<HSSFShape> children = hssfSheet.getDrawingPatriarch().getChildren();
for (HSSFShape hssfShape : children) {
//通过shape 获得anchor 来获取图片坐标
HSSFAnchor anchor = hssfShape.getAnchor();
if (hssfShape instanceof HSSFPicture) {
//获得picture
HSSFPicture hssfPicture = (HSSFPicture) hssfShape;
//2007之前的需要通过 picture的索引 ----》 在全局的图片信息里面获取图片数据
int i = hssfPicture.getPictureIndex() - 1;
//获得图片数据
HSSFPictureData hssfPictureData = pictures.get(i);
//拿到图片的坐标
String xy = anchor.getDx1() + "_" + anchor.getDy1();
map.put(xy, (PictureData) hssfPictureData);
}
}
return map;
} else {
return null;
}
}
} ![Java小案例——随机数猜测随机数猜测[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)