读取excel文件数据集，生成固定格式excel文件

对excel读写操作，关键依赖库：jxl.jar
写入的excel文件只能是.xls格式。xlsx格式，用wps打开另存为 Excel 97-2003文件（*.xls）

读：读取excel记录，获取每条记录的任意元素

代码实现

    /**
     * 读取excel的数据源
     * @param callBack 回调函数
     */
    public static void readExcel(CallBack callBack) {  
        System.out.print("============");
        int i;  
        Sheet sheet;  
        Workbook book;  
        Cell cell1,cell2,cell3,cell4,cell5,cell6,cell7,cell8,cell9;  
        try {   
            //hello.xls为要读取的excel文件名  
            book= Workbook.getWorkbook(new File("D://Javawork/Batch/input.xls"));//读取文件全路径 

            //获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)  
            sheet=book.getSheet(0);   
            //获取左上角的单元格  
            cell1=sheet.getCell(0,0);  
            System.out.println("标题："+cell1.getContents());   
            int count = sheet.getRows();
            System.out.println("count："+count);  //数据总条数 
            i=1;  //从1开始，过滤第一行
            while(i < count)  
            {  
                //获取每一行的单元格   
                cell1=sheet.getCell(0,i);//（列，行）  
                cell2=sheet.getCell(1,i);  
                cell3=sheet.getCell(2,i);  
                cell4=sheet.getCell(3,i);  
                cell5=sheet.getCell(4,i);  
                cell6=sheet.getCell(5,i);  
                cell7=sheet.getCell(6,i);  
                cell8=sheet.getCell(7,i);  
                cell9=sheet.getCell(8,i);  
                if("".equals(cell1.getContents())==true)break;      //如果读取的数据为空            
                System.out.println( cell1.getContents()+"\t"+cell2.getContents()+
                                "\t"cell3.getContents()+"\t"+cell4.getContents()+
                                "\t"+cell8.getContents()+"\t"+cell9.getContents()); 
                if(callBack != null){
                    System.out.println("================第"+i+"条数据开始====================");
                    callBack.result(cell1.getContents(),cell2.getContents(),cell3.getContents(),                                    cell4.getContents(),cell5.getContents(),cell6.getContents(),                                    cell7.getContents(),cell8.getContents(),cell9.getContents());
                }                
                i++;  
            }  
            book.close();   
        }  
        catch(Exception e)  {
            e.printStackTrace();
        }   
    }  

    //回调接口
    public interface CallBack{
        void result(String a1,String a2,String a3,String a4,String a5,String a6,
                            String a7,String a8,String a9);
    }

结果

写：把数据集以excel文件形式保存

代码实现

/**
     * 数据写入excel中，并生成固定格式excel
     * @param exportOrder 写入的数据源
     * @param fileName  保存文件名称
     * @throws Exception
     */
    public static void writeExcel(List<Order> exportOrder,
            String fileName) throws Exception {
        String[] title = { "编号", "上送报文","用户卡号","终端机号"};
        File file;
        File dir = new File("D:/Javawork/Batch");//保存文件的目录
        System.out.println("文件目录："+dir.toString());
        file = new File(dir, fileName + ".xls");
        if (!dir.exists()) {
            dir.mkdirs();
        }
        // 创建Excel工作表
        WritableWorkbook wwb;
        OutputStream os = new FileOutputStream(file);
        wwb = Workbook.createWorkbook(os);
        int count = exportOrder.size();
        int number = 0;
        //分页处理，否则报错
        if(count%65535 == 0){
            number = count/65535;
        }else {
            number = count/65535 + 1;
        }
        for(int j = 0 ; j < number; j ++){
            // 添加第一个工作表并设置第一个Sheet的名字
            WritableSheet sheet = wwb.createSheet("报文"+j, 0);
            Label label;
            for (int i = 0; i < title.length; i++) {
                // Label(x,y,z) 代表单元格的第x+1列，第y+1行, 内容z
                            // 在Label对象的子对象中指明单元格的位置和内容
                label = new Label(i, 0, title[i], getHeader());
                // 将定义好的单元格添加到工作表中
                sheet.addCell(label);
            }
            Order order;
            int account = 0;
            if((j+1) < number){
                account = 65535;
            }else {
                account = count - j*65535;
            }
            for (int i = 0; i < account; i++) {
                order = exportOrder.get(i);
                Label orderNum = new Label(0, i + 1, order.id + j*65535 + "");
                Label restaurant = new Label(1, i + 1, order.code);
                Label cardNo = new Label(2, i + 1, order.cardNo);
                Label termNo = new Label(3, i + 1, order.termNo);

                sheet.addCell(orderNum);
                sheet.addCell(restaurant);
                sheet.addCell(cardNo);
                sheet.addCell(termNo);
                System.out.println("第"+i+"个写入成功");
            }
        }
        // 写入数据
        wwb.write();
        // 关闭文件
        wwb.close();
        System.out.println("写入成功");
    }

    /**
     * excel显示风格
     * @return
     */
    public static WritableCellFormat getHeader() {
        WritableFont font = new WritableFont(WritableFont.TIMES, 10,
                WritableFont.BOLD);// 定义字体
        try {
            font.setColour(Colour.BLUE);// 蓝色字体
        } catch (WriteException e1) {
            e1.printStackTrace();
        }
        WritableCellFormat format = new WritableCellFormat(font);
        try {
            format.setAlignment(jxl.format.Alignment.CENTRE);// 左右居中
            format.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 上下居中
            // format.setBorder(Border.ALL, BorderLineStyle.THIN,
            // Colour.BLACK);// 黑色边框
            // format.setBackground(Colour.YELLOW);// 黄色背景
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return format;
    }

Order类

    public class Order implements Serializable {

        public int id;

        public String code;

        public String cardNo;//用户卡号

        public String termNo;//终端机号

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getCode() {
            return code;
        }

        public String getCardNo() {
            return cardNo;
        }

        public void setCardNo(String cardNo) {
            this.cardNo = cardNo;
        }

        public String getTermNo() {
            return termNo;
        }

        public void setTermNo(String termNo) {
            this.termNo = termNo;
        }

        public void setCode(String code) {
            this.code = code;
        }
        public Order(int id, String code) {
            this.id = id;
            this.code = code;
        }

        public Order(int id, String code, String cardNo, String termNo) {
            this.id = id;
            this.code = code;
            this.cardNo = cardNo;
            this.termNo = termNo;
        }
    }