jxl将list导入到Excel中供下载详解编程语言

    /** 
* 分隔符 
*/ 
private final static String SEPARATOR = "|"; 
/** 
* 由List导出至指定的Sheet,带total行(最后一行) 
* @param wb   模板的workbook 
* @param sheetNum   第几个表单 
* @param targetFilePath 生成文件夹路径 
* @param l 内容list集合,以|分割的对象string集合 
* @param headInfoRows  头信息的行数 
* @param columnsLength  列数 
* @param remarkRowNumber 备注所在行 
* @param remark  备注 
* @return 
* @throws WriteException 
* @throws IOException 
* int 
*/ 
public static int exportExcelFromList(jxl.Workbook wb, int sheetNum, 
String targetFilePath, List<String> l, int headInfoRows, 
int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException { 
// 创建可写入的Excel工作薄对象 
WritableWorkbook wwb = null; 
int writeCount = 0; 
// 单元格样式 
// WritableFont bold = new 
// WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示 
WritableCellFormat normalFormat = new WritableCellFormat( 
NumberFormats.TEXT); 
normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN, 
jxl.format.Colour.BLACK); 
//设置字体;   
WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);   
WritableCellFormat normalFormat_total = new WritableCellFormat( 
font); 
normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN, 
jxl.format.Colour.BLACK); 
try { 
// 创建可写入的Excel工作薄对象 
wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb); 
WritableSheet ws = wwb.getSheet(0); 
Label cellRemark = new Label(0, remarkRowNumber, remark, 
normalFormat); 
ws.addCell(cellRemark); 
int row = l.size(); 
int columns = columnsLength; 
String[] ary = new String[120]; 
for (int i = 0; i < row; i++) { 
ary = l.get(i).split("//" + SEPARATOR); 
for (int j = 0; j < columns; j++) { 
if(i==row-1) 
{ 
Label cell = new Label(j, i + headInfoRows, ary[j], 
normalFormat_total); 
ws.addCell(cell); 
}else 
{ 
Label cell = new Label(j, i + headInfoRows, ary[j], 
normalFormat); 
ws.addCell(cell); 
} 
} 
writeCount++; 
} 
wwb.write(); 
} catch (Exception ex) { 
ex.printStackTrace(); 
} finally { 
if (wwb != null) { 
wwb.close(); 
} 
} 
return writeCount; 
} 
/** 
* 导出不需要合计行 
* @param wb 
* @param sheetNum 
* @param targetFilePath 
* @param l 
* @param headInfoRows 
* @param columnsLength 
* @param remarkRowNumber 
* @param remark 
* @return 
* @throws WriteException 
* @throws IOException 
*/ 
public static int exportExcelFromListNoTotal(jxl.Workbook wb, int sheetNum, 
String targetFilePath, List<String> l, int headInfoRows, 
int columnsLength,int remarkRowNumber,String remark) throws WriteException, IOException { 
// 创建可写入的Excel工作薄对象 
WritableWorkbook wwb = null; 
int writeCount = 0; 
// 单元格样式 
// WritableFont bold = new 
// WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示 
WritableCellFormat normalFormat = new WritableCellFormat( 
NumberFormats.TEXT); 
normalFormat.setBorder(Border.ALL, BorderLineStyle.THIN, 
jxl.format.Colour.BLACK); 
//设置字体;   
WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   
WritableCellFormat normalFormat_total = new WritableCellFormat( 
font); 
normalFormat_total.setBorder(Border.ALL, BorderLineStyle.THIN, 
jxl.format.Colour.BLACK); 
try { 
// 创建可写入的Excel工作薄对象 
wwb = jxl.Workbook.createWorkbook(new File(targetFilePath), wb); 
WritableSheet ws = wwb.getSheet(0); 
Label cellRemark = new Label(0, remarkRowNumber, remark, 
normalFormat); 
ws.addCell(cellRemark); 
int row = l.size(); 
int columns = columnsLength; 
String[] ary = new String[120]; 
for (int i = 0; i < row; i++) { 
ary = l.get(i).split("//" + SEPARATOR); 
for (int j = 0; j < columns; j++) { 
Label cell = new Label(j, i + headInfoRows, ary[j], 
normalFormat); 
ws.addCell(cell); 
} 
writeCount++; 
} 
wwb.write(); 
} catch (Exception ex) { 
ex.printStackTrace(); 
} finally { 
if (wwb != null) { 
wwb.close(); 
} 
} 
return writeCount; 
}

        List<DetectorHistory> dfList = service.getList();   //获取对象集合 
List<String> exportList = new ArrayList<String>(); 
StringBuffer sbList = new StringBuffer(); 
if (dfList!=null&&dfList.size()>0) { 
for (DetectorHistory ele:dfList) { 
sbList.delete(0, sbList.length());
　　　　　　　　　　//加入|
                 //加入属性
                 ... 
exportList.add(sbList.toString()); 
} 
}

        //模板所在文件夹路径 
String tempPath = req.getSession().getServletContext() 
.getRealPath(CommonValue.FileTemplatePath); 
//生成文件所在文件夹路径 
String exportFilePath = req.getSession().getServletContext() 
.getRealPath(CommonValue.ExportFilePath); 
//导出文件名 
String exportFileName = ""; 
//模板文件名,事先生成好以及头文件情况 
String targetFileName = "ReportTmp_detectorHistory.xls"; 
//生成的行数 
int operatorCount = 0;  
JSONObject jsonObject = new JSONObject(); 
try { 
// 文件导出 
if (exportList.size() > 0) { 
//生成文件名 
exportFileName = "detectorHistory" + CommonTool.getNowDateStr2() 
+ "." + targetFileName.split("//.")[1]; 
//利用模板生成Workbook 
Workbook rw = jxl.Workbook.getWorkbook(new File(tempPath 
+ File.separator + targetFileName)); 
// 写入备注文件 
String remarkInfo = "统计时间:" + CommonTool.getNowDateStr2() + "    金额单位:元"; 
//rw为模板workbook，0为sheetnum，其次为导出文件路径，exportList为|分割属性的string对象集合,4为头的行数,14为列数,1为备注所在行(从0开始),remarkinfo为备注所在行的信息 
operatorCount = ExcelHelper_ChargeSituation.exportExcelFromListNoTotal(rw, 
0, exportFilePath + File.separator + exportFileName, 
exportList, 4, 14, 1, remarkInfo); 
} 
jsonObject.put("operatorCount", operatorCount);  //返回操作条数 
jsonObject.put("exportFilePath", CommonValue.ExportFilePath 
+ File.separator + exportFileName);  //返回生成的文件路径 
if(jsonObject.get("operatorCount")!=null&&Integer.valueOf(jsonObject.get("operatorCount").toString())<=0) 
{//当生成内容条数为0时 
jsonObject.put("rtnCode", "404"); 
}else 
{ 
jsonObject.put("rtnCode", "0"); 
} 
} catch (BiffException e) { 
e.printStackTrace(); 
} catch (WriteException e) { 
e.printStackTrace(); 
}finally{ 
} 
res.resetBuffer(); 
res.setContentType("text/html;charset=UTF-8"); 
res.getOutputStream().write(jsonObject.toString().getBytes("utf-8")); 
res.getOutputStream().flush(); 
return null;