poi操作Excel相关对象属性介绍及中级应用

之前发布了一篇基于Servlet的poi实现Excel的读取,下载的文章,这里对poi中HSSF接口做详细的介绍;HSSF接口专门处理Excel的,在poi中应该算是比较成熟的一部分了;poi主要接口如下:

1.HSSF － 提供读写Microsoft Excel格式档案的功能。 

2.XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。

3.HWPF － 提供读写Microsoft Word格式档案的功能。

4.HSLF － 提供读写Microsoft PowerPoint格式档案的功能。

5.HDGF － 提供读写Microsoft Visio格式档案的功能

反正就是当别人问到poi了就只知道处理excel就行;不说废话了,进入正题

HSSFWorkbook excell 文档对象介绍
HSSFSheet excell的表单
HSSFRow excell的行
HSSFCell excell的格子单元
HSSFFont excell字体
HSSFName 名称
HSSFDataFormat 日期格式
HSSFHeader sheet头
HSSFFooter sheet尾
HSSFCellStyle cell样式
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表

下面是初级应用代码(Java工程),导包就不说了,上篇文章有下载地址链接

package com.wf.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;

public class PoiTest {

    /**
     * 创建Excel
     */
    public void createExcel(){
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("My First Sheet");//创建工作区,名称
            HSSFRow row = sheet.createRow(0);//第一行
            HSSFCell cell = row.createCell(0);//第一列
            cell.setCellValue("Hello poi");//写入值
            FileOutputStream fileOut = new FileOutputStream("d:\test.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }  
    }
    /**
     * 读取excel,注意至少要有一行一列,否则无值
     */
    public void readExcel(){
        try {
            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(0);//第一列
            String msg = cell.getStringCellValue();//值
            System.out.println("cell'value="+msg);
        } catch (Exception e) {
            e.printStackTrace();
        }  
    }
    /**
     * 修改/写入值
     */
    public void updateCellValue() {
        try {
            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(0);
            cell.setCellValue("a test");
            FileOutputStream fileOut = new FileOutputStream("d:\test.xls");
            wb.write(fileOut);
            fileOut.close();
        } catch (Exception e) {
            e.printStackTrace();
        }  
    }
}

下面是中级应用:

public void test1(){
        try {
            // load源文件   
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("filePath"));   
            HSSFWorkbook wb = new HSSFWorkbook(fs);   
            for (int i = 0; i < wb.getNumberOfSheets(); i++) {   
                HSSFSheet sheet = wb.getSheetAt(i);   
                for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j ++) {   
                    HSSFRow row = sheet.getRow(i);   
                    if (row != null) {   
                        //....操作
                    }   
                }   
            }   
            //目标文件   
            FileOutputStream fos = new FileOutputStream("objectPath");   
            //写文件   
            wb.write(fos);   
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }   
    }

相关操作:

1、得到列和单元格
HSSFRow row = sheet.getRow(i);   
HSSFCell cell = row.getCell((short) j);  
2、设置sheet名称和单元格内容为中文
wb.setSheetName(n, "中文",HSSFCell.ENCODING_UTF_16);       
cell.setEncoding(1);   
cell.setCellValue("中文");  
3、单元格内容未公式或数值，可以这样读写
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);   
cell.getNumericCellValue()  
4、设置列宽、行高
sheet.setColumnWidth(column,width);   
row.setHeight(height);  
5、添加区域，合并单元格
Region region = new Region(rowFrom,columnFrom,rowTo,columnTo);   
sheet.addMergedRegion(region);   
//得到所有区域   
sheet.getNumMergedRegions()  
6、根据单元格不同属性返回字符串数值
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=" ";   
            break;   
        case HSSFCell.CELL_TYPE_BOOLEAN:   
            break;   
        case HSSFCell.CELL_TYPE_ERROR:   
            break;   
        default:   
            break;   
        }   
    return cellValue;   
}  
7、常用单元格边框格式
虚线HSSFCellStyle.BORDER_DOTTED
实线HSSFCellStyle.BORDER_THIN
public static HSSFCellStyle getCellStyle( type){      
    HSSFWorkbook wb = new HSSFWorkbook();   
    HSSFCellStyle style = wb.createCellStyle();   
    style.setBorderBottom(type);//下边框    
    style.setBorderLeft(type);//左边框    
    style.setBorderRight(type);//右边框    
    style.setBorderTop(type);//上边框    
    return style;   
}  
8、设置字体和内容位置
HSSFFont f  = wb.createFont();   
f.setFontHeightInPoints( 11);//字号   
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗   
style.setFont(f);   
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中   
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中   
style.setRotation( rotation);//单元格内容的旋转的角度   
HSSFDataFormat df = wb.createDataFormat();   
style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式   
cell.setCellFormula(string);//给单元格设公式   
style.setRotation( rotation);//单元格内容的旋转的角度   
cell.setCellStyle(style);   
9、插入图片
//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray   
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();   
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));   
ImageIO.write(bufferImg,"jpg",byteArrayOut);   
//读进一个excel模版   
FileInputStream fos = new FileInputStream(filePathName+"/stencil.xlt");    
fs = new POIFSFileSystem(fos);   
//创建一个工作薄   
HSSFWorkbook wb = new HSSFWorkbook(fs);   
HSSFSheet sheet = wb.getSheetAt(0);   
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();   
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255, 0,0,10,10);        
patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));  
10、设置列自动换行
HSSFCellStyle cellStyle =  workbook.createCellStyle();
cellStyle.setWrapText(true);
sheet.setDefaultColumnStyle(0, cellStyle);
//设置列的宽度
sheet.setColumnWidth((short)0,(short)9000);
sheet.setDefaultColumnStyle((short)0, cellStyle);
//与
sheet.setDefaultColumnWidth((short)70);冲突
只会换行 不会设置列宽

