SELECT
    cols.COLUMN_NAME AS 字段,
    cols.COLUMN_TYPE AS 数据类型,
	IF(pks.CONSTRAINT_TYPE = 'PRIMARY KEY', 'YES', 'NO') AS 是否为主键,
	IF(idxs.INDEX_NAME IS NOT NULL, 'YES', 'NO') AS 是否为索引,
    cols.IS_NULLABLE AS 是否为空,
    cols.COLUMN_DEFAULT AS 默认值,
    cols.COLUMN_COMMENT AS 备注
FROM
    INFORMATION_SCHEMA.COLUMNS AS cols
LEFT JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kc ON kc.TABLE_SCHEMA = cols.TABLE_SCHEMA AND kc.TABLE_NAME = cols.TABLE_NAME AND kc.COLUMN_NAME = cols.COLUMN_NAME
LEFT JOIN
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS pks ON pks.TABLE_SCHEMA = kc.TABLE_SCHEMA AND pks.TABLE_NAME = kc.TABLE_NAME AND pks.CONSTRAINT_TYPE = 'PRIMARY KEY' AND kc.CONSTRAINT_NAME = pks.CONSTRAINT_NAME
LEFT JOIN
    INFORMATION_SCHEMA.STATISTICS AS idxs ON idxs.TABLE_SCHEMA = cols.TABLE_SCHEMA AND idxs.TABLE_NAME = cols.TABLE_NAME AND idxs.COLUMN_NAME = cols.COLUMN_NAME
WHERE
    cols.TABLE_SCHEMA = 'db' -- 替换为您的数据库名称
    AND cols.TABLE_NAME = 'table' -- 替换为您的表名称
ORDER BY
    cols.ORDINAL_POSITION ASC;  -- 按列在表中的顺序排列

<template>
	<el-button type="primary" @click="updateData" size="small" plain>数据上传</el-button>
	<el-dialog :title="upload.title" v-model="upload.open" width="400px" align-center append-to-body>
		<el-upload
			ref="uploadRef"
			:limit="1"
			accept=".xlsx, .xls"
			:headers="upload.headers"
			:action="upload.url"
			:disabled="upload.isUploading"
			:on-progress="handleFileUploadProgress"
			:on-success="handleFileSuccess"
			:auto-upload="false"
			drag
		>
			<el-icon class="el-icon--upload">
				<upload-filled/>
			</el-icon>
			<div class="el-upload__text">将文件拖到此处，或<em>点击上传</em></div>
			<template #tip>
				<div class="el-upload__tip text-center">
					<span>仅允许导入xls、xlsx格式文件。</span>
				</div>
			</template>
		</el-upload>
		<template #footer>
			<div class="dialog-footer">
				<el-button type="primary" @click="submitFileForm">确 定</el-button>
				<el-button @click="upload.open = false">取 消</el-button>
			</div>
		</template>
	</el-dialog>
</template>
<script setup>

const upload = reactive({
     
	// 是否显示弹出层（用户导入）
	open: false,
	// 弹出层标题（用户导入）
	title: "标题",
	// 是否禁用上传
	isUploading: false,
	// 是否更新已经存在的用户数据
	updateSupport: 0,
	// 设置上传的请求头部
	headers: {
     Authorization: "Bearer " + getToken()},
	// 上传的地址
	url: import.meta.env.VITE_APP_BASE_API + "/yourControllerApi"
});

//数据上传
const updateData = () => {
     
	upload.title = "数据上传";
	upload.open = true;
}

//文件上传中处理
const handleFileUploadProgress = (event, file, fileList) => {
     
	upload.isUploading = true;
	console.log(upload.url)
};

//文件上传成功处理
const handleFileSuccess = (rp, file, fileList) => {
     
	//这里的rp就是后端controller的响应数据
	console.log(rp)
	upload.open = false;
	upload.isUploading = false;
	proxy.$refs["uploadRef"].handleRemove(file);
};
</script>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

