导入导出1

已于 2022-11-30 09:22:33 修改
阅读量229 收藏
点赞数
文章标签： excel
于 2020-07-28 16:15:19 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_26272613/article/details/107627824
版权

导入1

  1. 导入依赖
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
</dependency>

或者

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.2.0</version>
</dependency>

controller

@ApiOperation(value = "工地分包情况月报表-导入", tags = {SwaggerTagConstants.ADMIN})
    @POST
    @Path("import")
    @Consumes({"multipart/form-data"})
    @Authorization(permission = "siteSubcontract:add,siteSubcontract:update")
    public WebApiResponse importSysDepart(@Context SecurityContext context, @FormDataParam("file") InputStream inputStream
            ,@QueryParam("filetype")String filetype,@QueryParam("districtname")String districtname) {
        Map<String, String> map = siteSubcontractService.importExcel(inputStream, 1l, filetype, districtname);
        if (map!=null&&map.get("code").equals("400")){
            return new WebApiResponse(400, map.get("msg"), null);
        }
         return done();
    }
  1. 手动导入方法实现
@Override
    @Transactional
    public synchronized Map<String,String> importExcel(InputStream inputStream,Long userid,String filetype,String districtname){
        Map<String,String> msgMap = new HashMap<>();
        msgMap.put("code","0");
        try {
            Workbook wb;
            if ("xls".equals(filetype)){
                wb = new HSSFWorkbook(inputStream);
            }else if ("xlsx".equals(filetype)){
                wb = new XSSFWorkbook(inputStream);
            }else {
                wb = new XSSFWorkbook(inputStream);
            }
            //开始解析
            Sheet sheet = wb.getSheetAt(0);
            //获取标题行
            Row title = sheet.getRow(1);
            String titleStr = title.getCell(0).toString();
            int flag = 0;
            for (int i = 0;i < titleStr.length();i++){
                if(String.valueOf(titleStr.charAt(i)).equals("区")||String.valueOf(titleStr.charAt(i)).equals("市")
                        ||String.valueOf(titleStr.charAt(i)).equals("县")){
                    flag = i+1;
                }
            }
            //获取区名
            String area = titleStr.substring(0, flag);
            //判断截取的区名是否存在数据库中
            SysDepart byDepartNameSub = sysDepartRepository.findByDepartName(area);
            if(byDepartNameSub == null||byDepartNameSub.equals("")){
                msgMap.put("code","400");
                msgMap.put("msg","请输入正确的区域名称");
                return msgMap;
            }
            if (!districtname.equals(area)){
                msgMap.put("code","400");
                msgMap.put("msg","所传文件区域与所选区域不符,请重新选择上传文件");
                return msgMap;
            }
            if (area.equals("")||area==null){
                msgMap.put("code","400");
                msgMap.put("msg","表格标题格式错误,请以XXX区(市/县)开头");
                return msgMap;
            }
            int firstRowIndex = sheet.getFirstRowNum() + 5;
            int lastRowIndex = sheet.getLastRowNum();
            List<SiteSubcontractDto> dtos = new LinkedList<>();
            String pinyinszm = Arith.getPinyinszm(byDepartNameSub.getDepartName());
            Map<String, String> getqynum = this.getqynum(pinyinszm);
            int sz = 0;
            if(getqynum.size()>0&&!StringUtils.isEmpty(getqynum.get("sz"))){
                sz = Integer.valueOf(getqynum.get("sz"));
            }
            for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
                sz = sz + 1;
                SiteSubcontractDto dto = new SiteSubcontractDto();
                dto.setOrgcode(byDepartNameSub.getOrgCode());
                dto.setDepartid(byDepartNameSub.getId());
                dto.setArea(byDepartNameSub.getDepartName());
                String qynum = Arith.getqynum(pinyinszm,sz);
                dto.setSitenum(qynum);
                dto.setNum(String.valueOf(sz));
                Row row = sheet.getRow(rIndex);
                String validobj = validobj(row.getCell(0));
                if("合计".equals(validobj)){
                    break;
                }else{
                    int setvalue = setvalue(dto, rIndex, sheet);
                    dtos.add(dto);
                    rIndex = setvalue;
                }

            }
                if(dtos.size()>0){
                    this.saveAll(dtos);
                }



        } catch (IOException e) {
            e.printStackTrace();
        }
        return msgMap;
    }

