jxl.jar调入java_通过jxl.jar 读取、导出excel的实例代码

package export.excel;

import java.io.BufferedInputStream;

import java.io.BufferedOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.OutputStream;

import java.util.ArrayList;

import java.util.List;

import java.util.Random;

import jxl.Cell;

import jxl.Sheet;

import jxl.Workbook;

import jxl.format.Alignment;

import jxl.format.VerticalAlignment;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import export.vo.PersonVo;

/**导出excel文件

*

* @author 路人甲

*

*/

public class ExportExcel {

public List personVoList;

public ExportExcel()

{

initdata();

}

/**

* 初始化数据

*/

public void initdata()

{

// 随机函数

Random random = new Random();

personVoList = new ArrayList();

// 初始化100个人进集合

PersonVo personVo = null;

for (int i = 0; i < 100; i++)

{

personVo = new PersonVo();

personVo.setId("" + i);

personVo.setName("路人" + i);

if (i%2==0)

{

personVo.setSex("男");

}

else

{

personVo.setSex("女");

}

// 取100岁以内的随机年龄

personVo.setAge("" + random.nextInt(100));

personVoList.add(personVo);

}

}

/**导出数据以Excel格式导出

* exportName 导出文件名称

* @param exportName

*/

public void exportPerson(String exportName)

{

try{

File excelFile = new File(exportName + "Temp.xls");

// 文件格式

WritableCellFormat format = new WritableCellFormat();

// x  靠左

format.setAlignment(Alignment.RIGHT);

// y 靠顶

format.setVerticalAlignment(VerticalAlignment.TOP);

// 创建一个工作文件

WritableWorkbook writableWorkbook = Workbook.createWorkbook(excelFile);

// 创建一个工作簿

WritableSheet sheet1 = writableWorkbook.createSheet("个人信息列表", 0);

//创建行数 设置行的宽度

sheet1.setColumnView(0, 10);

sheet1.setColumnView(1, 10);

sheet1.setColumnView(2, 10);

sheet1.setColumnView(3, 10);

// 设置行的值

sheet1.addCell(new Label(0, 0, "ID", format));

sheet1.addCell(new Label(1, 0, "姓名", format));

sheet1.addCell(new Label(2, 0, "性别", format));

sheet1.addCell(new Label(3, 0, "年龄", format));

// 把集合写入到excel中

int rowNum = 1;

for (PersonVo bean : personVoList)

{

int colspanNum = 0;

sheet1.addCell(new Label(colspanNum, rowNum, bean.getId(), format));

sheet1.addCell(new Label(colspanNum++, rowNum, bean.getName(), format));

sheet1.addCell(new Label(colspanNum++, rowNum, bean.getSex(), format));

sheet1.addCell(new Label(colspanNum++, rowNum, bean.getAge(), format));

rowNum++;

}

writableWorkbook.write();

writableWorkbook.close();

//   如果是在网络下载的，那么就写这些

//            super.getHttpServletResponse().setContentType("application/x-msdownload");

//            String encodetittle = new String(excelName.getBytes("GBK"), "ISO-8859-1");

//            super.getHttpServletResponse().addHeader("Content-Disposition","attachment;filename="+encodetittle+".xls");

FileInputStream finput = new FileInputStream(excelFile);

//            OutputStream output = super.getHttpServletResponse().getOutputStream();

File fout = new File(exportName + ".xls");

OutputStream output = new FileOutputStream(fout);

BufferedInputStream buffin = new BufferedInputStream(finput);

BufferedOutputStream buffout = new BufferedOutputStream(output);

byte[] buffer = new byte[4096];

int count = 0;

while ((count = buffin.read(buffer, 0, buffer.length)) > 0) {

buffout.write(buffer, 0, count);

}

buffin.close();

buffout.close();

finput.close();

output.close();

excelFile.delete();

}

catch (Exception e) {

e.printStackTrace();

}

finally

{

System.out.println("完成导出操作");

}

}

/**导入数据以Excel格式导出

*

* @param importExcel 导入Excel文件名称

*/

public void importPerson(String importExcel)

{

try{

File excelFile = new File(importExcel+".xls");

// 创建一个工作文件

Workbook workbook = Workbook.getWorkbook(excelFile);

// 获得第一个工作簿 这里有两种方法获取sheet表,1为名字，而为下标，从0开始

Sheet sheet = workbook.getSheet(0);

// 总记录数

int allRow = sheet.getRows();

int allColspan = sheet.getColumns();

System.out.println(allRow);

System.out.println(allColspan);

// 取数据

for (int i=0; i

{

// 取出每一列的值

for (int j=0; j

{

//     sheet.getCell(列数, 行数);

Cell cell = sheet.getCell(j, i);

// 打印出该列的值

System.out.print(cell.getContents() + "\t");

}

System.out.println();

}

//关闭

workbook.close();

}catch (Exception e) {

e.printStackTrace();

}

finally

{

System.out.println("完成导入操作");

}

}

/**

* @param args

*/

public static void main(String[] args) {

ExportExcel exportExcel = new ExportExcel();

// 设置路径

String srcPath = "C:/Quarantine/PersonVo";

//  导出

//  exportExcel.exportPerson(srcPath);

// 读取

exportExcel.importPerson(srcPath);

}

}