Excel导入导出
由于项目中经常需要进行数据的导入和导出。所以研究下Excel的导入导出技术并作出整理。采用的是Apache POI 对Excel的支持。
Apache POI 是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。
其中 HSSF － 提供读写Microsoft Excel XLS格式档案的功能 。XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
网上搜索资料并进行了修改封装：
1、ExcelBeanUtils.java 工具类
1 /** 2 * <p>Excel导入数据时进行bean的复制</p> 3 * -为Date类型注册类型转换器 4 * @version V1.0 5 */ 6 public class ExcelBeanUtils extends org.apache.commons.beanutils.BeanUtils{ 7 8 private static final String CONTEXT_KEY_FORMAT_DATE_VALUE = "yyyy-MM-dd"; 9 private static final String CONTEXT_KEY_FORMAT_DATETIME_VALUE = "yyyy-MM-dd HH:mm:ss"; 10 private static final String CONTEXT_KEY_FORMAT_TIME_VALUE = "HH:mm:ss"; 11 12 static { 13 DateConverter dateConverter = new DateConverter(null); 14 dateConverter.setUseLocaleFormat(true); 15 dateConverter.setPatterns(new String[]{CONTEXT_KEY_FORMAT_DATE_VALUE, CONTEXT_KEY_FORMAT_DATETIME_VALUE, CONTEXT_KEY_FORMAT_TIME_VALUE}); 16 ConvertUtils.register(dateConverter, Date.class); 17 } 18 19 public static class DateConverter extends DateTimeConverter { 20 21 public DateConverter() { 22 23 } 24 25 public DateConverter(Object defaultValue) { 26 super(defaultValue); 27 } 28 29 @SuppressWarnings("rawtypes") 30 protected Class getDefaultType() { 31 return Date.class; 32 } 33 34 @SuppressWarnings("rawtypes") 35 @Override 36 protected Object convertToType(Class type, Object obj) throws Exception { 37 if (obj == null) { 38 return null; 39 } 40 String value = obj.toString().trim(); 41 if (value.length() == 0) { 42 return null; 43 } 44 return super.convertToType(type, obj); 45 } 46 } 47 48 public static void populateBean(Object bean, Map<?, ?> properties) throws IllegalAccessException, InvocationTargetException{ 49 populate(bean, properties); 50 } 51 }
2、ExcelColumn.java 表头信息和ExcelHead.java列信息
1 /** 2 * <p>Excel列信息</p> 3 * @author maxianming 2016-1-21 上午10:40:42 4 * @version V1.0 5 */ 6 public class ExcelColumn { 7 /** 8 * 列索引 9 */ 10 private Integer index; 11 /** 12 * 实际字段名称 13 */ 14 private String fieldName; 15 /** 16 * 表格中的显示名称 17 */ 18 private String fieldDispName; 19 /** 20 * 字段类型。数字类型还是日期等 21 */ 22 private Integer type; 23 24 public ExcelColumn() { 25 26 } 27 28 public ExcelColumn(int index, String fieldName, String fieldDispName) { 29 this.index = index; 30 this.fieldName = fieldName; 31 this.fieldDispName = fieldDispName; 32 } 33 34 public ExcelColumn(int index, String fieldName, String fieldDispName, int type) { 35 this.index = index; 36 this.fieldName = fieldName; 37 this.fieldDispName = fieldDispName; 38 this.type = type; 39 } 40 41 public Integer getIndex() { 42 return index; 43 } 44 45 public void setIndex(Integer index) { 46 this.index = index; 47 } 48 49 public String getFieldName() { 50 return fieldName; 51 } 52 53 public void setFieldName(String fieldName) { 54 this.fieldName = fieldName; 55 } 56 57 public String getFieldDispName() { 58 return fieldDispName; 59 } 60 61 public void setFieldDispName(String fieldDispName) { 62 this.fieldDispName = fieldDispName; 63 } 64 65 public Integer getType() { 66 return type; 67 } 68 69 public void setType(Integer type) { 70 this.type = type; 71 } 72 73 }
ExcelHead.java 表头信息
1 public class ExcelHead { 2 3 /** 4 * 列信息 5 */ 6 private List<ExcelColumn> columns; 7 8 /** 9 * 需要转换的列 10 */ 11 private Map<String, Map<?, ?>> columnsConvertMap; 12 13 /** 14 * 头部所占用的行数 15 */ 16 private int rowCount; 17 18 /** 19 * 头部所占用的列数 20 */ 21 private int columnCount; 22 23 public List<ExcelColumn> getColumns() { 24 return columns; 25 } 26 27 public int getRowCount() { 28 return rowCount; 29 } 30 31 public int getColumnCount() { 32 return columnCount; 33 } 34 35 public void setColumns(List<ExcelColumn> columns) { 36 this.columns = columns; 37 } 38 39 public void setRowCount(int rowCount) { 40 this.rowCount = rowCount; 41 } 42 43 public void setColumnCount(int columnCount) { 44 this.columnCount = columnCount; 45 } 46 47 public Map<String, Map<?, ?>> getColumnsConvertMap() { 48 return columnsConvertMap; 49 } 50 51 public void setColumnsConvertMap(Map<String, Map<?, ?>> columnsConvertMap) { 52 this.columnsConvertMap = columnsConvertMap; 53 } 54 55 @Override 56 public String toString() { 57 return "ExcelHead [columnCount=" + columnCount + ", columns=" + columns 58 + ", columnsConvertMap=" + columnsConvertMap + ", rowCount=" 59 + rowCount + "]"; 60 } 61 62 }
3、ExcelHelper.java 导入导出的工具类（借鉴网上他人代码） 采用泛型方式可直接导入相应对象类型的数据
8 package com.hikvision.finance.isms.common.excel; 9 10 import java.io.File; 11 import java.io.FileInputStream; 12 import java.io.FileNotFoundException; 13 import java.io.FileOutputStream; 14 import java.io.InputStream; 15 import java.lang.reflect.InvocationTargetException; 16 import java.math.BigDecimal; 17 import java.util.ArrayList; 18 import java.util.Date; 19 import java.util.HashMap; 20 import java.util.List; 21 import java.util.Map; 22 23 import org.apache.commons.lang3.StringUtils; 24 import org.apache.poi.openxml4j.exceptions.InvalidFormatException; 25 import org.apache.poi.ss.usermodel.Cell; 26 import org.apache.poi.ss.usermodel.DateUtil; 27 import org.apache.poi.ss.usermodel.Row; 28 import org.apache.poi.ss.usermodel.Sheet; 29 import org.apache.poi.ss.usermodel.Workbook; 30 31 import com.google.common.collect.Lists; 32 import com.hikvision.finance.core.util.BeanUtils; 33 import com.hikvision.finance.core.util.DateUtils; 34 import com.hikvision.finance.fwork.exception.ExpectedException; 35 import com.hikvision.finance.isms.common.excel.model.ExcelColumn; 36 import com.hikvision.finance.isms.common.excel.model.ExcelHead; 37 import com.hikvision.finance.isms.common.excel.utils.ExcelBeanUtils; 38 import com.hikvision.finance.isms.common.excel.utils.ExcelUtils; 39 40 /** 41 * <p>导入、导出的Excel帮助类</p> 42 * -导入数据到相关泛型对象T中 43 * @author maxianming 2016-1-21 下午12:14:35 44 * @version V1.0 45 */ 46 public class ExcelHelper<T> { 47 48 private IPoiExcelOperation excelOperation; 49 50 public IPoiExcelOperation getExcelOperation() { 51 return excelOperation; 52 } 53 54 public void setExcelOperation(IPoiExcelOperation excelOperation) { 55 this.excelOperation = excelOperation; 56 } 57 58 public ExcelHelper(IPoiExcelOperation excelOperation){ 59 this.excelOperation = excelOperation; 60 } 61 /** 62 * <p>将excel中数据导入到list中</p> 63 * 文件读取失败会抛出ExpectedException 64 * @author maxianming 2016-1-21 下午12:21:24 65 * @param head 文件头信息 66 * @param file 导入的数据源 67 * @param cls 保存当前数据的对象 68 * @return 69 */ 70 public List<T> importToObjectList(ExcelHead head, File file, Class<T> cls) { 71 List<T> contents = null; 72 FileInputStream fis; 73 List<List<?>> rows = null; // 根据excel 每行 生成list类型的数据 74 try { 75 fis = new FileInputStream(file); 76 rows = excelFileConvertToList(fis); 77 } catch (Exception ex) { 78 ex.printStackTrace(); 79 throw new ExpectedException("","读取文件失败"); 80 } 81 // 1.删除头信息 82 if(rows != null){ 83 for (int i = 0; i < head.getRowCount(); i++) { 84 rows.remove(0); 85 } 86 } 87 // 2.将表结构转换成Map 88 Map<Integer, String> excelHeadMap = convertExcelHeadToMap(head.getColumns()); 89 // 3.构建为对象 90 contents = buildDataObject(excelHeadMap, head.getColumnsConvertMap(), rows, cls); 91 return contents; 92 } 93 94 /** 95 * 将Excel文件内容转换为List对象 96 * @author maxianming 2016-1-20 下午6:15:05 97 * @param fis excel文件 98 * @return List<List> list存放形式的内容 99 * @throws Exception 100 */ 101 public List<List<?>> excelFileConvertToList(FileInputStream fis) throws Exception{ 102 Workbook wb = this.excelOperation.readExcel(fis); 103 Sheet sheet = wb.getSheetAt(0); 104 List<List<?>> rows = new ArrayList<List<?>>(); 105 if(sheet != null){ 106 for (Row row : sheet) { 107 if(!ExcelUtils.isBlankRow(row)){ 108 List<Object> cells = new ArrayList<Object>(); 109 for (Cell cell : row) { 110 Object obj = null; 111 obj = this.getValue(cell); 112 cells.add(obj); 113 } 114 rows.add(cells); 115 } 116 } 117 } 118 return rows; 119 } 120 /** 121 * <p>将Excel中的数据类型进行转换</P> 122 * @author maxianming 2016-1-21 下午6:27:54 123 * @param cell 124 * @return 125 */ 126 private Object getValue(Cell cell) { 127 Object value = null; 128 if(cell != null){ 129 switch (cell.getCellType()) { 130 case Cell.CELL_TYPE_STRING: 131 value = cell.getRichStringCellValue().getString(); 132 break; 133 case Cell.CELL_TYPE_NUMERIC: 134 if (DateUtil.isCellDateFormatted(cell)) { 135 value = cell.getDateCellValue(); 136 } else { 137 BigDecimal big = new BigDecimal(cell.getNumericCellValue()); 138 String strValue = big.toString(); 139 // 解决1234.0 去掉后面的.0 140 if(null != strValue && !"".equals(strValue.trim())){ 141 String[] item = strValue.split("[.]"); 142 if(1 < item.length && "0".equals(item[1])){ 143 strValue = item[0]; 144 } 145 }; 146 value = strValue; 147 } 148 break; 149 case Cell.CELL_TYPE_BOOLEAN: 150 value = cell.getBooleanCellValue(); 151 break; 152 case Cell.CELL_TYPE_FORMULA: 153 value = String.valueOf(cell.getNumericCellValue()); //读公式计算值 154 if (value.equals("NaN")) { // 如果获取的数据值为非法值,则转换为获取字符串 155 value = cell.getStringCellValue().toString(); 156 } 157 break; 158 default: 159 value = null; 160 } 161 } 162 return value; 163 } 164 165 /** 166 * <p>将报表结构转换成Map</p> 167 * @author maxianming 2016-1-22 下午2:43:01 168 * @param excelColumns 169 * @return 170 */ 171 private Map<Integer, String> convertExcelHeadToMap(List<ExcelColumn> excelColumns) { 172 Map<Integer, String> excelHeadMap = new HashMap<Integer, String>(); 173 for (ExcelColumn excelColumn : excelColumns) { 174 if(StringUtils.isNotEmpty(excelColumn.getFieldName())) { 175 excelHeadMap.put(excelColumn.getIndex(), excelColumn.getFieldName()); 176 } 177 } 178 return excelHeadMap; 179 } 180 181 /** 182 * <p>根据Excel生成数据对象</P> 183 * @author maxianming 2016-1-21 下午1:42:22 184 * @param excelHeadMap 表头信息 185 * @param excelHeadConvertMap 需要特殊转换的单元 186 * @param rows Excel文件中数据的List对象 187 * @param cls 转换为的对象 188 * @return 189 */ 190 private List<T> buildDataObject(Map<Integer, String> excelHeadMap, Map<String, Map<?, ?>> excelHeadConvertMap, List<List<?>> rows, Class<T> cls) { 191 List<T> contents = Lists.newArrayList(); 192 for (List<?> list : rows) { 193 // 1.如果当前第一列中无数据,则忽略当前行的数据 194 if(list == null || list.get(0) == null) { 195 break; 196 } 197 // 2.当前行的数据放入map中,生成<fieldName, value>的形式 198 Map<String, Object> rowMap = rowListToMap(excelHeadMap, excelHeadConvertMap, list); 199 // 3.将当前行转换成对应的对象 200 T obj = null; 201 try { 202 203 obj = cls.newInstance(); 204 } catch (InstantiationException ex) { 205 ex.printStackTrace(); 206 } catch (IllegalAccessException ex) { 207 ex.printStackTrace(); 208 } 209 210 try { 211 ExcelBeanUtils.populateBean(obj, rowMap); 212 } catch (IllegalAccessException e) { 213 e.printStackTrace(); 214 throw new ExpectedException("","导入文件内容有误!"); 215 } catch (InvocationTargetException e) { 216 e.printStackTrace(); 217 throw new ExpectedException("","导入文件内容有误!"); 218 } 219 220 contents.add(obj); 221 } 222 return contents; 223 } 224 /** 225 * <p>将行转行成map,生成<fieldName, value>的形式</p> 226 * @author maxianming 2016-1-21 下午1:46:57 227 * @param excelHeadMap 表头信息 228 * @param excelHeadConvertMap 需要转换的信息 229 * @param list excel中的数据 230 * @throws ExpectedException 当导入文件不是按模板定义好的格式时，抛出异常。 231 * @return 232 */ 233 private Map<String, Object> rowListToMap(Map<Integer, String> excelHeadMap, Map<String, Map<?, ?>> excelHeadConvertMap, List<?> list) { 234 Map<String, Object> rowMap = new HashMap<String, Object>(); 235 if(excelHeadMap.size() > list.size()){ 236 throw new ExpectedException("", "导入文件的内容格式有误!"); 237 } 238 for(int i = 0; i < list.size(); i++) { 239 String fieldName = excelHeadMap.get(i); 240 if(fieldName != null) { 241 // 得到一行数据中每个单元格的value 242 Object value = list.get(i); 243 if(excelHeadConvertMap != null && excelHeadConvertMap.get(fieldName) != null) { 244 value = excelHeadConvertMap.get(fieldName).get(value); 245 } 246 rowMap.put(fieldName, value); 247 } 248 } 249 return rowMap; 250 } 251 252 /*--------------------------------导出功能还未完善-----------------------------------*/ 253 254 /** 255 * 导出数据至Excel文件 256 * @author maxianming 2016-1-20 下午6:41:30 257 * @param head 报表头信息 258 * @param modelFile 导出文件 259 * @param outputFile 导出文件 260 * @param dataList 导入excel报表的数据来源 261 */ 262 public void exportExcelFile(ExcelHead head, File modelFile, File outputFile, List<?> dataList) { 263 InputStream inp = null; 264 Workbook wb = null; 265 try { 266 // 1.读取导出excel模板 267 inp = new FileInputStream(modelFile); 268 wb = this.excelOperation.readExcel(inp); 269 Sheet sheet = wb.getSheetAt(0); 270 // 2.生成导出数据 271 buildExcelData(sheet, head, dataList); 272 273 // 3.导出到文件中 274 FileOutputStream fileOut = new FileOutputStream(outputFile); 275 wb.write(fileOut); 276 fileOut.close(); 277 } catch (FileNotFoundException ex) { 278 ex.printStackTrace(); 279 } catch (InvalidFormatException ex) { 280 ex.printStackTrace(); 281 } catch (Exception ex) { 282 ex.printStackTrace(); 283 } 284 } 285 /** 286 * <p>生成导出至Excel文件的数据</p> 287 * @author maxianming 2016-1-22 下午2:45:45 288 * @param sheet 工作区间 289 * @param head excel表头 290 * @param dataList 导入excel报表的数据来源 291 */ 292 private void buildExcelData(Sheet sheet, ExcelHead head, List<?> dataList) { 293 List<ExcelColumn> excelColumns = head.getColumns(); 294 Map<String, Map<?, ?>> excelHeadConvertMap = head.getColumnsConvertMap(); 295 // 1.将表结构转换成Map 296 Map<Integer, String> excelHeadMap = convertExcelHeadToMap(excelColumns); 297 // 2.从第几行开始插入数据 298 int startRow = head.getRowCount(); 299 int order = 1; 300 for (Object obj : dataList) { 301 Row row = sheet.createRow(startRow++); 302 for (int j = 0; j < excelColumns.size(); j++) { 303 Cell cell = row.createCell(j); 304 cell.setCellType(excelColumns.get(j).getType()); 305 String fieldName = excelHeadMap.get(j); 306 if(fieldName != null) { 307 Object valueObject = null; 308 try { 309 valueObject = BeanUtils.getProperty(obj, fieldName); 310 } catch (Exception e){ 311 e.printStackTrace(); 312 } 313 /* 314 * 如果存在需要转换的字段信息，则进行转换 315 */ 316 if(excelHeadConvertMap != null && excelHeadConvertMap.get(fieldName) != null) { 317 valueObject = excelHeadConvertMap.get(fieldName).get(valueObject); 318 } 319 320 if(valueObject == null) { 321 cell.setCellValue(""); 322 } else if (valueObject instanceof Integer) { 323 cell.setCellValue((Integer)valueObject); 324 } else if (valueObject instanceof String) { 325 cell.setCellValue((String)valueObject); 326 } else if (valueObject instanceof Date) { 327 cell.setCellValue(DateUtils.getStringDateTime((Date)valueObject)); 328 } else { 329 cell.setCellValue(valueObject.toString()); 330 } 331 } else { 332 cell.setCellValue(order++); 333 } 334 } 335 } 336 } 337 338 }
4、ExcelHelper.java 工厂化方法按Excel格式产生特定的Excel导入导出工具
1 ** 2 * <p>产生一个ExcelHelper工具类</p> 3 * @author maxianming 2016-1-22 下午4:28:35 4 * @version V1.0 5 */ 6 public class ExcelHelperFactory { 7 /** 8 * <p>根据后缀名类型产生一个ExcelHelper类</p> 9 * @author maxianming 2016-1-22 下午4:30:41 10 * @param cls 11 */ 12 public static <T> ExcelHelper<T> createExcelHelper(String fileName){ 13 ExcelHelper<T> excelHelper = null; 14 if(StringUtils.isNotBlank(fileName)){ 15 String type = fileName.substring(fileName.lastIndexOf(".") + 1); 16 if("xls".equals(type)){ 17 excelHelper = new ExcelHelper<T>(new HSSFExcel()); 18 } else if("xlsx".equals(type)){ 19 excelHelper = new ExcelHelper<T>(new XSSFExcel()); 20 } else{ 21 throw new ExpectedException("","不支持Excel文件的扩展名【" + type +"】"); 22 } 23 } 24 return excelHelper; 25 } 26 }
最后实际应用例子：
// 1、Excel中每一行的信息
List<ExcelColumn> excelColumns = Lists.newArrayList();
excelColumns.add(new ExcelColumn(0, "strName", "机构名称"));
excelColumns.add(new ExcelColumn(1, "nlevel","机构级别"));
excelColumns.add(new ExcelColumn(2, "strCode", "机构号"));
excelColumns.add(new ExcelColumn(3, "strAddress","机构地址"));
excelColumns.add(new ExcelColumn(4, "parentOrg", "上级机构"));
// 2、机构级别中显示的名称-数据库中整数
Map<String, Integer> levelMap = new HashMap<String, Integer>(){
{
put("总行", 0); put("一级分行", 1); put("二级分行", 2);
put("直属支行", 3); put("支行", 4); put("办事处", 5);
put("经营支行", 6); put("分理处", 7); put("储蓄所",8);
}
};
// 3、excel中显示的信息转换为数据库中的值
Map<String, Map<?, ?>> excelColumnsConvertMap = Maps.newHashMap();
excelColumnsConvertMap.put("nlevel", levelMap);
// 4、组装excel信息
ExcelHead excelHead = new ExcelHead();
excelHead.setColumnCount(2);
excelHead.setColumns(excelColumns);
excelHead.setColumnsConvertMap(excelColumnsConvertMap);
// 5、Excel导入类使用
List<OrganizationDto> orgs = Lists.newArrayList();
ExcelHelper<OrganizationDto> excelHelper = ExcelHelperFactory.createExcelHelper(fileName);
orgs = excelHelper.importToObjectList(excelHead, orgFile, OrganizationDto.class);
