jxl，java操作Excel的运用：小型的课时统计系统

来源：互联网发布：旱雪知乎编辑：程序博客网时间：2024/06/05 13:25

问题描述：

  利用文件IO流 写一个课时统计程序 每天输入当天课时 最后统计出一共上了多少课时   每天的日期及上课班级  还有课时总计 都要记录下来 可以生成一个excl表格文件   注意程序的健壮性，如输入的课时非法 要有相应提示处理

首先是要求操作excel文件，所以就到导入一个jxl包：

直接下载地址（迅雷上新建任务即可）：
http://nchc.dl.sourceforge.net/project/jexcelapi/jexcelapi/2.6.6/jexcelapi_2_6_6.zip
解压后将里面的jxl.jar导入：
右键工程，选最下面一个，然后java build path->add external jars->选择刚刚解压包里的jxl.jar
这里写图片描述

选上导入的jxl.jar包，还有上一篇写的jfree实现折线图的两个包，都会用上
这里写图片描述

包导入成功以后，就了解一个基本使用：

import java.io.IOException;import java.io.OutputStream;import java.util.Calendar;import java.util.Date;import jxl.Workbook;import jxl.write.Boolean;import jxl.write.DateFormats;import jxl.write.DateTime;import jxl.write.Label;import jxl.write.Number;import jxl.write.WritableCellFormat;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;public class ComplexDataExcelWrite {    public void createExcel(OutputStream os) throws WriteException,IOException {        //创建工作薄        WritableWorkbook workbook = Workbook.createWorkbook(os);        //创建新的一页        WritableSheet sheet = workbook.createSheet("First Sheet", 0);        //创建要显示的具体内容        Label formate = new Label(0,0,"数据格式");        sheet.addCell(formate);        Label floats = new Label(1,0,"浮点型");        sheet.addCell(floats);        Label integers = new Label(2,0,"整型");        sheet.addCell(integers);        Label booleans = new Label(3,0,"布尔型");        sheet.addCell(booleans);        Label dates = new Label(4,0,"日期格式");        sheet.addCell(dates);        Label example = new Label(0,1,"数据示例");        sheet.addCell(example);        //浮点数据        Number number = new Number(1,1,3.1415926535);        sheet.addCell(number);        //整形数据        Number ints = new Number(2,1,15042699);        sheet.addCell(ints);        Boolean bools = new Boolean(3,1,true);        sheet.addCell(bools);        //日期型数据        Calendar c = Calendar.getInstance();        Date date = c.getTime();        WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);        DateTime dt = new DateTime(4,1,date,cf1);        sheet.addCell(dt);        //把创建的内容写入到输出流中，并关闭输出流        workbook.write();        workbook.close();        os.close();    }}

这里写图片描述

因为时间的关系，没有每天输入，我要将之前的数据到excel文件里，就用到基本的使用（创建的Excel在D:/class.xls里）：

package com.practice.class_hour;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Calendar;import java.util.Date;import jxl.Workbook;import jxl.write.DateFormats;import jxl.write.DateTime;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;public class XlsTest {    public static void createExcel2(OutputStream os) throws WriteException,IOException, ParseException {        //创建工作薄        WritableWorkbook workbook = Workbook.createWorkbook(os);        //创建新的一页        WritableSheet sheet = workbook.createSheet("First Sheet", 0);        //创建要显示的具体内容        Label formate = new Label(0,0,"时间");        sheet.addCell(formate);        Label floats = new Label(1,0,"课时数");        sheet.addCell(floats);        Label integers = new Label(2,0,"班级");        sheet.addCell(integers);        Label booleans = new Label(3,0,"课时总数");        sheet.addCell(booleans);        Label startDate = new Label(4, 0, "初始时间");        sheet.addCell(startDate);        Label label = new Label(5, 0, "显示周期");        sheet.addCell(label);        jxl.write.Number number = new jxl.write.Number(1,1,5);        sheet.addCell(number);//        Calendar c=Calendar.getInstance();//        Date date1=sdf.parse("2016/10/26");//        c.setTime(date1);//        Date date=c.getTime();//        WritableCellFormat cf1=new WritableCellFormat(DateFormats.FORMAT1);//        DateTime  dt=new DateTime(0,1,date,cf1);//        sheet.addCell(dt);        Label a1 = new Label(0,1,"2016.10.29");        sheet.addCell(a1);        Label b1 = new Label(2,1,"1612");        sheet.addCell(b1);        jxl.write.Number number2 = new jxl.write.Number(1,2,7);        sheet.addCell(number2);        Label a2 = new Label(0,2,"2016.10.31");        sheet.addCell(a2);        Label b2 = new Label(2,2,"1614");        sheet.addCell(b2);        jxl.write.Number number3 = new jxl.write.Number(1,3,10);        sheet.addCell(number3);        Label a3 = new Label(0,3,"2016.11.02");        sheet.addCell(a3);        Label b3 = new Label(2,3,"1614");        sheet.addCell(b3);        jxl.write.Number number4 = new jxl.write.Number(1,4,15);        sheet.addCell(number4);        Label a4 = new Label(0,4,"2016.11.03");        sheet.addCell(a4);        Label b4 = new Label(2,4,"1612");        sheet.addCell(b4);        jxl.write.Number number5 = new jxl.write.Number(1,5,8);        sheet.addCell(number5);        Label a5 = new Label(0,5,"2016.11.10");        sheet.addCell(a5);        Label b5 = new Label(2,5,"1610");        sheet.addCell(b5);        jxl.write.Number num = new jxl.write.Number(3,                5, 45);        sheet.addCell(num);        Label label2 = new Label(4, 5, "2016.10.29");        sheet.addCell(label2);        jxl.write.Number num2 = new jxl.write.Number(5,                5, 2);        sheet.addCell(num2);//        Calendar c = Calendar.getInstance();//        Date date = c.getTime();//        WritableCellFormat cf1 = new WritableCellFormat(DateFormats.FORMAT1);//        DateTime dt = new DateTime(4,1,date,cf1);//        sheet.addCell(dt);        //把创建的内容写入到输出流中，并关闭输出流        workbook.write();        workbook.close();        os.close();    }    public static void main(String args[]){        try {            FileOutputStream os=new FileOutputStream(new File("D:/class.xls"));            //createExcel(os);            createExcel2(os);        } catch (WriteException | IOException | ParseException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }}

然后就生成了如下的表：
这里写图片描述

利用这个已用的表可以看到想要的效果：
主代码：

package com.practice.class_hour;import java.awt.Color;import java.awt.Font;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import java.util.Scanner;import org.jfree.chart.ChartFactory;import org.jfree.chart.ChartFrame;import org.jfree.chart.JFreeChart;import org.jfree.chart.axis.AxisSpace;import org.jfree.chart.labels.ItemLabelAnchor;import org.jfree.chart.labels.ItemLabelPosition;import org.jfree.chart.labels.StandardXYItemLabelGenerator;import org.jfree.chart.plot.XYPlot;import org.jfree.chart.renderer.xy.XYItemRenderer;import org.jfree.chart.renderer.xy.XYLineAndShapeRenderer;import org.jfree.chart.title.TextTitle;import org.jfree.data.time.TimeSeries;import org.jfree.data.time.TimeSeriesCollection;import org.jfree.data.time.Year;import org.jfree.ui.RectangleInsets;import org.jfree.ui.TextAnchor;import org.omg.Messaging.SyncScopeHelper;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;import jxl.write.Label;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;public class MainMethod {    static List<Long> list = new ArrayList<>();// 用来存入每天时间的毫秒数.    static List<Integer> gettime = new ArrayList<>();// 存入每天写讲的课时量    static String str = "2016.10.26";// 设置初始时间为2016/16/26    static SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");// 日期表达格式    static Long time;    static int days = 2;// 折线图显示的周期数    static int num = 0;// 从初始时间到目前所过的天数    public static void sum() {// 将时间统一放入list表里，方便之后使用        try {            time = sdf.parse(str).getTime();        } catch (Exception e) {            // TODO: handle exception        }        list.add(time);// 将初始时间作为第一个时间存入list中        for (; time < System.currentTimeMillis(); num++) {// 将每天的毫秒数存入list，并将gettime赋初值            time += 24 * 60 * 60 * 1000 * days;            list.add(time);            gettime.add(0);        }    }    // 创建一个Excel文件    public static void createExcel(WritableSheet sheet, List<ClassHour> slist)            throws WriteException, IOException {        // 创建要显示的具体内容        int i = 0;        for (; i < slist.size(); i++) {// 将slist的内容导入            jxl.write.Number number = new jxl.write.Number(1, i + 1,                    slist.get(i).getNum());            sheet.addCell(number);            Label label = new Label(2, i + 1, slist.get(i).getClassName());            sheet.addCell(label);            Label date = new Label(0, i + 1, slist.get(i).getDate());            sheet.addCell(date);        }    }    // 将原有Excel文件里的内容放入到slist中去    public static List<ClassHour> copyFile(File file, List<ClassHour> slist)            throws BiffException, IOException {        Workbook book = Workbook.getWorkbook(file);        // 获得第一个工作表对象        Sheet sheet = book.getSheet(0);        // 得到第一列第一行的单元格        int rownum = sheet.getRows();// 得到行数        for (int i = 1; i < rownum; i++)// 循环进行读写        {            ClassHour ch = new ClassHour();            Cell cell1 = sheet.getCell(0, i);            ch.setDate(cell1.getContents());            cell1 = sheet.getCell(1, i);            ch.setNum(Integer.parseInt(cell1.getContents()));            cell1 = sheet.getCell(2, i);            ch.setClassName(cell1.getContents());            slist.add(ch);        }        book.close();        return slist;    }    // 对Excel文件就行读取，将每天对应课时数放入gettime中    public static void check(File file)            throws BiffException, IOException, ParseException {        // 得到file（Excel）的工作簿        Workbook book = Workbook.getWorkbook(file);        // 获得第一个工作表对象        Sheet sheet = book.getSheet(0);        int rownum = sheet.getRows();// 得到行数        for (int i = 1; i < rownum; i++) {            Cell cell1 = sheet.getCell(0, i);            String result = cell1.getContents();// 取出日期            Cell cell2 = sheet.getCell(1, i);            String number = cell2.getContents();// 取出课时数            Long ltime = sdf.parse(result).getTime();            {                for (int j = 0; j < num; j++) {                    if (ltime < list.get(j + 1) && ltime >= list.get(j)) {                        gettime.set(j,                                gettime.get(j) + Integer.parseInt(number));// 将课时数放入对应的天里                        break;                    }                }            }        }        book.close();    }    // 得到Excel文件里总的课时数    public static int getNum(File file) throws BiffException, IOException {        Workbook book = Workbook.getWorkbook(file);        // 获得第一个工作表对象        Sheet sheet = book.getSheet(0);        int rownum = sheet.getRows();// 得到行数        int sum = 0;        for (int i = 1; i < rownum; i++)// 循环进行读写        {            Cell cell1 = sheet.getCell(1, i);            String result = cell1.getContents();            sum += Integer.parseInt(result);        }        book.close();        return sum;    }    // 得到初始时间str和周期days    public static void getDateDays(File file)            throws BiffException, IOException {        Workbook book = Workbook.getWorkbook(file);        // 获得第一个工作表对象        Sheet sheet = book.getSheet(0);        int rownum = sheet.getRows();// 得到行数        Cell cell1 = sheet.getCell(4, rownum-1);        str = cell1.getContents();        cell1 = sheet.getCell(5, rownum-1);        days = Integer.parseInt(cell1.getContents());        book.close();    }    // 得到Excel文件里总的行数    public static int getRowNum(File file) throws BiffException, IOException {        Workbook book = Workbook.getWorkbook(file);        // 获得第一个工作表对象        Sheet sheet = book.getSheet(0);        int rownum = sheet.getRows();// 得到行数        return rownum;    }    // 画折线图    public static void draw() {        // 首先构造数据        @SuppressWarnings("deprecation")        TimeSeries timeSeries = new TimeSeries("课时量", Year.class);        // 时间曲线数据集合        TimeSeriesCollection lineDataset = new TimeSeriesCollection();        // 构造数据集合        for (int i = 0; i < num; i += 1) {            timeSeries.add(new Year(i + 1), gettime.get(i));        }        lineDataset.addSeries(timeSeries);        JFreeChart chart = ChartFactory.createTimeSeriesChart("",                "date" + "(Every " + days + " days)", "num", lineDataset, true,                true, true);        // 增加标题        chart.setTitle(new TextTitle("每" + days + "天讲课课时变化量",                new Font("隶书", Font.ITALIC, 20)));        chart.setAntiAlias(true);        XYPlot plot = (XYPlot) chart.getPlot();        plot.setAxisOffset(new RectangleInsets(10, 10, 10, 10));// 图片区与坐标轴的距离        plot.setOutlinePaint(Color.PINK);        plot.setInsets(new RectangleInsets(15, 15, 15, 15));// 坐标轴与最外延的距离        // plot.setOrientation(PlotOrientation.HORIZONTAL);//图形的方向，包括坐标轴。        AxisSpace as = new AxisSpace();        as.setLeft(25);        as.setRight(25);        plot.setFixedRangeAxisSpace(as);        chart.setPadding(new RectangleInsets(5, 5, 5, 5));        chart.setNotify(true);        // 设置曲线是否显示数据点        XYLineAndShapeRenderer xylineandshaperenderer = (XYLineAndShapeRenderer) plot                .getRenderer();        xylineandshaperenderer.setBaseShapesVisible(true);        // 设置曲线显示各数据点的值        XYItemRenderer xyitem = plot.getRenderer();        xyitem.setBaseItemLabelsVisible(true);        xyitem.setBasePositiveItemLabelPosition(new ItemLabelPosition(                ItemLabelAnchor.INSIDE10, TextAnchor.BASELINE_LEFT));        xyitem.setBaseItemLabelGenerator(new StandardXYItemLabelGenerator());        xyitem.setBaseItemLabelFont(new Font("Dialog", 1, 14));        plot.setRenderer(xyitem);        // 显示        ChartFrame frame = new ChartFrame("java", chart);        frame.pack();        frame.setVisible(true);    }    // // 修改设置（周期的选择，显示初始时间的选择）    // public static void change() {    // try (Scanner cin = new Scanner(System.in)) {    // System.out.println("修改周期请输入‘1’，修改初始计算时间请输入‘2’：");    // int a = cin.nextInt();    // if (a == 1) {    // System.out.println("请输入需要修改的周期（单位：天）：");    // days = cin.nextInt();    // } else {    // System.out.println("请输入显示的初始时间（格式：2016.01.01）：");    // str = cin.next();    // }    // }    // }    // 主要内容，数据的读取和处理    public static void makeExcel(File file)            throws BiffException, IOException, WriteException {        Scanner cin = new Scanner(System.in);        ClassHour ch = new ClassHour();        int rowNum = 2;        int sum = 0;        List<ClassHour> slist = new ArrayList<>();        if (file.exists()) {// 如果Excel文件不为空，就将其内容放入slist中            rowNum = getRowNum(file);            sum = getNum(file);            slist = copyFile(file, slist);        }        int flag = 1;// 退出输入标志变量        do {            loop : {                int num = 0;                System.out.println("请输入今天已讲课时数和班级名称（换行输出），输入-1时进入设置，输入0时退出：");                try {                    num = cin.nextInt();                    flag = num;                    if (flag == 0)                        break;                    if (flag == -1) {                        System.out.println("修改周期请输入‘1’，修改初始计算时间请输入‘2’：");                        int a = cin.nextInt();                        if (a == 1) {                            System.out.println("请输入需要修改的周期（单位：天）：");                            days = cin.nextInt();                        } else {                            System.out.println("请输入显示的初始时间（格式：2016.01.01）：");                            str = cin.next();                        }                        break loop;                    }                    String className = cin.next();                    if (num < 0 && num != -1) {                        throw new Exception();                    } else if (num > 12) {                        throw new Exception();                    }                    ch.setNum(num);                    ch.setClassName(className);                    ch.setDate(sdf.format(new Date()));                    slist.add(ch);                    ch = new ClassHour();                    sum += num;                } catch (Exception e) {// 输入异常时就行处理                    System.out.println("请输入正确的课时数数据！");                    String st = cin.nextLine();// 将异常数据吃掉                }            }        } while (flag != 0);        try {            file.createNewFile();            FileOutputStream os = new FileOutputStream(file);            // 创建工作薄            WritableWorkbook workbook = Workbook.createWorkbook(os);            // 创建新的一页            WritableSheet sheet = workbook.createSheet("First Sheet", 0);            Label label = new Label(0, 0, "时间");            sheet.addCell(label);            Label label2 = new Label(1, 0, "课时数");            sheet.addCell(label2);            Label label3 = new Label(2, 0, "班级");            sheet.addCell(label3);            Label label4 = new Label(3, 0, "课时总数");            sheet.addCell(label4);// 以上设置第一行文字            Label label5 = new Label(4, 0, "初始时间");            sheet.addCell(label5);            Label label6 = new Label(5, 0, "显示周期");            sheet.addCell(label6);            jxl.write.Number number = new jxl.write.Number(3,                    slist.size(), sum);            sheet.addCell(number);            Label label7 = new Label(4, slist.size(), str);            sheet.addCell(label7);            jxl.write.Number number2 = new jxl.write.Number(5,                    slist.size(), days);            sheet.addCell(number2);            createExcel(sheet, slist);            workbook.write();            workbook.close();            os.close();        } catch (WriteException | IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        cin.close();    }    // 输出方法    public static void outPut(File file)            throws BiffException, IOException, ParseException, WriteException {        if (file.exists())            getDateDays(file);        makeExcel(file);        sum();        check(file);        System.out.println("每" + days + "天课时情况：");        for (int i = 0; i < num; i++) {            String stime = sdf.format(new Date(list.get(i)));            String etime = sdf.format(new Date(list.get(i + 1) - 1));            System.out.println(" 第" + (i + 1) + "周期：" + stime + "到" + etime                    + "讲了  " + gettime.get(i) + "个课时     ");        }        draw();    }    public static void main(String[] args)            throws BiffException, WriteException, IOException, ParseException {        File file = new File("D:/class.xls");        outPut(file);    }    // 之前的处理方法    // public static void main(String[] args)    // throws BiffException, IOException, WriteException, ParseException {    // Scanner cin = new Scanner(System.in);    // ClassHour ch = new ClassHour();    // List<ClassHour> list = new ArrayList<>();    // int flag = 1;    // do {    // System.out.println("请输入今天已讲课时数和班级名称（中间用空格隔开），输入0时退出：");    // try {    // int num = cin.nextInt();    // flag = num;    // if (flag == 0)    // break;    // String className = cin.next();    // ch.setNum(num);    // ch.setClassName(className);    // list.add(ch);    // ch = new ClassHour();    // } catch (Exception e) {    // System.out.println("请输入正确的数据！");    // String st = cin.next();    // }    // } while (flag != 0);    // File file = new File("D:/class.xls");    // File file1 = new File("D:/class1.xls");    // if (!file.exists() && !file1.exists()) {    // try {    // file.createNewFile();    // FileOutputStream os = new FileOutputStream(file);    // // 创建工作薄    // WritableWorkbook workbook = Workbook.createWorkbook(os);    // // 创建新的一页    // WritableSheet sheet = workbook.createSheet("First Sheet", 0);    // Label label = new Label(0, 0, "时间");    // sheet.addCell(label);    // Label label2 = new Label(1, 0, "课时数");    // sheet.addCell(label2);    // Label label3 = new Label(2, 0, "班级");    // sheet.addCell(label3);    // Label label4 = new Label(3, 0, "课时总数");    // sheet.addCell(label4);    // createExcel(sheet, list, 1);    // workbook.write();    // workbook.close();    // os.close();    // outPut(file);    // draw();    // // System.out.println(getNum(file));    // } catch (WriteException | IOException e) {    // // TODO Auto-generated catch block    // e.printStackTrace();    // }    // } else {    // if (!file.exists()) {    // File file2 = file;    // file = file1;    // file1 = file2;    // }    // // System.out.println(file+" "+file1);    // InputStream instream = new FileInputStream(file);    // Workbook readwb = Workbook.getWorkbook(instream);    // Sheet sheet = readwb.getSheet(0);    // // 得到第一列第一行的单元格    // int columnum = sheet.getRows();// 得到列数    // jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(file1,    // readwb);    // // 读取第一张工作表    // jxl.write.WritableSheet ws = wwb.getSheet(0);    // createExcel(ws, list, columnum);    //    // // jxl.write.WritableCell wc = ws.getWritableCell(3, 0);    // // if (wc.getType() == CellType.EMPTY) {    // // wc=new Label(3,0, "20");    // // ws.addCell(wc);    // // }    //    // instream.close();    // wwb.write();    // wwb.close();    // file.delete();    // cin.close();    // // System.out.println(getNum(file1));    // outPut(file1);    // draw();    // }    // }}

ClassHour类：

public class ClassHour {    private int num=0;    private String className;    private String date;    @Override    public String toString() {        return num+" "+ className+" "+ date;    }    public ClassHour() {    }    public ClassHour(int num, String className, String date) {        super();        this.num = num;        this.className = className;        this.date = date;    }    public int getNum() {        return num;    }    public void setNum(int num) {        this.num = num;    }    public String getClassName() {        return className;    }    public void setClassName(String className) {        this.className = className;    }    public String getDate() {        return date;    }    public void setDate(String date) {        this.date = date;    }}

最后实现每天每次输入的数据都可以记录下来，，并且绘制折线图看到每天讲课数量的变化量（修改初始日期，和sum方法里的time+=后面的数，可以改变初始日期和折现图的周期）

效果图：
这里写图片描述

2016/11/14输入的数据，然后Excel文件的内容：
这里写图片描述

每天纪录下来就会得到更好的效果。

0 0