精华内容
下载资源
问答
  • 批量生成excel文件并压缩下载到浏览器 问题叙述 以往批量生成文件并压缩下载到浏览器可分为三步操作。第一,生成excel文件并写到当地磁盘中;第二,将磁盘中的excel打成压缩包;第三,将压缩包相应到浏览器,并下载...

    批量生成excel文件并压缩下载到浏览器

    问题叙述

    以往批量生成文件并压缩下载到浏览器可分为三步操作。第一,生成excel文件并写到当地磁盘中;第二,将磁盘中的excel打成压缩包;第三,将压缩包相应到浏览器,并下载到客户端。由于现在业务需求的需要,一步完成这项操作,即生成excel文件们不得保存到本地内存再压缩,而是直接在内存中以流的形式生成压缩包。

    开发思路

    下面就是我的开发思路。
    在程序中生成Excel文件,放到ByteOutputStream中,再把ByteOutputStream放到byte[]数组中,然后把数组写到zipEntry中,最后把zip压缩包的流都缓存到新的ByteOutputStream中(ByteArrayOutputStream也可以),把新建的流转成byte[],并响应到浏览器。

    实例代码

    control层

    @Controller
    public class ExcelController{
    	@RequestMapping(value = "downloadAllRecord", produces = "text/html;charset=UTF-8")
    	@DataSource("dataSourceLjl")
    	public void downloadExcelZip(HttpServletResponse response, HttpServletRequest request) {
    		try {
    			long startTime = System.currentTimeMillis();
    			appointmentService.getImformationToExcel( response, request);
    			long endTime = System.currentTimeMillis();
    			// 程序运行时间,据此可做适当优化
    			// 通常都是优化sql的慢查询
    			System.out.println(endTime - startTime); 
    
    		}  catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    }
    

    service层

    @Service
    public class ExcelService{
    	/**
    	 * 将数据库中的预约信息按领取地点分成多个excel表格并压缩输出
    	 */
    	public void getImformationToExcel(
    			HttpServletResponse response, HttpServletRequest request) {
    		ServletOutputStream sos = null;
    		ZipOutputStream zipOutputStream = null;
    
    		try {
    			// 输出流
    			sos = response.getOutputStream();
    			zipOutputStream = new ZipOutputStream(sos);
    			// 压缩文件名
    			String zipname = "zipFileName.zip";
    			String agent = request.getHeader("USER-AGENT");
    			//这里有个坑,卡了我一下午,火狐浏览器需要特殊处理,不然下载的文件没有后缀名
    			if (agent != null && agent.toLowerCase().indexOf("firefox") > 0) {
    				zipname = "=?UTF-8?B?" + (new String(Base64Utils.encodeToString(zipname.getBytes("UTF-8")))) + "?=";
    			} else {
    				zipname = java.net.URLEncoder.encode(zipname, "UTF-8");
    			}
    			response.setHeader("Content-Disposition", "attachment;filename=" + zipname);
    			
    			for (String place : placeList) {
    				XSSFWorkbook workbook = new XSSFWorkbook();
    				// 构建excel  我前面的博客有写如何生成excel的 这里就不重复叙述了
    				workbook = FilepersonalUtil.generateExcel();
    				// 生成的每一个excel文件名
    				String fileName = "excel.xlsx";
    				// 将生成的excel生成流
    				ByteArrayOutputStream baos = new ByteArrayOutputStream();
    				workbook.write(baos);
    				// 压缩单个excel文件的输出流 到zip输出流
    				FilepersonalUtil.compressFileToZipStream(zipOutputStream, baos, fileName);
    				baos.close();
    			}
    			// 将zip输出流冲刷出去,将所有缓冲的数据强制发送到目的地。
    			zipOutputStream.flush();
    			zipOutputStream.close();
    			sos.close();
    		} catch (Exception e) {
    			MyLoggerUtils.logError(e.getMessage());
    		} finally {
    			try {
    				if (zipOutputStream != null)
    					zipOutputStream.close();
    				if (sos != null)
    					sos.close();
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    }
    

    FilepersonalUtil 工具类(重点)

    public class FilepersonalUtil{
    	/**
    	 * 压缩单个excel文件的输出流 到zip输出流,注意zipOutputStream未关闭,需要交由调用者关闭之
    	 * 
    	 * @param zipOutputStream   zip文件的输出流
    	 * @param excelOutputStream excel文件的输出流
    	 * @param excelFilename     文件名可以带目录,例如 TestDir/test1.xlsx
    	 */
    	public static void compressFileToZipStream(ZipOutputStream zipOutputStream, ByteArrayOutputStream excelOutputStream,
    			String excelFilename) {
    		byte[] buf = new byte[1024];
    		try {
    			// 将需要压缩的excel流转成字节数组
    			byte[] content = excelOutputStream.toByteArray();
    			ByteArrayInputStream is = new ByteArrayInputStream(content);
    			BufferedInputStream bis = new BufferedInputStream(is);
    			// 添加zip实体到zip流中
    			zipOutputStream.putNextEntry(new ZipEntry(excelFilename));
    			// 将字节从文件传输到ZIP文件
    			int len;
    			while ((len = bis.read(buf)) > 0) {
    				zipOutputStream.write(buf, 0, len);
    			}
    			zipOutputStream.closeEntry();
    			bis.close();// 记得关闭流
    			is.close();
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    	}
    }
    
    展开全文
  • POI批量导出Excel ZIP打包下载

    千次阅读 2018-11-07 17:04:13
    POI批量导出Excel ZIP打包下载 1.公共抽象导出Excel类  需要自己实现两个抽象方法: getColumValueForColunmName : 扩展方法:根据名称判断来做值得转换 比如:if(key==date) value='2018-11-07' ...

    POI批量导出Excel ZIP打包下载

    1.公共抽象导出Excel类

    •   需要自己实现两个抽象方法:
      • getColumValueForColunmName :
        • 扩展方法:根据名称判断来做值得转换
        • 比如:if(key==date) value='2018-11-07'
      • getExcelWorkbook:
        • 生成Excel方法(参数详情请参阅代码)
    package com.haixianglicai.erp.accessCount.service.base;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    import java.util.concurrent.TimeUnit;
    
    import javax.annotation.Resource;
    import javax.transaction.Transactional;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRichTextString;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.hssf.util.HSSFColor;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.redis.core.RedisTemplate;
    import org.springframework.data.redis.core.ValueOperations;
    import org.springframework.util.StringUtils;
    
    import com.alibaba.fastjson.JSON;
    import com.alibaba.fastjson.JSONObject;
    import com.haixianglicai.erp.dao.walrus.mapper.MemberQueryCountDataMapper;
    import com.haixianglicai.erp.service.EnvironmentService;
    import com.haixianglicai.erp.vo.MemberQueryCountDataVO;
    import com.hanya.core.util.DateUtil;
    import com.hanya.core.util.MD5Utils;
    
    /**
     * 
     * @author HX-011
     *
     * @param <T>
     */
    public abstract class AbstractExportExcelService<T> {
    	protected static Logger logger = LoggerFactory.getLogger(AbstractExportExcelService.class);
    	//Excel保存的路径位置
    	public static String EXCEL_SAVE_PATH = "/excel";
    	//Excel 导出的最大行数
    	protected static int EXCEL_MAX_ROW = 65534;
    	
    	@Resource(name="activityStringRedisTemplate")
        protected ValueOperations<String, String> stringRedisValueOpsTemporary;
    	@Resource(name="activityStringRedisTemplate")
        protected RedisTemplate<String, String> stringRedisTemplateTemporary;
    	@Autowired
    	protected EnvironmentService environmentService;
    	
    	/**
    	 * 生成excel工作簿
    	 * @param titleNames:excel标题
    	 * @param colunmNames:excel字段名
    	 * @param dataList:数据集合
    	 * @param colunms:需要导出excel的列明
    	 * @return
    	 */
    	@SuppressWarnings("deprecation")
    	public HSSFWorkbook generateWorkbook(String titleName,String[] colunmNames, List<Map<String, Object>> dataList, String[] colunms) {
    		HSSFWorkbook workbook=new HSSFWorkbook();
    		HSSFCellStyle cellStyle = createStyle(workbook, (short) 14);	//生成样式
            HSSFSheet sheet=workbook.createSheet();
            HSSFRow row=null;
            HSSFCell cell=null;
            int titleLength=colunmNames.length;
            int columsLength=colunms.length;
            int rowNum=0;//行数
            
            //1.添加EXCEL标题
            if(!StringUtils.isEmpty(titleName)) {
            	//合并单元格
            	sheet.addMergedRegion(new CellRangeAddress(0,0,0,colunms.length-1));
            	row=sheet.createRow(rowNum);
            	row.setHeight((short) (25*30));
            	cell=row.createCell(0);
            	cell.setCellStyle(cellStyle);
            	cell.setCellValue(new HSSFRichTextString(titleName));
            	rowNum++;//下一行
            }
            
            //2.添加列名
            cellStyle = createStyle(workbook, (short) 11);
            row=sheet.createRow(rowNum);
            row.setHeight((short) (25*25));
            for (int i=0;i<titleLength;i++) {
                sheet.setColumnWidth((short) i, (short) (55 * 100));
                cell=row.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(new HSSFRichTextString(colunmNames[i]));
            }
            rowNum++;//下一行
            
            //3.添加数据
            if(dataList!=null&&dataList.size()>0){
                for(int i=0;i<dataList.size();i++){
                    row=sheet.createRow(rowNum);
                    Map<String, Object> dataMap=dataList.get(i);
                    for(int k=0;k<columsLength;k++){
                    	String colunmName = colunms[k];
                        String	columValue=dataMap.get(colunmName)==null?"":dataMap.get(colunmName).toString();
                        String tmpValue = getColumValueForColunmName(colunmName,columValue);
                        if(!StringUtils.isEmpty(tmpValue)) {
                        	columValue=tmpValue;
                        }
                        cell=row.createCell(k);
                        cell.setCellValue(new HSSFRichTextString(columValue));
                    }
                    rowNum++;
                }
            }
            return workbook;
    	}
    	
    	/**
    	 * 初始化样式
    	 * @param workbook
    	 * @param fontSize
    	 * @return
    	 */
    	private HSSFCellStyle createStyle(HSSFWorkbook workbook,short fontSize) {
    		HSSFCellStyle cellStyle=workbook.createCellStyle();//样式
            HSSFFont font=workbook.createFont();
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
            font.setColor(HSSFColor.BLACK.index);
            font.setFontHeightInPoints(fontSize);//设置字体大小  
            cellStyle.setFont(font);
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中      
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
            return cellStyle;
    	}
    	
    	/**
    	 * 获取列名
    	 * @param colunmName
    	 * @param columValue
    	 * @return
    	 */
    	public abstract String getColumValueForColunmName(String colunmName,String columValue);
    	
    	/**
    	 * 锁
    	 * @param id
    	 * @param name
    	 * @return
    	 */
    	public boolean lock(String id,String name) {
    		String key = name +"-"+ id;
    		if(!stringRedisTemplateTemporary.hasKey(key)) {
    			boolean flag = stringRedisValueOpsTemporary.setIfAbsent(key, id);
    			stringRedisTemplateTemporary.expire(key, 1, TimeUnit.HOURS);
    			return flag;
    		}else {
    			return stringRedisValueOpsTemporary.setIfAbsent(key, id);
    		}
    	}
    	
    	/**
    	 * 释放锁
    	 * @param id
    	 * @param name
    	 */
    	public void unLock(String id,String name) {
    		String key = name +"-"+ id;
    		stringRedisTemplateTemporary.delete(key);
    	}
    	
    	/**
    	 * 批量导出Excel数据
    	 * @param lists
    	 * @param fileName
    	 * @param path
    	 * @param titleName
    	 * @throws IOException
    	 */
    	public void exportExcelData(List<T> lists,String fileName,String path) throws IOException {
    		exportExcelData(lists, fileName, path, null);
    	}
    	
    	/**
    	 * 批量导出Excel数据
    	 * @param lists:
    	 * @param fileName
    	 * @param path
    	 * @param titleName
    	 * @throws IOException
    	 */
    	public List<String> exportExcelData(List<T> lists,String fileName,String path,String titleName) throws IOException {
    		List<String> fileNames = new ArrayList<String>();
    		int size = (null == lists ? 0 : lists.size());
    		// 导出Excel
    		if (size > 0) {
    			logger.warn("Excel文件存储路径【"+path+"】");
    			if (size > EXCEL_MAX_ROW) {
    				int loop = size % EXCEL_MAX_ROW == 0 ? (size / EXCEL_MAX_ROW) : (size / EXCEL_MAX_ROW) + 1;
    				
    				int j = 0;
    				for (int i = 1; i <= loop; i++) {
    					List<Map<String, Object>> tmpList = new ArrayList<Map<String, Object>>();
    					int maxRow = EXCEL_MAX_ROW * i;
    					if (maxRow > size) {
    						maxRow = size;
    					}
    					String tmpFileName = fileName+"-"+i+".xls";
    					logger.warn("总数:【" + size + "】--分几次导出:【" + loop + "】--循环第:【"+i+"】次--开始位置:【" + j + "】--结束位置:【" + maxRow + "】--文件名:【"+tmpFileName+"】");
    					
    					for (; j < maxRow; j++) {
    						Map<String,Object> tmpMap = JSONObject.parseObject(JSON.toJSONString(lists.get(j)));
    						tmpList.add(tmpMap);
    					}
    					HSSFWorkbook workbook = getExcelWorkbook(titleName,tmpList);	//获取导出workbook
    					exportExcel(workbook, tmpFileName, path);	//输出Excel
    					fileNames.add(tmpFileName);
    				}
    			}else {
    				List<Map<String, Object>> tmpList = new ArrayList<Map<String, Object>>();
    				for (int i = 0; i <lists.size(); i++) {
    					Map<String,Object> tmpMap = JSONObject.parseObject(JSON.toJSONString(lists.get(i)));
    					tmpList.add(tmpMap);
    				}
    				HSSFWorkbook workbook = getExcelWorkbook(titleName,tmpList);	//获取导出workbook
    				String tmpFileName = fileName+".xls";
    				logger.warn("总数:【" + size + "】--开始位置:【0】--结束位置:【" + size + "】--文件名:【"+tmpFileName+"】");
    				exportExcel(workbook, tmpFileName, path);	//输出Excel
    				fileNames.add(tmpFileName);
    			}
    		}
    		return fileNames;
    	}
    	
    	/**
    	 * 获取导出Excel的工作簿
    	 * @param data
    	 * @return
    	 */
    	public abstract HSSFWorkbook getExcelWorkbook(String titleName,List<Map<String, Object>> data);
    	
    	/**
    	 * 导出Excel
    	 * @param workbook
    	 * @param fileName
    	 * @param path
    	 * @throws IOException
    	 */
    	public void exportExcel(HSSFWorkbook workbook, String fileName, String path) throws IOException {
    		OutputStream out = null;
    		try {
    			File file = new File(path);
    			if(!file.exists()) {
    				file.mkdirs();
    			}
    			//fileName = MD5Utils.MD5(fileName);
    			File filePath = new File(path,fileName);
    			out = new FileOutputStream(filePath);
    			workbook.write(out);
    		} catch (Exception e) {
    			logger.warn("导出excel失败:	文件名:【"+fileName+"】		文件路径:【"+path+"】");
    			e.printStackTrace();
    			throw new IOException();
    		} finally {
    			if (null != out) {
    				out.close();
    			}
    		}
    	}
    	
    	/**
    	 * 删除文件夹
    	 * @param folderPath
    	 */
    	public static void delFolder(String folderPath) {
    		try {
    			delAllFile(folderPath); // 删除完里面所有内容
    			String filePath = folderPath;
    			filePath = filePath.toString();
    			java.io.File myFilePath = new java.io.File(filePath);
    			myFilePath.delete(); // 删除空文件夹
    		} catch (Exception e) {
    			e.printStackTrace();
    			logger.warn("删除文件夹失败:文件路径【"+folderPath+"】");
    		}
    	}
    	
    	/**
    	 * 删除文件
    	 * @param path
    	 * @return
    	 */
    	public static boolean delAllFile(String path) {
    		boolean flag = false;
    		File file = new File(path);
    		if (!file.exists()) {
    			return flag;
    		}
    		if (!file.isDirectory()) {
    			return flag;
    		}
    		String[] tempList = file.list();
    		File temp = null;
    		for (int i = 0; i < tempList.length; i++) {
    			if (path.endsWith(File.separator)) {
    				temp = new File(path + tempList[i]);
    			} else {
    				temp = new File(path + File.separator + tempList[i]);
    			}
    			if (temp.isFile()) {
    				temp.delete();
    			}
    			if (temp.isDirectory()) {
    				delAllFile(path + "/" + tempList[i]);// 先删除文件夹里面的文件
    				delFolder(path + "/" + tempList[i]);// 再删除空文件夹
    				flag = true;
    			}
    		}
    		return flag;
    	}
    	
    	protected String getExcelSavePath() {
    		return environmentService.getCompanyRealPath()+EXCEL_SAVE_PATH;
    	}
    }
    

    2.service 代码

    package com.haixianglicai.erp.accessCount.service.impl;
    
    import java.io.File;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.springframework.stereotype.Service;
    import org.springframework.util.StringUtils;
    
    import com.haixianglicai.erp.accessCount.service.MemberQueryCountDataService;
    import com.haixianglicai.erp.accessCount.service.base.AbstractExportExcelService;
    import com.hanya.core.util.DateUtil;
    import com.hanya.walrus.domain.Member;
    
    @Service
    public class MemberServiceImpl extends AbstractExportExcelService<Member> {
    	private MemberQueryCountDataService memberQueryCountDataService;
    	
    	/**
    	 * 导出excel方法
    	 * @param lists
    	 * @throws Exception
    	 */
    	public void exportExcel(List<Member> lists) throws Exception {
    		String realPath = getExcelSavePath();
    		try {
    			long startTime = System.currentTimeMillis();
    			String yyyyMMdd = DateUtil.format(new Date(), DateUtil.SIMPLE_FORMAT);
    			realPath += File.separator + yyyyMMdd+File.separator;
    			String HHss = DateUtil.format(new Date(), "HHmmss");
    			realPath += File.separator + HHss;
    			
    			String excelFileName = "excel文件名";		//需要自己定义
    			String excelTitleName = "excel标题名";	//需要自己定义
    			List<String> fileNames = exportExcelData(lists,excelFileName,realPath,excelTitleName);
    			long entTime = System.currentTimeMillis();
    			System.out.println("导出Excel【成功】:开始时间【"+startTime+"】--结束时间【"+entTime+"】");
    			
    			//保存导出Excel记录
    			String createName = "操作人";	//需要自己定义
    			//此代码省略...
    			memberQueryCountDataService.saveExportExcelToMemberQueryCountDataTable(fileNames, realPath,excelTitleName,createName);
    		}catch (Exception e) {
    			delFolder(realPath);	//删除失败的文件夹
    			throw new Exception();
    		}
    	}
    	
    	@Override
    	public String getColumValueForColunmName(String colunmName, String columValue) {
    		String val = "";
    		try {
    			if("registRationDate".equals(colunmName)) {
    				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    				val = format.format(format.parse(columValue));
    			}
    			if("capitalMoney".equals(colunmName)) {
    				val = covertAmount(columValue);
    			}
    		}catch (Exception e) {
    			e.printStackTrace();
    		}
    		return val;
    	}
    
    	@Override
    	public HSSFWorkbook getExcelWorkbook(String titleName, List<Map<String, Object>> data) {
    		String colunmNameStr = "phone,capitalMoney,registRationDate,typeId";
    		String[] colunms = colunmNameStr.split(",");
    		String[] colunmNames = {"手机号码","投资总金额","用户注册时间","投资计划"};
    		return generateWorkbook(titleName,colunmNames, data, colunms);
    	}
    	
    	/**
    	 * 转换成美式金额的格式 如:"123,232,434.23"
    	 * @param str
    	 * @return
    	 */
    	public static String covertAmount(String amount) {
    		if(StringUtils.isEmpty(amount)){
    			return "0";
    		}
    		if(Double.parseDouble(amount) == 0){
    			return "0";
    		}
    		DecimalFormat df = new DecimalFormat("#,###.00");
    		try {//避免传递的不是数字类型
    			return  df.format(Double.valueOf(amount));
    		} catch (Exception e){
    			return "0";
    		}
    	}
    }

    3.控制层代码:

    package com.haixianglicai.erp.accessCount.controller.base;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import java.util.Enumeration;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.commons.beanutils.BeanUtils;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.propertyeditors.CustomDateEditor;
    import org.springframework.util.StringUtils;
    import org.springframework.web.bind.WebDataBinder;
    import org.springframework.web.bind.annotation.InitBinder;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.ResponseBody;
    
    import com.haixianglicai.erp.accessCount.service.MemberQueryCountDataService;
    import com.haixianglicai.erp.accessCount.service.base.AbstractExportExcelService;
    import com.haixianglicai.erp.accessCount.util.CompressedFileUtil;
    import com.haixianglicai.erp.service.EnvironmentService;
    import com.haixianglicai.erp.support.FormResult;
    import com.haixianglicai.erp.support.PageNation;
    import com.haixianglicai.erp.vo.MemberQueryCountDataVO;
    import com.hanya.core.util.DateUtil;
    import com.hanya.core.util.MD5Utils;
    
    /**
     * 
     * @author HX-011
     *
     */
    public abstract class BaseDataQueryController {
    	protected static Logger logger = LoggerFactory.getLogger(BaseDataQueryController.class);
    	
    	@Autowired
    	private EnvironmentService environmentService;
    	@Autowired
    	private MemberQueryCountDataService memberQueryCountDataService;
    	
    	@RequestMapping("/{folder}/{fileName}")
    	public String index(@PathVariable("folder") String folder,@PathVariable("fileName") String fileName,HttpServletRequest request) {
    		request.setAttribute("linkUrl", link());
    		
    		Enumeration<String> enumeration = request.getParameterNames();
    		while(enumeration.hasMoreElements()) {
    			String key = enumeration.nextElement();
    			String value = request.getParameter(key);
    			request.setAttribute(key, value);
    		}
    		return "/"+folder+"/"+fileName;
    	}
    	
    	@RequestMapping("/link")
    	@ResponseBody
    	public String link() {
    		String link = environmentService.getRealDomain()+ AbstractExportExcelService.EXCEL_SAVE_PATH;
    		return link;
    	}
    	
    	@RequestMapping("/fileSystem/list")
    	@ResponseBody
    	public PageNation<MemberQueryCountDataVO> fileSystemList(@RequestParam Map<String,Object> form) {
    		int total = memberQueryCountDataService.count(form);
    		List<MemberQueryCountDataVO> lists = memberQueryCountDataService.queryForList(form);
    		try {
    			if(total>0) {
    				lists = memberQueryCountDataService.queryForList(form);
    			}
    		}catch (Exception e) {
    			e.printStackTrace();
    		}
    		return new PageNation<MemberQueryCountDataVO>(total,lists);
    	}
    	
    	@RequestMapping("/fileSystem/download")
    	public void fileSystemDownload(Long id,HttpServletResponse response) {
    		InputStream in = null;
    		ServletOutputStream out = null;
    		int type = -1;
    		String parentPath = null;
    		File file = null;
    		
    		try {
    			MemberQueryCountDataVO vo = memberQueryCountDataService.findById(id);
    			type = vo.getType();
    			String filePath = vo.getFilePath();
    			String fileName = vo.getType()==1?vo.getName():vo.getFolderName();
    			parentPath =  new File(filePath).getParentFile().getPath();
    			 
    			if(type!=1 && type!=2) {
    				logger.error("文件下载【type】参数类型错误!|{}|{}|{}",type,filePath,fileName);
    				//return new FormResult(false);
    			}
    			
    			String _fileName = null;
    			if(type==1) {	//打包下载
    				_fileName = fileName+"_"+DateUtil.format(new Date(), DateUtil.YMDHMS)+".zip";
    			}else {	//单个下载文件名
    				_fileName = fileName;
    			}
    			logger.error("文件开始下载:{}|{}|{}",type,filePath,fileName);
    			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.addHeader("Content-Disposition", "attachment;filename="+new String(_fileName.getBytes("GBK"), "ISO-8859-1"));
    			
    			out = response.getOutputStream();
    			if(type==1) {  //下载文件夹所有文件:打包压缩文件批量下载
    				//压缩文件
    				logger.error("文件开始压缩:目录:【{}】|文件名:【{}】",filePath,_fileName);
    				CompressedFileUtil.compressedFile(filePath, parentPath, _fileName);
    				logger.error("文件开始压缩成功,保存目录:【{}】",parentPath);
    				file = new File(parentPath,_fileName);
    			}else {//单个文件下载
    				//String tmpFileName = MD5Utils.MD5(fileName);
    				file = new File(filePath,fileName);
    			}
    			
    			//下载文件
    			if(file.exists()) {
    				in = new FileInputStream(file);
    				byte[] buffer = new byte[1024*4];
    				int len = -1;
    				while((len = in.read(buffer)) != -1) {
    					out.write(buffer, 0, len);
    				}
    			}
    			logger.error("下载文件 true"+file.getPath());
    			//return new FormResult(true);
    		}catch (Exception e) {
    			logger.error("下载excel文件异常!|{}",e.getMessage());
    			e.printStackTrace();
    			//return new FormResult(false);
    		}finally {
    			try {
    				if(null!=in) in.close();
    				if(null!=out) out.close();
    			} catch (IOException e) {
    				e.printStackTrace();
    			}
    			
    			if(type==1 && null!=file && file.exists()) {
    				file.delete();
    				logger.error("删除下载zip文件:{}",file.getPath());
    			}
    		}
    	}
    	
    	protected Map<String,Object> requestParameterToMap(HttpServletRequest request){
    		Map<String,Object> params = new HashMap<String,Object>();
    		Enumeration<String> en = request.getParameterNames();
    		while(en.hasMoreElements()) {
    			String key = en.nextElement();
    			String value = request.getParameter(key);
    			if(!StringUtils.isEmpty(key) && !StringUtils.isEmpty(value)) {
    				params.put(key, value);
    			}
    		}
    		return params;
    	}
    	
    	@InitBinder    
    	protected void initBinder(WebDataBinder binder) {
    		binder.registerCustomEditor(Date.class, new CustomDateEditor(new SimpleDateFormat(DateUtil.DEFAULT_FORMAT), true));    
    	}
    }
    

    4.工具类(Excel打包Zip):

    package com.haixianglicai.erp.accessCount.util;
    
    import java.io.BufferedOutputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.util.zip.ZipEntry;
    import java.util.zip.ZipOutputStream;
    
    import org.springframework.util.StringUtils;
    
    /**
     * @Description: 文件压缩工具类 将指定文件/文件夹压缩成zip、rar压缩文件
     */
    public class CompressedFileUtil {
    	public static void compressedFile(String resourcesPath, String targetPath) throws Exception {
    		compressedFile(resourcesPath, targetPath, null);
    	}
    
    	/**
    	 * @desc 将源文件/文件夹生成指定格式的压缩文件,格式zip
    	 * @param resourePath
    	 *            源文件/文件夹
    	 * @param targetPath
    	 *            目的压缩文件保存路径
    	 * @return void
    	 * @throws Exception
    	 */
    	public static void compressedFile(String resourcesPath, String targetPath, String fileName) throws Exception {
    		File resourcesFile = new File(resourcesPath); // 源文件
    		File targetFile = new File(targetPath); // 目的
    		// 如果目的路径不存在,则新建
    		if (!targetFile.exists()) {
    			targetFile.mkdirs();
    		}
    		String targetName = (StringUtils.isEmpty(fileName) ? resourcesFile.getName() : fileName) + (fileName.indexOf(".zip")==-1?".zip":""); // 目的压缩文件名
    		FileOutputStream outputStream = new FileOutputStream(new File(targetPath,targetName));
    		ZipOutputStream out = new ZipOutputStream(new BufferedOutputStream(outputStream));
    		createCompressedFile(out, resourcesFile, "");
    		out.close();
    	}
    
    	/**
    	 * @desc 生成压缩文件。 如果是文件夹,则使用递归,进行文件遍历、压缩 如果是文件,直接压缩
    	 * @param out
    	 *            输出流
    	 * @param file
    	 *            目标文件
    	 * @return void
    	 * @throws Exception
    	 */
    	public static void createCompressedFile(ZipOutputStream out, File file, String dir) throws Exception {
    		//System.out.println(file.getPath());
    		// 如果当前的是文件夹,则进行进一步处理
    		if (file.isDirectory()) {
    			// 得到文件列表信息
    			File[] files = file.listFiles();
    			// 将文件夹添加到下一级打包目录
    			out.putNextEntry(new ZipEntry(dir + "/"));
    			dir = dir.length() == 0 ? "" : dir + "/";
    			// 循环将文件夹中的文件打包
    			for (int i = 0; i < files.length; i++) {
    				createCompressedFile(out, files[i], dir + files[i].getName()); // 递归处理
    			}
    		} else { // 当前的是文件,打包处理
    			// 文件输入流
    			FileInputStream fis = new FileInputStream(file);
    			out.putNextEntry(new ZipEntry(dir));
    			// 进行写操作
    			int j = 0;
    			byte[] buffer = new byte[1024];
    			while ((j = fis.read(buffer)) > 0) {
    				out.write(buffer, 0, j);
    			}
    			// 关闭输入流
    			fis.close();
    		}
    	}
    
    	public static void main(String[] args) {
    		try {
    			CompressedFileUtil.compressedFile("E:\\data01\\nfs\\public\\excel\\2018-10-11\\invest", "E:\\data01\\nfs\\public\\excel\\2018-10-11","2018-10-12");
    //			String filePath = "\\data01\\nfs\\public\\excel\\2018-10-11\\invest\\2018-10-12.zip";
    //			File file = new File(filePath);
    //			file.delete();
    			System.out.println("压缩文件已经生成...");
    		} catch (Exception e) {
    			System.out.println("压缩文件生成失败...");
    			e.printStackTrace();
    		}
    	}
    }

    5.创建表SQL:

    CREATE TABLE `member_query_count_data` (
      `id` bigint(10) NOT NULL AUTO_INCREMENT,
      `path` varchar(200) COLLATE utf8_bin DEFAULT NULL COMMENT '文件全路径',
      `folder_name` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '文件夹名',
      `type` int(2) DEFAULT '0' COMMENT '类型:1-文件夹 2-文件',
      `status` int(2) DEFAULT '0' COMMENT '状态:1:启用 2为禁用',
      `parent_id` bigint(9) DEFAULT '0' COMMENT '父类节点',
      `create_date` varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT '创建日期',
      `create_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '创建人',
      `create_time` datetime DEFAULT NULL COMMENT '创建时间',
      `update_name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '修改人',
      `update_time` datetime DEFAULT NULL COMMENT '修改时间',
      `name` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '导出Excel类型名称',
      PRIMARY KEY (`id`),
      KEY `ct` (`create_date`)
    ) ENGINE=InnoDB AUTO_INCREMENT=464 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='erp定向数据统计数据表';
    

     

    展开全文
  • 根据数据批量生成excel文件

    千次阅读 2018-06-20 09:47:01
    第一步导入依赖: ...--excel支持--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.11</version> </...

    第一步导入依赖:

     

    <!--excel支持-->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.11</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>3.11</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.11</version>
    </dependency>

    第二步前端控制器实现:

     

    /**
         * 日志下载
         * @param year
         * @param month
         * @param daily
         * @param name
         * @param depts
         * @param response
         * @return
         */
        @GetMapping("download")
        public ResponseEntity download(Integer year, Integer month, Integer daily, String name, String depts, HttpServletResponse response) throws ParseException {
            if(StringUtils.isEmpty(year)||StringUtils.isEmpty(month)){
                return new ResponseEntity(SupConstant.BAD_PARAMETER, HttpStatus.BAD_REQUEST);
            }
            SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd");
            //起始时间
            Date stateTime;
            //结束时间
            Date endTime;
            if(StringUtils.isEmpty(daily)){
                stateTime=format.parse(year+"-"+month+"-01");
                if(month==12){
                    endTime=format.parse((year+1)+"-01-01");
                }else {
                    endTime=format.parse(year+"-"+(month+1)+"-01");
                }
            }else {
                stateTime=format.parse(year+"-"+month+"-"+daily);
                endTime=new Date(stateTime.getTime()+24*60*60*1000);
            }
            Wrapper<DailyRecord> wrapper=new EntityWrapper<>();
            wrapper.ge("record_time",stateTime);
            wrapper.lt("record_time",endTime);
            //用户名
            if (!StringUtils.isEmpty(name)) {
                wrapper.like("creator_name",name);
            }
            //部门集合
            if(!StringUtils.isEmpty(depts)){
                wrapper.andNew();
                String[] split = depts.split("-");
                for (int i = 0; i < split.length; i++) {
                    String s = split[i];
                    if(i==0){
                        wrapper.where("dept_id={0}",s);
                    }else {
                        wrapper.or("dept_id={0}",s);
                    }
    
                }
            }
    
            List<DailyRecord> dailyRecords = dailyRecordService.selectList(wrapper);
            if (dailyRecords.size()>0) {
                //创建excel生成对象
                SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
                Sheet sheet = sxssfWorkbook.createSheet("journail");
                //创建标题行
                Row row = sheet.createRow(0);
                //创建样式对象
                CellStyle cellStyle = sxssfWorkbook.createCellStyle();
                //设置背景
                cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
                //创建字体样式
                Font font = sxssfWorkbook.createFont();
                font.setFontHeightInPoints((short) 14);//设置字体大小
                font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
                //赋予字体样式
                cellStyle.setFont(font);
                //标题数组
                String[] titles={"姓名","部门","岗位","日志应填日期","实际填写日期",
                        "领导指示安排","批注建议","个人工作日志","批注建议","工作完成情况"
                        ,"批注建议","问题及思路","批注建议"};
                for (int i = 0; i < titles.length; i++) {
                    Cell cell = row.createCell(i);
                    cell.setCellValue(titles[i]);
                    cell.setCellStyle(cellStyle);
                }
    
                for (int i = 0; i < dailyRecords.size(); i++) {
                    DailyRecord dailyRecord = dailyRecords.get(i);
                    //内容
                    Row row1 = sheet.createRow(i + 1);
    
                    row1.createCell(0).setCellValue(dailyRecord.getCreatorName());
                    row1.createCell(1).setCellValue(dailyRecord.getDeptName());
                    if(!StringUtils.isEmpty(dailyRecord.getCreatorPostion())){
                        row1.createCell(2).setCellValue(dailyRecord.getCreatorPostion());
                    }
                    row1.createCell(3).setCellValue(dailyRecord.getRecordTime());
                    row1.createCell(4).setCellValue(dailyRecord.getGmtCreate());
                    if(!StringUtils.isEmpty(dailyRecord.getArrange())){
                        row1.createCell(5).setCellValue(dailyRecord.getArrange());
                    }
                    if(!StringUtils.isEmpty(dailyRecord.getCreatorPostion())){
                        row1.createCell(6).setCellValue(dailyRecord.getArrangeComment());
                    }
                    if(!StringUtils.isEmpty(dailyRecord.getPlan())){
                        row1.createCell(7).setCellValue(dailyRecord.getPlan());
                    }
                    if(!StringUtils.isEmpty(dailyRecord.getPlanComment())){
                        row1.createCell(8).setCellValue(dailyRecord.getPlanComment());
                    }
                    if(!StringUtils.isEmpty(dailyRecord.getFinish())){
                        row1.createCell(9).setCellValue(dailyRecord.getFinish());
                    }
                    if(!StringUtils.isEmpty(dailyRecord.getFinishComment())){
                        row1.createCell(10).setCellValue(dailyRecord.getFinishComment());
                    }
                    if(!StringUtils.isEmpty(dailyRecord.getAdvise())){
                        row1.createCell(11).setCellValue(dailyRecord.getAdvise());
                    }
                    if(!StringUtils.isEmpty(dailyRecord.getAdviseComment())){
                        row1.createCell(12).setCellValue(dailyRecord.getAdviseComment());
                    }
    
                }
                //创建文件名
                String filename = "日志.xlsx";
                //创建输出对象
                OutputStream out;
                response.setContentType("application/ms-excel;charset=UTF-8");
                try {
                    response.setHeader("Content-Disposition", "attachment;filename=" +
                            new String(filename.getBytes("gb2312"), "ISO8859-1"));
                    out = response.getOutputStream();
                    sxssfWorkbook.write(out);// 将数据写出去
                    log.info("导出" + filename + "成功!");
                    out.close();
                    return new ResponseEntity("数据导出成功", HttpStatus.OK);
                } catch (Exception e) {
                    e.printStackTrace();
                    log.info("导出" + filename + "失败!");
                    return new ResponseEntity("数据导出失败", HttpStatus.OK);
                }
            }else {
                return new ResponseEntity(SupConstant.NOT_INQUIRED,HttpStatus.OK);
            }
        }

    第三步测试页面js(条件模拟a标签):

     

    //下载日志
    function download(){
        var work= $('form[id="tree_form"]').serializeArray();
        //年
        var year=$("select[name='year']").val();
        //月
        var month=$("select[name='month']").val();
        if(year==""||month==""){
            createDomHint.msgIter('请选择日期')
            return;
        }
        let  a = document.createElement('a');
        if(work.length>0){
            if(work.length==total){
                    a.href = apiExtend.journal.download + '?current=' + page.current + '&size=' + page.size + '&depts=&'+ $('#header_conter_left_form').serialize(),
                    document.body.appendChild(a),
                    a.click(),
                    document.body.removeChild(a)
            }else {
                var ids="";
                for (var i = 0; i < work.length; i++) {
                    ids += work[i].value+"-";
                }
                a.href = apiExtend.journal.download + '?current=' + page.current + '&size=' + page.size + '&depts='+ids+'&'+ $('#header_conter_left_form').serialize(),
                    document.body.appendChild(a),
                    a.click(),
                    document.body.removeChild(a)
    
            }
        }else {
                a.href = apiExtend.journal.download + '?current=' + page.current + '&size=' + page.size + '&depts=&'+ $('#header_conter_left_form').serialize(),
                document.body.appendChild(a),
                a.click(),
                document.body.removeChild(a)
        }
    
    }

    第四步实现效果:

    展开全文
  • java批量生成excel文件

    2017-02-25 12:36:00
    1、导入用于操作excel的jar,地址... 2、生成excel使用的模版文件,地址:https://pan.baidu.com/s/1c2y1rIo 3、java代码如下: package test.job.day1130; import java.io.BufferedInputStream; import jav...

    1、导入用于操作excel的jar,地址:https://pan.baidu.com/s/1qXADRlU

    2、生成excel使用的模版文件,地址:https://pan.baidu.com/s/1c2y1rIo

    3、java代码如下:

    package test.job.day1130;
    
    import java.io.BufferedInputStream;
    import java.io.BufferedOutputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileOutputStream;
    import java.io.InputStream;
    import java.io.OutputStream;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    
    public class ExcelUtil {
        private File createExcelFile(String path,String fileName)throws Exception{
            InputStream in = null;
            OutputStream out = null;
            File excelFile = createNewFile(path,fileName);
            //System.out.println(excelFile.getName());
            //模版
            File templateFile = new File(path+"/template","template.xls");
            if(!templateFile.exists())
                throw new Exception("模版文件不存在");
            //System.out.println(templateFile.getName());
            try{
                in = new BufferedInputStream(new FileInputStream(templateFile),1024);
                out = new BufferedOutputStream(new FileOutputStream(excelFile),1024);
                byte[] buffer = new byte[1024];
                int len;
                while((len=in.read(buffer)) != -1){
                    out.write(buffer,0,len);
                    out.flush();
                }
            }finally{
                if(in != null)
                    in.close();
                if(out != null)
                    out.close();
            }
            return excelFile;
        }
        
        /*初始化excel文件*/
        private void  initExcelFile(File excelFile,String prefix)throws Exception{
            InputStream is = null;
            OutputStream out = null;
            HSSFWorkbook workbook = null;
            HSSFSheet sheet = null;
            
            is = new FileInputStream(excelFile);
            
            workbook = new HSSFWorkbook(is);
            String suffix = "";
            //获取第一个sheet
            sheet = workbook.getSheetAt(0);
            
            if(sheet != null){
                //写数据
                for(int i=0;i<399;i++){
                    HSSFRow row = sheet.createRow(i);
                    HSSFCell cell = row.createCell(0);
                    
                    if(i == 0){
                        cell.setCellValue("帐号");
                        cell = row.createCell(1);
                        cell.setCellValue("密码");
                        continue;
                    }
                    
                    if(i < 10){
                        suffix = "00" + i;
                    }
                    else if(i < 100){
                        suffix = "0" + i;
                    }
                    else{
                        suffix = i + "";
                    }
                    cell.setCellValue(prefix + suffix);
                    cell = row.createCell(1);
                    cell.setCellValue("000000");
                }
                out = new FileOutputStream(excelFile);
                workbook.write(out);
            }
            out.flush();
            out.close();
            
        }
        
        private File createNewFile(String path,String fileName)throws Exception{
            File newFile = new File(path,fileName);
            
            if(!newFile.exists())
                newFile.createNewFile();
            
            return newFile;
        }
        
        
        public static void main(String[] args)throws Exception{        
            
            String path = "d:/excelFiles";
            String fileName = "";
            String prefix = "";
            String tmpStr = "";
            //char[] charArr = {'A','B','C','D','E','F','G','H','I','J'};
            char[] charArr = {'O','P','Q'};
            long t0 = System.currentTimeMillis();
            for(int i=0;i<charArr.length;i++){
                for(int j=0;j<100;j++){
                    if(j<10){
                        tmpStr = "0" + j;
                    }else{
                        tmpStr = "" + j;
                    }
                    
                    prefix = charArr[i] + tmpStr;
                    fileName = "file" + prefix + ".xls";
                    ExcelUtil eu = new ExcelUtil();
                    System.out.println("正在创建 " + fileName + "文件..");
                    File f = eu.createExcelFile(path,fileName);
                    eu.initExcelFile(f,prefix);
                }
            }
            long t1 = System.currentTimeMillis();
            
            System.out.println("耗时:" + (t1-t0)/1000 + "秒钟");
            
            
            
    //        String fileName = "file000.xls";
    //        ExcelUtil eu = new ExcelUtil();
    //        File f = eu.createExcelFile(path,fileName);
    //        eu.initExcelFile(f,"a00");
        }
    }

    4、生成效果如下:

    转载于:https://www.cnblogs.com/boluoboluo/p/6441532.html

    展开全文
  • Java通过POI读取Excel遍历数据,根本word模板批量生成word文档,demo可运行,可根据需求修改
  • 了解可springPOI导出excel 话不多,放码过来 application.yml配置文件 zipfilepath: #path: /export/data/www/zip path: E:\2345Downloads http: http://devh5.warehouseadmin.chujiayoupin.com/zip 配置...
  • 好久不见,最近在做一个导出批量excel的功能,因为不希望通过先写出单个excel到本地,然后再压缩成zip后导出。 因此随手百度,找到倒流进ZipOutputStream,然后变成zip导出。 demo代码如下 @RequestMapping(value...
  • 继上一篇“使用POI读取Excel文件”文章之后有对生成Excel文件进行如下操作: 导出Excel文件: packagecom.sanji.io; importjava.io.FileOutputStream; importjava.util.List; importorg....
  • 临下班前有个需求,有个同事有一份excel数据需要导入到hive中,到手后发现需要导入的excel文件有5个,且每个excel有60个sheet,每个sheet文件是顶行的,由于文件是xls格式的,单excel文件数据量大概在390万左右,且...
  • 前台接收.xlsx文件,后台工具类使用的HSSFWorkbook,自己测试没问题正常导出打开了,结果提测试,测试说excel打不开,折腾了半天才知道,前台接收.xlsx,后台工具类就必须用XSSFWorkbook,如果用HSSFWorkbook就会...
  • poi上传Excel文件批量的添加数据 :https://blog.csdn.net/kangshifu007/article/details/103149764 步骤 : 1.添加依赖 2.添加工具类 , 3.根据实际情况创建相对应的实体类(下面有具体的说明) 4.编写controller层...
  • 我使用的是Springboot框架开发的。首先需要在pom.xml文件中引入以下maven包: <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> ...
  • java 利用poi实现解析EXCEL,并根据某列版本号进行分组,把分组后的数据生成新的EXCEL文件。 controller(逻辑层) import com.example.model.Excelmodel; import org.apache.poi.hssf.usermodel.*; import org....
  • java后台利用Apache poi 生成excel文档提供前台下载

    万次阅读 多人点赞 2016-12-06 20:44:39
    java在后台利用Apache poi 生成excel文档提供前台下载
  • 下面两个方法,在处理 20000行*100列 的数据时,在 1核1G 的服务器上仍然会内存溢出。 方法一: Office软件一直是一个诲誉参半的软件,广大普通计算机用户用Office来满足日常办公需求,于是就产生...POI的诞生解决了E
  • 使用POI批量导出文件

    2019-11-22 13:16:47
    每个站点的数据写入到单独的excel,保存到服务器,最后将所有excel压缩为一个zip文件,输出到response。 代码大体结构如下: //导出功能入口 @RequestMapping(value="exportAll") public void exportAll(String ...
  • POI依赖的jar包 org.apache.poi poi-ooxml 3.14 前台(使用easyUI框架)//导出 $("#button-export").click(function(){ window.location.href="../../area_batchExport.action";
  • Java利用POI生成Excel

    2018-06-29 14:28:44
    一、首先需要弄清excel的层次结构:.xls 97-03版excel ====》对应类HSSFWorkbook;.xlsx 07版excel ======》对应类 XSSFWorkbook;结构组成:工作簿:workbook表空间:sheet行:row列:column单元格:cell结构关系...
  • //创建输入流的excel文件 HSSFCell cell = null; System.out.println("NumOfSheets: "+wb.getNumberOfSheets()); //多个工作表 for(int i=0;i();i++){ //得到工作表 HSSFSheet hf = wb.getSheetAt(i); ...
  • 使用POI操作Excel修改模板(批量替换excel中的数据)import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org....
  • poi 文件批量上传下载

    2020-06-04 17:09:27
    poi Excel文件的导入导出 Apache POI... 读取 Excel文件的导入 使用场景:批量注册 Excel文件中 ----》读取文件的数据 Java程序 ----》添加数据库 为什么需要使用Poi呢? 文本文件 通过流可以直接读取为可以识别的字符
  • demo已上传,直接用idea打开运行即可,可根据个人需求,进行二次开发 https://download.csdn.net/download/u010368726/12317633
  • 文章目录SpringBoot整合poi实现Excel文件的导入和导出1、引入Maven依赖2、添加ExcelFormatUtil工具类3、Excel导入应用案例4、Excel导出应用案例 SpringBoot整合poi实现Excel文件的导入和导出 1、引入Maven依赖 <...
  • java项目根据现有的excel模板生成新的excel表并 导入数据
  • import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.*; ...
  • OA (ssh) 基本实现(poi 生成 Excel , struts2动态下载 mysql数据库文件) 学习笔记(含源代码) 借鉴 风中叶 老师的视频,写的文章,代码比较详实。 说了很多我的看法,和思考,做了充分的日志
  • 如题:文件信息的批量导入...... 项目中经常会遇到客户的一些单表信息的数据批量导入,也就是提供定制Excel表,再把Excel表中的数据提取到...2、POI读取Excel表格,把数据封装为持久化实体List 3、批量导入数据库
  • Java中生成excel文件返回数据 1.需求描述 开发中遇到过很多查询界面,有很多的筛选条件,选择或输入筛选条件后,点击查询,后台查询出符合条件的数据,前台做展示。最近遇到新的需求,需要将条件查询的结果做excel...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,623
精华内容 649
关键字:

poi批量生成excel文件