spring视图3-Excel视图

最新推荐文章于 2022-11-07 21:29:33 发布

meililiuwei

最新推荐文章于 2022-11-07 21:29:33 发布

阅读量541

点赞数

分类专栏： spring 文章标签： spring excel java

本文链接：https://blog.csdn.net/meililiuwei/article/details/21461973

版权

spring 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

使用 poi-3.9导出Excel文件

需要 jar 包 poi-3.9.jar （MS-excel2003），

其他的是 MS-Excel2007 需要的 jar poi-ooxml-schemas-3.9.jar ，dom4j-1.6.1.jar，stax-api-1.0.1.jar，xmlbeans-2.3.0.jar，poi-ooxml-3.9.jar

spring 配置文件中需要增加

<!-- 使用 poi 导出Excel文件 -->
	<bean name="ExcelPoiView" class="com.common.view.SpringPoiExcelView"></bean>

视图解析器 SpringPoiExcelView 文件内容

package com.common.view;

import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

public class SpringPoiExcelView extends AbstractExcelView {

	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest req, HttpServletResponse res)
			throws Exception {
		String[] sheetNames = (String[])model.get("sheetNames");
		String headName = (String)model.get("headName");
		LinkedHashMap<String,String> columns = (LinkedHashMap<String,String>)model.get("columns");
		List list = (List)model.get("data");
		HSSFSheet sheet =null;
		if(sheetNames != null &&sheetNames.length>0){
			sheet= workbook.createSheet(sheetNames[0]);
		}else{
			sheet= workbook.createSheet();
		}
		sheet.setDefaultColumnWidth(columns.size());
		HSSFCell cell=getCell(sheet,0, 0);
		setText(cell, headName);
		Collection col= columns.values();
		Set keyset= columns.keySet();
		Iterator it =col.iterator();
		int x= 0;
		while(it.hasNext()){
			cell = getCell(sheet,1,x);
			setText(cell, it.next().toString());
			x++;
		}
		
		for(int i=0;i<list.size();i++){
			JSONObject json = JSONObject.fromObject(list.get(i));
			it = keyset.iterator();
		    x= 0;
			while(it.hasNext()){
				cell = getCell(sheet,i+2,x);
				setText(cell, json.getString(it.next().toString()));
				x++;
			}
		}
	    res.setHeader("Content-Disposition", "filename="
                + new String("测试123.xls".getBytes("gb2312"), "iso8859-1"));
		
	}
	
	

}

需要继承spring自带的 Excel 视图类 AbstractExcelView，但此类只能用于 Excel 2003 的导出

control 内容

	@RequestMapping(value="/test/excel")
	public ModelAndView showExcel(){
		System.out.println("转化Excel开始");
		TJM01 jm = new TJM01();
		jm.setAac001(1l);
		jm.setAac003("张1");
		jm.setAac004("男1");
		
		TJM01 jm2 = new TJM01();
		jm2.setAac001(2l);
		jm2.setAac003("张2");
		jm2.setAac004("男2");
		
		TJM01 jm3 = new TJM01();
		jm3.setAac001(3l);
		jm3.setAac003("张3");
		jm3.setAac004("男3");
		
		TJM01 jm4 = new TJM01();
		jm4.setAac001(4l);
		jm4.setAac003("张4");
		jm4.setAac004("男4");
		
		TJM01 jm5 = new TJM01();
		jm5.setAac001(5l);
		jm5.setAac003("张5");
		jm5.setAac004("男5");
		
		TJM01 jm6 = new TJM01();
		jm6.setAac001(6l);
		jm6.setAac003("张6");
		jm6.setAac004("男6");
		
		List list = new ArrayList();
		list.add(jm);
		list.add(jm2);
		list.add(jm3);
		list.add(jm4);
		list.add(jm5);
		list.add(jm6);
		Map map = new HashMap();
		map.put("data", list);
		
		LinkedHashMap<String ,String> lhm = new LinkedHashMap<String, String>();
		lhm.put("aac001", "人员医保号");
		lhm.put("aac003", "姓名");
		lhm.put("aac004", "性别");
		map.put("columns", lhm);
		
		return new ModelAndView("ExcelPoiView",map);
	}

我们在浏览器输入 http://localhost:8080/atoty/test/excel.do ，便能导出 Excel 文件

但我们需要导出的是 Excel2003和 Excel2007 都能用的视图,，此时我们需要继承 spring 抽象视图类 AbstractView

SpringPoiExcelView 的内容

package com.common.view;

import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.servlet.view.AbstractView;

import com.common.tools.exceptions.BussException;

public class SpringPoiExcelView extends AbstractView {
	private static final String default_content_type = "application/vnd.ms-excel";
	private static final String default_char_encoding = "UTF-8";
	private String encoding = default_char_encoding;
	private String fileName="Excel文件导出.xls";
	private String[] sheetNames={"1","2","3"};
	private String headName;
	
	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public void setSheetNames(String[] sheetNames) {
		this.sheetNames = sheetNames;
	}

