Solo  当前访客:5 登录 注册

喧哗博客-http://blog.xuahua.com

繁华过后的沉寂--技术经验分享
浏览次数: 100,921    文章总数: 91    评论总数: 3
标签:

java提高篇-导出execl(具体使用篇) 有更新!

现在这篇文章,讲的是如何使用前一篇文章导出通用工具类

具体如下:

ackage com.xuahua.utils.export;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.CellView;
import jxl.write.Label;
import jxl.write.Number;

import com.xuahua.pojo.TMemCashTradeRecordVo;
import com.xuahua.utils.ExportExcelUtils;

public class TradeExportUtils extends ExportExcelUtils {
	
	@Override
	public List<Map<String, Object>> getHeaders() {
		List<Map<String, Object>> headers = new ArrayList<Map<String, Object>>();

		Map<String, Object> header = new HashMap<String, Object>();
		header.put("value", "序");
		headers.add(header);

		header = new HashMap<String, Object>();
		header.put("value", "用户名");
		headers.add(header);

		header = new HashMap<String, Object>();
		header.put("value", "交易号");
		headers.add(header);

		header = new HashMap<String, Object>();
		header.put("value", "交易前余额");
		headers.add(header);

		header = new HashMap<String, Object>();
		header.put("value", "交易金额");
		headers.add(header);

		header = new HashMap<String, Object>();
		header.put("value", "交易后余额");
		headers.add(header);

		header = new HashMap<String, Object>();
		header.put("value", "交易类型");
		headers.add(header);

		header = new HashMap<String, Object>();
		header.put("value", "收入支出");
		headers.add(header);
		
		header = new HashMap<String, Object>();
		header.put("value", "交易支出");
		headers.add(header);
		
		header = new HashMap<String, Object>();
		header.put("value", "订单号");
		headers.add(header);
		
		header = new HashMap<String, Object>();
		header.put("value", "摘要");
		headers.add(header);
		return headers;
	}

	@Override
	public void writeContent(List list) throws Exception{
		List<TMemCashTradeRecordVo> currencyCardList = list;
		CellView cellView = new CellView();  
	    cellView.setAutosize(true); //设置自动大小
		for (int i = 0; i < currencyCardList.size(); i++) {
			TMemCashTradeRecordVo vo = currencyCardList.get(i);

			number = new jxl.write.Number(0, rowIndex, (i + 1), cellFormat1);
			ws.addCell(number);
			
			label = new jxl.write.Label(1, rowIndex, vo.getUsername(),cellFormat1); // 用户名
			ws.addCell(label);

			label = new jxl.write.Label(2, rowIndex, vo.getTradeno(),cellFormat1); // 交易流水号
			ws.addCell(label);
			
			number = new Number(3, rowIndex, vo.getAccountcashmoney().add(vo.getAccountfrzoenmoney()).doubleValue(),cellFormat1); // 交易前金额
			ws.addCell(number);
			
			number = new Number(4, rowIndex, vo.getTradetotalmoney().doubleValue(),cellFormat1); // 交易金额
			ws.addCell(number);
			
			number = new Number(5, rowIndex, vo.getCashmoney().add(vo.getFrzoenmoney()).doubleValue(),cellFormat1); // 交易后金额
			ws.addCell(number);
			
			label = new jxl.write.Label(6, rowIndex,vo.getTradetype(), cellFormat1); // 交易类型
			ws.addCell(label);
			
			label = new jxl.write.Label(7, rowIndex,vo.getInorout(), cellFormat1); // 收入支出类型
			ws.addCell(label);

			label = new Label(8, rowIndex, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(vo.getCreatetime()),cellFormat1);
			ws.addCell(label);
			
			label = new Label(9, rowIndex, vo.getGeneralno(),cellFormat1); // 订单号
			ws.addCell(label);

			label = new Label(10, rowIndex, vo.getRemark(),cellFormat1); // 备注
			
			ws.setColumnView(i, cellView);
			ws.addCell(label);

			rowIndex++;
		}

	}
	
}

看了上面的代码以后,怎么样?会使用了么?

java提高篇-导出execl(通用工具类) 有更新!

在web开发中,经常会遇到需要把数据导出到EXCEL中,并提供下载。

那么本文将提供一个通用的导出EXCEL的工具类。。。具体的实现或自定义的样式,可以自己定义。

请看下文代码

package com.xuahua.utils;

import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.log4j.Logger;

/**
 * @author wyong 提供公用方法,来生成xls。 具体的数据对象由writeContent实现,标签页的头由getHeaders实现。
 * 
 */
public class ExportExcelUtils {

	protected Label label;
	protected Number number;

	protected WritableWorkbook wwb;
	protected WritableSheet ws;
	protected int rowIndex = 0;
	protected int MAXRECORD = 60000;// 每个工作空间最大记录条数。

	protected WritableCellFormat titleFormat1;
	protected WritableCellFormat headerFormat1;
	protected WritableCellFormat cellFormat1;
	protected WritableCellFormat dateCellFormat;

