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 20142014年7月4日上午11:33:53 * * @see */@Controllerpublic class ReportControl {@Autowiredprivate ReportService reportService;@Autowiredprivate ShowService showService;@Autowiredprivate DataFileService dataFileService;@Autowiredprivate 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();}}}}