Java *** 作Excel表(一) 首次源码分析和初次读写 *** 作

Java *** 作Excel表(一) 首次源码分析和初次读写 *** 作 Java *** 作Excel表(一) 首次源码分析和初次读写 *** 作 前言

       前段时间一直想着用Python来 *** 作Excel表，但是发现自己由于长期使用Java,C++这种语法类型的语言后，实在是难以适应Python的语法,最后决定还是来用Java *** 作。在网上看到有一些jar包可以实现，于是我就去Maven网站上找了一下。导下依赖吧。

       由于我也是第一次接触这个jar包,所以今天想先看一下源码和它的功能再来研究怎么使用。如果理解错了还望指出，多多交流。^_^

       不想看源码想捡现成的可以直接翻到最下面^_^

       看源码最快也最直接的方式就是把注释和方法名称，变量名称翻译了。英语不好的同学最好建议下载一个有道词典

        
            org.apache.poi
            poi-ooxml
            4.0.1
        
        
            net.sourceforge.jexcelapi
            jxl
            2.6.12
        

Workbook类

       英文意思是(计算机上面的工作簿)。这个类表示工作簿。包含各种工厂方法并提供提供对工作表访问的各种访问器。

public abstract class Workbook
{
  
  private static final String VERSION = "2.6.12";
  
  protected Workbook() { }

  
  public abstract Sheet[] getSheets();

  
  public abstract String[] getSheetNames();

  
  public abstract Sheet getSheet(int index)
    throws IndexOutOfBoundsException;

  
  public abstract Sheet getSheet(String name);

  
  public static String getVersion()
  {
    return VERSION;
  }

  
  public abstract int getNumberOfSheets();

  
  public abstract Cell findCellByName(String name);

  
  public abstract Cell getCell(String loc);

  
  public abstract Range[] findByName(String name);

  
  public abstract String[] getRangeNames();


  
  public abstract boolean isProtected();

  
  protected abstract void parse() throws BiffException, PasswordException;

  
  public abstract void close();

  
  public static Workbook getWorkbook(java.io.File file)
    throws IOException, BiffException
  {
    return getWorkbook(file, new WorkbookSettings());
  }

  
  public static Workbook getWorkbook(java.io.File file, WorkbookSettings ws)
    throws IOException, BiffException
  {
    FileInputStream fis = new FileInputStream(file);
		//始终关闭输入流，不管是否
		//文件可以被解析感谢Steve Hahn
    File dataFile = null;

    try
    {
      dataFile = new File(fis, ws);
    }
    catch (IOException e)
    {
      fis.close();
      throw e;
    }
    catch (BiffException e)
    {
      fis.close();
      throw e;
    }

    fis.close();

    Workbook workbook = new WorkbookParser(dataFile, ws);
    workbook.parse();

    return workbook;
  }

  
  public static Workbook getWorkbook(InputStream is)
    throws IOException, BiffException
  {
    return getWorkbook(is, new WorkbookSettings());
  }

  
  public static Workbook getWorkbook(InputStream is, WorkbookSettings ws)
    throws IOException, BiffException
  {
    File dataFile = new File(is, ws);

    Workbook workbook = new WorkbookParser(dataFile, ws);
    workbook.parse();

    return workbook;
  }

  
  public static WritableWorkbook createWorkbook(java.io.File file)
    throws IOException
  {
    return createWorkbook(file, new WorkbookSettings());
  }

  
  public static WritableWorkbook createWorkbook(java.io.File file,
                                                WorkbookSettings ws)
    throws IOException
  {
    FileOutputStream fos = new FileOutputStream(file);
    WritableWorkbook w = new WritableWorkbookImpl(fos, true, ws);
    return w;
  }

  
  public static WritableWorkbook createWorkbook(java.io.File file,
                                                Workbook in)
    throws IOException
  {
    return createWorkbook(file, in, new WorkbookSettings());
  }

  
  public static WritableWorkbook createWorkbook(java.io.File file,
                                                Workbook in,
                                                WorkbookSettings ws)
    throws IOException
  {
    FileOutputStream fos = new FileOutputStream(file);
    WritableWorkbook w = new WritableWorkbookImpl(fos, in, true, ws);
    return w;
  }

  
  public static WritableWorkbook createWorkbook(OutputStream os,
                                                Workbook in)
    throws IOException
  {
    return createWorkbook(os, in, ((WorkbookParser) in).getSettings());
  }

  
  public static WritableWorkbook createWorkbook(OutputStream os,
                                                Workbook in,
                                                WorkbookSettings ws)
    throws IOException
  {
    WritableWorkbook w = new WritableWorkbookImpl(os, in, false, ws);
    return w;
  }

  
  public static WritableWorkbook createWorkbook(OutputStream os)
    throws IOException
  {
    return createWorkbook(os, new WorkbookSettings());
  }

  
  public static WritableWorkbook createWorkbook(OutputStream os,
                                                WorkbookSettings ws)
    throws IOException
  {
    WritableWorkbook w = new WritableWorkbookImpl(os, false, ws);
    return w;
  }
}

