利用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=)