spirngmvc怎么输出excel视图

发布网友发布时间：2022-05-16 04:23

共2个回答

热心网友时间：2022-04-27 01:30

一) 其实这个功能在spring2.x时代就提供了。一直没用过，今天在spring-mvc3.2.x的环境下试验了一次。还算简单易用。
二) 依赖。
spring依赖POI或jExcel来实现对excel输出的支持，前者是apache出品，貌似名气更大，本例使用第一个。

<groupId>org.apache.poi</groupId>

</dependency>

三) spring提供了一个AbstractExcelView作为自己实现的视图的父类。实例代码如下。

package ying.car.view;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.List;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.IndexedColors;

import org.joda.time.DateTime;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.web.servlet.view.document.AbstractExcelView;

import ying.car.binding.DateRange;

import ying.car.domain.RefuelingRecord;

public class RefuelingRecordExcelView extends AbstractExcelView {

private static final Logger LOGGER = LoggerFactory.getLogger(RefuelingRecordExcelView.class);

private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyyMMdd");

@Override

@SuppressWarnings({"unchecked"})

protected void buildExcelDocument(

Map<String, Object> model, // MVC中的M就在这里了

HSSFWorkbook workbook,

HttpServletRequest request,

HttpServletResponse response) throws Exception

{

("yyyy/MM/dd"));

LOGGER.debug("end: {}", new DateTime(dr.getEnd()).toString("yyyy/MM/dd"));

}

HSSFSheet sheet = workbook.createSheet(DATE_FORMAT.format(dr.getStart()) + "-" + DATE_FORMAT.format(dr.getEnd()));

setColumnsWidth(sheet);

fillTableHeader(workbook, sheet);

fillTableBody(workbook, sheet, rrl);

}

private void setColumnsWidth(HSSFSheet sheet) {

final int[] warr = new int[] {

500, // <空>

4500, // 日期

4500, // 车辆

4500, // 燃油种类

4500, // 燃油单价

4500, // 加油方式

4500, // 加油量

3000, // 花费

12000 // 备注

};

for (int i = 0; i < warr.length; i ++) {

sheet.setColumnWidth(i, warr[i]);

}

// 填充表格头

private void fillTableHeader(HSSFWorkbook workbook, HSSFSheet sheet) {

final String[] contents = new String[] {

"日期",

"车辆",

"燃油种类",

"燃油单价(元/升)",

"加油方式",

"加油量(升)",

"花费(元)",

"备注"

};

int r = 1;

int c = 1;

CellStyle style = workbook.createCellStyle();

style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); // 填充*

style.setFillPattern(CellStyle.SOLID_FOREGROUND); // 填充方式

// 设置border

style.setBorderLeft(CellStyle.BORDER_THIN);

style.setBorderRight(CellStyle.BORDER_THIN);

style.setBorderTop(CellStyle.BORDER_THIN);

style.setBorderBottom(CellStyle.BORDER_THIN);

for (int i = 0; i < contents.length; i ++) {

Cell cell = getCell(sheet, r, c + i);

cell.setCellValue(contents[i]);

cell.setCellStyle(style);

}

private void fillTableBody(HSSFWorkbook workbook, HSSFSheet sheet, List<RefuelingRecord> records) {

// 通用style

CellStyle style = workbook.createCellStyle();

style.setFillForegroundColor(IndexedColors.WHITE.getIndex()); // 填充白色

style.setFillPattern(CellStyle.SOLID_FOREGROUND); // 填充方式

style.setBorderLeft(CellStyle.BORDER_THIN);

style.setBorderRight(CellStyle.BORDER_THIN);

style.setBorderTop(CellStyle.BORDER_THIN);

style.setBorderBottom(CellStyle.BORDER_THIN);

// 日期style

CellStyle dateStyle = workbook.createCellStyle();

dateStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); // 填充白色

dateStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // 填充方式

dateStyle.setBorderLeft(CellStyle.BORDER_THIN);

dateStyle.setBorderRight(CellStyle.BORDER_THIN);

dateStyle.setBorderTop(CellStyle.BORDER_THIN);

dateStyle.setBorderBottom(CellStyle.BORDER_THIN);

dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

int r = 2;

int c = 1;

Cell cell = null;

for (int i = 0; i < records.size(); i ++) {

RefuelingRecord rr = records.get(i);

// 日期

cell = getCell(sheet, r, c + 0);

if (rr.getDate() != null)

cell.setCellValue(rr.getDate());

cell.setCellStyle(dateStyle);

// 车辆

cell = getCell(sheet, r, c + 1);

if (rr.getVehicle().getNickname() != null)

cell.setCellValue(rr.getVehicle().getNickname());

cell.setCellStyle(style);

// 燃油种类

cell = getCell(sheet, r, c + 2);

if (rr.getGasType() != null) {

String s = null;

switch (rr.getGasType()) {

case _0: s = "0号柴油"; break;

case _93: s = "93号汽油"; break;

case _97: s = "97号汽油"; break;

case _98: s = "98号汽油"; break;

}

cell.setCellValue(s);

}

cell.setCellStyle(style);

// 单价

cell = getCell(sheet, r, c + 3);

if (rr.getPriceOfGas() != null)

cell.setCellValue(rr.getPriceOfGas());

cell.setCellStyle(style);

// 加油方式

cell = getCell(sheet, r, c + 4);

if (rr.getRefuelingType() != null) {

String s = null;

switch (rr.getRefuelingType()) {

case FIXED_CUBAGE:

s = "固定容积"; break;

case FIXED_MONEY:

s = "固定金额"; break;

case FULL:

s = "加满"; break;

}

cell.setCellValue(s);

}

cell.setCellStyle(style);

// 加油量

cell = getCell(sheet, r, c + 5);

if (rr.getCubageOfGas() != null)

cell.setCellValue(rr.getCubageOfGas());

cell.setCellStyle(style);

// 花费

cell = getCell(sheet, r, c + 6);

if (rr.getSumOfMoney() != null)

cell.setCellValue(rr.getSumOfMoney());

cell.setCellStyle(style);

// 备注

cell = getCell(sheet, r, c + 7);

if (rr.getComment() != null)

cell.setCellValue(rr.getComment());

cell.setCellStyle(style);

r ++;

}

cell.setCellStyle(style);

// 燃油种类
cell = getCell(sheet, r, c + 2);
if (rr.getGasType() != null) {
String s = null;
switch (rr.getGasType()) {
case _0: s = "0号柴油"; break;
case _93: s = "93号汽油"; break;
case _97: s = "97号汽油"; break;
case _98: s = "98号汽油"; break;
}
cell.setCellValue(s);
}
cell.setCellStyle(style);

// 单价
cell = getCell(sheet, r, c + 3);
if (rr.getPriceOfGas() != null)
cell.setCellValue(rr.getPriceOfGas());
cell.setCellStyle(style);

// 加油方式
cell = getCell(sheet, r, c + 4);
if (rr.getRefuelingType() != null) {
String s = null;
switch (rr.getRefuelingType()) {
case FIXED_CUBAGE:
s = "固定容积"; break;
case FIXED_MONEY:
s = "固定金额"; break;
case FULL:
s = "加满"; break;
}
cell.setCellValue(s);
}
cell.setCellStyle(style);

// 加油量
cell = getCell(sheet, r, c + 5);
if (rr.getCubageOfGas() != null)
cell.setCellValue(rr.getCubageOfGas());
cell.setCellStyle(style);

// 花费
cell = getCell(sheet, r, c + 6);
if (rr.getSumOfMoney() != null)
cell.setCellValue(rr.getSumOfMoney());
cell.setCellStyle(style);

// 备注
cell = getCell(sheet, r, c + 7);
if (rr.getComment() != null)
cell.setCellValue(rr.getComment());
cell.setCellStyle(style);

r ++;
}
}
}

