操作Excel工具类（基于Apache的POI类库）
上一篇介绍的是基于jxl.jar的操作Excel工具类，但是jxl.jar的licence限制了它不能用于商业项目，所以本篇介绍Apache的POI类库。
?
上篇文章在这里
操作Excel工具类（基于jxl.jar）
?
功能简介：
1、向Excel文档插入数据，可以是多行可以是多列，保留原单元格格式不变
2、向Excel文档插入一个新行，并且使用与上一行完全相同的格式
3、等等
?
需要的第三方JAR包：
poi-3.8-20120326.jar
poi-examples-3.8-20120326.jar
poi-excelant-3.8-20120326.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
poi-scratchpad-3.8-20120326.jar
stax-api-1.0.1.jar
?
完整的工具类的代码如下：
感谢gegewuqin9和在世界的中心呼喚愛在回复中建议，对读取单元格值的地方做了修改。
?
?
import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStream;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;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.ss.usermodel.WorkbookFactory;/** * Excel工具类 * * <pre> * 基于Apache的POI类库 * </pre> * * @author 陈峰 */public class POIExcelMakerUtil {private File excelFile;private InputStream fileInStream;private Workbook workBook;public POIExcelMakerUtil(File file) throws Exception {this.excelFile = file;this.fileInStream = new FileInputStream(this.excelFile);this.workBook = WorkbookFactory.create(this.fileInStream);}/** * 写入一组值 * * @param sheetNum * 写入的sheet的编号 * @param fillRow * 是写入行还是写入列 * @param startRowNum * 开始行号 * @param startColumnNum * 开始列号 * @param contents * 写入的内容数组 * @throws Exception */public void writeArrayToExcel(int sheetNum, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents)throws Exception {Sheet sheet = this.workBook.getSheetAt(sheetNum);writeArrayToExcel(sheet, fillRow, startRowNum, startColumnNum, contents);}/** * 写入一组值 * * @param sheetNum * 写入的sheet的名称 * @param fillRow * 是写入行还是写入列 * @param startRowNum * 开始行号 * @param startColumnNum * 开始列号 * @param contents * 写入的内容数组 * @throws Exception */public void writeArrayToExcel(String sheetName, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents)throws Exception {Sheet sheet = this.workBook.getSheet(sheetName);writeArrayToExcel(sheet, fillRow, startRowNum, startColumnNum, contents);}private void writeArrayToExcel(Sheet sheet, boolean fillRow,int startRowNum, int startColumnNum, Object[] contents)throws Exception {for (int i = 0, length = contents.length; i < length; i++) {int rowNum;int columnNum;// 以行为单位写入if (fillRow) {rowNum = startRowNum;columnNum = startColumnNum + i;}// 以列为单位写入else {rowNum = startRowNum + i;columnNum = startColumnNum;}this.writeToCell(sheet, rowNum, columnNum,convertString(contents[i]));}}/** * 向一个单元格写入值 * * @param sheetNum * sheet的编号 * @param rowNum * 行号 * @param columnNum * 列号 * @param value * 写入的值 * @throws Exception */public void writeToExcel(int sheetNum, int rowNum, int columnNum,Object value) throws Exception {Sheet sheet = this.workBook.getSheetAt(sheetNum);this.writeToCell(sheet, rowNum, columnNum, value);}/** * 向一个单元格写入值 * * @param sheetName * sheet的名称 * @param columnRowNum * 单元格的位置 * @param value * 写入的值 * @throws Exception */public void writeToExcel(String sheetName, int rowNum, int columnNum,Object value) throws Exception {Sheet sheet = this.workBook.getSheet(sheetName);this.writeToCell(sheet, rowNum, columnNum, value);}/** * 向一个单元格写入值 * * @param sheetNum * sheet的编号 * @param columnRowNum * 单元格的位置 * @param value * 写入的值 * @throws Exception */public void writeToExcel(int sheetNum, String columnRowNum, Object value)throws Exception {Sheet sheet = this.workBook.getSheetAt(sheetNum);this.writeToCell(sheet, columnRowNum, value);}/** * 向一个单元格写入值 * * @param sheetNum * sheet的名称 * @param columnRowNum * 单元格的位置 * @param value * 写入的值 * @throws Exception */public void writeToExcel(String sheetName, String columnRowNum, Object value)throws Exception {Sheet sheet = this.workBook.getSheet(sheetName);this.writeToCell(sheet, columnRowNum, value);}private void writeToCell(Sheet sheet, String columnRowNum, Object value)throws Exception {int[] rowNumColumnNum = convertToRowNumColumnNum(columnRowNum);int rowNum = rowNumColumnNum[0];int columnNum = rowNumColumnNum[1];this.writeToCell(sheet, rowNum, columnNum, value);}/** * 将单元格的行列位置转换为行号和列号 * * @param columnRowNum * 行列位置 * @return 长度为2的数组，第1位为行号，第2位为列号 */private static int[] convertToRowNumColumnNum(String columnRowNum) {columnRowNum = columnRowNum.toUpperCase();char[] chars = columnRowNum.toCharArray();int rowNum = 0;int columnNum = 0;for (char c : chars) {if ((c >= 'A' && c <= 'Z')) {columnNum = columnNum * 26 + ((int) c - 64);} else {rowNum = rowNum * 10 + new Integer(c + "");}}return new int[] { rowNum - 1, columnNum - 1 };}private void writeToCell(Sheet sheet, int rowNum, int columnNum,Object value) throws Exception {Row row = sheet.getRow(rowNum);Cell cell = row.getCell(columnNum);if (cell == null) {cell = row.createCell(columnNum);}cell.setCellValue(convertString(value));}/** * 读取一个单元格的值 * * @param sheetName * sheet的名称 * @param columnRowNum * 单元格的位置 * @return * @throws Exception */public Object readCellValue(String sheetName, String columnRowNum)throws Exception {Sheet sheet = this.workBook.getSheet(sheetName);int[] rowNumColumnNum = convertToRowNumColumnNum(columnRowNum);int rowNum = rowNumColumnNum[0];int columnNum = rowNumColumnNum[1];Row row = sheet.getRow(rowNum);if (row != null) {Cell cell = row.getCell(columnNum);if (cell != null) {return getCellValue(cell);}}return null;}/** * 获取单元格中的值 * * @param cell 单元格 * @return */private static Object getCellValue(Cell cell) {int type = cell.getCellType();switch (type) {case Cell.CELL_TYPE_STRING:return (Object) cell.getStringCellValue();case Cell.CELL_TYPE_NUMERIC:Double value = cell.getNumericCellValue();return (Object) (value.intValue());case Cell.CELL_TYPE_BOOLEAN:return (Object) cell.getBooleanCellValue();case Cell.CELL_TYPE_FORMULA:return (Object) cell.getArrayFormulaRange().formatAsString();case Cell.CELL_TYPE_BLANK:return (Object) "";default:return null;}}/** * 插入一行并参照与上一行相同的格式 * * @param sheetNum * sheet的编号 * @param rowNum * 插入行的位置 * @throws Exception */public void insertRowWithFormat(int sheetNum, int rowNum) throws Exception {Sheet sheet = this.workBook.getSheetAt(sheetNum);insertRowWithFormat(sheet, rowNum);}/** * 插入一行并参照与上一行相同的格式 * * @param sheetName * sheet的名称 * @param rowNum * 插入行的位置 * @throws Exception */public void insertRowWithFormat(String sheetName, int rowNum)throws Exception {Sheet sheet = this.workBook.getSheet(sheetName);insertRowWithFormat(sheet, rowNum);}private void insertRowWithFormat(Sheet sheet, int rowNum) throws Exception {sheet.shiftRows(rowNum, rowNum + 1, 1);Row newRow = sheet.createRow(rowNum);Row oldRow = sheet.getRow(rowNum - 1);for (int i = oldRow.getFirstCellNum(); i < oldRow.getLastCellNum(); i++) {Cell oldCell = oldRow.getCell(i);if (oldCell != null) {CellStyle cellStyle = oldCell.getCellStyle();newRow.createCell(i).setCellStyle(cellStyle);}}}/** * 重命名一个sheet * * @param sheetNum * sheet的编号 * @param newName * 新的名称 */public void renameSheet(int sheetNum, String newName) {this.workBook.setSheetName(sheetNum, newName);}/** * 重命名一个sheet * * @param oldName * 旧的名称 * @param newName * 新的名称 */public void renameSheet(String oldName, String newName) {int sheetNum = this.workBook.getSheetIndex(oldName);this.renameSheet(sheetNum, newName);}/** * 删除一个sheet * * @param sheetName * sheet的名称 */public void removeSheet(String sheetName) {this.workBook.removeSheetAt(this.workBook.getSheetIndex(sheetName));}/** * 写入Excel文件并关闭 */public void writeAndClose() {if (this.workBook != null) {try {FileOutputStream fileOutStream = new FileOutputStream(this.excelFile);this.workBook.write(fileOutStream);if (fileOutStream != null) {fileOutStream.close();}} catch (Exception e) {e.printStackTrace();}}if (this.fileInStream != null) {try {this.fileInStream.close();} catch (Exception e) {}}}private static String convertString(Object value) {if (value == null) {return "";} else {return value.toString();}}}???
?
?private static Object getColumnValue(Row row, int i) {Cell cell = row.getCell(i);int type = cell.getCellType();switch (type) {case Cell.CELL_TYPE_STRING:return (Object)cell.getStringCellValue();case Cell.CELL_TYPE_NUMERIC:Double value = cell.getNumericCellValue();return (Object)(value.intValue());case Cell.CELL_TYPE_BLANK:return (Object)"";case Cell.CELL_TYPE_BOOLEAN:return (Object)cell.getBooleanCellValue();case Cell.CELL_TYPE_FORMULA:return (Object)cell.getArrayFormulaRange().formatAsString();default:return null;}}private static Object getColumnValue(Row row, int i) {Cell cell = row.getCell(i);int type = cell.getCellType();switch (type) {case Cell.CELL_TYPE_STRING:return (Object)cell.getStringCellValue();case Cell.CELL_TYPE_NUMERIC:Double value = cell.getNumericCellValue();return (Object)(value.intValue());case Cell.CELL_TYPE_BLANK:return (Object)"";case Cell.CELL_TYPE_BOOLEAN:return (Object)cell.getBooleanCellValue();case Cell.CELL_TYPE_FORMULA:return (Object)cell.getArrayFormulaRange().formatAsString();default:return null;}}
private static Object getColumnValue(Row row, int i) {Cell cell = row.getCell(i);int type = cell.getCellType();switch (type) {case Cell.CELL_TYPE_STRING:return (Object)cell.getStringCellValue();case Cell.CELL_TYPE_NUMERIC:Double value = cell.getNumericCellValue();return (Object)(value.intValue());case Cell.CELL_TYPE_BLANK:return (Object)"";case Cell.CELL_TYPE_BOOLEAN:return (Object)cell.getBooleanCellValue();case Cell.CELL_TYPE_FORMULA:return (Object)cell.getArrayFormulaRange().formatAsString();default:return null;}}