利用POI实现Execl的导入导出

最新推荐文章于 2024-05-13 13:51:17 发布

杨不同

最新推荐文章于 2024-05-13 13:51:17 发布

阅读量413

点赞数 3

文章标签： POI

本文链接：https://blog.csdn.net/weixin_43800798/article/details/84533272

版权

#导入POI依赖架包

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.15</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.15</version>
</dependency>

##将数据库的数据导出成Excel
###先看项目结构，采用基于Maven的Spring boot 项目
在这里插入图片描述

###MySql数据库数据:
在这里插入图片描述

###下一步就是把数据库数据导出成Excel，这里利用了我的Controller层的exportUserAll方法
####第一步：导出MySql数据，变成Excel文件流
Control层代码

package com.travelsky.pms.importandexport.controller;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import com.travelsky.pms.common.dto.JSONResponse;
import com.travelsky.pms.common.entity.UserEntity;
import com.travelsky.pms.common.util.UUIDUtil;
import com.travelsky.pms.importandexport.service.ExcelService;
import com.travelsky.pms.importandexport.util.ExcelUtil;

@org.springframework.stereotype.Controller
@RequestMapping
public class Controller {
	@Autowired
	ExcelService service;
	/*http://127.0.0.1:88/exportUserAll
	 * Execl导入
	 */
	   
