java poi 注解_POI导入导出Excel 利用自定义注解

1 importorg.apache.poi.hssf.usermodel.HSSFCell;2 importorg.apache.poi.hssf.usermodel.HSSFRow;3 importorg.apache.poi.hssf.usermodel.HSSFSheet;4 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;5 importorg.apache.poi.ss.usermodel.CellType;6 importorg.omg.CORBA.Object;7

8 importjavax.servlet.http.HttpServletRequest;9 importjavax.servlet.http.HttpServletResponse;10 import java.io.*;11 importjava.lang.reflect.Field;12 importjava.net.URL;13 importjava.text.SimpleDateFormat;14 import java.util.*;15

16 import staticorg.apache.poi.ss.usermodel.CellType.STRING;17

18 /**

19 * Created by wangjianjun on 2017/5/8.20 */

21 public class ExcelUtil{22

23 Classclazz;24

25 publicExcelUtil(){26

27 }28

29 public ExcelUtil(Classclazz) {30 this.clazz =clazz;31 }32

33 public List importExcel(String sheetName, HSSFWorkbook workbook) throwsInspectionServiceException{34

35 int maxCol = 0;36 List list = new ArrayList<>();37 try{38

39 HSSFSheet sheet =workbook.getSheet(sheetName);40

41 if (sheet == null){42 //sheet = workbook.getSheetAt(0);43 //if (!sheet.getSheetName().equals(sheetName))44 //return null;

45 throw new InspectionServiceException("未找到对应的sheetName："+sheetName);46 }47

48 int rows =sheet.getPhysicalNumberOfRows();49

50 if (rows > 0){51 List allFields = getMappedField(clazz,null);52 Map fieldMap = new HashMap<>();53 for(Field field:allFields){54 if (field.isAnnotationPresent(ExcelVOAttribute.class)){55 ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);56 int col =getExcelCol(attr.column());57 maxCol =Math.max(col,maxCol);58 fieldMap.put(col,field);59 }60 }61

62 for (int i=1;i

72 CellType cellType =cell.getCellTypeEnum();73 String value;74 switch(cellType){75 caseNUMERIC:76 cell.setCellType(STRING);77 value =String.valueOf(cell.getStringCellValue());78 break;79 caseBOOLEAN:80 value =String.valueOf(cell.getBooleanCellValue());81 break;82 default:83 value =cell.getStringCellValue();84 break;85 }86

87 if (value == null || "".equals(value))88 continue;89

90 entity = (entity == null ?clazz.newInstance():entity);91 Field field =fieldMap.get(j);92 if (field == null)93 continue;94

95 Class fieldType =field.getType();96 if (String.class ==fieldType)97 field.set(entity,String.valueOf(value));98 else if ((Integer.TYPE ==fieldType)99 || (Integer.class ==fieldType)) {100 field.set(entity, Integer.parseInt(value));101 } else if ((Long.TYPE ==fieldType)102 || (Long.class ==fieldType)) {103 field.set(entity, Long.valueOf(value));104 } else if ((Float.TYPE ==fieldType)105 || (Float.class ==fieldType)) {106 field.set(entity, Float.valueOf(value));107 } else if ((Short.TYPE ==fieldType)108 || (Short.class ==fieldType)) {109 field.set(entity, Short.valueOf(value));110 } else if ((Double.TYPE ==fieldType)111 || (Double.class ==fieldType)) {112 field.set(entity, Double.valueOf(value));113 } else if (Character.TYPE ==fieldType) {114 if ((value != null) && (value.length() > 0)) {115 field.set(entity, Character116 .valueOf(value.charAt(0)));117 }118 }else if (Boolean.TYPE ==fieldType){119 field.set(entity,Boolean.valueOf(value));120 }121 }122 if (entity != null)123 list.add(entity);124 }125 }126

127 } catch(InstantiationException e) {128 throw newInspectionServiceException(e.getMessage());129 } catch(IllegalAccessException e) {130 throw newInspectionServiceException(e.getMessage());131 }132

133 returnlist;134 }135

136 public void exportExcel(List list,HSSFWorkbook workbook,String sheetName,int sheetIx) throwsInspectionServiceException{137

138 genOneSheet(list,workbook);139 workbook.setSheetName(sheetIx,sheetName);140 }141

