poi 遍历excel行列 读写文件 反射（动态调用方法） 下载 （测试代码）

package com.binhaifast.report.web.controller;


import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.binhaifast.report.entity.Report;
import com.binhaifast.report.service.ReportService;
import com.binhaifast.show.entity.Zcfzb1;
import com.binhaifast.show.service.ShowService;
import com.binhaifast.show.service.Zcfzb1Service;
import com.binhaifast.sys.entity.DataFile;
import com.binhaifast.sys.entity.User;
import com.binhaifast.sys.service.DataFileService;
import com.common.easyui.hibernate.page.Page;
/**
 * 
 * ClassName:ReportControl
 *
 * @author   刘各欢
 * @version  
 * @since    Ver 1.1
 * @Date	 2014	2014年7月4日		上午11:33:53
 *
 * @see
 */
@Controller
public class ReportControl {

	@Autowired
	private ReportService reportService;
	@Autowired
	private ShowService showService;
	@Autowired
	private DataFileService dataFileService;
	@Autowired
	private Zcfzb1Service zcfzb1service;
	
	
	
	@RequestMapping(value="/report/export.do",method=RequestMethod.GET)
	public void exportGet(){
	}
	
	/**
	 * 
	 * exportPost: 输出所有Report,为easyui所要求格式,{total:x,rows:[{xxx,xxx}]} post方法
	 * 
	 * @author 刘各欢
	 * @param page 当前页码
	 * @param rows 每页的结果数量
	 * @return
	 * @since  　Ver 1.1
	 */
	@ResponseBody
	@RequestMapping(value="/report/export.do",method=RequestMethod.POST)
	public Map<String,Object> exportPost(Page<Report> page){
		page=reportService.findByPage(page);
		List<Report> list = page.getList();
		List<Map<String,String>> datas = new ArrayList<Map<String,String>>();
		for (Report report : list) {
			Map<String, String> tempMap = this.reportToMap(report);
			datas.add(tempMap);
		}
		page.setDatas(datas);
		Map<String, Object> map = page.getMap();
		return map;
	}
	
	/**
	 * 
	 * convertToMap:将单个Report内的字段转换为单个Map
	 * 
	 * @author 刘各欢
	 * @param report
	 * @return
	 * @since  　Ver 1.1
	 */
	public Map<String,String> reportToMap(Report report){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss");
		String createDateString = sdf.format(report.getCreateDate());
		
		String id = report.getId();
		String name = report.getName();
		String createDate = createDateString;
		String fileId = report.getDataFileId();
		String createUserId = report.getCreateUserId();
		User user = report.getCreateUser();
		String realName = user.getRealName();
		String companyId = report.getCompanyId();
		
		
		Map<String,String> map = new HashMap<String,String>();
		map.put("id", id);
		map.put("name", name);
		map.put("createDate", createDate);
		map.put("fileId", fileId);
		map.put("createUserId", createUserId);
		map.put("realName", realName);
		map.put("companyId", companyId);
		map.put("downloadLink", "abcacb");
		return map;
	}
	
	/**
	 * 
	 * exportExcelGet: 导出excel
	 * 
	 * @author 刘各欢
	 * @since  　Ver 1.1
	 */
	@RequestMapping(value="/report/exportExcel.do",method=RequestMethod.GET)
	public void exportExcelGet(String year,String month,HttpServletRequest request) throws Exception{
		String path = request.getContextPath();
		String basePath = request.getScheme() + "://"
				+ request.getServerName() + ":" + request.getServerPort()
				+ path + "/"+"res/ex.xls";
		Report report=new Report();
		report.setCreateDate(new Date());
		String userid = 1+"";//((User)(request.getSession().getAttribute("curruser"))).getId();
		report.setCreateUserId(userid);
		report.setName("");
		reportService.save(report, basePath);
		
	}
	
