寫(xiě)在最前面：
　　　　使用的是JAVA POI實(shí)現(xiàn)的導(dǎo)出Excel；
　　　　POI 提供了對(duì)2003版本的Excel的支持 ---- HSSFWorkbook
　　　　POI 提供了對(duì)2007版本以及更高版本的支持 ---- XSSFWorkbook

引入相關(guān)依賴：

<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency> /*** 讀取Excel文件的內(nèi)容* @param inputStream excel文件，以InputStream的形式傳入* @param sheetName sheet名字* @return 以List返回excel中內(nèi)容*/public static List<Map<String, String>> readExcel(InputStream inputStream, String sheetName) {//定義工作簿XSSFWorkbook xssfWorkbook = null;try {xssfWorkbook = new XSSFWorkbook(inputStream);} catch (Exception e) {System.out.println("Excel data file cannot be found!");}//定義工作表XSSFSheet xssfSheet;if (sheetName.equals("")) {// 默認(rèn)取第一個(gè)子表xssfSheet = xssfWorkbook.getSheetAt(0);} else {xssfSheet = xssfWorkbook.getSheet(sheetName);}List<Map<String, String>> list = new ArrayList<Map<String, String>>();//定義行//默認(rèn)第一行為標(biāo)題行，index = 0XSSFRow titleRow = xssfSheet.getRow(0);//循環(huán)取每行的數(shù)據(jù)for (int rowIndex = 1; rowIndex < xssfSheet.getPhysicalNumberOfRows(); rowIndex++) {XSSFRow xssfRow = xssfSheet.getRow(rowIndex);if (xssfRow == null) {continue;}Map<String, String> map = new LinkedHashMap<String, String>();//循環(huán)取每個(gè)單元格(cell)的數(shù)據(jù)for (int cellIndex = 0; cellIndex < xssfRow.getPhysicalNumberOfCells(); cellIndex++) {XSSFCell titleCell = titleRow.getCell(cellIndex);XSSFCell xssfCell = xssfRow.getCell(cellIndex);map.put(getString(titleCell),getString(xssfCell));}list.add(map);}return list;}/*** 把單元格的內(nèi)容轉(zhuǎn)為字符串* @param xssfCell 單元格* @return 字符串*/public static String getString(XSSFCell xssfCell) {if (xssfCell == null) {return "";}if (xssfCell.getCellTypeEnum() == CellType.NUMERIC) {return String.valueOf(xssfCell.getNumericCellValue());} else if (xssfCell.getCellTypeEnum() == CellType.BOOLEAN) {return String.valueOf(xssfCell.getBooleanCellValue());} else {return xssfCell.getStringCellValue();}}/*** 把內(nèi)容寫(xiě)入Excel* @param list 傳入要寫(xiě)的內(nèi)容，此處以一個(gè)List內(nèi)容為例，先把要寫(xiě)的內(nèi)容放到一個(gè)list中* @param outputStream 把輸出流懟到要寫(xiě)入的Excel上，準(zhǔn)備往里面寫(xiě)數(shù)據(jù)*/public static void writeExcel(List<List> list, OutputStream outputStream) {//創(chuàng)建工作簿XSSFWorkbook xssfWorkbook = null;xssfWorkbook = new XSSFWorkbook();//創(chuàng)建工作表XSSFSheet xssfSheet;xssfSheet = xssfWorkbook.createSheet();//創(chuàng)建行XSSFRow xssfRow;//創(chuàng)建列，即單元格CellXSSFCell xssfCell;//把List里面的數(shù)據(jù)寫(xiě)到excel中for (int i=0;i<list.size();i++) {//從第一行開(kāi)始寫(xiě)入xssfRow = xssfSheet.createRow(i);//創(chuàng)建每個(gè)單元格Cell，即列的數(shù)據(jù)List sub_list =list.get(i);for (int j=0;j<sub_list.size();j++) {xssfCell = xssfRow.createCell(j); //創(chuàng)建單元格xssfCell.setCellValue((String)sub_list.get(j)); //設(shè)置單元格內(nèi)容}}//用輸出流寫(xiě)到exceltry {xssfWorkbook.write(outputStream);outputStream.flush();outputStream.close();}catch (IOException e) {e.printStackTrace();}}

附：把一個(gè)Map中的所有鍵和值分別放到一個(gè)list中，再把這兩個(gè)list整個(gè)放到一個(gè)大的list里面，即 [ [key1,key2,key3…] , [value1,value2,value3…] ]

public static List<List> convertMapToList(Map map) {List<List> list = new ArrayList<List>();List<String> key_list = new LinkedList<String>();List<String> value_list = new LinkedList<String>();Set<Entry<String,String>> set = map.entrySet();Iterator<Entry<String,String>> iter1 = set.iterator();while (iter1.hasNext()) {key_list.add(iter1.next().getKey());}list.add(key_list);Collection<String> value = map.values();Iterator<String> iter2 = value.iterator();while (iter2.hasNext()) {value_list.add(iter2.next());}list.add(value_list);return list;}

---

---

---

---

導(dǎo)出接口：

package com.wqd.dove.demo.export.controller;import com.wqd.dove.demo.export.mapper.UserMapper; import com.wqd.dove.demo.export.pojo.User; import lombok.RequiredArgsConstructor; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController;import javax.servlet.http.HttpServletResponse; import java.io.IOException; import java.io.UnsupportedEncodingException; import java.net.URLEncoder; import java.util.List;/*** @author frank* @description 導(dǎo)出Excel文件控制器* @date 2021/12/18 14:06*/ @RestController @RequiredArgsConstructor @RequestMapping(value = {"/export"}) public class MyExcelExportController {private final UserMapper userMapper;@GetMapping(value = "/excel")public void exportExcel(HttpServletResponse response) throws UnsupportedEncodingException {List<User> list = userMapper.list();XSSFWorkbook workbook = new XSSFWorkbook();XSSFSheet sheet = workbook.createSheet();XSSFRow row0 = sheet.createRow(0);row0.createCell(0).setCellValue("姓名");row0.createCell(1).setCellValue("地址");row0.createCell(2).setCellValue("密碼");row0.createCell(3).setCellValue("電話");for (int i = 0; i < list.size(); i++) {XSSFRow row = sheet.createRow(i + 1);XSSFCell cell = row.createCell(0);cell.setCellValue(list.get(i).getName());XSSFCell cell1 = row.createCell(1);cell1.setCellValue(list.get(i).getAddress());XSSFCell cell2 = row.createCell(2);cell2.setCellValue(list.get(i).getPassword());XSSFCell cell3 = row.createCell(3);cell3.setCellValue(list.get(i).getPhone());}response.setCharacterEncoding("UTF-8");//讓服務(wù)器告訴瀏覽器它發(fā)送的數(shù)據(jù)屬于什么文件類型response.setHeader("content-Type", "application/vnd.ms-excel");//當(dāng)Content-Type 的類型為要下載的類型時(shí) , 這個(gè)信息頭會(huì)告訴瀏覽器這個(gè)文件的名字和類型。response.setHeader("Content-Disposition","attachment;filename=" + URLEncoder.encode("b.xlsx", "UTF-8"));try {workbook.write(response.getOutputStream());} catch (IOException e) {e.printStackTrace();}}}

訪問(wèn)接口：

成功下載：

總結(jié)

以上是生活随笔為你收集整理的java用XSSFWorkbook实现读写Excel的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。