编写jxl

HttpServletResponse response = this.getResponse();
OutputStream os = response.getOutputStream();// 取得输出流
response.reset();// 清空输出流
String fileName = URLEncoder.encode(assortcodeName , "UTF-8");//设置下载文件名
response.setHeader( "Content-Disposition", "attachment;filename=\""+ 
new String( fileName.getBytes( "gb2312" ), "ISO8859-1" )+ ".xls" + "\"");// 设定输出文件头
response.setContentType("application/msexcel");// 定义输出类型
WorkbookSettings wbSetting = new WorkbookSettings(); 
wbSetting.setUseTemporaryFileDuringWrite(true);   
        wbSetting.setTemporaryFileDuringWriteDirectory(new File("C:\\temp"));//临时文件夹的位置
WritableWorkbook wbook = Workbook.createWorkbook(os,wbSetting); // 建立excel文件
WritableSheet wsheet = wbook.createSheet(assortcodeName, 0); // sheet名称
wsheet.getSettings().setDefaultColumnWidth(18);
mappingIds=codecheckattributeManager.getObjectByAssortcodeId(assortcodeId).getMappingIds();
List<Long> mids = AOFUtil.getSplitLongList(mappingIds, ",");
List<Mappingdb> mappingList = mappingdbManager.getObjects(mids,
assortcodeId);
List<String> errorList = new ArrayList<String>();
List<Integer> errorindexList = new ArrayList<Integer>();
List<String> headsqlList = new ArrayList<String>();
// Lable（列，行，值）
int index = 0;
/*headsqlList.add("UUID");
wsheet.addCell(new Label(0, 1, "主数据唯一标识"));
wsheet.addCell(new Label(0, 1, "唯一标识码"));*/
for (Mappingdb base : mappingList) {
headsqlList.add(base.getFieldval());
try {
wsheet.addCell(new Label(index, 0, base.getDefineType()));//添加第一行
wsheet.addCell(new Label(index, 1, base.getName()));//添加第一行
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
index++;
}
/* 定义数据部分-start */
StringBuffer dataSql = genrateQueryCodecheckListSQL(assortcodeId, mappingList,
createdate, verName,jobid);
page.setPageSize(10000);
int totalCount = sckSqlNewManager.getPageTotalNum(dataSql);
page.setTotalCount(totalCount);
int pageTotal = page.getTotalPages();
Map<String, String> fontsMap = Shengpizi.getMoreFont();
// 封装数据部分
int headSize = headsqlList.size();
int rows = 2;
headsqlList.add("errorfields");//添加错误字段标识
String[] errorfield=null;
for (int pageNo = 1; pageNo <= pageTotal; pageNo++) {
page.setPageNo(pageNo);
SqlList sqlList = sckSqlNewManager.getMainCodeDataList(page,
assortcodeId, dataSql, headsqlList, "", initSortString());

for (Object[] object : sqlList.getDatas()) {
errorfield =object[2+headSize].toString().split("\\,");//拆分错误字段标识
errorindexList.clear();//清空错误字段数字索引
for(String ef:errorfield){
errorindexList.add(headsqlList.indexOf(ef));//根据错误字段名确定确定每条数据中错误字段的位置
}
for (int x = 2; x < headSize + 2; x++) {//headSize + 2限制最后一个错误字段标识不显示
try {
if(errorindexList.contains(x-2)){
WritableCellFormat cf = new WritableCellFormat(); 
cf.setBackground(Colour.ICE_BLUE);
wsheet.addCell(new Label(x - 2, rows, replaceSPZ(
removeNull(object[x]), fontsMap),cf));
}else{
wsheet.addCell(new Label(x - 2, rows, replaceSPZ(
removeNull(object[x]), fontsMap)));
}

 
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
rows++;
}
// 自动翻页


}


// 主体内容生成结束
wbook.write(); // 写入文件
try {
wbook.close();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
os.close(); // 关闭流