web下pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
全局pom.xml
<poi.version>3.10-FINAL</poi.version>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
类位置
ExporterFactory
package com.opensesame.tms.platform.exporter;
import com.opensesame.tms.platform.exporter.head.ExporterHead;
import com.opensesame.tms.platform.exporter.template.AbstractExporterTemplate;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
/**
* 导出工厂
* @author sonny
*/
public class ExporterFactory {
protected Map<Class<? extends AbstractExporterTemplate>, ? extends AbstractExporterTemplate> exporterTemplate;
private static final String EXCEL_FILENAME_EXTENSION = ".xlsx";
/**
* 导出excel
*
* @param response 响应流
* @param map 文件名 和 数据映射
* @param exporterHead 导出文件头
* @param et 导出模板
* @param args
* @throws IOException
* @throws InvalidFormatException
*/
@SuppressWarnings({ "rawtypes", "unchecked" })
public void process(HttpServletResponse response, List<? extends LinkedHashMap> data, ExporterHead exporterHead, Class<? extends AbstractExporterTemplate> et, Object... args)
throws IOException, InvalidFormatException {
AbstractExporterTemplate abstractExporterTemplate = exporterTemplate.get(et);
Workbook workBook = null;
workBook = new XSSFWorkbook();
CellStyle cellStyle = getCellStyle(workBook);
Sheet sheet = workBook.createSheet();
// 设置表头
abstractExporterTemplate.drawHead(workBook , sheet, exporterHead ,cellStyle, args);
int startRow = abstractExporterTemplate.getStartRowNumber();
// 写入业务数据
for (LinkedHashMap<String, String> temp : data) {
Row row = (Row) sheet.createRow(startRow++);
int rowNum = 0;
for (String value : temp.values()) {
Cell cell = row.createCell(rowNum++);
cell.setCellStyle(cellStyle);
cell.setCellValue(value);
}
}
// 收尾工作
abstractExporterTemplate.windup(workBook, sheet, exporterHead, cellStyle, args);
// 设置响应头
setResponseHead(response, getFileName(abstractExporterTemplate, exporterHead));
// 输出
ServletOutputStream out = response.getOutputStream();
workBook.write(out);
out.close();
out.flush();
}
/**
* 获取单元格样式
*
* @param workBook
* @return
*/
public static CellStyle getCellStyle(Workbook workBook) {
CellStyle style = workBook.createCellStyle();
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
Font font = workBook.createFont();
font.setFontName("Calibri");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
/**
* 获取文件名称
*
* @param abstractExporterTemplate
* @param exporterHead
* @return
*/
public static String getFileName(AbstractExporterTemplate abstractExporterTemplate, ExporterHead exporterHead) {
if (null != exporterHead && StringUtils.isNotEmpty(exporterHead.getFileName())) {
return exporterHead.getFileName();
}
if (null != exporterHead && StringUtils.isNotEmpty(exporterHead.getTitle())) {
return exporterHead.getTitle();
}
return abstractExporterTemplate.getDefaultFileName();
}
/**
* 设置响应头信息
*
* @param response
* @param fileName
* @throws UnsupportedEncodingException
*/
public static void setResponseHead(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
response.setCharacterEncoding("UTF-8");
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//解决文件名中文乱码
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-Disposition", "attachment;fileName=\"" + new String(fileName.getBytes("utf-8"), "ISO8859-1") + EXCEL_FILENAME_EXTENSION + "\"");
}
public Map<Class<? extends AbstractExporterTemplate>, ? extends AbstractExporterTemplate> getExporterTemplate() {
return exporterTemplate;
}
public void setExporterTemplate(Map<Class<? extends AbstractExporterTemplate>, ? extends AbstractExporterTemplate> exporterTemplate) {
this.exporterTemplate = exporterTemplate;
}
}
ExporterTemplate
package com.opensesame.tms.platform.exporter.template;
import com.opensesame.tms.platform.exporter.head.ExporterHead;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
public interface ExporterTemplate {
// 设置表头信息
void drawHead(Workbook workBook, Sheet sheet, ExporterHead exporterHead, CellStyle cellStyle, Object... args);
// 画完表格清理工作
void windup(Workbook workBook, Sheet sheet, ExporterHead exporterHead, CellStyle cellStyle, Object... args);
}
AbstractExporterTemplate
package com.opensesame.tms.platform.exporter.template;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
public abstract class AbstractExporterTemplate implements ExporterTemplate {
int startRowNumber = 0;
String defaultFileName = "导出数据";
String templateFilePath = "";
public int getStartRowNumber() {
return startRowNumber;
}
public void setStartRowNumber(int startRowNumber) {
this.startRowNumber = startRowNumber;
}
public String getDefaultFileName() {
return defaultFileName;
}
public void setDefaultFileName(String defaultFileName) {
this.defaultFileName = defaultFileName;
}
public String getTemplateFilePath() {
return templateFilePath;
}
public void setTemplateFilePath(String templateFilePath) {
this.templateFilePath = templateFilePath;
}
/**
* 获取单元格样式
*
* @param workBook
* @return
*/
public static CellStyle getDateCellStyle(Workbook workBook) {
CellStyle style = workBook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
Font font = workBook.createFont();
font.setFontName("Calibri");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
/**
* 获取单元格样式没有边框
*
* @param workBook
* @return
*/
public static CellStyle getTitleCellStyle(Workbook workBook) {
CellStyle style = workBook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setBorderBottom(XSSFCellStyle.BORDER_NONE);
style.setBorderTop(XSSFCellStyle.BORDER_NONE);
style.setBorderRight(XSSFCellStyle.BORDER_NONE);
style.setBorderLeft(XSSFCellStyle.BORDER_NONE);
Font font = workBook.createFont();
font.setFontName("Calibri");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
}
ExporterHead
package com.opensesame.tms.platform.exporter.head;
import java.io.Serializable;
public class ExporterHead implements Serializable {
private static final long serialVersionUID = 1L;
public ExporterHead() {
super();
}
public ExporterHead(String title) {
super();
this.title = title;
}
public ExporterHead(String fileName, String title) {
super();
this.fileName = fileName;
this.title = title;
}
// 文件名
String fileName;
// 标题
String title;
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
}
Controller
/**
* 导出参考价设置
*
* @return
* @throws IOException
* @throws InvalidFormatException
* @throws NoSuchMethodException
* @throws InvocationTargetException
* @throws IllegalAccessException
*/
//用post原因,输入的查询参数太长了
@RequestMapping(value = "/exportPrice", method = {RequestMethod.POST})
@ResponseBody
@ApiOperation(value = "导出参考价设置", notes = "导出参考价设置", response = String.class)
public void exportinsurance(HttpServletResponse response,
@ApiParam(value = "发货机构") @RequestParam(required = false) List<Long> deliveryOrgIds,
@ApiParam(value = "到货机构") @RequestParam(required = false) List<Long> receiveOrgIds,
@ApiParam(value = "状态:1有效、2无效") @RequestParam(required = true) Integer validStatus)
throws IOException, InvalidFormatException, IllegalAccessException, InvocationTargetException, NoSuchMethodException {
List<LinkedHashMap> result = new LinkedList<>();
List<ConfigReferencePriceDto> exportList = configReferencePriceService.findExportList(deliveryOrgIds, receiveOrgIds, validStatus);
String[] cols = {"deliveryOrgId", "receiveOrgId", "unitVolumePrice", "unitWeightPrice", "thresholdVolume", "thresholdVolumePrice", "thresholdWeight", "thresholdWeightPrice", "singlePiecePrice",
"abnormityCoefficient", "equipmentCoefficient", "status", "updateTime"
};
List<Organization> organizationList = organizationService.findByNamedParamList(null);
List<String> columns = new ArrayList<>();
for (String s : cols) {
columns.add(s);
}
for (ConfigReferencePriceDto priceDto : exportList) {
LinkedHashMap<String, String> data = new LinkedHashMap<>();
for (String column : columns) {
String value = "";
if (column.equals("deliveryOrgId")) {
//收货机构
if (priceDto.getDeliveryOrgId() != null) {
for (Organization organ : organizationList) {
if (organ.getId().equals(priceDto.getDeliveryOrgId())) {
value = organ.getName();
}
}
}
} else if (column.equals("receiveOrgId")) {
//收货机构
if (priceDto.getReceiveOrgId() != null) {
for (Organization organ : organizationList) {
if (organ.getId().equals(priceDto.getReceiveOrgId())) {
value = organ.getName();
}
}
}
} else if (column.equals("unitVolumePrice")) {
//标准体积参考价
value = String.valueOf(priceDto.getUnitVolumePrice());
} else if (column.equals("unitWeightPrice")) {
//标准重量参考价
value = String.valueOf(priceDto.getUnitWeightPrice());
} else if (column.equals("thresholdVolume")) {
//体积阈值
value = String.valueOf(priceDto.getThresholdVolume());
} else if (column.equals("thresholdVolumePrice")) {
//超体积阈值价格
value = String.valueOf(priceDto.getThresholdVolumePrice());
} else if (column.equals("thresholdWeight")) {
//重量阈值
value = String.valueOf(priceDto.getThresholdWeight());
} else if (column.equals("thresholdWeightPrice")) {
//超重量阈值价格
value = String.valueOf(priceDto.getThresholdWeightPrice());
} else if (column.equals("singlePiecePrice")) {
//单价参考价
value = String.valueOf(priceDto.getSinglePiecePrice());
} else if (column.equals("abnormityCoefficient")) {
//异形件系数
value = String.valueOf(priceDto.getAbnormityCoefficient());
} else if (column.equals("equipmentCoefficient")) {
//设备系数
value = String.valueOf(priceDto.getEquipmentCoefficient());
} else if (column.equals("status")) {
switch (priceDto.getStatus()) {
case 1:
value = "有效";
break;
case 2:
value = "无效";
break;
default:
value = "";
}
} else if (column.equals("updateTime")) {
value = DateUtils.getStrDate(priceDto.getUpdateTime(), "yyyy-MM-dd HH:mm:ss");
}
data.put(column, value);
}
result.add(data);
}
exporterFactory.process(response, result, new ExporterHead("参考价设置"), ReferencePriceExporterTemplate.class, columns);
}
ExporterConfig类
exporterTemplate.put(ReferencePriceExporterTemplate.class, getReferencePriceExporterTemplate());
@Bean
public ReferencePriceExporterTemplate getReferencePriceExporterTemplate() {
ReferencePriceExporterTemplate referencePriceExporterTemplate = new ReferencePriceExporterTemplate();
referencePriceExporterTemplate.setDefaultFileName("参考价设置");
referencePriceExporterTemplate.setStartRowNumber(3);
return referencePriceExporterTemplate;
}
ReferencePriceExporterTemplate类
package com.opensesame.platform.web.exporter.template;
import com.opensesame.core.lang.Tuple;
import com.opensesame.core.util.DateUtils;
import com.opensesame.platform.web.exporter.head.ExporterHead;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@SuppressWarnings("unchecked")
public class ReferencePriceExporterTemplate extends AbstractExporterTemplate {
private static final Map<String, Tuple<String, Integer>> METADATA = new HashMap<String, Tuple<String, Integer>>();
static {
METADATA.put("deliveryOrgId", new Tuple<String, Integer>("收货机构", 20 * 256));
METADATA.put("receiveOrgId", new Tuple<String, Integer>("到货机构", 20 * 256));
METADATA.put("unitVolumePrice", new Tuple<String, Integer>("标准体积参考价(元 /m3)", 25 * 256));
METADATA.put("unitWeightPrice", new Tuple<String, Integer>("标准重量参考价(元 /kg)", 25 * 256));
METADATA.put("thresholdVolume", new Tuple<String, Integer>("体积阈值(m3)", 20 * 256));
METADATA.put("thresholdVolumePrice", new Tuple<String, Integer>("超体积阈值价格(元/m3)", 25 * 256));
METADATA.put("thresholdWeight", new Tuple<String, Integer>("重量阈值(kg)", 20 * 256));
METADATA.put("thresholdWeightPrice", new Tuple<String, Integer>("超重量阈值价格(元/kg)", 25 * 256));
METADATA.put("singlePiecePrice", new Tuple<String, Integer>("单件参考价", 15 * 256));
METADATA.put("abnormityCoefficient", new Tuple<String, Integer>("异形件系数", 15 * 256));
METADATA.put("equipmentCoefficient", new Tuple<String, Integer>("设备系数", 15 * 256));
METADATA.put("status", new Tuple<String, Integer>("状态", 15 * 256));
METADATA.put("updateTime", new Tuple<String, Integer>("时间", 25 * 256));
}
@Override
public void drawHead(Workbook workBook, Sheet sheet, ExporterHead exporterHead, CellStyle cellStyle, Object... args) {
List<String> columns = (List<String>) args[0];
// 设置标题
CellRangeAddress head = new CellRangeAddress(0, 0, 0, columns.size());
sheet.addMergedRegion(head);
Cell headCell = sheet.createRow(0).createCell(0);
headCell.setCellValue(exporterHead.getTitle());
headCell.setCellStyle(getHeadCellStyle(workBook));
RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_THIN, head, sheet, workBook);
RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_THIN, head, sheet, workBook);
RegionUtil.setBorderRight(XSSFCellStyle.BORDER_THIN, head, sheet, workBook);
RegionUtil.setBorderTop(XSSFCellStyle.BORDER_THIN, head, sheet, workBook);
// 设置时间
CellRangeAddress date = new CellRangeAddress(1, 1, 0, columns.size());
sheet.addMergedRegion(date);
Cell dateCell = sheet.createRow(1).createCell(0);
dateCell.setCellValue("导出时间:" + DateFormatUtils.format(new Date(), DateUtils.DATE_TIME_FORMAT));
dateCell.setCellStyle(getDateCellStyle(workBook));
RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_THIN, date, sheet, workBook);
RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_THIN, date, sheet, workBook);
RegionUtil.setBorderRight(XSSFCellStyle.BORDER_THIN, date, sheet, workBook);
RegionUtil.setBorderTop(XSSFCellStyle.BORDER_THIN, date, sheet, workBook);
Row row = sheet.createRow(2);
int columnNum = 0;
CellStyle titleCellStyle = getTitleCellStyle(workBook);
for (String column : columns) {
Cell cell = row.createCell(columnNum++);
cell.setCellStyle(titleCellStyle);
cell.setCellValue(METADATA.get(column).getA());
}
//将字段改为数字格式
List<LinkedHashMap> data = (List<LinkedHashMap>) args[1];
int startRow = 3;
for (LinkedHashMap<String, Object> temp : data) {
Row dataRow = sheet.createRow(startRow++);
int rowNum = 0;
for (Map.Entry<String, Object> value : temp.entrySet()) {
Cell cell = dataRow.createCell(rowNum++);
if (value.getKey().equals("capacity") || value.getKey().equals("netWeight")) {
CellStyle cellStyleNumber = getCellStyle(workBook);
cell.setCellStyle(cellStyleNumber);
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(Double.parseDouble(value.getValue().toString()));
} else {
cell.setCellStyle(cellStyle);
cell.setCellValue(value.getValue().toString());
}
}
}
}
@Override
public void windup(Workbook workBook, Sheet sheet, ExporterHead exporterHead, CellStyle cellStyle, Object... args) {
List<String> columns = (List<String>) args[0];
int columnNum = 0;
//去掉列自适应宽度
// sheet.autoSizeColumn(columnNum++, true);
for (String column : columns) {
sheet.setColumnWidth(columnNum++, METADATA.get(column).getB());
}
}
/**
* 获取单元格样式
*
* @param workBook
* @return
*/
public static CellStyle getHeadCellStyle(Workbook workBook) {
CellStyle style = workBook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
Font font = workBook.createFont();
font.setFontName("Calibri");
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 20);
style.setFont(font);
return style;
}
/**
* 获取单元格样式
*
* @param workBook
* @return
*/
public static CellStyle getDateCellStyle(Workbook workBook) {
CellStyle style = workBook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
Font font = workBook.createFont();
font.setFontName("Calibri");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
/**
* 获取单元格样式
*
* @param workBook
* @return
*/
public static CellStyle getTitleCellStyle(Workbook workBook) {
CellStyle style = workBook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setBorderBottom(XSSFCellStyle.BORDER_THIN);
style.setBorderTop(XSSFCellStyle.BORDER_THIN);
style.setBorderRight(XSSFCellStyle.BORDER_THIN);
style.setBorderLeft(XSSFCellStyle.BORDER_THIN);
Font font = workBook.createFont();
font.setFontName("Calibri");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
}