Java使用jxl包进行Excel表格的操作

使用jxl包进行的Excel文件操作

创建一个工作簿Workbook

WritableWorkbook workbook = Workbook.createWorkbook(new File("F:\\学生信息表.xls"));

读取工作簿

Workbook.getWorkbook(java.io.File file);

创建工作表

WritableSheet sheet = workbook.createSheet("学生信息表",0);

创建标签并加入到工作表

sheet.addCell(new Label(col,row,"title"));

将工作写入文件

// 写入数据 
workbook.write(); 
// 关闭文件 
workbook.close(); 

行列的批量操作

//获取所有的工作表 
jxl.write.WritableSheet[] sheetList = workbook.getSheets(); 
//获取第1列所有的单元格 
jxl.Cell[] cellc = sheet.getColumn(0); 
//获取第1行所有的单元格 
jxl.Cell[] cellr = sheet.getRow(0); 
//获取第1行第1列的单元格 
Cell c = sheet.getCell(0, 0);

获取单元格的值

//获取单元格的值，不管什么单元格，返回都是字符串 
    String value = c.getContents();

填充数据
填充数据这块稍微麻烦点，涉及到数据单元格的格式问题

//填充数字
        jxl.write.Number numb = new jxl.write.Number(1, 1, 250); 
        sheet.addCell(numb);
 
//填充格式化的数字
        jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#.##"); 
        jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(nf); 
        jxl.write.Number n = new jxl.write.Number(2, 1, 2.451, wcf); 
        sheet.addCell(n);
 
//填充日期
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); 
        String newdate = sdf.format(new Date()); 
        label = new Label(2, 2, newdate); 
        sheet.addCell(label);
 
//填充文本
        label = new Label(3, 3, "周星驰"); 
        sheet.addCell(label);
 
//填充boolean值
        jxl.write.Boolean bool = new jxl.write.Boolean(4, 1, true); 
        sheet.addCell(bool);

合并单元格
通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的。
表示将从第x+1列，y+1行到m+1列，n+1行合并 (四个点定义了两个坐标，左上角和右下角)
结果是合并了m-x+1行，n-y+1列，两者乘积就是合并的单元格数量

sheet.mergeCells(0, 6, 3, 8); 
label = new Label(0, 6, "合并了12个单元格"); 
sheet.addCell(label);

改变单元格样式

   WritableCellFormat wc = new WritableCellFormat(); 
   // 设置居中 
   wc.setAlignment(Alignment.CENTRE); 
   // 设置边框线 
   wc.setBorder(Border.ALL, BorderLineStyle.THIN); 
   // 设置单元格的背景颜色 
   wc.setBackground(jxl.format.Colour.RED); 
   label = new Label(1, 5, "字体", wc); 
   sheet.addCell(label);

设置单元格字体

   // 设置字体 
   jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("楷书"), 20); 
   WritableCellFormat font = new WritableCellFormat(wfont); 
   label = new Label(2, 6, "楷书", font); 
   sheet.addCell(label);

练习：向表中添加100名学生，并且数据是随机的

public class Test01 {
    public static void main(String[] args) {
        try {
            WritableWorkbook workbook = Workbook.createWorkbook(new File("F:\\学生信息表.xls"));
            WritableSheet sheet = workbook.createSheet("学生信息表",0);
            String title[] = {"学生编号","学生姓名","性别","学生年龄","学生出生日期","表录入时间"};
            //将表头属性录入
            for (int i = 0; i < 6; i++) {
                sheet.addCell(new Label(i,0,title[i]));
            }
            Random r = new Random();
            SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
            for (int i = 1; i < 100; i++) {
                Calendar c = Calendar.getInstance();
                //获得年龄
                int age = r.nextInt(20)+10;
                //获得年龄所对应的年份
                int year = c.get(Calendar.YEAR) - age;
                //设置到对应年份
                c.set(Calendar.YEAR,year);
                //获得随机月份
                int month = r.nextInt(12) + 1;
                //设置到月份
                c.set(Calendar.MONTH,month-1);
                //获得当前月的最大天数，并在天数中进行随机取值
                int  day = r.nextInt(c.get(Calendar.DAY_OF_MONTH));
                //设置到年龄所对应的时间日期
                c.set(year,month,day);
                sheet.addCell(new Label(0,i,i+""));
                sheet.addCell(new Label(1,i,"李某"+i));
                sheet.addCell(new Label(2,i,r.nextInt(2)==1?"男":"女"));
                sheet.addCell(new Label(3,i,String.valueOf(age)));
                sheet.addCell(new Label(4,i,sd.format(c.getTime())));
                Calendar c1 = Calendar.getInstance();
                sheet.addCell(new Label(5,i,sd.format(c1.getTime())));
            }
            workbook.write();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }
}