一个基于POI的通用excel导入导出工具类的简单实现及使用方法
一、引入poi相关依赖（及spring上传文件相关配置，不再解释）
1 <!-- apache poi start -->
2 <poi.version>3.14</poi.version>
3 <dependency>
4 <groupId>org.apache.poi</groupId>
5 <artifactId>poi</artifactId>
6 <version>${poi.version}</version>
7 </dependency>
8
9 <dependency>
10 <groupId>org.apache.poi</groupId>
11 <artifactId>poi-scratchpad</artifactId>
12 <version>${poi.version}</version>
13 </dependency>
14
15 <dependency>
16 <groupId>org.apache.poi</groupId>
17 <artifactId>poi-ooxml</artifactId>
18 <version>${poi.version}</version>
19 </dependency>
20 <!-- apache poi end -->
二、excel导入导出工具类的实现
1 /**
2 * @Description
3 * @author zhaomin E-mail:min.zhao@mljr.com
4 * @date 创建时间：2017年2月14日 下午2:13:30
5 * @version 1.0
6 */
7 public class ImportExcelUtil {
8 final static String notnullerror = "请填入第{0}行的{1},{2}不能为空";
9 final static String errormsg = "第{0}行的{1}数据导入错误";
10
11 /**
12 * 导入Excel
13 *
14 * @param clazz
15 * @param xls
16 * @return
17 * @throws Exception
18 */
19 @SuppressWarnings("rawtypes")
20 public static List importExcel(Class<?> clazz, InputStream xls) throws Exception {
21 try {
22 // 取得Excel
23 HSSFWorkbook wb = new HSSFWorkbook(xls);
24 HSSFSheet sheet = wb.getSheetAt(0);
25 Field[] fields = clazz.getDeclaredFields();
26 List<Field> fieldList = new ArrayList<Field>(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 = new EmployeeDTO();
36 // 行循环
37 List<ImportModel> modelList = new ArrayList<ImportModel>(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 = new Exception(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(), new Date(parseDate(parseString(cell))));
57 } else {
58 BeanUtils.setProperty(model, field.getName(),
59 new Date(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 new Timestamp(parseDate(parseString(cell))));
66 } else {
67 BeanUtils.setProperty(model, field.getName(),
68 new Timestamp(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 new java.sql.Date(parseDate(parseString(cell))));
75 } else {
76 BeanUtils.setProperty(model, field.getName(),
77 new java.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 new BigDecimal(cell.getNumericCellValue()));
89 } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
90 BeanUtils.setProperty(model, field.getName(), new BigDecimal(parseString(cell)));
91 }
92 } else {
93 if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
94 BeanUtils.setProperty(model, field.getName(),
95 new BigDecimal(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 throw nullError;
111 }
112 modelList.add(model);
113 }
114 return modelList;
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 * @param clazz
128 * @param map
129 * @param rowSize
130 * @return
131 */
132 public static InputStream excelModelbyClass(Class<?> clazz, Map<Integer, String[]> map, 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 = new HSSFWorkbook();
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(new HSSFRichTextString(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<Integer> cells = new ArrayList<Integer>();
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(new HSSFRichTextString(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 = DVConstraint
189 .createExplicitListConstraint(map.get(modelProp.colIndex()));
190 CellRangeAddressList regions = new CellRangeAddressList(2, rowSize, modelProp.colIndex(),
191 modelProp.colIndex());
192 HSSFDataValidation dataValidation = new HSSFDataValidation(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 = new HSSFDataValidation(regions, constraint);
213 sheet.addValidationData(dataValidation);
214 }
215 }
216
217 ByteArrayOutputStream os = new ByteArrayOutputStream();
218 try {
219 wb.write(os);
220 } catch (IOException e) {
221 e.printStackTrace();
222 }
223
224 byte[] b = os.toByteArray();
225
226 ByteArrayInputStream in = new ByteArrayInputStream(b);
227 return in;
228 } catch (Exception e) {
229 e.printStackTrace();
230 return null;
231 }
232 }
233
234 private static String parseString(HSSFCell cell) {
235 return String.valueOf(cell).trim();
236 }
237
238 private static long parseDate(String dateString) throws ParseException {
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 new Date().getTime();
249 }
250 }
251
252 }
三、自定义spring注解
1 @Retention(RetentionPolicy.RUNTIME)
2 @Target(ElementType.FIELD)
3 public @interface ModelProp{
4 public String name();
5 public int colIndex() default -1;
6 public boolean nullable() default true;
7 public String interfaceXmlName() default "";
8 }
1 @Retention(RetentionPolicy.RUNTIME)
2 @Target(ElementType.TYPE)
3 public @interface ModelTitle{
4 public String name();
5 }
四、定义实体类父类
1 public class ImportModel {
2
3 }
五、定义实体类
1 @ModelTitle(name="人员列表")
2 public class EmployeeDTO extends ImportModel implements Serializable {
3
4 private static final long serialVersionUID = -3434719712955859295L;
5
6 private Long id;
7 @ModelProp(name = "电话", colIndex = 1, nullable = false)
8 private String telephone;
9
10 @ModelProp(name = "名称", colIndex = 0, nullable = false)
11 private String name;
12
13 @ModelProp(name = "性别", colIndex = 2, nullable = false)
14 private Integer sex;
15 }
六、定义controller
1 @RestController
2 @RequestMapping("/api/excelOpera")
3 public class ImportEmployeeController extends BaseController {
4
5 private static Logger logger = LoggerFactory.getLogger(ImportEmployeeController.class);
6 /**
7 * 导入excel表
8 * @version 1.0
9 * @since 1.0
10 */
11 @RequestMapping(path = "/importEmployee", method = RequestMethod.POST)
12 public RespMsg uploadExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
13 try{
14
15 List<EmployeeDTO> employeeDTOList = ImportExcelUtil.importExcel(EmployeeDTO.class, file.getInputStream());
16 //可做持久化操作，现只打印观察
17 for(EmployeeDTO employeeDTO : employeeDTOList){
18 logger.info("name=" + employeeDTO.getName() + ",telephone=" + employeeDTO.getTelephone()+",sex=" + employeeDTO.getSex());
19 }
20 }catch(Exception e){
21 logger.error(e.getMessage());
22 }
23 return null;
24 }
25 /**
26 * 导出excel模版
27 * @version 1.0
28 * @since 1.0
29 */
30 @RequestMapping(path = "/downloadEmployeeModel", method = RequestMethod.GET)
31 public RespMsg downloadEmployeeModel(HttpServletResponse response) {
32 try{
33 response.setContentType("application/xls");
34 response.addHeader("Content-Disposition", "attachment;filename="+new String(("eeelist").getBytes("UTF-8"),"iso-8859-1")+".xls");
35 Map<Integer,String[]> paramMap = new HashMap<Integer,String[]>();
36 //excel第三行为下拉选择框
37 paramMap.put(2, new String[]{"man","women"});
38 BufferedInputStream input = new BufferedInputStream(ImportExcelUtil.excelModelbyClass(EmployeeDTO.class, paramMap, null));
39 byte buffBytes[] = new byte[1024];
40 OutputStream os = response.getOutputStream();
41 int read = 0;
42 while ((read = input.read(buffBytes)) != -1) {
43 os.write(buffBytes, 0, read);
44 }
45 os.flush();
46 os.close();
47 input.close();
48 return success("下载成功！");
49 }catch(Exception e){
50 logger.error("downloadEmployeeModel() catch Exception ",e);
51 return fail("下载失败！");
52 }
53 }
54
55 }
56
至此全部工具类的实现已经完成，可以请求访问检验一下结果。
欢迎关注我的公众号：“从零开始的it转行生”
浙公网安备 33010602011771号