java 程序分别写入Excel及text文件内容并实现下载功能

最新推荐文章于 2024-08-29 03:49:18 发布

deelless

最新推荐文章于 2024-08-29 03:49:18 发布

阅读量1.6k

点赞数 4

分类专栏：文件

本文链接：https://blog.csdn.net/weixin_43078114/article/details/90753277

版权

文件专栏收录该内容

7 篇文章 1 订阅

订阅专栏

1.导包

	<dependency>
		<groupId>org.apache.commons</groupId>
		<artifactId>commons-lang3</artifactId>
		<version>3.0</version>
	</dependency>

	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.16</version>
	</dependency>

	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.16</version>
	</dependency>

2.controller

import com.example.demo.util.ExcelUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
@Controller
@RequestMapping("/mail")
public class downloadExampleController {
	/**
	 *下载示例
	 *@param:fileExt：文件类型   xlsx为excel文件，txt文件文本文件
	 *@return:
	 *@Author:zym
	 *@Date:2019/6/3
	 */
	@RequestMapping("/downloadExample.do")
	public void downloadExample( String fileExt, HttpServletRequest request,
								HttpServletResponse response) throws ServletException, IOException {
		response.reset();
		String fileName = "联系人地址示例";
	    if (fileExt != null && (fileExt.equalsIgnoreCase("xlsx"))) {
			fileName = fileName + ".xlsx";
		} else {
			fileName = fileName + ".txt";
		}
		// 表头
		StringBuilder data = new StringBuilder();
	    //判断参数类型，构造excel文件。equalsIgnoreCase方法比较两个字符串是否相等并且忽略大小写
		if (StringUtils.equalsIgnoreCase(fileExt, "xlsx")) {
			// Excel文件表头
			String[] excelHead = {"邮箱", "昵称", "手机"};
			XSSFWorkbook workbook = new XSSFWorkbook();
			//行
			List<List<Object>> rowList = new ArrayList<>();
			//列
			List<Object> columnList1 = new ArrayList<>();
				//Excel第一行，第一列
				columnList1.add("example@example.com");
				List<Object> columnList2=new ArrayList<>();
				//Excel第二行，第一列
				columnList2.add("example@example.com");
				//Excel第二行，第二列
				columnList2.add("姓名");
				List<Object> columnList3=new ArrayList<>();
				//Excel第三行，第一列
				columnList3.add("example@example.com");
				//Excel第三行，第二列
				columnList3.add("姓名1");
				//Excel第三行，第三列
				columnList3.add("13112345678");

				rowList.add(columnList1);
				rowList.add(columnList2);
				rowList.add(columnList3);


			ExcelUtil.createExcelSheet(workbook, excelHead, rowList, fileName.substring(0, fileName.lastIndexOf(".")));
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			workbook.write(os);
			fileName = ExcelUtil.encodeFilename(fileName, request);
			this.download(fileName, os.toByteArray(), request, response);
			//默认其他参数构造txt文件内容
		} else {   
				data.append("邮箱，昵称，手机").append("\r\n");
				data.append("example@example.org").append("\r\n");
				data.append("example1@example.org,姓名1").append("\r\n");
				data.append("example2@example.org,姓名2,13112345678").append("\r\n");
			fileName = ExcelUtil.encodeFilename(fileName, request);
			this.download(fileName, data, request, response);
		}
	}


