读写excel
poi 版本，可能还有别的依赖，但都是3.17这个版本
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency>
代码
package com.excel.util; import org.apache.commons.lang3.time.FastDateFormat; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import org.apache.poi.util.IOUtils; import org.apache.poi.xssf.usermodel.*; import java.io.ByteArrayOutputStream; import java.io.File; import java.lang.reflect.Field; import java.math.BigDecimal; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; public class ExcelUtil { public static <T> void read(String path, Class<T> tClass,int headNum) { File xlsx = new File(path); try (Workbook workbook = WorkbookFactory.create(xlsx)) { Sheet sheet = workbook.getSheetAt(0); int rowNum = sheet.getPhysicalNumberOfRows(); Row headRow = sheet.getRow(headNum); short cellNum = headRow.getLastCellNum(); String[] titles = new String[cellNum]; for (int j = 0; j < cellNum; j++) { Cell cell = headRow.getCell(j); titles[j] = cell.getStringCellValue(); } List<T> pojoList = new ArrayList<>(rowNum - 1); Field[] fields = tClass.getDeclaredFields(); for (int startIndex = headNum + 1; startIndex < rowNum; startIndex++) { Map<String, Object> valMap = new HashMap<>(); Row row = sheet.getRow(startIndex); short lastCellNum = row.getLastCellNum(); for (int j = 0; j < lastCellNum; j++) { Cell cell = row.getCell(j); valMap.put(titles[j], getCellValue(cell,fields[j])); } T t = mapToPojo(valMap, tClass); pojoList.add(t); } System.out.println(); } catch (Exception e) { throw new RuntimeException(e); } } private static <T> T mapToPojo(Map<String, Object> valMap, Class<T> clazz) { Field[] fields = clazz.getDeclaredFields(); T instance = null; try { instance = clazz.newInstance(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { throw new RuntimeException(e); } for (Field field : fields) { String name = field.getName(); field.setAccessible(true); try { field.set(instance, valMap.get(name)); } catch (IllegalAccessException e) { throw new RuntimeException(e); } } return instance; } private static Object getCellValue(Cell cell, Field field) throws IllegalAccessException, InstantiationException { CellType cellTypeEnum = cell.getCellTypeEnum(); if (cellTypeEnum == CellType.BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellTypeEnum() == CellType.NUMERIC) { Object cellValue ; if (HSSFDateUtil.isCellDateFormatted(cell)) { //判断是日期类型 CellStyle cellStyle = cell.getCellStyle(); String dataFormatString = cellStyle.getDataFormatString(); FastDateFormat dateformat; if (dataFormatString.endsWith("ss")){// 包含时间 dateformat = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss"); }else {// 只有日期 dateformat = FastDateFormat.getInstance("yyyy-MM-dd"); } Date dt = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());//获取成DATE类型 cellValue = dateformat.format(dt); }else{ Class<?> type = field.getType(); if (type == String.class){ DecimalFormat df = new DecimalFormat("0"); cellValue = df.format(cell.getNumericCellValue()); }else if (type == BigDecimal.class){ cellValue = new BigDecimal(cell.getNumericCellValue() + ""); } else if (type == Integer.class ){ cellValue =(int)cell.getNumericCellValue(); } else if (type == Float.class ){ cellValue =Float.valueOf(cell.getNumericCellValue() + ""); } else if (type == Double.class ){ cellValue =Double.valueOf(cell.getNumericCellValue() + ""); } else if (type == Long.class ){ cellValue =Long.valueOf(cell.getNumericCellValue() + ""); } else if (type == BigDecimal.class){ cellValue = new BigDecimal(cell.getNumericCellValue() + ""); } else { cellValue = null; } } return cellValue; } else { return cell.getStringCellValue(); } } public static byte[] exportListXlsx(String sheetTitle, List<String> title, List<List<String>> list) { XSSFWorkbook wb = new XSSFWorkbook();//创建excel表 XSSFSheet sheet = wb.createSheet(sheetTitle); sheet.setDefaultColumnWidth(20);//设置默认行宽 //表头样式（加粗，水平居中，垂直居中） XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 //设置边框样式 XSSFFont fontStyle = wb.createFont(); cellStyle.setFont(fontStyle); //标题样式（加粗，垂直居中） XSSFCellStyle titleCellStyle = wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 titleCellStyle.setFont(fontStyle); //设置边框样式 //字段样式（垂直居中） XSSFCellStyle dataCellStyle = wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 //创建表头 XSSFRow row = sheet.createRow(0); row.setHeightInPoints(20);//行高 XSSFCell cell = row.createCell(0); cell.setCellValue(sheetTitle); cell.setCellStyle(cellStyle); //创建标题 XSSFRow rowTitle = sheet.createRow(0); rowTitle.setHeightInPoints(20); XSSFCell hc; for (int i = 0; i < title.size(); i++) { hc = rowTitle.createCell(i); hc.setCellValue(title.get(i)); hc.setCellStyle(titleCellStyle); } byte result[] = null; ByteArrayOutputStream out = null; try { //创建表格数据 int i = 1; for (List<String> data : list) { XSSFRow rowBody = sheet.createRow(i); rowBody.setHeightInPoints(20); int j = 0; // 显示的列是你所要封装的实体类的每个对象，而且顺序是你创建对象的顺序 for (String val : data) { hc = rowBody.createCell(j); hc.setCellValue(val); hc.setCellStyle(dataCellStyle); j++; } i++; } out = new ByteArrayOutputStream(); wb.write(out); result = out.toByteArray(); } catch (Exception ex) { throw new RuntimeException("报表导出异常" + ex.getMessage()); } finally { IOUtils.closeQuietly(out); } return result; } public static byte[] exportXlsx(String sheetTitle, String[] title, List<?> list) { XSSFWorkbook wb = new XSSFWorkbook();//创建excel表 XSSFSheet sheet = wb.createSheet(sheetTitle); sheet.setDefaultColumnWidth(20);//设置默认行宽 sheet.createFreezePane(0,1); //表头样式（加粗，水平居中，垂直居中） XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 //设置边框样式 XSSFFont fontStyle = wb.createFont(); cellStyle.setFont(fontStyle); //标题样式（加粗，垂直居中） XSSFCellStyle titleCellStyle = wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 titleCellStyle.setFont(fontStyle); //设置边框样式 //字段样式（垂直居中） XSSFCellStyle dataCellStyle = wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 //创建表头 XSSFRow row = sheet.createRow(0); row.setHeightInPoints(20);//行高 XSSFCell cell = row.createCell(0); cell.setCellValue(sheetTitle); cell.setCellStyle(cellStyle); //创建标题 XSSFRow rowTitle = sheet.createRow(0); rowTitle.setHeightInPoints(20); XSSFCell hc; for (int i = 0; i < title.length; i++) { hc = rowTitle.createCell(i); hc.setCellValue(title[i]); hc.setCellStyle(titleCellStyle); } byte result[] = null; ByteArrayOutputStream out = null; try { //创建表格数据 Field[] fields; int i = 1; for (Object obj : list) { fields = obj.getClass().getDeclaredFields(); XSSFRow rowBody = sheet.createRow(i); rowBody.setHeightInPoints(20); int j = 0; // 显示的列是你所要封装的实体类的每个对象，而且顺序是你创建对象的顺序 for (Field f : fields) { f.setAccessible(true); hc = rowBody.createCell(j); if (f.get(obj) != null) { hc.setCellValue(f.get(obj).toString()); } else { hc.setCellValue(""); } hc.setCellStyle(dataCellStyle); j++; } i++; } out = new ByteArrayOutputStream(); wb.write(out); result = out.toByteArray(); } catch (Exception ex) { throw new RuntimeException("报表导出异常" + ex.getMessage()); } finally { IOUtils.closeQuietly(out); } return result; } public static byte[] exportXls(String sheetTitle, String[] title, List<?> list) { HSSFWorkbook wb = new HSSFWorkbook();//创建excel表 HSSFSheet sheet = wb.createSheet(sheetTitle); sheet.setDefaultColumnWidth(20);//设置默认行宽 //表头样式（加粗，水平居中，垂直居中） HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 //设置边框样式 cellStyle.setBorderBottom(BorderStyle.THIN); //下边框 cellStyle.setBorderBottom(BorderStyle.THIN); //下边框 cellStyle.setBorderLeft(BorderStyle.THIN);//左边框 cellStyle.setBorderTop(BorderStyle.THIN);//上边框 cellStyle.setBorderRight(BorderStyle.THIN);//右边框 HSSFFont fontStyle = wb.createFont(); cellStyle.setFont(fontStyle); //标题样式（加粗，垂直居中） HSSFCellStyle cellStyle2 = wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 cellStyle2.setFont(fontStyle); //设置边框样式 cellStyle2.setBorderBottom(BorderStyle.THIN); //下边框 cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框 cellStyle2.setBorderTop(BorderStyle.THIN);//上边框 cellStyle2.setBorderRight(BorderStyle.THIN);//右边框 //字段样式（垂直居中） HSSFCellStyle cellStyle3 = wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 //设置边框样式 cellStyle3.setBorderBottom(BorderStyle.THIN); //下边框 cellStyle3.setBorderLeft(BorderStyle.THIN);//左边框 cellStyle3.setBorderTop(BorderStyle.THIN);//上边框 cellStyle3.setBorderRight(BorderStyle.THIN);//右边框 //创建表头 HSSFRow row = sheet.createRow(0); row.setHeightInPoints(20);//行高 HSSFCell cell = row.createCell(0); cell.setCellValue(sheetTitle); cell.setCellStyle(cellStyle); //创建标题 HSSFRow rowTitle = sheet.createRow(0); rowTitle.setHeightInPoints(20); HSSFCell hc; for (int i = 0; i < title.length; i++) { hc = rowTitle.createCell(i); hc.setCellValue(title[i]); hc.setCellStyle(cellStyle2); } byte result[] = null; ByteArrayOutputStream out = null; try { //创建表格数据 Field[] fields; int i = 1; for (Object obj : list) { fields = obj.getClass().getDeclaredFields(); HSSFRow rowBody = sheet.createRow(i); rowBody.setHeightInPoints(20); int j = 0; // 显示的列是你所要封装的实体类的每个对象，而且顺序是你创建对象的顺序 for (Field f : fields) { f.setAccessible(true); hc = rowBody.createCell(j); if (f.get(obj) != null) { hc.setCellValue(f.get(obj).toString()); } else { hc.setCellValue(""); } hc.setCellStyle(cellStyle3); j++; } i++; } out = new ByteArrayOutputStream(); wb.write(out); result = out.toByteArray(); } catch (Exception ex) { throw new RuntimeException("报表导出异常" + ex.getMessage()); } finally { IOUtils.closeQuietly(out); } return result; } }
controller 下载
@GetMapping("/export")
public void export(HttpServletResponse response) throws IOException {
List<String> title = Arrays.asList("id","名字","生日");
FastDateFormat format = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss");
List<List<String>> list = new ArrayList<>();
for (int i = 0; i < 3; i++) {
List<String> data = new ArrayList<>();
data.add(i+"");
data.add("小苏:" + i);
data.add(format.format(new Date()));
list.add(data);
}
byte[] bytes = ExcelUtil.exportListXlsx("one", title, list);
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-download");
response.addHeader("Content-Disposition",
"attachment;fileName=1.xlsx" );
ServletOutputStream os = response.getOutputStream();
os.write(bytes);
}