欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

java编程EXCEL导出,支持多sheet页导出

程序员文章站 2022-07-13 12:58:27
...
  1. excel导出工具类
import java.util.Collection;

/**
 * Excle导出参数列表实体类
 */

public class ExportExcleDTO<T> {

	 * 
	 * @param title
	 *            表格标题名
	 * @param headers
	 *            表格属性列名数组
	 * @param dataset
	 *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
	 *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
	 * @param pattern
	 *            如果有时间数据,设定输出格式。默认为"yyyMMdd"
	 * @param formatType
	 *            如果有Double类型数据,设定数字输出格式,默认为"General"
	 */
	private String title;
	private String[] headers;
	private Collection<T> dataSet;
	private String pattern;
	private String formatType;
	
	public ExportExcleDTO() {}
	
	public ExportExcleDTO(String title, String[] headers, Collection<T> dataSet, String pattern, String formatType) {
		super();
		this.title = title;
		this.headers = headers;
		this.dataSet = dataSet;
		this.pattern = pattern;
		this.formatType = formatType;
	}

	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String[] getHeaders() {
		return headers;
	}
	public void setHeaders(String[] headers) {
		this.headers = headers;
	}
	public Collection<T> getDataSet() {
		return dataSet;
	}
	public void setDataSet(Collection<T> dataSet) {
		this.dataSet = dataSet;
	}
	public String getPattern() {
		return pattern;
	}
	public void setPattern(String pattern) {
		this.pattern = pattern;
	}
	public String getFormatType() {
		return formatType;
	}
	public void setFormatType(String formatType) {
		this.formatType = formatType;
	}
	
}



import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.phfund.common.dto.ExportExcleDTO;
import com.phfund.report.util.ReportUtils;
/**
 * Excle导出工具类
 */
public class ExportExcelUtils<T> {

	private static Logger logger = LogManager.getLogger(ReportUtils.class);

	/**
	 * @param exportList
	 *            Excle导出实体类
	 * @param out
	 *            与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public void exportExcel(List<ExportExcleDTO> exportList, OutputStream out) {
		XSSFWorkbook workbook = null;
		try {
		// 声明一个工作薄
		workbook = new XSSFWorkbook();
		for(ExportExcleDTO<T> exportDTO :exportList){
			// 生成一个表格
			XSSFSheet sheet = workbook.createSheet(exportDTO.getTitle());
			// 设置表格默认列宽度为20个字节
			sheet.setDefaultColumnWidth(20);
			XSSFCellStyle style2 = workbook.createCellStyle();
			XSSFDataFormat df = workbook.createDataFormat();
			//设置边框为细边框
			style2.setBorderBottom(BorderStyle.THIN);
			style2.setBorderLeft(BorderStyle.THIN);
			style2.setBorderRight(BorderStyle.THIN);
			style2.setBorderTop(BorderStyle.THIN);

			// 产生表格标题行
			XSSFRow row = sheet.createRow(0);
			for (short i = 0; i < exportDTO.getHeaders().length; i++) {
				XSSFCell cell = row.createCell(i);
				XSSFCellStyle style3 = workbook.createCellStyle();
				style3.setBorderBottom(BorderStyle.THIN);
				style3.setBorderLeft(BorderStyle.THIN);
				style3.setBorderRight(BorderStyle.THIN);
				style3.setBorderTop(BorderStyle.THIN);
				XSSFFont font = workbook.createFont();
				font.setBold(true);//字体加粗
				style3.setFont(font);
				cell.setCellStyle(style3);
				XSSFRichTextString text = new XSSFRichTextString(exportDTO.getHeaders()[i]);
				cell.setCellValue(text);
			}

			// 遍历集合数据,产生数据行
			Iterator<T> it = exportDTO.getDataSet().iterator();
			int index = 0;
			while (it.hasNext()) {
				index++;
				row = sheet.createRow(index);
				T t = (T) it.next();
				// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
				String typeName = t.getClass().getTypeName();
				if("java.util.LinkedHashMap".equals(typeName)){
					LinkedHashMap map = (LinkedHashMap) t;
					int i=0;
					for (Object key : map.keySet()) {
						XSSFCell cell = row.createCell(i);
						cell.setCellStyle(style2);
						Object value = map.get(key);
						setValue(exportDTO.getPattern(), exportDTO.getFormatType(), style2, df, cell, value);
						i++;
					}
				}else{
					Field[] fields = t.getClass().getDeclaredFields();
					for (short i = 0; i < fields.length; i++) {
						XSSFCell cell = row.createCell(i);
						cell.setCellStyle(style2);
						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[] {});
							setValue(exportDTO.getPattern(), exportDTO.getFormatType(), style2, df, cell, value);
						} catch (Exception e) {
							logger.error("导出Excel异常,信息:" + e.getMessage());
						}
					}
				}
			}
			
		}
		workbook.write(out);
		} catch (IOException e) {
			logger.error("导出Excel异常,信息:" + e.getMessage());
		}finally{
			try {
				workbook.close();
			} catch (IOException e) {
				logger.error("关闭workbook异常,信息:" + e.getMessage());
			}
		}
	}

	@SuppressWarnings("deprecation")
	private void setValue(String pattern, String formatType,
			XSSFCellStyle style2, XSSFDataFormat df, XSSFCell cell, Object value) {
		// 判断值的类型后进行强制类型转换
		String textValue = null;
		if (value instanceof Integer) {
			int intValue = (Integer) value;
			cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue(intValue);
		} else if (value instanceof Float) {
			float fValue = (Float) value;
			XSSFRichTextString xfValue = new XSSFRichTextString(
					String.valueOf(fValue));
			cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue(xfValue);
		} else if (value instanceof Double) {
			double dValue = (Double) value;
			cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
			//设置数字输出格式
			style2.setDataFormat(df.getFormat(formatType));
			cell.setCellStyle(style2);
			cell.setCellValue(dValue);
		} else if (value instanceof Long) {
			long longValue = (Long) value;
			cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue(longValue);
		} else if (value instanceof Date) {
			Date date = (Date) value;
			SimpleDateFormat sdf = new SimpleDateFormat(pattern);
			textValue = sdf.format(date);
		} else if(value instanceof String){
			cell.setCellValue(value+"");
		}else {
			// 其它数据类型都当作字符串简单处理
			if (value == null || "null".equals(value)) {
				textValue = "";
			} else {
				textValue = value + "";
			}
		}
		// 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成
		if (textValue != null) {
			//Pattern p = Pattern.compile("^//d+(//.//d+)?$");
			Pattern p = Pattern.compile("^[+-]?([1-9][0-9]*|0)(\\.[0-9]+)?$");
			Matcher matcher = p.matcher(textValue);
			if (matcher.matches()) {
				// 是数字当作double处理
				cell.setCellValue(Double.parseDouble(textValue));
			} else {
				XSSFRichTextString richString = new XSSFRichTextString(textValue);
				cell.setCellValue(richString);
			}
		}
	}

}

相关标签: java