Java将数据集写入新的excel表中

引入maven依赖

<!-- 操作excel -->
		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>
<!-- 操作excel -->

实例演示

package com.leo.springboot.excel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.leo.springboot.entity.User;
import com.leo.springboot.util.ExportExcel;

public class ExcelOutData {

	public static void main(String[] args) {
		action();
	}

	public static void action() {
		//User为自定义的java实体类，这里就不引入代码了
		// 用于导出的数据集合
		List<User> dataset = new ArrayList<User>();
		// 填充dataset
		for (int i = 0; i < 10; i++) {
			User bean = new User(i, "name", 1, 21);
			dataset.add(bean);
		}
		// Excel文件
		File tempFile = null;
		try {
			// Excel导出工具类
			ExportExcel<User> ex = new ExportExcel<User>();
			String title="页签1";
			// 导出的标题列
			String[] headers = { "标题1", "标题2", "标题3", "标题4", "标题5", "标题6" };
			// 时间格式化
			SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
			// 要保存的文件名
			String filename = "bill_" + format.format(new Date()) + ".xls";
			// 要保存的根目录
			String rootDir = "X:\\Excel";
			// 要保存的目录路径
			String path = rootDir + File.separator + "tempfile";
			File saveDir = new File(path);
			if (!saveDir.exists()) {
				saveDir.mkdirs();// 如果文件不存在则创建文件夹
			}
			// 文件路径
			path = path + File.separator + filename;
			tempFile = new File(path); // 初始化临时文件
			// 输出流
			OutputStream out = new FileOutputStream(tempFile);
			
			// 导出到Excel
			ex.exportExcel(title, headers, dataset, out, "yyyy-MM-dd HH:mm");
			
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		} 
	}
}

封装成，Excel导出工具类

package com.leo.springboot.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

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.HSSFRichTextString;
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.hssf.util.HSSFColor;

public class ExportExcel<T> {
	// 导出2003版本Excel文件。参数列表：Excel页签名，表头名，数据源集合，输出流包含文件地址，日期格式
	public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 生成一个样式
		HSSFCellStyle style = workbook.createCellStyle();
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 12);
		// 把字体应用到当前的样式
		style.setFont(font);
		// 设置低边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// 设置低边框颜色
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (short i = 0; i < headers.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}
		// 遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while (it.hasNext()) {
			index++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			Field[] fields = t.getClass().getDeclaredFields();
			for (short i = 0; i < fields.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(style);
				Field field = fields[i];
				String fieldName = field.getName();
				String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
				try {
					Class tCls = t.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
					Object value = getMethod.invoke(t, new Object[] {});
					// 判断值的类型后进行强制类型转换
					String textValue = null;
					if (value instanceof Integer) {
						int intValue = (Integer) value;
						cell.setCellValue(intValue);
					} else if (value instanceof Long) {
						long longValue = (Long) value;
						cell.setCellValue(longValue);
					} else if (value instanceof Boolean) {
						boolean bValue = (Boolean) value;
						textValue = "1";
						if (!bValue) {
							textValue = "0";
						}
					} else if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat(pattern);
						textValue = sdf.format(date);
					} else {
						// 其它数据类型都当作字符串简单处理
						if (value == null) {
							textValue = "";
						} else {
							textValue = value.toString();
						}

					}

					if (textValue != null) {
						Pattern p = Pattern.compile("^//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if (matcher.matches()) {
							// 是数字当作double处理
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							HSSFRichTextString richString = new HSSFRichTextString(textValue);
							richString.applyFont(font);
							cell.setCellValue(richString);
						}
					}
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				} finally {
					// 清理资源
				}
			}

		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}