	@RequestMapping(value="/report/exportExcel.do",method=RequestMethod.POST)
	public void exportExcelPost(HttpServletRequest request) throws Exception{
	}
	
	/**
	 * 
	 * downloadExcel: 下载excel
	 * 
	 * @author 刘各欢
	 * @since  　Ver 1.1
	 */
	@RequestMapping(value="/report/downloadExcel.do",method=RequestMethod.GET)
	public void downloadExcelGet(String fileId,HttpServletRequest request,HttpServletResponse response) throws Exception{
		//DataFile dataFile = dataFileService.findByOne();
		DataFile dataFile = dataFileService.findById("40288109475ca1c201475ca20a9a0001");
		OutputStream out = response.getOutputStream();
		response.setContentType("x-excel");
		response.setHeader("Content-Disposition","attachment;filename="+URLEncoder.encode("测试.xls","UTF-8"));
		FileCopyUtils.copy(dataFile.getContents(), out);
		out.flush();
		out.close();
	}
	
	@RequestMapping(value="/report/downloadExcel.do",method=RequestMethod.POST)
	public void downloadExcelPost(HttpServletRequest request) throws Exception{
	}
	/**
	 * 
	 * et:测试写入excel到硬盘
	 * 
	 * @author 李宁
	 * @param request
	 * @throws Exception
	 * @since  fhd　Ver 1.1
	 */
	@SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
	@ResponseBody
	@RequestMapping(value="/report/et.do")
	public void et(HttpServletRequest request) throws Exception{
		List<Zcfzb1> list = zcfzb1service.findBySome(2013, 7);
		Zcfzb1 z = null;
		if(!list.isEmpty()){
			z = list.get(0);
		}
		
		
		String path = request.getContextPath();
		String basePath = request.getScheme() + "://"
				+ request.getServerName() + ":" + request.getServerPort()
				+ path + "/"+"res/ex.xls";
		
		URL url = new URL(basePath);
		InputStream inputStream =null;
		try {
			inputStream=url.openStream();
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			HSSFSheet sheet = workbook.getSheet("资产负债表");
			Iterator rowIterator = sheet.rowIterator();

			System.out.println(z.getJyxjrzcQms());
			Class c = Class.forName("com.binhaifast.show.entity.Zcfzb1");  
			
			while(rowIterator.hasNext()){
				HSSFRow row = (HSSFRow) rowIterator.next();
				Iterator cellIterator = row.cellIterator();
				while(cellIterator.hasNext()){
					HSSFCell cell = (HSSFCell) cellIterator.next();
					String cellstring = cell.getStringCellValue();
					if(cellstring.startsWith("##")){
						String field = cellstring.substring(3,cellstring.length());
						String first = cellstring.substring(2, 3).toUpperCase();
						String v = "get"+first+field;
						
						Method m = c.getMethod(v );//动态调用Zcfzb1内的get方法
				        if((m.invoke(z)!=null)){
				        	Double d = (Double)m.invoke(z);
				        	cell.setCellValue( d );
				        }else{
				        	String d = "";
				        	cell.setCellValue( d );
				        }
					}
				}
			}
			//HSSFRow row = sheet.getRow(5);
			//HSSFCell cell = row.getCell(1);
			
			//cell.setCellValue(z.getJyxjrzcQms());
			DataFile dataFile = new DataFile();
			dataFile.setContents(workbook.getBytes());
			dataFile.setCreateDate(new Date());
			dataFile.setCreateUserId("1");
			dataFile.setRealName("Liu");
			dataFile.setSuffix("xls");
			dataFile.setPath("");
			dataFileService.save(dataFile);
			
			File file = new File("F:/exx.xls");
			FileOutputStream fos = new FileOutputStream(file);
			workbook.write(fos);
			fos.flush();  
			fos.close();
			inputStream.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			if(inputStream!=null){
				inputStream.close();
			}
			
		}

	}
	
}

版权声明：本文为博主原创文章，未经博主允许不得转载。