JAVA导出excel 直接弹出下载框

痛定思痛。 2023-10-09 10:06 33阅读 0赞

转自：[https://blog.csdn.net/qq\_38423105/article/details/80782283][https_blog.csdn.net_qq_38423105_article_details_80782283]

效果展示：

![893718-20190920144241602-1847444443.png][]

![893718-20190920144248190-104243705.png][]

1.首先准备jar包

<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi</artifactId>  
    <version>3.8</version>  
</dependency>  
2.前台页面访问 导出excel的接口

通过window.location.href来访问接口，直接弹出下载框

// 导出数据  
$('\#outExcel').click(function()\{  
    var status = $('\#status').val();  
    window.location.href = baseUrl + '/order/excel/orderrdExportExcel?status="+status;  
\});  
3.后台通过获取response中的OutStream 流 ，将excel文件写到流里面，代码如下:

@GetMapping("/orderrdExportExcel")  
public void grouppExportExcel(HttpServletRequest request,Orderrd orderrd,HttpServletResponse response) throws Exception\{  
    List<Orderrd> grouppList = orderrdService.selectAllForLayUI(orderrd);  
    //导出文件的标题  
String title = "退款订单"+DateUtil.getSimpleDate01(new Date())+".xlsx";  
//设置表格标题行  
String\[\] headers = new String\[\] \{"序号","流水编号","用户账号","用户姓名","收款账号","账户余额","退款金额","手续费","申请时间"\};  
List<Object\[\]> dataList = new ArrayList<Object\[\]>();  
Object\[\] objs = null;  
for (int i = 0; i < grouppList.size(); i++) \{  
objs = new Object\[headers.length\];  
objs\[0\] = 0;//设置序号,在工具类中会出现  
objs\[1\] = grouppList.get(i).getOrderrdCode();  
objs\[2\] = grouppList.get(i).getUsername();  
objs\[3\] = grouppList.get(i).getUserName();  
objs\[4\] = grouppList.get(i).getAccount();  
objs\[5\] = grouppList.get(i).getPremain();  
objs\[6\] = grouppList.get(i).getPricePre();  
objs\[7\] = grouppList.get(i).getPricePay();  
objs\[8\] = grouppList.get(i).getCreateTime();  
dataList.add(objs);//数据添加到excel表格  
\}  
//使用流将数据导出  
OutputStream out = null;  
try \{  
    //防止中文乱码  
String headStr = "attachment; filename=\\"" + new String( title.getBytes("gb2312"), "ISO8859-1" ) + "\\"";  
response.setContentType("octets/stream");  
response.setContentType("APPLICATION/OCTET-STREAM");  
response.setHeader("Content-Disposition", headStr);  
out = response.getOutputStream();  
ExportExcelSeedBack ex = new ExportExcelSeedBack(title, headers, dataList);//没有标题  
ex.export(out);  
out.flush();  
out.close();  
\} catch (Exception e) \{  
e.printStackTrace();  
\}  
  
\}  
4.工具类如下：

import java.io.FileOutputStream;  
import java.io.IOException;  
import java.io.OutputStream;  
import java.util.ArrayList;  
import java.util.List;  
  
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;  
  
public class ExportExcelSeedBack \{  
  
//显示的导出表的标题  
private String title;  
//导出表的列名  
private String\[\] rowName ;  
  
private List<Object\[\]> dataList = new ArrayList<Object\[\]>();  
  
HttpServletResponse response;  
  
  
//构造方法，传入要导出的数据  
public ExportExcelSeedBack(String title,String\[\] rowName,List<Object\[\]> dataList)\{  
this.dataList = dataList;  
this.rowName = rowName;  
this.title = title;  
\}  
  
/\*  
\* 导出数据  
\* \*/  
public void export(OutputStream out) throws Exception\{  
try\{  
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象  
HSSFSheet sheet = workbook.createSheet(title); // 创建工作表  
  
// 产生表格标题行  
// HSSFRow rowm = sheet.createRow(0);  
// HSSFCell cellTiltle = rowm.createCell(0);  
  
//sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】  
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象  
HSSFCellStyle style = this.getStyle(workbook); //单元格样式对象  
  
// sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));//合并单元格  
// cellTiltle.setCellStyle(columnTopStyle);  
// cellTiltle.setCellValue(title);  
  
// 定义所需列数  
int columnNum = rowName.length;  
HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行)  
  
// 将列头设置到sheet的单元格中  
for(int n=0;n<columnNum;n++)\{  
HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格  
cellRowName.setCellType(HSSFCell.CELL\_TYPE\_STRING); //设置列头单元格的数据类型  
HSSFRichTextString text = new HSSFRichTextString(rowName\[n\]);  
cellRowName.setCellValue(text); //设置列头单元格的值  
cellRowName.setCellStyle(columnTopStyle); //设置列头单元格样式  
\}  
  
//将查询出的数据设置到sheet对应的单元格中  
for(int i=0;i<dataList.size();i++)\{  
Object\[\] obj = dataList.get(i);//遍历每个对象  
HSSFRow row = sheet.createRow(i+1);//创建所需的行数（从第二行开始写数据）  
  
for(int j=0; j<obj.length; j++)\{  
HSSFCell cell = null; //设置单元格的数据类型  
if(j == 0)\{  
cell = row.createCell(j,HSSFCell.CELL\_TYPE\_NUMERIC);  
cell.setCellValue(i+1);  
\}else\{  
cell = row.createCell(j,HSSFCell.CELL\_TYPE\_STRING);  
if(!"".equals(obj\[j\]) && obj\[j\] != null)\{  
cell.setCellValue(obj\[j\].toString()); //设置单元格的值  
\}  
\}  
cell.setCellStyle(style); //设置单元格样式  
\}  
\}  
//让列宽随着导出的列长自动适应  
for (int colNum = 0; colNum < columnNum; 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().getBytes().length;  
// if (columnWidth < length) \{  
// columnWidth = length;  
// \}  
// \}  
// \}  
if (currentRow.getCell(colNum) != null) \{  
HSSFCell currentCell = currentRow.getCell(colNum);  
if (currentCell.getCellType() == HSSFCell.CELL\_TYPE\_STRING) \{  
int length = 0;  
try \{  
length = currentCell.getStringCellValue().getBytes().length;  
\} catch (Exception e) \{  
//e.printStackTrace();  
\}  
if (columnWidth < length) \{  
columnWidth = length;  
\}  
\}  
\}  
  
\}  
if(colNum == 0)\{  
sheet.setColumnWidth(colNum, (columnWidth-2) \* 256);  
\}else\{  
sheet.setColumnWidth(colNum, (columnWidth+4) \* 256);  
\}  
\}  
if(workbook !=null)\{  
try\{  
workbook.write(out);  
\}catch (IOException e) \{  
e.printStackTrace();  
\}  
\}  
  
\}catch(Exception e)\{  
e.printStackTrace();  
\}  
finally\{  
out.close();  
\}  
\}  
  
