java使用POI操作excel文件

更新時間：2017年03月23日 14:04:41 投稿：sxyy

本文主要介紹了java使用POI操作excel文件,實現(xiàn)批量導(dǎo)出和導(dǎo)入的方法。具有很好的參考價值。下面跟著小編一起來看下吧

一、POI的定義

JAVA中操作Excel的有兩種比較主流的工具包： JXL 和 POI 。jxl 只能操作Excel 95, 97, 2000也即以.xls為后綴的excel。而poi可以操作Excel 95及以后的版本，即可操作后綴為 .xls 和 .xlsx兩種格式的excel。

POI全稱 Poor Obfuscation Implementation,直譯為“可憐的模糊實現(xiàn)”，利用POI接口可以通過JAVA操作Microsoft office 套件工具的讀寫功能。官網(wǎng)：http://poi.apache.org ，POI支持office的所有版本，首先去官網(wǎng)下載如下界面：

下載完后，打開“poi-bin-3.15-20160924.tar.gz”獲取操作excel需要的jar包，并將這些jar包復(fù)制到項目中。對于只操作2003 及以前版本的excel，只需要poi-3.15.jar ，如果需要同時對2007及以后版本進(jìn)行操作則需要復(fù)制

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

以及復(fù)制在ooxml-lib目錄下的xmlbeans-2.6.0.jar(但不知為何,我下的jar文件中沒有dom4j.jar)這個文件,還是加上dom4j.jar,防止報錯.

二、使用junit進(jìn)行操作Excel測試

首先明確Excel工作簿對象、工作表對象、行對象、以及單元格對象。

具體代碼如下注意要分清楚究竟是2007版本以前,還是2007版本以后(包括2007版本):下面這段代碼是2007版本以前的:

這段代碼只是將數(shù)據(jù)寫入到Excel文件中創(chuàng)建

public static void main(String[] args) throws Exception {
  /**
   * 注意這只是07版本以前的做法對應(yīng)的excel文件的后綴名為.xls
   * 07版本和07版本以后的做法excel文件的后綴名為.xlsx
   */
  //創(chuàng)建新工作簿
  HSSFWorkbook workbook = new HSSFWorkbook();
  //新建工作表
  HSSFSheet sheet = workbook.createSheet("hello");
  //創(chuàng)建行,行號作為參數(shù)傳遞給createRow()方法,第一行從0開始計算
  HSSFRow row = sheet.createRow(0);
  //創(chuàng)建單元格,row已經(jīng)確定了行號,列號作為參數(shù)傳遞給createCell(),第一列從0開始計算
  HSSFCell cell = row.createCell(2);
  //設(shè)置單元格的值,即C1的值(第一行,第三列)
  cell.setCellValue("hello sheet");
  //輸出到磁盤中
  FileOutputStream fos = new FileOutputStream(new File("E:\\root\\sheet\\11.xls"));
  workbook.write(fos);
  workbook.close();
  fos.close();
 }

結(jié)果如下圖：

同樣也可以對讀取Excel文件,得到Excel文件的數(shù)據(jù)，并將其打印出來，代碼如下：

@Test
 public void testReadExcel() throws Exception
 {
  //創(chuàng)建輸入流
  FileInputStream fis = new FileInputStream(new File("E:\\root\\sheet\\11.xls"));
  //通過構(gòu)造函數(shù)傳參
  HSSFWorkbook workbook = new HSSFWorkbook(fis);
  //獲取工作表
  HSSFSheet sheet = workbook.getSheetAt(0);
  //獲取行,行號作為參數(shù)傳遞給getRow方法,第一行從0開始計算
  HSSFRow row = sheet.getRow(0);
  //獲取單元格,row已經(jīng)確定了行號,列號作為參數(shù)傳遞給getCell,第一列從0開始計算
  HSSFCell cell = row.getCell(2);
  //設(shè)置單元格的值,即C1的值(第一行,第三列)
  String cellValue = cell.getStringCellValue();
  System.out.println("第一行第三列的值是"+cellValue);
  workbook.close();
  fis.close();
 }

結(jié)果如下圖：

上面操作的都是07版本以前的Excel文件,即后綴名為.xls,07和07版本以后的Excel文件后綴名為.xlsx相應(yīng)的工作簿的對象名也改為:

//創(chuàng)建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();

代碼如下,創(chuàng)建excel文件并保存數(shù)據(jù)到excel文件:

