导出excel文件

1.下载：poi-3.7-20101029.jar包并导入项目。

 

2.ExtportUtil.java

package com.wondersgroup.qyws.sjzk.action.sjhc;

import java.math.BigDecimal;
import java.util.List;

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.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;

public class ExtportUtil {
	public static int  writeRecords(HSSFRow row, HSSFSheet sheet,
			int nColumn, HSSFCell cell, HSSFWorkbook workbook, int iRow, List rs) {
		int size = rs.size();
		for (int j = 0; j < size; j++) {
			row = sheet.createRow(iRow);
			Object obj[] = new Object[]{};
			obj = (Object[]) rs.get(j);
			for (int k = 0; k < nColumn; k++) {
				cell = row.createCell(k);
				if (obj[k] != null) {
					cell.setCellValue(new HSSFRichTextString(obj[k]+""));
				} else {
					cell.setCellValue(new HSSFRichTextString("无效数据"));
				}
				HSSFCellStyle cellStyle = workbook.createCellStyle();
				cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
				cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
				cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
				cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
				
				//针对导出上传接口表的红色背景样式：
				if( obj.length==5 && obj[3].toString().equals("s") && ((BigDecimal)obj[4]).intValue()==0 ){
					cellStyle.setFillForegroundColor(HSSFColor.RED.index);
					cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
				}
				// 设置显示格式，避免点击后变成科学计数法了
				cellStyle.setDataFormat((short) 0x31);
				cellStyle.setWrapText(true);
				cell.setCellStyle(cellStyle);
			}
			iRow = iRow + 1;
		}
		return iRow-1;
	}
	public static int  writeRecordsWithNo(HSSFRow row, HSSFSheet sheet,
			int nColumn, HSSFCell cell, HSSFWorkbook workbook, int iRow, List rs) {
		int size = rs.size();
		for (int j = 0; j < size; j++) {
			row = sheet.createRow(iRow);
			Object obj[] = new Object[]{};
			obj = (Object[]) rs.get(j);
			for (int k = 0; k < nColumn; k++) {
				if(k==0){
					//序号单元格
					cell = row.createCell(k);
					cell.setCellValue(new HSSFRichTextString((j+1)+""));
					HSSFCellStyle cellStyle = workbook.createCellStyle();
					cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
					cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
					cellStyle.setDataFormat((short) 0x31);
					cellStyle.setWrapText(true);
					//针对导出上传接口表的红色背景样式：
					if( obj.length==5 && obj[3].toString().equals("s") && ((BigDecimal)obj[4]).intValue()==0 ){
						cellStyle.setFillForegroundColor(HSSFColor.RED.index);
						cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
					}
					cell.setCellStyle(cellStyle);
				}else{
					//数据单元格
					cell = row.createCell(k);
					if (obj[k-1] != null) {
						cell.setCellValue(new HSSFRichTextString(obj[k-1]+""));
					} else {
						cell.setCellValue(new HSSFRichTextString("无效数据"));
					}
					HSSFCellStyle cellStyle = workbook.createCellStyle();
					cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
					cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
					cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
					
					//针对导出上传接口表的红色背景样式：
					if( obj.length==5 && obj[3].toString().equals("s") && ((BigDecimal)obj[4]).intValue()==0 ){
						cellStyle.setFillForegroundColor(HSSFColor.RED.index);
						cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
					}
					// 设置显示格式，避免点击后变成科学计数法了
					cellStyle.setDataFormat((short) 0x31);
					cellStyle.setWrapText(true);
					cell.setCellStyle(cellStyle);
				}
			}
			iRow = iRow + 1;
		}
		return iRow-1;
	}
// 大标题样式
	public static HSSFCellStyle getStyle1(HSSFWorkbook workbook) {
		HSSFCellStyle style = workbook.createCellStyle(); // 设置单元格样式
		HSSFFont font = workbook.createFont();
		font.setFontName(HSSFFont.FONT_ARIAL);// 字体
		font.setFontHeightInPoints((short) 18);// 字号
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFont(font);
		return style;
	}

	// 小标题样式
	public static HSSFCellStyle getStyle2(HSSFWorkbook workbook) {
		// 一、数据质量报告
		HSSFCellStyle style = workbook.createCellStyle(); // 设置单元格样式
		HSSFFont font = workbook.createFont();
		font.setFontName(HSSFFont.FONT_ARIAL);// 字体
		font.setFontHeightInPoints((short) 12);// 字号
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setFont(font);
		return style;
	}

	// 描述文本样式
	public static HSSFCellStyle getStyle3(HSSFWorkbook workbook) {
		// 一、数据质量报告
		HSSFCellStyle style = workbook.createCellStyle(); // 设置单元格样式
		HSSFFont font = workbook.createFont();
		font.setFontName(HSSFFont.FONT_ARIAL);// 字体
		font.setFontHeightInPoints((short) 10);// 字号
		style.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
		style.setFont(font);
		return style;
	}

