Java Excel 文件导出导入
特点：
支持xls、xlsx格式；
导出时支持自定义表格标题和表格数据，支持一个文档里多个Sheet；
依赖：
<!--excel util-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
代码：
1 import com.fasterxml.jackson.databind.exc.InvalidFormatException; 2 import com.sensetime.sensestudy.bff.util.vo.StuExpDownloadVO; 3 import com.sensetime.sensestudy.bff.util.vo.StuTaskDownloadVO; 4 import lombok.AllArgsConstructor; 5 import lombok.Data; 6 import lombok.extern.slf4j.Slf4j; 7 import org.apache.poi.hssf.usermodel.HSSFClientAnchor; 8 import org.apache.poi.hssf.usermodel.HSSFRichTextString; 9 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 10 import org.apache.poi.ss.usermodel.*; 11 import org.apache.poi.xssf.usermodel.XSSFRichTextString; 12 import org.apache.poi.xssf.usermodel.XSSFWorkbook; 13 import org.joda.time.DateTime; 14 15 import java.io.*; 16 import java.lang.reflect.Field; 17 import java.nio.file.Files; 18 import java.nio.file.Paths; 19 import java.time.Instant; 20 import java.time.ZoneId; 21 import java.time.ZonedDateTime; 22 import java.time.format.DateTimeFormatter; 23 import java.util.*; 24 import java.util.regex.Matcher; 25 import java.util.regex.Pattern; 26 27 28 /** 工具类，用于将数据导出到excel文件（可以导出到一个文件的多个Sheet） 或 从excel文件读取数据 */ 29 @Slf4j 30 public class ExcelUtil { 31 32 public static void main(String[] args) throws IOException { 33 String fileName = "test.xls"; 34 exportExcel( 35 Arrays.asList( 36 new SheetInfo<StuTaskDownloadVO>( 37 "class1", ("用户名, 姓名, 完成情况, 提交时间, 分数").split(","), 38 Arrays.asList(new StuTaskDownloadVO("zhangsna", "张三", "未开始", ZonedDateTime.now().toInstant(), 100.0)) 39 ), 40 new SheetInfo<StuTaskDownloadVO>( 41 "class2", ("用户名, 姓名, 完成情况, 提交时间, 分数").split(","), Arrays.asList( 42 new StuTaskDownloadVO("wangwu", "王五", "已完成", ZonedDateTime.now().toInstant(), 20.0), 43 new StuTaskDownloadVO("zhaoliu", "赵六", "未开始", ZonedDateTime.now().toInstant(), 19.0)) 44 ) 45 ), Files.newOutputStream(Paths.get("a.xls"))); 46 47 exportExcel( 48 ("用户名, 姓名, 完成情况, 得分, 评语").split(","), 49 Arrays.asList(new StuExpDownloadVO("zhangsna", "张三", "未开始", 22.0, "good"), new StuExpDownloadVO("lisi", "李四", "进行中", 21.0, "bad")), 50 Files.newOutputStream(Paths.get("b.xls"))); 51 } 52 53 private ExcelUtil() { 54 } 55 56 private static final String datetimeFormat = "yyyy-MM-dd HH:mm:ss"; 57 58 /** 59 * @see {@link #exportExcel(String[] headers, Collection dataset, OutputStream out, String datetimeFormat) } 60 */ 61 public static <T> void exportExcel(String[] headers, Collection<T> dataSet, OutputStream out) { 62 exportExcel(headers, dataSet, out, datetimeFormat); 63 } 64 65 66 /** 67 * 利用JAVA的反射机制，将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定输出流。<br> 68 * <br> 69 * <b>输出后会关闭输出流</b> 70 * 71 * @param headers 表格属性列名数组。注意：数组中列名的顺序须与待导出的javabean属性的定义顺序一致 72 * @param dataset 需要显示的数据集合，集合中java bean对象的所有非继承属性的值均会被输出。此方法支持的 73 * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) 74 * @param out 与输出设备关联的流对象，可以将 EXCEL 文档导出到本地文件或者网络中 75 * @param datetimeFormat 如果有时间数据，设定输出格式。 76 */ 77 public static <T> void exportExcel(String[] headers, Collection<T> dataset, OutputStream out, String datetimeFormat) { 78 exportExcel(Arrays.asList(new SheetInfo("sheet 0", headers, dataset)), out, datetimeFormat); 79 } 80 81 public static <T> void exportExcel(List<SheetInfo<T>> sheetInfos, OutputStream out) { 82 exportExcel(sheetInfos, out, datetimeFormat); 83 } 84 85 /** 86 * 利用JAVA的反射机制，将放置在JAVA集合中并且符合一定条件的数据以EXCEL的形式输出到指定输出流。<br> 87 * <br> 88 * <b>输出后会关闭输出流</b> 89 * 90 * @param sheetInfos EXCEL 文档中的Sheet信息定义 91 * @param out 与输出设备关联的流对象，可以将 EXCEL 文档导出到本地文件或者网络中 92 * @param datetimeFormat 如果有时间数据，设定输出格式。默认会采用服务器所在时区 93 */ 94 public static <T> void exportExcel(List<SheetInfo<T>> sheetInfos, OutputStream out, String datetimeFormat) { 95 sheetInfos = Optional.ofNullable(sheetInfos).orElse(Collections.emptyList()); 96 // 生成Excel文档 97 Workbook workbook = generateWorkbook(); 98 // 生成表头样式 99 CellStyle tableHeaderStyle = generateTableHeaderStyle(workbook); 100 // 生成表体样式 101 CellStyle tableBodyStyle = generateTableBodyStyle(workbook); 102 // 时间值格式化工具 103 DateTimeFormatter sdf = DateTimeFormatter.ofPattern(datetimeFormat).withZone(ZoneId.systemDefault()); 104 // 生成Excel文档中的sheet 105 for (int siIndex = 0; siIndex < sheetInfos.size(); siIndex++) { 106 SheetInfo sheetInfo = sheetInfos.get(siIndex); 107 String[] headers = sheetInfo.getHeaders(); 108 //创建sheet 109 Sheet sheet = workbook.createSheet(); 110 workbook.setSheetName(siIndex, sheetInfo.getName()); 111 sheet.setDefaultColumnWidth(18); 112 // 声明一个绘制图片的顶级管理器 113 Drawing patriarch = sheet.createDrawingPatriarch(); 114 // 定义注释的大小和位置 115 // HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 116 // 0, 0, 0, (short) 4, 2, (short) 6, 5)); 117 // 设置注释内容 118 // comment.setString(new HSSFRichTextString("可以在POI中添加注释！")); 119 // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容. 120 // comment.setAuthor("sensetime"); 121 122 // 填充表格标题行 123 int rowIndex = 0; 124 Row row = sheet.createRow(rowIndex); 125 for (int i = 0; i < headers.length; i++) { 126 Cell cell = row.createCell(i); 127 cell.setCellStyle(tableHeaderStyle); 128 cell.setCellValue(generateRichTextString(headers[i])); 129 } 130 rowIndex++; 131 132 // 填充表格数据行 133 Iterator<T> rowDataIt = sheetInfo.getDataset().iterator(); 134 Field[] fields = null; 135 while (rowDataIt.hasNext()) { 136 row = sheet.createRow(rowIndex); 137 T t = (T) rowDataIt.next(); 138 // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值 139 if (null == fields) { 140 fields = t.getClass().getDeclaredFields(); 141 } 142 for (int i = 0; i < fields.length && i < headers.length; i++) { 143 //HSSFCell cell = row.createCell(i); 144 Cell cell = row.createCell(i); 145 cell.setCellStyle(tableBodyStyle); 146 147 Field field = fields[i]; 148 field.setAccessible(true); 149 try { 150 Object value = field.get(t); 151 // 判断值的类型后进行强制类型转换 152 String textValue = null; 153 // if (value instanceof Integer) { 154 // int intValue = (Integer) value; 155 // cell.setCellValue(intValue); 156 // } else if (value instanceof Float) { 157 // float fValue = (Float) value; 158 // textValue = new HSSFRichTextString( 159 // String.valueOf(fValue)); 160 // cell.setCellValue(textValue); 161 // } else if (value instanceof Double) { 162 // double dValue = (Double) value; 163 // textValue = new HSSFRichTextString( 164 // String.valueOf(dValue)); 165 // cell.setCellValue(textValue); 166 // } else if (value instanceof Long) { 167 // long longValue = (Long) value; 168 // cell.setCellValue(longValue); 169 // } 170 if (value == null) { 171 textValue = ""; 172 } else if (value instanceof Date || value instanceof Instant) { 173 Instant instant = null; 174 if (value instanceof Date) { 175 Date date = (Date) value; 176 instant = date.toInstant(); 177 } else { 178 instant = (Instant) value; 179 } 180 textValue = sdf.format(instant); 181 } else if (value instanceof byte[]) { 182 // 有图片时，设置行高为60px; 183 row.setHeightInPoints(60); 184 // 设置图片所在列宽度为80px,注意这里单位的一个换算 185 sheet.setColumnWidth(i, (short) (35.7 * 80)); 186 sheet.autoSizeColumn(i); 187 byte[] bsValue = (byte[]) value; 188 HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, rowIndex, (short) 6, rowIndex); 189 anchor.setAnchorType(ClientAnchor.AnchorType.byId(2)); 190 patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); 191 } else { 192 // 其它数据类型都当作字符串简单处理 193 textValue = value.toString(); 194 } 195 // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成 196 if (textValue != null) { 197 Pattern p = Pattern.compile("^//d+(//.//d+)?$"); 198 Matcher matcher = p.matcher(textValue); 199 if (matcher.matches()) { 200 // 是数字则当作double处理 201 cell.setCellValue(Double.parseDouble(textValue)); 202 } else { 203 cell.setCellValue(generateRichTextString(textValue)); 204 } 205 } 206 } catch (Exception e) { 207 log.error(e.getMessage(), e); 208 } 209 } 210 rowIndex++; 211 } 212 } 213 214 215 try { 216 workbook.write(out); 217 } catch (IOException e) { 218 log.error(e.getMessage(), e); 219 } finally {// 清理资源 220 if (null != workbook) { 221 try { 222 workbook.close(); 223 } catch (IOException e) { 224 log.error(e.getMessage(), e); 225 } 226 } 227 if (null != out) { 228 try { 229 out.close(); 230 } catch (IOException e) { 231 log.error(e.getMessage(), e); 232 } 233 } 234 } 235 } 236 237 /** 238 * 读取excel文件内容，存入到字节流。每行结尾加了换行符，一行中各字段后跟一逗号来分隔 239 */ 240 public static ByteArrayInputStream importExcel(InputStream in, int sheetIndex) throws InvalidFormatException, IOException, InstantiationException, IllegalAccessException { 241 ByteArrayOutputStream baos = new ByteArrayOutputStream(); 242 try (Workbook workbook = generateWorkbookFromInputStream(in)) { 243 Sheet sheet = workbook.getSheetAt(sheetIndex); 244 for (int ri = sheet.getFirstRowNum(); ri <= sheet.getLastRowNum(); ri++) { 245 Row row = sheet.getRow(ri); 246 for (int ci = row.getFirstCellNum(); ci < row.getLastCellNum(); ci++) { 247 Cell cell = row.getCell(ci); 248 String value = getValue(cell) + ", "; 249 baos.write(value.getBytes()); 250 // System.out.printf("%s ", value); 251 } 252 baos.write("\n".getBytes()); 253 // System.out.println(""); 254 } 255 } catch (Exception e) { 256 log.error(e.getMessage(), e); 257 } 258 ; 259 260 261 return new ByteArrayInputStream(baos.toByteArray()); 262 } 263 264 private static String getValue(Cell cell) { 265 if (cell.getCellType() == CellType.BOOLEAN) { 266 // 返回布尔类型的值 267 return String.valueOf(cell.getBooleanCellValue()); 268 } else if (cell.getCellType() == CellType.NUMERIC) { 269 // 返回数值类型的值 270 return String.valueOf(cell.getNumericCellValue()); 271 } else { 272 // 返回字符串类型的值 273 return String.valueOf(cell.getStringCellValue()); 274 } 275 } 276 277 /** 278 * excepl 文档中的一个Sheet的信息定义 279 */ 280 @Data 281 @AllArgsConstructor 282 public static class SheetInfo<T> { 283 private String name;//表格名称 284 private String[] headers;//表格列名 285 private Collection<T> dataset;//表格数据，每个元素代表一行 286 } 287 288 289 //以下为为了同时兼容 xls、xlsx 格式的excel文档而定义的方法 290 /** 291 * 是否生成xlsx文档，为true则生成xlsx格式的文档（Excel2007及以后的版本，扩展名是.xlsx）、否则生成xls格式的文档（Excel2003及以前的版本，扩展名是.xls）。 292 */ 293 private static final boolean USE_XLSX = true; 294 295 private static RichTextString generateRichTextString(String val) { 296 RichTextString res = USE_XLSX ? new XSSFRichTextString(val) : new HSSFRichTextString(val); 297 //res.applyFont(new xx); 298 return res; 299 } 300 301 302 /** 303 * 创建一个excel文档 304 */ 305 private static Workbook generateWorkbook() { 306 //HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls 307 //XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx 308 Workbook res = USE_XLSX ? new XSSFWorkbook() : new HSSFWorkbook(); 309 return res; 310 } 311 312 /** 313 * 创建一个excel文档 314 */ 315 private static Workbook generateWorkbookFromInputStream(InputStream in) throws IOException { 316 //HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls 317 //XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx 318 Workbook res = USE_XLSX ? new XSSFWorkbook(in) : new HSSFWorkbook(in); 319 return res; 320 } 321 322 private static CellStyle generateTableHeaderStyle(Workbook workbook) { 323 CellStyle style = workbook.createCellStyle(); 324 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); 325 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); 326 style.setBorderBottom(BorderStyle.THIN); 327 style.setBorderLeft(BorderStyle.THIN); 328 style.setBorderRight(BorderStyle.THIN); 329 style.setBorderTop(BorderStyle.THIN); 330 style.setAlignment(HorizontalAlignment.CENTER); 331 Font font = workbook.createFont(); 332 font.setColor(IndexedColors.WHITE.getIndex()); 333 font.setFontHeightInPoints((short) 12); 334 //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 335 style.setFont(font); 336 337 return style; 338 } 339 340 private static CellStyle generateTableBodyStyle(Workbook workbook) { 341 CellStyle style = workbook.createCellStyle(); 342 style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); 343 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); 344 style.setBorderBottom(BorderStyle.THIN); 345 style.setBorderLeft(BorderStyle.THIN); 346 style.setBorderRight(BorderStyle.THIN); 347 style.setBorderTop(BorderStyle.THIN); 348 style.setAlignment(HorizontalAlignment.CENTER); 349 style.setVerticalAlignment(VerticalAlignment.CENTER); 350 Font font = workbook.createFont(); 351 font.setColor(IndexedColors.GREY_80_PERCENT.index); 352 //font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); 353 style.setFont(font); 354 355 return style; 356 } 357 358 }
