import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.HashMap;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelReportFile {
private static final long serialVersionUID = 612381396391426330L;
private Workbook workBook = null;
private HashMap<Integer, CellStyle> numberCellStyles = null;
private CellStyle textCellStyle = null;
private static int DEFAULT_SHEET_INDEX = 0;
public ExcelReportFile(File file) throws FileNotFoundException, IOException {
try {
//读取office 2007版本以上
workBook = new XSSFWorkbook(new FileInputStream(file));
} catch (Exception ex) {
//读取office 2003版本以下
workBook = new HSSFWorkbook(new FileInputStream(file));
}
}
public Object getValue(int rowIndex, int colIndex) {
return readCellValue(rowIndex, colIndex);
}
/**
* 通过行号,列号读取excel单元格数据
*
* @param rowNo
* @param colNo
* @return
*/
public String getValueByNo(int rowNo, int colNo) {
Object rtnValue = readCellValue(rowNo - 1, colNo - 1);
String sValue = String.valueOf((rtnValue == null) ? "" : rtnValue);
return sValue;
}
private Object readCellValue(int rowIndex, int colIndex) {
Object sCellValue = null;
Row row = workBook.getSheetAt(0).getRow(rowIndex);
if (row != null) {
Cell cell = row.getCell(colIndex);
if (cell != null) {
int cellType = cell.getCellType();
// HSSFCell.CELL_TYPE_FORMULA
// Empty
if (cellType == Cell.CELL_TYPE_BLANK) {
sCellValue = null;
// int dCellValue = 0;
// sCellValue = dCellValue;
}
// String
if (cellType == Cell.CELL_TYPE_STRING) {
sCellValue = cell.getRichStringCellValue().getString()
.trim();
}
// Number
if (cellType == Cell.CELL_TYPE_NUMERIC) {
int dCellValue = (int) cell.getNumericCellValue();
sCellValue = dCellValue;
}
// formula
if (cellType == Cell.CELL_TYPE_FORMULA) {
sCellValue = cell.getCellFormula();
}
}
}
return sCellValue;
}
public void writeNumber(int sheetIndex, int rowIndex, int colIndex,
Number value, int scale) {
Cell cell = getCell(sheetIndex, rowIndex, colIndex);
// HSSFCellStyle cellStyle = getNumberCellStyle(scale);
// cell.setCellStyle(cellStyle);
cell.setCellValue(value.doubleValue());
}
public void writeText(int sheetIndex, int rowIndex, int colIndex,
String value) {
Cell cell = getCell(sheetIndex, rowIndex, colIndex);
// HSSFCellStyle cellStyle = getTextCellStyle();
// cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
// cell.setCellStyle(cellStyle);
cell.setCellValue(new HSSFRichTextString(value));
}
public void writeText(int rowIndex, int colIndex, String value) {
writeText(DEFAULT_SHEET_INDEX, rowIndex, colIndex, value);
}
public void writeTextByNo(int rowIndex, int colIndex, String value) {
writeText(DEFAULT_SHEET_INDEX, rowIndex - 1, colIndex - 1, value);
}
public void writeNumber(int rowIndex, int colIndex, Number value, int scale) {
writeNumber(DEFAULT_SHEET_INDEX, rowIndex, colIndex, value, scale);
}
public void writeNumberByNo(int rowIndex, int colIndex, Number value,
int scale) {
writeNumber(DEFAULT_SHEET_INDEX, rowIndex - 1, colIndex - 1, value,
scale);
}
private Cell getCell(int sheetIndex, int rowIndex, int colIndex) {
// Sheet
Sheet sheet = null;
try {
sheet = workBook.getSheetAt(sheetIndex);
} catch (IllegalArgumentException ex) {
sheet = workBook.createSheet();
}
// Row
Row row = null;
row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
// Cell
Cell cell = null;
cell = row.getCell(colIndex);
if (cell == null) {
cell = row.createCell(colIndex);
}
return cell;
}
private CellStyle getNumberCellStyle(int scale) {
if (this.numberCellStyles == null) {
this.numberCellStyles = new HashMap<Integer, CellStyle>();
}
if (this.numberCellStyles.get(Integer.valueOf(scale)) == null) {
CellStyle numberCellStyle = workBook.createCellStyle();
StringBuilder zeroBd = new StringBuilder();
DataFormat format = this.workBook.createDataFormat();
zeroBd.append("0");
if (scale > 0) {
zeroBd.append(".");
for (int zCount = 0; zCount < scale; zCount++) {
zeroBd.append("0");
}
}
short doubleFormat = format.getFormat(zeroBd.toString());
numberCellStyle.setDataFormat(doubleFormat);
this.numberCellStyles.put(Integer.valueOf(scale), numberCellStyle);
return numberCellStyle;
} else {
return this.numberCellStyles.get(Integer.valueOf(scale));
}
}
public void write(OutputStream stream) throws IOException {
if (this.workBook != null && stream != null) {
this.workBook.write(stream);
}
}
private CellStyle getTextCellStyle() {
if (textCellStyle != null) {
return textCellStyle;
} else {
return this.workBook.createCellStyle();
}
}
/**
* 通过英文字母获得列号
*
* @param colIndexStr
* @return
*/
public static int convertColIndexString2Number(String colIndexStr) {
int len = colIndexStr.toUpperCase().length();
char[] chars = colIndexStr.toCharArray();
int col = 0;
for (int index = 0; index < len; index++) {
char ca = chars[index];
int charAInt = Character.getNumericValue('A');
int charInt = Character.getNumericValue(ca);
BigDecimal bg = new BigDecimal(26);
col = col + bg.pow(len - index - 1).intValue()
* (charInt - charAInt + 1);
}
return col;
}
}
所需jar包:
poi-3.8-20120326.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
xbean.jar
缺一不可哦。