如何在grails中导出Excel列表

一。在src/java文件夹中写一个公共类ExporToExcel.

二。application.properties中写出导出excel表对应列的ascill 转义的网址是：http://www.ostools.net/encode?type=3
比如：csxj=区域-110,部门-100,一、二干光缆长度-110,执行计划人数-112,轨迹长(km)-190
把他转义为csxj=\u533a\u57df-110,\u90e8\u95e8-100,\u4e00\u3001\u4e8c\u5e72\u5149\u7f06\u957f\u5ea6-110,\u6267\u884c\u8ba1\u5212\u4eba\u6570-112,\u8f68\u8ff9\u957f(km)-190
放到application.properties中就Ok了。
三。Controller中写一个getExcel方法 然后调用ExtToExcel.

package org.telecom.xls;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;
import java.util.ResourceBundle;

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.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.InputStream;
import java.io.FileInputStream;

/**
 * 导出Excel
 * @author dailiangjun
 * @version 1.2 
 * @since jdk1.5
 */
public class ExporToExcel {
	
	private int index = 0;
	private String reportName;
	private List<List<String>> result;
	private List<String> heads = new ArrayList<String>();
	private List<Integer> colums = new ArrayList<Integer>();
	private Map<String, CellStyle> styles = null;

	/**
	 * 初始化
	 */
	private  void init(){
    	//Properties property = new Properties();
		//try {
			/*String path = System.getProperty("user.dir");  //获取路径方法
		    path =path.replace("\", "/");
			String filePath = "/"+path+"/application.properties"; //得到Properties文件的目录
            InputStream in =  new FileInputStream(filePath);
			property.load(in);
			String value = (String)property.get(reportName);
			*/
			 /**
			  * 从外存读文件来设置对象的值，有ResourceBundle
				因为FileReader还不能把配置文件和jar包打在一起发布
			  */
			ResourceBundle rb = ResourceBundle.getBundle("application", Locale.getDefault()); 
			String value= rb.getString(reportName); //获取键值
		
			String[] temp = value.split(",");
			for(int i=0;i<temp.length;i++){
				String[] hc = temp[i].split("-");
				if(hc.length!=2){
					throw new RuntimeException("请确列名称和列宽度是否使用-分割!");
				}
				heads.add(hc[0]);
				colums.add(Integer.valueOf(hc[1]));
			}
	//	}// catch (IOException e) {
			//e.printStackTrace();
		//}
	}
	
	/**
	 * 创建表头
	 * @param sheet
	 */
	private void createHead(Sheet sheet){
		CellStyle sytle = styles.get("header");
		Row row = sheet.createRow(index++);
		Cell cell;
		row.setHeightInPoints(23);
		for(int i=0;i<heads.size();i++){
			cell = row.createCell(i);
			cell.setCellValue(heads.get(i));
			cell.setCellStyle(sytle);
		}
	}
	
	/**
	 * 生成内容
	 * @param sheet
	 */
	private void genContent(Sheet sheet){
		CellStyle sytle = styles.get("content");
		int size = result.size();
		for(int i=0;i<size;i++){
			Row row = sheet.createRow(index++);
			List<String> item = result.get(i);
			int length = heads.size();
			for(int colum=0;colum<length;colum++){
				Cell cell = row.createCell(colum);
			//	cell.setCellValue(item.get(colum));
				cell.setCellValue(String.valueOf(item.get(colum)));
				
				cell.setCellStyle(sytle);
			}
		}
	}
	
	/**
	 * 生成样式
	 * @param wk
	 */
	private void createStyle(Workbook wk){
		styles = new HashMap<String, CellStyle>();
		// 普通字体
		Font normalFont = wk.createFont();
		normalFont.setFontHeightInPoints((short) 10);
		
		// 加粗字体
		Font boldFont = wk.createFont();
		boldFont.setFontHeightInPoints((short) 13);
		boldFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		
		// 表头格式
		CellStyle headerStyle = wk.createCellStyle();
		headerStyle.setFont(boldFont);
		headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		styles.put("header", headerStyle);
		
		// 内容格式
		CellStyle contentStyle = wk.createCellStyle();
		contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
		contentStyle.setFont(normalFont);
		styles.put("content", contentStyle);
		
	}
	
	/**
	 * 创建Excel
	 * @return
	 */
	private Workbook genExcel(){
		Workbook wb = new HSSFWorkbook();
		init();
		Sheet sheet = wb.createSheet(reportName);
		for(int i=0;i<heads.size();i++){
			int size = 32 * colums.get(i);
			sheet.setColumnWidth(i,size);
		}
		createStyle(wb);
		createHead(sheet);
		genContent(sheet);
		
		return wb;
	}
	
	/**
	 * 导出
	 * @param name
	 * @param result
	 * @return
	 */
	public static byte[] export(String name,List<List<String>> result){
		if(name==null || " ".equals(name)){
			throw new IllegalArgumentException("需要导出的表名称不能为空!");
		}
		if(result == null){
			throw new IllegalArgumentException("结果集合不能为空!");
		}
		ExporToExcel obj = new ExporToExcel();
		obj.reportName = name;
		obj.result = result;
		ByteArrayOutputStream out = new ByteArrayOutputStream();
		Workbook wb = obj.genExcel();
		try {
			wb.write(out);
			return out.toByteArray();
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		}finally{
			if(out!=null){
				try{
					out.close();
				}catch(IOException e){
					e.printStackTrace();
				}
			}
		}
		
	}

}


   def getExcel = {
	   def filename  = "";
	   def list="";
	   byte[] result="";
	   filename = "错时巡检统计.xls";
	   filename = new String(filename.getBytes("GB2312"),"ISO8859-1");
	   list = geterrorratereport();//geterrorratereport()是我自己写的一个方法，从数据库中获得所有列表的数据。
	   result = ExporToExcel.export("csxj",list);
	   response.reset();
	   response.setContentType("application/octet-stream");
	   response.setHeader("Content-Disposition", "inline;filename="+filename);
	   response.setCharacterEncoding("ISO8859-1");
	   OutputStream out = response.getOutputStream();
	   out.write(result);
	   out.flush();
	   out.close();
	   result="";
	   }