单元格复制操作

package com.wf.test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.Region;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
 * 将某SHEET页中的某几行复制到某SHEET页的某几行中。抱括被合并了的单元格。
 */
public class RowCopy {
    /**
     * @param args
     * @throws IOException
     * @throws FileNotFoundException
     */
    @SuppressWarnings("deprecation")
    public static void main(String[] args) {
        try {
            POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("d:\\exlsample.xls"));
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            // source为源sheet 页,target为目标sheet页
            copyRows(wb, "source", "target", 5, 6, 20);
            FileOutputStream fileOut = new FileOutputStream("d:\\exlsample.xls");
            wb.write(fileOut);
            fileOut.flush();
            fileOut.close();
            System.out.println("Operation finished");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * @param wb
     *            HSSFWorkbook
     * @param pSourceSheetName
     *            源sheet页名称
     * @param pTargetSheetName
     *            目标sheet页名称
     * @param pStartRow
     *            源sheet页中的起始行
     * @param pEndRow
     *            源sheet页中的结束行
     * @param pPosition
     *            目标sheet页中的开始行
     */
    public static void copyRows(HSSFWorkbook wb, String pSourceSheetName,

    String pTargetSheetName, int intStartRow, int intEndRow, int intPosition) {
        // EXECL中的行是从1开始的，而POI中是从0开始的，所以这里要减1.
        int pStartRow = intStartRow - 1;
        int pEndRow = intEndRow - 1;
        int pPosition = intPosition - 1;
        HSSFRow sourceRow = null;
        HSSFRow targetRow = null;
        HSSFCell sourceCell = null;
        HSSFCell targetCell = null;
        HSSFSheet sourceSheet = null;
        HSSFSheet targetSheet = null;
        Region region = null;
        int cType;
        int i;
        int j;
        int targetRowFrom;
        int targetRowTo;
        if ((pStartRow == -1) || (pEndRow == -1)) {
            return;
        }
        sourceSheet = wb.getSheet(pSourceSheetName);
        targetSheet = wb.getSheet(pTargetSheetName);
        System.out.println(sourceSheet.getNumMergedRegions());
        // 拷贝合并的单元格
        for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            region = sourceSheet.getMergedRegionAt(i);
            if ((region.getRowFrom() >= pStartRow) && (region.getRowTo() <= pEndRow)) {
                targetRowFrom = region.getRowFrom() - pStartRow + pPosition;
                targetRowTo = region.getRowTo() - pStartRow + pPosition;
                region.setRowFrom(targetRowFrom);
                region.setRowTo(targetRowTo);
                targetSheet.addMergedRegion(region);
            }
        }
        // 设置列宽
        for (i = pStartRow; i <= pEndRow; i++) {
            sourceRow = sourceSheet.getRow(i);
            if (sourceRow != null) {
                for (j = sourceRow.getLastCellNum(); j > sourceRow.getFirstCellNum(); j--) {
                    targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
                    targetSheet.setColumnHidden(j, false);
                }
                break;
            }
        }
        // 拷贝行并填充数据
        for (; i <= pEndRow; i++) {
            sourceRow = sourceSheet.getRow(i);
            if (sourceRow == null) {
                continue;
            }
            targetRow = targetSheet.createRow(i - pStartRow + pPosition);
            targetRow.setHeight(sourceRow.getHeight());
            for (j = sourceRow.getFirstCellNum(); j < sourceRow.getPhysicalNumberOfCells(); j++) {
                sourceCell = sourceRow.getCell(j);
                if (sourceCell == null) {
                    continue;
                }
                targetCell = targetRow.createCell(j);
                targetCell.setCellStyle(sourceCell.getCellStyle());
                cType = sourceCell.getCellType();
                targetCell.setCellType(cType);
                switch (cType) {
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    targetCell.setCellValue(sourceCell.getBooleanCellValue());
                    System.out.println("--------TYPE_BOOLEAN:" + targetCell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    targetCell.setCellErrorValue(sourceCell.getErrorCellValue());
                    System.out.println("--------TYPE_ERROR:" + targetCell.getErrorCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    // parseFormula这个函数的用途在后面说明
                    targetCell.setCellFormula(parseFormula(sourceCell.getCellFormula()));
                    System.out.println("--------TYPE_FORMULA:" + targetCell.getCellFormula());
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    targetCell.setCellValue(sourceCell.getNumericCellValue());
                    System.out.println("--------TYPE_NUMERIC:" + targetCell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    targetCell.setCellValue(sourceCell.getRichStringCellValue());
                    System.out.println("--------TYPE_STRING:" + i + targetCell.getRichStringCellValue());
                    break;
                }
            }
        }
    }
    /**
     * POI对Excel公式的支持是相当好的，但是有一个问题，如果公式里面的函数不带参数，比如now()或today()，
     * 那么你通过getCellFormula()取出来的值就是now(ATTR(semiVolatile))和today(ATTR(semiVolatile))，
     * 这样的值写入Excel是会出错的，这也是我上面copyRow的函数在写入公式前要调用parseFormula的原因，
     * parseFormula这个函数的功能很简单，就是把ATTR(semiVolatile)删掉。
     * @param pPOIFormula
     * @return
     */
    private static String parseFormula(String pPOIFormula) {
        final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
        StringBuffer result = null;
        int index;
        result = new StringBuffer();
        index = pPOIFormula.indexOf(cstReplaceString);
        if (index >= 0) {
            result.append(pPOIFormula.substring(0, index));
            result.append(pPOIFormula.substring(index + cstReplaceString.length()));
        } else {
            result.append(pPOIFormula);
        }
        return result.toString();
    }
}