private static void setColumnWidth(WritableSheet sheet, int columnWidth[]) {
for (int i = 0; i < columnWidth.length; i++) {
sheet.setColumnView(i, columnWidth[i]);
}
}
public static void wirtePrject(String fileName, String titles[],
int columnWidth[], String result[][]) {
WritableWorkbook workbook;
String worksheet = "result";
try {
ByteArrayOutputStream stream = new ByteArrayOutputStream();
workbook = Workbook.createWorkbook(stream);
WritableSheet sheet = workbook.createSheet(worksheet, 0); // 添加第一个工作表
WritableFont titlefont = new WritableFont(WritableFont.createFont("宋体"), 9, WritableFont.BOLD);
WritableCellFormat titleFormat = new WritableCellFormat(titlefont);// 设置字体
titleFormat.setAlignment(jxl.format.Alignment.CENTRE); // 水平居中
titleFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 垂直居中
titleFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);// 设置边框
titleFormat.setWrap(true);
// 内容格式
WritableFont contentFont = new WritableFont(WritableFont.createFont("Arial"), 9, WritableFont.NO_BOLD);
WritableCellFormat contentFormat = new WritableCellFormat(contentFont);
contentFormat.setAlignment(jxl.format.Alignment.CENTRE); // 水平居中
contentFormat.setWrap(true);// 设置自动换行
contentFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);// 设置边框
// 设置表头
for (int i = 0; i < titles.length; i++) {
Label title = new Label(i, 0, titles[i], titleFormat);// Label(列号,行号// ,内容,格式// )
sheet.addCell(title);
}
if (columnWidth != null && columnWidth.length > 0) {
setColumnWidth(sheet, columnWidth);// 设置列宽
}
if (result != null && result.length > 0) {
for (int i = 0; i < result.length; i++) {
for (int j = 0; j < result[i].length; j++) {
Label label = new Label(j, i + 1, result[i][j], contentFormat);
sheet.addCell(label);
}
}
}
workbook.write();
workbook.close();
InputStream is = new ByteArrayInputStream(stream.toByteArray());
Filedownload.save(is, "application/x-download", fileName);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
// 导出excel
public void onClick$btnExportExcel() {
if(accountVOList.size()==0){
showInformationBox("没有要导出的数据记录");
return;
}
btnflag = true;
executeQuery(null);
String fileName = "accountList.xls";
// 准备设置excel 工作表的标题
String[] title = { "账户申请单编号", "账号", "账户全称", "开户行", "账户余额", "开户时间",
"账户状态","是否可打印"};
int[] columnWidth={20,20,20,20,10,10,10,10};
String result[][]=null;
if (!CollectionUtils.isEmpty(accountVOList)) {
result = new String[accountVOList.size()][8];
for (int i = 0; i < accountVOList.size(); i++) {
AccountDto accountDto=new AccountDto();
try {
ObjectUtil.copyProperties(accountDto, accountVOList.get(i));
} catch (Exception e1) {
e1.printStackTrace();
}
result[i][0] = accountDto.getApplyCode();
result[i][1] = accountDto.getAccountNumber();
result[i][2] = accountDto.getAccountName();
result[i][3] = accountDto.getBankFullName();
result[i][4] =(accountDto.getBalanceAmount()==null?"":accountDto.getBalanceAmount().toString());
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
result[i][5] =(accountDto.getCreateDate()==null?"":sdf.format(accountDto.getCreateDate())) ;
AccountStatusConverter aConverter = new AccountStatusConverter();
result[i][6] =aConverter.coerceToUi(accountDto.getStatus(), null).toString();
result[i][7] =accountDto.getPrintStatus();
}
}
ExcelWirter.wirtePrject(fileName, title, columnWidth, result);
}
或者:
// 导出excel
public void onClick$btnExportExcel() {
if(accountVOList.size()==0){
showInformationBox("没有要导出的数据记录");
return;
}
btnflag = true;
executeQuery(null);
// 准备设置excel 工作表的标题
String[] title = { "账户申请单编号", "账号", "账户全称", "开户行", "账户余额", "开户时间",
"账户状态" };
try {
// 创建Excel工作薄
WritableWorkbook wwb;
JFileChooser chooser = new JFileChooser();
String defaultDisk = "e:/";
String defaultFileName = "账号列表";
chooser.setCurrentDirectory(new File(defaultDisk));//设置默认目录 打开直接默认E盘
chooser.setSelectedFile(new File(defaultFileName)); //设置默认文件名
int result = chooser.showSaveDialog(chooser);
File testfile1 = chooser.getSelectedFile();
if (result == chooser.CANCEL_OPTION) {
return;
}
// 新建立一个jxl文件, 即在 e盘下生成testJXL.xls
OutputStream os = new FileOutputStream(testfile1.getAbsolutePath()+ ".xls");
wwb = Workbook.createWorkbook(os);
// 添加第一个工作表并设置第一个Sheet的名字
WritableSheet sheet = wwb.createSheet("账户信息", 0);
Label label;
for (int i = 0; i < title.length; i++) {
// Label(x,y,z) 代表单元格的第x+1列,第y+1行,内容 z
// 在Label对象的子对象中指明单元格的位置和内容
label = new Label(i, 0, title[i]);
// 定义好的单元格添加到工作表中
sheet.addCell(label);
}
// 下面是填充数据
for (int i = 0; i < accountVOList.size(); i++) {
AccountDto accountDto=new AccountDto();
ObjectUtil.copyProperties(accountDto, accountVOList.get(i));
// 填充账户申请单编号
label = new Label(0, i + 1, accountDto.getApplyCode());
sheet.addCell(label);
// 账号
label = new Label(1, i + 1, accountDto.getAccountNumber());
sheet.addCell(label);
// 账户全称
label = new Label(2, i + 1, accountDto.getAccountName());
sheet.addCell(label);
// 开户行
label = new Label(3, i + 1, accountDto.getBankFullName());
sheet.addCell(label);
// 账户余额
if(null!=accountDto.getBalanceAmount()){
label = new Label(4, i + 1, accountDto.getBalanceAmount().toString());
}else{
label = new Label(4, i + 1, "");
}
sheet.addCell(label);
// 开户时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String newdate = sdf.format(accountDto.getCreateDate());
label = new Label(5, i + 1, newdate);
sheet.addCell(label);
// 账户状态
AccountStatusConverter aConverter = new AccountStatusConverter();
label = new Label(6, i + 1, aConverter.coerceToUi(accountDto.getStatus(), null).toString());
sheet.addCell(label);
}
sheet.setColumnView(0, 20);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 20);
sheet.setColumnView(4, 10);
sheet.setColumnView(5, 10);
sheet.setColumnView(6, 10);
// 写入数据
wwb.write();
// 关闭文件
wwb.close();
} catch (Exception e) {
e.printStackTrace();
}
}