基于ssm的poi反射bean实例
一:该例子是笔者在实际项目应用过程中,针对项目完成的一套基于poi的导入导出例子,其中一些与项目有关的代码大家直接替换成自己的需求即可。
二:笔者在项目中使用的是poi的XSSF,对应maven的pom依赖如下:
- <poi.version>3.9</poi.version>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>${poi.version}</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml</artifactId>
- <version>${poi.version}</version>
- </dependency>
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi-ooxml-schemas</artifactId>
- <version>${poi.version}</version>
- </dependency>
接下来首先是jsp页面:
- <script type="text/javascript">
- $(document).ready(function() {
- $("#btnExport").click(function(){
- top.$.jBox.confirm("确认要导出工单数据吗?","系统提示",function(v,h,f){
- if(v=="ok"){
- $("#searchForm").attr("action","${ctx}/wxpt/workorder/wxptWorkorderInfo/export");
- $("#searchForm").submit();
- }
- },{buttonsFocus:1});
- top.$('.jbox-body .jbox-icon').css('top','55px');
- });
- $("#btnImportSubmit").click(function(){
- $.jBox($("#importBox").html(), {title:"导入数据", buttons:{"关闭":true},
- bottomText:"导入文件不能超过5M,仅允许导入“xls”或“xlsx”格式文件!"});
- });
- });
- </script>
- <script type="text/javascript" src="${ctxStatic}/js/modules/wxpt/workorder/wxptWorkorderInfo.js"></script>
- </head>
- <body>
- <div id="importBox">
- <form id="importForm" action="${ctx}/wxpt/workorder/wxptWorkorderInfo/import" method="post" enctype="multipart/form-data"
- class="form-search" style="padding-left:20px;text-align:center;" onsubmit="loading('正在导入,请稍等...');"><br/>
- <input id="uploadFile" name="file" type="file" style="width:330px"/><br/><br/>
- <input id="btnImportSubmit" class="btn btn-primary" type="submit" value=" 导 入 "/>
- <input id="btnExport" class="btn btn-primary" type="button" value="导出"/>
- <a href="${ctx}/sys/user/import/template">下载模板</a>
- </form>
- </div>
- </body>
其中导出涉及不到表单数据的提交,笔者为了操作简便,直接将查询的表单的action换成了导出的地址,在此并没有列出来,大家换成a标签也可。
然后是控制层代码:
导出:
- /**
- *导出数据
- *@version 2018-04-02
- */
- @RequiresPermissions("wxpt:workorder:wxptWorkorderInfo:view")
- @RequestMapping(value = "export", method = RequestMethod.POST)
- public String exportToFile(HttpServletRequest request, HttpServletResponse response,
- RedirectAttributes redirectAttributes, WxptWorkorderInfo wxptWorkorderInfo) {
- try {
- String fileName = DateUtils.getDate("工单yyyyMMddHHmmss") + ".xlsx";
- List<WxptWorkorderInfo> list = wxptWorkorderInfoService.findList(wxptWorkorderInfo);
- new ExportExcel("工单", WxptWorkorderInfo.class).setDataList(list).write(response, fileName).dispose();
- return null;
- } catch (Exception e) {
- addMessage(redirectAttributes, "导出工单失败!失败信息:" + e.getMessage());
- }
- return "redirect:" + Global.getAdminPath() + "/wxpt/workorder/wxptWorkorderInfo/?repage";
- }
exportExcel:(一个工具类,直接复制粘贴即可,注释很明确)
- /**
- * Copyright © 2012-2016 <a href="https://github.com/thinkgem/smkj">smkj</a> All rights reserved.
- */
- package com.sm.modules.wxpt.utils.excel;
- import java.io.FileNotFoundException;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.OutputStream;
- import java.lang.reflect.Field;
- import java.lang.reflect.Method;
- import java.util.Collections;
- import java.util.Comparator;
- import java.util.Date;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.commons.lang3.StringUtils;
- import org.apache.poi.ss.usermodel.Cell;
- import org.apache.poi.ss.usermodel.CellStyle;
- import org.apache.poi.ss.usermodel.Comment;
- import org.apache.poi.ss.usermodel.Font;
- import org.apache.poi.ss.usermodel.IndexedColors;
- 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.ss.util.CellRangeAddress;
- import org.apache.poi.xssf.streaming.SXSSFWorkbook;
- import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
- import org.apache.poi.xssf.usermodel.XSSFRichTextString;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import com.google.common.collect.Lists;
- import com.sm.modules.sys.utils.DictUtils;
- import com.sm.utils.Encodes;
- import com.sm.utils.Reflections;
- import com.sm.utils.excel.annotation.ExcelField;
- /**
- * 导出Excel文件(导出“XLSX”格式,支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion)
- * @author 冯文哲
- * @version 2013-04-21
- */
- public class ExportExcel {
- private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
- /**
- * 工作薄对象
- */
- private SXSSFWorkbook wb;
- /**
- * 工作表对象
- */
- private Sheet sheet;
- /**
- * 样式列表
- */
- private Map<String, CellStyle> styles;
- /**
- * 当前行号
- */
- private int rownum;
- /**
- * 注解列表(Object[]{ ExcelField, Field/Method })
- */
- List<Object[]> annotationList = Lists.newArrayList();
- /**
- * 构造函数
- * @param title 表格标题,传“空值”,表示无标题
- * @param cls 实体对象,通过annotation.ExportField获取标题
- */
- public ExportExcel(String title, Class<?> cls){
- this(title, cls, 1);
- }
- /**
- * 构造函数
- * @param title 表格标题,传“空值”,表示无标题
- * @param cls 实体对象,通过annotation.ExportField获取标题
- * @param type 导出类型(1:导出数据;2:导出模板)
- * @param groups 导入分组
- */
- public ExportExcel(String title, Class<?> cls, int type, int... groups){
- // Get annotation field
- Field[] fs = cls.getDeclaredFields();
- for (Field f : fs){
- ExcelField ef = f.getAnnotation(ExcelField.class);
- if (ef != null && (ef.type()==0 || ef.type()==type)){
- if (groups!=null && groups.length>0){
- boolean inGroup = false;
- for (int g : groups){
- if (inGroup){
- break;
- }
- for (int efg : ef.groups()){
- if (g == efg){
- inGroup = true;
- annotationList.add(new Object[]{ef, f});
- break;
- }
- }
- }
- }else{
- annotationList.add(new Object[]{ef, f});
- }
- }
- }
- // Get annotation method
- Method[] ms = cls.getDeclaredMethods();
- for (Method m : ms){
- ExcelField ef = m.getAnnotation(ExcelField.class);
- if (ef != null && (ef.type()==0 || ef.type()==type)){
- if (groups!=null && groups.length>0){
- boolean inGroup = false;
- for (int g : groups){
- if (inGroup){
- break;
- }
- for (int efg : ef.groups()){
- if (g == efg){
- inGroup = true;
- annotationList.add(new Object[]{ef, m});
- break;
- }
- }
- }
- }else{
- annotationList.add(new Object[]{ef, m});
- }
- }
- }
- // Field sorting
- Collections.sort(annotationList, new Comparator<Object[]>() {
- @Override
- public int compare(Object[] o1, Object[] o2) {
- return new Integer(((ExcelField)o1[0]).sort()).compareTo(
- new Integer(((ExcelField)o2[0]).sort()));
- };
- });
- // Initialize
- List<String> headerList = Lists.newArrayList();
- for (Object[] os : annotationList){
- String t = ((ExcelField)os[0]).title();
- // 如果是导出,则去掉注释
- if (type==1){
- String[] ss = StringUtils.split(t, "**", 2);
- if (ss.length==2){
- t = ss[0];
- }
- }
- headerList.add(t);
- }
- initialize(title, headerList);
- }
- /**
- * 构造函数
- * @param title 表格标题,传“空值”,表示无标题
- * @param headers 表头数组
- */
- public ExportExcel(String title, String[] headers) {
- initialize(title, Lists.newArrayList(headers));
- }
- /**
- * 构造函数
- * @param title 表格标题,传“空值”,表示无标题
- * @param headerList 表头列表
- */
- public ExportExcel(String title, List<String> headerList) {
- initialize(title, headerList);
- }
- /**
- * 初始化函数
- * @param title 表格标题,传“空值”,表示无标题
- * @param headerList 表头列表
- */
- private void initialize(String title, List<String> headerList) {
- this.wb = new SXSSFWorkbook(500);
- this.sheet = wb.createSheet("Export");
- this.styles = createStyles(wb);
- // Create title
- if (StringUtils.isNotBlank(title)){
- Row titleRow = sheet.createRow(rownum++);
- titleRow.setHeightInPoints(30);
- Cell titleCell = titleRow.createCell(0);
- titleCell.setCellStyle(styles.get("title"));
- titleCell.setCellValue(title);
- sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
- titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
- }
- // Create header
- if (headerList == null){
- throw new RuntimeException("headerList not null!");
- }
- Row headerRow = sheet.createRow(rownum++);
- headerRow.setHeightInPoints(16);
- for (int i = 0; i < headerList.size(); i++) {
- Cell cell = headerRow.createCell(i);
- cell.setCellStyle(styles.get("header"));
- String[] ss = StringUtils.split(headerList.get(i), "**", 2);
- if (ss.length==2){
- cell.setCellValue(ss[0]);
- Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
- new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
- comment.setString(new XSSFRichTextString(ss[1]));
- cell.setCellComment(comment);
- }else{
- cell.setCellValue(headerList.get(i));
- }
- sheet.autoSizeColumn(i);
- }
- for (int i = 0; i < headerList.size(); i++) {
- int colWidth = sheet.getColumnWidth(i)*2;
- sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
- }
- log.debug("Initialize success.");
- }
- /**
- * 创建表格样式
- * @param wb 工作薄对象
- * @return 样式列表
- */
- private Map<String, CellStyle> createStyles(Workbook wb) {
- Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
- CellStyle style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- Font titleFont = wb.createFont();
- titleFont.setFontName("Arial");
- titleFont.setFontHeightInPoints((short) 16);
- titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
- style.setFont(titleFont);
- styles.put("title", style);
- style = wb.createCellStyle();
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- style.setBorderRight(CellStyle.BORDER_THIN);
- style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderLeft(CellStyle.BORDER_THIN);
- style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderTop(CellStyle.BORDER_THIN);
- style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
- Font dataFont = wb.createFont();
- dataFont.setFontName("Arial");
- dataFont.setFontHeightInPoints((short) 10);
- style.setFont(dataFont);
- styles.put("data", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(CellStyle.ALIGN_LEFT);
- styles.put("data1", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(CellStyle.ALIGN_CENTER);
- styles.put("data2", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- style.setAlignment(CellStyle.ALIGN_RIGHT);
- styles.put("data3", style);
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"));
- // style.setWrapText(true);
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- Font headerFont = wb.createFont();
- headerFont.setFontName("Arial");
- headerFont.setFontHeightInPoints((short) 10);
- headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
- headerFont.setColor(IndexedColors.WHITE.getIndex());
- style.setFont(headerFont);
- styles.put("header", style);
- return styles;
- }
- /**
- * 添加一行
- * @return 行对象
- */
- public Row addRow(){
- return sheet.createRow(rownum++);
- }
- /**
- * 添加一个单元格
- * @param row 添加的行
- * @param column 添加列号
- * @param val 添加值
- * @return 单元格对象
- */
- public Cell addCell(Row row, int column, Object val){
- return this.addCell(row, column, val, 0, Class.class);
- }
- /**
- * 添加一个单元格
- * @param row 添加的行
- * @param column 添加列号
- * @param val 添加值
- * @param align 对齐方式(1:靠左;2:居中;3:靠右)
- * @return 单元格对象
- */
- public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
- Cell cell = row.createCell(column);
- String cellFormatString = "@";
- try {
- if(val == null){
- cell.setCellValue("");
- }else if(fieldType != Class.class){
- cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
- }else{
- if(val instanceof String) {
- cell.setCellValue((String) val);
- }else if(val instanceof Integer) {
- cell.setCellValue((Integer) val);
- cellFormatString = "0";
- }else if(val instanceof Long) {
- cell.setCellValue((Long) val);
- cellFormatString = "0";
- }else if(val instanceof Double) {
- cell.setCellValue((Double) val);
- cellFormatString = "0.00";
- }else if(val instanceof Float) {
- cell.setCellValue((Float) val);
- cellFormatString = "0.00";
- }else if(val instanceof Date) {
- cell.setCellValue((Date) val);
- cellFormatString = "yyyy-MM-dd HH:mm";
- }else {
- cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
- "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
- }
- }
- if (val != null){
- CellStyle style = styles.get("data_column_"+column);
- if (style == null){
- style = wb.createCellStyle();
- style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));
- style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
- styles.put("data_column_" + column, style);
- }
- cell.setCellStyle(style);
- }
- } catch (Exception ex) {
- log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
- cell.setCellValue(val.toString());
- }
- return cell;
- }
- /**
- * 添加数据(通过annotation.ExportField添加数据)
- * @return list 数据列表
- */
- public <E> ExportExcel setDataList(List<E> list){
- for (E e : list){
- int colunm = 0;
- Row row = this.addRow();
- StringBuilder sb = new StringBuilder();
- for (Object[] os : annotationList){
- ExcelField ef = (ExcelField)os[0];
- Object val = null;
- // Get entity value
- try{
- if (StringUtils.isNotBlank(ef.value())){
- val = Reflections.invokeGetter(e, ef.value());
- }else{
- if (os[1] instanceof Field){
- val = Reflections.invokeGetter(e, ((Field)os[1]).getName());
- }else if (os[1] instanceof Method){
- val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
- }
- }
- // If is dict, get dict label
- if (StringUtils.isNotBlank(ef.dictType())){
- val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
- }
- }catch(Exception ex) {
- // Failure to ignore
- log.info(ex.toString());
- val = "";
- }
- this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
- sb.append(val + ", ");
- }
- log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
- }
- return this;
- }
- /**
- * 输出数据流
- * @param os 输出数据流
- */
- public ExportExcel write(OutputStream os) throws IOException{
- wb.write(os);
- return this;
- }
- /**
- * 输出到客户端
- * @param fileName 输出文件名
- */
- public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
- response.reset();
- response.setContentType("application/octet-stream; charset=utf-8");
- response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
- write(response.getOutputStream());
- return this;
- }
- /**
- * 输出到文件
- * @param fileName 输出文件名
- */
- public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
- FileOutputStream os = new FileOutputStream(name);
- this.write(os);
- return this;
- }
- /**
- * 清理临时文件
- */
- public ExportExcel dispose(){
- wb.dispose();
- return this;
- }
- // /**
- // * 导出测试
- // */
- // public static void main(String[] args) throws Throwable {
- //
- // List<String> headerList = Lists.newArrayList();
- // for (int i = 1; i <= 10; i++) {
- // headerList.add("表头"+i);
- // }
- //
- // List<String> dataRowList = Lists.newArrayList();
- // for (int i = 1; i <= headerList.size(); i++) {
- // dataRowList.add("数据"+i);
- // }
- //
- // List<List<String>> dataList = Lists.newArrayList();
- // for (int i = 1; i <=1000000; i++) {
- // dataList.add(dataRowList);
- // }
- //
- // ExportExcel ee = new ExportExcel("表格标题", headerList);
- //
- // for (int i = 0; i < dataList.size(); i++) {
- // Row row = ee.addRow();
- // for (int j = 0; j < dataList.get(i).size(); j++) {
- // ee.addCell(row, j, dataList.get(i).get(j));
- // }
- // }
- //
- // ee.writeFile("target/export.xlsx");
- //
- // ee.dispose();
- //
- // log.debug("Export success.");
- //
- // }
- }
该导出是基于bean的导出,就要用到java的反射机制,关于bean的配置是在java实体类上通过注解来实现的:
- /**
- * Copyright © 2012-2016 <a href="http://www.yh3m.com">smkj</a> All rights reserved.
- */
- package com.sm.modules.wxpt.workorder.entity;
- import java.util.Date;
- import javax.validation.constraints.NotNull;
- import org.hibernate.validator.constraints.Length;
- import org.hibernate.validator.constraints.NotEmpty;
- import com.fasterxml.jackson.annotation.JsonFormat;
- import com.sm.common.persistence.DataEntity;
- /**
- * 工单表Entity
- * @author 冯文哲
- * @version 2018-04-02
- */
- public class WxptWorkorderInfo extends DataEntity<WxptWorkorderInfo> {
- private static final long serialVersionUID = 1L;
- private String systemId; // 工单导入或录入时自动生成的系统编号
- private String oldSystemId; // 工单导入时原厂家自带的编号
- private String uname; // 用户姓名
- public WxptWorkorderInfo() {
- super();
- }
- public WxptWorkorderInfo(String id){
- super(id);
- }
- @Length(min=0, max=64, message="工单导入时原厂家自带的编号长度必须介于 0 和 64 之间")
- @com.sm.utils.excel.annotation.ExcelField(title = "服务报告", align = 2, sort = 1)
- public String getOldSystemId() {
- return oldSystemId;
- }
- public void setOldSystemId(String oldSystemId) {
- this.oldSystemId = oldSystemId;
- }
- @Length(min=0, max=64, message="用户姓名长度必须介于 0 和 64 之间")
- @NotEmpty(message="用户姓名不能为空")
- @com.sm.utils.excel.annotation.ExcelField(title = "客户信息", align = 2, sort = 3)
- public String getUname() {
- return uname;
- }
- public void setUname(String uname) {
- this.uname = uname;
- }
- }
该注解定义:
- /**
- * Copyright © 2012-2016 <a href="https://github.com/thinkgem/smkj">smkj</a> All rights reserved.
- */
- package com.sm.common.utils.excel.annotation;
- import java.lang.annotation.ElementType;
- import java.lang.annotation.Retention;
- import java.lang.annotation.RetentionPolicy;
- import java.lang.annotation.Target;
- /**
- * Excel注解定义
- * @author ThinkGem
- * @version 2013-03-10
- */
- @Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
- @Retention(RetentionPolicy.RUNTIME)
- public @interface ExcelField {
- /**
- * 导出字段名(默认调用当前字段的“get”方法,如指定导出字段为对象,请填写“对象名.对象属性”,例:“area.name”、“office.name”)
- */
- String value() default "";
- /**
- * 导出字段标题(需要添加批注请用“**”分隔,标题**批注,仅对导出模板有效)
- */
- String title();
- /**
- * 字段类型(0:导出导入;1:仅导出;2:仅导入)
- */
- int type() default 0;
- /**
- * 导出字段对齐方式(0:自动;1:靠左;2:居中;3:靠右)
- */
- int align() default 0;
- /**
- * 导出字段字段排序(升序)
- */
- int sort() default 0;
- /**
- * 如果是字典类型,请设置字典的type值
- */
- String dictType() default "";
- /**
- * 反射类型
- */
- Class<?> fieldType() default Class.class;
- /**
- * 字段归属组(根据分组导出导入)
- */
- int[] groups() default {};
- }
注解的title即导出的标题。
导入: 首先创造一个map做excel标题和实体类字段映射用,即下面代码的header。
然后调用:
- bindToModels方法将excel映射到实体类上,后续代码可自行处理业务逻辑。
- /**
- * 导入数据
- *
- * @param file
- * @param redirectAttributes
- * @return
- */
- @RequiresPermissions("wxpt:workorder:wxptWorkorderInfo:edit")
- @RequestMapping(value = "import", method = RequestMethod.POST)
- public String importFile(MultipartFile file, RedirectAttributes redirectAttributes) {
- Map<String, String> header = new HashMap<String, String>();
- header.put("服务报告", "oldSystemId");
- header.put("服务类别", "serviceType");
- header.put("客户信息", "uname");
- header.put("市", "areaCode");
- header.put("地址", "uaddress");
- header.put("产品信息", "brand");
- header.put("附加保修文档号", "fujiabaoxiu");
- header.put("维修建议", "weixiujianyi");
- header.put("配件建议", "peijianjianyi");
- header.put("申请备注", "notesAndInstructions");
- header.put("申请时间", "serviceTime");
- header.put("技术文档", "jishuwendang");
- try {
- int successNum = 0;
- int failureNum = 0;
- StringBuilder failureMsg = new StringBuilder();
- ImBeanExcel export = new ImBeanExcel(header);
- export.init(file);
- //List<WxptWorkorderInfo> list = ei.getDataList(WxptWorkorderInfo.class);
- List<WxptWorkorderInfo> list = export.bindToModels(WxptWorkorderInfo.class, true);
- if (export.hasError())
- {
- System.out.println(export.getError().toString());
- }
- for (WxptWorkorderInfo workorder : list) {
- String indent = randomIndentnum.getIndent();
- workorder.setSystemId(indent);
- for (int j = 0; j < workorder.getUname().length(); j++) {//处理导入数据
- //拆分用户和电话
- if(workorder.getUname().charAt(j)>=48 && workorder.getUname().charAt(j)<=57){
- String uname = workorder.getUname().substring(0,j);
- String uphone = workorder.getUname().substring(j,workorder.getUname().length());
- workorder.setUname(uname);
- workorder.setUphone(uphone.trim());
- }
- }
- //拆分品牌,产品类别,服务,型号
- String [] bra = workorder.getBrand().split("\\s+");
- List<WxptBrandsDict> brandsDict = wxptBrandsDictService.findList(new WxptBrandsDict());
- List<WxptProductDict> productDict = wxptProductDictService.findList(new WxptProductDict());
- List<WxptServiceDict> serviceDict = wxptServiceDictService.findList(new WxptServiceDict());
- workorder = ImportDivision.divisionBrand(bra,workorder,brandsDict,productDict,serviceDict);
- }
- for (WxptWorkorderInfo workorder : list) {
- try {
- if ("true".equals(checkOldSystemId("", workorder.getOldSystemId()))) {
- BeanValidators.validateWithException(validator, workorder);
- wxptWorkorderInfoService.save(workorder);
- successNum++;
- } else {
- failureMsg.append("<br/>工单编号 " + workorder.getOldSystemId() + " 已存在; ");
- failureNum++;
- }
- } catch (ConstraintViolationException ex) {
- failureMsg.append("<br/>工单编号 " + workorder.getOldSystemId() + " 导入失败:");
- List<String> messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": ");
- for (String message : messageList) {
- failureMsg.append(message + "; ");
- failureNum++;
- }
- } catch (Exception ex) {
- failureMsg.append("<br/>工单编号 " + workorder.getOldSystemId() + " 导入失败:" + ex.getMessage());
- }
- }
- if (failureNum > 0) {
- failureMsg.insert(0, ",失败 " + failureNum + " 条工单,导入信息如下:");
- }
- addMessage(redirectAttributes, "已成功导入 " + successNum + " 条工单" + failureMsg);
- } catch (Exception e) {
- addMessage(redirectAttributes, "导入工单失败!失败信息:" + e.getMessage());
- }
- return "redirect:" + adminPath + "/wxpt/workorder/wxptWorkorderInfo?repage";
- }
ImBeanExcel.java工具类:
- package com.sm.modules.wxpt.utils.excel;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.IOException;
- import java.lang.reflect.Method;
- import java.text.ParseException;
- import java.text.SimpleDateFormat;
- import java.util.ArrayList;
- import java.util.Date;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.apache.log4j.Logger;
- import org.apache.poi.ss.usermodel.DateUtil;
- import org.apache.poi.xssf.usermodel.XSSFCell;
- import org.apache.poi.xssf.usermodel.XSSFDataFormat;
- import org.apache.poi.xssf.usermodel.XSSFRow;
- import org.apache.poi.xssf.usermodel.XSSFSheet;
- import org.apache.poi.xssf.usermodel.XSSFWorkbook;
- import org.springframework.web.multipart.MultipartFile;
- /**
- * 将excel数据装换成数据模型
- *
- * @author
- *
- */
- public class ImBeanExcel {
- private static final int HEADER = 0;
- private static final int START = 1;
- private Logger log = Logger.getLogger(this.getClass());
- private XSSFWorkbook book;
- /**
- * key:excel对应标题 ,value:对象属性
- */
- private Map<String, String> associations;
- /**
- * 装换失败的数据信息,记录行数
- */
- private StringBuffer error = new StringBuffer(0);
- private Map<Integer, String> header;
- /**
- * 默认的日期格式
- */
- private String date_format = "yyyy-MM-dd";
- private SimpleDateFormat format;
- /**
- * 初始化工作簿
- *
- * @param file
- */
- public void init(MultipartFile file) {
- FileInputStream in;
- try {
- in = (FileInputStream) file.getInputStream();
- book = new XSSFWorkbook(in);
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- public ImBeanExcel(Map<String, String> associations) {
- this.associations = associations;
- format = new SimpleDateFormat(date_format);
- }
- public ImBeanExcel(Map<String, String> associations, String date_format) {
- this.associations = associations;
- this.date_format = date_format;
- format = new SimpleDateFormat(date_format);
- }
- /**
- *
- * @return true 存在错误,false 不存在错误
- */
- public boolean hasError() {
- return error.capacity() > 0;
- }
- public StringBuffer getError() {
- return error;
- }
- /**
- * 获取第一行标题栏数据
- *
- * @param sheet
- * @return map key:标题栏列下标(0开始) value 标题栏值
- */
- private void loadHeader(XSSFSheet sheet) {
- this.header = new HashMap<Integer, String>();
- XSSFRow row = sheet.getRow(HEADER);
- int columns = row.getLastCellNum();
- for (int i = 0; i < columns; i++) {
- log.debug("加载标题栏:" + row.getCell(i).getStringCellValue());
- String value = row.getCell(i).getStringCellValue();
- if (null == value) {
- throw new RuntimeException("标题栏不能为空!");
- }
- header.put(i, value);
- }
- log.debug("<<<<<<<<<<<<标题栏加载完毕>>>>>>>>>>>");
- }
- /**
- *
- * @param clazz
- * @param required
- * 是否每个属性都是必须的
- * @return
- */
- public <T> List<T> bindToModels(Class clazz, boolean required) throws Exception {
- // 获取第一页
- XSSFSheet sheet = this.book.getSheetAt(0);
- int rowNum = sheet.getLastRowNum();// 获取行数
- if (rowNum < 1) {
- return new ArrayList<T>();
- }
- // 加载标题栏数据
- this.loadHeader(sheet);
- List<T> result = new ArrayList<T>();
- for (int i = START; i < rowNum; i++) {
- XSSFRow row = sheet.getRow(i);
- int cellNum = row.getLastCellNum();
- T instance = (T) clazz.newInstance();
- for (int columns = 0; columns < cellNum; columns++) {
- XSSFCell cell = row.getCell(columns);
- // 判断单元格的数据类型
- String value = loadCellType(cell);
- // 获取单元格的值
- if (null == value) {
- // 如果为必填的则将错误信息记录
- if (required) {
- this.error.append("第" + (i + 1) + "行," + header.get(columns) + "字段,数据为空,跳过!").append("\n");
- log.debug("第" + (i + 1) + "行," + header.get(columns) + "字段,数据为空,跳过!");
- continue;
- }
- }else if("".equals(value.trim())){
- // 如果为必填的则将错误信息记录
- if (required) {
- this.error.append("第" + (i + 1) + "行," + header.get(columns) + "字段,数据为空,跳过!").append("\n");
- log.debug("第" + (i + 1) + "行," + header.get(columns) + "字段,数据为空,跳过!");
- continue;
- }
- } else {
- String key = header.get(columns);
- // 加载实际值
- value = value.replaceAll( "\\\\","");
- this.loadValue(clazz, instance, this.associations.get(key), value);
- }
- }
- result.add(instance);
- }
- log.debug("<<<<<装换完成" + (this.hasError() ? "有错误信息" : "") + ",共有对象:" + result.size() + "个" + ">>>>>>");
- return result;
- }
- /**
- * 将单元格数据转换成string类型
- *
- * @param cellType
- * @param cell
- * @return
- */
- private String loadCellType(XSSFCell cell) {
- String value = null;
- switch (cell.getCellType()) {
- case XSSFCell.CELL_TYPE_BOOLEAN:
- value = String.valueOf(cell.getBooleanCellValue());
- break;
- case XSSFCell.CELL_TYPE_NUMERIC:
- // 判断当前的cell是否为Date
- if (DateUtil.isCellDateFormatted(cell)) {
- value = this.formateDate(cell.getDateCellValue());
- } else {
- value = String.valueOf((long) cell.getNumericCellValue());
- }
- break;
- case XSSFCell.CELL_TYPE_STRING:
- value = cell.getStringCellValue();
- break;
- case XSSFCell.CELL_TYPE_FORMULA:
- log.debug("不支持函数!");
- break;
- }
- return value;
- }
- /**
- * 注入属性值
- *
- * @param instance
- * @param pro
- * 属性对象
- * @param value
- * 属性值
- */
- @SuppressWarnings("unchecked")
- private <T> void loadValue(Class clazz, T instance, String pro, String value)
- throws SecurityException, NoSuchMethodException, Exception {
- String getMethod = this.initGetMethod(pro);
- Class type = clazz.getDeclaredMethod(getMethod, null).getReturnType();
- Method method = clazz.getMethod(this.initSetMethod(pro), type);
- if (type == String.class) {
- method.invoke(instance, value);
- } else if (type == int.class || type == Integer.class) {
- method.invoke(instance, Integer.parseInt(value));
- } else if (type == long.class || type == Long.class) {
- method.invoke(instance, Long.parseLong(value));
- } else if (type == float.class || type == Float.class) {
- method.invoke(instance, Float.parseFloat(value));
- } else if (type == double.class || type == Double.class) {
- method.invoke(instance, Double.parseDouble(value));
- } else if (type == Date.class) {
- method.invoke(instance, this.parseDate(value));
- }
- }
- private Date parseDate(String value) throws ParseException {
- value = value.replaceAll("/", "-");
- return format.parse(value);
- }
- private String formateDate(Date date) {
- return format.format(date);
- }
- public String initSetMethod(String field) {
- return "set" + field.substring(0, 1).toUpperCase() + field.substring(1);
- }
- public String initGetMethod(String field) {
- return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);
- }
- public String getDate_format() {
- return date_format;
- }
- public void setDate_format(String date_format) {
- this.date_format = date_format;
- }
- }
至此一套完整流程的导入导出基本完成,可复用。