Java poi Excel下载

Java Excel下载

通过查数据库表，获得数据，然后将数据插入Excel表后下载

通过三种方法简洁明了说明了具体步骤

大家需要哪个看哪个方法就行，创建Excel的时候为了好看我设置了一些东西，如果不需要可以直接删掉

创建新的Excel表 createExcel方法

向Excel表中添加数据 addOobExcel方法

下载Excel downExcel方法

尬包：

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.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;

代码：

public class asd {
	public String newFileName = null;
	public File newFileDir = null;
	public String toFilePath = null;
	public static final String[] columnName = { "列一(选填)", "列二(必填)", "列三(选填)" };
	
	/**
	 * 将数据库中的数据导出Excel
	 * @param textString 数据库查出的数据
	 * @return
	 */
	public void downDevice(List<Student> textString)
	{
		//Excel下载的路径,为tomcat下项目中的class文件下，也可自己定义路径
		//the result save:/D:/tomcat8/tomcat/webapps/CIS/WEB-INF/classes/Excel_1509157574761.xls
		String path = Thread.currentThread().getContextClassLoader ().getResource("").getPath();
		//以当前时间为新的Excel文件名称
		this.newFileName = "Excel_" + System.currentTimeMillis() + ".xls";
		//Excel保存路径
		this.toFilePath = path + newFileName;
		// 如果文件夹不存在，就创建文件夹。
		newFileDir = new File(toFilePath);
		if (!(newFileDir.exists()) && !(newFileDir.isDirectory()))
		{
			boolean creadok = newFileDir.mkdirs();
			if (!creadok)
			{
				System.out.println("Failed to create dir: " + toFilePath);
			}
		}
		System.out.println("the result save:" + toFilePath);
		// 创建新的Excel表
		createExcel(newFileDir.getAbsolutePath(), toFilePath, newFileName, columnName);
		// 向Excel表中添加数据
		addOobExcel(toFilePath, newFileName, textString);
		// 下载Excel
		downExcel(toFilePath);
		
	}
	/**
	 * 创建Excel表
	 * @param targetFilePath 
	 * @param toFilePath Excel保存路径
	 * @param sheetName Excel名称
	 * @param titleRow Excel列名
	 */
	@SuppressWarnings("resource")
	public void createExcel(String targetFilePath, String toFilePath, String sheetName, String titleRow[])
	{
		System.out.println("create new Excel");
		// 创建workbook
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
		HSSFSheet sheet1 = workbook.createSheet(sheetName);
		FileOutputStream fo = null;
		BufferedOutputStream out = null;
		File file = new File(targetFilePath);
		if (!file.exists())
		{
			System.out.println("file：" + targetFilePath + "not exit");
			return;
		}
		if (new File(toFilePath).exists())
		{
			new File(toFilePath).delete();
		}
		try
		{
			fo = new FileOutputStream(toFilePath);
			out = new BufferedOutputStream(fo);
			// 添加表头
			Row row = sheet1.createRow(0); // 创建第一行
			for (int i = 0; i < titleRow.length; i++)
			{
				String string = titleRow[i];
				if (string.contains("选填"))
				{
					HSSFCellStyle style = workbook.createCellStyle();
					//设置字体加粗
					HSSFFont font = workbook.createFont();
					//粗体显示
					font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
					//设置字体格式
					font.setFontName("宋体");
					style.setFont(font);
					// 设置背景颜色
					style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
					style.setFillPattern(CellStyle.SOLID_FOREGROUND);
					//自定义RGB
					HSSFPalette customPalette = workbook.getCustomPalette();
					customPalette.setColorAtIndex(IndexedColors.LIGHT_YELLOW.getIndex(),
							(byte) 255, (byte) 230, (byte) 153);
					// 设置列宽
					sheet1.setColumnWidth(i, 9000);

					// 创建第i列
					HSSFCell cell = (HSSFCell) row.createCell(i);
					cell.setCellValue(titleRow[i]);
					//让设置生效
					cell.setCellStyle(style);
				} else
				{
					HSSFCellStyle style = workbook.createCellStyle();
					//设置字体加粗
					HSSFFont font = workbook.createFont();
					//粗体显示
					font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
					//设置字体格式
					font.setFontName("宋体");
					style.setFont(font);
					// 设置背景颜色
					style.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
					style.setFillPattern(CellStyle.SOLID_FOREGROUND);
					//自定义RGB
					HSSFPalette customPalette = workbook.getCustomPalette();
					customPalette.setColorAtIndex(IndexedColors.SEA_GREEN.getIndex(), (byte) 112,
							(byte) 173, (byte) 71);
					// 设置列宽
					sheet1.setColumnWidth(i, 9000);
					// 创建第i列
					HSSFCell cell = (HSSFCell) row.createCell(i);
					cell.setCellValue(titleRow[i]);
					//让设置生效
					cell.setCellStyle(style);
				}
			}
			fo = new FileOutputStream(toFilePath);
			out = new BufferedOutputStream(fo);
			workbook.write(out);
		} catch (Exception e)
		{
			System.out.println(e);
		} finally
		{
			try
			{
				out.flush();
			} catch (IOException e)
			{
				System.out.println(e);
			}
		}
	}
	/**
	 * 向Excel表中添加数据
	 * @param toFilePath Excel保存路径
	 * @param sheetName Excel名称
	 * @param excelOobBeanLst Excel保存的内容
	 */
	public static void addOobExcel(String toFilePath, String sheetName, List<Student> Students)
	{
		System.out.println("Insert Information to NewOobExcel begin");
		// 创建workbook
		File file = new File(toFilePath);
		HSSFWorkbook workbook = null;
		try
		{
			workbook = new HSSFWorkbook(new FileInputStream(file));
		} catch (FileNotFoundException e)
		{
			System.out.println(e);
		} catch (IOException e)
		{
			System.out.println(e);
		}
		// 流
		FileOutputStream out = null;
		HSSFSheet sheet = workbook.getSheet(sheetName);
		// 获取表格的总行数 并加一 为最新一行的数据插入点
		int rowCount = sheet.getLastRowNum() + 1;
		// 获取表头的列数
		int columnCount = sheet.getRow(0).getLastCellNum();
		try
		{
			//传过来实体类的话有多少个属性sb.append多少次
			for (Student s : Students)
			{
				// 获得每列数据
				StringBuffer sb = new StringBuffer();
				//尽量用不常见的符号分割，我用逗号分割出了奇怪BUG
				sb.append(s.getName() + "~");
				sb.append(s.getAge() + "~");
				sb.append(s.getSex() + "~");
				//防止向Excel添加"null"值
				String oob = sb.toString().replaceAll("null", "");
				String[] split = oob.split("~", -1);
				// 最新要添加的一行
				Row row = sheet.createRow(rowCount);
				for (int i = 0; i < columnCount; i++)
				{
					org.apache.poi.ss.usermodel.Cell cell = row.createCell(i);
					// 每列内容写入
					cell.setCellValue(split[i]);
				}
				//行数加一
				rowCount += 1;
			}
			out = new FileOutputStream(toFilePath);
			workbook.write(out);
		} catch (Exception e)
		{
			System.out.println(e);
		} finally
		{
			try
			{
				out.close();
				System.out.println("addExcel end");
			} catch (IOException e)
			{
				System.out.println(e);
			}
		}
	}
	
