Excel导出实现

JxlExportExcel.java

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.joda.time.DateTime;

import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class JxlExportExcel<T> {
	
	

	public JxlExportExcel() {
		super();
		
	}

	private WritableWorkbook workbook;

	private WritableSheet sheet;

	private WritableFont fontHeader;

	private WritableCellFormat formatHeader;

	private WritableFont fontContent;

	private WritableCellFormat formatContent;

	public void exportExcel(String[] headers, List<T> dataset, OutputStream os) {
		exportExcel(null, headers, dataset, os, null);
	}
	
	public void exportExcel(String[] headers, List<T> dataset, OutputStream os, String pattern) {
		exportExcel(null, headers, dataset, os, pattern);
	}

	public void exportExcel(String title, String[] headers, List<T> dataset, OutputStream os, String pattern) {
		if (title == null || title.equals("")) {
			title = "导出Excel列表";
		}
		
		if(pattern == null || pattern.equals("")) {
			pattern = "yyyy-MM-dd HH:mm:ss";
		}
		SimpleDateFormat dateFormat = new SimpleDateFormat(pattern);
		// 创建工作薄
		// WritableWorkbook workbook=null;
		try {
			workbook = Workbook.createWorkbook(os);
			// 创建新的一页
			sheet = workbook.createSheet(title, 0);
			initExcelStyle();
			for (int i = 0; i < headers.length; i++) {
				Label header = new Label(i, 0, headers[i]);
				header.setCellFormat(formatHeader);
				sheet.addCell(header);
			}

			for (int i = 0; i < dataset.size(); i++) {
				addContent(dataset.get(i), i + 1, dateFormat);
			}

		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} finally {
			// 把创建的内容写入到输出流中，并关闭输出流
			try {
				if (workbook != null) {
					workbook.write();
					workbook.close();
				}
				if (os != null) {
					os.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	private void initExcelStyle() throws WriteException {
		SheetSettings settings = sheet.getSettings();
		settings.setDefaultColumnWidth(18);
		sheet.setRowView(0, 500, false);
		fontHeader = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,
				UnderlineStyle.NO_UNDERLINE);
		formatHeader = new WritableCellFormat(fontHeader);
		formatHeader.setBackground(Colour.SKY_BLUE);
		formatHeader.setBorder(Border.ALL, BorderLineStyle.THIN);
		formatHeader.setAlignment(Alignment.CENTRE);
		
		fontContent = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD,
				false, UnderlineStyle.NO_UNDERLINE);
		formatContent = new WritableCellFormat(fontContent);
		formatContent.setBackground(Colour.VERY_LIGHT_YELLOW);
		formatContent.setBorder(Border.ALL, BorderLineStyle.THIN);
		formatContent.setAlignment(Alignment.CENTRE);
		//formatContent.setShrinkToFit(true);//设置如果字数超过单元格则自动缩小字体
		//CellView cellView = new CellView();  
		//cellView.setAutosize(true); //设置自动大小  
		//sheet.setColumnView(1, cellView);//根据内容自动设置列宽
		formatContent.setWrap(true);
	}

	private void addContentCell(WritableCell cell) throws RowsExceededException, WriteException {
		cell.setCellFormat(formatContent);
		sheet.addCell(cell);
	}

	private void addContent(T t, int r, SimpleDateFormat dateFormat) throws IllegalAccessException, IllegalArgumentException,
			InvocationTargetException, NoSuchMethodException, SecurityException, RowsExceededException, WriteException {
		// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
		Field[] fields = t.getClass().getDeclaredFields();
		for (short i = 0; i < fields.length; i++) {
			Field field = fields[i];
			String fieldName = field.getName();
			String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);

			Class tCls = t.getClass();
			Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
			Object value = getMethod.invoke(t, new Object[] {});
			// 判断值的类型后进行强制类型转换
			String textValue = null;

			if (value == null) {
				textValue = "";
			} else if (value instanceof Timestamp) {
				Timestamp date = (Timestamp) value;
				textValue = dateFormat.format(date);
			} else if (value instanceof BigDecimal) {
				textValue = value.toString();
			} else {
				// 其它数据类型都当作字符串简单处理
				textValue = value.toString();
			}
			// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
			if (textValue != null) {
				Pattern p = Pattern.compile("^//d+(//.//d+)?$");
				Matcher matcher = p.matcher(textValue);
				if (matcher.matches()) {
					// 是数字当作double处理
					Number number = new Number(i, r, Double.parseDouble(textValue));
					addContentCell(number);
				} else {
					Label cell = new Label(i, r, textValue);
					addContentCell(cell);
				}
			}

		}
	}
}

二：导出方法

/**
	 * 导出线下对账
	 * 
	 * @return
	 * @throws Exception 
	 */
	@RequestMapping("/lineExcel")
	public void lineExcel(OfflineListDto dto, String token,HttpServletResponse response) throws Exception {
		

		List<OfflineOderPo> list=intelleaseFinanceOrderService.getOfflineOrderList(dto);
		
		String[] headers = new String[]{"确认状态","订单编号","订单状态","结算方式","创建时间","实际开始时间","实际结束时间","场地编号"};
	    JxlExportExcel<OfflineOderPo> ex = new JxlExportExcel<OfflineOderPo>(); 
	    try {
		    response.setContentType("application/msexcel;"); 
		    response.addHeader("Content-Disposition","attachment; filename=" + URLEncoder.encode("线下对账", "UTF-8")+".xls"); 
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		} 
	    try {
			 ex.exportExcel(headers, list,response.getOutputStream());  
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}    
	}
	

三：

四：

 

转载于:https://my.oschina.net/u/2942156/blog/835606