spring boot:使用poi导出excel电子表格文件(spring boot 2.3.1)

http://poi.apache.org/

https://github.com/liuhongdi/exportexcel

<!--引入poi begin-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency> <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!--poi end--> <!--mybatis begin-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<!--mybatis end--> <!--mysql begin-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--mysql end-->

#mysql
spring.datasource.url=jdbc:mysql://localhost:3306/store?characterEncoding=utf8&useSSL=false
spring.datasource.username=root
spring.datasource.password=lhddemo
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver#mybatis
mybatis.mapper-locations=classpath:/mapper/*Mapper.xml
mybatis.type-aliases-package=com.example.demo.mapper
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

CREATE TABLE `goods` (
`goodsId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`goodsName` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT 'name',
`subject` varchar(200) NOT NULL DEFAULT '' COMMENT '标题',
`price` decimal(15,2) NOT NULL DEFAULT '0.00' COMMENT '价格',
`stock` int(11) NOT NULL DEFAULT '0' COMMENT 'stock',
PRIMARY KEY (`goodsId`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品表'

public class ExcelUtil { //save excel to a file
public static void saveExcelFile( HSSFWorkbook wb, String filepath){ File file = new File(filepath);
if (file.exists()) {
file.delete();
}
try {
wb.write(new FileOutputStream(file));
} catch (Exception e) {
e.printStackTrace();
}
} //download a execl file
public static void downExecelFile(HSSFWorkbook wb,String filename) {
ServletRequestAttributes servletRequestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = servletRequestAttributes.getResponse();
try {
// 输出Excel文件
OutputStream output = response.getOutputStream();
response.reset();
//设置文件头
response.setHeader("Content-Disposition",
"attchement;filename=" + new String(filename.getBytes("utf-8"),
"ISO8859-1"));
response.setContentType("application/msexcel");
wb.write(output);
wb.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}

@RestController
@RequestMapping("/home")
public class HomeController { @Resource
private GoodsMapper goodsMapper; @GetMapping("/exportexcel")
public void exportExcel() { HSSFWorkbook wb = new HSSFWorkbook();
// 根据页面index 获取sheet页
HSSFSheet sheet = wb.createSheet("商品基本信息");
createTitle(wb,sheet); //设置列宽度
List<Goods> goodsList = goodsMapper.selectAllGoods();
//表内容从第二行开始
int i=1;
for (Goods goodsOne : goodsList) {
HSSFRow row = sheet.createRow(i + 1);
//创建HSSFCell对象 设置单元格的值
row.createCell(0).setCellValue(goodsOne.getGoodsId());
row.createCell(1).setCellValue(goodsOne.getGoodsName());
row.createCell(2).setCellValue(goodsOne.getSubject());
row.createCell(3).setCellValue(goodsOne.getStock());
i++;
} //保存成文件
ExcelUtil.saveExcelFile(wb,"/data/file/html/商品信息.xls");
//下载文件
ExcelUtil.downExecelFile(wb,"商品信息.xls");
} //创建标题和表头
private void createTitle(HSSFWorkbook workbook,HSSFSheet sheet){
//用CellRangeAddress合并单元格，在这里作为sheet中的标题
//参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, 3);
sheet.addMergedRegion(region1); HSSFRow row_title = sheet.createRow(0);
row_title.setHeightInPoints(26);
HSSFCell cell_title;
cell_title = row_title.createCell(0);
cell_title.setCellValue("商品信息表"); //设置标题样式:居中加粗
HSSFCellStyle style_title = workbook.createCellStyle();
HSSFFont font_title = workbook.createFont();
font_title.setBold(true);
font_title.setFontHeightInPoints((short) 20);
font_title.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
style_title.setFont(font_title);
style_title.setAlignment(HorizontalAlignment.CENTER);
style_title.setVerticalAlignment(VerticalAlignment.CENTER);
cell_title.setCellStyle(style_title); //以下为表头
HSSFRow row = sheet.createRow(1);
//设置行高
row.setHeightInPoints(18);
//设置列宽度
sheet.setColumnWidth(0,10*256);
sheet.setColumnWidth(1,30*256);
sheet.setColumnWidth(2,30*256);
sheet.setColumnWidth(3,10*256);
//设置样式:居中加粗
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBold(true);
font.setColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());
style.setFont(font);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//添加4个字段
HSSFCell cell;
cell = row.createCell(0);
cell.setCellValue("id");
cell.setCellStyle(style); cell = row.createCell(1);
cell.setCellValue("商品名称");
cell.setCellStyle(style); cell = row.createCell(2);
cell.setCellValue("商品描述");
cell.setCellStyle(style); cell = row.createCell(3);
cell.setCellValue("库存数量");
cell.setCellStyle(style);
}
}

http://127.0.0.1:8080/home/exportexcel

. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.3.1.RELEASE)