设为首页收藏本站
开启辅助访问

在路上

 找回密码
 立即注册
在路上 › 站点首页 ›学习 › 查看内容

java实现文件导入导出

2016-7-29 15:41| 发布者: zhangjf| 查看: 596| 评论: 0

摘要: 文件导入导出必须代码 ExportExcel.java /** * Copyright ? 2012-2014 a href=https://github.com/thinkgem/jeesiteJeeSite/a All rights reserved. */package com.thinkgem.jeesite.common.utils.excel; import jav ...

文件导入导出必须代码

ExportExcel.java

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

导出测试

  2. public static void main(String[] args) throws Throwable {
  4. List<String> headerList = Lists.newArrayList();
  5. for (int i = 1; i <= 10; i++) {
  6. headerList.add("表头"+i);
  7. }
  9. List<String> dataRowList = Lists.newArrayList();
  10. for (int i = 1; i <= headerList.size(); i++) {
  11. dataRowList.add("数据"+i);
  12. }
  14. List<List<String>> dataList = Lists.newArrayList();
  15. for (int i = 1; i <=1000000; i++) {
  16. dataList.add(dataRowList);
  17. }
  19. ExportExcel ee = new ExportExcel("表格标题", headerList);
  21. for (int i = 0; i < dataList.size(); i++) {
  22. Row row = ee.addRow();
  23. for (int j = 0; j < dataList.get(i).size(); j++) {
  24. ee.addCell(row, j, dataList.get(i).get(j));
  25. }
  26. }
  28. ee.writeFile("target/export.xlsx");
  30. ee.dispose();
  32. log.debug("Export success.");
  34. }
复制代码

