java实现excel下载和上传的工具类详解编程语言

java实现excel下载和上传的工具类

网上搜索了很多工具类的写法，大多都是poi的应用，

需要用到的poi的maven包

<!– 上传excel –>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <!– 上传excel –>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>

其中有一篇写的比较精炼和容易理解，但是却又几处错误无法直接使用，现将修改后的代码粘贴如下：

package com.estun.util; 
 
import java.io.IOException; 
import java.io.InputStream; 
import java.io.OutputStream; 
import java.lang.reflect.Field; 
import java.lang.reflect.Method; 
import java.util.ArrayList; 
import java.util.List; 
 
import javax.servlet.http.HttpServletRequest; 
 
import org.apache.log4j.Logger; 
import org.apache.poi.hssf.usermodel.HSSFCell; 
import org.apache.poi.hssf.usermodel.HSSFCellStyle; 
import org.apache.poi.hssf.usermodel.HSSFFont; 
import org.apache.poi.hssf.usermodel.HSSFRichTextString; 
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.hssf.util.HSSFColor; 
import org.apache.poi.ss.usermodel.Cell; 
import org.apache.poi.ss.usermodel.Row; 
import org.apache.poi.ss.usermodel.Sheet; 
import org.apache.poi.ss.usermodel.Workbook; 
import org.apache.poi.xssf.usermodel.XSSFWorkbook; 
 
/** 
 * excel的上传与下载的工具类 
 * @author gaozhen 
 * @time 2017年10月30日下午3:02:58 
 */ 
public class ExcelUtil { 
    private static final Logger logger = Logger.getLogger(ExcelUtil.class); 
     
    /**  
    * @Title: createWorkbook  
    * @Description: 判断excel文件后缀名，生成不同的workbook  
    * @param @param is 
    * @param @param excelFileName 
    * @param @return 
    * @param @throws IOException 
    * @return Workbook 
    * @throws  
    */ 
    public Workbook createWorkbook(InputStream is,String excelFileName) throws IOException{ 
        if (excelFileName.endsWith(".xls")) { 
            return new HSSFWorkbook(is); 
        }else if (excelFileName.endsWith(".xlsx")) { 
            return new XSSFWorkbook(is); 
        } 
        return null; 
    } 
 
    /**  
    * @Title: getSheet  
    * @Description: 根据sheet索引号获取对应的sheet 
    * @param @param workbook 
    * @param @param sheetIndex 
    * @param @return 
    * @return Sheet 
    * @throws  
    */ 
    public Sheet getSheet(Workbook workbook,int sheetIndex){ 
        return workbook.getSheetAt(0);         
    } 
     
