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

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();

}
希望能帮到前进路上的您，欢迎指教