一:该例子是笔者在实际项目应用过程中,针对项目完成的一套基于poi的导入导出例子,其中一些与项目有关的代码大家直接替换成自己的需求即可。

 

二:笔者在项目中使用的是poi的XSSF,对应maven的pom依赖如下:

  1. <poi.version>3.9</poi.version>
  2. <dependency>
  3. <groupId>org.apache.poi</groupId>
  4. <artifactId>poi</artifactId>
  5. <version>${poi.version}</version>
  6. </dependency>
  7. <dependency>
  8. <groupId>org.apache.poi</groupId>
  9. <artifactId>poi-ooxml</artifactId>
  10. <version>${poi.version}</version>
  11. </dependency>
  12. <dependency>
  13. <groupId>org.apache.poi</groupId>
  14. <artifactId>poi-ooxml-schemas</artifactId>
  15. <version>${poi.version}</version>
  16. </dependency>

  接下来首先是jsp页面:

  1. <script type="text/javascript">
  2. $(document).ready(function() {
  3. $("#btnExport").click(function(){
  4. top.$.jBox.confirm("确认要导出工单数据吗?","系统提示",function(v,h,f){
  5. if(v=="ok"){
  6. $("#searchForm").attr("action","${ctx}/wxpt/workorder/wxptWorkorderInfo/export");
  7. $("#searchForm").submit();
  8. }
  9. },{buttonsFocus:1});
  10. top.$('.jbox-body .jbox-icon').css('top','55px');
  11. });
  12. $("#btnImportSubmit").click(function(){
  13. $.jBox($("#importBox").html(), {title:"导入数据", buttons:{"关闭":true},
  14. bottomText:"导入文件不能超过5M,仅允许导入“xls”或“xlsx”格式文件!"});
  15. });
  16. });
  17. </script>
  18. <script type="text/javascript" src="${ctxStatic}/js/modules/wxpt/workorder/wxptWorkorderInfo.js"></script>
  19. </head>
  20. <body>
  21. <div id="importBox">
  22. <form id="importForm" action="${ctx}/wxpt/workorder/wxptWorkorderInfo/import" method="post" enctype="multipart/form-data"
  23. class="form-search" style="padding-left:20px;text-align:center;" onsubmit="loading('正在导入,请稍等...');"><br/>
  24. <input id="uploadFile" name="file" type="file" style="width:330px"/><br/><br/>  
  25. <input id="btnImportSubmit" class="btn btn-primary" type="submit" value=" 导 入 "/>
  26. <input id="btnExport" class="btn btn-primary" type="button" value="导出"/>
  27. <a href="${ctx}/sys/user/import/template">下载模板</a>
  28. </form>
  29. </div>
  30. </body>

  其中导出涉及不到表单数据的提交,笔者为了操作简便,直接将查询的表单的action换成了导出的地址,在此并没有列出来,大家换成a标签也可。

 

然后是控制层代码:

