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 ListgetExcelList() 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");
}
