首页 > 代码库 > 使用jxls技术导入Excel模版数据(转自其他博客)

使用jxls技术导入Excel模版数据(转自其他博客)

第一步:先确定好Excel导入的格式以及各表格字段值的含义


技术分享

 

第二步:定义好解析的XML--videoConfig.xml

 

 

<?xml version="1.0" encoding="UTF-8"?> 
<workbook> 
   <worksheet name="Sheet1"> 
    <section startRow="0" endRow="0"/> 
    <loop startRow="1" endRow="1" items="videoInfoList" var="videoInfo" varType="com.iflytek.weike.job.bo.VideoInfo"> 
    <section startRow="1" endRow="1"> 
    <mapping row="1" col="0">videoInfo.index</mapping> 
    <mapping row="1" col="1">videoInfo.videoName</mapping> 
    <mapping row="1" col="2">videoInfo.resourceId</mapping> 
    <mapping row="1" col="3">videoInfo.upload</mapping> 
    <mapping row="1" col="4">videoInfo.content</mapping> 
    <mapping row="1" col="5">videoInfo.schoolName</mapping>
   </section> 
    <loopbreakcondition> 
     <rowcheck offset="0">
        <cellcheck offset="0"></cellcheck> 
      </rowcheck> 
    </loopbreakcondition> 
    </loop> 
    </worksheet> 
</workbook>

 

 第三步:生成一下解析的实体类VideoInfo(这个需要根据excel文件的列去手工写一个)

 

public class VideoInfo {
	//序号
	private int index;
	//视频名称(全称)
	private String videoName;
	//视频资源ID
	private String resourceId;
	//上传者
	private String upload;
	//课程说明
	private String content;
	//学校名称
	private String schoolName;
	
	public VideoInfo() {
	}
	public VideoInfo(int index, String videoName, String resourceId, String upload, String content, String schoolName) {
		super();
		this.index = index;
		this.videoName = videoName;
		this.resourceId = resourceId;
		this.upload = upload;
		this.content = content;
		this.schoolName = schoolName;
	}
	public int getIndex() {
		return index;
	}
	public void setIndex(int index) {
		this.index = index;
	}
	public String getVideoName() {
		return videoName;
	}
	public void setVideoName(String videoName) {
		this.videoName = videoName;
	}
	public String getResourceId() {
		return resourceId;
	}
	public void setResourceId(String resourceId) {
		this.resourceId = resourceId;
	}
	public String getUpload() {
		return upload;
	}
	public void setUpload(String upload) {
		this.upload = upload;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getSchoolName() {
		return schoolName;
	}
	public void setSchoolName(String schoolName) {
		this.schoolName = schoolName;
	}
	@Override
	public String toString() {
		return "VideoInfo [index=" + index + ", videoName=" + videoName + ", resourceId=" + resourceId + ", upload="
				+ upload + ", content=" + content + ", schoolName=" + schoolName + "]";
	}
	
	
}

 
 第四步:添加jxls的jar包,我这里项目用maven管理jar包的版本是1.0.6大家可以去下面这个maven资源库下                载jar包  maven资源库地址:http://mvnrepository.com/open-source/excel-libraries;

 

第五步:windows弹框选择文件并解析Excel数据,这个windows文件框选择文件我以前还是真没做过在网上               找了一个很好用的方法请看代码:

[java] view plain copy
 
