Java SRping boot returns a single Excel and packages multiple files back to the compressed file for download

directory

introduce

The effect

code:

Rely on

The controller class

The log

---

introduce

• A single type of build excel reads the data and then the build returns it
• Multiple files, and then put the file to the local, find a record directory
• File traversal is then added to the compressed package
• Return compressed package
• Now that you have all the code in your control class, you can adjust the following as necessary
• ok
•  

The effect

• Query a return

• Check the excel

 

• Query multiple returns

• Check the zip

code:

• The following code includes
• Rely on
• code

Rely on

<! <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId> Poi-ooxml </artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> The < version > 3.17 < / version > < / dependency >Copy the code

 

The controller class

package com.superman.global.ctr;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationHome;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.superman.global.service.PosetgresqlService;

/**
 * 导出数据
 * 
 * @author yushen
 *
 */
@Controller
@RequestMapping(value = "/ExportDataCtr")
public class ExportDataCtr {

	private static final Logger logger = LoggerFactory.getLogger(ExportDataCtr.class);

	@Autowired
	private PosetgresqlService ps;

	/**
	 * 导出excel get 请求 返回单个表数据 or 多个文件压缩后的 zip <P/>
	 * <p/>
	 * 
	 * 测试url 单个返回excel: /ExportDataCtr/excel/code_bmlx/1/100 <P/>
	 * 测试url 多个返回zip: http://localhost:9997/ExportDataCtr/excel/code_bmlx,sampling_for/1/100<P/>
	 * 
	 * 测试地址规则 get : /ExportDataCtr/excel/{tables}/{page}/{pageSize}<P/>
	 * 
	 * @param tables 多个table 用英文逗号隔开 必传<P/>
	 * @param page   必传<P/>
	 * @param pageSize   必传<P/>
	 * 
	 * @return 返回excel 文件<P/>
	 * 
	 * @throws Exception<P/>
	 * 
	 */
	@GetMapping(value = "/excel/{tables}/{page}/{pageSize}")
	public void getUserInfoEx(HttpServletResponse response, @PathVariable("tables") String tables,
			@PathVariable("page") int page, @PathVariable("pageSize") int pageSize) throws Exception {

		if (tables.contains("--") || tables.equals(""))
			return;
		 
		if(!tables.contains(",")) {// 单个走这个
			HashMap<String, Object> map0 = new HashMap<String, Object>();
			map0.put("sql", "select * from " + tables + " limit " + pageSize + " OFFSET " + (page - 1) * pageSize);

			List<HashMap<String, Object>> userList = ps.getTableData(map0);

			if (userList.size() == 0)
				return;

			String zarr = "";
			for (String str : userList.get(0).keySet()) {
				zarr += "," + str;
			}
			String[] theadarr = zarr.substring(1).split(",");

			String fileName = tables + "_数据.xls"; // 文件名
			String workbookname = "统计表"; // 工作页名
			dcsj(theadarr, userList, response, fileName, workbookname);
		}else {
			// 多个走这个
			ApplicationHome home = new ApplicationHome(getClass());
			File jarFile = home.getSource();
			String FilePath = jarFile.getParentFile().toString() + "\\zorefile\\";
			File fileDir = new File(FilePath); 
			fileDir.mkdirs(); 
			
			List<String> fileNames = new ArrayList<String>();
			for (int i = 0; i < tables.split(",").length; i++) {
				HashMap<String, Object> map0 = new HashMap<String, Object>();
				map0.put("sql", "select * from " + tables.split(",")[i] + " limit " + pageSize + " OFFSET " + (page - 1) * pageSize);
				List<HashMap<String, Object>> userList = ps.getTableData(map0);
				if (userList.size() == 0)
					 continue;

				String zarr = "";
				for (String str : userList.get(0).keySet()) {
					zarr += "," + str;
				}
				String[] theadarr = zarr.substring(1).split(",");

				String fileName = tables.split(",")[i] + "_数据.xls"; // 文件名
				String workbookname = "统计表"; // 工作页名
				
				String file = dcsjs(theadarr, userList, response, fileName, workbookname, FilePath);

				fileNames.add(file);
			}
			
			pzfiles(response, fileNames);
			logger.info("开始清空缓存目录"+FilePath);
			delAllFile(FilePath);
			return;
		}
		

		
		
	}

