利用Struts2进行Excel文件的生成及下载功能实现。

利用Struts2进行Excel文件的生成及下载功能实现。

首先在Action控制层中编写如下方法：

/**
* 下载Demo（通过数据构造Excel文件并下载）
*
* @return Demo查询结果
*/
public InputStream getDemoSearchInputStream() {

    byte[] bytes = null;
    setDownloadFileName("下载测试_" + DateUtils.formatCn(false) + ".xls");
    ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    try {
          //传入流信息及相关参数,用以生成Excel文件
          //TODO 生成相应文件信息 DownLoadService.createExcelFile(outputStream);
          //...
          bytes = outputStream.toByteArray();

    } catch (Exception e) {
          LogFactory.warnWeb("获取下载文件出错 ");
    }

    return new ByteArrayInputStream(bytes);
}

在Service业务层实现Excel文件构造：

/**
	 * 获取数据并构造Excel文件供客户端下载
	 * 
	 * @param userId 用户编号
	 * 
	 * @param outputStream 提供下载的数据输入流
	 * @param opaccount 账户
	 */
	public static void createExcelFile(OutputStream outputStream, int userId, String opaccount) {
		try {
			
			// WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"));
			WritableWorkbook workbook = Workbook.createWorkbook(outputStream);
			WritableSheet sheet = workbook.createSheet("下载测试(Demo)", 0);
			
			// 设置列
			sheet.setColumnView(0, 30);
			sheet.setColumnView(1, 20);
			sheet.setColumnView(2, 30);
			sheet.setColumnView(3, 30);
			sheet.setColumnView(4, 30);
			sheet.setColumnView(5, 20);
			sheet.setColumnView(6, 30);
			sheet.setColumnView(7, 20);
			sheet.setColumnView(8, 10);
			sheet.setColumnView(9, 30);
			sheet.setColumnView(10, 20);
			sheet.setColumnView(11, 40);
			
			// 设置行
			sheet.setRowView(0, 300);
			sheet.setRowView(1, 300);
			sheet.setRowView(2, 300);
			sheet.setRowView(3, 300);
			sheet.setRowView(4, 300);
			sheet.setRowView(5, 300);
			sheet.setRowView(6, 300);
			sheet.setRowView(7, 300);
			sheet.setRowView(8, 300);
			sheet.setRowView(9, 300);
			sheet.setRowView(10, 300);
			sheet.setRowView(11, 300);
			
			// 标题的格式
			WritableFont title_style_format = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);
			WritableCellFormat title_cell_format = new WritableCellFormat(title_style_format);
			title_cell_format.setAlignment(jxl.format.Alignment.CENTRE); // //把水平对齐方式指定为居中
			title_cell_format.setWrap(true);// 是否自动换行
			title_cell_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 单元格边框
			title_cell_format.setBackground(Colour.GREY_50_PERCENT);// 灰色背景
			
			// 正文格式
			WritableFont content_label_style_format = new WritableFont(WritableFont.COURIER, 9, WritableFont.NO_BOLD, false);
			WritableCellFormat content_label_cell_format = new WritableCellFormat(content_label_style_format);
			content_label_cell_format.setAlignment(jxl.format.Alignment.CENTRE); // //把水平对齐方式指定为居中
			content_label_cell_format.setWrap(true);// 是否自动换行
			content_label_cell_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 单元格边框
			content_label_cell_format.setBackground(Colour.GREY_25_PERCENT);// 灰色背景
			
			// 金额格式
			NumberFormat content_money_format_g = new NumberFormat("#####0.00");
			WritableCellFormat content_money_style_format = new WritableCellFormat(content_money_format_g);
			content_money_style_format.setAlignment(jxl.format.Alignment.CENTRE); // //把水平对齐方式指定为居中
			content_money_style_format.setWrap(false);// 是否自动换行
			content_money_style_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 单元格边框
			content_money_style_format.setBackground(Colour.GREY_25_PERCENT);// 灰色背景
			
			// 日期格式
			
			DateFormat custom_date_format = new DateFormat("yyyy-MM-dd HH:mm:ss");
			WritableCellFormat content_date_cell_format = new WritableCellFormat(custom_date_format);
			content_date_cell_format.setAlignment(jxl.format.Alignment.CENTRE); // //把水平对齐方式指定为居中
			content_date_cell_format.setWrap(false);// 是否自动换行
			content_date_cell_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 单元格边框
			content_date_cell_format.setBackground(Colour.GREY_25_PERCENT);// 灰色背景
			
			Label label_0_title = new Label(0, 1, "订单号", title_cell_format);
			Label label_1_title = new Label(1, 1, "交易类型", title_cell_format);
			Label label_2_title = new Label(2, 1, "创建时间", title_cell_format);
			Label label_3_title = new Label(3, 1, "交易名称", title_cell_format);
			Label label_4_title = new Label(4, 1, "订单编号", title_cell_format);
			Label label_5_title = new Label(5, 1, "对方信息", title_cell_format);
			Label label_6_title = new Label(6, 1, "交易金额", title_cell_format);
			Label label_7_title = new Label(7, 1, "交易状态", title_cell_format);			
			Label label_8_title = new Label(8, 1, "收入/支出", title_cell_format);
			Label label_9_title = new Label(9, 1, "折扣金额", title_cell_format);
			Label label_10_title = new Label(10, 1, "手续费", title_cell_format);
			Label label_11_title = new Label(11, 1, "备注信息", title_cell_format);
			
			sheet.addCell(label_0_title);
			sheet.addCell(label_1_title);
			sheet.addCell(label_2_title);
			sheet.addCell(label_3_title);
			sheet.addCell(label_4_title);
			sheet.addCell(label_5_title);
			sheet.addCell(label_6_title);
			sheet.addCell(label_7_title);
			sheet.addCell(label_8_title);
			sheet.addCell(label_9_title);
			sheet.addCell(label_10_title);
			sheet.addCell(label_11_title);
			
			
			//此处获取相应数据
			//List<TranBean> list = ...
			
			for ( int i = 0 ; i < list.size() ; i++ ) {
				
				TranBean tranBean = list.get(i);
				OrdersPaymentInfo ordersInfo = tranBean.getOrdersInfo();
				if ( ValidateUtil.isNull(ordersInfo) ) continue;
				PaymentTypeEnum paymentTypeEnum = PaymentTypeEnum.getPaymentTypeEnumByValue(ordersInfo.getPaytype());
				String payTypeStr = ValidateUtil.isNotNull(paymentTypeEnum) ? paymentTypeEnum.getName() : null;
				String payStatusStr = StatusEnum.PaymentStatus.getPaymentStatusEnumByValue(ordersInfo.getSts()).getName();
				Label label_0_content = new Label(0, i + 2, ValidateUtil.isEmpty(ordersInfo.getMerchantorderid()) ? "--" : ordersInfo.getMerchantorderid(), content_label_cell_format);
				Label label_1_content = new Label(1, i + 2, payTypeStr, content_label_cell_format);
				DateTime date_2_content = new DateTime(2, i + 2, ordersInfo.getCreatetime(), content_date_cell_format);
				Label label_3_content = new Label(3, i + 2, ordersInfo.getOrdersummary(), content_label_cell_format);
				Label label_4_content = new Label(4, i + 2, ordersInfo.getOrdersid(), content_label_cell_format);
				Label label_5_content = new Label(5, i + 2, PaymentTypeEnum.MERCHANT.equals(paymentTypeEnum) ? "--"
						: ValidateUtil.isEmpty(ordersInfo.getOppositename()) ? SystemConstant.SYSTEM_OPPOSITENAME : ordersInfo.getOppositename(), content_label_cell_format);
				jxl.write.Number number_6_content = new jxl.write.Number(6, i + 2, ordersInfo.getOrderamount() / 100f, content_money_style_format);
				Label label_7_content = new Label(7, i + 2, payStatusStr, content_label_cell_format);
				
				Label label_8_content = new Label(8, i + 2, _getChangeTypeText(ordersInfo, userId), content_label_cell_format);
				jxl.write.Number number_9_content = new jxl.write.Number(9, i + 2, ordersInfo.getDiscountamount() / 100f, content_money_style_format);
				jxl.write.Number number_10_content = new jxl.write.Number(10, i + 2, ordersInfo.getPoundage() / 100f, content_money_style_format);
				Label label_11_content = new Label(11, i + 2, ordersInfo.getOrdersummary(), content_label_cell_format);
				
				sheet.addCell(label_0_content);
				sheet.addCell(label_1_content);
				sheet.addCell(date_2_content);
				sheet.addCell(label_3_content);
				sheet.addCell(label_4_content);
				sheet.addCell(label_5_content);
				sheet.addCell(number_6_content);
				sheet.addCell(label_7_content);
				sheet.addCell(label_8_content);
				sheet.addCell(number_9_content);
				sheet.addCell(number_10_content);
				sheet.addCell(label_11_content);
				
			}
			
			// 第一行格式
			
			WritableFont front_style_format = new WritableFont(WritableFont.ARIAL, 9, WritableFont.BOLD, false);
			front_style_format.setColour(Colour.BLUE);
			WritableCellFormat front_cell_format = new WritableCellFormat(front_style_format);
			front_cell_format.setAlignment(jxl.format.Alignment.CENTRE); // //把水平对齐方式指定为居中
			front_cell_format.setWrap(true);// 是否自动换行
			front_cell_format.setBorder(Border.ALL, BorderLineStyle.THIN);// 单元格边框
			front_cell_format.setBackground(Colour.GRAY_25);
			
			Label label_front = new Label(0, 0, "下载Demo数据", front_cell_format);
			
			sheet.mergeCells(0, 0, 11, 0);// 合并第一列第一行到第十二列第一行的所有单元格
			sheet.addCell(label_front);
			
			sheet.getSettings().setProtected(true);
			sheet.getSettings().setPassword("cmexico");
			
			workbook.write();
			workbook.close();
			
		} catch (Exception e) {
			LogFactory.errorWeb("下载Demo出错！", e);
		}
	}

防止中文名乱码（IE、火狐等）:

/**
	 * @return the downloadFileName
	 */
	public String getDownloadFileName() {
		
		String fileName = "默认文件名称.xls";
		if ( ValidateUtil.isEmpty(downloadFileName) ) setDownloadFileName(fileName);
		try {
			
			String agent = request.getHeader("User-Agent");
			if ( ValidateUtil.isEmpty(agent) ) return downloadFileName;
			
			agent = agent.toLowerCase();
			
			if ( agent.contains("firefox") ) return new String(downloadFileName.getBytes(), "ISO8859-1");
			
			if ( agent.contains("msie") ) return URLEncoder.encode(downloadFileName, "UTF-8");
			
			return URLEncoder.encode(downloadFileName, "UTF-8");
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return downloadFileName;
	}

完成了主要业务编码，下面对Struts文件做如下配置即可：

<action name="userProxyTemplate" class="com.hli.DownLoadAction">
		<result name="success" type="stream">
			<param name="contentType">application/msword</param>
			<param name="inputName">demoSearchInputStream</param>
			<param name="contentDisposition">attachment;filename="${downloadFileName}"</param>
			<param name="bufferSize">4096</param>
		</result>
	</action>

注：若浏览器点击下载乱码，需要在web.xml文件中添加配置：

   

    <mime-mapping>
     <extension>xls</extension>
     <mime-type>application/x-download</mime-type>
    </mime-mapping>