@ApiOperation(value = "导入模板下载" ,notes = "导入模板下载")
@GetResource(name = "导入模板下载", path = "/excelTemplate")
public void excelTemplate(HttpServletRequest request,HttpServletResponse response){
try {
response.setContentType("application/binary;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("采样点导入模板"+".xls", "UTF-8"));
hssfWorkbookUtil.getSamplingPointHSSF(request,response);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.function.Function;
import java.util.stream.Collectors;
@Component
public class HSSFWorkbookUtil {
@Resource
DictService dictService;
@Resource
DictTypeService dictTypeService;
public void getSamplingPointHSSF(HttpServletRequest request, HttpServletResponse response) throws IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
setDate(workbook);
setDict(workbook);
OutputStream out = response.getOutputStream();
workbook.write(out);
out.flush();
out.close();
}
private void setDate(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFSheet dateSheet = workbook.createSheet(SAMPLING_POINT_SHEET_NAME);
String[] title = {"编号", "区域","环境类型", "频率", "采样点名称", "规则"};
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 1, 0, title.length - 1);
dateSheet.addMergedRegion(cellRangeAddress);
HSSFCell cell = dateSheet.createRow(0).createCell(0);
cell.setCellStyle(style);
cell.setCellValue("采样点导入");
HSSFCellStyle titleStyle = creatStyle(workbook);
HSSFRow row = dateSheet.createRow(2);
for (int i = 0; i < title.length; i++) {
dateSheet.setColumnWidth(i, title[i].getBytes(StandardCharsets.UTF_8).length * 256);
HSSFCell titleCell = row.createCell(i);
titleCell.setCellStyle(titleStyle);
titleCell.setCellValue(title[i]);
}
}
private void setDict(HSSFWorkbook workbook) {
String[] titleCode = {"environment" ,"area" ,"frequency"};
Map<String, SysDictType> dictTypeMap =
dictTypeService.list(new QueryWrapper<SysDictType>().lambda().in(SysDictType::getDictTypeCode, Arrays.asList(titleCode)))
.stream()
.collect(Collectors.toMap(SysDictType::getDictTypeCode, Function.identity()));
HSSFCellStyle titleStyle = creatStyle(workbook);
HSSFSheet dictSheet = workbook.createSheet("数据字典");
HSSFRow row = dictSheet.createRow(0);
AtomicInteger index = new AtomicInteger(0);
AtomicInteger maxLine = new AtomicInteger(1);
dictTypeMap.forEach((k,v)->{
HSSFCell cell = row.createCell(index.getAndIncrement());
cell.setCellStyle(titleStyle);
cell.setCellValue(v.getDictTypeName());
DictRequest dictRequest = new DictRequest();
dictRequest.setDictTypeCode(k);
List<SysDict> dictList = dictService.findList(dictRequest);
for (int i = 0; i < dictList.size(); i++) {
HSSFRow row1 = null;
if(i+1 >= maxLine.get()){
row1 = dictSheet.createRow(maxLine.getAndIncrement());
}else{
row1 =dictSheet.getRow(i+1);
}
row1.createCell(index.get() - 1).setCellValue(dictList.get(i).getDictName());
}
});
}
private HSSFCellStyle creatStyle(HSSFWorkbook workbook){
HSSFCellStyle titleStyle = workbook.createCellStyle();
titleStyle.setAlignment(HorizontalAlignment.CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("微软雅黑");
font.setBold(true);
font.setFontHeightInPoints((short) 11);
titleStyle.setFont(font);
return titleStyle;
}
}