java freemarker jxl excel_jxl导出excel。

jxl导出excel。

[package com.action; import java.io.FileOutputStream;      import java.io.IOException; import java.io.OutputStream;      import java.text.SimpleDateFormat;

最近在做exce的导出，用的是jxl，[ ExcelUtil .java   import jxl.format.Alignment; import jxl.format.Border; import jxl.format.BorderLineStyle; import jxl.format.Colour; import jxl.format.感觉上没有什么难处，但是哪个单元格的合并和填充值的时候感觉好难。

贴出来让大家看看，看看哪里还有不妥之处：

页面也就是一个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

label = new Label(0, j+2,map.get("key").toString(),titleFormat);

sheet.addCell(label);

}

//添加單元格內容

int size=0;

for(int i=0;i

List itemlist=(List) list.get(i);// size=itemlist.size();// for(int j=0;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

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

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

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

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();

}

}

}

}

[200使用jsp导出到excel汇总(jxl) 设置excel的导出格式 因为ms word和excel的文档都支持html文本格式，因此可以先用word或excel做好模版，另存为Web页，然后将该html改成