java jxl.write.label_jxl的简单使用

package com.gdapp.oa.app;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.List;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.format.Alignment;

import jxl.format.Border;

import jxl.format.BorderLineStyle;

import jxl.format.CellFormat;

import jxl.format.VerticalAlignment;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

public class ExcelAPP

{

private static void createTitle(WritableSheet wsheet) throws Exception

{

wsheet.addCell(new Label(0, 2, "信件号"));

wsheet.addCell(new Label(1, 2, "信件类型"));

wsheet.addCell(new Label(2, 2, "信件标题"));

wsheet.addCell(new Label(3, 2, "信件内容"));

wsheet.addCell(new Label(4, 2, "发信人"));

wsheet.addCell(new Label(5, 2, "邮政编码"));

wsheet.addCell(new Label(6, 2, "发信人地址"));

wsheet.addCell(new Label(7, 2, "电话"));

wsheet.addCell(new Label(8, 2, "发信人信箱"));

wsheet.addCell(new Label(9, 2, "信件状态"));

wsheet.addCell(new Label(10, 2, "转发/办理部门"));

wsheet.addCell(new Label(11, 2, "来信时间"));

wsheet.addCell(new Label(12, 2, "分办时间"));

wsheet.addCell(new Label(13, 2, "办回时间"));

}

private static String toUtf8String(String s)

{

StringBuffer sb = new StringBuffer();

for (int i = 0; i < s.length(); i++)

{

char c = s.charAt(i);

if (c >= 0 && c <= 255)

{

sb.append(c);

} else

{

byte[] b;

try

{

b = Character.toString(c).getBytes("utf-8");

} catch (Exception ex)

{

System.out.println(ex);

b = new byte[0];

}

for (int j = 0; j < b.length; j++)

{

int k = b[j];

if (k < 0)

k += 256;

sb.append("%" + Integer.toHexString(k).toUpperCase());

}

}

}

return sb.toString();

}

//直接在本地生成文件

static void writeXls()

{

System.out.println("writing exls.");

WritableWorkbook wwb = null;

WritableSheet sheet = null;

try

{

String xlsName = "c:\\excel2.xls";

File afile = new File(xlsName);

if (!afile.exists())

{

afile.createNewFile();

}

wwb = Workbook.createWorkbook(afile);

sheet = wwb.createSheet("bicashy的测试", 0); //创建标签页

WritableFont wf = new WritableFont(WritableFont.TIMES, 10,

WritableFont.BOLD, false);

//WritableFont wf = new WritableFont(WritableFont.ARIAL, 16,

//WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,

//Colour.BLACK);

WritableCellFormat format = new WritableCellFormat(wf); //添加格式化样式

format.setAlignment(Alignment.CENTRE); //设置水平对齐方式

format.setVerticalAlignment(VerticalAlignment.CENTRE); //设置垂直对齐方式

//remember,the first parameter is column index.

Label cell = new Label(0, 0, "row 1,column 1", format);//set the value of cell (0,0)

sheet.addCell(cell);

cell = new Label(1, 0, "row 1,column 2", format);

sheet.addCell(cell);

cell = new Label(0, 1, "row 2,column 1", format);

sheet.addCell(cell);

createTitle(sheet);

//将第四行合并前三列，行列默认都是从0开始的

sheet.mergeCells(0,3,2,3);

cell = new Label(0,3,"合并了三个单元格", format);

sheet.addCell(cell);

sheet.mergeCells(0,4,0,6);

cell = new Label(0,4,"合并三列", format);

sheet.addCell(cell);

wwb.write();

System.out.println("finished.");

} catch (WriteException e)

{

e.printStackTrace();

} catch (Exception e)

{

e.printStackTrace();

} finally

{

if (wwb != null)

{

try

{

wwb.close();

} catch (Exception e1)

{

e1.printStackTrace();

}

}

}

}

//直接在本地生成统计文件

static void writeXlsTJ()

{

// 准备设置excel工作表的标题

String[] title = {"编号","产品名称","产品价格","产品数量","生产日期","产地","是否出口"};

try {

// 获得开始时间

long start = System.currentTimeMillis();

// 输出的excel的路径

String filePath = "c:\\test.xls";

// 创建Excel工作薄

WritableWorkbook wwb;

// 新建立一个jxl文件,即在C盘下生成test.xls

OutputStream os = new FileOutputStream(filePath);

wwb=Workbook.createWorkbook(os);

// 添加第一个工作表并设置第一个Sheet的名字

WritableSheet sheet = wwb.createSheet("产品清单", 0);

Label label;

for(int i=0;iwritablesheet.mergeCells(int x,int y,int m,int n);来实现的

* 表示将从第x+1列，y+1行到m+1列，n+1行合并

*

* */

sheet.mergeCells(0,3,2,3);

label = new Label(0,3,"合并了三个单元格");

sheet.addCell(label);

/*

*

* 定义公共字体格式

* 通过获取一个字体的样式来作为模板

* 首先通过web.getSheet(0)获得第一个sheet

* 然后取得第一个sheet的第二列，第一行也就是"产品名称"的字体

* */

CellFormat cf = wwb.getSheet(0).getCell(1, 0).getCellFormat();

WritableCellFormat wc = new WritableCellFormat();

// 设置居中

wc.setAlignment(Alignment.CENTRE);

// 设置边框线

wc.setBorder(Border.ALL, BorderLineStyle.THIN);

// 设置单元格的背景颜色

wc.setBackground(jxl.format.Colour.RED);

label = new Label(1,5,"字体",wc);

sheet.addCell(label);

// 设置字体

jxl.write.WritableFont wfont = new jxl.write.WritableFont(WritableFont.createFont("隶书"),20);

WritableCellFormat font = new WritableCellFormat(wfont);

label = new Label(2,6,"隶书",font);

sheet.addCell(label);

// 写入数据

wwb.write();

// 关闭文件

wwb.close();

long end = System.currentTimeMillis();

System.out.println("操作共用的时间:"+(end-start)+"毫秒");

} catch (Exception e) {

e.printStackTrace();

}

}

//生成流，以便进行下载(用时可将流返回)

static void writeXlsStream()

{

try

{

ByteArrayOutputStream baos = new ByteArrayOutputStream();

WritableWorkbook wbook = Workbook.createWorkbook(baos);

wbook.write();

wbook.close();

byte[] bs = baos.toByteArray();

baos.close();

//inputstream 可以用于返回

InputStream inputstream = new ByteArrayInputStream(bs);

String fileName = toUtf8String("数据统计.xls");

} catch (WriteException e)

{

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e)

{

// TODO Auto-generated catch block

e.printStackTrace();

}

}

//读本地文件

static void readXls()

{

try

{

String filePath = "c:\\excel2.xls";

InputStream is = new FileInputStream(filePath);

Workbook wb = Workbook.getWorkbook(is); //得到工作薄

Sheet sheet = wb.getSheet(0); //得到工作薄中的第一个工作表

Cell cell = sheet.getCell(0,0);//得到工作表的第一个单元格,即A1

String content=cell.getContents();//getContents()将Cell中的字符转为字符串

System.out.println("A1内容=="+content);

System.out.println(sheet.getCell(0, 1).getContents());

System.out.println("总行数=="+sheet.getRows());

System.out.println("总列数=="+sheet.getColumns());

wb.close();//关闭工作薄

is.close();//关闭输入流

} catch (Exception e)

{

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void main(String[] args)

{

//writeXls();

readXls();

}

}