142 private void genOneSheet(List list,HSSFWorkbook workbook) throwsInspectionServiceException{143

144 List fields = getMappedField(clazz,null);145 HSSFSheet sheet =workbook.createSheet();146

147 HSSFRow row;148 HSSFCell cell;149 row = sheet.createRow(0);150 for (int j=0;j

159 int startNo = 0;160 int endNo =list.size();161 for (int j=startNo;j

180 /**

181 * 得到实体类所有通过注解映射了数据表的字段182 *@paramclazz183 *@paramfields184 *@return

185 */

186 private List getMappedField(Class clazz,Listfields){187

188 if (fields == null){189 fields = new ArrayList<>();190 }191

192 Field[] allFields =clazz.getDeclaredFields();193 for(Field field:allFields){194 if (field.isAnnotationPresent(ExcelVOAttribute.class)){195 fields.add(field);196 }197 }198

199 if (clazz.getSuperclass()!=null && !clazz.getSuperclass().equals(Object.class)){200 getMappedField(clazz.getSuperclass(),fields);201 }202

203 returnfields;204 }205

206 /**

207 * 将EXCEL中A,B,C,D,E列映射成0,1,2,3208 *209 *@paramcol210 */

211 public static intgetExcelCol(String col) {212 col =col.toUpperCase();213 //从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。

214 int count = -1;215 char[] cs =col.toCharArray();216 for (int i = 0; i < cs.length; i++) {217 count += (cs[i] - 64) * Math.pow(26, cs.length - 1 -i);218 }219 returncount;220 }221

222 public voiddownLoad(String filePath, HttpServletResponse response,223 boolean isOnLine) throwsException {224 File f = newFile(filePath);225 /*

226 * if (!f.exists()) { response.sendError(404, "File not found!");227 * return; }228 */

229 BufferedInputStream br = new BufferedInputStream(newFileInputStream(f));230 byte[] buf = new byte[1024];231 int len = 0;232 response.reset(); //非常重要233 //在线打开方式

234 if(isOnLine) {235 URL u = newURL(filePath);236 response.setContentType(u.openConnection().getContentType());237 response.setHeader("Content-Disposition", "inline; filename="

238 +toUTF8(f.getName()));239 //文件名应该编码成UTF-8

240 }241 //纯下载方式

242 else{243 response.setContentType("application/x-msdownload");244 response.setHeader("Content-Disposition", "attachment; filename="

245 +toUTF8(f.getName()));246 }247 OutputStream out =response.getOutputStream();248 while ((len = br.read(buf)) > 0)249 out.write(buf, 0, len);250 out.flush();251 br.close();252 out.close();253 }254

255 //UTF-8编码

256 publicString toUTF8(String s) {257 StringBuffer sb = newStringBuffer();258 for (int i = 0; i < s.length(); i++) {259 char c =s.charAt(i);260 if (c >= 0 && c <= 255) {261 sb.append(c);262 } else{263 byte[] b;264 try{265 b = Character.toString(c).getBytes("utf-8");266 } catch(Exception ex) {267 System.out.println(ex);268 b = new byte[0];269 }270 for (int j = 0; j < b.length; j++) {271 int k =b[j];272 if (k < 0)273 k += 256;274 sb.append("%" +Integer.toHexString(k).toUpperCase());275 }276 }277 }278 returnsb.toString();279 }280

281 public staticString genUploadPathName(HttpServletRequest request,String title){282 String unloadPath = request.getSession().getServletContext().getRealPath("/") + "\\download\\"

283 + "excel" + "\\";284

285 //自动生成日期

286 SimpleDateFormat autoDate = newSimpleDateFormat(287 "yyyyMMddHHmmssSSS");288 //excel名为：当前名+日期时间289 //title += autoDate.format(new Date());

290 if (title==null || title.equals(""))291 title = "inspectionExport";292 File dirFile = newFile(unloadPath);293 //如果dir对应的文件不存在，或者不是一个目录，则退出

294 if (!dirFile.exists() || !dirFile.isDirectory()) {295 dirFile.mkdirs();296 }297

298 return unloadPath+title + ".xls";299 }300 }