package com.leinovo.qes.portal.modules.report.controller;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Arrays;
import java.util.List;
/**
* @description: ExcelController
* @author: leiming5
* @date: 2020-10-28 17:35
*/
@Api(tags = "Excel Download 接口")
@RestController
public class ExcelController {
/**
* EXCEL 生成,然后,下载
*
* @param response 响应对象
* @author: leiming5
*/
@ApiOperation("report/download")
@GetMapping("report/download")
public void test22(HttpServletResponse response) {
String fileName = "template.XLSX";
response.setHeader("content-disposition", "attachment;filename=" + fileName);
try (OutputStream out = response.getOutputStream()) {
Workbook workbook = new XSSFWorkbook();
//生成一个表格
Sheet sheet = workbook.createSheet("模板Excel");
// 获取表格数据
List<List<String>> excelHead = generateHead();
List<List<Object>> excelData = generateData();
createExcel(sheet, excelHead, excelData, workbook);
workbook.write(out);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 将表头和数据一起写入excel,并设置样式
*
* @param sheet 表格
* @param excelHead 表头数据
* @param excelData 表个数据
* @param workbook 当前工作簿
* @author: leiming5
*/
private void createExcel(Sheet sheet, List<List<String>> excelHead, List<List<Object>> excelData, Workbook workbook) {
// 放入表头字段
for (int i = 0; i < excelHead.size(); i++) {
Row row = sheet.createRow(i);
List<String> list = excelHead.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell = row.createCell(j);
cell.setCellValue(list.get(j));
cell.setCellStyle(getCellStyle(workbook, "head"));
if (i == 1) {
// sheet.autoSizeColumn(j); // 设置自适应列宽
sheet.setColumnWidth(j, 18 * 256);
}
}
}
// 合并标题行
CellRangeAddress range = new CellRangeAddress(0, 0, 0, excelHead.get(1).size() - 1);
sheet.addMergedRegion(range);
// 设置列宽
// sheet.setDefaultColumnWidth(18); // 设置默认列宽
sheet.setColumnWidth(0, 26 * 256);
sheet.setColumnWidth(1, 26 * 256);
// 放入数据
for (int i = 0; i < excelData.size(); i++) {
Row row = sheet.createRow(i + excelHead.size());
List<Object> list = excelData.get(i);
for (int j = 0; j < list.size(); j++) {
Cell cell = row.createCell(j);
Object o = list.get(j);
String str = String.valueOf(o);
if (isNumeric(str)) {
cell.setCellValue(Double.parseDouble(str));
} else {
cell.setCellValue(str);
}
cell.setCellStyle(getCellStyle(workbook, "body"));
}
}
}
/**
* 判断表格数据类型
*
* @param str 原始数据
* @return true:数字类型,false:字符类型
* @author: leiming5
*/
public static boolean isNumeric(String str) {
try {
Double.valueOf(str);
} catch (NumberFormatException e) {
return false;
}
return true;
}
/**
* 设置单元个样式
*
* @param workbook 工作簿
* @param type 自定义单元格类型,head:表头,body:表格主体
* @return 单元格样式
* @author: leiming5
*/
private CellStyle getCellStyle(Workbook workbook, String type) {
// Sheet样式
CellStyle columnHeadStyle = workbook.createCellStyle();
// 背景色的设定 自动换行
columnHeadStyle.setWrapText(true);
//设置字体颜色
Font font = workbook.createFont();
short colorIndex;
switch (type) {
case "head":
colorIndex = HSSFColor.HSSFColorPredefined.RED.getIndex();
break;
case "body":
colorIndex = HSSFColor.HSSFColorPredefined.BLACK.getIndex();
break;
default:
colorIndex = HSSFColor.HSSFColorPredefined.BLACK.getIndex();
}
font.setColor(colorIndex);
//将字体设置应用到当前样式
columnHeadStyle.setFont(font);
//水平居中,垂直居中
columnHeadStyle.setAlignment(HorizontalAlignment.CENTER);
columnHeadStyle.setVerticalAlignment(VerticalAlignment.CENTER);
return columnHeadStyle;
}
/**
* 模拟表格数据
*
* @return 表格数据
* @author: leiming5
*/
private List<List<Object>> generateData() {
List<Object> rowOne = Arrays.asList(1, "张三", "男", 18);
List<Object> rowTwo = Arrays.asList(2, "李四", "女", 19);
List<Object> rowTree = Arrays.asList(3, "李mao", "女", 21);
return Arrays.asList(rowOne, rowTwo, rowTree);
}
/**
* 模拟表头字段
*
* @return 表头字段
* @author: leiming5
*/
private List<List<String>> generateHead() {
List<String> headerOne = Arrays.asList("这是标题", "", "", "");
List<String> headerTwo = Arrays.asList("序号", "姓名", "性别", "年龄");
return Arrays.asList(headerOne, headerTwo);
}
}
poi excel 下载
最新推荐文章于 2024-08-08 22:58:45 发布