说明:数据库使用mysql,导入的字段类型只能是有varchar,Date 类型
火狐导出文件时做特殊设置,不能用url编码
源代码参考附件:
ExcelUtil.java: 导入核心代码
package com.lw.util; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.math.BigDecimal; import java.net.URLEncoder; import java.sql.SQLException; import java.text.ParseException; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.InputMismatchException; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.io.FilenameUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFDateUtil; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellValue; import org.apache.poi.ss.usermodel.FormulaEvaluator; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * Excel组件 * * @author liwei * @version 1.0 * @since 1.0 */ public class ExcelUtil { /** * Excel 2003 */ private final static String XLS = "xls"; /** * Excel 2007 */ private final static String XLSX = "xlsx"; public static void main(String[] args) { String file = "d://importStudentDatas.xls"; String[] columnArray = new String[]{"code", "name", "birthDate"}; Map<String, ColumnCheckTypeEnum> checkColumn = new HashMap<String, ColumnCheckTypeEnum>(); checkColumn.put("code", ColumnCheckTypeEnum.CHECK_TYPE_PRICE_1); try { List<Student> list = ExcelUtil.excelToList(new File(file), 0, Student.class, columnArray, checkColumn); System.out.println(list.size()); for (int i = 0; i < list.size(); i++) { Student stu = list.get(i); System.out.println("编码:" + stu.getCode() + " >>>>> 名称:" + stu.getName() + " >>>>> 生日:" + stu.getBirthDate()); } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchFieldException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (ParseException e) { e.printStackTrace(); } } /*** * 导入Excel数据 * * <li>1、读取excel数据</li> * <li>2、校验数据的合法性(日期,金额,字符长度(和数据库结构比较))</li> * <li>3、合法数据绑定到bean对象中(反射)</li> * <li>4、得到数据层面校验通过的bean对象集合,</li> * @param file 导入数据文件 * @param entityClass bean对象类型bean.class * @param sheetIndex sheet索引 * @param columnArray 字段列数组 (需要导入的字段数组) * @param checkColumn 需要校验格式的字段列Map * @throws IOException * @throws NoSuchFieldException * @throws SecurityException * @throws InstantiationException * @throws SQLException * @throws IllegalAccessException * @throws ParseException */ public static <T> List<T> excelToList(File file, Integer sheetIndex, Class<T> entityClass, String[] columnArray, Map<String, ColumnCheckTypeEnum> checkColumn) throws IOException, SecurityException, NoSuchFieldException, InstantiationException, SQLException, IllegalAccessException, ParseException { List<T> list = new ArrayList<T>(); Workbook workbook = null; if (XLS.equalsIgnoreCase(FilenameUtils.getExtension(file.getName()))) { workbook = new HSSFWorkbook(new FileInputStream(file)); } else if (XLSX.equalsIgnoreCase(FilenameUtils.getExtension(file.getName()))) { workbook = new XSSFWorkbook(new FileInputStream(file)); } else { throw new IOException("导入excel出错,不支持文件类型!"); } if(sheetIndex==null) { sheetIndex = 0; } if((sheetIndex+1)>workbook.getNumberOfSheets()) { throw new IndexOutOfBoundsException("导入excel出错,指定sheet索引越界!"); } // sheet中要导出的列 if(columnArray==null || columnArray.length<1) { throw new NullPointerException("导入excel出错,导入列设置错误!"); } // 拿到sheet Sheet sheet = workbook.getSheetAt(sheetIndex); String sheetName = sheet.getSheetName(); // sheetName 使用表名称 // 解析公式结果 FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); // 每个sheet中的数据 List<Map<String, Object>> dataList = readSheet(sheet, evaluator, columnArray); // 数据库表对应的字段列信息 List<ColumnData> columnDatas = null; try { columnDatas = ProDataUtil.getMySqlColumnDatas(sheetName, "scp"); } catch (SQLException e) { throw new SQLException("导入excel出错,获取表信息错误!"); } // 遍历数据库表对应的字段列信息 for(int j=0; j<dataList.size(); j++) { // 拿到每一行的数据 Map<String, Object> rowData = dataList.get(j); T o = null; try { o = (T) entityClass.newInstance(); } catch (IllegalAccessException e) { throw new IllegalAccessException("导入excel出错,错误信息:" + e.getMessage()); } for(int k=0; k<columnDatas.size(); k++) { ColumnData cd = columnDatas.get(k); Object value = rowData.get(cd.getColumnLowName()); String errMsg = "导入excel出错,错误位置>>:sheet【" + sheetName + "】中,第【"+ (j+1+1) +"】行,第【" + (getIndexOfArrayItem(cd.getColumnLowName(), columnArray) + 1 + 1) + "】列。错误信息:"; // 1、先根据设置,校验自定义校验的列 if(checkColumn.containsKey(cd.getColumnLowName())==false || ColumnCheckTypeEnum.CHECK_TYPE_NOTNULL.equals(checkColumn.get(cd.getColumnLowName()))==false) { if(value==null) { continue; } } ColumnCheckTypeEnum checkType = checkColumn.get(cd.getColumnLowName()); // 非空校验 if(ColumnCheckTypeEnum.CHECK_TYPE_NOTNULL.equals(checkType)) { if(value==null) { throw new InputMismatchException(errMsg + "此列为非空列,请检查excel数据是否为空!"); } } // 整数类型校验 else if(ColumnCheckTypeEnum.CHECK_TYPE_LONG.equals(checkType)) { try { Long.parseLong(value.toString()); } catch (NumberFormatException e) { throw new NumberFormatException(errMsg + "此列为整数列,单元格应设置为文本类型,请检查excel数据是否整数数字或单元格是否为文本类型!" + e.getMessage()); } } // 价格类型校验,取2位小数 else if(ColumnCheckTypeEnum.CHECK_TYPE_PRICE.equals(checkType)) { try { String price = value.toString(); new BigDecimal(price); if(price.indexOf(".")>-1 && price.split("\\.")[1].length()>2) { throw new InputMismatchException(errMsg + "此列为金额数值列,小数点后不超过2位小数,请检查excel数据是否合格!"); } } catch (NumberFormatException e) { throw new NumberFormatException(errMsg + "此列为金额数值列,请检查excel数据是否合格!"); } } // 价格类型校验,取4位小数 else if(ColumnCheckTypeEnum.CHECK_TYPE_PRICE_1.equals(checkType)) { try { String price = value.toString(); new BigDecimal(price); if(price.indexOf(".")>-1 && price.split("\\.")[1].length()>4) { throw new InputMismatchException(errMsg + "此列为金额数值列,小数点后不超过4位小数,请检查excel数据是否合格!"); } } catch (NumberFormatException e) { throw new NumberFormatException(errMsg + "此列为金额数值列,请检查excel数据是否合格!"); } } // 2、自定义校验完成后,进行常规校验 // 通过属性名称获取属性,把值设置到属性里面 Field field = entityClass.getDeclaredField(cd.getColumnLowName()); field.setAccessible(true); // 设置属性可访问, private if("java.lang.String".equalsIgnoreCase(cd.getJavaType())) { if(value.toString().length()>Integer.parseInt(cd.getDataMaxLength())) { throw new SQLException(errMsg + "字符长度超过数据库设置长度限制!数据库长度【"+cd.getDataMaxLength()+"】,当前文本长度【"+value.toString().length()+"】"); } try { field.set(o, value.toString()); } catch (IllegalArgumentException e) { throw new IllegalArgumentException(errMsg + e.getMessage()); } catch (IllegalAccessException e) { throw new IllegalAccessException(errMsg + e.getMessage()); } } else if("java.util.Date".equalsIgnoreCase(cd.getJavaType())) { try { field.set(o, (Date)value); } catch (ClassCastException e) { throw new ClassCastException(errMsg + "数据格式错误,无法将【"+value+"】转换为java.util.Date类型日期格式。" + e.getMessage()); } catch (IllegalArgumentException e) { throw new IllegalArgumentException(errMsg + e.getMessage()); } catch (IllegalAccessException e) { throw new IllegalAccessException(errMsg + e.getMessage()); } } else if("java.lang.Integer".equalsIgnoreCase(cd.getJavaType())) { try { // field.setInt(o, Integer.parseInt(value.toString())); // Int to Integer 赋值时类型不匹配 field.set(o, Integer.parseInt(value.toString())); } catch (NumberFormatException e) { throw new NumberFormatException(errMsg + "数据格式错误,无法将【"+value+"】转换为java.lang.Integer类型。" + e.getMessage()); } catch (IllegalArgumentException e) { throw new IllegalArgumentException(errMsg + e.getMessage()); } catch (IllegalAccessException e) { throw new IllegalAccessException(errMsg + e.getMessage()); } } } list.add(o); } return list; } /*** * 读取单个sheet * <p>导入Excel数据使用私有方法</p> * @param sheet 单个sheet * @param evaluator 解析公式结果 * @param columnArray 字段列数组 */ private static List<Map<String, Object>> readSheet(Sheet sheet, FormulaEvaluator evaluator, String... columnArray) { List<Map<String, Object>> list = new ArrayList<Map<String, Object>>(); int firstRowNum = sheet.getFirstRowNum(); int lastRowNum = sheet.getLastRowNum(); // 从第二行开始读取数据 for (int rowIndex = firstRowNum + 1; rowIndex <= lastRowNum; rowIndex++) { Row row = sheet.getRow(rowIndex); short firstCellNum = row.getFirstCellNum(); // short maxColIx = row.getLastCellNum(); Map<String, Object> rowMap = new HashMap<String, Object>(); // 读取列的时候,按照设置好的字段列的数量长度循环读取 for (short colIndex = firstCellNum; colIndex < columnArray.length; colIndex++) { Cell cell = row.getCell(new Integer(colIndex)+1); // 从第二列开始导入, 第一列序号列不做导入 CellValue cellValue = evaluator.evaluate(cell); if (cellValue == null) { continue; } // 经过公式解析,最后只存在Boolean、Numeric和String三种数据类型,此外就是Error了 // 其余数据类型,根据官方文档,完全可以忽略http://poi.apache.org/spreadsheet/eval.html switch (cellValue.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: rowMap.put(columnArray[colIndex], cellValue.getBooleanValue()); break; case Cell.CELL_TYPE_NUMERIC: // 这里的日期类型会被转换为数字类型,需要判别后区分处理 if (HSSFDateUtil.isCellDateFormatted(cell)) { rowMap.put(columnArray[colIndex], cell.getDateCellValue()); } else { rowMap.put(columnArray[colIndex], cellValue.getNumberValue()); } break; case Cell.CELL_TYPE_STRING: rowMap.put(columnArray[colIndex], cellValue.getStringValue()); break; case Cell.CELL_TYPE_FORMULA: break; case Cell.CELL_TYPE_BLANK: break; case Cell.CELL_TYPE_ERROR: break; default: break; } } list.add(rowMap); } return list; } private static int getIndexOfArrayItem(String arrItem, String[] array) { int index = -1; for (int i = 0; i < array.length; i++) { if(array[i].equals(arrItem)) { index = i; break; } } return index; } /*** * 导出Excel数据 * @param sheetName sheet名称 * @param fileName 导出文件名 * @param list 要导出的数据 * @param response * @throws IOException */ public static void exportDatas(String sheetName, String fileName, List<Map<String, Object>> list, HttpServletRequest request, HttpServletResponse response) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(sheetName); int rowNum = 0; // 设置单元格样式(标题样式) HSSFCellStyle styleTitle = wb.createCellStyle(); styleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTitle.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTitle.setBorderTop(HSSFCellStyle.BORDER_THIN); // 对齐方式 styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleTitle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 字体 HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); styleTitle.setFont(font); // 设置单元格样式(数据值样式) HSSFCellStyle styleContent = wb.createCellStyle(); styleContent.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleContent.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleContent.setBorderRight(HSSFCellStyle.BORDER_THIN); styleContent.setBorderTop(HSSFCellStyle.BORDER_THIN); styleContent.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 标题行 HSSFRow rowTitle = sheet.createRow(rowNum++); int i = 0; HSSFCell cellH0 = rowTitle.createCell(i++); cellH0.setCellValue("序号"); cellH0.setCellStyle(styleTitle); for(Map.Entry<String, Object> tmp : list.get(0).entrySet()) { HSSFCell cellH = rowTitle.createCell(i++); cellH.setCellValue(tmp.getKey()); cellH.setCellStyle(styleTitle); } int cellOrder = 1; for (Map<String, Object> map : list) { HSSFRow row = sheet.createRow(rowNum++); int j = 0; // 序号 HSSFCell cellC0 = row.createCell(j++); cellC0.setCellValue(cellOrder++); cellC0.setCellStyle(styleContent); for(Map.Entry<String, Object> entry : map.entrySet()) { HSSFCell cellC = row.createCell(j++); sheet.setColumnWidth(cellC.getColumnIndex(), 256 * (11 + 10)); cellC.setCellValue(entry.getValue()+""); cellC.setCellStyle(styleContent); } } String agent = request.getHeader("User-Agent"); // 火狐浏览器导出文件不会url解码 if(StringUtil.isEmpty(agent)==false && agent.toLowerCase().indexOf("firefox")>-1) { response.setHeader("Content-disposition", "attachment;filename=\"" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1")+".xls\""); } else { response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName+".xls", "UTF-8")); } response.setContentType("application/vnd.ms-excel;charset=UTF-8"); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); } }
ProDataUtil.java 获取数据库表信息
package com.lw.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class ProDataUtil { private static String url = "jdbc:mysql://192.168.1.1:3306/dbName?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull"; private static String username = "root"; private static String password = "root"; private static String driverClassName = "com.mysql.jdbc.Driver"; /** * 查询表的字段,封装成List * * @param tableName 表名 * @param schemaName 数据库名 * @return * @throws SQLException */ public static List<ColumnData> getMySqlColumnDatas(String tableName, String schemaName) throws SQLException { String SQLColumns = " select COLUMN_NAME,DATA_TYPE,column_comment,numeric_scale,numeric_precision,character_maximum_length,ordinal_position from information_schema.COLUMNS where table_name = '" + tableName + "'" + " and TABLE_SCHEMA='" + schemaName + "' " + " ORDER by ordinal_position"; Connection con = getConnection(); PreparedStatement ps = con.prepareStatement(SQLColumns); List<ColumnData> columnList = new ArrayList<ColumnData>(); ResultSet rs = ps.executeQuery(); while (rs.next()) { String name = rs.getString(1); String lowName = getColumnBeanName(name); // 将XXX_XXX转换成xxxXxx的格式 String type = rs.getString(2).toUpperCase(); // mysql 区分大小写 String javaType = getType(rs.getString(2), rs.getString(4), rs.getString(5)); // mysql 区分大小写 String comment = rs.getString(3); String dataScale = rs.getString(4); String dataPrecision = rs.getString(5); String dataMaxLength = rs.getString(6); ColumnData cd = new ColumnData(name, lowName, type, javaType, comment, dataScale, dataPrecision, dataMaxLength); columnList.add(cd); } rs.close(); ps.close(); con.close(); return columnList; } public static Connection getConnection() throws SQLException { try{ Class.forName(driverClassName); }catch(Exception e){ e.printStackTrace(); } return DriverManager.getConnection(url, username, password); } private static String getColumnBeanName(String column) { String[] split = column.split("_"); StringBuffer columnVal = new StringBuffer(); if (split.length > 1) { for (int i = 0; i < split.length; i++) { String colVal = ""; if (i == 0) { colVal = split[i].toLowerCase(); columnVal.append(colVal); } else { colVal = split[i].substring(0, 1).toUpperCase() + split[i].substring(1, split[i].length()).toLowerCase(); columnVal.append(colVal); } } columnVal.toString(); } else { String colVal = column.toLowerCase(); columnVal.append(colVal); } return columnVal.toString(); } /*** * 获取java类型 * @param type 数据库数据类型 * @param dataScale 小数位数 * @param dataPrecision 数据精度 * @return */ private static String getType(String type, String dataScale, String dataPrecision){ type=type.toLowerCase(); if("char".equalsIgnoreCase(type) || "varchar".equalsIgnoreCase(type)|| "varchar2".equalsIgnoreCase(type)){ return "java.lang.String"; }else if("NUMBER".equalsIgnoreCase(type)|| "numeric".equalsIgnoreCase(type)){// if((dataScale!=null&&!dataScale.equals("")&&!dataScale.equals("0"))){ if(dataPrecision!=null&&dataPrecision.equals("38")){ return "java.math.BigDecimal"; }else{ return "java.lang.Double"; } }else { if(dataPrecision!=null&&dataPrecision.equals("38")){ return "java.math.BigDecimal"; }else{ return "java.lang.Long"; } } }else if("decimal".equalsIgnoreCase(type)){// return "java.math.BigDecimal"; }else if( "DATE".equalsIgnoreCase(type)){ return "java.util.Date"; }else if( "DATETIME".equalsIgnoreCase(type)){ return "java.util.Date"; }else if( "BLOB".equalsIgnoreCase(type)){ return "java.sql.Blob"; }else if( "CLOB".equalsIgnoreCase(type)){ return "java.sql.Clob"; }else if("int".equalsIgnoreCase(type)){ return "java.lang.Integer"; }else if("TINYINT".equalsIgnoreCase(type)){ return "java.lang.Boolean"; }else if("double".equalsIgnoreCase(type)){ return "java.math.BigDecimal"; }else if("datetime".equalsIgnoreCase(type)){ return "java.util.Date"; } return null; } }
相关推荐
本篇将详细讲解如何利用Java的开源库Apache POI,结合反射机制,通过ExcelUtil工具类简化Excel读取的过程。 Apache POI是一个强大的库,它允许Java程序员创建、修改和展示MS Office格式的文件,包括Excel。在传统的...
在Java编程中,ExcelUtil是一个常见的工具类,用于处理与Microsoft Excel文件相关的操作,如导入数据、导出数据等。这个工具类的使用极大地简化了开发者处理Excel文件的工作流程,提高了开发效率。以下是对ExcelUtil...
《ExcelUtil便捷读取工具 v3.1.6——高效处理Excel数据的利器》 ExcelUtil便捷读取工具是一款高效且易用的Java库,专为处理Excel数据而设计,适用于各种开发场景,如数据分析、报表生成、数据导入导出等。在v3.1.6...
`ExcelUtil` 是一个在Java开发中常用的工具类,用于处理Microsoft Excel文件。这个类通常包含了一系列静态方法,便于开发者对Excel数据进行读取、写入、格式转换等操作,而无需深入理解底层的API细节。Excel文件在...
"ExcelUtil万能读取类兼容03/07"指的是一个Java编程中的类库,设计用于读取不同版本的Excel文件,包括Microsoft Excel 2003(.xls格式)和2007以后的版本(.xlsx格式)。这类工具对于开发人员来说至关重要,因为它们...
在Java开发中,ExcelUtil是一个常用的工具类,它简化了基于Apache POI库的Excel文件解析和生成过程。Apache POI是Apache软件基金会的一个开源项目,提供了读取和写入Microsoft Office格式文件的能力,包括Excel。...
封装了excel工具类,只需要几行代码就能实现导入导出功能,适用导出List和List的类型。项目来源于:https://github.com/SargerasWang/ExcelUtil
ExcelUtil.java
本压缩包"excelUtil.zip"提供了两个关键类:`ExcelUtils`和`ExcelData`,它们是针对Excel操作的工具类,方便我们进行Excel文件的导出。 首先,`ExcelData`类是用来存储待导出的Excel数据的结构体。它通常包含两个...
ExcelUtil是一个用于操作Excel的Java工具类库,它基于Apache POI库,为开发者提供了方便、高效的Excel数据读写功能。POI是Apache软件基金会的一个开源项目,专门用于处理Microsoft Office格式的文件,如Excel、Word...
《ExcelUtil便捷读取工具 v3.1.5 源码分析》 在IT行业中,数据处理是一项至关重要的任务,而Excel作为一种广泛使用的电子表格工具,其数据处理能力强大,但手动操作效率较低。因此,开发能够高效读取和处理Excel...
ExcelUtil是一个开源项目,专为Java开发者设计,用于处理Excel数据。它基于流行的Apache POI库,提供了方便、高效的API,使得在Java应用程序中读取、写入和操作Excel文件变得简单易行。这个项目的目的是简化Excel与...
这里提供的"ExcelUtil.java"工具类将帮助我们实现这一目标。 首先,我们需要引入Apache POI库,这是一个强大的API,专门用于处理Microsoft Office格式的文件,包括Excel。你可以通过Maven或Gradle将Apache POI添加...
ExcelUtil java excel工具类
为您提供ExcelUtil便捷读取工具下载,ExcelUtil 借助反射和 POI 对 Excel 读取,省略了以往读取 Excel 的繁琐步骤,调用 ExcelUtil 只需要2步,对,你没有看错,2步足以读取到 Excel 的内容。自动赋值,传入对应...