	// 数据表格标题样式
	public static HSSFCellStyle getThStyle(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_GENERAL);
//		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle.setWrapText(true);
		return cellStyle;
	}
	
	public static String getValue(String content) {
		String temp = "";
		if(content.startsWith("0")){
			temp = content.substring(1);
		}else{
			temp = content.substring(0);
		}
		return temp;
	}
}

3.应用：

package com.wondersgroup.qyws.sjzk.action.sjhc;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
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.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.apache.poi.ss.util.CellRangeAddress;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;

import com.opensymphony.xwork2.ActionContext;
import com.wondersgroup.qyws.sjzk.service.FxbgService;
import com.wondersgroup.qyws.sjzk.util.Constants;

@Controller()
@Scope("prototype")
public class ExtportFxbgAction {
	@Autowired
	private FxbgService fxbgService;
	// 描述分数
	private double hczhdf;
	private double sclxxdf;
	private double sjscfwdf;
	private double sjzldf;
	// 数据表格
	private List hczhdfList;
	private List sclxxdfList;
	private List sjscfwdfList;
	private List sjzldfList;
	private List zbdbList;
	// 评分分级
	private List zhdfGradeList;
	private List lxxGradeList;
	private List fwdfGradeList;
	private List zldfGradeList;
	//查询条件和条件展示
	private String sd = "";// 起始日期 - 查询条件
	private String ed = "";// 截止日期 - 查询条件
	private String ssd = "";// 起始日期 - 展示条件
	private String sed = "";// 截止日期 - 展示条件
	//报告描述
	private String msg;
	private String ds;
	
	/**
	 * 导出分析报告
	 * 
	 * @throws UnsupportedEncodingException
	 * */
	public String exportFxbg() throws UnsupportedEncodingException {
		ActionContext context = ActionContext.getContext();
		HttpServletResponse response = (HttpServletResponse) context
				.get(ServletActionContext.HTTP_RESPONSE);
		response.setContentType("application/vnd.ms-excel");
		if(ds == null){
			ds = (String) context.getSession().get("ds");
		}else{
			context.getSession().put("ds", ds);
		}
		String csd = "";
		String ced = "";
		if (sd.isEmpty()) {
			//默认执行
			int cha = ds.equals(Constants.CEN_INTER) ? 1 : 2;
			Calendar now = Calendar.getInstance();
			now.add(Calendar.DATE, -cha);
			ssd = now.get(Calendar.YEAR)+"年"+(now.get(Calendar.MONTH) + 1)+"月1日";
			sed = now.get(Calendar.YEAR)+"年"+(now.get(Calendar.MONTH) + 1)+"月"+(now.get(Calendar.DATE))+"日";
			ced = new SimpleDateFormat("yyyyMMdd").format(now.getTime());
			csd = ced.substring(0,6)+"01";//该月一号
			ed = new SimpleDateFormat("yyyy-MM-dd").format(now.getTime());
			sd = ed.substring(0,8)+"01";
		}else{
			//条件查询
			String sm = "";
			String se = "";
			String em = "";
			String ee = "";
			sm = ExtportUtil.getValue(sd.substring(4, 6));
			se = ExtportUtil.getValue(sd.substring(6, 8));
			em = ExtportUtil.getValue(ed.substring(4, 6));
			ee = ExtportUtil.getValue(ed.substring(6, 8));
			ssd = sd.substring(0, 4)+"年"+sm+"月"+se+"日";
			sed = ed.substring(0, 4)+"年"+em+"月"+ee+"日";
			csd = sd.substring(0, 4)+sd.substring(5, 7)+sd.substring(8, 10);
			ced = ed.substring(0, 4)+ed.substring(5, 7)+ed.substring(8, 10);
		}

		Map vals = fxbgService.getFxbg(csd, ced, ds);
		
		hczhdf = ((Double) vals.get("hczhdf")).doubleValue();
		sclxxdf = ((Double) vals.get("sclxxdf")).doubleValue();
		sjscfwdf = ((Double) vals.get("sjscfwdf")).doubleValue();
		sjzldf = ((Double) vals.get("sjzldf")).doubleValue();
		
		hczhdfList = (List) vals.get("hczhdfList");
		sclxxdfList = (List) vals.get("sclxxdfList");
		sjscfwdfList = (List) vals.get("sjscfwdfList");
		sjzldfList = (List) vals.get("sjzldfList");
		
		//[[0,''],[3,'a,b,c'],[4,'d,e,f,g],[0,'']]
		// 综合得分机构评级：[3，'jg1,jg2,jg3']
		zhdfGradeList = (List) vals.get("zhdfGradeList");
		// 数据上传连续性得分机构评级
		lxxGradeList = (List) vals.get("lxxGradeList");
		// 数据上传范围得分机构评级
		fwdfGradeList = (List) vals.get("fwdfGradeList");
		// 数据质量得分机构评级
		zldfGradeList = (List) vals.get("zldfGradeList");
//
		// 指标对比zbdbList
		zbdbList = (List) vals.get("zbdbList");
//		
		// 数据状况描述：
		msg = (String)vals.get("msg");
		
		String xlsName = "数据上传核查报告.xls";
		// Excel文档对象，会把数据导入到这个文档
		HSSFWorkbook workbook = new HSSFWorkbook();
		response.setHeader("Content-Disposition", "attachment; filename="
				+ URLEncoder.encode(xlsName, "utf-8"));

		int iRow = 0;// 行下标，整个sheet的行标控制
		workbook = transHczhdfListToExcel(workbook, iRow);

		ServletOutputStream fOut;
		try {
			fOut = response.getOutputStream();
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	private HSSFWorkbook transHczhdfListToExcel(HSSFWorkbook workbook, int iRow) {
		String[] theme = { "一、综合得分报告：", "二、上传连续性报告", "三、上传表范围报告", "四、数据质量报告","五、业务指标核对报告","六、评价与建议" };
		String[] titles1 = { "序号", "医疗机构", "得分" };
		String[] titles2 = { "序号", "医疗机构", "期内上传数据天数", "期内天数", "得分" };
		String[] titles3 = { "序号", "医疗机构", "实际上传表范围", "应上传表范围", "得分" };
		String[] titles4 = { "序号", "字典核查得分", "非空核查得分", "关联核查得分", "可追溯性核查得分", "数据质量得分" };
		String[] titles5 = { "数据来源\\指标", "门诊人次", "门诊总收入", "门诊药品收入", 
				"门诊检验人次", "门诊检查人次","门诊手术人次", "出院人次", "出院病人床日数", "出院病人总收入", "出院病人药品收入",
				"住院检验人次", "住院检查人次", "住院手术人次" };
		// 创建工作表(sheet)
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, "数据上传核查报告");// 下标0为第一个sheet

		// ------------------------ 大标题 ----------------------------------
		// 给sheet创建一个行
		HSSFRow row = sheet.createRow(iRow);// iRow = 0
		HSSFCell cell = null;
		cell = row.createCell(0);// 创建一个单元格
		// 设置字体样式
		HSSFCellStyle cellStyle1 = ExtportUtil.getStyle1(workbook); // 设置单元格样式
		cell.setCellStyle(cellStyle1);
		// 设置文件名第一行展示，这里题目名和sheet名一样
		cell.setCellValue(new HSSFRichTextString("数据上传核查报告"));
		sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4));

