欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

jxl导出excel。 ExcelJ#freemarkerCacheJSP

程序员文章站 2022-03-02 16:59:43
...
最近在做exce的导出,用的是jxl,感觉上没有什么难处,但是哪个单元格的合并和填充值的时候感觉好难。
贴出来让大家看看,看看哪里还有不妥之处:
页面也就是一个jsp页面没啥可说的。

由于用的struts2,所有在struts2的action中添加了:如下:
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Pragma","No-cache");
response.setHeader("Cache-Control","no-cache");
response.setDateHeader("Expires", 0);
而后又写了一个jxl的导出的类:
package com.ruling.cost.utils;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import freemarker.log.Logger;

import jxl.Cell;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.JxlWriteException;
import jxl.write.biff.RowsExceededException;
import jxl.write.Number;

public class JxlExceptor {
//private static Logger log = Logger.getLogger(JxlExceptor.class);
OutputStream out;
// BufferedOutputStream bos;
private static JxlExceptor jxl = null;
private WritableWorkbook write = null;
WritableSheet sheet = null;
Label label = null;
Number num = null;
DateTime date = null;

WritableCellFormat headerFormat;
WritableCellFormat headtoFormat;
WritableCellFormat titleFormat;
WritableCellFormat otherFormat;
WritableCellFormat detFormat;
WritableCellFormat numFormat;
WritableCellFormat dateFormat;
WritableCellFormat headertitleFormat;// 报表第二标题样式。
WritableCellFormat floatFormat;

private JxlExceptor() {
};

public static JxlExceptor getInstence() {
if (jxl == null)
jxl = new JxlExceptor();
return jxl;
}
public void exportExcelMianMethod(HttpServletResponse response, String title)
throws IOException {
    out = response.getOutputStream();
   write = Workbook.createWorkbook(out);
   response.setContentType("aplication/vnd.ms-excel");
    response.addHeader("Content-Disposition", "inline; filename="
+ new String(title.getBytes("GB2312"), "ISO8859_1") + ".xls"); // 有中文必须转码
}
public void exportFormatDate() throws WriteException {

WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 12,
WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
// 表头样式
headerFormat = new WritableCellFormat(headerFont);
// 文字水平居中对齐
headerFormat.setAlignment(Alignment.CENTRE);

// 表头信息
WritableFont allToFont = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
Colour.BLACK);
headtoFormat = new WritableCellFormat(allToFont);
headtoFormat.setAlignment(Alignment.LEFT);

// 标题样式
titleFormat = new WritableCellFormat(allToFont);
titleFormat.setAlignment(Alignment.CENTRE);
titleFormat.setBackground(Colour.getInternalColour(22));
titleFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);

// 显示数据样式
detFormat = new WritableCellFormat(allToFont);
// 文字水平靠右对齐
detFormat.setAlignment(Alignment.RIGHT);
detFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
headertitleFormat = new WritableCellFormat(allToFont);
headertitleFormat.setAlignment(Alignment.CENTRE);
NumberFormat fivedps1 = new NumberFormat("#.00");
floatFormat = new WritableCellFormat (fivedps1);
jxl.write.NumberFormat cnf=  new jxl.write.NumberFormat("###,###,###,###,###.00");
jxl.write.WritableCellFormat cwcfN =   new jxl.write.WritableCellFormat(cnf);
cwcfN.setBorder(Border.ALL, BorderLineStyle.THIN);




}
public void getExcerByuser1(String filename,List listitem,List userlist,List list,HttpServletResponse response){
try {

exportExcelMianMethod(response, filename);
exportFormatDate();
sheet=write.createSheet("title",0);
sheet.mergeCells(0, 0, 0, 1);
sheet.setRowView(0, 500);
label = new Label(0, 0, "title", headerFormat);
sheet.addCell(label);
sheet.mergeCells(1,0,userlist.size(),0);
label = new Label(1, 0, "user", headerFormat);
sheet.addCell(label);
int healen = userlist.size();//添加user列表
for (int i = 0; i < healen; i++) {
Map map=(Map) userlist.get(i);
label = new Label(i+1,1,map.get("key").toString(), titleFormat);
sheet.addCell(label);

}
label = new Label(healen+1,1,"统计", titleFormat);
sheet.addCell(label);
int margelent=listitem.size();// for(int j=0;j<margelent;j++){// Map map=(Map) listitem.get(j);
label = new Label(0, j+2,map.get("key").toString(),titleFormat);
sheet.addCell(label);
}
//添加單元格內容
int size=0;
for(int i=0;i<list.size()-1;i++){
List itemlist=(List) list.get(i);// size=itemlist.size();// for(int j=0;j<itemlist.size();j++){
label = new Label(i+1,2+j,itemlist.get(j).toString(),detFormat);
sheet.addCell(label);
}
List zongjilist=(List) list.get(list.size()-1);
for(int z=0;z<margelent;z++){
label = new Label(healen+1,z+2,zongjilist.get(z).toString(),detFormat);
sheet.addCell(label);
}
}
label = new Label(0,size+1,"总计",titleFormat);
sheet.addCell(label);

write.write();
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
write.close();
//out.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}

}
public void getExcerByuser2(String filename,List listitem,List userlist,List list,HttpServletResponse response){
try {

exportExcelMianMethod(response, filename);
exportFormatDate();
sheet=write.createSheet("test",0);
sheet.mergeCells(0, 0, 0, 1);
sheet.setRowView(0, 500);
label = new Label(0, 0, "title", headerFormat);
sheet.addCell(label);
sheet.mergeCells(1,0,userlist.size(),0);
label = new Label(1, 0, "user", headerFormat);
sheet.addCell(label);
int healen = userlist.size(); for (int i = 0; i < healen; i++) {
Map map=(Map) userlist.get(i);
label = new Label(i+1,1,map.get("key").toString(), titleFormat);
sheet.addCell(label);

}
label = new Label(healen+1,1,"统计", titleFormat);
sheet.addCell(label);
int margelent=listitem.size();/ for(int j=0;j<margelent;j++){//
Map map=(Map) listitem.get(j);
label = new Label(0, j+2,map.get("key").toString(),titleFormat);
sheet.addCell(label);
}
//添加單元格內容
int size=0;
for(int i=0;i<list.size()-1;i++){// List itemlist=(List) list.get(i);// size=itemlist.size();// for(int j=0;j<itemlist.size();j++){
num = new jxl.write.Number(i+1,2+j,Double.parseDouble(itemlist.get(j).toString()),floatFormat);
sheet.addCell(num);
}
List zongjilist=(List) list.get(list.size()-1);
for(int z=0;z<margelent;z++){
num = new Number(healen+1,z+2,Double.parseDouble(zongjilist.get(z).toString()),floatFormat);
sheet.addCell(num);
}
}
label = new Label(0,size+1,"总计",titleFormat);
sheet.addCell(label);

write.write();
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
write.close();
//out.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}

}
}