下载文档原格式
Java中Easypoi实现excel多sheet表导⼊导出功能Easypoi简化了开发中对⽂档的导⼊导出实现，并不像poi那样都要写⼤段⼯具类来搞定⽂档的读写。
第⼀步引⼊Easypoi依赖<!-- 导出⽂件⼯具 EasyPoi实现Excel读写管理测试⽤例 --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.2.0</version></dependency>Easypoi的注解使⽤说明（存留查看即可）第⼆步定义对应表格头数据对象实体类(注解的使⽤可以查阅上⾯的按需使⽤即可)@Setter@Getter@ToStringpublic class LoginCaseDto {@Excel(name = "flag(0是反向，1是正向)",orderNum = "1",width = 20)private String flag;@Excel(name = "urlid(访问id)",orderNum = "2",width = 20)private String urlid;@Excel(name = "name(登录账号)",orderNum = "3",width = 20)private String name;@Excel(name = "pwd(登录密码)",orderNum = "4",width = 20)private String pwd;@Excel(name = "desc(期望提⽰语)",orderNum = "5",width = 40)private String desc;@Excel(name = "actual(实际测试结果)",orderNum = "6",width = 40 )private String actual;@Excel(name = "urlpath(被测路径)",orderNum = "7",width = 40 )private String urlpath;}public class LoginUrlDto {@Excel(name = "id（访问测试类型）",orderNum = "1",width = 20)private String id;@Excel(name = "type(请求类型)",orderNum = "2",width = 20)private String type;@Excel(name = "url(访问地址)",orderNum = "3",width = 40)private String url;}第三步：封装Easypoi⼯具类（⽹上查了很多但是并不完整，这⾥补充下）关键封装⼯具类多sheet导⼊⽅法/*** 功能描述：根据接收的Excel⽂件来导⼊多个sheet,根据索引可返回⼀个集合* @param filePath 导⼊⽂件路径* @param sheetIndex 导⼊sheet索引* @param titleRows 表标题的⾏数* @param headerRows 表头⾏数* @param pojoClass Excel实体类* @return*/public static <T> List<T> importExcel(String filePath,int sheetIndex,Integer titleRows, Integer headerRows, Class<T> pojoClass) {// 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有⼏个sheet页ImportParams params = new ImportParams();// 第⼏个sheet页params.setStartSheetIndex(sheetIndex);params.setTitleRows(titleRows);params.setHeadRows(headerRows);List<T> list = null;try {list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);} catch (NoSuchElementException e) {throw new RuntimeException("模板不能为空");} catch (Exception e) {e.printStackTrace();}return list;}excel导⼊⽰例（直接传⼊sheet索引获取对应的sheet表）多sheet表导出⽅法使⽤(需要把导⼊的多sheet表数据转成list集合获取新数据后调⽤该⽅法重新写⼊)/*** 功能描述：把同⼀个表格多个sheet测试结果重新输出，如果后续增加多个List<Map<String, Object>>对象，需要后⾯继续追加* @ExcelEntiry sheet表格映射的实体对象* @return*/public static String exportSheet( Object...objects){Workbook workBook = null;try {// 创建参数对象（⽤来设定excel得sheet得内容等信息）ExportParams deptExportParams = new ExportParams();// 设置sheet得名称deptExportParams.setSheetName("登录⽤例");// 设置sheet表头名称deptExportParams.setTitle("测试⽤例");// 创建sheet1使⽤得mapMap<String, Object> deptExportMap = new HashMap<>();// title的参数为ExportParams类型，⽬前仅仅在ExportParams中设置了sheetNamedeptExportMap.put("title", deptExportParams);// 模版导出对应得实体类型deptExportMap.put("entity", LoginCaseDto.class);// sheet中要填充得数据deptExportMap.put("data", objects[0]);ExportParams empExportParams = new ExportParams();empExportParams.setTitle("被测RUL路径");empExportParams.setSheetName("被测url");// 创建sheet2使⽤得mapMap<String, Object> empExportMap = new HashMap<>();empExportMap.put("title", empExportParams);empExportMap.put("entity", LoginUrlDto.class);empExportMap.put("data", objects[1]);// 将sheet1、sheet2使⽤得map进⾏包装List<Map<String, Object>> sheetsList = new ArrayList<>();sheetsList.add(deptExportMap);sheetsList.add(empExportMap);// 执⾏⽅法workBook = EasyPoiUtil.exportExcel(sheetsList, ExcelType.HSSF);//String fileName = URLEncoder.encode("test", "UTF-8");String filepath = (String) LoadStaticConfigUtil.getCommonYml( "testcaseexcel.cases");FileOutputStream fos = new FileOutputStream(filepath);workBook.write(fos);fos.close();}catch (Exception e){e.printStackTrace();}finally {if(workBook != null) {try {workBook.close();} catch (IOException e) {e.printStackTrace();}}}return "success";}最后即可获取新的测试结果表格。
Java使⽤POI读取和写⼊Excel指南（转）做项⽬时经常有通过程序读取Excel数据，或是创建新的Excel并写⼊数据的需求；⽹上很多经验教程⾥使⽤的POI版本都⽐较⽼了，⼀些API在新版⾥已经废弃，这⾥基于最新的Apache POI 4.0.1版本来总结⼀下整个读取和写⼊Excel的过程，希望能帮助到需要的⼈ ^_^1. 准备⼯作1.1 在项⽬中引⼊Apache POI相关类库引⼊ Apache POI 和 Apache POI-OOXML 这两个类库，Maven坐标如下：<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.0.1</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.0.1</version></dependency>2. 读取或写⼊Excel数据2.1 ⽰例程序结构说明简单说明⼀下⽰例程序的整体结构：ExcelReader.java是实现读取Excel数据功能的类；ExcelWriter.java是创建新的Excel并向其中写⼊数据的类；ExcelDataVO.java封装了读取或写⼊时每⼀“⾏”的数据；MainTest.java是⽰例程序的⼊⼝类，其中演⽰了读取和写⼊Excel数据的整个过程；2.2 读取数据⽰例程序需要从桌⾯读取 readExample.xlsx 内的数据，readExample.xlsx 的内容如下：读取Excel时主要调⽤ExcelReader.java类来读取和解析Excel的具体内容，这⾥以读取系统⽂件的形式演⽰读取过程：（兼容 xls 和 xlsx）2.2.1 主程序⼊⼝类代码：/*** Author: Dreamer-1* Date: 2019-03-01* Time: 10:13* Description: ⽰例程序⼊⼝类*/public class MainTest {public static void main(String[] args) {// 设定Excel⽂件所在路径String excelFileName = "/Users/Dreamer-1/Desktop/myBlog/java解析Excel/readExample.xlsx";// 读取Excel⽂件内容List<ExcelDataVO> readResult = ExcelReader.readExcel(excelFileName);// todo 进⾏业务操作}}读取和写⼊时封装每⼀“⾏”数据的ExcelDataVO.java代码如下：/*** Author: Dreamer-1* Date: 2019-03-01* Time: 11:33* Description: 读取Excel时，封装读取的每⼀⾏的数据*/public class ExcelDataVO {/*** 姓名*/private String name;/*** 年龄*/private Integer age;/*** 居住地*/private String location;/*** 职业*/private String job;public String getName() {return name;}public void setName(String name) { = name;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}public String getLocation() {return location;}public void setLocation(String location) {this.location = location;}public String getJob() {return job;}public void setJob(String job) {this.job = job;}}2.2.2 Excel解析类的代码：/*** Author: Dreamer-1* Date: 2019-03-01* Time: 10:21* Description: 读取Excel内容*/public class ExcelReader {private static Logger logger = Logger.getLogger(ExcelReader.class.getName()); // ⽇志打印类private static final String XLS = "xls";private static final String XLSX = "xlsx";/*** 根据⽂件后缀名类型获取对应的⼯作簿对象* @param inputStream 读取⽂件的输⼊流* @param fileType ⽂件后缀名类型（xls或xlsx）* @return 包含⽂件数据的⼯作簿对象* @throws IOException*/public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException { Workbook workbook = null;if (fileType.equalsIgnoreCase(XLS)) {workbook = new HSSFWorkbook(inputStream);} else if (fileType.equalsIgnoreCase(XLSX)) {workbook = new XSSFWorkbook(inputStream);}return workbook;}/*** 读取Excel⽂件内容* @param fileName 要读取的Excel⽂件所在路径* @return 读取结果列表，读取失败时返回null*/public static List<ExcelDataVO> readExcel(String fileName) {Workbook workbook = null;FileInputStream inputStream = null;try {// 获取Excel后缀名String fileType = fileName.substring(stIndexOf(".") + 1, fileName.length());// 获取Excel⽂件File excelFile = new File(fileName);if (!excelFile.exists()) {logger.warning("指定的Excel⽂件不存在！");return null;}// 获取Excel⼯作簿inputStream = new FileInputStream(excelFile);workbook = getWorkbook(inputStream, fileType);// 读取excel中的数据List<ExcelDataVO> resultDataList = parseExcel(workbook);return resultDataList;} catch (Exception e) {logger.warning("解析Excel失败，⽂件名：" + fileName + " 错误信息：" + e.getMessage());return null;} finally {try {if (null != workbook) {workbook.close();}if (null != inputStream) {inputStream.close();}} catch (Exception e) {logger.warning("关闭数据流出错！错误信息：" + e.getMessage());return null;}}}/*** 解析Excel数据* @param workbook Excel⼯作簿对象* @return 解析结果*/private static List<ExcelDataVO> parseExcel(Workbook workbook) {List<ExcelDataVO> resultDataList = new ArrayList<>();// 解析sheetfor (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {Sheet sheet = workbook.getSheetAt(sheetNum);// 校验sheet是否合法if (sheet == null) {continue;}// 获取第⼀⾏数据int firstRowNum = sheet.getFirstRowNum();Row firstRow = sheet.getRow(firstRowNum);if (null == firstRow) {logger.warning("解析Excel失败，在第⼀⾏没有读取到任何数据！");}// 解析每⼀⾏的数据，构造数据对象int rowStart = firstRowNum + 1;int rowEnd = sheet.getPhysicalNumberOfRows();for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {Row row = sheet.getRow(rowNum);if (null == row) {continue;}ExcelDataVO resultData = convertRowToData(row);if (null == resultData) {logger.warning("第 " + row.getRowNum() + "⾏数据不合法，已忽略！");continue;}resultDataList.add(resultData);}}return resultDataList;}/*** 将单元格内容转换为字符串* @param cell* @return*/private static String convertCellValueToString(Cell cell) {if(cell==null){return null;}String returnValue = null;switch (cell.getCellType()) {case NUMERIC: //数字Double doubleValue = cell.getNumericCellValue();// 格式化科学计数法，取⼀位整数DecimalFormat df = new DecimalFormat("0");returnValue = df.format(doubleValue);break;case STRING: //字符串returnValue = cell.getStringCellValue();break;case BOOLEAN: //布尔Boolean booleanValue = cell.getBooleanCellValue();returnValue = booleanValue.toString();break;case BLANK: // 空值break;case FORMULA: // 公式returnValue = cell.getCellFormula();break;case ERROR: // 故障break;default:break;}return returnValue;}/*** 提取每⼀⾏中需要的数据，构造成为⼀个结果数据对象** 当该⾏中有单元格的数据为空或不合法时，忽略该⾏的数据** @param row ⾏数据* @return 解析后的⾏数据对象，⾏数据错误时返回null*/private static ExcelDataVO convertRowToData(Row row) {ExcelDataVO resultData = new ExcelDataVO();Cell cell;int cellNum = 0;// 获取姓名cell = row.getCell(cellNum++);String name = convertCellValueToString(cell);resultData.setName(name);// 获取年龄cell = row.getCell(cellNum++);String ageStr = convertCellValueToString(cell);if (null == ageStr || "".equals(ageStr)) {// 年龄为空resultData.setAge(null);} else {resultData.setAge(Integer.parseInt(ageStr));}// 获取居住地cell = row.getCell(cellNum++);String location = convertCellValueToString(cell);resultData.setLocation(location);// 获取职业cell = row.getCell(cellNum++);String job = convertCellValueToString(cell);resultData.setJob(job);return resultData;}}2.2.3 应⽤场景补充⼀般我们会有这样的应⽤场景，即：在前台页⾯的⽂件上传⼊⼝上传本地的Excel⽂件到后台，后台收到Excel⽂件后进⾏解析并做对应的业务操作；这⾥假设前台已经有了上传⽂件的⼊⼝，再简单贴⼀下后台的解析代码；后台接收前台数据的Controller层代码⽰例：@PostMapping("/uploadExcel")public ResponseEntity<?> uploadImage(MultipartFile file) {// 检查前台数据合法性if (null == file || file.isEmpty()) {logger.warning("上传的Excel商品数据⽂件为空！上传时间：" + new Date());return new ResponseEntity<>(HttpStatus.BAD_REQUEST);}try {// 解析ExcelList<ExcelDataVO> parsedResult = ExcelReader.readExcel(file);// todo 进⾏业务操作return new ResponseEntity<>(HttpStatus.OK);} catch (Exception e) {logger.warning("上传的Excel商品数据⽂件为空！上传时间：" + new Date());return new ResponseEntity<>(HttpStatus.BAD_REQUEST);}}ExcelReader.java中的readExcel()⽅法需要做⼀定的修改，代码如下：/*** 读取Excel⽂件内容* @param file 上传的Excel⽂件* @return 读取结果列表，读取失败时返回null*/public static List<ExcelDataVO> readExcel(MultipartFile file) {Workbook workbook = null;try {// 获取Excel后缀名String fileName = file.getOriginalFilename();if (fileName == null || fileName.isEmpty() || stIndexOf(".") < 0) {logger.warning("解析Excel失败，因为获取到的Excel⽂件名⾮法！");return null;}String fileType = fileName.substring(stIndexOf(".") + 1, fileName.length());// 获取Excel⼯作簿workbook = getWorkbook(file.getInputStream(), fileType);// 读取excel中的数据List<ExcelDataVO> resultDataList = parseExcel(workbook);return resultDataList;} catch (Exception e) {logger.warning("解析Excel失败，⽂件名：" + file.getOriginalFilename() + " 错误信息：" + e.getMessage());return null;} finally {try {if (null != workbook) {workbook.close();}} catch (Exception e) {logger.warning("关闭数据流出错！错误信息：" + e.getMessage());return null;}}}2.3 写⼊数据写⼊数据时主要调⽤ExcelWriter.java来创建Excel⼯作簿对象并写⼊数据，这⾥以写⼊系统⽂件数据为例演⽰写⼊的过程：2.3.1 主程序⼊⼝类代码/*** Author: Dreamer-1* Date: 2019-03-01* Time: 10:13* Description: ⽰例程序⼊⼝类*/public class MainTest {private static Logger logger = Logger.getLogger(MainTest.class.getName());public static void main(String[] args) {// 创建需要写⼊的数据列表List<ExcelDataVO> dataVOList = new ArrayList<>(2);ExcelDataVO dataVO = new ExcelDataVO();dataVO.setName("⼩明");dataVO.setAge(18);dataVO.setLocation("⼴州");dataVO.setJob("⼤学⽣");ExcelDataVO dataVO2 = new ExcelDataVO();dataVO2.setName("⼩花");dataVO2.setAge(19);dataVO2.setLocation("深圳");dataVO2.setJob("⼤学⽣");dataVOList.add(dataVO);dataVOList.add(dataVO2);// 写⼊数据到⼯作簿对象内Workbook workbook = ExcelWriter.exportData(dataVOList);// 以⽂件的形式输出⼯作簿对象FileOutputStream fileOut = null;try {String exportFilePath = "/Users/Dreamer-1/Desktop/myBlog/java解析Excel/writeExample.xlsx";File exportFile = new File(exportFilePath);if (!exportFile.exists()) {exportFile.createNewFile();}fileOut = new FileOutputStream(exportFilePath);workbook.write(fileOut);fileOut.flush();} catch (Exception e) {logger.warning("输出Excel时发⽣错误，错误原因：" + e.getMessage());} finally {try {if (null != fileOut) {fileOut.close();}if (null != workbook) {workbook.close();}} catch (IOException e) {logger.warning("关闭输出流时发⽣错误，错误原因：" + e.getMessage());}}}}2.3.2 写⼊Excel类的代码ExcelWriter.java类中，你可以根据实际需要替换 CELL_HEADS 列头的信息，然后重写convertDataToRow⽅法，转换你⾃⼰的⾏数据；/*** Author: Dreamer-1* Date: 2019-03-01* Time: 11:09* Description: ⽣成Excel并写⼊数据*/public class ExcelWriter {private static List<String> CELL_HEADS; //列头static{// 类装载时就载⼊指定好的列头信息，如有需要，可以考虑做成动态⽣成的列头CELL_HEADS = new ArrayList<>();CELL_HEADS.add("姓名");CELL_HEADS.add("年龄");CELL_HEADS.add("居住城市");CELL_HEADS.add("职业");}/*** ⽣成Excel并写⼊数据信息* @param dataList 数据列表* @return 写⼊数据后的⼯作簿对象*/public static Workbook exportData(List<ExcelDataVO> dataList){// ⽣成xlsx的ExcelWorkbook workbook = new SXSSFWorkbook();// 如需⽣成xls的Excel，请使⽤下⾯的⼯作簿对象，注意后续输出时⽂件后缀名也需更改为xls//Workbook workbook = new HSSFWorkbook();// ⽣成Sheet表，写⼊第⼀⾏的列头Sheet sheet = buildDataSheet(workbook);//构建每⾏的数据内容int rowNum = 1;for (Iterator<ExcelDataVO> it = dataList.iterator(); it.hasNext(); ) {ExcelDataVO data = it.next();if (data == null) {continue;}//输出⾏数据Row row = sheet.createRow(rowNum++);convertDataToRow(data, row);}return workbook;}/*** ⽣成sheet表，并写⼊第⼀⾏数据（列头）* @param workbook ⼯作簿对象* @return 已经写⼊列头的Sheet*/private static Sheet buildDataSheet(Workbook workbook) {Sheet sheet = workbook.createSheet();// 设置列头宽度for (int i=0; i<CELL_HEADS.size(); i++) {sheet.setColumnWidth(i, 4000);}// 设置默认⾏⾼sheet.setDefaultRowHeight((short) 400);// 构建头单元格样式CellStyle cellStyle = buildHeadCellStyle(sheet.getWorkbook());// 写⼊第⼀⾏各列的数据Row head = sheet.createRow(0);for (int i = 0; i < CELL_HEADS.size(); i++) {Cell cell = head.createCell(i);cell.setCellValue(CELL_HEADS.get(i));cell.setCellStyle(cellStyle);}return sheet;}/*** 设置第⼀⾏列头的样式* @param workbook ⼯作簿对象* @return 单元格样式对象*/private static CellStyle buildHeadCellStyle(Workbook workbook) {CellStyle style = workbook.createCellStyle();//对齐⽅式设置style.setAlignment(HorizontalAlignment.CENTER);//边框颜⾊和宽度设置style.setBorderBottom(BorderStyle.THIN);style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框style.setBorderLeft(BorderStyle.THIN);style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框style.setBorderRight(BorderStyle.THIN);style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框style.setBorderTop(BorderStyle.THIN);style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框//设置背景颜⾊style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND);//粗体字设置Font font = workbook.createFont();font.setBold(true);style.setFont(font);return style;}/*** 将数据转换成⾏* @param data 源数据* @param row ⾏对象* @return*/private static void convertDataToRow(ExcelDataVO data, Row row){int cellNum = 0;Cell cell;// 姓名cell = row.createCell(cellNum++);cell.setCellValue(null == data.getName() ? "" : data.getName());// 年龄cell = row.createCell(cellNum++);if (null != data.getAge()) {cell.setCellValue(data.getAge());} else {cell.setCellValue("");}// 所在城市cell = row.createCell(cellNum++);cell.setCellValue(null == data.getLocation() ? "" : data.getLocation());// 职业cell = row.createCell(cellNum++);cell.setCellValue(null == data.getJob() ? "" : data.getJob());}}⽰例程序运⾏后将会在指定的系统路径下⽣成 writeExample.xlsx⽂件，其内容如下：2.3.3 应⽤场景补充⼀般写⼊Excel时会有这样的场景：前台页⾯上有⼀个导出按钮，点击后将后台某张表⾥的数据以Excel的形式导出，导出的Excel⽂件通过浏览器下载到⽤户系统中；这⾥默认前台页⾯已经有相应的按钮功能，给出对应的Controller层代码供参考：@GetMapping("/exportExcel")public void exportExcel(HttpServletRequest request, HttpServletResponse response) {Workbook workbook = null;OutputStream out = null;try {// todo 根据业务需求获取需要写⼊Excel的数据列表 dataList// ⽣成Excel⼯作簿对象并写⼊数据workbook = ExcelWriter.exportData(dataList);// 写⼊Excel⽂件到前端if(null != workbook){String excelName = "⽰例Excel导出";String fileName = excelName + DateUtil.format(new Date(), DateUtil.SHORT_DATE) + ".xlsx";fileName = new String(fileName.getBytes("UTF-8"),"iso8859-1");response.setHeader("Content-Disposition", "attachment;filename=" + fileName);response.setContentType("application/x-download");response.setCharacterEncoding("UTF-8");response.addHeader("Pargam", "no-cache");response.addHeader("Cache-Control", "no-cache");response.flushBuffer();out = response.getOutputStream();workbook.write(out);out.flush();}} catch (Exception e) {logger.WARNING("写⼊Excel过程出错！错误原因：" + e.getMessage());} finally {try {if (null != workbook) {workbook.close();}if (null != out) {out.close();}} catch (IOException e) {logger.WARNING("关闭workbook或outputStream出错！");}}}// 前台页⾯发送请求到后台Controller时的JS代码可参考：var url = "/exportExcel";window.location=url;原⽂链接：https:///Dreamer-1/p/10469430.html。
java使⽤poi把从数据库中取出的数据写⼊到excel⽂件中并保存到指定⽂件路径 有时候我们要把从数据库中取出的数据导⼊到excel中，使取到的数据看起来更加的直观和⽅便，在java中如何实现取到的数据导⼊到excel中呢？以下就是使⽤poi⼯具吧数据写⼊excel⽂件中的解决⽅法：Excel表格扩展名有.xlsx和.xls两种格式百度上对两种⽂件的介绍有很多就不⼀⼀列举，基本的不同总结下来有以下⼏点： 在java中读取和写⼊.xls格式使⽤maven导⼊jar包：<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.16</version></dependency> 在java中读取和写⼊.xlsx格式使⽤maven导⼊jar包：<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.16</version></dependency> 然后就可以使⽤jar包读取excel⽂件，并保存到本地指定的位置，⾸先把从数据库中取出的信息放到⼀个list中，然后从list中⼀⼀读取数据，写⼊到excel⽂件中，由于后⾯还有需求约定好使⽤.xlsx⽂件，这⾥⽣成的excel⽂件类型便是.xlsx⽂件,如果需求对⽂件类型没有要求，尽量⽣成.xls⽂件。
/**** @param stuList 从数据库中查询需要导⼊excel⽂件的信息列表* @return 返回⽣成的excel⽂件的路径* @throws Exception*/public static String stuList2Excel(List<Student> stuList) throws Exception {SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMdd hhmmss");Workbook wb = new XSSFWorkbook();//标题⾏抽出字段String[] title = {"序号","学号", "姓名", "性别", "⼊学时间", "住址", "⼿机号", "其他信息"};//设置sheet名称，并创建新的sheet对象String sheetName = "学⽣信息⼀览";Sheet stuSheet = wb.createSheet(sheetName);//获取表头⾏Row titleRow = stuSheet.createRow(0);//创建单元格，设置style居中，字体，单元格⼤⼩等CellStyle style = wb.createCellStyle();Cell cell = null;//把已经写好的标题⾏写⼊excel⽂件中for (int i = 0; i < title.length; i++) {cell = titleRow.createCell(i);cell.setCellValue(title[i]);cell.setCellStyle(style);}//把从数据库中取得的数据⼀⼀写⼊excel⽂件中Row row = null;for (int i = 0; i < stuList.size(); i++) {//创建list.size()⾏数据row = stuSheet.createRow(i + 1);//把值⼀⼀写进单元格⾥//设置第⼀列为⾃动递增的序号row.createCell(0).setCellValue(i + 1);row.createCell(1).setCellValue(stuList.get(i).getStuId());row.createCell(2).setCellValue(stuList.get(i).getStuName());row.createCell(3).setCellValue(stuList.get(i).getGender());//把时间转换为指定格式的字符串再写⼊excel⽂件中if (stuList.get(i).getEnterTime() != null) {row.createCell(4).setCellValue(sdf.format(stuList.get(i).getEnterTime()));}row.createCell(5).setCellValue(stuList.get(i).getAddress());row.createCell(6).setCellValue(stuList.get(i).getPhone());row.createCell(6).setCellValue(stuList.get(i).getPhone());row.createCell(7).setCellValue(stuList.get(i).getOtherInfo());}//设置单元格宽度⾃适应，在此基础上把宽度调⾄1.5倍for (int i = 0; i < title.length; i++) {stuSheet.autoSizeColumn(i, true);stuSheet.setColumnWidth(i, stuSheet.getColumnWidth(i) * 15 / 10);}//获取配置⽂件中保存对应excel⽂件的路径，本地也可以直接写成F：excel/stuInfoExcel路径String folderPath = ResourceBundle.getBundle("systemconfig").getString("downloadFolder") + File.separator + "stuInfoExcel";//创建上传⽂件⽬录File folder = new File(folderPath);//如果⽂件夹不存在创建对应的⽂件夹if (!folder.exists()) {folder.mkdirs();}//设置⽂件名String fileName = sdf1.format(new Date()) + sheetName + ".xlsx";String savePath = folderPath + File.separator + fileName;// System.out.println(savePath);OutputStream fileOut = new FileOutputStream(savePath);wb.write(fileOut);fileOut.close();//返回⽂件保存全路径return savePath;} 注意事项：1. 这⾥的数据使⽤的是数据库中的测试数据，⽣产环境数据字段会更多，数据会更复杂，要根据不同的数据进⾏处理。
Java导出Excel完整例子+完整代码，使用easypoi导出Excel+通用工具类前言：在之前写Excel导入导出功能的时候，使用更多的可能是apache 的poi，相信用过poi的应该都会感觉poi使用起来还是有点点麻烦的，所以，如果你是：不太熟悉poi的、不想写太多重复太多的、只是简单的导入导出的。
那么相信我，使用easypoi绝对很适合。
easypoi，正如同它的名字一样，主打的功能就是容易，让一个没见接触过poi的人员，就可以很方便的写出Excel导出、Excel导入，通过简单的注解就可以完成以前复杂的写法。
so，今天就用easypoi来做一个导出Excel的完整案例，项目是SpringBoot的项目一、引入pom依赖<!-- excle导入导出依赖包 start --><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>3.2.0</version></dependency><de pendency><groupId>cn.afterturn</groupId><artifactId>easyp oi-web</artifactId><version>3.2.0</version></dependency><de pendency><groupId>cn.afterturn</groupId><artifactId>easyp oi-annotation</artifactId><version>3.2.0</version></dependenc y><!-- excle导入导出依赖包 end -->注意：有的文章或者博客可能说可以不用上边三个依赖这么麻烦，直接用 easypoi-spring-boot-starter 一个依赖就可以了但是，用easypoi-spring-boot-starter 这个可能会导致你的springboot项目jar包依赖冲突，所以还是推荐使用上边的三个依赖二、定义需要导出的实体对象Userimport cn.afterturn.easypoi.excel.annotation.Excel;import cn .afterturn.easypoi.excel.annotation.ExcelTarget;import lombok.D ata;import java.io.Serializable;import java.util.Date;/*** 导出对应的实体类** @author caoju*/@ExcelTarget("user")@Datapublic class User implements Serializable {@Excel(name = "id",width=7)private In teger id;@Excel(name = "姓名",width=15)private String name;@Excel(name = "性别",replace = { "男_1", "女_2" },width=10)private Integer sex;@Excel(name = "创建时间",exportFormat = "yyyy-MM-dd HH:mm:ss",width=25)private Date createTime;}三、Excel导出工具类和导出自定义样式类不要看下面的两个类这么长，好像多复杂似的别担心！就是个工具类而已啦~你懂的，直接ctrl c、ctrl v（我也懂的，热爱编程的你，最喜欢的就是这个 c v大法，简单粗暴，不多bibi！hia hia hia~~~）import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn. afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.e asypoi.excel.entity.TemplateExportParams;import cn.afterturn.ea sypoi.excel.entity.enmus.ExcelType;import com.xiaoleilu.hutool.d ate.DateUtil;import com.xiaoleilu.hutool.util.StrUtil;import lomb ok.extern.slf4j.Slf4j;import ermodel.Workbo ok;import javax.servlet.http.HttpServletResponse;import java.io.I OException;import java.io.OutputStream;import .URLEnc oder;import java.util.ArrayList;import java.util.List;import java.uti l.Map;/*** Excel导出工具类** @author caoju*/@Slf4jpublic class ExcelUtils {/** 允许导出的最大条数 */private static final Integer EXPORT_EXCEL_MAX_NUM = 10000;/*** 导出Excel** @param workbook workbook流* @param fileName 文件名* @param response 响应*/public static void exportExcel(Workbook workbook, String file Name, HttpServletResponse response) {//给文件名拼接上日期fileName = fileName + StrUtil.UNDERLINE + DateUtil.today();//输出文件try (OutputStream out = response.getOutputStream()) {//获取文件名并转码String name = URLEncoder.encode(fileName, "UTF-8");//设置编码response.setCharacterEncoding("UTF-8");//设置http响应头告诉浏览器，以下载的形式处理响应的数据response.setContentType("application/force-download");//设置下载文件的默认名称response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xlsx");//输出表格workbook.write(out);} catch (IOException e) {log.error("文件导出异常,详情如下:", e);} finally {try {if (workbook != null) {//关闭输出流workbook.close();}} catch (IOException e) {log.error("文件导出异常,详情如下:", e);}}}/*** 获取导出的 Workbook对象** @param title 大标题* @param sheetName 页签名* @param object 导出实体* @param list 数据集合* @return Workbook*/public static Workbook getWorkbook(Stri ng title, String sheetName, Class object, List list) {//判断导出数据是否为空if (list == null) {list = new ArrayList<>();}//判断导出数据数量是否超过限定值if (list.size() > EXPORT_EXCEL_MAX_NUM) {title = "导出数据行数超过:" + EXPORT_EXCEL_MAX_NUM + "条,无法导出、请添加查询条件后再进行导出!";list = new ArrayList<>();}//导出参数ExportParams exportParams = new ExportParams(title, sheetNa me, ExcelType.XSSF);//设置导出样式，可以不设置使用默认的样式，这里用的是自定义样式exportParams.setStyle(ExcelStyle.class);//输出Workbook流return ExcelExportUtil.exportExcel(exportParams, object, list);}/** * 获取导出的 Workbook对象** @param path 模板路径* @param map 导出内容map* @return Workbook*/public static Workbook getWorkboo k(String path, Map<String, Object> map) {//获取导出模板TemplateExportParams params = new TemplateExportParams(p ath);//设置导出样式params.setStyle(ExcelStyle.class);//输出Workbook流return ExcelExportUtil.exportExcel(params, map);}} import cn.afterturn.easypoi.excel.entity.params.ExcelExportE ntity;import cn.afterturn.easypoi.excel.entity.params.ExcelForEac hParams;import cn.afterturn.easypoi.excel.export.styler.ExcelExp ortStylerDefaultImpl;import cn.afterturn.easypoi.excel.export.sty ler.IExcelExportStyler;import ermodel.*;/*** Excel导出自定义样式类** @author caoju*/public class ExcelStyle extends ExcelExportSt ylerDefaultImpl implements IExcelExportStyler {private static fin al short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFor mat("TEXT");private static final short FONT_SIZE_TEN = 10;privat e static final short FONT_SIZE_ELEVEN = 11;private static final sh ort FONT_SIZE_TWELVE = 12;/*** 大标题样式*/private CellStyle headerStyle;/*** 每列标题样式*/private CellStyle titleStyle;/*** 数据行样式*/private CellStyle styles;public ExcelStyle(Workbook workbook) {super(workbook);this.init(workbook);}/*** 初始化样式** @param workbook*/private void init(Workbook workbook) {/ /大标题样式this.headerStyle = initHeaderStyle(workbook);//每列标题样式this.titleStyle = initTitleStyle(workbook);//数据行样式this.styles = initStyles(workbook);}/*** 大标题样式** @param color* @return*/@Overridepublic CellStyle getHead erStyle(short color) {return headerStyle;}/*** 每列标题样式** @param color* @return*/@Overridepublic CellStyle getTitleS tyle(short color) {return titleStyle;}/*** 数据行样式** @param parity 可以用来表示奇偶行* @param entity 数据内容* @return 样式*/@Overridepublic CellStyle getStyles(boolean parity, ExcelExpo rtEntity entity) {return styles;}/*** 获取样式方法** @param dataRow 数据行* @param obj 对象* @param data 数据*/@Overridepublic CellStyle getStyles(Cell cell, int dataRow, Exc elExportEntity entity, Object obj, Object data) {return getStyles(t rue, entity);}/*** 模板使用的样式设置*/@Overridepublic CellStyle getT emplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {return null;}/*** 初始化--大标题样式** @param workbook* @return*/private CellStyle initHeaderStyl e(Workbook workbook) {CellStyle style = getBaseCellStyle(work book);style.setFont(getFont(workbook, FONT_SIZE_TWELVE, tru e));return style;}/*** 初始化--每列标题样式** @param workbook* @return*/private CellStyle initTitleStyle( Workbook workbook) {CellStyle style = getBaseCellStyle(workbo ok);style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false)); //背景色 POI IndexedColors 编码与颜色枚举类对照 : https:///ju_362204801/article/details/1060791 71style.setFillForegroundColor(IndexedColors.PALE_BLUE.getInd ex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);re turn style;}/*** 初始化--数据行样式** @param workbook* @return*/private CellStyle initStyles(Wor kbook workbook) {CellStyle style = getBaseCellStyle(workbook); style.setFont(getFont(workbook, FONT_SIZE_TEN, false));style.se tDataFormat(STRING_FORMAT);return style;}/*** 基础样式** @return*/private CellStyle getBaseCellStyle(Workbook workb ook) {CellStyle style = workbook.createCellStyle();//下边框style.setBorderBottom(BorderStyle.THIN);//左边框style.setBorderLeft(BorderStyle.THIN);//上边框style.setBorderTop(BorderStyle.THIN);//右边框style.setBorderRight(BorderStyle.THIN);//水平居中style.setAlignment(HorizontalAlignment.CENTER);//上下居中style.setVerticalAlignment(VerticalAlignment.CENTER);//设置自动换行style.setWrapText(true);return style;}/*** 字体样式** @param size 字体大小* @param isBold 是否加粗* @return*/private Font getFont(Workbook workbook, short siz e, boolean isBold) {Font font = workbook.createFont();//字体样式font.setFontName("宋体");//是否加粗font.setBold(isBold);//字体大小font.setFontHeightInPoints(size);return font;}} 再说一下哈，上边面两个类虽然很长，但就是个工具类而已啦~ 不多说！你懂的！直接ctrl c、ctrl v四、测试导出方法的Controllerimport mon.collect.Lists;import com.qtyc.u tils.ExcelUtils;import org.springframework.web.bind.annotation. RequestMapping;import org.springframework.web.bind.annotat ion.RequestMethod;import org.springframework.web.bind.anno tation.RestController;import javax.servlet.http.HttpServletRespo nse;import java.util.Date;import java.util.List;/*** 导出Excel测试Controller** @author caoju*/@RestControllerpublic class ExcelE xportController {/*** 导出Excel测试*/@RequestMapping(value = "/exportExcelTest", method = Req uestMethod.GET)public void exportExcelTest(HttpServletRespon se response) {List<User> personList = this.findUser();ExcelUtils.e xportExcel(ExcelUtils.getWorkbook("用户信息列表", "用户信息", User.class, personList), "用户信息列表导出", response);}/*** 模拟数据库查询返回用户信息*/private List<User> findUser() {List<User> list = Lists.newArray List();User user = new User();user.setId(10);user.setName("张三");user.setSex(1);user.setCreateTime(new Date());list.add(user);Us er user1 = new User();user1.setId(26);user1.setName("李四");user1.setSex(1);user1.setCreateTime(new Date());list.add(user1 );User user2 = new User();user2.setId(28);user2.setName("王丽");user2.setSex(2);user2.setCreateTime(new Date());list.add(user2 );return list;}}五、测试浏览器输入：可以看到浏览器左下角弹出了下载窗口，自动下载了Excel打开下载的文件夹可以看到刚才下载的Excel打开Excel看下里边的内容可以看到咱们想要导出的内容已经导出了，其中的标题，表头，以及每行每列的背景颜色，字体等等都可以通过代码根据自己的需求来自定义的。
JAVA实现Excel导⼊导出以及excel样式设置JAVA实现Excel导⼊/导出以及excel样式设置图2.1 POI的⽬录结构 POI使⽤初步 POI提供给⽤户使⽤的对象在ermodel包中,主要部分包括Excel对象、样式和格式，还有辅助操作等。
最主要的⼏个对象如表3.1所⽰： 表3.1 POI主要对象POI对象名称对应的Excel对象HSSFWorkbook⼯作簿HSSFSheet⼯作表HSSFRow⾏HSSFCell单元格 下⾯我们来看如下的例⼦，使⽤表3.1中的对象在程序的当前⽬录下创建⼀个Excel⽂件test.xls，在第⼀个单元格中写⼊内容，然后读出第⼀个单元格的内容。
完整的程序如下：import ermodel.HSSFWorkbook;import ermodel.HSSFSheet;import ermodel.HSSFRow;import ermodel.HSSFCell;import java.io.FileOutputStream;import java.io.FileInputStream;public class CreateXL{ public static String xlsFile="test.xls"; //产⽣的Excel⽂件的名称 public static void main(String args[]) { try { HSSFWorkbook workbook = new HSSFWorkbook(); //产⽣⼯作簿对象 HSSFSheet sheet = workbook.createSheet(); //产⽣⼯作表对象 //设置第⼀个⼯作表的名称为firstSheet //为了⼯作表能⽀持中⽂，设置字符编码为UTF_16 workbook.setSheetName(0,"firstSheet",HSSFWorkbook.ENCODING_UTF_16); //产⽣⼀⾏ HSSFRow row = sheet.createRow((short)0); //产⽣第⼀个单元格 HSSFCell cell = row.createCell((short) 0); //设置单元格内容为字符串型 cell.setCellType(HSSFCell.CELL_TYPE_STRING); //为了能在单元格中写⼊中⽂，设置字符编码为UTF_16。
Java中导⼊、导出Excel⼀、介绍当前B/S模式已成为应⽤开发的主流，⽽在企业办公系统中，常常有客户这样⼦要求：你要把我们的报表直接⽤Excel打开(电信系统、银⾏系统)。
或者是：我们已经习惯⽤Excel 打印。
这样在我们实际的开发中，很多时候需要实现导⼊、导出Excel的应⽤。
⽬前，⽐较常⽤的实现Java导⼊、导出Excel的技术有两种Jakarta POI和Java Excel下⾯我就分别讲解⼀下如何使⽤这两个技术实现导⼊、导出Excel⼆、使⽤Jakarta POI导⼊、导出ExcelJakarta POI 是⼀套⽤于访问微软格式⽂档的Java API。
Jakarta POI有很多组件组成，其中有⽤于操作Excel格式⽂件的HSSF和⽤于操作Word的HWPF，在各种组件中⽬前只有⽤于操作Excel的HSSF相对成熟。
官⽅主页，API⽂档2.1 环境配置2.1.1下载jar官⽅下载：这⾥可以下载到它的最新版本和⽂档，⽬前最新版本是3.7，这⾥使⽤⽐较稳定的3.6版。
2.1.2加⼊jar包将根⽬录下的poi-3.6-20091214.jar和Lib⽬录下三个通⽤包 commons-logging-1.1.jar junit-3.8.1.jar log4j-1.2.13.jar拷贝到项⽬的Lib下2.2 Jakarta POI HSSF API组件HSSF（⽤于操作Excel的组件）提供给⽤户使⽤的对象在ermodel包中,主要部分包括Excel对象，样式和格式，还有辅助操作。
有以下⼏种对象：常⽤组件：HSSFWorkbook excel的⽂档对象HSSFSheet excel的表单HSSFRow excel的⾏HSSFCell excel的格⼦单元HSSFFont excel字体HSSFDataFormat ⽇期格式HSSFHeader sheet头HSSFFooter sheet尾（只有打印的时候才能看到效果）样式：HSSFCellStyle cell样式辅助操作包括：HSSFDateUtil ⽇期HSSFPrintSetup 打印HSSFErrorConstants 错误信息表2.3 基本操作步骤⾸先，理解⼀下⼀个Excel的⽂件的组织形式，⼀个Excel⽂件对应于⼀个workbook(HSSFWorkbook)，⼀个workbook可以有多个sheet（HSSFSheet）组成，⼀个sheet是由多个row（HSSFRow）组成，⼀个row是由多个cell（HSSFCell）组成。
Java使⽤Apache.POI中HSSFWorkbook导出到Excel的实现⽅法使⽤Apache.POI中HSSFWorkbook导出到Excel，具体内容如下所⽰：1.引⼊Poi依赖（3.12）依赖如下：<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.12</version></dependency>2.创建实体类（User.java）package com.kd.nm.entity.pojo;/*** 实体类（User）** author ⼩⾠哥哥*/public class User {// ⽤户编号private String userNo;// ⽤户名称private String userName;// 年龄private String age;// ⽆参构造public User() {}// 有参构造public User(String userNo, String userName, String age) {erNo = userNo;erName = userName;this.age = age;}// get与set⽅法进⾏封装public String getUserNo() {return userNo;}public void setUserNo(String userNo) {erNo = userNo;}public String getUserName() {return userName;}public void setUserName(String userName) {erName = userName;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}// 重新toString⽅法@Overridepublic String toString() {return "User{" +"userNo='" + userNo + '\'' +", userName='" + userName + '\'' +", age='" + age + '\'' +'}';}}3.Excel相关⼯具类（ExcelUtil、ReflectUtil）package com.kd.nm.util;import java.util.ArrayList;import java.util.List;import java.util.Map;import ermodel.*;/*** Description : Excel相关⼯具类** @author: ⼩⾠哥哥**/public class ExcelUtil {/*** ⽣成excel表格* @param heads 表头内容* @param data 数据内容* @return*/public static HSSFWorkbook creatExcel(Map<String, String> heads, List data) {// 声明⼀个⼯作薄HSSFWorkbook workbook = new HSSFWorkbook();// ⽣成⼀个表格HSSFSheet sheet = workbook.createSheet();// ⽣成标题⾏样式HSSFCellStyle headStyle = creatStyle(workbook, (short) 14);// ⽣成表格内容样式HSSFCellStyle bodyStyle = creatStyle(workbook, (short) 10);// 标题元素List<String> keys = new ArrayList<String>(heads.keySet());// 像素单位short px = 1000;// 设置列宽for (int columnIndex = 0; columnIndex < keys.size(); columnIndex++) {sheet.setColumnWidth(columnIndex, 6 * px);}// ⽣成表格for (int rowNum = 0; rowNum <= data.size(); rowNum++) {// 创建⾏HSSFRow row = sheet.createRow(rowNum);for (int cellNum = 0; cellNum < keys.size(); cellNum++) {// 创建列HSSFCell cell = row.createCell(cellNum);// 标题if (rowNum == 0) {cell.setCellStyle(headStyle);cell.setCellValue(heads.get(keys.get(cellNum)));} else { // 内容cell.setCellStyle(bodyStyle);// 通过反射获取cell.setCellValue(ReflectUtil.getValue(keys.get(cellNum), data.get(rowNum - 1))); }}}return workbook;}/*** ⽣成样式* @param workbook* @param size* @return*/public static HSSFCellStyle creatStyle(HSSFWorkbook workbook, short size) { HSSFCellStyle style = workbook.createCellStyle();style.setAlignment((HSSFCellStyle.ALIGN_CENTER));style.setVerticalAlignment((HSSFCellStyle.VERTICAL_CENTER));HSSFFont font = workbook.createFont();font.setFontHeightInPoints(size);font.setFontName("微软雅⿊");style.setFont(font);style.setBorderBottom(HSSFCellStyle.BORDER_THIN);style.setBorderTop(HSSFCellStyle.BORDER_THIN);style.setBorderRight(HSSFCellStyle.BORDER_THIN);style.setBorderLeft(HSSFCellStyle.BORDER_THIN);return style;}}package com.kd.nm.util;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.util.ReflectionUtils;import java.beans.PropertyDescriptor;import ng.reflect.Method;/*** 反射⼯具包** @author: ⼩⾠哥哥*/public class ReflectUtil {private static final Logger logger = LoggerFactory.getLogger(ReflectUtil.class); public static String getValue(String key, Object obj) {String value = "";try {// 获取当前属性PropertyDescriptor pd = new PropertyDescriptor(key, obj.getClass());// 获取get⽅法Method getMd = pd.getReadMethod();value = getMd.invoke(obj).toString();} catch (Exception e) {logger.error("获取内容失败！");e.printStackTrace();}return value;}public static void setValue(String key, String value, Object obj) {try {// 获取当前属性PropertyDescriptor pd = new PropertyDescriptor(key, obj.getClass());// 获取set⽅法Method writeMd = pd.getWriteMethod();writeMd.invoke(obj, value);} catch (Exception e) {logger.error("设置内容失败！");e.printStackTrace();}}}4.后端控制器代码@RequestMapping(value = "/exportExcel",method = RequestMethod.GET,produces = "application/json")public void exportExcel(HttpServletResponse httpServletResponse) throws IOException {// 表头内容（可在前端设置，通过参数传递进来） Key是实体类的属性值，value是表头的lableMap<String,String> head = new HashMap<>();head.put("userNo","⽤户编号");head.put("userName","⽤户名称");head.put("age","年龄");// 表格数据内容,模拟数据库查询出来的数据List<User> data = new ArrayList<>();data.add(new User("1","⼩⾠哥哥","18"));data.add(new User("2","⼩猪妹妹","18"));data.add(new User("3","⼤猪哥哥","18"));// ⽣成⼯作薄HSSFWorkbook hssfWorkbook = ExcelUtil.creatExcel(head, data);// 定义⽂件名String fileName = "导出Excel表格";httpServletResponse.setHeader("Cache-Control", "max-age=0");httpServletResponse.setContentType("application/vnd.ms-excel");httpServletResponse.addHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"),"ISO-8859-1") + ".xls");OutputStream outputStream = httpServletResponse.getOutputStream();hssfWorkbook.write(outputStream);outputStream.flush();outputStream.close();}5.访问映射地址接⼝访问：到此这篇关于Java使⽤Apache.POI中HSSFWorkbook导出到Excel的实现⽅法的⽂章就介绍到这了,更多相关Apache.POI中HSSFWorkbook导出到Excel内容请搜索以前的⽂章或继续浏览下⾯的相关⽂章希望⼤家以后多多⽀持！。
Java⽤POI解析excel并获取所有单元格数据 1.导⼊POI相关jar包 org.apache.poi jar 2.代码⽰例public List getAllExcel(File file, String tableName, String fname, String enterpriseId, String reportId, String projectId) throws FileNotFoundException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchMethodException, SecurityException, IllegalArgumentException, InvocationTargetException, ParseException { List listt = new ArrayList(); try { FileInputStream fis = new FileInputStream(file); Workbook workbook = null; if (fname.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(fis); } else if (fname.toLowerCase().endsWith("xls")) { workbook = new HSSFWorkbook(new POIFSFileSystem(fis)); } int numberOfSheets = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { Sheet sheet = workbook.getSheetAt(i); for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) { // 获取每⾏ XSSFRow row = (XSSFRow) sheet.getRow(j); if(row!=null){ List list = new ArrayList(); for (int k = 0; k < sheet.getRow(0).getPhysicalNumberOfCells(); k++) { // 获取每个单元格 Cell cell = row.getCell(k); if (cell == null) { list.add(""); continue; } switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: list.add(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { list.add(cell.getDateCellValue()); } else { list.add(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN: list.add(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: list.add(cell.getCellFormula()); break; default: list.add(""); break; } } listt.add(getByReflect(tableName, list, enterpriseId,reportId, projectId)); } } } fis.close(); } catch (IOException e) { e.printStackTrace(); } return listt;}。
Java利⽤poi⽣成excel表格所需jar包，如下所⽰写⼀个excel⼯具类 ExcelUtils .javaimport ng.reflect.Field;import java.util.Iterator;import java.util.List;import ermodel.HSSFCell;import ermodel.HSSFCellStyle;import ermodel.HSSFDataFormat;import ermodel.HSSFRow;import ermodel.HSSFSheet;import ermodel.HSSFWorkbook;public class ExcelUtils {/*** 导出excel* @param headerName （excel列名称）* @param headerKey （导出对象属性名）* @param sheetName （excel 页签名称）* @param dataList （导出的数据）* @return*/public static HSSFWorkbook createExcel(String[] headerName, String[] headerKey, String sheetName, List dataList) { try {if (headerKey.length <= 0) {return null;}if (dataList.size() <= 0) {return null;}HSSFWorkbook wb = new HSSFWorkbook();HSSFSheet sheet;if ((sheetName == null) || (sheetName.equals("")))sheet = wb.createSheet("Sheet1");else {sheet = wb.createSheet(sheetName);}HSSFRow row = sheet.createRow(0);HSSFCellStyle style = wb.createCellStyle();style.setAlignment((short)2);HSSFCell cell = null;if (headerName.length > 0) {for (int i = 0; i < headerName.length; i++) {cell = row.createCell(i);cell.setCellValue(headerName[i]);cell.setCellStyle(style);}}int n = 0;HSSFCellStyle contextstyle = wb.createCellStyle();contextstyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00_);(#,##0.00)"));HSSFCellStyle contextstyle1 = wb.createCellStyle();HSSFDataFormat format = wb.createDataFormat();contextstyle1.setDataFormat(format.getFormat("@"));HSSFCell cell0 = null;HSSFCell cell1 = null;for (Iterator localIterator = dataList.iterator(); localIterator.hasNext();) {Object obj = localIterator.next();Field[] fields = obj.getClass().getDeclaredFields();row = sheet.createRow(n + 1);for (int j = 0; j < headerKey.length; j++) {if (headerName.length <= 0) {cell0 = row.createCell(j);cell0.setCellValue(headerKey[j]);cell0.setCellStyle(style);}for (int i = 0; i < fields.length; i++) {if (fields[i].getName().equals(headerKey[j])) {fields[i].setAccessible(true);if (fields[i].get(obj) == null) {row.createCell(j).setCellValue("");break;}if ((fields[i].get(obj) instanceof Number)) {cell1 = row.createCell(j);cell1.setCellType(0);cell1.setCellStyle(contextstyle);cell1.setCellValue(Double.parseDouble(fields[i].get(obj).toString())); break;}if ("".equals(fields[i].get(obj))) {cell1 = row.createCell(j);cell1.setCellStyle(contextstyle1);row.createCell(j).setCellValue("");cell1.setCellType(1);break;}row.createCell(j).setCellValue(fields[i].get(obj).toString());break;}}}n++;}for (int i = 0; i < headerKey.length; i++) {sheet.setColumnWidth(i, headerKey[i].getBytes().length*2*256);}HSSFWorkbook localHSSFWorkbook1 = wb;return localHSSFWorkbook1;} catch (Exception e) {e.printStackTrace();return null;} finally {}}}添加⼀个vo，studentVo.javapublic class StudentVo {private int id;private String sex;private String name;private String grade;public String getId() {return id;}public void setId(String id) {this.id = id;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getName() {return name;}public void setName(String name) { = name;}public String getGrade() {return grade;}public void setGrade(String grade) {this.grade = grade;}}在controller类中添加导出excel接⼝，如下所⽰/*** 国⽹数据导出* @param request* @param response*/@RequestMapping("/exportGWDataManageList")public void exportGWDataManageList(HttpServletRequest request, HttpServletResponse response){ try{List<StudentVo> voList = new ArrayList<StudentVo>();StudentVo vo = new StudentVo();vo.setId("1");vo.setSex("男");vo.setName("张三");vo.setGrade("⼆年级");voList.add(vo);vo = new StudentVo();vo.setId("2");vo.setSex("⼥");vo.setName("李四");vo.setGrade("⼀年级");voList.add(vo);vo = new StudentVo();vo.setId("3");vo.setSex("男");vo.setName("王五");vo.setGrade("三年级");voList.add(vo);String[] headerName = { "序号","性别", "姓名", "年级"};String[] headerKey = { "id","sex", "name", "grade"};HSSFWorkbook wb = ExcelUtils.createExcel(headerName, headerKey, "年数据管理", voList);if (wb == null) {return;}response.setContentType("application/vnd.ms-excel");SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");Date date = new Date();String str = sdf.format(date);String fileName = "学⽣信息管理" + str;response.setHeader("Content-disposition","attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls");OutputStream ouputStream = response.getOutputStream();ouputStream.flush();wb.write(ouputStream);ouputStream.close();} catch (Exception e) {e.printStackTrace();}}页⾯只有⼀个⽣成excel按钮，如下所⽰点击按钮⽣成excel，内容如下所⽰。
poi 拷贝行合并单元格POI（Apache POI）是一个用于操作Microsoft Office文件的开源Java库。
在很多业务场景中，我们常常需要对Excel文件进行操作，例如读取数据、写入数据、格式化表格等。
其中，拷贝行和合并单元格是Excel操作中常见且有实用性的功能。
在本文中，我们将一步一步地介绍如何使用POI库来实现拷贝行和合并单元格的功能。
第一步：导入POI库首先，我们需要在Java工程中引入POI库。
POI库的最新稳定版本为5.0.0。
你可以在第二步：创建Excel文件在使用POI库操作Excel文件之前，我们首先需要创建一个Excel文件。
可以使用POI库提供的Workbook接口的实现类来创建一个新的工作簿对象。
以下是一个示例代码：javaimport ermodel.Workbook;import ermodel.XSSFWorkbook;import java.io.FileOutputStream;import java.io.IOException;public class ExcelDemo {public static void main(String[] args) {Workbook workbook = new XSSFWorkbook(); 创建新的工作簿对象 try {FileOutputStream fileOutputStream = new FileOutputStream("sample.xlsx");workbook.write(fileOutputStream); 将工作簿写入输出流fileOutputStream.close();workbook.close(); 关闭工作簿} catch (IOException e) {e.printStackTrace();}}}在上面的示例代码中，我们创建了一个名为"sample.xlsx"的新Excel文件，并将其保存在当前工作目录中。
