第15篇:数据图层-表格点位——把 Excel 里的一行行坐标,撒成满屏的点

import { Cartesian2, Cartesian3, Color, CustomDataSource, PropertyBag, ConstantProperty } from 'cesium'import * as XLSX from 'xlsx'// 1. 文件 -> 行对象数组async function readTable(file: File) {  const workbook = XLSX.read(await file.arrayBuffer(), { cellDates: true })  const sheet = workbook.Sheets[workbook.SheetNames[0]]      // 改这里：默认取第一个工作表  return XLSX.utils.sheet_to_json(sheet, { defval: null, raw: false }) as Record<string, string>[]}// 2. 行 -> Entityfunction addPoints(viewer: any, rows: Record<string, string>[]) {  const source = new CustomDataSource('站点表')              // 改这里：图层名  for (const row of rows) {    const lon = Number(row['经度'])                          // 改这里：字段名    const lat = Number(row['纬度'])    // 坐标不合法就跳过，不报错    if (!Number.isFinite(lon) || !Number.isFinite(lat)) continue    if (lon < -180 || lon > 180 || lat < -90 || lat > 90) continue    source.entities.add({      name: String(row['名称'] ?? '未命名'),      position: Cartesian3.fromDegrees(lon, lat),      point: { pixelSize: 10, color: Color.fromHsl(0.55, 0.68, 0.58, 1), outlineColor: Color.WHITE, outlineWidth: 1 },      label: { text: String(row['名称'] ?? ''), font: '12px sans-serif', fillColor: Color.WHITE, pixelOffset: new Cartesian2(0, -18) },      // 每一列都塞进属性包，点选时原样取回      properties: new PropertyBag(        Object.fromEntries(Object.entries(row).map(([k, v]) => [k, new ConstantProperty(String(v))]))      )    })  }  viewer.dataSources.add(source)  viewer.flyTo(source)  return source}

