有时候我们会需要做excel的报表,下面这个例子详细的给出了读写及定义样式的方法。
package com.test;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/** *//**
*
* CopyRight (C) www.blogjava.net/ilovezmh All rights reserved.<p>
*
* WuHan Inpoint Information Technology Development,Inc.<p>
*
* Author zhu<p>
*
* @version 1.0 2007-2-6
*
* <p>Base on : JDK1.5<p>
*
*/
public class JexcelSample ...{
/** *//**
* 写excel文件
*
*/
public void writeExc(File filename)...{
WritableWorkbook wwb = null;
try
...{
wwb = Workbook.createWorkbook(filename);
}
catch (Exception e)...{
e.printStackTrace();
}
//创建Excel工作表
WritableSheet ws = wwb.createSheet("通讯录", 0);//创建sheet
try ...{
ws.mergeCells(0, 0, 2, 1);//合并单元格(左列，左行，右列，右行)从第1行第1列到第2行第3列
Label header = new Label(0, 0, "通讯录(191026班)", getHeader());
ws.addCell(header);//写入头
Label l = new Label(0, 2, "姓名", getTitle());//第3行
ws.addCell(l);
l = new Label(1, 2, "电话", getTitle());
ws.addCell(l);
l = new Label(2, 2, "地址", getTitle());
ws.addCell(l);
l = new Label(0, 3, "小祝", getNormolCell());//第4行
ws.addCell(l);
l = new Label(1, 3, "1314***0974", getNormolCell());
ws.addCell(l);
l = new Label(2, 3, "武汉武昌", getNormolCell());
ws.addCell(l);
l = new Label(0, 4, "小施", getNormolCell());//第5行
ws.addCell(l);
l = new Label(1, 4, "1347***5057", getNormolCell());
ws.addCell(l);
l = new Label(2, 4, "武汉武昌", getNormolCell());
ws.addCell(l);
ws.setColumnView(0,20);//设置列宽
ws.setColumnView(1,20);
ws.setColumnView(2,40);
ws.setRowView(0,400);//设置行高
ws.setRowView(1,400);
ws.setRowView(2,500);
ws.setRowView(3,500);
ws.setRowView(4,500);
} catch (RowsExceededException e1) ...{
e1.printStackTrace();
} catch (WriteException e1) ...{
e1.printStackTrace();
}
//输出流
try ...{
wwb.write();
} catch (IOException ex) ...{
// TODO 自动生成 catch 块
ex.printStackTrace();
}
//关闭流
try ...{
wwb.close();
} catch (WriteException ex) ...{
// TODO 自动生成 catch 块
ex.printStackTrace();
} catch (IOException ex) ...{
// TODO 自动生成 catch 块
ex.printStackTrace();
}
//outStream.close();
System.out.println("写入成功！ ");
}
public void readExc(File filename) throws BiffException, IOException...{
Workbook wb = Workbook.getWorkbook(filename);
Sheet s = wb.getSheet(0);//第1个sheet
Cell c = null;
int row = s.getRows();//总行数
int col = s.getColumns();//总列数
for(int i=0;i<row;i++)...{
for(int j=0;j<col;j++)...{
c = s.getCell(j,i);
System.out.print(c.getContents()+" ");
}
System.out.println();
}
}
/** *//**
* 设置头的样式
* @return
*/
public static WritableCellFormat getHeader()...{
WritableFont font = new WritableFont(WritableFont.TIMES, 24 ,WritableFont.BOLD);//定义字体
try ...{
font.setColour(Colour.BLUE);//蓝色字体
} catch (WriteException e1) ...{
// TODO 自动生成 catch 块
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) ...{
// TODO 自动生成 catch 块
e.printStackTrace();
}
return format;
}
/** *//**
* 设置标题样式
* @return
*/
public static WritableCellFormat getTitle()...{
WritableFont font = new WritableFont(WritableFont.TIMES, 14);
try ...{
font.setColour(Colour.BLUE);//蓝色字体
} catch (WriteException e1) ...{
// TODO 自动生成 catch 块
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);
} catch (WriteException e) ...{
// TODO 自动生成 catch 块
e.printStackTrace();
}
return format;
}
/** *//**
* 设置其他单元格样式
* @return
*/
public static WritableCellFormat getNormolCell()...{//12号字体,上下左右居中,带黑色边框
WritableFont font = new WritableFont(WritableFont.TIMES, 12);
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);
} catch (WriteException e) ...{
// TODO 自动生成 catch 块
e.printStackTrace();
}
return format;
}
public static void main(String[] args) throws IOException, BiffException...{
JexcelSample js = new JexcelSample();
File f = new File("D:\address.xls");
f.createNewFile();
js.writeExc(f);
js.readExc(f);
}
}
生成的excel表格如下：
下面是另一种写法
其中setHeaderOfTable（）方法的效率不是很好
writeExc（）方法重写加了几个方法
public void writeExc(File filename) throws Exception ...{
WritableWorkbook wwb = null;
try ...{
wwb = Workbook.createWorkbook(filename);
} catch (Exception e) ...{
e.printStackTrace();
}
// 创建Excel工作表
WritableSheet ws = wwb.createSheet("通讯录", 0);// 创建sheet
try ...{
// ws.mergeCells(0, 0, 2, 1);// 合并单元格(左列，左行，右列，右行)从第1行第1列到第2行第3列
// Label header = new Label(0, 0, "通讯录(191026班)", getHeader());
// ws.addCell(header);// 写入头
// Label l = new Label(0, 2, "姓名", getTitle());// 第3行
// ws.addCell(l);
// l = new Label(1, 2, "电话", getTitle());
// ws.addCell(l);
// l = new Label(2, 2, "地址", getTitle());
// ws.addCell(l);
// l = new Label(0, 3, "小祝", getNormolCell());// 第4行
// ws.addCell(l);
// l = new Label(1, 3, "1314***0974", getNormolCell());
// ws.addCell(l);
// l = new Label(2, 3, "武汉武昌", getNormolCell());
// ws.addCell(l);
// l = new Label(0, 4, "小施", getNormolCell());// 第5行
// ws.addCell(l);
// l = new Label(1, 4, "1347***5057", getNormolCell());
// ws.addCell(l);
// l = new Label(2, 4, "武汉武昌", getNormolCell());
// ws.addCell(l);
addLableCellToWritableSheet(ws,0, 0, 2,1,getHeader(),"通讯录(191026班)");
addLableCellToWritableSheet(ws,0, 2,getTitle(),"姓名");
addLableCellToWritableSheet(ws,1, 2,getTitle(),"电话");
addLableCellToWritableSheet(ws,2, 2,getTitle(),"地址");
addLableCellToWritableSheet(ws,0, 3,getNormolCell(),"小祝");
addLableCellToWritableSheet(ws,1, 3,getNormolCell(),"1314***0974");
addLableCellToWritableSheet(ws,2, 3,getNormolCell(),"武汉武昌");
addLableCellToWritableSheet(ws,0, 4,getNormolCell(),"小施");
addLableCellToWritableSheet(ws,1, 4,getNormolCell(),"1347***5057");
addLableCellToWritableSheet(ws,2, 4,getNormolCell(),"武汉武昌");
ws.setColumnView(0, 20);// 设置列宽
ws.setColumnView(1, 20);
ws.setColumnView(2, 40);
ws.setRowView(0, 400);// 设置行高
ws.setRowView(1, 400);
ws.setRowView(2, 500);
ws.setRowView(3, 500);
ws.setRowView(4, 500);
//数据测试
String name = "小祝";
String tel1 = "1314***0974";
String tel2 = "027********";
String tel3 = null;
String add = "武汉武昌";
Integer age = new Integer(10);
Long classno = new Long(1);
Double money = new Double(2500.15);
String[][] strArray =...{...{"姓名","电话","电话","电话","地址","年龄","班级","工资"},
...{"姓名","手机","公司","住宅","地址","年龄","班级","工资"}};
setHeaderOfTable(strArray,ws,0,5,getTitle());
addDataCellToWritableSheet(ws,0,7,0,7,getNormolCell(),name);
addDataCellToWritableSheet(ws,1,7,1,7,getNormolCell(),tel1);
addDataCellToWritableSheet(ws,2,7,2,7,getNormolCell(),tel2);
addDataCellToWritableSheet(ws,3,7,3,7,getNormolCell(),tel3);
addDataCellToWritableSheet(ws,4,7,4,7,getNormolCell(),add);
addDataCellToWritableSheet(ws,5,7,5,7,getNormolCell(),age);
addDataCellToWritableSheet(ws,6,7,6,7,getNormolCell(),classno);
addDataCellToWritableSheet(ws,7,7,7,7,getNormolCell(),money);
} catch (RowsExceededException e1) ...{
e1.printStackTrace();
} catch (WriteException e1) ...{
e1.printStackTrace();
}
// 输出流
try ...{
wwb.write();
} catch (IOException ex) ...{
// TODO 自动生成 catch 块
ex.printStackTrace();
}
// 关闭流
try ...{
wwb.close();
} catch (WriteException ex) ...{
// TODO 自动生成 catch 块
ex.printStackTrace();
} catch (IOException ex) ...{
// TODO 自动生成 catch 块
ex.printStackTrace();
}
// outStream.close();
System.out.println("写入成功！ ");
} 回复 更多评论
# re: 用jexcel读写excel的.xls文件的例子2007-02-07 13:51 | 睿不可当
以下是我添加的方法
// 设置Table中的字符项
private static void setHeaderOfTable(String[][] strArray,WritableSheet ws,
int startColumn,int startRow,WritableCellFormat format) throws Exception...{
List list = new ArrayList();
for(int i = 0;i<strArray.length;i++)...{
List tempList = (ArrayList)getWholeItemCoordinate(strArray[i],i,startColumn,startRow);
for(Iterator it = tempList.iterator();it.hasNext();)...{
Object[] coordinate = (Object[])it.next() ;
list.add(coordinate);
}
}
List resultList = (ArrayList)getCombinedItemCoordinate(list);
for(Iterator it = resultList.iterator();it.hasNext();)...{
Object[] coordinate = (Object[]) it.next();
addLableCellToWritableSheet(ws,
((Integer)coordinate[1]).intValue(),
((Integer)coordinate[0]).intValue(),
((Integer)coordinate[3]).intValue(),
((Integer)coordinate[2]).intValue(),
format,(String)coordinate[4]);
}
}
public static int[] getTheSameItem(String[] strArray,int itemNum,
int startNum) throws Exception ...{
int[] array = new int[2];
for(int i = startNum;i<strArray.length-1;i++)...{
if(strArray[i].equals(strArray[i+1]))...{
itemNum = i+1;
}else...{
startNum = i+1;
break;
}
}
array[0] = itemNum;
array[1] = startNum;
return array;
}
public static List getItemList(String[] strArray) throws Exception ...{
int itemNum = 0;
int startNum = 0;
List list = new ArrayList();
List itemList = new ArrayList();
List startList = new ArrayList();
int strArrayOfLength = strArray.length-1;
do...{
try ...{
int[] array = new int[2];
itemList.add(new Integer(itemNum));
startList.add(new Integer(startNum));
array = getTheSameItem(strArray,itemNum,startNum);
itemNum = array[0];
startNum = array[1];
} catch (Exception e) ...{
e.printStackTrace();
}
}while(itemNum != strArrayOfLength && startNum != strArrayOfLength );
itemList.add(new Integer(itemNum));
startList.add(new Integer(startNum));
list.add(itemList);
list.add(startList);
return list;
} 回复 更多评论
# re: 用jexcel读写excel的.xls文件的例子2007-02-07 13:51 | 睿不可当
public static List getItemCoordinate(String[] strArray)...{
List resultList = new ArrayList();
List itemList = new ArrayList();
List startList = new ArrayList();
try ...{
List list = getItemList(strArray);
itemList = (ArrayList)list.get(0);
startList = (ArrayList)list.get(1);
if(!itemList.isEmpty())...{
int count = itemList.size()-1;
int lastStartNum = strArray.length-1;
for(int i = 0;i<count;i++)...{
Object[] coordinate = new Object[3];
Object[] tempCoordinate = new Object[3];
int itemNum = ((Integer)itemList.get(i)).intValue();
int startNum = ((Integer)startList.get(i)).intValue();
int itemNumNext = ((Integer)itemList.get(i+1)).intValue();
int startNumNext = ((Integer)startList.get(i+1)).intValue();
if(itemNum == itemNumNext && startNumNext != lastStartNum)...{
coordinate[0] = startList.get(i);
coordinate[1] = startList.get(i);
coordinate[2] = strArray[startNum];
resultList.add(coordinate);
}else if(itemNum != itemNumNext && startNumNext != lastStartNum)...{
coordinate[0] = startList.get(i);
coordinate[1] = itemList.get(i+1);
coordinate[2] = strArray[startNum];
resultList.add(coordinate);
}else if(itemNum == itemNumNext && startNumNext == lastStartNum)...{
coordinate[0] = startList.get(i);
coordinate[1] = startList.get(i);
coordinate[2] = strArray[startNum];
resultList.add(coordinate);
tempCoordinate[0] = new Integer(lastStartNum);
tempCoordinate[1] = new Integer(lastStartNum);
tempCoordinate[2] = strArray[lastStartNum];
resultList.add(tempCoordinate);
}else...{
coordinate[0] = startList.get(i);
coordinate[1] = itemList.get(i+1);
coordinate[2] = strArray[startNum];
resultList.add(coordinate);
tempCoordinate[0] = new Integer(lastStartNum);
tempCoordinate[1] = new Integer(lastStartNum);
tempCoordinate[2] = strArray[lastStartNum];
resultList.add(tempCoordinate);
}
}
}
} catch (Exception e) ...{
e.printStackTrace();
}
return resultList;
}
public static List getWholeItemCoordinate(String[] strArray,int i,
int startColumn,int startRow)...{
List list = (ArrayList)getItemCoordinate(strArray);
List resultList = new ArrayList();
for(Iterator it = list.iterator();it.hasNext();)...{
Object[] coordinate = new Object[5];
Object[] temp = (Object[])it.next() ;
coordinate[0] = new Integer(i+startRow);
coordinate[1] = new Integer(((Integer)temp[0]).intValue()+startColumn);
coordinate[2] = new Integer(i+startRow);
coordinate[3] = new Integer(((Integer)temp[1]).intValue()+startColumn);
coordinate[4] = temp[2];
resultList.add(coordinate);
}
return resultList;
}
public static List combineItemCoordinate(List list)...{
List resultList = new ArrayList();
boolean isExistTheSame = false;
for(int i = 0;i<list.size();i++)...{
Object[] coordFirst = (Object[]) list.get(i);
for(int j = i+1;j<list.size();j++)...{
isExistTheSame = false;
Object[] coordNext = (Object[]) list.get(j);
if( ((Integer)coordFirst[1]).intValue() == ((Integer)coordNext[1]).intValue() &&
((Integer)coordFirst[3]).intValue() == ((Integer)coordNext[3]).intValue() &&
((String)coordFirst[4]).equals((String)coordNext[4]))...{
Object[] temp = new Object[5];
temp[0] = coordFirst[0];
temp[1] = coordFirst[1];
temp[2] = coordNext[2];
temp[3] = coordNext[3];
temp[4] = coordNext[4];
resultList.add(temp);
isExistTheSame = true;
list.remove(list.get(j));
break;
}
}
if(!isExistTheSame)...{
resultList.add(coordFirst);
}
}
return resultList;
}
public static List getCombinedItemCoordinate(List list)...{
List resultList = (ArrayList)list;
int startCount = 0;
int endCount = 0;
do...{
startCount = resultList.size();
resultList = combineItemCoordinate(resultList);
endCount = resultList.size();
}while(startCount>endCount);
return resultList;
} 回复 更多评论
# re: 用jexcel读写excel的.xls文件的例子2007-02-07 13:52 | 睿不可当
//将Label添加到WritableSheet
private static void addLableCellToWritableSheet(WritableSheet ws,
int startCoulmn, int startRow, WritableCellFormat format,
String str)throws Exception...{
Label label = new Label(startCoulmn, startRow , str,format);
ws.addCell(label);
}
private static void addLableCellToWritableSheet(WritableSheet ws,
int startCoulmn, int startRow, int endCoulmn,
int endRow, WritableCellFormat format,String str)
throws Exception...{
Label label = new Label(startCoulmn, startRow , str,format);
ws.mergeCells(startCoulmn, startRow, endCoulmn, endRow);
ws.addCell(label);
}
//将数据添加到WritableSheet 如果为Null 就输出空
private static void addDataCellToWritableSheet(WritableSheet ws,
int startCoulmn, int startRow, int endCoulmn,
int endRow, WritableCellFormat format,Object obj)
throws Exception...{
ws.mergeCells(startCoulmn, startRow, endCoulmn, endRow);
if(obj == null)...{
Blank blank = new Blank(startCoulmn,startRow,format);
ws.addCell(blank);
}
else if(obj instanceof String)...{
String antetype = (String) obj;
ws.addCell((WritableCell) NullToEmpty.nullToEmptyValue(antetype,startCoulmn,startRow,format));
}
else if(obj instanceof Double)...{
Double antetype = (Double) obj;
ws.addCell((WritableCell) NullToEmpty.doubleToEmptyValue(antetype,startCoulmn,startRow,format));
}
else if(obj instanceof Integer)...{
Integer antetype = (Integer) obj;
ws.addCell((WritableCell) NullToEmpty.integerToEmptyValue(antetype,startCoulmn,startRow,format));
}
else if(obj instanceof Long)...{
Long antetype = (Long) obj;
ws.addCell((WritableCell) NullToEmpty.longToEmptyValue(antetype,startCoulmn,startRow,format));
}
} 回复 更多评论
# re: 用jexcel读写excel的.xls文件的例子2007-02-07 13:54 | 睿不可当
还有一个工具类
import java.text.DecimalFormat;
import jxl.write.Blank;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
public class NullToEmpty ...{
public static Object nullToEmptyValue(String str,int i1,int i2,WritableCellFormat allThin)...{
if(str==null || "".equals(str))...{
return new Blank(i1,i2,allThin);
}else...{
return new Label(i1, i2, str,allThin);
}
}
public static Object nullToEmptyTempValue(String str,int i1,int i2,WritableCellFormat allThin,String temp)...{
if(str==null || "".equals(str))...{
return new Label(i1, i2, temp,allThin);
}else...{
return new Label(i1, i2, temp+str,allThin);
}
}
public static Object integerToEmptyValue(Integer str,int i1,int i2,WritableCellFormat allThin)...{
if(str==null || str==new Integer(0))...{
return new Blank(i1,i2,allThin);
}else...{
return new Label(i1, i2, str.toString(),allThin);
}
}
public static Object longToEmptyValue(Long str,int i1,int i2,WritableCellFormat allThin)...{
if(str==null || str==new Long(0))...{
return new Blank(i1,i2,allThin);
}else...{
return new Label(i1, i2, str.toString(),allThin);
}
}
public static Object doubleToEmptyValue(Double str,int i1,int i2,WritableCellFormat allThin)...{
if(str==null || str.equals(new Double(0)))...{
return new Blank(i1,i2,allThin);
}else...{
return new Label(i1, i2, parseMoneyFormat(str.doubleValue()),allThin);
}
}
public static String parseMoney(double money)
...{
DecimalFormat format = new DecimalFormat();
format.setMaximumFractionDigits(2);
format.setMinimumFractionDigits(2);
return format.format(money);
}
public static String parseMoneyFormat(double money)
...{
DecimalFormat myformat1 = new DecimalFormat("###,##0.00");
if(money==0D)...{
return "0";
}else...{
return myformat1.format(money);
}
}
public static String parseMoneyFormatStr(Double money)
...{
DecimalFormat myformat1 = new DecimalFormat("###,##0.00");
if(money==null || "".equals(money))...{
return "0";
}else...{
return myformat1.format(money);
}
}
}
posted on 2007-10-23 11:44
飞鸟 阅读(1513)
评论(0) 编辑 收藏 所属分类:
JAVA