jxl生成类似月报表的excel文件

Question

用jxl生成类似月报表的excel文件，但做出来的效果很死，每个月固定31天，而且没有星期显示，各位能给点建议吗？代码如下

package com.rf.dwgj.util;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExpToExcel {//生成Excel文件
	
public static void expExcle(File file,List<Map<String,List<String>>> rows,String[] titles,String title) throws FileNotFoundException {
		
		OutputStream os= new FileOutputStream(file);
		WritableWorkbook workbook = null;
		
		try {
			// 创建新的Excel 工作簿
			workbook = Workbook.createWorkbook(os);
			// 在Excel工作簿中建一工作表，其名为:第一页
			jxl.write.WritableSheet wsheet = workbook.createSheet(title, 0); // sheet();
			WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 14,//大标题文字设置
					WritableFont.BOLD, false,
					jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
			titleFormat.setAlignment(Alignment.CENTRE);//设置文字居中
			wsheet.mergeCells(0,0, titles.length-1, 0);
			Label ybbTitle = new Label(0, 0, title, titleFormat);
			wsheet.addCell(ybbTitle);
			// 循环标头
			WritableFont font = new WritableFont(WritableFont.ARIAL, 11,//标题文字设置
					WritableFont.BOLD, false,
					jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			WritableCellFormat format = new WritableCellFormat(font);
			for (int i = 0; i < titles.length; i++) {
				Label wlabel1 = new Label(i, 1, titles[i], format); // 列、行单元格中的文本、文本格式
				wsheet.addCell(wlabel1);
				if(i==0||i==1){
					wsheet.setColumnView(i, 12); 
				}else{
					wsheet.setColumnView(i, 3); 
				}
				
			}
			font = new jxl.write.WritableFont(WritableFont.createFont("宋体"),
					12, WritableFont.NO_BOLD, false,
					jxl.format.UnderlineStyle.NO_UNDERLINE,
					jxl.format.Colour.BLACK);
			format = new jxl.write.WritableCellFormat(font);

			// 循环表内数据
			int flag = 0;//合并单元格位置标记
			for (int i = 1; i <= rows.size(); i++) {
				wsheet.mergeCells(0, i+1+flag, 0, i+2+flag);//合并单元格
				Map<String,List<String>> row = (Map<String,List<String>>) rows.get(i-1);
				System.out.println(row.size());
					Set<String> keys = row.keySet();
					Iterator<String> it = keys.iterator();
					List<String> infos = new ArrayList<String>();
					while(it.hasNext()){
						String info = (String)it.next();
						System.out.println("info"+info);
						infos.add(info);
						System.out.println("infos"+infos);
					}
					for(int j=2;j>=0;j--){
						Label label = null;
						if(j==2){//姓名 j表示是列数 i表示的是行数 都是从0开始
							String info = infos.get(j);
							System.out.println(info);
							label = new Label(0, i+1+flag , infos.get(2), format);
							System.out.println("label....."+label);
							wsheet.addCell(label);
						}
						if(j==0){
							List<String> sbkq = row.get("上班");
							for(int x=0;x<sbkq.size();x++){
								Label l1  = new Label(x+2,i+1+flag,sbkq.get(x),format);
								wsheet.addCell(l1);
							}
							label = new Label(1, i+1+flag , infos.get(0), format);
							wsheet.addCell(label);
						}
						if(j==1){
							List<String> xbkq = row.get("下班");
							label = new Label(1, i+2+flag , infos.get(1), format);
							for(int x=0;x<xbkq.size();x++){
								Label l1  = new Label(x+2,i+2+flag,xbkq.get(x),format);
								wsheet.addCell(l1);
							}
							wsheet.addCell(label);
						}
					}
					flag++;
				}
			workbook.write(); // 写入文件
			workbook.close();
		} catch (WriteException ex1) {
			System.out.println("WriteException:" + ex1.getMessage());
		} catch (IOException ex2) {
			System.out.println("IOException:" + ex2.getMessage());
		}
		
	}
	
	public static void main(String[] args) {
		File file = new File("D:\test1\test.xls");
		String[]titles = {"姓名","上下班","1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31"};
		//List<List<String>> rows = new ArrayList<List<String>>();
		List<Map<String,List<String>>> rows = new ArrayList<Map<String,List<String>>>();
		Map<String,List<String>> row = new TreeMap<String,List<String>>();
		Map<String,List<String>> row2 = new TreeMap<String,List<String>>();
		Map<String,List<String>> row3 = new TreeMap<String,List<String>>();
		List<String> info = new ArrayList<String>();
		
		for(int i=0;i<31;i++){
			info.add("√");
		}
		row.put("上班", info);
		row.put("下班", info);
		row.put("eee", null);
		
		
		row2.put("上班", info);
		row2.put("下班", info);
		row2.put("xxx", null);
		
		row3.put("上班", info);
		row3.put("下班", info);
		row3.put("qqq", null);
		rows.add(row);
		rows.add(row2);
		rows.add(row3);
		try {
			expExcleYbb(file, rows, titles, "test");
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

Answer 1

呃。代码要封装。比如说第一步是初始化excel，第二步是写入数据，第3步是关闭excel。

代码结构可以这样，WritableWorkbook workBook，WritableSheet sheet 作为类的成员变量。每一个步骤写成一个方法。然后组装起来就灵活了。