import * as XLSX from 'xlsx'async function parseTable(file: File) {  const workbook = XLSX.read(await file.arrayBuffer(), { cellDates: true })  return workbook.SheetNames.map((name) => {    const sheet = workbook.Sheets[name]    // 数据区：第一行当表头，每行转成一个对象    const rows = XLSX.utils.sheet_to_json(sheet, { defval: null, raw: false })    // 表头行：header:1 让它返回"数组的数组"，取第 0 行    const headers = (XLSX.utils.sheet_to_json(sheet, { header: 1, defval: null })[0] ?? [])      .map((v: unknown) => String(v)).filter(Boolean)    return { name, rows, headers }  }).filter((s) => s.rows.length > 0 && s.headers.length > 0)}

functionguessField(names: string[], patterns: RegExp[]): string{  return names.find((name) => patterns.some((p) => p.test(name))) ?? names[0] ?? ''}// 按顺序试，命中第一个就停const longitudeField = guessField(headers, [/经度/i, /longitude/i, /^lon/i, /x坐标/i])const latitudeField  = guessField(headers, [/纬度/i, /latitude/i, /^lat / ^lng{sheet.name}`)sheet.rows.forEach((row, index) => {  const longitude = Number(row[sheet.longitudeField])  const latitude  = Number(row[sheet.latitudeField])  // 数值校验：非数字、越界，一律静默跳过  if(!Number.isFinite(longitude) || !Number.isFinite(latitude)      || longitude < -180 || longitude > 180 || latitude < -90 || latitude > 90) return  // 每一列都包一层 ConstantProperty：告诉 Cesium "这个值不会随时间变"  const values = Object.fromEntries(    Object.entries(row).map(([key, value]) => [key, new ConstantProperty(String(value))])  )  const label = sheet.labelField ? String(row[sheet.labelField] ?? '') : ''  source.entities.add({    name: label || `站点表 - {index + 1}`,    position: Cartesian3.fromDegrees(longitude, latitude),   // 注意：没传第三参数 = 高度 0    point: { pixelSize: 10, color: layerColor, outlineColor: Color.WHITE, outlineWidth: 1 },    label: label ? {      text: label, font: '12px sans-serif', fillColor: Color.WHITE,      show: new ConstantProperty(true),      pixelOffset: new Cartesian2(0, -18),                    // 往上抬 18px，别压住点    }: undefined,    properties: new PropertyBag(values)                        // 原始表格字段全留在这  })})viewer.dataSources.add(source)void viewer.flyTo(source)

// ① 眼睛：整层显隐function toggleLayer(layer: PointLayer) {  layer.dataSource.show = !layer.visible  // ...同步 UI 状态}// ② 标：只开关文字，点还在function toggleLabels(layer: PointLayer) {  const labelsVisible = !layer.labelsVisible  layer.dataSource.entities.values.forEach((entity) => {    if (entity.label) entity.label.show = new ConstantProperty(labelsVisible)  })  // ...同步 UI 状态}// ③ 跳：飞过去function flyToLayer(layer: PointLayer) { void viewer?.flyTo(layer.dataSource) }// ④ ✕：移除图层function removeLayer(layer: PointLayer) {  viewer?.dataSources.remove(layer.dataSource, true)  layers.value = layers.value.filter((item) => item !== layer)  selectedInfo.value = null}

function handleEntityPick(position: Cartesian2) {  if (!viewer || disposed || viewer.isDestroyed()) return  const entity = viewer.scene.pick(position)?.id as Entity | undefined  if (!entity?.properties) { selectedInfo.value = null; return }  // 属性是"随时间取值"的，要传一个时间进去；静态数据传当前时刻即可  const values = entity.properties.getValue(JulianDate.now()) as Record<string, unknown> | undefined  selectedInfo.value = {    name: entity.name || '表格记录',    properties: Object.entries(values ?? {}).map(([k, v]) => [k, typeof v === 'object' ? JSON.stringify(v) : String(v)])  }}// 挂载clickHandler = new ScreenSpaceEventHandler(viewer.scene.canvas)clickHandler.setInputAction(  (movement: ScreenSpaceEventHandler.PositionedEvent) => handleEntityPick(movement.position),  ScreenSpaceEventType.LEFT_CLICK)

onBeforeUnmount(() => {  disposed = true  clickHandler?.destroy()                                          // ① 事件句柄  layers.value.forEach((l) => viewer?.dataSources.remove(l.dataSource, true))  // ② 各图层（带 destroy）  destroyScene(viewer)                                             // ③ 场景本身  viewer = undefined})

<script setup lang="ts">import { onBeforeUnmount, onMounted, ref, shallowRef } from 'vue'import { Cartesian2, Cartesian3, Color, ConstantProperty, CustomDataSource,         JulianDate, PropertyBag, ScreenSpaceEventHandler, ScreenSpaceEventType,         type Entity, type Viewer } from 'cesium'import * as XLSX from 'xlsx'import { createMapScene, destroyScene, loadBingImagery, type SceneCallbacks } from '../../lib/cesium-scene'type CellValue = string | number | boolean | nulltype TableRow = Record<string, CellValue>type ParsedSheet = {  name: string; rows: TableRow[]; headers: string[]  longitudeField: string; latitudeField: string; labelField: string}type PointLayer = {  id: number; name: string; format: string; featureCount: number  visible: boolean; labelsVisible: boolean; dataSource: CustomDataSource}const container = ref<HTMLElement | null>(null)const layers = shallowRef<PointLayer[]>([])const pendingSheets = ref<ParsedSheet[]>([])const selectedSheetNames = ref<string[]>([])const pendingFileName = ref('')const errorMessage = ref('')const loading = ref(false)const selectedInfo = ref<{ name: string; properties: Array<[string, string]> } | null>(null)let viewer: Viewer | undefinedlet clickHandler: ScreenSpaceEventHandler | undefinedlet layerSequence = 0let disposed = falseconst sceneCallbacks: SceneCallbacks = {  onStatus: (message) => { /* 顶部遮罩提示 */ },  onBasemapReady: () => { /* 底图就绪，清掉提示 */ }}const formatCell = (v: CellValue) => (v === null || v === undefined ? '' : String(v))/** 表头推断：按顺序试正则，一个没中就退到第一列 */functionguessField(names: string[], patterns: RegExp[]): string{  return names.find((n) => patterns.some((p) => p.test(n))) ?? names[0] ?? ''}async function parseTable(file: File) {  const workbook = XLSX.read(await file.arrayBuffer(), { cellDates: true })  const sheets = workbook.SheetNames.map((name) => {    const sheet = workbook.Sheets[name]    const rows = XLSX.utils.sheet_to_json<TableRow>(sheet, { defval: null, raw: false })    const rawHeaders = XLSX.utils.sheet_to_json<CellValue[]>(sheet, { header: 1, defval: null })[0] ?? []    const headers = rawHeaders.map(formatCell).filter(Boolean)    return {      name, rows, headers,      longitudeField: guessField(headers, [/经度/i, /longitude/i, /^lon/i, /x坐标/i]),      latitudeField:  guessField(headers, [/纬度/i, /latitude/i, /^lat{invalid.name}"选择经度和纬度字段`; return }  let firstDataSource: CustomDataSource | undefined  selectedSheets.forEach((sheet, sheetIndex) => {    const layerName = `{sheet.name}`    const source = new CustomDataSource(layerName)    const layerColor = Color.fromHsl((Math.random() + sheetIndex * 0.61803398875) % 1, 0.68, 0.58, 1)    sheet.rows.forEach((row, index) => {      const longitude = Number(row[sheet.longitudeField])      const latitude  = Number(row[sheet.latitudeField])      if (!Number.isFinite(longitude) || !Number.isFinite(latitude)          || longitude < -180 || longitude > 180 || latitude < -90 || latitude > 90) return      const values = Object.fromEntries(        Object.entries(row).map(([k, v]) => [k, new ConstantProperty(formatCell(v))])      )      const label = sheet.labelField ? formatCell(row[sheet.labelField]) : ''      source.entities.add({        name: label || `{index + 1}`,        position: Cartesian3.fromDegrees(longitude, latitude),        point: { pixelSize: 10, color: layerColor, outlineColor: Color.WHITE, outlineWidth: 1 },        label: label ? { text: label, font: '12px sans-serif', fillColor: Color.WHITE,                         show: new ConstantProperty(true), pixelOffset: new Cartesian2(0, -18) } : undefined,        properties: new PropertyBag(values)      })    })    if (source.entities.values.length > 0) {      viewer?.dataSources.add(source)      layers.value = [...layers.value, { id: ++layerSequence, name: layerName, format: 'XLSX',        featureCount: source.entities.values.length, visible: true, labelsVisible: true, dataSource: source }]      firstDataSource ??= source    }  })  if (!firstDataSource) { errorMessage.value = '所选工作表没有找到有效的经纬度记录'; return }  void viewer.flyTo(firstDataSource)  pendingSheets.value = []; selectedSheetNames.value = []; pendingFileName.value = ''}function handleEntityPick(position: Cartesian2) {  if (!viewer || disposed || viewer.isDestroyed()) return  const entity = viewer.scene.pick(position)?.id as Entity | undefined  if (!entity?.properties) { selectedInfo.value = null; return }  const values = entity.properties.getValue(JulianDate.now()) as Record<string, unknown> | undefined  selectedInfo.value = {    name: entity.name || '表格记录',    properties: Object.entries(values ?? {}).map(([k, v]) => [k, typeof v === 'object' ? JSON.stringify(v) : String(v)])  }}onMounted(() => {  if (!container.value) return  try {    viewer = createMapScene(container.value, sceneCallbacks)    loadBingImagery(viewer, sceneCallbacks)    clickHandler = new ScreenSpaceEventHandler(viewer.scene.canvas)    clickHandler.setInputAction(      (movement: ScreenSpaceEventHandler.PositionedEvent) => handleEntityPick(movement.position),      ScreenSpaceEventType.LEFT_CLICK    )  } catch (error) { errorMessage.value = String(error) }})onBeforeUnmount(() => {  disposed = true  clickHandler?.destroy()  layers.value.forEach((l) => viewer?.dataSources.remove(l.dataSource, true))  destroyScene(viewer)  viewer = undefined})</script>

// encode_cell 拼出 "A2" 这样的单元格地址，直接取 SheetJS 的原始单元格对象const ref = XLSX.utils.encode_cell({ r: rowIndex + 1, c: colIndex })  // +1 跳过表头行const cell = sheet[ref]// 实测 cell = {"t":"n","v":1234567890123,"w":"1.23457E+12"}//   t = 类型（n 数字 / s 字符串 / d 日期）//   v = 原始值   ← 我们要的就是它//   w = 格式化后的显示值 ← 被科学计数法坑的就是它const rawValue = cell?.v