Java使用poi生成Excel文件

poi-3.8-20100326.jar

1。

 

package com.jnpc.protect.radiation.action;

import java.io.FileOutputStream;
import java.io.IOException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

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.apache.poi.ss.usermodel.Cell;

/**
 * 生成excel
 * 使用的是HSSFWorkbook，仅能生成xls格式的excel
 * 在创建存储excel文件流的时候注意不能用.xlsx的扩展名
 * @author Uself 2017-10-12 下午4:21:43
 * @see	http://www.cnblogs.com/yxth/p/6495244.html
 * 		http://blog.csdn.net/preterhuman_peak/article/details/40615303
 */
public class CreateExcel {
	//excel表头名称
	private static String[] COLUMN_NAMES = {"ID", "姓名", "年龄", "性别"};
	//填充数据的key
	private static String[] COLUMNS = {"id", "name", "age", "sex"};
	public static void main(String[] args) {
		JSONArray ary = new JSONArray();
		JSONObject obj = new JSONObject();
		JSONObject obj2 = new JSONObject();
		JSONObject obj3 = new JSONObject();
		obj.put("id", "1");
		obj.put("name", "小明");
		obj.put("age", "18");
		obj.put("sex", "男");
		obj2.put("id", "2");
		obj2.put("name", "小华");
		obj2.put("age", "20");
		obj2.put("sex", "男");
		obj3.put("id", "3");
		obj3.put("name", "小丽");
		obj3.put("age", "18");
		obj3.put("sex", "女");
		ary.add(obj);
		ary.add(obj2);
		ary.add(obj3);
		createExcel(ary, "第一页", "D:/testCreateExcel.xls");
	}
	/**
	 * 生成excel表格
	 * @param jsonArray	填充excel的对象，可以灵活的修改成List<T>
	 * @param sheetName	sheet页的名字
	 * @param savePath	excel保存的路径 D:/text.xls
	 */
	public static void createExcel(JSONArray jsonArray, String sheetName, String savePath){
		FileOutputStream out = null;
		try {
			//创建工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			//创建sheet
			HSSFSheet sheet = createSheet(workbook, sheetName);
			//创建单元格样式
			HSSFCellStyle style = createCellStyle(workbook);
			//创建头部
			createHead(sheet, style);
			//创建主体内容
			createMainContent(sheet, style, jsonArray);
			out = new FileOutputStream(savePath);
			workbook.write(out);


            /*直接下载
response.setContentType("multipart/form-data");
        String fileName = "会议执行情况汇总表（" + month + "月）.xls";
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8") + ";filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
        BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream());
        workbook.write(bos);
        bos.flush();
        workbook.close();*/

		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("======e========" + e.getMessage());
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 创建sheet
	 * @param workbook	工作簿
	 * @param sheetName	sheet名字
	 * @return
	 */
	public static HSSFSheet createSheet(HSSFWorkbook workbook, String sheetName){
		HSSFSheet sheet = null;
		//2创建sheet表格
		sheet = workbook.createSheet(sheetName);
                //设置所有的单元格格式
                sheet.setDefaultColumnWidth(20);
                //单独设置时必须是256的倍数
                //sheet.setColumnWidth(0,5120);  
                //sheet.setColumnWidth(1,5120);  
                //sheet.setColumnWidth(2,25600);
                return sheet;
	}
	/**
	 * 创建单元格样式
	 * @param workbook	工作簿
	 * @return
	 */
	public static HSSFCellStyle createCellStyle(HSSFWorkbook workbook){
		HSSFCellStyle style = null;
		//1创建文字样式
		HSSFFont font = workbook.createFont();
		//设置文字字体
		font.setFontName("宋体");
		//设置文字大小
		font.setFontHeightInPoints((short) 10);
		//2.创建单元格样式
		style = workbook.createCellStyle();
		// 左右居中  
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
		// 上下居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//设置边框大小
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		//3.将文字样式应用到单元格中
		style.setFont(font);
		return style;
	}
	/**
	 * 设置excel头部
	 * @param sheet
	 * @param style
	 */
	public static void createHead(HSSFSheet sheet, HSSFCellStyle style){
		HSSFRow row = sheet.createRow(0);
		for(int i = 0; i < COLUMN_NAMES.length; i ++){
			Cell cell = row.createCell(i);
			cell.setCellValue(COLUMN_NAMES[i]);
			cell.setCellStyle(style);
		}
	}
	/**
	 * 创建主题内容
	 * @param sheet		sheet表格
	 * @param style		单元格样式
	 * @param jsonArray	json数据对象
	 */
	public static void createMainContent(HSSFSheet sheet, HSSFCellStyle style, JSONArray jsonArray){
		/*
		 * 遍历数据创建相应的行以及单元格
		 * 第一行已经创建，从第二行开始创建，下标为1
		 */
		for(int i = 1; i <= jsonArray.size(); i ++){
			//创建行
			HSSFRow row = sheet.createRow(i);
			JSONObject jsonObject = jsonArray.getJSONObject(i - 1);
			for(int j = 0; j < COLUMNS.length; j ++){
				Cell cell = row.createCell(j);
				cell.setCellValue(jsonObject.getString(COLUMNS[j]));
				cell.setCellStyle(style);
			}
		}
	}
}

在文件输出的时候，添加下面的代码可以定义名称，实现直接下载，不需要指定文件目录

OutputStream out = response.getOutputStream();
String fileName = "excel.xls";
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8") + ";filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
workBook.write(out);