java导出数据到Excel03和07

Java 操作 Excel

关于Java操作Excel文件，最近在网上经常看到有网友寻求这方面的资料，在这里我简单介绍下我对这方面的了解

一、  读取Excel文件，现在比较流行的第三方jar包有apache的poi和另一个jar包jxl

1先见poi，这个是目前做的最突出的一个操作Excel文件的工具包，支持Excel03、Excel07版，目前最高的版本是3.8，需要下载的工具包有：poi-3.8-20120326.jar,poi-ooxml-3.8-20120323.jar,

poi-ooxml-schemas-3.8-20120326.jar,xbean.jar,dom4j.jar

如果单纯操作Excel03的话，那可以只下载poi-3.8-20120326.jar，后面几个jar包是为Excel07服务的，这是由于Excel07的文件存储结构导致的

以下是我封装好的poi操作Excel03的代码：

package org.gdupt.poiexcel;

 

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

 

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.gdupt.exceptions.SampleException;

import org.gdupt.interfaces.RExcel;

 

public class ReadExcel03 implements RExcel{

    private InputStream is ;

    private HSSFWorkbook wb;

    private HSSFSheet[] sheets;

    private HSSFSheet sheet;

    private HSSFRow row;

    private int sheetNum;

    private int rowNum;

    private int colNum;

   

    public ReadExcel03(String path) {

       if(!path.trim().toLowerCase().endsWith(".xls")) {

           throw new SampleException("不是有效的2003Excel文件");

       }

       if(checkFile(path)) {

           try {

              is = new FileInputStream(path);

              wb = new HSSFWorkbook(is);

              initSheet();

           } catch (FileNotFoundException e) {

              throw new SampleException("读取的目标文件不存在");

           } catch (IOException e) {

              throw new SampleException("打开Excel文件失败");

           }

       } else {

           throw new SampleException("无法使用在其他进程或者程序已经打开的文件");

       }

      

    }

   

    public void initSheet() {//初始化Excel文件的信息

       sheetNum = wb.getNumberOfSheets();

       sheets = new HSSFSheet[sheetNum];

       for(int i = 0 ; i < sheetNum; i++) {

           sheets[i] = wb.getSheetAt(i);

       }

    }

   

    public void setSheet(int index) {//设置待操作的工作页

       if(sheets == null || sheetNum <= index) {

           throw new SampleException("无法获取无效的工作页");

       }

       sheet = sheets[index];

       rowNum = getRowNum();

       colNum = getColNum();

    }

   

    public int getRowNum() {

       if(rowNum != 0)

           return rowNum;

       if(sheet != null) {

           rowNum = getRowNum(sheet);

           return rowNum;

       } else {

           throw new SampleException("无法获取无效的工作页的总行数");

       }

    }

   

    public int getColNum() {//获取指定工作页面的列数

       if(sheet == null) {

           throw new SampleException("未指定操作的工作页");

       }

       if(colNum != 0)

           return colNum;

       HSSFRow row = sheet.getRow(0);

       this.colNum = getCellNum(row);

       return colNum;

    }

   

    public int getCellNum(HSSFRow row) {

       int first = row.getFirstCellNum();

       int last = row.getLastCellNum();

       int cellNum = last - first;

       return cellNum;

    }

   

    public int getRowNum(HSSFSheet sheet) {//获取总行数

       int first = sheet.getFirstRowNum();

       int last = sheet.getLastRowNum() ;

       int rowCount = last-first+1;

       return rowCount;

    }

   

    public void setRow(int index) {//选中指定行数据

       if(sheet != null) {

           if(index > rowNum) {

              throw new SampleException("指定获取的行"+index+"超出了最大行数"+rowNum);

           }

           row = sheet.getRow(index);

       } else {

           throw new SampleException("未指定操作的工作页");

       }

    }

 

    public boolean checkFile(String path) {//检查文件是否有其他程序或者进程在使用

       boolean result = false;

       File file = new File(path);

       if(!file.exists()) {

           throw new SampleException("指定操作的目标文件不存在");

       } else {

           File nFile = new File(path);

           result = file.renameTo(nFile);

       }

       return result;

    }

   

    public String[] getRowValues(int index) {//获取一行的值

       return getCellValues(index, 0);

    }

   