  1. /** 
  2.      * 打开文件选择窗口选择导入文件 
  3.      * @return 返回文件路径 
  4.      * @throws Exception 
  5.      */  
  6.     public String getExcelPath() throws Exception{  
  7.         UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());  
  8.         JFileChooser jFileChooser=new JFileChooser();  
  9.         int i = jFileChooser.showOpenDialog(null);   
  10.         if(i== jFileChooser.APPROVE_OPTION){ //打开文件                          
  11.             String path = jFileChooser.getSelectedFile().getAbsolutePath();  
  12.             String fileName = jFileChooser.getSelectedFile().getName();  
  13.             String extName =fileName.substring(fileName.lastIndexOf(".")+1,fileName.length());  
  14.             System.out.println("当前文件路径:"+path+";\n当前文件名:"+fileName+";\n当前文件扩展名:"+extName);  
  15.             if(null!=extName&&"xlsx".equals(extName)){  
  16.                 return path;  
  17.             }else{  
  18.                 System.out.println("您好,只能导入扩展名为xlsx的Excel文件!");  
  19.                 return null;  
  20.             }  
  21.         }else{  
  22.             System.out.println("没有选中文件");  
  23.             return null;  
  24.         }  
  25. }  
  26.   
  27.   
  28. /** 
  29.      * 使用jxls解析导入的Excel 
  30.      * @param path 导入文件路径 
  31.      * @return List<VideoInfo> 导入对象集合 
  32.      */  
  33.     public List<VideoInfo> getExcelDataForVideoInfo(String path){  
  34.         List<VideoInfo> videoInfoList = new ArrayList<VideoInfo>();  
  35.         try {  
  36.             InputStream inputXML = new BufferedInputStream(getClass().getClassLoader().getResourceAsStream(ConsForSystem.XML_CONFIG));  
  37.             XLSReader mainReader = ReaderBuilder.buildFromXML( inputXML );  
  38.             InputStream inputXLS = new BufferedInputStream(new FileInputStream(new File(path)));  
  39.             VideoInfo videoInfo = new VideoInfo();  
  40.             Map<String,Object> beans = new HashMap<String,Object>();  
  41.             beans.put("videoInfo", videoInfo);  
  42.             beans.put("videoInfoList", videoInfoList);  
  43.             XLSReadStatus readStatus = mainReader.read( inputXLS, beans);  
  44.             if(readStatus.isStatusOK()){  
  45.                 System.out.println("jxls读取Excel成功!");  
  46.             }  
  47.         } catch (Exception e) {  
  48.             e.printStackTrace();  
  49.         }  
  50.         return videoInfoList;  
  51.     }  

    其中有个静态变量我是统一写在配置类中的:

     public static String XML_CONFIG ="videoConfig.xml";

 

     第六步:写一个main函数执行我们写好的方法试一下

     

[java] view plain copy
 
  1. public class Test {  
  2.     public static void main(String[] args) {  
  3.         SyncDataServiceImpl syncDataService = new SyncDataServiceImpl();  
  4.         try {  
  5.             String filePath = syncDataService.getExcelPath();  
  6.             if(null!=filePath&&StringUtils.isNotBlank(filePath)){  
  7.                 //导入Excel文件解析信息获取资源id  
  8.                 List<VideoInfo> infoList = syncDataService.getExcelDataForVideoInfo(filePath);  
  9.                 System.out.println("infoList大小==="+infoList.size());  
  10.                 for(VideoInfo video:infoList){  
  11.                     System.out.println("打印ideoInfo详细信息======"+video.toString());  
  12.                 }  
  13.             }  
  14.         } catch (Exception e) {  
  15.             e.printStackTrace();  
  16.         }  
  17.           
  18.     }  
  19. }  

 

    其中SyncDataServiceImpl类是我把前面二个方法写到这个类里面了,里面还有一些其他的业务处理逻辑,就不贴上来了, new SyncDataServiceImpl()对象就可以调用刚才的方法了!

 下面的运行截图:

 
技术分享
 

 运行结果截图,导入Excel成功:


技术分享
 
相比较POI来读取Excel数据个人觉得jxls用起来还是更方便一点!同时jxls导出Excel也是比较方便的,有自己的标签类似JSTL,以后有时间再写一篇吧!希望能帮到需要的人,哈哈!有写的不对的希望高手可以指点一下!谢谢!

使用jxls技术导入Excel模版数据(转自其他博客)