java操作Excel的poi的导出Excel表格

1、数据库中建表

具体数据库文件附项目中。

2、利用Maven创建Web项目，配置ssm环境（最后附上项目下载地址）

ssm环境搭建可参考我的另一篇博客：https://blog.youkuaiyun.com/class157/article/details/92077081

项目目录结构如下：

页面效果：

点击导出表格按钮，执行函数，请求后台：

 function  exportExcel(){
        window.open("../room/TestExcel")
    }

效果展示：

点击模板导出表格，执行函数，请求后台：

 function TemplateExcel() {
        window.open("../room/TemplateExcel");
    }

效果展示：

后台部分代码如下（需要完整代码可下载项目）：

package com.scce.controller;

import com.github.pagehelper.PageInfo;
import com.scce.pojo.Room;
import com.scce.service.RoomService;
import com.scce.utils.ExcelUtil;
import com.scce.utils.ResponseUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.util.HashMap;
import java.util.List;

@RestController
@RequestMapping("/room")
public class RoomController {
    @Autowired
    private RoomService roomService;

    //查询房间信息并分页
    @RequestMapping("/getRoom")
    public HashMap getRoom(int pageNum, int pageSize, HttpServletResponse response) throws Exception {
        System.out.println("执行查询房间信息的方法");
        PageInfo<Room> pageInfo = roomService.getRoom(pageNum, pageSize);
        System.out.println(pageInfo.getList());
        HashMap<String, Object> o = new HashMap<String, Object>();
        o.put("rows", pageInfo.getList());
        o.put("total", pageInfo.getTotal());
        return o;
    }

    //导出Excel
    @RequestMapping("/TestExcel")
    public String TestExcel(HttpServletResponse response) throws Exception {
        System.out.println("进入TestExcel");
        try {
            Workbook wb = new HSSFWorkbook();  //创建工作簿
            String headers[] = {"房间号", "价格", "房间类型", "押金", "客房状态"};
            List<Room> list = roomService.getAllRoom();
            ExcelUtil.fillExcelData(list, wb, headers);
            ResponseUtil.export(response, wb, "导出excel.xls");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    //模板导出
    @RequestMapping("/TemplateExcel")
    public String TemplateExcel(HttpServletResponse response){
        System.out.println("进入TemplateExcel");
        try {
            List<Room> list = roomService.getAllRoom();
            Workbook wb = ExcelUtil.fillExcelDataWithTemplate(list, "userExporTemplate.xls");
            ResponseUtil.export(response, wb, "模板导出excel.xls");
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
}

工具类，ExcelUtil.java

package com.scce.utils;

import com.scce.pojo.Room;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.InputStream;
import java.util.List;

public class ExcelUtil {
	/**
	 * 封装Excel的集合
	 * @param list
	 * @param wb
	 * @param headers
	 * @throws Exception
	 */
	public static void fillExcelData(List list, Workbook wb, String[] headers)throws Exception{
		int rowIndex=0;
		Sheet sheet=wb.createSheet();
		Row row=sheet.createRow(rowIndex++);
		for(int i=0;i<headers.length;i++){
			row.createCell(i).setCellValue(headers[i]);
		}
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
			Room room= (Room) list.get(i);
			row=sheet.createRow(rowIndex++);
				row.createCell(0).setCellValue(room.getRoomNumber());
				row.createCell(1).setCellValue((room.getPrice()));
				row.createCell(2).setCellValue(room.getRoomType());
				row.createCell(3).setCellValue(room.getDeposit());
				row.createCell(4).setCellValue(room.getStatusstr());
		}
	}

	/**
	 * 读取模板，将数据一个一个的放在模板中
	 * @param list
	 * @param templateFileName
	 * @return
	 * @throws Exception
	 */
	public static Workbook fillExcelDataWithTemplate(List list, String templateFileName)throws Exception{
		System.out.println("进入fillExcelDataWithTemplate:"+templateFileName);
		//Class.getResourceAsStream(String path) ： path 不以’/'开头时默认是从此类所在的包下取资源
		// ，以’/'开头则是从ClassPath根下获取。其只是通过path构造一个绝对路径，最终还是由ClassLoader获取资源。
		//getResourceAsStream(String path)  com.scce.template
		InputStream inp=ExcelUtil.class.getResourceAsStream("/com/scce/template/"+templateFileName);
		System.out.println(inp);
		//POIFSFileSystem 进行解析Excel
		POIFSFileSystem fs=new POIFSFileSystem(inp);
		Workbook wb=new HSSFWorkbook(fs);
		Sheet sheet=wb.getSheetAt(0);
		int rowIndex=1;
		for (int i = 0; i < list.size(); i++) {
			System.out.println(list.get(i));
			Room room= (Room) list.get(i);
			Row row=sheet.createRow(rowIndex++);
			row=sheet.createRow(rowIndex++);
			row.createCell(0).setCellValue(room.getRoomNumber());
			row.createCell(1).setCellValue((room.getPrice()));
			row.createCell(2).setCellValue(room.getRoomType());
			row.createCell(3).setCellValue(room.getDeposit());
			row.createCell(4).setCellValue(room.getStatusstr());
		}
		return wb;
	}
	/**
	 * @param hssfCell
	 * @return
	 */
	public static String formatCell(HSSFCell hssfCell){
		if(hssfCell==null){
			return "";
		}else{
			if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_BOOLEAN){
				return String.valueOf(hssfCell.getBooleanCellValue());
			}else if(hssfCell.getCellType()==HSSFCell.CELL_TYPE_NUMERIC){
				return String.valueOf(hssfCell.getNumericCellValue());
			}else{
				return String.valueOf(hssfCell.getStringCellValue());
			}
		}
	}

}

源码：https://github.com/LuoXuYang1997/poi.git