访问Servlet 下载Excel文件（JAVA POI）

最新推荐文章于 2021-11-19 09:25:52 发布

CCUT

最新推荐文章于 2021-11-19 09:25:52 发布

阅读量727

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/qq_29622845/article/details/106118193

版权

java 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

项目需求：在页面以时间间隔为查询条件，查询数据，并支持导出数据到excel并下载。

现在记录一下导出数据到excel并下载的部分。

jsp部分(把下载按钮跳转到 exportFile方法)

function exportFile(){
    post('ExportTertiaySector.action',{"projectId":projectId,"implFrom2":implFrom2,"implTo2":implTo2})
}

function post(URL, PARAMS) {
  var temp = document.createElement("form");
  temp.action = URL;
  temp.method = "post";
  temp.style.display = "none";
  for (var x in PARAMS) {
    var opt = document.createElement("textarea");
    opt.name = x;
    opt.value = PARAMS[x];
    // alert(opt.name)
    temp.appendChild(opt);
  }
  document.body.appendChild(temp);
  temp.submit();
  return temp;
}

Servlet 部分

package com.teamlive.h3c.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.inject.Inject;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.json.simple.JSONObject;

import com.teamlive.h3c.service.IQueryOriginalSolutionStatistics;
import com.teamlive.utils.Constants;
import com.teamlive.utils.GuiceService;

public class ExportTertiarySectorServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	@Inject
	IQueryOriginalSolutionStatistics expSvc;
	
	static final Logger LOG = Logger.getLogger(ExportTertiarySectorServlet.class);

	protected String success;
	
	@Override
	protected void doGet(final HttpServletRequest req, final HttpServletResponse resp) throws ServletException, IOException {
		String projectId = req.getParameter("projectId");
		doService(req, resp, projectId);
	}
	
	@Override
	protected void doPost(final HttpServletRequest req, final HttpServletResponse resp) throws ServletException, IOException {
		doGet(req, resp);
	}
	
	@Override
	public void init(final ServletConfig config) throws ServletException {
		super.init(config);
		success = config.getInitParameter(Constants.KEY_SUCCESS);
		if (success == null) {
			success = config.getServletContext().getInitParameter("jsp-success");
		}
		GuiceService.inject(this);
	}
	
	String doService(HttpServletRequest req, HttpServletResponse resp, String projectId) throws IOException {
		String implFrom = req.getParameter("implFrom2").replace("-", "");
		String implTo = req.getParameter("implTo2").replace("-", "");
		List<Map<String,String>> dataList = new ArrayList<Map<String,String>>();
		String pathStr = req.getServletContext().getRealPath("");
		if(StringUtils.isNotBlank(pathStr) && pathStr.contains("webapp") && pathStr.contains("com.teamlive.h3c.integration")) {
			try {
				//读取一个空白Excel，我是在webapp下面建了一个叫按部门统计新提交需求.xlsx的文件
				XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(pathStr+"/按部门统计新提交需求.xlsx")));
				XSSFSheet sheet = wb.getSheetAt(0);
				//获取要写入Excel的数据
				dataList = expSvc.getThirdDepartment(projectId, implFrom, implTo);
				XSSFRow row = sheet.createRow(0);
				//设置表头宽度
				sheet.setColumnWidth(0, 40 * 256);
				sheet.setColumnWidth(1, 30 * 256);
				//设置表头文字
				Cell cell = row.createCell(0);
				cell.setCellValue("三级部门名称（解决方案开发部下属三级部门）");
				Cell cell1 = row.createCell(1);
				cell1.setCellValue("本月新增提交需求数量");
				//写入内容
				for (int i = 1; i <= dataList.size(); i++) {
					Map map = dataList.get(i-1);
					XSSFRow rows = sheet.createRow(i);
					Cell cells0 = rows.createCell(0);
					cells0.setCellValue(map.get("departmentName").toString());
					Cell cells1 = rows.createCell(1);
					cells1.setCellValue(map.get("reqNum").toString());
				}
				String fileName = "按部门统计新提交需求"+implFrom+"-"+implTo+".xlsx";
				String file = new String(fileName.getBytes("utf-8"),"iso-8859-1");
				resp.addHeader("Content-Disposition", "attachment;filename="+file);
				InputStream inputStream = new FileInputStream(pathStr+"/按部门统计新提交需求.xlsx");
				OutputStream outputStream = resp.getOutputStream();
				//下载文件
				wb.write(outputStream);
				inputStream.close();
				outputStream.flush();
				outputStream.close();
			} catch (Exception e) {
				e.printStackTrace();
				LOG.error(e.getMessage(),e);
			}
		}
		return "";
	}
}

算是一个工作记录吧