导出

  1. 依赖
com.alibaba easyexcel 2.1.4 

 2. 导出controller方法

@ApiOperation(value = "工地分包情况月报表-导出", tags = {SwaggerTagConstants.ADMIN})
    @POST
    @Path("export")
    @Authorization(permission = "siteSubcontract:list")
    public void exportSiteSubcontractList(@Context SecurityContext context, @Context HttpServletResponse response, Query query
            ,@QueryParam("districtname")String districtname) {
        if(StringUtils.isEmpty(districtname)){
            districtname = "中原区";
        }
         String title = "工地分包情况月报表信息表";
        List<SiteSubcontractDto> siteSubcontractList = siteSubcontractService.getSiteSubcontractList(query);
        LinkedList<SiteSubcontractDto> linklists = new LinkedList<>();
        for(int j=0;j<siteSubcontractList.size();j++){
            SiteSubcontractDto obj = siteSubcontractList.get(j);
            String[] unitsobjects = obj.getUnits().split(",");
            String[] namesobjects = obj.getNames().split(",");
            String[] phonenumbersobjects = obj.getPhonenumbers().split(",");
            String[] positionsobjects = obj.getPositions().split(",");
            for(int i=0;i<4;i++){
                SiteSubcontractDto obj1 = setvalue(obj, unitsobjects, namesobjects, phonenumbersobjects, positionsobjects, i);
                obj.setOrdernum(String.valueOf(j));
                linklists.add(obj1);
            }
        };
        try {
            this.exportExcel(response,linklists,districtname,siteSubcontractList);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public SiteSubcontractDto setvalue(SiteSubcontractDto obj,String[] utils,String[] names,String[] phonenum,String[] positions,int i){
        SiteSubcontractDto clone = null;
        try {
            clone = (SiteSubcontractDto)obj.clone();
            try {
                clone.setUnits(validobj(utils[i]));
            }catch (Exception e){
                clone.setUnits("");
            }
            try {
                clone.setNames(validobj(names[i]));
            }catch (Exception e){
                clone.setNames("");
            }
            try {
                clone.setPhonenumbers(validobj(phonenum[i]));
            }catch (Exception e){
                clone.setPhonenumbers("");
            }
            try {
                clone.setPositions(validobj(positions[i]));
            }catch (Exception e){
                clone.setPositions("");
            }
        } catch (CloneNotSupportedException e) {
            e.printStackTrace();
        }

            return clone;
    }

    //根据单元格数据类型返回
    public String validobj(Object obj){
        if(!StringUtils.isEmpty(obj)){
            return String.valueOf(obj);
        }
        return "";
    }
    private WebApiResponse exportExcel(@Context HttpServletResponse response, List<SiteSubcontractDto> pilebodycheckMonthDtoList,String departName,List<SiteSubcontractDto> lists) throws IOException {
//        for (int i = 1; i <= pilebodycheckMonthDtoList.size(); i++) {
//            pilebodycheckMonthDtoList.get(i - 1).setOrdernum(i + "");
//        }
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(departName+"建筑垃圾排放工地四级责任分包台账统计表", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        //内容样式策略
        WriteCellStyle contentWriteCellStyle = ExcelContentStyle.getWriteCellStyle();
        //头策略使用默认
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(contentWriteFont);
        headWriteCellStyle.setFillBackgroundColor((short) 0);
        EasyExcel.write(response.getOutputStream(), SiteSubcontractDto.class)
                .excelType(ExcelTypeEnum.XLS).head(SiteSubcontractDto.class)
                .registerWriteHandler(new SiteSheetWriteHandler())
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
                .sheet(departName+"建筑垃圾排放工地四级责任分包台账统计表")
                .useDefaultStyle(true).relativeHeadRowIndex(3)
                .doWrite(siteSubcontractService.addSumColomn(pilebodycheckMonthDtoList,lists));
        return new WebApiResponse(200, "生成excel文件成功", null);
    }

拦截器

package com.jpxx.admin.sitesubcontract.web.api;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.WorkbookWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.jpxx.admin.sitesubcontract.service.api.dto.SiteSubcontractDto;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.util.LinkedList;
import java.util.List;

public class SiteSheetWriteHandler implements WorkbookWriteHandler {
    @Override
    public void beforeWorkbookCreate() {

    }

    @Override
    public void afterWorkbookCreate(WriteWorkbookHolder writeWorkbookHolder) {

    }

    @Override
    public void afterWorkbookDispose(WriteWorkbookHolder writeWorkbookHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        String sheetName = sheet.getSheetName();
        String departName = sheetName.replace("建筑垃圾排放工地四级责任分包台账统计表", "");
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) 500);
        Cell cell = row1.createCell(0);
        //设置单元格内容
        cell.setCellValue("附件2");
        //设置标题
        Row row2 = sheet.createRow(1);
        row2.setHeight((short) 800);
        Cell cell1 = row2.createCell(0);
        cell1.setCellValue(departName+"（建筑垃圾排放工地四级责任分包台账统计表");
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 30);
        font.setFontHeight((short) 200);
        cellStyle.setFont(font);
        cell1.setCellStyle(cellStyle);
        sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 20));
        //设置填表日期,填报人,联系方式
        Row row3 = sheet.createRow(2);
        row3.setHeight((short) 500);
        row3.createCell(1).setCellValue("填表日期");
        row3.createCell(12).setCellValue("填表人");
        row3.createCell(18).setCellValue("联系方式");

        int firstRowIndex = sheet.getFirstRowNum() + 5;
        int lastRowIndex = sheet.getLastRowNum();
        for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
            Row row = sheet.getRow(rIndex);
            String validobj = validobj(row.getCell(0));
            if("合计".equals(validobj)){
                break;
            }else{
                for(int i=0;i<=18;i++){
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(rIndex, rIndex+3, i, i));
                }


                rIndex = rIndex+3;
            }

        }
    }

    //根据单元格数据类型返回
    public String validobj(Object obj){
        if(!StringUtils.isEmpty(obj)){
            return String.valueOf(obj);
        }
        return "";
    }
}

