利用java代码简单的导出Excel表格
POI简介:Apache POI是Apache软件基金会的开源项目,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。 .NET的开发人员则可以利用NPOI (POI for .NET) 来存取 Microsoft Office文档的功能。
官方主页 http://poi.apache.org/index.html,
API文档 http://poi.apache.org/apidocs/index.html
现在用的比较多的都是用POI技术来导出或者导入Excel,所以我们就用POI吧,用POI导出Excel我们首先要下载所需的jar包然后导入到我们的项目中,用maven的同学只需找到相关依赖加入到pom.xml里面即可。
-
下载jar包:
官方下载:http://poi.apache.org/download.html 这里可以下载到它的最新版本和文档 -
将jar包加入到项目中:
如果是用maven的可自行到maven中央仓库搜索poi然后选择对应的版本即可,也可以直接将下面代码复制到pom.xml。<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.0.1</version> </dependency>
-
Jakarta POI HSSF API组件:
HSSF(用于操作Excel的组件)提供给用户使用的对象rg.apache.poi.hssf.usermodel包中,主要部分包括Excel对象,样式和格式,有以下几种常用的对象:常用组件: HSSFWorkbook excel的文档对象 HSSFSheet excel的表单 HSSFRow excel的行 HSSFCell excel的格子单元 HSSFFont excel字体 样式: HSSFCellStyle cell样式
4.基本操作步骤:
首先,我们应该要知道的是,一个Excel文件对应一个workbook,一个workbook中有多个sheet组成,一个sheet是由多个行(row)和列(cell)组成。那么我们用poi要导出一个Excel表格的正确顺序应该是:1、用HSSFWorkbook打开或者创建“Excel文件对象” 2、用HSSFWorkbook对象返回或者创建Sheet对象 3、用Sheet对象返回行对象,用行对象得到Cell对象 4、对Cell对象读写。 5、将生成的HSSFWorkbook放入HttpServletResponse中响应到前端页面
5.编写工具类
package com.gs.java_poi_t1.util; import org.apache.poi.hssf.usermodel.*; 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; } }
6.控制层代码
package com.gs.java_poi_t1.controller; import com.gs.java_poi_t1.bean.Employee; import com.gs.java_poi_t1.util.ExcelUtil; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.util.ArrayList; import java.util.List; @Controller @RequestMapping(value = "/report") public class ExcelController { // @Resource(name = "reportService") // private ReportManager reportService; @RequestMapping("/index") public String toindex(){ return "/index"; } /** * 导出报表 * * @return */ @RequestMapping(value = "/export") @ResponseBody public void export(HttpServletRequest request, HttpServletResponse response) throws Exception { //获取数据 // List<PageData> list = reportService.bookList(page); List list = new ArrayList(); list.add(new Employee(10001,"张三","18890123456",2,"1.jpg")); list.add(new Employee(10002,"李四","15678129025",4,"2.jpg")); list.add(new Employee(10003,"王五","13921403367",1,"3.jpg")); list.add(new Employee(10001,"张三","18890123456",2,"1.jpg")); list.add(new Employee(10002,"李四","15678129025",4,"2.jpg")); list.add(new Employee(10003,"王五","13921403367",1,"3.jpg")); //excel标题 String[] title = {"编号", "名称", "头像", "号码", "部门编号"}; //excel文件名 String fileName = "学生信息表" + System.currentTimeMillis() + ".xls"; //sheet名 String sheetName = "学生信息表"; //长度为 多少个字段 // Integer index=title.length; //定义二维数组 String content[][] = new String[list.size()][title.length]; for (int i = 0; i < list.size(); i++) { // content[i]= new String[title.length]; Employee obj = (Employee) list.get(i); System.out.println(); content[i][0] = obj.getEid()+""; content[i][1] = obj.getEname(); content[i][2] = obj.getFace(); content[i][3] = obj.getPhone(); content[i][4] = obj.getDid()+""; } //创建HSSFWorkbook HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null); //响应到客户端 try { this.setResponseHeader(response, fileName); OutputStream os = response.getOutputStream(); 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(), "utf-8"); } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block e.printStackTrace(); } response.setContentType("application/octet-stream;charset=utf-8"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); response.addHeader("Pargam", "no-cache"); response.addHeader("Cache-Control", "no-cache"); } catch (Exception ex) { ex.printStackTrace(); } } }
7.javabean实体类
package com.gs.java_poi_t1.bean; public class Employee { private Integer eid; private String ename; private String phone; private Integer did; private String face; public Employee() { } public Employee(Integer eid, String ename, String phone, Integer did, String face) { this.eid = eid; this.ename = ename; this.phone = phone; this.did = did; this.face = face; } @Override public String toString() { return "Employee{" + "eid=" + eid + ", ename='" + ename + '\'' + ", phone='" + phone + '\'' + ", did=" + did + ", face='" + face + '\'' + '}'; } public Integer getEid() { return eid; } public void setEid(Integer eid) { this.eid = eid; } public String getEname() { return ename; } public void setEname(String ename) { this.ename = ename; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } public Integer getDid() { return did; } public void setDid(Integer did) { this.did = did; } public String getFace() { return face; } public void setFace(String face) { this.face = face; } }
8.页面跳转
好啦,到此大功告成,谢谢大家耐心看完,喜欢的可以支持一下,有了大家的鼓励我会更有动力!!!