@Test
 public void write07() throws Exception
 {
  //創(chuàng)建工作簿
  XSSFWorkbook workbook = new XSSFWorkbook();
  //新建工作表
  XSSFSheet sheet = workbook.createSheet("hello");
  //創(chuàng)建行,0表示第一行
  XSSFRow row = sheet.createRow(0);
  //創(chuàng)建單元格行號由row確定,列號作為參數(shù)傳遞給createCell;第一列從0開始計算
  XSSFCell cell = row.createCell(2);
  //給單元格賦值
  cell.setCellValue("hello sheet");
  //創(chuàng)建輸出流
  FileOutputStream fos = new FileOutputStream(new File("E:\\root\\sheet\\hello.xlsx"));
  workbook.write(fos);
  workbook.close();
  fos.close();
 }

與之對應(yīng)的讀取數(shù)據(jù),代碼如下:

@Test
 public void read07() throws Exception
 {
  //創(chuàng)建輸入流
  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);
  //創(chuàng)建單元格行號由row確定,列號作為參數(shù)傳遞給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();
 }

問題出現(xiàn)了,也可以解釋為需求:當(dāng)不能確定究竟是讀取07以前(例如2003,95,97,2000)還是07版本以后的Excel文件,我們當(dāng)然希望程序能夠自動識別,并創(chuàng)建相應(yīng)的對象,去操作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("第一行第一列的數(shù)據(jù)是:"+cell.getStringCellValue());
  }
 }

學(xué)完了上面幾個例子,接下來就是應(yīng)用它了,我們經(jīng)常需要在一個頁面中批量導(dǎo)出和批量導(dǎo)出數(shù)據(jù),這里就涉及到對excel文件的操作,當(dāng)然還有其它的文件格式,我們使用一個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工具類,支持批量導(dǎo)出
 * @author lizewu
 *
 */
public class ExcelUtil {
  /**
   * 將用戶的信息導(dǎo)入到excel文件中去
   * @param userList 用戶列表
   * @param out 輸出表
   */
  public static void exportUserExcel(List<User> userList,ServletOutputStream out)
  {
    try{
      //1.創(chuàng)建工作簿
      HSSFWorkbook workbook = new HSSFWorkbook();
      //1.1創(chuàng)建合并單元格對象
      CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,4);//起始行,結(jié)束行,起始列,結(jié)束列
      //1.2頭標(biāo)題樣式
      HSSFCellStyle headStyle = createCellStyle(workbook,(short)16);
      //1.3列標(biāo)題樣式
      HSSFCellStyle colStyle = createCellStyle(workbook,(short)13);
      //2.創(chuàng)建工作表
      HSSFSheet sheet = workbook.createSheet("用戶列表");
      //2.1加載合并單元格對象
      sheet.addMergedRegion(callRangeAddress);
      //設(shè)置默認(rèn)列寬
      sheet.setDefaultColumnWidth(25);
      //3.創(chuàng)建行
      //3.1創(chuàng)建頭標(biāo)題行;并且設(shè)置頭標(biāo)題
      HSSFRow row = sheet.createRow(0);
      HSSFCell cell = row.createCell(0);
      //加載單元格樣式
      cell.setCellStyle(headStyle);
      cell.setCellValue("用戶列表");
      //3.2創(chuàng)建列標(biāo)題;并且設(shè)置列標(biāo)題
      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++)
        {
          //創(chuàng)建數(shù)據(jù)行,前面有兩行,頭標(biāo)題行和列標(biāo)題行
          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);//垂直居中
    //創(chuàng)建字體
    HSSFFont font = workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints(fontsize);
    //加載字體
    style.setFont(font);
    return style;
  }
}

緊接著就是在UseService中調(diào)用方法并寫出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.創(chuàng)建輸入流
    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.讀取行
      //判斷行數(shù)大于二,是因為數(shù)據(jù)從第三行開始插入
      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());
          //設(shè)置性別
          Cell cell3 = row0.getCell(3);
          boolean gender = cell3.getStringCellValue() == "男"?true:false;
          user.setGender(gender);
          //設(shè)置手機(jī)
          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);
          //設(shè)置電子郵箱
          Cell cell5 = row0.getCell(5);
          user.setEmail(cell5.getStringCellValue());
          //默認(rèn)用戶密碼是123456
          user.setPassword("123456");
          //用戶默認(rèn)狀態(tài)是有效
          user.setState(User.USER_STATE_VALIDE);
          //保存用戶
          save(user);
        }
      }
      workbook.close();
      inputStream.close();
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }

最后就是在Action中調(diào)用service方法:

//導(dǎo)出用戶列表
    public void exportExcel()
    {
      try
      {
        //1.查找用戶列表
        userList = userService.findObjects();
        //2.導(dǎo)出
        HttpServletResponse response = ServletActionContext.getResponse();
        //這里設(shè)置的文件格式是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";
    }

注意的是應(yīng)該使用ServletOutputStream這個類,最后實現(xiàn)了批量導(dǎo)出和導(dǎo)入數(shù)據(jù)。

導(dǎo)出用戶結(jié)果如下圖;