导出指定格式的excel

用到的包jxl-2.6.jar

附件，为元代码。

类为抽象类，需要写其中的一些方法。

比如：

//标题

@Override
public List<Map<String, Object>> buildConfig() {
String config = "[{parent:'标准客户',label:'主体',data:{column:'main_body',source:'data',colmarge:1,decode:{1:'A',2:'B',3:'C'},default:''}},"
+ "{parent:'标准客户',label:'供应商编号',data:{column:'ebs_supplier_id',source:'data',colmarge:1}},"
+ "{parent:'标准客户',label:'供应商名称',data:{column:'ebs_supplier_name',source:'data',colmarge:1}},"
+ "{parent:'标准客户',label:'20个自然日内触发预警I的天数',data:{column:'warningone_days',source:'data',colmarge:1,default:0}},"
+ "{parent:'标准客户',label:'20个自然日内触发预警II的天数',data:{column:'warningtwo_days',source:'data',colmarge:1,default:0}},"
+ "{parent:'标准客户',label:'20个自然日内近30天净销售金额/审批时D值＜0.6的天数',data:{column:'total_divivalued_lasttwenty',source:'data',colmarge:1,default:0}},"
+ "{parent:'标准客户',label:'是否需要调整D值',data:{column:'is_changeD',source:'data',colmarge:1,default:0,decode:{1:是,0:否}}},"
+ "{parent:'标准客户',label:'统计日期',data:{column:'statistics_date',source:'data',colmarge:1,type:'date'}}"
+ "]";
return DoubleRounder.getGson().fromJson(config, new TypeToken<List<Map<String, Object>>>(){}.getType());
}

//内容

@Override
public Map<String, Object> queryExportData(List<?> ids)
throws CommException {
Map<String, Object> map = new HashMap<String, Object>();
Map<String, List<Map<String, Object>>> resultdata = new HashMap<String, List<Map<String, Object>>>();
List<Map<String, Object>> listex = null;
List<Map<String, Object>> listRk = rkMonitorDao.findfindValueChangeReport(sql.toString(),getQueryParam(queryInfo,startTime,endTime));
for(int i=0;listRk !=null && i< listRk.size();i++){
listex = Lists.newArrayList();
listex.add(listRk.get(i));
resultdata.put((String.valueOf(listRk.get(i).get("id"))),listex);
}
map.put("data", resultdata);
return map;
}

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.biff.DisplayFormat;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Blank;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormats;
import jxl.write.WritableCell;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;


