| commit | author | age | ||
| a18bfa | 1 | package com.changhong.epc.rely.api.tool; |
| Z | 2 | |
| 3 | import com.github.andyczy.java.excel.LocalExcelUtils; | |
| 4 | import com.iemsoft.framework.cloud.core.tools.ObjectUtil; | |
| 5 | import org.apache.poi.hssf.usermodel.HSSFWorkbook; | |
| 6 | import org.apache.poi.ss.usermodel.*; | |
| 7 | import org.apache.poi.xssf.streaming.SXSSFWorkbook; | |
| 8 | import org.apache.poi.xssf.usermodel.XSSFWorkbook; | |
| 9 | ||
| 10 | import java.io.*; | |
| 11 | import java.text.SimpleDateFormat; | |
| 12 | import java.util.*; | |
| 13 | import java.util.function.Consumer; | |
| 14 | ||
| 15 | public class ExcelTool { | |
| 16 | ||
| 17 | public static final String OFFICE_EXCEL_2003_POSTFIX = "xls"; | |
| 18 | public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx"; | |
| 19 | ||
| 20 | public static int importExcel(InputStream inputStream, String fileName, Consumer<Map<String, String>> consumer) throws IOException { | |
| 21 | Workbook workbook = getWorkBook(inputStream, fileName); | |
| 22 | if (workbook == null) { | |
| 23 | return 0; | |
| 24 | } | |
| 25 | Sheet sheet = workbook.getSheetAt(0); | |
| 26 | if (sheet == null) { | |
| 27 | return 0; | |
| 28 | } | |
| 29 | Iterator<Row> rowIterator = sheet.rowIterator(); | |
| 30 | // 略过汉字标题 | |
| 31 | if(rowIterator.hasNext()) rowIterator.next(); | |
| 32 | // 记录code标题 | |
| 33 | String[] columns, values; | |
| 34 | if(rowIterator.hasNext()) { | |
| 35 | columns = getCallVals(rowIterator.next()); | |
| 36 | }else{ | |
| 37 | return 0; | |
| 38 | } | |
| 39 | Map<String, String> res; | |
| 40 | int line = 0; | |
| 41 | while (rowIterator.hasNext()){ | |
| 42 | values = getCallVals(rowIterator.next()); | |
| 43 | res = new HashMap<>(); | |
| 44 | for (int i = 0, j = Math.min(columns.length, values.length); i < j; i++) { | |
| 45 | res.put(columns[i], values[i]); | |
| 46 | } | |
| 47 | consumer.accept(res); | |
| 48 | line++; | |
| 49 | } | |
| 50 | return line; | |
| 51 | } | |
| 52 | ||
| 53 | public static String[] getCallVals(Row row){ | |
| 54 | Iterator<Cell> cellIterator = row.cellIterator(); | |
| 55 | List<String> res = new LinkedList<>(); | |
| 56 | while (cellIterator.hasNext()){ | |
| 57 | Cell cell = cellIterator.next(); | |
| 58 | res.add(convertCellToString(cell)); | |
| 59 | } | |
| 60 | return res.stream().toArray(String[]::new); | |
| 61 | } | |
| 62 | ||
| 63 | public static String convertCellToString(Cell cell){ | |
| 64 | //如果为null会抛出异常，应当返回空字符串 | |
| 65 | if (cell == null) | |
| 66 | return ""; | |
| 67 | ||
| 68 | //POI对单元格日期处理很弱，没有针对的类型，日期类型取出来的也是一个double值，所以同样作为数值类型 | |
| 69 | //解决日期2006/11/02格式读入后出错的问题，POI读取后变成“02-十一月-2006”格式 | |
| 70 | if(cell.toString().contains("-") && checkDate(cell.toString())){ | |
| 71 | String ans; | |
| 72 | try { | |
| 73 | ans = new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue()); | |
| 74 | } catch (Exception e) { | |
| 75 | ans = cell.toString(); | |
| 76 | } | |
| 77 | return ans; | |
| 78 | } | |
| 79 | ||
| 80 | cell.setCellType(CellType.STRING); | |
| 81 | return cell.getStringCellValue(); | |
| 82 | } | |
| 83 | ||
| 84 | /** | |
| 85 | * 判断是否是“02-十一月-2006”格式的日期类型 | |
| 86 | */ | |
| 87 | private static boolean checkDate(String str){ | |
| 88 | String[] dataArr =str.split("-"); | |
| 89 | try { | |
| 90 | if(dataArr.length == 3){ | |
| 91 | int x = Integer.parseInt(dataArr[0]); | |
| 92 | String y = dataArr[1]; | |
| 93 | int z = Integer.parseInt(dataArr[2]); | |
| 94 | if(x>0 && x<32 && z>0 && z< 10000 && y.endsWith("月")){ | |
| 95 | return true; | |
| 96 | } | |
| 97 | } | |
| 98 | } catch (Exception e) { | |
| 99 | return false; | |
| 100 | } | |
| 101 | return false; | |
| 102 | } | |
| 103 | ||
| 104 | private static Workbook getWorkBook(InputStream inputStream, String fileName) throws IOException { | |
| 105 | if(fileName.endsWith(OFFICE_EXCEL_2003_POSTFIX)){ | |
| 106 | return new HSSFWorkbook(inputStream); | |
| 107 | }else if(fileName.endsWith(OFFICE_EXCEL_2010_POSTFIX)){ | |
| 108 | return new XSSFWorkbook(inputStream); | |
| 109 | }else{ | |
| 110 | return null; | |
| 111 | } | |
| 112 | } | |
| 113 | ||
| 114 | public static void outExcel(OutputStream os, Map<String, String> title, List<Map<String, String>> data) throws IOException { | |
| 115 | SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook()); | |
| 116 | Sheet sheet = workbook.createSheet(); | |
| 117 | if(ObjectUtil.notEmpty(title)){ | |
| 118 | int i = 0; | |
| 119 | Row titleName = sheet.createRow(0); | |
| 120 | Row titleCode = sheet.createRow(1); | |
| 121 | for(Map.Entry<String, String> entry : title.entrySet()){ | |
| 122 | Cell cellName = titleName.createCell(i); | |
| 123 | Cell cellCode = titleCode.createCell(i); | |
| 124 | cellName.setCellValue(entry.getValue()); | |
| 125 | cellCode.setCellValue(entry.getKey()); | |
| 126 | i++; | |
| 127 | } | |
| 128 | } | |
| 129 | if(ObjectUtil.notEmpty(data)){ | |
| 130 | ||
| 131 | } | |
| 132 | workbook.write(os); | |
| 133 | } | |
| 134 | ||
| 135 | ||
| 136 | } | |
