一、添加依赖
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.6</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.6</version></dependency>
二、自定义注解
@Target({ElementType.FIELD, ElementType.TYPE})@Documented@Retention(RetentionPolicy.RUNTIME)public @interface ExcelTag {/*** 表头** @return*/String tag();/*** 字体颜色** @return*/IndexedColors fontColor() default IndexedColors.BLACK;}
三、工具类
public class ExcelUtils {/*** 解析数据，将inputStream转为List** @param excel* @param clazz* @param <T>* @return* @throws Exception*/public static <T> List<T> parse(InputStream excel, Class<T> clazz) throws Exception {XSSFWorkbook xssfWorkbook = new XSSFWorkbook(excel);XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);XSSFRow titleCell = xssfSheet.getRow(0);List<T> dataList = new ArrayList<>(xssfSheet.getLastRowNum());T datum;Map<String, Field> fieldMap = getFieldMap(clazz);for (int i = 1; i <= xssfSheet.getLastRowNum(); i++) {XSSFRow xssfRow = xssfSheet.getRow(i);datum = clazz.newInstance();int minCell = xssfRow.getFirstCellNum();int maxCell = xssfRow.getLastCellNum();for (int cellNum = minCell; cellNum <= maxCell; cellNum++) {XSSFCell title = titleCell.getCell(cellNum);if (title == null) {continue;}String tag = title.getStringCellValue();Field field = fieldMap.get(tag);if (field == null) {continue;}Class<?> type = field.getType();Object value = null;XSSFCell cell = xssfRow.getCell(cellNum);if (cell == null) {continue;}if (type.equals(String.class)) {cell.setCellType(Cell.CELL_TYPE_STRING);value = cell.getStringCellValue();} else if (type.equals(Date.class)) {value = cell.getDateCellValue();}PropertyUtils.setProperty(datum, field.getName(), value);}dataList.add(datum);}return dataList;}private static <T> Map<String, Field> getFieldMap(Class<T> clazz) {Field[] fields = FieldUtils.getFieldsWithAnnotation(clazz, ExcelTag.class);Map<String, Field> fieldMap = new HashMap<>(fields.length / 3 * 4);for (Field field : fields) {ExcelTag annotation = field.getAnnotation(ExcelTag.class);fieldMap.put(annotation.tag(), field);}return fieldMap;}/*** 导出数据到outputStream** @param outputStream* @param dataList* @param clazz* @param <T>* @throws Exception*/public static <T> void export(OutputStream outputStream, List<T> dataList, Class<T> clazz) throws Exception {XSSFWorkbook wb = new XSSFWorkbook();ExcelTag annotation = clazz.getAnnotation(ExcelTag.class);String tag = annotation.tag();//工作簿XSSFSheet sheet = wb.createSheet(tag);Field[] fields = FieldUtils.getFieldsWithAnnotation(clazz, ExcelTag.class);//表头XSSFRow headers = sheet.createRow(0);for (int index = 0; index < fields.length; index++) {Field field = fields[index];int type = 0;if (String.class.equals(field.getType())) {type = Cell.CELL_TYPE_STRING;}ExcelTag excelTag = field.getAnnotation(ExcelTag.class);XSSFCell cell = headers.createCell(index, type);XSSFCellStyle style = wb.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER);XSSFFont font1 = wb.createFont();font1.setColor(excelTag.fontColor().getIndex());style.setFont(font1);cell.setCellStyle(style);tag = excelTag.tag();cell.setCellValue(tag);sheet.setColumnWidth(index, tag.getBytes().length*256);//设置列宽（中文适用）}//插入数据XSSFRow row;Field field;for (int i = 0; i < dataList.size(); i++) {T datum = dataList.get(i);row = sheet.createRow(i + 1);for (int index = 0; index < fields.length; index++) {field = fields[index];int type = 0;XSSFCell cell = row.createCell(index, type);XSSFCellStyle style = wb.createCellStyle();style.setAlignment(HSSFCellStyle.ALIGN_CENTER);cell.setCellStyle(style);Object property = PropertyUtils.getProperty(datum, field.getName());if (String.class.equals(field.getType())) {cell.setCellValue((String) property);} else if (Integer.class.equals(field.getType())) {cell.setCellValue((Integer) property);}else if (double.class.equals(field.getType())) {cell.setCellValue((double) property);}else if (Date.class.equals(field.getType())) {cell.setCellValue((Date) property);}else {cell.setCellValue((String) property);}}}//生成文档wb.write(outputStream);}}
四、数据传输对象
package org.lhj.pro.poiImport;import org.apache.poi.ss.usermodel.IndexedColors; import lombok.Data;@Data@ExcelTag(tag = "路由信息")public class RoutingExcelDto {@ExcelTag(tag = "机构编号", fontColor = IndexedColors.RED)private Integer orgCode;@ExcelTag(tag = "机构名称", fontColor = IndexedColors.RED)private String orgName;@ExcelTag(tag = "服务编号", fontColor = IndexedColors.RED)private Integer serviceCode;@ExcelTag(tag = "应用类型")private String applicationType;@ExcelTag(tag = "业务领域")private String businessArea;@ExcelTag(tag = "服务名称")private String serviceName;@ExcelTag(tag = "大版本号", fontColor = IndexedColors.RED)private double bigVersion;@ExcelTag(tag = "小版本号", fontColor = IndexedColors.RED)private double version;@ExcelTag(tag = "服务描述")private String description;@ExcelTag(tag = "系统名称", fontColor = IndexedColors.RED)private String systemName;@ExcelTag(tag = "服务地址", fontColor = IndexedColors.RED)private String servicePath;@ExcelTag(tag = "第三方服务名称", fontColor = IndexedColors.RED)private String thirdServiceName;public RoutingExcelDto(Integer orgCode, String orgName, Integer serviceCode, String applicationType,String businessArea, String serviceName, double bigVersion, double version, String description,String systemName, String servicePath, String thirdServiceName) {super();Code = orgCode;Name = orgName;this.serviceCode = serviceCode;this.applicationType = applicationType;this.businessArea = businessArea;this.serviceName = serviceName;this.bigVersion = bigVersion;this.version = version;this.description = description;this.systemName = systemName;this.servicePath = servicePath;this.thirdServiceName = thirdServiceName;}public RoutingExcelDto() {super();}}
五、创建 Controller测试
package org.lhj.pro.poiImport;import java.io.FileNotFoundException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import java.util.Random;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.multipart.MultipartFile;@RestController@RequestMapping("/import")public class ImportController {/*** excell导出* @param response* @return* @throws FileNotFoundException* @throws Exception*/@RequestMapping("/exportExcel")public String exportExcel(HttpServletResponse response) throws FileNotFoundException, Exception {List<RoutingExcelDto> dataList = new ArrayList<>();for (int i = 1; i < 10; i++) {RoutingExcelDto routingExcelDto = new RoutingExcelDto(i,"123",new Random().nextInt(1000)+1000, "y", "123", "123", 1.1, 1.11, "123", "123", "123", "123");dataList.add(routingExcelDto);}ServletOutputStream out = response.getOutputStream();String title = new String((new SimpleDateFormat("yyyy-MM-dd HH-mm-ss").format(new Date())).getBytes(),"UTF-8");response.setHeader("Content-disposition", "filename=" + title +RoutingExcelDto.class.getSimpleName()+ ".xls");ExcelUtils.export(out, dataList, RoutingExcelDto.class);return "导出列表成功！";}/*** excel导入* @param excel* @return List集合*/@RequestMapping("/parseExcel")public List<RoutingExcelDto> parseExcel(MultipartFile excel) {List<RoutingExcelDto> parseList;try {parseList = ExcelUtils.parse(excel.getInputStream(), RoutingExcelDto.class);return parseList;} catch (Exception e) {throw new RuntimeException(e.getMessage());}}}
如果觉得《Java-Poi导入导出excel工具类》对你有帮助，请点赞、收藏，并留下你的观点哦！
