一、pom文件
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.5.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.atzhongruan</groupId> <artifactId>excelutils</artifactId> <version>0.0.1-SNAPSHOT</version> <name>excelutils</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.0.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.10</version> </dependency> <!--引入druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.8</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!--热部署--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <optional>true</optional> </dependency> <!--pageHelper插件--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.10</version> </dependency> <!--读取excel文件--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
二、工具类
package com.atzhongruan.utils; import com.atzhongruan.constant.ExcelConstant; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.OutputStream; import java.lang.reflect.Method; import java.util.*; public class ExcelUtils { protected static Logger log = LoggerFactory.getLogger(ExcelUtils.class); /** * * @param filePath 文件路径 d://test.xls或者d://test.xlsx * @param sheetIndex 第x个sheet * @return */ public static List<Map<String, String>> readExcel(String filePath, int sheetIndex) { List<Map<String, String>>result = new ArrayList<>(); try { Sheet sheet = null; if (filePath.endsWith(ExcelConstant.FT_XLS)) { FileInputStream is = new FileInputStream(filePath); XSSFWorkbook sheets = new XSSFWorkbook(is); sheet= sheets.getSheetAt(sheetIndex); } else { OPCPackage pkg = OPCPackage.open(new File(filePath)); XSSFWorkbook wb = new XSSFWorkbook(pkg);// 读取excel工作簿 sheet = wb.getSheetAt(sheetIndex); // 读取excel的sheet,0表示读取第一个 pkg.close(); } result = realSheetValue(sheet); } catch (Exception e) { log.error("[读取Excel<{}>出错]:", filePath, e); } return result; } private static List<Map<String, String>> realSheetValue(Sheet sheet) { boolean result = true; String errMsg = ""; List<Map<String, String>> list = new ArrayList<>(); int i , j ; //去除表头令i=1 Row row0 = sheet.getRow(0); for (i = 1; i <= sheet.getLastRowNum(); i++) { try { Row row = sheet.getRow(i); // 取出sheet中的某一行数据 if (row != null) { Map<String, String> rowList = new HashMap<>(row.getPhysicalNumberOfCells()); // 获取该行中总共有多少列数据row.getLastCellNum() for (j = 0; j < row.getLastCellNum(); j++) { try { Cell cell = row.getCell(j); // 获取该行中的一个单元格对象 if (cell != null) {// 判断cell是否为空 if (cell.getCellType() == ExcelConstant.CT_NUMERIC) { if (HSSFDateUtil.isCellDateFormatted(cell)) {// 判断是否日期类型 Date dateValue = HSSFDateUtil.getJavaDate(cell.getNumericCellValue()); } else { rowList.put(row0.getCell(j).getStringCellValue(), String.valueOf((int)cell.getNumericCellValue())); } } else if (cell.getCellType() == ExcelConstant.CT_FORMULA) {// 读取公式的值 try { rowList.put(row0.getCell(j).getStringCellValue(), String.valueOf(cell.getStringCellValue())); } catch (IllegalStateException e) { rowList.put(row0.getCell(j).getStringCellValue(), String.valueOf(cell.getStringCellValue())); } } else { rowList.put(row0.getCell(j).getStringCellValue(), String.valueOf(cell.getStringCellValue())); } } else {// 如果cell为空,用空格字段代替 rowList.put(row0.getCell(j).getStringCellValue(), ""); } } catch (Exception e) { log.error("读取{}行{}列时出错", i + 1, j + 1); errMsg = errMsg + "读取" + (i + 1) + "行" + (j + 1) + "列时出错;"; rowList.put(row0.getCell(j).getStringCellValue(), ""); } } list.add(rowList); } } catch (Exception e) { log.error("读取{}行时出错", i + 1); errMsg = errMsg + "读取" + (i + 1) + "行时出错"; } } return list; } /** * excel导出 * @param fileNamePath 导出的文件名称 * @param sheetName 导出的sheet名称 * @param list 数据集合 * @param titles 第一行表头 * @param fieldNames 字段名称数组 * @return * @throws Exception * @author yzChen * @date 2017年5月6日 下午3:53:47 */ public static <T> File export(String fileNamePath, String sheetName, List<T> list, String[] titles, String[] fieldNames) throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = null; // 对每个表生成一个新的sheet,并以表名命名 if(sheetName == null){ sheetName = "sheet1"; } sheet = wb.createSheet(sheetName); // 设置表头的说明 HSSFRow topRow = sheet.createRow(0); for(int i = 0; i < titles.length; i++){ setCellGBKValue(topRow.createCell(i), titles[i]); } String methodName = ""; Method method = null; T t = null; Object ret = null; // 遍历生成数据行,通过反射获取字段的get方法 for (int i = 0; i < list.size(); i++) { t = list.get(i); HSSFRow row = sheet.createRow(i+1); Class<? extends Object> clazz = t.getClass(); for(int j = 0; j < fieldNames.length; j++){ methodName = "get" + capitalize(fieldNames[j]); try { method = clazz.getDeclaredMethod(methodName); } catch (java.lang.NoSuchMethodException e) { // 不存在该方法,查看父类是否存在。此处只支持一级父类,若想支持更多,建议使用while循环 if(null != clazz.getSuperclass()) { method = clazz.getSuperclass().getDeclaredMethod(methodName); } } if(null == method) { throw new Exception(clazz.getName() + " don't have menthod --> " + methodName); } ret = method.invoke(t); setCellGBKValue(row.createCell(j), ret + ""); } } File file = null; OutputStream os = null; file = new File(fileNamePath); try { os = new FileOutputStream(file); wb.write(os); } catch (Exception e) { throw new Exception("write excel file error!", e); } finally { if(null != os) { os.flush(); os.close(); } } return file; } private static String capitalize(final String str) { int strLen; if (str == null || (strLen = str.length()) == 0) { return str; } final char firstChar = str.charAt(0); final char newChar = Character.toTitleCase(firstChar); if (firstChar == newChar) { // already capitalized return str; } char[] newChars = new char[strLen]; newChars[0] = newChar; str.getChars(1,strLen, newChars, 1); return String.valueOf(newChars); } private static void setCellGBKValue(HSSFCell cell, String value) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setEncoding(HSSFCell.ENCODING_UTF_16); cell.setCellValue(value); } }
三、测试类
package com.atzhongruan.excelutils; import com.atzhongruan.excelutils.mapper.TbBrandMapper; import com.atzhongruan.pojo.TbBrand; import com.atzhongruan.utils.ExcelUtils; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import java.util.List; import java.util.Map; @RunWith(SpringRunner.class) @SpringBootTest public class ExcelutilsApplicationTests { @Autowired private TbBrandMapper brandMapper; @Test public void exportExcel() { //设置表头名字 String[] titles = new String[]{"id", "name", "first_Char"}; //获取属性值(必须与实体类的get/set一致) String[] fieldNames = new String[]{"id", "name", "firstChar"}; //查询的数据结果 List<TbBrand> expList = brandMapper.findAll(); //生成的excel的路径(包括文件名) String fileNamePath = "D:/TbBrand.xls"; try { ExcelUtils.export(fileNamePath, "订单", expList, titles, fieldNames); } catch (Exception e) { e.printStackTrace(); } } @Test //查看打印的map数据 //表的字段名就是map的key public void findTtpe() { //path--->d://test.xls List<Map<String, String>> result = ExcelUtils.readExcel("d://aaa.xlsx", 0); for (Map list : result) { System.out.println(list); } } @Test public void importExcel() { //path--->d://test.xls List<Map<String, String>> result = ExcelUtils.readExcel("d://aaa.xlsx", 0); for (Map map : result) { TbBrand tbBrand = new TbBrand(); Long id=null; if(null!=String.valueOf(map.get("Id"))&&""!=String.valueOf(map.get("Id"))){ id = Long.valueOf(String.valueOf(map.get("Id"))); } tbBrand.setId(id); tbBrand.setFirstChar(String.valueOf(map.get("FirstChar"))); tbBrand.setName(String.valueOf(map.get("Name"))); brandMapper.insert(tbBrand); } } }
参考文章:导入Excel :https://blog.csdn.net/xiongyouqiang/article/details/78114176
导出Excel:百度云链接:https://pan.baidu.com/s/1jIzFFPO