网站不备案什么意思企业营销培训课程
java 使用poi-ooxml 3.17 excel的通用导入导出，导出的样式未做处理，可自行处理
可以在csdn下载或者在gitHub（ GitHub - aojd/excel-importAndExport: java poi 3.17导入导出excel通用方法，导出表的样式未做维护 ）git clone
也可以在csdn搜索 【java poi-ooxml 3.17 excel通用导入导出】、【完整 excel通用导入导出】
或者 https://download.csdn.net/download/qq_30620793/10922408
通用java 《=》excel 功能：能实现java的实体类和excel的互相转化。对标题、表头进行垂直水平居中，其他样式均未添加。
这里测试的代码
一、excel =》实体类
1.一个sheet
//指定输入文件
FileInputStream fis = new FileInputStream("D:\\data\\test2.xls");
//指定每列对应的类属性
LinkedHashMap<String, String> alias = new LinkedHashMap<>();
alias.put("姓名", "name");
alias.put("年龄", "age");
//转换成指定类型的对象数组
List<TestUser> pojoList = ExcelUtil2.excel2Pojo(fis, TestUser.class, alias, 0);2.多个sheet
//指定输入文件
FileInputStream fis = new FileInputStream("D:\\data\\test2.xls");List<SheetImport> listImport = new ArrayList<>();
//指定每列对应的类属性
LinkedHashMap<String, String> alias = new LinkedHashMap<>();
alias.put("姓名", "name");
alias.put("年龄", "age");
SheetImport sheetImport = new SheetImport();
sheetImport.setAlias(alias);
sheetImport.setClaz(TestUser.class);
sheetImport.setParam(0);
listImport.add(sheetImport);
LinkedHashMap<String, String> alias2 = new LinkedHashMap<>();
alias2.put("姓名", "name");
alias2.put("年龄", "age");
alias2.put("日期", "dat");
SheetImport sheetImport2 = new SheetImport();
sheetImport2.setAlias(alias2);
sheetImport2.setClaz(TestUserSS.class);
sheetImport2.setParam(0);
listImport.add(sheetImport2);
//转换成指定类型的对象数组
List<?> pojoList = ExcelUtil2.excel2PojoSheetList(listImport,fis);
List<TestUser> testUserList = new ArrayList<>();
for (TestUser te: (List<TestUser>)pojoList.get(0)) {TestUser testUser = new TestUser();PropertyUtils.copyProperties(testUser,te);testUserList.add(testUser);
}List<TestUserSS> testUserSSList = new ArrayList<>();
for (TestUserSS te: (List<TestUserSS>)pojoList.get(1)) {TestUserSS testUseSS = new TestUserSS();PropertyUtils.copyProperties(testUseSS,te);testUserSSList.add(testUseSS);
}二、实体类 =》excel
1.一个sheet
//将生成的excel转换成文件，还可以用作文件下载
File file = new File("D:\\data\\testExport.xls");
FileOutputStream fos = new FileOutputStream(file);//对象集合
List<TestUser> pojoExport = new ArrayList<>();
for (int i = 0; i < 5; i++) {TestUser user = new TestUser();user.setName("老李");user.setAge(50);pojoExport.add(user);
}
//设置属性别名（列名）
LinkedHashMap<String, String> aliasE = new LinkedHashMap<>();
aliasE.put("name", "姓名");
aliasE.put("age", "年龄");
//标题
String headLine = "用户表";
List<MergeData> list = new ArrayList<>();
list.add(new MergeData("start", 1, 2, 0, 2));
// list.add(new MergeData("end", 1, 2, 2, 3));
ExcelUtil2.pojo2Excel(pojoList, fos, aliasE, new UtilExcel("table", 4), list);
// ExcelUtil2.pojo2Excel(pojoList, fos, aliasE, new UtilExcel("table",2));2.多个sheet
//将生成的excel转换成文件，还可以用作文件下载
File file = new File("D:\\data\\testExport2.xls");
FileOutputStream fos = new FileOutputStream(file);List<SheetExport> sheetExport = new ArrayList<>();SheetExport us = new SheetExport();
LinkedHashMap<String, String> aliaEp = new LinkedHashMap<>();
aliaEp.put("name", "姓名");
aliaEp.put("age", "年龄");
us.setAlias(aliaEp);
us.setSheetName("第一");
us.setPojoList(testUserList);
us.setUtilExcel(new UtilExcel("table", 1));
sheetExport.add(us);SheetExport usS = new SheetExport();
LinkedHashMap<String, String> aliasEx = new LinkedHashMap<>();
aliasEx.put("name", "姓名");
aliasEx.put("age", "年龄");
aliasEx.put("dat", "日期");
usS.setAlias(aliasEx);
usS.setSheetName("sheet name");
usS.setPojoList(testUserSSList);
List<MergeData> lisor = new ArrayList<>();
lisor.add(new MergeData("start", 1, 2, 0, 2));
usS.setMergeDataList(lisor);
usS.setUtilExcel(new UtilExcel("table", 1));
sheetExport.add(usS);ExcelUtil2.pojo2ExcelSheetList(sheetExport, fos);三、相关的excel
四、ExcelUtil2源码 实在不想往github弄了
import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.Map.Entry;/*** 里面使用的所有行数都是从0开始的* 使用的jar包* <dependency>* <groupId>org.apache.poi</groupId>* <artifactId>poi-ooxml</artifactId>* <version>3.17</version>* </dependency>* <dependency>* <groupId>commons-beanutils</groupId>* <artifactId>commons-beanutils</artifactId>* <version>1.9.3</version>* </dependency>*/
public class ExcelUtil2 {/*** 将对象数组转换成excel<br/>** @param pojoList 对象数组* @param out 输出流* @param alias 指定对象属性别名，生成列名和列顺序Map<"类属性名","列名">* @param utilExcel 表头对象* @throws Exception*/public static <T> void pojo2Excel(List<T> pojoList, OutputStream out, LinkedHashMap<String, String> alias, UtilExcel utilExcel) throws Exception {//创建一个工作簿XSSFWorkbook wb = new XSSFWorkbook();if (utilExcel == null) utilExcel = new UtilExcel();//创建一个表XSSFSheet sheet = wb.createSheet();// 需要表头if (utilExcel.getFieldRow() > utilExcel.getTableHeadRow()) {//创建第一行，作为表名XSSFRow row = sheet.createRow(utilExcel.getTableHeadRow());// 这个方法感觉是直接跳到对应行的XSSFCell cell = row.createCell(0);cell.setCellValue(utilExcel.getTableHeadName());sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, alias.size() - 1));}// 在第一行插入列名insertColumnName(utilExcel.getFieldRow(), sheet, alias);// 从第指定行开始插入数据insertColumnDate(utilExcel.getDataStarRow(), pojoList, sheet, alias);// 输出表格文件try {wb.write(out);} catch (IOException e) {e.printStackTrace();} finally {wb.close();}}/*** 多个sheet导出excel 复杂表头或者非复杂表头<br/>** @param exportList sheet对象的list* @param out 输出流* @throws Exception*/public static <T> void pojo2ExcelSheetList(List<SheetExport> exportList, OutputStream out) throws Exception {//创建一个工作簿XSSFWorkbook wb = new XSSFWorkbook();// 设置居中样式XSSFCellStyle xssStyle = wb.createCellStyle();xssStyle.setAlignment(HorizontalAlignment.CENTER);xssStyle.setVerticalAlignment(VerticalAlignment.CENTER);for (SheetExport sheetData : exportList) {//创建一个表XSSFSheet sheet = wb.createSheet(sheetData.getSheetName());// 需要表头if (sheetData.getUtilExcel().getFieldRow() > sheetData.getUtilExcel().getTableHeadRow()) {XSSFRow row = sheet.createRow(sheetData.getUtilExcel().getTableHeadRow());// 这个方法感觉是直接跳到对应行的XSSFCell cell = row.createCell(0);cell.setCellValue(sheetData.getUtilExcel().getTableHeadName());sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, sheetData.getAlias().size() - 1));// 设置居中样式cell.setCellStyle(xssStyle);}if (sheetData.getMergeDataList() == null) {if (sheetData.getUtilExcel().getFieldRow() < sheetData.getUtilExcel().getDataStarRow()) {// 插入列名insertColumnName(sheetData.getUtilExcel().getFieldRow(), sheet, sheetData.getAlias());// 从第指定行开始插入数据insertColumnDate(sheetData.getUtilExcel().getDataStarRow(), sheetData.getPojoList(), sheet, sheetData.getAlias());} else {insertColumnName(sheetData.getUtilExcel().getFieldRow(), sheet, sheetData.getAlias());insertColumnDate(sheetData.getUtilExcel().getFieldRow() + 1, sheetData.getPojoList(), sheet, sheetData.getAlias());}} else {// 插入复杂表头(表的标题和字段名之间)XSSFRow rowTable = sheet.createRow(sheetData.getUtilExcel().getTableHeadRow() + 1);for (MergeData mergeData : sheetData.getMergeDataList()) {sheet.addMergedRegion(new CellRangeAddress(mergeData.getStartRow(), mergeData.getEndRow(), mergeData.getStartCol(), mergeData.getEndCol()));// 插入复杂表头的数据XSSFCell tableCellValue = rowTable.createCell(mergeData.getStartCol());tableCellValue.setCellValue(mergeData.getName());// 这里可以对单元格做样式处理// 设置居中样式tableCellValue.setCellStyle(xssStyle);}// 如果插入数据的行小于指定的数据行，就默认在复杂表头的下方int maxHeadRow = 0;for (MergeData me : sheetData.getMergeDataList()) {if (me.getEndRow() > maxHeadRow) maxHeadRow = me.getEndRow();}if (maxHeadRow < sheetData.getUtilExcel().getFieldRow() && sheetData.getUtilExcel().getFieldRow() < sheetData.getUtilExcel().getDataStarRow()) {// 插入列名insertColumnName(sheetData.getUtilExcel().getFieldRow(), sheet, sheetData.getAlias());// 从第指定行开始插入数据insertColumnDate(sheetData.getUtilExcel().getDataStarRow(), sheetData.getPojoList(), sheet, sheetData.getAlias());} else {insertColumnName(maxHeadRow + 1, sheet, sheetData.getAlias());insertColumnDate(maxHeadRow + 2, sheetData.getPojoList(), sheet, sheetData.getAlias());}}}// 输出表格文件try {wb.write(out);} catch (IOException e) {e.printStackTrace();} finally {wb.close();}}/*** 将对象数组转换成excel，并增加复杂表头，除去表名和显示列的名称的那一行** @param pojoList 对象数组* @param out 输出流* @param alias 指定对象属性别名，生成列名和列顺序Map<"类属性名","列名">* @param utilExcel 表头对象* @param mergeDataList 合并行中的所有数据，包括不和并的* @throws Exception*/public static <T> void pojo2Excel(List<T> pojoList, OutputStream out, LinkedHashMap<String, String> alias, UtilExcel utilExcel, List<MergeData> mergeDataList) throws Exception {if (utilExcel == null) throw new Exception("UtilExcel 对象为空");//创建一个工作簿XSSFWorkbook wb = new XSSFWorkbook();// 设置居中样式// 设置表头文字格式// XSSFCellStyle cellStyle = wb.createCellStyle();// XSSFFont font = wb.createFont();// font.setFontName("宋体");// font.setFontHeightInPoints((short) 36);// cellStyle.setFont(font);// cellStyle.setAlignment(HorizontalAlignment.CENTER);XSSFCellStyle xssStyle = wb.createCellStyle();xssStyle.setAlignment(HorizontalAlignment.CENTER);xssStyle.setVerticalAlignment(VerticalAlignment.CENTER);//创建一个表XSSFSheet sheet = wb.createSheet();// 需要表头if (utilExcel.getFieldRow() > utilExcel.getTableHeadRow()) {//创建第一行，作为表名XSSFRow row = sheet.createRow(utilExcel.getTableHeadRow());// 这个方法感觉是直接跳到对应行的 后面不需要再次调用该方法，应该是使用该方法 可以独立设置该行的样式XSSFCell cell = row.createCell(0);cell.setCellValue(utilExcel.getTableHeadName());sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, alias.size() - 1));// 设置居中样式cell.setCellStyle(xssStyle);}// 插入复杂表头XSSFRow rowTable = sheet.createRow(utilExcel.getTableHeadRow() + 1);for (MergeData mergeData : mergeDataList) {sheet.addMergedRegion(new CellRangeAddress(mergeData.getStartRow(), mergeData.getEndRow(), mergeData.getStartCol(), mergeData.getEndCol()));// 插入数据XSSFCell tableCellValue = rowTable.createCell(mergeData.getStartCol());tableCellValue.setCellValue(mergeData.getName());// 这里可以对单元格做样式处理// 设置居中样式tableCellValue.setCellStyle(xssStyle);}// 如果插入数据的行小于指定的数据行，就默认在复杂表头的下方int maxHeadRow = 0;for (MergeData me : mergeDataList) {if (me.getEndRow() > maxHeadRow) maxHeadRow = me.getEndRow();}if (maxHeadRow < utilExcel.getFieldRow() && utilExcel.getFieldRow() < utilExcel.getDataStarRow()) {// 插入列名insertColumnName(utilExcel.getFieldRow(), sheet, alias);// 从第指定行开始插入数据insertColumnDate(utilExcel.getDataStarRow(), pojoList, sheet, alias);} else {insertColumnName(maxHeadRow + 1, sheet, alias);insertColumnDate(maxHeadRow + 2, pojoList, sheet, alias);}// 输出表格文件try {wb.write(out);} catch (IOException e) {e.printStackTrace();} finally {wb.close();}}/*** 将excel表转换成指定类型的对象数组** @param claz 类型* @param alias 列别名,格式要求：Map<"列名","类属性名">* @param param 指定第几行行为字段名(数据在字段的下一行，默认)，第一行为0* @return* @throws IOException* @throws IllegalArgumentException* @throws IllegalAccessException* @throws SecurityException* @throws NoSuchFieldException* @throws InstantiationException* @throws InvocationTargetException*/public static <T> List<T> excel2Pojo(InputStream inputStream, Class<T> claz, LinkedHashMap<String, String> alias, Integer param) throws IOException {XSSFWorkbook wb = new XSSFWorkbook(inputStream);try {XSSFSheet sheet = wb.getSheetAt(0);//生成属性和列对应关系的map，Map<类属性名，对应一行的第几列>Map<String, Integer> propertyMap = generateColumnPropertyMap(sheet, alias, param);//根据指定的映射关系进行转换List<T> pojoList = generateList(sheet, propertyMap, claz, param);return pojoList;} catch (Exception e) {e.printStackTrace();return null;} finally {wb.close();}}public static <T> List<T> excel2PojoSheetList(List<SheetImport> list, InputStream inputStream) throws IOException {XSSFWorkbook wb = new XSSFWorkbook(inputStream);List<T> pojoList = new ArrayList<>();for (int i = 0; i < list.size(); i++) {try {XSSFSheet sheet = wb.getSheetAt(i);//生成属性和列对应关系的map，Map<类属性名，对应一行的第几列>Map<String, Integer> propertyMap = generateColumnPropertyMap(sheet, list.get(i).getAlias(), list.get(i).getParam());//根据指定的映射关系进行转换pojoList.add((T) generateList(sheet, propertyMap, list.get(i).getClaz(), list.get(i).getParam()));} catch (Exception e) {e.printStackTrace();} finally {wb.close();}}return pojoList;}/*** 将对象数组转换成excel** @param pojoList 对象数组* @param out 输出流* @param alias 指定对象属性别名，生成列名和列顺序* @throws Exception*/public static <T> void pojo2Excel(List<T> pojoList, OutputStream out, LinkedHashMap<String, String> alias) throws Exception {//获取类名作为标题String headLine = "";if (pojoList.size() > 0) {Object pojo = pojoList.get(0);Class<? extends Object> claz = pojo.getClass();headLine = claz.getName();pojo2Excel(pojoList, out, alias, new UtilExcel(headLine, 1));}}/*** 将对象数组转换成excel,列名为对象属性名** @param pojoList 对象数组* @param out 输出流* @param headLine 表标题* @throws Exception*/public static <T> void pojo2Excel(List<T> pojoList, OutputStream out, String headLine) throws Exception {//获取类的属性作为列名LinkedHashMap<String, String> alias = new LinkedHashMap<String, String>();if (pojoList.size() > 0) {Object pojo = pojoList.get(0);Field[] fields = pojo.getClass().getDeclaredFields();String[] name = new String[fields.length];Field.setAccessible(fields, true);for (int i = 0; i < name.length; i++) {name[i] = fields[i].getName();alias.put(isNull(name[i]).toString(), isNull(name[i]).toString());}pojo2Excel(pojoList, out, alias, new UtilExcel(headLine, 1));}}/*** 将对象数组转换成excel，列名默认为对象属性名，标题为类名** @param pojoList 对象数组* @param out 输出流* @throws Exception*/public static <T> void pojo2Excel(List<T> pojoList, OutputStream out) throws Exception {//获取类的属性作为列名LinkedHashMap<String, String> alias = new LinkedHashMap<String, String>();//获取类名作为标题String headLine = "";if (pojoList.size() > 0) {Object pojo = pojoList.get(0);Class<? extends Object> claz = pojo.getClass();headLine = claz.getName();Field[] fields = claz.getDeclaredFields();String[] name = new String[fields.length];Field.setAccessible(fields, true);for (int i = 0; i < name.length; i++) {name[i] = fields[i].getName();alias.put(isNull(name[i]).toString(), isNull(name[i]).toString());}pojo2Excel(pojoList, out, alias, new UtilExcel(headLine, 1));}}/*** 此方法作用是创建表头的列名** @param alias 要创建的表的列名与实体类的属性名的映射集合* @param rowNum 指定行创建列名* @return*/private static void insertColumnName(int rowNum, XSSFSheet sheet, Map<String, String> alias) {XSSFRow row = sheet.createRow(rowNum);//列的数量int columnCount = 0;Set<Entry<String, String>> entrySet = alias.entrySet();for (Entry<String, String> entry : entrySet) {// 创建第一行的第columnCount个格子XSSFCell cell = row.createCell(columnCount++);// 将此格子的值设置为alias中的键名cell.setCellValue(isNull(entry.getValue()).toString());}}/*** 从指定行开始插入数据** @param beginRowNum 开始行* @param models 对象数组* @param sheet 表* @param alias 列别名* @throws Exception*/private static <T> void insertColumnDate(int beginRowNum, List<T> models, XSSFSheet sheet, Map<String, String> alias) throws Exception {for (T model : models) {// 创建新的一行XSSFRow rowTemp = sheet.createRow(beginRowNum++);// 获取列的迭代Set<Entry<String, String>> entrySet = alias.entrySet();// 从第0个格子开始创建int columnNum = 0;for (Entry<String, String> entry : entrySet) {// 获取属性值String property = BeanUtils.getProperty(model, entry.getKey());// 创建一个格子XSSFCell cell = rowTemp.createCell(columnNum++);// 得知string可以转化的类型if (isDouble(property)) {cell.setCellValue(Double.valueOf(property));} else if (isInt(property)) {cell.setCellValue(Integer.valueOf(property));} else if (isDateAndTime(property)) {// 只对日期加time的做转化SimpleDateFormat formatter;if (property.indexOf("-") >= 1) {formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");} else if (property.indexOf("/") >= 1) {formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");} else if (property.indexOf(".") >= 1) {formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");} else {formatter = new SimpleDateFormat("yyyyMMdd HH:mm:ss");}Date date = formatter.parse(property);cell.setCellValue(date);} else {cell.setCellValue(property);}}}}// 判断是否为空，若为空设为""private static Object isNull(Object object) {if (object != null) {return object;} else {return "";}}/*** 生成一个属性-列的对应关系的map** @param sheet 表* @param alias 别名* @return*/private static Map<String, Integer> generateColumnPropertyMap(XSSFSheet sheet, LinkedHashMap<String, String> alias, Integer param) {Map<String, Integer> propertyMap = new HashMap<>();if (param == null || param < 0) param = 1;XSSFRow propertyRow = sheet.getRow(param);short firstCellNum = propertyRow.getFirstCellNum();short lastCellNum = propertyRow.getLastCellNum();for (int i = firstCellNum; i < lastCellNum; i++) {Cell cell = propertyRow.getCell(i);if (cell == null) {continue;}// 列名String cellValue = cell.getStringCellValue();// 对应属性名String propertyName = alias.get(cellValue);propertyMap.put(propertyName, i);}return propertyMap;}/*** 根据指定关系将表数据转换成对象数组** @param sheet 表* @param propertyMap 属性映射关系Map<"属性名",一行第几列>* @param claz 类类型* @return* @throws InstantiationException* @throws IllegalAccessException* @throws InvocationTargetException*/private static <T> List<T> generateList(XSSFSheet sheet, Map<String, Integer> propertyMap, Class<T> claz, Integer param) throws Exception {if (param == null || param < 0) param = 1;// 对象数组List<T> pojoList = new ArrayList<>();int index = 0;for (Row row : sheet) {// 跳过标题和列名if (row.getRowNum() < param + 1) {continue;}T instance = claz.newInstance();Set<Entry<String, Integer>> entrySet = propertyMap.entrySet();for (Entry<String, Integer> entry : entrySet) {/** CellTypeEnum 类型 值* NUMERIC 数值型 0* STRING 字符串型 1* FORMULA 公式型 2* BLANK 空值 3* BOOLEAN 布尔型 4* ERROR 错误 5** 4.0以上将会移除 替换为getCellType* */// 获取此行指定列的值,即为属性对应的值switch (row.getCell(entry.getValue()).getCellTypeEnum()) {case _NONE:System.out.println("****************************不知道的类型*********************************");throw new Exception("第" + index + "行【" + row.getCell(entry.getValue()) + "】导入数据异常");case BLANK:BeanUtils.setProperty(instance, entry.getKey(), null);break;case NUMERIC:int numericType = row.getCell(entry.getValue()).getCellStyle().getDataFormat();if (numericType == 0) {// 数字类型int pInt = (int) row.getCell(entry.getValue()).getNumericCellValue();BeanUtils.setProperty(instance, entry.getKey(), pInt);break;} else {Date date = row.getCell(entry.getValue()).getDateCellValue();BeanUtils.setProperty(instance, entry.getKey(), date);break;}case STRING:String pString = row.getCell(entry.getValue()).getStringCellValue();BeanUtils.setProperty(instance, entry.getKey(), pString);break;case FORMULA:System.out.println("**该类型【FORMULA】未做处理，因为没见过这种类型，于ExcelUtil2.generateList方法中修改！");break;case BOOLEAN:boolean pBoolean = row.getCell(entry.getValue()).getBooleanCellValue();BeanUtils.setProperty(instance, entry.getKey(), pBoolean);break;case ERROR:System.out.println("****************************error*********************************");throw new Exception("第" + index + "行【" + row.getCell(entry.getValue()) + "】导入数据异常");}}pojoList.add(instance);index++;}return pojoList;}/*** 将excel表转换成指定类型的对象数组，列名即作为对象属性** @param claz 类型* @return* @throws IOException* @throws InstantiationException* @throws SecurityException* @throws NoSuchFieldException* @throws IllegalAccessException* @throws IllegalArgumentException* @throws InvocationTargetException*/public static <T> List<T> excel2Pojo(InputStream inputStream, Class<T> claz) throws IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException, InstantiationException, IOException, InvocationTargetException {LinkedHashMap<String, String> alias = new LinkedHashMap<String, String>();Field[] fields = claz.getDeclaredFields();for (Field field : fields) {alias.put(field.getName(), field.getName());}List<T> pojoList = excel2Pojo(inputStream, claz, alias, 1);return pojoList;}/*** String可以转化的类型判断** @param str*/
// 是否为浮点数private static boolean isDouble(String str) {return str.matches("^[-+]?[1-9][0-9]*\\.?[0-9]+$");}// 是否为整数private static boolean isInt(String str) {return str.matches("^[-+]?[1-9]\\d*$");}// 必须日期加时间 [2018-02-14 00:00:00] 使用反向引用进行简化，年份0001-9999，格式yyyy-MM-dd或yyyy-M-d，连字符可以没有或是“-”、“/”、“.”之一。private static boolean isDateAndTime(String str) {return str.matches("^(?:(?!0000)[0-9]{4}([-/.]?)(?:(?:0?[1-9]|1[0-2])\\1(?:0?[1-9]|1[0-9]|2[0-8])|(?:0?[13-9]|1[0-2])\\1(?:29|30)|(?:0?[13578]|1[02])\\1(?:31))|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)([-/.]?)0?2\\2(?:29))\\s+([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$");}// 必须日期加时间 [2018-02-14] 使用反向引用进行简化，年份0001-9999，格式yyyy-MM-dd或yyyy-M-d，连字符可以没有或是“-”、“/”、“.”之一。private static boolean isDate(String str) {return str.matches("^(?:(?!0000)[0-9]{4}([-/.]?)(?:(?:0?[1-9]|1[0-2])([-/.]?)(?:0?[1-9]|1[0-9]|2[0-8])|(?:0?[13-9]|1[0-2])([-/.]?)(?:29|30)|(?:0?[13578]|1[02])([-/.]?)31)|(?:[0-9]{2}(?:0[48]|[2468][048]|[13579][26])|(?:0[48]|[2468][048]|[13579][26])00)([-/.]?)0?2([-/.]?)29)$");}
}class UtilExcel {private String tableHeadName;// 表头名称private Integer tableHeadRow;// 表头名称所在的行private Integer fieldRow;// 字段所在的行private Integer dataStarRow;// 插入数据开始的row/*** 默认sheet的信息<br/>* tableHeadName = "export excel"<br/>* fieldRow = 0<br/>* dataStarRow = 1<br/>*/UtilExcel() {this.tableHeadName = "export excel";this.tableHeadRow = 0;this.fieldRow = 1;this.dataStarRow = 2;}/*** sheet的基本信息** @param tableHeadName 表头名称* @param fieldRow sheet表格对应实体字段所在的行*/public UtilExcel(String tableHeadName, int fieldRow) {this.tableHeadName = tableHeadName;this.tableHeadRow = 0;// 如果fieldRow = tableHeadRow，则没有表头this.fieldRow = fieldRow;this.dataStarRow = fieldRow + 1;}/*** sheet的基本信息** @param tableHeadName 表头名称* @param fieldRow sheet表格字段所在的行* @param dataStarRow 插入数据开始的行*/public UtilExcel(String tableHeadName, int fieldRow, int dataStarRow) {this.tableHeadName = tableHeadName;if (fieldRow > 0) {this.tableHeadRow = fieldRow - 1;// 如果fieldRow = tableHeadRow，则没有表头} else {this.tableHeadRow = 0;}this.fieldRow = fieldRow;this.dataStarRow = dataStarRow;}/*** sheet的基本信息** @param tableHeadName 表头名称* @param tableHeadRow 表头名称所在的行* @param fieldRow sheet表格字段所在的行* @param dataStarRow 插入数据开始的行*/public UtilExcel(String tableHeadName, int tableHeadRow, int fieldRow, int dataStarRow) {this.tableHeadName = tableHeadName;this.tableHeadRow = tableHeadRow;this.fieldRow = fieldRow;this.dataStarRow = dataStarRow;}public String getTableHeadName() {return tableHeadName;}public void setTableHeadName(String tableHeadName) {this.tableHeadName = tableHeadName;}public Integer getTableHeadRow() {return tableHeadRow;}public void setTableHeadRow(Integer tableHeadRow) {this.tableHeadRow = tableHeadRow;}public Integer getFieldRow() {return fieldRow;}public void setFieldRow(Integer fieldRow) {this.fieldRow = fieldRow;}public Integer getDataStarRow() {return dataStarRow;}public void setDataStarRow(Integer dataStarRow) {this.dataStarRow = dataStarRow;}
}class MergeData {private String name;private int startRow;private int endRow;private int startCol;private int endCol;public MergeData() {}public MergeData(String name, int startRow, int endRow, int startCol, int endCol) {this.name = name;this.startRow = startRow;this.endRow = endRow;this.startCol = startCol;this.endCol = endCol;}public String getName() {return name;}public void setName(String name) {this.name = name;}public int getStartRow() {return startRow;}public void setStartRow(int startRow) {this.startRow = startRow;}public int getEndRow() {return endRow;}public void setEndRow(int endRow) {this.endRow = endRow;}public int getStartCol() {return startCol;}public void setStartCol(int startCol) {this.startCol = startCol;}public int getEndCol() {return endCol;}public void setEndCol(int endCol) {this.endCol = endCol;}
}class SheetExport {private String sheetName;private List<?> pojoList;private LinkedHashMap<String, String> alias;private UtilExcel utilExcel;private List<MergeData> mergeDataList;public String getSheetName() {return sheetName;}public void setSheetName(String sheetName) {if (!"".equals(sheetName)) this.sheetName = sheetName;}public List<?> getPojoList() {return pojoList;}public void setPojoList(List<?> pojoList) {this.pojoList = pojoList;}public LinkedHashMap<String, String> getAlias() {return alias;}public void setAlias(LinkedHashMap<String, String> alias) {this.alias = alias;}public UtilExcel getUtilExcel() {return utilExcel;}public void setUtilExcel(UtilExcel utilExcel) {this.utilExcel = utilExcel;}public List<MergeData> getMergeDataList() {return mergeDataList;}public void setMergeDataList(List<MergeData> mergeDataList) {this.mergeDataList = mergeDataList;}
}class SheetImport {private Class<?> claz;private LinkedHashMap<String, String> alias;private Integer param;public Class<?> getClaz() {return claz;}public void setClaz(Class<?> claz) {this.claz = claz;}public LinkedHashMap<String, String> getAlias() {return alias;}public void setAlias(LinkedHashMap<String, String> alias) {this.alias = alias;}public Integer getParam() {return param;}public void setParam(Integer param) {this.param = param;}
}