添加合计

@Override
    public List<SiteSubcontractDto> addSumColomn(List<SiteSubcontractDto> lists,List<SiteSubcontractDto> sitelists) {
        double totalgarbage = 0;
        double rubbishcurrentmonthgovernance = 0;
        double rubbishgrandtotalgovernance = 0;
        double rubbishdirectutilization = 0;
        double rubbishrecyclingamount = 0;
        double totalspoil = 0;
        double spoilcurrentmonthgovernance = 0;
        double spoilgrandtotalgovernance = 0;
        SiteSubcontractDto multiLineHeadExcelModel = new SiteSubcontractDto();
        for (int i = 0;i < sitelists.size();i++){
            if (!StringUtils.isEmpty(sitelists.get(i).getTotalgarbage())){
                totalgarbage += Double.valueOf(sitelists.get(i).getTotalgarbage());
            }
            if (!StringUtils.isEmpty(sitelists.get(i).getRubbishcurrentmonthgovernance())){
                rubbishcurrentmonthgovernance += Double.valueOf(sitelists.get(i).getRubbishcurrentmonthgovernance());
            }
            if (!StringUtils.isEmpty(sitelists.get(i).getRubbishgrandtotalgovernance())){
                rubbishgrandtotalgovernance += Double.valueOf(sitelists.get(i).getRubbishgrandtotalgovernance());
            }
            if (!StringUtils.isEmpty(sitelists.get(i).getRubbishdirectutilization())){
                rubbishdirectutilization += Double.valueOf(sitelists.get(i).getRubbishdirectutilization());
            }
            if (!StringUtils.isEmpty(sitelists.get(i).getRubbishrecyclingamount())){
                rubbishrecyclingamount += Double.valueOf(sitelists.get(i).getRubbishrecyclingamount());
            }
            if (!StringUtils.isEmpty(sitelists.get(i).getTotalspoil())){
                totalspoil += Double.valueOf(sitelists.get(i).getTotalspoil());
            }
            if (!StringUtils.isEmpty(sitelists.get(i).getSpoilcurrentmonthgovernance())){
                spoilcurrentmonthgovernance += Double.valueOf(sitelists.get(i).getSpoilcurrentmonthgovernance());
            }
            if (!StringUtils.isEmpty(sitelists.get(i).getSpoilgrandtotalgovernance())){
                spoilgrandtotalgovernance += Double.valueOf(sitelists.get(i).getSpoilgrandtotalgovernance());
            }
            multiLineHeadExcelModel.setOrdernum("合计").setTotalgarbage(totalgarbage).setRubbishcurrentmonthgovernance(rubbishcurrentmonthgovernance)
                    .setRubbishgrandtotalgovernance(rubbishgrandtotalgovernance).setRubbishdirectutilization(rubbishdirectutilization).setRubbishrecyclingamount(rubbishrecyclingamount).setTotalspoil(totalspoil)
                    .setSpoilcurrentmonthgovernance(spoilcurrentmonthgovernance).setSpoilgrandtotalgovernance(spoilgrandtotalgovernance);
        }

        lists.add(multiLineHeadExcelModel);
        return lists;
    }

