java使用poi实现对excle的通用导入导出功能

前言

好几年前写的老代码了，最近又有excel的导入导出功能，整理了一下发出来吧。

poi maven包引入

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

util类

注意util类中的 string[][] table，均为表头与列名和属性名的对象，不能搞错了。

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelPoiUtil {

    /**
     * 导出excel2007
     * 
     * @author wxr
     * @date 2017年9月20日
     * @param object list<entity> 或者 list<map>
     * @param table
	 *   public static final String[][] CargoLocationTable = {{"表头名称"}
	 * 	,{"列名","货位编号","货位名称","最大容量（立方米）","最大承重（KG）","备注"}
	 * 	,{"列对应属性名","code","locationName","volumeMax","bearingMax","remark"}};
     * @param out
     */
    public static void createExcel(Object object,String[][] table,OutputStream out) {
    	List<Object> list = (List<Object>) object;
    	
        // 创建一个Excel文件
    	XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建一个工作表
    	XSSFSheet sheet = workbook.createSheet("表一");
        
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, table[1].length-1));
        
        
        
        XSSFFont font=workbook.createFont();//表头字体
        font.setFontHeightInPoints((short)14);
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//字体增粗
        font.setFontName("宋体");
        
        XSSFFont font1=workbook.createFont();//标题字体
        font1.setFontHeightInPoints((short)12);
        font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//字体增粗
        font1.setFontName("宋体");
        
        int rowNum = 0;
        
        // 添加表头行
        XSSFRow hssfRow = sheet.createRow(rowNum);
        rowNum++;
        
        // 设置单元格格式居中
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        cellStyle.setFont(font);
        
        XSSFCellStyle cellStyle1 = workbook.createCellStyle();
        cellStyle1.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        cellStyle1.setFont(font1);
        
        XSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        
        int[] cellWeight = new int[table[1].length];
        
        try{
        	//添加标题
        	XSSFCell headCell_1 = hssfRow.createCell(0);
        	hssfRow.setHeightInPoints(30);//设置行高
            headCell_1.setCellValue(table[0][0]);
            headCell_1.setCellStyle(cellStyle);
            
            
        	
	        // 添加表头内容
        	hssfRow = sheet.createRow(rowNum);
        	hssfRow.setHeightInPoints(25);
            rowNum++;
	        for(int n=0;n<table[1].length;n++){
	        	XSSFCell headCell = hssfRow.createCell(n);
	            headCell.setCellValue(table[1][n]);
	            headCell.setCellStyle(cellStyle1);
	            
	            if(cellWeight[n]<table[1][n].getBytes().length*256){
	            	cellWeight[n] = table[1][n].getBytes().length*256;
	            }
	        }     
	
	        // 添加数据内容
	        for (int i = 0; i < list.size(); i++) {
	        	hssfRow = sheet.createRow(rowNum);
	        	rowNum++;
	            Object obj = list.get(i);
	
		            if(obj instanceof Map){
		            	Map<String,Object> objMap = (Map<String,Object>) obj;
		                for(int m=0;m<table[2].length;m++){
		    	            String param = table[2][m];
			    	            // 创建单元格，并设置值
		    	            XSSFCell cell = hssfRow.createCell(m);
			            	if("".equals(param)){
			                    cell.setCellValue("");
			            	}else if("index".equals(param)){
			            		int index = i+1;
			            		cell.setCellValue(index);
			            	}else{
								String value = "";
								Object o = objMap.get(param);
			                    if(o instanceof Date){//将日期格式转换成字符型
				                    value = (o==null?null:format.format((Date) o));
			                    }else{
				                    value = (o==null?null:o.toString());
			                    }
			                    if(value!=null){
			                    	cell.setCellValue(value);
			                    	
			                    	if("remark".equals(param)||param.indexOf("Remark")>0){
			                    		
			                    	}else{
				        	            if(cellWeight[m]<value.getBytes().length*256){
				        	            	cellWeight[m] = value.getBytes().length*256;
				        	            }
			                    	}
			                    }
			            	}
			            	cell.setCellStyle(cellStyle2);
			            }
		            }else{
		                for(int m=0;m<table[2].length;m++){
		    	            String param = table[2][m];
			    	        // 创建单元格，并设置值
		    	            XSSFCell cell = hssfRow.createCell(m);

			            	
			            	if("".equals(param)){
			                    cell.setCellValue("");
			            	}else if("index".equals(param)){
			            		int index = i+1;
			            		cell.setCellValue(index);
			            	}else{
					            // 将属性的首字符大写，方便构造get，set方法  
					            String nameCapital = param.substring(0, 1).toUpperCase() + param.substring(1);
					            
					            Method method = obj.getClass().getMethod("get" + nameCapital);
					            
					            String value = "";
					            // 调用getter方法获取属性值  
					            Object o = method.invoke(obj);
			            		
			                    if(o instanceof Date){//将日期格式转换成字符型
				                    value = (o==null?null:format.format((Date) o));
			                    }else{
				                    value = (o==null?null:o.toString());
			                    }
			                    if(value!=null){
			                    	cell.setCellValue(value);
			                    	if("remark".equals(param)||param.indexOf("Remark")>0){
			                    	}else{
				        	            if(cellWeight[m]<value.getBytes().length*256){
				        	            	cellWeight[m] = value.getBytes().length*256;
				        	            }
			                    	}

			                    }
			            	}
			            	cell.setCellStyle(cellStyle2);
			            }
		            }
	        }
	        
	        
	        for(int i = 0; i < cellWeight.length; i++){//设置最大宽度
	        	sheet.setColumnWidth(i,cellWeight[i]);
	        }
	        
        // 保存Excel文件
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 读取excel2003
     * 
     * @author wxr
     * @date 2017年9月20日
     * @param inputStream
     * @param table
     * @return
     */
    public static List<Map<String,String>> readExcel2003(InputStream inputStream,String[][] table) {
    	List<Map<String,String>> list = new ArrayList<Map<String,String>>();
        HSSFWorkbook workbook = null;

        try {
            // 读取Excel文件
            workbook = new HSSFWorkbook(inputStream);

            HSSFSheet hssfSheet = workbook.getSheetAt(0);
            if (hssfSheet == null) {
                return null;
            }
            //验证标题
            HSSFRow tableName = hssfSheet.getRow(0);
            HSSFCell tableCell = tableName.getCell(0);
            
            if(!table[0][0].equals(tableCell.getStringCellValue())){
            	return null;
            }
            
            //验证表头
            tableName = hssfSheet.getRow(2);
            for(int i=0;i<table[1].length;i++){
            	tableCell = tableName.getCell(i);
                if(!table[1][i].equals(tableCell.getStringCellValue())){
                	return null;
                }
            }
            
            // 循环行
            for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }

                // 将单元格中的内容存入集合
                Map<String,String> map = new HashMap<String,String>();
                
                for(int i=0;i<table[2].length;i++){
                	if("".equals(table[2][i])){
                		continue;
                	}
                	HSSFCell cell = hssfRow.getCell(i);
                	if(cell==null||getValue2003(cell)==null||"".equals(getValue2003(cell))){
                		continue;
                	}else{
                		map.put(table[2][i], getValue2003(cell));
                	}
                }

                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    /**
     * 读取excel2007
     * 
     * @author wxr
     * @date 2017年9月20日
     * @param inputStream
     * @param table
     * @return
     */
    public static List<Map<String,String>> readExcel2007(InputStream inputStream,String[][] table) {
    	List<Map<String,String>> list = new ArrayList<Map<String,String>>();
    	XSSFWorkbook workbook = null;

        try {
            // 读取Excel文件
            workbook = new XSSFWorkbook(inputStream);

            XSSFSheet hssfSheet = workbook.getSheetAt(0);
            if (hssfSheet == null) {
                return null;
            }
            //验证标题
            XSSFRow tableName = hssfSheet.getRow(0);
            XSSFCell tableCell = tableName.getCell(0);
            
            if(!table[0][0].equals(tableCell.getStringCellValue())){
            	return null;
            }
            
            //验证表头
            tableName = hssfSheet.getRow(2);
            for(int i=0;i<table[1].length;i++){
            	tableCell = tableName.getCell(i);
                if(!table[1][i].equals(tableCell.getStringCellValue())){
                	return null;
                }
            }
            
            // 循环行
            for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            	XSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                // 将单元格中的内容存入集合
                Map<String,String> map = new HashMap<String,String>();
                
                for(int i=0;i<table[2].length;i++){
                	if("".equals(table[2][i])){
                		continue;
                	}
                	XSSFCell cell = hssfRow.getCell(i);
                	if(cell==null||getValue2007(cell)==null||"".equals(getValue2007(cell))){
                		continue;
                	}else{
                		map.put(table[2][i], getValue2007(cell));
                	}
                }
                
                if(map.size()>0){
                	list.add(map);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    private static String getValue2003(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
             // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
	           } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
             // 返回数值类型的值
           return String.valueOf(hssfCell.getNumericCellValue());
       } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
         }
    }
    
    private static String getValue2007(XSSFCell hssfCell) {
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
             // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
	           } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
             // 返回数值类型的值
           return String.valueOf(hssfCell.getNumericCellValue());
       } else {
            // 返回字符串类型的值
            return String.valueOf(hssfCell.getStringCellValue());
         }
    }
}

Controller 中使用

RequestAttributes attributes = RequestContextHolder.getRequestAttributes();
HttpServletResponse response = ((ServletRequestAttributes) attributes).getResponse();
String fileName="xxx.xlsx";  
response.setContentType("application/vnd.ms-excel");  
response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(fileName, "utf-8"));

ExcelPoiUtil.createExcel(list, table, response.getOutputStream())