java excel动态导出excel_java后台动态生成导出excel

p

ublic void export(List wechatUserList, HttpServletResponse response) throws IOException, URISyntaxException {

File newFile = createNewFile(getClass().getClassLoader().getResource("../../resources/excel/wechatUserInfoTemplate.xls").toURI().getPath(), new URI("../../resources/xls").getPath());//防止路径中的空格转义为%20

InputStream is = null;

HSSFWorkbook workbook = null;

HSSFSheet sheet = null;

try {

is = new FileInputStream(newFile);// 将excel文件转为输入流

workbook = new HSSFWorkbook(is);// 创建个workbook，

sheet = workbook.getSheetAt(0);

} catch (Exception e1) {

e1.printStackTrace();

}

if (sheet != null) {

try {

// 写数据

FileOutputStream fos = new FileOutputStream(newFile);

HSSFRow row = sheet.createRow(1);

HSSFCell cell = row.createCell(0);

HSSFCellStyle style = workbook.createCellStyle();

style.setAlignment(CellStyle.ALIGN_CENTER);

style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

style.setWrapText(true);

int i = 1;

for (WechatUser wechatUser : wechatUserList) {

List childrenList = wechatUser.getChildrenList();

int size = childrenList.size();

row = sheet.getRow(i);

if (row == null) {

row = sheet.createRow(i);

}

cell = getCell(row,0,cell);

cell.setCellStyle(style);

cell.setCellValue(wechatUser.getNickname());

cell = getCell(row,1,cell);

cell.setCellStyle(style);

cell.setCellValue(wechatUser.getName());

cell = getCell(row,2,cell);

cell.setCellStyle(style);

cell.setCellValue(wechatUser.getPhone());

cell = getCell(row,3,cell);

cell.setCellStyle(style);

cell.setCellValue((null == wechatUser.getSex() ? "" :

(0 == wechatUser.getSex() ? "男" : "女")));

cell = getCell(row,4,cell);

cell.setCellStyle(style);

cell.setCellValue(wechatUser.getGrade().getName());

cell = getCell(row,5,cell);

cell.setCellStyle(style);

cell.setCellValue(wechatUser.getDefaultAddress());

if (1 <= size) {

cell = getCell(row,6,cell);

cell.setCellStyle(style);

cell.setCellValue(childrenList.get(0).getName());

cell = getCell(row,7,cell);

cell.setCellStyle(style);

cell.setCellValue((null == childrenList.get(0).getGender() ? "" :

(0 == childrenList.get(0).getGender() ? "男" : "女")));

cell = getCell(row,8,cell);

cell.setCellStyle(style);

cell.setCellValue(

(null == childrenList.get(0).getBirthDay() ? "" :

DateUtil.dateToStr(

childrenList.get(0).getBirthDay(),

"yyyy-MM-dd")));

}

if (2 == size) {

cell = getCell(row,9,cell);

cell.setCellStyle(style);

cell.setCellValue(childrenList.get(1).getName());

cell = getCell(row,10,cell);

cell.setCellStyle(style);

cell.setCellValue((null == childrenList.get(1).getGender() ? "" :

(0 == childrenList.get(1).getGender() ? "男" : "女")));

cell = getCell(row,11,cell);

cell.setCellStyle(style);

cell.setCellValue((null == childrenList.get(1).getBirthDay() ? "" :

DateUtil.dateToStr(

childrenList.get(1).getBirthDay(),

"yyyy-MM-dd")));

}

i++;

}

//内容

workbook.write(fos);

fos.flush();

fos.close();

// 下载

InputStream fis = new BufferedInputStream(new FileInputStream(

newFile));

byte[] buffer = new byte[fis.available()];

fis.read(buffer);

fis.close();

response.reset();

response.setContentType("text/html;charset=UTF-8");

OutputStream toClient = new BufferedOutputStream(

response.getOutputStream());

response.setContentType("application/x-msdownload");

String fileName = URLEncoder.encode(

"会员信息" + ".xls",

"UTF-8");

fileName = new String(fileName.getBytes(), "ISO8859-1");

response.addHeader("Content-Disposition",

"attachment;filename=" + fileName);

response.addHeader("Content-Length", "" + newFile.length());

toClient.write(buffer);

toClient.flush();

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

if (null != is) {

is.close();

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

// 删除创建的新文件

this.deleteFile(newFile);

}