欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

操作Excel工具类(基于Apache的POI类库)

程序员文章站 2022-04-28 14:15:40
...

上一篇介绍的是基于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();
		}
	}

}