|
pom.xml依赖
org.apache.poi
poi
RELEASE
org.apache.poi
poi-ooxml
RELEASE
org.apache.httpcomponents
httpcore
4.4.9
读取
package com.example.test;
import org.apache.http.entity.ContentType;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.mock.web.MockMultipartFile;
import org.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
public class ExcelReadTest {
/**
*根据文件路径得到MultipartFile
* @param url
* @return
* @throws IOException
*/
public MultipartFile fileToMultipartFile(String url) throws IOException {
File file = new File(url);
FileInputStream fileInputStream = new FileInputStream(file);
MultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(),
ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream);
return multipartFile;
}
/**
* 将文件转化为可操作的类型
* @param multipartFile
* @return
* @throws IOException
*/
public Workbook getWorkFile(MultipartFile multipartFile) throws IOException {
Workbook workbook = null;
//获取文件的类型
String type = multipartFile.getOriginalFilename().substring(multipartFile.getOriginalFilename().lastIndexOf(".")+1);
//获取文件字节输入流
InputStream in = multipartFile.getInputStream();
if ("xls".equals(type)) {
workbook = new HSSFWorkbook(in);
} else if ("xlsx".equals(type)) {
workbook = new XSSFWorkbook(in);
}
return workbook;
}
/**
* 遍历读取excel的每一个单元格
* @param workbook
*/
public void readFile(Workbook workbook) {
//遍历sheet
for (int numSheet = 0; numSheet < workbook.getNumberOfSheets(); numSheet++) {
//得到单个sheet
Sheet sheet = workbook.getSheetAt(numSheet);
if (sheet == null) {
continue;
}
//得到单个sheet的行数
int rowCount = sheet.getLastRowNum();
//从第二行开始，遍历Sheet的每一行(第一行一般是标题，所以不遍历)
for (int rowNum = 1; rowNum |