POI導出/導入Excel工具類(原來Excel導入導出這麼簡單)
一.首先廢話不多說,先導包
<!--POI相關坐标-->
<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>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>
二.需要給實體類排序,導入排序注解的工具類
/**
* 給實體類排序的工具類
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAttribute {
/** 對應的列名稱 */
String name() default "";
/** 列序号 */
int sort();
/** 字段類型對應的格式 */
String format() default "";
}
三.Excel根據給定模闆導出成Excel的工具類
/**
* 導出Excel工具類
* @param <T>
*/
@Getter
@Setter
public class ExcelExportUtil<T> {
private int rowIndex;
private int styleIndex;
private String templatePath;
private Class clazz;
private Field fields[];
public ExcelExportUtil(Class clazz,int rowIndex,int styleIndex) {
this.clazz = clazz;
this.rowIndex = rowIndex;
this.styleIndex = styleIndex;
fields = clazz.getDeclaredFields();
}
/**
* 基于注解導出
*/
public void export(HttpServletResponse response,InputStream is, List<T> objs,String fileName) throws Exception {
XSSFWorkbook workbook = new XSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
CellStyle[] styles = getTemplateStyles(sheet.getRow(styleIndex));
AtomicInteger datasAi = new AtomicInteger(rowIndex);
for (T t : objs) {
Row row = sheet.createRow(datasAi.getAndIncrement());
for(int i=0;i<styles.length;i++) {
Cell cell = row.createCell(i);
cell.setCellStyle(styles[i]);
for (Field field : fields) {
if(field.isAnnotationPresent(ExcelAttribute.class)){
field.setAccessible(true);
ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
if(i == ea.sort()) {
cell.setCellValue(field.get(t).toString());
}
}
}
}
}
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/octet-stream");
response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes("ISO8859-1")));
response.setHeader("filename", fileName);
workbook.write(response.getOutputStream());
}
public CellStyle[] getTemplateStyles(Row row) {
CellStyle [] styles = new CellStyle[row.getLastCellNum()];
for(int i=0;i<row.getLastCellNum();i++) {
styles[i] = row.getCell(i).getCellStyle();
}
return styles;
}
}
四.導入Excel擷取 List<實體類>集合的工具類
/**
* 導入Excel的工具類
* @param <T>
*/
public class ExcelImportUtil<T> {
private Class clazz;
private Field fields[];
public ExcelImportUtil(Class clazz) {
this.clazz = clazz;
fields = clazz.getDeclaredFields();
}
/**
* 基于注解讀取excel
* is: 檔案上傳的流資訊
* rowIndex: 讀取資料的起始行數
* cellIndex: 讀取資料的起始單元格位置
*/
public List<T> readExcel(InputStream is, int rowIndex,int cellIndex) {
List<T> list = new ArrayList<T>();
T entity = null;
try {
XSSFWorkbook workbook = new XSSFWorkbook(is);
Sheet sheet = workbook.getSheetAt(0);
// 不準确
int rowLength = sheet.getLastRowNum();
System.out.println(sheet.getLastRowNum());
for (int rowNum = rowIndex; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row row = sheet.getRow(rowNum);
entity = (T) clazz.newInstance();
System.out.println(row.getLastCellNum());
for (int j = cellIndex; j < row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
for (Field field : fields) {
if(field.isAnnotationPresent(ExcelAttribute.class)){
field.setAccessible(true);
ExcelAttribute ea = field.getAnnotation(ExcelAttribute.class);
if(j == ea.sort()) {
field.set(entity, covertAttrType(field, cell));
}
}
}
}
list.add(entity);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
/**
* 類型轉換 将cell 單元格格式轉為 字段類型
*/
private Object covertAttrType(Field field, Cell cell) throws Exception {
String fieldType = field.getType().getSimpleName();
if ("String".equals(fieldType)) {
return getValue(cell);
}else if ("Date".equals(fieldType)) {
return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse(getValue(cell)) ;
}else if ("int".equals(fieldType) || "Integer".equals(fieldType)) {
return Integer.parseInt(getValue(cell));
}else if ("double".equals(fieldType) || "Double".equals(fieldType)) {
return Double.parseDouble(getValue(cell));
}else {
return null;
}
}
/**
* 格式轉為String
* @param cell
* @return
*/
public String getValue(Cell cell) {
if (cell == null) {
return "";
}
switch (cell.getCellType()) {
case STRING:
return cell.getRichStringCellValue().getString().trim();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date dt = DateUtil.getJavaDate(cell.getNumericCellValue());
return new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(dt);
} else {
// 防止數值變成科學計數法
String strCell = "";
Double num = cell.getNumericCellValue();
BigDecimal bd = new BigDecimal(num.toString());
if (bd != null) {
strCell = bd.toPlainString();
}
// 去除 浮點型 自動加的 .0
if (strCell.endsWith(".0")) {
strCell = strCell.substring(0, strCell.indexOf("."));
}
return strCell;
}
case BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());
default:
return "";
}
}
}
五.最佳實踐(先模闆導出,再模闆導入)
1.因為Excel和實體類順序可能不一樣,是以要給實體類加上排序的注解
/*
*随便模拟一個實體類
*/
private String id;
/**
* 手機号碼
*/
@ExcelAttribute(sort = 2)
private String mobile;
/**
* 使用者名稱
*/
@ExcelAttribute(sort = 1)
private String username;
/**
* 部門ID
*/
@ExcelAttribute(sort = 6)
private String departmentId;
/**
* 入職時間
*/
@ExcelAttribute(sort = 5)
private Date timeOfEntry;
/**
* 聘用形式
*/
@ExcelAttribute(sort = 4)
private Integer formOfEmployment;
/**
* 工号
*/
@ExcelAttribute(sort = 3)
private String workNumber;
Excel示例
2.示範根據模闆将資料導出為Excel
//1.先從資料庫中查詢出一個List集合來,就是往Excel填充的資料
List<T> list = xxxService.findByReport(Id);
//2.Excel模闆在resources檔案夾下,讀取Excel模闆檔案
Resource resource = new ClassPathResource("excel-template/hr-demo.xlsx");
FileInputStream fis = new FileInputStream(resource.getFile());
//3.通過工具類下載下傳
//ExcelExportUtil( 第一個參數實體類的位元組碼檔案, 第二個參數從第幾個行開始寫資料,第三個參數從第幾行開始讀取模班的樣式)
export(response響應對象,輸入流檔案,資料(list集合), "Excel叫啥名")
new ExcelExportUtil(實體類的位元組碼檔案.class,2,2).export(response,fis,list,示範Excel.xlsx");
3.讀取Excel裡的資料構造List集合
@RequestMapping(value="/user/import",method = RequestMethod.POST)
//從前端上傳過來的檔案
public Result importUser(@RequestParam(name="file") MultipartFile file) throws Exception {
//ExcelImportUtil(第一個參數實體類的位元組碼檔案)
//readExcel(第一個參數檔案的輸入流, 第二個參數讀取資料的起始行數, 第三個參數讀取資料的起始單元格位置)
List<實體類> list = new ExcelImportUtil<實體類>(實體類.class).readExcel(file.getInputStream(), 1, 1);
![Java小案例——随機數猜測随機數猜測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)