详解java封装实现Excel建表读写操作
对 Excel 进行读写操作是生产环境下常见的业务,网上搜索的实现方式都是基于POI和JXL第三方框架,但都不是很全面。小编由于这两天刚好需要用到,于是就参考手写了一个封装操作工具,基本涵盖了Excel表(分有表头和无表头)的创建,并对它们进行读写操作。为方便大家,有需要者可以点击文后点解下载直接使用哦,当然也可以根据自己需求举一反三自己定制,相信对于聪明的你也不是什么难事。话不多说,直接贴源码
创新互联-专业网站定制、快速模板网站建设、高性价比全椒网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式全椒网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖全椒地区。费用合理售后完善,十年实体公司更值得信赖。
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
有表头实现类
/** * @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 = 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
无表头实现类
/** * @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
附图:
测试1
测试二:
看起来效果还不错,当然还有很多不完善的地方,有需要的朋友可以在此基础上扩展定制,例如读取表数据结构方式,实现行数增删改查据或者创建表标题等等。
或者有朋友有更好的实现方案,欢迎前来交流!
最后的最后,当然忘不了附上笨工具的源码啦!
https://github.com/yumiaoxia/excel-commom-demo.git
标题名称:详解java封装实现Excel建表读写操作
路径分享:http://myzitong.com/article/gohcpe.html