@PostMapping("/yourControllerApi")
public AjaxResult importData(@RequestBody MultipartFile file){
   
    return stockMgntService.importData(file);
}

@Service
public class StockMgntServiceImpl implements StockMgntService {
   
	@Override
    public AjaxResult importData(MultipartFile file) {
   
    	try(InputStream is = file.getInputStream();) {
   
            Workbook workbook = WorkbookFactory.create(is);
            int numberOfSheets = workbook.getNumberOfSheets();
            if (numberOfSheets != 1){
   
            	//要处理多个sheet，循环就可以
                System.out.println("只允许有1个Sheet");
                return null;
            }
            //取得第一个sheet
            Sheet sheet = workbook.getSheetAt(0);
            //获取最后一行的索引，但通常会比预想的要多出几行。
            int lastRowNum = sheet.getLastRowNum();
            //循环读取每一行
            for (int rowNum = 0; rowNum < lastRowNum; rowNum++) {
   
                Row rowData = sheet.getRow(rowNum);
                if (rowData != null){
   
                	//可以使用rowData.getLastCellNum()获取最后一列的索引，这里只有6行，是假设现在的excel模板是固定的6行
                    for(int cellNum = 0; cellNum < 6; ++cellNum){
   
                        Cell cell = rowData.getCell(cellNum);
                        if (cell == null){
   continue;}
                        cell.setCellType(CellType.STRING);
                        if (!cell.getStringCellValue().isEmpty()){
   
                            System.out.println(cell.getStringCellValue());
                        }
                        /*
                        利用这个方法可以更好的将所有数据以String获取到
						Cell cell = productCodeRow.getCell(cellNum , Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                		String cellValue = dataFormatter.formatCellValue(cell );
						*/
                    }
                }
            }
        }catch (Exception e) {
   
        	System.out.println(e.getMessage());
            throw new RuntimeException();
        }
	}
}

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

@GetMapping("/yourControllerApi")
public AjaxResult exportData(@RequestParam String moveNo, HttpServletResponse response){
   
    System.out.println(moveNo);
    System.out.println(response);
    return stockMgntService.exportData(moveNo, response);
}

@Service
public class StockMgntServiceImpl implements StockMgntService {
   
@Override
    public AjaxResult exportData(String moveNo, HttpServletResponse response) {
   
    //这里是从后台resources文件夹下读取一个excel模板
	ClassPathResource resource = new ClassPathResource("template/模板.xlsx");
        try(InputStream fis = resource.getInputStream();
            Workbook workbook = WorkbookFactory.create(fis);
            ServletOutputStream os = response.getOutputStream()
        ){
   
        	//这块写入你的数据
        	//往第一个sheet的第一行的第2列和第5列写入数据
        	Sheet sheet = workbook.getSheetAt(0);
            sheet.getRow(0).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue("test");
            sheet.getRow(0).getCell(5, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).setCellValue("test");
            /*......你的操作......*/
            //这块开始配置传输
			response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=template.xlsx");
            response.flushBuffer();
            workbook.write(os);
            os.flush();
            os.close();
        }catch (IOException e) {
   
			throw new RuntimeException(e);
		}
		return null;
	}
}

<template>
	<el-button type="primary" @click="downloadData" size="small" plain>模板下载</el-button>
</template>

<script setup>
const downloadData = () => {
     
	console.log(queryParams.moveNo)
	axios({
     
        url: '/yourControllerApi',
        method: 'get',
        params: {
     moveNo:'0000212132142'},
        responseType: "blob"
    }).then(rp=>{
     
		console.log(rp)
		const blob = new Blob([rp], {
     type: 'application/vnd.ms-excel'});
		let link = document.createElement('a');
		link.href = URL.createObjectURL(blob);
		link.setAttribute('download', '模板下载.xlsx');
		link.click();
		link = null;
	});
}
</script>