    public String[] getCellValues(int index, int col) {

       List values = new ArrayList();

       if(sheet != null) {

           if(index > rowNum) {

              throw new SampleException("指定获取的行"+index+"超出了最大行数"+rowNum);

           }

           setRow(index);

           if(col == 0)

              col = colNum;

           for(int i = 0 ; i <  col; i++) {

              values.add(getCellToString(i));

           }

           row = null;

           return values.toArray(new String[col]);

       } else {

           throw new SampleException("未指定操作的工作页");

       }

    }

   

    public String getCellToString(int i) {//获取指定单元格的内容

       if(i > colNum) {

           throw new SampleException("请求获取的单元格不存在");

       }

      

       HSSFCell cell = row.getCell(i);

       String str = getCellFormatValue(cell);

       return str;

    }

   

     

    private String getCellFormatValue(HSSFCell cell) {

        String cellvalue = "";

        if (cell != null) {

            // 判断当前Cell的Type

            switch (cell.getCellType()) {

            // 如果当前Cell的Type为NUMERIC

            case HSSFCell.CELL_TYPE_NUMERIC:

            case HSSFCell.CELL_TYPE_FORMULA:

                // 判断当前的cell是否为Date

                if (HSSFDateUtil.isCellDateFormatted(cell)) {

                    // 如果是Date类型则，转化为Data格式

                    Date date = cell.getDateCellValue();

                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

                    cellvalue = sdf.format(date);

                   

                }

                else { // 如果是纯数字；取得当前Cell的数值

                    cellvalue = String.valueOf(cell.getNumericCellValue());

                    if(cellvalue.endsWith(".0")) {

                    cellvalue = cellvalue.replace(".0", "");

                    }

                }

                break;

            

            // 如果当前Cell的Type为STRING，取得当前的Cell字符串

            case HSSFCell.CELL_TYPE_STRING:

                cellvalue = cell.getRichStringCellValue().getString();

                break;

            case HSSFCell.CELL_TYPE_BOOLEAN:

              boolean comment = cell.getBooleanCellValue();

              cellvalue = comment?"Y":"N";

              break;

            // 默认的Cell值

            default:

                cellvalue = "";

            }

        } else {

            cellvalue = "";

        }

        return cellvalue;

 

    }

   

   

    public static void main(String[] args) {

       String path = "C:\\Users\\Administrator\\Desktop\\test.xls";

       ReadExcel03 excel = new ReadExcel03(path);

       excel.setSheet(0);

       System.out.println("总行数：" + excel.getRowNum());

       System.out.println("总列数：" + excel.getColNum());

       for(int i = 0 ; i < excel.getRowNum() ; i++) {

           String[] values = excel.getRowValues(i);

           for(int j = 0 ; j < values.length ; j++) {

              System.out.print(values[j] + " ");

               if(j == values.length-1)

                  System.out.println();

           }

       }

    }

   

    public void close() {

       try {

           if(is != null) {

              is.close();

           }

       } catch (IOException e) {

           e.printStackTrace();

       }

    }

 

    @Override

    public int getSheetNum() {

       if(sheets != null)

           return sheets.length;

       else

           throw new SampleException("无效的Excel文件");

    }

 

    @Override

    public List getCellValues(int index) {

       return null;

    }

}

在使用的使用初始化类后要先调用setSheet方法，不然是无法继续操作的

接着是操作Excel07的，其实都大同小异：

package org.gdupt.poiexcel;

 

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.List;

 

import org.apache.poi.hssf.usermodel.HSSFDateUtil;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.gdupt.exceptions.SampleException;

import org.gdupt.interfaces.RExcel;

 

public class ReadExcel07 implements RExcel{

   private InputStream is ;

   private XSSFWorkbook wb;

   private XSSFSheet[] sheets;

   private XSSFSheet sheet;

   private XSSFRow row;

   private int sheetNum;

   private int rowNum;

   private int colNum;

  

   public ReadExcel07(String path) {

      if(!path.trim().toLowerCase().endsWith(".xlsx")) {

        throw new SampleException("不是有效的2007Excel文件");

      }

      if(checkFile(path)) {

        try {

           is = new FileInputStream(path);

           wb = new XSSFWorkbook(is);

          

           initSheet();

        } catch (FileNotFoundException e) {

           throw new SampleException("读取的目标文件不存在");

        } catch (IOException e) {

           throw new SampleException("打开Excel文件失败");

        }

      } else {

        throw new SampleException("无法使用在其他进程或者程序已经打开的文件");

      }

     

   }

  

