添加链接
link管理
链接快照平台
  • 输入网页链接,自动生成快照
  • 标签化管理网页链接
* 通过输入的编号范围查询出需要导出的数据 @RequestMapping ( value = "/exportFixByMonths" , method = RequestMethod . GET ) public void exportFixByMonths ( ActivitiesBean bean , HttpServletResponse response , HttpServletRequest request , @RequestParam String startnum , @RequestParam String endnum ) throws IOException { int STARTNUM = Integer . parseInt ( startnum ) ; int ENDNUM = Integer . parseInt ( endnum ) ; List < File > srcfile = new ArrayList < File > ( ) ; //声明一个集合,用来存放多个Excel文件路径及名称 for ( int i = STARTNUM ; i <= ENDNUM ; i ++ ) { bean . setCOMMITTEE ( i ) ; // 文件模板路径 String rootpath = ( ( HttpServletRequest ) request ) . getSession ( ) . getServletContext ( ) . getRealPath ( "/muban" ) ; File file = new File ( rootpath + File . separator + "tongjis.xlsx" ) ; // 新的文件名 String filename = "测试表" + new SimpleDateFormat ( "yyyyMMddHHmmssSSS" ) . format ( new Date ( ) ) + ".xlsx" ; File newFile = ExcelUtil . createNewFile ( filename , file ) ; // 新文件写入数据,并下载***************************************************** InputStream is = null ; XSSFWorkbook workbook = null ; //HSSFWorkbook workbook=null; XSSFSheet sheet = null ; try { is = new FileInputStream ( newFile ) ; // 将excel文件转为输入流 workbook = new XSSFWorkbook ( is ) ; // 创建个workbook, //workbook = new HSSFWorkbook(is);// 创建个workbook, // 获取第一个sheet sheet = workbook . getSheetAt ( 0 ) ; } catch ( Exception e1 ) { e1 . printStackTrace ( ) ; if ( sheet != null ) { try { // 写数据 FileOutputStream fos = new FileOutputStream ( newFile ) ; XSSFRow row = sheet . getRow ( 1 ) ; if ( row == null ) { row = sheet . createRow ( 1 ) ; XSSFCell cell = row . getCell ( 0 ) ; if ( cell == null ) { cell = row . createCell ( 0 ) ; //根据id查询数据 TestBean testBean = activitiesService . queryceshi ( bean ) ; if ( testBean != null && ! "" . equals ( testBean ) ) { String committeename = testBean . getCOMMITTEENAME ( ) != null ? testBean . getCOMMITTEENAME ( ) + " " : " " ; String cppccsession = testBean . getCPPCCSESSION ( ) != null ? testBean . getCPPCCSESSION ( ) + " " : " " ; String positions = testBean . getPOSITIONS ( ) != null ? testBean . getPOSITIONS ( ) + " " : " " ; cell = sheet . getRow ( 1 ) . getCell ( 0 ) ; cell . setCellValue ( committeename ) ; cell = sheet . getRow ( 1 ) . getCell ( 1 ) ; cell . setCellValue ( cppccsession ) ; cell = sheet . getRow ( 1 ) . getCell ( 2 ) ; cell . setCellValue ( i ) ; cell = sheet . getRow ( 2 ) . getCell ( 3 ) ; cell . setCellValue ( positions ) ; workbook . write ( fos ) ; fos . flush ( ) ; fos . close ( ) ; } catch ( Exception e ) { e . printStackTrace ( ) ; } finally { try { if ( null != is ) { is . close ( ) ; } catch ( Exception e ) { e . printStackTrace ( ) ; srcfile . add ( newFile ) ; //指定磁盘目录 String strpath = SysParamInit . zipPath + File . separator ; //例如D://file //指定.zip格式和名称 String pathname = new SimpleDateFormat ( "yyyyMMddHHmmssSSS" ) . format ( new Date ( ) ) + ".zip" ; File zipfile = new File ( strpath + pathname ) ; //压缩多个excel文件为.zip格式并删除 ExcelUtil . zipFiles ( srcfile , zipfile ) ; ExcelUtil . deleteFiles ( srcfile ) ; //下载.zip格式文件并删除 ExcelUtil . downFile ( response , strpath , pathname ) ; ExcelUtil . deleteZip ( zipfile ) ; * 读取excel模板,并复制到新文件中供写入和下载 * @return public static File createNewFile ( String filename , File file ) { // 读取模板,并赋值到新文件************************************************************ // 保存文件的路径 String realPath = SysParamInit . zipPath ; ; // 判断路径是否存在 File dir = new File ( realPath ) ; if ( ! dir . exists ( ) ) { dir . mkdirs ( ) ; // 写入到新的excel File newFile = new File ( realPath , filename ) ; try { newFile . createNewFile ( ) ; // 复制模板到新文件 fileChannelCopy ( file , newFile ) ; } catch ( Exception e ) { e . printStackTrace ( ) ; return newFile ;
public class SysParamInit {
	private static final String SYS_CONFIG="config.properties";
	public static String zipPath;
	static {
		java.util.Properties prop = Properties.loadProperties(SYS_CONFIG);
		SysParamInit.zipPath=prop.getProperty("zipPath");

config.properties文件里指定压缩文件路径

zipPath=D://uploadfile
	 * 复制文件
	 * @param s
	 *            源文件
	 * @param t
	 *            复制到的新文件
	public static void fileChannelCopy(File s, File t) {
		try {
			InputStream in = null;
			OutputStream out = null;
			try {
				in = new BufferedInputStream(new FileInputStream(s), 1024);
				out = new BufferedOutputStream(new FileOutputStream(t), 1024);
				byte[] buffer = new byte[1024];
				int len;
				while ((len = in.read(buffer)) != -1) {
					out.write(buffer, 0, len);
			} finally {
				if (null != in) {
					in.close();
				if (null != out) {
					out.close();
		} catch (Exception e) {
			e.printStackTrace();
     * 将多个Excel打包成zip文件 
     * @param srcfile 
     * @param zipfile 
    public static void zipFiles(List<File> srcfile, File zipfile) {    
        byte[] buf = new byte[1024];    
        try {    
            // Create the ZIP file    
            ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));    
            // Compress the files    
            for (int i = 0; i < srcfile.size(); i++) {    
                File file = srcfile.get(i);    
                FileInputStream in = new FileInputStream(file);    
                // Add ZIP entry to output stream.    
                out.putNextEntry(new ZipEntry(file.getName()));    
                // Transfer bytes from the file to the ZIP file    
                int len;    
                while ((len = in.read(buf)) > 0) {    
                    out.write(buf, 0, len);    
                // Complete the entry    
                out.closeEntry();    
                in.close();    
            // Complete the ZIP file    
            out.close();   
        } catch (IOException e) {    
           e.printStackTrace();  
	 * 删除多个文件方法
	 * @param srcfile
	public static void deleteFiles(List<File> srcfile) {
		for (File file : srcfile) {
			if (file.exists()) {
				file.delete();
 public static void downFile(HttpServletResponse response,String serverPath, String str) { 
        try { 
        	String path = serverPath + str;    
            File file = new File(path);    
            if (file.exists()) {    
                InputStream ins = new FileInputStream(path);    
                BufferedInputStream bins = new BufferedInputStream(ins);// 放到缓冲流里面    
                OutputStream outs = response.getOutputStream();// 获取文件输出IO流    
                BufferedOutputStream bouts = new BufferedOutputStream(outs);    
                response.setContentType("application/x-download");// 设置response内容的类型    
                response.setHeader(    
                        "Content-disposition",    
                        "attachment;filename="    
                                + URLEncoder.encode(str, "GBK"));// 设置头部信息    
                int bytesRead = 0;    
                byte[] buffer = new byte[8192];    
                 //开始向网络传输文件流    
                while ((bytesRead = bins.read(buffer, 0, 8192)) != -1) {    
                   bouts.write(buffer, 0, bytesRead);    
               bouts.flush();// 这里一定要调用flush()方法    
                ins.close();    
                bins.close();    
                outs.close();    
                bouts.close();    
            } else {    
                response.sendRedirect("../error.jsp");    
        } catch (IOException e) {    
            e.printStackTrace();  
     * 删除zip
    public static void deleteZip(File path) {
		if (path.exists()) {
			path.delete();

在这里插入图片描述
在这里插入图片描述

转自:专业IT技术发表平台