POI导入excel

「爱情、让人受尽委屈。」 2022-02-27 01:24 294阅读 0赞

1.官方下载：[http://poi.apache.org/download.html][http_poi.apache.org_download.html] 或者导入pom

<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.14</version>
    </dependency>

maven所使用jar包，没有使用maven的话，就用poi-3.9.jar和poi-ooxml-3.9.jar（这个主要是用于Excel2007以后的版本)  
批量导出学生信息  
我前端用easyui的datagrid  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMjQ5ODky_size_16_color_FFFFFF_t_70][]dg为表格的id  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMjQ5ODky_size_16_color_FFFFFF_t_70 1]

//导出
    	@RequestMapping("/inputStudentExecl") 
    	public void inputStudentExecl(String ids,HttpServletRequest request,HttpServletResponse response) {
    		List<Student> list=null;
    		if (!ids.equals("")) {
    			String[] id = ids.split(",");
    			List<Integer> list_ids = new ArrayList<Integer>();
    			for (int i = 0; i < id.length; i++) {
    				list_ids.add(Integer.parseInt(id[i]));
    			}
    			list = studentService.findStudentByIds(list_ids);
    		} else {
    			/*StudentWhere studentWhere2=new StudentWhere();
    			studentWhere2.setStuName(studentWhere.getStuName());*/
    			//list=studentService.findAllStdent(new StudentWhere(studentWhere.getStuName(), studentWhere.getPhone(), studentWhere.getQq(), studentWhere.getIsPay(), studentWhere.getIsEffective(), studentWhere.getIsReturn(), studentWhere.getTime(), studentWhere.getStartTime(), studentWhere.getFinishTime()));
    			list=studentService.findStudent();
    		}
    		//获取数据
            //excel标题
    		String[] title = {"卡号","姓名","性别","状态","备注","学生学号","系","专业","学历"};
    		//excel文件名
    		String fileName = "学生信息表"+System.currentTimeMillis()+".xls";
    		//sheet名
    		String sheetName = "学生信息表";
    		//从数据库获得的数据
    		String[][] content = new String[list.size()][title.length];
    		for (int i = 0; i < list.size(); i++) {
    		//每条数据依此取出
                Student obj = list.get(i);
                Memberships memberships = list.get(i).getMemberships();
                //三元表达式判断数据是否为null
                content[i][0] =obj.getCardNo()!=null?obj.getCardNo():null;
                content[i][1] = obj.getName()!=null?obj.getName().toString():null;
                content[i][2] = obj.getSex()!=null?obj.getSex().toString():null;
                content[i][3] = obj.getStatus()!=null?obj.getStatus().toString():null;
                content[i][4] = obj.getRemark()!=null?obj.getRemark().toString():null;
                content[i][5] = obj.getStuNo()!=null?obj.getStuNo().toString():null;
                content[i][6] = memberships.getDepartment()!=null?memberships.getDepartment().toString():null;
                content[i][7] = memberships.getSpecialty()!=null?memberships.getSpecialty().toString():null;
                content[i][8] = memberships.getDegree()!=null?memberships.getDegree().toString():null;
        	  }
    
        	   //创建HSSFWorkbook 
        	   HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);
    
        	   //响应到客户端
        	   try {
    				this.setResponseHeader(response, fileName);//response响应//fileName文件名
    				OutputStream os = response.getOutputStream();//FileOutputStream的形式进行整行的写入
    				wb.write(os);//向缓冲流中写入数据
    				os.flush();//关闭缓冲流
    				os.close();
        	   } catch (Exception e) {
        		   e.printStackTrace();
        	   }
    	}
    	// 发送响应流方法
    		public void setResponseHeader(HttpServletResponse response, String fileName) {
    			try {
    				try {
    					fileName = new String(fileName.getBytes(), "ISO8859-1");//解决了传输过程的中文乱码问题
    				} catch (UnsupportedEncodingException e) {
    					e.printStackTrace();
    				}
    				//该实体头的作用是让服务器告诉浏览器它发送的数据属于什么文件类型
    				response.setContentType("application/octet-stream;charset=ISO8859-1");
    				//Content-Type 的类型为要下载的类型时 , 这个信息头会告诉浏览器这个文件的名字和类型
    				response.setHeader("Content-Disposition", "attachment;filename="
    						+ fileName);
    				//客户端不缓存
    				response.addHeader("Pargam", "no-cache");
    				response.addHeader("Cache-Control", "no-cache");
    			} catch (Exception ex) {
    				ex.printStackTrace();
    			}
    		}
    }

封装Util类  
package com.ysd.util;

import org.apache.poi.hssf.usermodel.HSSFCell;  
import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
import org.apache.poi.hssf.usermodel.HSSFRow;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelUtil \{

/**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){
    
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }
    
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
    
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
    
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
       /* style.setAlignment(HSSFCellStyle.ALIGN_CENTER); */// 创建一个居中格式
    
        //声明列对象
        HSSFCell 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;
    }

mapper

/**
    	 * 根据ids查询学生信息
    	 * @param ids id集合
    	 * @return 
    	 */
    	List<Student> getStudentByIds(List<Integer> ids);

mapper.xml

<select id="getStudentByIds" resultMap="studentMapping">
    		SELECT s.*,m.Department,m.Specialty,m.Degree from  student s LEFT JOIN memberships m ON m.Id=s.MembershipId where s.Id in
    		<foreach collection="list" open="(" separator="," close=")" index="index" item="item">
    			#{item}
    		</foreach>
    	</select>

[http_poi.apache.org_download.html]: http://poi.apache.org/download.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMjQ5ODky_size_16_color_FFFFFF_t_70]: /images/20220227/3fa7c2f9299440c6a2ce6d88dda09d20.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxMjQ5ODky_size_16_color_FFFFFF_t_70 1]: /images/20220227/ba07e418651943aebcf0bf1e0b994f63.png