使用POI, 我的Excel操作类

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 * 获得、设置单元格的内容
 */
package util.excel;

import java.text.DecimalFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;

/**
 *
 * @author Administrator
 */
public class CellContent {

    private static DecimalFormat decimalFormat = new DecimalFormat("#.##"); //格式化数字为正整数

    public static String getContent(HSSFCell cell) {//获得一个单元格的内容
        if (cell == null) {
            return "";
        }
        String content = "";
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            content = decimalFormat.format(cell.getNumericCellValue());
        } else {
            try {
                content = cell.getStringCellValue().trim();
            } catch (java.lang.IllegalStateException e) {
                content = decimalFormat.format(cell.getNumericCellValue());
            }
        }
        return content;
    }
    //设置一个单元格的内容

    public static void setContent(HSSFCell cell, String content) {
        if (cell == null) {
            return;
        }
        try {
            double d = Double.parseDouble(content);
            cell.setCellValue(d);
        } catch (Exception e) {
            cell.setCellValue(content);
        }
    }
}

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package util.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.IndexedColors;

/**
 *
 * @author Administrator
 */
public class CellStyle {

    private HSSFWorkbook book = null; //
    private HSSFCellStyle fullStyle = null;//单元格格式

    public CellStyle(HSSFWorkbook book) {
        this.book = book;
        this.fullStyle = book.createCellStyle(); //full style
        this.initStyle();
    }

    public HSSFCellStyle getStyle() {
        return this.fullStyle;
    }

    //只用于更改字体
    public void initStyle(int fontSize) {
        HSSFFont font = book.createFont();//字体
        font.setFontName("宋体");
        font.setFontHeight((short) (fontSize * 20));
        font.setBoldweight((short) (fontSize * 20));
        fullStyle.setFont(font);
    }

    private void initStyle() {//初始化调用这个方法，使用默认字体
        this.otherStyle();//先设置其它样式
        HSSFFont font = book.createFont();//字体
        font.setFontName("宋体");
        font.setFontHeight((short) (12 * 20));
        font.setBoldweight((short) (12 * 20));
        fullStyle.setFont(font);
    }
    //除字体之外的其它格式

    private void otherStyle() {
        fullStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //薄边框
        fullStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        fullStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        fullStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        fullStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); //边框颜色
        fullStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        fullStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        fullStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        fullStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中对齐
    }
}

[code]
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
* 将sheet页面格式化，让它beautiful
*/
package util.excel;

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;

/**
*
* @author Administrator
*/
public class BeautySheet {

private HSSFSheet sheet = null;
private HSSFWorkbook book = null;

public BeautySheet(HSSFSheet sheet, HSSFWorkbook book) {
this.sheet = sheet;
this.book = book;
}

public void beautyThis() {
CellStyle style = new CellStyle(book);//创建一个cellstyle，单元格样式
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
HSSFRow row = sheet.getRow(i);
for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
HSSFCell cell = row.getCell(j);
if (cell != null) {
cell.setCellStyle(style.getStyle());
}
}
}
HSSFRow firstRow = sheet.getRow(0);
for (int i = 0; i < firstRow.getPhysicalNumberOfCells(); i++) { //Sheet页面自动控制列宽 根据第一行
sheet.autoSizeColumn(i);
}
}
}
[/code]
[code]
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
* 将该JTable导出到一个poi sheet
*/
package util.excel;

import java.util.Vector;
import javax.swing.JTable;
import javax.swing.table.DefaultTableColumnModel;
import javax.swing.table.DefaultTableModel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

/**
*
* @author Administrator
*/
public class SheetExporter {

private JTable table = null;//表格
private DefaultTableModel tableModel = null;//表格的数据模型
private HSSFSheet sheet;//要导出的sheet页

public SheetExporter(JTable table, HSSFSheet sheet) {
this.table = table;
this.tableModel = (DefaultTableModel) table.getModel();
this.sheet = sheet;
}

//导出表格：这里只包含表格的列名称以及表格内容啊！
public void export() {
this.exportHead(this.columnName());//导出表头
this.exportContent();//导出表格内容(数据部分，即不包含列名称的)
}
//获得表格的列名称

private Vector<Object> columnName() {
DefaultTableColumnModel columnModel = (DefaultTableColumnModel) table.getTableHeader().getColumnModel(); //表格的列模型
Vector<Object> columnNames = new Vector<Object>();//columnName
for (int i = 0; i < columnModel.getColumnCount(); i++) {
columnNames.add(columnModel.getColumn(i).getHeaderValue());
}
return columnNames;
}
//导出表头:列名称 默认导入到第0行，从第0列开始导出

private void exportHead(Vector<Object> v) {
HSSFRow row = this.sheet.createRow(0);//创建第0行
for (int i = 0; i < v.size(); i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(v.get(i).toString());
}
}
//导出JTable的内容部分，不含表头

private void exportContent() {
Vector<Vector<Object>> v = this.tableModel.getDataVector();
for (int i = 0; i < v.size(); i++) {
HSSFRow row = this.sheet.createRow(i + 1); //创建行
Vector<Object> v_o = v.get(i);
for (int j = 0; j < v_o.size(); j++) {
String str = (v_o.get(j) == null) ? "" : v_o.get(j).toString();
HSSFCell cell = row.createCell(j);
try {
double d = Double.parseDouble(str);
cell.setCellValue(d);
} catch (Exception e) {
cell.setCellValue(str);
}
}
}
}
}
[/code]