首页 » 技术分享 » Struts2 POI实现导出Excel功能

Struts2 POI实现导出Excel功能

　
评论关闭　812次浏览

在项目中需要将数据导出Excel表格，之前也没接触过，顺便记录一下。

index.jsp页面很简单，只有一个按钮。

<body>
    <s:form action="export">
    	<s:submit value="export"></s:submit>
    </s:form>
</body>

ExportAction如下，数据直接写死，只是简单实现功能而已。

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.opensymphony.xwork2.ActionSupport;

public class ExportAction extends ActionSupport{
	private String attFileName;
	private InputStream attInputStream;

	public String export() throws Exception {
		List<Map> listStudents = new ArrayList<Map>();
		for(int i=0; i<10; i++){
			Map map = new HashMap();
			map.put("name", "student" + i);
			map.put("age", 23 + i);
			map.put("class", "Class " + i);
			map.put("height", 180 + i);
			map.put("weight", 70 + i);
			map.put("birthday", new Date());
			listStudents.add(map);
		}
		String[] keys = {"name", "age", "class", "height", "weight", "birthday"};
		String[] titles= {"姓名", "年龄", "班级", "身高", "体重", "生日"};
		attInputStream = Util.exportToExcel(listStudents, keys, titles);
		this.attFileName = "学生表";
		this.attFileName = new String(this.attFileName.getBytes("GBK"), "ISO-8859-1");
		return "exportToExcel";
	}

	public String getAttFileName() {
		return attFileName;
	}

	public void setAttFileName(String attFileName) {
		this.attFileName = attFileName;
	}

	public InputStream getAttInputStream() {
		return attInputStream;
	}

	public void setAttInputStream(InputStream attInputStream) {
		this.attInputStream = attInputStream;
	}
	
}

Action中用到的Util类实现如下，也是最主要的导出实现类。

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import freemarker.template.utility.DateUtil;


public class Util {
	public static InputStream exportToExcel(List<Map> listMap, String[] keys,
			String[] titles) throws IOException {
		//新建Excel
		XSSFWorkbook workbook = new XSSFWorkbook();
		//新建Sheet
		XSSFSheet sheet = workbook.createSheet("sheet1");
		//设置表头格式
		XSSFCellStyle headStyle = workbook.createCellStyle();
		headStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 上下居中
		headStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
		XSSFFont font = workbook.createFont();
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗
		font.setFontName("宋体");
		headStyle.setFont(font);
		//设置表行格式
		XSSFCellStyle lineStyle = workbook.createCellStyle();
		lineStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
		lineStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
		lineStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);//设置边框
		lineStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
		lineStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
		lineStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
		lineStyle.setWrapText(true);//自动换行
		//写表头
		int rowIndex = 0;
		XSSFRow row = sheet.createRow(rowIndex++);
		for (int i = 0; i < titles.length; i++) {
			XSSFCell cell = row.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(titles[i]);
		}
		//写表行
		for(Map map : listMap){
			row = sheet.createRow(rowIndex++);
			for(int i=0; i<keys.length; i++){
				XSSFCell cell = row.createCell(i);
				String key = keys[i];
				if(!key.equals("") && map.get(key)!=null){
					if (map.get(key) instanceof Date) {
						String value = dateToString((Date) map.get(key));
						cell.setCellValue(value);
					} else if (map.get(key) instanceof BigDecimal || map.get(key) instanceof Double || map.get(key) instanceof Float) {
						Double d = convertToDouble(map.get(key));
						cell.setCellValue(d);
					} else {
						String value = map.get(key).toString();
						cell.setCellValue(value);
					}
				}
				cell.setCellStyle(lineStyle);
			}
		}
		//生成流
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		InputStream is = null;
		try{
			workbook.write(os);
			os.flush();
			byte[] content = os.toByteArray();
			is = new ByteArrayInputStream(content);
		}catch(Exception e){
			if(os != null){
				os.close();
				os = null;
			}
		}
		return is;
	}

	private static Double convertToDouble(Object object) {
		// TODO Auto-generated method stub
		if (object == null || object.toString().equals("")) {
			return 0.0;
		}
		return Double.parseDouble(object.toString());
	}

	private static String dateToString(Date date) {
		// TODO Auto-generated method stub
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String cdate = formatter.format(date);
		return cdate;
	}
}

最后就是Struts2配置文件。

<package name="test" namespace="/" extends="struts-default">  
        <action name="export" class="ExportAction" method="export">  
            <result name="exportToExcel" type="stream">  
				<param name="contentType">application/vnd.ms-excel</param>  
				<param name="contentDisposition">attachment;filename="${attFileName}.xlsx"</param>  
				<param name="inputName">attInputStream</param>  
			</result> 
        </action>  
</package>

实现效果如下：

转载自原文链接, 如需删除请联系管理员。

原文链接：Struts2 POI实现导出Excel功能，转载请注明来源！

赞 0