学步园

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<PersonVo> personVoList;

public ExportExcel()
{
initdata();
}

/**
* 初始化数据
*/
public void initdata()
{
// 随机函数
Random random = new Random();
personVoList = new ArrayList<PersonVo>();
// 初始化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<allRow; i++)
{
// 取出每一列的值
for (int j=0; j<allColspan; 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);
}

}