导出:

  1. /**
  2. *导出数据
  3. *@version 2018-04-02
  4. */
  5. @RequiresPermissions("wxpt:workorder:wxptWorkorderInfo:view")
  6. @RequestMapping(value = "export", method = RequestMethod.POST)
  7. public String exportToFile(HttpServletRequest request, HttpServletResponse response,
  8. RedirectAttributes redirectAttributes, WxptWorkorderInfo wxptWorkorderInfo) {
  9. try {
  10. String fileName = DateUtils.getDate("工单yyyyMMddHHmmss") + ".xlsx";
  11. List<WxptWorkorderInfo> list = wxptWorkorderInfoService.findList(wxptWorkorderInfo);
  12. new ExportExcel("工单", WxptWorkorderInfo.class).setDataList(list).write(response, fileName).dispose();
  13. return null;
  14. } catch (Exception e) {
  15. addMessage(redirectAttributes, "导出工单失败!失败信息:" + e.getMessage());
  16. }
  17. return "redirect:" + Global.getAdminPath() + "/wxpt/workorder/wxptWorkorderInfo/?repage";
  18. }

  exportExcel:(一个工具类,直接复制粘贴即可,注释很明确)

  1. /**
  2. * Copyright © 2012-2016 <a href="https://github.com/thinkgem/smkj">smkj</a> All rights reserved.
  3. */
  4. package com.sm.modules.wxpt.utils.excel;
  5.  
  6. import java.io.FileNotFoundException;
  7. import java.io.FileOutputStream;
  8. import java.io.IOException;
  9. import java.io.OutputStream;
  10. import java.lang.reflect.Field;
  11. import java.lang.reflect.Method;
  12. import java.util.Collections;
  13. import java.util.Comparator;
  14. import java.util.Date;
  15. import java.util.HashMap;
  16. import java.util.List;
  17. import java.util.Map;
  18.  
  19. import javax.servlet.http.HttpServletResponse;
  20.  
  21. import org.apache.commons.lang3.StringUtils;
  22. import org.apache.poi.ss.usermodel.Cell;
  23. import org.apache.poi.ss.usermodel.CellStyle;
  24. import org.apache.poi.ss.usermodel.Comment;
  25. import org.apache.poi.ss.usermodel.Font;
  26. import org.apache.poi.ss.usermodel.IndexedColors;
  27. import org.apache.poi.ss.usermodel.Row;
  28. import org.apache.poi.ss.usermodel.Sheet;
  29. import org.apache.poi.ss.usermodel.Workbook;
  30. import org.apache.poi.ss.util.CellRangeAddress;
  31. import org.apache.poi.xssf.streaming.SXSSFWorkbook;
  32. import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
  33. import org.apache.poi.xssf.usermodel.XSSFRichTextString;
  34. import org.slf4j.Logger;
  35. import org.slf4j.LoggerFactory;
  36.  
  37. import com.google.common.collect.Lists;
  38. import com.sm.modules.sys.utils.DictUtils;
  39. import com.sm.utils.Encodes;
  40. import com.sm.utils.Reflections;
  41. import com.sm.utils.excel.annotation.ExcelField;
  42.  
  43. /**
  44. * 导出Excel文件(导出“XLSX”格式,支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion)
  45. * @author 冯文哲
  46. * @version 2013-04-21
  47. */
  48. public class ExportExcel {
  49. private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
  50. /**
  51. * 工作薄对象
  52. */
  53. private SXSSFWorkbook wb;
  54. /**
  55. * 工作表对象
  56. */
  57. private Sheet sheet;
  58. /**
  59. * 样式列表
  60. */
  61. private Map<String, CellStyle> styles;
  62. /**
  63. * 当前行号
  64. */
  65. private int rownum;
  66. /**
  67. * 注解列表(Object[]{ ExcelField, Field/Method })
  68. */
  69. List<Object[]> annotationList = Lists.newArrayList();
  70. /**
  71. * 构造函数
  72. * @param title 表格标题,传“空值”,表示无标题
  73. * @param cls 实体对象,通过annotation.ExportField获取标题
  74. */
  75. public ExportExcel(String title, Class<?> cls){
  76. this(title, cls, 1);
  77. }
  78. /**
  79. * 构造函数
  80. * @param title 表格标题,传“空值”,表示无标题
  81. * @param cls 实体对象,通过annotation.ExportField获取标题
  82. * @param type 导出类型(1:导出数据;2:导出模板)
  83. * @param groups 导入分组
  84. */
  85. public ExportExcel(String title, Class<?> cls, int type, int... groups){
  86. // Get annotation field
  87. Field[] fs = cls.getDeclaredFields();
  88. for (Field f : fs){
  89. ExcelField ef = f.getAnnotation(ExcelField.class);
  90. if (ef != null && (ef.type()==0 || ef.type()==type)){
  91. if (groups!=null && groups.length>0){
  92. boolean inGroup = false;
  93. for (int g : groups){
  94. if (inGroup){
  95. break;
  96. }
  97. for (int efg : ef.groups()){
  98. if (g == efg){
  99. inGroup = true;
  100. annotationList.add(new Object[]{ef, f});
  101. break;
  102. }
  103. }
  104. }
  105. }else{
  106. annotationList.add(new Object[]{ef, f});
  107. }
  108. }
  109. }
  110. // Get annotation method
  111. Method[] ms = cls.getDeclaredMethods();
  112. for (Method m : ms){
  113. ExcelField ef = m.getAnnotation(ExcelField.class);
  114. if (ef != null && (ef.type()==0 || ef.type()==type)){
  115. if (groups!=null && groups.length>0){
  116. boolean inGroup = false;
  117. for (int g : groups){
  118. if (inGroup){
  119. break;
  120. }
  121. for (int efg : ef.groups()){
  122. if (g == efg){
  123. inGroup = true;
  124. annotationList.add(new Object[]{ef, m});
  125. break;
  126. }
  127. }
  128. }
  129. }else{
  130. annotationList.add(new Object[]{ef, m});
  131. }
  132. }
  133. }
  134. // Field sorting
  135. Collections.sort(annotationList, new Comparator<Object[]>() {
  136. @Override
  137. public int compare(Object[] o1, Object[] o2) {
  138. return new Integer(((ExcelField)o1[0]).sort()).compareTo(
  139. new Integer(((ExcelField)o2[0]).sort()));
  140. };
  141. });
  142. // Initialize
  143. List<String> headerList = Lists.newArrayList();
  144. for (Object[] os : annotationList){
  145. String t = ((ExcelField)os[0]).title();
  146. // 如果是导出,则去掉注释
  147. if (type==1){
  148. String[] ss = StringUtils.split(t, "**", 2);
  149. if (ss.length==2){
  150. t = ss[0];
  151. }
  152. }
  153. headerList.add(t);
  154. }
  155. initialize(title, headerList);
  156. }
  157. /**
  158. * 构造函数
  159. * @param title 表格标题,传“空值”,表示无标题
  160. * @param headers 表头数组
  161. */
  162. public ExportExcel(String title, String[] headers) {
  163. initialize(title, Lists.newArrayList(headers));
  164. }
  165. /**
  166. * 构造函数
  167. * @param title 表格标题,传“空值”,表示无标题
  168. * @param headerList 表头列表
  169. */
  170. public ExportExcel(String title, List<String> headerList) {
  171. initialize(title, headerList);
  172. }
  173. /**
  174. * 初始化函数
  175. * @param title 表格标题,传“空值”,表示无标题
  176. * @param headerList 表头列表
  177. */
  178. private void initialize(String title, List<String> headerList) {
  179. this.wb = new SXSSFWorkbook(500);
  180. this.sheet = wb.createSheet("Export");
  181. this.styles = createStyles(wb);
  182. // Create title
  183. if (StringUtils.isNotBlank(title)){
  184. Row titleRow = sheet.createRow(rownum++);
  185. titleRow.setHeightInPoints(30);
  186. Cell titleCell = titleRow.createCell(0);
  187. titleCell.setCellStyle(styles.get("title"));
  188. titleCell.setCellValue(title);
  189. sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
  190. titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
  191. }
  192. // Create header
  193. if (headerList == null){
  194. throw new RuntimeException("headerList not null!");
  195. }
  196. Row headerRow = sheet.createRow(rownum++);
  197. headerRow.setHeightInPoints(16);
  198. for (int i = 0; i < headerList.size(); i++) {
  199. Cell cell = headerRow.createCell(i);
  200. cell.setCellStyle(styles.get("header"));
  201. String[] ss = StringUtils.split(headerList.get(i), "**", 2);
  202. if (ss.length==2){
  203. cell.setCellValue(ss[0]);
  204. Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
  205. new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
  206. comment.setString(new XSSFRichTextString(ss[1]));
  207. cell.setCellComment(comment);
  208. }else{
  209. cell.setCellValue(headerList.get(i));
  210. }
  211. sheet.autoSizeColumn(i);
  212. }
  213. for (int i = 0; i < headerList.size(); i++) {
  214. int colWidth = sheet.getColumnWidth(i)*2;
  215. sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
  216. }
  217. log.debug("Initialize success.");
  218. }
  219. /**
  220. * 创建表格样式
  221. * @param wb 工作薄对象
  222. * @return 样式列表
  223. */
  224. private Map<String, CellStyle> createStyles(Workbook wb) {
  225. Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
  226. CellStyle style = wb.createCellStyle();
  227. style.setAlignment(CellStyle.ALIGN_CENTER);
  228. style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
  229. Font titleFont = wb.createFont();
  230. titleFont.setFontName("Arial");
  231. titleFont.setFontHeightInPoints((short) 16);
  232. titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
  233. style.setFont(titleFont);
  234. styles.put("title", style);
  235.  
  236. style = wb.createCellStyle();
  237. style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
  238. style.setBorderRight(CellStyle.BORDER_THIN);
  239. style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  240. style.setBorderLeft(CellStyle.BORDER_THIN);
  241. style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  242. style.setBorderTop(CellStyle.BORDER_THIN);
  243. style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  244. style.setBorderBottom(CellStyle.BORDER_THIN);
  245. style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
  246. Font dataFont = wb.createFont();
  247. dataFont.setFontName("Arial");
  248. dataFont.setFontHeightInPoints((short) 10);
  249. style.setFont(dataFont);
  250. styles.put("data", style);
  251. style = wb.createCellStyle();
  252. style.cloneStyleFrom(styles.get("data"));
  253. style.setAlignment(CellStyle.ALIGN_LEFT);
  254. styles.put("data1", style);
  255.  
  256. style = wb.createCellStyle();
  257. style.cloneStyleFrom(styles.get("data"));
  258. style.setAlignment(CellStyle.ALIGN_CENTER);
  259. styles.put("data2", style);
  260.  
  261. style = wb.createCellStyle();
  262. style.cloneStyleFrom(styles.get("data"));
  263. style.setAlignment(CellStyle.ALIGN_RIGHT);
  264. styles.put("data3", style);
  265. style = wb.createCellStyle();
  266. style.cloneStyleFrom(styles.get("data"));
  267. // style.setWrapText(true);
  268. style.setAlignment(CellStyle.ALIGN_CENTER);
  269. style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
  270. style.setFillPattern(CellStyle.SOLID_FOREGROUND);
  271. Font headerFont = wb.createFont();
  272. headerFont.setFontName("Arial");
  273. headerFont.setFontHeightInPoints((short) 10);
  274. headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
  275. headerFont.setColor(IndexedColors.WHITE.getIndex());
  276. style.setFont(headerFont);
  277. styles.put("header", style);
  278. return styles;
  279. }
  280.  
  281. /**
  282. * 添加一行
  283. * @return 行对象
  284. */
  285. public Row addRow(){
  286. return sheet.createRow(rownum++);
  287. }
  288.  
  289. /**
  290. * 添加一个单元格
  291. * @param row 添加的行
  292. * @param column 添加列号
  293. * @param val 添加值
  294. * @return 单元格对象
  295. */
  296. public Cell addCell(Row row, int column, Object val){
  297. return this.addCell(row, column, val, 0, Class.class);
  298. }
  299. /**
  300. * 添加一个单元格
  301. * @param row 添加的行
  302. * @param column 添加列号
  303. * @param val 添加值
  304. * @param align 对齐方式(1:靠左;2:居中;3:靠右)
  305. * @return 单元格对象
  306. */
  307. public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
  308. Cell cell = row.createCell(column);
  309. String cellFormatString = "@";
  310. try {
  311. if(val == null){
  312. cell.setCellValue("");
  313. }else if(fieldType != Class.class){
  314. cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
  315. }else{
  316. if(val instanceof String) {
  317. cell.setCellValue((String) val);
  318. }else if(val instanceof Integer) {
  319. cell.setCellValue((Integer) val);
  320. cellFormatString = "0";
  321. }else if(val instanceof Long) {
  322. cell.setCellValue((Long) val);
  323. cellFormatString = "0";
  324. }else if(val instanceof Double) {
  325. cell.setCellValue((Double) val);
  326. cellFormatString = "0.00";
  327. }else if(val instanceof Float) {
  328. cell.setCellValue((Float) val);
  329. cellFormatString = "0.00";
  330. }else if(val instanceof Date) {
  331. cell.setCellValue((Date) val);
  332. cellFormatString = "yyyy-MM-dd HH:mm";
  333. }else {
  334. cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
  335. "fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
  336. }
  337. }
  338. if (val != null){
  339. CellStyle style = styles.get("data_column_"+column);
  340. if (style == null){
  341. style = wb.createCellStyle();
  342. style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));
  343. style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
  344. styles.put("data_column_" + column, style);
  345. }
  346. cell.setCellStyle(style);
  347. }
  348. } catch (Exception ex) {
  349. log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
  350. cell.setCellValue(val.toString());
  351. }
  352. return cell;
  353. }
  354.  
  355. /**
  356. * 添加数据(通过annotation.ExportField添加数据)
  357. * @return list 数据列表
  358. */
  359. public <E> ExportExcel setDataList(List<E> list){
  360. for (E e : list){
  361. int colunm = 0;
  362. Row row = this.addRow();
  363. StringBuilder sb = new StringBuilder();
  364. for (Object[] os : annotationList){
  365. ExcelField ef = (ExcelField)os[0];
  366. Object val = null;
  367. // Get entity value
  368. try{
  369. if (StringUtils.isNotBlank(ef.value())){
  370. val = Reflections.invokeGetter(e, ef.value());
  371. }else{
  372. if (os[1] instanceof Field){
  373. val = Reflections.invokeGetter(e, ((Field)os[1]).getName());
  374. }else if (os[1] instanceof Method){
  375. val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
  376. }
  377. }
  378. // If is dict, get dict label
  379. if (StringUtils.isNotBlank(ef.dictType())){
  380. val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
  381. }
  382. }catch(Exception ex) {
  383. // Failure to ignore
  384. log.info(ex.toString());
  385. val = "";
  386. }
  387. this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
  388. sb.append(val + ", ");
  389. }
  390. log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
  391. }
  392. return this;
  393. }
  394. /**
  395. * 输出数据流
  396. * @param os 输出数据流
  397. */
  398. public ExportExcel write(OutputStream os) throws IOException{
  399. wb.write(os);
  400. return this;
  401. }
  402. /**
  403. * 输出到客户端
  404. * @param fileName 输出文件名
  405. */
  406. public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
  407. response.reset();
  408. response.setContentType("application/octet-stream; charset=utf-8");
  409. response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
  410. write(response.getOutputStream());
  411. return this;
  412. }
  413. /**
  414. * 输出到文件
  415. * @param fileName 输出文件名
  416. */
  417. public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
  418. FileOutputStream os = new FileOutputStream(name);
  419. this.write(os);
  420. return this;
  421. }
  422. /**
  423. * 清理临时文件
  424. */
  425. public ExportExcel dispose(){
  426. wb.dispose();
  427. return this;
  428. }
  429. // /**
  430. // * 导出测试
  431. // */
  432. // public static void main(String[] args) throws Throwable {
  433. //
  434. // List<String> headerList = Lists.newArrayList();
  435. // for (int i = 1; i <= 10; i++) {
  436. // headerList.add("表头"+i);
  437. // }
  438. //
  439. // List<String> dataRowList = Lists.newArrayList();
  440. // for (int i = 1; i <= headerList.size(); i++) {
  441. // dataRowList.add("数据"+i);
  442. // }
  443. //
  444. // List<List<String>> dataList = Lists.newArrayList();
  445. // for (int i = 1; i <=1000000; i++) {
  446. // dataList.add(dataRowList);
  447. // }
  448. //
  449. // ExportExcel ee = new ExportExcel("表格标题", headerList);
  450. //
  451. // for (int i = 0; i < dataList.size(); i++) {
  452. // Row row = ee.addRow();
  453. // for (int j = 0; j < dataList.get(i).size(); j++) {
  454. // ee.addCell(row, j, dataList.get(i).get(j));
  455. // }
  456. // }
  457. //
  458. // ee.writeFile("target/export.xlsx");
  459. //
  460. // ee.dispose();
  461. //
  462. // log.debug("Export success.");
  463. //
  464. // }
  465.  
  466. }

  该导出是基于bean的导出,就要用到java的反射机制,关于bean的配置是在java实体类上通过注解来实现的:

  1. /**
  2. * Copyright © 2012-2016 <a href="http://www.yh3m.com">smkj</a> All rights reserved.
  3. */
  4. package com.sm.modules.wxpt.workorder.entity;
  5.  
  6. import java.util.Date;
  7.  
  8. import javax.validation.constraints.NotNull;
  9.  
  10. import org.hibernate.validator.constraints.Length;
  11. import org.hibernate.validator.constraints.NotEmpty;
  12.  
  13. import com.fasterxml.jackson.annotation.JsonFormat;
  14. import com.sm.common.persistence.DataEntity;
  15.  
  16. /**
  17. * 工单表Entity
  18. * @author 冯文哲
  19. * @version 2018-04-02
  20. */
  21. public class WxptWorkorderInfo extends DataEntity<WxptWorkorderInfo> {
  22. private static final long serialVersionUID = 1L;
  23. private String systemId; // 工单导入或录入时自动生成的系统编号
  24. private String oldSystemId; // 工单导入时原厂家自带的编号
  25. private String uname; // 用户姓名
  26. public WxptWorkorderInfo() {
  27. super();
  28. }
  29.  
  30. public WxptWorkorderInfo(String id){
  31. super(id);
  32. }
  33.  
  34.  
  35. @Length(min=0, max=64, message="工单导入时原厂家自带的编号长度必须介于 0 和 64 之间")
  36. @com.sm.utils.excel.annotation.ExcelField(title = "服务报告", align = 2, sort = 1)
  37. public String getOldSystemId() {
  38. return oldSystemId;
  39. }
  40.  
  41. public void setOldSystemId(String oldSystemId) {
  42. this.oldSystemId = oldSystemId;
  43. }
  44. @Length(min=0, max=64, message="用户姓名长度必须介于 0 和 64 之间")
  45. @NotEmpty(message="用户姓名不能为空")
  46. @com.sm.utils.excel.annotation.ExcelField(title = "客户信息", align = 2, sort = 3)
  47. public String getUname() {
  48. return uname;
  49. }
  50.  
  51. public void setUname(String uname) {
  52. this.uname = uname;
  53. }
  54. }

  该注解定义:

  1. /**
  2. * Copyright © 2012-2016 <a href="https://github.com/thinkgem/smkj">smkj</a> All rights reserved.
  3. */
  4. package com.sm.common.utils.excel.annotation;
  5.  
  6. import java.lang.annotation.ElementType;
  7. import java.lang.annotation.Retention;
  8. import java.lang.annotation.RetentionPolicy;
  9. import java.lang.annotation.Target;
  10.  
  11. /**
  12. * Excel注解定义
  13. * @author ThinkGem
  14. * @version 2013-03-10
  15. */
  16. @Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
  17. @Retention(RetentionPolicy.RUNTIME)
  18. public @interface ExcelField {
  19.  
  20. /**
  21. * 导出字段名(默认调用当前字段的“get”方法,如指定导出字段为对象,请填写“对象名.对象属性”,例:“area.name”、“office.name”)
  22. */
  23. String value() default "";
  24. /**
  25. * 导出字段标题(需要添加批注请用“**”分隔,标题**批注,仅对导出模板有效)
  26. */
  27. String title();
  28. /**
  29. * 字段类型(0:导出导入;1:仅导出;2:仅导入)
  30. */
  31. int type() default 0;
  32.  
  33. /**
  34. * 导出字段对齐方式(0:自动;1:靠左;2:居中;3:靠右)
  35. */
  36. int align() default 0;
  37. /**
  38. * 导出字段字段排序(升序)
  39. */
  40. int sort() default 0;
  41.  
  42. /**
  43. * 如果是字典类型,请设置字典的type值
  44. */
  45. String dictType() default "";
  46. /**
  47. * 反射类型
  48. */
  49. Class<?> fieldType() default Class.class;
  50. /**
  51. * 字段归属组(根据分组导出导入)
  52. */
  53. int[] groups() default {};
  54. }

  注解的title即导出的标题。

