精华内容
下载资源
问答
  • 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();之前调用即可!
    还需要注意的是,合并的单元格显示的内容是第一个单元格的内容,不是所有单元格的内容追加!

    展开全文
  • 文章From : ...import java.io.File; import java.io.IOException; import java.util.Date; import jxl.Workbook; import jxl.write.DateFormat; import jxl.

    文章From : http://blog.sina.com.cn/s/blog_8d960c4c0101cd7n.html
    import java.io.File;
    import java.io.IOException;
    import java.util.Date;
    import jxl.Workbook;
    import jxl.write.DateFormat;
    import jxl.write.DateTime;
    import jxl.write.NumberFormat;
    import jxl.write.WritableSheet;
    import jxl.write.WritableWorkbook;
    import jxl.write.Label;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableFont;
    import jxl.write.WritableImage;
    import jxl.write.Number;
    import jxl.write.Boolean;
    public class Test {
    //生成excel文件
    public static void writeExcel() throws IOException{
    try{
    String Divpath = “d:\test”;//文件保存路径
    File dirFile = new File(Divpath);
    if(!dirFile.exists()){//文件路径不存在时,自动创建目录
    dirFile.mkdir();
    }
    String path = Divpath+”\test.xls”;//文件名字
    //创建一个可写入的excel文件对象
    WritableWorkbook workbook = Workbook.createWorkbook(new File(path));
    //使用第一张工作表,将其命名为“测试”
    WritableSheet sheet = workbook.createSheet(“测试”, 0);

            //设置字体种类和格式
             WritableFont bold = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
             WritableCellFormat wcfFormat = new WritableCellFormat(bold);
             wcfFormat.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
    
            //单元格是字符串格式!第一个是代表列数,第二是代表行数,第三个代表要写入的内容,第四个代表字体格式  (0代表excel的第一行或者第一列)   
             Label label01 = new Label(0, 0, "测试数据:",wcfFormat); //这里的(0,0)表示第一行第一列的表格       
             sheet.addCell(label01);
             Label label02 = new Label(1, 0, "测试的结果是成功的");            
             sheet.addCell(label02);
    
            //合并单元格,合并既可以是横向的,也可以是纵向的       
         //这里的第一个数据代表第二列,第二个数据代表第一行,第三个数据代表第四列,第四个数据代表第二行
          sheet.mergeCells(1, 0, 3, 1);
          //设置第2行的高度
             sheet.setRowView(1,400,false);       
             //设置列宽
             sheet.setColumnView(0, 15);
             sheet.setColumnView(1, 40);
    
            //插入图片
             File file=new File("d:\\test\\123.png");            
            //WritableImage前面四个参数的类型都是double,依次是 x, y, width, height,这里的宽和高可不是图片的宽和高,而是图片所要占的单位格的个数
             WritableImage image=new WritableImage(1, 3, 1, 3,file);
             sheet.addImage(image);
    
             //整型数据
             Number label2 = new Number(0, 1,31415926);
             sheet.addCell(label2);
    
            //添加带有formatting的Number对象
                NumberFormat nf = new NumberFormat("#.##");
                WritableCellFormat wcfN = new WritableCellFormat(nf);
                Number labelNF = new Number(0, 3, 3.1415926, wcfN);
                sheet.addCell(labelNF);
             //boolean型数据
             Boolean label3 = new Boolean(0,4,true);
             sheet.addCell(label3);
    
             //添加DateTime对象
                DateTime labelDT = new DateTime(0, 5, new Date());
             sheet.addCell(labelDT);
           //添加带有formatting的DateFormat对象
             DateFormat df = new DateFormat("yyyy-MM-dd HH:mm:ss"); //HH是24小时制,hh是12小时制
                WritableCellFormat wcfDF = new WritableCellFormat(df);
                DateTime labelDTF = new DateTime(4, 1, new Date(), wcfDF);
                sheet.addCell(labelDTF);
                //关闭对象,释放资源
                workbook.write();
                workbook.close();
    
    }catch(Exception e){
           e.printStackTrace();
         }
    

    }
    public static void main(String[] args) {
    try {
    writeExcel();
    } catch (IOException e) {
    e.printStackTrace();
    }
    }
    }

    展开全文
  • java导出excel模板,java设置excel模板只能输入时间,数字,excel输入限制 没废话,直接上代码 package com.artfess.rfdm.util; import java.io.BufferedInputStream; import java.io.BufferedOutputStream; import...

    java导出excel模板,java设置excel模板只能输入时间,数字,excel输入限制

    没废话,直接上代码

    package com.artfess.rfdm.util;
    
    import java.io.BufferedInputStream;
    import java.io.BufferedOutputStream;
    import java.io.ByteArrayInputStream;
    import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    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.CellRangeAddressList;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.CreationHelper;
    import org.apache.poi.ss.usermodel.DataValidation;
    import org.apache.poi.ss.usermodel.DataValidationConstraint;
    import org.apache.poi.ss.usermodel.DataValidationHelper;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Controller;
    
    import com.zx.system.annotation.ControllerMapping;
    import com.zx.system.bmp.client.CommonController;
    import com.zx.system.util.SDO;
    import com.zx.system.util.data.jdbc.SQLQuery;
    
    @Controller
    @ControllerMapping({ "excel" })
    public class ExcelController extends CommonController {
    	
    	
    	@Autowired
    	protected SQLQuery sqlQuery;
    	/**
    		 * 导出
    		 * @param blacklist
    		 * @param model
    		 * @return
    		 */
    		
    	 public String exportTable () throws IOException {
    		      
    		 HttpServletRequest request = getRequest();
    		 HttpServletResponse response = getResponse();
    		 
    			request.setCharacterEncoding("UTF-8");
    			SDO params = this.getSDO();
    			String sysModuleTableId = params.getString("sysModuleTableId");
    			List<Map<String, Object>> getById = selectByid(sysModuleTableId);
    			String resourceCatalogId = String.valueOf(getById.get(0).get("id"));
    			String tabname = params.getString("name");
    		    List<Map<String, Object>> data=queryCodeCount(resourceCatalogId);
    		     /**
    		      * 以下为生成Excel操作
    		      */
    		     // 1.创建一个workbook,对应一个Excel文件
    		     HSSFWorkbook wb = new HSSFWorkbook();
    		     // 2.在workbook中添加一个sheet,对应Excel中的一个sheet
    		     HSSFSheet sheet = wb.createSheet(tabname);
    		     sheet.setDefaultColumnWidth((short) 15);//设置表格的宽度
    		     // 3.在sheet中添加表头第0行,老版本poi对excel行数列数有限制short
    		     //第一行 
    		     HSSFRow row1 = sheet.createRow((int) 0);
    		     row1.setHeightInPoints(25);//设置表格一行的高度
    		     //第二行 
    		    /* HSSFRow row2 = sheet.createRow((int) 1);
    		     row2.setHeightInPoints(20);*/
    		     //正文 
    		     HSSFRow row = sheet.createRow((int) 1);   //第二行注释掉 so这行为第二行
    		     row.setHeightInPoints(20);//设置表格一行的高度
    		     // 4.创建单元格,设置值表头,设置表头居中
    		     HSSFCellStyle style1 = wb.createCellStyle();
    		     // 居中格式
    		     style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //		     style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
    //		     style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
    //		     style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
    		 style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
    		     // 生成一个字体
    		     HSSFFont font = wb.createFont();
    		     font.setFontHeightInPoints((short) 12);
    		     font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
    		     // 把字体应用到当前的样式
    		     style1.setFont(font);
    		     HSSFCellStyle style = wb.createCellStyle();
    		     // 居中格式
    		     style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		     style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    		     //solid 填充  foreground  前景色
    		     style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    			 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
    		     style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
    		     style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
    		     style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
    		    
    		     //合并单元格
    		     CellRangeAddress region = new CellRangeAddress(0, 0, 0, data.size()-1);
    		     sheet.addMergedRegion(region);
    		     HSSFCellStyle style2 = wb.createCellStyle();
    		     style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
    		     style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
    		     style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
    		     style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
    		     style2.setFont(font);
    		     HSSFCellStyle style5 = wb.createCellStyle();
    		     style5.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
    		     HSSFCell cell = row.createCell(0);
    		     HSSFCell cell1 = row1.createCell(0);
    		     cell1.setCellValue(tabname); //filetitle
    	            
    	            
    	            
    		     SimpleDateFormat dataFor = new SimpleDateFormat("yyy-MM-dd HH:mm:ss");
    		     //日期格式化  
    		     DecimalFormat doubleFor = new DecimalFormat("0.00");  //格式化数字 
    		     // 设置表头  单元格
    		     cell1.setCellStyle(style1);
    		     
    		     
    		     /**设置单元格格式为文本格式*/
    //	    	 HSSFCellStyle textStyle = wb.createCellStyle();
    //	    	 HSSFDataFormat format = wb.createDataFormat();
    //	    	 textStyle.setDataFormat(format.getFormat("@"));
    	    	 
    		     style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		     style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    		     //solid 填充  foreground  前景色
    		     style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    		     style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
    		     style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
    		     style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
    		     style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
    //           获取需要验证的列号List<Integer> list = 
    			 List<Integer> dateTypeStart = new ArrayList<Integer>();
    			 List<Integer> numTypeStart = new ArrayList<Integer>();
    			 
    			 
    		     for(int i=0;i<data.size();i++) {
    		    	 row1.createCell(i+1).setCellStyle(style1);
    		    	 cell.setCellValue(String.valueOf(data.get(i).get("fieldCn")));
    			     cell.setCellStyle(style);
    			     cell = row.createCell(i+1);
    //			     在这记录需要验证的开始行和结束行
    			     Map<String, Object> param =data.get(i);
    	        	 String uiComCode=param.get("uiComCode").toString();
    	        	 if(uiComCode.equals("date")||uiComCode.equals("time")||uiComCode.equals("datetime")||uiComCode.equals("float")||uiComCode.equals("double")) {
    	        		 dateTypeStart.add(i);
    	        	 }else if(uiComCode.equals("number")||uiComCode.equals("int")||uiComCode.equals("Long")) {
    	        		 numTypeStart.add(i);
    	        	 }
    	        	 
    		     }
    		     
    		     
    		     //创建数据验证类
                 DataValidationHelper helper = sheet.getDataValidationHelper();
    //	    	 这个是时间类型的    根据获取的行号和列号进行 验证
    	    	 for(int n = 0 ; n <dateTypeStart.size() ; n++) {
    	             //设置验证生效的范围
    	             CellRangeAddressList addressList = new CellRangeAddressList(1, 60000, dateTypeStart.get(n), dateTypeStart.get(n));
    	             //设置验证方式
    	             DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,"1900-01-01","2099-12-31","yyyy-MM-dd");
    	             //创建验证对象
    	             DataValidation dataValidation = helper.createValidation(constraint, addressList);
    	             //错误提示信息
    	             dataValidation.createErrorBox("提示","请输入[yyyy-MM-dd]格式日期");
    	             dataValidation.setShowErrorBox(true);
    	             //验证和工作簿绑定
    	             sheet.addValidationData(dataValidation);
    	    	 }
    
    	    	 
    	    	 for(int m = 0 ; m <numTypeStart.size() ; m++) {
    	    		 //设置验证生效的范围
    	             CellRangeAddressList addressList1 = new CellRangeAddressList(1, 60000, numTypeStart.get(m), numTypeStart.get(m));
    	             //设置验证方式
    	             DataValidationConstraint constraint1 =helper.createDecimalConstraint
    	            		 (DataValidationConstraint.OperatorType.BETWEEN, String.valueOf(0), String.valueOf(999999999));
    	             //创建验证对象
    	             DataValidation validation = helper.createValidation(constraint1, addressList1);
    	             //错误提示信息
    	             validation.createErrorBox("提示", "请输入【0-999999999】之间的两位小数");
    	             //设置是否显示错误窗口
    	             validation.setShowErrorBox(true);
    	             //验证和工作簿绑定
    	             sheet.addValidationData(validation);
    	    	 }
                
                 
    		     
    		     // 文本格式
    		     HSSFCellStyle textStyle = wb.createCellStyle();
    	    	 HSSFDataFormat format = wb.createDataFormat();
    	    	 textStyle.setDataFormat(format.getFormat("@"));
    		     // 居中格式
    	    	 textStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    
    		     // 日期格式
    	    	 HSSFCellStyle dateStyle = wb.createCellStyle();
                 HSSFDataFormat format1= wb.createDataFormat();
                 dateStyle.setDataFormat(format1.getFormat("yyyy年m月d日"));
                 dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                 
                 CellStyle cellStyle = wb.createCellStyle();
                 CreationHelper createHelper = wb.getCreationHelper();
                 cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy h.mm;@"));
                 
                
                 
                 
    		     // 数值格式  
                 HSSFCellStyle numStyle = wb.createCellStyle();
                 numStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
                 numStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                 
                 
                 HSSFCellStyle numStyle1 = wb.createCellStyle();  
                 numStyle1.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));  
    
    	    	//--------------
    	    	 for (int i = 0; i < 60000; i++) {
    //	    		 sheet.addValidationData(dataValidation);
    		         row = sheet.createRow((int) i + 2);  //循环遍历 第二行开始
    		         
    		         // 创建单元格,设置值
    		    	 for(int j=0;j<data.size();j++) {
    		          cell = row.createCell(j);//区
    		        	 Map<String, Object> param =data.get(j);
    		        	 String uiComCode=param.get("uiComCode").toString();
    		        	 if(uiComCode.equals("date")||uiComCode.equals("time")||uiComCode.equals("datetime")||uiComCode.equals("float")||uiComCode.equals("double")) {
    		        		 cell.setCellStyle(dateStyle);//设置单元格格式为"时间"
    		        		 dateTypeStart.add(j);
    		        	 }else if(uiComCode.equals("number")||uiComCode.equals("int")||uiComCode.equals("Long")) {
    		        		 cell.setCellStyle(numStyle);//设置单元格格式为"数值"
    		        		 numTypeStart.add(j);
    		        	 }else {
    		        		 cell.setCellStyle(textStyle);//设置单元格格式为"文本"
    		        	 }
    		         
    		    	 cell.setCellType(HSSFCell.CELL_TYPE_STRING); 
    		    	 }
    		     }
    		     
    	    	
                 
    
    		     String fileName = String.valueOf(tabname);
    		     ByteArrayOutputStream os = new ByteArrayOutputStream();
    		     wb.write(os);
    		     byte[] content = os.toByteArray();
    		     InputStream is = new ByteArrayInputStream(content);
    		     // 设置response参数,可以打开下载页面
    		     response.reset();
    		     response.setContentType("application/vnd.ms-excel;charset=utf-8");
    		     response.setHeader("Content-Disposition", "attachment;filename="
    		             + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
    		     ServletOutputStream out = response.getOutputStream();
    		     BufferedInputStream bis = null;
    		     BufferedOutputStream bos = null;
    		
    		     try {
    		         bis = new BufferedInputStream(is);
    		         bos = new BufferedOutputStream(out);
    		         byte[] buff = new byte[2048];
    		         int bytesRead;
    		         // Simple read/write loop.
    		         while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
    		             bos.write(buff, 0, bytesRead);
    		         }
    		     } catch (Exception e) {
    		         // TODO: handle exception
    		         e.printStackTrace();
    		     } finally {
    		         if (bis != null)
    		             bis.close();
    		         if (bos != null)
    		             bos.close();
    		     }
    			return null;
    			}	
    //	获取列的值
    		public List<Map<String, Object>> queryCodeCount(String id) {
    			String sql = null;
    			if(id!=null && !"".equals(id)){
    				sql = "select b.*  from SYS_MODULE_TABLE a left join SYS_MODULE_TABLE_FIELD b on a.id = b.TABLE_ID "
    						+ " where b.FIELD_TYPE = 1 and b.FLAG=1  AND a.id ='"+id+"' "
    						+ "  ORDER BY b.catalog_item_code asc "; 
    			}
    			return this.sqlQuery.getJDBCDao().queryToMapListByMapParam(sql, null);
    		}
    	
    //		根据目录id获取表id
    		public List<Map<String, Object>> selectByid(String id) {
    			String sql = null;
    			if(id!=null && !"".equals(id)){
    				sql = "select id from SYS_MODULE_TABLE where table_name=(select TABLE_NAME from SYS_MODULE_TABLE   WHERE RESOURCE_CATALOG_ID='"+id+"')";
    			}
    			return this.sqlQuery.getJDBCDao().queryToMapListByMapParam(sql, null);
    		}
    	
    }
    
    
    
    展开全文
  • java 数据导出Excel java POI 导出数据Excel 一、说明 1、使用Apache poi 实现java中导出excel格式数据。 2、依赖pom: <dependency> <groupId>org.apache.poi</groupId> <artifa....

                       java 数据导出Excel java POI 导出数据Excel

     

    一、说明

    1、使用Apache poi 实现java中导出excel格式数据。

    2、依赖pom:

         <dependency>
    	    <groupId>org.apache.poi</groupId>
    	    <artifactId>poi</artifactId>
    	    <version>3.15</version>
    	</dependency>
        
    	<dependency>
    	    <groupId>org.apache.poi</groupId>
    	    <artifactId>poi-ooxml</artifactId>
    	    <version>3.15</version>
    	</dependency>

    二、具体代码实现

    1、创建 ExcelExport 类,实现excel数据导出

    import java.io.File;
    import java.io.FileOutputStream;
    import java.net.URLEncoder;
    import java.util.List;
    import javax.servlet.http.HttpServletResponse;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Font;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.xssf.streaming.SXSSFCell;
    import org.apache.poi.xssf.streaming.SXSSFRow;
    import org.apache.poi.xssf.streaming.SXSSFSheet;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFRichTextString;
    
    /**
     * description: Excel 数据输出
     * @version v1.0
     * @author w
     * @date 2020年3月30日下午1:46:59
     **/
    public class ExcelExport {
    	
    	private ExcelExport INSTANSE = new ExcelExport ();
    	
    	/**
    	 * description: 导出数据excel
    	 * @param sheetName
    	 * @param headers
    	 * @param dataList
    	 * @param destFile
    	 * @return void
    	 * @version v1.0
    	 * @author w
    	 * @date 2020年3月30日 下午2:23:39
    	 */
    	public static void export(String sheetName, String[] headers, List<List<Object>> dataList, File destFile) throws Exception {
    		SXSSFWorkbook workbook = new SXSSFWorkbook();
    		createSheet(sheetName, headers, dataList, workbook);
    		workbook.write(new FileOutputStream(destFile));
    	}
    	
    	
    	/**
    	 * description: 导出excel --- 支持web
    	 * @param sheetName  sheet表名字
    	 * @param headers 表头
    	 * @param dataList 表数据
    	 * @param fileName  导出文件名
    	 * @param response 
    	 * @return void
    	 * @version v1.0
    	 * @author w
    	 * @date 2020年3月31日 下午2:48:46
    	 */
    	public static void export(String sheetName , String[] headers , List<List<Object>> dataList ,String fileName 
    			 								   , HttpServletResponse response) throws Exception {
    		SXSSFWorkbook workbook = new SXSSFWorkbook();
    		createSheet(sheetName, headers, dataList, workbook);
    		response.reset();
            response.setContentType("application/octet-stream; charset=utf-8");
            response.setHeader("Content-Disposition", "attachment; filename="+URLEncoder.encode(fileName ,"UTF-8"));
            workbook.write(response.getOutputStream());
            // 删除临时文件
            workbook.dispose();
    	}
    	
    	/**
    	 * description: 创建sheet表格
    	 * @param sheetName  表sheet 名字
    	 * @param headers  表头
    	 * @param dataList 表数据
    	 * @param wb
    	 * @return void
    	 * @version v1.0
    	 * @author w
    	 * @date 2020年3月30日 下午2:33:39
    	 */
    	public static void createSheet(String sheetName , String[] headers , List<List<Object>> dataList , SXSSFWorkbook wb) {
    		SXSSFSheet sheet = wb.createSheet(sheetName);
    		// 设置表头和单元格格式
    		CellStyle headStyle = setHeaderStyle(wb);
    		CellStyle bodyStyle = setBodyStyle(wb);
    		// 创建表头和单元格数据
            createHeader(headers, sheet, headStyle);
            createBody(dataList, sheet, bodyStyle);
    	}
    	
    	/**
    	 * description: 创建表头
    	 * @param headers
    	 * @param sheet
    	 * @param headStyle
    	 * @return void
    	 * @version v1.0
    	 * @author w
    	 * @date 2020年3月30日 下午3:03
    	 */
    	private static void createHeader(String[] headers, SXSSFSheet sheet, CellStyle headStyle) {
    		SXSSFRow row = sheet.createRow(0);
    		row.setHeightInPoints(16F);
    		for (int i = 0; i < headers.length; i++) {
    			// 创建单元格
    			SXSSFCell cell = row.createCell(i);
    			cell.setCellStyle(headStyle);
    			XSSFRichTextString text = new XSSFRichTextString(headers[i]);
                cell.setCellValue(text);
                sheet.trackAllColumnsForAutoSizing();
                sheet.autoSizeColumn(i);
    		}
    	}
    	
    	/**
    	 * description: 表格中填充数据
    	 * @param dataList
    	 * @param sheet
    	 * @param bodyStyle
    	 * @return void
    	 * @version v1.0
    	 * @author w
    	 * @date  2020年3月30日 下午3:13
    	 */
    	private static void createBody(List<List<Object>> dataList, SXSSFSheet sheet, CellStyle bodyStyle) {
    		for (int i = 0; i < dataList.size(); i++) {
    			// 从第二行开始,第一行做表头
    			SXSSFRow row = sheet.createRow(i+1);
    			List<Object> rowList = dataList.get(i);
    			for (int j = 0; j < rowList.size(); j++) {
    				SXSSFCell cell = row.createCell(j);
    				cell.setCellStyle(bodyStyle);
    				XSSFRichTextString text = new XSSFRichTextString(rowList.get(j).toString());
    				cell.setCellValue(text);
    				sheet.trackAllColumnsForAutoSizing();
    				sheet.autoSizeColumn(i);
    			}
    		}
    	}
    	
    	/**
    	 * description: 设置单元格内容样式
    	 * @param wb
    	 * @return HSSFCellStyle
    	 * @version v1.0
    	 * @author w
    	 * @date 2020年3月30日 下午2:42:39
    	 */
    	private static CellStyle setBodyStyle(SXSSFWorkbook wb) {
    		// 设置表格单元格格式
    		CellStyle style = wb.createCellStyle();
            style.setFillForegroundColor(HSSFColor.WHITE.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_LEFT);
    
            // 设置字体格式
            Font font = wb.createFont();
            font.setFontName("微软雅黑");
            font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            style.setFont(font);
            return style;
    	}
    
    	/**
    	 * description: 设置表头样式 
    	 * @param wb
    	 * @return
    	 * @return HSSFCellStyle
    	 * @version v1.0
    	 * @author w
    	 * @date 2020年3月30日 下午2:38:39
    	 */
    	private static CellStyle setHeaderStyle(SXSSFWorkbook wb) {
    		// 设置表格单元格格式
    		CellStyle style = wb.createCellStyle();
    		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
    		style.setBorderRight(CellStyle.BORDER_THIN);
    		style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    		style.setBorderLeft(CellStyle.BORDER_THIN);
    		style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    		style.setBorderTop(CellStyle.BORDER_THIN);
    		style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    		style.setBorderBottom(CellStyle.BORDER_THIN);
    		style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
    	    
    	    // 设置字体格式
    	    Font font = wb.createFont();
    	    font.setFontName("微软雅黑");
    	    font.setFontHeightInPoints((short) 10);
    	    style.setFont(font);
    	    return style;
    	}
    
    }
    

     

    2、创建ExcelController,测试web情况下,导出数据

        /**
    	 * description: excel 数据导出 
    	 * @return String
    	 * @version v1.0
    	 * @author w
    	 * @throws IOException 
    	 * @date 2020年3月31日 下午2:41:03
    	 */
    	@RequestMapping(value= "/export")
    	public String export(HttpServletResponse response) throws IOException {
    		String[] headers = new String[] {"姓名","年龄","级别"};
    		List<List<Object>> dataList = new ArrayList<>();
    		for(int x = 0 ; x < 3 ; x++) {
    			List<Object> data = new ArrayList<Object>();
    			data.add("姓名ssssss"+x);
    			data.add(18+x);
    			data.add("级别"+x);
    			dataList.add(data);
    		}
    		try {
    			ExcelExport.export("用户数据", headers, dataList,"poi导出模板.xlsx" ,response);
    			return null ;
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return "导出失败 --";
    	}

    3、测试,访问: http://localhost:999/spring-mvc-tourist/excelController/export

    4、结果:浏览器返回excel,数据都填充进去

    三、总结

     

    1、本示例使用的是 poi 3.15版的,其他版本之间,可能出现无法使用的情况,需要做部分调整。

    2、具体使用,请查看 ExcelExport 类中方法注释情况。

    3、不懂的,可以私信提供源码的噢。

     

     

     

    展开全文
  • Java 设置Excel自适应行高、列宽

    千次阅读 2020-02-11 11:17:02
    excel中,可通过设置自适应行高或列宽...2.固定列宽,缩小数据字体大小适应列宽 本文将通过java程序来介绍具体的自适应设置方法。 工具:Free Spire.XLS for Java (免费版) 注:可通过官网下载包,并...
  • Java实现excel数据量导出

    万次阅读 热门讨论 2019-04-25 10:25:57
    //设置数据行单元格样式,根据需要设置 cell.setCellValue(map.get(fields[cellnum]) == null ? "" : map.get(fields[cellnum]).toString()); } } //自定义各列宽度 //setSheet(sheet); os = response....
  • JavaExcel文件写入数据

    千次阅读 2017-03-28 14:42:15
    JavaExcel文件写入数据
  • Java操作Excel之POI:java读写excel文件以及打印设置 POI的jar包下载地址:http://poi.apache.org/download.html 注意:项目中导入poi 4.0.1的jar包,其他版本不确保没有错误。 HSSFCellStyle.VERTICAL_CENTER...
  • Java Poi操作Excel写入数据设置style

    千次阅读 2018-01-21 14:15:43
    Excel中写入数据,并再追加一遍相同的数据ExcelWriter.writeToExcel方法可以向指定excel表中写入mapList中的数据;ExcelWriter.addToExcel方法可以向指定excel表中追加mapLit中的数据
  • 本文使用jxl.jar工具类库将数据按列写入Excel设置格式(字体、背景色、自动列宽、对齐方式等)。
  • 最近的项目需要使用大量的Excel表,策划把数据都做成Excel表,通过编辑器把Excel表的数据导入到数据库,编辑器提供各种编辑操作,还要提供将数据库导出成Excel表的功能。借助于JAVA EXCEL API,这个问题就很简单了。...
  • JAVAexcel数据的批量导入

    千次阅读 2018-03-30 13:47:01
    首先看下工具类:package ... import java.awt.Color; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import j...
  • java设置Excel单元格格式 POI

    万次阅读 2012-04-23 17:58:54
    POI中可能会用到一些需要设置EXCEL单元格格式的操作小结: 先获取工作薄对象: HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFCellStyle setBorder = wb.createCellStyle(); ...
  • 接口导出Excel的时候,Excel的格式有时候很复杂,包含图片,字体居中等等格式,当然通过poi的合并单元格那些操作也能实现,但比较复杂。比如这种Excel(仅仅是测试数据): ![在这里插入图片描述]...
  • Java Excel API既可以从本地文件系统的一个文件(.xls),也可以从输入流中读取Excel数据表。读取Excel数据表的第一步是创建Workbook(术 语:工作薄),下面的代码片段举例说明了应该如何操作:(完整代码见ExcelReading...
  • Java Excel SXSSFWorkbook大量数据导出

    千次阅读 2017-04-13 16:41:14
    本文将介绍Java Excel SXSSFWorkbook大量数据导出的方法
  • java解析excel数据

    万次阅读 2009-10-18 18:18:00
    网站上对它的特征有如下描述: ● 支持Excel 95-2000的所有版本 ● 生成Excel 2000标准格式 ● 支持字体、数字、日期操作 ● 能够修饰单元格属性 ● 支持图像和图表 应该说以上功能已经能够大致满足我们的需要。...
  • 效果如下: 1:开始页面 2:点击下载Excel模板按钮实现的效果: ...4:点击预览,弹出本地文件,选择...6:注意事项以及代码如下:注:1:后台代码72,文件的存储位置:必须在webapp下建立upload/excel
  • Java导出数据Excel

    万次阅读 多人点赞 2017-07-18 11:56:05
    Java操作Excel数据表,导出工具类,让导出更便捷!
  • javaexcel创建,数据录入与读取(代码加完美注释!),值得收藏!
  • Java修改Excel单元格的数据及格式

    千次阅读 2012-06-15 17:57:11
    转自:aking21alinjuju:《Java开发有一说一》——Java修改Excel单元格的数据及格式 继前两节的Java读取、写入Excel后,本期将推出Java修改Excel数据以及格式的方法和技巧,如果大家学习了前面的读、写Excel,...
  • sheet wb创建等不再赘述算了,复制粘贴下:Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet();...改变部分字体颜色: Font font = wb.createFont(); font.setFontHeightInPoints((sho
  • Java实现Excel多表头动态数据导出

    千次阅读 2020-07-23 13:05:12
    好久没时间写帖子了,由于工作需要,写一个基于JAVA实现的Excel多表头动态导出功能,首先可能这个叫法比较啰嗦,下面我们先看看什么是Excel多表头动态导出(效果图): 它包含两部分:1、是表头,就像大家看到的...
  • java POI Excel数据导入数据库

    千次阅读 2018-11-22 22:27:45
    Apache POI 是用 Java 编写的免费开源的跨平台的 Java API,Apache POI 提供 API 给 Java 程式对 Microsoft Office(Excel、WORD、PowerPoint、Visio 等,主要实现用于 Excel)格式档案读和写的功能,POI 为 “ Poor...
  • 转自:aking21alinjuju:《Java开发有一说一》——向Excel文件中写数据  接着上一节的内容,本节主要讲述如何通过Java程序向Excel文件中写数据,包括:1、数据类型的控制;2、单元格及数据的格式化。 要快速...
  • java解析excel数据(转)

    千次阅读 2011-05-20 09:57:00
    网站上对它的特征有如下描述: ...最关键的是这套API是纯Java的,并不依赖Windows系统,即使运行在Linux下,它同样能够正确的处理Excel 文件。另外需要说明的是,这套API对图形和图表
  • Java操作poi导出Excel自定义字体颜色

    千次阅读 2019-06-18 14:26:25
    Java操作poi导出Excel自定义字体颜色功能介绍POI操作Excel第一步创建一个导出的工具类整体定义表格字体样式自定义表格字体样式总结 功能介绍 Apache POI 是用Java编写的免费开源的跨平台的 Java API,Apache POI提供...
  • 源码案例: 1.需要实体类型加上列名注解 ...import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Getter; import lombok.Setter; import lombok.ToString; import org.hibernate.valid...
  • 本文使用jxl.jar工具类库将数据按列写入Excel设置格式(字体、背景色、自动列宽、对齐方式等)。 /** * 按列写入Excel设置格式 * * @param outputUrl * 输出路径 * @param sheetName * 工作薄...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 13,785
精华内容 5,514
关键字:

java设置excel行数据字体

java 订阅