赞
踩
更新时间2018-12-27 最近看了一个大神写的通过注解的形式来实现excel中表头名称 选项的设置,觉得很受启发,所以决定更新一版我的小文章。当然不会让大家失望的。这次更改我使用了注解工具类来侵入性更小的实现简单的excel导入导出。
demo源码地址:https://gitee.com/ytrlmy/importexcel/tree/importexcel
再次更新一下,如何使用最简单的jar包更优雅的完成excel的导入和导出,包括多个sheet,多表头合并等等,我觉得再怎么复杂的excel都可以达到要求吧,先上图,
下图来自csdn博客,我们来分析一下表头合并的问题。
第一种方案如下:
- /*
- * ExcelUtil工具类实现功能,借鉴了大神写法:
- * 导出时传入list<T>,即可实现导出为一个excel,其中每个对象T为Excel中的一条记录.
- * 导入时读取excel,得到的结果是一个list<T>.T是自己定义的对象.
- * 需要导出的实体对象只需简单配置注解就能实现灵活导出,通过注解您可以方便实现下面功能:
- * 1.实体属性配置了注解就能导出到excel中,每个属性都对应一列.
- * 2.列名称可以通过注解配置.
- * 3.导出到哪一列可以通过注解配置.
- * 4.鼠标移动到该列时提示信息可以通过注解配置.
- * 5.用注解设置只能下拉选择不能随意填写功能.
- * 6.用注解设置是否只导出标题而不导出内容,这在导出内容作为模板以供用户填写时比较实用.(导出一个空表,类似模板)
- */
详细代码如下:
- public class ExcelUtil<T> {
-
- private Class<T> clazz;
-
- public ExcelUtil(Class<T> clazz) {
- this.clazz = clazz;
- }
-
- /**
- * 导出excel文件
- *
- * @param sheetName 页名称
- * @param input 输入流程
- * @return 文件数据
- */
- public List<T> importExcel(String sheetName, InputStream input) {
- int maxCol = 0;
- List<T> list = new ArrayList<>();
- try {
- HSSFWorkbook workbook = new HSSFWorkbook(input);
- HSSFSheet sheet = workbook.getSheet(sheetName);
- if (!sheetName.trim().equals("")) {
- sheet = workbook.getSheet(sheetName);// 如果指定sheet名,则取指定sheet中的内容.
- }
- if (sheet == null) {
- sheet = workbook.getSheetAt(0); // 如果传入的sheet名不存在则默认指向第1个sheet.
- }
- int rows = sheet.getPhysicalNumberOfRows();
- if (rows > 0) {// 有数据时才处理
- // Field[] allFields = clazz.getDeclaredFields();// 得到类的所有field.
- List<Field> allFields = getMappedFiled(clazz, null);
- Map<Integer, Field> fieldsMap = new HashMap<>();// 定义一个map用于存放列的序号和field.
- for (Field field : allFields) { // 将有注解的field存放到map中.
- if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
- ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);
- int col = getExcelCol(attr.column());// 获得列号
- maxCol = Math.max(col, maxCol);
- // System.out.println(col + "====" + field.getName());
- field.setAccessible(true);// 设置类的私有字段属性可访问.
- fieldsMap.put(col, field);
- }
- }
- for (int i = 1; i < rows; i++) {// 从第2行开始取数据,默认第一行是表头.
- HSSFRow row = sheet.getRow(i);
- // int cellNum = row.getPhysicalNumberOfCells();
- // int cellNum = row.getLastCellNum();
- T entity = null;
- for (int j = 0; j <= maxCol; j++) {
- HSSFCell cell = row.getCell(j);
- if (cell == null) {
- continue;
- }
- int cellType = cell.getCellType();
- String c;
- if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {
- c = String.valueOf(cell.getNumericCellValue());
- } else if (cellType == HSSFCell.CELL_TYPE_BOOLEAN) {
- c = String.valueOf(cell.getBooleanCellValue());
- } else {
- c = cell.getStringCellValue();
- }
- if (c == null || c.equals("")) {
- continue;
- }
- entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建.
- // System.out.println(cells[j].getContents());
- Field field = fieldsMap.get(j);// 从map中得到对应列的field.
- if (field == null) {
- continue;
- }
- // 取得类型,并根据对象类型设置值.
- Class<?> fieldType = field.getType();
- if (String.class == fieldType) {
- field.set(entity, String.valueOf(c));
- } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
- field.set(entity, Integer.parseInt(c));
- } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
- field.set(entity, Long.valueOf(c));
- } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
- field.set(entity, Float.valueOf(c));
- } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
- field.set(entity, Short.valueOf(c));
- } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
- field.set(entity, Double.valueOf(c));
- } else if (Character.TYPE == fieldType) {
- if (c.length() > 0) {
- field.set(entity, c.charAt(0));
- }
- }
- }
- if (entity != null) {
- list.add(entity);
- }
- }
- }
- } catch (IOException | InstantiationException | IllegalAccessException | IllegalArgumentException e) {
- e.printStackTrace();
- }
- return list;
- }
-
- /**
- * 对list数据源将其里面的数据导入到excel表单
- *
- * @param lists 数据
- * @param sheetNames 工作表的名称
- * @param output java输出流
- * @return 状态
- */
- public boolean exportExcel(List<T> lists[], String sheetNames[], OutputStream output) {
- if (lists.length != sheetNames.length) {
- System.out.println("数组长度不一致");
- return false;
- }
- HSSFWorkbook workbook = new HSSFWorkbook();// 产生工作薄对象
- for (int ii = 0; ii < lists.length; ii++) {
- List<T> list = lists[ii];
- String sheetName = sheetNames[ii];
- List<Field> fields = getMappedFiled(clazz, null);
- HSSFSheet sheet = workbook.createSheet();// 产生工作表对象
- workbook.setSheetName(ii, sheetName);
- HSSFRow row;
- HSSFCell cell;// 产生单元格
- HSSFCellStyle style = workbook.createCellStyle();
- style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
- style.setFillBackgroundColor(HSSFColor.GREY_40_PERCENT.index);
- row = sheet.createRow(0);// 产生一行
- // 写入各个字段的列头名称
- for (Field field : fields) {
- ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);
- int col = getExcelCol(attr.column());// 获得列号
- cell = row.createCell(col);// 创建列
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型
- cell.setCellValue(attr.name());// 写入列名
- // 如果设置了提示信息则鼠标放上去提示.
- if (!attr.prompt().trim().equals("")) {
- setHSSFPrompt(sheet, "", attr.prompt(), 1, lists[0].size(), col, col);// 这里默认设了2-101列提示.
- }
- // 如果设置了combo属性则本列只能选择不能输入
- if (attr.combo().length > 0) {
- setHSSFValidation(sheet, attr.combo(), 1, lists[0].size(), col, col);// 这里默认设了2-101列只能选择不能输入.
- }
- cell.setCellStyle(style);
- }
- int startNo = 0;
- int endNo = list.size(); // 写入各条记录,每条记录对应excel表中的一行
- for (int i = startNo; i < endNo; i++) {
- row = sheet.createRow(i + 1 - startNo);
- T vo = list.get(i); // 得到导出对象.
- for (Field field : fields) {
- field.setAccessible(true);// 设置实体类私有属性可访问
- ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class);
- try {
- // 根据ExcelVOAttribute中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
- if (attr.isExport()) {
- cell = row.createCell(getExcelCol(attr.column()));
- // 创建cell
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
- cell.setCellValue(field.get(vo) == null ? ""
- : String.valueOf(field.get(vo)));// 如果数据存在就填入,不存在填入空格.
- }
- } catch (IllegalArgumentException | IllegalAccessException e) {
- e.printStackTrace();
- }
- }
- }
- }
- try {
- output.flush();
- workbook.write(output);
- output.close();
- return true;
- } catch (IOException e) {
- e.printStackTrace();
- System.out.println("Output is closed ");
- return false;
- }
- }
-
- /**
- * 对list数据源将其里面的数据导入到excel表单
- *
- * @param list 数据
- * @param sheetName 工作表的名称
- * @param output java输出流
- * @return 状态
- */
- @SuppressWarnings("unchecked")
- public boolean exportExcel(List<T> list, String sheetName, OutputStream output) {
- //此处 对类型进行转换
- List<T> ilist = new ArrayList<>();
- ilist.addAll(list);
- List<T>[] lists = new ArrayList[1];
- lists[0] = ilist;
- String[] sheetNames = new String[1];
- sheetNames[0] = sheetName;
- return exportExcel(lists, sheetNames, output);
- }
-
- /**
- * 将EXCEL中A, B, C, D, E列映射成0, 1, 2, 3
- *
- * @param col 行
- */
- public static int getExcelCol(String col) {
- col = col.toUpperCase();
- // 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。
- int count = -1;
- char[] cs = col.toCharArray();
- count += IntStream.range(0, cs.length).map(i -> (int) ((cs[i] - 64) * Math.pow(26, cs.length - 1 - i))).sum();
- return count;
- }
-
- /**
- * 设置单元格上提示
- *
- * @param sheet 要设置的sheet.
- * @param promptTitle 标题
- * @param promptContent 内容
- * @param firstRow 开始行
- * @param endRow 结束行
- * @param firstCol 开始列
- * @param endCol 结束列
- * @return 设置好的sheet.
- */
- public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow,
- int endRow, int firstCol, int endCol) {
- // 构造constraint对象
- DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
- // 四个参数分别是:起始行、终止行、起始列、终止列
- CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
- // 数据有效性对象
- HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);
- data_validation_view.createPromptBox(promptTitle, promptContent);
- sheet.addValidationData(data_validation_view);
- return sheet;
- }
-
- /**
- * 设置某些列的值只能输入预制的数据, 显示下拉框.
- *
- * @param sheet 要设置的sheet.
- * @param textlist 下拉框显示的内容
- * @param firstRow 开始行
- * @param endRow 结束行
- * @param firstCol 开始列
- * @param endCol 结束列
- * @return 设置好的sheet.
- */
- public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow,
- int firstCol, int endCol) {
- // 加载下拉列表内容
- DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);
- // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列
- CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
- // 数据有效性对象
- HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
- sheet.addValidationData(data_validation_list);
- return sheet;
- }
-
- /**
- * 得到实体类所有通过注解映射了数据表的字段
- *
- * @param clazz 类型
- * @param fields 文件集合
- * @return 文件集合
- */
- @SuppressWarnings("rawtypes")
- private List<Field> getMappedFiled(Class clazz, List<Field> fields) {
- if (fields == null) {
- fields = new ArrayList<>();
- }
- Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段
- // 得到所有field并存放到一个list中.
- for (Field field : allFields) {
- if (field.isAnnotationPresent(ExcelVOAttribute.class)) {
- fields.add(field);
- }
- }
- if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) {
- getMappedFiled(clazz.getSuperclass(), fields);
- }
- return fields;
- }
- }
工具类有了,我们需要一个自定义一个注解,方便在excel列对象上直接使用该注解对应出需要导出导入的内容,如下:
- @Retention(RetentionPolicy.RUNTIME)
- @Target({java.lang.annotation.ElementType.FIELD})
- public @interface ExcelVOAttribute {
-
- //导出到Excel中的名字.
- public abstract String name();
-
- //配置列的名称,对应A,B,C,D....
- public abstract String column();
-
- //提示信息.
- public abstract String prompt() default "";
-
- //设置只能选择不能输入的列内容
- public abstract String[] combo() default {};
-
- //是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
- public abstract boolean isExport() default true;
- }
接下来你就可以使用一个简单的例子来实现,我想导出一个excel,方便用户提现申请审核,然后再把审核结果批量导入到数据库,同时更新数据库内容,首先需要定义一个excel列对应的实体对象取名(可随意)为:BackstageWithdrawDepositInfo,可使用lombok插件,少写getter和setter,这里就不详细介绍了,如下:
- @Data
- @Builder
- @NoArgsConstructor
- @AllArgsConstructor
- public class BackstageWithdrawDepositInfo {
-
- @ExcelVOAttribute(name = "钱包详情id", column = "A")
- private Long walletDetailsId;
-
- @ExcelVOAttribute(name = "用户id", column = "B")
- private Long userId;
-
- @ExcelVOAttribute(name = "申请时间", column = "C")
- private String createDate;
-
- @ExcelVOAttribute(name = "用户昵称", column = "D")
- private String nickName;
-
- @ExcelVOAttribute(name = "用户头像", column = "E")
- private String headImageUrl;
-
- @ExcelVOAttribute(name = "用户电话", column = "F")
- private String phoneNumber;
-
- @ExcelVOAttribute(name = "用户截止当日总提现次数", column = "G")
- private Long withdrawCount;
-
- @ExcelVOAttribute(name = "用户成功提现总金额(分)", column = "H")
- private Long withdrawSum;
-
- @ExcelVOAttribute(name = "本次提现金额(分)", column = "I")
- private Long count;
-
- @ExcelVOAttribute(name = "批复状态", column = "J", combo = {"提现审核", "提现成功", "提现失败"})
- private String status;
-
- @ExcelVOAttribute(name = "拒绝原因", column = "K", combo = {"抱歉,活动已结束,更多精彩活动正在筹备中,敬请期待……",
- "抱歉,今日平台提现金额已达上线,明天早点来哦", "抱歉,平台显示账户异常,如有疑问,请联系小轻老师咨询(微信号:qingtiku)",
- "其他原因,详情可联系小轻老师咨询(微信号:qingtiku)"})
- private String note;
- }
这样呢一个简单的对象就构建完成了,但是呢,如何使用咱们工具类中对应的导入和导出方法呢,回想一下里面方法参数可能就有思路了,回顾一下:
- /**
- * 对list数据源将其里面的数据导入到excel表单
- *
- * @param list 数据
- * @param sheetName 工作表的名称
- * @param output java输出流
- * @return 状态
- */
- @SuppressWarnings("unchecked")
- public boolean exportExcel(List<T> list, String sheetName, OutputStream output) {
- //此处 对类型进行转换
- List<T> ilist = new ArrayList<>();
- ilist.addAll(list);
- List<T>[] lists = new ArrayList[1];
- lists[0] = ilist;
- String[] sheetNames = new String[1];
- sheetNames[0] = sheetName;
- return exportExcel(lists, sheetNames, output);
- }
从上面方法中看出导出操作需要的参数是,一个list集合(对应的我们构建的实体类BackstageWithdrawDepositInfo,其次呢还要指定一个excel的Sheet名称和一个具体路径的输出流对象),具体的输出流就很简单了,就是把该excel指定一个输出的路径,FileOutputStream out = new FileOutputStream(filePath); 然后把out参数传入进去即可,最后调用exportExcel()方法即可,看着是不是很简单。但是下面这个需求就是在代码中一行行写每个excel的列,可做一点比较,我会把两个代码都放在码云上。
第二种方案(推荐)如下:
推荐使用Alibaba的开源二方包,简单的两行的两行代码,在配合上我第一种方案的Excel工具类,简直so easy,具体地址是:
https://github.com/alibaba/easyexcel
其maven依赖是:
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>easyexcel</artifactId>
- <version>1.0.1</version>
- </dependency>
我把我项目中用到的主要核心代码贴出来,让大家看一哈,
第一步,在值对象上来确定表头,这个需要继承BaseRowModel类,如下:
- @Data
- @Builder
- @AllArgsConstructor
- @NoArgsConstructor
- public class LegionUserDataDTO extends BaseRowModel {
- /**
- * 渠道来源
- */
- @ExcelProperty(value = "渠道来源", index = 0)
- private String branch;
-
- /**
- * 进入小程序次数
- */
- @ExcelProperty(value = "用户昵称", index = 1)
- private String nickName;
-
- /**
- * 授权手机号数量
- */
- @ExcelProperty(value = "授权手机号", index = 2)
- private String phoneNumber;
-
- }
第二步,需要构建一个文件的输出流,需要把数据写入到文件里,具体构建步骤方法有很多种,我这边使用了一个FileUtil工具类完成的,网上有很多,具体如下:
- // 先创建一个文件夹
- FileUtil.createTargetDir(BASE_PATH);
- // 指定该文件的路径
- String filePath = BASE_PATH + "芒果会计-军团数据统计-" + DateUtil.parseDateToStr(new Date(), "yyyyMMdd") + ".xls";
- // 通过文件路径构造出输出流
- FileOutputStream out = new FileOutputStream(filePath);
- // 接下来就是最主要的步骤,直接调用提供的Excelriter想excel写入数据
- ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLS, true);
- // 指定写入的sheet 有多个sheet就new 出多个,也可使用工厂方法,传入的参数1 表示为第一个sheet
- 0 表示从第一个单元格开始写入,传入继承了BaseRowModel的类和sheet名称即可。
- Sheet sheetStatistics = new Sheet(1, 0, LegionDataDTO.class, "渠道数据", null);
- // 开始写入 传入需要写入的数据集合和sheet对象
- writer.write(legionDataDTOS, sheetStatistics);
-
- Sheet sheetLegionUsers = new Sheet(2, 0, LegionUserDataDTO.class, "用户手机号", null);
- writer.write(legionUserDataDTOS, sheetLegionUsers);
- // 写入完成
- writer.finish();
- try {
- out.close();
- } catch (IOException e) {
- log.error("芒果会计-军团数据统计, error:", e.getMessage());
- }
然后你就能看到excel里有你需要的数据了。更复杂的表头需要在值对象继承BaseRowModel时候在该对象的字段上进行设置。
第三种方案如下:
一个很简单的需求,从后台中选取一个的Excel文件,并直接导入到数据库,然后根据导入的表查询个人信息,由于表的数据内容还挺多,有好几万条,所幸是单表。
一个很愚蠢的做法是每解析一个Row,然后构建一个新的对象,再把每个cell的值赋给该对象,直到循环结束,再通过mabatis的insert语句入库,这样很low,有没有???但是数据也能跑的过去,能完成入库,不过两万条数据,连解析带入库一共花了将近20分钟,天啦噜,这样客户会炸毛的。没办法,还得修改,看了很多办法,一种最直接的就是把解析和入库分开,然后重新构建sql批量插入,因为mybatis用的是generator插件自动生成的代码,想把自己构建的mapper接口内容放在mybatis自己的目录下,但是每次重新执行插件时候,自己新写的接口文件就会被清除掉,无奈之举,只能重新构建一个文件夹(excelmapper)来存储我的查询接口,这才逃过一劫,当我进行测试时候又报错了,无法扫描到excelmapper下面的接口内容,尝试在单元测试类上面配置@componentScan("com.net.excelmapper")注解,如果启动类在根包下面,则你可以在该类上添加@ComponentScan
注解而不需要添加任何参数,Spring Boot会在根包下面搜索注有@Component
, @Service
, @Repository
, @Controller
注解的所有类,并将他们注册为Spring Beans,否则,你需要在@ComponentScan
注解上定义basePackages或者其他属性。最后的解决办法是在启动类上添加了对应excelmapper文件夹下的扫描路径,@MapperScan({"com.neo.mapper","com.neo.excelmapper"}),合法化测试终于通过了,可是还有一个问题,上传超过了限制的默认值,只好在配置文件添加了配置项:
spring.http.multipart.max-file-size = 2048KB //支持文件上传最大的限制
spring.http.multipart.max-request-size = 10240kB //最大支持请求
spring.http.multipart.enabled = true#默认支持文件上传。
spring.http.multipart.file-size-threshold = 0#支持文件写入磁盘。
spring.http.multipart.location =#上传文件的临时目录
因为用的是表单进行提交,<input type =“file”>这种情况下在form标签下需要特殊的指定属性文件为formdata文件,例如:<form role =“form”action =“/ importExcel”method =“post” enctype =“multipart / form-data”>,这下终于可以完成数据导入了,不料,两万条数据果然还是花了20分钟,修改意见也就上面提到的,分开操作,先把解析的数据封装到一个对象上,并且存储到列表集合中,在从几何中批量插入。
- excelmapper接口:
- 公共接口ExcelMapper {
-
- void batchInsert(List <Tbagent> tbagentList);
- }
公共接口ExcelMapper {
void batchInsert(List <Tbagent> tbagentList);
}
excelmapper.xml中主要的SQL语句,也就是执行批量操作的SQL
- <insert id="batchInsert" parameterType="java.util.List">
- INSERT INTO agent(job_number,department,region,username,idcard,company_rankings,department_rankings,region_rankings,
- distance_first_company,distance_first_department,distance_first_region)
- VALUES
- <foreach collection="list" item="item" index="index" separator=",">
- (#{item.job_number},#{item.department},#{item.region},#{item.username},#{item.idcard},#{item.company_rankings},
- #{item.department_rankings},#{item.region_rankings},#{item.distance_first_company},#{item.distance_first_department},#{item.distance_first_region})
-
- </foreach>
- </insert>
- 主要的服务层:
-
- /**
- * Created by Administrator on 2018/1/11.
- */
- @Service
- public class IimportServiceImpl implements IimportService {
-
- private final static String XLS = "xls";
- public static final String XLSX = "xlsx";
-
- private final static Logger logger = LoggerFactory.getLogger(IimportServiceImpl.class);
-
- @Autowired private TbagentMapper tbagentMapper;
-
- @Autowired private SqlSessionTemplate sqlSessionTemplate;
-
- @Autowired private ExcelMapper excelMapper;
-
- @Override
- public Integer importExcel(MultipartFile myFile) {
- //1. 使用HSSFWorkbook 打开或者创建 “Excel对象”
- //2. 用HSSFWorkbook返回对象或者创建sheet对象
- //3. 用sheet返回行对象,用行对象得到Cell对象
- //4. 对Cell对象进行读写
- List<Tbagent> tbagents = new ArrayList<>();
- Workbook workbook = null;
- String fileName = myFile.getOriginalFilename();// 获取文件名
- logger.info("【fileName】{}",fileName);
- if (fileName.endsWith(XLS))
- {
- try {
- workbook = new HSSFWorkbook(myFile.getInputStream());// 2003版本
-
- } catch (IOException e) {
- e.printStackTrace();
- }
- }else if(fileName.endsWith(XLSX)){
- try {
- workbook = new XSSFWorkbook(myFile.getInputStream());// 2007版本
- } catch (IOException e) {
- e.printStackTrace();
- }
- }else{
- throw new LianjiaException(ResultEnum.FILE_IS_NOT_EXCEL); // 文件不是Excel文件
- }
- Sheet sheet = workbook.getSheet("sheet1");
- int rows = sheet.getLastRowNum();
- logger.info("【rows】{}",rows);
- if(rows == 0){
- throw new LianjiaException(ResultEnum.DATA_IS_NULL);// 数据为空 请填写数据
- }
- long startTime = System.currentTimeMillis();
- for(int i = 1;i<= rows+1;i++){
- Row row = sheet.getRow(i);
-
- if(row !=null){
-
- Tbagent tbagent = new Tbagent();
- // 部门
- String department = getCellValue(row.getCell(0));
- tbagent.setDepartment(department);
- // 用户姓名
- String username = getCellValue(row.getCell(1));
- tbagent.setUsername(username);
- // 工号
- String jobNumer = getCellValue(row.getCell(2));
- if(!StringUtils.isEmpty(jobNumer)){
- Integer job_number = Integer.parseInt(jobNumer);
- tbagent.setJob_number(Integer.valueOf(jobNumer));
- }
- // 身份证后六位
- String idcard = getCellValue(row.getCell(3));
- tbagent.setIdcard(idcard);
- // 公司排名
- String companyRankings = getCellValue(row.getCell(4));
- if(!StringUtils.isEmpty(companyRankings)){
- Integer new_companyRankings = Integer.parseInt(companyRankings);
- tbagent.setCompany_rankings(new_companyRankings);
- }
- // 事业部排名
- String departmenRanks = getCellValue(row.getCell(5));
- if(!StringUtils.isEmpty(departmenRanks)){
- Integer new_departmentRanks = Integer.parseInt(departmenRanks);
- tbagent.setDepartment_rankings(new_departmentRanks);
- }
- // 大区排名
- String region_Ranks = getCellValue(row.getCell(6));
- if(!StringUtils.isEmpty(region_Ranks)){
- Integer new_region_Rankings = Integer.parseInt(region_Ranks);
- //logger.info("【大区排名】{}",new_region_Rankings);
- tbagent.setRegion_rankings(new_region_Rankings);
- }// 距离公司第一名差距
- String distance_first_company = getCellValue(row.getCell(7));
- if(!StringUtils.isEmpty(distance_first_company)){
- Long new_distance_first_company = Long.parseLong(distance_first_company);
- tbagent.setDistance_first_company(new_distance_first_company);
- }
- // 距离事业部第一名差距
- String distance_first_deparment = getCellValue(row.getCell(8));
- if(!StringUtils.isEmpty(distance_first_deparment)){
- Long new_distance_first_deparment = Long.parseLong(distance_first_deparment);
- tbagent.setDistance_first_department(new_distance_first_deparment);
- }
- // 距离大区第一名差距
- String distance_first_region = getCellValue(row.getCell(9));
- if(!StringUtils.isEmpty(distance_first_region)){
- Long new_distance_first_region = Long.parseLong(distance_first_region);
- tbagent.setDistance_first_region(new_distance_first_region);
- }
- //System.out.println(JSON.toJSON(tbagent));
- //tbagentMapper.insert(tbagent);
- tbagents.add(tbagent);
- //logger.info("插入数据完成");
- }
-
- }
- excelMapper.batchInsert(tbagents); // 批量插入 五秒完成
- long endTime = System.currentTimeMillis();
- long totaltime = endTime - startTime;
- logger.info("【消耗时间为】{}",totaltime); // 将近两万条数据 3秒解析完成
- logger.info("【第一条数据为】{}",JSON.toJSON(tbagents.get(0)));
- return rows;
- }
-
- public String getCellValue(Cell cell) {
- String value = "";
- if (cell != null) {
- switch(cell.getCellType()){
- case HSSFCell.CELL_TYPE_NUMERIC:// 数字
- value = cell.getNumericCellValue()+ " ";
- if(HSSFDateUtil.isCellDateFormatted(cell)){
- Date date = cell.getDateCellValue();
- if(date != null){
- value = new SimpleDateFormat("yyyy-MM-dd").format(date); // 日期格式化
- }else{
- value = "";
- }
- }else {
- // 解析cell时候 数字类型默认是double类型的 但是想要获取整数类型 需要格式化
- value = new DecimalFormat("0").format(cell.getNumericCellValue());
- }
- break;
- case HSSFCell.CELL_TYPE_STRING: // 字符串
- value = cell.getStringCellValue();
- break;
- case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean类型
- value = cell.getBooleanCellValue()+"";
- break;
- case HSSFCell.CELL_TYPE_BLANK: // 空值
- value = "";
- break;
- case HSSFCell.CELL_TYPE_ERROR: // 错误类型
- value ="非法字符";
- break;
- default:
- value = "未知类型";
- break;
- }
-
- }
- return value.trim();
- }
-
- }
部分效果图:
登录效果图如上。
不太完美的地方就是没有一个进度条给客户看,后期需要做处理。清空数据用的是MySQL的的截断语句,直接清空所有的数据。然后让用户在导入到数据库。
源码地址 importexcel 分支
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。