四) Controller中返回逻辑视图名 (代码片段)

Java代码

@RequiresUser // 安全框架用元注释

@RequiresRoles({"ROLE_USER"})

@RequestMapping(value = "/list/excel", method = RequestMethod.GET)

public String listByExcel(

@DateRangeFormat(pattern = "yyyy-MM-dd") @RequestParam("dateRange") DateRange dateRange,

ModelMap modelMap

)

{

}

// 放入model

modelMap.put("dateRange", dateRange);

modelMap.put("refuelingRecordList", gasService.findRefuelingRecordByDateRange(currentUserId, dateRange));

return "refueling-record-list"; // 最终返回逻辑视图名

}

五) 为spring-mvc配置多个视图解析器。

</bean>

六) 效果图

热心网友时间：2022-04-27 02:48

核心思路:
1.视图：给spring提供excel视图支持
2.数据：查询业务数据
3.载体：poi组件构建表格样式

1.给项目增加excel支持,项目中导入poi-*.jar等操作excel文件的jar文件(我使用的是poi3.8版本的)
poi-3.8-20120326.jar
poi-excelant-3.8-20120326.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
poi-scratchpad-3.8-20120326.jar

2.扩展AbstractExcelView抽象类，实现自己的excel类,需要实现buildExcelDocument方法,我实现的类是com.ttpod.stusys.common.excel.ExcelRevenueReportView,主要代码如下:
List<Log> logList = (List<Log>) logList;//从控制器Controller中返回的业务数据
HSSFSheet sheet = workbook.createSheet("tableTitle"); //创建表格
HSSFRow header = sheet.createRow(0);//定义表格的表头
header.createCell(0).setCellValue(cellTitle);//创建表头列
HSSFRow row = sheet.createRow(rowNum); //创建表格行
row.createCell(0).setCellValue(从logList中解析的数据); //创建单元格并且给其赋值

3.在spring-servlet.xml中配置excel视图,代码片段如下：
No1.

<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver"
p:prefix="/" p:suffix=".jsp">

<property name="order" value="1" />
</bean>
No2.

<bean class="org.springframework.web.servlet.view.XmlViewResolver">
<property name="location">

<value>classpath:spring-excel-views.xml</value>
</property>
<property name="order" value="10" />
</bean>
No3.

<bean id="view_excel"
class="com.ttpod.stusys.common.excel.ExcelRevenueReportView">
</bean>
No.4
也可以不创建spring-excel-views.xml文件,直接在spring-servlet.xml文件中定义如下内容:
<bean class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="10"/>
<bean id="view_excel"
class="com.ttpod.stusys.common.excel.ExcelRevenueReportView">
</bean>

4.在控制器Controller中返回excel格式的视图,代码片段如下:
@Controller
@RequestMapping("/view/")
public class ExportController{
/**
* 展示excel视图的方法
/
@RequestMapping("/excel")
protected ModelAndView excel(...){
String filename = new String("中文文件名称".getBytes(),"iso8859-1");
response.setHeader("Content-Disposition","attachment;filename="+filename+".xls");
List<Log> logList = 调用业务层查询日志数据
//view_excel是在spring配置文件里配置的ExcelRevenueReportView,第二个和第三个参数采用键值对方法提供给buildExcelDocument方法使用
return new ModelAndView("view_excel","logList",logList);
}
}

5.打开浏览器访问,会以下载文件的方式下载excel数据
http://127.0.0.1:8080/项目部署名称/view/excel.do