67,512
社区成员
发帖
与我相关
我的任务
分享
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Random;
import com.neusoft.tqms.vo.OrganVO;
import com.neusoft.tqms.vo.ParameterVO;
import com.neusoft.tqms.vo.TraineesVO;
import com.neusoft.tqms.vo.TrainingProjectVO;
import com.neusoft.tqms.vo.UserVO;
import jxl.Workbook;
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;
import jxl.format.Alignment;
import jxl.format.Colour;
public class Excel {
/**
* 存放文件目录名
*/
public String fullDir="";
//定义File对象
private File f=null;
/**
* 定义导出的文件名
*/
public String fileName="";
//定义WritableWorkbook对象
private WritableWorkbook book=null;
//定义工作表
private WritableSheet sheet=null;
//标题单元格样式
private WritableCellFormat cellTitle=null;
//头部单元格样式
private WritableCellFormat cellHead=null;
//结果集单元格样式
private WritableCellFormat cellResult=null;
//格式字体
private WritableFont wft=null;
private WritableFont wfh=null;
/**
* 构造函数,初始化Excel
*/
public Excel(){
try {
//文件保存目录
String url=Excel.class.getResource("/").toString();
//url:file:/C:/tomcat5.5/webapps/tqms/WEB-INF/classes/
int lastIndex=url.length()-16;
//去掉路径中的"file:/"和"WEB-INF/classes/"
String filePath=url.toString().substring(6,lastIndex)+"export/";
f=new File(filePath);
//在filePath下创建文件夹
f.mkdirs();
//定义日期格式
SimpleDateFormat sdf = new SimpleDateFormat("yyMMddHHmm");
String nameFirst=sdf.format(new java.util.Date());
Random ra = new Random();
String nameLast = String.valueOf(ra.nextInt(999));
//保存的EXCEL文件名
fileName=nameFirst+nameLast;
//EXCEL存放目录(绝对路径)
fullDir = filePath+fileName+".xls";
//创建目录
f.mkdirs();
//创建WritableWorkbook对象
book=Workbook.createWorkbook(new File(fullDir));
//sheet工作表名称
sheet = book.createSheet("导出数据信息", 0);
//初始化标题单元格
cellTitle = new WritableCellFormat();
//初始化头部单元格
cellHead = new WritableCellFormat();
//初始化结果集单元格
cellResult = new WritableCellFormat();
//定义字体样式
wft = new WritableFont(WritableFont.createFont("宋体"),18,WritableFont.BOLD);
//设置颜色
wft.setColour(Colour.BLUE);
wfh = new WritableFont(WritableFont.createFont("宋体"),14,WritableFont.BOLD);
//为标题和表头分配样式
cellTitle.setFont(wft);
//对齐方式
cellTitle.setAlignment(Alignment.CENTRE);
//头部字体格式
cellHead.setFont(wfh);
//头部对齐格式
cellHead.setAlignment(Alignment.CENTRE);
//正文格式
cellResult.setAlignment(Alignment.CENTRE);
cellResult.setWrap(true);
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
/**
*
* Create date:Apr 4, 2011
* Method name:exportUserData
* Description: [导出数据]
* return:void
*/
public void exportUserData(List<UserVO> userList){
int count = userList.size();
try {
//(开始列,开始行,结束列,结束行),合并第一行的第1-8列
sheet.mergeCells(0, 0, 7, 0);
//设置每一列宽度均为20
for(int i=0;i<8;i++){
sheet.setColumnView(i, 20);
}
//标题列
Label la1 = new Label(0,0,"用户信息");
la1.setCellFormat(cellTitle);
Label[] lhead = new Label[8];
//结果集为count行,7列(字段数)
Label[][] lresult = new Label[count][8];
//(列数,行数,单元格内容)
lhead[0] = new Label(0,1,"人员编码");
lhead[1] = new Label(1,1,"机构");
lhead[2] = new Label(2,1,"部门");
lhead[3] = new Label(3,1,"职务");
lhead[4] = new Label(4,1,"姓名");
lhead[5] = new Label(5,1,"性别");
lhead[6] = new Label(6,1,"电话");
lhead[7] = new Label(7,1,"E-MAIL");
for(int i=0;i<count;i++){
UserVO user=userList.get(i);
//(列数,行数,单元格内容)
lresult[i][0] = new Label(0,i+2,user.getUserCode());
//给第二列(机构)赋值
lresult[i][1] = new Label(1,i+2,user.getOrgan());
//给第三列(部门)赋值
lresult[i][2] = new Label(2,i+2,user.getDepartment());
//给第四列(职务)赋值
lresult[i][3] = new Label(3,i+2,user.getPosition());
//给第五列(姓名)赋值
lresult[i][4] = new Label(4,i+2,user.getUserName());
//给第六列(性别)赋值
lresult[i][5] = new Label(5,i+2,user.getUserSex());
//给第七列(电话)赋值
lresult[i][6] = new Label(6,i+2,user.getTelephone());
//给第八列(E-MAIL)赋值
lresult[i][7] = new Label(7,i+2,user.getEmail());
//将字段加入工作表
for(int j=0;j<8;j++){
//结果集单元格格式
lresult[i][j].setCellFormat(cellResult);
//添加到sheet中
sheet.addCell(lresult[i][j]);
}
}
//将头部标题舔到工作表
sheet.addCell(la1);
for(int k=0;k<8;k++){
//设置标题栏样式
lhead[k].setCellFormat(cellHead);
//将标题舔到工作表
sheet.addCell(lhead[k]);
}
//写入数据
book.write();
//关闭文件
book.close();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public boolean exportExcel(ExportCustomerDto expDto)
throws Exception
{
boolean isSuccess = false;
//开始构造导出Excel文件名称
StringBuffer fileNameBuffer = new StringBuffer();
fileNameBuffer.append(CustomerConstants.EXPORT_CUS_FILENAME);
SimpleDateFormat fmtDate = new SimpleDateFormat("yyyyMMddHHmmss");
fileNameBuffer.append(fmtDate.format(new Date()));
fileNameBuffer.append((int) (10F * (new Random()).nextFloat()));
fileNameBuffer.append(".xls");
//构造导出Excel文件名称完成
HSSFWorkbook workbook = new HSSFWorkbook(); // 产生工作簿对象
HSSFSheet sheet = workbook.createSheet(); // 产生工作表对象
// 设置第一个工作表的名称为firstSheet
// 为了工作表能支持中文,设置字符编码为UTF_16
workbook.setSheetName(0, "客户批量导出", HSSFWorkbook.ENCODING_UTF_16);
// 产生一行
HSSFRow row = sheet.createRow((short) 0);
// 产生第一个单元格
String[] tableHead = {"姓名","电话","性别"................};
for (int i = 0; i < tableHead.length; i++)
{
HSSFCell cell = row.createCell((short) i);
// 设置单元格内容为字符串型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 为了能在单元格中写入中文,设置字符编码为UTF_16。
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
// 往第一个单元格中写入信息
cell.setCellValue(tableHead[i]);
}
List list = cusExportDao.cusExportList(expDto);//从数据库查的所有客户List
int rowcount = 0;
for (int j = 0; j < list.size(); j++)
{
ExportCustomerDto dto = (ExportCustomerDto) list.get(j);
rowcount = j + 1;
// 产生一行
HSSFRow row2 = sheet.createRow((short) rowcount);
for (int k = 0; k < tableHead.length; k++)
{
HSSFCell cell2 = row2.createCell((short) k);
// 设置单元格内容为字符串型
cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
// 为了能在单元格中写入中文,设置字符编码为UTF_16。
cell2.setEncoding(HSSFCell.ENCODING_UTF_16);
// 往第一个单元格中写入信息
if (k == 0)
{
cell2.setCellValue(dto.getCustomerName());
}
if (k == 1)
{
cell2.setCellValue(dto.getStrCustomerType());
}
if (k == 2)
{
cell2.setCellValue(dto.getStrSex());
}
if (k == 3)
{
cell2.setCellValue(dto.getPersonCardId());
}
if (k == 4)
{
cell2.setCellValue(dto.getTelPhone());
}
if (k == 5)
{
cell2.setCellValue(dto.getCellPhone());
}
if (k == 6)
{
cell2.setCellValue(dto.getAddress());
}
if (k == 7)
{
cell2.setCellValue(dto.getMailCode());
}
if (k == 8)
{
cell2.setCellValue(dto.getEmail());
}
if (k == 9)
{
cell2.setCellValue(dto.getViteDate());
}
if (k == 10)
{
cell2.setCellValue(dto.getSaleGuid());
}
// ////这里要加入要写到EXCEL表格中去的值
}
}
String fullFileName = System.getProperty("java.io.tmpdir") + "\\" + fileNameBuffer.toString();
expDto.setFilepath(fullFileName);
System.out.println(expDto.getFilepath());
FileOutputStream fOut = new FileOutputStream(fullFileName);
workbook.write(fOut);
fOut.flush();
fOut.close();
isSuccess = true;
return isSuccess;
}