java使用POI操作excel文件

一、poi的定义

java中操作excel的有两种比较主流的工具包： jxl 和 poi 。jxl 只能操作excel 95, 97, 2000也即以.xls为后缀的excel。而poi可以操作excel 95及以后的版本，即可操作后缀为 .xls 和 .xlsx两种格式的excel。

poi全称 poor obfuscation implementation,直译为“可怜的模糊实现”，利用poi接口可以通过java操作microsoft office 套件工具的读写功能。官网： ，poi支持office的所有版本，首先去官网下载如下界面：

下载完后，打开“poi-bin-3.15-20160924.tar.gz”获取操作excel需要的jar包，并将这些jar包复制到项目中。对于只操作2003 及以前版本的excel，只需要poi-3.15.jar ，如果需要同时对2007及以后版本进行操作则需要复制

poi-ooxml-3.15.jar
poi-ooxml-schemas-3.15.jar

以及复制在ooxml-lib目录下的xmlbeans-2.6.0.jar(但不知为何,我下的jar文件中没有dom4j.jar)这个文件,还是加上dom4j.jar,防止报错.

二、使用junit进行操作excel测试

首先明确excel工作簿对象、工作表对象、行对象、以及单元格对象。

具体代码如下注意要分清楚究竟是2007版本以前,还是2007版本以后(包括2007版本):下面这段代码是2007版本以前的:

这段代码只是将数据写入到excel文件中创建

public static void main(string[] args) throws exception {
  /**
   * 注意这只是07版本以前的做法对应的excel文件的后缀名为.xls
   * 07版本和07版本以后的做法excel文件的后缀名为.xlsx
   */
  //创建新工作簿
  hssfworkbook workbook = new hssfworkbook();
  //新建工作表
  hssfsheet sheet = workbook.createsheet("hello");
  //创建行,行号作为参数传递给createrow()方法,第一行从0开始计算
  hssfrow row = sheet.createrow(0);
  //创建单元格,row已经确定了行号,列号作为参数传递给createcell(),第一列从0开始计算
  hssfcell cell = row.createcell(2);
  //设置单元格的值,即c1的值(第一行,第三列)
  cell.setcellvalue("hello sheet");
  //输出到磁盘中
  fileoutputstream fos = new fileoutputstream(new file("e:\\root\\sheet\\11.xls"));
  workbook.write(fos);
  workbook.close();
  fos.close();
 }

结果如下图：

同样也可以对读取excel文件,得到excel文件的数据，并将其打印出来，代码如下：

@test
 public void testreadexcel() throws exception
 {
  //创建输入流
  fileinputstream fis = new fileinputstream(new file("e:\\root\\sheet\\11.xls"));
  //通过构造函数传参
  hssfworkbook workbook = new hssfworkbook(fis);
  //获取工作表
  hssfsheet sheet = workbook.getsheetat(0);
  //获取行,行号作为参数传递给getrow方法,第一行从0开始计算
  hssfrow row = sheet.getrow(0);
  //获取单元格,row已经确定了行号,列号作为参数传递给getcell,第一列从0开始计算
  hssfcell cell = row.getcell(2);
  //设置单元格的值,即c1的值(第一行,第三列)
  string cellvalue = cell.getstringcellvalue();
  system.out.println("第一行第三列的值是"+cellvalue);
  workbook.close();
  fis.close();
 }

结果如下图：

上面操作的都是07版本以前的excel文件,即后缀名为.xls,07和07版本以后的excel文件后缀名为.xlsx相应的工作簿的对象名也改为:

//创建工作簿
xssfworkbook workbook = new xssfworkbook();

代码如下,创建excel文件并保存数据到excel文件:

@test
 public void write07() throws exception
 {
  //创建工作簿
  xssfworkbook workbook = new xssfworkbook();
  //新建工作表
  xssfsheet sheet = workbook.createsheet("hello");
  //创建行,0表示第一行
  xssfrow row = sheet.createrow(0);
  //创建单元格行号由row确定,列号作为参数传递给createcell;第一列从0开始计算
  xssfcell cell = row.createcell(2);
  //给单元格赋值
  cell.setcellvalue("hello sheet");
  //创建输出流
  fileoutputstream fos = new fileoutputstream(new file("e:\\root\\sheet\\hello.xlsx"));
  workbook.write(fos);
  workbook.close();
  fos.close();
 }

与之对应的读取数据,代码如下:

