简单的ssm+poi导出数据到excel表实现

冷不防 2023-11-12 06:16 132阅读 0赞

1.需求分析：从数据库提取需要的数据在前端页面显示，前端页面导出，后端实现  
2.建立数据库，根据字段建立相应的excel列名（需要先建好excel模板）  
3.编写工具类  
public class InsertRow \{  
private String excelPath;  
private int insertStartPointer;  
private String sheetName;  
private List<List> context;

/**
     * 构建一个excel(2010)文档插入数据对象,此对象包含要要操作的excel文档对象和要插入的数据以及掺入的位置
     * @param excelPath excel文件路径
     * @param insertStartPointer 插入的行号,从0开始
     * @param sheetName 工作表名称
     * @param context 要插入的内容
     */
    public InsertRow(String excelPath, int insertStartPointer, String sheetName,List<List<String>> context) {
    	super();
    	this.excelPath = excelPath;
    	this.insertStartPointer = insertStartPointer;
    	this.sheetName = sheetName;
    	this.context = context;
    }
    
    public void insertRows() {
    	XSSFWorkbook wb = returnWorkBookGivenFileHandle();
        XSSFSheet sheet = wb.getSheet(sheetName);
        for (List<String> list : context) {
        	XSSFRow row = createRow(sheet, insertStartPointer);
            createCell(row,list);
    	}
        
        saveExcel(wb);
        
    
    }
    private void saveExcel(XSSFWorkbook wb) {
        FileOutputStream fileOut;
        try {
            fileOut = new FileOutputStream(excelPath);
            wb.write(fileOut);
            fileOut.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    
    }
    private XSSFCell createCell(XSSFRow row, List<String> cells) {
    	for (int i = 0; i < cells.size(); i++) {
    		row.createCell(i).setCellValue(cells.get(i));
    	}
        return null;
    }
    private XSSFWorkbook returnWorkBookGivenFileHandle() {
    	XSSFWorkbook wb = null;
        FileInputStream fis = null;
        File f = new File(excelPath);
        try {
            if (f != null) {
                fis = new FileInputStream(f);
                wb = new XSSFWorkbook(fis);
            }
        } catch (Exception e) {
        	e.printStackTrace();
            return null;
        } finally {
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return wb;
    }
    private XSSFRow createRow(XSSFSheet sheet, Integer rowIndex) {
        XSSFRow row = null;
        if (sheet.getRow(rowIndex) != null) {
            int lastRowNo = sheet.getLastRowNum();
            sheet.shiftRows(rowIndex, lastRowNo, 1);
        }
        row = sheet.createRow(rowIndex);
        return row;
    }

\}  
4.service接口实现类

@Override  
public File getExcel(List list, String realPath) \{  
List<List> dataForExcel = getDataForExcel(list);  
File file = madeMashineReportExcel(dataForExcel, realPath);  
return file;  
\}

/\*\*

*  为生成Excel数据方法  
    \*/  
    private List<List> getDataForExcel(List list) \{
    
    List<List> llist = new ArrayList<List>();
    
    for (int n = list.size()-1; n > 0 ; n–) \{  
    SaleInfo saleInfo = list.get(n);  
    List strlist = new ArrayList();  
    String str = saleInfo.toString();  
    //打印测试  
    //System.out.println(str+"==============》");
    
        String[] arr = str.split(",");
        
         for(int i = 0; i < arr.length; i++){
         	strlist.add(i, arr[i]);
         }
         llist.add(strlist);
    
    \}  
    return llist;  
    \}

private File madeMashineReportExcel(List<List> dataForExcel, String rootPath) \{  
// TODO Auto-generated method stub  
String templateName = “saleinfo.xlsx”;  
String resultFileName = rootPath+"/template/temp/saleinfo.xlsx";

try {
    	FileUtils.copyFile(new File(rootPath+"/template/"+templateName), new File(resultFileName));
    
    	new InsertRow(resultFileName,2,"Sheet1",dataForExcel).insertRows();
    
    
    } catch (IOException e) {
    	// TODO Auto-generated catch block
    	e.printStackTrace();
    }
    
    
    return new File(resultFileName);

\}  
5.前端页面关键代码  
<% request.setAttribute(“ctx”, request.getContextPath()); %>  
生成报表  
6.controller  
@Controller  
public class UserInfoController \{  
@Autowired  
private SaleInfoService saleInfoService;  
/\*\*  
\* 查询所有信息  
\* @param model  
\* @return  
*/  
@RequestMapping(value="/selectInfo.html",method=RequestMethod.GET)  
public String selectAllUserInfo(Model model,HttpSession session/*,  
@RequestParam(required=true,value=“pageNum”,defaultValue=“1”)Integer pageNum,  
@RequestParam(required=true,value=“pageSize”,defaultValue=“20”)Integer pageSize\*/)\{  
Store store=(Store) session.getAttribute(Constants.USERINFO\_SESSION);  
String salename=store.getStorename();  
//PageHelper.startPage(pageNum, pageSize);  
List list=saleInfoService.selectAllByStorename(salename);  
//PageInfo pageInfo=new PageInfo<>(list);  
//model.addAttribute(“pageInfo”,pageInfo);  
model.addAttribute(“saleInfolist”,list);  
session.setAttribute(“saleInfolist”, list);  
return “userInfo”;  
\}

/**
     * 生成报表
     * @param request
     * @param response
     * @param session
     * @throws IOException
     */
    @RequestMapping("/getExcel")
    public void getExcel(HttpServletRequest request, HttpServletResponse response, HttpSession session) throws IOException{
    	String realPath = request.getSession().getServletContext().getRealPath("");
    	//从session中获取list
    	List<SaleInfo> list = (List<SaleInfo>) session.getAttribute("saleInfolist");
    	File excelByDate = saleInfoService.getExcel(list,realPath);
    	byte[] data = FileUtils.readFileToByteArray(excelByDate);
    	String fileName = excelByDate.getName();
    	fileName = URLEncoder.encode(fileName, "UTF-8");  
    	response.reset();  
    	response.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\"");  
    	response.addHeader("Content-Length", "" + data.length);  
    	response.setContentType("application/octet-stream;charset=UTF-8");  
    	OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());  
    	outputStream.write(data);  
    	outputStream.flush();  
    	outputStream.close();  
    	excelByDate.delete();
    
    }
    希望能帮到前进路上的您，欢迎指教