精华内容
下载资源
问答
  • Excel导入导出工具类

    2020-10-20 10:44:23
    Excel导入导出工具类,基于POI实现;紧密结合实体类,导入功能返回List,方便MyBatis批量导入。导出功能自带下载功能。
  • excel导入导出工具类

    千次阅读 2020-09-18 23:10:42
    文章目录使用java导出导入excel工具类导出导出工具类导入工具类1工具类2 导出 /** * * @return */ @Override public File exportLogInfo() { Map<String,Object> resultMap = new HashMap<String, ...

    使用java导出导入excel工具类

    导出

    /**
         *
         * @return
         */
        @Override
        public File exportLogInfo() {
            Map<String,Object> resultMap = new HashMap<String, Object>();
            String sqlExceptSelect = "select *  from ZBIMS.IMS_EM_BasicInfo_DD where DELETEFLAG = 'N'";
    
            Map<String,String> IMS_EM_BasicInfo_DD_Columns = new LinkedHashMap<>();
            IMS_EM_BasicInfo_DD_Columns.put("DDSERIALNUM","xx");
            IMS_EM_BasicInfo_DD_Columns.put("MISSILEMODEL","xx");
            IMS_EM_BasicInfo_DD_Columns.put("DDTYPE","xx");
            IMS_EM_BasicInfo_DD_Columns.put("ZDBTYPE","xxx种类");
            IMS_EM_BasicInfo_DD_Columns.put("UNITS","xx");
            IMS_EM_BasicInfo_DD_Columns.put("COMPLETIONTIME","xx");
            IMS_EM_BasicInfo_DD_Columns.put("DELIVERYDATE","xx");
            IMS_EM_BasicInfo_DD_Columns.put("SPECIFIEDLIFE","xx");
            IMS_EM_BasicInfo_DD_Columns.put("PERIODICCHECKDATE","");
            IMS_EM_BasicInfo_DD_Columns.put("PERIODICCHECKCYCLE","");
            IMS_EM_BasicInfo_DD_Columns.put("PROLONGLIFENUM","");
            IMS_EM_BasicInfo_DD_Columns.put("LIFEEXTENSION","本次xx");
            IMS_EM_BasicInfo_DD_Columns.put("LIFEDATE","xx");
            
            ExcelExportUtil excelExportUtil = new ExcelExportUtil();
            File file = new File(excelExportUtil.getTitle());
            
            try {
                List<Record> ddList = Db.find(sqlExceptSelect);
                excelExportUtil.saveFile(IMS_EM_BasicInfo_DD_Columns, ddList, file);
                resultMap.put("msg","成功");
                resultMap.put("code",0);
            } catch (Exception e) {
                e.printStackTrace();
                resultMap.put("msg","失败");
                resultMap.put("code",1);
            }
            return file;
        }
    

    导出工具类

    package com.ims.zb.missile;
    
    import com.jfinal.plugin.activerecord.Record;
    import org.apache.poi.hssf.usermodel.*;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.BorderStyle;
    
    import java.io.File;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    
    public class ExcelExportUtil {
        private static final String filePath = "/download/temp/";
    
        public static String getTitle(){
            File f = new File(filePath);
            if (!f.exists()){
                f.mkdirs();
            }
            Date date = new Date();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            String title = filePath + df.format(date)+"_xx基本信息.xls";
            System.out.println("********title="+title);
            return title;
        }
        public static String getTitle1(){
            File f = new File(filePath);
            if (!f.exists()){
                f.mkdirs();
            }
            Date date = new Date();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            String title = filePath + df.format(date)+"_xx设备信息.xls";
            System.out.println("********title="+title);
            return title;
        }
    
        public static String getTitle2(){
            File f = new File(filePath);
            if (!f.exists()){
                f.mkdirs();
            }
            Date date = new Date();
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            String title = filePath + df.format(date)+"_软件列表信息.xls";
            System.out.println("********title="+title);
            return title;
        }
        public static File saveFile(Map<String,String> headData, List<Record> list, File file){
            //创建工作簿
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
            //创建工作表
            HSSFSheet hssfSheet = hssfWorkbook.createSheet();
            //创建行
            HSSFRow row = hssfSheet.createRow(0);
            //创建单元格
            HSSFCell cell = null;
            //初始化
            int rowIndex = 0;
            int cellIndex = 0;
    
            /**
             * 单元格 样式
             */
            HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            // cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
            // cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中
    
            HSSFFont titleFont = hssfWorkbook.createFont();
            // titleFont.setFontHeight();
            titleFont.setBold(true);
    
            //创建标题行
            row = hssfSheet.createRow(rowIndex);
            rowIndex++;
            //遍历标题
            for (String h : headData.keySet()){
                //创建列
                cell = row.createCell(cellIndex);
                //索引递增
                cellIndex++;
                //逐列插入标题
                cell.setCellValue(headData.get(h));
            }
    
            Record record = null;
    
            if (list != null){
                //循环所有记录
                for (int i = 0;i<list.size();i++){
                    //获取当前行数据
                    row = hssfSheet.createRow(rowIndex);
                    record = list.get(i);
                    rowIndex++;
                    cellIndex = 0;
                    for (String h : headData.keySet()){
                        cell = row.createCell(cellIndex);
                        cellIndex++;
                        cell.setCellValue(record.get(h) == null ? "" : record.get(h).toString());
                    }
                }
            }
            try {
                FileOutputStream fileOutputStream = new FileOutputStream(file);
                hssfWorkbook.write(fileOutputStream);
                fileOutputStream.flush();
                fileOutputStream.close();
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            }
            return file;
        }
    }
    
    

    导入

     /**
         *
         * @param file
         * @param type
         * @param dd_id
         * @return
         */
        @Override
        public Map<String, Object> importInfoUseExcel(UploadFile file, int type,int dd_id) {
            long l = System.currentTimeMillis();
            Date date = new Date(l);
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
            String time = dateFormat.format(date);
            Map<String, Object> retMap = new HashMap<String, Object>();
    
            Map<String,String> columnMap = null;
            Class<? extends Model<?>> modelClass = null;
            switch (type){
                case 0 : {
                    columnMap = TableColumn.IMS_EM_BasicInfo_DD_Columns;
                    modelClass = ImsEmBasicinfoDd.class;
                    break;
                }
                case 1 : {
                    columnMap = TableColumn.IMS_EM_BasicInfo_DSSB_Columns;
                    modelClass = ImsEmBasicinfoDssb.class;
                    break;
                }
                case 2 : {
                    columnMap = TableColumn.IMS_EM_State_DD_Columns;
                    modelClass = ImsEmStateDd.class;
                    break;
                }
    
                case 3: {
                    columnMap = TableColumn.IMS_EM_Software_Columns;
                    modelClass = ImsEmSoftware.class;
                }
    
                default: break;
            }
    
            try {
                List<Model<?>> modelList = AnalysisExcelFile.analysisExcel(file,columnMap,modelClass);
                boolean flag = true;
                for (int i = 0; i < modelList.size(); i++) {
                    if (type!=0) {
                        modelList.get(i).set("DD_ID",dd_id);
                    }
    
                    if (type==3) {
                        modelList.get(i).remove("ID");
                    }
    
                    if (type==1) {
                        String serialnum = modelList.get(i).getStr("SERIALNUM");
                        modelList.get(i).remove("ID");
                        List<Record> records = Db.find("select * from ZBIMS.IMS_EM_BasicInfo_DSSB where SERIALNUM=? AND DD_ID=? AND DELETEFLAG='N'", serialnum,dd_id);
                        if (records.size()!=0) {
                            retMap.put("msg","导入失败,存在相同的"+serialnum+"设备");
                            retMap.put("code",1);
                            flag = false;
                        }
                    }else if (type==0) {
                        String ddserialnum = modelList.get(i).getStr("DDSERIALNUM");
                        List<Record> records = Db.find("select * from ZBIMS.IMS_EM_BasicInfo_DD where DDSERIALNUM=? AND DELETEFLAG='N'", ddserialnum);
                        if (records.size()!=0) {
                            retMap.put("msg","导入失败,存在相同的"+ddserialnum+"编号");
                            retMap.put("code",1);
                            flag = false;
                        }
                    }
    
                    //存入时标
                    if (type==0 || type==1) {
                        modelList.get(i).set("TIMESCALE",time);
                    }
    
                }
                if (flag) {
                    Db.batchSave(modelList, modelList.size());
                    retMap.put("msg", "导入成功");
                    retMap.put("code", 0);
                }
            } catch (Exception e) {
                e.printStackTrace();
                retMap.put("msg", "导入失败");
                retMap.put("code", 1);
            }
            return retMap;
        }
    

    工具类1

    package com.ims.common.utils;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    public class TableColumn {
    
        public static final Map<String,String> IMS_EM_BasicInfo_DD_Columns = new HashMap<String, String>(); //基本信息
    
        static{
            IMS_EM_BasicInfo_DD_Columns.put("xx","DDSERIALNUM");
            IMS_EM_BasicInfo_DD_Columns.put("xx","MISSILEMODEL");
            IMS_EM_BasicInfo_DD_Columns.put("xx种","DDTYPE");
            IMS_EM_BasicInfo_DD_Columns.put("xx类","ZDBTYPE");
            IMS_EM_BasicInfo_DD_Columns.put("xx位","UNITS");
            IMS_EM_BasicInfo_DD_Columns.put("xx总期","COMPLETIONTIME");
            IMS_EM_BasicInfo_DD_Columns.put("xx期","DELIVERYDATE");
            IMS_EM_BasicInfo_DD_Columns.put("xx规命","SPECIFIEDLIFE");
            IMS_EM_BasicInfo_DD_Columns.put("定期","PERIODICCHECKDATE");
            IMS_EM_BasicInfo_DD_Columns.put("定期","PERIODICCHECKCYCLE");
            IMS_EM_BasicInfo_DD_Columns.put("第寿","PROLONGLIFENUM");
            IMS_EM_BasicInfo_DD_Columns.put("本命","LIFEEXTENSION");
            IMS_EM_BasicInfo_DD_Columns.put("xx期","LIFEDATE");
    
        }
        
    }
    
    

    工具类2

    package com.ims.common.utils;
    
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.DecimalFormat;
    import java.text.NumberFormat;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    import com.jfinal.plugin.activerecord.Db;
    import com.jfinal.plugin.activerecord.Model;
    import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import com.jfinal.upload.UploadFile;
    
    public class AnalysisExcelFile {
    
    	/**
    	 *
    	 * @param uploadFile  上传的文件
    	 * @param columnMap  excel的列名与model的属性之间的映射关系
    	 * @param modelClass  model的类
    	 * @return
    	 */
    	public static List<Model<?>> analysisExcel(UploadFile uploadFile,Map<String,String> columnMap,Class<? extends Model<?>> modelClass){
    		List<Model<?>> models = null;  //excel表格的每一行映射为一个model
    		List<List<String>> excelData = analysisExcel(uploadFile, columnMap);
    		if(excelData !=null){
    			models = new ArrayList<Model<?>>();
    			List<String> head = excelData.get(0);
    			for(int i=1;i<excelData.size();i++){
    				try {
    					Model<?> model = modelClass.newInstance();
    					List<String> cellValueList = excelData.get(i);
    					for(int j=0;j<cellValueList.size();j++){
    						model.set(head.get(j),cellValueList.get(j));
    						ModelUtils.setAttrs(model,ModelUtils.INSERT);
    					}
    
    					models.add(model);
    
    				} catch (InstantiationException e) {
    					e.printStackTrace();
    				} catch (IllegalAccessException e) {
    					e.printStackTrace();
    				}
    			}
    
    		}
    		return models;
    	}
    
    	/**
    	 * 解析excel文件的每一个单元格的内容
    	 * @param uploadFile
    	 * @return
    	 */
    	public static List<List<String>> analysisExcel(UploadFile uploadFile,Map<String,String> columnMap){
    		Workbook wb = null;
    		Sheet sheet = null;
    		Row row = null;
    		String cellValue = null;
    		List<List<String>> list = null;
    		List<String> rowList = null;
    		wb = readExcel(uploadFile.getUploadPath()+"/"+uploadFile.getFileName());
    		if(wb != null){
    			list = new ArrayList<List<String>>();
    			sheet = wb.getSheetAt(0); //获取第一个工作簿
    			int rownum = sheet.getLastRowNum() + 1;  //从0开始
    			int colnum = sheet.getRow(0).getLastCellNum();
    			for(int i = 0;i<rownum;i++){
    				row = sheet.getRow(i);
    				if(row != null){
    					rowList = new ArrayList<String>();
    					for(int j = 0;j<colnum;j++){
    					    if (row.getCell(j)==null) {
    					        cellValue = null;
                            }else if(row.getCell(j).getCellType()==Cell.CELL_TYPE_BLANK){
    					    	cellValue=null;
    						}else {
                                cellValue = getCellFormatValue(row.getCell(j)).toString();
    							System.out.print(cellValue+",");
                                if(i==0){//如果是第一行,存入的就是事先定义好的表结构
                                    cellValue = columnMap.get(cellValue);
                                }
                            }
    						rowList.add(cellValue);
    					}
    				}
    				System.out.println();
    
    				list.add(rowList);
    			}
    		}
    		return list;
    	}
    
    	
    	//读取excel文件
    
    	/**
    	 *
    	 * @param filepath
    	 * @return
    	 */
    	public static Workbook readExcel(String filepath){
    		Workbook wb = null;
    		if(filepath == null){
    			return null;
    		}
    		String extString = filepath.substring(filepath.lastIndexOf(".")).toLowerCase();
    		InputStream iStream = null;
    		try {
    			iStream = new FileInputStream(filepath);
    			if(".xls".equals(extString)){
    				wb = new HSSFWorkbook(iStream);
    			}else if(".xlsx".equals(extString)){
    				wb = new XSSFWorkbook(iStream);
    			}else{
    				wb = null;
    			}
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		
    		return wb;
    	}
    
    	/**
    	 *
    	 * @param cell
    	 * @return
    	 */
    	public static String getStringCellValue(Cell cell) {
    		if(cell == null){
    			return "";
    		}
    		String strCell = "";
    		switch (cell.getCellType()) {
    			case HSSFCell.CELL_TYPE_STRING:
    				strCell = cell.getStringCellValue();
    				break;
    			case HSSFCell.CELL_TYPE_NUMERIC:
    				if (HSSFDateUtil.isCellDateFormatted(cell)) {  // 处理日期格式、时间格式
    					SimpleDateFormat sdf = null;
    					if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
    						sdf = new SimpleDateFormat("HH:mm");
    					} else {// 日期
    						sdf = new SimpleDateFormat("yyyy-MM-dd");
    					}
    					Date date = cell.getDateCellValue();
    					strCell = sdf.format(date);
    				} else if (cell.getCellStyle().getDataFormat() == 58||cell.getCellStyle().getDataFormat() == 14||cell.getCellStyle().getDataFormat() == 57||cell.getCellStyle().getDataFormat() == 31) {
    // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
    					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    					double value = cell.getNumericCellValue();
    					Date date = org.apache.poi.ss.usermodel.DateUtil
    							.getJavaDate(value);
    					strCell = sdf.format(date);
    				} else {
    					double value = cell.getNumericCellValue();
    					CellStyle style = cell.getCellStyle();
    					DecimalFormat format = new DecimalFormat();
    					String temp = style.getDataFormatString();
    // 单元格设置成常规
    					if (temp.equals("General")) {
    						format.applyPattern("#");
    					}
    					strCell = format.format(value);
    				}
    				break;
    			case HSSFCell.CELL_TYPE_BOOLEAN:
    				strCell = String.valueOf(cell.getBooleanCellValue());
    				break;
    			case HSSFCell.CELL_TYPE_FORMULA://新加的公式类型
    				strCell =cell.getCellFormula().toString();
    				break;
    			case HSSFCell.CELL_TYPE_BLANK:
    				strCell = "";
    				break;
    			default:
    				strCell = "";
    				break;
    		}
    		if (strCell.equals("") || strCell == null) {
    			return "";
    		}
    		if (cell == null) {
    			return "";
    		}
    		return strCell;
    	}
    	
    	//获取单元格的数据
    
    	/**
    	 *
    	 * @param cell
    	 * @return
    	 */
        public static Object getCellFormatValue(Cell cell){
            Object cellValue = null;
            if(cell != null){
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:{   //数字
    //                    cellValue = String.valueOf(cell.getNumericCellValue());
                     /*   NumberFormat nf = NumberFormat.getInstance();
                        cellValue = nf.format(cell.getNumericCellValue());
                        if (((String) cellValue).indexOf(",")>=0) {
                            cellValue = ((String) cellValue).replace(",","");
                        }*/
    
    					if (HSSFDateUtil.isCellDateFormatted(cell)) {  // 处理日期格式、时间格式
    						SimpleDateFormat sdf = null;
    						if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
    							sdf = new SimpleDateFormat("HH:mm");
    						} else {// 日期
    							sdf = new SimpleDateFormat("yyyy-MM-dd");
    						}
    						Date date = cell.getDateCellValue();
    						cellValue = sdf.format(date);
    					} else if (cell.getCellStyle().getDataFormat() == 58||cell.getCellStyle().getDataFormat() == 14||cell.getCellStyle().getDataFormat() == 57||cell.getCellStyle().getDataFormat() == 31) {
    // 处理自定义日期格式:m月d日(通过判断单元格的格式id解决,id的值是58)
    						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    						double value = cell.getNumericCellValue();
    						Date date = org.apache.poi.ss.usermodel.DateUtil
    								.getJavaDate(value);
    						cellValue = sdf.format(date);
    					} else {
    						double value = cell.getNumericCellValue();
    						CellStyle style = cell.getCellStyle();
    						DecimalFormat format = new DecimalFormat();
    						String temp = style.getDataFormatString();
    // 单元格设置成常规
    						if (temp.equals("General")) {
    							format.applyPattern("#");
    						}
    						cellValue = format.format(value);
    					}
                        break;
                    }
                    case Cell.CELL_TYPE_FORMULA:{//公式
                        if(DateUtil.isCellDateFormatted(cell)){
                            cellValue = cell.getDateCellValue();
                        }else{
                            cellValue = String.valueOf(cell.getNumericCellValue());
                        }
                        break;
                    }
                    case Cell.CELL_TYPE_STRING:{//字符串
                        cellValue = cell.getRichStringCellValue();
                        break;
                    }
    //				case Cell.CELL_TYPE_BLANK:{
    //					cellValue = "";
    //				}
                    default:
                        break;
                }
            }
            return cellValue;
        }
    	
    }
    
    
    
    
    展开全文
  • java EXCEL导入导出工具类 包含具体代码 课根据需求改造
  • 一个简单的Excel导入导出工具类 1. 接口描述 Excel导入导出工具类说明: 类名:ExcelUtil 主要功能:实现Excel导出下载及导入解析功能 导入解析Excel接口 方法名:readExcel 输入参数: 参数类型 说明...

    一个简单的Excel导入导出工具类

    1. 接口描述

    Excel导入导出工具类说明:

    类名:ExcelUtil

    主要功能:实现Excel导出下载及导入解析功能

    1. 导入解析Excel接口

    方法名:readExcel

    输入参数:

    参数类型 说明
    MultipartFile 接收到前台传递的Excel文件信息
    Integer Sheet页序号

    返回值

    返回值类型 说明
    List<Map<String, String>> list中每一个Map代表一行数据, key为表头信息
    1. 导出接口

    方法名:exportMultisheetExcel

    输入参数:

    参数类型 说明
    String 接收到前台传递的Excel文件信息
    List<Map<String, Object>> list中每一个Map代表一个sheet页 Map参数:headers(String[])导出表头信息dataList(Collection<T>)导出数据信息 fileName(String)sheet页名称 exportFields(String[])导出表头对应的字段
    HttpServletResponse Response响应

    2. 代码

    package com.smxny.util;
    ​
    import java.io.BufferedOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.net.URLEncoder;
    import java.text.DateFormat;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Collection;
    import java.util.Date;
    import java.util.Iterator;
    import java.util.LinkedHashMap;
    import java.util.List;
    import java.util.Map;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.DateUtil;
    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.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.springframework.web.multipart.MultipartFile;
    ​
    import javax.servlet.http.HttpServletResponse;
    ​
    /**
     * Excel导入导出
     * 
     * @Author:
     * @Date:
     */
    public class ExcelUtil {
    ​
        /**
         * 导出多个sheet的excel
         * 
         * @param name
         * @param mapList
         * @param response
         * @param <T>
         */
        public static <T> void exportMultisheetExcel(String name, List<Map<String, Object>> mapList,
                HttpServletResponse response) {
            BufferedOutputStream bos = null;
            try {
                // 导出Excel名称
                String fileName = name + ".xlsx";
                bos = getBufferedOutputStream(fileName, response);
                doExport(mapList, bos);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (bos != null) {
                        bos.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    ​
        /**
         * 从excel中读内容
         * 
         * @param filePath
         * @param sheetIndex
         * @return
         */
        public static List<Map<String, String>> readExcel(MultipartFile importFile, Integer sheetIndex) {
            List<Map<String, String>> dataList = new ArrayList<>();
            // 创建工作薄
            Workbook wb = createWorkBook(importFile);
            if (wb != null) {
                // 获取sheet页
                Sheet sheet = wb.getSheetAt(sheetIndex);
                // 获取物理行数
                int maxRownum = sheet.getPhysicalNumberOfRows();
                // 获取第一行
                Row firstRow = sheet.getRow(0);
                // 获取物理列数
                int maxColnum = firstRow.getPhysicalNumberOfCells();
                String columns[] = new String[maxColnum];
                for (int i = 0; i < maxRownum; i++) {
                    Map<String, String> map = null;
                    if (i > 0) {
                        // 获取数据时创建map
                        map = new LinkedHashMap<>();
                        firstRow = sheet.getRow(i);
                    }
                    if (firstRow != null) {
                        String cellData = null;
                        for (int j = 0; j < maxColnum; j++) {
                            cellData = (String) getCellFormatValue(firstRow.getCell(j));
                            // 表头
                            if (i == 0) {
                                columns[j] = cellData;
                            } else {
                                // 数据
                                map.put(columns[j], cellData);
                            }
                        }
                    } else {
                        break;
                    }
                    if (i > 0) {
                        dataList.add(map);
                    }
                }
            }
            return dataList;
        }
    ​
        private static BufferedOutputStream getBufferedOutputStream(String fileName, HttpServletResponse response)
                throws Exception {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            return new BufferedOutputStream(response.getOutputStream());
        }
    ​
        private static <T> void doExport(List<Map<String, Object>> mapList, OutputStream outputStream) throws IOException {
            int maxBuff = 100;
            // 创建工作薄
            SXSSFWorkbook wb = new SXSSFWorkbook(maxBuff);
            try {
                // 循环(多个sheet页)
                for (int i = 0; i < mapList.size(); i++) {
                    Map<String, Object> map = mapList.get(i);
                    // 获取表头
                    String[] headers = (String[]) map.get("headers");
                    // 获取数据
                    Collection<T> dataList = (Collection<T>) map.get("dataList");
                    // 获取sheet页名称
                    String fileName = (String) map.get("fileName");
                    // 获取表头对应的个字段
                    String[] exportFields = (String[]) map.get("exportFields");
                    // 生成sheet页
                    createSheet(wb, exportFields, headers, dataList, fileName, maxBuff);
                }
    ​
                if (outputStream != null) {
                    wb.write(outputStream);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            } finally {
                wb.close();
            }
    ​
        }
    ​
        private static <T> void createSheet(SXSSFWorkbook wb, String[] exportFields, String[] headers,
                Collection<T> dataList, String fileName, int maxBuff) throws NoSuchFieldException, IllegalAccessException, IOException {
            // 创建sheet页
            Sheet sh = wb.createSheet(fileName);
            // 创建表头
            Row headerRow = sh.createRow(0);
            // 获取表头长度
            int headerSize = headers.length;
            for (int cellnum = 0; cellnum < headerSize; cellnum++) {
                // 创建单元格
                Cell cell = headerRow.createCell(cellnum);
                // 设置单元格宽度
                sh.setColumnWidth(cellnum, 4000);
                // 设置表头单元格的值
                cell.setCellValue(headers[cellnum]);
            }
            int rownum = 0;
            if (dataList != null) {
                Iterator<T> iterator = dataList.iterator();
                while (iterator.hasNext()) {
                    // 获取数据值
                    T data = iterator.next();
                    // 创建行数, 从第二行开始
                    Row row = sh.createRow(rownum + 1);
                    // 获取导出个字段
                    Field[] fields = getExportFields(data.getClass(), exportFields);
                    for (int cellnum = 0; cellnum < headerSize; cellnum++) {
                        // 创建单元格
                        Cell cell = row.createCell(cellnum);
                        // 获取单元格字段
                        Field field = fields[cellnum];
                        // 设置字段设置单元个值
                        setData(field, data, field.getName(), cell);
                    }
                    rownum = sh.getLastRowNum();
                    rownum = sh.getLastRowNum();
                    // 大数据量时将之前的数据保存到硬盘
                    if (rownum % maxBuff == 0) {
                        ((SXSSFSheet) sh).flushRows(maxBuff); // 超过100行后将之前的数据刷新到硬盘
                    }
                }
            }
        }
    ​
        private static Field[] getExportFields(Class<?> targetClass, String[] exportFieldNames) {
            Field[] fields = null;
            if (exportFieldNames == null || exportFieldNames.length < 1) {
                fields = targetClass.getDeclaredFields();
            } else {
                fields = new Field[exportFieldNames.length];
                for (int i = 0; i < exportFieldNames.length; i++) {
                    try {
                        fields[i] = targetClass.getDeclaredField(exportFieldNames[i]);
                    } catch (Exception e) {
                        try {
                            fields[i] = targetClass.getSuperclass().getDeclaredField(exportFieldNames[i]);
                        } catch (Exception e1) {
                            throw new IllegalArgumentException("无法获取导出字段", e);
                        }
    ​
                    }
                }
            }
            return fields;
        }
    ​
        /**
         * 根据属性设置对应的属性值
         *
         * @param dataField 属性
         * @param object    数据对象
         * @param property  表头的属性映射
         * @param cell      单元格
         * @param <T>
         * @throws IllegalAccessException
         * @throws NoSuchFieldException
         */
        private static <T> void setData(Field dataField, T object, String property, Cell cell)
                throws IllegalAccessException, NoSuchFieldException {
            // 允许访问private属性
            dataField.setAccessible(true);
            // 获取值
            Object val = dataField.get(object);
            // 根据数值类型设置单元格值
            if (val != null) {
                if (dataField.getType().toString().endsWith("String")) {
                    cell.setCellValue((String) val);
                } else if (dataField.getType().toString().endsWith("Integer")
                        || dataField.getType().toString().endsWith("int")) {
                    cell.setCellValue((Integer) val);
                } else if (dataField.getType().toString().endsWith("Long")
                        || dataField.getType().toString().endsWith("long")) {
                    cell.setCellValue(val.toString());
                } else if (dataField.getType().toString().endsWith("Double")
                        || dataField.getType().toString().endsWith("double")) {
                    cell.setCellValue((Double) val);
                } else if (dataField.getType().toString().endsWith("Date")) {
                    DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    cell.setCellValue(format.format((Date) val));
                } else {
                    cell.setCellValue(val.toString());
                }
            }
        }
    ​
        private static Workbook createWorkBook(MultipartFile file) {
            Workbook wb = null;
            if (file == null) {
                return null;
            }
            // 获取文件名
            String fileName = file.getOriginalFilename();
            // 获取文件后缀
            String extString = fileName.substring(fileName.lastIndexOf("."));
            InputStream is = null;
            try {
                is = file.getInputStream();
                ;
                if (".xls".equals(extString)) {
                    return wb = new HSSFWorkbook(is);
                } else if (".xlsx".equals(extString)) {
                    return wb = new XSSFWorkbook(is);
                } else {
                    return wb;
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return wb;
        }
    ​
        /**
         * 将字段转为相应的格式
         * @param cell
         * @return
         */
        private static Object getCellFormatValue(Cell cell) {
            Object cellValue = null;
            if (cell != null) {
                // 判断cell类型
                switch (cell.getCellType()) {
                // 数值型
                case Cell.CELL_TYPE_NUMERIC: {
                    cellValue = new DecimalFormat("#.#####").format(cell.getNumericCellValue());
                    break;
                }
                // 公式型
                case Cell.CELL_TYPE_FORMULA: {
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = cell.getDateCellValue(); 转换为日期格式YYYY-mm-dd
                    } else {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                // 字符串类型
                case Cell.CELL_TYPE_STRING: {
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
                }
            } else {
                cellValue = "";
            }
            return cellValue;
        }
    ​
    }
    ​
    展开全文
  • Java excel导入导出工具类+注解的实现;比较适用于做公共excel导入导出;还提供一个模板样例,样例中前端请求用的是axios,后台是Java实现。有任何问题,随时提问。
  • 通过采用反射和特性,实现Excel数据注入到指定的Model实例集合中。同理实现Model实例集合输出到指定excel文件中,省去手工硬编码实现m属性字段和Excel列的对应关系。
  • 基于poi的excel导入导出封装,poi版本 <groupId>org.apache.poi <artifactId>poi <version>4.1.0 <groupId>org.apache.poi <artifactId>poi-ooxml <version>4.1.0 </dependency>
  • Excel导入导出工具类,使用注解实现,基于若依框架工具类修改Maven依赖Java 代码使用案例 第六更,一个超级好用的工具类,只需要在实体类上加注解,就可以实现将 List 集合导出ExcelExcel数据导入生成 List ,支持...

    Excel导入导出工具类,使用注解实现,基于若依框架工具类修改


    第六更,一个超级好用的工具类,只需要在实体类上加注解,就可以实现将 List 集合导出Excel,Excel数据导入生成 List ,支持日期转换,内容转换(如 0 -> 女,1 -> 男),默认值设置。用法也可参考:http://doc.ruoyi.vip/#/standard/htsc?id=导入导出

    Maven依赖

    	<dependencies>
          <dependency>
               <groupId>org.apache.commons</groupId>
               <artifactId>commons-lang3</artifactId>
               <version>3.7</version>
           </dependency>
    
           <dependency>
               <groupId>org.apache.poi</groupId>
               <artifactId>poi</artifactId>
               <version>3.17</version>
           </dependency>
    
           <dependency>
               <groupId>org.apache.poi</groupId>
               <artifactId>poi-ooxml</artifactId>
               <version>3.17</version>
           </dependency>
       </dependencies>
    

    Java 代码

    Excel注解,加在实体类的域上

    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    /**
     * 自定义导出Excel数据注解
     */
    @Retention(RetentionPolicy.RUNTIME)
    @Target(ElementType.FIELD)
    public @interface Excel {
        /**
         * 导出到Excel中的名字.
         */
        public String name();
    
        /**
         * 日期格式, 如: yyyy-MM-dd
         */
        public String dateFormat() default "";
    
        /**
         * 读取内容转表达式 (如: 0=男,1=女,2=未知)
         */
        public String readConverterExp() default "";
    
        /**
         * 导出时在excel中每个列的高度 单位为字符
         */
        public double height() default 14;
    
        /**
         * 导出时在excel中每个列的宽 单位为字符
         */
        public double width() default 16;
    
        /**
         * 文字后缀,如% 90 变成90%
         */
        public String suffix() default "";
    
        /**
         * 当值为空时,字段的默认值
         */
        public String defaultValue() default "";
    
        /**
         * 提示信息
         */
        public String prompt() default "";
    
        /**
         * 设置只能选择不能输入的列内容.
         */
        public String[] combo() default {};
    
        /**
         * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
         */
        public boolean isExport() default true;
    
        /**
         * 另一个类中的属性名称,支持多级获取,以小数点隔开
         */
        public String targetAttr() default "";
    
        /**
         * 字段类型(0:导出导入;1:仅导出;2:仅导入)
         */
        Type type() default Type.ALL;
    
        public enum Type {
            ALL(0), EXPORT(1), IMPORT(2);
            private final int value;
    
            Type(int value) {
                this.value = value;
            }
    
            public int value() {
                return this.value;
            }
        }
    }
    

    这里是工具类,注意,线程不安全,更改样式的位置我已经用TODO标记

    //注意这里的包名
    import com.xxx.annotation.Excel;
    //内部枚举,前面要带 Excel
    import com.xxx.annotation.Excel.Type;
    import org.apache.commons.lang3.StringUtils;
    import org.apache.commons.lang3.time.DateUtils;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFDataValidation;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.io.*;
    import java.lang.reflect.Field;
    import java.lang.reflect.Method;
    import java.math.BigDecimal;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * Excel相关处理
     */
    public class ExcelUtil<T> {
        private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
    
        /**
         * Excel sheet最大行数,默认65536
         */
        private static final int sheetSize = 65536;
    
        /**
         * 工作表名称
         */
        private String sheetName;
    
        /**
         * 导出类型(EXPORT:导出数据;IMPORT:导入模板)
         */
        private Type type;
    
        /**
         * 工作薄对象
         */
        private Workbook wb;
    
        /**
         * 工作表对象
         */
        private Sheet sheet;
    
        /**
         * 导入导出数据列表
         */
        private List<T> list;
    
        /**
         * 注解列表
         */
        private List<Field> fields;
    
        /**
         * 实体对象
         */
        private Class<T> clazz;
    
    
        private String[] parsePatterns = {
                "yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy-MM",
                "yyyy/MM/dd", "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm", "yyyy/MM",
                "yyyy.MM.dd", "yyyy.MM.dd HH:mm:ss", "yyyy.MM.dd HH:mm", "yyyy.MM"};
    
        public ExcelUtil(Class<T> clazz) {
            this.clazz = clazz;
        }
    
        private void init(List<T> list, String sheetName, Type type) {
            if (list == null) {
                list = new ArrayList<T>();
            }
            this.list = list;
            this.sheetName = sheetName;
            this.type = type;
            createExcelField();
            createWorkbook();
        }
    
        /**
         * 对excel表单默认第一个索引名转换成list
         *
         * @return 转换后集合
         */
        public List<T> importExcel(InputStream is) throws Exception {
            return importExcel(StringUtils.EMPTY, is);
        }
    
        /**
         * 对excel表单指定表格索引名转换成list
         *
         * @param sheetName 表格索引名
         * @return 转换后集合
         */
        public List<T> importExcel(String sheetName, InputStream is) throws Exception {
            this.type = Type.IMPORT;
            this.wb = WorkbookFactory.create(is);
            List<T> list = new ArrayList<T>();
            Sheet sheet = null;
            if (StringUtils.isNotEmpty(sheetName)) {
                // 如果指定sheet名,则取指定sheet中的内容.
                sheet = wb.getSheet(sheetName);
            } else {
                // 如果传入的sheet名不存在则默认指向第1个sheet.
                sheet = wb.getSheetAt(0);
            }
    
            if (sheet == null) {
                throw new IOException("文件sheet不存在");
            }
    
            int rows = sheet.getPhysicalNumberOfRows();
    
            if (rows > 0) {
                // 默认序号
                int serialNum = 0;
                // 有数据时才处理 得到类的所有field.
                Field[] allFields = clazz.getDeclaredFields();
                // 定义一个map用于存放列的序号和field.
                Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
                for (int col = 0; col < allFields.length; col++) {
                    Field field = allFields[col];
                    Excel attr = field.getAnnotation(Excel.class);
                    if (attr != null && (attr.type() == Type.ALL || attr.type() == type)) {
                        // 设置类的私有字段属性可访问.
                        field.setAccessible(true);
                        fieldsMap.put(++serialNum, field);
                    }
                }
                for (int i = 1; i < rows; i++) {
                    // 从第2行开始取数据,默认第一行是表头.
                    Row row = sheet.getRow(i);
                    int cellNum = serialNum;
                    T entity = null;
                    for (int column = 0; column < cellNum; column++) {
                        Object val = this.getCellValue(row, column);
    
    
                        // 如果不存在实例则新建.
                        entity = (entity == null ? clazz.newInstance() : entity);
                        // 从map中得到对应列的field.
                        Field field = fieldsMap.get(column + 1);
                        // 取得类型,并根据对象类型设置值.
                        Class<?> fieldType = field.getType();
    
                        String valStr = "";
    
                        if (fieldType != null) {
                            Excel attr = field.getAnnotation(Excel.class);
                            if (StringUtils.isNotEmpty(attr.readConverterExp())) {
                                valStr = val.toString();
                            }
                        }
    
                        if (String.class == fieldType) {
                            String s = Convert.toStr(val);
                            if (StringUtils.endsWith(s, ".0")) {
                                val = StringUtils.substringBefore(s, ".0");
                            } else {
                                val = Convert.toStr(val);
                            }
                        } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                            val = Convert.toInt(val);
                        } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                            val = Convert.toLong(val);
                        } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                            val = Convert.toDouble(val);
                        } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                            val = Convert.toFloat(val);
                        } else if (BigDecimal.class == fieldType) {
                            val = Convert.toBigDecimal(val);
                        } else if (Date.class == fieldType) {
                            if (val instanceof String) {
                                val = DateUtils.parseDate(val.toString(), parsePatterns);
                            } else if (val instanceof Double) {
                                val = DateUtil.getJavaDate((Double) val);
                            }
                        }
                        if (fieldType != null) {
                            Excel attr = field.getAnnotation(Excel.class);
                            String propertyName = field.getName();
                            if (StringUtils.isNotEmpty(attr.targetAttr())) {
                                propertyName = field.getName() + "." + attr.targetAttr();
                            } else if (StringUtils.isNotEmpty(attr.readConverterExp())) {
                                val = reverseByExp(valStr, attr.readConverterExp());
                            }
                            invokeSetter(entity, propertyName, val);
                        }
                    }
                    list.add(entity);
                }
            }
            return list;
        }
    
        /**
         * 反射设置属性值,仅匹配方法名
         */
        private void invokeSetter(Object obj, String propertyName, Object value) {
            if (obj == null) {
                return;
            }
            Method[] methods = obj.getClass().getDeclaredMethods();
            try {
                for (Method method : methods) {
                    if (method.getName().equals("set" + StringUtils.capitalize(propertyName))) {
                        //类型转换,这里是setter,只有一个参数
                        Class<?>[] c = method.getParameterTypes();
                        //不匹配需要转换
                        if (value != null && !value.getClass().equals(c[0])) {
                            if (c[0] == String.class) {
                                value = Convert.toStr(value);
                            } else if (c[0] == Integer.class) {
                                value = Convert.toInt(value);
                            } else if (c[0] == Long.class) {
                                value = Convert.toLong(value);
                            }
                        }
    
    
                        method.invoke(obj, value);
                    }
                }
            } catch (Exception e) {
                log.error("反射异常", e);
            }
    
        }
    
    
        /**
         * 对list数据源将其里面的数据导入到excel表单
         *
         * @param list      导出数据集合
         * @param sheetName 工作表的名称
         * @param path      导出的位置
         * @return 结果
         */
        public String exportExcel(List<T> list, String sheetName, String filename, String path) {
            this.init(list, sheetName, Type.EXPORT);
            return exportExcel(path, filename, 1);
        }
    
        //导出模板
        @SuppressWarnings("unchecked")
        public String exportExcelTemplate(String sheetName, String filename, String path) {
            this.init(Collections.EMPTY_LIST, sheetName, Type.EXPORT);
            return exportExcel(path, filename, 0);
        }
    
        /**
         * 对list数据源将其里面的数据导入到excel表单
         *
         * @param sheetName 工作表的名称
         * @return 结果
         */
        private String importTemplateExcel(String sheetName, String filename, String path) {
            this.init(null, sheetName, Type.IMPORT);
            return exportExcel(path, filename, 1);
        }
    
        /**
         * 对list数据源将其里面的数据导入到excel表单
         * TODO:这里可以更改样式
         * @return 返回文件名,也可以改成文件路径
         */
        private String exportExcel(String path, String filename, Integer type) {
            OutputStream out = null;
            try {
                // 取出一共有多少个sheet.
                double sheetNo = Math.ceil(list.size() / sheetSize);
                for (int index = 0; index <= sheetNo; index++) {
                    createSheet(sheetNo, index);
                    Cell cell = null; // 产生单元格
    
                    // 产生一行
                    Row row = sheet.createRow(0);
                    // 写入各个字段的列头名称
                    for (int i = 0; i < fields.size(); i++) {
                        Field field = fields.get(i);
                        Excel attr = field.getAnnotation(Excel.class);
                        // 创建列
                        cell = row.createCell(i);
                        // 设置列中写入内容为String类型
                        cell.setCellType(CellType.STRING);
                        CellStyle cellStyle = wb.createCellStyle();
                        cellStyle.setAlignment(HorizontalAlignment.CENTER);
                        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                        if (attr.name().contains("注:")) {
                            Font font = wb.createFont();
                            font.setColor(HSSFFont.COLOR_RED);
                            cellStyle.setFont(font);
                            cellStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());
                            sheet.setColumnWidth(i, 6000);
                        } else {
                            Font font = wb.createFont();
                            // 粗体显示
                            font.setBold(true);
                            // 选择需要用到的字体格式
                            cellStyle.setFont(font);
                            cellStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());
                            // 设置列宽
                            sheet.setColumnWidth(i, (int) ((attr.width() + 0.72) * 256));
                            row.setHeight((short) (attr.height() * 20));
                        }
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        cellStyle.setWrapText(true);
                        cell.setCellStyle(cellStyle);
    
                        // 写入列名
                        cell.setCellValue(attr.name());
    
                        // 如果设置了提示信息则鼠标放上去提示.
                        if (StringUtils.isNotEmpty(attr.prompt())) {
                            // 这里默认设了2-101列提示.
                            setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, i, i);
                        }
                        // 如果设置了combo属性则本列只能选择不能输入
                        if (attr.combo().length > 0) {
                            // 这里默认设了2-101列只能选择不能输入.
                            setXSSFValidation(sheet, attr.combo(), 1, 100, i, i);
                        }
                    }
                    if (1 == type) {
                        fillExcelData(index, row, cell);
                    }
                }
                filename = encodingFilename(filename);
                out = new FileOutputStream(getAbsoluteFile(filename, path));
                wb.write(out);
                return filename;
            } catch (Exception e) {
                log.error("导出Excel异常{}", e.getMessage());
                throw new RuntimeException("导出Excel失败,请联系网站管理员!");
            } finally {
                if (wb != null) {
                    try {
                        wb.close();
                    } catch (IOException e1) {
                        e1.printStackTrace();
                    }
                }
                if (out != null) {
                    try {
                        out.close();
                    } catch (IOException e1) {
                        e1.printStackTrace();
                    }
                }
            }
        }
    
        /**
         * 填充excel数据
         *
         * @param index 序号
         * @param row   单元格行
         * @param cell  类型单元格
         */
        private void fillExcelData(int index, Row row, Cell cell) {
            int startNo = index * sheetSize;
            int endNo = Math.min(startNo + sheetSize, list.size());
            // 写入各条记录,每条记录对应excel表中的一行
            CellStyle cs = wb.createCellStyle();
            cs.setAlignment(HorizontalAlignment.CENTER);
            cs.setVerticalAlignment(VerticalAlignment.CENTER);
            for (int i = startNo; i < endNo; i++) {
                row = sheet.createRow(i + 1 - startNo);
                // 得到导出对象.
                T vo = (T) list.get(i);
                for (int j = 0; j < fields.size(); j++) {
                    // 获得field.
                    Field field = fields.get(j);
                    // 设置实体类私有属性可访问
                    field.setAccessible(true);
                    Excel attr = field.getAnnotation(Excel.class);
                    try {
                        // 设置行高
                        row.setHeight((short) (attr.height() * 20));
                        // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
                        if (attr.isExport()) {
                            // 创建cell
                            cell = row.createCell(j);
                            cell.setCellStyle(cs);
                            if (vo == null) {
                                // 如果数据存在就填入,不存在填入空格.
                                cell.setCellValue("");
                                continue;
                            }
    
                            // 用于读取对象中的属性
                            Object value = getTargetValue(vo, field, attr);
                            String dateFormat = attr.dateFormat();
                            String readConverterExp = attr.readConverterExp();
                            if (StringUtils.isNotEmpty(dateFormat) && value != null) {
                                cell.setCellValue(new SimpleDateFormat(dateFormat).format((Date) value));
                            } else if (StringUtils.isNotEmpty(readConverterExp) && value != null) {
                                cell.setCellValue(convertByExp(String.valueOf(value), readConverterExp));
                            } else {
                                cell.setCellType(CellType.STRING);
                                // 如果数据存在就填入,不存在填入空格.
                                cell.setCellValue(value == null ? attr.defaultValue() : value + attr.suffix());
                            }
                        }
                    } catch (Exception e) {
                        log.error("导出Excel失败{}", e);
                    }
                }
            }
        }
    
        /**
         * 设置 POI XSSFSheet 单元格提示
         *
         * @param sheet         表单
         * @param promptTitle   提示标题
         * @param promptContent 提示内容
         * @param firstRow      开始行
         * @param endRow        结束行
         * @param firstCol      开始列
         * @param endCol        结束列
         */
        private void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
                                   int firstCol, int endCol) {
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            DataValidation dataValidation = helper.createValidation(constraint, regions);
            dataValidation.createPromptBox(promptTitle, promptContent);
            dataValidation.setShowPromptBox(true);
            sheet.addValidationData(dataValidation);
        }
    
        /**
         * 设置某些列的值只能输入预制的数据,显示下拉框.
         *
         * @param sheet    要设置的sheet.
         * @param textlist 下拉框显示的内容
         * @param firstRow 开始行
         * @param endRow   结束行
         * @param firstCol 开始列
         * @param endCol   结束列
         * @return 设置好的sheet.
         */
        private void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
            DataValidationHelper helper = sheet.getDataValidationHelper();
            // 加载下拉列表内容
            DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
            // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            // 数据有效性对象
            DataValidation dataValidation = helper.createValidation(constraint, regions);
            // 处理Excel兼容性问题
            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
    
            sheet.addValidationData(dataValidation);
        }
    
        /**
         * 解析导出值 0=男,1=女,2=未知
         *
         * @param propertyValue 参数值
         * @param converterExp  翻译注解
         * @return 解析后值
         * @throws Exception
         */
        private static String convertByExp(String propertyValue, String converterExp) throws Exception {
            try {
                String[] convertSource = converterExp.split(",");
                for (String item : convertSource) {
                    String[] itemArray = item.split("=");
                    if (itemArray[0].equals(propertyValue)) {
                        return itemArray[1];
                    }
                }
            } catch (Exception e) {
                throw e;
            }
            return propertyValue;
        }
    
        /**
         * 反向解析值 男=0,女=1,未知=2
         *
         * @param propertyValue 参数值
         * @param converterExp  翻译注解
         * @return 解析后值
         * @throws Exception
         */
        private static String reverseByExp(String propertyValue, String converterExp) throws Exception {
            try {
                String[] convertSource = converterExp.split(",");
                for (String item : convertSource) {
                    String[] itemArray = item.split("=");
                    if (itemArray[1].equals(propertyValue)) {
                        return itemArray[0];
                    }
                }
            } catch (Exception e) {
                throw e;
            }
            return propertyValue;
        }
    
        /**
         * 编码文件名
         */
        private String encodingFilename(String filename) {
            if (StringUtils.isNotEmpty(filename)) {
    
                filename = filename + ".xlsx";
            } else {
    
                filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";
            }
            return filename;
        }
    
        /**
         * 获取下载路径
         *
         * @param filename 文件名称
         */
        private String getAbsoluteFile(String filename, String path) {
            String downloadPath = path + filename;
            File desc = new File(downloadPath);
            if (!desc.getParentFile().exists()) {
                desc.getParentFile().mkdirs();
            }
            return downloadPath;
        }
    
        /**
         * 获取bean中的属性值
         *
         * @param vo    实体对象
         * @param field 字段
         * @param excel 注解
         * @return 最终的属性值
         * @throws Exception
         */
        private Object getTargetValue(T vo, Field field, Excel excel) throws Exception {
            Object o = field.get(vo);
            if (StringUtils.isNotEmpty(excel.targetAttr())) {
                String target = excel.targetAttr();
                if (target.contains(".")) {
                    String[] targets = target.split("[.]");
                    for (String name : targets) {
                        o = getValue(o, name);
                    }
                } else {
                    o = getValue(o, target);
                }
            }
            return o;
        }
    
        /**
         * 以类的属性的get方法方法形式获取值
         */
        private Object getValue(Object o, String name) throws Exception {
            if (StringUtils.isNotEmpty(name)) {
                Class<?> clazz = o.getClass();
                String methodName = "get" + name.substring(0, 1).toUpperCase() + name.substring(1);
                Method method = clazz.getMethod(methodName);
                o = method.invoke(o);
            }
            return o;
        }
    
        /**
         * 得到所有定义字段
         */
        private void createExcelField() {
            this.fields = new ArrayList<Field>();
            List<Field> tempFields = new ArrayList<>();
            Class<?> tempClass = clazz;
            tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
            while (tempClass != null) {
                tempClass = tempClass.getSuperclass();
                if (tempClass != null) {
                    tempFields.addAll(Arrays.asList(tempClass.getDeclaredFields()));
                }
            }
            putToFields(tempFields);
        }
    
        /**
         * 放到字段集合中
         */
        private void putToFields(List<Field> fields) {
            for (Field field : fields) {
                Excel attr = field.getAnnotation(Excel.class);
                if (attr != null && (attr.type() == Type.ALL || attr.type() == type)) {
                    this.fields.add(field);
                }
            }
        }
    
        /**
         * 创建一个工作簿
         */
        private void createWorkbook() {
            this.wb = new SXSSFWorkbook(500);
        }
    
        /**
         * 创建工作表
         *
         * @param sheetNo sheet数量
         * @param index   序号
         */
        private void createSheet(double sheetNo, int index) {
            this.sheet = wb.createSheet();
            // 设置工作表的名称.
            if (sheetNo == 0) {
                wb.setSheetName(index, sheetName);
            } else {
                wb.setSheetName(index, sheetName + index);
            }
        }
    
        /**
         * 获取单元格值
         *
         * @param row    获取的行
         * @param column 获取单元格列号
         * @return 单元格值
         */
        private Object getCellValue(Row row, int column) {
            if (row == null) {
                return row;
            }
            Object val = "";
            try {
                Cell cell = row.getCell(column);
                if (cell != null) {
                    if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                        val = cell.getNumericCellValue();
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                        } else {
                            if ((Double) val % 1 > 0) {
                                val = new DecimalFormat("0.00").format(val);
                            } else {
                                val = new DecimalFormat("0").format(val);
                            }
                        }
                    } else if (cell.getCellTypeEnum() == CellType.STRING) {
                        val = cell.getStringCellValue();
                    } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                        val = cell.getBooleanCellValue();
                    } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                        val = cell.getErrorCellValue();
                    }
    
                }
            } catch (Exception e) {
                return val;
            }
            return val;
        }
    
    
        /**
         * 内部类,类型转换器
         */
        private static class Convert {
    
            static String toStr(Object value) {
                if (null == value) {
                    return null;
                }
                if (value instanceof String) {
                    return (String) value;
                }
                return value.toString();
            }
    
            static Integer toInt(Object value) {
                if (value == null) {
                    return null;
                }
                if (value instanceof Integer) {
                    return (Integer) value;
                }
                if (value instanceof Number) {
                    return ((Number) value).intValue();
                }
                final String valueStr = toStr(value);
                if (StringUtils.isEmpty(valueStr)) {
                    return null;
                }
                try {
                    return Integer.parseInt(valueStr.trim());
                } catch (Exception e) {
                    return null;
                }
            }
    
            static Long toLong(Object value) {
                if (value == null) {
                    return null;
                }
                if (value instanceof Long) {
                    return (Long) value;
                }
                if (value instanceof Number) {
                    return ((Number) value).longValue();
                }
                final String valueStr = toStr(value);
                if (StringUtils.isEmpty(valueStr)) {
                    return null;
                }
                try {
                    // 支持科学计数法
                    return new BigDecimal(valueStr.trim()).longValue();
                } catch (Exception e) {
                    return null;
                }
            }
    
            static Double toDouble(Object value) {
                if (value == null) {
                    return null;
                }
                if (value instanceof Double) {
                    return (Double) value;
                }
                if (value instanceof Number) {
                    return ((Number) value).doubleValue();
                }
                final String valueStr = toStr(value);
                if (StringUtils.isEmpty(valueStr)) {
                    return null;
                }
                try {
                    // 支持科学计数法
                    return new BigDecimal(valueStr.trim()).doubleValue();
                } catch (Exception e) {
                    return null;
                }
            }
    
            static Float toFloat(Object value) {
                if (value == null) {
                    return null;
                }
                if (value instanceof Float) {
                    return (Float) value;
                }
                if (value instanceof Number) {
                    return ((Number) value).floatValue();
                }
                final String valueStr = toStr(value);
                if (StringUtils.isEmpty(valueStr)) {
                    return null;
                }
                try {
                    // 支持科学计数法
                    return new BigDecimal(valueStr.trim()).floatValue();
                } catch (Exception e) {
                    return null;
                }
            }
    
            static BigDecimal toBigDecimal(Object value) {
                if (value == null) {
                    return null;
                }
                if (value instanceof BigDecimal) {
                    return (BigDecimal) value;
                }
                if (value instanceof Long) {
                    return new BigDecimal((Long) value);
                }
                if (value instanceof Double) {
                    return new BigDecimal((Double) value);
                }
                if (value instanceof Integer) {
                    return new BigDecimal((Integer) value);
                }
                final String valueStr = toStr(value);
                if (StringUtils.isEmpty(valueStr)) {
                    return null;
                }
                try {
                    return new BigDecimal(valueStr);
                } catch (Exception e) {
                    return null;
                }
            }
    
        }
    }
    

    使用案例

    实体类

    import java.io.Serializable;
    import java.util.Date;
    
    public class User implements Serializable {
       @Excel(name = "序号")
       private Integer id;
    
       @Excel(name = "姓名")
       private String name;
    
       @Excel(name = "生日",dateFormat = "yyyy-MM-dd")
       private Date birthday;
    
       @Excel(name = "性别",readConverterExp = "0=女,1=男")
       private Integer gender;
       
       public User() {
       }
    
       public User(Integer id, String name, Date birthday, Integer gender) {
           this.id = id;
           this.name = name;
           this.birthday = birthday;
           this.gender = gender;
       }
       
    //getter and setter ...
    
    }
    

    测试方法

       @Test
        public void test1() {
            ExcelUtil<User> excelUtil = new ExcelUtil<>(User.class);
            List<User> list = new ArrayList<>();
            User u1 = new User(1,"小王",new Date(),0);
            User u2 = new User(2,"小李",new Date(),1);
            list.add(u1);
            list.add(u2);
            excelUtil.exportExcel(list, "用户", "用户表", "D:\\");
        }
    
        @Test
        public void test2()throws Exception {
            ExcelUtil<User> excelUtil = new ExcelUtil<>(User.class);
            InputStream is = new FileInputStream("D:\\用户表.xlsx");
            List<User> list = excelUtil.importExcel(is);
            System.out.println(list);
        }
    
    展开全文

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 15,159
精华内容 6,063
关键字:

excel导入导出工具类