读取.xlsx和.xls
依赖：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<!-- 读取大量excel数据时使用 -->
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>
public class TestUtils {
public static List<List<Object>> readExcel(String filePath, int sheetIndex, String sheetName) {
List<List<Object>> data = new ArrayList<>();
Workbook book = null;
InputStream inputStream = null;
POIFSFileSystem pSystem = null;
try {
/*
*简单判断后缀名，如需通过文件流判断文件类型，
* 请调用getFileTypeByStream方法
* Excel( xls) 文件头：504B03
* Excel( xlsx) 文件头：D0CF11
* */
boolean xls = filePath.endsWith(".xls");
boolean xlsx = filePath.endsWith(".xlsx");
Sheet sheet = null;
inputStream = new FileInputStream(new File(filePath));
ZipSecureFile.setMinInflateRatio(-1.0d);
if (xls) {
// 解析excel
pSystem = new POIFSFileSystem(inputStream);
// 获取整个excel
book = new HSSFWorkbook(pSystem);
}
if (xlsx) {
// 将输入流转换为工作簿对象,大文件读取
book = StreamingReader.builder()
.rowCacheSize(100)//读取到内存中的行数，默认10
.bufferSize(4096)//读取资源，缓存到内存的字节大小。默认1024
.open(inputStream);//打开资源。只能是xlsx文件
// 直接通过流获取整个excel
// book = new XSSFWorkbook(inputStream);
}
if (book != null) {
//获取第一个表单sheet
sheet = book.getSheetAt(sheetIndex);
if (sheetName != null && sheetName.length() > 0) {
sheet = book.getSheet(sheetName);
}
if (sheet != null) {
// 循环行数依次获取列数
int rowIndex = 0;
for (Row row : sheet) {
if (row != null) {
// 获取此行的第一列
int firstCell = 0;
/*
*获取此行的存在数据的第一列
* int firstCell = row.getFirstCellNum();
* */
// 获取此行的存在数据的最后一列
int lastCell = row.getLastCellNum();
// 创建集合，保存每一行的每一列
List<Object> list = new ArrayList<>();
for (int j = firstCell; j < lastCell; j++) {
// 获取第 j 列
Cell cell = row.getCell(j);
if (cell != null) {
Object value = null;
//根据数据类型来获取值
switch (cell.getCellType()) {
case STRING:
value = cell.getStringCellValue();
break;
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型
// 短日期转化为字符串
Date date = cell.getDateCellValue();
if (date != null) {
// 标准0点 1970/01/01 08:00:00
if (date.getTime() % 86400000 == 16 * 3600 * 1000 && cell.getCellStyle().getDataFormat() == 14) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
}
}
} else {
try {
value = getRealStringValueOfDouble(cell.getNumericCellValue());
if (rowIndex == 0) {
Calendar calendar = new GregorianCalendar(1900, 0, -1);
Date date = DateUtils.dateAddDay(calendar.getTime(), NumUtils.parserInt(value));
value = DateUtils.parseData(date, "yyyy-MM-dd");
}
} catch (Exception e) {
value = cell.toString();
}
}
break;
case BOOLEAN:
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
value = "";
break;
case FORMULA: //公式类型
value = parseFormula(cell);
break;
default:
value = cell.toString();
}
list.add(value);
} else {
list.add("");
}
}
data.add(list);
// System.out.println(list);
rowIndex++;
}
}
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (pSystem != null) {
pSystem.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (book != null) {
book.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return data;
}
/**
* 解析公式
*
* @param cell - 单元格
* @return String - 结果
*/
public static String parseFormula(Cell cell) {
String data = null;
try {
switch (cell.getCachedFormulaResultType()) {
case NUMERIC:
if (0 == cell.getCellStyle().getDataFormat()) {
data = String.format("%.4f", cell.getNumericCellValue());
} else {
data = String.valueOf(cell.getNumericCellValue());
}
break;
case STRING:
data = String.valueOf(cell.getRichStringCellValue());
break;
case BOOLEAN:
data = String.valueOf(cell.getBooleanCellValue());
break;
case ERROR:
data = String.valueOf(cell.getErrorCellValue());
break;
default:
data = cell.getCellFormula();
}
} catch (Exception e) {
data = cell.getStringCellValue();
}
return data;
}
private static String getRealStringValueOfDouble(Double d) {
String doubleStr = d.toString();
boolean b = doubleStr.contains("E");
int indexOfPoint = doubleStr.indexOf('.');
if (b) {
int indexOfE = doubleStr.indexOf('E');
BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint
+ BigInteger.ONE.intValue(), indexOfE));
int pow = Integer.valueOf(doubleStr.substring(indexOfE
+ BigInteger.ONE.intValue()));
int xsLen = xs.toByteArray().length;
int scale = xsLen - pow > 0 ? xsLen - pow : 0;
doubleStr = String.format("%." + scale + "f", d);
} else {
Pattern p = Pattern.compile(".0$");
java.util.regex.Matcher m = p.matcher(doubleStr);
if (m.find()) {
doubleStr = doubleStr.replace(".0", "");
}
}
return doubleStr;
}
/**
* 只能读xlsx
*
* @param inputStream
* @param sheetIndex
* @param sheetName
* @return
*/
public static List<List<Object>> readExcel(InputStream inputStream, int sheetIndex, String sheetName) {
List<List<Object>> data = new ArrayList<>();
Workbook book = null;
POIFSFileSystem pSystem = null;
try {
Sheet sheet = null;
// 将输入流转换为工作簿对象,大文件读取
book = StreamingReader.builder()
.rowCacheSize(100)//读取到内存中的行数，默认10
.bufferSize(4096)//读取资源，缓存到内存的字节大小。默认1024
.open(inputStream);//打开资源。只能是xlsx文件
if (book != null) {
//获取第一个表单sheet
//获取第一个表单sheet
sheet = book.getSheetAt(sheetIndex);
if (sheetName != null && sheetName.length() > 0) {
sheet = book.getSheet(sheetName);
}
if (sheet != null) {
int rowIndex = 0;
// 循环行数依次获取列数
for (Row row : sheet) {
if (row != null) {
// 获取此行的第一列
int firstCell = 0;
/*
*获取此行的存在数据的第一列
* int firstCell = row.getFirstCellNum();
* */
// 获取此行的存在数据的最后一列
int lastCell = row.getLastCellNum();
// 创建集合，保存每一行的每一列
List<Object> list = new ArrayList<>();
for (int j = firstCell; j < lastCell; j++) {
// 获取第 j 列
Cell cell = row.getCell(j);
if (cell != null) {
Object value = null;
//根据数据类型来获取值
switch (cell.getCellType()) {
case STRING:
value = cell.getStringCellValue();
break;
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型
// 短日期转化为字符串
Date date = cell.getDateCellValue();
if (date != null) {
// 标准0点 1970/01/01 08:00:00
if (date.getTime() % 86400000 == 16 * 3600 * 1000 && cell.getCellStyle().getDataFormat() == 14) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
if (rowIndex == 0) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
value = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
}
}
}
} else {
try {
value = getRealStringValueOfDouble(cell.getNumericCellValue());
if (rowIndex == 0) {
Calendar calendar = new GregorianCalendar(1900, 0, -1);
Date date = DateUtils.dateAddDay(calendar.getTime(), NumUtils.parserInt(value));
value = DateUtils.parseData(date, "yyyy-MM-dd");
}
} catch (Exception e) {
value = cell.toString();
}
}
break;
case BOOLEAN:
value = Boolean.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
value = "";
break;
case FORMULA: //公式类型
value = parseFormula(cell);
break;
default:
value = cell.toString();
}
list.add(value);
} else {
list.add("");
}
}
data.add(list);
rowIndex++;
// System.out.println(list);
}
}
}
}
} catch (Exception e) {
throw e;
} finally {
try {
if (pSystem != null) {
pSystem.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if (book != null) {
book.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return data;
}
public static void exportExcel(OutputStream outputStream, List<Document> data, String sheetName) throws IOException {
XSSFWorkbook xk = null;
try {
if (data != null && data.size() > 0) {
// 创建一个xlsx
xk = new XSSFWorkbook();
// 创建sheetname第一页页名
XSSFSheet sheet = xk.createSheet(sheetName);
// 字体样式
XSSFFont xssfFont = xk.createFont();
xssfFont.setBold(true);
xssfFont.setFontName("楷体");
xssfFont.setFontHeight(11);
// 表头样式
XSSFCellStyle headStyle = xk.createCellStyle();
// 继承字体样式
headStyle.setFont(xssfFont);
// 竖向居中，横向居中
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置标题一行的单元格的填充颜色为灰色
headStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headStyle.setWrapText(true); // 设置自动换行
headStyle.setHidden(true); // 高度自动
// 创建第一行标题
XSSFRow row1 = sheet.createRow(0);
// 设置宽度
sheet.setDefaultColumnWidth(30);
// 第一行第一列
Document document = data.get(0);
if (document != null) {
List<String> head = new ArrayList<>(document.keySet());
for (int i = 0; i < head.size(); i++) {
XSSFCell Cell1 = row1.createCell(i);
Cell1.setCellValue(head.get(i));
Cell1.setCellStyle(headStyle);
}
// 创建第二行内容
int j = 1;
for (int i = 0; i < data.size(); i++) {
Document document1 = data.get(i);
XSSFRow row2 = sheet.createRow(j++);
for (int k = 0; k < head.size(); k++) {
XSSFCell Cell11 = row2.createCell(k);
Cell11.setCellValue(DocumentUtils.getStringValue(document1, head.get(k)));
}
}
// 转为二进制流进行吐出
xk.write(outputStream);
}
}
} catch (Exception e) {
throw e;
} finally {
try {
if (xk != null) {
xk.close();
}
} catch (Exception e) {
}
try {
outputStream.flush();
} catch (Exception e) {
}
try {
outputStream.close();
} catch (Exception e) {
}
}
}
}
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
