ssm导出列表数据

最新推荐文章于 2023-05-01 14:23:07 发布

雨润泽林

最新推荐文章于 2023-05-01 14:23:07 发布

阅读量2.2k

点赞数

分类专栏： springmvc 文章标签：批量导出excel 导出列表

本文链接：https://blog.csdn.net/qq_17555933/article/details/51733095

版权

springmvc 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1、制作模板：

2、jsp：

<span style="font-size:12px;"><%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %> 
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta charset="utf-8">
<title></title>
<meta name="keywords" content="">
<meta name="description" content="">
<meta name="author" content="">
<META HTTP-EQUIV="Pragma" CONTENT="no-cache">
<META HTTP-EQUIV="Expires" CONTENT="-1">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="shortcut icon" href="">
</head>
<script type="text/javascript">
function enterpListExport(){
	var url = encodeURI(encodeURI('<%=request.getContextPath()%>/czevent/eventListExport.html'));
	window.open(url,'产值事件列表','');
}
</script>
<body >
   <div class="page005">
       <div class="row">
           <div class="col-xs-9">
               <div class="panel panel-ui">
                   <div class="panel-heading">
                       <form class="form-inline">
                           <div class="form-group">
                               <a href="javascript:;" οnclick="jkManage.export();" class="export action">事件导出</a>
                           </div>
                       </form>
                   </div>
                   <div class="panel-body">
                       <table class="table table-print-list">
                           <thead>
                           <tr>
                               <th>所属分类</th>
                               <th>产值事件</th>
                               <th>产值类型</th>
                               <th>产值最小值</th>
                               <th>产值最大值</th>
                               <th>经办人</th>
                               <th>审核人</th>
                               <th>管理</th>
                           </tr>
                           </thead>
                           <tbody id="body_czevent">
                           </tbody>
                       </table>
                       <nav class="text-right">
                           <ul class="pagination" id="pagination_czevent">
                           </ul>
                       </nav>
                   </div>
               </div>
           </div>
       </div>
   </div>
</body>
</html>

</span>

2、controller

<span style="font-size:12px;">
@Controller
@RequestMapping(value="/czevent")
public class CZEventController   extends Component<CzEvent>{
    private static String filename="";
    /**
     * 产值事件导出
     * @param request
     * @param response
     * @param page
     * @param entity
     */
    @RequestMapping(value="eventListExport")
    public void eventListExport(HttpServletRequest request, HttpServletResponse response,Page<CzEvent> page,CzEvent entity){
        try {
            EnterpriseInfo enterpriseInfo = this.getEnterprise(request);
            
            entity.setOrgid(enterpriseInfo.getEnterpriseid());
            this.setParams(request, entity, page);
            List<CzEvent> list = czEventService.queryByPage(page);
            for(CzEvent item : list){
                
                String opids = item.getOperatid();
                item.setOperatid(opids.split("\\|")[0]);
            
            String checkids = item.getCheckedid();
            item.setCheckedid(checkids.split("\\|")[0]);
            
            if (item.getCzparentid().equals("n")) {
                item.setCzparentid("流程内");
            }else if (item.getCzparentid().equals("w")) {
                item.setCzparentid("流程外");
            }
            
            if (item.getCztypeid().equals("1")) {
                item.setCztypeid("实产值");
            }else if (item.getCztypeid().equals("2")) {
                item.setCztypeid("虚产值");
            }else if (item.getCztypeid().equals("3")) {
                item.setCztypeid("创富产值");
            }else{
                item.setCztypeid("");
            }
        }
        
        Date currentTime = new Date();
        String nameString = new String("产值事件".getBytes("GB2312"),"8859_1");
        String tablename = nameString+(new SimpleDateFormat("yyyyMMddHHmmss").format(currentTime));
        
        Map<String, Object> data = new HashMap<String, Object>();
        data.put("list", list);
        response.setHeader("Content-disposition", "attachment; filename="+tablename+".xls");
        //定义输出类型为excel
        response.setContentType("application/msexcel");
        Excel excel = new Excel();
        excel.init("excelmodel/czeventListExport.xls", data);
        excel.writeTo(response.getOutputStream());
    } catch (Exception e) {
        log.error("操作异常",e);
        }
    }
}

</span>

3、Execl.java工具类

package com.util;

/**
 * Excel包装类。
 */
public class Excel {
	Logger logger = Logger.getLogger(Excel.class);
	/** Excel模版路径 */
	private static String REPORT_DIR = "";
	private XLSTransformer transformer = new XLSTransformer();
	private Workbook workbook;

	/**
	 * 初始化生成Excel。
	 * 
	 * @param templateFileName
	 *            模版文件名称
	 * @param model
	 *            数据模型
	 */
	public void init(String templateFileName, Map<String, ?> model) {
		try {
			String string = getClass().getResource("/").getPath();
			logger.info("Excel==============string:"+string);
			String[] strs = string.split("/");
			String path = "";
			for (int i = 0; i < strs.length - 2; i++) {
				path += strs[i] + File.separator;
			}
			path = path + templateFileName;
//			path = path.substring(1, path.length());
			logger.info(">>>>>>>>>>模板文件路径为：" + path);
			File file = new File(path);
			
			InputStream in = new FileInputStream(file);

			//InputStream in = getClass().getResourceAsStream(path);
			workbook = transformer.transformXLS(in, model);
			//transformer.t
			in.close();

		} catch (Exception e) {
			throw new BusinessException("生成Excel时发生异常。", e);
		}
	}

