package com.nzw.test;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import com.nzw.entity.User;
/**
* Excel数据的导入导出
* @author Administrator
*
*/
public class TestExcel {
public static void main(String[] args) {
System.out.println(3&2);
// String filepath="D:"+"\\usering.xls";
// expExcel(filepath);
String filename="D:"+File.separator+"myuser6.xls";
List<User> list = new ArrayList<User>();
User u1 = new User(new Long(1000),"张三","13968556355","湖南岳阳","5845121@qq.com");
User u2 = new User(new Long(1001),"李四","13968556355","湖南益阳","5873122@qq.com");
User u3 = new User(new Long(1002),"王五","13968556355","湖南浏阳","5675121@qq.com");
User u4 = new User(new Long(1003),"赵六","13968556355","湖南邵阳","5848561@qq.com");
User u5 = new User(new Long(1004),"陈七","13968556355","湖南衡阳","5842344@qq.com");
User u6 = new User(new Long(1006),"红霸","13968556355","湖南耒阳","5678121@qq.com");
list.add(u1);
list.add(u2);
list.add(u3);
list.add(u4);
list.add(u5);
list.add(u6);
impExcel(list,filename);
}
/**
* 将Excel中的数据导入到数据库中
* @param filePath 文件名
*/
public static void expExcel(String filePath){
//实例化一个工作簿对象
Workbook workBook;
try {
workBook = Workbook.getWorkbook(new File(filePath));
//获取该工作表中的第一个工作表
Sheet sheet=workBook.getSheet(0);
//获取该工作表的行数，以供下面循环使用
int rowSize=sheet.getRows();
System.out.println("======总共"+rowSize+"行=====");
for(int i=0;i<rowSize;i++){
//姓名
String name = sheet.getCell(0,i).getContents();
//电话
String phone = sheet.getCell(1,i).getContents();
//地址
String address = sheet.getCell(2,i).getContents();
//邮箱
String email = sheet.getCell(3,i).getContents();
//密码
String password = sheet.getCell(4,i).getContents();
System.out.println("===="+name+"===="+phone+"====="+address+"======"+email+"========="+password+"=====");
}
}catch (IOException e) {
System.out.println("未找到Excel文件");
e.printStackTrace();
}
catch (Exception e) {
System.out.println("操作Excel失败");
e.printStackTrace();
}
}
/**
* 将数据库中的数据导入到Excel中
* @param list 数据库中要导入到Excel中的数据
* @param fileName 文件名
*/
public static void impExcel(List<User> list,String fileName){
WritableWorkbook wb = null;
File file = new File(fileName);
int count=0;
try {
//判断文件是否存在
if(!file.exists()){
file.createNewFile();
}
//以fileName为名字创建一个workBook
wb = Workbook.createWorkbook(file);
//创建工作表 ，名字test01 第一个工作簿
WritableSheet sheet = wb.createSheet("test01",0);
//合并单元格，表示从第一列开始到第5列结束，第一行开始到第2行结束进行合并
sheet.mergeCells(0, 0, 4, 1);
//将第一行的高度设置为200
sheet.setRowView(2, 500);
//将第三、四、五列的宽度设置成200
sheet.setColumnView(2, 15);
sheet.setColumnView(3, 15);
sheet.setColumnView(4, 20);
//查询数据库中的数据
//TODO
//单元格内容居中显示
WritableCellFormat cellFormat = new WritableCellFormat();
cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
WritableCellFormat cellFormat2 = new WritableCellFormat();
cellFormat2.setAlignment(jxl.format.Alignment.CENTRE);
/*
* WritableFont.createFont("宋体")：设置字体为宋体
* 10：设置字体大小
* WritableFont.BOLD:设置字体加粗（BOLD：加粗 NO_BOLD：不加粗）
* false：设置非斜体
* UnderlineStyle.NO_UNDERLINE：没有下划线
*/
WritableFont font
= new WritableFont(WritableFont.createFont("宋体"),14,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE);
//要插入到的Excel表格的行号，默认从0开始 表头行
Label head = new Label(0,0,"用户信息数据");
Label id = new Label(0,2,"ID号");//表示第一行第1列
Label username = new Label(1,2,"姓名");//表示第2行第一列
Label phone = new Label(2,2,"电话");//表示第一行第3列
Label address = new Label(3,2,"地址");//表示第一行第4列
Label email = new Label(4,2,"邮箱");//表示第一行第5列
head.setCellFormat(cellFormat2);//设置居中
cellFormat2.setFont(font);//设置字体
head.setCellFormat(cellFormat2);
//使相应的列单元格内容居中显示
id.setCellFormat(cellFormat);
username.setCellFormat(cellFormat);
phone.setCellFormat(cellFormat);
address.setCellFormat(cellFormat);
email.setCellFormat(cellFormat);
sheet.addCell(head);
sheet.addCell(id);
sheet.addCell(username);
sheet.addCell(phone);
sheet.addCell(address);
sheet.addCell(email);
System.out.println("=====总共:"+list.size()+" 行========");
//循环插入到Excel电子表格中
for(int i=0;i<list.size();i++){
Label id_i = new Label(0, i+3, list.get(i).getId()+"");
Label name_i = new Label(1,i+3,list.get(i).getUsername());
Label phone_i = new Label(2,i+3,list.get(i).getUsertel());
Label addr_i = new Label(3,i+3,list.get(i).getUseradd());
Label email_i = new Label(4,i+3,list.get(i).getEmail());
//单元格内容居中显示
id_i.setCellFormat(cellFormat);
name_i.setCellFormat(cellFormat);
phone_i.setCellFormat(cellFormat);
addr_i.setCellFormat(cellFormat);
email_i.setCellFormat(cellFormat);
sheet.addCell(id_i);
sheet.addCell(name_i);
sheet.addCell(phone_i);
sheet.addCell(addr_i);
sheet.addCell(email_i);
count++;
}
System.out.println("==========导入Excel:"+count+" 行==========");
//写进Excel文件中
wb.write();
//关闭Excel工作簿对象
wb.close();
} catch (IOException e) {
System.out.println("创建文件失败!");
}
catch (Exception e) {
System.out.println("操作失败!");
}
}
}