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;
}
}
相关推荐
这个实例提供了两种方法来导出Excel文件：通过Servlet和通过main方法。这两种方法都是在Java环境中操作Excel数据的有效方式。 首先，让我们详细了解一下Apache POI库。POI提供了一个API，允许开发者在Java应用程序...
"Excel通用导出excel文件"这个主题涵盖了如何在不同情况下高效地实现这一目标，不论你是要处理小规模的数据还是大规模的数据集。下面，我们将深入探讨Excel的通用导出功能，以及如何利用它来优化你的工作流程。 ...
"C# 导出Excel文件 打开Excel文件格式与扩展名指定格式不一致的解决办法" 在导出Excel文件时，经常会遇到“文件格式与扩展名指定格式不一致”的问题，这是因为大多数人在导出Excel文件时，都是默认保存Excel的格式...
本篇文章将深入探讨如何使用C#来创建和导出Excel文件。 首先，你需要一个库来处理Excel文件。Microsoft提供了`Microsoft.Office.Interop.Excel`命名空间，这是一个COM接口，可以直接与Excel应用程序交互。但是，...
本文实例为大家分享了Winform实现导入导出Excel文件的具体代码，供大家参考，具体内容如下 /// /// 导出Excel文件 /// /// /// <param name=dataSet></param> /// 数据集 /// 导出后是否打开文件 /// ...
QT导出EXCEL，从tableWidget列表获取数据导出到excel文件。导出过程使用了excel模板文件，可以从tableWidget读取固定的几列数据导出到excel文件中。其中excel事先设置好文件格式，后续直接读数写数即可。开发过程...
自定义的一个excel文件导入导出类，支持Excel2003到2010的格式。里面也有简单的配置文件的创建。
C#快速导出Excel文件，3秒可导10万行数据
导出Excel文件涉及到从数据库或内存中的数据生成新的Excel文件。这个过程与导入类似，但方向相反。 1. **创建Excel工作簿**：使用EPPlus创建一个新的ExcelPackage对象，然后添加工作簿和工作表。 2. **填充数据**...
easyexcel 导出excel文件带多个图片JAVA代码包
"Html导出Excel文件(兼容所有浏览器)"的主题着重于实现这个功能，并确保在各种不同的浏览器环境下都能正常工作。这通常涉及到前端技术与后端技术的结合，以及对浏览器兼容性的深入理解。 首先，我们要明白HTML表格...
在本示例"SSM框架利用poi导入导出Excel文件 demo"中，我们将探讨如何在SSM项目中使用Apache POI库来实现Excel文件的导入与导出功能。 Apache POI是一个流行的开源库，它允许开发者在Java应用程序中创建、修改和显示...
Visual C++源代码 194 如何使用剪贴板导出Excel文件Visual C++源代码 194 如何使用剪贴板导出Excel文件Visual C++源代码 194 如何使用剪贴板导出Excel文件Visual C++源代码 194 如何使用剪贴板导出Excel文件Visual ...
Visual C++源代码 150 如何以数据源方式导出Excel文件Visual C++源代码 150 如何以数据源方式导出Excel文件Visual C++源代码 150 如何以数据源方式导出Excel文件Visual C++源代码 150 如何以数据源方式导出Excel文件...
Visual C++源代码 195 如何使用自动化导出Excel文件Visual C++源代码 195 如何使用自动化导出Excel文件Visual C++源代码 195 如何使用自动化导出Excel文件Visual C++源代码 195 如何使用自动化导出Excel文件Visual ...
在MVC架构下，处理导入和导出Excel文件是常见的需求，尤其在数据管理、报表生成以及数据交换场景中。下面我们将详细探讨如何在MVC应用中实现这个功能。 首先，导入Excel文件涉及到读取Excel数据并将其转化为可操作...
在Asp.net开发中，有时候我们需要将数据导出到Excel文件以便用户下载或者进行进一步的数据处理。... 1. **导出CSV文件** ...通过实践，开发者可以更好地掌握在Asp.net中导出Excel文件的技巧，提高项目开发效率。
本项目“poi多线程大数据导出excel文件”提供了一个解决方案，利用多线程来提高Excel的大数据导出效率。 Apache POI 3.1版本是较早的版本，而项目中使用了更新的4.1版本，这意味着它可能利用了更多优化和新特性。在...
Java实现导出Excel文件和XML文件 Java实现导出Excel文件和XML文件是Java语言中的一种常见需求，本文将详细介绍如何使用Java实现导出Excel文件和XML文件的功能。 一、功能简述 点击“导出”按钮，弹出文件选择...