java项目中Excel文件的导入导出

1 package poi.excel;  2   3 import java.io.IOException;  4 import java.io.InputStream;  5 import java.io.OutputStream;  6 import java.lang.reflect.Field;  7 import java.lang.reflect.Method;  8 import java.util.ArrayList;  9 import java.util.List; 10  11 import javax.servlet.http.HttpServletRequest; 12  13 import org.apache.log4j.Logger; 14 import org.apache.poi.hssf.usermodel.HSSFCell; 15 import org.apache.poi.hssf.usermodel.HSSFCellStyle; 16 import org.apache.poi.hssf.usermodel.HSSFFont; 17 import org.apache.poi.hssf.usermodel.HSSFRichTextString; 18 import org.apache.poi.hssf.usermodel.HSSFRow; 19 import org.apache.poi.hssf.usermodel.HSSFSheet; 20 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 21 import org.apache.poi.hssf.util.HSSFColor; 22 import org.apache.poi.ss.usermodel.Cell; 23 import org.apache.poi.ss.usermodel.Row; 24 import org.apache.poi.ss.usermodel.Sheet; 25 import org.apache.poi.ss.usermodel.Workbook; 26 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 27  28  29  30 /**  31 * @ClassName: ExcelUtil  32 * @Description: Excel导入导出工具类 33 * @author 周宣 34 * @date 2016-11-8 下午7:16:11  35 *   36 */ 37 public class ExcelUtil { 38     private static final Logger logger = Logger.getLogger(ExcelUtil.class); 39      40     /**  41     * @Title: createWorkbook  42     * @Description: 判断excel文件后缀名，生成不同的workbook  43     * @param @param is 44     * @param @param excelFileName 45     * @param @return 46     * @param @throws IOException 47     * @return Workbook 48     * @throws  49     */ 50     public Workbook createWorkbook(InputStream is,String excelFileName) throws IOException{ 51         if (excelFileName.endsWith(".xls")) { 52             return new HSSFWorkbook(is); 53         }else if (excelFileName.endsWith(".xlsx")) { 54             return new XSSFWorkbook(is); 55         } 56         return null; 57     } 58  59     /**  60     * @Title: getSheet  61     * @Description: 根据sheet索引号获取对应的sheet 62     * @param @param workbook 63     * @param @param sheetIndex 64     * @param @return 65     * @return Sheet 66     * @throws  67     */ 68     public Sheet getSheet(Workbook workbook,int sheetIndex){ 69         return workbook.getSheetAt(0);         70     } 71      72     /**  73     * @Title: importDataFromExcel  74     * @Description: 将sheet中的数据保存到list中， 75     * 1、调用此方法时，vo的属性个数必须和excel文件每行数据的列数相同且一一对应，vo的所有属性都为String 76     * 2、在action调用此方法时，需声明  77     *     private File excelFile;上传的文件 78     *     private String excelFileName;原始文件的文件名 79     * 3、页面的file控件name需对应File的文件名 80     * @param @param vo javaBean 81     * @param @param is 输入流 82     * @param @param excelFileName 83     * @param @return 84     * @return List<Object> 85     * @throws  86     */ 87     public List<Object> importDataFromExcel(Object vo,InputStream is,String excelFileName){ 88         List<Object> list = new ArrayList<Object>(); 89         try { 90             //创建工作簿 91             Workbook workbook = this.createWorkbook(is, excelFileName); 92             //创建工作表sheet 93             Sheet sheet = this.getSheet(workbook, 0); 94             //获取sheet中数据的行数 95             int rows = sheet.getPhysicalNumberOfRows(); 96             //获取表头单元格个数 97             int cells = sheet.getRow(0).getPhysicalNumberOfCells(); 98             //利用反射，给JavaBean的属性进行赋值 99             Field[] fields = vo.getClass().getDeclaredFields();100             for (int i = 1; i < rows; i++) {//第一行为标题栏，从第二行开始取数据101                 Row row = sheet.getRow(i);102                 int index = 0;103                 while (index < cells) {104                     Cell cell = row.getCell(index);105                     if (null == cell) {106                         cell = row.createCell(index);107                     }108                     cell.setCellType(Cell.CELL_TYPE_STRING);109                     String value = null == cell.getStringCellValue()?"":cell.getStringCellValue();110                     111                     Field field = fields[index];112                     String fieldName = field.getName();113                     String methodName = "set"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);114                     Method setMethod = vo.getClass().getMethod(methodName, new Class[]{String.class});115                     setMethod.invoke(vo, new Object[]{value});116                     index++;117                 }118                 if (isHasValues(vo)) {//判断对象属性是否有值119                     list.add(vo);120                     vo.getClass().getConstructor(new Class[]{}).newInstance(new Object[]{});//重新创建一个vo对象121                 }122                 123             }124         } catch (Exception e) {125             logger.error(e);126         }finally{127             try {128                 is.close();//关闭流129             } catch (Exception e2) {130                 logger.error(e2);131             }132         }133         return list;134         135     }136     137     /** 138     * @Title: isHasValues 139     * @Description: 判断一个对象所有属性是否有值，如果一个属性有值(分空)，则返回true140     * @param @param object141     * @param @return142     * @return boolean143     * @throws 144     */145     public boolean isHasValues(Object object){146         Field[] fields = object.getClass().getDeclaredFields();147         boolean flag = false;148         for (int i = 0; i < fields.length; i++) {149             String fieldName = fields[i].getName();150             String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);151             Method getMethod;152             try {153                 getMethod = object.getClass().getMethod(methodName);154                 Object obj = getMethod.invoke(object);155                 if (null != obj && "".equals(obj)) {156                     flag = true;157                     break;158                 }159             } catch (Exception e) {160                 logger.error(e);161             }162             163         }164         return flag;165         166     }167     168     public <T> void exportDataToExcel(List<T> list,String[] headers,String title,OutputStream os){169         HSSFWorkbook workbook = new HSSFWorkbook();170         //生成一个表格171         HSSFSheet sheet = workbook.createSheet(title);172         //设置表格默认列宽15个字节173         sheet.setDefaultColumnWidth(15);174         //生成一个样式175         HSSFCellStyle style = this.getCellStyle(workbook);176         //生成一个字体177         HSSFFont font = this.getFont(workbook);178         //把字体应用到当前样式179         style.setFont(font);180         181         //生成表格标题182         HSSFRow row = sheet.createRow(0);183         row.setHeight((short)300);184         HSSFCell cell = null;185         186         for (int i = 0; i < headers.length; i++) {        187             cell = row.createCell(i);188             cell.setCellStyle(style);189             HSSFRichTextString text = new HSSFRichTextString(headers[i]);190             cell.setCellValue(text);191         }192         193         //将数据放入sheet中194         for (int i = 0; i < list.size(); i++) {195             row = sheet.createRow(i+1);196             T t = list.get(i);197             //利用反射，根据JavaBean属性的先后顺序，动态调用get方法得到属性的值198             Field[] fields = t.getClass().getFields();199             try {200                 for (int j = 0; j < fields.length; j++) {201                     cell = row.createCell(j);202                     Field field = fields[j];203                     String fieldName = field.getName();204                     String methodName = "get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);205                     Method getMethod = t.getClass().getMethod(methodName,new Class[]{});206                     Object value = getMethod.invoke(t, new Object[]{});207                     208                     if(null == value)209                         value ="";210                     cell.setCellValue(value.toString());211                     212                 }213             } catch (Exception e) {214                 logger.error(e);215             }216         }217         218         try {219             workbook.write(os);220         } catch (Exception e) {221             logger.error(e);222         }finally{223             try {224                 os.flush();225                 os.close();226             } catch (IOException e) {227                 logger.error(e);228             }229         }230         231     }232     233     /** 234     * @Title: getCellStyle 235     * @Description: 获取单元格格式236     * @param @param workbook237     * @param @return238     * @return HSSFCellStyle239     * @throws 240     */241     public HSSFCellStyle getCellStyle(HSSFWorkbook workbook){242         HSSFCellStyle style = workbook.createCellStyle();243         style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);244         style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);245         style.setBorderBottom(HSSFCellStyle.BORDER_THIN);246         style.setBorderTop(HSSFCellStyle.BORDER_THIN);247         style.setLeftBorderColor(HSSFCellStyle.BORDER_THIN);248         style.setRightBorderColor(HSSFCellStyle.BORDER_THIN);249         style.setAlignment(HSSFCellStyle.ALIGN_CENTER);250         251         return style;252     }253 254     /** 255     * @Title: getFont 256     * @Description: 生成字体样式257     * @param @param workbook258     * @param @return259     * @return HSSFFont260     * @throws 261     */262     public HSSFFont getFont(HSSFWorkbook workbook){263         HSSFFont font = workbook.createFont();264         font.setColor(HSSFColor.WHITE.index);265         font.setFontHeightInPoints((short)12);266         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);267         return font;268     }269     270     public boolean isIE(HttpServletRequest request){271         return request.getHeader("USER-AGENT").toLowerCase().indexOf("msie")>0?true:false;    272     }273 }