实体类

package com.jpxx.admin.sitesubcontract.service.api.dto;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.fasterxml.jackson.annotation.JsonFormat;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

@Data
@AllArgsConstructor
@Accessors(chain = true)
@NoArgsConstructor
@ApiModel(value = "SiteSubcontractDto", description = "工地分包情况月报表")
public class SiteSubcontractDto implements Cloneable {

    @ExcelIgnore
    @ApiModelProperty(value = "id")
    private String id;

    @ExcelIgnore
    @ApiModelProperty(value = "工地编号(格式:例 siteEQQ0001)")
    private String sitenum;

    @ExcelIgnore
    @ApiModelProperty(value = "工地编号(格式:例 siteEQQ0001)数字")
    private String num;

    @ExcelProperty(value = {"序号","序号"},index = 0)
    @ApiModelProperty(value = "序号")
    private String ordernum;

    @ExcelProperty(value = {"工地名称","工地名称"},index = 1)
    @ApiModelProperty(value = "工地名称")
    private String sitename;

    @ExcelProperty(value = {"工地详细位置","工地详细位置"},index = 2)
    @ApiModelProperty(value = "工地详细位置")
    private String adress;

    @ExcelIgnore
    @ApiModelProperty(value = "区")
    private String area;

    @ExcelIgnore
    @ApiModelProperty(value = "经度")
    private double longitude;

    @ExcelIgnore
    @ApiModelProperty(value = "纬度")
    private double latitude;

    @ExcelProperty(value = {"建设单位","建设单位"},index = 3)
    @ApiModelProperty(value = "建设单位")
    private String buildunit;

    @ExcelProperty(value = {"施工单位","施工单位"},index = 4)
    @ApiModelProperty(value = "施工单位")
    private String constructionunit;

    @ExcelProperty(value = {"所属乡（镇、办）","所属乡（镇、办）"},index = 5)
    @ApiModelProperty(value = "所属乡（镇、办）")
    private String officecodename;

    @ExcelProperty(value = {"该工地拆迁垃圾治理情况","预计产生量（万方）"},index = 6)
    @ApiModelProperty(value = "拆迁垃圾预计产生量（万方）")
    private double totalgarbage;

    @ColumnWidth(15)
    @ExcelProperty(value = {"该工地拆迁垃圾治理情况","当月拆迁垃圾治理量（万方）"},index = 7)
    @ApiModelProperty(value = "当月拆迁垃圾治理量（万方）")
    private double rubbishcurrentmonthgovernance;

    @ColumnWidth(15)
    @ExcelProperty(value = {"该工地拆迁垃圾治理情况","截止目前拆迁垃圾累计治理量（万方）"},index = 8)
    @ApiModelProperty(value = "截止目前拆迁垃圾累计治理量（万方）")
    private double rubbishgrandtotalgovernance;

    @ColumnWidth(30)
    @ExcelProperty(value = {"该工地拆迁垃圾治理情况","拆迁垃圾直接利用量(现场粉碎成石子或骨料)（万方）"},index = 9)
    @ApiModelProperty(value = "拆迁垃圾直接利用量")
    private double rubbishdirectutilization;

    @ColumnWidth(15)
    @ExcelProperty(value = {"该工地拆迁垃圾治理情况","拆迁垃圾再生利用量(加工厂再生产品砖、骨料、砂浆、水稳、混凝土装配式、墙板楼板、粉碎成石子)（万方）"},index = 10)
    @ApiModelProperty(value = "拆迁垃圾回收利用量")
    private double rubbishrecyclingamount;

