零件统计表导出功能

bluejay

2025-04-12 643514ba15be4c373bfdf35e2d7c64e4cbda60a4

零件统计表导出功能

 aps-common/aps-common-core/src/main/java/com/aps/common/core/utils/poi/ExcelUtil.java

@@ -688,7 +688,7 @@
     * @param wb 工作薄对象
     * @return 样式列表
     */
    private Map<String, CellStyle> createStyles(Workbook wb)
    public Map<String, CellStyle>  createStyles(Workbook wb)
    {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

 aps-modules/aps-core/src/main/java/com/aps/core/controller/ApsPartRouteStatController.java

@@ -120,5 +120,10 @@
      return   apsPartRouteStatService.selectResourceDateStat();
    }

    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response)
    {
        apsPartRouteStatService.exportExcel(response);
    }

}

 aps-modules/aps-core/src/main/java/com/aps/core/service/IApsPartRouteStatService.java

@@ -4,6 +4,7 @@

import com.aps.common.core.web.domain.AjaxResult;
import com.aps.core.domain.ApsPartRouteStat;
import jakarta.servlet.http.HttpServletResponse;

/**
 * 零件统计表Service接口
@@ -64,4 +65,6 @@
    void updatePartRoutPlanDate();

    AjaxResult selectResourceDateStat();

    void exportExcel(HttpServletResponse response);
}

 aps-modules/aps-core/src/main/java/com/aps/core/service/impl/ApsPartRouteStatServiceImpl.java

@@ -8,10 +8,21 @@
import java.util.*;
import java.util.stream.Collectors;

import com.aps.common.core.utils.poi.ExcelUtil;
import com.aps.common.core.utils.uuid.IdUtils;
import com.aps.common.core.web.domain.AjaxResult;
import com.aps.core.domain.ApsPartPlan;
import com.aps.core.domain.ApsResourceDateStat;
import com.aps.core.mapper.ApsResourceGroupMapper;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.aps.core.mapper.ApsPartRouteStatMapper;
@@ -26,9 +37,11 @@
 * @author zhl
 * @date 2025-04-11
 */
@Slf4j
@Service
public class ApsPartRouteStatServiceImpl implements IApsPartRouteStatService 
{

    @Autowired
    private ApsPartRouteStatMapper apsPartRouteStatMapper;
    @Autowired
@@ -210,4 +223,97 @@
        return result;
    }

    @Override
    public void exportExcel(HttpServletResponse response) {
        SXSSFWorkbook wb = new SXSSFWorkbook(500);
        wb.createSheet();
        wb.setSheetName(0, "零件统计表");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");

        Map<String, CellStyle> styles = createStyles(wb);
        CellStyle title = styles.get("title");
        try
        {

            AjaxResult stat = selectResourceDateStat();
            List<String> days = (List<String>) stat.get("planTitle");
            List<Map<String, List<ApsResourceDateStat>>> table= (List<Map<String, List<ApsResourceDateStat>>>) stat.get("planTable");
            SXSSFSheet sheet = wb.getSheetAt(0);
            /*填写日期列 和 工时列*/
            SXSSFRow rowDay = sheet.createRow(0);
            SXSSFRow rowTitle = sheet.createRow(1);

            SXSSFCell daytitle = rowDay.createCell(0);
            daytitle.setCellValue("日期");
            daytitle.setCellStyle(title);
            SXSSFCell titleCell = rowTitle.createCell(0);
            titleCell.setCellValue("资源组");
            titleCell.setCellStyle(title);

            for (int i = 0; i < days.size(); i++) {
                SXSSFCell dateCell = rowDay.createCell(i * 3 + 1);
                SXSSFCell designHoursCell = rowTitle.createCell(i * 3 + 1);
                SXSSFCell requireHoursCell = rowTitle.createCell(i * 3 + 2);
                SXSSFCell loadCell = rowTitle.createCell(i * 3 + 3);
                dateCell.setCellValue(days.get(i));
                designHoursCell.setCellValue("设计工时");
                requireHoursCell.setCellValue("需求工时");
                loadCell.setCellValue("产能负荷");
                /*set cell style*/
                dateCell.setCellStyle(title);
                designHoursCell.setCellStyle(title);
                requireHoursCell.setCellStyle(title);
                loadCell.setCellStyle(title);

                /*合并日期单元格*/
                sheet.addMergedRegion( new CellRangeAddress(0, 0, i*3+1, i*3+3));
            }
            for (int i = 0; i < table.size(); i++) {
                Map<String, List<ApsResourceDateStat>> resourceList = table.get(i);
                /*创建数据行*/
                SXSSFRow dataRow  = sheet.createRow(i+2);
                for( Map.Entry<String, List<ApsResourceDateStat>> entry : resourceList.entrySet()){
                    String resourceName = entry.getKey();
                    List<ApsResourceDateStat> resourceDateStats = entry.getValue();
                    dataRow.createCell(0).setCellValue(resourceName);
                    for (int j = 0; j < resourceDateStats.size(); j++) {
                        ApsResourceDateStat apsResourceDateStat = resourceDateStats.get(j);
                        dataRow.createCell(j*3+1).setCellValue(apsResourceDateStat.getDesignTimes());
                        dataRow.createCell(j*3+2).setCellValue(apsResourceDateStat.getRequireTimes());
                        dataRow.createCell(j*3+3).setCellValue(apsResourceDateStat.getCapacityLoad().doubleValue()+"%");
                    }
                }

            }
            wb.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            log.error("导出Excel异常{}", e.getMessage());
        }
        finally
        {
            IOUtils.closeQuietly(wb);
        }

    }
    private Map<String,CellStyle> createStyles(SXSSFWorkbook wb)
    {
        Map<String,CellStyle> styles=new HashMap<>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short) 16);
        titleFont.setBold(true);
        style.setFont(titleFont);
        DataFormat dataFormat = wb.createDataFormat();
        style.setDataFormat(dataFormat.getFormat("@"));
        styles.put("title", style);
        return styles;
    }


}