效果图1
效果图2
效果图3
效果图4
说明：
实现各种各样样式的Excel，是我们在开发中比较头疼的事情，下面这个例子，是通过java语言，运用Apache HSSFWorkbook创建Excel的实现，可满足大多数的场景（XSSFWorkbook同理）。
一、Excel生成
1、Excel生成服务
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
@Component
public class ExcelReportMakerService {
@Autowired
private DOssClient ossClient;
@Value(value = "${dpos-service.excel.excelReport.fontName:宋体}")
private String fontName;
@Value(value = "${dpos-service.excel.excelReport.titleSize:25}")
private int titleSize;
@Value(value = "${dpos-service.excel.excelReport.subTitleSize:18}")
private int subTitleSize;
@Value(value = "${dpos-service.excel.excelReport.columnWidth:120}")
private int columnWidth;
public String make(ExcelReport excelReport) throws Exception {
// 第一步创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
// 第二步创建sheet
HSSFSheet sheet = workbook.createSheet(excelReport.getSheet());
// 1、声明一个工作薄
// 2、生成一个表格
List<List<Cell>> data = excelReport.getList();
int num = data.get(0).size();
sheet.setDefaultColumnWidth(columnWidth/num);
sheet.setDisplayGridlines(false); // 去除excel原有的空白白色边框
int offset = 0;
createTitle(new ExcelReport.ExcelTitle(workbook, sheet, excelReport.getTitle(), num, titleSize, offset++, HSSFCellStyle.ALIGN_CENTER));
if (excelReport.getSubTitle() != null) {
createTitle(new ExcelReport.ExcelTitle(workbook, sheet, excelReport.getSubTitle(), num, subTitleSize, offset++, HSSFCellStyle.ALIGN_LEFT));
}
fillData(workbook, sheet, data, offset);
String urlPath = uploadExcel(workbook, excelReport.getShop(), excelReport.getFileName());
return urlPath;
}
public void createTitle(ExcelTitle excelTitle) {
// 设置行的字体样式 ----------------------------------字体------------------------
HSSFFont firstFont = excelTitle.getWorkbook().createFont();
firstFont.setFontName(fontName);// 设置字体
firstFont.setFontHeightInPoints((short) excelTitle.getFontSize());// 设置字体大小
firstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
// 设置单元格的字体样式
HSSFCellStyle firstCellStyle = excelTitle.getWorkbook().createCellStyle();
firstCellStyle.setFont(firstFont);
firstCellStyle.setAlignment(excelTitle.getAlign());// 左右居中
firstCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
HSSFCellStyle tempStyle = excelTitle.getWorkbook().createCellStyle();
tempStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
HSSFRow firstRow = excelTitle.getSheet().createRow(excelTitle.getRow());
HSSFCell cell = firstRow.createCell(0);
cell.setCellValue(excelTitle.getTitle());
// 合并单元格
excelTitle.getSheet().addMergedRegion(new CellRangeAddress(excelTitle.getRow(),
excelTitle.getRow(), 0, excelTitle.getCoverCellsNum() - 1));
cell.setCellStyle(firstCellStyle);
HSSFCell cellTemp = firstRow.createCell(excelTitle.getCoverCellsNum());
cellTemp.setCellStyle(tempStyle);
}
public void fillData(HSSFWorkbook workbook, HSSFSheet sheet, List<List<Cell>> data, int offset) {
HSSFFont boldFont = workbook.createFont();
boldFont.setFontName(fontName);// 设置字体
boldFont.setFontHeightInPoints((short) 12);// 设置字体大小
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
HSSFFont normalFont = workbook.createFont();
normalFont.setFontName(fontName);// 设置字体
normalFont.setFontHeightInPoints((short) 12);// 设置字体大小
Map<String, HSSFCellStyle> mapStyle = new HashMap<String, HSSFCellStyle>();
for (List<Cell> list : data) {
HSSFRow row = sheet.createRow(offset);
// 生成列名00
for (int i = 0; i < list.size(); i++) {
//sheet.autoSizeColumn(i, true);
HSSFCell cell = row.createCell(i);
Cell mCell = list.get(i);
String value = mCell.getValue();
cell.setCellValue(value);
String align = mCell.getAlign();
String font = mCell.getFont();
String border = mCell.getBorder();
String key = align + font + border;
HSSFCellStyle style = mapStyle.get(key);
if (style == null) {
style = sheet.getWorkbook().createCellStyle();
if (ExcelReport.Cell.ALIGN_RIGHT.equals(mCell.getAlign())) {
style.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
}
if (ExcelReport.Cell.ALIGN_CENTER.equals(mCell.getAlign())) {
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
}
if (ExcelReport.Cell.FONT_BOLD.equals(mCell.getFont())) {
HSSFFont firstFont = workbook.createFont();
firstFont.setFontName(fontName);// 设置字体
firstFont.setFontHeightInPoints((short) 12);// 设置字体大小
firstFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
style.setFont(firstFont);
}
if (ExcelReport.Cell.BORDER_BOTTOM_NONE.equals(mCell.getBorder())) {
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
} else if (ExcelReport.Cell.BORDER_TOP_NONE.equals(mCell.getBorder())) {
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
} else {
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
}
}
mapStyle.put(key, style);
if (mCell.getNum() != null) {
sheet.addMergedRegion(new CellRangeAddress(cell.getRowIndex(), cell.getRowIndex(), cell.getCellNum(), cell.getCellNum()+mCell.getNum()-1));
}
cell.setCellStyle(style);
}
offset++;
}
}
/**
* 上传Excel文件
*
* @param workbook
* Workbook
* @param shop
* shopId
* @param fileName
* 文件名称
* @return
* @throws Exception
*/
public String uploadExcel(HSSFWorkbook workbook, String shop, String fileName) throws Exception {
StringBuilder pathBuilder = new StringBuilder();
pathBuilder.append(DOssKeyPrefix.getTempKeyPrefix());
pathBuilder.append("/");
pathBuilder.append(shop);
StringBuilder nameBuilder = new StringBuilder();
nameBuilder.append(System.nanoTime());
nameBuilder.append(".");
nameBuilder
.append(StringUtils.reverse(StringUtils.reverse(fileName).split("\\.")[0]).toLowerCase());
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ByteArrayInputStream bais = null;
try {
workbook.write(baos);
bais = new ByteArrayInputStream(baos.toByteArray());
DOssObject obj = ossClient.putObject(pathBuilder.toString(), nameBuilder.toString(), bais);
return obj.getObjectUrl();
} catch (Exception e) {
throw e;
} finally {
try {
workbook.close();
baos.close();
bais.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
2、相关实体
1、ExcelReport
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelReport {
private String title;// 主标题
private String subTitle;// 副标题
private String sheet;
private String shop;
private String fileName;
private final List<List<Cell>> list = new ArrayList<>();// 需要渲染的数据值
public static class Cell {
public static final String ALIGN_RIGHT = "right"; // 右对齐
public static final String ALIGN_CENTER = "center"; // 居中
public static final String ALIGN_LEFT = "left"; // 左对齐
public static final String FONT_DEFAULT = "default"; // 默认
public static final String FONT_BOLD = "bold"; // 字体为粗体
public static final String BORDER_BOTTOM_NONE = "bottom"; // 不加下边框
public static final String BORDER_TOP_NONE = "top"; // 不加上边框
private String value;
private String align = ALIGN_LEFT;
private String font = FONT_DEFAULT;
private String border;
private Integer num; //合并单元格个数
public Cell(String value, String align, String font, String border, Integer num) {
super();
this.value = value;
this.align = align;
this.font = font;
this.border = border;
this.num = num;
}
public Cell(String value, String align) {
super();
this.value = value;
this.align = align;
this.font = null;
this.border = null;
this.num = null;
}
public Cell(String value, String align, String font) {
super();
this.value = value;
this.align = align;
this.font = font;
this.border = null;
this.num = null;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public String getAlign() {
return align;
}
public void setAlign(String align) {
this.align = align;
}
public String getFont() {
return font;
}
public void setFont(String font) {
this.font = font;
}
public String getBorder() {
return border;
}
public void setBorder(String border) {
this.border = border;
}
public Integer getNum() {
return num;
}
public void setNum(Integer num) {
this.num = num;
}
}
public static class ExcelTitle {
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private String title;
private int coverCellsNum;
private int fontSize;
private int row;
private short align;
public ExcelTitle(HSSFWorkbook workbook, HSSFSheet sheet, String title, int coverCellsNum,
int fontSize, int row, short align) {
this.workbook = workbook;
this.sheet = sheet;
this.title = title;
this.coverCellsNum = coverCellsNum;
this.fontSize = fontSize;
this.row = row;
this.align = align;
}
public HSSFWorkbook getWorkbook() {
return workbook;
}
public void setWorkbook(HSSFWorkbook workbook) {
this.workbook = workbook;
}
public HSSFSheet getSheet() {
return sheet;
}
public void setSheet(HSSFSheet sheet) {
this.sheet = sheet;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getCoverCellsNum() {
return coverCellsNum;
}
public void setCoverCellsNum(int coverCellsNum) {
this.coverCellsNum = coverCellsNum;
}
public int getFontSize() {
return fontSize;
}
public void setFontSize(int fontSize) {
this.fontSize = fontSize;
}
public int getRow() {
return row;
}
public void setRow(int row) {
this.row = row;
}
public short getAlign() {
return align;
}
public void setAlign(short align) {
this.align = align;
}
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSubTitle() {
return subTitle;
}
public void setSubTitle(String subTitle) {
this.subTitle = subTitle;
}
public List<List<Cell>> getList() {
return list;
}
public void setList(List<List<Cell>> list) {
this.list.clear();
if (null != list) {
this.list.addAll(list);
}
}
public String getSheet() {
return sheet;
}
public void setSheet(String sheet) {
this.sheet = sheet;
}
public String getShop() {
return shop;
}
public void setShop(String shop) {
this.shop = shop;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
}
2、ExcelReport
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ExcelReport {
private String title;// 主标题
private String subTitle;// 副标题
private String sheet;
private String shop;
private String fileName;
private final List<List<Cell>> list = new ArrayList<>();// 需要渲染的数据值
public static class Cell {
public static final String ALIGN_RIGHT = "right"; // 右对齐
public static final String ALIGN_CENTER = "center"; // 居中
public static final String ALIGN_LEFT = "left"; // 左对齐
public static final String FONT_DEFAULT = "default"; // 默认
public static final String FONT_BOLD = "bold"; // 字体为粗体
public static final String BORDER_BOTTOM_NONE = "bottom"; // 不加下边框
public static final String BORDER_TOP_NONE = "top"; // 不加上边框
private String value;
private String align = ALIGN_LEFT;
private String font = FONT_DEFAULT;
private String border;
private Integer num; //合并单元格个数
public Cell(String value, String align, String font, String border, Integer num) {
super();
this.value = value;
this.align = align;
this.font = font;
this.border = border;
this.num = num;
}
public Cell(String value, String align) {
super();
this.value = value;
this.align = align;
this.font = null;
this.border = null;
this.num = null;
}
public Cell(String value, String align, String font) {
super();
this.value = value;
this.align = align;
this.font = font;
this.border = null;
this.num = null;
}
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public String getAlign() {
return align;
}
public void setAlign(String align) {
this.align = align;
}
public String getFont() {
return font;
}
public void setFont(String font) {
this.font = font;
}
public String getBorder() {
return border;
}
public void setBorder(String border) {
this.border = border;
}
public Integer getNum() {
return num;
}
public void setNum(Integer num) {
this.num = num;
}
}
public static class ExcelTitle {
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private String title;
private int coverCellsNum;
private int fontSize;
private int row;
private short align;
public ExcelTitle(HSSFWorkbook workbook, HSSFSheet sheet, String title, int coverCellsNum,
int fontSize, int row, short align) {
this.workbook = workbook;
this.sheet = sheet;
this.title = title;
this.coverCellsNum = coverCellsNum;
this.fontSize = fontSize;
this.row = row;
this.align = align;
}
public HSSFWorkbook getWorkbook() {
return workbook;
}
public void setWorkbook(HSSFWorkbook workbook) {
this.workbook = workbook;
}
public HSSFSheet getSheet() {
return sheet;
}
public void setSheet(HSSFSheet sheet) {
this.sheet = sheet;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public int getCoverCellsNum() {
return coverCellsNum;
}
public void setCoverCellsNum(int coverCellsNum) {
this.coverCellsNum = coverCellsNum;
}
public int getFontSize() {
return fontSize;
}
public void setFontSize(int fontSize) {
this.fontSize = fontSize;
}
public int getRow() {
return row;
}
public void setRow(int row) {
this.row = row;
}
public short getAlign() {
return align;
}
public void setAlign(short align) {
this.align = align;
}
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getSubTitle() {
return subTitle;
}
public void setSubTitle(String subTitle) {
this.subTitle = subTitle;
}
public List<List<Cell>> getList() {
return list;
}
public void setList(List<List<Cell>> list) {
this.list.clear();
if (null != list) {
this.list.addAll(list);
}
}
public String getSheet() {
return sheet;
}
public void setSheet(String sheet) {
this.sheet = sheet;
}
public String getShop() {
return shop;
}
public void setShop(String shop) {
this.shop = shop;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
}
3、Excel生成服务的调用
1、SaleReportExportController
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import com.wordnik.swagger.annotations.ApiOperation;
@Controller
@RequestMapping(value = "{shop}/report/export", produces = "application/json;charset=utf-8")
public class SaleReportExportController extends AbstractController {
@Autowired
private ExcelReportMakerService excelReportMakerService;
public String createExcelBySaleSku2(String shop, List<BSaleSku> bSaleSkus, Date begin, Date end,
String tags) throws Exception {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Shop shopEntity = shopService.get(shop);
if (null == shopEntity) {
throw new Exception("根据shop[" + shop + "]，无法获取到店铺信息！");
}
StringBuffer subTitle = new StringBuffer();
subTitle.append("时间：");
subTitle.append(sdf.format(begin) + "至" + sdf.format(end));
subTitle.append(" ");
subTitle.append("标签：");
subTitle.append(null == tags ? "" : tags);
subTitle.append(" ");
subTitle.append("门店：");
subTitle.append(null == shopEntity.getName() ? "" : shopEntity.getName());
ExcelReport excelReport = new ExcelReport();
excelReport.setTitle("销售报表-销售商品排行");
excelReport.setFileName("销售报表-销售商品排行.xls");
excelReport.setSheet("销售商品排行");
excelReport.setShop(shop);
excelReport.setSubTitle(subTitle.toString());
List<List<Cell>> listAll = new ArrayList<>();
// 列名及对其方式
List<ExcelReport.Cell> rowNameList = new ArrayList<>();
rowNameList.add(createCell("排名", null, Cell.FONT_BOLD));
rowNameList.add(createCell("商品名称", null, Cell.FONT_BOLD));
rowNameList.add(createCell("销售额", null, Cell.FONT_BOLD));
rowNameList.add(createCell("销售量", null, Cell.FONT_BOLD));
rowNameList.add(createCell("退货额", null, Cell.FONT_BOLD));
rowNameList.add(createCell("退货量", null, Cell.FONT_BOLD));
rowNameList.add(createCell("毛利额", null, Cell.FONT_BOLD));
rowNameList.add(createCell("毛利率", null, Cell.FONT_BOLD));
listAll.add(0, rowNameList);
for (int i = 0; i < bSaleSkus.size(); i++) {
BSaleSku bSaleSku = bSaleSkus.get(i);
List<ExcelReport.Cell> listData = new ArrayList<>();
listData.add(createCell(i + 1, Cell.ALIGN_RIGHT, null));
listData.add(createCell(bSaleSku.getName(), null, null));
listData.add(createCell(bSaleSku.getAmount(), Cell.ALIGN_RIGHT, null));
listData.add(createCell(bSaleSku.getTotal().setScale(3, BigDecimal.ROUND_HALF_UP).toString(),
Cell.ALIGN_RIGHT, null));
listData.add(createCell(bSaleSku.getReturnAmount(), Cell.ALIGN_RIGHT, null));
listData
.add(createCell(bSaleSku.getReturnQty().setScale(3, BigDecimal.ROUND_HALF_UP).toString(),
Cell.ALIGN_RIGHT, null));
listData.add(createCell(bSaleSku.getGrossAmount(), Cell.ALIGN_RIGHT, null));
listData.add(
createCell(bSaleSku.getGrossRate().setScale(2, BigDecimal.ROUND_HALF_UP).toString() + "%",
Cell.ALIGN_RIGHT, null));
listAll.add(listData);
}
excelReport.setList(listAll);
String urlPath = excelReportMakerService.make(excelReport);
return urlPath;
}
/**
* 目前只支持部分数据类型，古国需要，自行添加，Bigdecimal类型保留两位小数
*
* @param obj
* @return
*/
private Cell createCell(Object obj, String align, String font) {
if (null == obj) {
return null;
}
if (String.class.isInstance(obj)) {
return new Cell(obj.toString(), align, font, null, null);
} else if (BigDecimal.class.isInstance(obj)) {
return new Cell(((BigDecimal) obj).setScale(2, BigDecimal.ROUND_HALF_UP).toString(), align,
font, null, null);
} else if (Number.class.isInstance(obj)) {
return new Cell(String.valueOf(obj), align, font, null, null);
}
return null;
}
private Map<String, String> mapFilter(ListObjectRequest request) {
Map<String, String> result = new HashMap<>();
for (Entry<String, Object> entry : request.getFiltersAsEntries()) {
result.put(entry.getKey(), ObjectUtils.toString(entry.getValue(), null));
}
return result;
}
private Date parseDate(String dateStr, SimpleDateFormat sdf, String exception)
throws DposException {
try {
Date date = sdf.parse(dateStr);
return date;
} catch (Exception e) {
if (!"".equals(dateStr) && dateStr != null) {
throw new DposException(exception);
}
return null;
}
}
}
注：
该实现并非最好的实现方式，只是其中一种，但可以有效的满足大多数的情况，实例中只生成了最简单的一种，更复杂的效果，可以通过改变参数来实现。