    @ColumnWidth(15)
    @ExcelProperty(value = {"该工地拆迁垃圾治理情况","拆迁垃圾垃圾资源化利用方式及消纳利用地点"},index = 11)
    @ApiModelProperty(value = "拆迁垃圾垃圾资源化利用方式及消纳利用地点")
    private double rubbishdealadress;

    @ExcelProperty(value = {"该工地拆迁垃圾治理情况","拆迁垃圾运输公司名称"},index = 12)
    @ApiModelProperty(value = "拆迁垃圾运输公司名称")
    private String rubbishtransportcompany;

    @ExcelProperty(value = {"该工地工程弃土治理情况","工程弃土预计产生量（万方）"},index = 13)
    @ApiModelProperty(value = "工程弃土预计产生量（万方）")
    private double totalspoil;

    @ExcelProperty(value = {"该工地工程弃土治理情况","当月工程弃土治理量（万方）"},index = 14)
    @ApiModelProperty(value = "当月工程弃土治理量（万方）")
    private double spoilcurrentmonthgovernance;

    @ExcelProperty(value = {"该工地工程弃土治理情况","截止目前工程弃土累计治理量（万方）"},index = 15)
    @ApiModelProperty(value = "截止目前工程弃土累计治理量（万方）")
    private double spoilgrandtotalgovernance;

    @ExcelProperty(value = {"该工地工程弃土治理情况","工程弃土治理方式"},index = 16)
    @ApiModelProperty(value = "工程弃土治理方式")
    private String spoilgovernance;

    @ExcelProperty(value = {"该工地工程弃土治理情况","工程弃土消纳地点名称"},index = 17)
    @ApiModelProperty(value = "工程弃土消纳地点名称")
    private double spoildealadress;

    @ExcelProperty(value = {"该工地工程弃土治理情况","工程弃土运输公司名称"},index = 18)
    @ApiModelProperty(value = "工程弃土运输公司名称")
    private String spoiltransportcompany;

    @ExcelIgnore
    @ApiModelProperty(value = "工程弃土直接利用量")
    private double spoildirectutilization;

    @ExcelIgnore
    @ApiModelProperty(value = "工程弃土再生利用量")
    private double spoilrecyclingamount;

//    @ExcelIgnore
    @ExcelProperty(value = {"四级分包情况","单位"},index = 19)
    @ApiModelProperty(value = "单位")
    private String units;

//    @ExcelIgnore
    @ExcelProperty(value = {"四级分包情况","姓名"},index = 20)
    @ApiModelProperty(value = "姓名")
    private String names;

//    @ExcelIgnore
    @ExcelProperty(value = {"四级分包情况","职务"},index = 21)
    @ApiModelProperty(value = "职务")
    private String positions;

//    @ExcelIgnore
    @ExcelProperty(value = {"四级分包情况","手机号"},index = 22)
    @ApiModelProperty(value = "手机号")
    private String phonenumbers;

