Pio导出EXCEL
从上到下
@GetMapping("/exportTemp")
@ResponseBody
public void exportTemp(Long id, HttpServletResponse response, HttpServletRequest request) {
HSSFWorkbook wb = null;
OutputStream out = null;
try {
TempRecord tempRecord = tempRecordService.selectTempRecordById(id);
wb = tempRecordService.exportExcel(tempRecord);
String filename = tempRecord.getName();
response.addHeader("Content-Disposition", "attachment;filename=\""
+ new String((filename + ".xls").getBytes("GBK"),
"ISO8859_1") + "\"");
out = response.getOutputStream();
wb.write(out);
} catch (Exception e) {
logger.error("导出Excel异常{}", e.getMessage());
throw new BusinessException("导出Excel失败，请联系网站管理员！");
} finally {
if (wb != null) {
try {
wb.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
if (out != null) {
try {
out.close();
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
}
/**
* 导出excel表格
* @param tr
* @return
*/
public HSSFWorkbook exportExcel(TempRecord tr);
@Override
public HSSFWorkbook exportExcel(TempRecord tr) {
String lstId = tr.getlStId();
LocalStation localStation = localStationMapper.selectLocalStationByLStId(lstId);
String stName = localStation.getName();
String stVoltage = localStation.getVoltage();
List<TempYx> tempYxlist = tempYxMapper.selectTempYxListByTempId(tr.getId());
List<TempYc> tempYclist = tempYcMapper.selectTempYcListByTempId(tr.getId());
List<TempYk> tempYklist = tempYkMapper.selectTempYkListByTempId(tr.getId());
HSSFWorkbook workbook = new HSSFWorkbook();
try {
// header样式（黑色）
HSSFCellStyle headerStyle = workbook.createCellStyle();
headerStyle.setAlignment(HorizontalAlignment.CENTER);
headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headerStyle.setBorderBottom(BorderStyle.THIN);//下边框
headerStyle.setBorderLeft(BorderStyle.THIN);//左边框
headerStyle.setBorderTop(BorderStyle.THIN);//上边框
headerStyle.setBorderRight(BorderStyle.THIN);//右边框
headerStyle.setWrapText(true);// 自动换行
HSSFFont rowFont = workbook.createFont();// 标题字体格式
rowFont.setFontHeightInPoints((short) 10);// 字体大小
rowFont.setFontName("宋体");
headerStyle.setFont(rowFont);
// header样式（红色）
HSSFCellStyle headerRedStyle = workbook.createCellStyle();
headerRedStyle.setAlignment(HorizontalAlignment.CENTER);
headerRedStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headerRedStyle.setBorderBottom(BorderStyle.THIN);//下边框
headerRedStyle.setBorderLeft(BorderStyle.THIN);//左边框
headerRedStyle.setBorderTop(BorderStyle.THIN);//上边框
headerRedStyle.setBorderRight(BorderStyle.THIN);//右边框
headerRedStyle.setWrapText(true);// 自动换行
HSSFFont headerRedFont = workbook.createFont();
headerRedFont.setFontHeightInPoints((short) 10);
headerRedFont.setFontName("宋体");
headerRedFont.setColor(IndexedColors.RED.index);
headerRedStyle.setFont(headerRedFont);
// 正文样式
HSSFCellStyle rowStyle = workbook.createCellStyle();
rowStyle.setAlignment(HorizontalAlignment.LEFT);
rowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
rowStyle.setBorderBottom(BorderStyle.THIN);//下边框
rowStyle.setBorderLeft(BorderStyle.THIN);//左边框
rowStyle.setBorderTop(BorderStyle.THIN);//上边框
rowStyle.setBorderRight(BorderStyle.THIN);//右边框
rowStyle.setFont(rowFont);
rowStyle.setWrapText(true);
// 正文锁定样式
HSSFCellStyle lockstyle = workbook.createCellStyle();
lockstyle.setAlignment(HorizontalAlignment.LEFT);
lockstyle.setVerticalAlignment(VerticalAlignment.CENTER);
lockstyle.setBorderBottom(BorderStyle.THIN);//下边框
lockstyle.setBorderLeft(BorderStyle.THIN);//左边框
lockstyle.setBorderTop(BorderStyle.THIN);//上边框
lockstyle.setBorderRight(BorderStyle.THIN);//右边框
lockstyle.setFont(rowFont);
lockstyle.setLocked(true);
// 生成信息表参数
/*********************************************************信息表参数开始****************************************************/
HSSFSheet sheet1 = workbook.createSheet("信息表参数");
// 设置宽度
sheet1.setColumnWidth(0, 13 * 256);
sheet1.setColumnWidth(1, 37 * 256);
String[] title = {"变电站名称：", "电压等级：", "总控：", "设计单位：", "设计人员：", "运检人员：", "监控人员：", "备注："};
for (int i = 0; i < title.length; i++) {
HSSFRow row_ = sheet1.createRow(i + 1);
row_.setHeight((short) (15.625 * 18));
// 创建第一列
HSSFCell cell__0 = row_.createCell(0);
if (i == 7) {
cell__0.setCellStyle(headerRedStyle);
} else {
cell__0.setCellStyle(rowStyle);
}
cell__0.setCellValue(title[i]);
// 创建第二列
HSSFCell cell__1 = row_.createCell(1);
if (i == 0) {
cell__1.setCellStyle(rowStyle);
cell__1.setCellValue(stName);
} else if (i == 7) {
cell__1.setCellStyle(headerRedStyle);
cell__1.setCellValue("新增/变更/删除信号，红色字体为必填项");
}
}
// 生成遥信表
/*********************************************************遥信表开始****************************************************/
HSSFSheet sheet2 = workbook.createSheet("遥信表");
sheet2.setColumnWidth(0, 4000);
sheet2.setColumnWidth(1, 8000);
sheet2.setColumnWidth(2, 10000);
sheet2.setColumnWidth(3, 14000);
sheet2.setColumnWidth(4, 8000);
sheet2.setColumnWidth(5, 14000);
sheet2.setColumnWidth(6, 12000);
sheet2.setColumnWidth(7, 4000);
sheet2.setColumnWidth(8, 4000);
sheet2.setColumnWidth(9, 4000);
sheet2.setColumnWidth(10, 8000);
sheet2.setColumnWidth(11, 8000);
sheet2.setColumnWidth(12, 4000);
sheet2.setColumnWidth(13, 4000);
sheet2.setColumnWidth(14, 8000);
sheet2.setColumnWidth(15, 4000);
sheet2.setColumnWidth(16, 8000);
sheet2.setColumnWidth(17, 8000);
sheet2.setColumnWidth(18, 4000);
sheet2.setColumnWidth(19, 4000);
sheet2.setColumnWidth(20, 4000);
String[] yx_title = {"间隔", "设备", "信息描述", "备注", "遥信点号", "原遥信名称", "遥信名称", "是否关联开关", "备注说明",
"变更原因", "设备原始信息", "采集装置", "信息属性", "备注", "间隔名称", "间隔类型", "设备名称", "设备类型",
"设备电压等级", "设备型号", "生产厂家"};
// 冻结表头（前两行）
sheet2.createFreezePane(0, 2, 0, 2);
// 遥信创建第一行
HSSFRow yx_row_0 = sheet2.createRow(0);
yx_row_0.setHeight((short) (15.625 * 18));
for (int i = 0; i < yx_title.length; i++) {
HSSFCell yx_cell_0_ = yx_row_0.createCell(i);
yx_cell_0_.setCellStyle(headerRedStyle);
switch (i) {
case 0:
yx_cell_0_.setCellValue("标准典型信息");
break;
case 4:
yx_cell_0_.setCellValue("主站待接入遥信信息");
break;
case 10:
yx_cell_0_.setCellValue("采集装置信息");
break;
case 14:
yx_cell_0_.setCellValue("监控设备信息");
break;
default:
break;
}
}
/** 合并单元格 **/
sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
sheet2.addMergedRegion(new CellRangeAddress(0, 0, 4, 9));
sheet2.addMergedRegion(new CellRangeAddress(0, 0, 10, 13));
sheet2.addMergedRegion(new CellRangeAddress(0, 0, 14, 20));
// 遥信创建第二行
HSSFRow yx_row_1 = sheet2.createRow(1);
yx_row_1.setHeight((short) (15.625 * 48));
for (int i = 0; i < yx_title.length; i++) {
HSSFCell yx_cell_1_ = yx_row_1.createCell(i);
yx_cell_1_.setCellValue(yx_title[i]);
if (i == 3 || i == 8 || i == 13 || i == 15 || i == 17) {
yx_cell_1_.setCellStyle(headerStyle);// 黑色
} else {
yx_cell_1_.setCellStyle(headerRedStyle);// 红色
}
}
// 向表格添加数据，创建单元格，并赋值
for (int i = 0; i < tempYxlist.size(); i++) {
TempYx ver = tempYxlist.get(i);
HSSFRow yx_row_ = sheet2.createRow(i + 2);
yx_row_.setHeight((short) (15.625 * 18));
for (int j = 0; j < yx_title.length; j++) {
HSSFCell yx_cell__ = yx_row_.createCell(j);
yx_cell__.setCellStyle(rowStyle);
switch (j) {
case 0:
/** 间隔 */
if (ver.getYxBay() != null) {
yx_cell__.setCellValue(ver.getYxBay());
}
break;
case 1:
/** 设备 */
if (ver.getYxDev() != null) {
yx_cell__.setCellValue(ver.getYxDev());
}
break;
case 2:
/** 信息描述 */
if (ver.getYxNormInfo() != null) {
yx_cell__.setCellValue(ver.getYxNormInfo());
}
break;
case 3:
/** 备注 */
if (ver.getYxRemark() != null) {
yx_cell__.setCellValue(ver.getYxRemark());
}
break;
case 6:
/** 遥信名称 */
if (ver.getYxName() != null) {
yx_cell__.setCellValue(ver.getYxName());
yx_cell__.setCellStyle(lockstyle);
}
break;
case 11:
/** 采集装置 */
if (ver.getSdDevType() != null && (ver.getSdDevType().equals("保测一体装置") || ver.getSdDevType().equals("测控装置"))) {
yx_cell__.setCellValue(ver.getSdDevType());
yx_cell__.setCellStyle(lockstyle);
}
break;
case 14:
/** 间隔名称 */
if (ver.getBayName() != null) {
yx_cell__.setCellValue(ver.getBayName());
}
break;
case 15:
/** 间隔类型 */
if (ver.getSdBayType() != null) {
yx_cell__.setCellValue(ver.getSdBayType());
}
break;
case 16:
/** 设备名称 */
if (ver.getDevName() != null) {
yx_cell__.setCellValue(ver.getDevName());
}
break;
case 17:
/** 设备类型 */
if (ver.getSdDevType() != null) {
yx_cell__.setCellValue(ver.getSdDevType());
}
break;
case 18:
/** 设备电压等级 */
if (ver.getDevVoltage() != null) {
yx_cell__.setCellValue(ver.getDevVoltage());
}
break;
case 19:
/** 设备型号 */
if (ver.getEqupModel() != null) {
yx_cell__.setCellValue(ver.getEqupModel());
}
break;
case 20:
/** 生产厂家 */
if (ver.getEqupProducer() != null) {
yx_cell__.setCellValue(ver.getEqupProducer());
}
break;
default:
break;
}
}
}
/*********************************************************遥信表结束****************************************************/
// 生成遥测表
/*********************************************************遥测表开始****************************************************/
HSSFSheet sheet3 = workbook.createSheet("遥测表");
sheet3.setColumnWidth(0, 4000);
sheet3.setColumnWidth(1, 8000);
sheet3.setColumnWidth(2, 10000);
sheet3.setColumnWidth(3, 10000);
sheet3.setColumnWidth(4, 4000);
sheet3.setColumnWidth(5, 12000);
sheet3.setColumnWidth(6, 12000);
sheet3.setColumnWidth(7, 4000);
sheet3.setColumnWidth(8, 4000);
sheet3.setColumnWidth(9, 4000);
sheet3.setColumnWidth(10, 8000);
sheet3.setColumnWidth(11, 8000);
sheet3.setColumnWidth(12, 4000);
sheet3.setColumnWidth(13, 8000);
sheet3.setColumnWidth(14, 8000);
sheet3.setColumnWidth(15, 4000);
sheet3.setColumnWidth(16, 8000);
sheet3.setColumnWidth(17, 8000);
sheet3.setColumnWidth(18, 4000);
sheet3.setColumnWidth(19, 8000);
sheet3.setColumnWidth(20, 8000);
String[] yc_title = {"间隔", "信息描述", "越限", "备注", "遥测点号", "原遥测名称", "遥测名称", "上限", "下限", "单位",
"基值", "系数", "CT变比", "备注说明", "变更原因", "设备原始信息", "采集装置", "信息属性", "备注", "设备名称",
"间隔名称"};
sheet3.createFreezePane(0, 2, 0, 2);
// 遥测创建第一行
HSSFRow yc_row_0 = sheet3.createRow(0);
yc_row_0.setHeight((short) (15.625 * 18));
for (int i = 0; i < yc_title.length; i++) {
HSSFCell yc_cell_0_ = yc_row_0.createCell(i);
yc_cell_0_.setCellStyle(headerRedStyle);
switch (i) {
case 0:
yc_cell_0_.setCellValue("标准典型信息");
break;
case 4:
yc_cell_0_.setCellValue("主站待接入遥测信息");
break;
case 15:
yc_cell_0_.setCellValue("采集装置信息");
break;
case 19:
yc_cell_0_.setCellValue("监控设备信息");
break;
default:
break;
}
}
/** 合并单元格 **/
sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
sheet3.addMergedRegion(new CellRangeAddress(0, 0, 4, 14));
sheet3.addMergedRegion(new CellRangeAddress(0, 0, 15, 18));
sheet3.addMergedRegion(new CellRangeAddress(0, 0, 19, 20));
// 遥测创建第二行
HSSFRow yc_row_1 = sheet3.createRow(1);
yc_row_1.setHeight((short) (15.625 * 48));
for (int i = 0; i < yc_title.length; i++) {
HSSFCell yc_cell_1_ = yc_row_1.createCell(i);
yc_cell_1_.setCellValue(yc_title[i]);
if (i == 0 || i == 1 || i == 4 || i == 5 || i == 6 || i == 14 || i == 15 || i == 16 || i == 17 || i == 19 || i == 20) {
yc_cell_1_.setCellStyle(headerRedStyle);// 红色
} else {
yc_cell_1_.setCellStyle(headerStyle);// 黑色
}
}
// 向表格添加数据，创建单元格，并赋值
for (int i = 0; i < tempYclist.size(); i++) {
TempYc ver = tempYclist.get(i);
HSSFRow yc_row_ = sheet3.createRow(i + 2);
yc_row_.setHeight((short) (15.625 * 18));
for (int j = 0; j < yc_title.length; j++) {
HSSFCell yc_cell__ = yc_row_.createCell(j);
yc_cell__.setCellStyle(rowStyle);
switch (j) {
case 0:
/** 间隔 */
if (ver.getYcBay() != null) {
yc_cell__.setCellValue(ver.getYcBay());
}
break;
case 1:
/** 信息描述 */
if (ver.getYcNormInfo() != null) {
yc_cell__.setCellValue(ver.getYcNormInfo());
}
break;
case 2:
/** 越限 */
if (ver.getOverLimit() != null) {
if (1 == ver.getOverLimit()) {
yc_cell__.setCellValue("是");
} else {
yc_cell__.setCellValue("否");
}
}
break;
case 3:
/** 备注 */
if (ver.getYcRemark() != null) {
yc_cell__.setCellValue(ver.getYcRemark());
}
break;
case 6:
/** 遥测名称 */
if (ver.getYcName() != null) {
yc_cell__.setCellValue(ver.getYcName());
}
break;
case 9:
/** 单位 */
if (ver.getUnits() != null) {
yc_cell__.setCellValue(ver.getUnits());
}
break;
case 19:
/** 设备名称 */
if (ver.getDevName() != null) {
yc_cell__.setCellValue(ver.getDevName());
}
break;
case 20:
/** 间隔名称 */
if (ver.getBayName() != null) {
yc_cell__.setCellValue(ver.getBayName());
}
break;
default:
break;
}
}
}
/*********************************************************遥测表结束****************************************************/
// 生成遥控表
/*********************************************************遥控表结束****************************************************/
HSSFSheet sheet4 = workbook.createSheet("遥控表");
sheet4.setColumnWidth(0, 4000);
sheet4.setColumnWidth(1, 12000);
sheet4.setColumnWidth(2, 10000);
sheet4.setColumnWidth(3, 8000);
sheet4.setColumnWidth(4, 4000);
sheet4.setColumnWidth(5, 12000);
sheet4.setColumnWidth(6, 12000);
sheet4.setColumnWidth(7, 4000);
sheet4.setColumnWidth(8, 4000);
sheet4.setColumnWidth(9, 4000);
sheet4.setColumnWidth(10, 8000);
sheet4.setColumnWidth(11, 8000);
sheet4.setColumnWidth(12, 4000);
sheet4.setColumnWidth(13, 4000);
sheet4.setColumnWidth(14, 4000);
sheet4.setColumnWidth(15, 8000);
String[] yk_title = {"间隔", "信息描述", "备注", "遥控点号", "原遥控名称", "遥控名称", "检无压点号", "检同期点号",
"备注说明", "变更原因", "设备原始信息", "采集装置", "信息属性", "备注", "设备名称", "间隔名称"};
// 冻结表头
sheet4.createFreezePane(0, 2, 0, 2);
// 遥控创建第一行
HSSFRow yk_row_0 = sheet4.createRow(0);
yk_row_0.setHeight((short) (15.625 * 18));
for (int i = 0; i < yk_title.length; i++) {
HSSFCell yk_cell_0_ = yk_row_0.createCell(i);
yk_cell_0_.setCellStyle(headerRedStyle);
switch (i) {
case 0:
yk_cell_0_.setCellValue("标准典型信息");
break;
case 3:
yk_cell_0_.setCellValue("主站待接入遥控信息");
break;
case 10:
yk_cell_0_.setCellValue("采集装置信息");
break;
case 14:
yk_cell_0_.setCellValue("监控设备信息");
break;
default:
break;
}
}
/** 合并单元格 **/
sheet4.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
sheet4.addMergedRegion(new CellRangeAddress(0, 0, 3, 9));
sheet4.addMergedRegion(new CellRangeAddress(0, 0, 10, 13));
sheet4.addMergedRegion(new CellRangeAddress(0, 0, 14, 15));
// 遥控创建第二行
HSSFRow yk_row_1 = sheet4.createRow(1);
yk_row_1.setHeight((short) (15.625 * 48));
for (int i = 0; i < yk_title.length; i++) {
HSSFCell yk_cell_1_ = yk_row_1.createCell(i);
yk_cell_1_.setCellValue(yk_title[i]);
if (i == 0 || i == 1 || i == 3 || i == 4 || i == 5 || i == 9 || i == 10 || i == 11 || i == 12 || i == 14 || i == 15) {
yk_cell_1_.setCellStyle(headerRedStyle);// 红色
} else {
yk_cell_1_.setCellStyle(headerStyle);// 黑色
}
}
// 向表格添加数据，创建单元格，并赋值
for (int i = 0; i < tempYklist.size(); i++) {
TempYk ver = tempYklist.get(i);
HSSFRow yk_row_ = sheet4.createRow(i + 2);
yk_row_.setHeight((short) (15.625 * 18));
for (int j = 0; j < yk_title.length; j++) {
HSSFCell yk_cell__ = yk_row_.createCell(j);
yk_cell__.setCellStyle(rowStyle);
switch (j) {
case 0:
/** 间隔 */
if (ver.getYkBay() != null) {
yk_cell__.setCellValue(ver.getYkBay());
}
break;
case 1:
/** 信息描述 */
if (ver.getYkNormInfo() != null) {
yk_cell__.setCellValue(ver.getYkNormInfo());
}
break;
case 2:
/** 备注 */
if (ver.getYkRemark() != null) {
yk_cell__.setCellValue(ver.getYkRemark());
}
break;
case 5:
/** 遥控名称 */
if (ver.getYkName() != null) {
yk_cell__.setCellValue(ver.getYkName());
}
break;
case 14:
/** 设备名称 */
if (ver.getDevName() != null) {
yk_cell__.setCellValue(ver.getDevName());
}
break;
case 15:
/** 间隔名称 */
if (ver.getBayName() != null) {
yk_cell__.setCellValue(ver.getBayName());
}
break;
default:
break;
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
浙公网安备 33010602011771号