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 = getMethodValue(clz, obj, tempSort.get(r));String strValue = converType(value, dateFormat);cell = row.createCell(r);cell.setCellValue(strValue);}}}// FileOutputStream fos = new FileOutputStream("D:/tewst.xls");ByteArrayOutputStream os = new ByteArrayOutputStream();// 字节数组输出流try{wb.write(os);// 写入字节输出流对象是} catch (IOException e){e.printStackTrace();} finally{if (os != null){os.close();}}byte[] bArry = os.toByteArray();// 获取字节数组// 将数组字节 写入到输入流对象InputStream isba = new ByteArrayInputStream(bArry);return isba;}/** * 根据字段获取值 *  * @param clz * @param o * @param field * @return */@SuppressWarnings("rawtypes")public static Object getMethodValue(Class clz, Object o, String field)throws NoSuchMethodException, SecurityException,IllegalAccessException, IllegalArgumentException,InvocationTargetException{Character charss = field.charAt(0);field = field.replaceFirst(charss.toString(),Character.toUpperCase(charss) + "");System.out.println(field);// 获取值@SuppressWarnings("unchecked")Method getMethod = clz.getDeclaredMethod("get" + field, new Class[] {});Object value = getMethod.invoke(o, new Object[] {});return value;}public static String converType(Object value, String dateFormat){String rResult = "";// if (value instanceof Integer)// {// int result = (Integer) value;// return result;//// } else if (value instanceof Float)// {// float result = (Float) value;// return result;//// } else if (value instanceof Double)// {// double result = (Double) value;// return result;//// } else if (value instanceof Long)// {// long result = (Long) value;// return result;// }// if (value instanceof Boolean)// {// boolean result = (Boolean) value;// return result;//// } elseif (value instanceof Date){Date date = (Date) value;if ("".equals(dateFormat) || null == dateFormat){dateFormat = "yyyy-MM-dd";}// IllegalArgumentExceptionSimpleDateFormat sdf = new SimpleDateFormat(dateFormat);rResult = sdf.format(date);} else if (value instanceof byte[]){//} else{return value + "";}return rResult;}}

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]; // 缓存buffertry{// 获取输出流对象//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();}}}