package com.miyo.controller;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Font;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.*;
/**
* @author xiaoli.he
* @date 2022/5/20
*/
@Controller
public class TemplateController {
/**
* 根据模板修改下载对应的Excel
*
* @param response excel
* @param templateCode code
* @param includeFields 选中的字段
* @throws Exception null
*/
@SuppressWarnings("resource")
@RequestMapping("/download")
@ResponseBody
public void download(
HttpServletResponse response, String templateCode, @RequestBody List<String> includeFields)
throws Exception {
// 测试
int length = templateCode.length();
System.out.println(length);
// 判断得到那些值
// 模板文件放在resources中的download包中
String filePath =
Objects.requireNonNull(TemplateController.class.getClassLoader().getResource("download"))
.getPath()
+ "/template.xls";
FileInputStream fileInputStream = new FileInputStream(filePath);
BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workbook.getSheet("Sheet1");
// 得到第一行
HSSFRow row = sheet.getRow(0);
// 得到最后一列
short lastCellNum = row.getLastCellNum();
// 判断那些列需要采用
for (int j = 0; j < lastCellNum; j++) {
String cellValue = row.getCell(j).getStringCellValue();
HSSFCell cell = row.getCell(j);
// 没有则从模板中移除
if (!includeFields.contains(cellValue)) {
row.removeCell(cell);
}
}
bufferedInputStream.close();
// 输出Excel文件
OutputStream outputStream = response.getOutputStream();
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=template.xls");
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
/**
* 解析Excel
*
* @param file file
* @throws Exception null
*/
@SuppressWarnings("resource")
@RequestMapping("/parse")
@ResponseBody
public void parse(@RequestParam("file") MultipartFile file) throws Exception {
InputStream inputStream = file.getInputStream();
BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet = workbook.getSheetAt(0);
// 得到行数
int lastRowNum = sheet.getLastRowNum();
// 解析并且封装到一个list
List<Map<String, String>> listMaps = new ArrayList<>();
// 得到第一行作为表头
HSSFRow row1 = sheet.getRow(0);
int lastCellNum = row1.getLastCellNum();
String[] arr = new String[lastCellNum];
// 存入数组中
for (int t = 0; t < lastCellNum; t++) {
HSSFCell cell = row1.getCell(t);
String cellValue = cell.getStringCellValue();
arr[t] = cellValue;
}
for (int i = 1; i <= lastRowNum; i++) {
// 每一行数据对应一个map
Map<String, String> map = new HashMap<>(16);
HSSFRow row = sheet.getRow(i);
// 得到有几列
int num = row.getLastCellNum();
// 遍历每一行的单元格
for (int j = 0; j < num; j++) {
HSSFCell cell = row.getCell(j);
// 设置类型为String
cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
String cellValue = cell.getStringCellValue();
// 把表头和里面的值对应
map.put(arr[j], cellValue);
}
listMaps.add(map);
}
System.out.println(listMaps);
}
/**
* 生成Excel
*
* @param response Excel
* @throws Exception null
*/
@SuppressWarnings("resource")
@RequestMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) throws Exception {
// 创建一个excel的文档对象
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建excel的表单
HSSFSheet sheet = workbook.createSheet("Sheet1");
// cell样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
// 设置水平和垂直居中
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个字体
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 9);
// 设置字体的颜色
font.setColor(HSSFColor.BLUE.index);
// 设置字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
// 创建一个行
HSSFRow row = sheet.createRow(0);
// 设计表头
String[] tableHeaders = {
"公司", "毛利率", "净现比", "管理费用率", "销售费用率", "预付账款周转率", "应收账款周转率", "应付账款周转率", "其他应收款占总资产比", "其他应付应收比"
};
// 创建表头
for (int i = 0; i < tableHeaders.length; i++) {
// 如果用了添加表头
// 创建单元格并设置单元格内容
HSSFCell cell = row.createCell(i);
// 表头数组
cell.setCellValue(tableHeaders[i]);
// 赋予格式
cell.setCellStyle(cellStyle);
}
// 输出Excel文件
OutputStream outputStream = response.getOutputStream();
response.reset();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=template.xls");
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
}
}
