Java完成excel表格导出

来源：互联网发布：数据分析职业发展知乎编辑：程序博客网时间：2024/05/20 14:16

public class ExcleService {
public boolean exportExcle(List<Confirm> list){
try {
// 新建excel的工作薄文件
Date date = new Date();
DateFormat format = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String time = format.format(date);
OutputStream os=new FileOutputStream("d:/confirm/确认单"+time+".xls");
WritableWorkbook book = Workbook.createWorkbook(os);
// 生成名为"第一页"的工作表，参数0表示这是第一页
WritableSheet sheet1 = book.createSheet("第一页", 0);
/***********start第一行（各个列的标题）************/
//第一列第一行(0,0)
Label label1 = new Label(0, 0, "编号");
// 将定义好的单元格添加到工作表中
sheet1.addCell(label1);
//第二列第一行
Label label2 = new Label(1, 0, "基金账号");
sheet1.addCell(label2);
//第三列第一行
Label label3 = new Label(2, 0, "交易账号");
sheet1.addCell(label3);
Label label4 = new Label(3, 0, "确认编号");
sheet1.addCell(label4);
Label label5 = new Label(4, 0, "交易账户名称");
sheet1.addCell(label5);
Label label6 = new Label(5, 0, "客户类别");
sheet1.addCell(label6);
Label label7 = new Label(6, 0, "业务名称");
sheet1.addCell(label7);
Label label8 = new Label(7, 0, "确认标志");
sheet1.addCell(label8);
Label label9 = new Label(8, 0, "申请编号");
sheet1.addCell(label9);
Label label10 = new Label(9, 0, "基金代码");
sheet1.addCell(label10);
Label label11 = new Label(10, 0, "基金净值");
sheet1.addCell(label11);
Label label12 = new Label(11, 0, "基金名称");
sheet1.addCell(label12);
Label label13 = new Label(12, 0, "申请日期");
sheet1.addCell(label13);
Label label14 = new Label(13, 0, "确认日期");
sheet1.addCell(label14);
Label label15 = new Label(14, 0, "申请金额");
sheet1.addCell(label15);
Label label16 = new Label(15, 0, "申请份额");
sheet1.addCell(label16);
Label label17 = new Label(16, 0, "确认份额");
sheet1.addCell(label17);
Label label18 = new Label(17, 0, "确认金额");
sheet1.addCell(label18);
Label label19 = new Label(18, 0, "发送传真号");
sheet1.addCell(label19);
Label label20 = new Label(19, 0, "更新日期");
sheet1.addCell(label20);
Confirm confirm = new Confirm();
int n=1;
for(int i=1; i<=list.size(); i++){
confirm=list.get(i-1);
if(confirm!=null){
for(int j=0 ;j<21 ;j++) { //控制列数
if(j==0) {
Label label = new Label(j, n,confirm.getInt("id").toString());
sheet1.addCell(label);
}
if(j==1) {
Label label = new Label(j, n,confirm.getStr("fundAccount"));
sheet1.addCell(label);
}
if(j==2) {
Label label = new Label(j, n,confirm.getStr("volumeAccount"));
sheet1.addCell(label);
}
if(j==3) {
Label label = new Label(j, n,confirm.getStr("affirmNumber"));
sheet1.addCell(label);
}
if(j==4) {
Label label = new Label(j, n,confirm.getStr("volumeName"));
sheet1.addCell(label);
}
if(j==5) {
Label label = new Label(j, n,confirm.getStr("clientType"));
sheet1.addCell(label);
}
if(j==6) {
Label label = new Label(j, n,confirm.getStr("businessName"));
sheet1.addCell(label);
}
if(j==7) {
Label label = new Label(j, n,confirm.getStr("affirmSign"));
sheet1.addCell(label);
}
if(j==8) {
Label label = new Label(j, n,confirm.getStr("applyCode"));
sheet1.addCell(label);
}
if(j==9) {
Label label = new Label(j, n,confirm.getStr("fundCode"));
sheet1.addCell(label);
}
if(j==10) {
Label label = new Label(j, n,confirm.getStr("fundNetValue"));
sheet1.addCell(label);
}
if(j==11) {
Label label = new Label(j, n,confirm.getStr("fundName"));
sheet1.addCell(label);
}
if(j==12) {
Label label = new Label(j, n,confirm.getStr("applyDate"));
sheet1.addCell(label);
}
if(j==13) {
Label label = new Label(j, n,confirm.getStr("affirmDate"));
sheet1.addCell(label);
}
if(j==13) {
Label label = new Label(j, n,confirm.getStr("applyMoney"));
sheet1.addCell(label);
}
if(j==14) {
Label label = new Label(j, n,confirm.getStr("applyShare"));
sheet1.addCell(label);
}
if(j==15) {
Label label = new Label(j, n,confirm.getStr("affirmShare"));
sheet1.addCell(label);
}
if(j==16) {
Label label = new Label(j, n,confirm.getStr("affirmMoney"));
sheet1.addCell(label);
}
if(j==17) {
Eastfax ea=Eastfax.dao.findById(confirm.getInt("faxId"));
Label label = new Label(j, n,ea.getStr("faxNum"));
sheet1.addCell(label);
}
if(j==18) {
Label label = new Label(j, n,confirm.getStr("updateDate"));
sheet1.addCell(label);
}
}
confirm.set("exportStatus", "1");
confirm.update();
n++;
}
}
// 写入数据并关闭文件
book.write();
book.close();
os.close();
return true;
} catch (Exception e) {
System.out.println("生成信息表(Excel格式)时出错：");
e.printStackTrace();
return false;
}
}

}

//文件下载
public void download(String path, HttpServletResponse response) {
try {
// path是指欲下载的文件的路径。
File file = new File(path);
// 取得文件名。
String filename = file.getName();
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
response.addHeader("Content-Disposition", "attachment;filename="+ new String(filename.getBytes()));
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=gb2312");
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}

阅读全文

0 0