首页 > 代码库 > java 导出xls 通用工具类

java 导出xls 通用工具类

java  导出xls 通用工具类

package org.rui..util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

/**
 * 导入xls表格 把数据转换为数据流
 * 
 * 
 * @author liangrui
 * 
 *         encodFileNmae = java.net.URLEncoder.encode(tableName+".xls",
 *         "utf-8");
 *         response.setContentType("application/vnd.ms-excel;charset=utf-8");
 *         response.setHeader("Content-Disposition", "attachment;filename=" +
 *         encodFileNmae);
 *
 */
public class XlsUtil
{

	/**
	 * 把数据转换为输出流
	 * 
	 * @param sheetName
	 *            sheet名称
	 * @param header
	 *            表头map
	 * @param list
	 *            数据集合
	 * @param clz
	 *            对象字节码
	 * @param dateFormat
	 *            如果有日期 格式化的日期 默认 yyyy-MM-dd
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("rawtypes")
	public static InputStream getXlsIO(String sheetName,
			Map<String, String> header, List<Object> list, Class clz,
			String dateFormat) throws Exception
	{

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet(sheetName);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth(15);

		HSSFRow row = sheet.createRow(0);
		// 第四步,创建单元格,并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		// 设置这些样式
		// style.setFillForegroundColor(HSSFColor.CORAL.index);//back
		// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		// style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		// style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

		// 生成一个字体
		HSSFFont font = wb.createFont();
		font.setColor(HSSFColor.RED.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样式
		style.setFont(font);

		HSSFCell cell;

		List<String> tempSort = new ArrayList<String>();
		int cellIndex = 0;
		for (Entry<String, String> es : header.entrySet())
		{
			tempSort.add(es.getValue());
			cell = row.createCell(cellIndex);// 创建一个单元格,并放入数据
			cell.setCellStyle(style);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);// 指定编码
			cell.setCellValue(es.getKey()); // 设值
			cellIndex++;
		}

		if (list != null)
		{
			for (int i = 0; i < list.size(); ++i)
			{
				Object obj = clz.newInstance();
				obj = list.get(i);
				row = sheet.createRow(i + 1);// 创建一行,从0开始

				// 根据 v 找到方法 并获取值
				for (int r = 0; r < tempSort.size(); r++)
				{
					// 获取值
					Object value = http://www.mamicode.com/getMethodValue(clz, obj, tempSort.get(r));>
package org.servlet;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.utils.XlsUtil;

import test.Student;

/**
 * Servlet implementation class ExpXls
 */
@WebServlet("/ExpXls")
public class ExpXls extends HttpServlet
{
	private static final long serialVersionUID = 1L;

	/**
	 * Default constructor.
	 */
	public ExpXls()
	{

	}

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

	}

	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException
	{
		
		String tableName="报表名";
		try
		{
			Map<String, String> m = new HashMap<String, String>();
			m.put("id", "id");
			m.put("姓名", "name");
			m.put("年龄", "age");
			m.put("生日", "birth");

			List<Object> list = new ArrayList<Object>();
			SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");

			Student user1 = new Student(1, "张三张三张三张三张三张三三", 16,
					df.parse("1997-03-12"));
			Student user2 = new Student(2, "李四", 17, df.parse("1996-08-12"));
			Student user3 = new Student(3, "王五", 26, df.parse("1985-11-12"));
			list.add(user1);
			list.add(user2);
			list.add(user3);

			InputStream is = XlsUtil.getXlsIO("数据报表", m, list, Student.class,"");

			String encodFileNmae = "";

			encodFileNmae = java.net.URLEncoder.encode(tableName+".xls",
						"utf-8");
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
			response.setHeader("Content-Disposition", "attachment;filename="
					+ encodFileNmae);

			// 输出流
			OutputStream os = null;
			// ByteArrayOutputStream os=null;
			int leng = 0;
			byte[] bytes = new byte[1024]; // 缓存buffer
			try
			{
				// 获取输出流对象
				//os = response.out;
				 os=response.getOutputStream();
				// k始读取
				while ((leng = is.read(bytes)) > 0)
				{
					// 开始写入
					os.write(bytes, 0, leng);
				}

			} catch (Exception e)
			{
				e.printStackTrace();
			} finally
			{
				try
				{
					if (is != null)
					{
						is.close();
					}
					if (os != null)
					{
						os.close();
					}
				} catch (IOException e)
				{
					
					e.printStackTrace();
				}
			}

		} catch (Exception e)
		{

			e.printStackTrace();
		}

	}

}


java 导出xls 通用工具类