excel & work

目录

一、EXCEL

1、获取excel

2、导出excel

3、单元格样式

4、合并单元格

二、word

1、模板导出数据

2、导出图片

---

一、EXCEL

1、获取excel

//1、有模板
String url = "templates/summaryTable.xlsx";//路径
Resource resource = new ClassPathResource(url);
XSSFWorkbook workbook = new XSSFWorkbook(resource.getInputStream());
XSSFSheet sheet = workbook.getSheetAt(0);
//2、直接创建
Workbook wb = new XSSFWorkbook();  // or new XSSFWorkbook();
//创建第一个sheet（页）
Sheet sheet1 = wb.createSheet("sheet");

2、导出excel

//Controller

@PostMapping("analysisExport")
public ModelAndView analysisExport(HttpServletResponse response) {
    ModelAndView downloadMv = new ModelAndView();
    downloadMv.setView(new DownloadView());
    downloadMv.addObject(DownloadView.EXPORT_FILE, (InputStream) ExportIndividual());
    downloadMv.addObject(DownloadView.EXPORT_FILE_NAME, "excelName");
    downloadMv.addObject(DownloadView.EXPORT_FILE_TYPE, DownloadView.FILE_TYPE.XLSX);
    response.setHeader("fileName", java.net.URLEncoder.encode("excelName" + ".xlsx", "utf-8"));
    return downloadMv;
}

//Service

public InputStream ExportIndividual() throws IOException {
    Workbook wb = new XSSFWorkbook();
    //数据处理
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    wb.write(os);
    os.flush();
    os.close();
    return new ByteArrayInputStream(os.toByteArray());

}

3、单元格样式

public CellStyle createCellStyle(Workbook workbook, short fontsize, boolean Bold, boolean AlignmentCenter, Boolean AlignmentRight) {
        // TODO Auto-generated method stub
        CellStyle style = workbook.createCellStyle();
        //自动换行，配合\n使用
         cellCenterStyle.setWrapText(true);
        //是否水平居中
        if (AlignmentCenter) {
            style.setAlignment(HorizontalAlignment.CENTER);
        }
        if (AlignmentRight) {
            style.setAlignment(HorizontalAlignment.RIGHT);
        }
        if (!AlignmentCenter && !AlignmentRight) {
            style.setAlignment(HorizontalAlignment.LEFT);
        }
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 设置垂直居中
        //创建字体
        Font font = workbook.createFont();
        //是否加粗字体
        if (Bold) {
            font.setBold(true);
        }
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        //内容设置
        CellStyle cellNubStyle = wb.createCellStyle();
        cellNubStyle.setDataFormat(createHelper.createDataFormat().getFormat("#,##0.00"));//股数 
        cellDateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd")); //日期
        cellNubStyle.setDataFormat(createHelper.createDataFormat().getFormat("#,##0.00"));//百分比 
        return style;
    }

4、合并单元格

//起始行,结束行,起始列,结束列
CellRangeAddress callAddress = new CellRangeAddress(index, index, 0, 5);
sheet1.addMergedRegion(callAddress);

二、word

1、模板导出数据

（1）准备模板 -》语法：jinjia2【感觉“##”也不是有什么特殊的意义，可不可以在里面使用三元运算呢？】

a. «${d['#name#']!}»

b. 循环

«@before-row[#list schoolList as school]»

«${school['#reportNumber#']!}»

«@after-row[/#list][/»

（2）代码处理

public void generateFile(Map<String,Object> map) throws IOException, TemplateException {
        Map<String,Object> result = new HashMap<>();
        OutputStream out = null;
        InputStream in = null;
        //命名方式：独董主键+（保存or注册）
        String docxPath = "templates/resumePDF.docx";
        //文件路径
        String temFilePath = fileBasePath+"temp";
        try{
            Resource resource = new ClassPathResource(docxPath);
            in = resource.getInputStream();
            IXDocReport report = XDocReportRegistry.getRegistry().loadReport(in, TemplateEngineKind.Freemarker);
            IContext context = report.createContext();
            context.put("d", map);
            context.put("schoolList", map.get("#schoolList#"));
            //判断文件路径是否存在
            out = new FileOutputStream(temFilePath + "/fileUUID.docx");
            report.process(context, out);
            in.close();
            out.close();
        }catch(Exception e){
            logger.info("produceXdocReportWord error by catch {{{{{===="+e.getMessage());
        }finally{
            try {
                if(in != null){
                    in.close();
                }
                if(out != null){
                    out.close();
                }
            } catch (IOException e) {
                logger.info("produceXdocReportWord error by finally {{{{{===="+e.getMessage());
            }
        }
        //删除生成的文件
        fileServer.delete("/temp/fileUUID.docx");
    }

2、导出图片

<dependency>
    <groupId>com.deepoove</groupId>
    <artifactId>poi-tl</artifactId>
    <version>1.10.0</version>
</dependency>

 Map<String, Object> data = new HashMap<String, Object>();
            data.put("photo", new PictureRenderData(127, 185, fileBasePath+userInfoDto.getPhotoAddress()));
            XWPFTemplate xwpfTemplate = XWPFTemplate.compile(fileBasePath+""+"/temp/" + fileUUID+".docx")
                    .render(data);
            File targetFile = new File(fileBasePath+""+"/temp/" + fileUUID+"Phone.docx");
            FileOutputStream fileOut = new FileOutputStream(targetFile);
            xwpfTemplate.write(fileOut);
            fileOut.flush();
            fileOut.close();
            xwpfTemplate.close();