/\*  
\* 列头单元格样式  
\*/  
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) \{  
  
// 设置字体  
HSSFFont font = workbook.createFont();  
//设置字体大小  
font.setFontHeightInPoints((short)11);  
//字体加粗  
font.setBoldweight(HSSFFont.BOLDWEIGHT\_BOLD);  
//设置字体名字  
font.setFontName("Courier New");  
//设置样式;  
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.setFont(font);  
//设置自动换行;  
style.setWrapText(false);  
//设置水平对齐的样式为居中对齐;  
style.setAlignment(HSSFCellStyle.ALIGN\_CENTER);  
//设置垂直对齐的样式为居中对齐;  
style.setVerticalAlignment(HSSFCellStyle.VERTICAL\_CENTER);  
  
return style;  
  
\}  
  
/\*  
\* 列数据信息单元格样式  
\*/  
public HSSFCellStyle getStyle(HSSFWorkbook workbook) \{  
// 设置字体  
HSSFFont font = workbook.createFont();  
//设置字体大小  
//font.setFontHeightInPoints((short)10);  
//字体加粗  
//font.setBoldweight(HSSFFont.BOLDWEIGHT\_BOLD);  
//设置字体名字  
font.setFontName("Courier New");  
//设置样式;  
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.setFont(font);  
//设置自动换行;  
style.setWrapText(false);  
//设置水平对齐的样式为居中对齐;  
style.setAlignment(HSSFCellStyle.ALIGN\_CENTER);  
//设置垂直对齐的样式为居中对齐;  
style.setVerticalAlignment(HSSFCellStyle.VERTICAL\_CENTER);  
  
return style;  
  
\}  
  
  
  
\}

————————————————  
版权声明：本文为CSDN博主「wisdom-chen」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/qq\_38423105/article/details/80782283

转载于:https://www.cnblogs.com/sharpest/p/11557046.html

[https_blog.csdn.net_qq_38423105_article_details_80782283]: https://blog.csdn.net/qq_38423105/article/details/80782283
[893718-20190920144241602-1847444443.png]: https://img2018.cnblogs.com/blog/893718/201909/893718-20190920144241602-1847444443.png
[893718-20190920144248190-104243705.png]: https://img2018.cnblogs.com/blog/893718/201909/893718-20190920144248190-104243705.png