问题描述:
java poi 操作excel ,浏览器提交表单下载导出excel, 用火狐可以正常导出,用chrome一直无响应。
解决经过:
度娘了一堆答案,得到 靠谱的方案是需要设置response的Content-Length来解决,但是无法通过 excel的Workbook获取 length,于是又参照了 https://qieyi28.iteye.com/blog/2274852 的提示,先生成临时文件,再 file.length() 塞到 response的Content-Length里,最终解决。
以下是参照的文章原文(避免原作者删除,我还是copy一份)
第一步:导入poi jar包。因为是使用maven ,如下方式引入jar
Xml代码
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
- <version>3.13</version>
- </dependency>
第二步:jsp页面使用from表单请求
Html代码
- <form id="form_stock" action="${contextPath}/stock/toExportExcel">
- ...............
- </form>
第三步:设计controller
Java代码
- @RequestMapping("toExportExcel")
- public void exportExcel(StockExt stockExt,HttpServletRequest request, HttpServletResponse response) {
- List<Stock> list = stockService.queryList(stockExt);
- String[] rowsName = new String[] {"序号","货号","品名","规格","颜色","单位","批号","库存量","仓库","库位","单类型","所属客户","入库时间","实际库存量"};
- List<Object[]> dataList = new ArrayList<Object[]>();
- for (int i = 0; i < list.size(); i++) {
- Object[] objs = new Object[rowsName.length];// 创建13个数组长度
- StockVo sto = listVo.get(i);
- objs[0] = i;
- objs[1] = sto.getCargoNum();// 货号
- objs[2] = sto.getCargoName();// 品名
- objs[3] = sto.getCargoSpecifications();// 规格
- objs[4] = sto.getCargoColor();// 颜色
- objs[5] = sto.getUnitsName();// 单位
- objs[6] = TimeUtil.getTime(sto.getManufactureDate(), "yyyyMMdd");// 批号
- objs[7] = sto.getStockAmount();
- objs[8] = sto.getStorageName();// 仓库
- objs[9] = sto.getDepotName();// 库位
- objs[10] = (sto.getIsLoss() == 1) ? "库存单" : "报损单";
- objs[11] = sto.getNickName();// 所属客户
- objs[12] = sto.getModifyDate();
- objs[13] = "";// 留给仓管员导出人员任意填写
- dataList.add(objs);
- }
- String fileName = "库存单";
- //执行导出
- ExportExcel.exportExcel(request,response,fileName, rowsName, dataList, "yyyy-MM-dd HH:mm:ss");
- }
上面代码中,我们只要生成exportExcel方法里对应的3个参数就可以了,这个根据你代码灵活配置。
第四步:实现exportExcel方法
Java代码
- package com.honsend.common.jsp.poi;
- import java.io.BufferedInputStream;
- import java.io.BufferedOutputStream;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.io.OutputStream;
- import java.net.URLEncoder;
- import java.text.SimpleDateFormat;
- import java.util.Date;
- import java.util.Iterator;
- import java.util.List;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFFont;
- import org.apache.poi.hssf.usermodel.HSSFRichTextString;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.apache.poi.hssf.util.HSSFColor;
- import com.honsend.common.NumberUtil;
- import com.honsend.common.TimeUtil;
- /**
- * 利用开源组件POI3.13动态导出EXCEL文档
- *
- * @version v1.0
- * @param
- * */
- public class ExportExcel {
- public static final String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");
- /**
- * 这是一个通用的方法
- * @param fileName 文件名
- * @param headers 表格属性列名数组
- * @param dataset 需要显示的数据集合
- * @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd"
- */
- @SuppressWarnings("deprecation")
- public static HSSFWorkbook exportExcel(HttpServletRequest request,HttpServletResponse response,String fileName, String[] headers, List<Object[]> dataset, String pattern) {
- String docsPath = request.getSession().getServletContext().getRealPath("docs");//docs文件夹目录
- String filePath = docsPath + FILE_SEPARATOR + fileName+"-任意名.xls";
- // 声明一个工作薄
- HSSFWorkbook workbook = new HSSFWorkbook();
- // 生成一个表格
- HSSFSheet sheet = workbook.createSheet("Excel");
- // 设置表格默认列宽度为15个字节
- //sheet.setDefaultColumnWidth((short) 15);
- // 生成一个表格标题行样式
- HSSFCellStyle style = getColumnTopStyle(workbook);
- // 生成非标题样式
- HSSFCellStyle style2 = getColumnStyle(workbook);
- // 产生表格标题行
- HSSFRow row = sheet.createRow(0);
- for (short i = 0; i < headers.length; i++) {
- HSSFCell cell = row.createCell(i);
- cell.setCellStyle(style);
- HSSFRichTextString text = new HSSFRichTextString(headers[i]);
- cell.setCellValue(text);
- }
- // 遍历集合数据,产生数据行
- Iterator<Object[]> it = dataset.iterator();
- int index = 0;
- while (it.hasNext()) {
- index++;
- row = sheet.createRow(index);//从第1行开始创建
- Object[] obj = (Object[]) it.next();
- for (short i = 0; i < obj.length; i++) {
- HSSFCell cell = row.createCell(i);
- cell.setCellStyle(style2);
- Object value = obj[i];
- String textValue = null;
- if (!"".equals(value) && value != null) {
- if (value instanceof Integer) {
- int intValue = (Integer) value;
- cell.setCellValue(intValue);
- } else if (value instanceof Float) {
- float fValue = (Float) value;
- cell.setCellValue(fValue);
- } else if (value instanceof Double) {
- double dValue = (Double) value;
- cell.setCellValue(dValue);
- } else if (value instanceof Long) {
- long longValue = (Long) value;
- cell.setCellValue(longValue);
- } else if (value instanceof Date) {
- Date date = (Date) value;
- if(null==pattern||pattern.equals("")){
- pattern="yyyy-MM-dd";
- }
- SimpleDateFormat sdf = new SimpleDateFormat(pattern);
- textValue = sdf.format(date);
- cell.setCellValue(textValue);
- } else {
- // 其它数据类型都当作字符串简单处理
- textValue = value.toString();
- cell.setCellValue(textValue); // 设置单元格的值
- }
- } else {
- cell.setCellValue("");
- }
- }
- }
- //让列宽随着导出的列长自动适应
- for (int colNum = 0; colNum < headers.length; colNum++) {
- int columnWidth = sheet.getColumnWidth(colNum) / 256;
- for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
- HSSFRow currentRow;
- // 当前行未被使用过
- if (sheet.getRow(rowNum) == null) {
- currentRow = sheet.createRow(rowNum);
- } else {
- currentRow = sheet.getRow(rowNum);
- }
- if (currentRow.getCell(colNum) != null) {
- HSSFCell currentCell = currentRow.getCell(colNum);
- if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
- int length = currentCell.getStringCellValue() != null
- ? currentCell.getStringCellValue().getBytes().length : 10;
- if (columnWidth < length) {
- columnWidth = length;
- }
- }
- }
- }
- if (colNum == 0) {
- sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
- } else {
- sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
- }
- }
- try {
- if (createDir(docsPath)) {
- OutputStream out = new FileOutputStream(filePath);
- workbook.write(out);
- out.close();
- download(filePath, response);// 执行下载
- cleanFile(filePath);// 删除已完成下载的文件
- }
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- }catch (IOException e) {
- e.printStackTrace();
- }
- return workbook;
- }
- /**
- * 导出下载,弹出下载框
- * @param path 文件下载路径
- * @param response
- */
- public static void download(String path, HttpServletResponse response) {
- try {
- // path是指欲下载的文件的路径。
- File file = new File(path);
- // 取得文件名。
- String filename = file.getName();
- // 以流的形式下载文件。
- InputStream fis = new BufferedInputStream(new FileInputStream(path));
- byte[] buffer = new byte[fis.available()];
- fis.read(buffer);
- fis.close();
- // 清空response
- response.reset();
- // 设置response的Header
- response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "UTF-8"));
- response.addHeader("Content-Length", "" + file.length());
- OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
- response.setContentType("application/vnd.ms-excel;charset=gb2312");
- toClient.write(buffer);
- toClient.flush();
- toClient.close();
- } catch (IOException ex) {
- ex.printStackTrace();
- }
- }
- /**
- * 清除文件
- * @param filePath 文件路径
- */
- public static void cleanFile(String docsPath) {
- File file = new File(docsPath);
- file.delete();
- }
- /*
- * 列头单元格样式
- */
- public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
- // 设置样式;
- HSSFCellStyle style = workbook.createCellStyle();
- // 设置底边框;
- style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- // 设置底边框颜色;
- style.setBottomBorderColor(HSSFColor.BLACK.index);
- // 设置左边框;
- style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- // 设置左边框颜色;
- style.setLeftBorderColor(HSSFColor.BLACK.index);
- // 设置右边框;
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);
- // 设置右边框颜色;
- style.setRightBorderColor(HSSFColor.BLACK.index);
- // 设置顶边框;
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);
- // 设置顶边框颜色;
- style.setTopBorderColor(HSSFColor.BLACK.index);
- // 设置自动换行;
- style.setWrapText(false);
- // 设置水平对齐的样式为居中对齐;
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- // 设置垂直对齐的样式为居中对齐;
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- // 设置字体
- HSSFFont font = workbook.createFont();
- // 设置字体颜色
- font.setColor(HSSFColor.VIOLET.index);
- // 设置字体大小
- font.setFontHeightInPoints((short) 12);
- // 字体加粗
- font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- // 设置字体名字
- font.setFontName("Courier New");
- // 在样式用应用设置的字体;
- style.setFont(font);
- return style;
- }
- /*
- * 列数据信息单元格样式
- */
- public static HSSFCellStyle getColumnStyle(HSSFWorkbook workbook) {
- // 设置样式;
- HSSFCellStyle style = workbook.createCellStyle();
- // 设置底边框;
- style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
- // 设置底边框颜色;
- style.setBottomBorderColor(HSSFColor.BLACK.index);
- // 设置左边框;
- style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
- // 设置左边框颜色;
- style.setLeftBorderColor(HSSFColor.BLACK.index);
- // 设置右边框;
- style.setBorderRight(HSSFCellStyle.BORDER_THIN);
- // 设置右边框颜色;
- style.setRightBorderColor(HSSFColor.BLACK.index);
- // 设置顶边框;
- style.setBorderTop(HSSFCellStyle.BORDER_THIN);
- // 设置顶边框颜色;
- style.setTopBorderColor(HSSFColor.BLACK.index);
- // 设置自动换行;
- style.setWrapText(false);
- // 设置水平对齐的样式为居中对齐;
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- // 设置垂直对齐的样式为居中对齐;
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- // 设置字体
- HSSFFont font = workbook.createFont();
- // 设置字体大小
- font.setFontHeightInPoints((short) 10);
- font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
- // 设置字体名字
- font.setFontName("Courier New");
- // 在样式用应用设置的字体;
- style.setFont(font);
- return style;
- }
- /**
- * 创建目录
- * @param destDirName
- * @return true 存在目录 false 不存在目录
- */
- public static boolean createDir(String destDirName) {
- File dir = new File(destDirName);
- if (dir.exists()) {
- return true;
- }
- if (!destDirName.endsWith(File.separator)) {
- destDirName = destDirName + File.separator;
- }
- // 创建目录
- if (dir.mkdirs()) {
- System.out.println("创建目录" + destDirName + "成功!");
- return true;
- } else {
- System.out.println("创建目录" + destDirName + "失败!");
- return false;
- }
- }
- }