java导出excel并下载_超详细的java生成excel文件并下载

packagecom.test.demo.controllers;importcom.test.demo.domain.entities.Address;importcom.test.demo.services.ExcelService;importjxl.Workbook;importjxl.format.Alignment;importjxl.format.Border;importjxl.format.BorderLineStyle;importjxl.format.Colour;import jxl.format.*;importjxl.format.VerticalAlignment;import jxl.write.*;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RestController;importorg.springframework.web.context.request.RequestContextHolder;importorg.springframework.web.context.request.ServletRequestAttributes;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importjava.io.File;importjava.io.FileInputStream;importjava.io.InputStream;importjava.io.OutputStream;importjava.text.SimpleDateFormat;importjava.util.List;/***@authordyh

* @create 2018-07-14 下午8:20

* @desc excle表格功能编写

**/@RestController

@RequestMapping("/excel")public classExcelController {

@AutowiredprivateExcelService excelService;/*** 下载文件

*

*@return

*/@RequestMapping({"/download"})public voiddownload() {

ServletRequestAttributes requestAttributes=(ServletRequestAttributes) RequestContextHolder.getRequestAttributes();

HttpServletResponse response=requestAttributes.getResponse();

HttpServletRequest request=requestAttributes.getRequest();//文件名

String filename = "地址列表.xls";try{//写到服务器上

String path = request.getSession().getServletContext().getRealPath("") + "/" +filename;//写到服务器上(这种测试过，在本地可以，放到linux服务器就不行)//String path = this.getClass().getClassLoader().getResource("").getPath()+"/"+filename;

File name= newFile(path);//创建写工作簿对象

WritableWorkbook workbook =Workbook.createWorkbook(name);//工作表

WritableSheet sheet = workbook.createSheet("地址列表", 0);//设置字体;

WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

WritableCellFormat cellFormat= newWritableCellFormat(font);//设置背景颜色;

cellFormat.setBackground(Colour.WHITE);//设置边框;

cellFormat.setBorder(Border.ALL, BorderLineStyle.DASH_DOT);//设置文字居中对齐方式;

cellFormat.setAlignment(Alignment.CENTRE);//设置垂直居中;

cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);//分别给1,5,6列设置不同的宽度;

sheet.setColumnView(0, 15);

sheet.setColumnView(4, 60);

sheet.setColumnView(5, 35);//给sheet电子版中所有的列设置默认的列的宽度;

sheet.getSettings().setDefaultColumnWidth(20);//给sheet电子版中所有的行设置默认的高度，高度的单位是1/20个像素点,但设置这个貌似就不能自动换行了//sheet.getSettings().setDefaultRowHeight(30 * 20);//设置自动换行;

cellFormat.setWrap(true);//单元格

Label label0 = new Label(0, 0, "ID", cellFormat);

Label label1= new Label(1, 0, "省", cellFormat);

Label label2= new Label(2, 0, "市", cellFormat);

Label label3= new Label(3, 0, "区", cellFormat);

Label label4= new Label(4, 0, "详细地址", cellFormat);

Label label5= new Label(5, 0, "创建时间", cellFormat);

sheet.addCell(label0);

sheet.addCell(label1);

sheet.addCell(label2);

sheet.addCell(label3);

sheet.addCell(label4);

sheet.addCell(label5);//给第二行设置背景、字体颜色、对齐方式等等;

WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);

WritableCellFormat cellFormat2= newWritableCellFormat(font2);//设置文字居中对齐方式;

cellFormat2.setAlignment(Alignment.CENTRE);//设置垂直居中;

cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);

cellFormat2.setBackground(Colour.WHITE);

cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);

cellFormat2.setWrap(true);//记录行数

int n = 1;//查找所有地址

List

addressList =excelService.findAll();if (addressList != null && addressList.size() > 0) {//遍历

for(Address a : addressList) {

SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String createTime=sdf.format(a.getCreateTime());

Label lt0= new Label(0, n, a.getId() + "", cellFormat2);

Label lt1= new Label(1, n, a.getProvince(), cellFormat2);

Label lt2= new Label(2, n, a.getCity(), cellFormat2);

Label lt3= new Label(3, n, a.getArea(), cellFormat2);

Label lt4= new Label(4, n, a.getAddress(), cellFormat2);

Label lt5= new Label(5, n, createTime, cellFormat2);

sheet.addCell(lt0);

sheet.addCell(lt1);

sheet.addCell(lt2);

sheet.addCell(lt3);

sheet.addCell(lt4);

sheet.addCell(lt5);

n++;

}

}//开始执行写入操作

workbook.write();//关闭流

workbook.close();

}catch(Exception e) {

e.printStackTrace();

}//第六步，下载excel

OutputStream out= null;try{//1.弹出下载框，并处理中文

/**如果是从jsp页面传过来的话，就要进行中文处理，在这里action里面产生的直接可以用

* String filename = request.getParameter("filename");*/

/**if (request.getMethod().equalsIgnoreCase("GET")) {

filename = new String(filename.getBytes("iso8859-1"), "utf-8");

}*/response.addHeader("content-disposition", "attachment;filename="

+ java.net.URLEncoder.encode(filename, "utf-8"));//2.下载

out =response.getOutputStream();

String path3= request.getSession().getServletContext().getRealPath("") + "/" +filename;//inputStream：读文件，前提是这个文件必须存在，要不就会报错

InputStream is = newFileInputStream(path3);byte[] b = new byte[4096];int size =is.read(b);while (size > 0) {

out.write(b,0, size);

size=is.read(b);

}

out.close();

is.close();

}catch(Exception e) {

e.printStackTrace();

}

}

}