导出步骤
- 1、创建 workbook.createSheet
- 2、 获取查询数据
- 3、创建行 sheet.createRow(0);
- 设置行高 row.setHeight((short) 600);
- 4、创建列 row.createCell(i);
- 设置行宽 sheet.setColumnWidth(i, 4000);
- 设置单元格样式 cell.setCellStyle(getColumnTopStyle(workbook));
一、导出excel
@RequestMapping(value = "projectDownload", method = RequestMethod.GET)
@ApiOperation(value = "导出项目")
public void projectDownload(HttpServletResponse response,
@RequestParam(required = false) String projectName,
@RequestParam(required = false) String leaderId,
@RequestParam(required = false) ProjectType projectType,
@RequestParam(required = false) ProjectVerifyResult projectVerifyResult,
@RequestParam(required = false) Long startQueryTime,
@RequestParam(required = false) Long endQueryTime) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("项目表");
//获取excel数据
List<ProjectDto> list = projectService.findAll(projectName, leaderId, projectType, projectVerifyResult, startQueryTime, endQueryTime);
//设置导出的文件名称
String fileName = "项目详细信息" + ".xls";
//设置excel首行标题
String[] headers = {"项目ID", "项目名称", "项目负责人ID", "项目负责人名称", "分包金额", "项目**", "项目**", "项目**", "项目**", "项目**", "项目**", "项目**", "项目**", "开始时间", "结束时间", "实际结束时间", "项目是否完成", "实际进度"};
//headers表示excel表中第一行的表头
HSSFRow row = sheet.createRow(0);
//设置首行行高
row.setHeight((short) 600);
//在excel表中添加表头数据
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
//设置首行行宽
sheet.setColumnWidth(i, 4000);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
//设置首行样式
cell.setCellStyle(getColumnTopStyle(workbook));
cell.setCellValue(text);
}
//新增数据行，并且设置单元格数据
int rowNum = 1;
//在表中存放查询到的数据放入对应的列
if (list.size() > 0) {
for (ProjectDto projectDto : list) {
HSSFRow row1 = sheet.createRow(rowNum);
//设置行高
row1.setHeight((short) 500);
row1.createCell(0).setCellValue(projectDto.getId());
//省略
//枚举类型转换为中文
switch (projectDto.getProjectType().toString()) {
case "SOFTWARE":
row1.createCell(7).setCellValue("软件开发");
break;
case "INFORMATION":
row1.createCell(7).setCellValue("信息化");
break;
case "ELSE":
row1.createCell(7).setCellValue("其他");
}
//日期类型
row1.createCell(13).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date(projectDto.getStartTime())));
row1.createCell(14).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date(projectDto.getEndTime())));
row1.createCell(15).setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(new Date(projectDto.getRealityTime())));
//布尔类型转换
if (projectDto.isComplete()) {
row1.createCell(16).setCellValue("完成");
} else {
row1.createCell(16).setCellValue("未完成");
}
row1.createCell(17).setCellValue(projectDto.getRealityProgress()+"");
rowNum++;
//设置单元格样式
for (int ii = 0; ii < 18; ii++) {
row1.getCell(ii).setCellStyle(getStyle(workbook));
}
}
}
response.setHeader("Content-Disposition", "attachment;fileName=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
response.setContentType("application/octet-stream");
response.flushBuffer();
workbook.write(response.getOutputStream());
}二、excel 单元格样式
package ccsah.avatar.web.component.utils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
/**
* @Author: ***
* @DateTime: 2020/11/17 14:22
* @Description: TODO
*/
public class ExcelStyle {
/*
* 列头单元格样式
*/
public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 11);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
/*
* 列数据信息单元格样式
*/
public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.TAN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
return style;
}
//设置标题样式
public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
HSSFCellStyle cellStyle = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 13);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置水平对齐的样式为居中对齐;
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
return cellStyle;
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