导入: 首先创造一个map做excel标题和实体类字段映射用,即下面代码的header。

然后调用:

  1. bindToModels方法将excel映射到实体类上,后续代码可自行处理业务逻辑。
  1. /**
  2. * 导入数据
  3. *
  4. * @param file
  5. * @param redirectAttributes
  6. * @return
  7. */
  8. @RequiresPermissions("wxpt:workorder:wxptWorkorderInfo:edit")
  9. @RequestMapping(value = "import", method = RequestMethod.POST)
  10. public String importFile(MultipartFile file, RedirectAttributes redirectAttributes) {
  11. Map<String, String> header = new HashMap<String, String>();
  12. header.put("服务报告", "oldSystemId");
  13. header.put("服务类别", "serviceType");
  14. header.put("客户信息", "uname");
  15. header.put("市", "areaCode");
  16. header.put("地址", "uaddress");
  17. header.put("产品信息", "brand");
  18. header.put("附加保修文档号", "fujiabaoxiu");
  19. header.put("维修建议", "weixiujianyi");
  20. header.put("配件建议", "peijianjianyi");
  21. header.put("申请备注", "notesAndInstructions");
  22. header.put("申请时间", "serviceTime");
  23. header.put("技术文档", "jishuwendang");
  24. try {
  25. int successNum = 0;
  26. int failureNum = 0;
  27. StringBuilder failureMsg = new StringBuilder();
  28. ImBeanExcel export = new ImBeanExcel(header);
  29. export.init(file);
  30. //List<WxptWorkorderInfo> list = ei.getDataList(WxptWorkorderInfo.class);
  31. List<WxptWorkorderInfo> list = export.bindToModels(WxptWorkorderInfo.class, true);
  32. if (export.hasError())
  33. {
  34. System.out.println(export.getError().toString());
  35. }
  36. for (WxptWorkorderInfo workorder : list) {
  37. String indent = randomIndentnum.getIndent();
  38. workorder.setSystemId(indent);
  39. for (int j = 0; j < workorder.getUname().length(); j++) {//处理导入数据
  40. //拆分用户和电话
  41. if(workorder.getUname().charAt(j)>=48 && workorder.getUname().charAt(j)<=57){
  42. String uname = workorder.getUname().substring(0,j);
  43. String uphone = workorder.getUname().substring(j,workorder.getUname().length());
  44. workorder.setUname(uname);
  45. workorder.setUphone(uphone.trim());
  46. }
  47. }
  48. //拆分品牌,产品类别,服务,型号
  49. String [] bra = workorder.getBrand().split("\\s+");
  50. List<WxptBrandsDict> brandsDict = wxptBrandsDictService.findList(new WxptBrandsDict());
  51. List<WxptProductDict> productDict = wxptProductDictService.findList(new WxptProductDict());
  52. List<WxptServiceDict> serviceDict = wxptServiceDictService.findList(new WxptServiceDict());
  53. workorder = ImportDivision.divisionBrand(bra,workorder,brandsDict,productDict,serviceDict);
  54. }
  55. for (WxptWorkorderInfo workorder : list) {
  56. try {
  57. if ("true".equals(checkOldSystemId("", workorder.getOldSystemId()))) {
  58. BeanValidators.validateWithException(validator, workorder);
  59. wxptWorkorderInfoService.save(workorder);
  60. successNum++;
  61. } else {
  62. failureMsg.append("<br/>工单编号 " + workorder.getOldSystemId() + " 已存在; ");
  63. failureNum++;
  64. }
  65. } catch (ConstraintViolationException ex) {
  66. failureMsg.append("<br/>工单编号 " + workorder.getOldSystemId() + " 导入失败:");
  67. List<String> messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": ");
  68. for (String message : messageList) {
  69. failureMsg.append(message + "; ");
  70. failureNum++;
  71. }
  72. } catch (Exception ex) {
  73. failureMsg.append("<br/>工单编号 " + workorder.getOldSystemId() + " 导入失败:" + ex.getMessage());
  74. }
  75. }
  76. if (failureNum > 0) {
  77. failureMsg.insert(0, ",失败 " + failureNum + " 条工单,导入信息如下:");
  78. }
  79. addMessage(redirectAttributes, "已成功导入 " + successNum + " 条工单" + failureMsg);
  80. } catch (Exception e) {
  81. addMessage(redirectAttributes, "导入工单失败!失败信息:" + e.getMessage());
  82. }
  83. return "redirect:" + adminPath + "/wxpt/workorder/wxptWorkorderInfo?repage";
  84. }

  

