| 提交 | 用户 | age | ||
| 5c5945 | 1 | package com.hx.util; |
| E | 2 | |
| 3 | import java.io.File; | |
| 4 | import java.io.FileInputStream; | |
| 5 | import java.io.FileOutputStream; | |
| 6 | import java.text.DateFormat; | |
| 7 | import java.text.SimpleDateFormat; | |
| 8 | import java.util.ArrayList; | |
| 9 | import java.util.Date; | |
| 10 | ||
| 11 | import java.util.List; | |
| 12 | import java.util.Map; | |
| 13 | ||
| 14 | import com.hx.exception.TipsException; | |
| 15 | import org.apache.poi.hssf.usermodel.*; | |
| 16 | import org.apache.poi.ss.usermodel.Cell; | |
| 17 | import org.springframework.web.multipart.MultipartFile; | |
| 18 | ||
| 19 | ||
| 20 | /** | |
| 21 | * | |
| 22 | * @author hjr | |
| 23 | */ | |
| 24 | public final class ExcelUtil { | |
| 25 | ||
| 26 | /** | |
| 27 | * @param excelName | |
| 28 | * 文件名称 | |
| 29 | * @param outPath | |
| 30 | * 保存路径 | |
| 31 | * @param headList | |
| 32 | * Excel文件Head标题集合 | |
| 33 | * @param fieldList | |
| 34 | * Excel文件Field标题集合 根据field来寻找位置填充表格 | |
| 35 | * @param dataList | |
| 36 | * Excel文件数据内容部分 | |
| 37 | * @throws Exception | |
| 38 | */ | |
| 39 | public static String createExcel(String outPath, String excelName, | |
| 40 | String[] headList, String[] fieldList, | |
| 41 | List<Map<String, Object>> dataList) throws Exception { | |
| 42 | ||
| 43 | String filePath = null; | |
| 44 | // 创建新的Excel 工作簿 | |
| 45 | HSSFWorkbook workbook = new HSSFWorkbook(); | |
| 46 | ||
| 47 | // 在Excel工作簿中建一工作表，其名为缺省值 | |
| 48 | // 如要新建一名为"效益指标"的工作表，其语句为： | |
| 49 | // HSSFSheet sheet = workbook.createSheet("效益指标"); | |
| 50 | HSSFSheet sheet = workbook.createSheet(); | |
| 51 | // 在索引0的位置创建行（最顶端的行） | |
| 52 | HSSFRow row = sheet.createRow(0); | |
| 53 | // =============================================================== | |
| 54 | for (int i = 0; i < headList.length; i++) { | |
| 55 | ||
| 56 | // 在索引0的位置创建单元格（左上端） | |
| 57 | HSSFCell cell = row.createCell(i); | |
| 58 | // 定义单元格为字符串类型 | |
| 59 | cell.setCellType(HSSFCell.CELL_TYPE_STRING); | |
| 60 | // 在单元格中输入一些内容 | |
| 61 | cell.setCellValue(headList[i]); | |
| 62 | } | |
| 63 | // =============================================================== | |
| 64 | if (dataList != null) { | |
| 65 | for (int n = 0; n < dataList.size(); n++) { | |
| 66 | // 在索引1的位置创建行 | |
| 67 | HSSFRow row_value = sheet.createRow(n + 1); | |
| 68 | Map<String, Object> dataMap = dataList.get(n); | |
| 69 | // =============================================================== | |
| 70 | for (int i = 0; i < fieldList.length; i++) { | |
| 71 | // 在索引0的位置创建单元格（左上端） | |
| 72 | HSSFCell cell = row_value.createCell(i); | |
| 73 | // 定义单元格为字符串类型 | |
| 74 | cell.setCellType(HSSFCell.CELL_TYPE_STRING); | |
| 75 | // 在单元格中输入一些内容 | |
| 76 | cell.setCellValue(objToString(dataMap.get(fieldList[i]))); | |
| 77 | } | |
| 78 | // =============================================================== | |
| 79 | } | |
| 80 | } | |
| 81 | ||
| 82 | // 新建一输出文件流 | |
| 83 | File file = SimpleTool.createFile(outPath,excelName); | |
| 84 | FileOutputStream fOut = new FileOutputStream(file); | |
| 85 | // 把相应的Excel 工作簿存盘 | |
| 86 | workbook.write(fOut); | |
| 87 | fOut.flush(); | |
| 88 | // 操作结束，关闭文件 | |
| 89 | fOut.close(); | |
| 90 | ||
| 91 | if(outPath.endsWith("/")){ | |
| 92 | filePath = outPath + excelName; | |
| 93 | }else{ | |
| 94 | filePath = outPath +"/"+ excelName; | |
| 95 | } | |
| 96 | return filePath; | |
| 97 | } | |
| 98 | ||
| 99 | private static String objToString(Object obj) { | |
| 100 | if (obj == null) { | |
| 101 | return ""; | |
| 102 | } else { | |
| 103 | if (obj instanceof String) { | |
| 104 | return (String) obj; | |
| 105 | } else if (obj instanceof Date) { | |
| 106 | return null;// DateUtil.dateToString((Date) | |
| 107 | // obj,DateUtil.DATESTYLE_SHORT_EX); | |
| 108 | } else { | |
| 109 | return obj.toString(); | |
| 110 | } | |
| 111 | } | |
| 112 | } | |
| 113 | ||
| 114 | /** | |
| 115 | * 读取 Excel文件内容 | |
| 116 | * | |
| 117 | * @param file | |
| 118 | * @param header 是否包括表头 | |
| 119 | * @return | |
| 120 | * @throws Exception | |
| 121 | */ | |
| 122 | public static List<List<String>> readExcelByeFileData(MultipartFile file, boolean header) throws Exception { | |
| 123 | ||
| 124 | String fileName = file.getOriginalFilename(); | |
| 125 | if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) { | |
| 126 | throw new TipsException("上传文件格式不正确"); | |
| 127 | } | |
| 128 | ||
| 129 | // 结果集 | |
| 130 | List<List<String>> list = new ArrayList<>(); | |
| 131 | ||
| 132 | HSSFWorkbook hssfworkbook = new HSSFWorkbook(file.getInputStream()); | |
| 133 | ||
| 134 | // 遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数 | |
| 135 | for(int s=0;s<hssfworkbook.getNumberOfSheets();s++) { | |
| 136 | HSSFSheet hssfsheet = hssfworkbook.getSheetAt(s); | |
| 137 | int col = 0; | |
| 138 | // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数 去除标题 | |
| 139 | for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { | |
| 140 | HSSFRow hssfrow = hssfsheet.getRow(j); | |
| 141 | if(hssfrow!=null){ | |
| 142 | if(j == 0) { | |
| 143 | col = hssfrow.getPhysicalNumberOfCells(); | |
| 144 | if(!header) { | |
| 145 | //不包括表头 | |
| 146 | continue; | |
| 147 | } | |
| 148 | } | |
| 149 | // 单行数据 | |
| 150 | List<String> arrayString = new ArrayList<>(); | |
| 151 | for (int i = 0; i < col; i++) { | |
| 152 | HSSFCell cell = hssfrow.getCell(i); | |
| 153 | if (cell == null) { | |
| 154 | arrayString.add(""); | |
| 155 | } else if (cell.getCellType() == 0) { | |
| 156 | // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); | |
| 157 | if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { | |
| 158 | short format = cell.getCellStyle().getDataFormat(); | |
| 159 | if(format == 14 || format == 31 || format == 57 || format == 58){ | |
| 160 | //日期（中文时间格式的） | |
| 161 | Date d = cell.getDateCellValue(); | |
| 162 | DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); | |
| 163 | // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); | |
| 164 | arrayString.add(formater.format(d)); | |
| 165 | //arrayString[i] = formater.format(d); | |
| 166 | }else if (HSSFDateUtil.isCellDateFormatted(cell)) { | |
| 167 | Date d = cell.getDateCellValue(); | |
| 168 | //DateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒"); | |
| 169 | DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); | |
| 170 | arrayString.add(formater.format(d)); | |
| 171 | //arrayString[i] = formater.format(d); | |
| 172 | } else { | |
| 173 | if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){ | |
| 174 | arrayString.add(cell.getStringCellValue()); | |
| 175 | //arrayString[i] =cell.getStringCellValue(); | |
| 176 | }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){ | |
| 177 | arrayString.add(cell.getCellFormula()); | |
| 178 | //arrayString[i] =cell.getCellFormula(); | |
| 179 | }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){ | |
| 180 | HSSFDataFormatter dataFormatter = new HSSFDataFormatter(); | |
| 181 | arrayString.add(dataFormatter.formatCellValue(cell)); | |
| 182 | //arrayString[i] =dataFormatter.formatCellValue(cell); | |
| 183 | } | |
| 184 | } | |
| 185 | } | |
| 186 | } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){ | |
| 187 | arrayString.add(""); | |
| 188 | //arrayString[i] = ""; | |
| 189 | } else { // 如果EXCEL表格中的数据类型为字符串型 | |
| 190 | arrayString.add(cell.getStringCellValue().trim()); | |
| 191 | //arrayString[i] = cell.getStringCellValue().trim(); | |
| 192 | } | |
| 193 | } | |
| 194 | list.add(arrayString); | |
| 195 | } | |
| 196 | } | |
| 197 | } | |
| 198 | return list; | |
| 199 | } | |
| 200 | ||
| 201 | } | |