	public void setHeadName(String headName) {
		this.headName = headName;
	}

	public String getEncoding() {
		return encoding;
	}

	public void setEncoding(String encoding) {
		this.encoding = encoding;
	}

	public SpringPoiExcelView() {
		setContentType(default_content_type);
	}

	public void renderMergedOutputModel(Map<String, Object> model,
			HttpServletRequest req, HttpServletResponse res) throws Exception {
		Workbook wb =null;
		fillDefaultParam(model);
		if(!isExcel2003(model)){
			wb = new XSSFWorkbook();
		}else{
			wb = new HSSFWorkbook();
		}
		buildDocumentExcel(wb,model,req,res);
		res.setHeader("Content-Disposition", "filename="
                + new String(fileName.getBytes("gb2312"), "iso8859-1"));
		res.setContentType(getContentType());
		ServletOutputStream out = res.getOutputStream();
		wb.write(out);
		out.flush();
	}
	/**
	 * 判断 输入参数，参数没赋值，使用默认参数
	 * @param model
	 */
	private void fillDefaultParam(Map<String, Object> model){
		if(model.get("fileName") != null){
			fileName=model.get("fileName").toString();
		}
		if(model.get("sheetNames")!= null){
			sheetNames = (String[])model.get("sheetNames");
		}
		if(model.get("headName") != null){
			headName = model.get("headName").toString();
		}
		if(!fileName.endsWith(".xls")&&!fileName.endsWith(".xlsx")){
			throw new BussException("配置或赋值的Excel导出文件名不是合法的Excel文件名称");
		}
	}
	
	private void buildDocumentExcel(Workbook wb, Map<String, Object> model,
			HttpServletRequest req, HttpServletResponse res) {
		Sheet sheet = null;
		LinkedHashMap<String,String> columns = (LinkedHashMap<String,String>)model.get("columns");
		List datas = (List)model.get("data");
		if(sheetNames.length>0 && sheetNames[0]!= null){
			sheet= wb.createSheet(sheetNames[0]);
		}else{
			sheet=wb.createSheet();
		}
		sheet.setDefaultColumnWidth(columns.size());
		createExcelHead(columns,sheet);
		fillData(sheet,datas,columns);
	}

	private void fillData(Sheet sheet,List list, LinkedHashMap<String, String> columns) {
		if(list == null|| list.size()<= 0){
			return;
		}
		for(int i=0;i<list.size();i++){
			JSONObject json = JSONObject.fromObject(list.get(i));
			Iterator it = columns.keySet().iterator();
		    int x= 0;
			while(it.hasNext()){
				String column =it.next().toString();
				if(json.get(column) != null){
					setCellValue(sheet, i+2, x, json.getString(column));
				}else{
					setCellValue(sheet, i+2, x, "");
				}
				x++;
			}
		}		
	}

	private void createExcelHead(LinkedHashMap<String, String> columns,
			Sheet sheet) {
		if(columns == null || columns.size() <= 0){
			throw new BussException("需要导出的Excel列信息不能为空");
		}
		setCellValue(sheet,0, 0, headName);
		Collection<String> col = columns.values();
		Iterator it =col.iterator();
		int x= 0;
		while(it.hasNext()){
			if(it.next() == null || "".equals(it.next().toString())){
				throw new BussException("需要导出的Excel的 "+(x+1)+"列信息不能为空");
			}
			setCellValue(sheet, 1, x, it.next().toString());
			x++;
		}
	}

	/**
	 * 判断需要导出的Excel 文件 是  ms-Excel2003  版本 
	 * @param model 传入 的model
	 * 判断当不传入 文件名时，默认导出2003版
	 * @return 返回导出文件是否是  ms-Excel2003 
	 */
	
	private boolean isExcel2003(Map<String, Object> model){
		if(fileName != null && fileName.toString().endsWith(".xlsx")){
			return false;
		}else{
			return true;
		}
	}
	/**
	 * 为Excel 文件的单元格 赋值
	 * @param sheet Excel 当前工作的工作薄
	 * @param row   单元格所在的行
	 * @param col   单元格位置
	 * @param value 单元格赋入的值
	 */
	private void setCellValue(Sheet sheet,int row,int col,String value){
		Row sheetRow = sheet.getRow(row);
		if (sheetRow == null) {
			sheetRow = sheet.createRow(row);
		}
		Cell cell = sheetRow.getCell((short) col);
		if (cell == null) {
			cell = sheetRow.createCell((short) col);
		}
		cell.setCellType(cell.CELL_TYPE_STRING);
		cell.setCellValue(value);
	}

}

这样，我们就可以根据传入或注入的文件名导出 Excel2003 或2007 的文件了

文中所需要的poi3.9 及其他jar 下载免积分

http://download.csdn.net/detail/meililiuwei/7057639