WritableWorkbook类

       这个类的英文是 Writable Workbook "可写的工作簿",可见这个类是用来实现写 *** 作的

public abstract class WritableWorkbook{
  
  public static final WritableFont  ARIAL_10_PT =
    new WritableFont(WritableFont.ARIAL);

  
  public static final WritableFont HYPERlink_FONT =
    new WritableFont(WritableFont.ARIAL,
                     WritableFont.DEFAULT_POINT_SIZE,
                     WritableFont.NO_BOLD,
                     false,
                     UnderlineStyle.SINGLE,
                     Colour.BLUE);

  
  public static final WritableCellFormat NORMAL_STYLE =
    new WritableCellFormat(ARIAL_10_PT, NumberFormats.DEFAULT);

  
  public static final WritableCellFormat HYPERlink_STYLE =
    new WritableCellFormat(HYPERlink_FONT);

  
  public static final WritableCellFormat HIDDEN_STYLE = 
    new WritableCellFormat(new DateFormat(";;;"));

  
  protected WritableWorkbook()
  {
  }

  
  public abstract WritableSheet[] getSheets();

  
  public abstract String[] getSheetNames();

  
  public abstract WritableSheet getSheet(int index)
    throws IndexOutOfBoundsException;

  
  public abstract WritableSheet getSheet(String name);

  
  public abstract WritableCell getWritableCell(String loc);

  
  public abstract int getNumberOfSheets();

  
  public abstract void close() throws IOException, WriteException;

  
  public abstract WritableSheet createSheet(String name, int index);

  
  public abstract WritableSheet importSheet(String name, int index, Sheet s);

  
  public abstract void copySheet(int s, String name, int index);

  
  public abstract void copySheet(String s, String name, int index);

  
  public abstract void removeSheet(int index);

  
  public abstract WritableSheet moveSheet(int fromIndex, int toIndex);

  
  public abstract void write() throws IOException;

  
  public abstract void setProtected(boolean prot);

  
  public abstract void setColourRGB(Colour c, int r, int g, int b);

  
  public void copy(Workbook w)
  {
    //是一个抽象的方法——将方法主体留空吗
  }

  
  public abstract WritableCell findCellByName(String name);

  
  public abstract Range[] findByName(String name);

  
  public abstract String[] getRangeNames();

  
  public abstract void removeRangeName(String name);

  
  public abstract void addNameArea(String name,
                                   WritableSheet sheet,
                                   int firstCol,
                                   int firstRow,
                                   int lastCol,
                                   int lastRow);

  
  public abstract void setOutputFile(java.io.File fileName)
    throws IOException;
}

Sheet

       表示工作簿中的工作表。为个人提供句柄单元格或单元格行(按行或列分组)

public interface Sheet
{
  
  public Cell getCell(int column, int row);

  
  public Cell getCell(String loc);

  
  public int getRows();

  
  public int getColumns();

  
  public Cell[] getRow(int row);

  
  public Cell[] getColumn(int col);

  
  public String getName();

  
  public boolean isHidden();

  
  public boolean isProtected();

  
  public Cell findCell(String contents);

  
  public Cell findCell(String contents, 
                       int firstCol, 
                       int firstRow, 
                       int lastCol, 
                       int lastRow, 
                       boolean reverse);

  
  public Cell findCell(Pattern pattern, 
                       int firstCol, 
                       int firstRow,
                       int lastCol,  
                       int lastRow, 
                       boolean reverse);

  
  public LabelCell findLabelCell(String contents);

  
  public Hyperlink[] getHyperlinks();

  
  public Range[] getMergedCells();

  
  public SheetSettings getSettings();

  
  public CellFormat getColumnFormat(int col);

  
  public int getColumnWidth(int col);

  
  public CellView getColumnView(int col);

  
  public int getRowHeight(int row);

  
  public CellView getRowView(int row);

  
  public int getNumberOfImages();

  
  public Image getDrawing(int i);

  
  public int[] getRowPageBreaks();

  
  public int[] getColumnPageBreaks();
}

