Poi实现Excle表导入与导出

我不是女神ヾ 2022-05-10 07:54 189阅读 0赞

# 一、引入maven依赖 #

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-web</artifactId>
                <version>5.0.7.RELEASE</version>
            </dependency>

# 二、编写Poi工具类 #

/**
     * @author sunyiran
     * @date 2018-07-25
     * @purpose excel表解析工具
     */
    public class PoiUtil {
    
    	private static final  String EXCLE_2003 = "xls";
    	private static final  String EXCLE_2007 = "xlsx";
    
    	/**
    	 * single newInstance
    	 */
    	private PoiUtil() {
    
    	}
    
    	/**
    	 * 获取Excle
    	 * @param file
    	 * @return
    	 */
    	public static Sheet getSheet(MultipartFile file) {
    		String fileFileName = file.getOriginalFilename();
    		if (fileFileName.endsWith(EXCLE_2003)) {
    			HSSFWorkbook hssf = null;
    			try {
    				hssf = new HSSFWorkbook(file.getInputStream());
    			} catch (IOException e) {
    
    				e.printStackTrace();
    			}
    			HSSFSheet sheet = hssf.getSheetAt(0);
    			return sheet;
    		}
    		if (fileFileName.endsWith(EXCLE_2007)) {
    			XSSFWorkbook wssf = null;
    			try {
    				wssf = new XSSFWorkbook(file.getInputStream());
    			} catch (IOException e) {
    
    				e.printStackTrace();
    			}
    			XSSFSheet sheet = wssf.getSheetAt(0);
    			return sheet;
    		}
    		return null;
    	}
    
    	/**
    	 * 导出Excel  (表格默认宽度20*256)
    	 * @param sheetName sheet名称
    	 * @param title 标题
    	 * @param values 内容
    	 * @param wb HSSFWorkbook对象
    	 * @return
    	 */
    	public static XSSFWorkbook getXSSFWorkbook(String sheetName,String []title,String [][]values, XSSFWorkbook wb){
    
    		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
    		if(wb == null){
    			wb = new XSSFWorkbook();
    		}
    
    		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
    		XSSFSheet sheet = wb.createSheet(sheetName);
    
    		for (int i = 0; i < title.length; i++) {
    			sheet.setColumnWidth(i, 20 * 256);
    		}
    
    		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
    		XSSFRow row = sheet.createRow(0);
    
    		// 第四步，创建单元格，并设置值表头 设置表头居中
    		XSSFCellStyle style = wb.createCellStyle();
    		// 创建一个居中格式
    		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    
    		//声明列对象
    		XSSFCell cell = null;
    
    		//创建标题
    		for(int i=0;i<title.length;i++){
    			cell = row.createCell(i);
    			cell.setCellValue(title[i]);
    			cell.setCellStyle(style);
    		}
    
    		//创建内容
    		for(int i=0;i<values.length;i++){
    			row = sheet.createRow(i + 1);
    			for(int j=0;j<values[i].length;j++){
    				//将内容按顺序赋给对应的列对象
    				row.createCell(j).setCellValue(values[i][j]);
    			}
    		}
    		return wb;
    	}
    }

# 三、编写业务代码 #

@Transactional
        @Override
        public void importExcle(MultipartFile file, String handler) {
            Sheet sheet = PoiUtil.getSheet(file);
    
            Assert.notNull(sheet, "Excle表不正确");
      //根据model对象实际内容进行解析
            for (Row row : sheet) {
                // 跳过第一行
                if (row.getRowNum() == 0) {
                    continue;
                }
                // 跳过空白位置
                if (row.getCell(0) == null || StringUtils.isBlank(row.getCell(0).getStringCellValue())) {
                    continue;
                }
                //开始创建对象,为确保参数验证，先用createBean对象
                CommunityCreateBean createBean = new CommunityCreateBean();
                createBean.setCommunityName(row.getCell(0).getStringCellValue());
                createBean.setProvince(row.getCell(1).getStringCellValue());
                createBean.setCity(row.getCell(2).getStringCellValue());
                createBean.setPropertyName(row.getCell(3).getStringCellValue());
                //特别注意电话号码，在poi解析中只有字符串和浮点类型
                BigDecimal phone = new BigDecimal(row.getCell(4).getNumericCellValue());
                createBean.setPhone(phone.toString());
                createBean.setCreator(handler);
                createBean.setCreateTime(new Date());
                createBean.setOnOff(CommunityStatus.UNSTART.getStatus());
    
                CwCommunity exitCommunity = communityRepository.findByCommunityNameAndProvinceAndCity(createBean.getCommunityName(), createBean
                        .getProvince(), createBean.getCity());
                if (exitCommunity != null) {
                    throw new RuntimeException("该小区已经存在");
                }
                CwCommunity community = new CwCommunity();
                BeanUtils.copyProperties(createBean, community);
                communityRepository.save(community);
            }
        }
    
        @Transactional
        @Override
        public XSSFWorkbook exportExcle() {
            String name = "小区信息表";
            String[] title = {"小区名称", "所在省", "所在市", "物业名称", "物业电话"};
            List<CwCommunity> list = communityRepository.findAll();
            String[][] content = new String[list.size()][title.length];
            for (int i = 0; i < list.size(); i++) {
                CwCommunity community = list.get(i);
                content[i][0] = community.getCommunityName();
                content[i][1] = community.getProvince();
                content[i][2] = community.getCity();
                content[i][3] = community.getPropertyName();
                content[i][4] = community.getPhone();
            }
            XSSFWorkbook workbook = PoiUtil.getXSSFWorkbook(name, title, content, null);
            return workbook;
        }

# 四、测试 #

**        列表为空，现在点击导入小区信息**

![70][]

![70 1][]

![70 2][]

**然后选择导出小区信息**

![70 3][]

![70 4][]

**结果OK**

[70]: /images/20220510/08be9eac163541fc8fbc0c02dd949e33.png
[70 1]: /images/20220510/ffc3eb9cb0a347f688bc9fa2262c5853.png
[70 2]: /images/20220510/00371c0500b1452cbff5a792a4756112.png
[70 3]: /images/20220510/d58f70791c514955908b744db6b21da5.png
[70 4]: /images/20220510/5edbcf6a70db4aed801982f185deeae5.png