	//下载Excel文件
	public void download(String fileName, byte[] data, HttpServletRequest request, HttpServletResponse response) throws IOException {
		try {
			OutputStream out = response.getOutputStream();
			response.setContentType("application/octet-stream");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName);
			out.write(data);
			out.flush();
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	//下载文本文件
	public void download(String fileName, StringBuilder data, HttpServletRequest request, HttpServletResponse response) throws IOException {
		OutputStream ouputStream = response.getOutputStream();
		try {
			response.setContentType("application/csv;charset=GBK");
			request.setCharacterEncoding("GBK");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName);
			ouputStream.write(data.toString().getBytes("GBK"));
			ouputStream.flush();
			ouputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

3.测试页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8"  />
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1"/>
    <title>单文件上传</title>
</head>
<body>
    <a href="/mail/downloadExample.do?fileExt=txt">TXT"&nbsp;示例下载"</a>
    <br>
    <br>
    <a href="/mail/downloadExample.do?fileExt=xlsx">excle"&nbsp;示例下载"</a>
</body>
</html>

在这里插入图片描述

4.1txt示例：

在这里插入图片描述

4.2Excel示例：

在这里插入图片描述

补充：ExcelUtil类

import com.sun.xml.internal.messaging.saaj.packaging.mime.internet.MimeUtility;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletRequest;
import java.net.URLEncoder;
import java.util.List;

public class ExcelUtil {

	/**
	 * 样式设置
	 */
	public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook) {
		// *生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置这些样式
		// 前景色
		style.setFillForegroundColor(HSSFColor.WHITE.index);
		// 背景色
		style.setFillBackgroundColor(HSSFColor.WHITE.index);
		// 填充样式
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		// 设置底边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// 设置底边框颜色
		style.setBottomBorderColor(HSSFColor.WHITE.index);
		// 设置左边框
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		// 设置左边框颜色
		style.setLeftBorderColor(HSSFColor.BLACK.index);
		// 设置右边框
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		// 设置右边框颜色
		style.setRightBorderColor(HSSFColor.BLACK.index);
		// 设置顶边框
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// 设置顶边框颜色
		style.setTopBorderColor(HSSFColor.BLACK.index);
		// 设置自动换行
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置垂直对齐的样式为居中对齐
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// HSSFFont font = createCellFont(workbook);
		// // 把字体应用到当前的样式
		// style.setFont(font);
		return style;
	}

	/**
	 * 设置下载文件中文件的名称
	 *
	 * @param filename
	 * @param request
	 * @return
	 */
	public static String encodeFilename(String filename, HttpServletRequest request) {
		/**
		 * 获取客户端浏览器和操作系统信息 在IE浏览器中得到的是：User-Agent=Mozilla/4.0 (compatible; MSIE
		 * 6.0; Windows NT 5.1; SV1; Maxthon; Alexa Toolbar)
		 * 在Firefox中得到的是：User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1;
		 * zh-CN; rv:1.7.10) Gecko/20050717 Firefox/1.0.6
		 */
		String agent = request.getHeader("USER-AGENT");
		try {

			if ((agent != null) && (-1 != agent.indexOf("MSIE"))) {
				String newFileName = URLEncoder.encode(filename, "UTF-8");
				newFileName = StringUtils.replace(newFileName, "+", "%20");
				if (newFileName.length() > 150) {
					newFileName = new String(filename.getBytes("GB2312"), "ISO8859-1");
					newFileName = StringUtils.replace(newFileName, " ", "%20");
				}
				return newFileName;
			}
			if ((agent != null) && (-1 != agent.indexOf("Mozilla")))
				return MimeUtility.encodeText(filename, "UTF-8", "B");

			return filename;
		} catch (Exception ex) {
			return filename;
		}
	}
	/**
	 * 添加excel的sheet
	 * @param workbook 工作簿
	 * @param head excle的头部
	 * @param list 2维数组,excle的内容
	 * @param sheetName sheet的名称
	 */
	public static XSSFWorkbook createExcelSheet(XSSFWorkbook workbook, String[] head, List<List<Object>> list, String sheetName) {
		if (workbook == null) {
			workbook = new XSSFWorkbook();
		}
		// 生成一个表格
		XSSFSheet sheet = workbook.createSheet(sheetName);
		int rowIndex = 0;

		if (ArrayUtils.isNotEmpty(head)) {
			XSSFRow row = sheet.createRow(rowIndex);
			rowIndex++;

			// 设置表格头部
			for (int i = 0; i < head.length; i++) {
				// 创建单元格
				XSSFCell cell = row.createCell(i);
				// 获取内容
				XSSFRichTextString text = new XSSFRichTextString(head[i]);
				// 设置单元格内容
				cell.setCellValue(text);
				// 指定单元格格式：数值、公式或字符串
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			}
		}

		// 遍历集合数据,产生表格数据行
		for (int i=0; i<list.size(); i++) {
			// 创建新行(row)
			XSSFRow row = sheet.createRow(i + rowIndex);
			// 获取内容
			List<Object> valueList = list.get(i);
			for (int j=0; j<valueList.size(); j++) {
				// 创建单元格
				XSSFCell cell = row.createCell(j);
				Object obj = valueList.get(j);
				setCellValue(cell, obj);
			}
		}

		return workbook;
	}

	/**
	 * 设置单元格的格式和值
	 * @param cell
	 * @param obj
	 */
	private static void setCellValue(XSSFCell cell, Object obj) {
		if (obj instanceof String) {
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue((String) obj);
		} else if (obj instanceof Integer) {
			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue((Integer) obj);
		} else if (obj instanceof Double) {
			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue((Double) obj);
		} else if (obj instanceof Long) {
			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue((Long) obj);
		} else {
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(String.valueOf(obj));
		}
	}

}