JavaPoi的使用操作excel的框架

2019独角兽企业重金招聘Python工程师标准>>>

POI是java用来操作windows文档的一个框架。通过HSSF可以用java来操作excel的读写。我记得我毕业的第一个项目就是用POI来做的。呵呵。

下面是一个用poi读写excel的列子

public class PoiTest {
    public static void main(String args[]){
        try {
            //readSheet();
            writeSheet();
        } catch (IOException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    public static void readSheet() throws IOException {
        POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream("d:\\test.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        HSSFCell cell = row.getCell((short) 0);
        String msg = cell.getStringCellValue();
        System.out.println(msg);
    }

    public static void writeSheet() throws IOException {
        POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream("d:\\test.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        HSSFCell cell = row.getCell((short)0);
        cell.setCellValue("a test");
        // Write the output to a file
        FileOutputStream fileOut = new FileOutputStream("d:\\test.xls");
        wb.write(fileOut);
        fileOut.close();
    }
}

下面是一些其他的测试方法：

public static void test() throws IOException {
        POIFSFileSystem fs=new POIFSFileSystem(new FileInputStream("d:\\test.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        HSSFCell cell = row.getCell((short)0);
        //设置sheet名称
        wb.setSheetName(0, "中文");
        //设置宽度
        sheet.setColumnWidth(0,(short)1000);
        //设置高度
        row.setHeight((short)1000);
        //设置内容
        cell.setCellValue("中文");
        Region region = new Region((short)0,(short)0,(short)2,(short)2);
        sheet.addMergedRegion(region);
        //得到所有区域
        sheet.getNumMergedRegions();
        //设置字体和样式
        HSSFFont f  = wb.createFont();
        f.setFontHeightInPoints((short) 11);//字号
        f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
        HSSFCellStyle style = wb.createCellStyle();
        style.setFont(f);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
        style.setRotation((short)0);//单元格内容的旋转的角度
        //HSSFDataFormat df = wb.createDataFormat();
        //style.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式
        //cell.setCellFormula(string);//给单元格设公式
        cell.setCellStyle(style);
        FileOutputStream fileOut = new FileOutputStream("d:\\test.xls");
        wb.write(fileOut);
        fileOut.close();
    }

下面是根据单元格的样式来获取内容

public String getCellStringValue(HSSFCell cell) {
        String cellValue = "";
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
                    cellValue=" ";
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                cellValue="&nbsp;";
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                break;
            default:
                break;
        }
        return cellValue;
    }

其中的excel的结构及常用类如下：

HSSFWorkbook excell 文档对象介绍 
HSSFSheet excell的表单 
HSSFRow excell的行 
HSSFCell excell的格子单元 
HSSFFont excell字体 
HSSFName 名称 
HSSFDataFormat 日期格式 

HSSFHeader sheet头 
HSSFFooter sheet尾 
HSSFCellStyle cell样式 

HSSFDateUtil 日期 
HSSFPrintSetup 打印 
HSSFErrorConstants 错误信息表

参考文档：http://ltc603.iteye.com/blog/30184

http://ltc603.iteye.com/blog/30040

总结一下，poi是java来操作excel的工具，其中要熟悉各种对象对应的excel的结构。

转载于:https://my.oschina.net/zimingforever/blog/147096