@test
 public void read07() throws exception
 {
  //创建输入流
  fileinputstream fis = new fileinputstream(new file("e:\\root\\sheet\\hello.xlsx"));
  //由输入流得到工作簿
  xssfworkbook workbook = new xssfworkbook(fis);
  //得到工作表
  xssfsheet sheet = workbook.getsheet("hello");
  //得到行,0表示第一行
  xssfrow row = sheet.getrow(0);
  //创建单元格行号由row确定,列号作为参数传递给createcell;第一列从0开始计算
  xssfcell cell = row.getcell(2);
  //给单元格赋值
  string cellvalue = cell.getstringcellvalue();
  system.out.println("c1的值是"+cellvalue);
  int a[][] = new int[10][30];
  for(int i=0;i<a.length;i++)
  {
   system.out.println(i);
  }
  workbook.close();
  fis.close();
 }

问题出现了,也可以解释为需求:当不能确定究竟是读取07以前(例如2003,95,97,2000)还是07版本以后的excel文件,我们当然希望程序能够自动识别,并创建相应的对象,去操作excel文件,代码如下:

@test
 public void reda03and07() throws exception
 {
  //读取03或07的版本
  string filepath = "e:\\root\\sheet\\hello.xlsx";
  if(filepath.matches("^.+\\.(?i)((xls)|(xlsx))$"))
  {
   fileinputstream fis = new fileinputstream(filepath);
   boolean is03excell = filepath.matches("^.+\\.(?i)(xls)$")?true:false;
   workbook workbook = is03excell ? new hssfworkbook(fis):new xssfworkbook(fis);
   sheet sheet = workbook.getsheetat(0);
   row row = sheet.getrow(0);
   cell cell = row.getcell(2);
   system.out.println("第一行第一列的数据是:"+cell.getstringcellvalue());
  }
 }

学完了上面几个例子,接下来就是应用它了,我们经常需要在一个页面中批量导出和批量导出数据,这里就涉及到对excel文件的操作,当然还有其它的文件格式,我们使用一个llist<user> list 来保存,在这里我们写了一个excelutil这个工具类:代码如下:

package com.ittax.core.util;
import java.util.list;
import javax.servlet.servletoutputstream;
import org.apache.poi.hssf.usermodel.hssfcell;
import org.apache.poi.hssf.usermodel.hssfcellstyle;
import org.apache.poi.hssf.usermodel.hssffont;
import org.apache.poi.hssf.usermodel.hssfheader;
import org.apache.poi.hssf.usermodel.hssfrow;
import org.apache.poi.hssf.usermodel.hssfsheet;
import org.apache.poi.hssf.usermodel.hssfworkbook;
import org.apache.poi.ss.util.cellrangeaddress;
import com.ittax.nsfw.user.entity.user;
/**
 * excel工具类,支持批量导出
 * @author lizewu
 *
 */
public class excelutil {
  /**
   * 将用户的信息导入到excel文件中去
   * @param userlist 用户列表
   * @param out 输出表
   */
  public static void exportuserexcel(list<user> userlist,servletoutputstream out)
  {
    try{
      //1.创建工作簿
      hssfworkbook workbook = new hssfworkbook();
      //1.1创建合并单元格对象
      cellrangeaddress callrangeaddress = new cellrangeaddress(0,0,0,4);//起始行,结束行,起始列,结束列
      //1.2头标题样式
      hssfcellstyle headstyle = createcellstyle(workbook,(short)16);
      //1.3列标题样式
      hssfcellstyle colstyle = createcellstyle(workbook,(short)13);
      //2.创建工作表
      hssfsheet sheet = workbook.createsheet("用户列表");
      //2.1加载合并单元格对象
      sheet.addmergedregion(callrangeaddress);
      //设置默认列宽
      sheet.setdefaultcolumnwidth(25);
      //3.创建行
      //3.1创建头标题行;并且设置头标题
      hssfrow row = sheet.createrow(0);
      hssfcell cell = row.createcell(0);
      //加载单元格样式
      cell.setcellstyle(headstyle);
      cell.setcellvalue("用户列表");
      //3.2创建列标题;并且设置列标题
      hssfrow row2 = sheet.createrow(1);
      string[] titles = {"用户名","账号","所属部门","性别","电子邮箱"};
      for(int i=0;i<titles.length;i++)
      {
        hssfcell cell2 = row2.createcell(i);
        //加载单元格样式
        cell2.setcellstyle(colstyle);
        cell2.setcellvalue(titles[i]);
      }
      //4.操作单元格;将用户列表写入excel
      if(userlist != null)
      {
        for(int j=0;j<userlist.size();j++)
        {
          //创建数据行,前面有两行,头标题行和列标题行
          hssfrow row3 = sheet.createrow(j+2);
          hssfcell cell1 = row3.createcell(0);
          cell1.setcellvalue(userlist.get(j).getname());
          hssfcell cell2 = row3.createcell(1);
          cell2.setcellvalue(userlist.get(j).getaccount());
          hssfcell cell3 = row3.createcell(2);
          cell3.setcellvalue(userlist.get(j).getdept());
          hssfcell cell4 = row3.createcell(3);
          cell4.setcellvalue(userlist.get(j).isgender()?"男":"女");
          hssfcell cell5 = row3.createcell(4);
          cell5.setcellvalue(userlist.get(j).getemail());
        }
      }
      //5.输出
      workbook.write(out);
      workbook.close();
      //out.close();
    }catch(exception e)
    {
      e.printstacktrace();
    }
  }
  /**
   * 
   * @param workbook
   * @param fontsize
   * @return 单元格样式
   */
  private static hssfcellstyle createcellstyle(hssfworkbook workbook, short fontsize) {
    // todo auto-generated method stub
    hssfcellstyle style = workbook.createcellstyle();
    style.setalignment(hssfcellstyle.align_center);//水平居中
    style.setverticalalignment(hssfcellstyle.vertical_center);//垂直居中
    //创建字体
    hssffont font = workbook.createfont();
    font.setboldweight(hssffont.boldweight_bold);
    font.setfontheightinpoints(fontsize);
    //加载字体
    style.setfont(font);
    return style;
  }
}

