如何 读取 如何 存储
poi 操作 office
炳汉的独白
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
创建工作簿对象
从工作簿对象中获取 sheet 表 -- 表对象
从表中获取行 -- 行对象
从行中获取单元格 -- 单元格对象
从单元格中获取值 -- 单元格对象
//读取Excel文档内容 -- 导入
@Test
public void testRead()throws Exception{
//获取工作簿 -- 创建工作簿对象
//Workbook workbook = new XSSFWorkbook("C:\\Users\\Administrator\\Desktop\\工作表.xlsx");//针对xlsx格式文件
Workbook workbook = new HSSFWorkbook(new FileInputStream("C:\\Users\\Administrator\\Desktop\\工作表.xls"));//针对xlsx格式文件
//从工作簿对象中获取sheet表 -- 表对象
Iterator<Sheet> iterator = workbook.iterator();
while(iterator.hasNext()){
Sheet sheet = iterator.next();
//从表中获取行 -- 行对象
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
for(int i = firstRowNum;i <= lastRowNum;i++){
System.out.println("表内容：");
//从行中获取单元格 -- 单元格对象
Row row = sheet.getRow(i);
if(row == null){
continue;
}
//从单元格中获取值 -- 单元格对象
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
for(int j = firstCellNum;j <= lastCellNum;j++){
System.out.println("行内容：");
Cell cell = row.getCell(j);
if(cell == null)continue;
String value = cell.getStringCellValue();
System.out.println(value);
System.out.println("------------");
}
System.out.println("================");
}
}
}
工作簿对象 -- 对应一个未来的工作簿
创建 sheet 表对象 -- 对应一个未来的 sheet 表
创建行对象 -- 对应一个未来的行 -- 循环
创建单元格对象 -- 对应未来的单元格 -- 循环
给单元格设置内容
把内存内容写出 -- 写出到服务器本地、写出到用户端远程
释放资源 必须释放
//写出Excel数据 -- 导出
@Test
public void testWrite()throws Exception{
//工作簿对象 -- 对应一个未来的工作簿
//Workbook workbook = new XSSFWorkbook();
Workbook workbook = new HSSFWorkbook();
//创建sheet表对象 -- 对应一个未来的sheet表
Sheet sheet = workbook.createSheet("s1");
//创建行对象 -- 对应一个未来的行 -- 循环
Row row0 = sheet.createRow(0);
//创建单元格对象 -- 对应未来的单元格 -- 循环
Cell cell0 = row0.createCell(0);
//给单元格设置内容
cell0.setCellValue("opq");
//把内存内容写出 -- 写出到服务器本地、写出到用户端远程
//写出到本地
OutputStream os = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\1.xls");
workbook.write(os);
//释放资源
os.flush();
os.close();
workbook.close();
}
保证安全 防止不走前端 后端 验证
判断文件类型 endsWith
工具类 POIUtils
判断当前日期对应的数据是否存在
存在修改，不存在添加
LocalDate.parse 字符串日期转换
参数 DateTimeFormatter.ofPattern
Html
//上传成功提示
handleSuccess(response, file) {
if(response.flag){
this.$message({
message: response.message,
type: 'success'
});
}else{
this.$message.error(response.message);
}
}
Controller
//上传Excel文档
@PostMapping("uploadTempleate")
public Result uploadTempleate(MultipartFile excelFile){
try {
List<String[]> list = POIUtils.readExcel(excelFile);
tOrdersettingService.save(list);
return new Result(true, MessageConstant.UPLOAD_SUCCESS,null);
}catch (Exception e){
e.printStackTrace();
return new Result(false, MessageConstant.UPLOAD_FAIL + ":" + e.getMessage(),null);
}
}
ServiceImpl
@Transactional
@Override
public void save(List<String[]> list) {
for(String[] strs : list){
String dateStr = strs[0];
String numStr = strs[1];
//判断当前日期的数据是否已经存在，如果存在则修改，如果不存在则添加
QueryWrapper<TOrdersetting>wrapper = new QueryWrapper<>();
wrapper.eq("orderDate",dateStr);
TOrdersetting one = tOrdersettingMapper.selectOne(wrapper);
if(one != null){
//可以覆盖旧值，可以追加值
one.setNumber(Integer.parseInt(numStr));
tOrdersettingMapper.updateById(one);
}else {
TOrdersetting ordersetting = new TOrdersetting();
ordersetting.setNumber(Integer.parseInt(numStr));
ordersetting.setOrderDate(LocalDate.parse(dateStr, DateTimeFormatter.ofPattern("yyyy/MM/dd")));
tOrdersettingMapper.insert(ordersetting);
}
}
}
先有一个文件模板 样式弄好，导出时候填写新的值即可（比在程序中好操作）
Html
exportExcel(){
window.location.href = '/report/exportBusinessReport';
}
Controller
//导出Excel操作 -- 使用现成Excel表格文件
@GetMapping("exportBusinessReport")
public void exportBusinessReport(HttpServletResponse response){
try {
//准备数据
Map<String, Object> map = reportService.getBusinessReportData();
//获取模板文件
//String path = "E:\\src\\main\\resources\\static\\template\\report_template.xlsx";
URL url = this.getClass().getResource("/static/template/report_template.xlsx");
String path = url.getPath();
path = path.replaceAll("+", " ");
Workbook workbook = new XSSFWorkbook(path);
//对应单元格填充相关的值
Sheet sheet = workbook.getSheet("sheet1");
Row row2 = sheet.getRow(2);
Cell reportDate = row2.getCell(5);
reportDate.setCellValue((String) map.get("reportDate"));
////////////////////////////
Row row4 = sheet.getRow(4);
Cell todayNewMember = row4.getCell(5);
todayNewMember.setCellValue((Integer) map.get("todayNewMember"));
Cell totalMember = row4.getCell(7);
totalMember.setCellValue((Integer) map.get("totalMember"));
//////////////////
Row row5 = sheet.getRow(5);
Cell thisWeekNewMember = row5.getCell(5);
thisWeekNewMember.setCellValue((Integer) map.get("thisWeekNewMember"));
Cell thisMonthNewMember = row5.getCell(7);
thisMonthNewMember.setCellValue((Integer) map.get("thisMonthNewMember"));
//////////
Row row7 = sheet.getRow(7);
Cell todayOrderNumber = row7.getCell(5);
todayOrderNumber.setCellValue((Integer) map.get("todayOrderNumber"));
Cell todayVisitsNumber = row7.getCell(7);
todayVisitsNumber.setCellValue((Integer) map.get("todayVisitsNumber"));
Row row8 = sheet.getRow(8);
Cell thisWeekOrderNumber = row8.getCell(5);
thisWeekOrderNumber.setCellValue((Integer) map.get("thisWeekOrderNumber"));
Cell thisWeekVisitsNumber = row8.getCell(7);
thisWeekVisitsNumber.setCellValue((Integer) map.get("thisWeekVisitsNumber"));
Row row9 = sheet.getRow(9);
Cell thisMonthOrderNumber = row9.getCell(5);
thisMonthOrderNumber.setCellValue((Integer) map.get("thisMonthOrderNumber"));
Cell thisMonthVisitsNumber = row9.getCell(7);
thisMonthVisitsNumber.setCellValue((Integer) map.get("thisMonthVisitsNumber"));
//////
int i = 12;
List<Map<String, Object>> hotSetmeal = (List<Map<String, Object>>) map.get("hotSetmeal");
for(Map<String,Object>hot : hotSetmeal){
Row rowi = sheet.getRow(i);
Cell name = rowi.getCell(4);
name.setCellValue((String) hot.get("name"));
Cell count = rowi.getCell(5);
count.setCellValue((Long) hot.get("setmeal_count"));
Cell pro = rowi.getCell(6);
pro.setCellValue(((BigDecimal) hot.get("proportion")).toString());
i++;
}
//把文件传输/下载到用户端
OutputStream os = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");//代表的是Excel文件类型
response.setHeader("content-Disposition", "attachment;filename=report.xlsx");//指定以附件形式进行下载
workbook.write(os);
//释放资源
os.flush();
os.close();
workbook.close();
}catch (Exception e){
e.printStackTrace();
}
}