`

导出excel文件

阅读更多
package com.sf.novatar.tpl.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;


/**
 * 利用开源组件POI3.0.2动态导出EXCEL文档
 * 转载时请保留以下信息,注明出处!
 * @author leno
 * @version v1.0
 * @param <T> 应用泛型,代表任意一个符合javabean风格的类
 * 注意这里为了简单起见,boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 * byte[]表jpg格式的图片数据
 */

public class ExportExcel<T> {
	
	  public void exportExcel(Collection<T> dataset, OutputStream out , Map<String , String[]> mapper) throws IOException {
	      exportExcel("EXCEL导出", mapper, dataset, out, "yyyy-MM-dd");
	  }
	  @SuppressWarnings("unchecked")
	  public void exportExcel(String title, Map<String , String[]> mapper ,
	         Collection<T> dataset, OutputStream out, String pattern) throws IOException {
	      // 声明一个工作薄
	      HSSFWorkbook workbook = new HSSFWorkbook();
	      // 生成一个表格
	      HSSFSheet sheet = workbook.createSheet(title);
	      // 设置表格默认列宽度为15个字节
	      sheet.setDefaultColumnWidth((short) 15);
	      // 生成一个样式
	      HSSFCellStyle style = workbook.createCellStyle();
	      // 设置这些样式
	      style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
	      style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	      style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	      style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	      style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	      // 生成一个字体
	      HSSFFont font = workbook.createFont();
	      font.setColor(HSSFColor.VIOLET.index);
	      font.setFontHeightInPoints((short) 12);
	      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	      // 把字体应用到当前的样式
	      style.setFont(font);
	      // 生成并设置另一个样式
	      HSSFCellStyle style2 = workbook.createCellStyle();
	      style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
	      style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
	      style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	      style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	      style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
	      style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
	      style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	      style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	      // 生成另一个字体
	      HSSFFont font2 = workbook.createFont();
	      font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
	      // 把字体应用到当前的样式
	      style2.setFont(font2);
	      // 声明一个画图的顶级管理器
	      HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
	      // 定义注释的大小和位置,详见文档
	      HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
	      // 设置注释内容
	      comment.setString(new HSSFRichTextString("可以在POI中添加注释!"));
	      // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容.
	      comment.setAuthor("leno");
	      //产生表格标题行
	      HSSFRow row = sheet.createRow(0);
	      String[] headers = mapper.get("headers");
	      String[] properties = mapper.get("properties");
	      for (short i = 0; i < headers.length; i++) {
	         HSSFCell cell = row.createCell(i);
	         cell.setCellStyle(style);
	         HSSFRichTextString text = new HSSFRichTextString(headers[i]);
	         cell.setCellValue(text);
	      }
	      //遍历集合数据,产生数据行
	      Iterator<T> it = dataset.iterator();
	      int index = 0;
	      while (it.hasNext()) {
	         index++;
	         row = sheet.createRow(index);
	         T t = (T) it.next();
	         //利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
	         //Field[] fields = t.getClass().getDeclaredFields();
	         for (short i = 0; i < properties.length; i++) {
	            HSSFCell cell = row.createCell(i);
	            cell.setCellStyle(style2);
	            /*Field field = fields[i];
	            String fieldName = field.getName();*/
	            String fieldName = properties[i];
	            String getMethodName = "get"
	                   + fieldName.substring(0, 1).toUpperCase()
	                   + fieldName.substring(1);
	            try {
	                Class tCls = t.getClass();
	                Method getMethod = tCls.getMethod(getMethodName,
	                      new Class[] {});
	                Object value = getMethod.invoke(t, new Object[] {}) ;
	                if ( value == null ) {
	                	value = "";
	                }
	                cell.setCellValue(value.toString());
	                //判断值的类型后进行强制类型转换
	                String textValue = null;

	                if (value instanceof Date) {
	                   Date date = (Date) value;
	                   SimpleDateFormat sdf = new SimpleDateFormat(pattern);
	                    textValue = sdf.format(date);
	                }  else if (value instanceof byte[]) {
	                   // 有图片时,设置行高为60px;
	                   row.setHeightInPoints(60);
	                   // 设置图片所在列宽度为80px,注意这里单位的一个换算
	                   sheet.setColumnWidth(i, (short) (35.7 * 80));
	                   // sheet.autoSizeColumn(i);
	                   byte[] bsValue = (byte[]) value;
	                   HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
	                         1023, 255, (short) 6, index, (short) 6, index);
	                   anchor.setAnchorType(2);
	                   patriarch.createPicture(anchor, workbook.addPicture(
	                         bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
	                } else{
	                   //其它数据类型都当作字符串简单处理
	                   textValue = value.toString();
	                }
	                //如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
	                if(textValue!=null){
	                   Pattern p = Pattern.compile("^//d+(//.//d+)?$");  
	                   Matcher matcher = p.matcher(textValue);
	                   if(matcher.matches()){
	                      //是数字当作double处理
	                      cell.setCellValue(Double.parseDouble(textValue));
	                   }else{
	                      HSSFRichTextString richString = new HSSFRichTextString(textValue);
	                      HSSFFont font3 = workbook.createFont();
	                      font3.setColor(HSSFColor.BLUE.index);
	                      richString.applyFont(font3);
	                      cell.setCellValue(richString);
	                   }
	                }
	            } catch (SecurityException e) {
	                // TODO Auto-generated catch block
	                e.printStackTrace();
	            } catch (NoSuchMethodException e) {
	                // TODO Auto-generated catch block
	                e.printStackTrace();
	            } catch (IllegalArgumentException e) {
	                // TODO Auto-generated catch block
	                e.printStackTrace();
	            } catch (IllegalAccessException e) {
	                // TODO Auto-generated catch block
	                e.printStackTrace();
	            } catch (InvocationTargetException e) {
	                // TODO Auto-generated catch block
	                e.printStackTrace();
	            } finally {
	            	
	            }
	         }
	 
	      }
	      try {
	         workbook.write(out);
	      } catch (IOException e) {
	         // TODO Auto-generated catch block
	         e.printStackTrace();
	      } finally {
	    	  out.close();
	      }
	   }
	   
	   public static Map<String, String[]> parseXml( InputStream is ) throws DocumentException {
           SAXReader reader = new SAXReader();              
           Document  document = reader.read(is);
           Element root = document.getRootElement();
           @SuppressWarnings("unchecked")
           List<Element> columns = root.elements("column");
           String[] headers = new String[columns.size()];
           String[] properties = new String[columns.size()];
           for ( int i = 0; i < columns.size() ; i++ ) {
        	    headers[i] = columns.get(i).attribute("header").getValue();
        	    properties[i] = columns.get(i).attribute("property").getValue();
           }
           Map<String, String[]> map = new HashMap<String , String[]>();
           map.put("headers", headers);
           map.put("properties", properties);
		   return map;
	   }
	   
	   public static Map<String, String[]> parseXml2( InputStream is ) throws DocumentException {
           SAXReader reader = new SAXReader();              
           Document  document = reader.read(is);
           Element root = document.getRootElement();
           @SuppressWarnings("unchecked")
           List<Element> columns = root.elements("column");
           String[] types = new String[columns.size()];
           String[] properties = new String[columns.size()];
           for ( int i = 0; i < columns.size() ; i++ ) {
        	    types[i] = columns.get(i).attribute("type").getValue();
        	    properties[i] = columns.get(i).attribute("property").getValue();
           }
           Map<String, String[]> map = new HashMap<String , String[]>();
           map.put("types", types);
           map.put("properties", properties);
		   return map;
	   }
	   
}
/////导出方法2(same as 方法1)////////////////////////////////////////////
public void exportQueryResult(BeanBase bean) throws DocumentException, ParseException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException  {
		Map<String, String> paramsmap = bean.getDatas().get(0);
		String exportType = paramsmap.get("exportType");
		InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("excel/excelexport-" + exportType +".xml");
		Map<String , String[]> mapper = ExportExcel.parseXml(inputStream);
		String[] headers = mapper.get("headers");
		String[] properties = mapper.get("properties");
		Map<String, Object> map = new HashMap<String, Object>();
		List<String[]> excel = new ArrayList<String[]>();
		final Map<Integer, String> header = new HashMap<Integer, String>(); //报表头部
		header.put(headers.length, "RollPlan");
		int length = headers.length;
		String planDate = paramsmap.get("planDate");
		String reqDate = paramsmap.get("reqDate");
		String materialCode = paramsmap.get("materialCode");
		//String maturialDesc = paramsmap.get("maturialDesc");
		String supplierCode = paramsmap.get("supplierCode");
		//String supplierDesc = paramsmap.get("supplierDesc");
		String status = paramsmap.get("status");
		String sentStatus = paramsmap.get("sentStatus");
		PurRollPlanExample example = new PurRollPlanExample();
		PurRollPlanExample.Criteria cr = example.createCriteria();
		//########### 数据权限控制 START ###################
		AccessBean access = SessionUtil.getAttribute(SspConstants.ACCESS_KEY);
		if(access==null){
			throw new ServiceException("用户未授权");
		}
		//全网权限不控制
		if(!SspConstants.DATA_ADMIN.equals(access.getAdminUser())){
			if(SspConstants.DATA_SUP_ACCESS.equals(access.getAccessType())){              //判定是否是供应商权限
				cr.andSupplierCodeIn(access.getValueList());
			}/* else if (SspConstants.DATA_PUR_ACCESS.equals(access.getAccessType())){    //判定是否是采购权限
				String createPerson = UserUtil.getCurrentUser().getUsername();
				cr.andCreatePersonEqualTo(createPerson);
			}*/
		}
		//########### 数据权限控制  END  ###################
		if (StringUtils.isNotEmpty(materialCode)) {
		cr.andMaterialCodeEqualTo(materialCode);
		}
		/*if (StringUtils.isNotEmpty(maturialDesc)) {
			cr.andMaturialDescEqualTo(maturialDesc);
		}*/
		if (StringUtils.isNotEmpty(supplierCode)) {
			cr.andSupplierCodeEqualTo(supplierCode);
		}
		if (StringUtils.isNotEmpty(status)) {
			cr.andStatusEqualTo(status);
		}
		if (StringUtils.isNotEmpty(sentStatus)) {
			cr.andSentStatusEqualTo(sentStatus);
		}
		cr.andDelFlagEqualTo("1");
		if (StringUtils.isNotEmpty(planDate)) {
				cr.andPlanDateEqualTo(DateUtils.parseDate(planDate, "yyyy-MM"));
		}
		if (StringUtils.isNotEmpty(reqDate)) {
			try {
				cr.andReqDateEqualTo(new SimpleDateFormat("yyyy-MM-dd")
						.parse(reqDate));
			} catch (ParseException e) {
				logger.error("滚动计划根据需求日期查询异常!");
			}
		}
		
		List<PurRollPlan> list = null;
		try{
			list = purRollPlanDao.selectByExample(example);
		}catch(Exception e){
			//logger.error(e.getMessage(), e);
		}
		Class tCls = PurRollPlan.class;
		
		//导出id
		StringBuffer sbExp = new StringBuffer();
		if(CollectionUtils.isNotEmpty(list)) {
			for (PurRollPlan purDeliveryPlan : list) {
				sbExp.append(purDeliveryPlan.getId());
				String[] line = new String[length];
				
				String fieldName = "";
				for ( int i= 0 ; i < length ;i++) {
					fieldName = properties[i];
		            String getMethodName = "get"
		                   + fieldName.substring(0, 1).toUpperCase()
		                   + fieldName.substring(1);
	                Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
	                Object value = getMethod.invoke(purDeliveryPlan, new Object[] {});
	                String cellstr = "";
	                if ( value == null ) {
	                	cellstr = "";
	                } else if (value instanceof Date) {
	                	if("planDate".equals(fieldName)) {
	                		cellstr = DateUtil.date2Str((Date)value, DateUtil.SHORT_DATE_FORMAT );
	                		cellstr = cellstr.substring(0, 7);
	                	}
	                	if("reqDate".equals(fieldName) || "promiseDate".equals(fieldName)) {
	                		cellstr = DateUtil.date2Str((Date)value, DateUtil.SHORT_DATE_FORMAT );
	                	}
	                	if("createTime".equals(fieldName) || "updateTime".equals(fieldName)) {
	                		cellstr = DateUtil.date2Str((Date)value, DateUtil.NORMAL_DATE_FORMAT );
	                	}
		            } else  {
		            	cellstr = value.toString();
		            	if("status".equals(fieldName)) {
		            		if("1".equals(cellstr)) {
		            			cellstr = "已确认";
		            		}
		            		if("0".equals(cellstr)) {
		            			cellstr = "未确认";
		            		}
		            	}
		            }
	                line[i] = cellstr;
				}
				excel.add(line) ;
			}
		}
		map.put("orgCode","滚动计划");
		map.put("data", excel);
		map.put("header", header); //统计头部
		//map.put("titles", titles); //头部标题
		map.put("cellsTitle", headers);
		map.put("fileName", "RollPlan_" + DateUtils.formatDate(new Date(), "yyyy-MM-dd"));
		bean.getExpData().put("excelData", map);
/////////////////
public void suppDeliveryViewExport(BeanBase bean) throws DocumentException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
		String folder = AsnStockServiceImpl.class.getClassLoader().getResource("/excel").getPath();
		logger.info("folder : " + folder);
		Map<String, String> paramsmap = bean.getDatas().get(0);
		String exportType = paramsmap.get("exportType");
		InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("excel/excelexport-" + exportType +".xml");
		logger.info("folder2 : " + this.getClass().getClassLoader().getResource("excel/excelexport-" + exportType +".xml").getPath());
		String xmlPath = folder + File.separator + "excelexport-" + exportType +".xml";
		logger.info("xmlPath " + xmlPath);
		Map<String , String[]> mapper = ExportExcel.parseXml(inputStream);
		String[] headers = mapper.get("headers");
		String[] properties = mapper.get("properties");
		Map<String, Object> map = new HashMap<String, Object>();	
		List<String[]> excel = new ArrayList<String[]>();
		final Map<Integer, String> header = new HashMap<Integer, String>(); //报表头部
		header.put(headers.length, "SuppDeliveryView");
		int length = headers.length;
		String suppName = "%" + paramsmap.get("suppName") + "%";
		String material_name = "%" + paramsmap.get("material_name") + "%";
		String wHouse_name = "%" + paramsmap.get("wHouse_name") + "%";
		paramsmap.put("suppName", suppName);
		paramsmap.put("material_name", material_name);
		paramsmap.put("wHouse_name", wHouse_name);
		//########### 数据权限控制 START ###################
		AccessBean access = SessionUtil.getAttribute(SspConstants.ACCESS_KEY);
		if(access==null){
			throw new ServiceException("用户未授权");
		}
		//全网权限不控制
		if(!SspConstants.DATA_ADMIN.equals(access.getAdminUser())){
			if(SspConstants.DATA_SUP_ACCESS.equals(access.getAccessType())){               //判定是否是供应商权限
				//cr.andSupplierCodeIn(access.getValueList());
				paramsmap.put("supAccessSql", access.getAccessSql());
			} else if (SspConstants.DATA_INV_ACCESS.equals(access.getAccessType())){       //判定是否是仓管权限
				//cr.andRecWarehouseCodeIn(access.getValueList());
				paramsmap.put("invAccessSql", access.getAccessSql());
			} else if (SspConstants.DATA_PUR_ACCESS.equals(access.getAccessType())){       //判定是否是采购权限
				//cr.andRecWarehouseCodeIn(access.getValueList());
				paramsmap.put("purAccessSql", access.getAccessSql());
			}
		}
		//########### 数据权限控制  END  ###################
		List<Map<String, Object>> list = suppDeliveryViewDao.queryDeliveryViewList2(paramsmap);
		StringBuffer expId = new StringBuffer();
		for(Map<String, Object> m : list) {
			expId.append(m.get("material_code"));
			String[] line = new String[length];
			String fieldName = "";
			//int j = m.keySet().size();
			for ( int i=0; i<length; i++) {
				fieldName = properties[i];
                Object value = m.get(fieldName);
                String cellstr = "";
                if(value != null) {
                	cellstr = value.toString();
                	if("delivery_status".equals(fieldName)) { //字段显示调整
                		if("1".equals(cellstr)) {
	            			cellstr = "已完成收货";
	            		}
	            		if("0".equals(cellstr)) {
	            			cellstr = "未完成收货";
	            		}
                	}
                	if("isCreatedAsn".equals(fieldName)) {
                		if("1".equals(cellstr)) {
	            			cellstr = "已创建";
	            		}
	            		if("0".equals(cellstr)) {
	            			cellstr = "未创建";
	            		}
                	}
                	if("audit_status".equals(fieldName)) {
                		if("1".equals(cellstr)) {
	            			cellstr = "已审核";
	            		}
	            		if("0".equals(cellstr)) {
	            			cellstr = "未审核";
	            		}
                	}
                	if("receivedStatus".equals(fieldName)) {
                		if(Double.parseDouble(cellstr) <= 0) {
	            			cellstr = "已完成收货";
	            		} else {
	            			cellstr = "未完成收货";
	            		}
                	}
                	if("suppPromiseDelayTime".equals(fieldName)) {
                		if(Double.parseDouble(cellstr) <= 0) {
	            			cellstr = "";
	            		} else {
	            			cellstr = "承诺交货已延迟" + cellstr + "天";
	            		}
                	}
                	if("asnCreateDelay".equals(fieldName)) {
                		if(Double.parseDouble(cellstr) > -3) {
	            			cellstr = "创建ASN已延迟" + (Double.parseDouble(cellstr) + 3) + "天";
	            		} else {
	            			cellstr = "";
	            		}
                	}
                	if("deliveryDelay".equals(fieldName)) {
                		if("0".equals(m.get("isCreatedAsn"))) {
                			cellstr = "";
                		} 
                	}
                	if("currDate".equals(fieldName)) {
                		if("0".equals(m.get("isCreatedAsn"))) {
                			cellstr = "";
                		} else {
                			cellstr = Math.abs(Double.parseDouble(cellstr)) + "";
                		}
                	}
                } else {
                	cellstr = "";
                } 
                line[i] = cellstr;
			}
			excel.add(line) ;
		}
		map.put("orgCode","供应商交货可视化报表");
		map.put("data", excel);
		map.put("header", header); //统计头部
		map.put("cellsTitle", headers);
		map.put("fileName", "SuppDeliveryView_" + DateUtils.formatDate(new Date(), "yyyy-MM-dd"));
		bean.getExpData().put("excelData", map);

////xml文件格式///////////
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE bean [
  <!ELEMENT bean (column+)>
  <!ATTLIST bean class CDATA #REQUIRED>
  <!ATTLIST bean type CDATA #REQUIRED>
  <!ELEMENT column EMPTY>
  <!ATTLIST column property CDATA #REQUIRED>
  <!ATTLIST column header CDATA #REQUIRED>
]>
<bean class="AsnStock" type="AsnStock">
	<column property="mAsnno" header="Asn单号" />
	<column property="dPurOrderNo" header="采购订单号" />
	<column property="line_item" header="行项目" />
    <column property="mSuppCode" header="供应商编码" />
    <column property="suppName" header="供应商编码名称" />
    <column property="material_code" header="物料编码" />
    <column property="material_name" header="物料名称" />
    <column property="unit" header="单位" />
    <column property="reserve_amount" header="预约交货数量" />
    <column property="received_amount" header="实际收货数量" />
    <column property="diff" header="差异" />
    <column property="mRecWhouseCode" header="收货仓库代码" />
    <column property="mRecWhouseName" header="收货仓库名称" />
    <column property="quantity_time" header="订单需求日期" />
    <column property="mReserveDelivTime" header="预约交货日期" />
    <column property="warehouse_received_time" header="仓库实际收货日期" />
    <column property="mAuditStatus" header="仓库审核状态" />
    <column property="mAsnCreator" header="创建人" />
    <column property="mAsnCreateTime" header="创建时间" />
    <column property="mAsnUpdatePerson" header="修改人" />
    <column property="mAsnUpdateTime" header="修改时间" />
</bean>
///////controler层///////////////////////////
/**
	 * 业务处理方法 导出:<br>
	 * 
	 * @param bean
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@RequestMapping("/export")
	public ModelAndView export(BeanBase bean,HttpServletRequest request) {
		Map<String, Object> map = null;
		try {
			bean.setContent(bean.getContent() + getCreateId());
			String processorName = bean.getNeedcontrolprocess();
			if (StringUtils.isEmpty(processorName)) {
				bean = service.service(bean);
			} else {
				bean.getExpData().put("request", request);
				process(bean);
				bean.getExpData().put("request", null);
			}
			map = (Map<String, Object>) bean.getExpData().get("excelData");
		} catch (ServiceException e) {
			logger.info(e.getMessage(), e);
			bean.setStatus(BusinessStatus.FAIL);
			bean.setProcessMsg(e.getMessage());
		}
		return new ModelAndView(new ViewExcel(), map);
	}

///////////////////
2:

public void exportTaxFavInfo(BeanBase bean){
		Map<String, Object> map = new HashMap<String, Object>();
		List<String[]> excel = new ArrayList<String[]>();
		final String[] cellsTitle = 
		  new String[] { "项目","税局批复日期","纳税主体编码","纳税主体名称",
						 "公司代码","公司名称","减免原因","减免方式","减免期间-开始","减免期间-结束",
						 "减免税率","减免金额","备案金额","备案号","是否有税收承诺",
						 "承诺税种","承诺金额","承诺时间","备注",
						 "创建人","创建时间","修改人","修改时间"};
		final Map<Integer, String> header = new HashMap<Integer, String>(); //报表头部
		header.put(cellsTitle.length, "税务优惠信息");
		
		String[] cells = null;
		int length = cellsTitle.length;
		int row = 1;
		List<Map<String, Object>> list = null;
		try{
			list = taxFavInfoDao.queryTaxFavInfo(bean.getDatas().get(0), 0, Integer.MAX_VALUE);	
		}catch(Exception e){
			logger.info(e.getMessage(), e);
		}

		for (Map<String, Object> data : list) {
			cells = new String[length];
			int i = 0;
			cells[i++] = String.valueOf(row); // 序号
			cells[i++] = String.valueOf(data.get("PROJECT"));
			cells[i++] = String.valueOf(data.get("REVIEW_DATE"));
			cells[i++] = String.valueOf(data.get("YWLXMC"));
			cells[i++] = String.valueOf(data.get("TAX_PAY_NAME"));
			cells[i++] = String.valueOf(data.get("CORP_CODE"));
			cells[i++] = String.valueOf(data.get("CORP_NAME"));
			cells[i++] = String.valueOf(data.get("BREAKS_CAUSE"));
			cells[i++] = String.valueOf(data.get("BREAKS_TYPE"));
			cells[i++] = String.valueOf(data.get("BREAKS_BEGIN_TIME"));
			cells[i++] = String.valueOf(data.get("BREAKS_END_TIME"));
			cells[i++] = String.valueOf(data.get("BREAKS_RATE"));
			cells[i++] = String.valueOf(data.get("BREAKS_AMT"));
			cells[i++] = String.valueOf(data.get("RECORD_AMT"));
			cells[i++] = String.valueOf(data.get("RECORD_NUMBER"));
			cells[i++] = String.valueOf(data.get("IS_PROMISE"));
			cells[i++] = String.valueOf(data.get("PROMISE_BREED"));
			cells[i++] = String.valueOf(data.get("PROMISE_AMT"));
			cells[i++] = String.valueOf(data.get("PROMISE_TIME"));
			cells[i++] = String.valueOf(data.get("REMARKS"));
			cells[i++] = String.valueOf(data.get("CREATE_ID"));
			cells[i++] = String.valueOf(data.get("CREATE_TIME"));
			cells[i++] = String.valueOf(data.get("UPDATE_ID"));
			cells[i++] = String.valueOf(data.get("UPDATE_TIME"));
			excel.add(cells);
			row++;
		}
		map.put("orgCode", "税务优惠信息");
		map.put("data", excel);
		map.put("header", header); //统计头部
		//map.put("titles", titles); //头部标题
		map.put("cellsTitle", cellsTitle);
		map.put("fileName", "税务优惠信息" + DateUtils.formatDate(new Date(), "yyyy-MM-dd"));
		bean.getExpData().put("excelData", map);
	}
/**
*controler层,根据不同service会调用不同业务
*
*/

public ModelAndView export(BeanBase bean,HttpServletRequest request) {
		Map<String, Object> map = null;
		try {
			bean.setContent(bean.getContent() + getCreateId());
			String processorName = bean.getNeedcontrolprocess();
			if (StringUtils.isEmpty(processorName)) {
				bean = service.service(bean);
			} else {
				process(bean);
			}
			map = (Map<String, Object>) bean.getExpData().get("excelData");
		} catch (Throwable e) {
			logger.info(e.getMessage(), e);
			bean.setStatus(BusinessStatus.FAIL);
			bean.setProcessMsg(e.getMessage());
		}
		return new ModelAndView(new ViewExcel(), map);
	}

/**
 *ViewExcel方法
 **/
package com.sf.novatar.tpl.util;

import java.io.OutputStream;
import java.util.ArrayList;
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.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;

@SuppressWarnings("deprecation")
public class ViewExcel extends AbstractExcelView {

	@SuppressWarnings("unchecked")
	@Override
	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		// sheet存在导出文件的机构代码,导入时需要使用此数据项
		String orgCode = (String) model.get("orgCode");
		HSSFSheet sheet = workbook.createSheet(orgCode);

		sheet.setDefaultColumnWidth(12);
		String[] cellsTitle = (String[]) model.get("cellsTitle");
		HSSFCell cell = null;
		
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellStyle.setFont(font);
		
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框

		int row = 0; //开始行
		int column = 0; //开始列
		int columns = 0; //合并列数
		//报表头部
		Map<Integer, String> header = (Map<Integer, String>) model.get("header");
		if (header != null) {
			for(Map.Entry<Integer, String> entry: header.entrySet()) {
				columns = entry.getKey().intValue(); //合并单元格列数
				String cotent = entry.getValue(); //单元格内容
				
				cell = getCell(sheet, row, 0);
				cell.setCellType(HSSFCell.ENCODING_UTF_16);
				cell.setCellStyle(cellStyle); //设置单元格样式
				setText(cell, cotent); //设置第row行0列单元格
				
				for (int i = 1; i < columns; i++) { //从第2列开始
					cell = getCell(sheet, row, i);
					cell.setCellStyle(cellStyle); //设置单元格样式
					setText(cell, ""); //设置第row行1列到column列单元格
				}
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, columns - 1)); //合并单元格
			}
			row = 1; //余下的从第1行开始
		}

		columns = 0;
		String[] columnTitles = null;
		String columnTitle = "";
		//报表合并标题
		Map<Integer, String[]> titles = (Map<Integer, String[]>) model.get("titles");
		if (titles != null) {
			for (String content : cellsTitle) {
				columnTitles = titles.get(column);
				if (columnTitles != null) {
					columns = column + Integer.valueOf(columnTitles[0]) - 1;
					columnTitle = columnTitles[1];
				}
				if (columnTitles != null || (column <= columns && column != 0)) {
					cell = getCell(sheet, row, column);
					cell.setCellType(HSSFCell.ENCODING_UTF_16);
					cell.setCellStyle(cellStyle);
					setText(cell, columnTitle);
					sheet.addMergedRegion(new CellRangeAddress(row, row, column, columns));
					
					cell = getCell(sheet, row + 1, column);
					cell.setCellType(HSSFCell.ENCODING_UTF_16);
					cell.setCellStyle(cellStyle);
					setText(cell, content);
				} else {
					cell = getCell(sheet, row, column);
					cell.setCellType(HSSFCell.ENCODING_UTF_16);
					cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置单元格内容上下居中
					cell.setCellStyle(cellStyle);
					setText(cell, content);
					
					cell = getCell(sheet, row + 1, column);
					cell.setCellStyle(cellStyle);
					setText(cell, "");
					sheet.addMergedRegion(new CellRangeAddress(row, row + 1, column, column));
				}
				column += 1;
			}
			row += 2;
		} else {
			for (String content : cellsTitle) {
				cell = getCell(sheet, row, column);
				cell.setCellType(HSSFCell.ENCODING_UTF_16);
				cell.setCellStyle(cellStyle);
				setText(cell, content);
				column += 1;
			}
			row += 1;
		}
		
		cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //居中
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框

		ArrayList<String[]> excelData = (ArrayList<String[]>) model.get("data");
		if (excelData != null) {
			// 记录行
			for (String[] contents : excelData) {
				column = 0;
				for (String content : contents) {
					cell = getCell(sheet, row, column);
					cell.setCellType(HSSFCell.ENCODING_UTF_16);
					cell.setCellStyle(cellStyle);
					setText(cell, content);
					column += 1;
				}
				row += 1;
			}
		}
		
		Map<String[], List<String[]>> rowData = (Map<String[], List<String[]>>) model.get("rowData");
		if (rowData != null && rowData.size() > 0) {
			for(Map.Entry<String[], List<String[]>> entry: rowData.entrySet()) {
				String[] keys = entry.getKey();
				List<String[]> valueList = entry.getValue();
				int size = valueList.size();
				
				for (int i = 0; i < keys.length; i++) {
					cell = getCell(sheet, row, i);
					cell.setCellType(HSSFCell.ENCODING_UTF_16);
					cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //设置单元格内容上下居中
					cell.setCellStyle(cellStyle);
					setText(cell, keys[i]);
					
					if (valueList.size() > 1) {
						for (int j = 1; j < valueList.size(); j++) {
							cell = getCell(sheet, row + j, i);
							cell.setCellStyle(cellStyle);
							setText(cell, "");
						}
						sheet.addMergedRegion(new CellRangeAddress(row, row + valueList.size() - 1, i, i));
					}
				}
				
				for (int i = 0; i < valueList.size(); i++) {
					String[] values = valueList.get(i);
					for (int j = 0; j < values.length; j++) {
						cell = getCell(sheet, row + i, keys.length + j);
						cell.setCellType(HSSFCell.ENCODING_UTF_16);
						cell.setCellStyle(cellStyle);
						//setText(cell, values[i]);
						setText(cell, values[j]);
					}
				}
				row += size;
			}
		}
		
		columns = 0;
		//报表底部
		Map<Integer, List<String>> footer = (Map<Integer, List<String>>) model.get("footer");
		if (footer != null) {
			for(Map.Entry<Integer, List<String>> entry: footer.entrySet()) {
				columns = entry.getKey().intValue(); //合并单元格列数
				List<String> totals = entry.getValue();
				
				cell = getCell(sheet, row, 0);
				cell.setCellType(HSSFCell.ENCODING_UTF_16);
				cell.setCellStyle(cellStyle);
				setText(cell, "合计:");
				for (int k = 1; k < columns; k++) {
					cell = getCell(sheet, row, k);
					cell.setCellStyle(cellStyle);
					setText(cell, "");
				}
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, columns - 1));
				
				for (String total : totals) {
					cell = getCell(sheet, row, columns++);
					cell.setCellType(HSSFCell.ENCODING_UTF_16);
					cell.setCellStyle(cellStyle);
					setText(cell, total);
				}
				
				for (int i = columns; i < cellsTitle.length; i++) {
					cell = getCell(sheet, row, i);
					cell.setCellStyle(cellStyle);
					setText(cell, "");
				}
			}
		}
		
		String filename = model.get("fileName") + ".xls";// 设置下载时客户端Excel的名称
		filename = ExcelNameEncode.encodeFilename(filename, request);// 处理中文文件名
		response.setContentType("application/vnd.ms-excel;charset=UTF-8");
		// response.setContentType("application/msexcel;charset=GB2312");
		response.setHeader("Content-disposition", "attachment;filename="
				+ filename);
		OutputStream ouputStream = response.getOutputStream();
		workbook.write(ouputStream);
		ouputStream.flush();
		ouputStream.close();
	}
}







分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics