@RequestMapping("/linkman_export.jhtml")
public void linkmanExport(HttpServletRequest request, HttpServletResponse response, ModelMap model, String linkmanIds) {
try {
//要导出的数据
JSONArray list = linkmanMng.findByIds(linkmanIds);
String path = "/temp";
String fileName = "联系人数据.xls";
String filePath = path;
String sheetName = "客户联系人数据";
//获取数据转换的file文件
File file = exportCustomer(filePath, fileName, sheetName, list);
if (file != null && file.exists()) {
FileDownloadUtil.download(request, response, file.getName(), file, true);
}
} catch (Exception e) {
e.printStackTrace();
}
}
//数据设置文件格式及数据
public File exportCustomer(String filePath, String fileName, String sheetName, JSONArray linkmanList) throws Exception {
List<Label> titles = new ArrayList<Label>();
Integer[] columnView = new Integer[7];
WritableFont font = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false);
WritableCellFormat wcf = new WritableCellFormat(font);
try {
wcf.setBackground(Colour.LIGHT_GREEN);
wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
} catch (WriteException e1) {
e1.printStackTrace();
}
columnView[0] = 40;
titles.add(new Label(0, 0, "联系人名称", wcf));
columnView[1] = 20;
titles.add(new Label(1, 0, "手机号码", wcf));
columnView[2] = 40;
titles.add(new Label(2, 0, "邮箱地址", wcf));
columnView[3] = 30;
titles.add(new Label(3, 0, "固定电话", wcf));
columnView[4] = 20;
titles.add(new Label(4, 0, "生日", wcf));
columnView[5] = 10;
titles.add(new Label(5, 0, "性别", wcf));
columnView[6] = 20;
titles.add(new Label(6, 0, "职位", wcf));
List<String[]> datas = new ArrayList<String[]>();
if (linkmanList != null && linkmanList.size() > 0) {
String[] data = null;
for (int i = 0; i < linkmanList.size(); i++) {
JSONObject obj = linkmanList.getJSONObject(i);
data = new String[7];
data[0] = obj.getString("linkmanName");
data[1] = obj.getString("phone");
data[2] = obj.getString("email");
data[3] = obj.getString("tel");
data[4] = obj.getString("birthday");
if(obj.getString("birthday").equals("0")) {
data[5] = "男";
}else {
data[5] = "女";
}
data[6] = obj.getString("position");
datas.add(data);
}
try {
return createXlsFile(filePath, fileName, sheetName,
titles, datas, columnView, 0);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//转换file 文件
public static File createXlsFile(String filePath,String fileName, String sheetName, List<Label> titles, List<String[]> data, Integer[] columnView, int sheetNum)
throws IOException, RowsExceededException, WriteException {
if (StringUtils.isBlank(filePath)) {
System.out.println("file path has not exist");
return null;
}
if (titles == null || data == null) {
System.out.println("excel title and data not exist");
return null;
}
boolean hasColumView = false;
if (columnView != null && columnView.length > 0) {
hasColumView = true;
}
if (titles.size() != columnView.length) {
System.out.println("title size do not equals columnView length. ");
hasColumView = false;
}
File f = new File(filePath);
if (!f.getParentFile().exists()) {
f.getParentFile().mkdirs();
}
if (!f.exists()) {
f.mkdirs();
}
// 用JXL向新建的文件中添加内容
File file = new File(filePath+"/"+fileName);
OutputStream outf = new FileOutputStream(file);
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(outf);
jxl.write.WritableSheet ws = wwb.createSheet(sheetName, sheetNum);
// 添加标题
for (int i = 0; i < titles.size(); i++) {
if (hasColumView) {
ws.setColumnView(i, columnView[i]);
}
ws.addCell(titles.get(i));
}
// 设置数据
String[] dataLabels = null;
for (int i = 0; i < data.size(); i++) {
dataLabels = data.get(i);
for (int j = 0; j < dataLabels.length; j++) {
ws.addCell(new Label(j, i + 1, dataLabels[j]));
}
}
wwb.write();
wwb.close();
if (outf != null) {
outf.flush();
outf.close();
}
return file;
}
//文件下载功能
public static void download(HttpServletRequest request, HttpServletResponse response, String filename, File file, boolean del) throws IOException {
OutputStream outp = null;
FileInputStream in = null;
try {
String userAgent = request.getHeader("user-agent");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/octet-stream");
// 针对不同的浏览器需要有不同的编码,否则文件名会出现乱码
if (userAgent.toLowerCase().indexOf("firefox") > 0) {
filename = new String(filename.getBytes("UTF-8"), "ISO8859-1");
} else if (userAgent.toUpperCase().indexOf("MSIE") > 0) {
filename = URLEncoder.encode(filename, "UTF-8");
} else if (userAgent.toUpperCase().indexOf("CHORM") > 0) {
filename = new String(filename.getBytes("UTF-8"), "ISO8859-1");
} else {
filename = URLEncoder.encode(filename, "UTF-8");
}
response.addHeader("Content-disposition", "attachment;filename=" + filename);
outp = response.getOutputStream();
in = new FileInputStream(file);
byte[] b = new byte[1024];
int i = 0;
while ((i = in.read(b)) > 0) {
outp.write(b, 0, i);
}
outp.flush();
}
finally {
if (in != null) {
try {
in.close();
in = null;
}
catch (IOException e) {
e.printStackTrace();
}
}
if (outp != null) {
try {
outp.close();
outp = null;
}
catch (IOException e) {
e.printStackTrace();
}
}
if (file != null && del) {
if (file.exists()) {
file.delete();
}
}
}
}