	/**
	 * 下载Excel
	 * @return
	 */
	public String downExcel(String toFilePath) {
		System.out.println(" start downFile");
		HttpServletResponse response = ServletActionContext.getResponse();
		System.out.println(" start Excel path:"+toFilePath);
		File file = new File(toFilePath);
		String name = file.getName();
		/* 如果文件存在 */
		if (file.exists()) {
			System.out.println(" start down Excel Name:"+name);
			String disName = null;
			try {
				disName = URLEncoder.encode(name, "UTF-8");
			} catch (UnsupportedEncodingException e1) {
				e1.printStackTrace();
			}
			response.reset();
			response.setContentType("application/x-msdownload");
			response.addHeader("Content-Disposition", "attachment; filename=\""
					+ disName + "\"");
			InputStream inStream = null;
			ServletOutputStream servletOS = null;
			try {
				inStream = new FileInputStream(file);
				byte[] buf = new byte[4096];
				/* 创建输出流 */
				System.out.println(" get OutputStream");
				try {
					servletOS = response.getOutputStream();
				} catch (IOException e) {
					e.printStackTrace();
				}
				int readLength;
				while (((readLength = inStream.read(buf)) != -1)) {
					servletOS.write(buf, 0, readLength);
				}
			} catch (FileNotFoundException e1) {
				e1.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			} finally {
				try {
					inStream.close();
					servletOS.flush();
					servletOS.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			System.out.println(" end downFile");
		}
		return null;
	}
}