ImportExcel.java

  2. /**
  3. * Copyright ? 2012-2014 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
  4. */
  5. package com.thinkgem.jeesite.common.utils.excel;
  7. import java.io.File;
  8. import java.io.FileInputStream;
  9. import java.io.IOException;
  10. import java.io.InputStream;
  11. import java.lang.reflect.Field;
  12. import java.lang.reflect.Method;
  13. import java.util.Collections;
  14. import java.util.Comparator;
  15. import java.util.Date;
  16. import java.util.List;
  18. import org.apache.commons.lang3.StringUtils;
  19. import org.apache.poi.hssf.usermodel.HSSFWorkbook;
  20. import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
  21. import org.apache.poi.ss.usermodel.Cell;
  22. import org.apache.poi.ss.usermodel.DateUtil;
  23. import org.apache.poi.ss.usermodel.Row;
  24. import org.apache.poi.ss.usermodel.Sheet;
  25. import org.apache.poi.ss.usermodel.Workbook;
  26. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
  27. import org.slf4j.Logger;
  28. import org.slf4j.LoggerFactory;
  29. import org.springframework.web.multipart.MultipartFile;
  31. import com.google.common.collect.Lists;
  32. import com.thinkgem.jeesite.common.utils.Reflections;
  33. import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
  34. import com.thinkgem.jeesite.modules.sys.utils.DictUtils;
  36. /**
  37. * 导入Excel文件（支持“XLS”和“XLSX”格式）
  38. * @author ThinkGem
  39. * @version 2013-03-10
  40. */
  41. public class ImportExcel {
  43. private static Logger log = LoggerFactory.getLogger(ImportExcel.class);
  45. /**
  46. * 工作薄对象
  47. */
  48. private Workbook wb;
  50. /**
  51. * 工作表对象
  52. */
  53. private Sheet sheet;
  55. /**
  56. * 标题行号
  57. */
  58. private int headerNum;
  60. /**
  61. * 构造函数
  62. * @param path 导入文件，读取第一个工作表
  63. * @param headerNum 标题行号，数据行号=标题行号+1
  64. * @throws InvalidFormatException
  65. * @throws IOException
  66. */
  67. public ImportExcel(String fileName, int headerNum)
  68. throws InvalidFormatException, IOException {
  69. this(new File(fileName), headerNum);
  70. }
  72. /**
  73. * 构造函数
  74. * @param path 导入文件对象，读取第一个工作表
  75. * @param headerNum 标题行号，数据行号=标题行号+1
  76. * @throws InvalidFormatException
  77. * @throws IOException
  78. */
  79. public ImportExcel(File file, int headerNum)
  80. throws InvalidFormatException, IOException {
  81. this(file, headerNum, 0);
  82. }
  84. /**
  85. * 构造函数
  86. * @param path 导入文件
  87. * @param headerNum 标题行号，数据行号=标题行号+1
  88. * @param sheetIndex 工作表编号
  89. * @throws InvalidFormatException
  90. * @throws IOException
  91. */
  92. public ImportExcel(String fileName, int headerNum, int sheetIndex)
  93. throws InvalidFormatException, IOException {
  94. this(new File(fileName), headerNum, sheetIndex);
  95. }
  97. /**
  98. * 构造函数
  99. * @param path 导入文件对象
  100. * @param headerNum 标题行号，数据行号=标题行号+1
  101. * @param sheetIndex 工作表编号
  102. * @throws InvalidFormatException
  103. * @throws IOException
  104. */
  105. public ImportExcel(File file, int headerNum, int sheetIndex)
  106. throws InvalidFormatException, IOException {
  107. this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
  108. }
  110. /**
  111. * 构造函数
  112. * @param file 导入文件对象
  113. * @param headerNum 标题行号，数据行号=标题行号+1
  114. * @param sheetIndex 工作表编号
  115. * @throws InvalidFormatException
  116. * @throws IOException
  117. */
  118. public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
  119. throws InvalidFormatException, IOException {
  120. this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
  121. }
  123. /**
  124. * 构造函数
  125. * @param path 导入文件对象
  126. * @param headerNum 标题行号，数据行号=标题行号+1
  127. * @param sheetIndex 工作表编号
  128. * @throws InvalidFormatException
  129. * @throws IOException
  130. */
  131. public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
  132. throws InvalidFormatException, IOException {
  133. if (StringUtils.isBlank(fileName)){
  134. throw new RuntimeException("导入文档为空!");
  135. }else if(fileName.toLowerCase().endsWith("xls")){
  136. this.wb = new HSSFWorkbook(is);
  137. }else if(fileName.toLowerCase().endsWith("xlsx")){
  138. this.wb = new XSSFWorkbook(is);
  139. }else{
  140. throw new RuntimeException("文档格式不正确!");
  141. }
  142. if (this.wb.getNumberOfSheets()<sheetIndex){
  143. throw new RuntimeException("文档中没有工作表!");
  144. }
  145. this.sheet = this.wb.getSheetAt(sheetIndex);
  146. this.headerNum = headerNum;
  147. log.debug("Initialize success.");
  148. }
  150. /**
  151. * 获取行对象
  152. * @param rownum
  153. * @return
  154. */
  155. public Row getRow(int rownum){
  156. return this.sheet.getRow(rownum);
  157. }
  159. /**
  160. * 获取数据行号
  161. * @return
  162. */
  163. public int getDataRowNum(){
  164. return headerNum+1;
  165. }
  167. /**
  168. * 获取最后一个数据行号
  169. * @return
  170. */
  171. public int getLastDataRowNum(){
  172. return this.sheet.getLastRowNum()+headerNum;
  173. }
  175. /**
  176. * 获取最后一个列号
  177. * @return
  178. */
  179. public int getLastCellNum(){
  180. return this.getRow(headerNum).getLastCellNum();
  181. }
  183. /**
  184. * 获取单元格值
  185. * @param row 获取的行
  186. * @param column 获取单元格列号
  187. * @return 单元格值
  188. */
  189. public Object getCellValue(Row row, int column){
  190. Object val = "";
  191. try{
  192. Cell cell = row.getCell(column);
  193. if (cell != null){
  194. if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
  195. val = cell.getNumericCellValue();
  196. }else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
  197. val = cell.getStringCellValue();
  198. }else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
  199. val = cell.getCellFormula();
  200. }else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
  201. val = cell.getBooleanCellValue();
  202. }else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
  203. val = cell.getErrorCellValue();
  204. }
  205. }
  206. }catch (Exception e) {
  207. return val;
  208. }
  209. return val;
  210. }
  212. /**
  213. * 获取导入数据列表
  214. * @param cls 导入对象类型
  215. * @param groups 导入分组
  216. */
  217. public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
  218. List<Object[]> annotationList = Lists.newArrayList();
  219. // Get annotation field
  220. Field[] fs = cls.getDeclaredFields();
  221. for (Field f : fs){
  222. ExcelField ef = f.getAnnotation(ExcelField.class);
  223. if (ef != null && (ef.type()==0 || ef.type()==2)){
  224. if (groups!=null && groups.length>0){
  225. boolean inGroup = false;
  226. for (int g : groups){
  227. if (inGroup){
  228. break;
  229. }
  230. for (int efg : ef.groups()){
  231. if (g == efg){
  232. inGroup = true;
  233. annotationList.add(new Object[]{ef, f});
  234. break;
  235. }
  236. }
  237. }
  238. }else{
  239. annotationList.add(new Object[]{ef, f});
  240. }
  241. }
  242. }
  243. // Get annotation method
  244. Method[] ms = cls.getDeclaredMethods();
  245. for (Method m : ms){
  246. ExcelField ef = m.getAnnotation(ExcelField.class);
  247. if (ef != null && (ef.type()==0 || ef.type()==2)){
  248. if (groups!=null && groups.length>0){
  249. boolean inGroup = false;
  250. for (int g : groups){
  251. if (inGroup){
  252. break;
  253. }
  254. for (int efg : ef.groups()){
  255. if (g == efg){
  256. inGroup = true;
  257. annotationList.add(new Object[]{ef, m});
  258. break;
  259. }
  260. }
  261. }
  262. }else{
  263. annotationList.add(new Object[]{ef, m});
  264. }
  265. }
  266. }
  267. // Field sorting
  268. Collections.sort(annotationList, new Comparator<Object[]>() {
  269. public int compare(Object[] o1, Object[] o2) {
  270. return new Integer(((ExcelField)o1[0]).sort()).compareTo(
  271. new Integer(((ExcelField)o2[0]).sort()));
  272. };
  273. });
  274. //log.debug("Import column count:"+annotationList.size());
  275. // Get excel data
  276. List<E> dataList = Lists.newArrayList();
  277. for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
  278. E e = (E)cls.newInstance();
  279. int column = 0;
  280. Row row = this.getRow(i);
  281. StringBuilder sb = new StringBuilder();
  282. for (Object[] os : annotationList){
  283. Object val = this.getCellValue(row, column++);
  284. if (val != null){
  285. ExcelField ef = (ExcelField)os[0];
  286. // If is dict type, get dict value
  287. if (StringUtils.isNotBlank(ef.dictType())){
  288. val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
  289. //log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
  290. }
  291. // Get param type and type cast
  292. Class<?> valType = Class.class;
  293. if (os[1] instanceof Field){
  294. valType = ((Field)os[1]).getType();
  295. }else if (os[1] instanceof Method){
  296. Method method = ((Method)os[1]);
  297. if ("get".equals(method.getName().substring(0, 3))){
  298. valType = method.getReturnType();
  299. }else if("set".equals(method.getName().substring(0, 3))){
  300. valType = ((Method)os[1]).getParameterTypes()[0];
  301. }
  302. }
  303. //log.debug("Import value type: ["+i+","+column+"] " + valType);
  304. try {
  305. if (valType == String.class){
  306. String s = String.valueOf(val.toString());
  307. if(StringUtils.endsWith(s, ".0")){
  308. val = StringUtils.substringBefore(s, ".0");
  309. }else{
  310. val = String.valueOf(val.toString());
  311. }
  312. }else if (valType == Integer.class){
  313. val = Double.valueOf(val.toString()).intValue();
  314. }else if (valType == Long.class){
  315. val = Double.valueOf(val.toString()).longValue();
  316. }else if (valType == Double.class){
  317. val = Double.valueOf(val.toString());
  318. }else if (valType == Float.class){
  319. val = Float.valueOf(val.toString());
  320. }else if (valType == Date.class){
  321. val = DateUtil.getJavaDate((Double)val);
  322. }else{
  323. if (ef.fieldType() != Class.class){
  324. val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
  325. }else{
  326. val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
  327. "fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());
  328. }
  329. }
  330. } catch (Exception ex) {
  331. log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
  332. val = null;
  333. }
  334. // set entity value
  335. if (os[1] instanceof Field){
  336. Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);
  337. }else if (os[1] instanceof Method){
  338. String mthodName = ((Method)os[1]).getName();
  339. if ("get".equals(mthodName.substring(0, 3))){
  340. mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
  341. }
  342. Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});
  343. }
  344. }
  345. sb.append(val+", ");
  346. }
  347. dataList.add(e);
  348. log.debug("Read success: ["+i+"] "+sb.toString());
  349. }
  350. return dataList;
  351. }
  353. }
复制代码

导入测试

  2. public static void main(String[] args) throws Throwable {
  4. ImportExcel ei = new ImportExcel("target/export.xlsx", 1);
  6. for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {
  7. Row row = ei.getRow(i);
  8. for (int j = 0; j < ei.getLastCellNum(); j++) {
  9. Object val = ei.getCellValue(row, j);
  10. System.out.print(val+", ");
  11. }
  12. System.out.print("n");
  13. }
  15. }
复制代码

收藏 邀请
上一篇：C#使用MySQLConnectorNet和MySQLDriverCS操作MySQL的方法下一篇：详解Java中使用ImageIO类对图片进行压缩的方法

最新评论

相关分类

小黑屋|在路上 ( 蜀ICP备15035742号-1 )  

;

GMT+8, 2025-5-6 13:09

Copyright 2015-2025 djqfx

返回顶部