详解java封装实现Excel建表读写操作

网友投稿 270 2023-01-22


详解java封装实现Excel建表读写操作

对 Excel 进行读写操作是生产环境下常见的业务,网上搜索的实现方式都是基于POI和JXL第三方框架,但都不是很全面。由于这两天刚好需要用到,于是就参考手写了一个封装操作工具,基本涵盖了Excel表(分有表头和无表头)的创建,并对它们进行读写操作。为方便大家,有需要者可以点击文后点解下载直接使用哦,当然也可以根据自己需求举一反三自己定制,相信对于聪明的你也不是什么难事。话不多说,直接贴源码

pom.xml 文件:

UTF-8

1.8

1.8

junit

junit

4.11

test

org.apache.poi

poi

3.17

org.projectlombok

lombok

1.18.0

provided

org.slf4j

slf4j-log4j12

1.8.0-beta2

test

log4j

log4j

1.2.17

org.slf4j

slf4j-api

1.8.0-beta2

建表工具类:ExcelBuider.java

/**

* 建表工具类

* @author Sherman

* email:1253950375@qq.com

* created in 2018/8/24

*/

@Slf4j

public class ExcelBuilder {

private static HSSFSheet sheet;

private static HSSFWorkbook wb;

private static boolean hasHeader;

/**

* 初始化

* @param excellName 表名

*/

public ExcelBuilder(String excellName) {

wb = new HSSFWorkbook();

sheet = wb.createSheet(excellName);

}

/**

* 设置表头,装配表头数据

* @param value 字符串数组,用来作为表头的值

*

*/

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;

}

/**

* excel 表内容装配

* @param content 待装配表格内容的二维数组

* @return

*/

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;

}

/**

* 实例化

* @param hasHeader 是否有表头

* @return Excel表格

*/

public AbstractExcel build(Boolean hasHeader) {

return hasHeader ? new HeaderExcel(sheet) : new NoHeaderExcel(sheet);

}

/**

*

* @param fontName 字体名字

* @param isBold 是否粗体

* @param fontSize 字体大小

* @return 字体

*/

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的抽象父类:

/**

* @author Sherman

* created in 2018/8/24

*/

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();

http:// 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;

}

}

有表头实现类

/**

* @author Sherman

* created in 2018/8/24

*/

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 = nullmgFMeTrOV;

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.gethttp://Row(i).getLastCellNum(); j++) {

map.put(getStringFormatCellValue(headRow.getCell(j)), getStringFormatCellValue(currentRow.getCell(j)));

}

payLoad.add(map);

}

return payLoad;

}

}

无表头实现类

/**

* @author Sherman

* created in 2018/8/24

*/

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;

}

}

测试工具类:

/**

* Unit test for simple App.

*/

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 rowList = new LinkedList<>();

for (int j = 0; j <= 4; j++) {

rowList.add(i+String.valueOf(j));

}

valueList.add(rowList);

}

AbstractExcel excel = new ExcelBuilder("报名表")

.header(headers)

.content(valueList)

.autoColumnWidth()

.build(true);

try {

File file = new File("E:\\excel\\test.xls");

FileOutputStream op = new FileOuthttp://putStream(file);

excel.write(op);

} catch (IOException e) {

e.printStackTrace();

}

}

/**

* 测试读取表数据操作

*/

@Test

public void testImportExcel(){

AbstractExcel excel = new HeaderExcel(null,"E:/excel/test.xls");

List> values = excel.getPayload();

values.forEach(stringStringMap -> {

stringStringMap.entrySet().forEach(stringStringEntry -> {

System.out.println(stringStringEntry.getKey()+"---->"+stringStringEntry.getValue());

});

});

}

}

附图:

测试1

测试二:

看起来效果还不错,当然还有很多不完善的地方,有需要的朋友可以在此基础上扩展定制,例如读取表数据结构方式,实现行数增删改查据或者创建表标题等等。

或者有朋友有更好的实现方案,欢迎前来交流!

最后的最后,当然忘不了附上笨工具的源码啦!

https://github.com/yumiaoxia/excel-commom-demo.git


版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:api接口的版本管理工具(api接口的版本管理工具有哪些)
下一篇:api的接口文档管理工具(API管理工具)
相关文章

 发表评论

暂时没有评论,来抢沙发吧~