1 、页面代码
2， Action
package com.tempus.userCenter.web.complaints;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONArray;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import com.tempus.modules.web.struts2.Struts2Utils;
import com.tempus.modules.web.struts2.StrutsTemplte;
import com.tempus.userCenter.entity.authority.Users;
import com.tempus.userCenter.entity.complaints.CustomManage;
import com.tempus.userCenter.utils.ExportExcel;
@SuppressWarnings("serial")
public class SatisfiedchartAction extends StrutsTemplte {
// 基本属性
private String startdate;
private String enddate;
private String person;
private String sql;
private String fileurl;
public String srtXML;
List<Object> lo = new ArrayList<Object>();
@SuppressWarnings("unchecked")
public ArrayList ColumnNameList = new ArrayList();
// 记录总量
private int amount = 0;
// 满意记录数
private int count1 = 0;
// 不满意记录数
private int count2 = 0;
// 不需回访记录数
private int count3 = 0;
// 查看报表方法
@SuppressWarnings("unchecked")
public String qurey() {
if (startdate != null || enddate != null) {
srtXML = this.getXML();
request.setAttribute("srtXML", srtXML);
request.setAttribute("department", department);
request.setAttribute("person", person);
}
return SUCCESS;
}
<<报表形式
<chart caption='基于产品的投诉分析' shownames='1' showValues='1' yAxisMaxValue='5' baseFontSize='12' outCnvBaseFontSize='15'>
<categories>
<category label='酒店'/>
<category label='国际机票'/>
<category label='火车票'/>
<category label='度假产品'/>
<category label='国内酒店'/>
<category label='国际酒店'/>
<category label='国内机票'/>
</categories>
<dataset seriesName='投诉' showValues='1'>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='1'/>
<set value ='0'/>
</dataset>
<dataset seriesName='建议' showValues='1'>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
</dataset>
<dataset seriesName='差错' showValues='1'>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
</dataset>
<dataset seriesName='表扬' showValues='1'>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
<set value ='0'/>
</dataset>
</chart>
》》》》
private String getXML() {
StringBuffer sb = new StringBuffer();
sb
.append("<chart rotateYAxisName=\'0\' baseFont=\'宋体\' decimals=\'2\' yAxisMaxValue=\'5\' baseFontSize=\'14\' palette=\'3\' caption=\'");
sb.append("满意度分析");
sb.append("\' xAxisName=\'");
sb.append("满意度");
sb
.append("\' showValues=\'1\' decimals=\'0\' formatNumberScale=\'0\' useRoundEdges=\'1\'>\n");
sql = this.getSQL();
lo = complaintsManager.findBySQL(sql);
for (int i = 0; i < lo.size(); i++) {
Object[] le = (Object[]) lo.get(i);
if ("满意".equals(le[0])) {
count1 = count1 + Integer.parseInt(le[1].toString());
}
if ("不满意".equals(le[0])) {
count2 = count2 + Integer.parseInt(le[1].toString());
}
if ("不需回访".equals(le[0])) {
count3 = count3 + Integer.parseInt(le[1].toString());
} else {
sb.append("<set label=\'");
sb.append(le[0].toString());
sb.append("\' value=\'");
sb.append(le[1].toString());
sb.append("\' />\n");
}
}
amount = count1 + count2 + count3;
String stylesString = "<styles>\n<definition>\n"
+ "<style name=\'CaptionFontStyle\' type=\'font\' face=\'Verdana\' size=\'18\' bold=\'1\'/>\n"
+ "<style name=\'xAxisNameStyle\' type=\'font\' face=\'Verdana\' size=\'15\' bold=\'1\' />\n</definition>\n"
+ "<application>\n"
+ "<apply toObject=\'Caption\' styles=\'CaptionFontStyle\' />\n"
+ "<apply toObject=\'xAxisName\' styles=\'xAxisNameStyle\' />\n"
+ "</application>\n</styles>\n";
sb.append(stylesString);
srtXML = sb.toString() + "</chart>";
return srtXML;
}
//通过处理部门得到具体的处理人
//定义ext传过来的参数对应
private String department;
public void qureyPerson() throws Exception{
//new一个响应的对象
HttpServletResponse response = ServletActionContext.getResponse();
//参数赋值
String cp_department = department;
//定义sql
String sql1=" select name from t_users t where t.departmentcomposename ="+"'"+cp_department+"'";
//查询数据
List<Users> jsrListName = (List)complaintsManager.findBySQL(sql1);
response.setContentType("text/javascript"); //后台控制的代码
//new 一个 writer 对象
PrintWriter writer = response.getWriter();
//将得到的list集合转为JSON对象传给前台处理
JSONArray j = JSONArray.fromObject(jsrListName);
//调用有值 返回给ext
writer.println("{'success':true,'cmList':"+j.toString()+"}");
}
//報表查詢方法
public List<Object> findBySQL(final String sql) {
return complaintsdao.findBySQL(sql);
}
public List<Object> findBySQL(final String sql) {
Session session = this.getSession();
List<Object> catNameList = null;
try {
catNameList = session.createSQLQuery(sql).list();
return catNameList ;
}catch(Exception ex)
{
ex.printStackTrace();
}
return catNameList;
}
public Session getSession() {
return sessionFactory.getCurrentSession();
}
// 报表数据导出
@SuppressWarnings("unchecked")
public void toExcel() {
try {
// this.getSQL();
this.getXML();
ExportExcel exportExcel = new ExportExcel();
String path = this.getClass().getClassLoader().getResource("/")
.getPath();
path = path.substring(1, path.length());
fileurl = File.separator + path + "Satisfiedchar.xls";
String title = "满意度分析";
ArrayList ColumnNameList = new ArrayList();
ColumnNameList.add("满意度");
ColumnNameList.add("单数数");
// lo = complaintsManager.findBySQL(sql);
exportExcel.ExportExcelClient(title, ColumnNameList, lo, startdate,
enddate, fileurl);
HttpServletResponse response = Struts2Utils.getResponse();
// 重置响应
response.reset();
// 设置响应头为二进制流
response.setContentType("application/x-msdownload;charset=utf-8");
response.setContentType("APPLICATION/OCTET-STREAM ");
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition",
"attachment; filename=Satisfiedchar.xls");
InputStream is = new FileInputStream(fileurl);
HSSFWorkbook wb = new HSSFWorkbook(is);
// 得到 文件流
OutputStream out = response.getOutputStream();
wb.write(out);
is.close();
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 拼接SQL语句
@SuppressWarnings("static-access")
public String getSQL() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String enddate1 = null;
try {
Date date = sdf.parse(enddate);
Calendar calendar = new GregorianCalendar();
calendar.setTime(date);
calendar.add(calendar.DATE, 1);// 把日期往后增加一天.整数往后推,负数往前移动
date = calendar.getTime(); // 这个时间就是日期往后推一天的结果
enddate1 = sdf.format(date);
if ("".equals(department) || department == null) {
sql = "select t.customer_stai, count(*) from comp_disposal t ,complaints t1 where t1.gdTime >= to_date('"
+ startdate
+ "','yyyy-mm-dd') and t1.gdTime <= to_date('"
+ enddate1
+ "','yyyy-mm-dd') and t.gdid=t1.gdid"
+ " and t.customer_stai is not null"
+ " group by t.customer_stai";
} else {
if ("".equals(person) || person == null) {
sql = "select t.customer_stai, count(*) from comp_disposal t ,complaints_assign t1 where t1.fptime >= to_date('"
+ startdate
+ "','yyyy-mm-dd') and t1.fptime <= to_date('"
+ enddate1
+ "','yyyy-mm-dd') and t.gdid=t1.gdid"
+ " and t.customer_stai is not null"
+ " and t1.jsrdep='"
+ department
+ "' group by t.customer_stai";
} else {
sql = "select t.customer_stai, count(*) from comp_disposal t ,complaints_assign t1 where t1.fptime >= to_date('"
+ startdate
+ "','yyyy-mm-dd') and t1.fptime <= to_date('"
+ enddate1
+ "','yyyy-mm-dd') and t.gdid=t1.gdid"
+ " and t.customer_stai is not null"
+ " and t1.jsrname='"
+ person
+ "' group by t.customer_stai";
}
}
} catch (ParseException e) {
e.printStackTrace();
}
return sql;
}
//通过处理部门得到具体的处理人
//定义ext传过来的参数对应
private String department;
public void qureyPerson() throws Exception{
//new一个响应的对象
HttpServletResponse response = ServletActionContext.getResponse();
//参数赋值
String cp_department = department;
//定义sql
String sql1=" select name from t_users t where t.departmentcomposename ="+"'"+cp_department+"'";
//查询数据
List<Users> jsrListName = (List)complaintsManager.findBySQL(sql1);
response.setContentType("text/javascript"); //后台控制的代码
//new 一个 writer 对象
PrintWriter writer = response.getWriter();
//将得到的list集合转为JSON对象传给前台处理
JSONArray j = JSONArray.fromObject(jsrListName);
//调用有值 返回给ext
writer.println("{'success':true,'cmList':"+j.toString()+"}");
}
// ----------------------------get、set------------------------------------------
public String getStartdate() {
return startdate;
}
public void setStartdate(String startdate) {
this.startdate = startdate;
}
public String getEnddate() {
return enddate;
}
public void setEnddate(String enddate) {
this.enddate = enddate;
}
public String getFileurl() {
return fileurl;
}
public void setFileurl(String fileurl) {
this.fileurl = fileurl;
}
public String getSrtXML() {
return srtXML;
}
public void setSrtXML(String srtXML) {
this.srtXML = srtXML;
}
public String getPerson() {
return person;
}
public void setPerson(String person) {
this.person = person;
}
public String getDepartment() {
return department;
}
public void setDepartment(String department) {
this.department = department;
}
public int getAmount() {
return amount;
}
public void setAmount(int amount) {
this.amount = amount;
}
public int getCount1() {
return count1;
}
public void setCount1(int count1) {
this.count1 = count1;
}
public int getCount2() {
return count2;
}
public void setCount2(int count2) {
this.count2 = count2;
}
public int getCount3() {
return count3;
}
public void setCount3(int count3) {
this.count3 = count3;
}
}
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
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.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.hssf.util.Region;
/**
* EXCEL报表工具类.
*
* @author
* @version $Revision:$
*/
public class ExportExcel {
private HSSFWorkbook wb = new HSSFWorkbook();
private HSSFSheet sheet = wb.createSheet();
/**
* @return the sheet
*/
public HSSFSheet getSheet() {
return sheet;
}
/**
* @param sheet the sheet to set
*/
public void setSheet(HSSFSheet sheet) {
this.sheet = sheet;
}
/**
* @return the wb
*/
public HSSFWorkbook getWb() {
return wb;
}
/**
* @param wb the wb to set
*/
public void setWb(HSSFWorkbook wb) {
this.wb = wb;
}
@SuppressWarnings("unchecked")
public void ExportExcelClient(String title,ArrayList ColumnNameList,List<Object> lo,String startdate,String enddate,String fileurl){
//创建列标头LIST
// 计算该报表的列数
int number = ColumnNameList.size();
// 给工作表列定义列宽(实际应用自己更改列数)
// for (int i = 0; i < number; i++) {
// sheet.setColumnWidth((short)i, (short)6000);
//
// }
// 创建单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle();
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 指定当单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 创建报表头部
this.createNormalHead(title, number);
// 设置第二行
String[] params = new String[] {startdate, enddate };
this.createNormalTwoRow(params, number);
// 设置列头
HSSFRow row2 = sheet.createRow(2);
for(int i=0;i<number;i++){
sheet.setColumnWidth((short)i, (short)6000);
HSSFCell cell = row2.createCell((short)i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
cell.setCellValue(ColumnNameList.get(i).toString());
}
// 循环创建中间的单元格的各项的值
for (int i = 3; i <= lo.size()+2; i++) {
HSSFRow row = sheet.createRow((short) i);
Object[] le = (Object[]) lo.get(i-3);
for (int j = 0; j < number; j++) {
if(le[j]!=null){
HSSFCell cell = row.createCell((short)j);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
cell.setCellValue(le[j].toString());
}
//
// this
// .cteateCell(wb, row, (short) j,
// HSSFCellStyle.ALIGN_CENTER_SELECTION, le[j].toString());
}
}
this.outputExcel(fileurl);
}
/**
* 创建通用EXCEL头部
*
* @param headString 头部显示的字符
* @param colSum 该报表的列数
*/
public void createNormalHead(String headString, int colSum) {
HSSFRow row = sheet.createRow(0);
// 设置第一行
HSSFCell cell = row.createCell((short)0);
row.setHeight((short) 400);
// 定义单元格为字符串类型
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(headString);
// 指定合并区域
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (colSum-1)));
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 300);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
/**
* 创建通用报表第二行
*
* @param params 统计条件数组
* @param colSum 需要合并到的列索引
*/
public void createNormalTwoRow(String[] params, int colSum) {
HSSFRow row1 = sheet.createRow(1);
row1.setHeight((short) 300);
HSSFCell cell2 = row1.createCell((short)0);
cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
cell2.setCellValue("统计时间：" + params[0] + "至"
+ params[1]);
// 指定合并区域
sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) (colSum-1)));
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格字体
HSSFFont font = wb.createFont();
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
cell2.setCellStyle(cellStyle);
}
/**
* 设置报表标题
*
* @param columHeader 标题字符串数组
*/
public void createColumHeader(String[] columHeader) {
// 设置列头
HSSFRow row2 = sheet.createRow(2);
// 指定行高
row2.setHeight((short) 600);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 250);
cellStyle.setFont(font);
/*cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单无格的边框为粗体
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);*/
// 设置单元格背景色
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFCell cell3 = null;
for (int i = 0; i < columHeader.length; i++) {
cell3 = row2.createCell((short)i);
cell3.setCellType(HSSFCell.ENCODING_UTF_16);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(columHeader[i]);
}
}
/**
* 创建内容单元格
*
* @param wb HSSFWorkbook
* @param row HSSFRow
* @param col short型的列索引
* @param align 对齐方式
* @param val 列值
*/
public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col,
short align, String val) {
HSSFCell cell = row.createCell((short)col);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(val);
HSSFCellStyle cellstyle = wb.createCellStyle();
cellstyle.setAlignment(align);
cell.setCellStyle(cellstyle);
}
/**
* 创建合计行
* @param colSum 需要合并到的列索引
* @param cellValue
*/
public void createLastSumRow(int colSum, String[] cellValue) {
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 250);
cellStyle.setFont(font);
HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));
HSSFCell sumCell = lastRow.createCell((short)0);
sumCell.setCellValue("合计");
sumCell.setCellStyle(cellStyle);
sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short) 0,
sheet.getLastRowNum(), (short) colSum));// 指定合并区域
for (int i = 2; i < (cellValue.length + 2); i++) {
sumCell = lastRow.createCell((short)i);
sumCell.setCellStyle(cellStyle);
sumCell.setCellValue(cellValue[i-2]);
}
}
/**
* 输入EXCEL文件
*
* @param fileName 文件名
*/
public void outputExcel(String fileName) {
FileOutputStream fos = null;
try {
File file = new File(fileName);
if(file.exists()){
file.delete();
fos = new FileOutputStream(file);
wb.write(fos);
fos.close();
}else{
fos = new FileOutputStream(file);
wb.write(fos);
fos.close();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
687
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