	// 处理多个文件
	private String dcsjs(String[] theadarr, List<HashMap<String, Object>> rows, HttpServletResponse response,
			String fileName, String workbookname, String FilePath) throws Exception {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(workbookname);
		createTitle(workbook, sheet, theadarr);// 创建表头
		// 设置日期格式
//        HSSFCellStyle style = workbook.createCellStyle();
//        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
		// 新增数据行，并且设置单元格数据
		int rowNum = 1;
		for (HashMap<String, Object> obj : rows) {
			HSSFRow row = sheet.createRow(rowNum);
			int numl = 0;
			for (Object str : obj.keySet()) {
				row.createCell(numl++).setCellValue(obj.get(str).toString());
			}
			rowNum++;
		}
		// 生成excel文件
		buildExcelFile(fileName, workbook);

		String savePath = FilePath  +fileName;
		
		logger.info(savePath);
		
		// 输出Excel文件
		try {
			File savePathfile = new File(savePath);
			if (!savePathfile.exists()) {
				try {
					savePathfile.createNewFile();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			FileOutputStream fos = new FileOutputStream(new File(savePath));
			workbook.write(fos);
			workbook.close();
			fos.close();
			logger.info("生成excel文档成功");
		} catch (Exception e) {
			logger.info(e.toString());
			logger.info("生成excel文档失败");
		}
		return savePath;
	}

	/**
	 * 导出excel
	 * 
	 * @param theadarr
	 * @param rows
	 * @param response
	 * @param fileName
	 * @param workbookname
	 * @throws Exception
	 */
	private void dcsj(String[] theadarr, List<HashMap<String, Object>> rows, HttpServletResponse response,
			String fileName, String workbookname) throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(workbookname);
		createTitle(workbook, sheet, theadarr);// 创建表头
		// 设置日期格式
//        HSSFCellStyle style = workbook.createCellStyle();
//        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
		// 新增数据行，并且设置单元格数据
		int rowNum = 1;
		for (HashMap<String, Object> obj : rows) {
			HSSFRow row = sheet.createRow(rowNum);
			int numl = 0;
			for (Object str : obj.keySet()) {
				row.createCell(numl++).setCellValue(obj.get(str).toString());
			}
			rowNum++;
		}
		// 生成excel文件
		buildExcelFile(fileName, workbook);

		// 浏览器下载excel
		buildExcelDocument(fileName, workbook, response);
	}

	// 创建表头
	private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] theadarr) {
		HSSFRow row = sheet.createRow(0);
		// 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
		sheet.setColumnWidth(1, 12 * 256);
		sheet.setColumnWidth(3, 17 * 256);
		// 设置为居中加粗
		HSSFCellStyle style = workbook.createCellStyle();
		HSSFFont font = workbook.createFont();
		font.setBold(true);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFont(font);
		HSSFCell cell;
		for (int i = 0; i < theadarr.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(theadarr[i]);
			cell.setCellStyle(style);
		}
	}

	// 生成excel文件
	protected void buildExcelFile(String filename, HSSFWorkbook workbook) throws Exception {
		FileOutputStream fos = new FileOutputStream(filename);
		workbook.write(fos);
		fos.flush();
		fos.close();
	}

	// 浏览器下载excel
	protected void buildExcelDocument(String filename, HSSFWorkbook workbook, HttpServletResponse response)
			throws Exception {
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
		OutputStream outputStream = response.getOutputStream();
		workbook.write(outputStream);
		outputStream.flush();
		outputStream.close();
	}

	// 瓶装files 及返回zip
	public void pzfiles(HttpServletResponse response, List<String> fileNames) {
		ApplicationHome home = new ApplicationHome(getClass());
		File jarFile = home.getSource();
		String FilePath = jarFile.getParentFile().toString() + "/zorefile/";

		String zipFilePath = FilePath + "dataresource.zip";

		// 导出zip
		File zip = new File(zipFilePath);

		// 将excel文件生成压缩文件
		File srcfile[] = new File[fileNames.size()];
		for (int i = 0; i < fileNames.size(); i++) {
			srcfile[i] = new File(fileNames.get(i));
		}

		filesToZip(srcfile, zip);
		response.setContentType("application/zip");
		response.setHeader("Location", zip.getName());
		response.setHeader("Content-Disposition", "attachment; filename=" + zip.getName());

		try {
			OutputStream outputStream = response.getOutputStream();
			InputStream inputStream = new FileInputStream(zipFilePath);
			byte[] buffer = new byte[1024];
			int i = -1;
			while ((i = inputStream.read(buffer)) != -1) {
				outputStream.write(buffer, 0, i);
			}
			outputStream.flush();
			outputStream.close();
			inputStream.close();
			outputStream = null;
		} catch (IOException e) {
			logger.info(e.toString());
		} finally {
			delAllFile(FilePath);
		}

	}

	// 生成压缩包
	public void filesToZip(File[] srcFiles, File zipFile) {
		// 判断压缩后的文件存在不，不存在则创建
		if (!zipFile.exists()) {
			try {
				zipFile.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		// 创建 FileOutputStream 对象
		FileOutputStream fileOutputStream = null;
		// 创建 ZipOutputStream
		ZipOutputStream zipOutputStream = null;
		// 创建 FileInputStream 对象
		FileInputStream fileInputStream = null;
		try {
			// 实例化 FileOutputStream 对象
			fileOutputStream = new FileOutputStream(zipFile);
			// 实例化 ZipOutputStream 对象
			zipOutputStream = new ZipOutputStream(fileOutputStream);
			// 创建 ZipEntry 对象
			ZipEntry zipEntry = null;
			// 遍历源文件数组
			for (int i = 0; i < srcFiles.length; i++) {
				// 将源文件数组中的当前文件读入 FileInputStream 流中
				fileInputStream = new FileInputStream(srcFiles[i]);
				// 实例化 ZipEntry 对象，源文件数组中的当前文件
				zipEntry = new ZipEntry(srcFiles[i].getName());
				zipOutputStream.putNextEntry(zipEntry);
				// 该变量记录每次真正读的字节个数
				int len;
				// 定义每次读取的字节数组
				byte[] buffer = new byte[1024];
				while ((len = fileInputStream.read(buffer)) > 0) {
					zipOutputStream.write(buffer, 0, len);
				}
			}
			zipOutputStream.closeEntry();
			zipOutputStream.close();
			fileInputStream.close();
			fileOutputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	// 以模板形式生成excel
	@RequestMapping(value = "exportDetail")
	public void exportDetail(HttpServletResponse response, HttpServletRequest request)
			throws InvalidFormatException, IOException {
		// 模板在项目存放位置
		String fileRootPath = request.getSession().getServletContext().getRealPath("/templates");
		// 模板文件名称
		String fileName = "";
		// 将excel导出的文件位置
		String filePath = "c:/" + File.separator;
		// 得到此路径下文件
		File fileDir = new File(filePath);
		// 创建文件夹
		if (!fileDir.exists() && !fileDir.isDirectory()) {
			fileDir.mkdirs();
		}
//		// 用于存放生成的excel文件名称
		List<String> fileNames = new ArrayList<String>();
//		// 导出Excel文件路径
//		String fullFilePath = "";
//		// 输入流
//		InputStream in = null;
//		// 输出流
//		FileOutputStream os = null;
//		// 循环导出excel到临时文件夹中
//		for (int i = 0; i < 10; i++) {
//			// 往excel填入内容
//			Map<String, Object> bean = new HashMap<String, Object>();
//			bean.put("xx", "xx");
//			// 每次导出的excel的文件名
//			String fileNameS = "xxxxx.xls";
//			if (bean != null) {
//				// XLSTransformer生成excel文件
//				XLSTransformer transformer = new XLSTransformer();
//				in = new FileInputStream(new File(fileRootPath + File.separator + fileName));
//				HSSFWorkbook workbook;
//				// 设置sheet页名称
//				String sheetName = "详细";
//				workbook = (HSSFWorkbook) transformer.transformXLS(in, bean);
//				// 设置sheet页名称
//				workbook.setSheetName(0, sheetName);
//				// 导出excel的全路径
//				fullFilePath = filePath + File.separator + fileNameS;
//				fileNames.add(fullFilePath);
//				os = new FileOutputStream(fullFilePath);
//				// 写文件
//				workbook.write(os);
//			}
//			// 清空流缓冲区数据
//			os.flush();
//			// 关闭流
//			os.close();
//			in.close();
//			os = null;
//		}

		ApplicationHome home = new ApplicationHome(getClass());
		File jarFile = home.getSource();
		String zipfile = jarFile.getParentFile().toString();

		// 导出压缩文件的全路径
		String zipFilePath = filePath + "压缩文件名" + ".zip";
		// 导出zip
		File zip = new File(zipFilePath);
		// 将excel文件生成压缩文件
		File srcfile[] = new File[fileNames.size()];
		for (int j = 0, n1 = fileNames.size(); j < n1; j++) {
			srcfile[j] = new File(fileNames.get(j));
		}
		ZipFiles(srcfile, zip);
		response.setContentType("application/zip");
		response.setHeader("Location", zip.getName());
		response.setHeader("Content-Disposition", "attachment; filename=" + zip.getName());
		OutputStream outputStream = response.getOutputStream();
		InputStream inputStream = new FileInputStream(zipFilePath);
		byte[] buffer = new byte[1024];
		int i = -1;
		while ((i = inputStream.read(buffer)) != -1) {
			outputStream.write(buffer, 0, i);
		}
		outputStream.flush();
		outputStream.close();
		inputStream.close();
		outputStream = null;

		try {
			delAllFile(filePath); // 删除完里面所有内容
			filePath = filePath.toString();
			java.io.File myFilePath = new java.io.File(filePath);
			myFilePath.delete(); // 删除空文件夹
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 压缩文件
	public void ZipFiles(File[] srcfile, File zipfile) {
		byte[] buf = new byte[1024];
		try {
			ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
			for (int i = 0; i < srcfile.length; i++) {
				FileInputStream in = new FileInputStream(srcfile[i]);
				out.putNextEntry(new ZipEntry(srcfile[i].getName()));
				int len;
				while ((len = in.read(buf)) > 0) {
					out.write(buf, 0, len);
				}
				out.closeEntry();
				in.close();
			}
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/***
	 * 删除指定文件夹下所有文件
	 * 
	 * @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]);// 先删除文件夹里面的文件
				flag = true;
			}
		}
		return flag;
	}
}
Copy the code

 

The log

2019-07-23 22:07:15.909 INFO 13484 -- [nio-9997-exec-1] O.A.C.C.C. [Tomcat].[/] : Initializing Spring FrameworkServlet 'dispatcherServlet' 2019-07-23 22:07:15.909 INFO 13484 -- [NIO-9997-exec-1] o.s.web.servlet.DispatcherServlet : FrameworkServlet 'dispatcherServlet': Initialization started the 2019-07-23 22:07:15. 952 INFO - 13484 [nio - 9997 - exec - 1] O.S.W eb. Servlet. DispatcherServlet: FrameworkServlet 'dispatcherServlet': Initialization Completed in 43 ms 2019-07-23 22:08:26.084 INFO 13484 -- [NIO-9997-exec-4] com.superman.global.ctr.ExportDataCtr : D: workspace2018_11_19_LJnew\ gx-ksy-udmp \target\zorefile\code_bmlx_ data. XLS 2019-07-23 22:08:26.090 INFO 13484 -- [nio-9997-exec-4] com.superman.global.ctr.ExportDataCtr : Generate excel document 2019-07-23 22:08:26 success. 123 INFO - 13484 [nio - 9997 - exec - 4] com. Seen superman. Global. CTR. ExportDataCtr: D: workspace2018_11_19_LJnew\ gx-ksy-udmp \target\zorefile\sampling_for_ data. XLS 2019-07-23 22:08:26.136 INFO 13484 -- [nio-9997-exec-4] com.superman.global.ctr.ExportDataCtr : Generate excel document 2019-07-23 22:08:26 success. 158 INFO - 13484 [nio - 9997 - exec - 4] com. Seen superman. Global. CTR. ExportDataCtr: Start clearing cache directory D: workspace2018_11_19_LJnew\ gX-ksy-udmp \target\zorefile\Copy the code

 

Add a query to the database to return the Chinese annotations of the table fields and return Excel ZIP

package com.superman.global.ctr;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

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.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.ApplicationHome;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;

import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.superman.global.service.PosetgresqlService;

/**
 * 导出数据
 * 
 * @author yushen
 *
 */
@Controller
@RequestMapping(value = "/ExportDataCtr")
public class ExportDataCtr {

	private static final Logger logger = LoggerFactory.getLogger(ExportDataCtr.class);

	 @Value("${ds.datasource.linktype}")
	 private String linktype;
	 
	@Autowired
	private PosetgresqlService ps;

	/**
	 * 导出excel get 请求 返回单个表数据 or 多个文件压缩后的 zip <P/>
	 * <p/>
	 * 
	 * 测试url 单个返回excel: /ExportDataCtr/excel/code_bmlx/1/100 <P/>
	 * 测试url 多个返回zip: http://localhost:9997/ExportDataCtr/excel/code_bmlx,sampling_for/1/100<P/>
	 * 
	 * 测试地址规则 get : /ExportDataCtr/excel/{tables}/{page}/{pageSize}<P/>
	 * 
	 * @param tables 多个table 用英文逗号隔开 必传<P/>
	 * @param page   必传<P/>
	 * @param pageSize   必传<P/>
	 * 
	 * @return 返回excel 文件<P/>
	 * 
	 * @throws Exception<P/>
	 * 
	 */
	@GetMapping(value = "/excel/{tables}/{page}/{pageSize}")
	public void getUserInfoEx(HttpServletResponse response, @PathVariable("tables") String tablesName,
			@PathVariable("page") int page, @PathVariable("pageSize") int pageSize) throws Exception {

		String tables = tablesName.replaceAll(" ", ""); 
		if (tables.contains("--") || tables.equals(""))
			return;
		
		
		if(!tables.contains(",")) {// 单个走这个
			HashMap<String, Object> map0 = new HashMap<String, Object>();
			map0.put("sql", "select * from " + tables + " limit " + pageSize + " OFFSET " + (page - 1) * pageSize);

			List<HashMap<String, Object>> userList = ps.getTableData(map0);

			if (userList.size() == 0)
				return;
			
			List<HashMap<String, Object>> tableinfoList = gettablesinfo(tables);
			
			String zarr = "";
			for (int z =0; z< tableinfoList.size(); z++) { 
				zarr += "," + tableinfoList.get(z).get("comment");
			}
//			for (String str : userList.get(0).keySet()) {
//				zarr += "," + str;
//			}
			String[] theadarr = zarr.substring(1).split(",");

			String fileName = tables + "_data.xls"; // 文件名
			String workbookname = "table"; // 工作页名
			dcsj(theadarr, userList, response, fileName, workbookname);
		}else {
			// 多个走这个
			ApplicationHome home = new ApplicationHome(getClass());
			File jarFile = home.getSource();
			String FilePath = jarFile.getParentFile().toString() + "/zorefile/";
			File fileDir = new File(FilePath); 
			if (!fileDir.exists()) {
				try {
					// 按照指定的路径创建文件夹
					fileDir.setWritable(true, false);
					fileDir.mkdir();
				} catch (Exception e) {
					logger.info(e.toString());
				}
			}
			
			List<String> fileNames = new ArrayList<String>();
			for (int i = 0; i < tables.split(",").length; i++) {
				HashMap<String, Object> map0 = new HashMap<String, Object>();
				map0.put("sql", "select * from " + tables.split(",")[i] + " limit " + pageSize + " OFFSET " + (page - 1) * pageSize);
				List<HashMap<String, Object>> userList = ps.getTableData(map0);
				if (userList.size() == 0)
					 continue;

				List<HashMap<String, Object>> tableinfoList = gettablesinfo(tables.split(",")[i]);
				
				String zarr = "";
				for (int z =0; z< tableinfoList.size(); z++) { 
					zarr += "," + tableinfoList.get(z).get("comment");
				}
				
//				String zarr = "";
//				for (String str : userList.get(0).keySet()) {
//					zarr += "," + str;
//				}
				String[] theadarr = zarr.substring(1).split(",");

				String fileName = tables.split(",")[i] + "_data.xls"; // 文件名
				String workbookname = "table"; // 工作页名
				
				String file = dcsjs(theadarr, userList, response, fileName, workbookname, FilePath);

				fileNames.add(file);
			}
			
			pzfiles(response, fileNames,FilePath);
			logger.info("开始清空缓存目录"+FilePath);
			delAllFile(FilePath);
			return;
		}
		
	}

	public List<HashMap<String,Object>> gettablesinfo(String tablename){
		 String sql = " SELECT \r\n" + 
		 		"		 	col_description(a.attrelid,a.attnum) as comment,\r\n" + 
		 		"		 	a.attname as name\r\n" + 
		 		"		FROM \r\n" + 
		 		"			pg_class as c,pg_attribute as a \r\n" + 
		 		"		where \r\n" + 
		 		"			c.relname = '"+tablename+"' \r\n" + 
		 		"			and a.attrelid = c.oid \r\n" + 
		 		"			and a.attnum>0 ";
		HashMap<String,Object> map = new HashMap<String,Object>();
		map.put("sql", sql);
		return ps.getTablesInfo(map);
	}
	// 处理多个文件
	private String dcsjs(String[] theadarr, List<HashMap<String, Object>> rows, HttpServletResponse response,
			String fileName, String workbookname, String FilePath) throws Exception {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(workbookname);
		createTitle(workbook, sheet, theadarr);// 创建表头
		// 设置日期格式
//        HSSFCellStyle style = workbook.createCellStyle();
//        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
		// 新增数据行，并且设置单元格数据
		int rowNum = 1;
		for (HashMap<String, Object> obj : rows) {
			HSSFRow row = sheet.createRow(rowNum);
			int numl = 0;
			for (Object str : obj.keySet()) {
				row.createCell(numl++).setCellValue(obj.get(str).toString());
			}
			rowNum++;
		}
		// 生成excel文件
		buildExcelFile(fileName, workbook);

		String savePath = FilePath  +fileName;
		
		logger.info(savePath);
		
		// 输出Excel文件
		try {
			File savePathfile = new File(savePath);
			if (!savePathfile.exists()) {
				try {
					savePathfile.createNewFile();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			FileOutputStream fos = new FileOutputStream(new File(savePath));
			workbook.write(fos);
			workbook.close();
			fos.close();
			logger.info("生成excel文档成功");
		} catch (Exception e) {
			logger.info(e.toString());
			logger.info("生成excel文档失败");
		}
		return savePath;
	}

	/**
	 * 导出excel
	 * 
	 * @param theadarr
	 * @param rows
	 * @param response
	 * @param fileName
	 * @param workbookname
	 * @throws Exception
	 */
	private void dcsj(String[] theadarr, List<HashMap<String, Object>> rows, HttpServletResponse response,
			String fileName, String workbookname) throws Exception {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(workbookname);
		createTitle(workbook, sheet, theadarr);// 创建表头
		// 设置日期格式
//        HSSFCellStyle style = workbook.createCellStyle();
//        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
		// 新增数据行，并且设置单元格数据
		int rowNum = 1;
		for (HashMap<String, Object> obj : rows) {
			HSSFRow row = sheet.createRow(rowNum);
			int numl = 0;
			for (Object str : obj.keySet()) {
				row.createCell(numl++).setCellValue(obj.get(str).toString());
			}
			rowNum++;
		}
		// 生成excel文件
		buildExcelFile(fileName, workbook);

		// 浏览器下载excel
		buildExcelDocument(fileName, workbook, response);
	}

	// 创建表头
	private void createTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] theadarr) {
		HSSFRow row = sheet.createRow(0);
		// 设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
		sheet.setColumnWidth(1, 12 * 256);
		sheet.setColumnWidth(3, 17 * 256);
		// 设置为居中加粗
		HSSFCellStyle style = workbook.createCellStyle();
		HSSFFont font = workbook.createFont();
		font.setBold(true);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFont(font);
		HSSFCell cell;
		for (int i = 0; i < theadarr.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(theadarr[i]);
			cell.setCellStyle(style);
		}
	}

	// 生成excel文件
	protected void buildExcelFile(String filename, HSSFWorkbook workbook) throws Exception {
		FileOutputStream fos = new FileOutputStream(filename);
		workbook.write(fos);
		fos.flush();
		fos.close();
	}

	// 浏览器下载excel
	protected void buildExcelDocument(String filename, HSSFWorkbook workbook, HttpServletResponse response)
			throws Exception {
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8"));
		OutputStream outputStream = response.getOutputStream();
		workbook.write(outputStream);
		outputStream.flush();
		outputStream.close();
	}

	// 瓶装files 及返回zip
	public void pzfiles(HttpServletResponse response, List<String> fileNames,String FilePath) {
		 

		String zipFilePath = FilePath + "dataresource.zip";

		// 导出zip
		File zip = new File(zipFilePath);

		// 将excel文件生成压缩文件
		File srcfile[] = new File[fileNames.size()];
		for (int i = 0; i < fileNames.size(); i++) {
			srcfile[i] = new File(fileNames.get(i));
		}

		filesToZip(srcfile, zip);
		response.setContentType("application/zip");
		response.setHeader("Location", zip.getName());
		response.setHeader("Content-Disposition", "attachment; filename=" + zip.getName());

		try {
			OutputStream outputStream = response.getOutputStream();
			InputStream inputStream = new FileInputStream(zipFilePath);
			byte[] buffer = new byte[1024];
			int i = -1;
			while ((i = inputStream.read(buffer)) != -1) {
				outputStream.write(buffer, 0, i);
			}
			outputStream.flush();
			outputStream.close();
			inputStream.close();
			outputStream = null;
		} catch (IOException e) {
			logger.info(e.toString());
		} finally {
		}

	}

	// 生成压缩包
	public void filesToZip(File[] srcFiles, File zipFile) {
		// 判断压缩后的文件存在不，不存在则创建
		if (!zipFile.exists()) {
			try {
				zipFile.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		// 创建 FileOutputStream 对象
		FileOutputStream fileOutputStream = null;
		// 创建 ZipOutputStream
		ZipOutputStream zipOutputStream = null;
		// 创建 FileInputStream 对象
		FileInputStream fileInputStream = null;
		try {
			// 实例化 FileOutputStream 对象
			fileOutputStream = new FileOutputStream(zipFile);
			// 实例化 ZipOutputStream 对象
			zipOutputStream = new ZipOutputStream(fileOutputStream);
			// 创建 ZipEntry 对象
			ZipEntry zipEntry = null;
			// 遍历源文件数组
			for (int i = 0; i < srcFiles.length; i++) {
				// 将源文件数组中的当前文件读入 FileInputStream 流中
				fileInputStream = new FileInputStream(srcFiles[i]);
				// 实例化 ZipEntry 对象，源文件数组中的当前文件
				zipEntry = new ZipEntry(srcFiles[i].getName());
				zipOutputStream.putNextEntry(zipEntry);
				// 该变量记录每次真正读的字节个数
				int len;
				// 定义每次读取的字节数组
				byte[] buffer = new byte[1024];
				while ((len = fileInputStream.read(buffer)) > 0) {
					zipOutputStream.write(buffer, 0, len);
				}
			}
			zipOutputStream.closeEntry();
			zipOutputStream.close();
			fileInputStream.close();
			fileOutputStream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	// 以模板形式生成excel
	@RequestMapping(value = "exportDetail")
	public void exportDetail(HttpServletResponse response, HttpServletRequest request)
			throws InvalidFormatException, IOException {
		// 模板在项目存放位置
		String fileRootPath = request.getSession().getServletContext().getRealPath("/templates");
		// 模板文件名称
		String fileName = "";
		// 将excel导出的文件位置
		String filePath = "c:/" + File.separator;
		// 得到此路径下文件
		File fileDir = new File(filePath);
		// 创建文件夹
		if (!fileDir.exists() && !fileDir.isDirectory()) {
			fileDir.mkdirs();
		}
//		// 用于存放生成的excel文件名称
		List<String> fileNames = new ArrayList<String>();
//		// 导出Excel文件路径
//		String fullFilePath = "";
//		// 输入流
//		InputStream in = null;
//		// 输出流
//		FileOutputStream os = null;
//		// 循环导出excel到临时文件夹中
//		for (int i = 0; i < 10; i++) {
//			// 往excel填入内容
//			Map<String, Object> bean = new HashMap<String, Object>();
//			bean.put("xx", "xx");
//			// 每次导出的excel的文件名
//			String fileNameS = "xxxxx.xls";
//			if (bean != null) {
//				// XLSTransformer生成excel文件
//				XLSTransformer transformer = new XLSTransformer();
//				in = new FileInputStream(new File(fileRootPath + File.separator + fileName));
//				HSSFWorkbook workbook;
//				// 设置sheet页名称
//				String sheetName = "详细";
//				workbook = (HSSFWorkbook) transformer.transformXLS(in, bean);
//				// 设置sheet页名称
//				workbook.setSheetName(0, sheetName);
//				// 导出excel的全路径
//				fullFilePath = filePath + File.separator + fileNameS;
//				fileNames.add(fullFilePath);
//				os = new FileOutputStream(fullFilePath);
//				// 写文件
//				workbook.write(os);
//			}
//			// 清空流缓冲区数据
//			os.flush();
//			// 关闭流
//			os.close();
//			in.close();
//			os = null;
//		}

		ApplicationHome home = new ApplicationHome(getClass());
		File jarFile = home.getSource();
		String zipfile = jarFile.getParentFile().toString();

		// 导出压缩文件的全路径
		String zipFilePath = filePath + "压缩文件名" + ".zip";
		// 导出zip
		File zip = new File(zipFilePath);
		// 将excel文件生成压缩文件
		File srcfile[] = new File[fileNames.size()];
		for (int j = 0, n1 = fileNames.size(); j < n1; j++) {
			srcfile[j] = new File(fileNames.get(j));
		}
		ZipFiles(srcfile, zip);
		response.setContentType("application/zip");
		response.setHeader("Location", zip.getName());
		response.setHeader("Content-Disposition", "attachment; filename=" + zip.getName());
		OutputStream outputStream = response.getOutputStream();
		InputStream inputStream = new FileInputStream(zipFilePath);
		byte[] buffer = new byte[1024];
		int i = -1;
		while ((i = inputStream.read(buffer)) != -1) {
			outputStream.write(buffer, 0, i);
		}
		outputStream.flush();
		outputStream.close();
		inputStream.close();
		outputStream = null;

		try {
			delAllFile(filePath); // 删除完里面所有内容
			filePath = filePath.toString();
			java.io.File myFilePath = new java.io.File(filePath);
			myFilePath.delete(); // 删除空文件夹
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 压缩文件
	public void ZipFiles(File[] srcfile, File zipfile) {
		byte[] buf = new byte[1024];
		try {
			ZipOutputStream out = new ZipOutputStream(new FileOutputStream(zipfile));
			for (int i = 0; i < srcfile.length; i++) {
				FileInputStream in = new FileInputStream(srcfile[i]);
				out.putNextEntry(new ZipEntry(srcfile[i].getName()));
				int len;
				while ((len = in.read(buf)) > 0) {
					out.write(buf, 0, len);
				}
				out.closeEntry();
				in.close();
			}
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/***
	 * 删除指定文件夹下所有文件
	 * 
	 * @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]);// 先删除文件夹里面的文件
				flag = true;
			}
		}
		return flag;
	}
}
Copy the code

 

 

ok

 

 

 

 

Continuously updated