源码看完了，简单 *** 作感受一下 实体类

       为了方便最后发送Json给前端，所以这里就弄个实体类,把Excel当成数据库来用

    class ExcelDo{
        private String userName;        //姓名
        private String company;         //公司
    }//记得get set

读取 *** 作

       由于读取Excel表，由于读出来的都是根据列的顺序来，为了不写一大堆的if,故使用循环来搞定。为了防止循环导致的数组越界，封装了一个辅助方法。第一次写，如果有更好的方法还望多多指教

   
  public List getExcelList() throws IOException, BiffException {
        List excelDoList = new ArrayList();                                //创建列表
        File xlsFile = new File("E:/ExcelProject/test.xls");                       //文件目录
        Workbook workbook = Workbook.getWorkbook(xlsFile);                                   //获得工作簿对象
        Sheet sheet = workbook.getSheet(0);                                            //获得工作表
        int rows = sheet.getRows();                                                          //获得行
        int cols = sheet.getColumns();                                                       //获得列
        int index = 0;
        for(int i = 0; i < rows; i++){                                                       //读取数据
            index = 0;
            ExcelDo excelDo = new ExcelDo();
            excelDo.setUserName(sheet.getCell(index,i).getContents());
            index = getIndex(index,cols);                                                   //每次添加完都调用一次
            excelDo.setCompany(sheet.getCell( index,i).getContents());
            excelDoList.add(excelDo);       //保存到列表中最后用来返回
        }
        workbook.close();
        return excelDoList;
    }

    
    private Integer getIndex(int index, int col){
        index++;
        if(index > col){
            index = col;
        }
        return index;
    }

测试

    public static void main(String[] args) throws IOException, BiffException{
        ExcelService excelService = new ExcelServiceImpl();
        List excelList = excelService.getExcelList();
        for(ExcelDo excelDo : excelList){
            System.out.println(excelDo.toString());
        }
    }

写 *** 作

    
    public void writeExcel(int row, int col,String data) throws IOException, WriteException {
        File xlsFile = new File("E:/ExcelProject/alvin.xls");
        // 创建一个工作簿
        WritableWorkbook workbook = Workbook.createWorkbook(xlsFile);
        // 创建一个工作表
        WritableSheet sheet = workbook.createSheet("Sheet1", 0);
        sheet.addCell(new Label(col, row, data));
        workbook.write();
        workbook.close();
    }

测试

    public static void main(String[] args) throws IOException, BiffException, WriteException {
        ExcelService excelService = new ExcelServiceImpl();
        excelService.writeExcel(2,2,"alvin");
    }

完整代码

package com.alvin.service.impl;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import com.alvin.domain.ExcelDo;
import com.alvin.service.ExcelService;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.Sheet;
public class ExcelServiceImpl implements ExcelService{


    
    public void writeExcel(int row, int col,String data) throws IOException, WriteException {
        File xlsFile = new File("E:/ExcelProject/alvin.xls");
        // 创建一个工作簿
        WritableWorkbook workbook = Workbook.createWorkbook(xlsFile);
        // 创建一个工作表
        WritableSheet sheet = workbook.createSheet("Sheet1", 0);
        sheet.addCell(new Label(col, row, data));
        workbook.write();
        workbook.close();
    }

    
  public List getExcelList() throws IOException, BiffException {
        List excelDoList = new ArrayList();                                //创建列表
        File xlsFile = new File("E:/ExcelProject/test.xls");                       //文件目录
        Workbook workbook = Workbook.getWorkbook(xlsFile);                                   //获得工作簿对象
        Sheet sheet = workbook.getSheet(0);                                            //获得工作表
        int rows = sheet.getRows();                                                          //获得行
        int cols = sheet.getColumns();                                                       //获得列
        int index = 0;
        for(int i = 0; i < rows; i++){                                                       //读取数据
            index = 0;
            ExcelDo excelDo = new ExcelDo();
            excelDo.setUserName(sheet.getCell(index,i).getContents());
            index = getIndex(index,cols);                                                   //每次添加完都调用一次
            excelDo.setCompany(sheet.getCell( index,i).getContents());
            excelDoList.add(excelDo);       //保存到列表中最后用来返回
        }
        workbook.close();
        return excelDoList;
    }

    
    private Integer getIndex(int index, int col){
        index++;
        if(index > col){
            index = col;
        }
        return index;
    }
}