java excel通用导入类_一个基于POI的通用excel导入导出工具类的简单实现及使用方法...

1 /**

2 * @Description3 *@authorzhaomin E-mail:min.zhao@mljr.com4 * @date 创建时间：2017年2月14日 下午2:13:305 *@version1.06 */

7 public classImportExcelUtil {8 final static String notnullerror = "请填入第{0}行的{1},{2}不能为空";9 final static String errormsg = "第{0}行的{1}数据导入错误";10

11 /**

12 * 导入Excel13 *14 *@paramclazz15 *@paramxls16 *@return

17 *@throwsException18 */

19 @SuppressWarnings("rawtypes")20 public static List importExcel(Class> clazz, InputStream xls) throwsException {21 try{22 //取得Excel

23 HSSFWorkbook wb = newHSSFWorkbook(xls);24 HSSFSheet sheet = wb.getSheetAt(0);25 Field[] fields =clazz.getDeclaredFields();26 List fieldList = new ArrayList(fields.length);27 for(Field field : fields) {28 if (field.isAnnotationPresent(ModelProp.class)) {29 ModelProp modelProp = field.getAnnotation(ModelProp.class);30 if (modelProp.colIndex() != -1) {31 fieldList.add(field);32 }33 }34 }35 EmployeeDTO employee = newEmployeeDTO();36 //行循环

37 List modelList = new ArrayList(sheet.getPhysicalNumberOfRows() * 2);38 for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {39 //数据模型

40 ImportModel model =(ImportModel) clazz.newInstance();41 int nullCount = 0;42 Exception nullError = null;43 for(Field field : fieldList) {44 ModelProp modelProp = field.getAnnotation(ModelProp.class);45 HSSFCell cell =sheet.getRow(i).getCell(modelProp.colIndex());46 try{47 if (cell == null || cell.toString().length() == 0) {48 nullCount++;49 if (!modelProp.nullable()) {50 nullError = newException(StringUtil.format(notnullerror,51 new String[] { "" + (1 +i), modelProp.name(), modelProp.name() }));52

53 }54 } else if (field.getType().equals(Date.class)) {55 if (Cell.CELL_TYPE_STRING ==cell.getCellType()) {56 BeanUtils.setProperty(model, field.getName(), newDate(parseDate(parseString(cell))));57 } else{58 BeanUtils.setProperty(model, field.getName(),59 newDate(cell.getDateCellValue().getTime()));60

61 }62 } else if (field.getType().equals(Timestamp.class)) {63 if (Cell.CELL_TYPE_STRING ==cell.getCellType()) {64 BeanUtils.setProperty(model, field.getName(),65 newTimestamp(parseDate(parseString(cell))));66 } else{67 BeanUtils.setProperty(model, field.getName(),68 newTimestamp(cell.getDateCellValue().getTime()));69 }70

71 } else if (field.getType().equals(java.sql.Date.class)) {72 if (Cell.CELL_TYPE_STRING ==cell.getCellType()) {73 BeanUtils.setProperty(model, field.getName(),74 newjava.sql.Date(parseDate(parseString(cell))));75 } else{76 BeanUtils.setProperty(model, field.getName(),77 newjava.sql.Date(cell.getDateCellValue().getTime()));78 }79 } else if (field.getType().equals(java.lang.Integer.class)) {80 if (Cell.CELL_TYPE_NUMERIC ==cell.getCellType()) {81 BeanUtils.setProperty(model, field.getName(), (int) cell.getNumericCellValue());82 } else if (Cell.CELL_TYPE_STRING ==cell.getCellType()) {83 BeanUtils.setProperty(model, field.getName(), Integer.parseInt(parseString(cell)));84 }85 } else if (field.getType().equals(java.math.BigDecimal.class)) {86 if (Cell.CELL_TYPE_NUMERIC ==cell.getCellType()) {87 BeanUtils.setProperty(model, field.getName(),88 newBigDecimal(cell.getNumericCellValue()));89 } else if (Cell.CELL_TYPE_STRING ==cell.getCellType()) {90 BeanUtils.setProperty(model, field.getName(), newBigDecimal(parseString(cell)));91 }92 } else{93 if (Cell.CELL_TYPE_NUMERIC ==cell.getCellType()) {94 BeanUtils.setProperty(model, field.getName(),95 newBigDecimal(cell.getNumericCellValue()));96 } else if (Cell.CELL_TYPE_STRING ==cell.getCellType()) {97 BeanUtils.setProperty(model, field.getName(), parseString(cell));98 }99 }100 } catch(Exception e) {101 e.printStackTrace();102 throw new Exception(StringUtil.format(errormsg, new String[] { "" + (1 +i), modelProp.name() })103 + "," +e.getMessage());104 }105 }106 if (nullCount ==fieldList.size()) {107 break;108 }109 if (nullError != null) {110 thrownullError;111 }112 modelList.add(model);113 }114 returnmodelList;115

116 } finally{117 xls.close();118 }119 }120

121 private final static int colsizeN = 630;122 private final static int colsizeM = 1000;123

124 /**

125 * 下载Excel模版126 *127 *@paramclazz128 *@parammap129 *@paramrowSize130 *@return

131 */

132 public static InputStream excelModelbyClass(Class> clazz, Mapmap, Integer rowSize) {133 try{134 if (!clazz.isAnnotationPresent(ModelTitle.class)) {135 throw new Exception("请在此类型中加上ModelTitle注解");136 }137 if (rowSize == null) {138 rowSize = 1000;139 }140 HSSFWorkbook wb = newHSSFWorkbook();141 HSSFSheet sheet =wb.createSheet();142 /**

143 * 设置标题样式144 */

145 HSSFCellStyle titleStyle =wb.createCellStyle();146 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);147 HSSFFont font =wb.createFont();148 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);149 font.setFontHeight((short) 400);150 titleStyle.setFont(font);151 HSSFCell titleCell = sheet.createRow(0).createCell(0); //创建第一行，并在该行创建单元格，设置内容，做为标题行

152 /**

153 * 获取标题154 */

155 ModelTitle modelTitle = clazz.getAnnotation(ModelTitle.class);156 titleCell.setCellValue(newHSSFRichTextString(modelTitle.name()));157 titleCell.setCellStyle(titleStyle);158

159 Field[] fields =clazz.getDeclaredFields();160 HSSFRow headRow = sheet.createRow(1);161 int colSzie = 0;162 /**

163 * 设置表头样式164 */

165 HSSFCellStyle headStyle =wb.createCellStyle();166 headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);167 HSSFFont headFont =wb.createFont();168 headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);169 headFont.setFontHeight((short) 240);170 headStyle.setFont(headFont);171 List cells = new ArrayList();172

