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
缺一不可哦。