poi 导入导出
工具类
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface PoiHandler {
String excelHeader() default "";
String dateTimePattern() default "yyyy-MM-dd HH:mm:ss";
String timestampPattern() default "yyyy-MM-dd HH:mm:ss";
String numberPattern() default "##.##";
boolean excelIgnore() default false;
}
public class PoiUtils {
public void createExcel(List<?> list,Class<?> clazz,File filePath) throws IOException{
String[] headers = getHeaders(clazz);
createExcel(list, clazz, filePath, headers);
}
public void createExcel(List<?> list,Class clazz,File filePath,String... headers) throws IOException{
if(isEmpty(list))return;
if(!filePath.exists()){
filePath.createNewFile();
}
FileOutputStream out = new FileOutputStream(filePath);
Workbook workbook = createWorkbook(list, clazz, headers);
workbook.write(out);
}
public void createExcel(List<?> list,Class clazz,OutputStream outputStream) throws IOException{
String[] headers = getHeaders(clazz);
createExcel(list, clazz, outputStream, headers);
}
public void createExcel(List<?> list,Class clazz,OutputStream outputStream,String... headers) throws IOException{
if(isEmpty(list))return;
Workbook workbook = createWorkbook(list, clazz, headers);
workbook.write(outputStream);
}
private Boolean isEmpty(List<?> list){
if(list == null || list.isEmpty()){
System.err.println("需要转换的集合不能为空!!!");
return true;
}else {
return false;
}
}
private Workbook createWorkbook(List<?> list, Class clazz, String[] headers) {
Workbook workbook = null ;
try {
workbook =new HSSFWorkbook();
} catch (Exception e) {
e.printStackTrace();
workbook=new XSSFWorkbook();
}
Sheet sheet=workbook.createSheet(clazz.getSimpleName());
sheet.setDefaultColumnWidth(10);
Row row=sheet.createRow(0);
row.setHeightInPoints((short)18);
int head_length=headers.length;
Cell[] cells=new HSSFCell[head_length];
for (int i=0;i<cells.length;i++) {
cells[i]=row.createCell(i);
cells[i].setCellType(CellType.STRING);
cells[i].setCellStyle(defaultHeaderCellStyle(workbook));
setCellValue(row, i, headers[i], sheet);
}
for (int i = 0; i < list.size(); i++) {
row = this.createRowData(sheet, i+1, list.get(i), clazz, head_length,workbook);
}
return workbook;
}
private Row createRowData(Sheet sheet, int rowNumber, Object obj, Class clazz, int head_length, Workbook workbook) {
Row row = sheet.createRow(rowNumber);
Field[] fields = clazz.getDeclaredFields();
int field_length = fields.length;
String cellValue = "";
for (int c=0,f = 0; c < head_length && f<field_length; c++,f++) {
try {
if( excelIgnore(fields[f]) ){
c--;continue;
}
} catch (ArrayIndexOutOfBoundsException e) {
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}
row.createCell(c);
row.getCell(c).setCellType(CellType.STRING);
row.getCell(c).setCellStyle(defaultCellStyle(workbook));
try {
fields[f].setAccessible(true);
cellValue = getFieldValue(fields[f], obj);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
System.out.println("cellValue==="+cellValue);
setCellValue(row, c, cellValue, sheet);
}
return row;
}
private void setCellValue(Row row, int c, String value, Sheet sheet){
System.out.println("{value:"+value+",length:"+(value.getBytes().length * 256)+"}");
if(value != null && !"".equals(value)){
int beforeWidth = sheet.getColumnWidth(c);
int nowWidth = value.getBytes().length * 256;
System.out.println("{beforeWidth:"+beforeWidth+",nowWidth:"+nowWidth+"}");
if(nowWidth > beforeWidth)sheet.setColumnWidth(c, nowWidth );
row.getCell(c).setCellValue(value);
}
}
private String getFieldValue(Field field,Object obj) throws IllegalArgumentException, IllegalAccessException{
Class<?> ft = null;
ft = field.getType();
System.out.println(field.getName()+" : "+ft);
field.setAccessible(true);
if(ft == String.class){
return String.valueOf(field.get(obj));
}else if(ft == Date.class){
PoiHandler poi = field.getAnnotation(PoiHandler.class);
return new SimpleDateFormat( poi==null ?"yyyy-MM-dd HH:mm:ss":poi.dateTimePattern()).format(field.get(obj));
}else if(ft == Timestamp.class){
String timeStr = "";
try {
PoiHandler poi = field.getAnnotation(PoiHandler.class);
timeStr = new SimpleDateFormat(poi==null ?"yyyy-MM-dd HH:mm:ss":poi.timestampPattern()).format(field.get(obj));
} catch (Exception e) {
e.printStackTrace();
timeStr = String.valueOf(field.get(obj));
}
return timeStr;
}else if(ft == Boolean.class || ft == boolean.class){
return (Boolean) field.get(obj) ? "是":"否";
}else if(ft == String[].class){
return Arrays.asList(field.get(obj)).toString();
}else {
return String.valueOf(field.get(obj));
}
}
private boolean excelIgnore(Field field){
PoiHandler poi = field.getAnnotation(PoiHandler.class);
if(poi == null)return false;
return poi.excelIgnore();
}
private String excelHeader(Field field){
PoiHandler poi = field.getAnnotation(PoiHandler.class);
if(poi == null){
System.err.println("POI工具异常!!!用法错误!!! 请传入Excel表头或者在实体类上写入表头注解,例如:@PoiHandler(excelHeader=\"学生姓名\")");
}
return poi.excelHeader();
}
private String[] getHeaders(Class<?> clazz){
List<Field> fields = new ArrayList<>(Arrays.asList(clazz.getDeclaredFields()));
Iterator<Field> iterator = fields.iterator();
while (iterator.hasNext()) {
Field temp = iterator.next();
if (excelIgnore(temp)){
iterator.remove();
}
}
String[] headers = new String[fields.size()];
for(int i = 0 ;i<fields.size();i++){
headers[i] = excelHeader(fields.get(i));
}
return headers;
}
private CellStyle defaultCellStyle(Workbook workbook){
CellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
private CellStyle defaultHeaderCellStyle(Workbook workbook){
CellStyle style = workbook.createCellStyle();
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
style.setBorderBottom(BorderStyle.SLANTED_DASH_DOT);
Font font = workbook.createFont();
font.setFontHeightInPoints((short)10);
font.setFontName("华文中宋");
font.setBold(true);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
public <T>T parseExcel(Class<T> clazz ,File filePath,String originalFilename){
InputStream inputStream = null;
try {
inputStream = new FileInputStream(filePath);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
if(inputStream == null){
System.err.println("请检查文件路径是否包含一个Excel文件!!!");
return null;
}
return (T) parseExcel(clazz, inputStream, originalFilename);
}
public <T>T parseExcel(Class<T> clazz ,InputStream inputStream,String originalFilename){
Workbook workbook=null;
Sheet sheet = null;
List<T> list = null;
workbook = getWorkbook(inputStream, originalFilename);
sheet = workbook.getSheetAt(0);
String[] headerArr = this.getSheetHeader(sheet);
System.out.println("表头是=>"+Arrays.asList(headerArr));
list = (List<T>) parseSheet(sheet, clazz);
return (T) list;
}
private Workbook getWorkbook(InputStream inputStream, String originalFilename){
Workbook workbook=null;
try {
if(originalFilename.toLowerCase().endsWith("xls"))workbook = new HSSFWorkbook(inputStream);
if(originalFilename.toLowerCase().endsWith("xlsx"))workbook = new XSSFWorkbook(inputStream);
}catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
private String[] getSheetHeader(Sheet sheet){
List<String> headerList = new ArrayList<String>();
Row firstrow=sheet.getRow(0);
Iterator<Cell> cIterator=firstrow.iterator();
while(cIterator.hasNext()){
headerList.add(cIterator.next().getStringCellValue());
}
String[] headerArr = new String[headerList.size()];
return headerList.toArray(headerArr);
}
private <T>T parseSheet(Sheet sheet, Class<T> clazz){
List<T> list = new ArrayList<T>();
Object object = null;
Row row= null;
Field[] fields = clazz.getDeclaredFields();
int rowNum = sheet.getLastRowNum();
System.out.println("Sheet表中行数 LastRowNum=> "+rowNum);
row = sheet.getRow(1);
int celNum = row.getLastCellNum();
System.out.println("Sheet表中列数 celNum=> "+celNum);
for (int r=1; r <= rowNum; r++) {
row = sheet.getRow(r);
if(row==null){break;}
for(int j=0;j<row.getLastCellNum();j++){
row.getCell(j).setCellType(CellType.STRING);
}
Object obj = parseRow(row, clazz, celNum, fields);
list.add((T) obj);
}
return (T) list;
}
private <T>T parseRow(Row row, Class<T> clazz, int celNum, Field[] fields){
Object object = null;
String fieldValue = "";
int field_length = fields.length;
try {
object = clazz.newInstance();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
for (int c=0,f = 0; c < celNum && f < field_length; c++,f++) {
try {
if( excelIgnore(fields[f]) ){
c--;continue;
}
} catch (ArrayIndexOutOfBoundsException e) {
e.printStackTrace();
}catch (Exception e) {
e.printStackTrace();
}
System.out.println("c===="+c+"celNum==="+celNum+"length==="+field_length);
fieldValue = row.getCell(c).getStringCellValue();
System.out.printf("文本值==========="+fieldValue);
try {
setFieldValue(fieldValue, fields[f], object);
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
return (T) object;
}
private void setFieldValue(String fieldValue,Field field,Object object) throws IllegalArgumentException, IllegalAccessException{
Class<?> ft = field.getType();
field.setAccessible(true);
if( fieldValue != null && !"".equals(fieldValue) ){
if(ft == String.class){
field.set(object, fieldValue);
}else if(ft == Integer.class || ft == int.class) {
System.out.println("filed======::::"+fieldValue);
field.set(object, Integer.valueOf(fieldValue));
}else if(ft == BigDecimal.class){
field.set(object, new BigDecimal(fieldValue));
}else if(ft == Date.class){
PoiHandler poi = field.getAnnotation(PoiHandler.class);
System.out.println("date==="+poi + "value==="+fieldValue);
Date dateValue = str2Date(fieldValue,poi==null ?null:poi.dateTimePattern());
field.set(object, dateValue);
}else if(ft == Timestamp.class){
PoiHandler poi = field.getAnnotation(PoiHandler.class);
Date dateValue = str2Date(fieldValue,poi==null ?null:poi.timestampPattern());
Timestamp timestamp = new Timestamp(dateValue.getTime());
field.set(object, timestamp);
}else if(ft == Boolean.class || ft == boolean.class){
field.set(object, Boolean.valueOf(fieldValue));
}else if(ft == Float.class || ft == float.class){
field.set(object, Float.valueOf(fieldValue));
}else if(ft == Double.class || ft == double.class){
field.set(object, Double.valueOf(fieldValue));
}else if(ft == Long.class || ft == long.class){
field.set(object, Long.valueOf(fieldValue));
}else if(ft == Short.class || ft == short.class){
field.set(object, Short.valueOf(fieldValue));
}else if(ft == Byte.class || ft == byte.class){
field.set(object, Byte.valueOf(fieldValue));
}else {
System.err.println(field.getName()+"XXXXXXXXXXX该单元格值{"+fieldValue+"} 对应的字段类型是=>{"+ft+"}");
field.set(object, fieldValue);
}
}
}
private Date str2Date(String value,String dateTimePattern){
System.out.println("dateTimePattern==="+dateTimePattern);
Date date = null;
SimpleDateFormat formatDateTime = null;
SimpleDateFormat formatDate = null;
if( value != null && !"".equals(value) && !"null".equals(value)){
if(dateTimePattern != null && !"".equals(dateTimePattern) && !"null".equals(value)){
formatDateTime = new SimpleDateFormat(dateTimePattern);
}else {
formatDateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
formatDate = new SimpleDateFormat("yyyy-MM-dd");
}
try {
date = formatDateTime.parse(value);
} catch (ParseException e) {
try {
date = formatDate.parse(value);
} catch (ParseException e2) {
e2.printStackTrace();
System.err.println("XXXXXXX请检查你Excel表格中日期的格式是否正确~~~");
}
}
}
return date;
}
}
导出
实体类
public class User{
@PoiHandler(excelHeader = "姓名")
private String username;
@PoiHandler(excelHeader = "所属院校")
private String name;
@PoiHandler(excelHeader = "真实姓名")
private String realName;
@PoiHandler(excelHeader = "性别")
private Integer gender;
@PoiHandler(excelHeader = "手机号")
private String telephone;
@PoiHandler(excelHeader = "座机号")
private String linephone;
@PoiHandler(excelHeader = "邮箱")
private String email;
@PoiHandler(excelHeader = "备注")
private String remark;
@PoiHandler(excelHeader = "组织id",excelIgnore = true)
private Integer orgId;
}
导入
@ResponseBody
@PostMapping(value="/upload1", name = "导入Excel表格")
public Object upload1(@RequestParam(value="myfile",required=false) MultipartFile myfile){
System.out.println("da==="+myfile);
String originalFilename = myfile.getOriginalFilename();
PoiUtils poiUtils = new PoiUtils();
List<User> list = null;
try {
list = (List<User>) poiUtils.parseExcel(User.class, myfile.getInputStream(), originalFilename);
for (User user: list) {
----------------赋值添加操作----------
userRoleService.save(userRole);
}
return new ResultException(200,"导入成功");
} catch (Exception e) {
e.printStackTrace();
return new ResultException(000,"导入失败");
}
}
导入模板下载
@RequestMapping(value = "/download")
public void downloadTpl(HttpServletResponse response,
HttpServletRequest request) throws Exception {
OutputStream out = null;
FileInputStream in = null;
try {
String fileName = "模板";
String excelPath = request.getSession().getServletContext().getRealPath("/poi/student.xlsx");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.reset();
response.addHeader("Content-Disposition", "attachment;filename="
+ fileName + "_" + System.currentTimeMillis() + ".xlsx");
response.setContentType("application/octet-stream;charset=UTF-8");
out = response.getOutputStream();
in = new FileInputStream(excelPath);
byte[] b = new byte[1024];
int len;
while ((len = in.read(b)) > 0) {
response.getOutputStream().write(b, 0, len);
}
out.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != in) {
in.close();
in = null;
}
if (null != out) {
out.close();
out = null;
}
}
}
![存放模板的路径](https://img-blog.csdnimg.cn/20191227161659732.png)
注意:导入导出的字段顺序要和实体类中运用 @PoiHandler(excelHeader=“”)该注解的顺序一致,不然会出现错误