Jxl操作Excel定义格式

定义格式工具类

package net.wwwyibu.orm;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
/**
 * 定义Excel格式
 * @author 马家立
 * @version 2018年9月27日
 */
public class ExceltitlefFormat {
    private WritableCellFormat titlefFormat;    //设置字体 宋体10
    private WritableCellFormat titlefFormat1;    //设置字体 宋体加粗18
    private WritableCellFormat titlefFormat2;    //设置字体 宋体加粗13
    private WritableCellFormat titlefFormat3;    //设置字体 宋体加粗10,标记待扣分,蓝色
    private WritableCellFormat titlefFormat4;    //设置字体 宋体加粗10,标记已扣分,红色
    private WritableCellFormat titlefFormat5;    //设置字体 宋体加粗10,标记免扣分,黑色
    private WritableCellFormat titlefFormat6;    //设置字体 宋体加粗10,绿色
    public WritableCellFormat getTitlefFormat(){
        try {
            jxl.write.WritableFont titleFont = new jxl.write.WritableFont(
                    WritableFont.createFont("宋体"), 10);
            titlefFormat = new WritableCellFormat(titleFont);
            titlefFormat.setAlignment(jxl.format.Alignment.CENTRE);//设置居中
            titlefFormat.setBorder(Border.ALL, BorderLineStyle.THIN);//设置边框
            /*//设置自动换行
            WritableCellFormat writableCellFormat = new WritableCellFormat(titleFont);
            writableCellFormat.setWrap(true);*/
            titlefFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
            titlefFormat.setWrap(true);
            //titlefFormat.setBackground(Colour.BLUE_GREY);  //设置背景颜色
        } catch (Exception e) {
            e.printStackTrace();
        }
        return titlefFormat;
    }
    public WritableCellFormat getTitlefFormat1() {
        try {
            jxl.write.WritableFont titleFont1 = new jxl.write.WritableFont(
                    WritableFont.createFont("宋体"), 18, WritableFont.BOLD);
            titlefFormat1 = new WritableCellFormat(titleFont1);
            titlefFormat1.setAlignment(jxl.format.Alignment.CENTRE);//设置居中
            titlefFormat1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
            titlefFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);//设置边框
        } catch (Exception e) {
            e.printStackTrace();
        }
        return titlefFormat1;
    }
    public WritableCellFormat getTitlefFormat2() {
        try {
            jxl.write.WritableFont titleFont2 = new jxl.write.WritableFont(
                    WritableFont.createFont("宋体"), 13, WritableFont.BOLD);
            titlefFormat2 = new WritableCellFormat(titleFont2);
            titlefFormat2.setAlignment(jxl.format.Alignment.CENTRE);//设置水平居中
            titlefFormat2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
            titlefFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);//设置边框
        } catch (Exception e) {
            e.printStackTrace();
        }
        return titlefFormat2;
    }
    public WritableCellFormat getTitlefFormat3() {
        try {
            jxl.write.WritableFont titleFont3 = new jxl.write.WritableFont(
                    WritableFont.createFont("宋体"),10,WritableFont.BOLD,
                    false,UnderlineStyle.NO_UNDERLINE,Colour.BLUE);
            titlefFormat3 = new WritableCellFormat(titleFont3);
            titlefFormat3.setAlignment(jxl.format.Alignment.CENTRE);//设置居中
            titlefFormat3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
            titlefFormat3.setBorder(Border.ALL, BorderLineStyle.THIN);//设置边框
        } catch (Exception e) {
            e.printStackTrace();
        }
        return titlefFormat3;
    }
    public WritableCellFormat getTitlefFormat4() {
        try {
            jxl.write.WritableFont titleFont4 = new jxl.write.WritableFont(
                    WritableFont.createFont("宋体"),10,WritableFont.BOLD,
                    false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
            titlefFormat4 = new WritableCellFormat(titleFont4);
            titlefFormat4.setAlignment(jxl.format.Alignment.CENTRE);//设置居中
            titlefFormat4.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
            titlefFormat4.setBorder(Border.ALL, BorderLineStyle.THIN);//设置边框
        } catch (Exception e) {
            e.printStackTrace();
        }
        return titlefFormat4;
    }
    public WritableCellFormat getTitlefFormat5() {
        try {
            jxl.write.WritableFont titleFont5 = new jxl.write.WritableFont(
                    WritableFont.createFont("宋体"),10,WritableFont.BOLD,
                    false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
            titlefFormat5 = new WritableCellFormat(titleFont5);
            titlefFormat5.setAlignment(jxl.format.Alignment.CENTRE);//设置居中
            titlefFormat5.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
            titlefFormat5.setBorder(Border.ALL, BorderLineStyle.THIN);//设置边框
        } catch (Exception e) {
            e.printStackTrace();
        }
        return titlefFormat5;
    }
    public WritableCellFormat getTitlefFormat6() {
        try {
            jxl.write.WritableFont titleFont6 = new jxl.write.WritableFont(
                    WritableFont.createFont("宋体"),10,WritableFont.BOLD,
                    false,UnderlineStyle.NO_UNDERLINE,Colour.GREEN);
            titlefFormat6 = new WritableCellFormat(titleFont6);
            titlefFormat6.setAlignment(jxl.format.Alignment.CENTRE);//设置居中
            titlefFormat6.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//垂直居中
            titlefFormat6.setBorder(Border.ALL, BorderLineStyle.THIN);//设置边框
        } catch (Exception e) {
            e.printStackTrace();
        }
        return titlefFormat6;
    }
    public static void main(String[] args) {
        ExceltitlefFormat oo = new ExceltitlefFormat();
        WritableCellFormat oooCellFormat = oo.getTitlefFormat();
        System.out.println(oooCellFormat);
        System.out.println(123);
    }
}

 测试导出Excel的Main方法:

import java.io.File;
import java.io.IOException;
import java.util.Date;
import jxl.Workbook;
import jxl.write.WriteException;

public static void main(String[] args) {
    try {
        File file = new File("D:\测试Excel导出.xlsx");
        if (!file.exists()) {
            file.createNewFile();
        }
        jxl.write.WritableWorkbook wwb = null;
        wwb = Workbook.createWorkbook(file);
        ExceltitlefFormat titlefFormat = new ExceltitlefFormat();
        // 创建一个Excel子表
        jxl.write.WritableSheet sheet = wwb.createSheet("表名", 1);
        // 可创建多个Excel子表,后面的数字增加即可
        // jxl.write.WritableSheet sheet1 = wwb.createSheet("表名", 2);
        // 设置列宽
        sheet.setColumnView(0, 10);// 参数1
        sheet.setColumnView(1, 15);// 参数2
        sheet.setColumnView(2, 20);// 参数3
        sheet.setColumnView(3, 25);// 参数4
        sheet.setColumnView(4, 30);// 参数5
        sheet.setColumnView(5, 36);// 参数6
        // 第一行(起始列,起始行,终止列,终止行)
        sheet.mergeCells(0, 0, 5, 0);
        jxl.write.Label labeltitleChild = new jxl.write.Label(0, 0, "标题", titlefFormat.getTitlefFormat1());
        sheet.addCell(labeltitleChild);
        // 第二行
        sheet.mergeCells(0, 1, 2, 1);
        sheet.addCell(new jxl.write.Label(0, 1, "参数跨列", titlefFormat.getTitlefFormat()));
        sheet.mergeCells(3, 1, 5, 1);
        sheet.addCell(new jxl.write.Label(3, 1, "导出时间：" + new Date(), titlefFormat.getTitlefFormat()));
        // 第三行
        sheet.addCell(new jxl.write.Label(0, 2, "参数1", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(1, 2, "参数2", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(2, 2, "参数3", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(3, 2, "参数4", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(4, 2, "参数5", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(5, 2, "参数6", titlefFormat.getTitlefFormat()));
        // 行
        int row = 3;
        // 可循环写入数据
        sheet.addCell(new jxl.write.Label(0, row, "参数1", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(1, row, "参数2", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(2, row, "参数3", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(3, row, "参数4", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(4, row, "参数5", titlefFormat.getTitlefFormat()));
        sheet.addCell(new jxl.write.Label(5, row, "参数6", titlefFormat.getTitlefFormat()));
        wwb.write();
        wwb.close();
        System.out.println("导出成功");
    } catch (WriteException e) {
        e.printStackTrace();
        System.out.println("导出失败");
    } catch (IOException e) {
        e.printStackTrace();
        System.out.println("导出失败");
   }
}