新手个人笔记-java获取Excel模板并生成新的Excel导出

前端大概的样式:

模板: 

 

效果:

HTML:

<span class="l" style="margin-left: 10px;">
	<a class="btn btn-primary radius" data-title="导出明细记录" onclick="downloadExcelDetails()" href="#"><i class="Hui-iconfont">&#xe600;</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);
	}
}

 

 

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读