使用Java操作Excel文件

我所知道的能够很好的操作Excel的开源实现有2个。一个是apache的Poi(http://poi.apache.org/) ,还有一个是Jxl(http://jexcelapi.sourceforge.net/)。

关于Poi，网上的介绍很多，我也写了一个简单的封装类，仅供参考。

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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.hssf.util.HSSFColor;

public class ExcelOutput ...{
    
    public static void printExcelReport(String filepath, String sheetName, String[] headers, Object[][] data) throws IOException...{
        
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);
        //HSSFDataFormat format = wb.createDataFormat();
        
        HSSFRow hdRow = sheet.createRow((short) 0);
        
        HSSFCellStyle hdStyle = wb.createCellStyle();
        hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        hdStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        hdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        hdStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        hdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        hdStyle.setRightBorderColor(HSSFColor.BLACK.index);
        hdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        hdStyle.setTopBorderColor(HSSFColor.BLACK.index);

        hdStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        hdStyle.setFillForegroundColor(HSSFColor.PINK.index);
        hdStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        
        for (int i = 0; i < headers.length; i++) ...{
            HSSFCell cell = hdRow.createCell((short)i);
            HSSFRichTextString value = new HSSFRichTextString(headers[i]);
            cell.setCellValue(value);
            cell.setCellStyle(hdStyle);
        }
        
        HSSFCellStyle dtStyle = wb.createCellStyle();
        dtStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        dtStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        dtStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        dtStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        dtStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        dtStyle.setRightBorderColor(HSSFColor.BLACK.index);
        dtStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        dtStyle.setTopBorderColor(HSSFColor.BLACK.index);
        for (int i = 0; i < data.length; i++) ...{
            HSSFRow dtRow = sheet.createRow((short) (i+1));
            Object[] row_data = data[i];
            for (int j = 0; j < row_data.length; j++) ...{
                Object cell_data = row_data[j];
                HSSFCell cell = dtRow.createCell((short)j);
                if (cell_data instanceof String)...{
                    cell.setCellValue(new HSSFRichTextString((String)cell_data));
                } else if (cell_data instanceof Double)...{
                    //dtStyle.setDataFormat(format.getFormat("m/d/yy"));
                    cell.setCellValue((Double)cell_data);
                } else if (cell_data instanceof Integer)...{
                    cell.setCellValue(Double.valueOf(String.valueOf(cell_data)));
                } else if (cell_data instanceof Date)...{
                    cell.setCellValue((Date)cell_data);
                }
                cell.setCellStyle(dtStyle);
            }
        }
        
        FileOutputStream fileOut = new FileOutputStream(filepath);
        wb.write(fileOut);
        fileOut.close();
    }
}

简单的测试代码：

import java.io.IOException;

public class ExcelDemo ...{

    public static void main(String[] args) throws IOException ...{
        
        String[] headers = ...{"工号", "姓名", "工资"};
        Object[][] data = new Object[][]...{...{"A001","小张","1200"},...{"A002","二毛","2000"},...{"A003","老李","1500"},...{"A004","阿三","1650"}};
        String filepath  = "C:/salary.xls";
        String sheetname = "salary";

        ExcelOutput.printExcelReport(filepath, sheetname, headers, data);
    }
}

Jxl的实现似乎也很不错，我在很早以前就收藏了他的一些示例，现在一并贴出来。

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelTest ...{

    public static void main(String[] args) ...{
        
        File file = new File("C:/test1.xls");
        WritableWorkbook workbook = null;
        try ...{
            workbook = Workbook.createWorkbook(file);
            WritableSheet imgSheet = workbook.createSheet("Images",0);       
            File imgFile = new File("C:/1.png");    
            insertImg(imgSheet,0,0,10,30,imgFile);
            workbook.write();
            workbook.close();
        } catch (WriteException e) ...{
            e.printStackTrace();
        } catch (IOException e) ...{
            e.printStackTrace();
        } 
    }
    
    public static String readExcel(File file)...{ 
        
        StringBuffer sb = new StringBuffer(); 
        Workbook wb = null;
        
        try ...{
            wb=Workbook.getWorkbook(file);
        } catch (BiffException e) ...{
            e.printStackTrace();
        } catch (IOException e) ...{
            e.printStackTrace();
        }
        
        if(wb == null) return null;
        
        Sheet[] sheet = wb.getSheets();
        if(sheet!=null&&sheet.length>0)...{
            for(int i = 0; i < sheet.length; i++)...{
                int rowNum = sheet[i].getRows();    
                for(int j = 0;j < rowNum; j++)...{
                    Cell[] cells = sheet[i].getRow(j);
                    if(cells!=null&&cells.length>0)...{
                        for(int k = 0; k < cells.length; k++)...{
                            String cellValue = cells[k].getContents();
                            sb.append(cellValue+" ");
                        }
                    }
                    sb.append(" ");
                }
                sb.append(" ");    
            }
        }
        wb.close();
        return sb.toString();
    }
    
    public static void writeExcel(String fileName)...{
        
        WritableWorkbook wwb = null;
        try ...{
            wwb = Workbook.createWorkbook(new File(fileName));
        } catch (IOException e) ...{
            e.printStackTrace();
        }
        if(wwb!=null)...{
            WritableSheet ws = wwb.createSheet("sheet1", 0);
            for(int i=0;i<10;i++)...{
                for(int j=0;j<5;j++)...{
                    Label labelC = new Label(j, i, "ROW："+(i+1)+",CELL:"+(j+1));
                    try ...{
                        ws.addCell(labelC);
                    } catch (RowsExceededException e) ...{
                        e.printStackTrace();
                    } catch (WriteException e) ...{
                        e.printStackTrace();
                    }
                }
            }
        }
        try ...{
            wwb.write();
            wwb.close();    
        } catch (IOException e) ...{
            e.printStackTrace();
        } catch (WriteException e) ...{
            e.printStackTrace();
        }
    }
    
    public static boolean searchKeyWord(File file,String keyWord)...{
        
        boolean res = false;
        Workbook wb = null;
        
        try ...{
            wb=Workbook.getWorkbook(file);
        } catch (BiffException e) ...{
            e.printStackTrace();
        } catch (IOException e) ...{
            e.printStackTrace();
        }
        
        if(wb == null) return res;
        
        Sheet[] sheet = wb.getSheets();
        
        boolean breakSheet = false;
        if(sheet!=null&&sheet.length>0)...{
            for(int i = 0; i < sheet.length; i++)...{
                if(breakSheet) break;
                int rowNum = sheet[i].getRows();
                boolean breakRow = false;
                for(int j = 0;j < rowNum; j++)...{
                    if(breakRow) break;
                    Cell[] cells = sheet[i].getRow(j);
                    if(cells!=null&&cells.length>0)...{
                        boolean breakCell = false;
                        for(int k = 0; k < cells.length; k++)...{
                            if(breakCell) break; 
                            String cellValue = cells[k].getContents();
                             if(cellValue == null) continue;
                             if(cellValue.contains(keyWord))...{
                                 res = true;    
                                 breakCell = true;    
                                 breakRow = true;    
                                 breakSheet = true;
                             }
                        }
                    }
                }  
            }
        }
        wb.close();
        return res;  
    }
    
    public static void insertImg(WritableSheet dataSheet, int col, int row, int width, int height, File imgFile)...{    
        WritableImage img = new WritableImage(col, row, width, height, imgFile);    
        dataSheet.addImage(img);    
    }
}