紧接着就是在useservice中调用方法并写出exportexcel方法:

@override
  public void exportexcel(list<user> userlist, servletoutputstream out) {
    // todo auto-generated method stub
    excelutil.exportuserexcel(userlist, out);
  }
  @override
  public void importexcel(file file, string excelfilename) {
    // todo auto-generated method stub
    //1.创建输入流
    try {
      fileinputstream inputstream = new fileinputstream(file);
      boolean is03excel = excelfilename.matches("^.+\\.(?i)(xls)$");
      //1.读取工作簿
      workbook workbook = is03excel?new hssfworkbook(inputstream):new xssfworkbook(inputstream);
      //2.读取工作表
      sheet sheet = workbook.getsheetat(0);
      //3.读取行
      //判断行数大于二,是因为数据从第三行开始插入
      if(sheet.getphysicalnumberofrows() > 2)
      {
        user user = null;
        //跳过前两行
        for(int k=2;k<sheet.getphysicalnumberofrows();k++ )
        {
          //读取单元格
          row row0 = sheet.getrow(k);
          user = new user();
          //用户名
          cell cell0 = row0.getcell(0);
          user.setname(cell0.getstringcellvalue());
          //账号
          cell cell1 = row0.getcell(1);
          user.setaccount(cell1.getstringcellvalue());
          //所属部门
          cell cell2 = row0.getcell(2);
          user.setdept(cell2.getstringcellvalue());
          //设置性别
          cell cell3 = row0.getcell(3);
          boolean gender = cell3.getstringcellvalue() == "男"?true:false;
          user.setgender(gender);
          //设置手机
          string mobile = "";
          cell cell4 = row0.getcell(4);
          try {
            mobile = cell4.getstringcellvalue();
          } catch (exception e) {
            // todo auto-generated catch block
            double dmoblie = cell4.getnumericcellvalue();
            mobile = bigdecimal.valueof(dmoblie).tostring();
          }
          user.setmobile(mobile);
          //设置电子邮箱
          cell cell5 = row0.getcell(5);
          user.setemail(cell5.getstringcellvalue());
          //默认用户密码是123456
          user.setpassword("123456");
          //用户默认状态是有效
          user.setstate(user.user_state_valide);
          //保存用户
          save(user);
        }
      }
      workbook.close();
      inputstream.close();
    } catch (exception e) {
      // todo auto-generated catch block
      e.printstacktrace();
    }
  }

最后就是在action中调用service方法:

//导出用户列表
    public void exportexcel()
    {
      try
      {
        //1.查找用户列表
        userlist = userservice.findobjects();
        //2.导出
        httpservletresponse response = servletactioncontext.getresponse();
        //这里设置的文件格式是application/x-excel
        response.setcontenttype("application/x-excel");
        response.setheader("content-disposition", "attachment;filename=" + new string("用户列表.xls".getbytes(), "iso-8859-1"));
        servletoutputstream outputstream = response.getoutputstream();
        userservice.exportexcel(userlist, outputstream);
        if(outputstream != null)
          outputstream.close();
      }catch(exception e)
      {
        e.printstacktrace();
      }
    }
    public string importexcel()
    {
      if(userexcel!= null)
      {
        //判断是否是excel文件
        if(userexcelfilename.matches("^.+\\.(?i)((xls)|(xlsx))$"))
        {
          userservice.importexcel(userexcel, userexcelfilename);
        }
      }
      return"list";
    }

注意的是应该使用servletoutputstream这个类,最后实现了批量导出和导入数据。

导出用户结果如下图;

导入结果如下图;

导入前:

导入后的结果;

ok,关于poi操作excel文件就暂时到此为止了

以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，同时也希望多多支持！