一、综述
在项目中，经常会遇到Excel文件的写入和读取等功能，但是使用POI原生的写法，有些时候较为繁琐，尤其是有合并单元格的情况。因此，我写了一个Excel读写工具，下面将我的设计向大家分享一下。
二、使用jar包
使用的是poi-3.15版本
三、工作簿写入功能设计
封装了三个类，SVWorkbook、SVSheet和SVCell，分别对应POI的Workbook、Sheet以及Cell。
1. SVWorkbook
封装了创建工作簿，创建表格页，关闭工作簿等方法，同时可以使用getWorkBook方法获取POI的工作簿对象，进行POI原生代码的操作。
package com.sv.excel.writer;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* sv工作簿
* @className SVWorkbook.java
* @author 银发Victorique
* @email 823245670@qq.com
* @date 2017年1月23日
*/
public class SVWorkbook {
private Workbook workbook;//POI工作簿对象
private FileOutputStream fos;//文件输出流
/**
* 构造函数，根据路径和文件名创建工作簿和输出流
* @param path 文件路径（不包含文件名称），例如：D:\sv\excel\demo\
* @param fileName 文件名，例如：svexceldemo.xls/svexceldemo.xlsx
*/
public SVWorkbook(String path, String fileName){
//判断文件类型，创建工作簿对象
if(fileName.endsWith(".xls")){
workbook = new HSSFWorkbook();
}else if(fileName.endsWith(".xlsx")){
workbook = new XSSFWorkbook();
}else{
return;
}
//根据文件路径和文件名称，创建文件输出流
try {
fos = new FileOutputStream(path+"/"+fileName);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
/**
* 创建sv表格页，为了更方便的进行行和单元格的添加，创建表格页时，需要填写最大列数，操作时只需添加单元格，换行等操作自动完成
* @author 银发Victorique
* @email 823245670@qq.com
* @param columns 表格的最大列数
* @return sv表格页对象
*/
public SVSheet createSheet(int columns){
return new SVSheet(this.workbook, this.workbook.createSheet(), columns);
}
/**
* 关闭，将创建的工作簿内容写入文件，同时关闭文件输出流
* 注：完成创建全部内容后，必须执行此方法，否则会造成数据没有写入，流没有关闭等严重问题
* @author 银发Victorique
* @email 823245670@qq.com
*/
public void close(){
if(workbook != null){
//写入内容
try {
workbook.write(fos);
} catch (IOException e) {
e.printStackTrace();
} finally {
if(fos != null){
//关闭文件流
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
/**
* 获取POI工作簿对象，此方法用于直接获取POI工作簿对象，使用POI原生的方法进行操作
* @author 银发Victorique
* @email 823245670@qq.com
* @return POI工作簿对象
*/
public Workbook getWorkBook(){
return this.workbook;
}
}2. SVSheet
封装了添加单元格，以及getSheet方法。我的设计理念是，不要每次都创建行，而是通过游标在方法内部进行行的创建。为了实现这一想法，在创建sheet的时候，需要指定数据的总列数（通常情况列数总是有办法算出最大值的），这样每次添加单元格的时候，进行验证当前列游标是否到达总列数，在确定是否自动创建行。当然我还提供了addRow的方法，更加的便利。
package com.sv.excel.writer;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.RichTextString;
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;
/**
* sv表格页
* @className SVSheet.java
* @author 银发Victorique
* @email 823245670@qq.com
* @date 2017年1月23日
*/
public class SVSheet {
private Workbook wb;//POI工作簿对象
private Sheet sheet;//POI表格页对象
private Row row;//POI行对象
private int columns;//表格页总列数，用于自动换行
private int rownum = 0;//行游标，用于记录当前插入单元格的行数
private int colnum = 0;//列游标，用于记录当前插入单元格的列数
private List<String> mergeBuffer = new ArrayList<>();//用于保存进行过合并单元格的单元格坐标，数据格式：行-列
/**
* 构造函数，用于初始化工作簿、表格页、总列数
* @param wb POI工作簿对象
* @param sheet POI表格页对象
* @param columns 表格页最大列数
*/
protected SVSheet(Workbook wb, Sheet sheet, int columns){
this.wb = wb;
this.sheet = sheet;
this.columns = columns;
}
/**
* 添加行
* @author 银发Victorique
* @email 823245670@qq.com
*/
public void addRow(){
row = this.sheet.createRow(rownum);
rownum ++;
colnum = 0;
}
/**
* 添加sv单元格
* @author 银发Victorique
* @email 823245670@qq.com
* @param svCell
*/
public void addCell(SVCell svCell){
//判断单元格是否被合并，并调整游标位置
String key = String.valueOf(this.rownum)+"-"+String.valueOf(this.colnum);
while(mergeBuffer.contains(key)){
colnum ++;
key = String.valueOf(this.rownum)+"-"+String.valueOf(this.colnum);
}
//判断是否需要创建行
if(row == null || colnum >= columns){
row = this.sheet.createRow(rownum);
rownum ++;
colnum = 0;
}
if(svCell.getRowspan() > 1 || svCell.getColspan() >1){
//合并单元格
if(row != null){
sheet.addMergedRegion(new CellRangeAddress(rownum-1, rownum-1+svCell.getRowspan()-1, colnum, colnum+svCell.getColspan()-1));
}else{
sheet.addMergedRegion(new CellRangeAddress(rownum, rownum+svCell.getRowspan()-1, colnum, colnum+svCell.getColspan()-1));
}
//保存合并的单元格坐标
for(int i=0;i<svCell.getRowspan();i++){
for(int j=0;j<svCell.getColspan();j++){
String value = String.valueOf(this.rownum+i)+"-"+String.valueOf(this.colnum+j);
mergeBuffer.add(value);
}
}
}
//创建单元格
Cell cell = row.createCell(colnum);
CellStyle cellStyle = null;
//创建单元格样式
if(svCell.getCellStyle() == null){
cellStyle = wb.createCellStyle();;
}else{
cellStyle = svCell.getCellStyle();
}
//获取单元格的值
Object value = svCell.getCellValue();
//单元格赋值
if(value instanceof Double){
cell.setCellValue((Double)value);
}else if(value instanceof Date){
CreationHelper createHelper = wb.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell.setCellValue((Date)value);
}else if(value instanceof Calendar){
CreationHelper createHelper = wb.getCreationHelper();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
cell.setCellValue((Calendar)value);
}else if(value instanceof RichTextString){
cell.setCellValue((RichTextString)value);
}else if(value instanceof String){
cell.setCellValue((String)value);
}
//添加单元格样式
cell.setCellStyle(cellStyle);
colnum ++;
//设置MyCell对象
svCell.setCell(cell);
}
/**
* 获取POI表格页对象，此方法用于直接获取POI表格页对象，使用POI原生的方法进行操作
* @author 银发Victorique
* @email 823245670@qq.com
* @return
*/
public Sheet getSheet(){
return this.sheet;
}
}3. SVCell
封装了添加跨行、跨列属性，设置单元格样式等方法，以及getCell方法
package com.sv.excel.writer;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.RichTextString;
/**
* sv单元格
* @className SVCell.java
* @author 银发Victorique
* @email 823245670@qq.com
* @date 2017年1月23日
*/
public class SVCell {
private Cell cell;//POI单元格对象
private int colspan = 1;//跨列
private int rowspan = 1;//跨行
private Double doubleValue;//双精度浮点数类型数据
private Date dateValue;//日期类型数据
private Calendar calendarValue;//日历类型数据
private RichTextString richTextStringValue;//富文本类型数据
private String stringValue;//字符串类型数据
private CellStyle cellStyle;//POI单元格样式
/**
* 设置双精度浮点数类型的值
* @author 银发Victorique
* @email 823245670@qq.com
* @param doubleValue
* @return sv单元格对象
*/
public SVCell setCellValue(double doubleValue){
this.resetValues();
this.doubleValue = doubleValue;
return this;
}
/**
* 设置日期类型的值
* @author 银发Victorique
* @email 823245670@qq.com
* @param dateValue
* @return sv单元格对象
*/
public SVCell setCellValue(Date dateValue){
this.resetValues();
this.dateValue = dateValue;
return this;
}
/**
* 设置日历格式的值
* @author 银发Victorique
* @email 823245670@qq.com
* @param calendarValue
* @return sv单元格对象
*/
public SVCell setCellValue(Calendar calendarValue){
this.resetValues();
this.calendarValue = calendarValue;
return this;
}
/**
* 设置POI富文本类型的值
* @author 银发Victorique
* @email 823245670@qq.com
* @param richTextStringValue
* @return sv单元格对象
*/
public SVCell setCellValue(RichTextString richTextStringValue){
this.resetValues();
this.richTextStringValue = richTextStringValue;
return this;
}
/**
* 设置字符串类型的值
* @author 银发Victorique
* @email 823245670@qq.com
* @param stringValue
* @return sv单元格对象
*/
public SVCell setCellValue(String stringValue){
this.resetValues();
this.stringValue = stringValue;
return this;
}
/**
* 获取单元格的值
* @author 银发Victorique
* @email 823245670@qq.com
* @return 返回不为null的值
*/
public Object getCellValue(){
if(doubleValue != null){
return this.doubleValue;
}else if(dateValue != null){
return this.dateValue;
}else if(calendarValue != null){
return this.calendarValue;
}else if(richTextStringValue != null){
return this.richTextStringValue;
}else if(stringValue != null){
return this.stringValue;
}else{
return null;
}
}
/**
* 设置单元格样式
* @author 银发Victorique
* @email 823245670@qq.com
* @param cellStyle POI单元格样式
* @return sv单元格
*/
public SVCell setCellStyle(CellStyle cellStyle){
this.cellStyle = cellStyle;
return this;
}
/**
* 获取单元格样式
* @author 银发Victorique
* @email 823245670@qq.com
* @return POI单元格样式
*/
public CellStyle getCellStyle(){
return this.cellStyle;
}
/**
* 获取POI单元格对象，此方法用于直接获取POI单元格对象，使用POI原生的方法进行操作
* @author 银发Victorique
* @email 823245670@qq.com
* @return POI单元格对象
*/
public Cell getCell(){
return this.cell;
}
/**
* 设置跨列
* @author 银发Victorique
* @email 823245670@qq.com
* @param colspan 跨列数量，默认为1
* @return sv单元格
*/
public SVCell setColspan(int colspan){
this.colspan = colspan;
return this;
}
/**
* 设置跨行
* @author 银发Victorique
* @email 823245670@qq.com
* @param rowspan 跨行数量，默认为1
* @return sv单元格
*/
public SVCell setRowspan(int rowspan){
this.rowspan = rowspan;
return this;
}
/**
* 设置单元格
* @author 银发Victorique
* @email 823245670@qq.com
* @param cell POI单元格
*/
protected void setCell(Cell cell){
this.cell = cell;
}
/**
* 获取跨列
* @author 银发Victorique
* @email 823245670@qq.com
* @return 跨列数
*/
protected int getColspan(){
return this.colspan;
}
/**
* 获取跨行
* @author 银发Victorique
* @email 823245670@qq.com
* @return 跨行数
*/
protected int getRowspan(){
return this.rowspan;
}
/**
* 重置单元格的值
* @author 银发Victorique
* @email 823245670@qq.com
*/
private void resetValues(){
doubleValue = null;
dateValue = null;
calendarValue = null;
richTextStringValue = null;
stringValue = null;
}
}4. 测试代码
SVWorkbook wb = new SVWorkbook("./", "workbook.xlsx");
SVSheet sheet = wb.createSheet(4);
SVCell cell = new SVCell();
cell.setCellValue("test");
sheet.addCell(cell);
cell = new SVCell();
cell.setCellValue(Calendar.getInstance());
cell.setColspan(3);
cell.setRowspan(2);
sheet.addCell(cell);
cell = new SVCell();
cell.setCellValue(123.231);
sheet.addCell(cell);
wb.close();四、工作簿读取功能设计
该功能一共实现了三种读出格式
1. 二维数组，值的数据类型为原始数据类型
/**
* 获取单元格原始数据
* @author 银发Victorique
* @email 823245670@qq.com
* @param cell 单元格
* @return 原始数据
*/
@SuppressWarnings("deprecation")
private static Object getCellValue(Cell cell){
if(cell == null){
return null;
}
switch (cell.getCellTypeEnum()) {
case STRING:
return cell.getRichStringCellValue();
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
return cell.getDateCellValue();
} else {
return cell.getNumericCellValue();
}
case BOOLEAN:
return cell.getBooleanCellValue();
case FORMULA:
return cell.getCellFormula();
case BLANK:
return null;
default:
return null;
}
}/**
* 获取原始数据类型的数组
* @author 银发Victorique
* @email 823245670@qq.com
* @param path 文件路径
* @param filename 文件名称
* @param sheetnum 表格页编号，从0开始
* @return 表格数据数组
*/
public static Object[][] getOriginalArray(String path, String filename, int sheetnum){
Object[][] returnArray = null;
InputStream is = null;
Workbook wb = null;
try {
is = new FileInputStream(path+"/"+filename);
if(filename.endsWith(".xls")){
wb = new HSSFWorkbook(is);
}else if(filename.endsWith(".xlsx")){
wb = new XSSFWorkbook(is);
}else{
return null;
}
int maxcol = 0;
for(Row row : wb.getSheetAt(sheetnum)){
if(row.getPhysicalNumberOfCells() > maxcol){
maxcol = row.getPhysicalNumberOfCells();
}
}
returnArray = new Object[wb.getSheetAt(sheetnum).getPhysicalNumberOfRows()][maxcol];
for(int i=0; i<wb.getSheetAt(sheetnum).getPhysicalNumberOfRows(); i++){
Row row = wb.getSheetAt(sheetnum).getRow(i);
for(int j=0; j<maxcol; j++){
Cell cell = row.getCell(j);
if(cell != null){
returnArray[i][j] = getCellValue(cell);
}else{
returnArray[i][j] = "";
}
}
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if(wb != null){
wb.close();
}
} catch (IOException e1) {
e1.printStackTrace();
} finally {
if(is != null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
return returnArray;
}2. List
/**
* 获取List<Map<String,Object>>类型的表格数据
* 要求导入的EXCEL文件有这样的格式：
* 第一行为表头，必须全部为字符串型数据，否则会抛出异常
* 之后的每一行的数据，都会以其对应的表头作为键存储在map中
* 如果某一单元格对应的表头为空，则此单元格数据被舍弃
* @author 银发Victorique
* @email 823245670@qq.com
* @param path 文件路径
* @param filename 文件名称
* @param sheetnum 表格页编号，从0开始
* @return 表格页数据
*/
public static List<Map<String, Object>> getMapList(String path, String filename, int sheetnum){
List<Map<String, Object>> returnList = new ArrayList<>();
InputStream is = null;
Workbook wb = null;
try {
is = new FileInputStream(path+"/"+filename);
if(filename.endsWith(".xls")){
wb = new HSSFWorkbook(is);
}else if(filename.endsWith(".xlsx")){
wb = new XSSFWorkbook(is);
}else{
return null;
}
Row firstRow = wb.getSheetAt(sheetnum).getRow(0);
for(int i=1; i<wb.getSheetAt(sheetnum).getPhysicalNumberOfRows(); i++){
Row row = wb.getSheetAt(sheetnum).getRow(i);
Map<String, Object> map = new HashMap<>();
for(int j=0; j<row.getPhysicalNumberOfCells(); j++){
Cell cell = row.getCell(j);
map.put(firstRow.getCell(j).getStringCellValue(), getCellValue(cell));
}
returnList.add(map);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
if(wb != null){
wb.close();
}
} catch (IOException e1) {
e1.printStackTrace();
} finally {
if(is != null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
return returnList;
}3. javabean list格式
package com.sv.excel.reader;
import java.util.Map;
/**
* Excel抽象类，用于读取数据为对象集合
* @className Excel.java
* @author 银发Victorique
* @email 823245670@qq.com
* @date 2017年1月23日
*/
public abstract class Excel {
/**
* 获取存放表头-字段对应关系表
* 数据格式为：{表头名称:字段名称}
* 阅读器将根据该关系表给字段赋值
* @author 银发Victorique
* @email 823245670@qq.com
* @return 表头-字段对应关系表
*/
public abstract Map<String, String> getLabelField();
}/**
* 获取bean集合类型的表格页数据
* 要求导入的EXCEL文件有这样的格式：
* 第一行为表头，必须全部为字符串型数据，否则会抛出异常
* 实例excel必须是抽象类Excel的子类的实例，并且getLabelField方法获取的Map需要与表头相对应，否则会被舍弃
* 之后的每一行的数据，都会以其对应的表头和getLabelField获取的Map作为依据，存放到Bean的对应字段中
* 如果某一单元格对应的表头为空，则此单元格数据被舍弃
* @author 银发Victorique
* @email 823245670@qq.com
* @param path 文件路径
* @param filename 文件名
* @param sheetnum 表格页编号，从0开始
* @param excel 抽象类Excel的子类的实例
* @return 表格页数据
*/
public static <T extends Excel> List<T> getBeanList(String path, String filename, int sheetnum, T excel){
List<T> returnList = new ArrayList<>();
Map<String, String> lfMap = excel.getLabelField();
InputStream is = null;
Workbook wb = null;
try {
is = new FileInputStream(path+"/"+filename);
if(filename.endsWith(".xls")){
wb = new HSSFWorkbook(is);
}else if(filename.endsWith(".xlsx")){
wb = new XSSFWorkbook(is);
}else{
return null;
}
Row firstRow = wb.getSheetAt(sheetnum).getRow(0);
for(int i=1; i<wb.getSheetAt(sheetnum).getPhysicalNumberOfRows(); i++){
Row row = wb.getSheetAt(sheetnum).getRow(i);
@SuppressWarnings("unchecked")
T instance = (T) excel.getClass().getConstructor().newInstance();
for(int j=0; j<row.getPhysicalNumberOfCells(); j++){
Cell cell = row.getCell(j);
String field = lfMap.get(firstRow.getCell(j).getStringCellValue());
if(field != null){
Object value = getCellValue(cell);
Class<?> clazz = null;{
if(value instanceof RichTextString){
clazz = String.class;
value = ((RichTextString)value).getString();
}else{
clazz = value.getClass();
}
}
try {
Method method = instance.getClass().getMethod("set"+field.substring(0,1).toUpperCase()+field.substring(1), clazz);
method.invoke(instance, value);
} catch (Exception e) {
if(value instanceof Double){
try {
Method method = instance.getClass().getMethod("set"+field.substring(0,1).toUpperCase()+field.substring(1), Integer.class);
method.invoke(instance, ((Double)value).intValue());
} catch(Exception e1){
Method method = instance.getClass().getMethod("set"+field.substring(0,1).toUpperCase()+field.substring(1), BigDecimal.class);
BigDecimal bd = new BigDecimal((Double)value);
method.invoke(instance, bd);
}
}else{
e.printStackTrace();
}
}
}
}
returnList.add(instance);
}
} catch (IOException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} finally {
try {
if(wb != null){
wb.close();
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if(is != null){
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
return returnList;
}4. 测试代码
//getOriginalArray方法测试
Object[][] rows = SVExcelReader.getOriginalArray("./", "workbook.xlsx", 0);
for (Object[] cols : rows) {
for (Object obj : cols) {
System.out.print(obj+"\t");
}
System.out.println();
}
//getMapList方法测试
List<Map<String, Object>> rows = SVExcelReader.getMapList("./", "workbook.xlsx", 0);
for (Map<String, Object> cols : rows) {
System.out.println(cols);
}
//getBeanList方法测试
List<Bean> rows = SVExcelReader.getBeanList("./", "workbook.xlsx", 0, new Bean());
for (Bean bean : rows) {
System.out.println(bean);
}
914
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
