Controller层的接口调用：
注意：此token中保存着导出人的信息，不需要的可以直接删除updateId字段
//FinanceParam为自定义的实体类，封装了导出时的搜索条件
@ResponseBody
@ApiOperation(value = "xxxx-导出-【0:sheet0，1:sheet1，2:sheet2,3:sheet0+sheet1+sheet2】")
@RequestMapping(value = "exportDetail", method = RequestMethod.GET)
public byte[] exportDetail(FinanceParam param, HttpServletResponse response, @RequestParam(value = "token", defaultValue = "") String token) {
try {
Integer updateId = null;
if (StringUtils.isNotBlank(token)) {
updateId = JWTUtil.getUserId(token);
}
//定义excel文件的名字
String fileName = "income_" + DateFormatUtils.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";
//设置头信息
response.setHeader("Content-Disposition", "attachment;filename=error.txt");// 为文件命名
//添加头信息：防止乱码
response.addHeader("Content-Disposition",
"inline;filename=" +
new String(fileName.getBytes("utf-8"), "iso8859-1"));
File file = null;
//创建excel表格
XSSFWorkbook wb = new XSSFWorkbook();
//获取数据(自定义方法)
getExportData(wb, param, updateId, fileName);
//export()为公共方法，见下；tempFolder为下载地址，需要在配置文件中配置，并使用@value()注解引入，示例如下
/*@Value("${excel.upload.path}")
private String tempFolder;*/
file = export(tempFolder + "/" + fileName);
try (FileOutputStream fout = new FileOutputStream(tempFolder + "/" + fileName)) {
wb.write(fout);
}
try (FileInputStream fis = new FileInputStream(file)) {
byte[] b = new byte[fis.available()];
if (fis.read(b) > 0) {
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);// 为文件命名
return b;
}
} catch (RuntimeException e) {
logger.error("baseException ", e);
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
} catch (Exception e) {
logger.error("导出文件异常", e);
}
return null;
}export()方法
public static File export(String filename) throws Exception {
File savePath = new File(filename);
if (!savePath.exists()) {
File fileParent = savePath.getParentFile();
fileParent.mkdirs();
if (!savePath.createNewFile()) {
throw new RuntimeException("create file fail.");
}
}
return savePath;
}getExportData（）
获取导出的数据，判断是否导出多个sheet,若sheet分开导出，则跳过此方法
private void getExportData(XSSFWorkbook wb, FinanceParam param, Integer updateId, String fileName) throws ParseException {
if (param.getExportCode() == 3) {
for (int i = 0; i < 3; i++) {
param.setExportCode(i);
exportDate(wb, param, updateId, fileName);
}
} else {
exportDate(wb, param, updateId, fileName);
}
}exportDate（）
此方法设置为不同的sheet设置了名字，BaseCommon为常量类，定义一些常用的字符串和类型，此处可以直接拿字符串代替，（不希望代码中出现汉字相关）
private void exportDate(XSSFWorkbook wb, FinanceParam param, Integer updateId, String fileName) throws ParseException {
//设置sheet名
String sheetName = null;
if (BaseCommon.CONSUM_TYPE_ID_0.equals(param.getExportCode())) {
sheetName = BaseCommon.MEAL_INCOME_DETAIL;
} else if (BaseCommon.CONSUM_TYPE_ID_1.equals(param.getExportCode())) {
sheetName = BaseCommon.ROOM_INCOME_DETAIL;
} else if (BaseCommon.CONSUM_TYPE_ID_2.equals(param.getExportCode())) {
sheetName = BaseCommon.OTHER_INCOME_DETAIL;
}
//创建sheet
XSSFSheet sheet = wb.createSheet(sheetName);
//ExcelData为自定义的实体对象，封装导出的数据：见下
//exportService为service层，所有导出在同已service中处理
ExcelData data = exportService.exportDetail(wb, param, sheet, financeService.exportDetail(param, updateId), fileName, financeService.income(param), managerMapper.selectByPrimaryKey(updateId).getName());
//设置表格数的样式,见下
writeRowsToExcel(wb, sheet, data.getRows(), data.getRowNum());
//设置自动换行，见下
autoSizeColumns(sheet, data.getTitles().size() + 1);
}ExcelData实体对象
import java.io.Serializable;
import java.util.List;
public class ExcelData implements Serializable {
private static final long serialVersionUID = 1L;
// 表头
private List<String> titles;
// 数据
private List<List<Object>> rows;
// 名称
private String name;
//表头所在行数：表头上可能设置导出的明细内容
private int rowNum;
public int getRowNum() {
return rowNum;
}
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
public List<String> getTitles() {
return titles;
}
public void setTitles(List<String> titles) {
this.titles = titles;
}
public List<List<Object>> getRows() {
return rows;
}
public void setRows(List<List<Object>> rows) {
this.rows = rows;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}exportDetail()方法
/**
* @param list：excel中遍历的对象，即列表
* 其他参数可根据需要进行删除
*/
public ExcelData exportDetail(XSSFWorkbook wb, FinanceParam param, XSSFSheet sheet, List<FinanceResult> list, String fileName, FinancesResult income, String name) {
//起始行为0
int rowNum = 0;
XSSFRow r0 = sheet.createRow(rowNum++);
XSSFCell c0 = r0.createCell(0);
c0.setCellValue("导出人：" + name);
XSSFRow r1 = sheet.createRow(rowNum++);
XSSFCell c1 = r1.createCell(0);
c1.setCellValue("导出时间：" + sdf.format(new Date()));
XSSFRow r2 = sheet.createRow(rowNum++);
XSSFCell c2 = r2.createCell(0);
c2.setCellValue("统计日期：" + param.getStartTime() + "至" + param.getEndTime());
XSSFRow r4 = sheet.createRow(rowNum++);
XSSFCell c4 = r4.createCell(0);
c4.setCellValue("收款记录：" + list.size() + "笔");
//以上为表的流水信息
//开始真正的数据操作
ExcelData data = new ExcelData();
data.setName(fileName);//文件名
String[] titles;
//根据不同的条件，设置不同的表头
switch (param.getExportCode()) {
case 1:
titles = new String[]{"日期", "交易号", "客户姓名", "手机号", "来源", "收入类别", "明细项目", "接待人数", "间夜数", "消费总额", "收入金额", "XXXX金", "XXXX金币", "备注"};
break;
case 0:
titles = new String[]{"日期", "交易号", "客户姓名", "手机号", "来源", "收入类别", "类别明细", "明细项目", "接待人数", "消费总额", "收入金额", "XXXX金", "XXXX金币", "核销状态", "备注"};
break;
case 2:
titles = new String[]{"日期", "交易号", "客户姓名", "手机号", "来源", "收入类别", "明细项目", "接待人数", "消费总额", "收入金额", "XXXX金", "XXXX金币", "备注"};
break;
default:
return null;
}
//writeTitlesToExcel()方法设计表头的样式，顺便返回白哦头下一行数：见下
rowNum = ExportExcelUtils.writeTitlesToExcel(wb, sheet, Arrays.asList(titles), rowNum);
data.setRowNum(rowNum);
//**************************************合并***start***********************
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.length - 1));
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, titles.length - 1));
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, titles.length - 1));
sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, titles.length - 1));
//sheet.addMergedRegion(new CellRangeAddress(4, 4, 0, titles.length - 1));
//***************************************合并***end***************************
data.setTitles(Arrays.asList(titles));
//开始设置数据
List<List<Object>> rows = new ArrayList<List<Object>>();
if (list != null && list.size() > 0) {
for (FinanceResult lo : list) {
List<Object> row = new ArrayList<Object>();
row.add(sdf1.format(lo.getNowDate()));
row.add(lo.getOrderNumber());
row.add(lo.getUserName());
row.add(lo.getPhone());
row.add(lo.getSource());
row.add(lo.getType());
if (BaseCommon.CONSUM_TYPE_ID_0.equals(param.getExportCode())) {
row.add(lo.getCateType());
}
row.add(lo.getSetMeal());
row.add(lo.getReceNumber());
if (BaseCommon.CONSUM_TYPE_ID_1.equals(param.getExportCode())) {
row.add(lo.getDays());
}
row.add("￥" + lo.getIncomeAll());
row.add("￥" +lo.getIncome());
row.add(lo.getCon());
row.add(lo.getSun());
if (BaseCommon.CONSUM_TYPE_ID_0.equals(param.getExportCode())) {
row.add(lo.getIsver());
}
row.add(lo.getRemark());
rows.add(row);
rowNum++;
}
}
//**************************************汇总合并***start***********************
sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum + 1, 0, titles.length - 1));
//***************************************汇总合并***end***************************
List<Object> row1 = new ArrayList<Object>();
row1.add("消费合计:￥" + income.getPriceAll() + " 收款合计:￥" + income.getDetailAll()
+ " 消费金合计:" + income.getConAll() +"个"+ " 日光金币合计:" + income.getSunAll() +"个");
rows.add(row1);
data.setRows(rows);
return data;
}writeTitlesToExcel()工具方法
public static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles,Integer rowNum) {
//方便无流水信息的接口调取，无流水信息，则可以不传入行数
int rowIndex = 0;
if (null !=rowNum){
rowIndex =rowNum;
}
int colIndex = 0;
XSSFFont titleFont = wb.createFont();
titleFont.setFontName("simsun");
titleFont.setBold(true);
// titleFont.setFontHeightInPoints((short) 14);
titleFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
titleStyle.setVerticalAlignment(titleStyle.getVerticalAlignmentEnum().CENTER);//居中
// titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
titleStyle.setFillForegroundColor(new XSSFColor(new java.awt.Color(182, 184, 192)));
// titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setFont(titleFont);
setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));
Row titleRow = sheet.createRow(rowIndex);
// titleRow.setHeightInPoints(25);
colIndex = 0;
for (String field : titles) {
Cell cell = titleRow.createCell(colIndex);
cell.setCellValue(field);
cell.setCellStyle(titleStyle);
colIndex++;
}
rowIndex++;
return rowIndex;
}writeRowsToExcel()工具方法
public static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
int colIndex = 0;
XSSFFont dataFont = wb.createFont();
dataFont.setFontName("simsun");
// dataFont.setFontHeightInPoints((short) 14);
dataFont.setColor(IndexedColors.BLACK.index);
XSSFCellStyle dataStyle = wb.createCellStyle();
dataStyle.setAlignment(HorizontalAlignment.RIGHT); //字体右对齐
// dataStyle.setAlignment(HorizontalAlignment.CENTER);//字体居中
dataStyle.setVerticalAlignment(dataStyle.getVerticalAlignmentEnum().CENTER);
// dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
dataStyle.setFont(dataFont);
setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new java.awt.Color(0, 0, 0)));
dataStyle.setWrapText(true);//自动换行
if (rows == null) {
return 0;
}
for (int i = 0; i < rows.size(); i++) {
List<Object> rowData = rows.get(i);
Row dataRow = sheet.createRow(rowIndex);
colIndex = 0;
for (int j = 0; j < rowData.size(); j++) {
Object cellData = rowData.get(j);
Cell cell = dataRow.createCell(colIndex);
if (cellData != null) {
/**图片插入*/
String cellDataStr = cellData.toString();
if (cellDataStr.endsWith(".jpg") || cellDataStr.endsWith(".JPG") || cellDataStr.endsWith(".jpeg") || cellDataStr.endsWith(".JPEG") || cellDataStr.endsWith(".png") || cellDataStr.endsWith(".PNG")) {
dataRow.setHeight((short) 600);
//画图的顶级管理器
XSSFDrawing patriarch = (XSSFDrawing) sheet.createDrawingPatriarch();
//为图片管理器配置参数
/**
* 该构造函数有8个参数
* 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
* 后四个参数，前两个个表示图片左上角所在的cellNum和 rowNum，后两个参数对应的表示图片右下角所在的cellNum和 rowNum，
* excel中的cellNum和rowNum的index都是从0开始的
*
*/
XSSFClientAnchor anchor = new XSSFClientAnchor(100, 100, 255, 255, j, i + 1, j + 1, i + 2);
anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_DO_RESIZE);
ByteArrayOutputStream byteOutPut = new ByteArrayOutputStream();
//读取到图片信息
BufferedImage bufferImage;
try {
// bufferImage = ImageIO.read(new File("C:/timg (4).jpg"));
String imageId = cellDataStr.split(".com/")[1].replace(".", "#").split("#")[0];
String localImageUrl = imageTempPath + imageId + ".jpg";
File file = new File(localImageUrl);
if (!file.exists()) {
DownLoadService.downloadImage(cellDataStr, imageId);
}
bufferImage = ImageIO.read(file);
//将图片写入到ByteArrayOutputStream中
ImageIO.write(bufferImage, "png", byteOutPut);
} catch (IOException e) {
}
//参数1 代表图片的位置信息 参数2 代表图片来源
patriarch.createPicture(anchor, wb.addPicture(byteOutPut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_PNG));
/**
* 设置打印区域
*/
//设置哪一个sheet中的第几行到第几行 第几列到第几列
wb.setPrintArea(0, 1, 9, 1, 10);
//设置纸张大小
sheet.getPrintSetup().setPaperSize(XSSFPrintSetup.A4_PAPERSIZE);
//设置是否显示网格线
sheet.setDisplayGridlines(true);
//设置是否打印网格线
sheet.setPrintGridlines(true);
} else {
cell.setCellValue(cellData.toString());
}
} else {
cell.setCellValue("");
}
cell.setCellStyle(dataStyle);
colIndex++;
}
rowIndex++;
}
return rowIndex;
}autoSizeColumns()工具方法
public static void autoSizeColumns(Sheet sheet, int columnNumber) {
for (int i = 0; i < columnNumber; i++) {
int orgWidth = sheet.getColumnWidth(i);
sheet.autoSizeColumn(i, true);
int newWidth = (int) (sheet.getColumnWidth(i) + 100);
if (newWidth > orgWidth) {
sheet.setColumnWidth(i, newWidth);
} else {
sheet.setColumnWidth(i, orgWidth);
}
}
}完成实例：
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
