POI导入，导出（分格式写入excel,合并单元格）

系列文章目录

之前的文章介绍了阿里easyExcel的导入导出，现在记录一下使用POI进行导入导出的方式

---

一、列固定

要导入、导出的数据列是固定的，直接上代码：

导入：

 @Override
    @Transactional(rollbackFor = Exception.class)
    public Result<?> importByExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        MultipartFile file = fileMap.get("file");//获取上传文件对象
        if (file == null){
            return Result.error("文件上传失败！");
        }
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = null;
        if (workbook != null && (sheet = workbook.getSheetAt(0)) != null){
            //总行数>2代表有数据，表头占一行
            int a = sheet.getLastRowNum();
            if (sheet.getLastRowNum() >= 1){
                XSSFRow row0 =sheet.getRow(0); //获取标题行
                //获取行里的总列数
                int columnNum = 0;
                HashSet<String> titles = new HashSet<>();//标题列表
                HashMap<Integer, String> columnsMap = new HashMap<>();
                HashMap<String, String> titlesCustomMap = new HashMap<>();//自定义标题列表对应字段
                if (row0 != null && (columnNum = row0.getPhysicalNumberOfCells()) > 0){ //列大于0
                    titlesCustomMap.put("部门属性","setDepartProperty");
                    titlesCustomMap.put("年","setYears");
                    titlesCustomMap.put("月","setMonths");
                    titlesCustomMap.put("预算实体","setBudgetEntity");
                    titlesCustomMap.put("新架构一级部门","setPrimaryDepart");
                    titlesCustomMap.put("新架构下二级展示部门","setSecondaryDepart");
                    titlesCustomMap.put("按平台分类","setPlatformClass");
                    titlesCustomMap.put("预算科目","setBudgetSubject");
                    titlesCustomMap.put("科目属性","setSubjectProperty");
                    titlesCustomMap.put("一级科目","setPrimarySubject");
                    titlesCustomMap.put("简化科目","setSimplifySubject");
                    titlesCustomMap.put("类型","setFinanceStype");
                    titlesCustomMap.put("项目","setProject");
                    titlesCustomMap.put("BU","setBu");
                    titlesCustomMap.put("版本","setVersion");
                    titlesCustomMap.put("金额","setFinanceMoney");
                    for (int i = 0; i < columnNum; i++) {
                        //获取标题行每个单元格
                        XSSFCell cell0 = row0.getCell(i);
                        if (cell0 == null) {
                            continue;
                        }
                        //获取标题
                        String title = cell0.getStringCellValue();
                        String invokeMethod = null;
                        if (StringUtils.isNoneBlank(title,(invokeMethod = titlesCustomMap.get(title))) && !titles.contains(title)) {//判断表头字段是否存在重复
                            titles.add(title);
                            columnsMap.put(i,invokeMethod);
                        }
                    }
                }
                if(titlesCustomMap.size() == columnsMap.size()){
                    Class<? extends UPmoProjectFinance> classType = UPmoProjectFinance.class;
//                    List<String> dataKeys = Arrays.asList(new String[]{"setYearMonths"});
                    List<String> bigDecimalKeys = Arrays.asList(new String[]{"setFinanceMoney"});
//                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-M");
//                    SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");

                    //获取总行数
                    int lastRowNum = sheet.getLastRowNum();
                    List<UPmoProjectFinance> uPmoProjectFinanceList = new ArrayList<>();  //接收数据
                    for(int m = 1; m <= lastRowNum; m++) {//从第2行开始读取数据
                        UPmoProjectFinance uPmoProjectFinance = new UPmoProjectFinance();
                        XSSFRow rowData = sheet.getRow(m);
                        for (Map.Entry<Integer, String> column : columnsMap.entrySet()) {
                            XSSFCell cell = rowData.getCell(column.getKey());
                            String invokeMethod = column.getValue();
                            if (cell != null && StringUtils.isNotBlank(invokeMethod)) {
                                cell.setCellType(CellType.STRING);
                                String stringCellValue = cell.getStringCellValue();
                                if(StringUtils.isNotBlank(stringCellValue)){
//                                    if(dataKeys.contains(invokeMethod)){
//                                        Method declaredMethod = classType.getMethod(invokeMethod, String.class);
//                                        Date date1Date = sdf.parse(stringCellValue);
//                                        declaredMethod.invoke(uPmoProjectFinance,sdf2.format(date1Date));
//                                    }else if (bigDecimalKeys.contains(invokeMethod)){
//                                        Method declaredMethod = classType.getMethod(invokeMethod, BigDecimal.class);
//                                        declaredMethod.invoke(uPmoProjectFinance,new BigDecimal(stringCellValue));
//                                    }
                                    if (bigDecimalKeys.contains(invokeMethod)){
                                        Method declaredMethod = classType.getMethod(invokeMethod, BigDecimal.class);
                                        declaredMethod.invoke(uPmoProjectFinance,new BigDecimal(stringCellValue));
                                    }
                                    else {
                                        Method declaredMethod = classType.getMethod(invokeMethod,String.class);
                                        declaredMethod.invoke(uPmoProjectFinance,stringCellValue);
                                    }
                                }
                            }
                        }
                        uPmoProjectFinanceList.add(uPmoProjectFinance);
                    }
                    for (UPmoProjectFinance uPmoProjectFinance : uPmoProjectFinanceList) {
                        if(uPmoProjectFinance.getMonths().split("月")[0].length() != 2){
                            uPmoProjectFinance.setMonths("0"+uPmoProjectFinance.getMonths().split("月")[0]);
                        }else {
                            uPmoProjectFinance.setMonths(uPmoProjectFinance.getMonths().split("月")[0]);
                        }
//
//                        QueryWrapper<UPmoProjectFinance> queryWrapper = new QueryWrapper<>();
//                        queryWrapper.eq("YEARS",uPmoProjectFinance.getYears());
//                        queryWrapper.eq("MONTHS",uPmoProjectFinance.getMonths());
//                        queryWrapper.eq("VERSION",uPmoProjectFinance.getVersion());
//                        queryWrapper.eq("PROJECT",uPmoProjectFinance.getProject());
//                        long count = this.count(queryWrapper);
//                        if (count>0){
//                            super.remove(queryWrapper);
//                        }
                    }
                    pmoProjectFinanceMapper.deleteSjYs();
                    super.saveBatch(uPmoProjectFinanceList);


                }else {
                    return Result.error("表头字段存在异常！");
                }

            }else {
                return Result.error("文件为空！");
            }
            return Result.ok("导入成功！");
        }else {
            return Result.error("文件导入失败！");
        }

    }

