这是一个简单的对poi的封装，只能简单的取值，设值，拷贝行，插入行等。
针对读取Excel模板后，填值再保存的应用，比较方便。
poi版本：3.13
贴代码：
package cn.com.gtmc.glaf2.util; import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map; import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
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.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
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.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelUtil {
private Workbook workbook;
private InputStream inputStream; private int activeSheetIndex = 0;
private FormulaEvaluator formulaEvaluator;
// 日期的格式化字符串
private String datePattern = "yyyy/MM/dd"; public void setDatePattern(String datePattern) {
this.datePattern = datePattern;
} private Map<String, CellStyle> mapCellStyle = new HashMap<>(); /**
* 取得cell的格式化样式
*
* @param format
* 这个format是Excel单元格支持的格式字符串 例如： 整数：#，##0 浮点数：#，##0.00 日期：yyyy/mm/dd
* yyyy"年"m"月"d"日"
* @return CellStyle
*/
private CellStyle getFormatCellStyle(String format) {
if (!mapCellStyle.containsKey(format)) {
CellStyle style = this.workbook.createCellStyle();
DataFormat df = this.workbook.createDataFormat();
style.setDataFormat(df.getFormat(format));
mapCellStyle.put(format, style);
}
return mapCellStyle.get(format);
} // 序号从0开始
/**
* @param file
* excel文件的路径
* @throws Exception
*/
public ExcelUtil(File file) throws Exception { String fileName = file.getName(); String prefix = fileName.substring(fileName.lastIndexOf('.') + 1);
inputStream = new FileInputStream(file);
Throwable localThrowable3 = null;
try {
if ("xls".equals(prefix)) {
workbook = new HSSFWorkbook(inputStream);
formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
} else if ("xlsx".equals(prefix)) {
workbook = new XSSFWorkbook(inputStream);
formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
}
} catch (Throwable localThrowable1) {
localThrowable3 = localThrowable1;
throw localThrowable1;
} finally {
if (inputStream != null) {
if (localThrowable3 != null) {
try {
inputStream.close();
} catch (Throwable localThrowable2) {
localThrowable3.addSuppressed(localThrowable2);
}
} else {
inputStream.close();
}
}
}
if (workbook == null) {
throw new RuntimeException("input file error");
}
} /**
* 保存excel到指定文件
*
* @throws IOException
*/
public void save(File file) throws IOException {
FileOutputStream fs = new FileOutputStream(file);
workbook.write(fs);
} /**
* 关闭excel
*
* @throws IOException
*/
public void close() throws IOException {
if (inputStream != null) {
inputStream.close();
}
workbook.close();
} /**
* 指定 当前活动的Sheet （默认是0）
*
* @param sheetIndex
* Sheet 页，从 0 开始
*/
public void setActiveSheet(int sheetIndex) {
this.activeSheetIndex = sheetIndex;
this.workbook.setActiveSheet(sheetIndex);
} /**
* 设定Sheet名
*
* @param sheetIndex
* Sheet 页，从 0 开始
* @param sheetName
* Sheet名
*/
public void setSheetName(int sheetIndex, String sheetName) {
this.workbook.setSheetName(sheetIndex, sheetName);
} /**
*
* 设置 cell 位置的单元格值
*
* @param cell
* 单元格的位置，如：A1,B2
* @param value
* 值
*/
public void setValueAt(String cell, Object value) {
CellAddress address = new CellAddress(cell);
setValueAt(address.getRow(), address.getCol(), value);
} /**
*
* 设置row 和 column 位置的单元格值
*
* @param rowIndex
* 指定行，从0开始
* @param colIndex
* 指定列，从0开始
* @param value
* 值
*/
public void setValueAt(int rowIndex, int colIndex, Object value) {
Sheet sheet = workbook.getSheetAt(this.activeSheetIndex);
Cell cell = getOrCreateCell(sheet, rowIndex, colIndex);
if (value == null) {
cell.setCellValue("");
} else {
if (value instanceof Integer) {
cell.setCellStyle(this.getFormatCellStyle("#,##0"));
cell.setCellValue((Integer) value); } else if (value instanceof Long) {
cell.setCellStyle(this.getFormatCellStyle("#,##0"));
cell.setCellValue((Long) value);
} else if (value instanceof Float) {
cell.setCellStyle(this.getFormatCellStyle("#,##0.00"));
cell.setCellValue((Float) value);
} else if (value instanceof Double) {
cell.setCellStyle(this.getFormatCellStyle("#,##0.00"));
cell.setCellValue((Double) value);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if (value instanceof Date) {
cell.setCellStyle(this.getFormatCellStyle("yyyy/mm/dd"));
cell.setCellValue((Date) value);
} else {
// 其他的，直接输出为字符串
cell.setCellValue(value.toString());
}
}
} /**
* 设定指定单元格的公式
*
* @param cell
* @param formula
*/
public void setFormulaAt(String cell, String formula) {
CellAddress address = new CellAddress(cell);
setFormulaAt(address.getRow(), address.getCol(), formula);
} /**
* 设定指定单元格的公式
*
* @param rowIndex
* @param colIndex
* @param formula
*/
public void setFormulaAt(int rowIndex, int colIndex, String formula) {
Sheet sheet = workbook.getSheetAt(this.activeSheetIndex);
Cell cell = getOrCreateCell(sheet, rowIndex, colIndex);
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula(formula);
} /**
* 取得指定位置的Cell（没有就创建新的Cell）
*
* @param sheet
* @param rowIndex
* @param colIndex
* @return
*/
private Cell getOrCreateCell(Sheet sheet, int rowIndex, int colIndex) {
Row row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
Cell cell = row.getCell(colIndex);
if (cell == null) {
cell = row.createCell(colIndex);
}
return cell;
} /**
*
* 返回 cell 位置的单元格值(String类型)
*
* @param cell
* 单元格的位置，如：A1,B2
* @return
*
*/
public String getValueAt(String cell) {
if (cell == null || "".equals(cell)) {
return "";
}
CellAddress address = new CellAddress(cell);
return this.getValueAt(address.getRow(), address.getCol());
} /**
*
* 返回 row 和 column 位置的单元格值
*
* @param rowIndex
* 指定行，从0开始
* @param colIndex
* 指定列，从0开始
* @return
*
*/
public String getValueAt(int rowIndex, int colIndex) {
Sheet sheet = workbook.getSheetAt(this.activeSheetIndex);
Row row = sheet.getRow(rowIndex);
if (row != null) {
return getCellValueToString(row.getCell(colIndex));
}
return "";
} /**
*
* 转换单元格的类型为String 默认的 <br>
* 默认的数据类型：CELL_TYPE_BLANK(3), CELL_TYPE_BOOLEAN(4),
* CELL_TYPE_ERROR(5),CELL_TYPE_FORMULA(2), CELL_TYPE_NUMERIC(0),
* CELL_TYPE_STRING(1)
*
* @param cell
* @return
*
*/
private String getCellValueToString(Cell cell) {
String strCell = "";
if (cell == null) {
return null;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
strCell = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat(datePattern);
strCell = sdf.format(cell.getDateCellValue());
} else {
strCell = String.valueOf(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_STRING:
strCell = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
strCell = String.valueOf(formulaEvaluator.evaluate(cell).getNumberValue());
break;
default:
break;
}
return strCell;
} /**
* 获取当前操作的excel的workbook
* 注意：
* 一般的简单操作都可以通过ExcelUtil来完成。
* 一些复杂的特殊的要求，ExcelUtil暂时没有提供，故开放workbook接口，
* 由外部调用来自己操作poi完成。
* @return Workbook
*/
public Workbook getWorkbook() {
return this.workbook;
} private void insertRow(int starRow, int rows) {
Sheet sheet = this.workbook.getSheetAt(this.activeSheetIndex);
sheet.shiftRows(starRow + 1, sheet.getLastRowNum(), rows, true, false);
// Parameters:
// startRow - the row to start shifting
// endRow - the row to end shifting
// n - the number of rows to shift
// copyRowHeight - whether to copy the row height during the shift
// resetOriginalRowHeight - whether to set the original row's height to the
// default
} /**
* 拷贝行，并将拷贝的行插入到拷贝行的下面
*
* @param startRow
* 拷贝开始行 从0开始
* @param endRow
* 拷贝结束行 从0开始
*/
public void copyAndInsertRows(int startRow, int endRow) {
int rowCount = endRow - startRow + 1;
this.insertRow(endRow, rowCount);
this.copyRows(startRow, endRow, startRow + rowCount);
} /**
* 拷贝行，并将拷贝的行粘贴到指定的位置
*
* @param startRow
* 开始行 从0开始
* @param endRow
* 结束行 从0开始
* @param pPosition
* 粘贴目标行的起始位置 从0开始
*/
public void copyRows(int startRow, int endRow, int pPosition) {
Sheet sheet = this.workbook.getSheetAt(this.activeSheetIndex);
int pStartRow = startRow;
int pEndRow = endRow;
int targetRowFrom;
int targetRowTo;
int columnCount;
CellRangeAddress region = null;
int i;
int j;
if (pStartRow == -1 || pEndRow == -1) {
return;
}
// 拷贝合并的单元格
for (i = 0; i < sheet.getNumMergedRegions(); i++) {
region = sheet.getMergedRegion(i);
if ((region.getFirstRow() >= pStartRow) && (region.getLastRow() <= pEndRow)) {
targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
targetRowTo = region.getLastRow() - pStartRow + pPosition;
CellRangeAddress newRegion = region.copy();
newRegion.setFirstRow(targetRowFrom);
newRegion.setFirstColumn(region.getFirstColumn());
newRegion.setLastRow(targetRowTo);
newRegion.setLastColumn(region.getLastColumn());
sheet.addMergedRegion(newRegion);
}
}
// 设置列宽
for (i = pStartRow; i <= pEndRow; i++) {
Row sourceRow = sheet.getRow(i);
columnCount = sourceRow.getLastCellNum();
if (sourceRow != null) {
Row newRow = sheet.createRow(pPosition - pStartRow + i);
newRow.setHeight(sourceRow.getHeight());
for (j = 0; j < columnCount; j++) {
Cell templateCell = sourceRow.getCell(j);
if (templateCell != null) {
Cell newCell = newRow.createCell(j);
copyCell(templateCell, newCell);
}
}
}
}
} private void copyCell(Cell srcCell, Cell distCell) {
distCell.setCellStyle(srcCell.getCellStyle());
if (srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
int srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == Cell.CELL_TYPE_STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == Cell.CELL_TYPE_BLANK) {
// nothing21
} else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == Cell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else { // nothing29 }
} /**
* Common conversion functions between Excel style A1, C27 style cell
* references, and POI usermodel style row=0, column=0 style references. eg "A1"
* and "BA2"
*/
public static final class CellAddress { private int _rowIndex;
private int _colIndex; /**
* @param cellAddr
* Excel style A1, C27
*/
public CellAddress(String cellAddr) {
String strColumn = "";
String strRow = "";
for (int i = 0; i < cellAddr.length(); i++) {
char c = cellAddr.charAt(i);
if (!Character.isDigit(c)) {
strColumn += c;
} else {
strRow += c;
}
}
this._rowIndex = Integer.parseInt(strRow) - 1;
this._colIndex = convertColStringToIndex(strColumn);
} /**
* @param rowIndex
* 0-based rowIndex
* @param colIndex
* 0-based colIndex
*/
public CellAddress(int rowIndex, int colIndex) {
this._rowIndex = rowIndex;
this._colIndex = colIndex;
} /**
* @return 0-based rowIndex
*/
public int getRow() {
return this._rowIndex;
} /**
* @return 0-based colIndex
*/
public int getCol() {
return this._colIndex;
} /**
* @return Excel style A1, C27
*/
public String formatAsString() {
StringBuffer sb = new StringBuffer();
sb.append(convertNumToColString(this._colIndex));
sb.append((this._rowIndex + 1));
return sb.toString();
} /**
* Converts an Excel column name like "C" to a zero-based index.
*
* @param name
* @return Index corresponding to the specified name
*/
private static int convertColStringToIndex(String name) {
int column = 0;
for (int i = 0; i < name.length(); ++i) {
int c = name.charAt(i);
column = (column * 26) + (c - 'A' + 1);
}
return column - 1;
} /**
* Takes in a 0-based base-10 column and returns a ALPHA-26 representation. eg
* column #3 -> D
*/
public static String convertNumToColString(int col) {
// Excel counts column A as the 1st column, we
// treat it as the 0th one
int excelColNum = col + 1; StringBuilder colRef = new StringBuilder(2);
int colRemain = excelColNum; while (colRemain > 0) {
int thisPart = colRemain % 26;
if (thisPart == 0) {
thisPart = 26;
}
colRemain = (colRemain - thisPart) / 26; // The letter A is at 65
char colChar = (char) (thisPart + 64);
colRef.insert(0, colChar);
} return colRef.toString();
}
}
}
测试类：
package cn.com.gtmc.glaf2.util; import static org.junit.Assert.assertEquals; import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date; import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.junit.Test;
import org.springframework.test.annotation.Rollback;
import org.springframework.transaction.annotation.Transactional; import cn.com.gtmc.glaf2.base.BaseTest; public class ExcelUtilTest extends BaseTest{ @Transactional
@Test
@Rollback(true)
public void cellAddressTest(){
ExcelUtil.CellAddress cellAddress = new ExcelUtil.CellAddress("A1");
assertEquals(0, cellAddress.getRow()); cellAddress = new ExcelUtil.CellAddress("B2");
assertEquals(1, cellAddress.getRow());
assertEquals(1, cellAddress.getCol()); cellAddress = new ExcelUtil.CellAddress(2,6);
assertEquals("G3", cellAddress.formatAsString());
} @Transactional
@Test
@Rollback(true)
public void readTemplateTest(){
File file = new File("D:\\TEST\\template.xlsx");
ExcelUtil util = null ;
try {
util = new ExcelUtil(file);
util.setActiveSheet(0);
// 字符串
util.setValueAt("B5", "100.25");
util.setValueAt("B6", "正则表达式");
// 公式
util.setFormulaAt("B7", "SUM(A1:A2)");
// 各种数据类型
int a = 100;
util.setValueAt("B8", a);
long b = 2500000l;
util.setValueAt("B9", b);
float c = 100.2569f;
util.setValueAt("B10", c); double d = 100.2569d;
util.setValueAt("B11", d); boolean bb = true;
util.setValueAt("B12", bb); util.setValueAt("B13", new Date()); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
util.save(new File("D:\\TEST\\" + "Temp" + sdf.format(new Date()) + ".xlsx"));
} catch (Exception e) {
e.printStackTrace();
}finally{
if(util!=null) {
try {
util.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
} @Transactional
@Test
@Rollback(true)
public void insertRowTest(){
File file = new File("D:\\TEST\\templateList.xlsx");
ExcelUtil util = null ;
try {
util = new ExcelUtil(file);
util.setActiveSheet(0); //util.insertRow(4, 2);
util.copyAndInsertRows(3, 4); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
util.save(new File("D:\\TEST\\" + "Temp" + sdf.format(new Date()) + ".xlsx"));
} catch (Exception e) {
e.printStackTrace();
}finally{
if(util!=null) {
try {
util.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
} @Transactional
@Test
@Rollback(true)
public void RowColNumberTest(){
File file = new File("D:\\TEST\\templateRowCell.xlsx");
ExcelUtil util = null ;
try {
util = new ExcelUtil(file);
util.setActiveSheet(0); Sheet sheet = util.getWorkbook().getSheetAt(0); System.out.println("getLastRowNum：" + sheet.getLastRowNum());
System.out.println("getFirstRowNum：" + sheet.getFirstRowNum());
System.out.println("getLeftCol：" + sheet.getLeftCol());
System.out.println("getTopRow：" + sheet.getTopRow());
System.out.println("getPhysicalNumberOfRows：" + sheet.getPhysicalNumberOfRows()); Row row = sheet.getRow(1);
System.out.println("getFirstCellNum：" + row.getFirstCellNum());
System.out.println("getLastCellNum：" + row.getLastCellNum());
System.out.println("getPhysicalNumberOfCells：" + row.getPhysicalNumberOfCells()); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
util.save(new File("D:\\TEST\\" + "Temp" + sdf.format(new Date()) + ".xlsx"));
} catch (Exception e) {
e.printStackTrace();
}finally{
if(util!=null) {
try {
util.close();
} catch (Exception e2) {
e2.printStackTrace();
}
}
}
}
}