正文
【JXL】导出Excel
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
基本设置
// File file = new File("d:\\writetest.xlsx");
// WritableWorkbook wwb = Workbook.createWorkbook(file);
String filePath = "d:\\writetest1.xlsx";
OutputStream os = new FileOutputStream(filePath);
WritableWorkbook wwb = Workbook.createWorkbook(os);
//创建Excel工作表
WritableSheet ws = wwb.createSheet("sheet1", 0);
//打印时横向；LANDSCAPE（横向）、PORTRAIT（纵向）
ws.setPageSetup(PageOrientation.LANDSCAPE, PaperSize.A4, 0.5d, 0.5d);
//去掉整个sheet中的网格线
ws.getSettings().setShowGridLines(false);
// // 高度设置 此方法有bug，不建议用
// ws.setRowView(12,1000);
//宽度设置 第六列
ws.setColumnView(5, 30);
//合并单元格
ws.mergeCells(0, 0, 22, 0);//合并单元格(左列，左行，右列，右行),类似坐标，但又不同
//添加文本单元格
ws.addCell(new Label(0, 0, "2016年财政教育经费投入情况调查表", getTitle()));
//布尔
ws.addCell(new Boolean(2, 7, true));
//日期类型
ws.addCell(new DateTime(3,7, new Date(),new WritableCellFormat(DateFormats.FORMAT1)));
//将建公式（求和）
ws.addCell(new Formula(1, 9, "SUM(B8:B9)", getCellNumberFormat()));
//超链接
ws.addCell(new Label(0, 16, "", getHeader()));//设置边框
WritableHyperlink link = new WritableHyperlink(0, 16, new URL("http://www.baidu.com"));
link.setDescription("链接使用 链接到NilPower");
ws.addHyperlink(link);
//数字
WritableCellFormat w1 = new WritableCellFormat(NumberFormats.FORMAT1);
ws.addCell(new Number(1, 12, 19915042699.6764, w1));
//导出图片 API中注明只支持png文件
File file = new File("C:\\Users\\jinxiang.qin.qjx\\Desktop\\1.png"); // 获得图片
WritableImage image = new WritableImage(0, 20, 3, 3, file); // 设置图片显示位置
// 4，4代表图片的高和宽占4个单元格
ws.addImage(image); // 加载图片
字体边框对齐方式等设置
/**
* 设置头的样式
*
* @return
*/
private WritableCellFormat getHeader() {
try {
//设置字体Arial,48号，加粗,是否斜体,双下划线
WritableFont font = new WritableFont(WritableFont.createFont("微软雅黑"), 48, WritableFont.BOLD, true, UnderlineStyle.DOUBLE);
//字体颜色
font.setColour(Colour.GREEN);
WritableCellFormat wcf = new WritableCellFormat(font);
//水平居中
wcf.setAlignment(Alignment.CENTRE);
//垂直居中
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框设置
wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
//背景颜色
wcf.setBackground(Colour.YELLOW);
//是否自动换行
wcf.setWrap(true);
return wcf;
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
字体输入方向
WritableCellFormat f= getHeader();
f.setOrientation(Orientation.HORIZONTAL);
ws.addCell(new Label(0, 18, "本年购置数",f));
WritableCellFormat f1= getHeader();
f1.setOrientation(Orientation.VERTICAL);
ws.addCell(new Label(1, 18, "本年购置数",f1));
WritableCellFormat f2= getHeader();
f2.setOrientation(Orientation.PLUS_90);
ws.addCell(new Label(2, 18, "本年购置数",f2));
WritableCellFormat f3= getHeader();
f3.setOrientation(Orientation.MINUS_90);
ws.addCell(new Label(3, 18, "本年购置数",f3));
WritableCellFormat f4= getHeader();
f4.setOrientation(Orientation.PLUS_45);
ws.addCell(new Label(4, 18, "本年购置数",f4));
WritableCellFormat f5= getHeader();
f5.setOrientation(Orientation.MINUS_45);
ws.addCell(new Label(5, 18, "本年购置数",f5));
WritableCellFormat f6= getHeader();
f6.setOrientation(Orientation.STACKED);
ws.addCell(new Label(6, 18, "本年购置数",f6));
日期格式化
/**
* 日期格式化（自定义）
* @return
* @throws Exception
*/
private WritableCellFormat getCellDateFormat() throws Exception {
//此处也可以使用 DateFormats
WritableCellFormat wcf = getCellFormat(new DateFormat("yyyy-MM-dd HH:mm:ss"));
//水平居中
wcf.setAlignment(Alignment.CENTRE);
return wcf;
}
数字格式化
/**
* 数字格式化例子
* @param ws
* @throws Exception
*/
private void getNumber(WritableSheet ws) throws Exception{
///////////////////////////////////////////////////
ws.addCell(new Label(0, 12, "FORMAT"));
// 四舍五入整数
WritableCellFormat w1 = new WritableCellFormat(NumberFormats.FORMAT1);
ws.addCell(new Number(1, 12, 19915042699.6764, w1));
// 四舍五入整数
WritableCellFormat w2 = new WritableCellFormat(NumberFormats.FORMAT2);
ws.addCell(new Number(2, 12, 29915042699.6764, w2));
// 四舍五入保留两位小数
WritableCellFormat w3 = new WritableCellFormat(NumberFormats.FORMAT3);
ws.addCell(new Number(3, 12, 39915042699.6764, w3));
// 四舍五入保留两位小数
WritableCellFormat w4 = new WritableCellFormat(NumberFormats.FORMAT4);
ws.addCell(new Number(4, 12, 49915042699.6764, w4));
// 四舍五入整数
WritableCellFormat w5 = new WritableCellFormat(NumberFormats.FORMAT5);
ws.addCell(new Number(5, 12, 59915042699.6764, w5));
// 四舍五入整数，前面加上了“￥”
WritableCellFormat w6 = new WritableCellFormat(NumberFormats.FORMAT6);
ws.addCell(new Number(6, 12, 69915042699.6764, w6));
// 四舍五入保留两位小数
WritableCellFormat w7 = new WritableCellFormat(NumberFormats.FORMAT7);
ws.addCell(new Number(7, 12, 79915042699.6764, w7));
// 四舍五入保留两位小数，前面加上了“￥”
WritableCellFormat w8 = new WritableCellFormat(NumberFormats.FORMAT8);
ws.addCell(new Number(8, 12, 89915042699.6764, w8));
WritableCellFormat w9 = new WritableCellFormat(NumberFormats.FORMAT9);
ws.addCell(new Number(9, 12, 95042699.6764, w9));
// 科学计数法展示
WritableCellFormat w10 = new WritableCellFormat(NumberFormats.FORMAT10);
ws.addCell(new Number(10, 12, 915042699.6764, w10));
// 四舍五入保留两位小数，无千位分隔符
WritableCellFormat w11 = new WritableCellFormat(NumberFormats.TEXT);
ws.addCell(new Number(11, 12, 915042699.6764, w11));
///////////////////////////////////////////////////
ws.addCell(new Label(0, 13, "DEFAULT"));
WritableCellFormat r1 = new WritableCellFormat(NumberFormats.DEFAULT);
ws.addCell(new Number(1, 13, 19915042699.6764, r1));
WritableCellFormat r2 = new WritableCellFormat(NumberFormats.INTEGER);
ws.addCell(new Number(2, 13, 29915042699.6764, r2));
WritableCellFormat r3 = new WritableCellFormat(NumberFormats.FLOAT);
ws.addCell(new Number(3, 13, 39915042699.6764, r3));
WritableCellFormat r4 = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER);
ws.addCell(new Number(4, 13, 49915042699.6764, r4));
WritableCellFormat r5 = new WritableCellFormat(NumberFormats.THOUSANDS_FLOAT);
ws.addCell(new Number(5, 13, 59915042699.6764, r5));
WritableCellFormat r6 = new WritableCellFormat(NumberFormats.ACCOUNTING_INTEGER);
ws.addCell(new Number(6, 13, 69915042699.6764, r6));
WritableCellFormat r7 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_INTEGER);
ws.addCell(new Number(7, 13, 79915042699.6764, r7));
WritableCellFormat r8 = new WritableCellFormat(NumberFormats.ACCOUNTING_FLOAT);
ws.addCell(new Number(8, 13, 89915042699.6764, r8));
WritableCellFormat r9 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_FLOAT);
ws.addCell(new Number(9, 13, 99915042699.6764, r9));
///////////////////////////////////////////////////
ws.addCell(new Label(0, 14, "百分比"));
WritableCellFormat c1 = new WritableCellFormat(NumberFormats.PERCENT_INTEGER);
ws.addCell(new Number(1, 14, 19915042699.6764, c1));
WritableCellFormat c2 = new WritableCellFormat(NumberFormats.PERCENT_FLOAT);
ws.addCell(new Number(2, 14, 29915042699.6764, c2));
WritableCellFormat c3 = new WritableCellFormat(NumberFormats.EXPONENTIAL);
ws.addCell(new Number(3, 14, 39915042699.6764, c3));
WritableCellFormat c4 = new WritableCellFormat(NumberFormats.FRACTION_ONE_DIGIT);
ws.addCell(new Number(4, 14, 49915042699.6764, c4));
WritableCellFormat c5 = new WritableCellFormat(NumberFormats.FRACTION_TWO_DIGITS);
ws.addCell(new Number(5, 14, 59915042699.6764, c5));
}
数字自定义格式化
/**
* 数字千位分隔符格式化,保留三位小数（自定义）
* @return
* @throws Exception
*/
private WritableCellFormat getCellNumberFormat() throws Exception {
WritableCellFormat wcf = getCellFormat(new NumberFormat("#,##0.000"));
// WritableCellFormat wcf = getCellFormat(NumberFormats.FORMAT3);
//水平居中
wcf.setAlignment(Alignment.RIGHT);
return wcf;
}
自动调整列宽（来自网络，用时自己测试）
方法1：
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
sheet.setColumnView(1, cellView);//根据内容自动设置列宽
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label);
////////////////////////////
方法2：
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label);
sheet.setColumnView(1, new String("zzzzzzzzzzzzzzzzzzzzzz").length());
其他常用API
4 jxl常用api （来自网络 ）
4.1.1 1、Workbook类提供的方法
int getNumberOfSheets() 获取工作表的总个数
Sheet[] getSheets() 获取数组型的工作表
Sheet getSheet(String name);//得到此对应名称的工作表
4.1.2 2、Sheet接口提供的方法
String getName() 获取工作表的名称
int getColumns() 获取Sheet表中所包含的总列数
Cell[] getColumn(int column) 获取某一列的所有单元格，
返回的是单元格对象数组
int getRows() 获取Sheet表中所包含的总行数
Cell[] getRow(int row) 获取某一行的所有单元格，返回的是单元格对象数组
Cell getCell(int column, int row)获取指定单元格的对象引用，需要注意的是它的两个参数，第一个是列数，第二个是行数，这与通常的行、列组合有些不同
WritableSheet.setRowView(int i,int height); 指定第i+1行的高度
WritableSheet.setColumnView(int i,int width); 指定第i+1列的宽度
// File file = new File("d:\\writetest.xlsx");
// WritableWorkbook wwb = Workbook.createWorkbook(file);
String filePath = "d:\\writetest1.xlsx";
OutputStream os = new FileOutputStream(filePath);
WritableWorkbook wwb = Workbook.createWorkbook(os);
//创建Excel工作表
WritableSheet ws = wwb.createSheet("sheet1", 0);
//打印时横向；LANDSCAPE（横向）、PORTRAIT（纵向）
ws.setPageSetup(PageOrientation.LANDSCAPE, PaperSize.A4, 0.5d, 0.5d);
//去掉整个sheet中的网格线
ws.getSettings().setShowGridLines(false);
// // 高度设置 此方法有bug，不建议用
// ws.setRowView(12,1000);
//宽度设置 第六列
ws.setColumnView(5, 30);
//合并单元格
ws.mergeCells(0, 0, 22, 0);//合并单元格(左列，左行，右列，右行),类似坐标，但又不同
//添加文本单元格
ws.addCell(new Label(0, 0, "2016年财政教育经费投入情况调查表", getTitle()));
//布尔
ws.addCell(new Boolean(2, 7, true));
//日期类型
ws.addCell(new DateTime(3,7, new Date(),new WritableCellFormat(DateFormats.FORMAT1)));
//将建公式（求和）
ws.addCell(new Formula(1, 9, "SUM(B8:B9)", getCellNumberFormat()));
//超链接
ws.addCell(new Label(0, 16, "", getHeader()));//设置边框
WritableHyperlink link = new WritableHyperlink(0, 16, new URL("http://www.baidu.com"));
link.setDescription("链接使用 链接到NilPower");
ws.addHyperlink(link);
//数字
WritableCellFormat w1 = new WritableCellFormat(NumberFormats.FORMAT1);
ws.addCell(new Number(1, 12, 19915042699.6764, w1));
//导出图片 API中注明只支持png文件
File file = new File("C:\\Users\\jinxiang.qin.qjx\\Desktop\\1.png"); // 获得图片
WritableImage image = new WritableImage(0, 20, 3, 3, file); // 设置图片显示位置
// 4，4代表图片的高和宽占4个单元格
ws.addImage(image); // 加载图片
/**
* 设置头的样式
*
* @return
*/
private WritableCellFormat getHeader() {
try {
//设置字体Arial,48号，加粗,是否斜体,双下划线
WritableFont font = new WritableFont(WritableFont.createFont("微软雅黑"), 48, WritableFont.BOLD, true, UnderlineStyle.DOUBLE);
//字体颜色
font.setColour(Colour.GREEN); WritableCellFormat wcf = new WritableCellFormat(font);
//水平居中
wcf.setAlignment(Alignment.CENTRE);
//垂直居中
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框设置
wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
//背景颜色
wcf.setBackground(Colour.YELLOW);
//是否自动换行
wcf.setWrap(true);
return wcf;
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
字体输入方向
WritableCellFormat f= getHeader();
f.setOrientation(Orientation.HORIZONTAL);
ws.addCell(new Label(0, 18, "本年购置数",f));
WritableCellFormat f1= getHeader();
f1.setOrientation(Orientation.VERTICAL);
ws.addCell(new Label(1, 18, "本年购置数",f1));
WritableCellFormat f2= getHeader();
f2.setOrientation(Orientation.PLUS_90);
ws.addCell(new Label(2, 18, "本年购置数",f2));
WritableCellFormat f3= getHeader();
f3.setOrientation(Orientation.MINUS_90);
ws.addCell(new Label(3, 18, "本年购置数",f3));
WritableCellFormat f4= getHeader();
f4.setOrientation(Orientation.PLUS_45);
ws.addCell(new Label(4, 18, "本年购置数",f4));
WritableCellFormat f5= getHeader();
f5.setOrientation(Orientation.MINUS_45);
ws.addCell(new Label(5, 18, "本年购置数",f5));
WritableCellFormat f6= getHeader();
f6.setOrientation(Orientation.STACKED);
ws.addCell(new Label(6, 18, "本年购置数",f6));
日期格式化
/**
* 日期格式化（自定义）
* @return
* @throws Exception
*/
private WritableCellFormat getCellDateFormat() throws Exception {
//此处也可以使用 DateFormats
WritableCellFormat wcf = getCellFormat(new DateFormat("yyyy-MM-dd HH:mm:ss"));
//水平居中
wcf.setAlignment(Alignment.CENTRE);
return wcf;
}
数字格式化
/**
* 数字格式化例子
* @param ws
* @throws Exception
*/
private void getNumber(WritableSheet ws) throws Exception{
///////////////////////////////////////////////////
ws.addCell(new Label(0, 12, "FORMAT"));
// 四舍五入整数
WritableCellFormat w1 = new WritableCellFormat(NumberFormats.FORMAT1);
ws.addCell(new Number(1, 12, 19915042699.6764, w1));
// 四舍五入整数
WritableCellFormat w2 = new WritableCellFormat(NumberFormats.FORMAT2);
ws.addCell(new Number(2, 12, 29915042699.6764, w2));
// 四舍五入保留两位小数
WritableCellFormat w3 = new WritableCellFormat(NumberFormats.FORMAT3);
ws.addCell(new Number(3, 12, 39915042699.6764, w3));
// 四舍五入保留两位小数
WritableCellFormat w4 = new WritableCellFormat(NumberFormats.FORMAT4);
ws.addCell(new Number(4, 12, 49915042699.6764, w4));
// 四舍五入整数
WritableCellFormat w5 = new WritableCellFormat(NumberFormats.FORMAT5);
ws.addCell(new Number(5, 12, 59915042699.6764, w5));
// 四舍五入整数，前面加上了“￥”
WritableCellFormat w6 = new WritableCellFormat(NumberFormats.FORMAT6);
ws.addCell(new Number(6, 12, 69915042699.6764, w6));
// 四舍五入保留两位小数
WritableCellFormat w7 = new WritableCellFormat(NumberFormats.FORMAT7);
ws.addCell(new Number(7, 12, 79915042699.6764, w7));
// 四舍五入保留两位小数，前面加上了“￥”
WritableCellFormat w8 = new WritableCellFormat(NumberFormats.FORMAT8);
ws.addCell(new Number(8, 12, 89915042699.6764, w8));
WritableCellFormat w9 = new WritableCellFormat(NumberFormats.FORMAT9);
ws.addCell(new Number(9, 12, 95042699.6764, w9));
// 科学计数法展示
WritableCellFormat w10 = new WritableCellFormat(NumberFormats.FORMAT10);
ws.addCell(new Number(10, 12, 915042699.6764, w10));
// 四舍五入保留两位小数，无千位分隔符
WritableCellFormat w11 = new WritableCellFormat(NumberFormats.TEXT);
ws.addCell(new Number(11, 12, 915042699.6764, w11));
///////////////////////////////////////////////////
ws.addCell(new Label(0, 13, "DEFAULT"));
WritableCellFormat r1 = new WritableCellFormat(NumberFormats.DEFAULT);
ws.addCell(new Number(1, 13, 19915042699.6764, r1));
WritableCellFormat r2 = new WritableCellFormat(NumberFormats.INTEGER);
ws.addCell(new Number(2, 13, 29915042699.6764, r2));
WritableCellFormat r3 = new WritableCellFormat(NumberFormats.FLOAT);
ws.addCell(new Number(3, 13, 39915042699.6764, r3));
WritableCellFormat r4 = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER);
ws.addCell(new Number(4, 13, 49915042699.6764, r4));
WritableCellFormat r5 = new WritableCellFormat(NumberFormats.THOUSANDS_FLOAT);
ws.addCell(new Number(5, 13, 59915042699.6764, r5));
WritableCellFormat r6 = new WritableCellFormat(NumberFormats.ACCOUNTING_INTEGER);
ws.addCell(new Number(6, 13, 69915042699.6764, r6));
WritableCellFormat r7 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_INTEGER);
ws.addCell(new Number(7, 13, 79915042699.6764, r7));
WritableCellFormat r8 = new WritableCellFormat(NumberFormats.ACCOUNTING_FLOAT);
ws.addCell(new Number(8, 13, 89915042699.6764, r8));
WritableCellFormat r9 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_FLOAT);
ws.addCell(new Number(9, 13, 99915042699.6764, r9));
///////////////////////////////////////////////////
ws.addCell(new Label(0, 14, "百分比"));
WritableCellFormat c1 = new WritableCellFormat(NumberFormats.PERCENT_INTEGER);
ws.addCell(new Number(1, 14, 19915042699.6764, c1));
WritableCellFormat c2 = new WritableCellFormat(NumberFormats.PERCENT_FLOAT);
ws.addCell(new Number(2, 14, 29915042699.6764, c2));
WritableCellFormat c3 = new WritableCellFormat(NumberFormats.EXPONENTIAL);
ws.addCell(new Number(3, 14, 39915042699.6764, c3));
WritableCellFormat c4 = new WritableCellFormat(NumberFormats.FRACTION_ONE_DIGIT);
ws.addCell(new Number(4, 14, 49915042699.6764, c4));
WritableCellFormat c5 = new WritableCellFormat(NumberFormats.FRACTION_TWO_DIGITS);
ws.addCell(new Number(5, 14, 59915042699.6764, c5));
}
数字自定义格式化
/**
* 数字千位分隔符格式化,保留三位小数（自定义）
* @return
* @throws Exception
*/
private WritableCellFormat getCellNumberFormat() throws Exception {
WritableCellFormat wcf = getCellFormat(new NumberFormat("#,##0.000"));
// WritableCellFormat wcf = getCellFormat(NumberFormats.FORMAT3);
//水平居中
wcf.setAlignment(Alignment.RIGHT);
return wcf;
}
自动调整列宽（来自网络，用时自己测试）
方法1：
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
sheet.setColumnView(1, cellView);//根据内容自动设置列宽
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label);
////////////////////////////
方法2：
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label);
sheet.setColumnView(1, new String("zzzzzzzzzzzzzzzzzzzzzz").length());
其他常用API
4 jxl常用api （来自网络 ）
4.1.1 1、Workbook类提供的方法
int getNumberOfSheets() 获取工作表的总个数
Sheet[] getSheets() 获取数组型的工作表
Sheet getSheet(String name);//得到此对应名称的工作表
4.1.2 2、Sheet接口提供的方法
String getName() 获取工作表的名称
int getColumns() 获取Sheet表中所包含的总列数
Cell[] getColumn(int column) 获取某一列的所有单元格，
返回的是单元格对象数组
int getRows() 获取Sheet表中所包含的总行数
Cell[] getRow(int row) 获取某一行的所有单元格，返回的是单元格对象数组
Cell getCell(int column, int row)获取指定单元格的对象引用，需要注意的是它的两个参数，第一个是列数，第二个是行数，这与通常的行、列组合有些不同
WritableSheet.setRowView(int i,int height); 指定第i+1行的高度
WritableSheet.setColumnView(int i,int width); 指定第i+1列的宽度
WritableCellFormat f= getHeader();
f.setOrientation(Orientation.HORIZONTAL);
ws.addCell(new Label(0, 18, "本年购置数",f)); WritableCellFormat f1= getHeader();
f1.setOrientation(Orientation.VERTICAL);
ws.addCell(new Label(1, 18, "本年购置数",f1)); WritableCellFormat f2= getHeader();
f2.setOrientation(Orientation.PLUS_90);
ws.addCell(new Label(2, 18, "本年购置数",f2)); WritableCellFormat f3= getHeader();
f3.setOrientation(Orientation.MINUS_90);
ws.addCell(new Label(3, 18, "本年购置数",f3)); WritableCellFormat f4= getHeader();
f4.setOrientation(Orientation.PLUS_45);
ws.addCell(new Label(4, 18, "本年购置数",f4)); WritableCellFormat f5= getHeader();
f5.setOrientation(Orientation.MINUS_45);
ws.addCell(new Label(5, 18, "本年购置数",f5)); WritableCellFormat f6= getHeader();
f6.setOrientation(Orientation.STACKED);
ws.addCell(new Label(6, 18, "本年购置数",f6));
/**
* 日期格式化（自定义）
* @return
* @throws Exception
*/
private WritableCellFormat getCellDateFormat() throws Exception {
//此处也可以使用 DateFormats
WritableCellFormat wcf = getCellFormat(new DateFormat("yyyy-MM-dd HH:mm:ss"));
//水平居中
wcf.setAlignment(Alignment.CENTRE);
return wcf;
}
数字格式化
/**
* 数字格式化例子
* @param ws
* @throws Exception
*/
private void getNumber(WritableSheet ws) throws Exception{
///////////////////////////////////////////////////
ws.addCell(new Label(0, 12, "FORMAT"));
// 四舍五入整数
WritableCellFormat w1 = new WritableCellFormat(NumberFormats.FORMAT1);
ws.addCell(new Number(1, 12, 19915042699.6764, w1));
// 四舍五入整数
WritableCellFormat w2 = new WritableCellFormat(NumberFormats.FORMAT2);
ws.addCell(new Number(2, 12, 29915042699.6764, w2));
// 四舍五入保留两位小数
WritableCellFormat w3 = new WritableCellFormat(NumberFormats.FORMAT3);
ws.addCell(new Number(3, 12, 39915042699.6764, w3));
// 四舍五入保留两位小数
WritableCellFormat w4 = new WritableCellFormat(NumberFormats.FORMAT4);
ws.addCell(new Number(4, 12, 49915042699.6764, w4));
// 四舍五入整数
WritableCellFormat w5 = new WritableCellFormat(NumberFormats.FORMAT5);
ws.addCell(new Number(5, 12, 59915042699.6764, w5));
// 四舍五入整数，前面加上了“￥”
WritableCellFormat w6 = new WritableCellFormat(NumberFormats.FORMAT6);
ws.addCell(new Number(6, 12, 69915042699.6764, w6));
// 四舍五入保留两位小数
WritableCellFormat w7 = new WritableCellFormat(NumberFormats.FORMAT7);
ws.addCell(new Number(7, 12, 79915042699.6764, w7));
// 四舍五入保留两位小数，前面加上了“￥”
WritableCellFormat w8 = new WritableCellFormat(NumberFormats.FORMAT8);
ws.addCell(new Number(8, 12, 89915042699.6764, w8));
WritableCellFormat w9 = new WritableCellFormat(NumberFormats.FORMAT9);
ws.addCell(new Number(9, 12, 95042699.6764, w9));
// 科学计数法展示
WritableCellFormat w10 = new WritableCellFormat(NumberFormats.FORMAT10);
ws.addCell(new Number(10, 12, 915042699.6764, w10));
// 四舍五入保留两位小数，无千位分隔符
WritableCellFormat w11 = new WritableCellFormat(NumberFormats.TEXT);
ws.addCell(new Number(11, 12, 915042699.6764, w11));
///////////////////////////////////////////////////
ws.addCell(new Label(0, 13, "DEFAULT"));
WritableCellFormat r1 = new WritableCellFormat(NumberFormats.DEFAULT);
ws.addCell(new Number(1, 13, 19915042699.6764, r1));
WritableCellFormat r2 = new WritableCellFormat(NumberFormats.INTEGER);
ws.addCell(new Number(2, 13, 29915042699.6764, r2));
WritableCellFormat r3 = new WritableCellFormat(NumberFormats.FLOAT);
ws.addCell(new Number(3, 13, 39915042699.6764, r3));
WritableCellFormat r4 = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER);
ws.addCell(new Number(4, 13, 49915042699.6764, r4));
WritableCellFormat r5 = new WritableCellFormat(NumberFormats.THOUSANDS_FLOAT);
ws.addCell(new Number(5, 13, 59915042699.6764, r5));
WritableCellFormat r6 = new WritableCellFormat(NumberFormats.ACCOUNTING_INTEGER);
ws.addCell(new Number(6, 13, 69915042699.6764, r6));
WritableCellFormat r7 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_INTEGER);
ws.addCell(new Number(7, 13, 79915042699.6764, r7));
WritableCellFormat r8 = new WritableCellFormat(NumberFormats.ACCOUNTING_FLOAT);
ws.addCell(new Number(8, 13, 89915042699.6764, r8));
WritableCellFormat r9 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_FLOAT);
ws.addCell(new Number(9, 13, 99915042699.6764, r9));
///////////////////////////////////////////////////
ws.addCell(new Label(0, 14, "百分比"));
WritableCellFormat c1 = new WritableCellFormat(NumberFormats.PERCENT_INTEGER);
ws.addCell(new Number(1, 14, 19915042699.6764, c1));
WritableCellFormat c2 = new WritableCellFormat(NumberFormats.PERCENT_FLOAT);
ws.addCell(new Number(2, 14, 29915042699.6764, c2));
WritableCellFormat c3 = new WritableCellFormat(NumberFormats.EXPONENTIAL);
ws.addCell(new Number(3, 14, 39915042699.6764, c3));
WritableCellFormat c4 = new WritableCellFormat(NumberFormats.FRACTION_ONE_DIGIT);
ws.addCell(new Number(4, 14, 49915042699.6764, c4));
WritableCellFormat c5 = new WritableCellFormat(NumberFormats.FRACTION_TWO_DIGITS);
ws.addCell(new Number(5, 14, 59915042699.6764, c5));
}
数字自定义格式化
/**
* 数字千位分隔符格式化,保留三位小数（自定义）
* @return
* @throws Exception
*/
private WritableCellFormat getCellNumberFormat() throws Exception {
WritableCellFormat wcf = getCellFormat(new NumberFormat("#,##0.000"));
// WritableCellFormat wcf = getCellFormat(NumberFormats.FORMAT3);
//水平居中
wcf.setAlignment(Alignment.RIGHT);
return wcf;
}
自动调整列宽（来自网络，用时自己测试）
方法1：
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
sheet.setColumnView(1, cellView);//根据内容自动设置列宽
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label);
////////////////////////////
方法2：
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label);
sheet.setColumnView(1, new String("zzzzzzzzzzzzzzzzzzzzzz").length());
其他常用API
4 jxl常用api （来自网络 ）
4.1.1 1、Workbook类提供的方法
int getNumberOfSheets() 获取工作表的总个数
Sheet[] getSheets() 获取数组型的工作表
Sheet getSheet(String name);//得到此对应名称的工作表
4.1.2 2、Sheet接口提供的方法
String getName() 获取工作表的名称
int getColumns() 获取Sheet表中所包含的总列数
Cell[] getColumn(int column) 获取某一列的所有单元格，
返回的是单元格对象数组
int getRows() 获取Sheet表中所包含的总行数
Cell[] getRow(int row) 获取某一行的所有单元格，返回的是单元格对象数组
Cell getCell(int column, int row)获取指定单元格的对象引用，需要注意的是它的两个参数，第一个是列数，第二个是行数，这与通常的行、列组合有些不同
WritableSheet.setRowView(int i,int height); 指定第i+1行的高度
WritableSheet.setColumnView(int i,int width); 指定第i+1列的宽度
/**
* 数字格式化例子
* @param ws
* @throws Exception
*/
private void getNumber(WritableSheet ws) throws Exception{
///////////////////////////////////////////////////
ws.addCell(new Label(0, 12, "FORMAT"));
// 四舍五入整数
WritableCellFormat w1 = new WritableCellFormat(NumberFormats.FORMAT1);
ws.addCell(new Number(1, 12, 19915042699.6764, w1)); // 四舍五入整数
WritableCellFormat w2 = new WritableCellFormat(NumberFormats.FORMAT2);
ws.addCell(new Number(2, 12, 29915042699.6764, w2)); // 四舍五入保留两位小数
WritableCellFormat w3 = new WritableCellFormat(NumberFormats.FORMAT3);
ws.addCell(new Number(3, 12, 39915042699.6764, w3)); // 四舍五入保留两位小数
WritableCellFormat w4 = new WritableCellFormat(NumberFormats.FORMAT4);
ws.addCell(new Number(4, 12, 49915042699.6764, w4)); // 四舍五入整数
WritableCellFormat w5 = new WritableCellFormat(NumberFormats.FORMAT5);
ws.addCell(new Number(5, 12, 59915042699.6764, w5)); // 四舍五入整数，前面加上了“￥”
WritableCellFormat w6 = new WritableCellFormat(NumberFormats.FORMAT6);
ws.addCell(new Number(6, 12, 69915042699.6764, w6)); // 四舍五入保留两位小数
WritableCellFormat w7 = new WritableCellFormat(NumberFormats.FORMAT7);
ws.addCell(new Number(7, 12, 79915042699.6764, w7)); // 四舍五入保留两位小数，前面加上了“￥”
WritableCellFormat w8 = new WritableCellFormat(NumberFormats.FORMAT8);
ws.addCell(new Number(8, 12, 89915042699.6764, w8)); WritableCellFormat w9 = new WritableCellFormat(NumberFormats.FORMAT9);
ws.addCell(new Number(9, 12, 95042699.6764, w9)); // 科学计数法展示
WritableCellFormat w10 = new WritableCellFormat(NumberFormats.FORMAT10);
ws.addCell(new Number(10, 12, 915042699.6764, w10)); // 四舍五入保留两位小数，无千位分隔符
WritableCellFormat w11 = new WritableCellFormat(NumberFormats.TEXT);
ws.addCell(new Number(11, 12, 915042699.6764, w11)); /////////////////////////////////////////////////// ws.addCell(new Label(0, 13, "DEFAULT")); WritableCellFormat r1 = new WritableCellFormat(NumberFormats.DEFAULT);
ws.addCell(new Number(1, 13, 19915042699.6764, r1)); WritableCellFormat r2 = new WritableCellFormat(NumberFormats.INTEGER);
ws.addCell(new Number(2, 13, 29915042699.6764, r2)); WritableCellFormat r3 = new WritableCellFormat(NumberFormats.FLOAT);
ws.addCell(new Number(3, 13, 39915042699.6764, r3)); WritableCellFormat r4 = new WritableCellFormat(NumberFormats.THOUSANDS_INTEGER);
ws.addCell(new Number(4, 13, 49915042699.6764, r4)); WritableCellFormat r5 = new WritableCellFormat(NumberFormats.THOUSANDS_FLOAT);
ws.addCell(new Number(5, 13, 59915042699.6764, r5)); WritableCellFormat r6 = new WritableCellFormat(NumberFormats.ACCOUNTING_INTEGER);
ws.addCell(new Number(6, 13, 69915042699.6764, r6)); WritableCellFormat r7 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_INTEGER);
ws.addCell(new Number(7, 13, 79915042699.6764, r7)); WritableCellFormat r8 = new WritableCellFormat(NumberFormats.ACCOUNTING_FLOAT);
ws.addCell(new Number(8, 13, 89915042699.6764, r8)); WritableCellFormat r9 = new WritableCellFormat(NumberFormats.ACCOUNTING_RED_FLOAT);
ws.addCell(new Number(9, 13, 99915042699.6764, r9)); /////////////////////////////////////////////////// ws.addCell(new Label(0, 14, "百分比")); WritableCellFormat c1 = new WritableCellFormat(NumberFormats.PERCENT_INTEGER);
ws.addCell(new Number(1, 14, 19915042699.6764, c1)); WritableCellFormat c2 = new WritableCellFormat(NumberFormats.PERCENT_FLOAT);
ws.addCell(new Number(2, 14, 29915042699.6764, c2)); WritableCellFormat c3 = new WritableCellFormat(NumberFormats.EXPONENTIAL);
ws.addCell(new Number(3, 14, 39915042699.6764, c3)); WritableCellFormat c4 = new WritableCellFormat(NumberFormats.FRACTION_ONE_DIGIT);
ws.addCell(new Number(4, 14, 49915042699.6764, c4)); WritableCellFormat c5 = new WritableCellFormat(NumberFormats.FRACTION_TWO_DIGITS);
ws.addCell(new Number(5, 14, 59915042699.6764, c5));
}
/**
* 数字千位分隔符格式化,保留三位小数（自定义）
* @return
* @throws Exception
*/
private WritableCellFormat getCellNumberFormat() throws Exception {
WritableCellFormat wcf = getCellFormat(new NumberFormat("#,##0.000")); // WritableCellFormat wcf = getCellFormat(NumberFormats.FORMAT3);
//水平居中
wcf.setAlignment(Alignment.RIGHT);
return wcf;
}
自动调整列宽（来自网络，用时自己测试）
方法1：
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
sheet.setColumnView(1, cellView);//根据内容自动设置列宽
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label);
////////////////////////////
方法2：
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label);
sheet.setColumnView(1, new String("zzzzzzzzzzzzzzzzzzzzzz").length());
其他常用API
4 jxl常用api （来自网络 ）
4.1.1 1、Workbook类提供的方法
int getNumberOfSheets() 获取工作表的总个数
Sheet[] getSheets() 获取数组型的工作表
Sheet getSheet(String name);//得到此对应名称的工作表
4.1.2 2、Sheet接口提供的方法
String getName() 获取工作表的名称
int getColumns() 获取Sheet表中所包含的总列数
Cell[] getColumn(int column) 获取某一列的所有单元格，
返回的是单元格对象数组
int getRows() 获取Sheet表中所包含的总行数
Cell[] getRow(int row) 获取某一行的所有单元格，返回的是单元格对象数组
Cell getCell(int column, int row)获取指定单元格的对象引用，需要注意的是它的两个参数，第一个是列数，第二个是行数，这与通常的行、列组合有些不同
WritableSheet.setRowView(int i,int height); 指定第i+1行的高度
WritableSheet.setColumnView(int i,int width); 指定第i+1列的宽度
方法1：
CellView cellView = new CellView();
cellView.setAutosize(true); //设置自动大小
sheet.setColumnView(1, cellView);//根据内容自动设置列宽
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label); ////////////////////////////
方法2：
label = new Label(1, 0, "zzzzzzzzzzzzzzzzzzzzzz");
sheet.addCell(label);
sheet.setColumnView(1, new String("zzzzzzzzzzzzzzzzzzzzzz").length());
4 jxl常用api （来自网络 ）
4.1.1 1、Workbook类提供的方法
int getNumberOfSheets() 获取工作表的总个数
Sheet[] getSheets() 获取数组型的工作表
Sheet getSheet(String name);//得到此对应名称的工作表
4.1.2 2、Sheet接口提供的方法
String getName() 获取工作表的名称
int getColumns() 获取Sheet表中所包含的总列数
Cell[] getColumn(int column) 获取某一列的所有单元格，
返回的是单元格对象数组
int getRows() 获取Sheet表中所包含的总行数
Cell[] getRow(int row) 获取某一行的所有单元格，返回的是单元格对象数组
Cell getCell(int column, int row)获取指定单元格的对象引用，需要注意的是它的两个参数，第一个是列数，第二个是行数，这与通常的行、列组合有些不同
WritableSheet.setRowView(int i,int height); 指定第i+1行的高度
WritableSheet.setColumnView(int i,int width); 指定第i+1列的宽度
