Apache POI报表工具类
package com
.itheima
.utils
;
import java
.io
.FileNotFoundException
;
import java
.io
.IOException
;
import java
.io
.InputStream
;
import java
.text
.SimpleDateFormat
;
import java
.util
.ArrayList
;
import java
.util
.List
;
import org
.apache
.poi
.hssf
.usermodel
.HSSFWorkbook
;
import org
.apache
.poi
.ss
.usermodel
.Cell
;
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
.XSSFWorkbook
;
import org
.springframework
.web
.multipart
.MultipartFile
;
public class POIUtils {
private final static String xls
= "xls";
private final static String xlsx
= "xlsx";
private final static String DATE_FORMAT
= "yyyy/MM/dd";
public static List
<String
[]> readExcel(MultipartFile file
) throws IOException
{
checkFile(file
);
Workbook workbook
= getWorkBook(file
);
List
<String
[]> list
= new ArrayList<String
[]>();
if(workbook
!= null
){
for(int sheetNum
= 0;sheetNum
< workbook
.getNumberOfSheets();sheetNum
++){
Sheet sheet
= workbook
.getSheetAt(sheetNum
);
if(sheet
== null
){
continue;
}
int firstRowNum
= sheet
.getFirstRowNum();
int lastRowNum
= sheet
.getLastRowNum();
for(int rowNum
= firstRowNum
+1;rowNum
<= lastRowNum
;rowNum
++){
Row row
= sheet
.getRow(rowNum
);
if(row
== null
){
continue;
}
int firstCellNum
= row
.getFirstCellNum();
int lastCellNum
= row
.getPhysicalNumberOfCells();
String
[] cells
= new String[row
.getPhysicalNumberOfCells()];
for(int cellNum
= firstCellNum
; cellNum
< lastCellNum
;cellNum
++){
Cell cell
= row
.getCell(cellNum
);
cells
[cellNum
] = getCellValue(cell
);
}
list
.add(cells
);
}
}
workbook
.close();
}
return list
;
}
public static void checkFile(MultipartFile file
) throws IOException
{
if(null
== file
){
throw new FileNotFoundException("文件不存在!");
}
String fileName
= file
.getOriginalFilename();
if(!fileName
.endsWith(xls
) && !fileName
.endsWith(xlsx
)){
throw new IOException(fileName
+ "不是excel文件");
}
}
public static Workbook
getWorkBook(MultipartFile file
) {
String fileName
= file
.getOriginalFilename();
Workbook workbook
= null
;
try {
InputStream is
= file
.getInputStream();
if(fileName
.endsWith(xls
)){
workbook
= new HSSFWorkbook(is
);
}else if(fileName
.endsWith(xlsx
)){
workbook
= new XSSFWorkbook(is
);
}
} catch (IOException e
) {
e
.printStackTrace();
}
return workbook
;
}
public static String
getCellValue(Cell cell
){
String cellValue
= "";
if(cell
== null
){
return cellValue
;
}
String dataFormatString
= cell
.getCellStyle().getDataFormatString();
if(dataFormatString
.equals("m/d/yy")){
cellValue
= new SimpleDateFormat(DATE_FORMAT
).format(cell
.getDateCellValue());
return cellValue
;
}
if(cell
.getCellType() == Cell
.CELL_TYPE_NUMERIC
){
cell
.setCellType(Cell
.CELL_TYPE_STRING
);
}
switch (cell
.getCellType()){
case Cell
.CELL_TYPE_NUMERIC
:
cellValue
= String
.valueOf(cell
.getNumericCellValue());
break;
case Cell
.CELL_TYPE_STRING
:
cellValue
= String
.valueOf(cell
.getStringCellValue());
break;
case Cell
.CELL_TYPE_BOOLEAN
:
cellValue
= String
.valueOf(cell
.getBooleanCellValue());
break;
case Cell
.CELL_TYPE_FORMULA
:
cellValue
= String
.valueOf(cell
.getCellFormula());
break;
case Cell
.CELL_TYPE_BLANK
:
cellValue
= "";
break;
case Cell
.CELL_TYPE_ERROR
:
cellValue
= "非法字符";
break;
default:
cellValue
= "未知类型";
break;
}
return cellValue
;
}
}
转载请注明原文地址: https://mac.8miu.com/read-500265.html