对 Excel 进行读写操作是生产环境下常见的业务，网上搜索的实现方式都是基于POI和JXL第三方框架，但都不是很全面。小编由于这两天刚好需要用到，于是就参考手写了一个封装操作工具，基本涵盖了Excel表（分有表头和无表头）的创建，并对它们进行读写操作。为方便大家，有需要者可以点击文后点解下载直接使用哦，当然也可以根据自己需求举一反三自己定制，相信对于聪明的你也不是什么难事。话不多说，直接贴源码
pom.xml 文件：
UTF-8 1.8 1.8 junit junit4.11 test org.apache.poi poi3.17 org.projectlombok lombok1.18.0 provided org.slf4j slf4j-log4j121.8.0-beta2 test log4j log4j1.2.17 org.slf4j slf4j-api1.8.0-beta2
建表工具类：ExcelBuider.java
@Slf4j
public class ExcelBuilder {
private static HSSFSheet sheet;
private static HSSFWorkbook wb;
private static boolean hasHeader;
public ExcelBuilder(String excellName) {
wb = new HSSFWorkbook();
sheet = wb.createSheet(excellName);
}
public ExcelBuilder header(String... value) {
if (value != null && value.length != 0) {
//设置表头样式
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font("黑体", true, 12));
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < value.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(value[i]);
cell.setCellStyle(cellStyle);
}
hasHeader = true;
}
return this;
}
public ExcelBuilder content(List> content) {
if (content != null && !content.isEmpty()) {
int index;
for (int i = 0; i < content.size(); i++) {
index = hasHeader == false ? i : i + 1;
HSSFRow row = sheet.createRow(index);
for (int j = 0; j < content.get(i).size(); j++) {
String r = "";
Object value = content.get(i).get(j);
//根据数据类型装配
if (value instanceof String) {
r = (String) value;
} else if (value instanceof Number) {
r = String.valueOf(value);
} else if (value instanceof BigDecimal) {
r = String.valueOf(value);
} else {
if (!(value instanceof Date) && !(value instanceof Timestamp)) {
if (!(value instanceof ZonedDateTime) && !(value instanceof LocalDateTime)) {
if (value instanceof Enum) {
r = ((Enum) value).name();
} else if (value != null) {
log.info("Error of create row, Unknow field type: " + value.getClass().getName());
}
} else {
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
r = formatter.format((TemporalAccessor) value);
}
} else {
DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
r = sdf.format(value);
}
}
row.createCell(j).setCellValue(r);
}
}
}
return this;
}
public ExcelBuilder autoColumnWidth() {
for (int j = 0; j < sheet.getRow(0).getLastCellNum(); j++) {
int maxLength = 0;
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
String value = sheet.getRow(i).getCell(j).getStringCellValue();
int length = 0;
if (value != null) {
length = value.getBytes().length;
}
if (length > maxLength) {
maxLength = length;
}
}
sheet.setColumnWidth(j, maxLength > 30 ? (30 * 256 + 186) : (maxLength * 256 + 186));
}
return this;
}
public AbstractExcel build(Boolean hasHeader) {
return hasHeader ? new HeaderExcel(sheet) : new NoHeaderExcel(sheet);
}
private HSSFFont font(String fontName, boolean isBold, int fontSize) {
HSSFFont font = wb.createFont();
if (fontName != null) font.setFontName(fontName);
else font.setFontName("黑体");
font.setBold(isBold);
font.setFontHeightInPoints((short) fontSize);
return font;
}
}
excel的抽象父类：
public abstract class AbstractExcel {
private final HSSFSheet sheet;
public AbstractExcel() {
HSSFWorkbook wb = new HSSFWorkbook();
sheet = wb.createSheet();
}
public AbstractExcel(String sheetName){
HSSFWorkbook wb = new HSSFWorkbook();
sheet = wb.createSheet(sheetName);
}
public AbstractExcel(HSSFSheet sheet) {
this.sheet = sheet;
}
public abstract List> getPayload();
public void write(OutputStream op) throws IOException {
sheet.getWorkbook().write(op);
sheet.getWorkbook().close();
}
public String getStringFormatCellValue(HSSFCell cell) {
String cellVal = "";
DecimalFormat df = new DecimalFormat("#");
switch (cell.getCellTypeEnum()) {
case STRING:
cellVal = cell.getStringCellValue();
break;
case NUMERIC:
String dataFormat = cell.getCellStyle().getDataFormatString();
if (DateUtil.isCellDateFormatted(cell)) {
cellVal = df.format(cell.getDateCellValue());
} else if ("@".equals(dataFormat)) {
cellVal = df.format(cell.getNumericCellValue());
} else {
cellVal = String.valueOf(cell.getNumericCellValue());
df = new DecimalFormat("#.#########");
cellVal = df.format(Double.valueOf(cellVal));
}
break;
case BOOLEAN:
cellVal = String.valueOf(cell.getBooleanCellValue());
break;
case FORMULA:
cellVal = String.valueOf(cell.getCellFormula());
break;
default:
cellVal = "";
}
return cellVal;
}
}
有表头实现类
public class HeaderExcel extends AbstractExcel {
private final static boolean hasHeader = true;
private final HSSFSheet sheet;
public HeaderExcel(HSSFSheet sheet) {
super(sheet);
this.sheet = sheet;
}
public HeaderExcel(String sheetName, String excelPath) {
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(excelPath)));
} catch (IOException e) {
e.printStackTrace();
}
sheet = sheetName == null || sheetName.isEmpty() ? wb.getSheetAt(0) : wb.getSheet(sheetName);
}
@Override
public List> getPayload() {
List> payLoad = new ArrayList<>();
HSSFRow headRow = sheet.getRow(0);
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
HSSFRow currentRow = sheet.getRow(i);
Map map = new HashMap<>();
for (int j = 0; j < sheet.getRow(i).getLastCellNum(); j++) {
map.put(getStringFormatCellValue(headRow.getCell(j)), getStringFormatCellValue(currentRow.getCell(j)));
}
payLoad.add(map);
}
return payLoad;
}
}
无表头实现类
public class NoHeaderExcel extends AbstractExcel {
private final static boolean hasHeader = false;
private HSSFSheet sheet;
public NoHeaderExcel(HSSFSheet sheet) {
super(sheet);
this.sheet = sheet;
}
public NoHeaderExcel(String sheetName, String excelPath) {
HSSFWorkbook wb = null;
try {
wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(excelPath)));
} catch (IOException e) {
e.printStackTrace();
}
sheet = sheetName == null || sheetName.isEmpty() ? wb.getSheetAt(0) : wb.getSheet(sheetName);
}
@Override
public List> getPayload() {
List> payLoad = new ArrayList<>();
for (int i = 0; i < sheet.getLastRowNum(); i++) {
HSSFRow currentRow = sheet.getRow(i);
Map map = new HashMap<>();
for (int j = 0; j <= sheet.getRow(i).getLastCellNum(); j++) {
map.put(String.valueOf(j), getStringFormatCellValue(currentRow.getCell(j)));
}
payLoad.add(map);
}
return payLoad;
}
}
测试工具类：
public class AppTest
{
@Test
public void testExportExcel()
{
//测试数据
String[] headers = new String[]{"A","B","C","D","E"};
List> valueList = new linkedList<>();
for (char i = 'A'; i <= 'E' ; i++) {
List
附图：
测试1
测试二：
看起来效果还不错，当然还有很多不完善的地方，有需要的朋友可以在此基础上扩展定制，例如读取表数据结构方式，实现行数增删改查据或者创建表标题等等。
或者有朋友有更好的实现方案，欢迎前来交流！
最后的最后，当然忘不了附上笨工具的源码啦！
https://github.com/yumiaoxia/excel-commom-demo.git
