java使用poi读取excel表格
买泛域名SSL证书 送5斤装现摘猕猴桃一箱、同时提供技开源商城搭建免费技术支持。
泛域名ssl证书 239元1年送1个月、单域名39元1年，Sectigo（原Comodo证书）全球可信证书，强大的兼容性，高度安全性,如有问题7天内可退、可开发票
加微信VX 18718058521 备注SSL证书
【腾讯云】2核2G4M云服务器新老同享99元/年，续费同价
泛域名ssl证书 239元1年送1个月、单域名39元1年，Sectigo（原Comodo证书）全球可信证书，强大的兼容性，高度安全性,如有问题7天内可退、可开发票
加微信VX 18718058521 备注SSL证书
【腾讯云】2核2G4M云服务器新老同享99元/年，续费同价
java读取excel中包括图片、日期、字符串格式。
导入依赖
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>commons-io</groupId> <artifactId>commons-io</artifactId> <version>2.6</version> </dependency> <dependency> <groupId>commons-fileupload</groupId> <artifactId>commons-fileupload</artifactId> <version>1.3.3</version> </dependency>
excel表格样式
实体类
public class User {
private Integer id;
private String name;
private Integer age;
private String sex;
private String picPath;
private String date;
public User() {
}
}读取表格工具类
import com.csv.pojo.User;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* Excel工具类
*/
public class ExcelUtil {
/**
* 判断文件是否存在
*
* @param fileName
* @return
*/
public static boolean fileExist(String fileName) {
boolean flag = false;
File file = new File(fileName);
flag = file.exists();
return flag;
}
/**
* 新文件写入数据
*
* @return
*/
public static List<User> writeNewFile() {
List<User> userList = new ArrayList<>();
User user;
for (int i = 0; i < 10; i++) {
user = new User();
user.setName("u" + i);
user.setAge(i);
user.setSex("男");
userList.add(user);
}
return userList;
}
/**
* 从excel中读取数据
*
* @param xls true xls文件，false xlsx文件
* @param inputStream 文件输入流
* @return 数据封装到对象
*/
public static List<User> getDataFromExcel(boolean xls, InputStream inputStream) {
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
Workbook workbook = null;
List<User> userList = new ArrayList<>();
try {
if (xls) {
workbook = new HSSFWorkbook(inputStream);
} else {
workbook = new XSSFWorkbook(inputStream);
}
// 得到一个工作表
Sheet sheet = workbook.getSheetAt(0);
// 得到表头
Row rowHead = sheet.getRow(0);
// 判断表头是否正确
if (rowHead.getPhysicalNumberOfCells() < 1) {
throw new Exception("表头错误");
}
// 获取照片
Map<String, PictureData> pictures;
if (xls) {
pictures = getPictures((HSSFSheet) sheet);
} else {
pictures = getPictures((XSSFSheet) sheet);
}
// 获取数据
for (int i = 2; i <= sheet.getLastRowNum(); i++) {
// 获取第i行
Row row = sheet.getRow(i);
// 获取第i行各个列的数据
Integer id = (int) row.getCell(0).getNumericCellValue();
String username = row.getCell(1).getStringCellValue();
Integer age = (int) row.getCell(2).getNumericCellValue();
String sex = row.getCell(3).getStringCellValue();
String savePath = savePicture(id, pictures.get(i + "-4"));
String date;
// 判断该单元格的数据类型
switch (row.getCell(5).getCellTypeEnum()) {
case STRING: // 字符串类型
date = row.getCell(5).getStringCellValue();
break;
case NUMERIC: // 日期类型
date = sf.format(row.getCell(5).getDateCellValue());
break;
default:
date = "";
break;
}
userList.add(new User(id, username, age, sex, savePath, date));
}
} catch (Exception e) {
e.printStackTrace();
}
return userList;
}
/**
* xls获取照片
*/
private static Map<String, PictureData> getPictures(HSSFSheet sheet) {
Map<String, PictureData> map = new HashMap<>();
List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren();
for (HSSFShape shape : list) {
if (shape instanceof HSSFPicture) {
HSSFPicture picture = (HSSFPicture) shape;
HSSFClientAnchor clientAnchor = picture.getClientAnchor();
HSSFPictureData pictureData = picture.getPictureData();
String key = clientAnchor.getRow1() + "-" + clientAnchor.getCol1();
map.put(key, pictureData);
}
}
return map;
}
/**
* xlsx获取照片
*/
private static Map<String, PictureData> getPictures(XSSFSheet sheet) {
Map<String, PictureData> map = new HashMap<>();
List<POIXMLDocumentPart> list = sheet.getRelations();
for (POIXMLDocumentPart part : list) {
if (part instanceof XSSFDrawing) {
XSSFDrawing drawing = (XSSFDrawing) part;
List<XSSFShape> shapes = drawing.getShapes();
for (XSSFShape shape : shapes) {
XSSFPicture picture = (XSSFPicture) shape;
XSSFClientAnchor anchor = picture.getPreferredSize();
CTMarker marker = anchor.getFrom();
String key = marker.getRow() + "-" + marker.getCol();
map.put(key, picture.getPictureData());
}
}
}
return map;
}
/**
* 照片保存在本地
*/
private static String savePicture(int id, PictureData picData) throws IOException {
if (picData != null) {
byte[] data = picData.getData();
String fileName = id + "-" + "照片";
String filePath = "D:\\JavaCode\\testmaven14excel\\image\\";
FileOutputStream out = new FileOutputStream(filePath + fileName + ".png");
out.write(data);
out.close();
return filePath + fileName + ".png";
}
return "";
}
}控制层接口
@RestController
@RequestMapping("/excel")
@CrossOrigin(maxAge = 3600)
public class ExcelController {
@RequestMapping(value = "/upload",method = RequestMethod.POST)
public ResultMessage readFile(@RequestParam(value = "excelFile") MultipartFile excelFile, HttpServletRequest request) throws IOException {
// 文件名
String name = excelFile.getOriginalFilename();
boolean b;
if (name.endsWith(".xls")) {
b=true;
}else if(name.endsWith(".xlsx")){
b=false;
}else if(name.endsWith(".jpg")||name.endsWith(".png")){
System.out.println("收到图片文件"+name);
return new ResultMessage(true,"这是图片",null);
}
else {
return new ResultMessage(false,"不是excel文件",null);
}
List<User> dataFromExcel = ExcelUtil.getDataFromExcel(b,excelFile.getInputStream());
for (User user : dataFromExcel) {
System.out.println(user.toString());
}
return new ResultMessage(true,"解析文件",dataFromExcel);
}
}效果演示
{
"id": 1,
"name": "瑞秋",
"age": 25,
"sex": "女",
"picPath": "D:\\JavaCode\\testmaven14excel\\image\\1-照片.png",
"date": "2022-02-18"
},
{
"id": 2,
"name": "莫妮卡",
"age": 26,
"sex": "女",
"picPath": "D:\\JavaCode\\testmaven14excel\\image\\2-照片.png",
"date": "2022-02-19"
},
{
"id": 3,
"name": "乔伊",
"age": 24,
"sex": "男",
"picPath": "",
"date": "2022-02-20"
},
{
"id": 4,
"name": "钱德勒",
"age": 26,
"sex": "男",
"picPath": "",
"date": "2022/2/21"
},
{
"id": 5,
"name": "罗斯",
"age": 27,
"sex": "男",
"picPath": "",
"date": "2022/2/22"
},
{
"id": 6,
"name": "菲比",
"age": 25,
"sex": "女",
"picPath": "",
"date": "2022-02-23"
}
