java用XSSFWorkbook实现读写Excel

你的名字 2023-10-02 13:43 15阅读 0赞

> 写在最前面：  
> 　　　　使用的是JAVA POI实现的导出Excel；  
> 　　　　POI 提供了对2003版本的Excel的支持 ---- HSSFWorkbook  
> 　　　　POI 提供了对2007版本以及更高版本的支持 ---- XSSFWorkbook

引入相关依赖：

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.1.2</version>
            </dependency>

/**
         * 读取Excel文件的内容
         * @param inputStream excel文件，以InputStream的形式传入
         * @param sheetName sheet名字
         * @return 以List返回excel中内容
         */
        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("")) {
        
                // 默认取第一个子表
                xssfSheet = xssfWorkbook.getSheetAt(0);
            } else {
        
                xssfSheet = xssfWorkbook.getSheet(sheetName);
            }
    
            List<Map<String, String>> list = new ArrayList<Map<String, String>>();
    
            //定义行
            //默认第一行为标题行，index = 0
            XSSFRow titleRow = xssfSheet.getRow(0);
    
            //循环取每行的数据
            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>();
                //循环取每个单元格(cell)的数据
                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;
        }
    
        /**
         * 把单元格的内容转为字符串
         * @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();
            }
        }
    
        /**
         * 把内容写入Excel
         * @param list 传入要写的内容，此处以一个List内容为例，先把要写的内容放到一个list中
         * @param outputStream 把输出流怼到要写入的Excel上，准备往里面写数据
         */
        public static void writeExcel(List<List> list, OutputStream outputStream) {
        
            //创建工作簿
            XSSFWorkbook xssfWorkbook = null;
            xssfWorkbook = new XSSFWorkbook();
    
            //创建工作表
            XSSFSheet xssfSheet;
            xssfSheet = xssfWorkbook.createSheet();
    
            //创建行
            XSSFRow xssfRow;
    
            //创建列，即单元格Cell
            XSSFCell xssfCell;
    
            //把List里面的数据写到excel中
            for (int i=0;i<list.size();i++) {
        
                //从第一行开始写入
                xssfRow = xssfSheet.createRow(i);
                //创建每个单元格Cell，即列的数据
                List sub_list =list.get(i);
                for (int j=0;j<sub_list.size();j++) {
        
                    xssfCell = xssfRow.createCell(j); //创建单元格
                    xssfCell.setCellValue((String)sub_list.get(j)); //设置单元格内容
                }
            }
    
            //用输出流写到excel
            try {
        
                xssfWorkbook.write(outputStream);
                outputStream.flush();
                outputStream.close();
            }catch (IOException e) {
        
                e.printStackTrace();
            }
    
        }

附：把一个Map中的所有键和值分别放到一个list中，再把这两个list整个放到一个大的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;
        }

--------------------

#### 导出接口： ####

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 导出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");
            //让服务器告诉浏览器它发送的数据属于什么文件类型
            response.setHeader("content-Type", "application/vnd.ms-excel");
            //当Content-Type 的类型为要下载的类型时 , 这个信息头会告诉浏览器这个文件的名字和类型。
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode("b.xlsx", "UTF-8"));
    
            try {
        
                workbook.write(response.getOutputStream());
            } catch (IOException e) {
        
                e.printStackTrace();
            }
        }
    
    }

访问接口：  
![在这里插入图片描述][3987a10c606b40e1975ca0277fc20fee.png]  
成功下载：  
![在这里插入图片描述][watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQXJjaGllX2phdmE_size_20_color_FFFFFF_t_70_g_se_x_16]

[3987a10c606b40e1975ca0277fc20fee.png]: https://img-blog.csdnimg.cn/3987a10c606b40e1975ca0277fc20fee.png
[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBAQXJjaGllX2phdmE_size_20_color_FFFFFF_t_70_g_se_x_16]: https://img-blog.csdnimg.cn/ab59989644f7482bb6c45521e5209c4e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQXJjaGllX2phdmE=,size_20,color_FFFFFF,t_70,g_se,x_16