1.要做这个动态的东西首先要一个基础组整出来,然后对应着它把数据进行循环
/**
* 导出日统计数据
* @return
*/
public String exportForDataDay(){
try {
getRequest().setCharacterEncoding("utf-8");
getResponse().setCharacterEncoding("utf-8");
getResponse().setContentType("application/x-download;charset=utf-8");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
String fname = Utils.getBytes(getRequest().getParameter("exportOss_date"));
String filename = fname+"DataDay.xls";
String filePath = getRequest().getRealPath("")+"/"+filename;
String sessionKey = getRequest().getParameter("key");
String title= getRequest().getParameter("title");
sessionKey =Utils.getBytes(sessionKey);
title = Utils.getBytes(title);
try {
if(filePath == null || filePath.trim().length() < 1 || sessionKey == null || sessionKey.length() < 1){
return LIST_ACTION;
}
String[][] data = (String[][])getRequest().getSession().getAttribute(sessionKey);
if(exportFormatDD(filePath,title,data)){
getResponse().setHeader("Content-Disposition", "attachment;filename=" +filename);
OutputStream out = getResponse().getOutputStream();
try { BufferedInputStream br = new BufferedInputStream(new FileInputStream(filePath));
byte[] buf = new byte[1024];
int len = 0;
while((len = br.read(buf)) >0)
out.write(buf,0,len);
} catch (IOException e) {
e.printStackTrace();
}finally{
if( out != null){
out.flush();
out.close();
}
}
deleteFile(filePath);// 清楚缓存文件
}else{
return LIST_ACTION;
}
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
/**
* 对日统计数据进行格式化输出到excel
* @param filePath 路径
* @param title sheet名称
* @param data excel内容
* @return 是否生成
*/
public boolean exportFormatDD(String filePath, String title, String[][] data) {
try {
if (data == null) {
return false;
}
WritableWorkbook wbook = Workbook.createWorkbook(new File(filePath)); // 建立excel文件
String tmptitle = title; // 标题
if (tmptitle == null || tmptitle.trim().length() < 1)
tmptitle = "OSS统计报表";
WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称
// 设置excel标题
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat wcfFC = new WritableCellFormat(wfont);
wcfFC.setAlignment(jxl.format.Alignment.CENTRE);
wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wcfFC.setBackground(Colour.ORANGE);
wsheet.addCell(new Label(0, 0, tmptitle, wcfFC));
wsheet.mergeCells(0, 0, 6, 0);
//设置首行
wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
wcfFC = new WritableCellFormat(wfont);
wcfFC.setAlignment(jxl.format.Alignment.CENTRE);
wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
wsheet.addCell(new Label(0, 1, data[0][0], wcfFC));//推广渠道
wsheet.setColumnView(0,20);
wsheet.mergeCells(0, 1, 0, 2);
wsheet.addCell(new Label(1, 1, data[0][1], wcfFC));//推广子渠道
wsheet.setColumnView(1,20);
wsheet.mergeCells(1, 1, 0, 2);
wsheet.addCell(new Label(2, 1, data[0][2], wcfFC));//业务类型
wsheet.setColumnView(2,10);
wsheet.mergeCells(2, 1, 0, 2);
wsheet.addCell(new Label(3, 1, "", wcfFC));//空白行
wsheet.mergeCells(3, 1, 6, 1);
wsheet.addCell(new Label(3, 2, data[0][3], wcfFC));//当日开通
wsheet.setColumnView(3,10);
wsheet.addCell(new Label(4, 2, data[0][4], wcfFC));//当日展示
wsheet.setColumnView(4,10);
wsheet.addCell(new Label(5, 2, data[0][5], wcfFC));//72H活跃用户量
wsheet.setColumnView(5,15);
wsheet.addCell(new Label(6, 2, data[0][6], wcfFC));//72H独立展示用户量
wsheet.setColumnView(6,18);
Map<String, List<String[]>> detail = proceData(data);
detail.remove("子渠道名");
Set<String> mySetD = new HashSet<String>();//存放最后合计用的D列单元格坐标
int row = 3;
WritableFont contentFont = new jxl.write.WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
WritableCellFormat contentCF = new WritableCellFormat(contentFont);
contentCF.setAlignment(jxl.format.Alignment.RIGHT);
contentCF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
for (String strings : detail.keySet()) {
List<String[]> list = detail.get(strings);
wsheet.addCell(new Label(0, row, ((String[])list.get(0))[0], wcfFC));//推广渠道 : 企业名片(百阅)
wsheet.mergeCells(0, row, 0, row+list.size());
for (String[] dataStrs : list) {
int col = 0;
for (int i=1;i<dataStrs.length;i++) {
// 行数据
if (col <= 1) {
if (col==1) {//区分来显或者来显+点
String last = dataStrs[1].substring(dataStrs[1].length()-1, dataStrs[1].length());
if (last.equals("1")) {
wsheet.addCell(new Label(col + 1, row, "来显", wcfFC));
}else {
wsheet.addCell(new Label(col + 1, row, "来显+点", wcfFC));
}
}else {
wsheet.addCell(new Label(col + 1, row, dataStrs[i], wcfFC));
}
} else {
wsheet.addCell(new jxl.write.Number(col + 1, row, Integer.valueOf(dataStrs[i].trim()), contentCF));
}
col++;
}
row++;
}
//小计行
// wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
// wcfFC = new WritableCellFormat(wfont);
// wcfFC.setAlignment(jxl.format.Alignment.CENTRE);
// wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
// wcfFC.setBackground(Colour.YELLOW);
wsheet.addCell(new Label(1, row , "小计" ));
wsheet.mergeCells(1, row , 2, row );
if (list.size()>1) {//应需求要求修改成特殊的小计
wsheet.addCell(new Formula(3, row, "SUM(D"+(row-(list.size()-1))+":D"+(row-(list.size()-1))+")", contentCF ));
wsheet.addCell(new Formula(4, row, "SUM(E"+(row-(list.size()-1))+":E"+(row-(list.size()-1))+")", contentCF ));
wsheet.addCell(new Formula(5, row, "SUM(F"+(row-(list.size()-1))+":F"+(row-(list.size()-1))+")", contentCF ));
wsheet.addCell(new Formula(6, row, "SUM(G"+(row-(list.size()-1))+":G"+(row-(list.size()-1))+")", contentCF ));
}else {//正确的小计
wsheet.addCell(new Formula(3, row, "SUM(D"+(row-(list.size()-1))+":D"+(row)+")", contentCF ));
wsheet.addCell(new Formula(4, row, "SUM(E"+(row-(list.size()-1))+":E"+(row)+")", contentCF ));
wsheet.addCell(new Formula(5, row, "SUM(F"+(row-(list.size()-1))+":F"+(row)+")", contentCF ));
wsheet.addCell(new Formula(6, row, "SUM(G"+(row-(list.size()-1))+":G"+(row)+")", contentCF ));
}
//添加需要合计的单元格
if (0==(row-(row-(list.size()-1)))) {
mySetD.add(""+row);
}else {
mySetD.add(""+row);
mySetD.add(""+(row-(list.size()-1)));
}
row++;
}
wsheet.addCell(new Label(0, row, "来显合计", wcfFC));//来显合计
wsheet.mergeCells(0, row, 2, 1);
wsheet.addCell(new Formula(3, row, "SUM("+makeXY("D",mySetD)+")", wcfFC));
wsheet.addCell(new Formula(4, row, "SUM("+makeXY("E",mySetD)+")", wcfFC));
wsheet.addCell(new Formula(5, row, "SUM("+makeXY("F",mySetD)+")", wcfFC));
wsheet.addCell(new Formula(6, row, "SUM("+makeXY("G",mySetD)+")", wcfFC));
//wsheet.addCell(new Label(0, data.length+3, "来显+点合计", wcfFC));//来显+点合计
//wsheet.setColumnView(2,10);
//wsheet.mergeCells(0, data.length+3, 2, 1);
// 主体内容生成结束
wbook.write(); // 写入文件
wbook.close();
} catch (Exception ex) {
ex.printStackTrace();
return false;
}
return true;
}
/**
* 拼接合计的单元格坐标
* @param string
* @param mySetD
* @return
*/
private String makeXY(String string, Set<String> mySetD) {
StringBuilder xy = new StringBuilder("");
for (String str : mySetD) {
xy.append(","+string+str);
}
return xy.toString().substring(1);
}
/**
* 处理数据格式
* @param data[企业名片(百阅A1), 100151, 来显/点, 15878, 81034, 1353028 , 123607 ] ;串1
* [企业名片(百阅S2), 100154, 来显/点, 0, 0, 0 , 0 ];串2
* @return Map<10015, List[串1,串2]>
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public Map<String, List<String[]>> proceData(String data[][]){
Map<String, List<String[]>> detail = new LinkedHashMap<String, List<String[]>>();
for (String[] strings : data) {
String key = strings[1].substring(0, strings[1].length()-1);
if (detail.get(key)!=null) {
List list = (List)detail.get(key);
list.add(strings);
}else {
List<String[]> list = new ArrayList<String[]>();
list.add(strings);
detail.put(key, list);
}
}
return detail;
}