public Map ExportExcel(String ppath,List<Map> list,String colnames,String coltitles) {
Map outmap = new HashMap<String, Object>();
//ppath:E:\wokespace\myeclipse2014workspace\cswl\WebRoot\
String path = ppath + "temple/t.xlsx";// 文件模板路径
File file = new File(path);
String realPath = ppath + "upload/temple"; // 保存文件的路径
String newFileName = System.currentTimeMillis() + ".xlsx";// 新的文件名
// 判断路径是否存在
File dir = new File(realPath);
if (!dir.exists()) {//路径不存则创建路径
dir.mkdirs();
}
// 写入到新的excel (路径,文件名)
File newFile = new File(realPath, newFileName);
try {
newFile.createNewFile();
//(模板,新文件)
fileChannelCopy(file, newFile); // 复制模板到新文件
} catch (Exception e) {
e.printStackTrace();
}
InputStream is = null;
SXSSFWorkbook workbook = null;
Sheet sheet = null;
try {
is = new FileInputStream(newFile);// 将excel文件转为输入流
XSSFWorkbook workbook1 = new XSSFWorkbook(is);
workbook = new SXSSFWorkbook(workbook1, 100);
sheet = workbook.getSheetAt(0); // 获取第一个sheet
} catch (Exception e1) {
e1.printStackTrace();
}
if (sheet != null) {
try {
// 写数据
FileOutputStream fos = new FileOutputStream(newFile);
String[] colnamearr = colnames.split(",");
String[] coltitlearr = coltitles.split(",");
Row row = sheet.createRow(0); //第一个sheet的第一行
Cell cell =null;
// 标题样式
XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
cellStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(222, 225, 230)));
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// 标题的高度
row.setHeight((short)600);
// 标题字体样式
XSSFFont titleFont = (XSSFFont) workbook.createFont();
titleFont.setBold(true);
titleFont.setFontHeight(11);
cellStyle.setFont(titleFont);
// 垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 横向居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
addBorder(cellStyle);
// 生成标题
for (int i = 0; i < colnamearr.length; i++) {// 表格的第一行是标题
// 单元格宽度加大点
sheet.setColumnWidth(i, 252*10);
cell = row.createCell(i);
// 写入内容
cell.setCellValue(coltitlearr[i]);
// 设置样式
cell.setCellStyle(cellStyle);
}
// 其他单元格的样式,添加边框
XSSFCellStyle cellBodyStyle=(XSSFCellStyle) workbook.createCellStyle();
addBorder(cellBodyStyle);
//生成数据
for(int i=0;i<list.size();i++){
Map map = list.get(i);//每个对象就是一行
row = sheet.createRow(i+1); //因为第一行是标题,所以从第二行开始
for (int j = 0; j < colnamearr.length; j++) {
cell = row.createCell(j);
cell.setCellValue(String.valueOf(map.get(colnamearr[j])==null?"":map.get(colnamearr[j])));
cell.setCellStyle(cellBodyStyle);
}
}
workbook.write(fos);
fos.flush();
fos.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (null != is) {
is.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
//文件删除
outmap.put("url","upload/temple/"+newFileName);
return outmap;
}
public void addBorder(XSSFCellStyle cellStyle){
// 边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
}
controller层进行调用:
public Map DepExport(
@RequestParam Map<String, Object> paramsMap, HttpSession session) {
Map outmap = new HashMap<String, Object>();
try {
String ppath =session.getServletContext().getRealPath("/") ;
List<Map> list = depService.getAllDepList(paramsMap);
String colnames = "";
String coltitles = "";
colnames = "code,name,c_province,c_city,c_area,c_addr,d_addtime";
coltitles = "代码,名称,省,市,区,详细地址,新增时间";
//获取区间范围内的数据
ExportExcel ex = new ExportExcel();
Map o = ex.ExportExcel(ppath, list, colnames, coltitles);
String excelurl = String.valueOf(o.get("url"));
outmap.put("err", "success");
outmap.put("url", excelurl);
} catch (Exception e) {
e.printStackTrace();
outmap.put("err", "导出失败");
}
return outmap;
}
效果: