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);
}
}
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("导出失败");
}
}
