jxl应用事例

实例中主要目的是解析jxl使用流程以及jxl绘制Excel的写法思路,代码掩去了项目中的真实数据,请根据需求酌情修改,如果有帮助到有需要的人,不胜欢喜。

Dao层为查询数据库,返回list数据,此处省略。

==============service

public void exportExcel(HttpServletResponse response){
try{
//列坐标
int columIndex[] = {0,1,2,3,4,5,6,};
//每一列宽度
int colwidth[] = {10,10,10,10,10,10};
//每一列字段key
String[] dataKey = {"test1","test2","test3","test4","test5","test6"};
//列表数据
List<Map<String,Object>> dataList = tableTestDao.findTableData();
//合计每一列的key
String[] countKey = {"test1","test2","test3","test4","test5","test6"}; //导出execl
tableExcelService.exportExcel( FILE_NAME, response, "测试表格", columIndex, colwidth, dataKey, dataList, countKey, "three" );
}catch (Exception e) {
throw new BusinessException(e, ExceptionEnum.NULL, e.getMessage());
}
}

=================TableExcelService工具类方法(1)

    /**
* 导出excel
* @param fileName文件名
* @param response
* @param title标题名
* @param columIndex列坐标
* @param colwidth每一列宽度
* @param dataKey每一列字段key
* @param dataList列表数据
* @throws Exception
*/
public void exportExcel(String fileName,HttpServletResponse response,String title,
int[] columIndex,int[] colwidth,String[] dataKey,List<Map<String,Object>> dataList,
String falg ) throws Exception{
OutputStream os=null;
WritableWorkbook workbook=null;
try{
os=response.getOutputStream();
fileName=URLEncoder.encode(fileName,"GB2312");
fileName=URLDecoder.decode(fileName, "ISO8859_1");
response.setHeader("Content-Disposition","attachment;filename="+fileName+".xls");
response.setContentType("application/msexcel");
workbook = Workbook.createWorkbook(os);
// 自定义的颜色
Color color = Color.decode("#CCFFCC");
workbook.setColourRGB(Colour.GREEN, color.getRed(),color.getGreen(), color.getBlue());
//创建excel第一个sheet页
WritableSheet sheet = workbook.createSheet("Sheet1", 0);
//创建标题和表头
if("three".equals( falg )||"four".equals( falg )){
sheet = createTableHead(sheet,title,columIndex,colwidth);
}else if("one".equals( falg )){
sheet = createTableHeadOne(sheet,title,columIndex,colwidth);
}else if("two".equals( falg )){
sheet = createTableHeadTwo(sheet,title,columIndex,colwidth);
}
//表格样式
WritableCellFormat tdWcfF = new WritableCellFormat();
tdWcfF.setBorder(Border.ALL, BorderLineStyle.THIN);
tdWcfF.setAlignment(Alignment.CENTRE);
tdWcfF.setVerticalAlignment(VerticalAlignment.CENTRE);
//填入列表数据
int startRow = 4;//从第五行开始填入值
Map<String,Object> dataMap = null;
Object obj=null;
for(int row = 0;row <dataList.size();row++){
dataMap = dataList.get( row );
for(int col=0;col<dataKey.length;col++){
obj = dataMap.get(dataKey[col]);
sheet.addCell(new Label(col,row+startRow,obj==null?"0":obj.toString(),tdWcfF));
}
}
//合计
if("three".equals( falg )||"four".equals( falg )){
int numStartRow = startRow+dataList.size();//到第几行了
sheet.mergeCells(0, numStartRow, 1, numStartRow);
sheet.addCell(new Label(0,numStartRow,"测试",tdWcfF));
for(int col=0;col<countKey.length;col++){
obj = countMap.get(countKey[col]);
sheet.addCell(new Label(col+2,numStartRow,obj==null?"0":obj.toString(),tdWcfF));
}
}else if("one".equals( falg )){
int numStartRow = startRow+dataList.size();//到第几行了
sheet.mergeCells(0, numStartRow, 0, numStartRow);
sheet.addCell(new Label(0,numStartRow,"测试",tdWcfF));
for(int col=0;col<countKey.length;col++){
obj = countMap.get(countKey[col]);
sheet.addCell(new Label(col+1,numStartRow,obj==null?"0":obj.toString(),tdWcfF));
}
}else if("two".equals( falg )){
int numStartRow = startRow+dataList.size();//到第几行了
sheet.mergeCells(0, numStartRow, 1, numStartRow);
sheet.addCell(new Label(0,numStartRow,"测试",tdWcfF));
for(int col=0;col<countKey.length;col++){
obj = countMap.get(countKey[col]);
sheet.addCell(new Label(col+2,numStartRow,obj==null?"0":obj.toString(),tdWcfF));
}
} workbook.write();
}catch (Exception e) {
throw new RuntimeException(e.getMessage(),e);
}finally{
if(workbook != null) {
workbook.close();
workbook=null;
}
if(os != null) {
os.flush();
os.close();
os=null;
}
}
}

