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();
}
}
}
}
poi 遍历excel行列 读写文件 反射(动态调用方法) 下载 (测试代码)
最新推荐文章于 2023-06-20 15:34:57 发布
这篇博客介绍了如何使用Apache POI库在Java中遍历Excel行列,读取和写入文件内容。通过反射动态调用对象方法,实现了数据的动态映射和导出。此外,还包含了下载Excel文件的方法。
摘要由CSDN通过智能技术生成