【超级简单】SpringBoot+poi实现excel导入导出

一、pom引入

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>RELEASE</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>RELEASE</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId></dependency>

二、实体类

package com.re.r.pojo;import lombok.Data;import lombok.EqualsAndHashCode;import lombok.experimental.Accessors;import java.io.Serializable;/*** <p>* * </p>** @author * @since -12-21*/@Data@EqualsAndHashCode(callSuper = false)@Accessors(chain = true)public class Teacher implements Serializable {private Integer id;private String teaNo;private String teaName;}

三、ExcelUntil类

package com.re.r.until;/*** 文件: ExcelUtils2* 描述:* 创建时间: -12-28 17:15** @author MSCIWANG*/import com.re.r.pojo.Teacher;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.*;import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;import java.awt.Color;import .URLEncoder;public class ExcelUtils2 {public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {// 告诉浏览器用什么软件可以打开此文件response.setHeader("content-Type", "application/vnd.ms-excel");// 下载文件的默认名称response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));exportExcel(data, response.getOutputStream());}public static void exportExcel(ExcelData data, OutputStream out) throws Exception {XSSFWorkbook wb = new XSSFWorkbook();try {String sheetName = data.getName();if (null == sheetName) {sheetName = "Sheet1";}XSSFSheet sheet = wb.createSheet(sheetName);writeExcel(wb, sheet, data);wb.write(out);} catch(Exception e){e.printStackTrace();}finally{//此处需要关闭 wb 变量out.close();}}private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {int rowIndex = 0;rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);autoSizeColumns(sheet, data.getTitles().size() + 1);}private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {int rowIndex = 0;int colIndex = 0;XSSFFont titleFont = wb.createFont();titleFont.setFontName("simsun");titleFont.setColor(IndexedColors.BLACK.index);XSSFCellStyle titleStyle = wb.createCellStyle();// titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));// titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);titleStyle.setFont(titleFont);//setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));Row titleRow = sheet.createRow(rowIndex);colIndex = 0;for (String field : titles) {Cell cell = titleRow.createCell(colIndex);cell.setCellValue(field);cell.setCellStyle(titleStyle);colIndex++;}rowIndex++;return rowIndex;}private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {int colIndex = 0;XSSFFont dataFont = wb.createFont();dataFont.setFontName("simsun");dataFont.setColor(IndexedColors.BLACK.index);XSSFCellStyle dataStyle = wb.createCellStyle();//dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);dataStyle.setFont(dataFont);// setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));for (List<Object> rowData : rows) {Row dataRow = sheet.createRow(rowIndex);colIndex = 0;for (Object cellData : rowData) {Cell cell = dataRow.createCell(colIndex);if (cellData != null) {cell.setCellValue(cellData.toString());} else {cell.setCellValue("");}cell.setCellStyle(dataStyle);colIndex++;}rowIndex++;}return rowIndex;}private static void autoSizeColumns(Sheet sheet, int columnNumber) {for (int i = 0; i < columnNumber; i++) {int orgWidth = sheet.getColumnWidth(i);sheet.autoSizeColumn(i, true);int newWidth = (int) (sheet.getColumnWidth(i) + 100);if (newWidth > orgWidth) {sheet.setColumnWidth(i, newWidth);} else {sheet.setColumnWidth(i, orgWidth);}}}private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {style.setBorderTop(border);style.setBorderLeft(border);style.setBorderRight(border);style.setBorderBottom(border);style.setBorderColor(XSSFCellBorder.BorderSide.TOP, color);style.setBorderColor(XSSFCellBorder.BorderSide.LEFT, color);style.setBorderColor(XSSFCellBorder.BorderSide.RIGHT, color);style.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM, color);}//导入方法public static List excel2Supplier(MultipartFile file) throws IOException {List list = new ArrayList<>();Teacher supplier = null;try {//1. 创建一个 workbook 对象HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());//2. 获取 workbook 中表单的数量int numberOfSheets = workbook.getNumberOfSheets();for (int i = 0; i < numberOfSheets; i++) {//3. 获取表单HSSFSheet sheet = workbook.getSheetAt(i);//4. 获取表单中的行数int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();for (int j = 0; j < physicalNumberOfRows; j++) {//5. 跳过标题行if (j == 0) {continue;//跳过标题行}//6. 获取行HSSFRow row = sheet.getRow(j);if (row == null) {continue;//防止数据中间有空行}//7. 获取列数int physicalNumberOfCells = row.getPhysicalNumberOfCells();supplier = new Teacher();for (int k = 0; k < physicalNumberOfCells; k++) {HSSFCell cell = row.getCell(k);//按照每一列的不同类型 获取数据switch (cell.getCellType()) {//如果是String类型case STRING:String cellValue = cell.getStringCellValue();switch (k) {//看它是第几列的内容case 0:supplier.setTeaName(cellValue);break;case 1:supplier.setTeaNo(cellValue);break;//省略其他的 case}break;case NUMERIC:int intValue = (int) cell.getNumericCellValue();switch (k) {//看它是第几列的内容case 2:supplier.setId(intValue);break;//省略其他的 case}break;default: {switch (k) {//不同类型的获取函数不同：//日期类型：getDateCellValue()//数字类型：getNumericCellValue()，返回double值//case 18://supplier.setStatus((int) cell.getNumericCellValue());//break;//省略其他的 case}}break;}}list.add(supplier);}}} catch (IOException e) {e.printStackTrace();}//返回搜集好数据的Listreturn list;}}

四、controller测试一下

templates建立上传前端文件

<!doctype html><html lang="en"><head><meta charset="UTF-8"><meta name="viewport"content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0"><meta http-equiv="X-UA-Compatible" content="ie=edge"><title>上传</title></head><body><form action="/ImportSup" method="post" enctype="multipart/form-data"><input type="file" name="file"><input type="submit" value="上传"></form></body></html>

package com.re.r.controller;import com.re.r.pojo.Teacher;import com.re.r.until.ExcelData;import com.re.r.until.ExcelUtils2;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.*;import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import static org.apache.tomcat.util.bcel.classfile.ElementValue.STRING;/*** 文件: UploadController* 描述:* 创建时间: -12-28 9:14** @author MSCIWANG*/@Controllerpublic class UploadController {/*** 导入excel* @param file* @return* @throws IOException*/@PostMapping("/ImportSup")@ResponseBodypublic String importSup(@RequestParam(value = "file") MultipartFile file) throws IOException {//获取数据集合List<Teacher> suppliers = ExcelUtils2.excel2Supplier(file);System.out.println(file.getOriginalFilename());System.out.println(suppliers);return "操作成功";//插入数据库//supplierService.addSuppliers(suppliers);}@RequestMapping("/index1")public String indexHtml() {return "index";}/*** 导出excel使用 ExcelUtils2工具栏* @param response* @throws Exception*/@RequestMapping(value = "/export", method = RequestMethod.GET)public void excel(HttpServletResponse response) throws Exception {ExcelData data = new ExcelData();data.setName("hello");List<String> titles = new ArrayList();titles.add("a1");titles.add("a2");titles.add("a3");data.setTitles(titles);List<List<Object>> rows = new ArrayList();List<Object> row = new ArrayList();row.add("11111111111");row.add("22222222222");row.add("3333333333");rows.add(row);data.setRows(rows);//生成本地// File f = new File("F:\\test.xlsx");// FileOutputStream out = new FileOutputStream(f);// ExcelUtils2.exportExcel(data, out);// out.close();//下载SimpleDateFormat fdate=new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");String fileName=fdate.format(new Date())+".xls";ExcelUtils2.exportExcel(response,fileName,data);}}

上传（导入）测试结果

导出测试结果

简单excel导入导出的就搞定了 ！！！！是不是很简单！！！