excel 文件导入，文件读取小例子
当知道一个excel格式内容的时候，我们需要把excel的内容传入到我们的数据库中。这个时候，我们就需要把excel一行行内容读取出来，转换为一个集合List，所以，就这样做就可以完成。这个List是个泛型，它装载对象。对象中的变量都是存在get与set方法。
首先，写一个工具类ExcelImportUtil.java：
View Code
package com.pingan.saims.scms.fundcost.util; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.math.BigDecimal; import java.text.ParseException; import java.util.ArrayList; import java.util.List; import java.util.Map; import org.apache.commons.fileupload.FileItem; import org.apache.poi.hssf.usermodel.HSSFWorkbook; 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; import com.opensymphony.oscache.util.StringUtil; import com.paic.pafa.app.biz.service.BusinessServiceException; import com.pingan.saims.scms.baseinfo.dto.CommonInfoDTO; import com.pingan.saims.scms.common.dto.ExcelDTO; import com.pingan.saims.scms.common.util.DateUtil; import com.pingan.saims.scms.eoaapproval.dto.MessageDTO; import com.pingan.saims.scms.fundplan.util.NumberUtils; import com.pingan.saims.scms.quartz.biz.service.util.JobUtils; import com.pingan.tsv2.tcms.attachinfo.util.AttachInfoUtil; /**EXCEL文件导入数据转换工具类 * * @author : EX-CHENWEIXIAN001 路人甲 * @create_date ：2013-3-6 下午03:26:04 * */ public class ExcelImportUtil { /**转化EXCEL为集合列表 * * @author : EX-CHENWEIXIAN001 路人甲 * @create_date ：2013-3-6 下午03:28:17 * @param fileItem * @param object * @param excelDTO * @param errorDataMap 错误信息 * @return * @throws BusinessServiceException * @throws IOException * @throws InstantiationException * @throws IllegalAccessException */ public static List changeExcel2List(FileItem fileItem, Object object, ExcelDTO excelDTO, Map<String, List<MessageDTO>> errorDataMap) throws BusinessServiceException,IOException,InstantiationException,IllegalAccessException { List resultList = new ArrayList(); // 转换结果 List<MessageDTO> messageDTOList = new ArrayList<MessageDTO>(); // 错误信息 String fileName = AttachInfoUtil.getFileName(fileItem.getName()); // 文件名 InputStream inputStream = fileItem.getInputStream(); // 流文件 String fileNamePostfix = fileName.substring(fileName.lastIndexOf(".") + 1); // 文件后缀 // 判断文件大小是否小于配置大小 if(fileItem.getSize() > excelDTO.getAllowSize() * 1024 * 1024) { throw new BusinessServiceException("上传文件太大["+(fileItem.getSize()/(1024*1024))+"]M，必须小于设置大小必须是["+ excelDTO.getAllowSize() +"]M!"); } Workbook wb = null; if ("xls".equals(fileNamePostfix)) wb = new HSSFWorkbook(inputStream); else if ("xlsx".equals(fileNamePostfix)) wb = new XSSFWorkbook(inputStream); if (wb == null) { throw new BusinessServiceException("打开Excel文件失败!"); } Sheet sheet = wb.getSheetAt(0); // 获取第一个工作簿 for (Row row : sheet) // 遍历所有行 { MessageDTO errorDTO = new MessageDTO(); // 开始读取位置 if ((row.getRowNum()+1) < excelDTO.getUsedCellBegin()) // 开始读取 { continue; } if (row.getLastCellNum() != excelDTO.getTitleSize()) // 读取数据列长度 { // throw new BusinessServiceException("Excel文件第["+(row.getRowNum()+1)+"]行不符合设置必须是["+ excelDTO.getTitleSize() +"]行的规则规则!"); errorDTO.setFlag(false); errorDTO.setMessage("Excel文件第["+(row.getRowNum()+1)+"]行不符合设置必须是["+ excelDTO.getTitleSize() +"]行的规则规则!"); messageDTOList.add(errorDTO); continue; } object = object.getClass().newInstance(); try { changeRow2Obj(row, object, excelDTO); resultList.add(object); } catch (BusinessServiceException e) { errorDTO.setFlag(false); errorDTO.setMessage(e.getMessage()); messageDTOList.add(errorDTO); continue; } } if (messageDTOList != null && messageDTOList.size() > 0 && errorDataMap != null) { errorDataMap.put("messageDTOList", messageDTOList); } return resultList; } /**转换行为对象 * * @author : EX-CHENWEIXIAN001 路人甲 * @create_date ：2013-3-5 上午10:22:37 * @param row excel单元行 * @param costRateDTO 结果对象 * @param roleMap 规则 */ public static void changeRow2Obj(Row row, Object boject, ExcelDTO excelDTO) throws BusinessServiceException { if (row == null) { return ; } // 遍历每一个单元格 for (Cell cell : row) { if ((cell.getColumnIndex()+1) < excelDTO.getUsedRowBegin()) { continue; } validateCell(cell, excelDTO.getDataTypeList(), boject); } } /**验证单元格的格式是否正确 * * @author : EX-CHENWEIXIAN001 路人甲 * @create_date ：2013-3-5 上午10:34:46 * @param cell * @param data_type_list * @return 返回空正确，否则异常 */ public static void validateCell(Cell cell, List<CommonInfoDTO> data_type_list, Object obj) throws BusinessServiceException { String cellText = cell.toString(); if (cellText.equals("--")) // 将cell值为--转换为0 { cellText = "0"; cell.setCellValue(cellText); } for (CommonInfoDTO commonInfoDTO : data_type_list) { String[] roleArray = commonInfoDTO.getCommonName().split("_"); // 当前列第几行规则 if ((cell.getColumnIndex()+1) == JobUtils.changeString2Int(roleArray[0])) { // 设置不允许为空，才需要验证格式类型什么的 if (roleArray[3].equals("notnull")) { // 但单元格为空 if (cell == null || StringUtil.isEmpty(cellText)) { throw new BusinessServiceException("配置中单元格["+(cell.getRowIndex()+1)+"行，"+(cell.getColumnIndex()+1)+"（"+getCellNameByNum(cell.getColumnIndex())+"）列]不允许为空！"); } // 格式 if (!validateFormat(cell, roleArray[2])) { throw new BusinessServiceException( "配置中单元格["+ (cell.getRowIndex()+1) +"行，"+ (cell.getColumnIndex()+1) +"（"+getCellNameByNum(cell.getColumnIndex())+"）列]错误，配置要求格式为["+roleArray[2]+"]，实际值为["+cellText+"]！"); } // 若是通过验证且是日期类型 if(roleArray[2].equals("date")) { try { cellText = DateUtil.formatDateToString(cell.getDateCellValue(),"yyyy-MM-dd"); }catch (Exception e) { } } } if (roleArray[3].equals("null") && cell != null) { if (cell == null || StringUtil.isEmpty(cellText)) { continue; } // 格式 if (!validateFormat(cell, roleArray[2])) { throw new BusinessServiceException( "配置中单元格["+ (cell.getRowIndex()+1) +"行，"+ (cell.getColumnIndex()+1) +"（"+getCellNameByNum(cell.getColumnIndex())+"）列]错误，配置要求格式为["+roleArray[2]+"]，实际值为["+cellText+"]！"); } // 若是通过验证且是日期类型 if(roleArray[2].equals("date")) { cellText = DateUtil.formatDateToString(cell.getDateCellValue(),"yyyy-MM-dd"); } } // 给对象变量赋值 try { setObjPropertyValue(obj, roleArray[1], cellText); } catch (Exception e) { throw new BusinessServiceException( "配置中单元格["+ (cell.getRowIndex()+1) +"行，"+ (cell.getColumnIndex()+1) +"（"+getCellNameByNum(cell.getColumnIndex())+"）列]错误，配置属性["+roleArray[1]+"],在对象中找不到！"+JobUtils.getExceptionDetail(e)); } } } } /*** * 通过反射设置对象的值 * @author : EX-CHENWEIXIAN001 路人甲 * @create_date ：2013-3-5 下午12:00:09 * @param obj * @param columnName * @param strText * @throws IllegalArgumentException * @throws IllegalAccessException * @throws InvocationTargetException * @throws SecurityException * @throws NoSuchMethodException * @throws ClassNotFoundException */ public static void setObjPropertyValue(Object obj,String columnName, Object strText) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, SecurityException, NoSuchMethodException, ClassNotFoundException { columnName = columnName.substring(0,1).toUpperCase() + columnName.substring(1, columnName.length()); // 获取方法 Method mGet = obj.getClass().getMethod("get" + columnName, new Class[] {}); // 这里是里设置属性的值 Method mSet = obj.getClass().getMethod("set" + columnName, new Class[]{ Class.forName(mGet.getReturnType().getName()) }); mSet.invoke(obj, new Object[]{ convertValue(strText,mGet.getReturnType()) }); } /**格式化设置类型 * * @author : EX-CHENWEIXIAN001 路人甲 * @create_date ：2013-3-5 上午10:49:11 * @param formatString * @return */ public static boolean validateFormat(Cell cell, String formatString) { // 数字 if(formatString.equals("number")) { try { new BigDecimal(cell.toString()); } catch(Exception e) { return false; } } else if(formatString.equals("date")) // 日期 { try { if (cell.getDateCellValue() == null) { try { DateUtil.formatDateToString(cell.toString(), "yyyy-MM-dd"); } catch (ParseException e) { try { DateUtil.formatDateToString(cell.toString(), "yyyy/MM/dd"); } catch (ParseException e2) { return false; } } } } catch (Exception ea) { try { DateUtil.formatDateToString(cell.toString(), "yyyy-MM-dd"); } catch (ParseException e) { try { DateUtil.formatDateToString(cell.toString(), "yyyy/MM/dd"); } catch (ParseException e2) { return false; } } } } // 字符串无需判断 else if(formatString.equals("string")) { } return true; } /**类型判断，若不满足可以添加 * * @author : EX-CHENWEIXIAN001 路人甲 * @create_date ：2013-3-6 下午01:24:51 * @param value * @param type * @return */ public static Object convertValue(Object value, Class type) { if (value == null || StringUtil.isEmpty(value.toString())) { return null; } String name = type.getName(); if ("java.lang.String".equals(name))// 字符串 { return value.toString(); } else if ("java.lang.Long".equals(name) || (type == Long.TYPE))// 长整形 { return Long.valueOf(value.toString()); } else if ("java.lang.Integer".equals(name) || (type == Integer.TYPE)) // 整数 { return Integer.valueOf(value.toString()); }else if ("java.math.BigDecimal".equals(name) || (type == Integer.TYPE)) // 大数字类型 { return NumberUtils.stringToBigDecimal(value.toString()); }else if ("java.util.Date".equals(name)) // 日期 { try { return DateUtil.formatDateToString(value.toString(), "yyyy-MM-dd"); } catch (ParseException e) { try { return DateUtil.formatDateToString(value.toString(), "yyyy/MM/dd"); } catch (ParseException e2) { return null; } } } return null; } /**获取excel列标识 * * @author : EX-CHENWEIXIAN001 路人甲 * @create_date ：2013-3-7 上午08:49:48 * @param number * @return */ public static String getCellNameByNum(int number) { String[] cellArray = {"A","B","C","D","E", "F","G","H","I","J", "K","L","M","N","O", "P","Q","R","S","T", "U","V","W","X","Y", "Z","AA","BB","CC","DD", "EE","FF","GG","HH","II", "JJ","KK","LL","MM","NN", "OO","PP","QQ","RR","SS", "TT","UU","VV","WW","XX", "YY","ZZ"}; if (number > cellArray.length) { return ""; } else { return cellArray[number]; } } }
调用的时候，这样调用：
View Code
ExcelDTO excelDTO = new ExcelDTO(); if ("insert".equals(importRateType)) { int allowSize = JobUtils.changeString2Int(BaseCommonCache.getInstanceNew().getName(BaseCommonConstants.COMMON_TYPE_20, BaseCommonConstants.EXCLE_ALLOW_SIZE)); // 允许大小 int titleSize = JobUtils.changeString2Int(BaseCommonCache.getInstanceNew().getName(BaseCommonConstants.COMMON_TYPE_20, BaseCommonConstants.EXCLE_TITLE_SIZE)); // 总列数 int usedCellBegin = JobUtils.changeString2Int(BaseCommonCache.getInstanceNew().getName(BaseCommonConstants.COMMON_TYPE_20, BaseCommonConstants.EXCLE_USED_CELL_BEGIN)); // 开始读列 int usedRowBegin = JobUtils.changeString2Int(BaseCommonCache.getInstanceNew().getName(BaseCommonConstants.COMMON_TYPE_20, BaseCommonConstants.EXCLE_USED_ROW_BEGIN)); // 开始读行 List<CommonInfoDTO> dataTypeList = BaseCommonCache.getInstanceNew().getList(BaseCommonConstants.COMMON_TYPE_20, BaseCommonConstants.EXCLE_DATA_TYPE); // 允许大小 excelDTO.setAllowSize(allowSize); // 允许文件大小 excelDTO.setTitleSize(titleSize); // 读取数据列长度 excelDTO.setUsedCellBegin(usedCellBegin); // 从第几行读起 excelDTO.setUsedRowBegin(usedRowBegin); // 从第几个单元格读起 excelDTO.setDataTypeList(dataTypeList); // 每个单元格的规则 return ExcelImportUtil.changeExcel2List(fileItem, new CostRateDTO(), excelDTO, errorDataMap); } else if ("update".equals(importRateType)) { int allowSize = JobUtils.changeString2Int(BaseCommonCache.getInstanceNew().getName(BaseCommonConstants.COMMON_TYPE_21, BaseCommonConstants.EXCLE_ALLOW_SIZE)); // 允许大小 int titleSize = JobUtils.changeString2Int(BaseCommonCache.getInstanceNew().getName(BaseCommonConstants.COMMON_TYPE_21, BaseCommonConstants.EXCLE_TITLE_SIZE)); // 总列数 int usedCellBegin = JobUtils.changeString2Int(BaseCommonCache.getInstanceNew().getName(BaseCommonConstants.COMMON_TYPE_21, BaseCommonConstants.EXCLE_USED_CELL_BEGIN)); // 开始读列 int usedRowBegin = JobUtils.changeString2Int(BaseCommonCache.getInstanceNew().getName(BaseCommonConstants.COMMON_TYPE_21, BaseCommonConstants.EXCLE_USED_ROW_BEGIN)); // 开始读行 List<CommonInfoDTO> dataTypeList = BaseCommonCache.getInstanceNew().getList(BaseCommonConstants.COMMON_TYPE_21, BaseCommonConstants.EXCLE_DATA_TYPE); // 允许大小 excelDTO.setAllowSize(allowSize); // 允许文件大小 excelDTO.setTitleSize(titleSize); // 读取数据列长度 excelDTO.setUsedCellBegin(usedCellBegin); // 从第几行读起 excelDTO.setUsedRowBegin(usedRowBegin); // 从第几个单元格读起 excelDTO.setDataTypeList(dataTypeList); // 每个单元格的规则 return ExcelImportUtil.changeExcel2List(fileItem, new CostTermCodeDTO(), excelDTO, errorDataMap); } else { throw new BusinessServiceException("导入类型不在控制范围之内，请联系管理员！"); }
其中：ExcelImportUtil.changeExcel2List(fileItem, new CostRateDTO(), excelDTO, errorDataMap)这个就是调用工具类转换EXCEL成为List的方法。
FileItems是apache的插件文件上传流传入的excel文件。
这里当前有两种格式的excel被导入：
一种insert：
| 序号 | 回购代码 | 日期 | 开盘利率 | 最高利率 | 最低利率 | 最新利率 | 成交量(万元) | 加权平均利率 | 前收盘利率 | 前加权平均利率 |
| 1 | R001 | 2013/2/28 | 4 | 6 | 3.4 | 4 | 43909417.9 | 4.059 | 3.96 | 3.9605 |
| 2 | R007 | 2013/2/28 | 4.2684 | 5.7 | 4 | 4.3 | 9929376 | 4.3009 | 4.26 | 4.2684 |
| 3 | R014 | 2013/2/28 | 4.8445 | 5.17 | 3.8 | 4.86 | 2583451.5 | 4.8733 | 4.84 | 4.8445 |
| 4 | R021 | 2013/2/28 | 4.62 | 4.95 | 4.5 | 4.62 | 407927 | 4.7183 | 4.6 | 4.6202 |
| 5 | R1M | 2013/2/28 | 4.57 | 4.8 | 4.5 | 4.5 | 370080 | 4.5874 | 4.9 | 4.5732 |
| 6 | R2M | 2013/2/28 | 4 | 4.8 | 4 | 4.6 | 289014 | 4.3393 | 4.55 | 4.3738 |
| 7 | R3M | 2013/2/28 | 4.07 | 4.13 | 4.07 | 4.13 | 86950 | 4.0845 | 4.05 | 4.077 |
| 8 | R4M | 2013/2/28 | 4.07 | 4.18 | 4.07 | 4.08 | 53280 | 4.1045 | 4.1 | 4.1003 |
| 9 | R6M | 2013/2/28 | 4.2 | 4.2 | 4.2 | 4.2 | 20000 | 4.2 | 4.1 | 4.1 |
一种update：
| 交易日期 | FR001(%) | FR007(%) | FR014(%) |
| 2013-02-27 | 3.9300 | 4.2200 | 4.8300 |
| 2013-02-26 | 3.6800 | 3.7900 | 4.2300 |
| 2013-02-25 | 2.3900 | 3.3100 | 3.3400 |
| 2013-02-22 | 2.2500 | 3.0800 | 3.0500 |
| 2013-02-21 | 2.1000 | 2.9983 | 3.0500 |
| 2013-02-20 | 1.9400 | 2.9600 | 3.0200 |
| 2013-02-19 | 1.9000 | 2.9600 | 2.9808 |
定义的insert规则：
allow_size allow_size excel导入配置：允许最大4M
title_size title_size excel导入配置：允许有11列
used_cell_begin used_cell_begin excel导入配置：从第2列开始读取数据
used_row_begin used_row_begin excel导入配置：从第2行开始读取数据
data_type data_type 排序号_对象字段sortNo_数字类型_可以为空
data_type data_type 排序号_对象字段termCode_字符串类型_不允许空
data_type data_type 排序号_对象字段rateDate_日期类型_不允许空
data_type data_type 排序号_对象字段openQuoRate_数字类型_可以为空
data_type data_type 排序号_对象字段topRate_数字类型_可以为空
data_type data_type 排序号_对象字段lowestRate_数字类型_可以为空
data_type data_type 排序号_对象字段newRate_数字类型_可以为空
data_type data_type 排序号_对象字段bargainOnAmount_数字类型_可以为空
data_type data_type 排序号_对象字段rate_数字类型_不允许空
data_type data_type 排序号_对象字段frontCloseQuoRate_数字类型_可以为空
data_type data_type 排序号_对象字段frontRate_数字类型_可以为空
定义的update规则：
allow_size allow_size excel导入配置：允许最大4M
title_size title_size excel导入配置：允许有4列
used_cell_begin used_cell_begin excel导入配置从第2列开始读取数据
used_row_begin used_row_begin excel导入配置从第1行开始读取数据
data_type data_type 排序号_对象字段rateDate_日期类型_不允许空
data_type data_type 排序号_对象字段termCodeR001_数字类型_不允许空
data_type data_type 排序号_对象字段termCodeR007_数字类型_不允许空
data_type data_type 排序号_对象字段termCodeR014_数字类型_不允许空
完成。