===========TableExcelService工具类方法(2)

   /**
* 创建标题和表头
* @param sheet
* @param title
* @return
* @throws Exception
*/
private WritableSheet createTableHead(WritableSheet sheet,String title,
int[] columIndex,int[] colwidth) throws Exception{
/*****格式设置******/
//标题
WritableCellFormat titleWcfF = new WritableCellFormat();
//边线细线
titleWcfF.setBorder(Border.ALL, BorderLineStyle.THIN);
//水平居中
titleWcfF.setAlignment(Alignment.CENTRE);
//垂直居中
titleWcfF.setVerticalAlignment(VerticalAlignment.CENTRE);
//设置显示的字体样式,字体,字号,是否粗体,字体颜色
titleWcfF.setFont(new WritableFont(WritableFont.createFont("楷体_GB2312"),14,WritableFont.NO_BOLD,false,
UnderlineStyle.NO_UNDERLINE,Colour.BLACK));
//表头
WritableCellFormat thWcfF = new WritableCellFormat();
thWcfF.setBorder(Border.ALL, BorderLineStyle.THIN);
thWcfF.setAlignment(Alignment.CENTRE);
thWcfF.setVerticalAlignment(VerticalAlignment.CENTRE);
thWcfF.setBackground(jxl.format.Colour.GREEN);
//创建标题,列,行,到第19列,到第0行
sheet.mergeCells(0, 0, 19, 0);
sheet.addCell(new Label(0,0,title,titleWcfF));
//表头创建
sheet.mergeCells(0, 1, 1, 3);//名称
sheet.addCell(new Label(0,1,"测试1",thWcfF));
//业务数(单位:个)列,行,到第19列,到第1行
sheet.mergeCells(2, 1, 19, 1);
sheet.addCell(new Label(2,1,"测试2",thWcfF));
sheet.mergeCells(2, 2, 10, 2);
sheet.addCell(new Label(2,2,"测试3",thWcfF));
sheet.mergeCells(11, 2, 19, 2);
sheet.addCell(new Label(11,2,"测试4",thWcfF));
sheet.mergeCells(2, 3, 3, 3);
sheet.addCell(new Label(2, 3,"测试5",thWcfF));
sheet.mergeCells(4, 3, 5, 3);
sheet.addCell(new Label(4, 3,"测试6",thWcfF));
//单个单元格表头第6列,第3行,单元格内容,单元格样式
sheet.addCell(new Label(6, 3,"测试7",thWcfF));
sheet.addCell(new Label(7, 3,"测试8",thWcfF));
sheet.addCell(new Label(8, 3,"测试9",thWcfF));
sheet.addCell(new Label(9, 3,"测试10",thWcfF));
sheet.addCell(new Label(10, 3,"测试11",thWcfF));
sheet.mergeCells(11, 3, 12, 3); //设置列宽
for(int i=0;i<columIndex.length;i++){
sheet.setColumnView(columIndex[i], colwidth[i]);
}
return sheet;
}
上一篇:Re:从 0 开始的微服务架构--(四)如何保障微服务架构下的数据一致性--转


下一篇:Java虚拟机学习(2):垃圾收集算法