poi工具类的概述：
Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
结构：
HSSF － 提供读写Microsoft Excel格式档案的功能。XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。HWPF － 提供读写Microsoft Word格式档案的功能。HSLF － 提供读写Microsoft PowerPoint格式档案的功能。HDGF － 提供读写Microsoft Visio格式档案的功能。
这里提供一个工具类用于生成Excel
ViewExcel.java –用于创建生成Excel
package com.xpseed.util;import java.io.OutputStream;import .URLEncoder;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import java.util.Map;import javax.mail.internet.MimeUtility;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import mons.lang.StringUtils;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.springframework.web.servlet.view.document.AbstractExcelView;import com.xpseed.constant.Constants;/*** poi excel(SpringMvc下载工具类)* @author LJH**/public class ViewExcel extends AbstractExcelView {public static class CellInfo {public int row;//行public int col;//列public int rowSpan;//行距public int colSpan;//列距public Object value;public String toCellString() {if (value == null)return "";return value.toString();}}public static class SheetInfo {//工作标签--包含多个cell（单元格）public String title;public int maxRow = 0;public int maxCol = 0;public List<CellInfo> cells = new ArrayList<ViewExcel.CellInfo>();/***用于创建一个Cell**/public CellInfo createCell(int row, int col, Object value, int rowSpan,int colSpan) {return init(row, col, value, rowSpan, colSpan);}private CellInfo init(int row, int col, Object value, int rowSpan,int colSpan) {CellInfo ret = new CellInfo();cells.add(ret);ret.row = row;ret.col = col;ret.value = value;ret.rowSpan = rowSpan;ret.colSpan = colSpan;if (ret.row > maxRow)maxRow = ret.row;if (ret.col > maxCol)maxCol = ret.col;return ret;}public CellInfo createCell(int row, int col, Object value) {return init(row, col, value, 1, 1);}}public static class ExcelInfo {//ExcelInfo() {}public String title;public List<SheetInfo> sheets = new ArrayList<ViewExcel.SheetInfo>();public SheetInfo createSheetInfo(String title) {SheetInfo ret = new SheetInfo();sheets.add(ret);ret.title = title;return ret;}}public static ExcelInfo createExcel(String title) {Excel文档包含多个标签ExcelInfo ret = new ExcelInfo();ret.title = title;return ret;}@SuppressWarnings("deprecation")@Overrideprotected void buildExcelDocument(Map<String, Object> modelMap,HSSFWorkbook workbook, HttpServletRequest request,HttpServletResponse response) throws Exception {SimpleDateFormat format = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");String filename = format.format(new Date()) + ".xls";ExcelInfo excelInfo = (ExcelInfo) modelMap.get(Constants.ExcelConstant.EXCEL_INFO);String excelName = (String) modelMap.get(Constants.ExcelConstant.EXCEL_NAME);if (excelName != null && !excelName.equals("")) {filename = excelName + ".xls";}for (SheetInfo sheetInfo : excelInfo.sheets) {HSSFSheet sheet = workbook.createSheet(sheetInfo.title);sheet.setDefaultColumnWidth((short) 12);for (CellInfo cell : sheetInfo.cells) {getCell(sheet, cell.row, cell.col).setCellValue(cell.toCellString());}}filename = encodeFilename(filename, request);// 处理中文文件名response.setContentType("application/vnd.ms-excel");response.setHeader("Content-disposition", "attachment;filename="+ filename);OutputStream ouputStream = response.getOutputStream();workbook.write(ouputStream);ouputStream.flush();ouputStream.close();}/*** 处理中文文件名* * @param filename = "测试.xls";// 设置下载时客户端Excel的名称* @param request* @return*/private String encodeFilename(String filename, HttpServletRequest request) {/*** 获取客户端浏览器和操作系统信息 在IE浏览器中得到的是：User-Agent=Mozilla/4.0 (compatible; MSIE* 6.0; Windows NT 5.1; SV1; Maxthon; Alexa Toolbar)* 在Firefox中得到的是：User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1;* zh-CN; rv:1.7.10) Gecko/0717 Firefox/1.0.6*/String agent = request.getHeader("USER-AGENT");try {if ((agent != null) && (-1 != agent.indexOf("MSIE"))) {String newFileName = URLEncoder.encode(filename, "UTF-8");newFileName = StringUtils.replace(newFileName, "+", "%20");if (newFileName.length() > 150) {newFileName = new String(filename.getBytes("GB2312"),"ISO8859-1");newFileName = StringUtils.replace(newFileName, " ", "%20");}return newFileName;}if ((agent != null) && (-1 != agent.indexOf("Mozilla")))return MimeUtility.encodeText(filename, "UTF-8", "B");return filename;} catch (Exception ex) {return filename;}}}
工具类的使用
这里的包含关系
Excel中包含一个或多个Sheet（工作标签），Sheet包含一个或多个cell（单元格）第一步：新建 一个Excel
ViewExcel viewExcel = null;String excelName = "Excel的名字"viewExcel = new ViewExcel();ViewExcel.ExcelInfo excelInfo = ViewExcel.createExcel(excelName);//创建excel文件
第二步建立一个或多个工作标签模板
String niceName ="Sheet名字"//这里注意使用上面的工具类这里如果用StringBuffer来拼接名字需用 String str = new String(buffer); 转为String，使用buffer.toString会保错ViewExcel.SheetInfo sheetInfo = excelInfo.createSheetInfo(nicName);sheetInfo.createCell(0, 0, "时间");//这里对于参数：行，列，单元格名字sheetInfo.createCell(0, 1, "班级");sheetInfo.createCell(0, 2, "姓名");
第三步写得单元格
int row = 1;for(MachineNicTraffic entity : items){sheetInfo.createCell(row, 0,DateUtil.toDateTimeString(entity.getStatTime()));sheetInfo.createCell(row, 1, TrafficUtil.toMorM(entity.getTx()));sheetInfo.createCell(row, 2, TrafficUtil.toMorM(entity.getRx()));++row;}
这里就已经完成Excel的创建了
- 第四步导出Excel
modelMap.put("excelName", excelName);modelMap.put("excelInfo", excelInfo);return new ModelAndView(viewExcel, modelMap);
前端js直接调用这个Controller就可以导出
location.href="Controller地址"
这里完整代码是公司的代码所以就不发了
欢迎留言，评论，有留必回。。一起讨论一起成长谢谢
