精华内容
下载资源
问答
  • 标题说的很清楚,利用asm-3.1.jar,cglib-2.2.jar,commons-io-1.3.2.jar,poi-3.9-20121203.jar 开发的一个工具包。 其中用到字节码编码,反射,excel操作。...主要功能1、自定义列导出excel Main()类为测试用例
  • java实现Excel动态列导出的简单例子

    千次阅读 2020-05-16 16:39:48
    可以通过该对象的set方法设置各个参数,headKey保存结果集中,数据对应的key值,ArrayList最佳,保证了导出列的顺序,同时限制了每个sheet页保存的最大数据行数为5W条,这些参数也可以放到属性中进行设置。...

    可以通过该对象的set方法设置各个参数,headKey保存结果集中,数据对应的key值,ArrayList最佳,保证了导出列的顺序,同时限制了每个sheet页保存的最大数据行数为5W条,这些参数也可以放到属性中进行设置。

    @Data
    public class ExcelExportUtil {
    
        //表头
        private String title;
        //各个列的表头
        private List<String> headList;
        //各个列的元素key值
        private List<String> headKey;
        // 各个列的宽度
        private List<Integer> headWidth;
        //需要填充的数据信息
        private List<HashMap> data;
        //字体大小
        private int fontSize = 14;
        //行高
        private int rowHeight = 30;
        //列宽
        private int columWidth = 200;
        //工作表
        private String sheetName = "sheet";
        // 文件名
        private String fileName;
    
        HSSFWorkbook wb;
    
        HSSFSheet sheet;
    
        HSSFCellStyle headCellStyle;
    
        HttpServletResponse response;
    
        public ExcelExportUtil exportExport(HttpServletResponse response) throws IOException {
            this.response = response;
            wb = new HSSFWorkbook();
            int size = data.size();
            int count = (size - 1) / 50000 + 1;
            for (int i = 0; i < count; i++) {
                sheet = wb.createSheet(sheetName + (i + 1));
                exportExport(sheet,
                        data.subList(i * 50000, ((i + 1) * 50000 > size ? size : 50000 * (i + 1))));
            }
            return this;
        }
    
        private void exportExport(HSSFSheet sheet, List<HashMap> data) throws IOException{
    
            //检查参数配置信息
            checkConfig();
            HSSFCellStyle cellStyle = wb.createCellStyle();
            HSSFDataFormat format = wb.createDataFormat();
            //这样才能真正的控制单元格格式,@就是指文本型
            cellStyle.setDataFormat(format.getFormat("@"));
    
            HSSFRow headRow = sheet.createRow(0);
            //设置列头元素
            for (int i = 0; i < headList.size(); i++) {
                Integer width = 15;
                if (headWidth != null && headWidth.size() >= headList.size()){
                    width = headWidth.get(i);
                }
                sheet.setColumnWidth(i, 256 * width + 184);
                HSSFCell cellHead = headRow.createCell(i);
                cellHead.setCellValue(headList.get(i));
                cellHead.setCellStyle(headCellStyle);
            }
    
            //开始写入实体数据信息
            int a = 1;
            for (int i = 0; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(a);
                HashMap map = data.get(i);
                HSSFCell cell;
                for (int j = 0; j < headKey.size(); j++) {
                    cell = row.createCell(j);
                    Object valueObject = map.get(headKey.get(j));
                    if (valueObject == null) {
                        valueObject = "";
                    }
                    if (valueObject instanceof Integer) {
                        //取出的数据是Integer
                        cell.setCellValue(((Integer) (valueObject)).floatValue());
                    } else if (valueObject instanceof BigDecimal) {
                        //取出的数据是BigDecimal
                        cell.setCellValue(((BigDecimal) (valueObject)).floatValue());
                    } else {
                        //取出的数据是字符串直接赋值
                        cell.setCellStyle(cellStyle);
                        cell.setCellValue(Strings.isNullOrEmpty(String.valueOf(valueObject)) ? "" : String.valueOf(valueObject));
                    }
                }
                a++;
            }
        }
    
        public void flushExplorer() throws Exception{
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));
            wb.write(response.getOutputStream());
        }
    
        /**
         * 检查数据配置问题
         *
         * @throws IOException 抛出数据异常类
         */
        protected void checkConfig() throws IOException {
            if (headKey == null) {
                throw new IOException("表头不能为空");
            }
    
            if (headWidth != null && headWidth.size() < headKey.size()){
                throw new IOException("设置宽度的列数必须超过表头列数");
            }
    
            if (fontSize < 0 || rowHeight < 0 || columWidth < 0) {
                throw new IOException("字体、宽度或者高度不能为负值");
            }
    
            if (Strings.isNullOrEmpty(sheetName)) {
                throw new IOException("工作表表名不能为NULL");
            }
            createDefaultHeadStyle();
        }
    
        public void createDefaultHeadStyle() {
            //创建表头样式
            headCellStyle= wb.createCellStyle();
            //居中
            headCellStyle.setAlignment(HorizontalAlignment.LEFT);
            //背景色
            headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
    
            //字体
            HSSFFont font = wb.createFont();
            font.setFontHeightInPoints((short)12);
            font.setBold(true);         //字体增粗
            headCellStyle.setFont(font);
    
            headCellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
            headCellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
            headCellStyle.setBorderTop(BorderStyle.THIN);// 上边框
            headCellStyle.setBorderRight(BorderStyle.THIN);// 右边框
        }
    }
    
    

    简单的调用例子:

    		ExcelExportUtil excelExportUtil = new ExcelExportUtil();
    		// 表头固定列
            List<String> headList = new ArrayList<>();
            headList.add("省");
            if (param.getCity() != 0){
                headList.add("市");
            }
    
            // 表头固定key
            List<String> headKey = new ArrayList<>();
            headKey.add("province");
            if (param.getCity() != 0){
                headKey.add("city");
            }
    
            for (HashMap dateInfo : date){
                for (String key : param.getNeedType()){
                    headList.add(dateInfo.get("day") + "日" + ConstantUtils.NEED_MAP.get(key));
                    headKey.add(dateInfo.get("day") + "_" + key);
                }
            }
    
            excelExportUtil.setHeadList(headList);
            excelExportUtil.setHeadKey(headKey);
            excelExportUtil.setData(list);
            excelExportUtil.setFileName("XXX日账");
            excelExportUtil.exportExport(response).flushExplorer();
    
    展开全文
  • JAVA导出EXCEL实现

    万次阅读 多人点赞 2019-07-31 18:39:28
    JAVA导出EXCEL实现

    ##JAVA导出EXCEL实现的多种方式
    java导出Excel的方法有多种,最为常用的方式就是使用第三方jar包,目前POI和JXL是最常用的二方包了,也推荐使用这两种。
    ###POI实现
    POI这里不详细解释,可参考徐老师发的博客:http://blog.csdn.net/evangel_z/article/details/7332535,他利用开源组件POI3.0.2动态导出EXCEL文档的通用处理类ExportExcel,详细使用方法下载最新代码看看就可以里,徐老师写的很明了!总之思路就是用Servlet接受post、get请求,获取文件导出路径,然后将测试数据封装好调用通用处理类导出Excel,然后再下载刚导出的Excel,会自动在浏览器弹出选择保存路径的弹出框,这样就达到里大家常见的文件导出下载的功能!当然,真正的项目里不可能把文件导出到本地,肯定是先吧文件导出到服务器上,再去服务器下载,对于用户来说就感觉好像直接就导出了!
    这种实现逻辑也可以修改,就是把通用处理类ExportExcel从void改为返回read好数据的InputStream,而不要直接就去write,然后调用下载的方法downLoad使用HttpServletResponse.getOutputStream()所得到的输出流来write数据,然后调用flush()时就会在页面弹出选择路径的弹出框,选择好后数据就真正从缓存输出到了Excel中,这样就省去里中间先要导出一次的步骤了。
    ###JXL实现
    我这里讲一下JXL,其实和POI差不多,就是调用的组件不同,引入的jar包不同了,整个Excel导出下载的逻辑还是一样的。好了,直接上代码,都是通用代码,以后都能用的上。
    先是几个mode类封装了在处理过程中会用到的模型。
    ExcelColMode 主要封装的是Map中的key或者dto中实现get方法的字段名,其实就是表格的标题的属性名。

    public class ExcelColMode {
    
    	/**
    	 * Map中的key或者dto中实现get方法的字段名
    	 */
    	private String name;
    
    	/** 列宽 */
    	private Integer width;
    
    	/**
    	 * 字体格式,可以设置字体大小,字体颜色,字体加粗
    	 */
    	private ExcelFontFormat fontFormat;
    
    	/**
    	 * 内容格式化
    	 */
    	private ExcelColModelFormatterInter contentFormatter;
    
    	public ExcelColMode(String name) {
    		this.name = name;
    	}
    
    	public ExcelColMode(String name, Integer width) {
    		this.name = name;
    		this.width = width;
    	}
    
    	public String getName() {
    		return name;
    	}
    
    	public void setName(String name) {
    		this.name = name;
    	}
    
    	public ExcelFontFormat getFontFormat() {
    		return fontFormat;
    	}
    
    	public void setFontFormat(ExcelFontFormat fontFormat) {
    		this.fontFormat = fontFormat;
    	}
    
    	public ExcelColModelFormatterInter getContentFormatter() {
    		return contentFormatter;
    	}
    
    	public void setContentFormatter(ExcelColModelFormatterInter contentFormatter) {
    		this.contentFormatter = contentFormatter
    	}
    
    	public Integer getWidth() {
    		return width;
    	}
    
    	public void setWidth(Integer width) {
    		this.width = width;
    	}
    
    }
    

    ExcelHeadCell 主要封装的是标题名

    public class ExcelHeadCell implements Comparable<ExcelHeadCell> {
    
    	/**
    	 * 列合并
    	 */
    	private int colSpan;
    
    	/**
    	 * 展现字符内容
    	 */
    	private String content;
    
    	/**
    	 * 父列的序列号
    	 */
    	private int fatherIndex;
    
    	/**
    	 * 字体格式等
    	 */
    	private ExcelFontFormat fontFormat;
    
    	private Integer height;
    
    	/**
    	 * 最基础的单元格,没有行合并和列合并
    	 * 
    	 * @param content
    	 */
    	public ExcelHeadCell(String content) {
    		this.colSpan = 1;
    		this.content = content;
    	}
    
    	public ExcelHeadCell(String content, Integer height) {
    		this.colSpan = 1;
    		this.content = content;
    		this.height = height;
    	}
    
    	public ExcelHeadCell(String content, int fatherIndex, ExcelFontFormat fontFormat) {
    		this.colSpan = 1;
    		this.content = content;
    		this.fatherIndex = fatherIndex;
    		this.fontFormat = fontFormat;
    	}
    
    	public int getColSpan() {
    		return colSpan;
    	}
    
    	public void setColSpan(int colSpan) {
    		this.colSpan = colSpan;
    	}
    
    	public String getContent() {
            return content;
    	}
    
    	public void setContent(String content) {
    		this.content = content;
    	}
    
    	public ExcelFontFormat getFontFormat() {
    		return fontFormat;
    	}
    
    	public void setFontFormat(ExcelFontFormat fontFormat) {
    		this.fontFormat = fontFormat;
    	}
    
    	public int getFatherIndex() {
    		return fatherIndex;
    	}
    
    	public void setFatherIndex(int fatherIndex) {
    		this.fatherIndex = fatherIndex;
    	}
    
    	public Integer getHeight() {
    		return height;
    	}
    
    	public void setHeight(Integer height) {
    		this.height = height;
    	}
    
    	public int compareTo(ExcelHeadCell o) {
    		int i = -1;
    		if (o == null) {
    			i = 1;
    		} else {
    			i = o.fatherIndex > this.fatherIndex ? -1 : 1;
    			if (o.fatherIndex == this.fatherIndex) {
    				i = 0;
    			}
    		}
    		return i;
    	}
    }
    

    ExcelExportRule 主要封装的是之前的ExcelColMode和ExcelHeadCell以及sheet页名称sheetName

    public class ExcelExportRule {
    
    	/**
    	 * 封装如何从数据集取数据,数据显示格式,日期格式和数字格式在这里设置
    	 */
    	private List<ExcelColMode> colModes;
    
    	/**
    	 * 封装EXCEL头部内容及内容显示格式
    	 */
    	private List<List<ExcelHeadCell>> headCols;
    
    	/**
    	 * 数据背景颜色区分,0:不区分,1:按行奇偶区分,奇数行白色,偶数行灰色,2:按列奇偶区分 奇数列白色,偶数列灰色, <br/>
    	 * <b>注意:此参数为0时,单元格设置的背景色才起作用</b>
    	 */
    	private int distinguishable = 0;
    
    	/**
    	 * EXCEL的sheet页名称
    	 */
    	private String sheetName;
    
    	/**
    	 * 是否树形结构,1:是,0:否
    	 */
    	private String hierarchical = "0";
    
    	/**
    	 * id字段名,当hierarchical="1"时候才起作用
    	 */
    	private String idName;
    
    	/**
    	 * 父id字段名,当hierarchical="1"时候才起作用
    	 */
    	private String pidName;
    
    	public List<ExcelColMode> getColModes() {
    		return colModes;
    	}
    
    	public void setColModes(List<ExcelColMode> colModes) {
    		this.colModes = colModes;
    	}
    
    	public List<List<ExcelHeadCell>> getHeadCols() {
    		return headCols;
    	}
    
    	public void setHeadCols(List<List<ExcelHeadCell>> headCols) {
    		this.headCols = headCols;
    	}
    
    	public int getDistinguishable() {
    		return distinguishable;
    	}
    
    	public void setDistinguishable(int distinguishable) {
    		this.distinguishable = distinguishable;
    	}
    
    	public String getSheetName() {
    		return sheetName;
    	}
    
    	public void setSheetName(String sheetName) {
    		this.sheetName = sheetName;
    	}
    
    	public String getHierarchical() {
    		return hierarchical;
    	}
    
    	public void setHierarchical(String hierarchical) {
    		this.hierarchical = hierarchical;
    	}
    
    	public String getIdName() {
    		return idName;
    	}
    
    	public void setIdName(String idName) {
    		this.idName = idName;
    	}
    
    	public String getPidName() {
    		return pidName;
    	}
    
    	public void setPidName(String pidName) {
    		this.pidName = pidName;
    	}
    
    	public void addExcelColMode(ExcelColMode excelColMode) {
    		if (colModes == null)
    			colModes = new ArrayList<ExcelColMode>();
    		colModes.add(excelColMode);
    	}
    
    	public void addExcelHeadCellList(List<ExcelHeadCell> list) {
    		if (headCols == null)
    			headCols = new ArrayList<List<ExcelHeadCell>>();
    		headCols.add(list);
    	}
    
    }
    

    ExcelFontFormat 封装的是表格的一些样式,如果对此没什么要求可以忽略

    public class ExcelFontFormat {
    
    	private int font = 0; // 字体 0:宋体,1:楷体,2:黑体,3:仿宋体,4:隶书
    	private Colour color = Colour.BLACK; // 字体颜色
    	private boolean bold = false; // 是否加粗
    	private int flow = 0; // 文字浮动方向,0:靠左(默认),1:居中,2:靠右,
    	private int fontSize = 0; // 文字大小,0:正常,-2,-1,0,1,2,3,4依次加大,最大到4
    	private Colour backgroundColor = Colour.WHITE; // 单元格填充色
    	private boolean italic;// 是否斜体
    	private int verticalAlign = 1; // 文字上下对齐 0:上 1:中 2:下
    
    	public int getFont() {
    		return font;
    	}
    
    	public void setFont(int font) {
    		this.font = font;
    	}
    
    	public Colour getColor() {
    		return color;
    	}
    
    	public void setColor(Colour color) {
    		this.color = color;
    	}
    
    	public Colour getBackgroundColor() {
    		return backgroundColor;
    	}
    
    	public void setBackgroundColor(Colour backgroundColor) {
    		this.backgroundColor = backgroundColor;
    	}
    
    	public boolean isBold() {
    		return bold;
    	}
    
    	public void setBold(boolean bold) {
    		this.bold = bold;
    	}
    
    	public int getFontSize() {
    		return fontSize;
    	}
    
    	public void setFontSize(int fontSize) {
    		this.fontSize = fontSize;
    	}
    
    	public int getFlow() {
    		return flow;
    	}
    
    	public void setFlow(int flow) {
    		this.flow = flow;
    	}
    
    	public Alignment convertFlow() {
    		return convertFlow(flow);
    	}
    	
    	public static Alignment convertFlow(int flow) {
    		Alignment al = null;
    		switch (flow) {
    		case 0:
    			al = Alignment.LEFT;
    			break;
    		case 1:
    			al = Alignment.CENTRE;
    			break;
    		case 2:
    			al = Alignment.RIGHT;
    			break;
    		default:
    			al = Alignment.LEFT;
    		}
    		return al;
    	}
    
    	public FontName convertFontName() {
    		return convertFontName(font);
    	}
    	
        public static FontName convertFontName(int font) {
    		FontName fn = null;
    		switch (font) {
    		case 0:
    			fn = WritableFont.createFont("SimSun");
    			break;
    		case 1:
    			fn = WritableFont.createFont("KaiTi");
    			break;
    		case 2:
    			fn = WritableFont.createFont("SimHei");
    			break;
    		case 3:
    			fn = WritableFont.createFont("FangSong");
    			break;
    		case 4:
    			fn = WritableFont.createFont("LiSu");
    			break;
    		default:
    			fn = WritableFont.createFont("STSong");
    		}
    		return fn;
    	}
    	
    	public int convertFontSize() {
    		return convertFontSize(fontSize);
    	}
    
    	public static int convertFontSize(int fontSize) {
    		return 12 + fontSize * 2;
    	}
    
    	@Override
    	public boolean equals(Object obj) {
    		boolean eq = false;
    		if (this == obj) {
    			eq = true;
    		} else if (obj != null && obj instanceof ExcelFontFormat) {
    			ExcelFontFormat e = (ExcelFontFormat) obj;
    			if (e.bold == this.bold && e.backgroundColor == this.backgroundColor && e.color == this.color
    					&& e.flow == this.flow && e.font == this.font && e.fontSize == this.fontSize
    					&& e.italic == this.italic) {
    				eq = true;
    			}
    		}
    		return eq;
    	}
    	
    	public boolean isItalic() {
    		return italic;
    	}
    
    	public void setItalic(boolean italic) {
    		this.italic = italic;
    	}
    
    	public int getVerticalAlign() {
    		return verticalAlign;
    	}
    
    	public void setVerticalAlign(int verticalAlign) {
    		this.verticalAlign = verticalAlign;
    	}
    
    }
    	
    

    4个mode类以及有了,我介绍的很简单,每个封装类其实还封装了一些其他的,但因为我的例子就只用到了这些就不多讲了。下面是Excel处理类ExcelHelper,代码比较多,其实大家不用管太多,粘贴过来用就行了,只要知道怎么用他(包括输入给些什么,输出的ByteArrayInputStream怎么用)就行。

    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.math.BigDecimal;
    import java.util.ArrayList;
    import java.util.Collections;
    import java.util.HashMap;
    import java.util.HashSet;
    import java.util.List;
    import java.util.Map;
    import java.util.Set;
    
    import jxl.Workbook;
    import jxl.format.Colour;
    import jxl.format.UnderlineStyle;
    import jxl.format.VerticalAlignment;
    import jxl.write.Label;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableFont;
    import jxl.write.WritableFont.FontName;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.WriteException;
    import jxl.write.biff.JxlWriteException;
    import jxl.write.biff.RowsExceededException;
    
    import org.apache.commons.logging.Log;
    import org.apache.commons.logging.LogFactory;
    
    import com.newsee.dto.common.ExcelColMode;
    import com.newsee.dto.common.ExcelExportRule;
    import com.newsee.dto.common.ExcelFontFormat;
    import com.newsee.dto.common.ExcelHeadCell;
    
    public class ExcelHelper {
        private static Log log = LogFactory.getLog(ExcelHelper.class);
    
    	/**
    	 * 实际需要展现的数据,支持DTO和Map
    	 */
    	private List<Object> rowDatas;
    
    	private Set<Object> writed;
    
    	/**
    	 * 取数据及数据展现相关
    	 */
    	private List<ExcelColMode> colModes;
    
    	/**
    	 * 行头(横向排列),如果有父行头则按父行头的顺序,没有父行头的按List顺序排列
    	 */
    	private List<List<ExcelHeadCell>> headCols;
    
    	/**
    	 * 数据背景颜色区分,0:不区分,1:按行奇偶区分,2:按列奇偶区分
    	 */
    	private int distinguishable;
    
    	/**
    	 * 缓存展现内容的sheet页
    	 */
    	private WritableSheet sheet;
    
    	/**
    	 * 缓存单元格格式
    	 */
    	private Map<ExcelFontFormat, WritableCellFormat> mappedFormat;
    
    	/**
    	 * id字段名称,用于树形结构
    	 */
    	private String idName;
    
    	/**
    	 * 父id字段名称,用于树形结构
    	 */
    	private String pidName;
    
    	private static String ONE_BLANK = " ";
    
    	private int curDataRowIndex;
    	private int curExcelRowIndex;
    	
    	public InputStream writeExcel(List<Object> rowDatas, ExcelExportRule rule) throws IOException, WriteException,
    			SecurityException, IllegalArgumentException, NoSuchMethodException, IllegalAccessException,
    			InvocationTargetException {
    		if (rule != null) {
    			this.rowDatas = rowDatas;
    			this.colModes = rule.getColModes();
    			this.headCols = rule.getHeadCols();
    			this.distinguishable = rule.getDistinguishable();
    			if (validate()) {
    				ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    				WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
    				String sheetName = rule.getSheetName();
    				if (StringUtil.isBlank(sheetName)) {
    					sheetName = "sheet0";
    				}
    				sheet = workbook.createSheet(sheetName, 0);
    				// 设置列宽
    				for (int i = 0; i < colModes.size(); i++) {
    					ExcelColMode colMode = colModes.get(i);
    					if (colMode.getWidth() != null)
    						sheet.setColumnView(i, colMode.getWidth());
    				}
    				writeHeads();
    				// 树形结构
    				if ("1".equals(rule.getHierarchical())) {
    					this.idName = rule.getIdName();
    					this.pidName = rule.getPidName();
    					writeTreeBody();
    				}
    				// 非树形结构
    				else {
    					writeBody();
    				}
    				workbook.write();
    				workbook.close();
    				return new ByteArrayInputStream(outputStream.toByteArray());
    			}
    		} else {
    			log.error("ExcelExportRule为空,无法导出excel");
    		}
    		return null;
    	}
    	
    	private boolean validate() {
    		if (colModes == null || colModes.size() == 0) {
    			log.error("读取数据的规则ExcelExportRule.colModes为空");
    			return false;
    		}
    		return true;
    	}
    	
    	private void writeHeads() throws JxlWriteException, WriteException {
    		curExcelRowIndex = 0;
    		if (headCols != null && !headCols.isEmpty()) {
    			int s = headCols.size();
    			if (s > 1) {
    				caculaterHeadColSpans();
    			}
    			for (int i = 0; i < s; i++) {
    				int tempColIndex = 0;
    				List<ExcelHeadCell> headRowCols = headCols.get(i);
    				for (int j = 0; j < headRowCols.size(); j++) {
    					ExcelHeadCell headCol = headRowCols.get(j);
    					writeHeadCell(headCol, tempColIndex);
    					tempColIndex += headCol.getColSpan();
    				}
    				curExcelRowIndex++;
    			}
    		}
    	}
    
    	// 计算标题需要列数
    	private void caculaterHeadColSpans() {
    	    int s = headCols.size();
    		for (int i = s - 1; i > 0; i--) {
    			List<ExcelHeadCell> subCols = headCols.get(i);
    			Collections.sort(subCols);
    			List<ExcelHeadCell> supCols = headCols.get(i - 1);
    			int[] fatherColSpans = new int[supCols.size()];
    			for (ExcelHeadCell subCol : subCols) {
    				int fi = subCol.getFatherIndex();
    				fatherColSpans[fi] += subCol.getColSpan();
    			}
    			for (int j = 0; j < supCols.size(); j++) {
    				ExcelHeadCell supCol = supCols.get(j);
    				if (fatherColSpans[j] > 0) {
    					supCol.setColSpan(fatherColSpans[j]);
    				}
    			}
    		}
    	}
    
    	private void writeHeadCell(ExcelHeadCell headCol, int colIndex) throws JxlWriteException, WriteException {
    		ExcelFontFormat eff = headCol.getFontFormat();
    		String content = headCol.getContent();
    		int colspan = headCol.getColSpan();
    		if (headCol.getHeight() != null)
    			sheet.setRowView(curExcelRowIndex, headCol.getHeight(), false);
    		writeCell(content, eff, colIndex, colspan);
    	}
    
    	private void writeCell(String content, ExcelFontFormat eff, int colIndex, int colspan) throws JxlWriteException,
    			WriteException {
    		if (eff != null) {
    		WritableCellFormat wcf = getCellFormat(eff);
    			sheet.addCell(new Label(colIndex, curExcelRowIndex, content, wcf));
    		} else {
    			sheet.addCell(new Label(colIndex, curExcelRowIndex, content));
    		}
    		if (colspan > 1) {
    			sheet.mergeCells(colIndex, curExcelRowIndex, colIndex + colspan - 1, curExcelRowIndex);
    		}
    	}
    
    	/**
    	 * 从缓存中取格式化的字体,没有则新建并缓存,生成EXCELL完成后需要清除缓存的字体
    	 * 
    	 * @param eff
    	 * @return
    	 * @throws WriteException
    	 */
    	private WritableCellFormat getCellFormat(ExcelFontFormat eff) throws WriteException {
    		WritableCellFormat wcf = null;
    		if (mappedFormat == null) {
    			mappedFormat = new HashMap<ExcelFontFormat, WritableCellFormat>();
    		} else {
    			wcf = mappedFormat.get(eff);
    		}
    		if (wcf == null) {
    			FontName fn = eff.convertFontName();
    			WritableFont wf = new WritableFont(fn, eff.convertFontSize(), eff.isBold() ? WritableFont.BOLD
    					: WritableFont.NO_BOLD, eff.isItalic(), UnderlineStyle.NO_UNDERLINE, eff.getColor());
    			wcf = new WritableCellFormat(wf);
    			wcf.setBackground(eff.getBackgroundColor());
    			wcf.setAlignment(eff.convertFlow());
    			wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.BLACK);
    			if (eff.getVerticalAlign() == 0)
    				wcf.setVerticalAlignment(VerticalAlignment.TOP);
    			else if (eff.getVerticalAlign() == 1)
    				wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
    			else if (eff.getVerticalAlign() == 2)
    				wcf.setVerticalAlignment(VerticalAlignment.BOTTOM);
    			mappedFormat.put(eff, wcf);
    		}
    		return wcf;
    	}
    
    	private void writeTreeBody() throws RowsExceededException, WriteException, SecurityException,
    			IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
    		if (rowDatas != null && rowDatas.size() > 0) {
    			curDataRowIndex = 1;
    			Object fo = rowDatas.get(0);
    			boolean isMap = fo instanceof Map;
    			if (isMap) {
    				writeTreeDatas4Map();
    			} else {
    				writeTreeDatas4Dto();
    			}
    		}
    	}
    
    	private void writeTreeDatas4Map() throws JxlWriteException, WriteException {
    		if (writed == null) {
    		writed = new HashSet<Object>();
    		}
    		for (Object data : rowDatas) {
    			if (!writed.contains(data)) {
    				Map m = (Map) data;
    				Object pid = m.get(pidName);
    				if (pid == null) {
    					writeRow4Map(data);
    					curDataRowIndex++;
    					curExcelRowIndex++;
    					writed.add(data);
    					writeSubDatas4Map(m, 1);
    				} else {
    					if (pid instanceof String) {
    						String ps = (String) pid;
    						if (StringUtil.isBlank(ps) || (Integer.valueOf(ps) <= 0)) {
    							writeRow4Map(data);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							writed.add(data);
    							writeSubDatas4Map(m, 1);
    						}
    					} else if (pid instanceof Integer) {
    						Integer pi = (Integer) pid;
    						if (pi <= 0) {
    							writeRow4Map(data);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							writed.add(data);
    							writeSubDatas4Map(m, 1);
    						}
    					} else if (pid instanceof Long) {
    						Long pl = (Long) pid;
    						if (pl.compareTo(0L) <= 0) {
    							writeRow4Map(data);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							writed.add(data);
    							writeSubDatas4Map(m, 1);
    						}
    					} else if (pid instanceof BigDecimal) {
    						if (((BigDecimal) pid).compareTo(BigDecimal.ZERO) <= 0) {
    							writeRow4Map(data);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							writed.add(data);
    							writeSubDatas4Map(m, 1);
    						}
    					}
    				}
    			}
    		}
    	}
    
    	private void writeSubDatas4Map(Map father, int deep) throws RowsExceededException, WriteException {
    		for (Object data : rowDatas) {
    			if (!writed.contains(data)) {
    		        Map m = (Map) data;
    				Object pid = m.get(pidName);
    				Object fid = father.get(idName);
    				if (pid != null) {
    					if (pid instanceof Long) {
    						Long pl = (Long) pid;
    						Long fl = null;
    						try {
    							fl = (Long) fid;
    						} catch (Exception e) {
    							if (fid instanceof BigDecimal) {
    								fl = ((BigDecimal) fid).longValue();
    							}
    						}
    						if (pl.equals(fl)) {
    							writeSubRow4Map(m, deep);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							int subDeep = deep + 1;
    							writed.add(data);
    							writeSubDatas4Map(m, subDeep);
    						}
    					} else if (pid instanceof String) {
    						String ps = (String) pid;
    						String fs = null;
    						try {
    							fs = (String) fid;
    						} catch (Exception e) {
    							if (fid instanceof BigDecimal) {
    								fs = ((BigDecimal) fid).toString();
    							}
    						}
    						if (ps.equals(fs)) {
    							writeSubRow4Map(m, deep);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							int subDeep = deep + 1;
    							writed.add(data);
    							writeSubDatas4Map(m, subDeep);
    						}
    					} else if (pid instanceof Integer) {
    						Integer pi = (Integer) pid;
    						Integer fi = null;
    						try {
    							fi = (Integer) fid;
    						} catch (Exception e) {
    							if (fid instanceof BigDecimal) {
    								fi = ((BigDecimal) fid).intValue();
    							}
    						}
    						if (pi.equals(fi)) {
    							writeSubRow4Map(m, deep);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							int subDeep = deep + 1;
    							writed.add(data);
    							writeSubDatas4Map(m, subDeep);
    						}
    					}
    				}
    			}
    		}
    	}
    
    	private void writeSubRow4Map(Map subMap, int deep) throws RowsExceededException, WriteException {
    	int tempColIndex = 0;
    		for (ExcelColMode mode : colModes) {
    			Object o = subMap.get(mode.getName());
    			String content = null;
    			if (o == null)
    				content = "";
    			else {
    				if (mode.getContentFormatter() != null)
    					content = mode.getContentFormatter().format(o);
    				else
    					content = o.toString();
    			}
    			if (tempColIndex == 0) {
    				int blankCount = 6 * deep;
    				for (int i = 0; i < blankCount; i++) {
    					content = ONE_BLANK + content;
    				}
    			}
    			writeContent(content, mode, tempColIndex);
    			tempColIndex++;
    		}
    	}
    
    	private void writeTreeDatas4Dto() throws SecurityException, IllegalArgumentException, NoSuchMethodException,
    			IllegalAccessException, InvocationTargetException, JxlWriteException, WriteException {
    		if (writed == null) {
    			writed = new HashSet<Object>();
    		}
    		for (Object data : rowDatas) {
    		if (!writed.contains(data)) {
    				Object pid = getValue(data, pidName);
    				if (pid == null) {
    					writeRow4Dto(data);
    					curDataRowIndex++;
    					curExcelRowIndex++;
    					writed.add(data);
    					writeSubDatas4Dto(data, 1);
    				} else {
    					if (pid instanceof String) {
    						String ps = (String) pid;
    						if (StringUtil.isBlank(ps) || (Integer.valueOf(ps)) <= 0) {
    							writeRow4Dto(data);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							writed.add(data);
    							writeSubDatas4Dto(data, 1);
    						}
    					} else if (pid instanceof Integer) {
    						Integer pi = (Integer) pid;
    						if (pi <= 0) {
    							writeRow4Dto(data);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							writed.add(data);
    							writeSubDatas4Dto(data, 1);
    						}
    					} else if (pid instanceof Long) {
    						Long pl = (Long) pid;
    						if (pl.compareTo(0L) <= 0) {
    							writeRow4Dto(data);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							writed.add(data);
    							writeSubDatas4Dto(data, 1);
    						}
    					}
    				}
    			}
    		}
    	}
    
    	private void writeSubDatas4Dto(Object father, int deep) throws SecurityException, IllegalArgumentException,
    			NoSuchMethodException, IllegalAccessException, InvocationTargetException, RowsExceededException,
    			WriteException {
    		for (Object data : rowDatas) {
    			if (!writed.contains(data)) {
    			Object pid = getValue(data, pidName);
    				Object fid = getValue(father, idName);
    				if (pid != null) {
    					if (pid instanceof Long) {
    						Long pl = (Long) pid;
    						Long fl = (Long) fid;
    						if (pl.equals(fl)) {
    							writeSubRow4Dto(data, deep);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							int subDeep = deep + 1;
    							writed.add(data);
    							writeSubDatas4Dto(data, subDeep);
    						}
    					} else if (pid instanceof String) {
    						String ps = (String) pid;
    						String fs = (String) fid;
    						if (ps.equals(fs)) {
    							writeSubRow4Dto(data, deep);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							int subDeep = deep + 1;
    							writed.add(data);
    							writeSubDatas4Dto(data, subDeep);
    						}
    					} else if (pid instanceof Integer) {
    						Integer pi = (Integer) pid;
    						Integer fi = (Integer) fid;
    						if (pi.equals(fi)) {
    							writeSubRow4Dto(data, deep);
    							curDataRowIndex++;
    							curExcelRowIndex++;
    							int subDeep = deep + 1;
    							writed.add(data);
    							writeSubDatas4Dto(data, subDeep);
    						}
    					}
    				}
    			}
    		}
    	}
    
    	private void writeSubRow4Dto(Object subData, int deep) throws SecurityException, IllegalArgumentException,
    			NoSuchMethodException, IllegalAccessException, InvocationTargetException, RowsExceededException,WriteException {
    		int tempColIndex = 0;
    		for (ExcelColMode mode : colModes) {
    			String field = mode.getName();
    			Object o = getValue(subData, field);
    			String content = null;
    			if (o == null)
    				content = "";
    			else {
    				if (mode.getContentFormatter() != null)
    					content = mode.getContentFormatter().format(o);
    				else
    					content = o.toString();
    			}
    			if (tempColIndex == 0) {
    				int blankCount = 6 * deep;
    				for (int i = 0; i < blankCount; i++) {
    					content = ONE_BLANK + content;
    				}
    			}
    			writeContent(content, mode, tempColIndex);
    		}
    	}
    
    	private void writeBody() throws RowsExceededException, WriteException, SecurityException, IllegalArgumentException,
    			NoSuchMethodException, IllegalAccessException, InvocationTargetException {
    		if (rowDatas != null && rowDatas.size() > 0) {
    			curDataRowIndex = 1;
    			Object fo = rowDatas.get(0);
    			if (fo instanceof Map) {
    				writeDatas4Map();
    			} else {
    				writeDatas4Dto();
    			}
    		}
    	}
    
    	private void writeDatas4Map() throws JxlWriteException, WriteException {
    		for (Object data : rowDatas) {
    			writeRow4Map(data);
    			curDataRowIndex++;
    			curExcelRowIndex++;
    			}
    	}
    
    	private void writeDatas4Dto() throws JxlWriteException, WriteException, SecurityException,
    			IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
    		for (Object data : rowDatas) {
    			writeRow4Dto(data);
    			curDataRowIndex++;
    			curExcelRowIndex++;
    		}
    	}
    
    	private void writeRow4Map(Object data) throws JxlWriteException, WriteException {
    		Map m = (Map) data;
    		int tempColIndex = 0;
    		for (ExcelColMode mode : colModes) {
    			Object o = m.get(mode.getName());
    			String content = null;
    			if (o == null)
    				content = "";
    			else {
    				if (mode.getContentFormatter() != null)
    					content = mode.getContentFormatter().format(o);
    				else
    					content = o.toString();
    			}
    			writeContent(content, mode, tempColIndex);
    			tempColIndex++;
    		}
    	}
    
    	private void writeContent(String content, ExcelColMode mode, int colIndex) throws RowsExceededException,
    			WriteException {
    		ExcelFontFormat eff = mode.getFontFormat();
    		if (distinguishable == 1) {
    			if (eff == null) {
    				eff = new ExcelFontFormat();
    			}
    			if (curDataRowIndex % 2 == 1) {
    				eff.setBackgroundColor(Colour.WHITE);
    			} else {
    				eff.setBackgroundColor(Colour.GRAY_25);
    			}
    		} else if (distinguishable == 2) {
    			if (eff == null) {
    				eff = new ExcelFontFormat();
    			}
    			if (colIndex % 2 == 1) {
    				eff.setBackgroundColor(Colour.WHITE);
    			} else {
    				eff.setBackgroundColor(Colour.GRAY_25);
    			}
    		}
    		writeCell(content, eff, colIndex, 1);
    	}
    
    	private void writeRow4Dto(Object data) throws JxlWriteException, WriteException, SecurityException,
    			IllegalArgumentException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
    		int tempColIndex = 0;
    		for (ExcelColMode mode : colModes) {
    			String field = mode.getName();
    			Object o = getValue(data, field);
    			String content = null;
    			if (o == null)
    				content = "";
    			else {
    				if (mode.getContentFormatter() != null)
    					content = mode.getContentFormatter().format(o);
    				else
    					content = o.toString();
    			}
    			writeContent(content, mode, tempColIndex);
    			tempColIndex++;
    		}
    	}
    
    	private Object getValue(Object data, String feild) throws SecurityException, NoSuchMethodException,
    			IllegalArgumentException, IllegalAccessException, InvocationTargetException {
    		if (feild.contains(".")) {
    			String fileds[] = feild.split("\\.");
    			Object value = null;
    			String methodName = "get" + fileds[0].substring(0, 1).toUpperCase() + fileds[0].substring(1);
    			try {
    				Method getMethod = data.getClass().getMethod(methodName);
    				value = getMethod.invoke(data);
    			} catch (NoSuchMethodException e) {
    				// e.printStackTrace();
    				try {
    					methodName = "get" + feild.substring(0, 1).toLowerCase() + feild.substring(1);
    					Method getMethod = data.getClass().getMethod(methodName);
    					value = getMethod.invoke(data);
    				} catch (Exception e2) {
    					e.printStackTrace();
    				}
    			}
    
    			Object value2 = null;
    			String methodName2 = "get" + fileds[1].substring(0, 1).toUpperCase() + fileds[1].substring(1);
    			try {
    				Method getMethod = value.getClass().getMethod(methodName2);
    				value2 = getMethod.invoke(value);
    			} catch (NoSuchMethodException e) {
    				// e.printStackTrace();
    				try {
    					methodName = "get" + fileds[1].substring(0, 1).toLowerCase() + fileds[1].substring(1);
    					Method getMethod = data.getClass().getMethod(methodName);
    					value = getMethod.invoke(value);
    				} catch (Exception e2) {
    					e.printStackTrace();
    				}
    			}
    			return value2;
    		} else {
    			Object value = null;
    			String methodName = "get" + feild.substring(0, 1).toUpperCase() + feild.substring(1);
    			try {
    				Method getMethod = data.getClass().getMethod(methodName);
    				value = getMethod.invoke(data);
    			} catch (NoSuchMethodException e) {
    				// e.printStackTrace();
    				try {
    					methodName = "get" + feild.substring(0, 1).toLowerCase() + feild.substring(1);
    					Method getMethod = data.getClass().getMethod(methodName);
    					value = getMethod.invoke(data);
    				} catch (Exception e2) {
    					e.printStackTrace();
    				}
    			}
    			return value;
    		}
    	}
    
    	/**
    	 * 释放资源
    	 */
    	public void clear() {
    		if (writed != null) {
    			writed = null;
    		}
    		if (rowDatas != null) {
    			rowDatas = null;
    		}
    		if (colModes != null) {
    			colModes = null;
    		}
    		if (headCols != null) {
    			headCols = null;
    		}
    		if (sheet != null) {
    			sheet = null;
    		}
    		if (mappedFormat != null) {
    			mappedFormat = null;
    		}
    		if (idName != null) {
    			idName = null;
    		}
    		if (pidName != null) {
    			pidName = null;
    		}
    	}
    
    	private static boolean isContainStyle(String style, String s1, String s2) {
    		String styleArr[] = style.split(";");
    		for (String s : styleArr) {
    			if (s.contains(s1) && s.contains(s2))
    				return true;
    		}
    		return false;
    	}
    
    	private static void writeSheetData(Integer startRow[], int level, Map<String, String> map, List<String> ridAry,
    			List<Map<String, String>> allData, WritableSheet sheet) throws RowsExceededException, WriteException {
    		String tab = "";
    		for (int i = 0; i < level - 1; i++) {
    			tab += "   ";
    		}
    		sheet.addCell(new Label(0, startRow[0], tab + map.get("index")));
    		for (int i = 0; i < ridAry.size(); i++) {
    			sheet.addCell(new Label(i + 1, startRow[0], map.get("R" + ridAry.get(i))));
    		}
    		startRow[0]++;
    		List<Map<String, String>> children = getChildren(allData, map.get("id"));
    		if (children != null && children.size() > 0) {
    			for (Map<String, String> child : children) {
    				writeSheetData(startRow, level + 1, child, ridAry, allData, sheet);
    			}
    		}
    	}
    
    	private static List<Map<String, String>> getFirstLeve(List<Map<String, String>> list) {
    		List<Map<String, String>> firstLevel = new ArrayList<Map<String, String>>();
    		if (list != null && list.size() > 0) {
    			for (Map<String, String> map : list) {
    				if (map.get("parentId") == null || map.get("parentId").length() == 0)
    					firstLevel.add(map);
    			}
    		}
    		return firstLevel;
    	}
    
    	private static List<Map<String, String>> getChildren(List<Map<String, String>> list, String id) {
    		List<Map<String, String>> children = new ArrayList<Map<String, String>>();
    		if (list != null && list.size() > 0) {
    			for (Map<String, String> map : list) {
    				if (map.get("parentId") != null && map.get("parentId").toString().equals(id))
    					children.add(map);
    			}
    		}
    		return children;
    	}
    
    	private static String getIndexData(String code, String rid, Map<String, List<HashMap<String, Object>>> tabDataMap) {
    		if (code == null || code.length() == 0)
    			return "";
    		if (tabDataMap == null || tabDataMap.size() == 0)
    			return "0";
    		String codeArr[] = code.split("\\.");
    		if (codeArr.length != 2)
    			return "";
    		List<HashMap<String, Object>> listMap = tabDataMap.get(codeArr[0]);
    		if (listMap == null || listMap.size() == 0)
    			return "0";
    		else {
    			for (int i = 0; i < listMap.size(); i++) {
    				HashMap<String, Object> map = listMap.get(i);
    				if (map.get("P_R_ID").toString().equals(rid)) {
    					Object obj = map.get(codeArr[1]);
    					if (obj != null)
    						return obj.toString();
    					else
    						return "0";
    					}
    			}
    			return "";
    		}
    	}
    
    	private static String getUnit(String code, Map<String, String> tabUnitMap) {
    		String result = "";
    		if (code != null) {
    			String codeArr[] = code.split("\\.");
    			if (codeArr.length == 2) {
    				if (tabUnitMap.get(codeArr[0]) != null)
    					result = tabUnitMap.get(codeArr[0]);
    			}
    		}
    		return result;
    	}
    }
    

    接下来是测试类。

    public class TestExcelUtil {
    	@Test
    	public void test() throws Exception {
    		ExcelHelper excelHelper = new ExcelHelper();
    		List<Object> rowDatas = new ArrayList<Object>();
    		Map<String, String> map = new HashMap<String, String>();
    		map.put("userName", "张三");
    		map.put("sex", "男");
    		rowDatas.add(map);
    
    		Map<String, String> map1 = new HashMap<String, String>();
    		map1.put("userName", "李四");
    		map1.put("sex", "男");
    		rowDatas.add(map1);
    
    		List<ExcelHeadCell> headCells = new ArrayList<ExcelHeadCell>();
    		headCells.add(new ExcelHeadCell("姓名"));
    		headCells.add(new ExcelHeadCell("性别"));
    		List<List<ExcelHeadCell>> headCellsList = new ArrayList<List<ExcelHeadCell>>();
    		headCellsList.add(headCells);
    		ExcelExportRule rule = new ExcelExportRule();
    		rule.setSheetName("测试");
    		rule.setHeadCols(headCellsList);
    
    		List<ExcelColMode> colModes = new ArrayList<ExcelColMode>();
    		colModes.add(new ExcelColMode("userName"));
    		colModes.add(new ExcelColMode("sex"));
    		rule.setColModes(colModes);
    
    		InputStream inputStream = excelHelper.writeExcel(rowDatas, rule);
    		File file = new File("d:/test.xls");
    		if (file.exists())
    			file.delete();
    		file.createNewFile();
    		FileOutputStream fileOutputStream = new FileOutputStream(file);
    		byte b[] = new byte[512];
    		int i = inputStream.read(b);
    		while (i != -1) {
    			fileOutputStream.write(b);
    			i = inputStream.read(b);
    		}
    		fileOutputStream.flush();
    		fileOutputStream.close();
    		inputStream.close();
    	}
    }
    

    通过看测试类应该就知道使用方法了吧,最后再给一个实际的从页面到后台的例子:从页面发来的一个请求,包含了表格的标题、内容、sheet名、Excel名,然后实现生成并下载Excel(在页面会弹出选择保存路径框)的过程。
    首先是页面:

    <!DOCTYPE html>
    <html>
    <head>
    	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    	<meta http-equiv="X-UA-Compatible" content="IE=EDGE" />
    	<title>导出Excel</title>
    	<script src="../../../ns-face-sys/common/lib/jquery-1.11.2.min.js"></script>
    	<script src="../../../ns-face-sys/common/js/newsee.js"></script>
    	<script src="../../../ns-face-sys/common/js/newsee.ui.js"></script>
    </head>
    <body>
    <form id="formid"  name= "myform" method = "post"  action = "/ns-face-sys/rest/system/export/excel">
    	<input id="headCells" name="headCells" value="" type="hidden"/>
    	<input id="rowDatas" name="rowDatas" value="" type="hidden"/>
    	<input id="sheetName" name="sheetName" value="人员信息" type="hidden"/>
    	<input id="excelName" name="excelName" value="人员信息表" type="hidden"/>
    	<table id="table">
    		<tr>
    			<td>序号</td>
    			<td>姓名</td>
    			<td>备注</td>
    		</tr>
    		<tr>
    			<td>1</td>
    			<td>yjc</td>
    	        <td>欣lp</td>
    		</tr>
    		<tr>
    			<td>2</td>
    			<td>lsx</td>
    			<td>诚lg</td>
    		</tr>
    		<tr>
    			<td>3</td>
    			<td>测试1</td>
    			<td>备注1</td>
    		</tr>
    		<tr>
    			<td>4</td>
    			<td>测试2</td>
    			<td>备注2</td>
    		</tr>
    	</table>
    	<input id="export" type="button" value="导出Excel" onclick="exportExcel()"/>
    </form>
    <script type="text/javascript" src="export_excel.js"></script>
    </body>
    </html>
    

    然后是js,里面做了把表格数据整合成后台需要的数据(每列数据用",“隔开,每行数据用”;"隔开,标题和内容分别放在隐藏的input里传给后台解析),放到隐藏域里,然后以提交表单的形式发送到后台。
    千万注意:不要使用ajax请求,虽然也能发到后台,但会导致浏览器收不到response,导致弹不出选择保存路径的弹出框了!!!

    function exportExcel(){
    	document.charset='utf-8';
    	var head = getHead();
    	var data = getData();
    //	var path = getPath();
    	$("#headCells").val(head);
    	$("#rowDatas").val(data);
    	$("#formid").submit();
    	
    //	var postData = [{
    //        Request: {
    //            Data: {
    //            	headCells : head,
    //    			rowDatas : data,
    //    			sheetName : "人员信息",
    //    			path : path,
    //    			excelName : "人员信息表"
    //            }
    //        }
    //    }]
    //	$.ajax({
    //            url: "/ns-face-sys/rest/system/export/excel",
    //            type: "post",
    //            dataType: 'json',
    //            async: true,
    //            data: { 
    //            	request: newsee.base.JsonArrayToStringCfz(postData) 
    //            }
    //	});
    function getHead(){
    	var head = "";
    	var uls = $("#table").find("tr");
    	var lis =  $(uls[0]).children("td");
    	for(var j=0;j<lis.length;j++){
    		if(j == lis.length-1){
    			head += $(lis[j]).text();
    		}else{
    			head += $(lis[j]).text() + ",";
    		}
    	}
    	return head;
    }
    
    function getData(){
    	var data = "";
    	var uls = $("#table").find("tr");
    	for(var i=1;i<uls.length;i++){
    		var lis =  $(uls[i]).children("td");
    		for(var j=0;j<lis.length;j++){
    			if(j == lis.length-1 && i == uls.length-1){
    				data += $(lis[j]).text();
    			}else if(j == lis.length-1 && i != uls.length-1){
    				data += $(lis[j]).text() + ";";
    			}else{
    				data += $(lis[j]).text() + ",";
    			}
    		}
    	}
    	return data;
    }
    
    function getPath(){
    	return "D:";
    }
    

    然后时控制器层代码,里面为了解决页面过来的数据有中文乱码,采用了笨方法,一个一个指定解码方式

    import javax.servlet.http.HttpServletResponse;
    import javax.ws.rs.FormParam;
    import javax.ws.rs.POST;
    import javax.ws.rs.Path;
    import javax.ws.rs.core.Context;
    
    import org.springframework.stereotype.Controller;
    
    import com.newsee.face.common.ExportExcel;
    
    @Path("system")
    @Controller
    public class SystemExcelFace {
    	
    	/**
    	 * 导出excel入口
    	 * @param request
    	 * @param response
    	 * @throws Exception
    	 */
    	@POST
    	@Path("/export/excel")
    	public void showImg(@FormParam("headCells") String headCells,
    			@FormParam("rowDatas") String rowDatas,
    			@FormParam("sheetName") String sheetName,
    			@FormParam("excelName") String excelName,
    			@Context HttpServletResponse response) throws Exception {
    		headCells=new String(headCells.getBytes("iso-8859-1"),"UTF-8");
    		rowDatas=new String(rowDatas.getBytes("iso-8859-1"),"UTF-8");
    		sheetName=new String(sheetName.getBytes("iso-8859-1"),"UTF-8");
    		excelName=new String(excelName.getBytes("iso-8859-1"),"UTF-8");
    		new ExportExcel().exportExcel(rowDatas, headCells, sheetName, excelName,response);
    	}
    
    }
    

    然后就是业务逻辑层了ExportExcel,这里就调用了之前介绍的导出Excel处理类ExcelHelper。

    import java.io.BufferedOutputStream;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.http.HttpServletResponse;
    
    import com.newsee.dto.common.ExcelColMode;
    import com.newsee.dto.common.ExcelExportRule;
    import com.newsee.dto.common.ExcelHeadCell;
    import com.newsee.util.ExcelHelper;
    import com.newsee.util.StringUtil;
    
    /**
     * 导出excel
     * 
     * @author yaojiacheng
     *
     */
    public class ExportExcel {
    
    	/**
    	 * 导出excel
    	 * 
    	 * @param requestContent
    	 * @return
    	 */
    	public void exportExcel(String rowData,String headCell,String sheetName,String excelName, HttpServletResponse response) throws Exception {
    	if (StringUtil.isAnyBlank(rowData, headCell)) {
    			return;
    		}
    		if(response == null){
    			return;
    		}
    
    		ExcelHelper excelHelper = new ExcelHelper();
    
    		List<ExcelHeadCell> headCells = new ArrayList<ExcelHeadCell>();
    		String[] headCellsArray = headCell.split(",");
    		for (String hc : headCellsArray) {
    			headCells.add(new ExcelHeadCell(hc));
    		}
    		List<List<ExcelHeadCell>> headCellsList = new ArrayList<List<ExcelHeadCell>>();
    		headCellsList.add(headCells);
    		
    		ExcelExportRule rule = new ExcelExportRule();
    		if (StringUtil.isBlank(sheetName)) {
    			rule.setSheetName("测试");
    		} else {
    			rule.setSheetName(sheetName);
    		}
    		rule.setHeadCols(headCellsList);
    
    		List<ExcelColMode> colModes = new ArrayList<ExcelColMode>();
    		for (int i = 0; i < headCellsArray.length; i++) {
    			colModes.add(new ExcelColMode(i + ""));
    		}
    		rule.setColModes(colModes);
    		
            List<Object> rowDatas = new ArrayList<Object>();
    		String[] rowDataArrays = rowData.split(";");
    		for (String rowDataArray : rowDataArrays) {
    			Map<String, String> map = new HashMap<String, String>();
    			String[] rowDataAs = rowDataArray.split(",");
    			for (int i = 0; i < rowDataAs.length; i++) {
    				map.put(i + "", rowDataAs[i]);
    			}
    			rowDatas.add(map);
    		}
    
    		InputStream fis = excelHelper.writeExcel(rowDatas, rule);
    		byte b[] = new byte[512];
    		// 清空response
    		response.reset();
    		// 设置response的Header
    		response.addHeader("Content-Disposition", "attachment;filename=" + 
    		URLEncoder.encode(excelName, "UTF-8")+ ".xls");
    		response.addHeader("Content-Length", "" + fis.available());
    		OutputStream toClient = new BufferedOutputStream(
    				response.getOutputStream());
    		response.setContentType("application/vnd.ms-excel;charset=gb2312");
    		int i = fis.read(b);
    		while (i != -1) {
    			toClient.write(b);
    			i = fis.read(b);
    		}
    		toClient.flush();
    		toClient.close();
    		fis.close();
    	}
    
    }
    

    如上,使用response.getOutputStream()得到的输出流来write数据后会先放在缓存中,到执行toClient.flush()就会在页面弹出选择保存路径的选择框,点击保存后就下载成功了。这里没有Excel导出的过程,直接将经过Excel处理类得到的InputStream拿过来读取,然后写进OutputStream下载,通常这是最优的方式。
    以上代码没有添加合并单元格的功能,需要合并单元格的同学们,可以自行修改代码,合并调用的方法就是:WritableSheet.mergeCells(int m,int n,int p,int q); 作用是从(m,n)到(p,q)的单元格全部合并,比如:
    //合并第一列第二行到第六列第二行的所有单元格
    sheet.mergeCells(0,1,5,1); //注意了m和p指的是列,n和q指的是行
    可以在workbook.write();workbook.close();之前调用即可!
    还需要注意的是,合并的单元格显示的内容是第一个单元格的内容,不是所有单元格的内容追加!

    展开全文
  • 可像EXCEL那样,导出选择。导出的内容是EXCE表格。
  • EasyPoi导出excel动态选择列

    千次阅读 2020-06-17 20:17:05
    主要使用easypoi中的ExcelExportEntity类对进行封装,可以设置的属性。 下面直接贴出自己的代码:(注意data数据类型List中的必须为map,不能为实体类,否则会报错) pom文件依赖: <dependency> <...

    主要使用easypoi中的ExcelExportEntity类对列进行封装,可以设置列的属性。

    下面直接贴出自己的代码:(注意data数据类型List中的必须为map,不能为实体类,否则会报错)

    pom文件依赖:

    <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-base</artifactId>
                <version>4.1.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-annotation</artifactId>
                <version>4.1.0</version>
            </dependency>
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-web</artifactId>
                <version>4.1.0</version>
            </dependency>

    自己写的工具类:

    /**
     * @author zhangz
     * @desc 简单excel动态选择列导出
     * @date 2020/6/16
     */
    public class ExcelExportUtils {
        private static Logger logger = LoggerFactory.getLogger(ConnUtil.class);
        /**
         * 选择列导出(单sheet)
         * @param fileNameUrl 文件导出服务器路径
         * @param sheetName sheet名称
         * @param dataList 数据list(map封装)
         * @param excelRows 导出的选择的列
         * @param title 标题(为空就传null)
         */
        public static void exportExcel(String fileNameUrl, String sheetName, List<Map<String,Object>> dataList, List<ExcelExportDTO> excelRows,String title) throws Exception{
            // 使用easypoi中的ExcelExportEntity对象存储要导出的列
            List<ExcelExportEntity> entityList = new ArrayList<>();
            excelRows.forEach(item->{
                ExcelExportEntity exportEntity = new ExcelExportEntity(item.getLineName(), item.getFieldName());
                exportEntity.setHeight(item.getHeight());
                exportEntity.setWidth(item.getWidth());
                entityList.add(exportEntity);
            });
            // 执行方法
            ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entityList ,dataList);
            String fileName = fileNameUrl;
            File file = new File(fileName);
            try{
                FileOutputStream fout = new FileOutputStream(file);
                workbook.write(fout);
                fout.close();
            }catch (Exception e){
                logger.error("导出失败-----------------------------------");
                throw new Exception("导出失败!");
            }
        }
    
        /**
         * 多sheet导出
         * @param fileNameUrl 文件导出服务器路径
         * @param multiSheetDTOList 多sheet中的属性
         */
        public static void exportMultiSheetExcel(String fileNameUrl, List<MultiSheetDTO> multiSheetDTOList) throws Exception{
            try{
                Workbook workbook = new HSSFWorkbook();
                // 遍历sheet
                for (MultiSheetDTO multiSheetDTO:multiSheetDTOList){
                    ExcelExportService server = new ExcelExportService();
                    ExportParams exportParams = new ExportParams(multiSheetDTO.getTitle(), multiSheetDTO.getSheetName(), ExcelType.XSSF);
                    List<ExcelExportEntity> entityList = new ArrayList<>();
                    multiSheetDTO.getExcelRows().forEach(item->{
                        ExcelExportEntity exportEntity = new ExcelExportEntity(item.getLineName(), item.getFieldName());
                        exportEntity.setHeight(item.getHeight());
                        exportEntity.setWidth(item.getWidth());
                        entityList.add(exportEntity);
                    });
                    server.createSheetForMap(workbook, exportParams, entityList, multiSheetDTO.getDataList());
                }
                FileOutputStream fos = new FileOutputStream(fileNameUrl);
                workbook.write(fos);
                fos.close();
            }catch (Exception e){
                logger.error("导出失败-----------------------------------");
                throw new Exception("导出失败!");
            }
        }
    }
    

    依靠的一些其他类

    ExcelExportDTO:
    package com.tdh.light.spxt.api.domain.dto.excel;
    
    /**
     * @author zhangz
     * @desc 动态选择列参数
     * @date 2020/6/16
     */
    public class ExcelExportDTO {
    
        private String lineName;
    
        private String fieldName;
    
        private Double width = 20D;
    
        private Double height = 10D;
    
        public ExcelExportDTO(){}
    
        public ExcelExportDTO(String lineName, String fieldName) {
            this.lineName = lineName;
            this.fieldName = fieldName;
        }
    
        public ExcelExportDTO(String lineName, String fieldName, Double width, Double height) {
            this.lineName = lineName;
            this.fieldName = fieldName;
            this.width = width;
            this.height = height;
        }
    
        public String getLineName() {
            return lineName;
        }
    
        public void setLineName(String lineName) {
            this.lineName = lineName;
        }
    
        public String getFieldName() {
            return fieldName;
        }
    
        public void setFieldName(String fieldName) {
            this.fieldName = fieldName;
        }
    
        public Double getWidth() {
            return width;
        }
    
        public void setWidth(Double width) {
            this.width = width;
        }
    
        public Double getHeight() {
            return height;
        }
    
        public void setHeight(Double height) {
            this.height = height;
        }
    }
    
    MultiSheetDTO:
    package com.tdh.light.spxt.api.domain.dto.excel;
    
    import java.util.List;
    import java.util.Map;
    
    /**
     * @author zhangz
     * @desc 多sheet传参
     * @date 2020/6/16
     */
    public class MultiSheetDTO {
    
        /**
         * sheet名称
         */
        private String sheetName;
    
        /**
         * 导出数据
         */
        private List<Map<String,Object>> dataList;
    
        /**
         * 导出列属性
         */
        private List<ExcelExportDTO> excelRows;
    
        /**
         * title
         */
        private String title;
    
        public String getSheetName() {
            return sheetName;
        }
    
        public void setSheetName(String sheetName) {
            this.sheetName = sheetName;
        }
    
        public List<Map<String, Object>> getDataList() {
            return dataList;
        }
    
        public void setDataList(List<Map<String, Object>> dataList) {
            this.dataList = dataList;
        }
    
        public List<ExcelExportDTO> getExcelRows() {
            return excelRows;
        }
    
        public void setExcelRows(List<ExcelExportDTO> excelRows) {
            this.excelRows = excelRows;
        }
    
        public String getTitle() {
            return title;
        }
    
        public void setTitle(String title) {
            this.title = title;
        }
    }
    

    这里面主要依靠了ExcelExportEntity 这个类来动态生成某个列

    展开全文
  • 而数据导出的格式一般是EXCEL或者PDF,我这里就用两篇文章分别给大家介绍下。(注意,我们这里说的数据导出可不是数据库中的数据导出!么误会啦^_^) 呵呵,首先我们来导出EXCEL格式的文件吧。现在主流的操作Ex...

            在web开发中,有一个经典的功能,就是数据的导入导出。特别是数据的导出,在生产管理或者财务系统中用的非常普遍,因为这些系统经常要做一些报表打印的工作。而数据导出的格式一般是EXCEL或者PDF,我这里就用两篇文章分别给大家介绍下。(注意,我们这里说的数据导出可不是数据库中的数据导出!么误会啦^_^)

             呵呵,首先我们来导出EXCEL格式的文件吧。现在主流的操作Excel文件的开源工具有很多,用得比较多的就是Apache的POI及JExcelAPI。这里我们用Apache POI!我们先去Apache的大本营下载POI的jar包:http://poi.apache.org/ ,我这里使用的是3.0.2版本。

            将3个jar包导入到classpath下,什么?忘了怎么导包?不会吧!好,我们来写一个导出Excel的实用类(所谓实用,是指基本不用怎么修改就可以在实际项目中直接使用的!)。我一直强调做类也好,做方法也好,一定要通用性和灵活性强。下面这个类就算基本贯彻了我的这种思想。那么,熟悉许老师风格的人应该知道,这时候该要甩出一长串代码了。没错,大伙请看:

    public class Student {
    	private long id;
    	private String name;
    	private int age;
    	private boolean sex;
    	private Date birthday;
    	// Constructor, Getter and Setter...
    }
    public class Book {
    	private int bookId;
    	private String name;
    	private String author;
    	private float price;
    	private String isbn;
    	private String pubName;
    	private byte[] preface;
    	// Constructor, Getter and Setter...
    }

    上面这两个类一目了然,就是两个简单的javabean风格的类。再看下面真正的重点类:

    public class ExportExcel<T> {
    	public void exportExcel(Collection<T> dataset, OutputStream out) {
    		exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd");
    	}
    
    	public void exportExcel(String[] headers, Collection<T> dataset,
    			OutputStream out) {
    		exportExcel("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
    	}
    
    	public void exportExcel(String[] headers, Collection<T> dataset,
    			OutputStream out, String pattern) {
    		exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern);
    	}
    
    	/**
    	 * 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
    	 *
    	 * @param title
    	 *            表格标题名
    	 * @param headers
    	 *            表格属性列名数组
    	 * @param dataset
    	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
    	 *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
    	 * @param out
    	 *            与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
    	 * @param pattern
    	 *            如果有时间数据,设定输出格式。默认为"yyy-MM-dd"
    	 */
    	@SuppressWarnings("unchecked")
    	public void exportExcel(String title, String[] headers,
    			Collection<T> dataset, OutputStream out, String pattern) {
    		// 声明一个工作薄
    		HSSFWorkbook workbook = new HSSFWorkbook();
    		// 生成一个表格
    		HSSFSheet sheet = workbook.createSheet(title);
    		// 设置表格默认列宽度为15个字节
    		sheet.setDefaultColumnWidth((short) 15);
    		// 生成一个样式
    		HSSFCellStyle style = workbook.createCellStyle();
    		// 设置这些样式
    		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    		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 = workbook.createFont();
    		font.setColor(HSSFColor.VIOLET.index);
    		font.setFontHeightInPoints((short) 12);
    		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    		// 把字体应用到当前的样式
    		style.setFont(font);
    		// 生成并设置另一个样式
    		HSSFCellStyle style2 = workbook.createCellStyle();
    		style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
    		style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
    		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    		// 生成另一个字体
    		HSSFFont font2 = workbook.createFont();
    		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    		// 把字体应用到当前的样式
    		style2.setFont(font2);
    		// 声明一个画图的顶级管理器
    		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    		// 定义注释的大小和位置,详见文档
    		HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(
    				0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
    		// 设置注释内容
    		comment.setString(new HSSFRichTextString("可以在POI中添加注释!"));
    		// 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.
    		comment.setAuthor("leno");
    		// 产生表格标题行
    		HSSFRow row = sheet.createRow(0);
    		for (short i = 0; i < headers.length; i++) {
    			HSSFCell cell = row.createCell(i);
    			cell.setCellStyle(style);
    			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
    			cell.setCellValue(text);
    		}
    		// 遍历集合数据,产生数据行
    		Iterator<T> it = dataset.iterator();
    		int index = 0;
    		while (it.hasNext()) {
    			index++;
    			row = sheet.createRow(index);
    			T t = (T) it.next();
    			// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
    			Field[] fields = t.getClass().getDeclaredFields();
    			for (short i = 0; i < fields.length; i++) {
    				HSSFCell cell = row.createCell(i);
    				cell.setCellStyle(style2);
    				Field field = fields[i];
    				String fieldName = field.getName();
    				String getMethodName = "get"
    						+ fieldName.substring(0, 1).toUpperCase()
    						+ fieldName.substring(1);
    				try {
    					Class tCls = t.getClass();
    					Method getMethod = tCls.getMethod(getMethodName,
    							new Class[] {});
    					Object value = getMethod.invoke(t, new Object[] {});
    					// 判断值的类型后进行强制类型转换
    					String textValue = null;
    					// if (value instanceof Integer) {
    					// int intValue = (Integer) value;
    					// cell.setCellValue(intValue);
    					// } else if (value instanceof Float) {
    					// float fValue = (Float) value;
    					// textValue = new HSSFRichTextString(
    					// String.valueOf(fValue));
    					// cell.setCellValue(textValue);
    					// } else if (value instanceof Double) {
    					// double dValue = (Double) value;
    					// textValue = new HSSFRichTextString(
    					// String.valueOf(dValue));
    					// cell.setCellValue(textValue);
    					// } else if (value instanceof Long) {
    					// long longValue = (Long) value;
    					// cell.setCellValue(longValue);
    					// }
    					if (value instanceof Boolean) {
    						boolean bValue = (Boolean) value;
    						textValue = "男";
    						if (!bValue) {
    							textValue = "女";
    						}
    					} else if (value instanceof Date) {
    						Date date = (Date) value;
    						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
    						textValue = sdf.format(date);
    					} else if (value instanceof byte[]) {
    						// 有图片时,设置行高为60px;
    						row.setHeightInPoints(60);
    						// 设置图片所在列宽度为80px,注意这里单位的一个换算
    						sheet.setColumnWidth(i, (short) (35.7 * 80));
    						// sheet.autoSizeColumn(i);
    						byte[] bsValue = (byte[]) value;
    						HSSFClientAnchor anchor = new HSSFClientAnchor(0,
    								0, 1023, 255, (short) 6, index, (short) 6,
    								index);
    						anchor.setAnchorType(2);
    						patriarch.createPicture(anchor, workbook
    								.addPicture(bsValue,
    										HSSFWorkbook.PICTURE_TYPE_JPEG));
    					} else {
    						// 其它数据类型都当作字符串简单处理
    						textValue = value.toString();
    					}
    					// 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
    					if (textValue != null) {
    						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
    						Matcher matcher = p.matcher(textValue);
    						if (matcher.matches()) {
    							// 是数字当作double处理
    							cell.setCellValue(Double.parseDouble(textValue));
    						} else {
    							HSSFRichTextString richString = new HSSFRichTextString(
    									textValue);
    							HSSFFont font3 = workbook.createFont();
    							font3.setColor(HSSFColor.BLUE.index);
    							richString.applyFont(font3);
    							cell.setCellValue(richString);
    						}
    					}
    				} catch (SecurityException e) {
    					e.printStackTrace();
    				} catch (NoSuchMethodException e) {
    					e.printStackTrace();
    				} catch (IllegalArgumentException e) {
    					e.printStackTrace();
    				} catch (IllegalAccessException e) {
    					e.printStackTrace();
    				} catch (InvocationTargetException e) {
    					e.printStackTrace();
    				} finally {
    					// 清理资源
    				}
    			}
    		}
    		try {
    			workbook.write(out);
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    
    	public static void main(String[] args) {
    		// 测试学生
    		ExportExcel<Student> ex = new ExportExcel<Student>();
    		String[] headers = { "学号", "姓名", "年龄", "性别", "出生日期" };
    		List<Student> dataset = new ArrayList<Student>();
    		dataset.add(new Student(10000001, "张三", 20, true, new Date()));
    		dataset.add(new Student(20000002, "李四", 24, false, new Date()));
    		dataset.add(new Student(30000003, "王五", 22, true, new Date()));
    		// 测试图书
    		ExportExcel<Book> ex2 = new ExportExcel<Book>();
    		String[] headers2 = { "图书编号", "图书名称", "图书作者", "图书价格", "图书ISBN",
    				"图书出版社", "封面图片" };
    		List<Book> dataset2 = new ArrayList<Book>();
    		try {
    			BufferedInputStream bis = new BufferedInputStream(
    					new FileInputStream("V://book.bmp"));
    			byte[] buf = new byte[bis.available()];
    			while ((bis.read(buf)) != -1) {
    				//
    			}
    			dataset2.add(new Book(1, "jsp", "leno", 300.33f, "1234567",
    					"清华出版社", buf));
    			dataset2.add(new Book(2, "java编程思想", "brucl", 300.33f,
    					"1234567", "阳光出版社", buf));
    			dataset2.add(new Book(3, "DOM艺术", "lenotang", 300.33f,
    					"1234567", "清华出版社", buf));
    			dataset2.add(new Book(4, "c++经典", "leno", 400.33f, "1234567",
    					"清华出版社", buf));
    			dataset2.add(new Book(5, "c#入门", "leno", 300.33f, "1234567",
    					"汤春秀出版社", buf));
    			OutputStream out = new FileOutputStream("E://a.xls");
    			OutputStream out2 = new FileOutputStream("E://b.xls");
    			ex.exportExcel(headers, dataset, out);
    			ex2.exportExcel(headers2, dataset2, out2);
    			out.close();
    			out2.close();
    			JOptionPane.showMessageDialog(null, "导出成功!");
    			System.out.println("excel导出成功!");
    		} catch (FileNotFoundException e) {
    			e.printStackTrace();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    }

            写完之后,如果您不是用eclipse工具生成的Servlet,千万别忘了在web.xml上注册这个Servelt。而且同样的,拷贝一张小巧的图书图片命名为book.jpg放置到当前WEB根目录的/WEB-INF/下。部署好web工程,用浏览器访问Servlet看下效果吧!是不是下载成功了。呵呵,您可以将下载到本地的excel报表用打印机打印出来,这样您就大功告成了。完事了我们就思考:我们发现,我们做的方法,不管是本地调用,还是在WEB服务器端用Servlet调用;不管是输出学生列表,还是图书列表信息,代码都几乎一样,而且这些数据我们很容器结合后台的DAO操作数据库动态获取。恩,类和方法的通用性和灵活性开始有点感觉了。好啦,祝您学习愉快!

    Java导出Excel弹出下载框

            将ExportExcel类的main方法改成public void test(),OutputStream out = new FileOutputStream("E://a.xls");这边可以对应Servlet适当改下路径,Servlet代码如下:

    public class ExcelServlet extends HttpServlet {
    	public void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		(new ExportExcel()).test();
    		String str = "a.xls";
    		//String path = request.getSession().getServletContext().getRealPath(str);
    		download("E://a.xls", response);
    	}
    	private void download(String path, HttpServletResponse response) {
    		try {
    			// path是指欲下载的文件的路径。
    			File file = new File(path);
    			// 取得文件名。
    			String filename = file.getName();
    			// 以流的形式下载文件。
    			InputStream fis = new BufferedInputStream(new FileInputStream(path));
    			byte[] buffer = new byte[fis.available()];
    			fis.read(buffer);
    			fis.close();
    			// 清空response
    			response.reset();
    			// 设置response的Header
    			response.addHeader("Content-Disposition", "attachment;filename="
    					+ new String(filename.getBytes()));
    			response.addHeader("Content-Length", "" + file.length());
    			OutputStream toClient = new BufferedOutputStream(
    					response.getOutputStream());
    			response.setContentType("application/vnd.ms-excel;charset=gb2312");
    			toClient.write(buffer);
    			toClient.flush();
    			toClient.close();
    		} catch (IOException ex) {
    			ex.printStackTrace();
    		}
    	}
    }

    补充:

    于2014-08-28补充

    今天(20140828)用博文中的代码重新调试了下,献上Java POI 导入导出Excel简单小例子一枚,方便你我。

    源代码下载地址:http://download.csdn.net/detail/evangel_z/7834173

    注:

    1)源代码是不含jar包的,jar包可以去本文补充部分下边的链接中下载,本地测试例子中所使用的所有jar包如下:

    2)直接使用该例子源代码的话,需要在E盘下放置一张名为book的png格式图片(book.png),用于导出含有图片的excel文件(b.xls)。当然,您可以根据实际需要更换代码中的图片路径;

    3)使用本文源代码正常启动服务器后,web页面导出Excel文档具体路径:http://localhost:8080/poi/export

    4)例子代码比较简单,仅供参考……

     

    于2014-12-02补充

    前段时间,在之前代码的基础上,抽空改了改代码,具体如下:

    1)去除图片和Excel文件未找到的bug;

    2)增加代码需要的jar包;

    3)完整代码已放在github上……

    最新代码下载地址:https://github.com/T5750/poi

     

    于2015-01-13补充

    由于不少热心网友问读取Excel模版导出相关问题,故今晚在之前代码的基础上,临时加了些代码,具体如下:

    1)新增使用POI读取Excel模版的例子,模版为poi/WebContent/docs/replaceTemplate.xls;

    2)在poi/src/replace/TestExcelReplace类中,请根据实际情况,调整读取和保存Excel的路径后,直接运行即可;

    最新代码下载地址不变,先到这里,抽空再优化……

     

    于2015-01-24补充

    前段时间,在之前代码的基础上,增加了种读取Excel模版导出的方式。今天,抽空改了改说明,具体如下:

    1)在poi/src/replace包中,新增上次补充里POI读取Excel模版的ReplaceExcelServlet.java,供web页面使用;

    2)在poi/src/template包中,增加了种读取Excel模版导出的方式,其对应的模版为poi/WebContent/docs/template.xls。请根据实际情况,调整读取和保存Excel的路径后,直接运行TestTemplate.java即可。TemplateServlet.java则对应web页面使用;

    最新代码下载地址不变……

     

    于2015-01-31补充

    昨晚,在之前代码的基础上,加上本文中可直接运行导出Excel的代码。具体如下:

    1)在poi/src/testExport包中,TestExportExcel.java,链接地址:https://github.com/T5750/poi/blob/master/src/testExport/TestExportExcel.java

    最新代码下载地址不变……

     

    于2015-02-10补充

    在之前代码的基础上,加上可以通过POI导出Excel2007的例子。具体如下:

    1)在poi/src/testExport包中,TestExportExcel2007.java,链接地址:https://github.com/T5750/poi/blob/master/src/testExport/TestExportExcel2007.java

     

    于2015-02-12补充

    今天,在之前代码的基础上,抽空改了改代码。具体如下:

    1)在poi/src/testExport包中,新增Excel2007Servlet.java。以及,修改相关配置;

    2)在poi/src/testExport包中,对导出Excel文件进行重命名,便于查看;

    3)更新该poi例子对应的帮助文档。

     

    于2019-07-23补充

    Servlet -> Spring Boot

    相关文章&官方文档&源代码下载地址:

    Java POI读取Office Excel (2003,2007)及相关jar包 Spring Boot

    最新官方文档:https://poi.apache.org/apidocs/index.html

    源代码下载地址:http://download.csdn.net/detail/evangel_z/7834173
    最新代码下载地址:https://github.com/T5750/poi

    展开全文
  • 导入功能:基于poi导入做了一层封装、支持注解方式标识属性对应Excel列、并支持简单规则校验、具体规则校验可以根据自己需求自定义 两种导出功能:一种基于poi的导出,一种基于jxls模板导出
  • java中Excel导出模板(跨行跨列导出) 笔者昨天有个需求,就是把下面的课时信息页签的内容原样导出: 这个地方看似不难,实际后台很复杂,数据的来源也复杂,并不好处理。但是这不是让我纠结的地方。 我纠结的...
  • easyui 导出excel导出隐藏

    千次阅读 2020-03-04 09:33:53
    easyui 的 datagrid-export.js 来导出excel,使用起来非常简单 官方文档 http://www.jeasyui.net/extension/204.html 但是导出的时候会把隐藏的也会导出来 解决方案:
  • 润乾报表页面超过255列导出excel

    千次阅读 2016-11-29 17:56:51
    直接在页面上点击导出excel的按钮,就会弹出对话框提示excel不支持超过255,也就无法正常导出,所以要想导出这样的报表就必须采取一些特殊的设置,下面就做一个简单的例子,实现超过255的不分页报表导出excel。...
  • 使用Easy POI的API实现POI对Excel的所有操作,并且它的封装类可以让你快速上手,无需编写大量的Excel格式设置等复杂代码,高效,完善,高并发
  • EasyPoi动态列导出Excel

    千次阅读 2020-01-18 15:10:07
    我心想很简单呀,不就是建个实体类,添加@Excel注解喽,然后直接导出不就行了。 **PS:**实体类的字段名就是上面的这个公司(有一名字叫公司,这个公司也是一家公司,不是用作区分的,你可以把这个公司理解为...
  • 前端的GridView不要用自动生成,否则取不到的资料,不知自动生成有...前端增加一个CheckBoxList用来显示GridView导出的时候选择: ; margin: 0 auto; border: 2px solid green; text-align: center;
  • 具体思路是:后端返回给我json数据,前端根据数据和具体的几项字段去导出excel表格,还有导出多个sheet,多页表格到一个excel表里面,具体思路 根据Export2Excel插件,并修改插件Export2Excel完成导出多页(多个sheet...
  • 提到Excel导出功能,可能很多人都使用springmvc框架做过,笔者今天要给大家分享的是基于springBoot开发Excel复杂模板导出功能(所谓复杂模板指在模板里的特定表头里有不同的单元格合并以及背景色,字体颜色的填充,...
  • Export2Excel.js导出excel

    2020-11-18 18:41:56
    包含Blob.js和Export2Excel.js文件,支持导出excel内容的宽度自适应,在vue项目中实现前端导出Excel文件
  • Kendo Grid 部分列导出excel

    千次阅读 2018-08-01 15:21:08
    导出excel时,不需要导出所有,部分导出excel bizNo不导出excel //点击 &amp;lt;button class=&quot;widthnotcertainbutton&quot; data-bind=&quot;events: {click:ExportVoucher}&...
  • table2excel指定不导出某些

    千次阅读 2018-02-27 23:07:23
    有时候用table2excel导出excel的时候,希望不导出某一些,除了可以用指定不导出类名之外,还可以这样 function ExportExcel(DivName, fileName, cols) { $("" + DivName).table2excel({ exclude: &...
  • C#-WinForm(2种dataGridView导出Excel)批量导出,高效率,36万行,15秒
  • 使用easypoi导出excel实现动态

    万次阅读 热门讨论 2019-03-01 10:41:20
    使用easypoi导出excel实现动态 说明: 使用的是easypoi进行导出 行头是动态生成 依据key进行匹配,进行数据填充 第一进行纵向动态合并 自己的一个使用,记录一下 实现效果 变更前样式 变更后样式 代码解析...
  • 1:引入相关js文件2:页面布局 带noExlclass的行不会被输出到excel中 带noExlclass的行不会被输出到excel中 这一行会被导出excel
  • 上图看效果 导出加上不同图片   1.导包 &lt;dependency&gt; &lt;groupId&gt;org.apache.poi&lt;/groupId&gt; &lt;artifactId&gt;poi&lt;/artifactId&gt;...
  • c#导出导入excel 自定义字段 自定义 OleDb方式的excel导入 可以实现自定义字段,不按照模版的方式导出
  • asp.net excel 选择文件夹 批量导出

    千次阅读 2013-05-14 17:30:46
    在客户端(非服务端) ,选择本地文件夹,一次导出多个excel。 前台:     导出excel function browseFolder(path) { try { var Message = "\u8bf7\u9009\u62e9\u6587\u4ef6\u5939"; //选择...
  • 进而找到了这个方法:表格数据导出Excel 思路很新奇:把选中的数据再用一个看不见的表格展示出来,最后导出! 如果你导出的地方较多,这样会产生较多的节点,用哪个方法你自己斟酌 正文 我用的是后面的方法 我有两...
  • Excel文件导入导出操作

    千次阅读 多人点赞 2020-10-11 21:44:14
    此篇内容主要分享一下工作中常用的Excel文件的解析和导出工作类实现。 实践 1.maven依赖 首先引入POI包依赖 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</...
  • NPOI 按模版导出Excel 自定义映射

    千次阅读 2016-12-09 16:22:56
    .net ExcelNPOIHelper.cs 类 按模版导出Excel 自定义映射使用的时候模版中要增加一行专门用来对应关系. 也可以定义这种特殊表头的Excel模版导出效果图 代码有需要的. 朋友请赞助1块钱吧…. 最近真的很缺钱...
  • 无限行导入导出EXCEL
  • 导入导出Excel,可读取任何版本Excel导出自定义列名、顺序、数据转换 自适应宽度
  • 导出的文件中包含一标识符(tag),长度一般都是十几位的字符和数字,例如:...在excel中那一按字符显示,就能完整显示出来了代码中添加空格,或者“ \t ”导出excel中显示转载地址:https://blog....

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 52,464
精华内容 20,985
关键字:

excel选择列导出