前端大概的样式:
模板:
效果:
HTML:
<span class="l" style="margin-left: 10px;">
<a class="btn btn-primary radius" data-title="导出明细记录" onclick="downloadExcelDetails()" href="#"><i class="Hui-iconfont"></i> 导出明细记录</a>
</span>
JS:
//导出Excel公共方法
function downloadExcelDetails() {
var logmin = $("#logmin").val();
var logmax = $("#logmax").val();
var compname = $("#compname").val();
var area_name = $("#p_status").val();
var d_type = $("#d_type").val();
//条件判断省略
//.....
window.location.href = "<%=basePath%>inspection/publicDownloadExcel.cyl?sDate="+ logmin +"&eDate="+ logmax +"&villageName="+ compname +"&area_name="+ area_name +"&d_type="+ d_type;
}
控制层( 根据个人需求修改,代码写的有点乱,没有什么设计模式,新手上路请多指教!):
@ResponseBody
@RequestMapping(value = "publicDownloadExcel")
public void downloadExcelQueryData(HttpServletRequest request, HttpServletResponse response, String sDate, String eDate, String villageName,
String d_type, String area_name, Authentication authentication) throws IOException {
SessionUser sessionUser = (SessionUser) authentication.getPrincipal();
String excelPathP = null;
String excelNameP = null;
Map<String, String> resultList = new HashMap<String, String>();
//导出明细
if (area_name != null && !area_name.equals("")) {
//导出明细
String templatefile = request.getServletContext().getRealPath("/");
if (d_type != null && d_type.equals("0")) {
templatefile += "excleTemplate/AreaTemplate.xlsx";//小区
}
if (d_type != null && d_type.equals("1")){
templatefile += "excleTemplate/CompanyTemplate.xlsx";//单位
}
List<ljCheckBodyTarget> resultDetailsList = dailyInService.queryDetailsInfoBydateAndvillageName(sDate, eDate, villageName, d_type, area_name, sessionUser.getLevel(), sessionUser.getId());
resultList = ExportExcelUtil.putInto1Excel(request.getServletContext(), null, resultDetailsList, templatefile, d_type);
excelPathP = resultList.get("path");
excelNameP = resultList.get("name");
}
//文件流的方式导出Excel
try {
// 输出响应正文的输出流
OutputStream out;
// 读取本地文件的输入流
InputStream in;
// 获得本地输入流
File file = new File("C:\\"+ excelPathP + excelNameP);
in = new FileInputStream(file);
// 设置响应正文的MIME类型
response.setContentType("application/octet-stream;charset=UTF-8");
String fileName = new String(excelNameP.getBytes("gb2312"), "iso8859-1");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
// 把本地文件发送给客户端
out = response.getOutputStream();
int byteRead = 0;
byte[] buffer = new byte[512];
while ((byteRead = in.read(buffer)) != -1) {
out.write(buffer, 0, byteRead);
}
in.close();
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
logger.error("文件下载出现异常", e);
}
}
生成Excel:
package com.cyl.util;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.imageio.ImageIO;
import javax.servlet.ServletContext;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.cyl.model.ljCheckBodyTarget;
import com.cyl.model.ljCheckInstanceM;
public class ExportExcelUtil {
private static Logger log = Logger.getLogger(ExportExcelUtil.class);
/**
* 利用List<LinkedHashMap>导出Excel,保证顺序,纯导出数据
*
* @param context
* 上下文会话对象
* @param dicument
* 生成文件的目錄文件名,參數為空時默認為excel
* @param list
* Unallowable类数据集
* @param templatefile
* 模板路径
* @param d_type 0区域 1单位
*
* @return path返回路徑;name文件名;
*/
public static Map<String, String> putInto1Excel(ServletContext context, String dicument,
List<ljCheckBodyTarget> list, String templatefile, String d_type){
Map<String, String> result = new HashMap<String, String>();
InputStream ins = null;
XSSFWorkbook wb = null;
XSSFSheet sheet = null;
XSSFRow row = null;
FileOutputStream out = null;
Cell cell = null;
if(!new File(templatefile).exists()){
System.out.println("文件不存在,已创建");
wb = new XSSFWorkbook();
sheet = wb.createSheet();
} else {
System.out.println("文件存在,读取流");
try {
ins = new FileInputStream(templatefile);
wb = new XSSFWorkbook(ins);
sheet = wb.getSheetAt(0);
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
int currentLastRowIndex = sheet.getLastRowNum();
row = sheet.createRow(currentLastRowIndex + 1);
try {
String fileName = TUtil.format("yyyy_MM_dd_HHmmssSSSSSS");
String filePath = null;
for (int i = 0; i < list.size(); i++) {
ljCheckBodyTarget data = list.get(i);
if (i == 0) {
//文件名称拼接我所要命名的文件名
fileName += "-"+ data.getAreaName();
fileName += ".xlsx";
row = sheet.getRow(2);//获取第二行
SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
SimpleDateFormat mm = new SimpleDateFormat("MM");
cell = row.getCell(0);//第0列
cell.setCellValue(sdf.format(data.getCheckDate()) +"年"+ mm.format(data.getCheckDate()) +"月"+ data.getAreaName());
cell.setCellStyle(cellSetStyle(wb, 0, 1));//Excel样式生成调用
}
row = sheet.createRow(currentLastRowIndex + i + 1);
if (d_type != null) {
cell = row.createCell(0);
cell.setCellValue( i + 1);
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(1);
cell.setCellValue(data.getAreaName());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(2);
cell.setCellValue(data.getVillageType());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(3);
cell.setCellValue(data.getVillageName());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(4);
cell.setCellValue(data.getTotalScore());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(5);
if (Double.parseDouble(data.getTotalScore()) >= 90) {
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell.setCellValue("达标");
}else if (Double.parseDouble(data.getTotalScore()) >= 80){
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell.setCellValue("未达标");
}else {
cell.setCellStyle(cellSetStyle(wb, 1, 0));
cell.setCellValue("未达标");
}
cell = row.createCell(6);
cell.setCellValue(data.getAttr1());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(7);
cell.setCellValue(data.getAttr2());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(8);
cell.setCellValue(data.getAttr3());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(9);
cell.setCellValue(data.getAttr4());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(10);
cell.setCellValue(data.getAttr5());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(11);
cell.setCellValue(data.getAttr6());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(12);
cell.setCellValue(data.getAttr7());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(13);
cell.setCellValue(data.getAttr8());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(14);
cell.setCellValue(data.getAttr9());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(15);
cell.setCellValue(data.getAttr10());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(16);
cell.setCellValue(data.getAttr11());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
if (!d_type.equals("1")) {//1代表单位
cell = row.createCell(17);
cell.setCellValue(data.getAttr12());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(18);
cell.setCellValue(data.getAttr13());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(19);
cell.setCellValue(data.getAttr14());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(20);
cell.setCellValue(data.getAttr15());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(21);
cell.setCellValue(data.getAttr16());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(22);
cell.setCellValue(data.getAttr17());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(23);
cell.setCellValue(data.getAttr18());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(24);
cell.setCellValue(data.getAttr19());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(25);
cell.setCellValue(data.getAttr20());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(26);
cell.setCellValue(data.getAttr21());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(27);
cell.setCellValue(data.getAttr22());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(28);
cell.setCellValue(data.getAttr23());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(29);
cell.setCellValue(data.getAttr24());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(30);
cell.setCellValue(data.getAttr25());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(31);
cell.setCellValue(data.getAttr26());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(32);
cell.setCellValue(data.getAttr27());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(33);
cell.setCellValue(data.getAttr28());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(34);
cell.setCellValue(data.getAttr29());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(35);
cell.setCellValue(data.getAttr30());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(36);
cell.setCellValue(data.getAttr31());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
cell = row.createCell(37);
cell.setCellValue(data.getAttr32());
cell.setCellStyle(cellSetStyle(wb, 0, 0));
}
}
}
//String mes = context.getRealPath("/");
String relpath = (StringUtils.isBlank(dicument) ? "excel" : dicument) + "/";
filePath = "C:\\" + relpath;// 文件存放路径
File fileDir = new File(filePath);
if (!(fileDir.exists() && fileDir.isDirectory())) {
new File(filePath).mkdirs();
}
result.put("path", relpath);
result.put("name", fileName);
out = new FileOutputStream(filePath + fileName);
wb.write(out);
} catch (FileNotFoundException e) {
e.printStackTrace();
log.error(e.getMessage());
} catch (IOException e) {
e.printStackTrace();
log.error(e.getMessage());
}finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return result;
}
/**
* 显示单元格,设置字体颜色
* @param wb
* @param mark
* @return
*/
public static CellStyle cellSetStyle(XSSFWorkbook wb, int mark, int boldMark) {
//创建样式1
XSSFCellStyle style = wb.createCellStyle();
//设置边框样式
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
// 指定单元格居中对齐
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
if (mark != 0 || boldMark != 0) {
//设置字体
XSSFFont font = wb.createFont();
if (mark == 1) {//颜色标记 1 红色
font.setColor((short)60);//设置字体颜色
}
if (boldMark == 1) {//标记 1为加粗
//自动换行
style.setWrapText(true);
//设置字体样式
font.setFontName("宋体");
//设置字体大小
font.setFontHeightInPoints((short) 9);
//设置字体
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//粗体显示
}
style.setFont(font);//选择需要用到的字体格式
}
return style;
}
}
基础类(根据个人需求取需要的):
package com.cyl.util;
import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.StringTokenizer;
import org.apache.log4j.Logger;
/**
* 基础类
*
* @author Qiang1_Zhang
*/
public class TUtil {
static Logger log = Logger.getLogger(TUtil.class);
/**
* 日期转换函数
*
* @param format
* 需要转换的格式
* @return 转换后的日期
*/
public static String format(String format) {
return new SimpleDateFormat(format).format(new Date());
}
/**
* 日期转换函数
*
* @param format
* 需要转换的格式
* @return 转换后的日期
*/
public static String format(Date date, String format) {
return new SimpleDateFormat(format).format(date);
}
/**
* 打印函数
*
* @param str
* 对象类型
*/
public static void print(Object str) {
System.out.println(str);
}
/**
* 计算距今指定天数的日期
*
* @param day
* 相差的天数,可为负数
* @return 计算之后的日期
*/
public static String GetDay(int day) {
Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cal.setTime(new Date());// 设置日历时间
cal.add(Calendar.DAY_OF_MONTH, day);// 天数
String strDate = sdf.format(cal.getTime());// 得到你想要的天数
return strDate;
}
/**
* 获取报表模板路径
*
* @return
*/
public static String getURL() {
String dir = System.getProperty("user.dir");
print("dir=" + dir);
dir = dir.substring(0, dir.lastIndexOf("\\"));
String filePath = dir;
return filePath;
}
/**
* String类型日期转换为长整型
*
* @param date
* String类型日期
* @param format
* 日期格式
* @return long
*/
public static long strDateToLong(String date, String... format) {
String format1 = null;
if (format.length != 0) {
format1 = format[0];
} else {
format1 = "yyyy-MM-dd HH:mm:ss";
}
String sDt = date;
SimpleDateFormat sdf = new SimpleDateFormat(format1);
long lTime = 0;
try {
Date dt2 = sdf.parse(sDt);
lTime = dt2.getTime();
print(lTime);
} catch (ParseException e) {
e.printStackTrace();
}
return lTime;
}
public static void longToString(long l) {
format("");
}
/**
* 获取文件创建时间
*
* @param file
* 文件目录
*/
public static String getCreateTime(File file) {
// file = new File("e:/1.xls");
String date = "";
// file.lastModified();
try {
Process process = Runtime.getRuntime().exec(
"cmd.exe /c dir " + file.getAbsolutePath() + "/tc");
InputStream is = process.getInputStream();
BufferedReader br = new BufferedReader(new InputStreamReader(is));
for (int i = 0; i < 5; i++) {// 前五行是其他的信息
br.readLine();
}
String createDateLine = br.readLine();
StringTokenizer tokenizer = new StringTokenizer(createDateLine);
date = tokenizer.nextToken() + " " + tokenizer.nextToken();
br.close();
// print(date);
} catch (IOException e) {
log.error("" + e.getMessage());
}
return date;
}
/**
* 获取文件最后修改时间
*
* @param filePath
* 文件目录
*/
public static void getLastModifyTime(File filePath) {
filePath = new File(
"\\\\10.131.18.8\\rt3生產機種\\ProductionReprot\\TraceAlterReprot-reprot");
File[] list = filePath.listFiles();
// for(File file : list){
// print(file.getAbsolutePath()+"\tcreate time:"+getCreateTime(file));
// }
for (File file : list) {
Date date = new Date(file.lastModified());
print(format(date, "yyyy-MM-dd"));
}
}
public static void getFile() {
String root = "\\\\10.131.18.8\\rt3生產機種\\ProductionReprot";
File filePath = new File(root);
File[] list = filePath.listFiles();
for (File file : list) {
print(file.getName()
+ "\t"
+ new File(file.getAbsolutePath() + "\\"
+ TUtil.format("yyyy-MM-dd") + ".xls").exists());
}
}
static void test() {
String today = TUtil.format("yyyy-MM-dd");
String dest = ReadProperties.ReadProprety("server.report.path")
+ "TraceAlterReprot-reprot" + "\\" + today + "\\";
print(dest);
File dir = new File(dest);// 创建当天目录
if (!dir.exists()) {
dir.mkdir();
}
}
public static void getTimeDifference() {
try {
Date d1 = new SimpleDateFormat("yyyy-MM-dd").parse("2014-09-15");
Date d2 = new SimpleDateFormat("yyyy-MM-dd").parse("2014-09-14");
print((d2.getTime() - d1.getTime()) / 1000 / 60 / 60 / 24);
} catch (ParseException e) {
e.printStackTrace();
}
}
public List<String> distinctList(List<String> list) {
HashSet<String> h = new HashSet<String>(list);
list.clear();
list.addAll(h);
return list;
}
public List<Object> removeDuplicate(List<Object> list) {
HashSet<Object> h = new HashSet<Object>(list);
list.clear();
list.addAll(h);
return list;
}
/**
* 获取四舍五入的整数
* @param input 乘数
* @param rate 比率
* @return 取整后的结果
*/
public double getRound(int input,double rate){
double tmp = input * rate;
return Math.round(tmp);
}
/**
* 获取四舍五入的整数
* @param input 乘数
* @param rate 比率
* @return 取整后的结果
*/
public double ceil(int input,double rate){
double tmp = input * rate;
return Math.ceil(tmp);
}
}