excel文档处理

package com.standard.monthreport.executeSupervise.bizc;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
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.ss.util.Region;
public class ExcleUtil {

// 设置cell编码解决中文高位字节截断
// private static short XLS_ENCODING = HSSFWorkbook.ENCODING_UTF_16;

// 定制日期格式
private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"
// 定制浮点数格式
private static String NUMBER_FORMAT = "#,##0.00";
private String xlsFileName;
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private HSSFRow row;
private HSSFComment comm;


/**
* 初始化Excel
*
* @param fileName
*            导出文件名
*/
public ExcleUtil(String fileName) {
this.xlsFileName = fileName;
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet(fileName);
}

public ExcleUtil() {
this.workbook = new HSSFWorkbook();
this.sheet = workbook.createSheet();

}


/**
* 导出Excel文件
*
* @throws XLSException
*/
public void exportXLS() {
FileOutputStream fOut = null;
try {
fOut = new FileOutputStream(xlsFileName);
workbook.write(fOut);
fOut.flush();
fOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally{
if(fOut!=null){
try {
fOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

public void exportXLS(OutputStream out) throws IOException {
workbook.write(out);
}

/**
* 增加一行
*
* @param index
*            行号
*/
public void createRow(int index) {
this.row = this.sheet.createRow(index);
}

    /**
     * 合并单元格
     * @param rowFrom 开始行
     * @param rowTo 结束行
     * @param columnFrom 开始列
     * @param columnTo 结束列
     */
    public void mergeCell(int rowFrom, int rowTo, int columnFrom, int columnTo) {
// TODO Auto-generated method stub
    Region region = new Region();
    region.setRowFrom(rowFrom);
    region.setRowTo(rowTo);
    region.setColumnFrom((short)columnFrom);
    region.setColumnTo((short)columnTo);
    sheet.addMergedRegion(region);
}


/**
* 设置单元格
*
* @param index
*            列号
* @param value
*            单元格填充值
*/
public void setCellString(int index, String value,String temp) {
HSSFCell cell = this.row.createCell(index);
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(( short ) 12 ); // 字体高度
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 宽度
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平布局：居中
        cellStyle.setWrapText(true);
HSSFRichTextString hssfString = new HSSFRichTextString(value);
    if(temp != null && temp.length() > 0){
        this.setDrawingPatriarch(cell,index,temp);
        }
    cell.setCellValue(hssfString); // 设置单元格内容
    cell.setCellStyle(cellStyle); // 设置单元格样式
    row.setHeight((short)600);
    sheet.setColumnWidth(index, 4000);

}


private void setDrawingPatriarch(HSSFCell cell,int index,String textStr){
// 添加单元格注释
        // 创建HSSFPatriarch对象,HSSFPatriarch是所有注释的容器.
        HSSFPatriarch patr = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置
        comm = patr.createComment( new HSSFClientAnchor( 0 , 0 , index , 0 , ( short ) 4 , 2 , ( short ) 8 , 7 ));
        // 设置注释内容
        comm.setString( new HSSFRichTextString(textStr));
        // 设置注释作者. 当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comm.setAuthor( "yinxiaobo" );
        cell.setCellComment(comm);
}

/**
* 设置单元格
*
* @param index
*            列号
* @param value
*            单元格填充值
*/
public void setCellCalendar(int index, Calendar value) {
HSSFCell cell = this.row.createCell(index);
// cell.setEncoding(XLS_ENCODING);
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
cell.setCellValue(value.getTime());
sheet.autoSizeColumn((short) index);
}

/**
* 设置单元格
*
* @param index
*            列号
* @param value
*            单元格填充值
*/
public void setCellNumber(int index, int value) {
HSSFCell cell = this.row.createCell(index);
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
sheet.autoSizeColumn((short) index);
}

public void setCellBoolean(int index, String value) {
HSSFCell cell = this.row.createCell(index);
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.setCellType(HSSFCell.CELL_TYPE_BOOLEAN);
HSSFRichTextString hssfString = new HSSFRichTextString(value);
cell.setCellValue(hssfString); // 设置单元格内容
sheet.autoSizeColumn((short) index);
}

/**
* 设置单元格
*
* @param index
*            列号
* @param value
*            单元格填充值
*/
public void setCellDouble(int index, double value) {
HSSFCell cell = this.row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
cell.setCellValue(value);
sheet.autoSizeColumn((short) index);
}

}