	@GetMapping("/exportUserAll")
	@ResponseBody
    public void exportUserAll(HttpServletResponse response) throws IOException {
	
		HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("信息表");//创建sheet对象
        List<UserEntity> userList =service.ExportUserInfor();//得到查询所有结果
        String Name ="USER"+ ".xls";//设置要导出的文件的名字
       
    

        String[] headers = {"序号","账号","密码","姓名", "电话号码", "git账号","电子邮箱","成绩","学分"};
        //headers表示excel表中第一行的表头
        HSSFRow row = sheet.createRow(0);//第一行
        //在excel表中添加表头
        for(int i=0;i<headers.length;i++){
            HSSFCell cell = row.createCell(i);//第几列
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        int rowNum = 1;
        //在表中存放查询到的数据放入对应的列
        for (UserEntity us : userList) {
            HSSFRow row1 = sheet.createRow(rowNum);//第几行
            row1.createCell(0).setCellValue(rowNum);
            row1.createCell(1).setCellValue(us.getAccount());
            row1.createCell(2).setCellValue(us.getPassword());
            row1.createCell(3).setCellValue(us.getName());
            row1.createCell(4).setCellValue(us.getPhone());
            row1.createCell(5).setCellValue(us.getGitlabAccount());
            row1.createCell(6).setCellValue(us.getEmail());
            row1.createCell(7).setCellValue(us.getScore());
            row1.createCell(8).setCellValue(us.getCredit());
            rowNum++;
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" +Name);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
		
}
	/*http://127.0.0.1:88/ImportUserAll?path=E:/1.xls
	 * Execl导入
	 */

	


	    /**
	     * 用于接收前端上传文件
	     * @param request
	     * @param file
	     * @throws Exception 
	     */
	 
		@RequestMapping("/ImportUserAll")    
	    @ResponseBody    
	    public JSONResponse importUserAll(@RequestParam("file") MultipartFile file,HttpServletRequest request) throws Exception {    
	        if (!file.isEmpty()) {    
	            
	            	
	            	String path=System.getProperty("user.dir");
	                BufferedOutputStream out = new BufferedOutputStream(    
	                        new FileOutputStream(new File(file.getOriginalFilename())));    
	                //System.out.println("文件名"+file.getOriginalFilename());	              
	               //System.out.println("目录"+path+"\\"+file.getOriginalFilename());
	               
	                out.write(file.getBytes());    
	                out.flush();    
	                out.close();
	                String filePath=path+"\\"+file.getOriginalFilename();
	                List<UserEntity> result=new ArrayList<>();
	        		List<Object> list=ExcelUtil.Execl2List(filePath);//调用工具类
	        		for(int i=0;i<list.size();i++) 
	        		{  
	        			List<Object> objectList=(List<Object>) list.get(i);
	        			int k=1;
	        			while(k<objectList.size())
	        			{    UserEntity ue=new UserEntity();
	        			     ue.setAccount(objectList.get(k).toString());
	        			     k+=1;
	        			     ue.setPassword(objectList.get(k).toString());
	        			     k+=1;
	        			     ue.setName(objectList.get(k).toString());
	        			     k+=1;			     
	        			     ue.setPhone(objectList.get(k).toString());
	        			     k+=1;
	        			     ue.setGitlabAccount(objectList.get(k).toString());
	        			     k+=1;
	        			     ue.setEmail(objectList.get(k).toString());
	        			     k+=1;
	        			     ue.setScore(Integer.parseInt(objectList.get(k).toString().substring(0,3)));
	        			     k+=1;
	        			     ue.setCredit(Integer.parseInt(objectList.get(k).toString().substring(0,3)));
	        			     k+=1;
	        			     ue.setId(UUIDUtil.random32());
	        			     ue.setRoleFlag(true);//true代表管理员，flase代表普通用户
	        			     ue.setDeleteFlag(false);//true代表没删
	        				 System.out.println(ue);
	        			     result.add(ue);//这里就是所有结果存为实体列表
	        			}
	        			
	        		}
	        		service.ImportUserInfor(result);//导入数据库业务		                       
	           
	        }
			return JSONResponse.ok("导入成功");
	        }

		
		}

实体类代码

package com.travelsky.pms.common.entity;

import lombok.Data;

@Data
public class UserEntity {
    private String id;

    private String account;

    private String password;

    private String name;

    private String phone;

    private String gitlabAccount;

    private String email;

    private Integer score;

    private Integer credit;

    private Boolean roleFlag;

    private Boolean deleteFlag;

  
}

具体的代码和解释我都有备注
写到这里就可以直接输入网址 http://127.0.0.1:88/exportUserAll，服务器后端就会自动写到浏览器，变成下载文件流：

在这里插入图片描述
打开的Excel文件：

####第二步前端接收
这里的前端接收并不简单!
一开始我就难在这里，因为我发现不知道用什么来接收后端的文件流

然后我也百度什么找资料，突然就发现了一个神器，直接利用标签的跳跃性范文后端服务器接口，就不需要前端接受
下载
html网页添加这个标签就能像之前那样自动下载
##导出讲完了，我们接着讨论怎么导入，导入相对麻烦一点，首先我们要明确需求是那种导。一般的Excel导入分为两种，第一种导入现有的本地Excel,第二种就把自己想成客服端，我要把Excel导入服务器，首先服务器得有这个文件吧，然后自动导入吧，毕竟我们做后端的，所以一般情况都是先上传然后在下载对吧；
###所以第一步上传文件，上传文件就是IO流的读写，一般来说大项目都是用一个云端暂存这些文件，我这里就不详细解说了，我就直接利用我自定义的importUserAll把文件上传到项目根目录上，上传后就会在这里显示1.xls文件
在这里插入图片描述
之后就是用IO流读取1.xml文件，利用我的ExcelUtil方法转化成list嵌套list.

package com.travelsky.pms.importandexport.util;


	import java.io.File;
	import java.io.FileInputStream;
	import java.io.IOException;
	import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
	import org.apache.poi.ss.usermodel.Cell;
	import org.apache.poi.ss.usermodel.Row;
	import org.apache.poi.ss.usermodel.Sheet;
	import org.apache.poi.ss.usermodel.Workbook;
	import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.travelsky.pms.common.exception.PMSException;
	 
	/**
	 *
	 * Description: Excel操作
	 * 
	 */
	public class ExcelUtil {
	 
		private static final String EXCEL_XLS = "xls";  
	    private static final String EXCEL_XLSX = "xlsx";  
	  
	    /** 
	     * 判断Excel的版本,获取Workbook 
	     * @param in 
	     * @param filename 
	     * @return 
	     * @throws IOException 
	     */  
	    public static Workbook getWorkbok(InputStream in,File file) throws IOException{  
	        Workbook wb = null;  
	        if(file.getName().endsWith(EXCEL_XLS)){  //Excel 2003  
	            wb = new HSSFWorkbook(in);  
	        }else if(file.getName().endsWith(EXCEL_XLSX)){  // Excel 2007/2010  
	            wb = new XSSFWorkbook(in);  
	        }  
	        return wb;  
	    }  
	  
	    /** 
	     * 判断文件是否是excel 
	     * @return 
	     * @throws Exception  
	     */  
	    public static boolean checkExcelVaild(File file) throws Exception{  
	        if(!file.exists()){  
	            throw new PMSException("文件不存在");
	        }  
	        if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))){  
	            throw new PMSException("文件不是Excel"); 
	            
	        }
			return true;  
	    }  
	    
	    /** 
	     * 读取Excel测试，兼容 Excel 2003/2007/2010 
	     * @return 
	     * @return 
	     * @throws Exception  
	      */ 
	    
 public static List<Object> Execl2List(String path) throws Exception {  
	       
	      
	        
	            // 同时支持Excel 2003、2007  
	            File excelFile = new File(path); // 创建文件对象  
	            FileInputStream in = new FileInputStream(excelFile); // 文件流  
	            if(checkExcelVaild(excelFile))
	            {
	            Workbook workbook = getWorkbok(in,excelFile);  
	            //Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel2003/2007/2010都是可以处理的  
	  
	            int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量  
	            List<Object> biglList=new ArrayList<>();
	            for(int k=0;k<sheetCount;k++)
	            { 
//	             // 遍历第一个Sheet  
	            Sheet sheet = workbook.getSheetAt(k);   // 遍历第三个Sheet  
	            
	      
	         
	          
	            // 为跳过第一行目录设置count  
	            int count = 0;
	            for (Row row : sheet) {
	            	
	            		// 跳过第一和第二行的目录  
	                    if(count < 1 ) {
	                        count++;  
	                        continue;  
	                    }
	                    
	                   
	                   
	                    
	                    //获取总列数(空格的不计算)
	                    int columnTotalNum = row.getPhysicalNumberOfCells();
	                    
	                   // System.out.println("总列数：" + columnTotalNum);
	                    
	                   // System.out.println("最大列数：" + row.getLastCellNum());
	                    
	                    //for循环的，不扫描空格的列
	               List<Object> list=new ArrayList<>();
                   for (Cell cell : row) {
                	   if(!cell.toString().equals(""))
                	     {list.add(cell);
                    	//System.out.println("列"+cell);
                    	}
                    }
                 //  System.out.println(list);//获取所有list集合	
                   biglList.add(list);
					
	            	
	            	
	            }
	            return biglList;
				
	           
	            }  }
	        
			return null;
	       
	    }


	    private static Object getValue(Cell cell) {
	    	Object obj = null;
	    	switch (cell.getCellTypeEnum()) {
		        case BOOLEAN:
		            obj = cell.getBooleanCellValue(); 
		            break;
		        case ERROR:
		            obj = cell.getErrorCellValue(); 
		            break;
		        case NUMERIC:
		            obj = cell.getNumericCellValue(); 
		            break;
		        case STRING:
		            obj = cell.getStringCellValue(); 
		            break;
		        default:
		            break;
	    	}
	    	return obj;
	    }

    

}