   public void initSheet() {

      sheetNum = wb.getNumberOfSheets();

      sheets = new XSSFSheet[sheetNum];

      for(int i = 0 ; i < sheetNum; i++) {

        sheets[i] = wb.getSheetAt(i);

      }

   }

  

   public void setSheet(int index) {

      if(sheets == null || sheetNum <= index) {

        throw new SampleException("无法获取无效的工作页");

      }

      sheet = sheets[index];

      rowNum = getRowNum();

      colNum = getColNum();

   }

  

   public int getRowNum() {

      if(rowNum != 0)

        return rowNum;

      if(sheet != null) {

        rowNum = getRowNum(sheet);

        return rowNum;

      } else {

        throw new SampleException("无法获取无效的工作页的总行数");

      }

   }

  

   public int getColNum() {//获取指定工作页面的列数

      if(sheet == null) {

        throw new SampleException("未指定操作的工作页");

      }

      if(colNum != 0)

        return colNum;

      XSSFRow row = sheet.getRow(0);

      this.colNum = getCellNum(row);

      return colNum;

   }

  

   public int getCellNum(XSSFRow row) {

      int first = row.getFirstCellNum();

      int last = row.getLastCellNum();

      int cellNum = last - first;

      return cellNum;

   }

  

   public int getRowNum(XSSFSheet sheet) {

      int first = sheet.getFirstRowNum();

      int last = sheet.getLastRowNum() ;

      int rowCount = last-first+1;

      return rowCount;

   }

  

   public void setRow(int index) {

      if(sheet != null) {

        if(index > rowNum) {

           throw new SampleException("指定获取的行"+index+"超出了最大行数"+rowNum);

        }

        row = sheet.getRow(index);

      } else {

        throw new SampleException("未指定操作的工作页");

      }

   }

 

   public boolean checkFile(String path) {

      boolean result = false;

      File file = new File(path);

      if(!file.exists()) {

        throw new SampleException("指定操作的目标文件不存在");

      } else {

        File nFile = new File(path);

        result = file.renameTo(nFile);

      }

      return result;

   }

  

   public String[] getRowValues(int index) {//获取一行的值

      return getCellValues(index, 0);

   }

  

   public String[] getCellValues(int index, int col) {

      List values = new ArrayList();

      if(sheet != null) {

        if(index > rowNum) {

           throw new SampleException("指定获取的行"+index+"超出了最大行数"+rowNum);

        }

        setRow(index);

        if(col == 0)

           col = colNum;

        for(int i = 0 ; i <  col; i++) {

           values.add(getCellToString(i));

        }

        row = null;

        return values.toArray(new String[col]);

      } else {

        throw new SampleException("未指定操作的工作页");

      }

   }

  

   public String getCellToString(int i) {//获取指定单元格的内容

      if(i > colNum) {

        throw new SampleException("请求获取的单元格不存在");

      }

     

      short index = (short) i;

      XSSFCell cell = row.getCell(index);

      String str = getCellFormatValue(cell);

      return str;

   }

  

    

    private String getCellFormatValue(XSSFCell cell) {

        String cellvalue = "";

        if (cell != null) {

            // 判断当前Cell的Type

            switch (cell.getCellType()) {

            // 如果当前Cell的Type为NUMERIC

            case XSSFCell.CELL_TYPE_NUMERIC:

            case XSSFCell.CELL_TYPE_FORMULA:

                // 判断当前的cell是否为Date

                if (HSSFDateUtil.isCellDateFormatted(cell)) {

                    // 如果是Date类型则，转化为Data格式

                    Date date = cell.getDateCellValue();

                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

                    cellvalue = sdf.format(date);

                   

                }

                else { // 如果是纯数字；取得当前Cell的数值

                    cellvalue = String.valueOf(cell.getNumericCellValue());

                    if(cellvalue.endsWith(".0")) {

                      cellvalue = cellvalue.replace(".0", "");

                    }

                }

                break;

           

            // 如果当前Cell的Type为STRING，取得当前的Cell字符串

            case XSSFCell.CELL_TYPE_STRING:

                cellvalue = cell.getRichStringCellValue().getString();

                break;

            case XSSFCell.CELL_TYPE_BOOLEAN:

              boolean comment = cell.getBooleanCellValue();

              cellvalue = comment?"Y":"N";

              break;

            // 默认的Cell值

            default:

                cellvalue = "";

            }

        } else {

            cellvalue = "";

        }

        return cellvalue;

 

    }

   

  

