savetoexcel.java

很好的java excle组件

package com.tools;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;

public class SaveToExcel extends HttpServlet {

	private static final long serialVersionUID = 1L;

	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {

		//String sheetName = (String) request.getAttribute("SheetName");
          String sheetName =request.getParameter("SheetName");
		if (null == sheetName || "".equals(sheetName)) {
			sheetName = "sheet0";
		}
		// 设置输出的格式
		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=GBK");
		// response.setContentType("application/msexcel");
		response.addHeader("Content-Disposition", "attachment; filename="
				+ sheetName + ".xls");

		// PrintWriter out = response.getWriter();
		HttpSession session = request.getSession(false);
		
		//从session中获取数据
//		ArrayList<Map<String, String>> dataList = (ArrayList<Map<String, String>>) session
//				.getAttribute("resultList");
//		ArrayList<String> colNameList = (ArrayList<String>) session
//				.getAttribute("colNameList");
		
		//临时获取数据
		ArrayList<Map<String, String>> dataList = this.getData();
         ArrayList<String> colNameList =this.getColNameList();

		// 总列数
		int colCount = colNameList.size();
		// 数据集总行数
		int rowCount = dataList.size();

		String path = request.getRealPath("/");
		System.out.println("########## path:" + path);
		String configpath =this.getServletConfig().getServletContext().getRealPath("/");
		System.out.println("########## config path:" + configpath);
		// 创建Excel文档
		// File newFile = new File(path);
		// if (!newFile.exists()) {
		// newFile.createNewFile();
		// }
		// 读取Excel文档
		// FileInputStream fileInput = new FileInputStream(path);
		// POIFSFileSystem fs = new POIFSFileSystem(fileInput);
		ExcelTools excelTools = ExcelTools.getInstance();
		// 创建工作薄
		HSSFWorkbook wb = excelTools.createWorkBook();
		// 创建工作表
		HSSFSheet sheet = excelTools.createSheet(wb, 0, sheetName);
		// 填充标题
		excelTools.fillTitle(sheet, sheetName, 0, (short) 0);

		// 设置样式-------begin---------
		// HSSFCellStyle titleStyle = wb.createCellStyle(); // 在工作薄的基础上建立一个样式
		// titleStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE); // 设置边框样式
		// titleStyle.setBorderLeft((short) 1); // 左边框
		// titleStyle.setBorderRight((short) 1); // 右边框
		// titleStyle.setBorderTop(HSSFCellStyle.BORDER_DOUBLE); // 顶边框
		// titleStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index); //
		// 填充的背景颜色
		// titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充图案
		// 设置样式-------end---------

		// 合并单元格
	    excelTools.mergeCell(sheet, 0, (short) 0, 0, (short) (colCount - 1));

		// 填充列名
		excelTools.fillColName(sheet, colNameList, 1);
		// 填充行数据
		excelTools.fillCellData(sheet, dataList, colNameList, 2);

		try {
			// FileOutputStream fout = new FileOutputStream(path);
			// 储存
			// wb.write(fout);
			wb.write(response.getOutputStream());
			// fout.close();
			// out.println("<div align=\"center\">保存成功<a
			// href='/struts/asset.xls'>查看asset.xls</a></div>");
			// 读到流中
			// InputStream inStream=new FileInputStream(path);

			// 循环取出流中的数据
			// byte[] b = new byte[100];
			// int len;
			// while((len=inStream.read(b)) >0)
			// response.getOutputStream().write(b,0,len);
			//			  
			// inStream.close();
		} catch (IOException e) {
			// out.println("更新失败，错误讯息：" + e.toString());
		}
	}

	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		this.doPost(request, response);
	}

	
	/**
	 * 填充数据
	 * @return
	 */
	private ArrayList<Map<String, String>> getData(){
		
		ArrayList<Map<String,String>> data = new ArrayList<Map<String,String>>();
		Map<String,String> map1 = new HashMap<String,String>();
		map1.put("a", "1a1");
		map1.put("b", "1a2");
		map1.put("c", "1a3");
		map1.put("d", "1a4");
		map1.put("e", "1a5");
		data.add(map1);
		
		Map<String,String> map2 = new HashMap<String,String>();
		map2.put("a", "2a1");
		map2.put("b", "2a2");
		map2.put("c", "2a3");
		map2.put("d", "2a4");
		map2.put("e", "2a5");
		data.add(map2);
		
		Map<String,String> map3 = new HashMap<String,String>();
		map3.put("a", "3a1");
		map3.put("b", "3a2");
		map3.put("c", "3a3");
		map3.put("d", "3a4");
		map3.put("e", "3a5");
		data.add(map3);
		
		Map<String,String> map4 = new HashMap<String,String>();
		map4.put("a", "4a1");
		map4.put("b", "4a2");
		map4.put("c", "4a3");
		map4.put("d", "4a4");
		map4.put("e", "4a5");
		data.add(map4);
		
		Map<String,String> map5 = new HashMap<String,String>();
		map5.put("a", "5a1");
		map5.put("b", "5a2");
		map5.put("c", "5a3");
		map5.put("d", "5a4");
		map5.put("e", "5a5");
		data.add(map5);
		
		return data;
		
	}
	
	/**
	 * 获取列名
	 * @return
	 */
	private ArrayList<String> getColNameList(){
		
		ArrayList<String> name = new ArrayList<String>();
		
		name.add("a");
		name.add("b");
		name.add("c");
		name.add("d");
		name.add("e");
		return name;
	}
}