173 for(Field field : fields) {174 if (field.isAnnotationPresent(ModelProp.class)) {175 ModelProp modelProp = field.getAnnotation(ModelProp.class);176 if (modelProp.colIndex() == -1)177 continue;178 cells.add(modelProp.colIndex());179 HSSFCell cell =headRow.createCell(modelProp.colIndex());180 cell.setCellValue(newHSSFRichTextString(modelProp.name()));181 cell.setCellStyle(headStyle);182 colSzie++;183 sheet.autoSizeColumn((short) modelProp.colIndex());184 sheet.setColumnWidth(modelProp.colIndex(), modelProp.name().length() * colsizeN +colsizeM);185

186 //设置列为下拉框格式

187 if (map != null && map.get(new Integer(modelProp.colIndex())) != null) {188 DVConstraint constraint =DVConstraint189 .createExplicitListConstraint(map.get(modelProp.colIndex()));190 CellRangeAddressList regions = new CellRangeAddressList(2, rowSize, modelProp.colIndex(),191 modelProp.colIndex());192 HSSFDataValidation dataValidation = newHSSFDataValidation(regions, constraint);193 sheet.addValidationData(dataValidation);194 }195 }196 }197 HSSFCellStyle cellStyle =wb.createCellStyle();198 HSSFDataFormat format =wb.createDataFormat();199 cellStyle.setDataFormat(format.getFormat("@"));200 for (int i = 2; i < rowSize; i++) {201 HSSFRow row =sheet.createRow(i);202 for(Integer integer : cells) {203 HSSFCell cell =row.createCell(integer);204 cell.setCellStyle(cellStyle);205 }206 }207 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSzie - 1));208 if (map != null) {209 for(Integer colIndex : map.keySet()) {210 DVConstraint constraint =DVConstraint.createExplicitListConstraint(map.get(colIndex));211 CellRangeAddressList regions = new CellRangeAddressList(2, 1000, colIndex, colIndex);212 HSSFDataValidation dataValidation = newHSSFDataValidation(regions, constraint);213 sheet.addValidationData(dataValidation);214 }215 }216

217 ByteArrayOutputStream os = newByteArrayOutputStream();218 try{219 wb.write(os);220 } catch(IOException e) {221 e.printStackTrace();222 }223

224 byte[] b =os.toByteArray();225

226 ByteArrayInputStream in = newByteArrayInputStream(b);227 returnin;228 } catch(Exception e) {229 e.printStackTrace();230 return null;231 }232 }233

234 private staticString parseString(HSSFCell cell) {235 returnString.valueOf(cell).trim();236 }237

238 private static long parseDate(String dateString) throwsParseException {239 if (dateString.indexOf("/") == 4) {240 return new SimpleDateFormat("yyyy/MM/dd").parse(dateString).getTime();241 } else if (dateString.indexOf("-") == 4) {242 return new SimpleDateFormat("yyyy-MM-dd").parse(dateString).getTime();243 } else if (dateString.indexOf("年") == 4) {244 return new SimpleDateFormat("yyyy年MM月dd").parse(dateString).getTime();245 } else if (dateString.length() == 8) {246 return new SimpleDateFormat("yyyyMMdd").parse(dateString).getTime();247 } else{248 return newDate().getTime();249 }250 }251

252 }