   public static void main(String[] args) {

      String path = "C:\\Users\\Administrator\\Desktop\\test.xlsx";

      ReadExcel07 excel = new ReadExcel07(path);

      excel.setSheet(0);

      System.out.println("总行数：" + excel.getRowNum());

      System.out.println("总列数：" + excel.getColNum());

      for(int i = 0 ; i < excel.getRowNum() ; i++) {

        String[] values = excel.getRowValues(i);

        for(int j = 0 ; j < values.length ; j++) {

           System.out.print(values[j] + " ");

           if(j == values.length-1)

              System.out.println();

        }

      }

   }

  

   public void close() {

      try {

        if(is != null) {

           is.close();

        }

      } catch (IOException e) {

        e.printStackTrace();

      }

   }

 

   @Override

   public int getSheetNum() {

      if(sheets != null)

        return sheets.length;

      else

        throw new SampleException("无效的Excel文件");

   }

 

   @Override

   public List getCellValues(int index) {

      return null;

   }

 

 

}代码几乎一样，唯一不同的是所使用的对象的区别，Excel03使用到的对象是HSSF开头的，而Excel07使用的是XSSF开头的

这两个类都有自带一个main方法可用于测试，这是读取excel文件的，希望对各位有一定的帮助

二、  接来下将以下jxl的操作，需要下载jxl.jar包，不过目前好像已经没有团队在维护该jar包了，而且该工具包不支持excel07的操作

package org.gdupt.jxlexcel;

 

import java.io.File;

import java.io.IOException;

import java.io.InputStream;

import java.util.Arrays;

import java.util.List;

import org.gdupt.interfaces.RExcel;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

 

public class ReadExcel implements RExcel{

   private Workbook wb;

   private Sheet[] sheets;

   private Sheet sheet;

   private int rowCount;

   private int colCount;

  

   public ReadExcel(InputStream is) {

      try {

        wb = Workbook.getWorkbook(is);

        sheets = wb.getSheets();

      } catch (BiffException e) {

        e.printStackTrace();

      } catch (IOException e) {

        e.printStackTrace();

      }

   }

  

   public Workbook getWorkbook() {

      return wb;

   }

  

   public ReadExcel(String filePath) {

      File file = new File(filePath);

      init(file);

   }

   public ReadExcel(File file) {

      init(file);

   }

   private void init(File file) {

      try {

        wb = Workbook.getWorkbook(file);

        sheets = wb.getSheets();

      } catch (BiffException e) {

        e.printStackTrace();

      } catch (IOException e) {

        e.printStackTrace();

      }

   }

  

   public void setSheet(int index) {

      if(index >= sheets.length)

        throw new IndexOutOfBoundsException("不存在该页数");

      sheet = sheets[index];

      rowCount = sheet.getRows();

      colCount = sheet.getColumns();

   }

  

   public int getSheetNum() {

      if(sheets == null || sheets.length==0)

        throw new NullPointerException("该文件没有工作页面");

      return sheets.length;

   }

   public int getRowNum() {

      if(sheet == null)

        throw new NullPointerException("未设定使用工作页面");

      if(rowCount <= 0)

        rowCount = sheet.getRows();

      return rowCount;

   }

   public int getColNum() {

      if(sheet == null)

        throw new NullPointerException("未设定使用工作页面");

      if(colCount <= 0)

        colCount = sheet.getColumns();

      return colCount;

   }

  

   public String[] getRowValues(int index) {

      Listvalues = getCellValues(index);

      int length = values.size();

      return values.toArray(new String[length]);

   }

  

   public List getCellValues(int index) {

      String[] v = getCellValues(index, 0);

      Listvalues = Arrays.asList(v);

      return values;

   }

  

   public String[] getCellValues(int index, int count) {

      if(sheet == null)

        throw new NullPointerException("未设定使用工作页面");

      if(index > getRowNum())

        throw new IndexOutOfBoundsException("不存在操作行");

      Cell[] cells = sheet.getRow(index);

      if(count == 0)

        count = cells.length;

      String[] values = new String[count];

      for(int i = 0 ; i < count ; i++) {

        values[i] = cells[i].getContents();

      }

      return values;

   }

   public void close(){

      wb.close();

      wb = null;

   }

}

不过相对起来，jxl用于操作excel03就方便多了