		// ------------------------ 日期说明 ----------------------------------
		iRow = iRow + 1;// 第二行 iRow= 1
		row = sheet.createRow(iRow);// 创建行
		HSSFCell rq = null;
		rq = row.createCell(0);// 创建单元格
		HSSFCellStyle rq_sty = ExtportUtil.getStyle3(workbook); // 设置单元格样式
		rq_sty.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
		rq.setCellStyle(rq_sty);
		rq.setCellValue(new HSSFRichTextString(ssd+" — "+sed));
		// new CellRangeAddress(y1,x1,y2,x2)
		sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4));

		// "其中得分为优的机构有2家医院,其中得分为良的机构有2家医院 ,"
		// + "其中得分为中的机构有4家医院，其中得分为差的机构有2家医院；详细列表如下：";
		String desc1 = "";
		String gs1 = "";
		if (zhdfGradeList.size() != 0) {
			desc1 = getHczhdfMs("数据质量核查综合", hczhdf, zhdfGradeList);
			gs1 = "注：得分= 上传连续性得分*权重 + 上传表范围得分*权重 + 数据质量得分*权重";
		}
		List la = setDescData(workbook, row, iRow, cell, theme[0], titles1,
				hczhdfList, desc1, gs1, true);
		String desc2 = "";
		String gs2 = "";
		if (sclxxdfList.size() != 0) {
			desc2 = getHczhdfMs("上传连续性", sclxxdf, lxxGradeList);
			gs2 = "注：得分=期内上传数据天数/期内天数*100";
		}
		la = setDescData((HSSFWorkbook) la.get(1), row, ((Integer) la.get(0))
				.intValue(), cell, theme[1], titles2, sclxxdfList, desc2, gs2, true);

		String desc3 = "";
		String gs3 = "";
		if (sjscfwdfList.size() != 0) {
			desc3 = getHczhdfMs("数据上传范围", sjscfwdf, fwdfGradeList);
			gs3 = "注：得分=实际上传表范围/应上传表范围*100";
		}
		la = setDescData((HSSFWorkbook) la.get(1), row, ((Integer) la.get(0))
				.intValue(), cell, theme[2], titles3, sjscfwdfList, desc3, gs3, true);

		String desc4 = "";
		String gs4 = "";
		if (sjzldfList.size() != 0) {
			desc4 = getHczhdfMs("数据质量", sjzldf, zldfGradeList);
			gs4 = "注：分项得分 = (数据总条数—错误数)/数据总条数*100；    数据质量得分 = 字典核查分项得分*权重 + 非空核查分项得分*权重 + 关联核查分项得分*权重 + 可追溯性核查分项得分*权重；";
		}
		
		la = setDescData((HSSFWorkbook) la.get(1), row, ((Integer) la.get(0))
				.intValue(), cell, theme[3], titles4, sjzldfList, desc4, gs4, true);

		//指标对比
		la = setDescData((HSSFWorkbook) la.get(1), row, ((Integer) la.get(0))
				.intValue(), cell, theme[4], titles5, zbdbList, "zbdb", "", false);
		
	// 六、评价与建议：
		iRow = ((Integer) la.get(0)).intValue() + 2;
		row = sheet.createRow(iRow);
		cell = row.createCell(0);
		cell.setCellStyle(ExtportUtil.getStyle2(workbook));
		cell.setCellValue(new HSSFRichTextString(theme[5]));
		sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4));

		iRow = iRow + 1;
		row = sheet.createRow(iRow);
		cell = row.createCell(0);
		cell.setCellStyle(ExtportUtil.getStyle3(workbook));
		cell.setCellValue(new HSSFRichTextString(msg));
		sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4));
		
		return workbook;
	}
	


	private String getHczhdfMs(String type, double fs, List list) {
		StringBuffer sb = new StringBuffer("本期" + type + "得分" + fs + ",");
		String[] grade = { "优", "良", "中", "差" };
		for (int i = 0; i < 4; i++) {
			int jgs = (Integer) list.get(2 * i);
			sb.append("本项得分为" + grade[i] + "的机构有" + list.get(2 * i) + "家医院");
			if (jgs != 0) {
				sb.append("：" + list.get(2 * i + 1) + "");
			}
			sb.append(";");
		}
		sb.append("详细列表如下：");
		return sb.toString();
	}

	private List setDescData(HSSFWorkbook workbook, HSSFRow row, int iRow,
			HSSFCell cell, String title, String[] ths, List list, String descc,
			String gs, boolean isOrder) {
		HSSFSheet sheet = workbook.getSheetAt(0);
		// ------------------------ 小标题 ----------------------------------
		iRow = iRow + 2;// 空一行
		row = sheet.createRow(iRow);// 创建行
		HSSFCell xbt = null;
		xbt = row.createCell(0);// 创建单元格
		HSSFCellStyle style2 = ExtportUtil.getStyle2(workbook); // 设置单元格样式
		xbt.setCellStyle(style2);
		xbt.setCellValue(new HSSFRichTextString(title));
		// new CellRangeAddress(y1,x1,y2,x2)
		sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 3));

		HSSFCellStyle thStyle = ExtportUtil.getThStyle(workbook);
		HSSFCellStyle desc_sty = ExtportUtil.getStyle3(workbook); // 设置单元格样式
		if (list.size() == 0) {
			iRow = iRow + 1;
			row = sheet.createRow(iRow);
			HSSFCell desc = null;
			desc = row.createCell(0);
			thStyle.setFillForegroundColor(HSSFColor.RED.index);
			desc.setCellStyle(thStyle);
			desc.setCellValue(new HSSFRichTextString("期内没有符合条件的数据。"));
			sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 3));
		} else {
			// ------------------------ 描述 --------------------------------
			if(!descc.isEmpty()){
				iRow = iRow + 1;// 第3行 iRow= 2
				row = sheet.createRow(iRow);
				HSSFCell desc = null;
				desc = row.createCell(0);
				desc.setCellStyle(desc_sty);
				desc.setCellValue(new HSSFRichTextString(descc));
				sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0,
						ths.length - 1));
			}

			// ------------------------ 数据表格 ----------------------------------
			iRow = iRow + 1;// 第4行 iRow= 3
			row = sheet.createRow(iRow);
			int nColumn = ths.length;
			for (int i = 1; i <= nColumn; i++) {
				sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度：18字符
				cell = row.createCell((i - 1));
				// --- 设置单元格样式 ---
				cell.setCellStyle(thStyle);
				// --- 给单元格赋值 ---
				cell.setCellValue(new HSSFRichTextString(ths[i - 1].toString()));
			}
			// --- 写入各条记录 ---
			iRow = iRow + 1;
			//导入的数据表格第一列是否有序号列
			if(isOrder){
				iRow = ExtportUtil.writeRecordsWithNo(row, sheet, nColumn, cell, workbook,
						iRow, list);
			}else{
				iRow = ExtportUtil.writeRecords(row, sheet, nColumn, cell, workbook,
						iRow, list);
			}
			// ------------------------- 注： -------------------
			if(!gs.isEmpty()){
				iRow = iRow + 1;
				row = sheet.createRow(iRow);
				HSSFCell ann = null;
				ann = row.createCell(0);
				HSSFCellStyle ann_sty = ExtportUtil.getStyle3(workbook);
				ann.setCellStyle(desc_sty);
				ann.setCellValue(new HSSFRichTextString(gs));
			}else{
				iRow = iRow + 1;
			}
			sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0,ths.length - 1));
		}
		List result = new ArrayList();
		result.add(iRow);
		result.add(workbook);
		return result;
	}

	public FxbgService getFxbgService() {
		return fxbgService;
	}

	public void setFxbgService(FxbgService fxbgService) {
		this.fxbgService = fxbgService;
	}

	public double getHczhdf() {
		return hczhdf;
	}

	public void setHczhdf(double hczhdf) {
		this.hczhdf = hczhdf;
	}

	public double getSclxxdf() {
		return sclxxdf;
	}

	public void setSclxxdf(double sclxxdf) {
		this.sclxxdf = sclxxdf;
	}

	public double getSjscfwdf() {
		return sjscfwdf;
	}

	public void setSjscfwdf(double sjscfwdf) {
		this.sjscfwdf = sjscfwdf;
	}

	public double getSjzldf() {
		return sjzldf;
	}

	public void setSjzldf(double sjzldf) {
		this.sjzldf = sjzldf;
	}

	public List getHczhdfList() {
		return hczhdfList;
	}

	public void setHczhdfList(List hczhdfList) {
		this.hczhdfList = hczhdfList;
	}

	public List getSclxxdfList() {
		return sclxxdfList;
	}

	public void setSclxxdfList(List sclxxdfList) {
		this.sclxxdfList = sclxxdfList;
	}

	public List getSjscfwdfList() {
		return sjscfwdfList;
	}

	public void setSjscfwdfList(List sjscfwdfList) {
		this.sjscfwdfList = sjscfwdfList;
	}

	public List getSjzldfList() {
		return sjzldfList;
	}

	public void setSjzldfList(List sjzldfList) {
		this.sjzldfList = sjzldfList;
	}

	public List getZhdfGradeList() {
		return zhdfGradeList;
	}

	public void setZhdfGradeList(List zhdfGradeList) {
		this.zhdfGradeList = zhdfGradeList;
	}

	public List getLxxGradeList() {
		return lxxGradeList;
	}

	public void setLxxGradeList(List lxxGradeList) {
		this.lxxGradeList = lxxGradeList;
	}

	public List getFwdfGradeList() {
		return fwdfGradeList;
	}

	public void setFwdfGradeList(List fwdfGradeList) {
		this.fwdfGradeList = fwdfGradeList;
	}

	public List getZldfGradeList() {
		return zldfGradeList;
	}

	public void setZldfGradeList(List zldfGradeList) {
		this.zldfGradeList = zldfGradeList;
	}

	public List getZbdbList() {
		return zbdbList;
	}

	public void setZbdbList(List zbdbList) {
		this.zbdbList = zbdbList;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}

	public String getSd() {
		return sd;
	}

	public void setSd(String sd) {
		this.sd = sd;
	}

	public String getEd() {
		return ed;
	}

	public void setEd(String ed) {
		this.ed = ed;
	}

	public String getSsd() {
		return ssd;
	}

	public void setSsd(String ssd) {
		this.ssd = ssd;
	}

	public String getSed() {
		return sed;
	}

	public void setSed(String sed) {
		this.sed = sed;
	}

	public String getDs() {
		return ds;
	}

	public void setDs(String ds) {
		this.ds = ds;
	}
}

  4.

