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();
}
希望能帮到前进路上的您,欢迎指教