查询数据并导出报表

最新推荐文章于 2024-06-12 17:33:26 发布

原创最新推荐文章于 2024-06-12 17:33:26 发布 · 2k 阅读

4 ·

CC 4.0 BY-SA版权

java基础专栏收录该内容

26 篇文章

订阅专栏

## 需求

页面查询用户表信息, 并实现数据导出(以.xlsx格式保存至本地)

报表导出展示效果:

一, 环境搭建

1, 新建一SSM测试项目(ssm_test), 用于测试报表导出功能, 具体搭建过程不再赘述;

2, 报表导出需要用到apache提供的两个jar包, 添加对应依赖

<!-- 用于进行数据报表导出操作 -->
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>3.14</version>
	</dependency>
	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi-ooxml</artifactId>
	    <version>3.14</version>
	</dependency>

二, 后台接口实现

1, DataExportController .java

## 这里查询数据库环节以固定结果集替代, 用于简单测试

package cn.tedu.test.controller;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

/*
 * 查询数据以报表的形式导出
 */
@Controller
public class DataExportController {
	public static final String[] HEADERS = {"姓名", "性别", "年龄", "工作", "住址"};

	@RequestMapping("/export.do")
	@ResponseBody
	public void doExport(HttpServletResponse response) throws Exception {
		List<List<String>> data = new ArrayList<List<String>>();
		for (int i = 0; i < 6; i++) {
			List<String> userInfo = new ArrayList<String>();
			userInfo.add("小明" + i);
			userInfo.add("男" + i);
			userInfo.add("27" + i);
			userInfo.add("软件开发" + i);
			userInfo.add("上海浦东" + i);
			data.add(userInfo);
		}
		ExcelUtil excelUtil = new ExcelUtil("userInfo");
		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet();
		excelUtil.formData(Arrays.asList(HEADERS), data, sheet);
		excelUtil.buildExcelDocument(workbook, response);
	}

}

## 备注: for循环中进行字符串加号拼接的性能不高, 建议使用 StringBuilder 或 StringBuffer的append()方法;

参考文章：《诡异的字符串问题。。。》

2, ExcelUtil.java

package cn.tedu.test.controller;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 * excel报表工具类
 */
public class ExcelUtil {

	private String filename = "";
	
	public ExcelUtil(String filename) {
		this.filename = filename;
	}
	
	public void buildExcelDocument(Workbook workbook, HttpServletResponse response) throws Exception {
		response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
		response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename + ".xlsx", "UTF-8"));
		OutputStream ouputStream = response.getOutputStream();
		workbook.write(ouputStream);
		ouputStream.flush();
		ouputStream.close();
	}
	
	// 填充表头和数据
	public void formData(List<String> headers, List<List<String>> data, Sheet sheet){
		int rowIndex = 0;
	    int colIndex = 0;
	    Row head= sheet.createRow(rowIndex++);
	    for (String title : headers) {
	    	Cell tCell = head.createCell(colIndex++);
	    	tCell.setCellValue(title);
		}
	    for (List<String>  record : data) {
	    	Row row = sheet.createRow(rowIndex++);
			colIndex = 0;
			for (String value : record) {
				Cell tCell = row.createCell(colIndex++);
				tCell.setCellValue(value);
			}
		}
	}

	public String getFilename() {
		return filename;
	}

	public void setFilename(String filename) {
		this.filename = filename;
	}
	
}

三, 前台代码实现

1, index,html

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>guess</title>

<script type="text/javascript" src="jquery.min.js"></script>
<script type="text/javascript">
  //body加载完调用;
  $(function(){
	  //导出数据报表
	  $("#export").click(function(){
		  alert('导出报表')
		  window.open("/ssm_test/export.do");
		}
	  );
  });  
</script>


</head>
<body>
    <input type="button" value="导出数据报表" id="export">
</body>
</html>

四, 功能测试

1, 访问路径

http://localhost:8080/ssm_test/

2, 如果访问被拦截, 需要设置下, 选中始终允许即ok!