package com.wondersgroup.qyws.sjzk.action.sjhc;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
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.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.ss.util.CellRangeAddress;
import org.apache.struts2.ServletActionContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;

import com.opensymphony.xwork2.ActionContext;
import com.wondersgroup.qyws.sjzk.service.FxbgService;
import com.wondersgroup.qyws.sjzk.util.Constants;

@Controller()
@Scope("prototype")
public class ExportDwfxbgAction {
	@Autowired
	private FxbgService fxbgService;
	private List zhlxxdf;
	private List itbsList;
	private List zbdbList;
	private List sjzldfList;
	private List lxxGradeList;
	private List sjscfwdfList;
	private List zhdfGradeList;
	private List fwdfGradeList;
	private List zldfGradeList;
	private String sd = "";// 起始日期 - 查询条件
	private String ed = "";// 截止日期 - 查询条件
	private String ssd = "";// 起始日期 - 展示条件
	private String sed = "";// 截止日期 - 展示条件
	private String jgdm = "";
	private String ds;
	private String msg;

	public String exportDwfxbg() throws UnsupportedEncodingException {
		ActionContext context = ActionContext.getContext();
		ds = (String) context.getSession().get("ds");
		
		HttpServletResponse response = (HttpServletResponse) context.get(ServletActionContext.HTTP_RESPONSE);
		response.setContentType("application/vnd.ms-excel");
		String csd = "";
		String ced = "";
		if (sd.isEmpty()) {
			// 默认执行
			int cha = ds.equals(Constants.CEN_INTER) ? 1 : 2;
			Calendar now = Calendar.getInstance();
			now.add(Calendar.DATE, -cha);
			ssd = now.get(Calendar.YEAR)+"年"+(now.get(Calendar.MONTH) + 1)+"月1日";
			sed = now.get(Calendar.YEAR)+"年"+(now.get(Calendar.MONTH) + 1)+"月"+(now.get(Calendar.DATE))+"日";
			ced = new SimpleDateFormat("yyyyMMdd").format(now.getTime());
			csd = ced.substring(0,6)+"01";//该月一号
			ed = new SimpleDateFormat("yyyy-MM-dd").format(now.getTime());
			sd = ed.substring(0,8)+"01";
		} else {
			// 条件查询
			String sm = "";
			String se = "";
			String em = "";
			String ee = "";
			sm = ExtportUtil.getValue(sd.substring(4, 6));
			se = ExtportUtil.getValue(sd.substring(6, 8));
			em = ExtportUtil.getValue(ed.substring(4, 6));
			ee = ExtportUtil.getValue(ed.substring(6, 8));
			ssd = sd.substring(0, 4)+"年"+sm+"月"+se+"日";
			sed = ed.substring(0, 4)+"年"+em+"月"+ee+"日";
			csd = sd.substring(0, 4)+sd.substring(5, 7)+sd.substring(8, 10);
			ced = ed.substring(0, 4)+ed.substring(5, 7)+ed.substring(8, 10);
		}
		Map vals = fxbgService.getDwFxbg(jgdm, csd, ced, ds);
		zhlxxdf = (List) vals.get("zhLxx");
		itbsList = (List) vals.get("itbsList");
		sjscfwdfList = (List) vals.get("sjscfwdfList");
		sjzldfList = (List) vals.get("zlhcList");
		zbdbList = (List) vals.get("zbdbList");

		// 综合得分机构评级：[3，'jg1,jg2,jg3']
		zhdfGradeList = (List) vals.get("zhdfGradeList");
		// 数据上传连续性得分机构评级
		lxxGradeList = (List) vals.get("lxxGradeList");
		// 数据上传范围得分机构评级
		fwdfGradeList = (List) vals.get("fwdfGradeList");
		// 数据质量得分机构评级
		zldfGradeList = (List) vals.get("zldfGradeList");
		
		// 指标对比zbdbList
		zbdbList = (List) vals.get("zbdbList");
		// 数据状况描述：
		msg = (String)vals.get("msg");
		
		String xlsName = zhlxxdf.get(6)+"数据上传核查报告.xls";
		// Excel文档对象，会把数据导入到这个文档
		HSSFWorkbook workbook = new HSSFWorkbook();
		response.setHeader("Content-Disposition", "attachment; filename="
				+ URLEncoder.encode(xlsName, "utf-8"));

		int iRow = 0;// 行下标，整个sheet的行标控制
		workbook = transHczhdfListToExcel(workbook, iRow);

		ServletOutputStream fOut;
		try {
			fOut = response.getOutputStream();
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return null;
	}

	private HSSFWorkbook transHczhdfListToExcel(HSSFWorkbook workbook, int iRow) {
		// 创建工作表(sheet)
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, zhlxxdf.get(6)+"数据上传核查报告");// 下标0为第一个sheet
		List datas = zhlxxdf;
		
		// ------------------------ 大标题 ----------------------------------
		// 给sheet创建一个行
		HSSFRow row = sheet.createRow(iRow);// iRow = 0
		HSSFCell cell = null;
		cell = row.createCell(0);// 创建一个单元格

		// 设置字体样式
		HSSFCellStyle cellStyle1 = ExtportUtil.getStyle1(workbook); // 标题单元格样式
		HSSFCellStyle style2 = ExtportUtil.getStyle2(workbook); // 二级标题单元格样式
		HSSFCellStyle rq_sty = ExtportUtil.getStyle3(workbook); // 日期文本单元格样式
		HSSFCellStyle desc_sty = ExtportUtil.getStyle3(workbook); // 描述文本单元格样式
		HSSFCellStyle thStyle = ExtportUtil.getThStyle(workbook); // 表格标题单元格样式

		cell.setCellStyle(cellStyle1);
		// 设置文件名第一行展示，这里题目名和sheet名一样
		cell.setCellValue(new HSSFRichTextString(zhlxxdf.get(6)+"数据上传核查报告"));
		sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4));

		// ------------------------ 日期说明 ----------------------------------
		iRow = iRow + 1;
		row = sheet.createRow(iRow);
		cell = row.createCell(0);
		rq_sty.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cell.setCellStyle(rq_sty);
		cell.setCellValue(new HSSFRichTextString(ssd+" — "+sed));
		sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4));

		// 一、综合得分报告：
		iRow = iRow + 2;
		row = sheet.createRow(iRow);
		cell = row.createCell(0);
		cell.setCellStyle(style2);
		cell.setCellValue(new HSSFRichTextString("一、综合得分报告"));
		sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 3));

		iRow = iRow + 1;
			setDescRow(iRow, row, cell, sheet, desc_sty, "本期数据质量核查综合得分为"
					+ datas.get(0) + "分。 其中上传连续性得分为" + ""
					+ datas.get(1) + "分。 上传范围得分为"
					+ datas.get(2) + "分。 数据质量得分为"
					+ datas.get(3) + "分。");

		iRow = iRow + 1;
		setDescRow(iRow, row, cell, sheet, desc_sty,
				"注：得分 = 上传连续性得分*权重 + 上传表范围得分*权重 + 数据质量得分*权重");

		// 二、上传连续性报告：
		iRow = iRow + 2;
		setDescRow(iRow, row, cell, sheet, style2, "二、上传连续性报告：");
		iRow = iRow + 1;
		setDescRow(iRow, row, cell, sheet, desc_sty, "本期上传连续性得分"
				+datas.get(1) + "分。期内上传天数为"
				+datas.get(4)+ " 天，期内天数为"
				+datas.get(5)+ "天。");

		iRow = iRow + 1;
		setDescRow(iRow, row, cell, sheet, desc_sty, "注：得分=期内上传数据天数/期内天数*100");

		// 三、数据上传范围报告：
		iRow = iRow + 2;
		setDescRow(iRow, row, cell, sheet, style2, "三、上传表范围报告：");
		// ------------------------ 数据表格 ----------------------------------
		if (datas.get(2) == null) {
			iRow = iRow + 1;
			row = sheet.createRow(iRow);
			setDescRow(iRow, row, cell, sheet, desc_sty, "本期数据上传总条数为0条。应上传表范围如下：");
		} else {
			iRow = iRow + 1;
			setDescRow(iRow, row, cell, sheet, desc_sty, "本期数据上传总条数为"
					+datas.get(7) + "条。应上传表范围如下（(红色记录表示期内从未上传过数据的接口表)）： ");

			iRow = iRow + 1;
			row = sheet.createRow(iRow);

			String[] ths1 = {"序号", "接口表", "接口表名称", "接口表类型" };
			int nColumn1 = ths1.length;
			for (int i = 1; i <= nColumn1; i++) {
				sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度：18字符
				cell = row.createCell((i - 1));
				cell.setCellStyle(thStyle);
				cell.setCellValue(new HSSFRichTextString(ths1[i - 1].toString()));
			}
			// --- 写入表格数据记录 ---
			iRow = iRow + 1;
			iRow = ExtportUtil.writeRecordsWithNo(row, sheet, nColumn1, cell, workbook,
					iRow, itbsList);

			// 本项得分为 58.25 分。详细列表如下：
			iRow = iRow + 1;
			setDescRow(iRow, row, cell, sheet, desc_sty, "本项得分为"
					+datas.get(2)+ "分。详细列表如下：");

			iRow = iRow + 1;
			row = sheet.createRow(iRow);
			String[] ths2 = { "序号", "日期", "实际上传表范围", "应上传表范围", "得分" };
			int nColumn2 = ths2.length;
			for (int i = 1; i <= nColumn2; i++) {
				sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度：18字符
				cell = row.createCell((i - 1));
				cell.setCellStyle(thStyle);
				cell.setCellValue(new HSSFRichTextString(ths2[i - 1].toString()));
			}
			iRow = iRow + 1;
			iRow = ExtportUtil.writeRecordsWithNo(row, sheet, nColumn2, cell, workbook,
					iRow, sjscfwdfList);
		}

		iRow = iRow + 1;
		setDescRow(iRow, row, cell, sheet, desc_sty, "注：得分 = 实际上传表范围/应上传表范围*100");

		// 四、数据质量报告：
		iRow = iRow + 2;
		setDescRow(iRow, row, cell, sheet, style2, "四、数据质量报告：");

		iRow = iRow + 1;
		row = sheet.createRow(iRow);
		if (datas.get(0) == null) {
			setDescRow(iRow, row, cell, sheet, desc_sty, "详细列表如下：");
		} else {
			String[] titles5 = { "序号","检查指标", "总条数", "错误数", "得分" };
			int tlsz5 = titles5.length;
			for (int i = 1; i <= tlsz5; i++) {
				sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度：18字符
				cell = row.createCell((i - 1));
				cell.setCellStyle(thStyle);
				cell.setCellValue(new HSSFRichTextString(titles5[i - 1].toString()));
			}
			iRow = iRow + 1;
			iRow = ExtportUtil.writeRecordsWithNo(row, sheet, tlsz5, cell, workbook,
					iRow, sjzldfList);
		}
		iRow = iRow + 1;
		setDescRow(iRow, row, cell, sheet, desc_sty,
				"注：分项得分=（数据总条数—错误数）/数据总条数*100");
		iRow = iRow + 1;
		setDescRow(iRow, row, cell, sheet, desc_sty, "数据质量得分=字典核查分项得分*权重 + 非空核查分项得分*权重 + 关联核查分项得分*权重 + 可追溯性核查分项得分*权重");

		// 五、业务指标核对报告：
		iRow = iRow + 2;
		setDescRow(iRow, row, cell, sheet, style2, "五、业务指标核对报告：");

		String[] titles6 = { "数据来源\\指标", "门诊人次", "门诊总收入", "门急诊药品收入", 
				"门诊检验人次", "门诊检查人次", "门诊手术人次", "出院人次", "出院病人床日数", "出院病人总收入", "出院病人药品收入",
				 "住院检验人次", "住院检查人次", "住院手术人次" };
		int tlsz6 = titles6.length;
		iRow = iRow + 1;
		row = sheet.createRow(iRow);
		for (int i = 1; i <= tlsz6; i++) {
			sheet.setColumnWidth(i - 1, 20 * 256);// 设置单元格宽度：18字符
			cell = row.createCell(i - 1);
			cell.setCellStyle(thStyle);
			cell.setCellValue(new HSSFRichTextString(titles6[i - 1]));
		}
		iRow = iRow + 1;
		iRow = ExtportUtil.writeRecords(row, sheet, tlsz6, cell, workbook, iRow,
				zbdbList);

		// 六、评价与建议：
		iRow = iRow + 2;
		setDescRow(iRow, row, cell, sheet, style2, "六、评价与建议：");

		iRow = iRow + 1;
		setDescRow(iRow,row,cell,sheet,desc_sty, msg);
		return workbook;
	}

	private void setDescRow(int iRow, HSSFRow row, HSSFCell cell,
			HSSFSheet sheet, HSSFCellStyle descSty, String string) {
		row = sheet.createRow(iRow);
		cell = row.createCell(0);
		cell.setCellStyle(descSty);
		cell.setCellValue(new HSSFRichTextString(string));
		sheet.addMergedRegion(new CellRangeAddress(iRow, iRow, 0, 4));
	}

	public FxbgService getFxbgService() {
		return fxbgService;
	}

	public void setFxbgService(FxbgService fxbgService) {
		this.fxbgService = fxbgService;
	}

	public List getZhlxxdf() {
		return zhlxxdf;
	}

	public void setZhlxxdf(List zhlxxdf) {
		this.zhlxxdf = zhlxxdf;
	}

	public List getItbsList() {
		return itbsList;
	}

	public void setItbsList(List itbsList) {
		this.itbsList = itbsList;
	}

	public List getSjscfwdfList() {
		return sjscfwdfList;
	}

	public void setSjscfwdfList(List sjscfwdfList) {
		this.sjscfwdfList = sjscfwdfList;
	}

	public List getSjzldfList() {
		return sjzldfList;
	}

	public void setSjzldfList(List sjzldfList) {
		this.sjzldfList = sjzldfList;
	}

	public List getZbdbList() {
		return zbdbList;
	}

	public void setZbdbList(List zbdbList) {
		this.zbdbList = zbdbList;
	}

	public List getZhdfGradeList() {
		return zhdfGradeList;
	}

	public void setZhdfGradeList(List zhdfGradeList) {
		this.zhdfGradeList = zhdfGradeList;
	}

	public List getLxxGradeList() {
		return lxxGradeList;
	}

	public void setLxxGradeList(List lxxGradeList) {
		this.lxxGradeList = lxxGradeList;
	}

	public List getFwdfGradeList() {
		return fwdfGradeList;
	}

	public void setFwdfGradeList(List fwdfGradeList) {
		this.fwdfGradeList = fwdfGradeList;
	}

	public List getZldfGradeList() {
		return zldfGradeList;
	}

	public void setZldfGradeList(List zldfGradeList) {
		this.zldfGradeList = zldfGradeList;
	}

	public String getSd() {
		return sd;
	}

	public void setSd(String sd) {
		this.sd = sd;
	}

	public String getEd() {
		return ed;
	}

	public void setEd(String ed) {
		this.ed = ed;
	}

	public String getSsd() {
		return ssd;
	}

	public void setSsd(String ssd) {
		this.ssd = ssd;
	}

	public String getSed() {
		return sed;
	}

	public void setSed(String sed) {
		this.sed = sed;
	}

	public String getDs() {
		return ds;
	}

	public void setDs(String ds) {
		this.ds = ds;
	}

	public String getJgdm() {
		return jgdm;
	}

	public void setJgdm(String jgdm) {
		this.jgdm = jgdm;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}
}