    @ExcelProperty(value = {"该工地拆迁垃圾、工程弃土治理全部完毕日期（工地治理完毕后，该工地台账信息不删除）","该工地拆迁垃圾、工程弃土治理全部完毕日期（工地治理完毕后，该工地台账信息不删除）"},format = "yyyy/MM/dd",index = 23)
    @ApiModelProperty(value = "该工地拆迁垃圾、工程弃土治理全部完毕日期（工地治理完毕后，该工地台账信息不删除）")
    @DateTimeFormat(pattern="yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date allovertime;

    @ExcelIgnore
    @ApiModelProperty(value = "区域代码")
    private String orgcode;

    @ExcelIgnore
    @ApiModelProperty(value = "拆迁垃圾治理完毕时间")
    @DateTimeFormat(pattern="yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date garbagetime;

    @ExcelIgnore
    @ApiModelProperty(value = "工程弃土治理完毕时间")
    @DateTimeFormat(pattern="yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
    private Date spoiltime;

    @ExcelIgnore
    @ApiModelProperty(value = "工地状态(已完工或正在治理)")
    private String status;

    @Override
    public Object clone() throws CloneNotSupportedException {
        SiteSubcontractDto o = null;
         try {
                o = (SiteSubcontractDto) super.clone();
            } catch (CloneNotSupportedException e) {
                    System.out.println(e.toString());
                 }
         return o;
    }

    @ExcelIgnore
    @ApiModelProperty(value = "区域id")
    private String departid;

}

@postMapping("pilebodystatisticsmonthexport")
public WebApiResponse<List<PilebodycheckMonthDto>> pilebodystatisticsmonthexport (HttpServletResponse response,String month) throws IOException { 
    List<PilebodycheckMonthDto> pilebodysList = pilebodycheckService.pilebodystatisticsmonth(sysDepartDto, month);
   //设置序号
    for (int i = 1;i <= pilebodysList.size();i++){
        pilebodysList.get(i-1).setOrderNum(i+"");
    }
    response.setContentType("application/vnd.ms-excel");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("存量建筑垃圾堆体治理进度月报表", "UTF-8");
    response.setHeader("Content-disposition",  "attachment;filename=" + fileName + ".xls");
    //内容样式策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    //垂直居中,水平居中
    contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
    contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
    contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
    contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
    contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
    //设置 自动换行
    contentWriteCellStyle.setWrapped(true);
    // 字体策略
    WriteFont contentWriteFont = new WriteFont();
    // 字体大小
    contentWriteFont.setFontHeightInPoints((short) 12);
    contentWriteCellStyle.setWriteFont(contentWriteFont);
    //头策略使用默认
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
 
    //excel如需下载到本地,只需要将response.getOutputStream()换成File即可(注释掉以上response代码)
    EasyExcel.write(response.getOutputStream(), PilebodycheckMonthDto.class)
            //设置输出excel版本,不设置默认为xlsx
            .excelType(ExcelTypeEnum.XLS).head(PilebodycheckMonthDto.class)
            //设置拦截器或自定义样式
            .registerWriteHandler(new MonthSheetWriteHandler())
            .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle))
            .sheet("存量建筑垃圾堆体治理进度月报表")
            //设置默认样式及写入头信息开始的行数
            .useDefaultStyle(true).relativeHeadRowIndex(3)
            //这里的addsumColomn方法是个添加合计的方法,可删除
            .doWrite(pilebodycheckService.addSumColomn(pilebodysList));
    return new WebApiResponse(200, "生成excel文件成功", null);
 
}
java张杰-汤阴
关注
导入导出功能
weixin_33670786的博客
09-30 253
一，步骤 1.导入： 一.ftl <form id="form" action="" enctype="multipart/form-data" method="post" > <input type="file" id="fileID" name="fileName" class="btn dropdown-toggle btn-primary" st...
poi生成excel大数据量的合并单元格操作优化
weixin_43998503的博客
08-03 2925
poi生成excel大数据量的合并单元格操作优化 之前根据官网给的api和example，对excel单元格的合并操作使用下方的代码（poi版本 3.12） public class MergedCells { public static void main(String[] args) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(
EasyExcel根据自定义模板导出Excel(包含图片、表格)
最新发布
ViperWhip的博客
12-12 1万+
提示：EasyExcel请使用 3.0 以上版本，我这里使用的是 3.0.2 版本：提示：我的需求是，前端页面有个 echarts 图，需要导出到excel,当然前端也可以直接导出图片；但是我是需要将图片插入到模板指定位置，模板中还有其他内容，所以需要前端先将图片传给我，后台再写入到模板中请点击这里。
Java笔记总结（四十）---POI的常用方法
不学习，永远发现不了自己的潜力
04-13 2459
getMergedRegion(int index) Returns the merged region at the specified index 返回指定索引处的合并区域 getMergedRegions() Returns the list of merged regions 返回合并区域的列表 getNumMergedRegions() returns the number of merged regions 返回合并区域的数量 addMergedRegion(CellRangeAddres
C#合并单元格，AddMergedRegion
youyou390的专栏
12-28 7038
做项目用到了合并单元格，本以为是件简单的事情，几句话搞定。 上网搜了下方法，百度排名第一的结果，立马跳出来。写得也很清楚 原文如下： 这里就介绍一个DLL文件NPOI.dll的合并单元格。 假如我们需要合并第一行和第二行： 代码：sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 0)); CellRangeAddress有4个参数...
UAP标准导入导出功能 UAP标准导入导出功能
10-30
《UAP标准导入导出功能详解》 在IT领域，数据的导入与导出是日常工作中不可或缺的一部分。尤其是在企业级应用系统中，如NC65这样的企业管理软件，UAP（通用应用平台）提供了标准化的导入导出功能，使得数据管理更为...
详解JS模块导入导出
10-18
### JS模块导入导出的概念与方法 #### 模块化开发背景 在前端开发中，模块化已经成为一种普遍的实践，其目的是将复杂的系统分解成一个个小的模块，便于维护和复用。模块化不仅可以提高代码的可读性，还可以在不同...
execl导入导出
12-27
"Excel导入导出"这一主题涵盖了如何将数据从外部源导入到Excel工作表，以及如何将Excel数据导出到其他格式或系统中。以下是对这个主题的详细阐述： **一、Excel数据导入** 1. **CSV导入**：CSV（逗号分隔值）是一...
YukBrandes#mysql_notes#11.导入导出1
07-25
# 导入文件本身没有换行导出数据库：select * into outfile "E:\test\test.txt" fields terminated by
Oracle 19c 备份恢复-导入导出
07-10
Oracle 19c 备份恢复-导入导出 Oracle 19c 备份恢复-导入导出是指使用 Oracle 数据库提供的工具 exp 和 imp 实现数据库的备份恢复和数据的导入导出。本文将详细介绍 Oracle 19c 备份恢复-导入导出的基本命令、工作...
使用EasyExcel在同一单元格内添加图片和文字，并作格式排版
阿任的博客
12-01 7968
java使用EasyExcel在同一单元格内添加图片和文字，并按格式要求进行排版
EasyExcel · 写excel
程序员朱永胜
05-09 1742
原文地址 通用数据生成 后面不会重复写 private List<DemoData> data() { List<DemoData> list = ListUtils.newArrayList(); for (int i = 0; i < 10; i++) { DemoData data = new DemoData(); data.setString("字符串" + i);
java poi 合并单元格
热门推荐
我要飞翔
03-29 8万+
poi 合并 单元格 XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); //这个就是合并单元格 //参数说明：1：开始行 2：结束行 3：开始列 4：结束列 //比如我要合并 第二行到第四行的 第六列到第八列 sheet.addMergedRegion(new CellRang
使用easyExcel导出
大表哥
09-13 3476
参考博文 https://www.yuque.com/easyexcel/doc/fill https://blog.csdn.net/qq_41514643/article/details/106993760 需要导入依赖的 上面两篇文章都有提交过 直接上才艺 1、常规导出（不合并表头的） public void exportExcel(HttpServletRequest request, HttpServletResponse response, Map<String, Ob
Excel的导入和导出功能实现
yzj479622672的博客
08-28 2万+
工作中经常会用到excel的导入和导出功能，这里我提供导入和导出类。 导入类（需要注意的地方我注释里面写好了）： package cn.teacheredu.utils; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Arr
使用EasyExcel导入和复杂头导出
weixin_43029947的博客
04-22 1583
在使用easy Excel之前，可以阅读一下它的官方的文档，因为我也是阅读文档，结合自己的业务需求写的。这里分享一下，我是如何写的。 pom.xml文件引入easy excel的依赖 <dependency> <groupId>com.github.xiaoymin</groupId> <artifactId>knife4j-spring-boot-starter&lt
easyexcel复杂表格---包含单元格合并，表格标题，以及自定义字段写入
jx1254916009的博客
03-31 5298
工作当中，遇到一个需要复杂表格的设计，第一次用easyexcel，在这里记录一下，看到的小伙伴大家一起进步。 需求 话不多说直接上图，大概就是需要这种格式的excel，根据选择下载的个数分成多个sheet 代码 好了需求来了，废话不说，直接上代码。 1.引入pom <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> &
EasyExcel个性化表头
kewen_123的博客
02-21 2028
EasyExcel个性化表头
EasyExcel写标注comment异常解决
人在码途，逐日拾光
09-01 2574
异常信息：Multiple cell comments in one cell are not allowed, cell: A1 java.lang.IllegalArgumentException: Multiple cell comments in one cell are not allowed, cell: A1 at org.apache.poi.xssf.usermodel.XSSFDrawing.createCellComment(XSSFDrawing.java:358) ~[poi.
Oracle导入导出操作指南
"Oracle导入导出操作手册，由孙海鹰在快钱支付清算信息有限公司编写，旨在指导开发人员进行数据库的导入导出操作。该手册适用于DBA和SCM内部，不对外公开，旨在提高工作效率。" Oracle的导入导出操作是数据库管理中...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值