ImBeanExcel.java工具类:

  1. package com.sm.modules.wxpt.utils.excel;
  2.  
  3. import java.io.FileInputStream;
  4. import java.io.FileNotFoundException;
  5. import java.io.IOException;
  6. import java.lang.reflect.Method;
  7. import java.text.ParseException;
  8. import java.text.SimpleDateFormat;
  9. import java.util.ArrayList;
  10. import java.util.Date;
  11. import java.util.HashMap;
  12. import java.util.List;
  13. import java.util.Map;
  14.  
  15. import org.apache.log4j.Logger;
  16. import org.apache.poi.ss.usermodel.DateUtil;
  17. import org.apache.poi.xssf.usermodel.XSSFCell;
  18. import org.apache.poi.xssf.usermodel.XSSFDataFormat;
  19. import org.apache.poi.xssf.usermodel.XSSFRow;
  20. import org.apache.poi.xssf.usermodel.XSSFSheet;
  21. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  22. import org.springframework.web.multipart.MultipartFile;
  23.  
  24. /**
  25. * 将excel数据装换成数据模型
  26. *
  27. * @author
  28. *
  29. */
  30. public class ImBeanExcel {
  31. private static final int HEADER = 0;
  32.  
  33. private static final int START = 1;
  34.  
  35. private Logger log = Logger.getLogger(this.getClass());
  36.  
  37. private XSSFWorkbook book;
  38. /**
  39. * key:excel对应标题 ,value:对象属性
  40. */
  41. private Map<String, String> associations;
  42. /**
  43. * 装换失败的数据信息,记录行数
  44. */
  45. private StringBuffer error = new StringBuffer(0);
  46.  
  47. private Map<Integer, String> header;
  48. /**
  49. * 默认的日期格式
  50. */
  51. private String date_format = "yyyy-MM-dd";
  52.  
  53. private SimpleDateFormat format;
  54.  
  55. /**
  56. * 初始化工作簿
  57. *
  58. * @param file
  59. */
  60. public void init(MultipartFile file) {
  61. FileInputStream in;
  62. try {
  63. in = (FileInputStream) file.getInputStream();
  64. book = new XSSFWorkbook(in);
  65.  
  66. } catch (FileNotFoundException e) {
  67. e.printStackTrace();
  68. } catch (IOException e) {
  69. e.printStackTrace();
  70. }
  71.  
  72. }
  73.  
  74. public ImBeanExcel(Map<String, String> associations) {
  75. this.associations = associations;
  76. format = new SimpleDateFormat(date_format);
  77.  
  78. }
  79.  
  80. public ImBeanExcel(Map<String, String> associations, String date_format) {
  81. this.associations = associations;
  82. this.date_format = date_format;
  83. format = new SimpleDateFormat(date_format);
  84. }
  85.  
  86. /**
  87. *
  88. * @return true 存在错误,false 不存在错误
  89. */
  90. public boolean hasError() {
  91. return error.capacity() > 0;
  92. }
  93.  
  94. public StringBuffer getError() {
  95. return error;
  96. }
  97.  
  98. /**
  99. * 获取第一行标题栏数据
  100. *
  101. * @param sheet
  102. * @return map key:标题栏列下标(0开始) value 标题栏值
  103. */
  104. private void loadHeader(XSSFSheet sheet) {
  105. this.header = new HashMap<Integer, String>();
  106. XSSFRow row = sheet.getRow(HEADER);
  107. int columns = row.getLastCellNum();
  108. for (int i = 0; i < columns; i++) {
  109. log.debug("加载标题栏:" + row.getCell(i).getStringCellValue());
  110. String value = row.getCell(i).getStringCellValue();
  111. if (null == value) {
  112. throw new RuntimeException("标题栏不能为空!");
  113. }
  114. header.put(i, value);
  115. }
  116. log.debug("<<<<<<<<<<<<标题栏加载完毕>>>>>>>>>>>");
  117. }
  118.  
  119. /**
  120. *
  121. * @param clazz
  122. * @param required
  123. * 是否每个属性都是必须的
  124. * @return
  125. */
  126. public <T> List<T> bindToModels(Class clazz, boolean required) throws Exception {
  127. // 获取第一页
  128. XSSFSheet sheet = this.book.getSheetAt(0);
  129. int rowNum = sheet.getLastRowNum();// 获取行数
  130.  
  131. if (rowNum < 1) {
  132. return new ArrayList<T>();
  133. }
  134. // 加载标题栏数据
  135. this.loadHeader(sheet);
  136. List<T> result = new ArrayList<T>();
  137.  
  138. for (int i = START; i < rowNum; i++) {
  139. XSSFRow row = sheet.getRow(i);
  140. int cellNum = row.getLastCellNum();
  141. T instance = (T) clazz.newInstance();
  142.  
  143. for (int columns = 0; columns < cellNum; columns++) {
  144. XSSFCell cell = row.getCell(columns);
  145. // 判断单元格的数据类型
  146. String value = loadCellType(cell);
  147.  
  148. // 获取单元格的值
  149. if (null == value) {
  150. // 如果为必填的则将错误信息记录
  151. if (required) {
  152. this.error.append("第" + (i + 1) + "行," + header.get(columns) + "字段,数据为空,跳过!").append("\n");
  153. log.debug("第" + (i + 1) + "行," + header.get(columns) + "字段,数据为空,跳过!");
  154. continue;
  155. }
  156. }else if("".equals(value.trim())){
  157. // 如果为必填的则将错误信息记录
  158. if (required) {
  159. this.error.append("第" + (i + 1) + "行," + header.get(columns) + "字段,数据为空,跳过!").append("\n");
  160. log.debug("第" + (i + 1) + "行," + header.get(columns) + "字段,数据为空,跳过!");
  161. continue;
  162. }
  163. } else {
  164. String key = header.get(columns);
  165. // 加载实际值
  166. value = value.replaceAll( "\\\\","");
  167. this.loadValue(clazz, instance, this.associations.get(key), value);
  168. }
  169. }
  170. result.add(instance);
  171. }
  172. log.debug("<<<<<装换完成" + (this.hasError() ? "有错误信息" : "") + ",共有对象:" + result.size() + "个" + ">>>>>>");
  173. return result;
  174. }
  175.  
  176. /**
  177. * 将单元格数据转换成string类型
  178. *
  179. * @param cellType
  180. * @param cell
  181. * @return
  182. */
  183. private String loadCellType(XSSFCell cell) {
  184. String value = null;
  185. switch (cell.getCellType()) {
  186. case XSSFCell.CELL_TYPE_BOOLEAN:
  187. value = String.valueOf(cell.getBooleanCellValue());
  188. break;
  189. case XSSFCell.CELL_TYPE_NUMERIC:
  190. // 判断当前的cell是否为Date
  191. if (DateUtil.isCellDateFormatted(cell)) {
  192. value = this.formateDate(cell.getDateCellValue());
  193. } else {
  194. value = String.valueOf((long) cell.getNumericCellValue());
  195. }
  196. break;
  197. case XSSFCell.CELL_TYPE_STRING:
  198. value = cell.getStringCellValue();
  199. break;
  200. case XSSFCell.CELL_TYPE_FORMULA:
  201. log.debug("不支持函数!");
  202. break;
  203. }
  204.  
  205. return value;
  206. }
  207.  
  208. /**
  209. * 注入属性值
  210. *
  211. * @param instance
  212. * @param pro
  213. * 属性对象
  214. * @param value
  215. * 属性值
  216. */
  217. @SuppressWarnings("unchecked")
  218. private <T> void loadValue(Class clazz, T instance, String pro, String value)
  219. throws SecurityException, NoSuchMethodException, Exception {
  220. String getMethod = this.initGetMethod(pro);
  221. Class type = clazz.getDeclaredMethod(getMethod, null).getReturnType();
  222.  
  223. Method method = clazz.getMethod(this.initSetMethod(pro), type);
  224.  
  225. if (type == String.class) {
  226. method.invoke(instance, value);
  227. } else if (type == int.class || type == Integer.class) {
  228. method.invoke(instance, Integer.parseInt(value));
  229.  
  230. } else if (type == long.class || type == Long.class) {
  231. method.invoke(instance, Long.parseLong(value));
  232.  
  233. } else if (type == float.class || type == Float.class) {
  234. method.invoke(instance, Float.parseFloat(value));
  235.  
  236. } else if (type == double.class || type == Double.class) {
  237. method.invoke(instance, Double.parseDouble(value));
  238.  
  239. } else if (type == Date.class) {
  240. method.invoke(instance, this.parseDate(value));
  241. }
  242.  
  243. }
  244.  
  245. private Date parseDate(String value) throws ParseException {
  246. value = value.replaceAll("/", "-");
  247. return format.parse(value);
  248. }
  249.  
  250. private String formateDate(Date date) {
  251. return format.format(date);
  252. }
  253.  
  254. public String initSetMethod(String field) {
  255. return "set" + field.substring(0, 1).toUpperCase() + field.substring(1);
  256. }
  257.  
  258. public String initGetMethod(String field) {
  259. return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);
  260. }
  261.  
  262. public String getDate_format() {
  263. return date_format;
  264. }
  265.  
  266. public void setDate_format(String date_format) {
  267. this.date_format = date_format;
  268. }
  269.  
  270. }

  至此一套完整流程的导入导出基本完成,可复用。

版权声明:本文为fengwenzhee原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://www.cnblogs.com/fengwenzhee/p/8732384.html