	private static final Logger logger = Logger
			.getLogger(ExportExcelUtils.class);

	public ExportExcelUtils() {
		try {
			headerFormat1 = initHeaderFormat();
			cellFormat1 = initCellFormat();
			dateCellFormat = initDateFormat();
			titleFormat1 = initTitleFormate();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void xls(OutputStream os, String sheetName, List contentList)
			throws Exception {
		wwb = Workbook.createWorkbook(os);
		try {
			int maxsize = contentList.size();
			if (maxsize > MAXRECORD) {
				int s = maxsize % MAXRECORD == 0 ? maxsize / MAXRECORD
						: maxsize / MAXRECORD + 1;
				List tmpList = new ArrayList(MAXRECORD);
				for (int i = 0; i < s; i++) {
					rowIndex = 0;
					tmpList = contentList.subList(i * MAXRECORD, (i + 1)
							* MAXRECORD > maxsize ? maxsize : (i + 1)
							* MAXRECORD);
					ws = wwb.createSheet(sheetName + "-" + i, i);
					this.writeHeaders();
					this.writeContent(tmpList);
				}
			} else {
				ws = wwb.createSheet(sheetName, 0);
				this.writeHeaders();
				this.writeContent(contentList);
			}
		} finally {
			wwb.write();
			wwb.close();

			os.flush();
			os.close();
		}
	}

	/**
	 * @param objlist
	 *            需要写入xls文件中的对象。做过处理的一个标签页最多60000条。
	 * @throws Exception
	 */
	public void writeContent(List objlist) throws Exception {
		throw new Exception("u` must implements this method.");
	};

	/**
	 * xls文件中的标题头。
	 * 
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> getHeaders() throws Exception {
		throw new Exception("u` must implements this method.");
	};

	// 写表头
	public void writeHeaders() throws Exception {
		List<Map<String, Object>> headers = this.getHeaders();

		for (int i = 0; i < headers.size(); i++) {
			Map<String, Object> map = headers.get(i);

			Label label = new Label(i, rowIndex, map.get("value").toString(),
					headerFormat1);
			ws.addCell(label);

		}
		rowIndex++;
	}

	/**
	 * 格式化日期单元格
	 * 
	 * @return
	 * @throws WriteException
	 */
	protected WritableCellFormat initDateFormat() throws WriteException {
		jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd HH:mm:ss");
		WritableCellFormat dateCellFormat = new jxl.write.WritableCellFormat(df);
		dateCellFormat.setAlignment(Alignment.LEFT);
		dateCellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
		dateCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		dateCellFormat.setAlignment(Alignment.CENTRE); //设置水平对齐
		dateCellFormat.setWrap(false); //设置自动换行
		return dateCellFormat;
	}

	/**
	 * 格式化单元格样式
	 * 
	 * @return
	 * @throws WriteException
	 */
	protected WritableCellFormat initCellFormat() throws WriteException {
		WritableFont cellFont1 = new WritableFont(WritableFont.TIMES, 12);
		WritableCellFormat cellFormat1 = new WritableCellFormat(cellFont1);
		cellFormat1.setAlignment(Alignment.LEFT);
		cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
		cellFormat1.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		cellFormat1.setAlignment(Alignment.CENTRE); //设置水平对齐
		cellFormat1.setWrap(false); //设置自动换行
		return cellFormat1;

	}

	/**
	 * @return 格式化headerformate
	 * @throws WriteException
	 */
	protected WritableCellFormat initHeaderFormat() throws WriteException {
		WritableFont headerFont1 = new WritableFont(WritableFont.TIMES, 14,
				WritableFont.BOLD);
		headerFont1.setColour(Colour.BLUE);
		WritableCellFormat headerFormat1 = new WritableCellFormat(headerFont1);
		headerFormat1.setAlignment(Alignment.CENTRE);
		headerFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
		headerFormat1.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		headerFormat1.setBackground(Colour.YELLOW);
		headerFormat1.setAlignment(Alignment.CENTRE); //设置水平对齐
		headerFormat1.setWrap(false); //设置自动换行
		return headerFormat1;
	}

	/**
	 * 格式化initTitleFormate1
	 * 
	 * @return
	 * @throws WriteException
	 */
	protected WritableCellFormat initTitleFormate() throws WriteException {
		WritableFont titleFont1 = new WritableFont(WritableFont.TIMES, 24,
				WritableFont.BOLD);
		titleFont1.setColour(Colour.BLUE);
		WritableCellFormat titleFormat1 = new WritableCellFormat(titleFont1);
		titleFormat1.setAlignment(Alignment.CENTRE);
		titleFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
		titleFormat1.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		titleFormat1.setBackground(Colour.YELLOW);
		titleFormat1.setAlignment(Alignment.CENTRE); //设置水平对齐
		titleFormat1.setWrap(false); //设置自动换行
		return titleFormat1;
	}
}

公告

喧哗博客--繁华过后的沉寂--技术经验分享^-^
Copyright (c) 2009-2020, b3log.org & hacpai.com