Spring mvc 导出table到Excel

时间:2021-09-01 17:27:42
    /**
*
* @Title: exportExcel
* @Description: TODO(导出到excel)
* @param Page page
* @return ModelAndView 返回类型
* @author yk
* @throws Exception
*/
@RequestMapping(value="/excel")
public ModelAndView exportExcel(Page page) throws Exception{
// 输出字符串到控制台
logBefore(logger, Jurisdiction.getUsername()+"导出配件订单列表到excel");
// Excel导出的数据上限为10000000 如果不设置则会只导出10条数据
page.setShowCount(10000000);
// 获得导出权限
if(!Jurisdiction.buttonJurisdiction(menuUrl, "cha")){return null;}
// 创建pd对象
PageData pd = this.getPageData();
// 将garage_id存入pd中
pd.put("garage_id", Jurisdiction.getGarageID());
// 将pd存入page对象
page.setPd(pd);
// 创建mv对象
ModelAndView mv = new ModelAndView();
// 创建Map对象,用于存入列名字与列值
Map<String,Object> dataMap = new HashMap<String,Object>();
// 创建titles对象,对象中的值为Excel的列名
List<String> titles = new ArrayList<String>();
// 向titles对象存入列名 start
titles.add("开单日期"); //
titles.add("单号"); //
titles.add("供应商名称"); //
titles.add("开单类型"); //
titles.add("结算金额"); //
titles.add("经手人"); //
titles.add("结算方式"); //
titles.add("订单状态"); //8
// 向titles对象存入列名 end // 将dataMap对象存入Excel表列名的数组
dataMap.put("titles", titles);
// 获取配件订单列表
List<PageData> varOList = partsOrderFormService.findPurchaseOrderlistPage(page);
// 创建varList对象用于存放Excel中列的值
List<PageData> varList = new ArrayList<PageData>();
// varList存值start
for(int i=0;i<varOList.size();i++){
PageData vpd = new PageData();
vpd.put("var1", varOList.get(i).get("create_datetime").toString()); //
vpd.put("var2", varOList.get(i).getString("order_no")); //
vpd.put("var3", varOList.get(i).getString("sup_name")); //
vpd.put("var4", varOList.get(i).get("order_form_type").toString()); //
vpd.put("var5", varOList.get(i).get("pur_total_price").toString()); //
vpd.put("var6", varOList.get(i).getString("pur_handler")); //
vpd.put("var7", varOList.get(i).get("account_type").toString()); //
vpd.put("var8", varOList.get(i).get("status_flag").toString()); //
varList.add(vpd);
}
// varList存值end
dataMap.put("varList", varList);
// 创建Excel工具类的 erv对象
ObjectExcelView erv = new ObjectExcelView();
// 创建Excel表
mv = new ModelAndView(erv,dataMap);
return mv;
}

工具类:

package com.cd.util;

import java.util.Date;
import java.util.List;
import java.util.Map; 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.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView; import com.cd.util.PageData;
import com.cd.util.Tools;
/**
* 导入到EXCEL
* 类名称:ObjectExcelView.java
* @author RB
* @version 1.0
*/
public class ObjectExcelView extends AbstractExcelView{ @Override
protected void buildExcelDocument(Map<String, Object> model,
HSSFWorkbook workbook, HttpServletRequest request,
HttpServletResponse response) throws Exception {
// TODO Auto-generated method stub
Date date = new Date();
String filename = Tools.date2Str(date, "yyyyMMddHHmmss");
HSSFSheet sheet;
HSSFCell cell;
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+filename+".xls");
sheet = workbook.createSheet("sheet1"); List<String> titles = (List<String>) model.get("titles");
int len = titles.size();
HSSFCellStyle headerStyle = workbook.createCellStyle(); //标题样式
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFFont headerFont = workbook.createFont(); //标题字体
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short)11);
headerStyle.setFont(headerFont);
short width = 20,height=25*20;
sheet.setDefaultColumnWidth(width);
for(int i=0; i<len; i++){ //设置标题
String title = titles.get(i);
cell = getCell(sheet, 0, i);
cell.setCellStyle(headerStyle);
setText(cell,title);
}
sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = workbook.createCellStyle(); //内容样式
contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
List<PageData> varList = (List<PageData>) model.get("varList");
int varCount = varList.size();
for(int i=0; i<varCount; i++){
PageData vpd = varList.get(i);
for(int j=0;j<len;j++){
String varstr = vpd.getString("var"+(j+1)) != null ? vpd.getString("var"+(j+1)) : "";
cell = getCell(sheet, i+1, j);
cell.setCellStyle(contentStyle);
setText(cell,varstr);
} } } }
  function outputEXCEL(){
// 改变Form表单的action
$("#Form").attr("action","partsOrderForm/excel.do").submit();
// 提交完了之后变回之前的action
$("#Form").attr("action","partsOrderForm/partsOrderFormList.do")
}