导出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());}}@Overridepublic void defineHeaders() {}@Overridepublic 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);}@Overridepublic 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 namePisTProduct 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 blocke1.printStackTrace();}?