导出：

    @Override
    @ApiOperation(value="数据导出", notes="数据导出")
    @PostMapping(value = "/projectDataToExcel")
    public Result<?> projectDataToExcel(HttpServletRequest request, HttpServletResponse response) {
        String project = "G20";
        String project1 = "Sabers";
        String dataDate = null;
        Map<String, List<G20SabersVo>> collect = commonService.getProjectInfo(project,dataDate);
        Map<String, List<G20SabersVo>> collect1 = commonService.getProjectInfo(project1,dataDate);
        List<G20SabersVo> projectInfoHouList = commonService.getProjectInfoHou();
        List<G20SabersVo> resultG20Total = commonService.getProjectInfoTotal(collect,projectInfoHouList);
        List<G20SabersVo> resultSabersTotal = commonService.getProjectInfoTotal(collect1,projectInfoHouList);
        resultG20Total.addAll(resultSabersTotal);
        resultG20Total = resultG20Total.stream().sorted(Comparator.comparing(G20SabersVo::getOrderNo)).collect(Collectors.toList());
        //导出
        String sheetName = "信息表";
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet(sheetName);
        //合并的表头
        String[] head = new String[]{"物料需求","物料需求","物料需求","物料需求","物料需求","物料需求","物料需求","实际缺料","合格","合格","合格","待检","待检","待检"};
        int titleIndex = 0;
        //表头对应数据查询字段
        Map<String, String> titleMap = new LinkedHashMap<>();
        sheet.setColumnWidth(titleIndex++, 5000); titleMap.put("getKldate", "日期");
        sheet.setColumnWidth(titleIndex++, 7000); titleMap.put("getErpWo", "工单");
        sheet.setColumnWidth(titleIndex++, 5000); titleMap.put("getMainitemNumber", "物料编号");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getQuantity", "需求");
        sheet.setColumnWidth(titleIndex++, 5000); titleMap.put("getRawItemNumber", "主替物料");
        sheet.setColumnWidth(titleIndex++, 10000); titleMap.put("getHpmc", "物料名称");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getQuantityIn", "收料");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getSjql", "实际缺料");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getHgXbkc", "线边库存");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getHgDfb", "待翻包");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getHgGwl", "高五路");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getDjN2", "N2#1F");
        sheet.setColumnWidth(titleIndex++, 3000); titleMap.put("getDjGwl", "高五路");
        //设置文档密级
        sheet.getHeader().setRight("文档密级：Confidential");
        int rowNumberHe = 0;//合并单元格的表头
        XSSFRow rowTitleHe = sheet.createRow(rowNumberHe++);
        int rowNumber = 1;//表头数据所在行
        XSSFRow rowTitle = sheet.createRow(rowNumber++);

        //实际缺料列设为红
        XSSFCellStyle cellStyleQlTitle = workbook.createCellStyle();
        cellStyleQlTitle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyleQlTitle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyleQlTitle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyleQlTitle.setBorderTop(BorderStyle.THIN);//上边框

        cellStyleQlTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyleQlTitle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        XSSFFont fontQlTitle = workbook.createFont();
        fontQlTitle.setColor(XSSFFont.COLOR_RED);
        cellStyleQlTitle.setFont(fontQlTitle);
        //设置单元格样式
        XSSFCellStyle cellStyleTitle = workbook.createCellStyle();
        cellStyleTitle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyleTitle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyleTitle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyleTitle.setBorderTop(BorderStyle.THIN);//上边框

        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        XSSFFont fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short)10);
        fontTitle.setBold(true);
        cellStyleTitle.setFont(fontTitle);
        //设置单元格样式
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中

        int cellIndex = 0;
        int cellIndexHe = 0;
        Set<Map.Entry<String, String>> entries = titleMap.entrySet();
        int i = 0;
        for (Map.Entry<String, String> titleEntry : entries) {
            XSSFCell cellHe = rowTitleHe.createCell(cellIndexHe++);
            cellHe.setCellValue(head[i]);
            cellHe.setCellStyle(cellStyleTitle);

            XSSFCell cell1 = rowTitle.createCell(cellIndex++);
            cell1.setCellValue(titleEntry.getValue());
            cell1.setCellStyle(cellStyleTitle);
            i++;
        }
        if(CollectionUtils.isNotEmpty(resultG20Total)){
            Class<? extends G20SabersVo> classType = G20SabersVo.class;
            List<String> bigDecimalKeys = Arrays.asList(new String[]{"getQuantity","getQuantityIn","getHgXbkc","getHgDfb","getHgGwl","getDjN2","getDjGwl"});
            List<String> bigDecimalQlKeys = Arrays.asList(new String[]{"getSjql"});
            for (G20SabersVo g20SabersVo : resultG20Total) {
                XSSFRow row = sheet.createRow(rowNumber++);
                cellIndex = 0;
                for (Map.Entry<String, String> titleEntry : entries) {
                    String key = titleEntry.getKey();
                    XSSFCell cell = row.createCell(cellIndex++);
                    Method declaredMethod = null;
                    try {
                        declaredMethod = classType.getMethod(key);
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                    }
                    Object invoke = null;
                    try {
                        invoke = declaredMethod.invoke(g20SabersVo);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    }
                    //设置格式
                    if(bigDecimalQlKeys.contains(key)){
                        cell.setCellStyle(cellStyleQlTitle);
                    }else{
                        cell.setCellStyle(cellStyle);
                    }
                    //设置内容
                    if(bigDecimalKeys.contains(key)){
                        cell.setCellValue(((Integer)invoke));
                    }else{
                        cell.setCellValue(String.valueOf(invoke));
                    }

                }
            }
        }
        //（2,1,2,7）合并列从第3行开始，合并第2,3,8数据，所合并列必须是同一数据类型
        PoiMergeCellUtil.mergeCells(sheet,2,1,2);
        PoiMergeCellUtil.mergeCells(sheet,2,7);
        //(0,0,0,0)合并行，从第1行开始,到第一行结束,1-7行合并
        PoiMergeCellUtil.addMergedRegion(sheet,0,0,0,6);
        PoiMergeCellUtil.addMergedRegion(sheet,0,0,8,10);
        PoiMergeCellUtil.addMergedRegion(sheet,0,0,11,12);
        try(ServletOutputStream out = response.getOutputStream()) {
            workbook.write(out);
            out.flush();
            return Result.ok("导出成功！");
        } catch (IOException e) {
            return Result.ok("导出失败！");
        }
    }