然后打印成
[
[1.0, 123456, 2ea1cd0405ab06baeb761e863ff199e4, 大爷, 18875150631, ls_nidaye, 123345@qq.com, 990.0, 990.0], [2.0, 123456, 2ea1cd0405ab06baeb761e863ff199e4, 大娘, 18875150632, ls_nidage, 165695@qq.com, 990.0, 990.0], [3.0, 123456, 2ea1cd0405ab06baeb761e863ff199e4, 大哥, 18875150631, ls_nidaba, 5164161@qq.com, 990.0, 990.0], [4.0, 123456, 2ea1cd0405ab06baeb761e863ff199e4, 小弟, 18875150631, ls_lan, 464645@qq.com, 990.0, 990.0]
]
这种resultlist -----list形式，然后我们要把每一个list迭代出来
UserEntity(id=d92a3d587492a8aacb24b3b289a7e6b6, account=123456, password=2ea1cd0405ab06baeb761e863ff199e4, name=大爷, phone=18875150631, gitlabAccount=ls_nidaye, email=123345@qq.com, score=990, credit=990, roleFlag=true, deleteFlag=false)

UserEntity(id=0674f01fcb82730a978467d787fa4bcf, account=123456, password=2ea1cd0405ab06baeb761e863ff199e4, name=大娘, phone=18875150632, gitlabAccount=ls_nidage, email=165695@qq.com, score=990, credit=990, roleFlag=true, deleteFlag=false)

UserEntity(id=f5206718089a77aa45b45559df28200f, account=123456, password=2ea1cd0405ab06baeb761e863ff199e4, name=大哥, phone=18875150631, gitlabAccount=ls_nidaba, email=5164161@qq.com, score=990, credit=990, roleFlag=true, deleteFlag=false)

UserEntity(id=a91d0f2af288660ac534513d9ae98934, account=123456, password=2ea1cd0405ab06baeb761e863ff199e4, name=小弟, phone=18875150631, gitlabAccount=ls_lan, email=464645@qq.com, score=990, credit=990, roleFlag=true, deleteFlag=false)
紧接着就是添加到数据库,我这里利用mapper.xmldie迭代list

insert into sys_user (id, account, password,
name, phone, gitlab_account,
email, score, credit,
role_flag, delete_flag)
values

(#{item.id},
#{item.account},
#{item.password},
#{item.name},
#{item.phone},
#{item.gitlabAccount},
#{item.email},
#{item.score},
#{item.credit},
#{item.roleFlag},
#{item.deleteFlag})
然后就可以看到数据库数据 !
在这里插入图片描述

杨不同

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
4
评论
利用POI实现Execl的导入导出

#导入POI依赖架包&lt;dependency&gt; &lt;groupId&gt;org.apache.poi&lt;/groupId&gt; &lt;artifactId&gt;poi&lt;/artifactId&gt; &lt;version&gt;3.15&lt;/version&gt;&lt;/dependency&gt;&lt;dependen
复制链接

扫一扫