先準備poi需要的坐标：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
以下是核心代碼：（每一行注釋都很清晰哦~）
@Controller
@RequestMapping(value = "/report")
public class ReportController {
/**
* 導出excel報表
*/
@GetMapping(value = "/excel")
public void excel( HttpServletResponse response) throws Exception {
List<TestDataVo> list = datalist();
HSSFWorkbook workbook = null ;
BufferedOutputStream bufferedOutPut = null;
try {
workbook = new HSSFWorkbook();
// 建立頁
HSSFSheet sheet = workbook.createSheet("Sheet1");
//設定列寬
sheet.setColumnWidth(0, 256*35);
sheet.setColumnWidth(1, 256*35);
sheet.setColumnWidth(2, 256*35);
sheet.setColumnWidth(3, 256*35);
sheet.setColumnWidth(4, 256*35);
// 建立行
HSSFRow firstrow = sheet.createRow(0);
// 建立列
HSSFCell cell0 = firstrow.createCell(0);
cell0.setCellStyle(getColumnTopStyle(workbook));
cell0.setCellValue("時間");
// 建立列
HSSFCell cell1 = firstrow.createCell(1);
cell1.setCellStyle(getColumnTopStyle(workbook));
cell1.setCellValue("姓名");
// 建立列
HSSFCell cell2 = firstrow.createCell(2);
cell2.setCellStyle(getColumnTopStyle(workbook));
cell2.setCellValue("單号");
// 建立列
HSSFCell cell3 = firstrow.createCell(3);
cell3.setCellStyle(getColumnTopStyle(workbook));
cell3.setCellValue("位址");
// 建立列
HSSFCell cell4 = firstrow.createCell(4);
cell4.setCellStyle(getColumnTopStyle(workbook));
cell4.setCellValue("用途");
for (TestDataVo testDataVo : list) {
HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
//設定單元格的值，并且設定樣式
HSSFCell cell00 = row.createCell(0);
cell00.setCellStyle(getStyle(workbook));
cell00.setCellValue(testDataVo.getDate());
//設定單元格的值，并且設定樣式
HSSFCell cell01 = row.createCell(1);
cell01.setCellStyle(getStyle(workbook));
cell01.setCellValue(testDataVo.getName());
//設定單元格的值，并且設定樣式
HSSFCell cell02 = row.createCell(2);
cell02.setCellStyle(getStyle(workbook));
cell02.setCellValue(testDataVo.getNumbers());
//設定單元格的值，并且設定樣式
HSSFCell cell03 = row.createCell(3);
cell03.setCellStyle(getStyle(workbook));
cell03.setCellValue(testDataVo.getAddress());
//設定單元格的值，并且設定樣式
HSSFCell cell04 = row.createCell(4);
cell04.setCellStyle(getStyle(workbook));
cell04.setCellValue(testDataVo.getPurpose());
}
String filename = "導出資料.xls";
response.setHeader("Content-Disposition",
"attachment;filename*=UTF-8''" + URLEncoder.encode(filename, "UTF-8"));
OutputStream outputStream = response.getOutputStream();
getColumnTopStyle(workbook);
bufferedOutPut = new BufferedOutputStream(outputStream);
workbook.write(bufferedOutPut);
bufferedOutPut.flush();
} finally {
if (bufferedOutPut != null)
bufferedOutPut.close();
if(workbook != null)
workbook.close();
}
}
下面是給excel設定表格+背景顔色
/*
* 列資料資訊單元格樣式
*/
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 設定字型
HSSFFont font = workbook.createFont();
//設定字型大小
font.setFontHeightInPoints((short)12);
//字型加粗
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//設定字型名字
font.setFontName("宋體");
//設定樣式;
HSSFCellStyle style = workbook.createCellStyle();
//設定背景顔色;
style.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
//solid 填充 foreground 前景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//設定底邊框;
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);
return style;
}
/*
* 列頭單元格樣式
*/
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 設定字型
HSSFFont font = workbook.createFont();
//設定字型大小
font.setFontHeightInPoints((short)14);
//字型加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//設定字型名字
font.setFontName("黑體");
//設定樣式;
HSSFCellStyle style = workbook.createCellStyle();
//設定背景顔色;
style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
//solid 填充 foreground 前景色
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//設定底邊框;
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);
return style;
}
}
以下就是效果圖，大家可以根據自己喜歡的顔色進行調整。
如果大家不知道有哪些顔色
請參考:https://blog.csdn.net/qq_41234832/article/details/103061884
如果此文章對您有幫助，拜托留個贊再走哦~