二、导入导出列不固定，导入行转列存库

代码如下：

package org.jeecg.modules.materialExcel.service.impl;

import com.aspose.pdf.Document;
import com.aspose.pdf.SaveFormat;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.jeecg.common.api.vo.Result;
import org.jeecg.modules.materialExcel.entity.UMaterialExcel;
import org.jeecg.modules.materialExcel.mapper.UMaterialExcelMapper;
import org.jeecg.modules.materialExcel.service.IUMaterialExcelService;
import org.jeecg.modules.materialExcel.utils.SendEmail;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


@Service
public class UMaterialExcelServiceImpl extends ServiceImpl<UMaterialExcelMapper, UMaterialExcel> implements IUMaterialExcelService {

    @Autowired
    private UMaterialExcelMapper uMaterialExcelMapper;
    @Autowired
    private SendEmail sendEmail;

    @Override
    @Transactional
    public Result<?> importByExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        String project = request.getParameter("project");
        if(StringUtils.isBlank(project)){
            throw new Exception("请输入导出项目");
        }
        MultipartFile file = fileMap.get("file");
        if (file == null) {
            return Result.error("文件上传失败！");
        }
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = null;
        if (workbook != null && (sheet = workbook.getSheetAt(0)) != null) {
            DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            if (sheet.getLastRowNum() >= 3) {
                XSSFRow row0 = sheet.getRow(0);//获取标题行
                XSSFRow row2 = sheet.getRow(1);//获取标题行
                int columnNum = 0; //获取行里的总列数
                HashSet<String> titles = new HashSet<>();//标题列表
                HashMap<Integer, String> columnsMap = new HashMap<>();
                HashMap<Integer, String> columnsDateMap = new HashMap<>();
                if (row0 != null && (columnNum = row0.getPhysicalNumberOfCells()) > 0) {//列大于0
                    HashMap<String, String> titlesColumnMap = new HashMap<>();//自定义标题列表对应字段
                    titlesColumnMap.put("Supplier", "supplier");
                    titlesColumnMap.put("SKU", "sku");
                    titlesColumnMap.put("Part#", "part");
                    titlesColumnMap.put("GTK PN", "gtkpn");
                    titlesColumnMap.put("Description", "description");
                    titlesColumnMap.put("Ship To", "shipto");
                    titlesColumnMap.put("Transit Mode", "transitmode");
                    //从5行，第7列获取表头信息
                    for (int i = 0; i < columnNum; i++) {
                        if(i < 5){
                            //从第2行，获取表头数据
                            XSSFCell cell = row2.getCell(i);
                            if(cell != null){
                                String title = cell.getStringCellValue();
                                if(StringUtils.isNotBlank(title) && !titles.contains(title)) {//判断表头字段是否存在重复
                                    titles.add(title);
                                    String invokeMethod = titlesColumnMap.get(title);
                                    if (StringUtils.isNotBlank(invokeMethod) ) {
                                        invokeMethod = "set"+invokeMethod.substring(0, 1).toUpperCase() + invokeMethod.substring(1);
                                        columnsMap.put(i,invokeMethod);
                                    }
                                }
                            }
                        }else{
                            //从第6行获取表头数据
                            XSSFCell cell = row0.getCell(i);
                            if(cell != null){
                                //0代表是数字类型
                                int a = cell.getCellType();
                                if(cell.getCellType() == 0 || cell.getCellType() == 2){
                                    String title = dateFormat.format(cell.getDateCellValue());
                                    columnsDateMap.put(i,title);
                                }else{
                                    String stringCellValue = cell.getStringCellValue();
                                    if(StringUtils.isNotBlank(stringCellValue)){
                                        columnsDateMap.put(i,stringCellValue);
                                    }
                                }
                            }
                        }
                    }
                }

                Class<? extends UMaterialExcel> classType = UMaterialExcel.class;
                int lastRowNum = sheet.getLastRowNum();
                ArrayList<UMaterialExcel> insertList = new ArrayList<>();
                for(int m = 2; m <= lastRowNum; m++) {//从第6行开始读取数据
                    XSSFRow rowData = sheet.getRow(m);//获取标题行
                    UMaterialExcel uMaterialExcel = new UMaterialExcel();
                    for (Map.Entry<Integer, String> column : columnsMap.entrySet()) {
                        XSSFCell cell = rowData.getCell(column.getKey());
                        String invokeMethod = column.getValue();
                        if (cell != null){
                            cell.setCellType(CellType.STRING);
                            Method declaredMethod = classType.getMethod(invokeMethod, String.class);
                            if (cell.getCellType() == 0) {
                                declaredMethod.invoke(uMaterialExcel, String.valueOf(cell.getNumericCellValue()));
                            } else {
                                declaredMethod.invoke(uMaterialExcel, cell.getStringCellValue());
                            }
                        }
                    }
                    for (Map.Entry<Integer, String> column : columnsDateMap.entrySet()) {
                        XSSFCell cell = rowData.getCell(column.getKey());
                        String date = column.getValue();
                        if (cell != null) {
                            String contentData = null;
                            if(cell.getCellType() == 0 || cell.getCellType() == 2){
                                contentData = new BigDecimal(cell.getNumericCellValue()).toString();

                            }else {
                                contentData = cell.getStringCellValue();
                            }
                            if(contentData != null){

                                UMaterialExcel materialExcel = new UMaterialExcel();
                                BeanUtils.copyProperties(uMaterialExcel, materialExcel);
                                materialExcel.setContentdate(date);
                                materialExcel.setContentdata(contentData);
                                //本周周一
                                materialExcel.setImportdates(dateFormat.format(getWeekDay()[1]));
                                //上周五
                                String zhouEr = dateFormat.format(getWeekDay()[1]);
                                Calendar calendar = Calendar.getInstance();
                                calendar.setTime(dateFormat.parse(zhouEr));
                                calendar.set(Calendar.DATE, calendar.get(Calendar.DATE) - 4);
                                String zhouWu = dateFormat.format(calendar.getTime());
                                materialExcel.setImportdate(zhouWu);
                                //第几周
                                materialExcel.setWeeknum(getWeeksInMonthOfDate(dateFormat.format(new Date())));
                                //表头日期是第几周
                                materialExcel.setTitleweek(getWeeksInMonthOfDate(date));
                                materialExcel.setProject(project);
                                insertList.add(materialExcel);
                            }
                        }
                    }
                }
                List<String> importDate = new ArrayList<>();
                for (UMaterialExcel uMaterialExcel : insertList) {
                    if (!importDate.contains(uMaterialExcel.getImportdate())){
                        importDate.add(uMaterialExcel.getImportdate());
                    }
                }
                QueryWrapper<UMaterialExcel> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("importdate",importDate.get(0))
                        .eq("project",project);
                long count = super.count();
                if (count>0){
                    super.remove(queryWrapper);
                }
                super.saveBatch(insertList);
                return Result.OK();
            }else {
                return Result.OK();
            }
        }
        return Result.error("文件导入失败！");
    }

    @Override
    public Result<?> exportXlsx(String project, HttpServletRequest request, HttpServletResponse response) {
        String projectName = project;
        if(StringUtils.isBlank(projectName)){
            return Result.error("请选择导出项目");
        }
//        List<UMaterialExcel> uMaterialExcels = uMaterialExcelMapper.list(projectName);
//        List<UMaterialExcel> uMaterialExcelsG = uMaterialExcelMapper.listG(projectName);
//        List<UMaterialExcel> uMaterialExcelsTotal = uMaterialExcelMapper.listTotal(projectName);
//        List<List<UMaterialExcel>> listList = Arrays.asList(uMaterialExcels,uMaterialExcelsG,uMaterialExcelsTotal);
        if (projectName!=null){
            new Thread(()->{
                String projectNameTh = projectName;
                excelExportXlsx(projectNameTh);
            }).start();
            return Result.OK("数据解析解析中....，文件将以邮件形式发送");
        }else {
            return Result.error("无数据！");
        }
    }

    @Override
    public Result<?> dataConversion(HttpServletRequest request, HttpServletResponse response) throws Exception {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        String project = request.getParameter("project");
        if(StringUtils.isBlank(project)){
            throw new Exception("请输入导出项目");
        }
        MultipartFile file = fileMap.get("file");
        if (file == null) {
            return Result.error("文件上传失败！");
        }
        int hang = 0;
        int sheetNum = 0;
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        if (workbook != null){

            int numberOfSheets = workbook.getNumberOfSheets();
            XSSFSheet sheet = null;
            for(int j = 0; j<numberOfSheets; j++){
                sheetNum++;
                System.out.println("sheet页"+sheetNum);
                if ((sheet = workbook.getSheetAt(j)) != null) {
                    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    if (sheet.getLastRowNum() >= 3) {
                        XSSFRow row3 = sheet.getRow(3);//获取标题行
                        int columnNum = 0; //获取行里的总列数
                        HashSet<String> titles = new HashSet<>();//标题列表
                        HashMap<Integer, String> columnsMap = new HashMap<>();
                        HashMap<Integer, String> columnsDateMap = new HashMap<>();
                        if (row3 != null && (columnNum = row3.getPhysicalNumberOfCells()) > 0) {//列大于0
                            HashMap<String, String> titlesColumnMap = new HashMap<>();//自定义标题列表对应字段
                            titlesColumnMap.put("Supplier", "supplier");
                            titlesColumnMap.put("GTK PN", "gtkpn");
                            titlesColumnMap.put("SKU", "sku");
                            titlesColumnMap.put("Description", "description");
                            titlesColumnMap.put("Part#", "part");
                            titlesColumnMap.put("Exfactory release date(cut off Sat by week)", "importdate");
                            titlesColumnMap.put("MRP running date", "importdates");
                            titlesColumnMap.put("周别", "weeknum");
                            //从5行，第7列获取表头信息
                            for (int i = 0; i < columnNum; i++) {
                                if(i < 8){
                                    //从第3行，获取表头数据
                                    XSSFCell cell = row3.getCell(i);
                                    if(cell != null){
                                        String title = cell.getStringCellValue();
                                        if(StringUtils.isNotBlank(title) && !titles.contains(title)) {//判断表头字段是否存在重复
                                            titles.add(title);
                                            String invokeMethod = titlesColumnMap.get(title);
                                            if (StringUtils.isNotBlank(invokeMethod) ) {
                                                invokeMethod = "set"+invokeMethod.substring(0, 1).toUpperCase() + invokeMethod.substring(1);
                                                columnsMap.put(i,invokeMethod);
                                            }
                                        }
                                    }
                                }else{
                                    XSSFCell cell = row3.getCell(i);
                                    if(cell != null){
                                        //0代表是数字类型
                                        int a = cell.getCellType();
                                        if(cell.getCellType() == 0 || cell.getCellType() == 2){
                                            String title = dateFormat.format(cell.getDateCellValue());
                                            columnsDateMap.put(i,title);
                                        }else{
                                            String stringCellValue = cell.getStringCellValue();
                                            if(StringUtils.isNotBlank(stringCellValue)){
                                                columnsDateMap.put(i,stringCellValue);
                                            }
                                        }
                                    }
                                }
                            }
                        }

                        Class<? extends UMaterialExcel> classType = UMaterialExcel.class;
                        int lastRowNum = sheet.getLastRowNum();
                        ArrayList<UMaterialExcel> insertList = new ArrayList<>();
                        for(int m = 4; m <= lastRowNum; m++) {//从第5行开始读取数据
                            hang++;
                            System.out.println("行："+hang);
                            XSSFRow rowData = sheet.getRow(m);//获取标题行
                            UMaterialExcel uMaterialExcel = new UMaterialExcel();
                            for (Map.Entry<Integer, String> column : columnsMap.entrySet()) {
                                XSSFCell cell = rowData.getCell(column.getKey());
                                String invokeMethod = column.getValue();
                                if (cell != null){
                                    //cell.setCellType(CellType.STRING);
                                    int b = cell.getCellType();
                                        Method declaredMethod = classType.getMethod(invokeMethod, String.class);
                                    if (cell.getCellType() == 1) {
                                        declaredMethod.invoke(uMaterialExcel, cell.getStringCellValue());
                                    }
                                    else {
                                        declaredMethod.invoke(uMaterialExcel, dateFormat.format(cell.getDateCellValue()));
                                    }
                                }
                            }
                            for (Map.Entry<Integer, String> column : columnsDateMap.entrySet()) {
                                XSSFCell cell = rowData.getCell(column.getKey());
                                String date = column.getValue();
                                if (cell != null) {
                                    String contentData = null;
                                    if(cell.getCellType() == 0 || cell.getCellType() == 2){
                                        contentData = new BigDecimal(cell.getNumericCellValue()).toString();

                                    }else {
                                        contentData = cell.getStringCellValue();
                                    }
                                    if(contentData != null){

                                        UMaterialExcel materialExcel = new UMaterialExcel();
                                        BeanUtils.copyProperties(uMaterialExcel, materialExcel);
                                        materialExcel.setContentdate(date);
                                        materialExcel.setContentdata(contentData);
                                        //表头日期是第几周
                                        materialExcel.setTitleweek(getWeeksInMonthOfDate(date));
                                        materialExcel.setProject(project);
                                        insertList.add(materialExcel);
                                    }
                                }
                            }
                        }

                        super.saveBatch(insertList);
                    }else {
                        return Result.error("导入失败!!!"+"第"+sheetNum+"个sheet页，第"+hang+"行数据错误");
                    }
                }
            }
            return Result.OK();
        }

        return Result.error("导入失败!!!"+"第"+sheetNum+"个sheet页，第"+hang+"行数据错误");
    }

    @Override
    public Result<?> pdfToExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        String fileStyle = request.getParameter("fileStyle");
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        MultipartFile file = fileMap.get("file");
        if (fileStyle.equals("1")){
            pdf2excel(file,response);
        }else if (fileStyle.equals("2")){
            pdf2word(file,response);
        }else if (fileStyle.equals("3")){
            pdf2ppt(file,response);
        }
        return Result.OK("转换成功");
    }

    @Override
    public List<String> getProject() {
        List<String> projectList = new ArrayList<>();
        List<Map<String,String>> project = uMaterialExcelMapper.getProject();
        if (project != null && project.size()>0){
            for (Map<String, String> map : project) {
                projectList.add(map.get("PROJECT"));
            }

//            projectList = project.stream()
//                    .map(m ->
//                        m.get("project")
//                    )
//                    .collect(Collectors.toList());
        }
        return projectList;
    }
    //根据当前时间获取其所在周的周一至周日的时间
    public static Date[] getWeekDay() {
        Calendar calendar = Calendar.getInstance();
        while (calendar.get(Calendar.DAY_OF_WEEK) != Calendar.MONDAY) {
            calendar.add(Calendar.DAY_OF_WEEK, -1);
        }
        Date[] dates = new Date[7];  // new Date[7] 星期日
        for (int i = 0; i < 7; i++) {  // i < 7 星期日
            dates[i] = calendar.getTime();
            calendar.add(Calendar.DATE, 1);
        }
        return dates;
    }
    //根据时间获取是第几周
    public static String getWeeksInMonthOfDate(String dateStr){
        SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
        Calendar calendar = Calendar.getInstance();
        try {
            calendar.setTime(df.parse(dateStr));
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //设置每周第一天为周一 默认每周第一天为周日
        calendar.setFirstDayOfWeek(Calendar.MONDAY);
        //设置一周的最小天数
        calendar.setMinimalDaysInFirstWeek(4);
        return String.valueOf(calendar.get(Calendar.YEAR)).substring(2).concat("W").
                concat(String.valueOf(String.format("%02d",calendar.get(Calendar.WEEK_OF_YEAR))));
    }

    /*
     * 列头单元格样式
     */
    public CellStyle getColumnTopStyle(XSSFWorkbook workbook) {

        CellStyle cellStyleTitle = workbook.createCellStyle();
        cellStyleTitle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyleTitle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyleTitle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyleTitle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleTitle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());//单元格颜色
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyleTitle.setAlignment(HorizontalAlignment.LEFT);//水平居中
        cellStyleTitle.setWrapText(true);//自动换行
        Font fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short)15);
        fontTitle.setBold(true);
        cellStyleTitle.setFont(fontTitle);

        return cellStyleTitle;

    }

    public XSSFCellStyle getCellStyle(XSSFWorkbook workbook){
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        return cellStyle;
    }

    public void excelExportXlsx(String projectName){
        XSSFWorkbook workbook = new XSSFWorkbook();
        workbook.setForceFormulaRecalculation(true);// 执行公式

        List<UMaterialExcel> uMaterialExcelPn = uMaterialExcelMapper.gtkPn(projectName);
        if (uMaterialExcelPn!=null && uMaterialExcelPn.size()>0) {
            for (UMaterialExcel uMaterialExcel : uMaterialExcelPn) {
                //封装数据
                LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap = new LinkedHashMap<>();
                HashMap<String, ArrayList<String>> titleMap = new LinkedHashMap<>();
                String gtkPn = uMaterialExcel.getGtkpn();
                List<UMaterialExcel> uMaterialExcels = uMaterialExcelMapper.gtkPnWlList(projectName, gtkPn);
                batchData(uMaterialExcels,projectName,workbook,titleMap,contentDateMap);

            }
        }
        List<UMaterialExcel> uMaterialExcelGb = uMaterialExcelMapper.gb(projectName);
        if (uMaterialExcelGb!=null && uMaterialExcelGb.size()>0){
            for (UMaterialExcel uMaterialExcelg : uMaterialExcelGb) {
                //封装数据
                LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap = new LinkedHashMap<>();
                HashMap<String, ArrayList<String>> titleMap = new LinkedHashMap<>();
                String gb = uMaterialExcelg.getGtkpn();
                List<UMaterialExcel> uMaterialExcelsG = uMaterialExcelMapper.listG(projectName,gb);
                batchData(uMaterialExcelsG,projectName,workbook,titleMap,contentDateMap);
            }
        }
        List<UMaterialExcel> uMaterialExcelsTotal = uMaterialExcelMapper.listTotal(projectName);
        if (uMaterialExcelsTotal!=null && uMaterialExcelsTotal.size()>0){
            //封装数据
            LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap = new LinkedHashMap<>();
            HashMap<String, ArrayList<String>> titleMap = new LinkedHashMap<>();
            batchData(uMaterialExcelsTotal,projectName,workbook,titleMap,contentDateMap);
        }
        String projectNameEmail = projectName;
        String path = "C:/infor/"+projectNameEmail+".xlsx";
        File file = new File(path);
        //如果已经存在则删除
        if (file.exists()) {
            file.delete();
        }
        //检查父包是否存在
        File parentFile = file.getParentFile();
        if (!parentFile.exists()) {
            parentFile.mkdirs();
        }
        //创建文件
        try {
            file.createNewFile();
            FileOutputStream out = new FileOutputStream(path);
            workbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
        sendEmail.mailSendApply(projectNameEmail);
    }

    public void dataEncapsulation(String projectName,XSSFWorkbook workbook,HashMap<String, ArrayList<String>> titleMap,LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap){
        //设置单元格样式
        CellStyle cellStyleTitle = workbook.createCellStyle();
        cellStyleTitle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyleTitle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyleTitle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyleTitle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleTitle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());//单元格颜色
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyleTitle.setWrapText(true);//自动换行
        Font fontTitle = workbook.createFont();
        fontTitle.setFontHeightInPoints((short) 8);
        fontTitle.setBold(true);
        cellStyleTitle.setFont(fontTitle);


        //设置单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderBottom(BorderStyle.THIN);//下边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        for (Map.Entry<String, LinkedHashMap<String, HashMap<String, String>>> mapEntry : contentDateMap.entrySet()) {
            XSSFSheet sheet = workbook.createSheet(mapEntry.getKey());
            //冻结前两行
            sheet.createFreezePane(0,2,0,2);
            //设置密级
            sheet.getHeader().setRight("文档密级：Confidential");
            ArrayList<String> list = titleMap.get(mapEntry.getKey());
            int columnIndex = 1;
            int titleIndex = 0;
            //最开始的一行放一个特殊标头
            //是物料号的才查，物料号的长度大于10
            String message = "";
            if (mapEntry.getKey().trim().length() >= 11) {
                List<Map<String, String>> titleBaseMeList = uMaterialExcelMapper.getTitleBaseMe(mapEntry.getKey());
                message = titleBaseMeList.get(0).get("MESSAGE");
            }


            CellRangeAddress regionB = new CellRangeAddress(0, 0, 0, 6);
            sheet.addMergedRegion(regionB);
            XSSFRow rowHeadTitle = sheet.createRow(0);
            XSSFCell cellHead = rowHeadTitle.createCell(0);
            cellHead.setCellValue(projectName + "-" + message);
            cellHead.setCellStyle(getColumnTopStyle(workbook));

            //表头对应数据查询字段
            XSSFRow rowTitle = sheet.createRow(columnIndex++);
            ArrayList<String> keyList = new ArrayList<>();
            List<String> titleList = Arrays.asList("importdate", "importdates", "weeknum");
            sheet.setColumnWidth(titleIndex, 2500);
            XSSFCell cell0 = rowTitle.createCell(titleIndex++);
            cell0.setCellStyle(cellStyleTitle);
            cell0.setCellValue("Exfactory");
            keyList.add("importdate");
            sheet.setColumnWidth(titleIndex, 2500);
            XSSFCell cell1 = rowTitle.createCell(titleIndex++);
            cell1.setCellStyle(cellStyleTitle);
            cell1.setCellValue("MRP running date");
            keyList.add("importdates");
            sheet.setColumnWidth(titleIndex, 4000);
            XSSFCell cell2 = rowTitle.createCell(titleIndex++);
            cell2.setCellStyle(cellStyleTitle);
            cell2.setCellValue("周别");
            keyList.add("weeknum");
            for (String s : list) {
                sheet.setColumnWidth(titleIndex, 4000);
                XSSFCell cell = rowTitle.createCell(titleIndex++);
                cell.setCellStyle(cellStyleTitle);
                cell.setCellValue(s);
                keyList.add(s);
            }
//                XSSFCell cellFinish = rowTitle.createCell(titleIndex++);
//                cellFinish.setCellStyle(cellStyleTitle);
//                cellFinish.setCellValue("Total");
            for (Map.Entry<String, HashMap<String, String>> entry : mapEntry.getValue().entrySet()) {
                XSSFRow row = sheet.createRow(columnIndex++);

                HashMap<String, String> value = entry.getValue();
                int rIndex = 0;
                for (String s : keyList) {
                    XSSFCell cell = row.createCell(rIndex++);
                    if (titleList.contains(s)) {
                        cell.setCellValue(value.get(s));
                    } else {
                        cell.setCellValue(Double.valueOf(value.get(s) == null ? "0" : value.get(s)));
                    }
                    cell.setCellStyle(cellStyle);
                }


//                    XSSFRow rowTotal = sheet.createRow(columnIndexTotal++);
//                    int rIndexTotal = 3;
//                    for (String s : keyList) {
//                        for (int i=3;i<columnIndexTotal;i++){
//                            XSSFRow row1 = sheet.getRow(i);
//                            XSSFRow row2 = sheet.getRow(i+1);
//                            XSSFCell cell = rowTotal.createCell(rIndexTotal++);
//                            Double aDouble = 0.0;
//                            Double bDouble = 0.0;
//                            if (titleList.contains(s)){
//                                cell.setCellValue(value.get(s));
//                            }else {
//                                if (row1.getCell(i).getCellType()==0){
//                                    aDouble = Double.valueOf(row1.getCell(i).getNumericCellValue());
//                                    //aDouble = Double.valueOf(row1.getCell(i).getStringCellValue()==null?"0":row1.getCell(i).getStringCellValue());
//                                }
//                                if (row2.getCell(i+1).getCellType()==0){
//                                    bDouble = Double.valueOf(row2.getCell(i+1).getNumericCellValue());
//                                    //aDouble = Double.valueOf(row2.getCell(i+1).getStringCellValue()==null?"0":row1.getCell(i+1).getStringCellValue());
//                                }
//                                cell.setCellValue(bDouble - aDouble);
//                            }
//                            cell.setCellStyle(cellStyle);
//                        }
//                    }

                int totalCell = sheet.getRow(1).getLastCellNum();// 获取第2行的数量
                int cellNum = sheet.getPhysicalNumberOfRows(); //当前第几行了
                XSSFCell cellTotal = row.createCell(totalCell);
                String colString = CellReference.convertNumToColString(totalCell - 1); //长度转成ABC列
                String sumString = "SUM(" + "D" + +cellNum + ":" + colString + cellNum + ")";
                cellTotal.setCellFormula(sumString);
                //cellTotal.setCellStyle(cellStyle);
            }
            if ("total".equals(mapEntry.getKey())) {
                int a = sheet.getPhysicalNumberOfRows(); //获取行
                int columnIndexTotal = a + 1;

                int b = sheet.getRow(1).getLastCellNum();//获取列
                int A = 0;
                int B = 0;
                for (int j = a - 4; j >= 0; j--) {
                    XSSFRow rowTotal = sheet.createRow(columnIndexTotal++);
                    A = a - j;
                    B = A - 1;
                    int rIndexTotal = 3;
                    //放置周别相减的表头
                    XSSFCell cellsss = rowTotal.createCell(rIndexTotal - 1);
                    String colString1 = CellReference.convertNumToColString(2); //长度转成ABC列
                    String sumString1 = colString1 + A + "&" + "\"-\"" + "&" + colString1 + B;
                    cellsss.setCellFormula(sumString1);
                    //放置新日期
                    XSSFCell cellxx = rowTotal.createCell(rIndexTotal - 3);
                    String colString2 = CellReference.convertNumToColString(0); //长度转成ABC列
                    String sumString2 = colString2 + A;
                    cellxx.setCellFormula(sumString2);
                    for (int i = 0; i < b - 2; i++) {
                        XSSFCell cell = rowTotal.createCell(rIndexTotal++);
                        String colString = CellReference.convertNumToColString(rIndexTotal - 1); //长度转成ABC列
                        String sumString = colString + A + "-" + colString + B;
                        cell.setCellFormula(sumString);
                    }
                }
            }


            //计算平均值
//                int avgIndex = 0;
//                int cellNumTotal = sheet.getPhysicalNumberOfRows(); //当前一共多少行
//                XSSFRow rowBottomTitle = sheet.createRow(cellNumTotal);
//                for (String s : keyList) {
//                    XSSFCell cellBottom = rowBottomTitle.createCell(avgIndex++);
//                    String colString = CellReference.convertNumToColString(avgIndex - 1); //长度转成ABC列
//                    String avgString = "AVERAGE(" + colString + "3:" + colString + cellNumTotal + ")";
//                    if (!titleList.contains(s)){
//                        cellBottom.setCellFormula(avgString);
//                    }
//                    //cellBottom.setCellStyle(cellStyle);
//                }
        }
    }

    public void batchData(List<UMaterialExcel> uMaterialExcels,String projectName,XSSFWorkbook workbook,HashMap<String, ArrayList<String>> titleMap,LinkedHashMap<String, LinkedHashMap<String, HashMap<String, String>>> contentDateMap){
        for (UMaterialExcel materialExcel : uMaterialExcels) {
            LinkedHashMap<String, HashMap<String, String>> dMap = contentDateMap.get(materialExcel.getGtkpn());
            if (dMap == null) {
                dMap = new LinkedHashMap<>();
                contentDateMap.put(materialExcel.getGtkpn(), dMap);
            }
            HashMap<String, String> map = dMap.get(materialExcel.getImportdate());
            if (map == null) {
                map = new HashMap<>();
                map.put("importdate", materialExcel.getImportdate());
                map.put("importdates", materialExcel.getImportdates());
                map.put("weeknum", materialExcel.getWeeknum());
                dMap.put(materialExcel.getImportdate(), map);
            }
            map.put(materialExcel.getContentdate(), materialExcel.getContentdata());
            ArrayList<String> titlelist = titleMap.get(materialExcel.getGtkpn());
            if (titlelist == null) {
                titlelist = new ArrayList<>();
                titleMap.put(materialExcel.getGtkpn(), titlelist);
            }
            if (!titlelist.contains(materialExcel.getContentdate())) {
                titlelist.add(materialExcel.getContentdate());
            }
        }

        dataEncapsulation(projectName, workbook, titleMap, contentDateMap);
    }

    /**
     * PDF转Excel操作
     *
     */
    public static void pdf2excel(MultipartFile file,HttpServletResponse response) {
        try {
//            dwg2GeoJson("D:\\oldwar\\CEP-BU4.dwg");
            long old = System.currentTimeMillis();
            InputStream inputStream = file.getInputStream();
            ServletOutputStream os = response.getOutputStream();
            Document doc = new Document(inputStream);//加载源文件数据
            doc.save(os, SaveFormat.Excel);//设置转换文件类型并转换
            os.close();
            long now = System.currentTimeMillis();
            System.out.println("共耗时：" + ((now - old) / 1000.0) + "秒");  //转化用时
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * PDF转Word操作
     *
     */
    public static void pdf2word(MultipartFile file,HttpServletResponse response) {
        try {
            long old = System.currentTimeMillis();
            InputStream inputStream = file.getInputStream();
            ServletOutputStream os = response.getOutputStream();
            Document doc = new Document(inputStream);//加载源文件数据
            doc.save(os, SaveFormat.DocX);//设置转换文件类型并转换
            os.close();
            long now = System.currentTimeMillis();
            System.out.println("共耗时：" + ((now - old) / 1000.0) + "秒");  //转化用时
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * PDF转ppt
     *
     */
    public static void pdf2ppt(MultipartFile file,HttpServletResponse response) {
        try {
            long old = System.currentTimeMillis();
            InputStream inputStream = file.getInputStream();
            ServletOutputStream os = response.getOutputStream();
            Document doc = new Document(inputStream);//加载源文件数据
            doc.save(os, SaveFormat.Pptx);//设置转换文件类型并转换
            os.close();
            long now = System.currentTimeMillis();
            System.out.println("共耗时：" + ((now - old) / 1000.0) + "秒");  //转化用时
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

//    /**
//     * 基于gdal实现dwg转GeoJson
//     * @param dwgPath dwg文件路径
//     * @return geojson
//     */
//    public static String dwg2GeoJson(String dwgPath) {
//        gdal.SetConfigOption("DXF_ENCODING", "UTF-8");
//        //将dwg转成dxf，如果源文件为dxf则跳过该步骤
//        String dxfPath = dwg2dxf(dwgPath);
//        //dxf转geojson
//        DataSource ds = ogr.Open(dxfPath, 0);
//        Driver driver = ogr.GetDriverByName("GeoJSON");
//        String geoPath = dxfPath.replace(".dxf", ".geojson");
//        driver.CopyDataSource(ds, geoPath, null).delete();
//        ds.delete();
//
//        //读取geojson
//        cn.hutool.core.io.file.FileReader fileReader = new cn.hutool.core.io.file.FileReader(geoPath,"GBK");
//        return fileReader.readString();
//    }

//    /**
//     * dwg文件转dxf文件
//     * @param dwgPath dwg文件路径
//     * @return 转换后的dxf文件路径
//     */
//    public static String dwg2dxf(String dwgPath) {
//        File dwg = new File(dwgPath);
//        if (dwg.exists()) {
//            if (!dwg.getName().endsWith(".dwg")) {
//                System.out.println("文件格式错误");
//                return "";
//            }
//            String outFile = dwg.getAbsolutePath().replace(".dwg", ".dxf");
//
//            LoadOptions loadOptions = new LoadOptions();
//            loadOptions.setSpecifiedEncoding(CodePages.SimpChinese);
//            CadImage cadImage = (CadImage) Image.load(dwgPath, loadOptions);
//            cadImage.save(outFile);
//            cadImage.close();
//            return outFile;
//        } else {
//            System.out.println("dwg文件不存在");
//            return "";
//        }
//    }
}

总结

提示：这里对文章进行总结：

例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。