导出EXCEL常用工具类

1、接口

 

package com.yihaodian.pis.common.util.export;

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

public interface ExcelExporter {

	void defineHeaders();
	
	void drawCellFormat();
	
	void addDataRow(List<String> dataRow);
	
	void writeTo(OutputStream output) throws Exception;
}

 2、ExportFactory。java

package com.yihaodian.pis.common.util.export;

import java.io.File;
import java.io.OutputStream;
import java.util.List;

/**
 * 
 * @author Tom
 *
 */
public class ExportFactory {
	
	private ExportFactory() {
	}
	
	public static void exportExcelOnTemplate(String templatePath,
			List<List<String>> dataRowList, OutputStream outputStream) throws Exception {
		ExcelExporter exporter = new TemplateExcelExporter(templatePath);
		exporter.defineHeaders();
		exporter.drawCellFormat();
		for (List<String> dataRow : dataRowList) {
			exporter.addDataRow(dataRow);
		}
		exporter.writeTo(outputStream);
	}
	
}

 3、导出工具类实现类

package com.yihaodian.pis.common.util.export;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class TemplateExcelExporter implements ExcelExporter {

	private static final int INDEX_COPIED_ROW = 1;

	private Log log = LogFactory.getLog(this.getClass());

	private Workbook outputWorkbook;
	
	private List<CellStyle> cellFormats = new ArrayList<CellStyle>();
	
	private List<Integer> cellTypes = new ArrayList<Integer>();

	private List<List<String>> dataRowList = new ArrayList<List<String>>();

	public TemplateExcelExporter(String templatePath) {
		File template = new File(templatePath);
		if (!template.isFile()) {
			throw new IllegalArgumentException("The template file is invalid. " + templatePath);
		}

		try {
			outputWorkbook = WorkbookFactory.create(new BufferedInputStream(new FileInputStream(templatePath)));
		} catch (Exception e) {
			log.error("Failed to parse this template", e.getCause());
		}
	}

	@Override
	public void defineHeaders() {
	}

	@Override
	public void drawCellFormat() {
		Sheet sheet = outputWorkbook.getSheetAt(0);
		
		Row formatRow = sheet.getRow(INDEX_COPIED_ROW);
		Iterator<Cell> it = formatRow.iterator();
		while (it.hasNext()) {
			Cell cell = it.next();
			cellFormats.add(cell.getCellStyle());
			cellTypes.add(cell.getCellType());
		}
	}
	
	@Override 
	public void addDataRow(List<String> dataRow) {
		if (dataRow.size() > cellFormats.size()) {
			throw new IllegalArgumentException("The size of dataRow cannot be greater than the template's.");
		}
		dataRowList.add(dataRow);
	}
	
	@Override
	public void writeTo(OutputStream output) throws Exception {
	Sheet sheet = outputWorkbook.getSheetAt(0);
		
		for (int i = 0; i < dataRowList.size(); i++) {
			List<String> dataRow = dataRowList.get(i);
			int rowIndex = i + 1;
			Row row;
			if (rowIndex == INDEX_COPIED_ROW) {
				row = sheet.getRow(rowIndex);
			} else {
				row = sheet.createRow(rowIndex);
			}
			for (int j = 0; j < dataRow.size(); j++) {
				Cell cell;
				if (rowIndex == INDEX_COPIED_ROW) {
					cell = row.getCell(j);
				} else {
					cell = row.createCell(j);
				}
				
				cell.setCellStyle(cellFormats.get(j));
				cell.setCellType(cellTypes.get(j));
				cell.setCellValue(dataRow.get(j));
			}
		}
		outputWorkbook.write(output);
	}
	
}

 4、具体实现