	/**
	 * 初始化生成多工作表的Excel。
	 * 
	 * @param data
	 *            Excel数据
	 */
	public void init(ExcelData data) {
		try {
			// 如果没有设置工作表名称列表，则根据指定的工作表名称字段自动生成。
			List<String> sheetNames = data.getSheetNames();
			if (sheetNames.isEmpty()) {
				for (Object sheetModel : data.getSheetModels()) {
					sheetNames.add(BeanUtils.getField(sheetModel,
							data.getSheetNameField()).toString());
				}
			}
			InputStream in = getClass().getResourceAsStream(
					REPORT_DIR + "/" + data.getTemplateFileName());
			workbook = transformer.transformMultipleSheetsList(in,
					data.getSheetModels(), sheetNames,
					data.getSheetModelName(), data.getOtherModel(),
					data.getStartSheetNum());
			in.close();
		} catch (Exception e) {
			throw new BusinessException("生成Excel时发生异常。", e);
		}
	}

	/**
	 * 转换成Excel文件输出流。
	 * 
	 * @return 返回Excel文件输出流。
	 */
	public ByteArrayOutputStream toOutputStream() {
		try {
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			workbook.write(out);
			out.flush();
			return out;
		} catch (Exception e) {
			throw new BusinessException("转换Excel文件输出流时发生异常。", e);
		}
	}

	/**
	 * 转换成Excel文件输入流。
	 * 
	 * @return 返回Excel文件输入流。
	 */
	public ByteArrayInputStream toInputStream() {
		try {
			ByteArrayOutputStream out = toOutputStream();
			ByteArrayInputStream in = new ByteArrayInputStream(
					out.toByteArray());
			out.close();
			return in;
		} catch (Exception e) {
			throw new BusinessException("转换Excel文件输入流时发生异常。", e);
		}
	}

	/**
	 * 将Excel文件写入到输出流。
	 * 
	 * @param out
	 *            输出流
	 */
	public void writeTo(OutputStream out) {
		try {
			workbook.write(out);
			out.flush();
			out.close();
		} catch (Exception e) {
			logger.info("Excel==============Exception:"+e.getMessage());
			throw new BusinessException("将Excel文件写入到输出流时发生异常。", e);
		}
	}

	public XLSTransformer getTransformer() {
		return transformer;
	}

	public void setTransformer(XLSTransformer transformer) {
		this.transformer = transformer;
	}

	public Workbook getWorkbook() {
		return workbook;
	}

	public void setWorkbook(Workbook workbook) {
		this.workbook = workbook;
	}
	
	
	public InputStream getInputStream(String tpath){
		InputStream in=null;
		try {
			String string = getClass().getResource("/").getPath();
			logger.info("Excel==============string:"+string);
			String[] strs = string.split("/");
			String path = "";
			for (int i = 0; i < strs.length - 2; i++) {
				path += strs[i] + File.separator;
			}
			path = path + tpath;
//			path = path.substring(1, path.length());
			logger.info(">>>>>>>>>>模板文件路径为：" + path);
			File file = new File(path);
			
			in = new FileInputStream(file);
		} catch (Exception e) {
		}
		return in;
	}
	/** 
     * 根据HSSFCell类型设置数据 
     *  
     * @param cell 
     * @return 
     */  
    public String getCellFormatValue(Cell cell) {  
        String cellvalue = "";  
        if (cell != null) {  
            // 判断当前Cell的Type  
            switch (cell.getCellType()) {  
            // 如果当前Cell的Type为NUMERIC  
            case HSSFCell.CELL_TYPE_FORMULA: {  
                // 判断当前的cell是否为Date  
                if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                    // 如果是Date类型则，转化为Data格式  
                    Date date = cell.getDateCellValue();  
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
                    cellvalue = sdf.format(date);  
  
                } else {  
                    // 如果是纯数字  
                    // 取得当前Cell的数值  
                    BigDecimal big = new BigDecimal(cell.getNumericCellValue());  
                    cellvalue = String.valueOf(big.longValue());  
                }  
                break;  
            }  
            case HSSFCell.CELL_TYPE_NUMERIC: {  
            	 if (HSSFDateUtil.isCellDateFormatted(cell)) {  
                     // 如果是Date类型则，转化为Data格式  
                     Date date = cell.getDateCellValue();  
                     SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  
                     cellvalue = sdf.format(date);  
   
                 } else {  
                     // 如果是纯数字  
                     // 取得当前Cell的数值  
                     BigDecimal big = new BigDecimal(cell.getNumericCellValue());  
                     cellvalue = String.valueOf(big.longValue());  
                 } 
                    // 如果是double，返回结果。  
                break;  
            }  
            // 如果当前Cell的Type为STRIN  
            case HSSFCell.CELL_TYPE_STRING:  
                // 取得当前的Cell字符串  
                cellvalue = cell.getRichStringCellValue().getString();  
                break;  
            // 默认的Cell值  
            default:  
                cellvalue = " ";  
            }  
        } else {  
            cellvalue = "";  
        }  
        return cellvalue;  
  
    }  
    
    public Iterator<Row> readExcel(String filePath) throws Exception {
         POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));  
         HSSFWorkbook workBook = new HSSFWorkbook(fs);  
         HSSFSheet sheet = workBook.getSheetAt(0);  
         Iterator<Row> it = sheet.rowIterator(); 
    	 return  it;
    }
}