public abstract class AbsExportExcel {

public WritableCell getColumnObject(int col, int row, Object v, Map<String, Object> config, Map<String, WritableCellFormat> cache, WritableFont font, boolean odd) throws WriteException{
Colour colour = odd?Colour.IVORY:Colour.WHITE;
if (CommValidation.isEmpty(v)) {
if (config.containsKey("default")) v = config.get("default");
else return new Blank(col,row,buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}
if (CommValidation.isEmpty(v)) return new Blank(col,row,buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
else if (config.containsKey("decode") && config.get("decode") != null) try{
return new Label(col,row,CommValidation.isEmpty(((Map<String, Object>)config.get("decode")).get(v.toString()))?v.toString():String.valueOf(((Map<String, Object>)config.get("decode")).get(v.toString())),buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}catch(Exception e){
return new Label(col,row,e.toString(),buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}else if (config.containsKey("unit") && "10k".equals(config.get("unit"))) try{
return new Number(col,row,Calculator.express("round(a/10000.0, 2)", v.toString()).doubleValue(),buildCachedFormat(cache, NumberFormats.THOUSANDS_FLOAT, font, colour));
}catch(Exception e){
return new Label(col,row,e.toString(),buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}else if (config.containsKey("unit") && "F10k".equals(config.get("unit"))) try{
return new Number(col,row,Calculator.express("floor(a/10000.0)", v.toString()).doubleValue(),buildCachedFormat(cache, NumberFormats.THOUSANDS_FLOAT, font, colour));
}catch(Exception e){
return new Label(col,row,e.toString(),buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}else if (config.containsKey("unit") && "‱".equals(config.get("unit"))) try{
return new Number(col,row,Calculator.express("round(a, 2)", v.toString()).doubleValue(),buildCachedFormat(cache, NumberFormats.FLOAT, font, colour));
}catch(Exception e){
return new Label(col,row,e.toString(),buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}else if (config.containsKey("unit") && "%".equals(config.get("unit"))) try{
return new Number(col,row,Calculator.express("round(a, 2)", v.toString()).doubleValue(),buildCachedFormat(cache, NumberFormats.PERCENT_INTEGER, font, colour));
}catch(Exception e){
return new Label(col,row,e.toString(),buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}else if (config.containsKey("type") && "date".equals(config.get("type"))) try{
return new DateTime(col,row,v instanceof Date?(Date)v:UtilDate.parseDate((String)v),buildCachedFormat(cache, DateFormats.FORMAT1, font, colour));
}catch(Exception e){
return new Label(col,row,e.toString(),buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}else if (config.containsKey("type") && "number".equals(config.get("type"))) try{
return new Number(col,row,new BigDecimal(v.toString()).doubleValue(),buildCachedFormat(cache, NumberFormats.THOUSANDS_FLOAT, font, colour));
}catch(Exception e){
return new Label(col,row,e.toString(),buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}else return new Label(col,row,v.toString(),buildCachedFormat(cache, NumberFormats.TEXT, font, colour));
}

public WritableCellFormat buildCachedFormat(Map<String, WritableCellFormat> cache, DisplayFormat display, WritableFont font, Colour colour) throws WriteException{
if (cache.containsKey(display.getFormatIndex()+""+colour.getValue())) return cache.get(display.getFormatIndex()+""+colour.getValue());
WritableCellFormat format = new WritableCellFormat(font, display);
format.setWrap(true);
format.setShrinkToFit(true);
format.setAlignment(Alignment.CENTRE);
format.setVerticalAlignment(VerticalAlignment.CENTRE);
format.setBorder(Border.ALL, BorderLineStyle.THIN);
format.setBackground(colour);//ICE_BLUE//PALE_BLUE
cache.put(display.getFormatIndex()+""+colour.getValue(), format);
return format;
}

public abstract Map<String, Object> queryExportData(List<?> ids) throws CommException;

public abstract List<Map<String, Object>> buildConfig();

public void initFontAndTitle(OutputStream out, String title) throws WriteException {
blodFont = new WritableFont(WritableFont.ARIAL, 25, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.YELLOW);
smallBoldFont = new WritableFont(WritableFont.ARIAL, 16, WritableFont.BOLD);
normalBoldFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD);
normalFont = new WritableFont(WritableFont.ARIAL, 9);
warnningFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.SINGLE, Colour.RED);
cfTitle = new WritableCellFormat(blodFont, NumberFormats.TEXT);
cfSubTitle = new WritableCellFormat(smallBoldFont, NumberFormats.TEXT);
cfNormalTitle = new WritableCellFormat(normalBoldFont, NumberFormats.TEXT);
cfTitle.setShrinkToFit(true);
cfTitle.setAlignment(Alignment.CENTRE);
cfTitle.setVerticalAlignment(VerticalAlignment.CENTRE);
cfTitle.setBorder(Border.ALL, BorderLineStyle.THIN);
cfTitle.setBackground(Colour.GRAY_50);
cfTitle.setWrap(true);
cfSubTitle.setShrinkToFit(true);
cfSubTitle.setAlignment(Alignment.CENTRE);
cfSubTitle.setVerticalAlignment(VerticalAlignment.CENTRE);
cfSubTitle.setBorder(Border.ALL, BorderLineStyle.THIN);
cfSubTitle.setBackground(Colour.GRAY_25);
cfSubTitle.setWrap(true);
cfNormalTitle.setShrinkToFit(true);
cfNormalTitle.setAlignment(Alignment.CENTRE);
cfNormalTitle.setVerticalAlignment(VerticalAlignment.CENTRE);
cfNormalTitle.setBorder(Border.ALL, BorderLineStyle.THIN);
cfNormalTitle.setBackground(Colour.GRAY_25);
}

public void initWorkbook(OutputStream out, String title) throws IOException{
ws = new WorkbookSettings();
ws.setEncoding("UTF-8");
wb = Workbook.createWorkbook(out, ws);
st = wb.createSheet(title, 0);
st.getSettings().setHorizontalCentre(true);
st.getSettings().setHorizontalFreeze(4);
st.getSettings().setShowGridLines(true);
st.getSettings().setVerticalCentre(true);
st.getSettings().setVerticalFreeze(3);
st.getSettings().setAutomaticFormulaCalculation(true);
st.getSettings().setDefaultColumnWidth(15);
}

public void export(String title, String header, OutputStream out, List<?> ids) throws Exception{
try {
initWorkbook(out, title);
} catch (Exception e) {
throw new Exception("初始化Excel工作区异常", e);
}
try {
initFontAndTitle(out, title);
} catch (Exception e) {
throw new  Exception("初始化字体和标题异常", e);
}
try{
meta = buildConfig();
}catch(Exception e){
throw new Exception("创建配置异常", e);
}
try{
data = queryExportData(ids);
}catch(Exception e){
throw new Exception("查询导出数据异常", e);
}
try{
exportExcelHeader(header);
}catch(Exception e){
throw new Exception("创建表头异常", e);
}
exportExcel(header, ids);
try {
wb.write();
wb.close();
wb = null;
} catch (IOException | WriteException e) {
throw new Exception("导出excel发生错误",e);
}
}

public void exportExcel(String header, List<?> ids) throws Exception{
try{
//handle data
List<Map<String, Object>> d;
if (ids != null) for (Object id : ids){
maxmerge = col = 0;row++;odd++;
try{
if (data == null) continue;
for (Map<String, Object> m : meta){
m = (Map<String, Object>) m.get("data");
if (m != null) {
// if (m.containsKey("decode") && !m.containsKey("cellFeatures") && m.get("decode") instanceof Map){
// WritableCellFeatures cf = new WritableCellFeatures();
// cf.setDataValidationList(((Map<String, String>)m.get("decode")).values());
// m.put("cellFeatures",cf);
// }
if (m.containsKey("column") && m.containsKey("source") && data.get(m.get("source")) != null 
&& (d=(List<Map<String, Object>>) ((Map<String, Object>)data.get(m.get("source"))).get(String.valueOf(id)))!=null){
colmerge = rowmerge = 0;
for (Map<String, Object> s : d){
WritableCell c = getColumnObject(col,row+rowmerge++,s.get(m.get("column")),m,cache,normalFont,odd%2==0);
// if (m.containsKey("cellFeatures")) c.setCellFeatures((WritableCellFeatures)m.get("cellFeatures"));
st.addCell(c);
}
maxmerge = Math.max(maxmerge, row+rowmerge);
}else{
st.addCell(getColumnObject(col,row,null,m,cache,normalFont,odd%2==0));
}
}
col++;
}
col = 0;
if (maxmerge > 1) {
for (Map<String, Object> m : meta){
m = (Map<String, Object>) m.get("data");
if (m != null && m.containsKey("colmarge")) {
st.mergeCells(col, row, col, maxmerge-1);
}else{
for (int r = row; r < maxmerge; r++)
if (CommValidation.isEmpty(st.getCell(col, r).getContents()))
st.addCell(new Blank(col,r,buildCachedFormat(cache, NumberFormats.TEXT, normalFont, odd%2==0?Colour.IVORY:Colour.WHITE)));
}
col++;
}
}
row = maxmerge>row?maxmerge-1:row;
if (row > 64500) {
WritableCell c = new Label(0,row+1,"数据记录已接近Excel可存储的最大记录条数, 后续数据全部省略!!",buildCachedFormat(cache, NumberFormats.TEXT, warnningFont, Colour.YELLOW));
WritableCellFeatures cf = new WritableCellFeatures();
cf.setComment("数据记录已接近Excel可存储的最大记录条数, 后续数据全部省略!!");
c.setCellFeatures(cf);
st.addCell(c);
st.mergeCells(0, row+1, count-1, row+1);
break;
}
}catch(Exception e){
throw e;
}
}
}catch(Exception e){
throw new Exception("导出excel发生错误",e);
}
}


public void exportExcelHeader(String header) throws WriteException, RowsExceededException {
row = 2;
for(Map<String, Object> m : meta){
st.addCell(new Label(col,row,m.containsKey("label")?(String)m.get("label"):"",cfNormalTitle));
if (m.containsKey("parent")){
if (parcol == col && "".equals(st.getCell(parcol, row-1).getContents())){
st.addCell(new Label(col,row-1,(String)m.get("parent"),cfSubTitle));
}else if (!st.getCell(parcol, row-1).getContents().equals(m.get("parent"))) {
st.addCell(new Label(col,row-1,(String)m.get("parent"),cfSubTitle));
st.mergeCells(parcol, row-1, col-1, row-1);
parcol = col;
}
}else{
parcol = col;
st.addCell(new Label(parcol,row-1,"",cfSubTitle));
st.mergeCells(parcol, row-1, col, row-1);
}
col++;count++;
}
st.mergeCells(parcol, row-1, col-1, row-1);
st.addCell(new Label(0,0,header,cfTitle));
st.mergeCells(0, 0, count-1, 0);
}

protected WritableFont blodFont, smallBoldFont, normalBoldFont, normalFont, warnningFont;
protected WritableCellFormat cfTitle, cfSubTitle, cfNormalTitle;
protected WorkbookSettings ws;
protected WritableWorkbook wb;
protected WritableSheet st;
protected List<Map<String, Object>> meta;
protected Map<String, Object> data;
protected Map<String, WritableCellFormat> cache = new HashMap<String, WritableCellFormat>();
protected int row = 0, col = 0, colmerge = 0, rowmerge = 0, parcol = 0, count = 0, maxmerge = 0, odd = 0;

public WritableFont getBlodFont() {
return blodFont;
}


public void setBlodFont(WritableFont blodFont) {
this.blodFont = blodFont;
}


public WritableFont getSmallBoldFont() {
return smallBoldFont;
}


public void setSmallBoldFont(WritableFont smallBoldFont) {
this.smallBoldFont = smallBoldFont;
}


public WritableFont getNormalBoldFont() {
return normalBoldFont;
}


public void setNormalBoldFont(WritableFont normalBoldFont) {
this.normalBoldFont = normalBoldFont;
}


public WritableFont getNormalFont() {
return normalFont;
}


public void setNormalFont(WritableFont normalFont) {
this.normalFont = normalFont;
}


public WritableFont getWarnningFont() {
return warnningFont;
}


public void setWarnningFont(WritableFont warnningFont) {
this.warnningFont = warnningFont;
}


public WritableCellFormat getCfTitle() {
return cfTitle;
}


public void setCfTitle(WritableCellFormat cfTitle) {
this.cfTitle = cfTitle;
}


public WritableCellFormat getCfSubTitle() {
return cfSubTitle;
}


public void setCfSubTitle(WritableCellFormat cfSubTitle) {
this.cfSubTitle = cfSubTitle;
}


public WritableCellFormat getCfNormalTitle() {
return cfNormalTitle;
}


public void setCfNormalTitle(WritableCellFormat cfNormalTitle) {
this.cfNormalTitle = cfNormalTitle;
}


public Map<String, WritableCellFormat> getCache() {
return cache;
}


public void setCache(Map<String, WritableCellFormat> cache) {
this.cache = cache;
}

}