poi导出

package com.pingan.zt.pamm.ordermanage.utils;

import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.usermodel.Cell;
public class ExcelUtil {
  private ExcelUtil() {}
  /**
   * 导出excel头部标题
   * @param title
   * @param cellRangeAddressLength
   * @return
   */
  public static HSSFWorkbook createWorkbook(String title){
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFCellStyle styleTitle = createStyle(workbook, (short)16);
    HSSFSheet sheet = workbook.createSheet(title);
    sheet.setDefaultColumnWidth(25);
    HSSFRow rowTitle = sheet.createRow(0);
    HSSFCell cellTitle = rowTitle.createCell(0);
    cellTitle.setCellStyle(styleTitle);
    return workbook;
  }
  /**
   * 设定二级标题
   * @param workbook
   * @param secondTitles
   * @return
   */
  public static HSSFWorkbook makeSecondHead(HSSFWorkbook workbook, String[] secondTitles){
    // 创建用户属性栏
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFRow rowField = sheet.createRow(0);
    HSSFCellStyle styleField = createStyle(workbook, (short)13);		
    for (int i = 0; i < secondTitles.length; i++) {
      HSSFCell cell = rowField.createCell(i);
      cell.setCellValue(secondTitles[i]);
      cell.setCellStyle(styleField);			
    }
    return workbook;
  }
  /**
   * 插入数据
   * @param workbook
   * @param dataList
   * @param beanPropertys
   * @return
   */
  public static <T> HSSFWorkbook exportExcelData(HSSFWorkbook workbook, List<T> dataList, String[] beanPropertys) throws Exception{
    HSSFSheet sheet = workbook.getSheetAt(0);
    // 填充数据
    HSSFCellStyle styleData = workbook.createCellStyle();
    styleData.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    styleData.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    for (int j = 0; j < dataList.size(); j++) {
      HSSFRow rowData = sheet.createRow(j + 1);
      T t = dataList.get(j);
      for(int k=0; k<beanPropertys.length; k++){
        Object value = BeanUtils.getProperty(t, beanPropertys[k]);
        HSSFCell cellData = rowData.createCell(k);
        String valueStr = value != null ? value.toString() : "";
        cellData.setCellValue(valueStr);
        cellData.setCellStyle(styleData);
      }			
    }
    return workbook;
  }
  /**
   * 通用的读取excel单元格的处理方法
   * @param cell
   * @return
   */
  private static Object getCellValue(Cell cell) {
    Object result = null;
    if (cell != null) {
      switch (cell.getCellType()) {
      case Cell.CELL_TYPE_STRING:
        result = cell.getStringCellValue();
        break;
      case Cell.CELL_TYPE_NUMERIC:
        //对日期进行判断和解析
        if(HSSFDateUtil.isCellDateFormatted(cell)){
          double cellValue = cell.getNumericCellValue();
          result = HSSFDateUtil.getJavaDate(cellValue);
        }
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        result = cell.getBooleanCellValue();
        break;
      case Cell.CELL_TYPE_FORMULA:
        result = cell.getCellFormula();
        break;
      case Cell.CELL_TYPE_ERROR:
        result = cell.getErrorCellValue();
        break;
      case Cell.CELL_TYPE_BLANK:
        break;
      default:
        break;
      }
    }
    return result;
  }
  /**
   * 提取公共的样式
   * @param workbook
   * @param fontSize
   * @return
   */
  private static HSSFCellStyle createStyle(HSSFWorkbook workbook, short fontSize){
    HSSFCellStyle style = workbook.createCellStyle();		
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    // 创建一个字体样式
    HSSFFont font = workbook.createFont();
    font.setFontHeightInPoints(fontSize);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setFont(font);
    return style;
  }
}

/**
	 * 待发货订单导出excel
	 * @param request
	 * @return
	 */
	@RequestMapping(value = "/sendOrderListExportToExcel.do")
	public void sendOrderListExportToExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {
		Map<String, Object> allPara = new HashMap<String, Object>();
		try {
			String merchantId = request.getParameter("merchantId");
			int pageNumber = Integer.parseInt(request.getParameter("pageNum"));
			int pageSize = Integer.parseInt(request.getParameter("pageSize"));
			String startDate = request.getParameter("startDate");
			String endDate = request.getParameter("endDate");
			List<String> allStates = new ArrayList<String>();
			allStates.add(OrderManageService.pay_succeed_state);
			allPara.put("state", allStates);
			pageNumber = (pageNumber - 1) * pageSize;
			allPara.put("merchantId", Long.parseLong(merchantId));
			allPara.put("pageNum", pageNumber);
			allPara.put("pageSize", pageSize);
			allPara.put("startDate", startDate);
			allPara.put("endDate", endDate);
			List<OrderVo> allOrderVos = sendServiceImpl.getSendList(allPara);
			
			HSSFWorkbook workbook = ExcelUtil.createWorkbook("待发货订单");
		    String[] secondTitles = {"订单号", "快递公司", "快递单号"};
		    workbook = ExcelUtil.makeSecondHead(workbook, secondTitles);
		    String[] beanProperty = {"orderId","goodFlowType","goodFlowNum"};
		    workbook = ExcelUtil.exportExcelData(workbook, allOrderVos, beanProperty);
		    workbook.write(response.getOutputStream());
		    
		    //输出Excel文件.
			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
			//中文文件名支持
			String encodedfileName = new String("data".getBytes("UTF-8"), "UTF-8");
			
			response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedfileName + ".xls\"");
			workbook.write(response.getOutputStream());
			response.getOutputStream().flush();
			
		} catch (Exception e) {
			String msg = "导出订单列表excel异常";
			logger.error(msg,e);
			throw new Exception(msg,e);
		}
	}