springboot poi多sheet excel导出
从hbase 查出数据 一个设备有多个参数 每个参数的数据为一个sheet 仅做记录
/**
* 输出Excel文档
* @param workbook
* @param resources 源数据
* @param headerNames 表头
* @param sheetName 表格名
* @param columnNum 列数量
* @param sheetNum 页码(sheet页码)
* @throws IOException
*/
public static void writeExcel(HSSFWorkbook workbook,
List<String[]> resources,
String[] headerNames,
String sheetName,
Integer columnNum,
Integer sheetNum) throws IOException {
// 创建表格
HSSFSheet sheet = workbook.createSheet();
sheet.setDefaultRowHeightInPoints(13);//默认宽度
workbook.setSheetName(sheetNum, sheetName);
// 设置列宽，根据
for(int i=0; i<=columnNum; i++){
sheet.setColumnWidth(i, 6000);
}
/*
* 创建合并区域
* CellRangeAddress(int 首行, int 最后一行, int 首列, int 最后一列);
*/
CellRangeAddress add = new CellRangeAddress(0, 0, 0, columnNum);
// 将创建的合并区域设置到表格中.
sheet.addMergedRegion(add);
// 创建行
Row header = sheet.createRow(0);
// 创建单元格. 合并后的单元格,编号合并.
//设置样式
CellStyle titleStyle = workbook.createCellStyle();
Font titlefont = workbook.createFont();
titlefont.setFontName("黑体");
//titlefont.setColor(IndexedColors.VIOLET.index);
titlefont.setFontHeightInPoints((short)20);
titlefont.setBold(true);
titleStyle.setFont(titlefont);
titleStyle.setAlignment(HorizontalAlignment.CENTER);
Cell c = header.createCell(0);
c.setCellValue(sheetName);
c.setCellStyle(titleStyle);
c = header.createCell(columnNum);
// 编写表头
// 定义表头的样式
CellStyle headerStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName("宋体");
//font.setColor(IndexedColors.VIOLET.index);
font.setFontHeightInPoints((short)16);
headerStyle.setFont(font);
headerStyle.setAlignment(HorizontalAlignment.CENTER);
// 设置单元格样式
Row headerRow = sheet.createRow(1);
for (int i = 0; i < headerNames.length; i++) {
Cell cell = headerRow.createCell(i);
// 设置单元格样式
cell.setCellStyle(headerStyle);
cell.setCellValue(headerNames[i]);
}
// 设置表格数据的样式
CellStyle bodyStyle = workbook.createCellStyle();
Font bodyFont = workbook.createFont();
bodyFont.setFontName("微软雅黑");
//bodyFont.setColor(IndexedColors.BLUE.index);
bodyFont.setFontHeightInPoints((short)12);
bodyStyle.setFont(bodyFont);
// 编辑表格体数据
for (int i = 0; i < resources.size(); i++) {
// 获取行数据
String[] temp = resources.get(i);
// 创建行
Row bodyRow = sheet.createRow(i + 2);
for (int cellNum = 0; cellNum < temp.length; cellNum++) {
Cell bodyCell = bodyRow.createCell(cellNum);
bodyCell.setCellStyle(bodyStyle);
bodyCell.setCellValue(temp[cellNum]);
}
}
sheet.getRow(0).setHeightInPoints(24);
sheet.getRow(1).setHeightInPoints(20);
}
@Service
@Slf4j
public class ExportEquipHistoryDataServiceImpl implements ExportEquipHistoryDataService {
@Resource(name = "connection2")
private Connection connection;
@Value("${hbase.equip2.table.name}")
private String TEST_EQUIP_TABLE_NAME;
@Autowired
private EquipHistoryDataMapper equipHistoryDataMapper;
private static final int DEVICE_DEFAULT_PAGE_SIZE = 50000;
@Override
public void exportHistory( Map<String, Object> map) throws IOException {
ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = servletRequestAttributes.getResponse();
String equipCode = (String) map.get("equipCode");
//根据机械码 获取设备信息
List<EquipReParamView> paramViews = equipHistoryDataMapper.paramList(equipCode);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
if (paramViews != null) {
for (int i = 0; i < paramViews.size(); i++){
EquipReParamView pv = paramViews.get(i);
map.put("collectId", pv.getCollectId());
List<String[]> historyData = EquipParamHistoryData(map, pv.getEquipName(), pv.getAlias());
try {
PoiExcelExport.writeExcel(hssfWorkbook, historyData,
new String[]{"设备编号",
"设备名称",
"采集时间",
"参数编号",
"参数名称",
"采集值",
"单位"},
pv.getAlias(),
7,
i
);
} catch (IOException e) {
e.printStackTrace();
}
}};
//输出
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("历史数据表", "utf-8"));
OutputStream out = new BufferedOutputStream(response.getOutputStream());
try{
hssfWorkbook.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try{
// 清理资源
out.close();//关闭
}catch(Exception e){
e.printStackTrace();
}
}
}
//List<Map<String,List<String[]>>>
private List<String[]> EquipParamHistoryData(Map<String, Object> map,String equipName,String paramName) {
String equipCode = (String) map.get("equipCode");
String collectId = String.valueOf(map.get("collectId"));
if (equipCode == null || equipCode.trim().equals("") ||collectId == null) {
return Collections.emptyList();
}
Scan scan = reBuildScan(map, equipCode, collectId);
Table table = null;
List<String[]> tm= new ArrayList<>();
ResultScanner rs = null;
String rowKey = null;
try {
table = connection.getTable(TableName.valueOf(TEST_EQUIP_TABLE_NAME));
rs = table.getScanner(scan);
Result r;
while ((r = rs.next()) != null) {
// 每一行数据
Map<String, String> columnMap = new HashMap<>();
// 行键，列族和列限定符一起确定一个单元（Cell）
rowKey = new String(r.getRow());
columnMap.put("rowKey", rowKey);
for (Cell cell : r.listCells()) {
columnMap.put(Bytes.toString(CellUtil.cloneQualifier(cell)), Bytes.toString(CellUtil.cloneValue(cell)));
}
if (rowKey != null) {
String collectTime = columnMap.get("collectTime");
String time = DateFormateUtils.milliSecond2Date(collectTime, "yyyy-MM-dd HH:mm:ss");
String[] res = new String[]{equipCode, equipName, time, columnMap.get("modelCode"), paramName, columnMap.get("data"), columnMap.get("unitName")};
tm.add(res);
}
}
} catch (IOException e) {
log.error(MessageFormat.format("查询历史数据失败,equipCode:{0}", equipCode), e);
return Collections.emptyList();
} finally {
if (rs != null) {
rs.close();
}
}
return tm;
}
private Scan reBuildScan(Map<String, Object> map, String equipCode, String collectId) {
Integer enterpriseId = (Integer) map.get("enterpriseId");
if(enterpriseId == null) {
enterpriseId = LoginContextHolder.getCurEnterPriseId();
}
String startDate = (String) map.get("startDate");
String endDate = (String) map.get("endDate");
String startRow = (String) map.get("startRow");
String endRow = (String) map.get("endRow");
Scan scan = new Scan();
if (StringUtils.isNotBlank(endRow)) {
scan.setStopRow(Bytes.toBytes(endRow));
} else if (StringUtils.isNotBlank(endDate)) {
scan.setStopRow(Bytes.toBytes(equipCode + "_" + collectId + "_" + enterpriseId+ "_" + startDate));
}
if (StringUtils.isNotBlank(startRow)) {
scan.setStartRow(Bytes.toBytes(startRow));
} else if (StringUtils.isNotBlank(startDate)) {
scan.setStartRow(Bytes.toBytes(equipCode + "_" + collectId + "_" + enterpriseId+ "_" + endDate));
}
//降序
scan.setReversed(true); //HBase自带的方法，但需要将startRow设置为终止行，endRow设置为起始行
//filter
FilterList filterList = new FilterList(FilterList.Operator.MUST_PASS_ALL);
Filter filter = new RowFilter(CompareFilter.CompareOp.EQUAL, new RegexStringComparator(equipCode + "_" + collectId+ "_" + enterpriseId+ "_.*"));
filterList.addFilter(filter);
int pageSize = (map != null && map.get("pageSize") != null) ? (int) map.get("pageSize") :
DEVICE_DEFAULT_PAGE_SIZE;
map.put("pageSize", pageSize);
Filter pageFilter = new PageFilter(pageSize + 1);
filterList.addFilter(pageFilter);
scan.setFilter(filterList);
return scan;
}
}