List<List<String>> colAll = new ArrayList<List<String>>();
		for (int i = 0; i < listOppon.size(); i++) {

			OpponInfoListVO opponInfoListVO = listOppon.get(i);
			List<String> col = new ArrayList<String>();
			// oppon site name
			PisTProduct oppProduct = opponInfoListVO.getPisTProduct();
			if (null == oppProduct) {
				continue;
			}
			col.add(siteMap.get(oppProduct.getSiteId()));

			// 1到4级的分类
			final int totalLevel = 4; // 当前设定共4层目录
			Long leafCategoryId = oppProduct.getCategoryId();
			List<String> categoryNameList = new ArrayList<String>();
			try {

				int level = 0;
				// (从叶子节点开始，逆向获取父节点Name)
				while (level < (totalLevel - 1)) {
					PisTCategory pisTCatagory = pisTCategoryService
							.getPisTCategoryById(leafCategoryId);
					if (null != pisTCatagory) {
						categoryNameList.add(pisTCatagory.getCategoryName());
						leafCategoryId = pisTCatagory.getParentId();
					} else {
						categoryNameList.add("");
					}
					level++;

				}
			} catch (ServiceException e) {
				e.printStackTrace();
			}
			for (int k = categoryNameList.size() - 1; k >= 0; k--) {
				col.add(categoryNameList.get(k));
			}
			if (categoryNameList.size() < totalLevel) {
				col.add("");
			}
			String[] oppStatArr = opponInfoListVO.getPisTProductStats().split(",");
			if (null == oppStatArr) {
				continue;
			}
			// 品牌
			col.add(oppProduct.getBrandName());
			// 竞争对手商品名称
			col.add(oppProduct.getProductName());
			// 链接
			col.add(oppProduct.getProductUrl());

			if (-1 != oppStatArr[1].indexOf("有货")) {
				// 当前价格
				col.add(Float.toString(oppProduct.getPrice()));
			} else {
				col.add("");
			}
            /**对手商品状态信息*/
			// 评论数
			col.add(oppStatArr[0]);
			// 库存状态
			col.add(oppStatArr[1]);
			// 抓取时间
			col.add(oppStatArr[2]);
			
			/**yhd产品信息*/
			// 一号店商品编码
			YhdBackProduct yhdBackProduct = opponInfoListVO.getYhdBackProduct();
			if (null != yhdBackProduct) {
				col.add(yhdBackProduct.getProductCode());
				col.add(yhdBackProduct.getProductName());
				// 一号店品牌(目前yhd数据库无此信息)
				col.add("");
				col.add(yhdBackProduct.getCategoryLvName2());
				col.add(yhdBackProduct.getCategoryLvName3());
				col.add(yhdBackProduct.getCategoryLvName4());
				col.add(yhdBackProduct.getPrice() == null ? null : Float
						.toString(yhdBackProduct.getPrice()));
				// 库存状态
				// col.add(yhdBackProduct.getStock() <= 0 ? "缺货" : "有货");
				if (yhdBackProduct.getStock() <= 0) {
					col.add("缺货");
				} else {
					col.add("有货");
				}
				col.add("http://www.yihaodian.com/product/detail.do?productID="
						+ yhdBackProduct.getProductId() + "&merchantID=1");
			} else if (null == yhdBackProduct && 0 == i) {
				for (int j = 0; j < 9; j++) {
					col.add("");
				}
			}
			if (opponInfoListVO.getResultMatchDto() != null) {
				SimpleDateFormat df = new SimpleDateFormat("yy/MM/dd HH:mm");
				col.add(null == opponInfoListVO.getResultMatchDto()
						.getCreateTime() ? "" : df.format(opponInfoListVO
						.getResultMatchDto().getCreateTime()));
				col.add(1 == opponInfoListVO.getResultMatchDto().getIsManual() ? "人工匹配"
						: "自动匹配");
			} else if (null == opponInfoListVO.getResultMatchDto() && 0 == i) {
				for (int j = 0; j < 2; j++) {
					col.add("");
				}
			}
			colAll.add(col);
		}
		HttpServletResponse response = ServletActionContext.getResponse();
		response.setContentType("application/octet-stream");
		/*response.setHeader("Content-Disposition",
				"attachment;filename=exceltext.xls");*/
		String destFileName = "PIS-竞争对手商品清单-";
		destFileName +=siteMap.get(listOppon.get(0).getPisTProduct().getSiteId());
		SimpleDateFormat sd = new SimpleDateFormat("yyyy.MM.dd");
		destFileName += "-" + sd.format(new Date()) +".xlsx" ;
		
		try {
			destFileName = URLEncoder.encode(destFileName, "UTF-8");
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
		//response.setCharacterEncoding("gbk");
		response.setHeader("Content-Disposition",
				"attachment;filename=" + destFileName);
		
		OutputStream output;
		
		String relPath = Thread.currentThread().getContextClassLoader().getResource("").getPath();
		relPath = relPath.replaceAll("classes/", "");
		String filePath = relPath + File.separator + "exceldepository" + File.separator +"opponentProduct.xlsx";
		try {
			output = response.getOutputStream();
			ExportFactory.exportExcelOnTemplate(filePath, colAll, output);
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}