    /**  
    * @Title: importDataFromExcel  
    * @Description: 将sheet中的数据保存到list中， 
    * 1、调用此方法时，vo的属性个数必须和excel文件每行数据的列数相同且一一对应，vo的所有属性都为String 
    * 2、在action调用此方法时，需声明  
    *     private File excelFile;上传的文件 
    *     private String excelFileName;原始文件的文件名 
    * 3、页面的file控件name需对应File的文件名 
    * @param @param vo javaBean 
    * @param @param is 输入流 
    * @param @param excelFileName 
    * @param @return 
    * @return List<Object> 
    * @throws  
    */ 
    public List<Object> importDataFromExcel(Object vo,InputStream is,String excelFileName){ 
        List<Object> list = new ArrayList<Object>(); 
        try { 
            //创建工作簿 
            Workbook workbook = this.createWorkbook(is, excelFileName); 
            //创建工作表sheet 
            Sheet sheet = this.getSheet(workbook, 0); 
            //获取sheet中数据的行数 
            int rows = sheet.getPhysicalNumberOfRows(); 
            //获取表头单元格个数 
            int cells = sheet.getRow(0).getPhysicalNumberOfCells(); 
            //利用反射，给JavaBean的属性进行赋值 
            Field[] fields = vo.getClass().getDeclaredFields(); 
            for (int i = 1; i < rows; i++) {//第一行为标题栏，从第二行开始取数据 
                Row row = sheet.getRow(i); 
                int index = 0; 
                while (index < cells) { 
                    Cell cell = row.getCell(index); 
                    if (null == cell) { 
                        cell = row.createCell(index); 
                    } 
                    cell.setCellType(Cell.CELL_TYPE_STRING); 
                    String value = null == cell.getStringCellValue()?"":cell.getStringCellValue(); 
                     
                    Field field = fields[index]; 
                    String fieldName = field.getName(); 
                    String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1); 
                    Method setMethod = vo.getClass().getMethod(methodName, new Class[]{String.class}); 
                    setMethod.invoke(vo, new Object[]{value}); 
                    index++; 
                } 
                if (isHasValues(vo)) {//判断对象属性是否有值 
                    list.add(vo); 
                    //vo=new Student(); 
                    //vo.getClass().newInstance(); 
                    vo=vo.getClass().getConstructor(new Class[]{}).newInstance(new Object[]{});//重新创建一个vo对象 
                } 
                 
            } 
        } catch (Exception e) { 
        	e.printStackTrace(); 
            logger.error(e); 
        }finally{ 
            try { 
                is.close();//关闭流 
            } catch (Exception e2) { 
                logger.error(e2); 
            } 
        } 
        return list; 
         
    } 
     
    /**  
    * @Title: isHasValues  
    * @Description: 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true 
    * @param @param object 
    * @param @return 
    * @return boolean 
    * @throws  
    */ 
    public boolean isHasValues(Object object){ 
        Field[] fields = object.getClass().getDeclaredFields(); 
        boolean flag = false; 
        for (int i = 0; i < fields.length; i++) { 
            String fieldName = fields[i].getName(); 
            String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1); 
            Method getMethod; 
            try { 
                getMethod = object.getClass().getMethod(methodName); 
                Object obj = getMethod.invoke(object); 
                if (null != obj && !"".equals(obj)) { 
                    flag = true; 
                    break; 
                } 
            } catch (Exception e) { 
                logger.error(e); 
            } 
             
        } 
        return flag; 
         
    } 
     
    public <T> void exportDataToExcel(List<T> list,String[] headers,String title,OutputStream os){ 
        HSSFWorkbook workbook = new HSSFWorkbook(); 
        //生成一个表格 
        HSSFSheet sheet = workbook.createSheet(title); 
        //设置表格默认列宽15个字节 
        sheet.setDefaultColumnWidth(15); 
        //生成一个样式 
        HSSFCellStyle style = this.getCellStyle(workbook); 
        //生成一个字体 
        HSSFFont font = this.getFont(workbook); 
        //把字体应用到当前样式 
        style.setFont(font); 
         
        //生成表格标题 
        HSSFRow row = sheet.createRow(0); 
        row.setHeight((short)300); 
        HSSFCell cell = null; 
         
        for (int i = 0; i < headers.length; i++) {         
            cell = row.createCell(i); 
            cell.setCellStyle(style); 
            HSSFRichTextString text = new HSSFRichTextString(headers[i]); 
            cell.setCellValue(text); 
        } 
         
        //将数据放入sheet中 
        for (int i = 0; i < list.size(); i++) { 
            row = sheet.createRow(i+1); 
            T t = list.get(i); 
            //利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值 
            Field[] fields = t.getClass().getFields(); 
            try { 
                for (int j = 0; j < fields.length; j++) { 
                    cell = row.createCell(j); 
                    Field field = fields[j]; 
                    String fieldName = field.getName(); 
                    String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1); 
                    Method getMethod = t.getClass().getMethod(methodName,new Class[]{}); 
                    Object value = getMethod.invoke(t, new Object[]{}); 
                     
                    if(null == value) 
                        value =""; 
                    cell.setCellValue(value.toString()); 
                     
                } 
            } catch (Exception e) { 
                logger.error(e); 
            } 
        } 
         
        try { 
            workbook.write(os); 
        } catch (Exception e) { 
            logger.error(e); 
        }finally{ 
            try { 
                os.flush(); 
                os.close(); 
            } catch (IOException e) { 
                logger.error(e); 
            } 
        } 
         
    } 
     
    /**  
    * @Title: getCellStyle  
    * @Description: 获取单元格格式 
    * @param @param workbook 
    * @param @return 
    * @return HSSFCellStyle 
    * @throws  
    */ 
    public HSSFCellStyle getCellStyle(HSSFWorkbook workbook){ 
        HSSFCellStyle style = workbook.createCellStyle(); 
        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); 
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); 
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); 
        style.setBorderTop(HSSFCellStyle.BORDER_THIN); 
        style.setLeftBorderColor(HSSFCellStyle.BORDER_THIN); 
        style.setRightBorderColor(HSSFCellStyle.BORDER_THIN); 
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); 
         
        return style; 
    } 
 
    /**  
    * @Title: getFont  
    * @Description: 生成字体样式 
    * @param @param workbook 
    * @param @return 
    * @return HSSFFont 
    * @throws  
    */ 
    public HSSFFont getFont(HSSFWorkbook workbook){ 
        HSSFFont font = workbook.createFont(); 
        font.setColor(HSSFColor.WHITE.index); 
        font.setFontHeightInPoints((short)12); 
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 
        return font; 
    } 
     
    public boolean isIE(HttpServletRequest request){ 
        return request.getHeader("USER-AGENT").toLowerCase().indexOf("msie")>0?true:false;     
    } 
}