POI操作Excel总结

POI操作Excel总结，如下：

1、导入jar包: [ poi-3.6.jar , poi-ooxml-3.6.jar , poi-ooxml-schemas-3.6.jar ]
如果使用了maven，则在pom里面添加如下依赖即可：

<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi</artifactId>  
    <version>3.6</version>  
</dependency>  
<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi-ooxml</artifactId>  
    <version>3.6</version>  
</dependency>  

2、记录一下平时工作中poi对Excel的基本操作,代码如下：

import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.ss.usermodel.Sheet;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Cell;  

public void poiMethod{  
    File file = new File("c:/example.xls");  
    FileInputStream fileInputStream = new FileInputStream(file);  

    Workbook book = WorkbookFactory.create(fileInputStream);  
    int sheetNum = book.getNumberOfSheets();//取得sheet数  
    Sheet[] sheets = new Sheet[sheetNum];  
    for (int i = 0; i < sheetNum; i++) {  
        sheets[i] = book.getSheetAt(i);  
    }  

    Sheet sheet = sheets[0];//取得第一个sheet  
    Row row = sh.getRow(0);//取得第一行  
    short cellNum = row.getLastCellNum();//取得该行的列数  
    for(int i=0;i<cellNum;i++){  
        Cell cell = row.getCell(i);//取得row行i列的cell  
        getValueByCell(cell)//根据cell取得cell的值  
    }  
}  

/** 
 * 根据cell取得cell的值 
 * 
 * @param cell 
 * @return 
 */  
private String getValueByCell(Cell cell) {  
    if (cell == null) {  
        return null;  
    }  
    int cellType = cell.getCellType();  
    try {  
        if (HSSFDateUtil.isCellDateFormatted(cell)) {  
            Date date = cell.getDateCellValue();  
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");  
            return sdf.format(date);  
        }  
    } catch (Exception e) {}  

    if (cellType == Cell.CELL_TYPE_BLANK) {  
        return cell.getStringCellValue();  
    } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {  
        return String.valueOf(cell.getBooleanCellValue());  
    } else if (cellType == Cell.CELL_TYPE_ERROR) {  
        return String.valueOf(cell.getErrorCellValue());  
    } else if (cellType == Cell.CELL_TYPE_FORMULA) {  
        return cell.getStringCellValue();  
    } else if (cellType == Cell.CELL_TYPE_NUMERIC) {  
        java.text.DecimalFormat formatter = new java.text.DecimalFormat("##########");  
        return formatter.format(cell.getNumericCellValue());  
    } else if (cellType == Cell.CELL_TYPE_STRING) {  
        return cell.getStringCellValue();  
    }  
    return null;  
}  

3、设置边框、背景色、字体、字体大小、居中等

import org.apache.poi.ss.usermodel.Workbook;  
import org.apache.poi.hssf.usermodel.HSSFWorkbook;  
import org.apache.poi.hssf.usermodel.HSSFSheet sheet;  
import org.apache.poi.ss.usermodel.Row;  
import org.apache.poi.ss.usermodel.Cell;  
import org.apache.poi.ss.usermodel.Font;  
import org.apache.poi.hssf.usermodel.HSSFCellStyle;  
import org.apache.poi.hssf.usermodel.HSSFPalette  

public void poiMethod(){  
    Workbook workbook = new HSSFWorkbook();  

    //设置背景色  
    HSSFPalette palette = ((HSSFWorkbook)workbook).getCustomPalette();  
    //根据具体的RGB值把蓝色重新定义  
    palette.setColorAtIndex(HSSFColor.BLUE.index, (byte)83, (byte)141, (byte)213);  

    //设置字体、字号  
    Font font = workbook.createFont();  
    font.setFontName("Arial");//设置字体  
    font.setFontHeightInPoints((short)26);//设置字体大小  

     //创建样式  
     CellStyle cellStyle = workbook.createCellStyle();  
     //把上面设置的背景色添加到样式  
     cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
     cellStyle.setFillForegroundColor(HSSFColor.BLUE.index);  
     //把上面设置的字体、字号添加到样式  
     cellStyle.setFont(font);  
     cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//设置居中  
     //以下4行是设置边框  
     cellStyle.setBorderBottom(CellStyle.BORDER_THIN);  
     cellStyle.setBorderLeft(CellStyle.BORDER_THIN);  
     cellStyle.setBorderRight(CellStyle.BORDER_THIN);  
     cellStyle.setTopBorderColor(CellStyle.BORDER_THIN);  

     //最后把样式应用到Cell  
      Sheet sheet = workbook.createSheet();  
      sheet.setDefaultColumnWidth(20);//默认把列宽设置为20
    sheet.setGridsPrinted(true);
      Row row = sheet.createRow(0);  
      Cell cell = row.createCell(0).  
      cell.setCellStyle(cellStyle);  
}  

4、合并单元格、隐藏单元格

import org.apache.poi.ss.util.CellRangeAddress;  
import org.apache.poi.hssf.usermodel.HSSFSheet;  

public void poiMethod(){  
    //以下四个参数：（int firstRow, int lastRow, int firstCol, int lastCol）  
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); //合并单元格，参数描述如上
    //如有样式的，需要在合并单元格后执行以下方法来设置样式，其中cellStyle参数是自定义的样式类 
    setRegionStyle(sheet,new CellRangeAddress(0, 0, 0,3),cellStyle);  

  //以下是隐藏单元格 
    sheet.createRow(3).setHeight((short) 0);// 隐藏第4行
    }  

/** 
 * 合并单元格后设置样式（边框、背景色、字体等） 
 * 
 * @param sheet 
 * @param cellRangeAddress 
 * @param cellStyle 
 */  
private void setRegionStyle(HSSFSheet sheet,CellRangeAddress cellRangeAddress, CellStyle cellStyle) {  
    for (int i = cellRangeAddress.getFirstRow(); i <= cellRangeAddress.getLastRow(); i++) {  
        HSSFRow row = sheet.getRow(i);  
        if(row==null){  
            row = sheet.createRow(i);  
        }  
        for (int j = cellRangeAddress.getFirstColumn(); j <=cellRangeAddress.getLastColumn(); j++) {  
            HSSFCell cell = row.getCell(j);  
            if(cell==null){  
                cell = row.createCell(j);  
            }  
            cell.setCellStyle(cellStyle);  
        }  
    }  
}  

5、下拉框的生成

import org.apache.poi.hssf.usermodel.DVConstraint;  
import org.apache.poi.hssf.usermodel.HSSFDataValidation;  
import org.apache.poi.ss.util.CellRangeAddressList;  

public void poiMethod(){  
    //下拉框的内容  
    String[] operArray = new String[] { "[1]Add", "[2]Modify", "[3]Delete" };  
    //对1-100行的第1列都生成下拉框  
    for (int i = 0; i < 100; i++) {  
        CellRangeAddressList regions = new CellRangeAddressList(beginRow, endRow - 1, 0, 0);  
       // 生成下拉框内容  
        DVConstraint constraint =   DVConstraint.createExplicitListConstraint(operArray);  
       // 绑定下拉框和作用区域  
        HSSFDataValidation dataValidataion = new HSSFDataValidation(regions, constraint);  
      // 对sheet页生效  
        sheet.addValidationData(dataValidataion);  
    }  
}