用jexcel读写excel的.xls文件的例子

用jexcel读写excel的.xls文件的例子

有时候我们会需要做excel的报表,下面这个例子详细的给出了读写及定义样式的方法。
(jexcel下载)
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./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("写入成功！\n");
 }
 
 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表格如下：

posted on 2007-02-06 13:21 小祝 阅读(90) 评论(7)  编辑 收藏 引用 所属分类: java技术

FeedBack:

# re: 用jexcel读写excel的.xls文件的例子

2007-02-07 13:48 | 睿不可当

不错 下面是我把你的用我以前的方法改写了一遍
你看看
其中setHeaderOfTable（）方法的效率不是很好
可以优化的 没时间想了 所以就这么着吧:)
我把writeExc（）方法重写了加勒几个方法
其他的没动
  回复  更多评论
  

# re: 用jexcel读写excel的.xls文件的例子

2007-02-07 13:49 | 睿不可当

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("写入成功！\n");
}  回复  更多评论
  

# 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);
}
}
}  回复  更多评论