1802 lines
91 KiB
C#
1802 lines
91 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.IO;
|
||
using System.Windows.Forms;
|
||
using System.Data.SQLite;
|
||
using NPOI.SS.UserModel;
|
||
using NPOI.XSSF.UserModel;
|
||
|
||
namespace 成套报价软件
|
||
{
|
||
/// <summary>
|
||
/// 导入报价 的交互逻辑。
|
||
/// 从甲方客户报价表 xlsx(含"屏柜汇总表"+"屏柜分项表")自动识别图号/箱号/元件,
|
||
/// 预览后导入到当前项目的报价库。
|
||
/// </summary>
|
||
public partial class 导入报价
|
||
{
|
||
// ====== 字段 ======
|
||
|
||
private string _projectId;
|
||
private string _dbPath;
|
||
private string _connString;
|
||
|
||
// 解析结果:图号→箱号→元件 层级数据
|
||
private List<图号数据> _图号列表 = new List<图号数据>();
|
||
/// <summary>汇总表读到的(图号名, (单位, 数量))列表，按顺序和分项表箱号配对。</summary>
|
||
private List<KeyValuePair<string, KeyValuePair<string, string>>> _汇总表箱号数据;
|
||
|
||
// 预览 DataTable
|
||
private DataTable _图号表;
|
||
private DataTable _箱号表;
|
||
private DataTable _元件表;
|
||
|
||
// 图号表名(与主窗口一致)
|
||
private const string TuhaoTableName = "advancedDataGridView9Table";
|
||
|
||
// ====== 构造函数 ======
|
||
|
||
// SharpDevelop 设计器需要无参数构造函数；运行时由主窗口调用带参数版本。
|
||
public 导入报价()
|
||
{
|
||
InitializeComponent();
|
||
}
|
||
|
||
public 导入报价(string projectId, string dbPath)
|
||
{
|
||
InitializeComponent();
|
||
_projectId = projectId;
|
||
_dbPath = dbPath;
|
||
// ★ A2(2026-10-01):原为裸串(无 WAL/超时,并发易锁死)——统一收敛到 数据库连接.串
|
||
_connString = 数据库连接.串(dbPath);
|
||
|
||
// 绑定事件
|
||
this.button1.Click += button1_Click;
|
||
this.roundedButton1.Click += roundedButton1_Click;
|
||
// ★ 新增:下拉框选择变化时自动解析并预览
|
||
this.resizableComboBox1.SelectedIndexChanged += resizableComboBox1_SelectedIndexChanged;
|
||
this.enhancedDataGridView1.SelectionChanged += 图号表_SelectionChanged;
|
||
this.enhancedDataGridView2.SelectionChanged += 箱号表_SelectionChanged;
|
||
// 用户在预览箱号表里编辑"箱名"列后,立即同步回数据模型(切换图号会重建预览行)
|
||
this.enhancedDataGridView2.CellEndEdit += 箱号表_CellEndEdit;
|
||
|
||
// 初始化预览 DataTable(列结构与主窗口报价表一致)
|
||
InitPreviewTables();
|
||
}
|
||
|
||
// ====== 事件 ======
|
||
|
||
/// <summary>下拉框选择变化 → 自动解析选中的 xlsx → 预览表格(不导入数据库)。</summary>
|
||
private void resizableComboBox1_SelectedIndexChanged(object sender, EventArgs e)
|
||
{
|
||
string xlsxPath = this.resizableComboBox1.SelectedItem as string;
|
||
if (string.IsNullOrWhiteSpace(xlsxPath) || !File.Exists(xlsxPath)) return;
|
||
try
|
||
{
|
||
string parseErr;
|
||
ParseAndPreview(xlsxPath, out parseErr);
|
||
if (!string.IsNullOrEmpty(parseErr))
|
||
{
|
||
MessageBox.Show(this, parseErr, "导入报价预览",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show(this, "解析报价表失败:\n" + ex.Message,
|
||
"导入报价预览", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
/// <summary>"选择..."按钮:选择 xlsx 文件,路径加入下拉框并选中(触发解析预览)。</summary>
|
||
private void roundedButton1_Click(object sender, EventArgs e)
|
||
{
|
||
using (var dlg = new OpenFileDialog())
|
||
{
|
||
dlg.Filter = "Excel 文件|*.xlsx|所有文件|*.*";
|
||
dlg.Title = "选择甲方客户报价表";
|
||
if (dlg.ShowDialog(this) != DialogResult.OK) return;
|
||
// 避免重复添加
|
||
bool found = false;
|
||
foreach (var item in this.resizableComboBox1.Items)
|
||
{
|
||
if (string.Equals(item as string, dlg.FileName, StringComparison.OrdinalIgnoreCase))
|
||
{
|
||
found = true;
|
||
break;
|
||
}
|
||
}
|
||
if (!found) this.resizableComboBox1.Items.Add(dlg.FileName);
|
||
// 设置选中项 → 自动触发 SelectedIndexChanged → 解析预览
|
||
this.resizableComboBox1.SelectedItem = dlg.FileName;
|
||
}
|
||
}
|
||
|
||
/// <summary>"导入报价表"按钮:若预览为空则先解析,然后把预览的数据导入到项目库。</summary>
|
||
private void button1_Click(object sender, EventArgs e)
|
||
{
|
||
// 1) 还没解析过? 先解析预览
|
||
if (_图号列表.Count == 0)
|
||
{
|
||
string xlsxPath = this.resizableComboBox1.SelectedItem as string;
|
||
if (string.IsNullOrWhiteSpace(xlsxPath))
|
||
{
|
||
using (var dlg = new OpenFileDialog())
|
||
{
|
||
dlg.Filter = "Excel 文件|*.xlsx|所有文件|*.*";
|
||
dlg.Title = "选择甲方客户报价表";
|
||
if (dlg.ShowDialog(this) != DialogResult.OK) return;
|
||
xlsxPath = dlg.FileName;
|
||
}
|
||
}
|
||
if (!File.Exists(xlsxPath))
|
||
{
|
||
MessageBox.Show(this, "文件不存在:\n" + xlsxPath, "导入报价",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
string parseErr;
|
||
try
|
||
{
|
||
ParseAndPreview(xlsxPath, out parseErr);
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show(this, "解析报价表失败:\n" + ex.Message + "\n\n" + ex.StackTrace,
|
||
"导入报价", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
return;
|
||
}
|
||
if (_图号列表.Count == 0)
|
||
{
|
||
string msg = "未能从报价表中识别到任何图号数据。\n请确认文件是甲方客户报价表(含\"屏柜汇总表\")。";
|
||
if (!string.IsNullOrEmpty(parseErr)) msg = parseErr;
|
||
MessageBox.Show(this, msg, "导入报价",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
}
|
||
|
||
// 2) 确认导入
|
||
int totalXh = 0, totalYj = 0;
|
||
foreach (var th in _图号列表)
|
||
{
|
||
totalXh += th.箱号列表.Count;
|
||
foreach (var xh in th.箱号列表) totalYj += xh.元件列表.Count;
|
||
}
|
||
string confirm = "即将导入当前预览数据到项目:\n" +
|
||
" 图号 " + _图号列表.Count + " 个\n" +
|
||
" 箱号 " + totalXh + " 个\n" +
|
||
" 元件 " + totalYj + " 条\n\n" +
|
||
"确认要导入吗?";
|
||
if (MessageBox.Show(this, confirm, "导入报价",
|
||
MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK) return;
|
||
|
||
// 3) 导入数据库
|
||
try
|
||
{
|
||
int thCount = 0, xhCount = 0, yjCount = 0;
|
||
ImportToDatabase(out thCount, out xhCount, out yjCount);
|
||
|
||
string msg = "导入完成!\n图号 " + thCount + " 个,箱号 " + xhCount + " 个,元件 " + yjCount + " 条。";
|
||
MessageBox.Show(this, msg, "导入报价",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
|
||
this.DialogResult = DialogResult.OK;
|
||
this.Close();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
MessageBox.Show(this, "导入失败:\n" + ex.Message + "\n\n" + ex.StackTrace,
|
||
"导入报价", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
/// <summary>AI/接口传入的映射字典 → 汇总列映射。键: seq/cabinet/name/model/spec/unit/qty(0基列号,-1=无)。</summary>
|
||
private static 汇总列映射 字典转汇总映射(Dictionary<string, object> d)
|
||
{
|
||
var m = new 汇总列映射();
|
||
if (d == null) return m;
|
||
Func<string, int, int> 取 = (k, def) =>
|
||
{
|
||
object v;
|
||
if (!d.TryGetValue(k, out v) || v == null) return def;
|
||
try { return Convert.ToInt32(v); } catch { return def; }
|
||
};
|
||
m.序号 = 取("seq", m.序号);
|
||
m.柜号 = 取("cabinet", m.柜号);
|
||
m.名称 = 取("name", m.名称);
|
||
m.型号 = 取("model", m.型号);
|
||
m.规格 = 取("spec", -1); // 未明确给出=无规格列
|
||
m.单位 = 取("unit", m.单位);
|
||
m.数量 = 取("qty", m.数量);
|
||
return m;
|
||
}
|
||
|
||
/// <summary>AI/接口传入的映射字典 → 分项列映射。键: seq/name/spec/unit/qty/price/amount/vendor。</summary>
|
||
private static 分项列映射 字典转分项映射(Dictionary<string, object> d)
|
||
{
|
||
var m = new 分项列映射();
|
||
if (d == null) return m;
|
||
Func<string, int, int> 取 = (k, def) =>
|
||
{
|
||
object v;
|
||
if (!d.TryGetValue(k, out v) || v == null) return def;
|
||
try { return Convert.ToInt32(v); } catch { return def; }
|
||
};
|
||
m.序号 = 取("seq", m.序号);
|
||
m.元件名称 = 取("name", m.元件名称);
|
||
m.型号规格 = 取("spec", m.型号规格);
|
||
m.单位 = 取("unit", m.单位);
|
||
m.数量 = 取("qty", m.数量);
|
||
m.单价 = 取("price", m.单价);
|
||
m.金额 = 取("amount", m.金额);
|
||
m.厂家 = 取("vendor", m.厂家);
|
||
return m;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 解析 xlsx 并填充到预览表(不导入数据库)。
|
||
/// 返回 null 表示成功,否则返回错误/警告信息。
|
||
/// ★映射参数(AI 看 /api/excelgrid 网格后确定): summarySheet/detailSheet=sheet名,
|
||
/// summary/detail=列映射字典;为 null 时自动识别表头(回退标准布局)。
|
||
/// </summary>
|
||
/// <summary>导入调试日志(logs\导入调试.log)。</summary>
|
||
private static void 导入日志(string msg)
|
||
{
|
||
try
|
||
{
|
||
string dir = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "logs");
|
||
System.IO.Directory.CreateDirectory(dir);
|
||
System.IO.File.AppendAllText(System.IO.Path.Combine(dir, "导入调试.log"),
|
||
DateTime.Now.ToString("HH:mm:ss.fff") + " " + msg + System.Environment.NewLine);
|
||
}
|
||
catch { }
|
||
}
|
||
|
||
private void ParseAndPreview(string xlsxPath, out string parseErr,
|
||
string 汇总sheet名 = null, string 分项sheet名 = null,
|
||
汇总列映射 汇总映射 = null, 分项列映射 分项映射 = null)
|
||
{
|
||
parseErr = null;
|
||
_图号列表.Clear();
|
||
using (var fs = new FileStream(xlsxPath, FileMode.Open, FileAccess.Read))
|
||
{
|
||
IWorkbook workbook = new XSSFWorkbook(fs);
|
||
// ★ 格式自动识别:正泰识图软件导出(设备汇总表+设备明细表) → 走专用解析;
|
||
// 否则按甲方报价表(屏柜汇总表+屏柜分项表)解析
|
||
if (workbook.GetSheet("设备汇总表") != null || workbook.GetSheet("设备明细表") != null)
|
||
{
|
||
图号名默认 = System.IO.Path.GetFileNameWithoutExtension(xlsxPath).Trim(); // ★文件名首尾空格必须去掉:带空格名会造成重复建表/查询失配
|
||
Parse设备汇总表(workbook);
|
||
Parse设备明细表(workbook);
|
||
}
|
||
else
|
||
{
|
||
Parse汇总表(workbook, 汇总映射, 汇总sheet名);
|
||
Parse分项表(workbook, 分项映射, 分项sheet名);
|
||
}
|
||
}
|
||
// ★ 按柜号名匹配（双方统一去掉"柜号："前缀再比较）
|
||
if (_汇总表箱号数据 != null && _汇总表箱号数据.Count > 0)
|
||
{
|
||
foreach (var th in _图号列表)
|
||
{
|
||
foreach (var xh in th.箱号列表)
|
||
{
|
||
string xhKey = xh.箱号.Trim();
|
||
if (xhKey.StartsWith("柜号：")) xhKey = xhKey.Substring(3).Trim();
|
||
else if (xhKey.StartsWith("柜号:")) xhKey = xhKey.Substring(3).Trim();
|
||
foreach (var entry in _汇总表箱号数据)
|
||
{
|
||
if (entry.Key == xhKey)
|
||
{
|
||
if (!string.IsNullOrWhiteSpace(entry.Value.Key)) xh.单位 = entry.Value.Key;
|
||
if (!string.IsNullOrWhiteSpace(entry.Value.Value)) xh.数量 = entry.Value.Value;
|
||
break;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// 剔除没有任何箱号的空图号(通常是被误识别的非报价信息)
|
||
for (int i = _图号列表.Count - 1; i >= 0; i--)
|
||
{
|
||
if (_图号列表[i].箱号列表.Count == 0) _图号列表.RemoveAt(i);
|
||
}
|
||
ShowPreview();
|
||
if (_图号列表.Count == 0)
|
||
{
|
||
parseErr = "未能从报价表中识别到任何图号/箱号数据。\n请确认文件是甲方客户报价表(含\"屏柜汇总表\")。";
|
||
}
|
||
}
|
||
|
||
// ====== 解析 xlsx ======
|
||
// 严格校验:只识别真实的报价数据行,过滤表头/说明/合计/非报价文字
|
||
|
||
// ---------- 表头驱动列识别（★不依赖固定列位，适应多种甲方报价文件） ----------
|
||
// 原则：先在 sheet 前部找表头行（含"序号"+"柜号"），按表头文字确定各列含义；
|
||
// 找不到表头行才回退到标准布局（历史文件兼容）。
|
||
|
||
/// <summary>屏柜汇总表列位映射（-1=该表没有此列）。默认=标准布局 A序号|B柜号|C箱柜名称|D箱柜型号|E规格|F单位|G数量。</summary>
|
||
private class 汇总列映射
|
||
{
|
||
public int 序号 = 0, 柜号 = 1, 名称 = 2, 型号 = 3, 规格 = -1, 单位 = 4, 数量 = 5, 单价 = -1;
|
||
}
|
||
|
||
/// <summary>屏柜分项表元件行列位映射。默认=标准布局 A序号|B元件名称|C型号规格|D单位|E数量|F单价|G金额|H生产厂家。</summary>
|
||
private class 分项列映射
|
||
{
|
||
public int 序号 = 0, 元件名称 = 1, 型号规格 = 2, 单位 = 3, 数量 = 4, 单价 = 5, 金额 = 6, 厂家 = 7;
|
||
}
|
||
|
||
/// <summary>表头单元格文本 → 汇总表列含义（返回命中列名，null=不认识）。先精确后模糊。</summary>
|
||
private static string 汇总表头命中(string t, 汇总列映射 m, int col)
|
||
{
|
||
// 已被更早精确命中的列不再覆盖
|
||
if (t == "序号") { m.序号 = col; return "序号"; }
|
||
if (t.Contains("柜号")) { m.柜号 = col; return "柜号"; }
|
||
if (t == "箱柜名称" || t == "名称" || t == "柜名") { m.名称 = col; return "名称"; }
|
||
if (t == "箱柜型号" || t == "型号") { m.型号 = col; return "型号"; }
|
||
if (t.Contains("规格") && !t.Contains("型号")) { m.规格 = col; return "规格"; }
|
||
if (t == "单位") { m.单位 = col; return "单位"; }
|
||
if (t.Contains("数量")) { m.数量 = col; return "数量"; }
|
||
if (t.Contains("单价")) { m.单价 = col; return "单价"; }
|
||
return null;
|
||
}
|
||
|
||
/// <summary>在 sheet 前部找汇总表表头行（某行同时出现"序号"与"柜号"），按文字填充列映射。
|
||
/// 找到返回表头行号；找不到返回 -1（保持默认映射）。</summary>
|
||
private static int 识别汇总表头(ISheet sheet, 汇总列映射 m)
|
||
{
|
||
int 上限 = Math.Min(sheet.LastRowNum, 40);
|
||
for (int i = 0; i <= 上限; i++)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
if (row == null) continue;
|
||
// 该行是否同时有"序号"和"柜号"字样
|
||
bool 有序号 = false, 有柜号 = false;
|
||
for (int c = row.FirstCellNum; c < row.LastCellNum; c++)
|
||
{
|
||
string t = GetCellString(row, c).Trim();
|
||
if (t == "序号") 有序号 = true;
|
||
if (t.Contains("柜号")) 有柜号 = true;
|
||
}
|
||
if (!有序号 || !有柜号) continue;
|
||
// 命中表头行 → 按文字逐列识别
|
||
for (int c = row.FirstCellNum; c < row.LastCellNum; c++)
|
||
{
|
||
string t = GetCellString(row, c).Trim();
|
||
if (t.Length == 0) continue;
|
||
汇总表头命中(t, m, c);
|
||
}
|
||
return i;
|
||
}
|
||
return -1;
|
||
}
|
||
|
||
/// <summary>在 sheet 前部找分项表元件表头行（某行同时出现"序号"与"元件名称"），填充列映射。</summary>
|
||
private static int 识别分项表头(ISheet sheet, 分项列映射 m)
|
||
{
|
||
int 上限 = Math.Min(sheet.LastRowNum, 40);
|
||
for (int i = 0; i <= 上限; i++)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
if (row == null) continue;
|
||
bool 有序号 = false, 有元件名 = false;
|
||
for (int c = row.FirstCellNum; c < row.LastCellNum; c++)
|
||
{
|
||
string t = GetCellString(row, c).Trim();
|
||
if (t == "序号") 有序号 = true;
|
||
if (t.Contains("元件名称") || t == "名称") 有元件名 = true;
|
||
}
|
||
if (!有序号 || !有元件名) continue;
|
||
for (int c = row.FirstCellNum; c < row.LastCellNum; c++)
|
||
{
|
||
string t = GetCellString(row, c).Trim();
|
||
if (t.Length == 0) continue;
|
||
if (t == "序号") { m.序号 = c; continue; }
|
||
if (t.Contains("元件名称") || t == "名称") { m.元件名称 = c; continue; }
|
||
if (t.Contains("规格")) { m.型号规格 = c; continue; } // "型号规格"/"规格"都认
|
||
if (t == "单位") { m.单位 = c; continue; }
|
||
if (t.Contains("数量")) { m.数量 = c; continue; }
|
||
if (t.Contains("单价") || t == "表价") { m.单价 = c; continue; }
|
||
if (t.Contains("金额") || t.Contains("总价")) { m.金额 = c; continue; }
|
||
if (t.Contains("厂家") || t.Contains("生产") || t == "品牌") { m.厂家 = c; continue; }
|
||
}
|
||
return i;
|
||
}
|
||
return -1;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 严格识别"屏柜汇总表"的图号区块 + 箱号数据行。
|
||
/// 箱号行:A列是合法序号(数字-数字或纯数字),B列(柜号)非空且非关键字。
|
||
/// 图号行:A列非空、B列为空、且A非关键字/非描述文字。
|
||
/// ★列位优先用外部映射（AI 看表确定，/api/importquote 的 mapping 传入）；
|
||
/// 无外部映射时自动识别表头行；都没有=标准布局。
|
||
/// </summary>
|
||
private void Parse汇总表(IWorkbook workbook, 汇总列映射 外部映射 = null, string sheet名 = null)
|
||
{
|
||
ISheet sheet = workbook.GetSheet(string.IsNullOrEmpty(sheet名) ? "屏柜汇总表" : sheet名);
|
||
if (sheet == null) return;
|
||
|
||
// ★ 列位来源优先级：AI 外部映射 > 表头自动识别 > 标准布局
|
||
汇总列映射 map;
|
||
int 表头行;
|
||
if (外部映射 != null)
|
||
{
|
||
map = 外部映射;
|
||
表头行 = -1; // AI 已给定映射，不再跳过任何行
|
||
}
|
||
else
|
||
{
|
||
map = new 汇总列映射();
|
||
表头行 = 识别汇总表头(sheet, map);
|
||
}
|
||
导入日志("[汇总表] 表头行=" + (表头行 + 1) + " (外部映射=" + (外部映射 != null ? "是" : "否") + ") 列映射: 柜号=" + map.柜号 + " 名称=" + map.名称
|
||
+ " 型号=" + map.型号 + " 规格=" + map.规格 + " 单位=" + map.单位 + " 数量=" + map.数量 + " 单价=" + map.单价);
|
||
|
||
// ★ 创建公式求值器：柜号列的 =屏柜分项表!B12 这类公式需要求值才能拿到实际柜号
|
||
IFormulaEvaluator evaluator = null;
|
||
try { evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); }
|
||
catch { }
|
||
|
||
图号数据 curTuhao = null;
|
||
_汇总表箱号数据 = new List<KeyValuePair<string, KeyValuePair<string, string>>>();
|
||
for (int i = 0; i <= sheet.LastRowNum; i++)
|
||
{
|
||
if (i == 表头行) continue; // 表头行自身不再参与数据识别
|
||
IRow row = sheet.GetRow(i);
|
||
if (row == null) continue;
|
||
string A = GetCellString(row, map.序号);
|
||
string B = GetCellString(row, map.柜号);
|
||
|
||
ICell bCell = row.GetCell(map.柜号);
|
||
// 柜号列有时被读取为公式文本/缓存文本"屏柜分项表!B12",必须按引用读取真实柜号
|
||
if (string.IsNullOrWhiteSpace(B) || B.IndexOf("屏柜分项表!", StringComparison.OrdinalIgnoreCase) >= 0)
|
||
{
|
||
System.IO.File.AppendAllText(System.IO.Path.Combine(
|
||
System.AppDomain.CurrentDomain.BaseDirectory, "公式求值_诊断.log"),
|
||
string.Format("行{0}: 柜号列{1}类型={2} 原值=[{3}] A=[{4}]" + System.Environment.NewLine,
|
||
i + 1, map.柜号, bCell != null ? bCell.CellType.ToString() : "NULL", B, A));
|
||
|
||
// 先按文本引用解析（兼容 NPOI 把公式返回成普通字符串的情况）
|
||
string refValue = ResolveFormulaReferenceText(workbook, B);
|
||
if (!string.IsNullOrWhiteSpace(refValue)) B = refValue;
|
||
|
||
// 如果 B 原来为空且确实是 Formula，再尝试 NPOI 公式求值
|
||
if (string.IsNullOrWhiteSpace(B) && bCell != null && bCell.CellType == CellType.Formula)
|
||
{
|
||
try
|
||
{
|
||
if (evaluator != null)
|
||
{
|
||
var evResult = evaluator.Evaluate(bCell);
|
||
if (evResult != null && evResult.CellType == CellType.String)
|
||
B = (evResult.StringValue ?? "").Trim();
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
System.IO.File.AppendAllText(System.IO.Path.Combine(
|
||
System.AppDomain.CurrentDomain.BaseDirectory, "公式求值_诊断.log"),
|
||
"求值异常=" + ex.Message + System.Environment.NewLine);
|
||
}
|
||
}
|
||
|
||
// 最后一层：从 CellFormula 解析
|
||
if (string.IsNullOrWhiteSpace(B) && bCell != null && bCell.CellType == CellType.Formula)
|
||
B = ResolveFormulaReference(workbook, bCell);
|
||
|
||
System.IO.File.AppendAllText(System.IO.Path.Combine(
|
||
System.AppDomain.CurrentDomain.BaseDirectory, "公式求值_诊断.log"),
|
||
" 解析柜号=[" + B + "]" + System.Environment.NewLine);
|
||
}
|
||
|
||
// 空行跳过
|
||
if (string.IsNullOrEmpty(A) && string.IsNullOrEmpty(B)) continue;
|
||
|
||
// ======================================================
|
||
// ① 识别:箱号数据行
|
||
// 条件1: A列是合法序号(如 "1", "1-1") 或 A列为空(部分Excel不填序号)
|
||
// 条件2: B列(柜号)非空且非关键字
|
||
// 条件3: F列(单位)或G列(数量)有值(确认是数据行而非标题)
|
||
// ======================================================
|
||
bool A是合法箱号序号 = IsValidXianghaoSeq(A);
|
||
string F值 = map.单位 >= 0 ? GetCellString(row, map.单位) : "";
|
||
string G值 = map.数量 >= 0 ? GetCellString(row, map.数量) : "";
|
||
bool FG有数据 = !string.IsNullOrWhiteSpace(F值) || !string.IsNullOrWhiteSpace(G值);
|
||
// ★ A列为"0"也是序号（部分Excel序号从0开始或公式残留），或者A空+F/G有值
|
||
bool A列可接受 = A是合法箱号序号 || string.IsNullOrWhiteSpace(A) || A.Trim() == "0";
|
||
// B列此时可能已解析为"柜号：K1/K12"，去掉前缀再过滤关键字
|
||
string B去前缀 = B.Trim();
|
||
if (B去前缀.StartsWith("柜号：")) B去前缀 = B去前缀.Substring(3).Trim();
|
||
else if (B去前缀.StartsWith("柜号:")) B去前缀 = B去前缀.Substring(3).Trim();
|
||
bool 是箱号数据行 = A列可接受 && FG有数据
|
||
&& !string.IsNullOrWhiteSpace(B去前缀) && !IsNonQuoteKeyword(B去前缀);
|
||
if (是箱号数据行)
|
||
{
|
||
// 确保有图号归属
|
||
if (curTuhao == null)
|
||
{
|
||
curTuhao = new 图号数据 { 名称 = "未命名图号" };
|
||
_图号列表.Add(curTuhao);
|
||
}
|
||
|
||
// ★ 记录汇总表的(柜号名, 单位, 数量)。B列读出的是"柜号：XXX"带前缀，
|
||
// 分项表解析出的箱号是"XXX"无前缀，匹配时也要统一去掉前缀
|
||
string cabinetKey = B.Trim();
|
||
if (cabinetKey.StartsWith("柜号：")) cabinetKey = cabinetKey.Substring(3).Trim();
|
||
else if (cabinetKey.StartsWith("柜号:")) cabinetKey = cabinetKey.Substring(3).Trim();
|
||
_汇总表箱号数据.Add(new KeyValuePair<string, KeyValuePair<string, string>>(
|
||
cabinetKey, new KeyValuePair<string, string>(F值, G值)));
|
||
|
||
var xh = new 箱号数据();
|
||
// 去掉"柜号："/"柜号:"前缀,与分项表解析保持一致
|
||
string xhName = B.Trim();
|
||
if (xhName.StartsWith("柜号：")) xhName = xhName.Substring(3).Trim();
|
||
else if (xhName.StartsWith("柜号:")) xhName = xhName.Substring(3).Trim();
|
||
xh.箱号 = xhName;
|
||
// ★ 名称列(箱柜名称/进线柜、PT柜等)= 用户要的「箱名」;
|
||
// 它也可能是公式引用分项表头行,用与型号/规格相同的方式解析出真实名称
|
||
string cRaw = map.名称 >= 0 ? GetCellString(row, map.名称) : "";
|
||
ICell cCell = map.名称 >= 0 ? row.GetCell(map.名称) : null;
|
||
string 箱名值 = ResolveCellOrReference(workbook, cRaw, cCell, evaluator);
|
||
xh.箱名 = 箱名值;
|
||
xh.箱柜名称 = 箱名值; // 沿用旧语义:落库到"箱柜类型"列
|
||
// ★ 型号/规格列也可能是公式引用分项表(读出来是"0"或引用文本)，
|
||
// 用与柜号列相同的方式解析：按引用去分项表读真实值
|
||
string dRaw = map.型号 >= 0 ? GetCellString(row, map.型号) : "";
|
||
string eRaw = map.规格 >= 0 ? GetCellString(row, map.规格) : "";
|
||
ICell dCell = map.型号 >= 0 ? row.GetCell(map.型号) : null;
|
||
ICell eCell = map.规格 >= 0 ? row.GetCell(map.规格) : null;
|
||
xh.型号 = ResolveCellOrReference(workbook, dRaw, dCell, evaluator);
|
||
xh.规格 = ResolveCellOrReference(workbook, eRaw, eCell, evaluator);
|
||
xh.单位 = F值;
|
||
xh.数量 = G值;
|
||
// 单位/数量为空时用合理默认值
|
||
if (string.IsNullOrWhiteSpace(xh.单位)) xh.单位 = "台";
|
||
if (string.IsNullOrWhiteSpace(xh.数量)) xh.数量 = "1";
|
||
// 箱号价格不识别,导入后由主窗口按元件价格自动计算
|
||
xh.单价 = 0m;
|
||
xh.总价 = 0m;
|
||
curTuhao.箱号列表.Add(xh);
|
||
continue;
|
||
}
|
||
|
||
// ======================================================
|
||
// ② 严格判断:图号区块标题行
|
||
// ======================================================
|
||
if (!string.IsNullOrWhiteSpace(A) && string.IsNullOrWhiteSpace(B) && !IsNonQuoteKeyword(A))
|
||
{
|
||
// 额外过滤:封面/标题行(含"公司"/"报价"/"项目"等)不作为图号
|
||
string aTrim = A.Trim();
|
||
if (aTrim.Contains("公司") || aTrim.Contains("报价(汇总)") || aTrim.Contains("报价(明细)")
|
||
|| aTrim.Contains("报价单") || aTrim.StartsWith("项目单位") || aTrim.StartsWith("项目名称")
|
||
|| aTrim.StartsWith("联系人") || aTrim.StartsWith("报价日期") || aTrim.StartsWith("文件编号"))
|
||
continue;
|
||
curTuhao = new 图号数据 { 名称 = aTrim };
|
||
_图号列表.Add(curTuhao);
|
||
continue;
|
||
}
|
||
// 其他情况:表头/合计/非报价文字 → 全部跳过
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 严格识别"屏柜分项表"的图号/箱号/元件行。
|
||
/// - 图号行:A非空B空且A非关键字
|
||
/// - 箱号行:B含"柜号"(全/半角冒号)且能提取到箱号名
|
||
/// - 元件行:A是正整数序号(如 1,2,3...)且B非空非关键字
|
||
/// ★元件行列位优先用外部映射（AI 确定）> 表头自动识别 > 标准布局。
|
||
/// </summary>
|
||
private void Parse分项表(IWorkbook workbook, 分项列映射 外部映射 = null, string sheet名 = null)
|
||
{
|
||
ISheet sheet = workbook.GetSheet(string.IsNullOrEmpty(sheet名) ? "屏柜分项表" : sheet名);
|
||
if (sheet == null) return;
|
||
|
||
分项列映射 m;
|
||
if (外部映射 != null) { m = 外部映射; }
|
||
else { m = new 分项列映射(); 识别分项表头(sheet, m); }
|
||
导入日志("[分项表] (外部映射=" + (外部映射 != null ? "是" : "否") + ") 列映射: 序号=" + m.序号 + " 元件名称=" + m.元件名称
|
||
+ " 型号规格=" + m.型号规格 + " 单位=" + m.单位 + " 数量=" + m.数量 + " 单价=" + m.单价 + " 金额=" + m.金额 + " 厂家=" + m.厂家);
|
||
|
||
图号数据 curTuhao = null;
|
||
箱号数据 curXianghao = null;
|
||
// 同名箱号按出现顺序匹配:记录每个箱号名在 curTuhao.箱号列表中下次匹配的起始索引
|
||
// 这样分项表里第二次出现的"柜号：低压联排"会匹配汇总表里第二个"低压联排"
|
||
var xhMatchIdx = new Dictionary<string, int>();
|
||
|
||
for (int i = 0; i <= sheet.LastRowNum; i++)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
if (row == null) continue;
|
||
string A = GetCellString(row, m.序号);
|
||
string B = GetCellString(row, m.元件名称);
|
||
|
||
// 空行跳过
|
||
if (string.IsNullOrWhiteSpace(A) && string.IsNullOrWhiteSpace(B)) continue;
|
||
|
||
// ======================================================
|
||
// ① 箱号标题行:B列包含"柜号"(全半角冒号都识别)
|
||
// ======================================================
|
||
if (!string.IsNullOrWhiteSpace(B) && B.IndexOf("柜号") >= 0)
|
||
{
|
||
string xhName = B.Replace("柜号：", "").Replace("柜号:", "").Trim();
|
||
if (string.IsNullOrWhiteSpace(xhName)) continue;
|
||
string xhModel = GetCellString(row, m.型号规格 >= 0 ? m.型号规格 + 1 : 3); // 箱号头行:型号标签的值通常在"型号规格列+1"(C标签→D值);无映射时=D列
|
||
string xhSpec = GetCellString(row, 8); // I列=规格
|
||
// 箱名标签行列位随表样漂移(这份表在 H=index7,老表在 G=index6):从"型号规格列+2"起向右找第一个非空
|
||
string xhLabel = "";
|
||
int 标签起 = (m.型号规格 >= 0 ? m.型号规格 + 2 : 5);
|
||
for (int c = 标签起; c <= Math.Min(8, row.LastCellNum - 1); c++)
|
||
{
|
||
string v = GetCellString(row, c);
|
||
if (!string.IsNullOrWhiteSpace(v)) { xhLabel = v; break; }
|
||
}
|
||
|
||
curXianghao = null;
|
||
if (curTuhao != null)
|
||
{
|
||
// ★ 按顺序匹配同名箱号:汇总表里可能有两个"低压联排",
|
||
// 分项表里两次出现"柜号：低压联排"应分别匹配第1个、第2个
|
||
int startIdx = 0;
|
||
xhMatchIdx.TryGetValue(xhName, out startIdx);
|
||
int foundIdx = -1;
|
||
for (int xi = startIdx; xi < curTuhao.箱号列表.Count; xi++)
|
||
{
|
||
if (curTuhao.箱号列表[xi].箱号 == xhName)
|
||
{
|
||
foundIdx = xi;
|
||
break;
|
||
}
|
||
}
|
||
if (foundIdx >= 0)
|
||
{
|
||
curXianghao = curTuhao.箱号列表[foundIdx];
|
||
// 汇总表没读到的,用分项表头行的值补齐(只在空时补,不覆盖)
|
||
if (string.IsNullOrWhiteSpace(curXianghao.箱名)) curXianghao.箱名 = xhLabel;
|
||
if (string.IsNullOrWhiteSpace(curXianghao.型号)) curXianghao.型号 = xhModel;
|
||
if (string.IsNullOrWhiteSpace(curXianghao.规格)) curXianghao.规格 = xhSpec;
|
||
// 下次同名箱号从这之后开始找
|
||
xhMatchIdx[xhName] = foundIdx + 1;
|
||
}
|
||
else
|
||
{
|
||
// 汇总表里没找到同名箱号 → 新建一个
|
||
curXianghao = new 箱号数据();
|
||
curXianghao.箱号 = xhName;
|
||
curXianghao.箱名 = xhLabel;
|
||
curXianghao.箱柜名称 = xhLabel;
|
||
curXianghao.型号 = xhModel;
|
||
curXianghao.规格 = xhSpec;
|
||
curTuhao.箱号列表.Add(curXianghao);
|
||
xhMatchIdx[xhName] = curTuhao.箱号列表.Count;
|
||
}
|
||
}
|
||
continue;
|
||
}
|
||
|
||
// ======================================================
|
||
// ② 图号标题行:A非空、B为空、A非关键字
|
||
// ======================================================
|
||
if (!string.IsNullOrWhiteSpace(A) && string.IsNullOrWhiteSpace(B) && !IsNonQuoteKeyword(A))
|
||
{
|
||
// 额外过滤:封面/标题行(含"公司"/"报价"/"项目"等)不作为图号
|
||
string aTrim = A.Trim();
|
||
if (aTrim.Contains("公司") || aTrim.Contains("报价(汇总)") || aTrim.Contains("报价(明细)")
|
||
|| aTrim.Contains("报价单") || aTrim.StartsWith("项目单位") || aTrim.StartsWith("项目名称")
|
||
|| aTrim.StartsWith("联系人") || aTrim.StartsWith("报价日期") || aTrim.StartsWith("文件编号"))
|
||
continue;
|
||
curXianghao = null;
|
||
curTuhao = null;
|
||
foreach (var th in _图号列表)
|
||
{
|
||
if (th.名称 == aTrim) { curTuhao = th; break; }
|
||
}
|
||
if (curTuhao == null)
|
||
{
|
||
curTuhao = new 图号数据 { 名称 = aTrim };
|
||
_图号列表.Add(curTuhao);
|
||
}
|
||
// ★ 切换图号时重置同名箱号匹配索引(每个图号独立计数)
|
||
xhMatchIdx.Clear();
|
||
continue;
|
||
}
|
||
|
||
// ======================================================
|
||
// ③ 元件数据行:A是正整数序号,B非空非关键字
|
||
// ======================================================
|
||
int yjSeq;
|
||
if (int.TryParse(A, out yjSeq) && yjSeq >= 1
|
||
&& !string.IsNullOrWhiteSpace(B) && !IsNonQuoteKeyword(B)
|
||
&& curXianghao != null)
|
||
{
|
||
var yj = new 元件数据();
|
||
yj.元件名称 = B.Trim();
|
||
yj.规格 = m.型号规格 >= 0 ? GetCellString(row, m.型号规格) : "";
|
||
yj.单位 = m.单位 >= 0 ? GetCellString(row, m.单位) : "";
|
||
yj.数量 = m.数量 >= 0 ? GetCellString(row, m.数量) : "";
|
||
yj.单价 = m.单价 >= 0 ? GetCellDecimal(row, m.单价) : 0m;
|
||
yj.总价 = m.金额 >= 0 ? GetCellDecimal(row, m.金额) : 0m;
|
||
yj.品牌 = m.厂家 >= 0 ? GetCellString(row, m.厂家) : "";
|
||
curXianghao.元件列表.Add(yj);
|
||
continue;
|
||
}
|
||
// 其他:表头/说明文字/合计行 → 全部跳过
|
||
}
|
||
}
|
||
|
||
// ====== 正泰识图软件格式(设备汇总表+设备明细表) ======
|
||
|
||
/// <summary>正泰格式无图号列,整份文件作为一个图号导入,图号名默认取文件名。</summary>
|
||
private string 图号名默认 = "导入图纸";
|
||
|
||
/// <summary>
|
||
/// 解析"设备汇总表"(表头第3行:序号|柜号|箱柜名称|箱柜型号|单位|数量|备注|箱柜尺寸|安装方式)。
|
||
/// 每个柜号 = 一个箱号行;全部挂在同一个图号(图号名=文件名)下。
|
||
/// </summary>
|
||
private void Parse设备汇总表(IWorkbook workbook)
|
||
{
|
||
ISheet sheet = workbook.GetSheet("设备汇总表");
|
||
if (sheet == null) return;
|
||
|
||
var th = new 图号数据 { 名称 = 图号名默认 };
|
||
_图号列表.Add(th);
|
||
导入日志("[设备汇总] LastRowNum=" + sheet.LastRowNum + " SheetName=" + sheet.SheetName);
|
||
|
||
for (int i = 0; i <= sheet.LastRowNum; i++)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
if (row == null) continue;
|
||
string 柜号 = GetCellString(row, 1);
|
||
if (i < 8) 导入日志("[设备汇总] 行" + i + " B列=[" + 柜号 + "] B格=" + (row.GetCell(1) == null ? "null" : row.GetCell(1).CellType.ToString()) + " 首格=" + (row.GetCell(0) == null ? "null" : row.GetCell(0).CellType.ToString()));
|
||
if (string.IsNullOrWhiteSpace(柜号)) continue;
|
||
// 跳过表头行(柜号列自身是"柜号")
|
||
if (柜号.Trim() == "柜号") continue;
|
||
|
||
var xh = new 箱号数据
|
||
{
|
||
箱号 = 柜号.Trim(),
|
||
箱柜名称 = GetCellString(row, 2).Trim(), // C=箱柜名称
|
||
型号 = GetCellString(row, 3).Trim(), // D=箱柜型号
|
||
单位 = GetCellString(row, 4).Trim(), // E=单位
|
||
数量 = GetCellString(row, 5).Trim(), // F=数量
|
||
规格 = GetCellString(row, 8).Trim(), // I=箱柜尺寸 → 规格列
|
||
};
|
||
if (string.IsNullOrWhiteSpace(xh.数量)) xh.数量 = "1";
|
||
if (string.IsNullOrWhiteSpace(xh.单位)) xh.单位 = "台";
|
||
th.箱号列表.Add(xh);
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 解析"设备明细表"(表头第3行:序号|柜号|元件名称|型号规格|品牌|单位|数量|表价|备注|物料编码|产地|原始型号)。
|
||
/// 柜号在组首行给出、后续行留空(合并单元格平铺);型号规格可能带中文名称后缀("CA1-100/4300B/40A 断路器")。
|
||
/// </summary>
|
||
private void Parse设备明细表(IWorkbook workbook)
|
||
{
|
||
ISheet sheet = workbook.GetSheet("设备明细表");
|
||
if (sheet == null || _图号列表.Count == 0) return;
|
||
var th = _图号列表[0];
|
||
|
||
箱号数据 curXianghao = null;
|
||
var xhMatchIdx = new Dictionary<string, int>(); // 同名柜号按顺序匹配
|
||
|
||
for (int i = 0; i <= sheet.LastRowNum; i++)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
if (row == null) continue;
|
||
string 柜号 = GetCellString(row, 1).Trim();
|
||
string 规格 = GetCellString(row, 3).Trim(); // D=型号规格
|
||
if (i < 8) 导入日志("[设备明细] 行" + i + " 柜号=[" + 柜号 + "] 规格=[" + 规格 + "]");
|
||
|
||
// 组首行:柜号非空 → 切换当前箱号(跳过表头"柜号"自身)
|
||
if (!string.IsNullOrWhiteSpace(柜号) && 柜号 != "柜号")
|
||
{
|
||
curXianghao = null;
|
||
int startIdx = 0;
|
||
xhMatchIdx.TryGetValue(柜号, out startIdx);
|
||
for (int xi = startIdx; xi < th.箱号列表.Count; xi++)
|
||
{
|
||
if (th.箱号列表[xi].箱号 == 柜号) { curXianghao = th.箱号列表[xi]; xhMatchIdx[柜号] = xi + 1; break; }
|
||
}
|
||
if (curXianghao == null)
|
||
{
|
||
// 明细表有、汇总表没有 → 补建箱号
|
||
curXianghao = new 箱号数据 { 箱号 = 柜号, 单位 = "台", 数量 = "1" };
|
||
th.箱号列表.Add(curXianghao);
|
||
}
|
||
}
|
||
|
||
// 元件行:型号规格非空(表头行"型号规格"跳过)
|
||
if (string.IsNullOrWhiteSpace(规格) || 规格 == "型号规格") continue;
|
||
if (curXianghao == null) continue;
|
||
|
||
string 名称;
|
||
string 纯规格 = 拆规格与名称(规格, out 名称);
|
||
var yj = new 元件数据
|
||
{
|
||
元件名称 = !string.IsNullOrWhiteSpace(GetCellString(row, 2).Trim()) ? GetCellString(row, 2).Trim() : 名称,
|
||
规格 = 纯规格,
|
||
品牌 = GetCellString(row, 4).Trim(), // E=品牌
|
||
单位 = GetCellString(row, 5).Trim(), // F=单位
|
||
数量 = GetCellString(row, 6).Trim(), // G=数量
|
||
单价 = ParseDecimalSafe(GetCellString(row, 7)), // H=表价
|
||
};
|
||
if (string.IsNullOrWhiteSpace(yj.单位)) yj.单位 = "只";
|
||
if (string.IsNullOrWhiteSpace(yj.数量)) yj.数量 = "1";
|
||
curXianghao.元件列表.Add(yj);
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// 拆"CA1-100/4300B/40A 断路器"→ 规格"CA1-100/4300B/40A" + 名称"断路器"。
|
||
/// 规则:取字符串里连续的中文段(≥2字)作名称并从原串剔除;无中文段则整串为规格、名称空。
|
||
/// "DJMB2 220/36 0.3kVA" 这类纯型号不受影响。
|
||
/// </summary>
|
||
private static string 拆规格与名称(string 原始, out string 名称)
|
||
{
|
||
名称 = "";
|
||
if (string.IsNullOrWhiteSpace(原始)) return "";
|
||
// 找最长的连续中文段(允许中间夹"-"如"熔断器-RT18"不常见,保守纯中文)
|
||
int bestStart = -1, bestLen = 0;
|
||
int i = 0;
|
||
while (i < 原始.Length)
|
||
{
|
||
if (原始[i] >= 0x4e00 && 原始[i] <= 0x9fa5)
|
||
{
|
||
int j = i;
|
||
while (j < 原始.Length && 原始[j] >= 0x4e00 && 原始[j] <= 0x9fa5) j++;
|
||
if (j - i > bestLen) { bestStart = i; bestLen = j - i; }
|
||
i = j;
|
||
}
|
||
else i++;
|
||
}
|
||
if (bestLen >= 2)
|
||
{
|
||
名称 = 原始.Substring(bestStart, bestLen);
|
||
string 规格 = (原始.Substring(0, bestStart) + " " + 原始.Substring(bestStart + bestLen))
|
||
.Trim();
|
||
// 压缩多空格 + 去掉规格尾部残留分隔符
|
||
规格 = System.Text.RegularExpressions.Regex.Replace(规格, @"\s+", " ").Trim(' ', '-', '/');
|
||
return 规格;
|
||
}
|
||
return 原始.Trim();
|
||
}
|
||
|
||
/// <summary>安全的 decimal 解析(空/非数字返回 0)。</summary>
|
||
private static decimal ParseDecimalSafe(string s)
|
||
{
|
||
decimal d;
|
||
if (decimal.TryParse((s ?? "").Trim(), out d)) return d;
|
||
return 0;
|
||
}
|
||
|
||
// ====== 严格校验辅助函数 ======
|
||
|
||
/// <summary>A列是否是合法的箱号序号格式:纯数字 或 "数字-数字"(如 1-1、2-7)。</summary>
|
||
private static bool IsValidXianghaoSeq(string s)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(s)) return false;
|
||
s = s.Trim();
|
||
// 纯数字
|
||
int n;
|
||
if (int.TryParse(s, out n) && n >= 1) return true;
|
||
// "数字-数字"
|
||
int dash = s.IndexOf('-');
|
||
if (dash > 0 && dash < s.Length - 1)
|
||
{
|
||
string left = s.Substring(0, dash);
|
||
string right = s.Substring(dash + 1);
|
||
int a, b;
|
||
if (int.TryParse(left, out a) && int.TryParse(right, out b) && a >= 1 && b >= 1) return true;
|
||
}
|
||
return false;
|
||
}
|
||
|
||
/// <summary>常见非报价关键字:表头、说明、合计、工程信息等,用于过滤。</summary>
|
||
private static bool IsNonQuoteKeyword(string s)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(s)) return true;
|
||
string k = s.Trim();
|
||
// 完全匹配
|
||
switch (k)
|
||
{
|
||
case "序号": case "柜号": case "箱柜名称": case "箱柜型号": case "箱柜规格":
|
||
case "单位": case "数量": case "单价": case "总价": case "备注":
|
||
case "元件名称": case "规格": case "表价": case "采购单价": case "采购系数": case "采购价":
|
||
case "报出单价": case "报出系数": case "报出价": case "品牌": case "系列": case "类型": case "图纸规格":
|
||
case "合计": case "总计": case "小计": case "单台合计": case "合计（元）": case "总计（元）":
|
||
case "成套报价表": case "工程名称": case "项目编号": case "工程地点": case "日期":
|
||
case "报价人": case "审核人": case "审批人": case "报价单位": case "电话": case "传真":
|
||
case "地址": case "邮编": case "毛利率": case "毛利": case "利润": case "利润率":
|
||
case "成本总价": case "单台成本": case "成本价": case "单台报出": case "箱柜类型":
|
||
case "图纸": case "型号": case "台数": case "箱柜数量":
|
||
case "成套设备报价(汇总)": case "成套设备报价(明细)": case "成套设备报价单":
|
||
case "单 价": case "总 价": case "生产厂家": case "联系人":
|
||
return true;
|
||
}
|
||
// 含关键字
|
||
if (k.StartsWith("工程名称") || k.StartsWith("项目编号") || k.StartsWith("工程地点")
|
||
|| k.StartsWith("日期") || k.StartsWith("报价人") || k.StartsWith("审核人")
|
||
|| k.StartsWith("审批人") || k.StartsWith("报价单位") || k.StartsWith("项目单位")
|
||
|| k.StartsWith("项目名称") || k.StartsWith("联系人") || k.StartsWith("联系电话")
|
||
|| k.StartsWith("报价日期") || k.StartsWith("文件编号")) return true;
|
||
if (k == "图号" || k == "图名" || k == "台数") return true;
|
||
return false;
|
||
}
|
||
|
||
// ====== 预览 ======
|
||
|
||
private void InitPreviewTables()
|
||
{
|
||
// 图号表
|
||
_图号表 = new DataTable();
|
||
_图号表.Columns.Add("序号", typeof(string));
|
||
_图号表.Columns.Add("图号", typeof(string));
|
||
_图号表.Columns.Add("图名", typeof(string));
|
||
_图号表.Columns.Add("台数", typeof(string));
|
||
_图号表.Columns.Add("箱柜数量", typeof(double));
|
||
_图号表.Columns.Add("单价", typeof(double));
|
||
_图号表.Columns.Add("总价", typeof(double));
|
||
_图号表.Columns.Add("成本总价", typeof(double));
|
||
_图号表.Columns.Add("毛利", typeof(double));
|
||
_图号表.Columns.Add("毛利率", typeof(double));
|
||
|
||
// 箱号表
|
||
_箱号表 = new DataTable();
|
||
_箱号表.Columns.Add("序号", typeof(string));
|
||
_箱号表.Columns.Add("箱号", typeof(string));
|
||
_箱号表.Columns.Add("箱名", typeof(string));
|
||
_箱号表.Columns.Add("型号", typeof(string));
|
||
_箱号表.Columns.Add("规格", typeof(string));
|
||
_箱号表.Columns.Add("单位", typeof(string));
|
||
_箱号表.Columns.Add("数量", typeof(string));
|
||
_箱号表.Columns.Add("单台成本", typeof(double));
|
||
_箱号表.Columns.Add("成本价", typeof(double));
|
||
_箱号表.Columns.Add("单台报出", typeof(double));
|
||
_箱号表.Columns.Add("报出价", typeof(double));
|
||
_箱号表.Columns.Add("利润", typeof(double));
|
||
_箱号表.Columns.Add("利润率", typeof(double));
|
||
_箱号表.Columns.Add("毛利率", typeof(double));
|
||
_箱号表.Columns.Add("箱柜类型", typeof(string));
|
||
_箱号表.Columns.Add("备注", typeof(string));
|
||
_箱号表.Columns.Add("图纸", typeof(string));
|
||
|
||
// 元件表
|
||
_元件表 = new DataTable();
|
||
_元件表.Columns.Add("序号", typeof(string));
|
||
_元件表.Columns.Add("元件名称", typeof(string));
|
||
_元件表.Columns.Add("规格", typeof(string));
|
||
_元件表.Columns.Add("单位", typeof(string));
|
||
_元件表.Columns.Add("数量", typeof(string));
|
||
_元件表.Columns.Add("表价", typeof(double));
|
||
_元件表.Columns.Add("采购单价", typeof(double));
|
||
_元件表.Columns.Add("采购系数", typeof(string));
|
||
_元件表.Columns.Add("采购价", typeof(double));
|
||
_元件表.Columns.Add("报出单价", typeof(double));
|
||
_元件表.Columns.Add("报出系数", typeof(string));
|
||
_元件表.Columns.Add("报出价", typeof(double));
|
||
_元件表.Columns.Add("图纸规格", typeof(string));
|
||
_元件表.Columns.Add("品牌", typeof(string));
|
||
_元件表.Columns.Add("系列", typeof(string));
|
||
_元件表.Columns.Add("类型", typeof(string));
|
||
_元件表.Columns.Add("备注", typeof(string));
|
||
|
||
// ★ 设计时列定义已在 Designer.cs 里(43列)，只需关自动生成，不要覆盖
|
||
this.enhancedDataGridView1.AutoGenerateColumns = false;
|
||
this.enhancedDataGridView2.AutoGenerateColumns = false;
|
||
this.enhancedDataGridView3.AutoGenerateColumns = false;
|
||
this.enhancedDataGridView1.DataSource = _图号表;
|
||
this.enhancedDataGridView2.DataSource = _箱号表;
|
||
this.enhancedDataGridView3.DataSource = _元件表;
|
||
}
|
||
|
||
|
||
/// <summary>把解析结果填充到预览表(图号表显示全部,箱号/元件联动)。</summary>
|
||
private void ShowPreview()
|
||
{
|
||
_图号表.Rows.Clear();
|
||
_箱号表.Rows.Clear();
|
||
_元件表.Rows.Clear();
|
||
|
||
int seq = 0;
|
||
foreach (var th in _图号列表)
|
||
{
|
||
seq++;
|
||
var r = _图号表.NewRow();
|
||
r["序号"] = seq.ToString();
|
||
r["图号"] = th.名称;
|
||
r["台数"] = "1";
|
||
r["箱柜数量"] = (double)th.箱号列表.Count;
|
||
_图号表.Rows.Add(r);
|
||
}
|
||
|
||
// 默认显示第一个图号的箱号 + 第一个箱号的元件
|
||
if (_图号列表.Count > 0)
|
||
{
|
||
Show箱号预览(_图号列表[0]);
|
||
if (_图号列表[0].箱号列表.Count > 0)
|
||
{
|
||
Show元件预览(_图号列表[0].箱号列表[0]);
|
||
}
|
||
}
|
||
|
||
// ★ 诊断日志:输出解析结果的前3行,排查"单位/数量/单台成本没识别出来"问题
|
||
try
|
||
{
|
||
var dbg = new System.Text.StringBuilder();
|
||
dbg.AppendLine("===== 导入报价 解析诊断 =====");
|
||
dbg.AppendLine("图号数: " + _图号列表.Count);
|
||
foreach (var th in _图号列表)
|
||
{
|
||
dbg.AppendLine("图号[" + th.名称 + "] 箱号数=" + th.箱号列表.Count);
|
||
foreach (var xh in th.箱号列表)
|
||
{
|
||
dbg.AppendLine(" 箱号[" + xh.箱号 + "] 型号=[" + xh.型号 + "] 规格=[" + xh.规格 +
|
||
"] 单位=[" + xh.单位 + "] 数量=[" + xh.数量 + "] 元件数=" + xh.元件列表.Count);
|
||
foreach (var yj in xh.元件列表)
|
||
dbg.AppendLine(" 元件[" + yj.元件名称 + "] 规格=[" + yj.规格 + "] 单位=[" + yj.单位 +
|
||
"] 数量=[" + yj.数量 + "] 单价=" + yj.单价 + " 总价=" + yj.总价 + " 品牌=[" + yj.品牌 + "]");
|
||
}
|
||
}
|
||
dbg.AppendLine("===== 箱号表 DataTable =====");
|
||
foreach (DataRow r in _箱号表.Rows)
|
||
{
|
||
var vals = new List<string>();
|
||
foreach (DataColumn c in _箱号表.Columns) vals.Add(c.ColumnName + "=" + r[c]);
|
||
dbg.AppendLine(" " + string.Join(" | ", vals));
|
||
}
|
||
dbg.AppendLine("===== 元件表 DataTable =====");
|
||
foreach (DataRow r in _元件表.Rows)
|
||
{
|
||
var vals = new List<string>();
|
||
foreach (DataColumn c in _元件表.Columns) vals.Add(c.ColumnName + "=" + r[c]);
|
||
dbg.AppendLine(" " + string.Join(" | ", vals));
|
||
}
|
||
System.IO.File.WriteAllText(System.IO.Path.Combine(
|
||
System.AppDomain.CurrentDomain.BaseDirectory, "导入报价_诊断.log"), dbg.ToString());
|
||
}
|
||
catch { }
|
||
}
|
||
|
||
private void Show箱号预览(图号数据 th)
|
||
{
|
||
_箱号表.Rows.Clear();
|
||
int seq = 0;
|
||
foreach (var xh in th.箱号列表)
|
||
{
|
||
seq++;
|
||
var r = _箱号表.NewRow();
|
||
r["序号"] = seq.ToString();
|
||
r["箱号"] = xh.箱号;
|
||
r["箱名"] = xh.箱名;
|
||
r["型号"] = xh.型号;
|
||
r["规格"] = xh.规格;
|
||
r["单位"] = xh.单位;
|
||
r["数量"] = xh.数量;
|
||
// 箱号价格不显示,导入后由主窗口按元件价格自动计算
|
||
r["单台报出"] = 0.0;
|
||
r["报出价"] = 0.0;
|
||
_箱号表.Rows.Add(r);
|
||
}
|
||
}
|
||
|
||
private void Show元件预览(箱号数据 xh)
|
||
{
|
||
_元件表.Rows.Clear();
|
||
int seq = 0;
|
||
foreach (var yj in xh.元件列表)
|
||
{
|
||
seq++;
|
||
var r = _元件表.NewRow();
|
||
r["序号"] = seq.ToString();
|
||
r["元件名称"] = yj.元件名称;
|
||
r["规格"] = yj.规格;
|
||
r["单位"] = yj.单位;
|
||
r["数量"] = yj.数量;
|
||
r["表价"] = (double)yj.单价;
|
||
r["采购系数"] = "1";
|
||
r["报出系数"] = "1";
|
||
r["报出价"] = (double)yj.总价;
|
||
r["品牌"] = yj.品牌;
|
||
r["类型"] = yj.元件名称 == "母排" ? "母排" : "元件";
|
||
_元件表.Rows.Add(r);
|
||
}
|
||
}
|
||
|
||
/// <summary>图号表选中行变化 → 刷新箱号表预览。</summary>
|
||
private void 图号表_SelectionChanged(object sender, EventArgs e)
|
||
{
|
||
if (this.enhancedDataGridView1.CurrentRow == null) return;
|
||
int idx = this.enhancedDataGridView1.CurrentRow.Index;
|
||
if (idx < 0 || idx >= _图号列表.Count) return;
|
||
Show箱号预览(_图号列表[idx]);
|
||
if (_图号列表[idx].箱号列表.Count > 0)
|
||
{
|
||
Show元件预览(_图号列表[idx].箱号列表[0]);
|
||
}
|
||
else
|
||
{
|
||
_元件表.Rows.Clear();
|
||
}
|
||
}
|
||
|
||
/// <summary>箱号表选中行变化 → 刷新元件表预览。</summary>
|
||
private void 箱号表_SelectionChanged(object sender, EventArgs e)
|
||
{
|
||
if (this.enhancedDataGridView1.CurrentRow == null) return;
|
||
int thIdx = this.enhancedDataGridView1.CurrentRow.Index;
|
||
if (thIdx < 0 || thIdx >= _图号列表.Count) return;
|
||
var th = _图号列表[thIdx];
|
||
|
||
if (this.enhancedDataGridView2.CurrentRow == null) return;
|
||
int xhIdx = this.enhancedDataGridView2.CurrentRow.Index;
|
||
if (xhIdx < 0 || xhIdx >= th.箱号列表.Count) return;
|
||
Show元件预览(th.箱号列表[xhIdx]);
|
||
}
|
||
|
||
/// <summary>用户编辑预览箱号表后,把单元格值同步回数据模型(目前支持"箱名"列)。
|
||
/// 预览行按当前图号的 箱号列表 顺序生成,行索引即可定位对象;切换图号会重建行,
|
||
/// 所以必须在这里立即回写,否则导入落库时丢掉用户填的箱名。</summary>
|
||
private void 箱号表_CellEndEdit(object sender, DataGridViewCellEventArgs e)
|
||
{
|
||
if (e.RowIndex < 0 || this.enhancedDataGridView2.CurrentRow == null) return;
|
||
if (this.enhancedDataGridView1.CurrentRow == null) return;
|
||
int thIdx = this.enhancedDataGridView1.CurrentRow.Index;
|
||
if (thIdx < 0 || thIdx >= _图号列表.Count) return;
|
||
var th = _图号列表[thIdx];
|
||
// 用触发编辑的行号(不是 CurrentRow),保证改的就是那一行的对象
|
||
int xhIdx = e.RowIndex;
|
||
if (xhIdx < 0 || xhIdx >= th.箱号列表.Count) return;
|
||
|
||
string colName = this.enhancedDataGridView2.Columns[e.ColumnIndex].DataPropertyName;
|
||
if (string.IsNullOrEmpty(colName)) colName = this.enhancedDataGridView2.Columns[e.ColumnIndex].HeaderText;
|
||
|
||
var cell = this.enhancedDataGridView2.Rows[e.RowIndex].Cells[e.ColumnIndex];
|
||
string val = (cell.Value == null || cell.Value == DBNull.Value) ? "" : cell.Value.ToString();
|
||
var xh = th.箱号列表[xhIdx];
|
||
if (colName == "箱名") xh.箱名 = val.Trim();
|
||
}
|
||
|
||
// ====== 导入数据库 ======
|
||
|
||
private void ImportToDatabase(out int thCount, out int xhCount, out int yjCount)
|
||
{
|
||
thCount = 0; xhCount = 0; yjCount = 0;
|
||
|
||
// 提交预览表格中未结束的编辑(如正在输入的箱名),确保数据模型是最新值
|
||
this.enhancedDataGridView2.EndEdit();
|
||
this.enhancedDataGridView1.EndEdit();
|
||
this.enhancedDataGridView3.EndEdit();
|
||
|
||
using (var conn = new SQLiteConnection(_connString))
|
||
{
|
||
conn.Open();
|
||
using (var tx = conn.BeginTransaction())
|
||
{
|
||
try
|
||
{
|
||
int thSeq = GetMaxSeq(conn, tx, TuhaoTableName, "序号");
|
||
foreach (var th in _图号列表)
|
||
{
|
||
thSeq++;
|
||
// INSERT 图号
|
||
string sqlTh = "INSERT INTO " + TuhaoTableName +
|
||
" (序号, 图号, 台数, 箱柜数量, 单价, 总价, 成本总价, 毛利, 毛利率) " +
|
||
"VALUES (@seq, @th, @ts, @xgs, 0, 0, 0, 0, 0);";
|
||
using (var cmd = new SQLiteCommand(sqlTh, conn, tx))
|
||
{
|
||
cmd.Parameters.AddWithValue("@seq", thSeq.ToString());
|
||
cmd.Parameters.AddWithValue("@th", th.名称);
|
||
cmd.Parameters.AddWithValue("@ts", "1");
|
||
cmd.Parameters.AddWithValue("@xgs", (double)th.箱号列表.Count);
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
thCount++;
|
||
|
||
// 为该图号创建箱号表
|
||
string xhTblName = GetNextTableId(conn, tx, "xh");
|
||
CreateXianghaoTable(conn, tx, xhTblName);
|
||
AddTableIndex(conn, tx, "箱号", th.名称, "", xhTblName);
|
||
|
||
int xhSeq = 0;
|
||
foreach (var xh in th.箱号列表)
|
||
{
|
||
xhSeq++;
|
||
string sqlXh = "INSERT INTO \"" + xhTblName + "\"" +
|
||
" (序号, 箱号, 箱名, 型号, 规格, 单位, 数量," +
|
||
" 单台成本, 成本价, 单台报出, 报出价, 利润, 利润率, 毛利率, 箱柜类型, 备注, 图纸) " +
|
||
"VALUES (@seq, @xh, @xm, @model, @spec, @unit, @qty," +
|
||
" 0, 0, 0, 0, 0, 0, 0, @type, @remark, '');";
|
||
using (var cmd = new SQLiteCommand(sqlXh, conn, tx))
|
||
{
|
||
cmd.Parameters.AddWithValue("@seq", xhSeq.ToString());
|
||
cmd.Parameters.AddWithValue("@xh", xh.箱号);
|
||
cmd.Parameters.AddWithValue("@xm", xh.箱名 ?? "");
|
||
cmd.Parameters.AddWithValue("@model", xh.型号);
|
||
cmd.Parameters.AddWithValue("@spec", xh.规格);
|
||
cmd.Parameters.AddWithValue("@unit", xh.单位);
|
||
cmd.Parameters.AddWithValue("@qty", xh.数量);
|
||
// 箱号价格不导入,全部置0,导入后由主窗口按元件价格自动计算
|
||
cmd.Parameters.AddWithValue("@type", xh.箱柜名称);
|
||
cmd.Parameters.AddWithValue("@remark", "");
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
xhCount++;
|
||
|
||
// 为该箱号创建元件表
|
||
if (xh.元件列表.Count > 0)
|
||
{
|
||
string yjTblName = GetNextTableId(conn, tx, "yj");
|
||
CreateYuanjianTable(conn, tx, yjTblName);
|
||
AddTableIndex(conn, tx, "元件", th.名称, xh.箱号, yjTblName);
|
||
|
||
int yjSeq = 0;
|
||
foreach (var yj in xh.元件列表)
|
||
{
|
||
yjSeq++;
|
||
decimal 采购单价 = yj.单价;
|
||
decimal 采购价 = yj.总价;
|
||
string sqlYj = "INSERT INTO \"" + yjTblName + "\"" +
|
||
" (序号, 元件名称, 规格, 单位, 数量," +
|
||
" 表价, 采购单价, 采购系数, 采购价," +
|
||
" 报出单价, 报出系数, 报出价," +
|
||
" 图纸规格, 品牌, 系列, 类型, 备注) " +
|
||
"VALUES (@seq, @name, @spec, @unit, @qty," +
|
||
" @price, @cgdj, '1', @cgj," +
|
||
" @bcdj, '1', @bcj," +
|
||
" '', @brand, '', @type, '');";
|
||
using (var cmd = new SQLiteCommand(sqlYj, conn, tx))
|
||
{
|
||
cmd.Parameters.AddWithValue("@seq", yjSeq.ToString());
|
||
cmd.Parameters.AddWithValue("@name", yj.元件名称);
|
||
cmd.Parameters.AddWithValue("@spec", yj.规格);
|
||
cmd.Parameters.AddWithValue("@unit", yj.单位);
|
||
cmd.Parameters.AddWithValue("@qty", yj.数量);
|
||
cmd.Parameters.AddWithValue("@price", (double)yj.单价);
|
||
cmd.Parameters.AddWithValue("@cgdj", (double)采购单价);
|
||
cmd.Parameters.AddWithValue("@cgj", (double)采购价);
|
||
cmd.Parameters.AddWithValue("@bcdj", (double)yj.单价);
|
||
cmd.Parameters.AddWithValue("@bcj", (double)yj.总价);
|
||
cmd.Parameters.AddWithValue("@brand", yj.品牌);
|
||
cmd.Parameters.AddWithValue("@type", yj.元件名称 == "母排" ? "母排" : "元件");
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
yjCount++;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
tx.Commit();
|
||
}
|
||
catch
|
||
{
|
||
try { tx.Rollback(); } catch { }
|
||
throw;
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
// ====== 数据库辅助方法 ======
|
||
|
||
/// <summary>查某列的最大数值序号(用于图号表序号)。</summary>
|
||
private static int GetMaxSeq(SQLiteConnection conn, SQLiteTransaction tx, string tableName, string colName)
|
||
{
|
||
int maxN = 0;
|
||
string sql = "SELECT " + colName + " FROM " + tableName + ";";
|
||
try
|
||
{
|
||
using (var cmd = new SQLiteCommand(sql, conn, tx))
|
||
using (var reader = cmd.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
string s = reader.IsDBNull(0) ? "" : reader.GetString(0);
|
||
int n;
|
||
if (int.TryParse(s, out n) && n > maxN) maxN = n;
|
||
}
|
||
}
|
||
}
|
||
catch { }
|
||
return maxN;
|
||
}
|
||
|
||
/// <summary>生成下一个表ID(xh_N / yj_M),查表索引最大序号+1。</summary>
|
||
private static string GetNextTableId(SQLiteConnection conn, SQLiteTransaction tx, string prefix)
|
||
{
|
||
int maxN = 0;
|
||
// 查表索引
|
||
string sql1 = "SELECT 实际表名 FROM 表索引 WHERE 实际表名 LIKE @p;";
|
||
using (var cmd1 = new SQLiteCommand(sql1, conn, tx))
|
||
{
|
||
cmd1.Parameters.AddWithValue("@p", prefix + "_%");
|
||
using (var reader = cmd1.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
string t = reader.IsDBNull(0) ? "" : reader.GetString(0);
|
||
string numPart = t.StartsWith(prefix + "_") ? t.Substring(prefix.Length + 1) : "";
|
||
int n;
|
||
if (int.TryParse(numPart, out n) && n > maxN) maxN = n;
|
||
}
|
||
}
|
||
}
|
||
// 查物理表
|
||
string sql2 = "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE @p;";
|
||
using (var cmd2 = new SQLiteCommand(sql2, conn, tx))
|
||
{
|
||
cmd2.Parameters.AddWithValue("@p", prefix + "_%");
|
||
using (var reader = cmd2.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
string t = reader.IsDBNull(0) ? "" : reader.GetString(0);
|
||
string numPart = t.StartsWith(prefix + "_") ? t.Substring(prefix.Length + 1) : "";
|
||
int n;
|
||
if (int.TryParse(numPart, out n) && n > maxN) maxN = n;
|
||
}
|
||
}
|
||
}
|
||
return prefix + "_" + (maxN + 1).ToString();
|
||
}
|
||
|
||
private static void CreateXianghaoTable(SQLiteConnection conn, SQLiteTransaction tx, string realTableName)
|
||
{
|
||
string sql =
|
||
"CREATE TABLE \"" + realTableName + "\" (" +
|
||
" 序号 TEXT, 箱号 TEXT, 箱名 TEXT, 型号 TEXT, 规格 TEXT, 单位 TEXT, 数量 TEXT," +
|
||
" 单台成本 REAL, 成本价 REAL, 单台报出 REAL, 报出价 REAL," +
|
||
" 利润 REAL, 利润率 REAL, 毛利率 REAL, 箱柜类型 TEXT, 备注 TEXT, 图纸 TEXT" +
|
||
");";
|
||
using (var cmd = new SQLiteCommand(sql, conn, tx))
|
||
{
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
}
|
||
|
||
private static void CreateYuanjianTable(SQLiteConnection conn, SQLiteTransaction tx, string realTableName)
|
||
{
|
||
string sql =
|
||
"CREATE TABLE \"" + realTableName + "\" (" +
|
||
" 序号 TEXT, 元件名称 TEXT, 规格 TEXT, 单位 TEXT, 数量 TEXT," +
|
||
" 表价 REAL, 采购单价 REAL, 采购系数 TEXT, 采购价 REAL," +
|
||
" 报出单价 REAL, 报出系数 TEXT, 报出价 REAL," +
|
||
" 图纸规格 TEXT, 品牌 TEXT, 系列 TEXT, 类型 TEXT, 备注 TEXT" +
|
||
");";
|
||
using (var cmd = new SQLiteCommand(sql, conn, tx))
|
||
{
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
}
|
||
|
||
private static void AddTableIndex(SQLiteConnection conn, SQLiteTransaction tx,
|
||
string type, string tuhao, string xianghao, string realTableName)
|
||
{
|
||
string sql = "INSERT INTO 表索引 (类型, 图号, 箱号, 实际表名) VALUES (@t, @th, @xh, @real);";
|
||
using (var cmd = new SQLiteCommand(sql, conn, tx))
|
||
{
|
||
cmd.Parameters.AddWithValue("@t", type);
|
||
cmd.Parameters.AddWithValue("@th", tuhao ?? "");
|
||
cmd.Parameters.AddWithValue("@xh", xianghao ?? "");
|
||
cmd.Parameters.AddWithValue("@real", realTableName);
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
}
|
||
|
||
// ====== NPOI 单元格读取辅助 ======
|
||
|
||
|
||
/// <summary>手动解析公式引用：从 "=屏柜分项表!B12" 提取工作表名和单元格地址，去目标表读值。</summary>
|
||
private static string ResolveFormulaReference(IWorkbook workbook, ICell formulaCell)
|
||
{
|
||
try
|
||
{
|
||
string formula = formulaCell.CellFormula;
|
||
if (string.IsNullOrEmpty(formula)) return "";
|
||
// 去掉 = 号
|
||
formula = formula.TrimStart('=');
|
||
// 找到工作表名!单元格地址
|
||
int bang = formula.IndexOf('!');
|
||
if (bang < 0) return "";
|
||
string sheetName = formula.Substring(0, bang).Replace("'", "").Replace("\"", "");
|
||
string cellAddr = formula.Substring(bang + 1).Replace("'", "").Replace("\"", "").Replace("$", "");
|
||
if (string.IsNullOrEmpty(sheetName) || string.IsNullOrEmpty(cellAddr)) return "";
|
||
ISheet refSheet = workbook.GetSheet(sheetName);
|
||
if (refSheet == null) return "";
|
||
// 解析行号列号
|
||
var addr = new NPOI.SS.Util.CellReference(cellAddr);
|
||
IRow refRow = refSheet.GetRow(addr.Row);
|
||
if (refRow == null) return "";
|
||
ICell refCell = refRow.GetCell(addr.Col);
|
||
if (refCell == null) return "";
|
||
return GetCellValueString(refCell);
|
||
}
|
||
catch { return ""; }
|
||
}
|
||
|
||
/// <summary>通用解析：D/E 等列的公式引用（读出"0"或引用文本时按引用取真实值）。</summary>
|
||
private static string ResolveCellOrReference(IWorkbook workbook, string raw,
|
||
NPOI.SS.UserModel.ICell cell, NPOI.SS.UserModel.IFormulaEvaluator evaluator)
|
||
{
|
||
// 已经有正常文本就直接用
|
||
if (!string.IsNullOrWhiteSpace(raw) && raw != "0" &&
|
||
raw.IndexOf("屏柜分项表!", StringComparison.OrdinalIgnoreCase) < 0)
|
||
return raw;
|
||
// 文本引用 → 手动解析
|
||
string byRef = ResolveFormulaReferenceText(workbook, raw);
|
||
if (!string.IsNullOrWhiteSpace(byRef) && byRef != "0") return byRef;
|
||
// Formula 类型 → NPOI 求值
|
||
if (cell != null && cell.CellType == CellType.Formula && evaluator != null)
|
||
{
|
||
try
|
||
{
|
||
var r = evaluator.Evaluate(cell);
|
||
if (r != null && r.CellType == CellType.String) return (r.StringValue ?? "").Trim();
|
||
if (r != null && r.CellType == CellType.Numeric) return r.NumberValue.ToString("0.####");
|
||
}
|
||
catch { }
|
||
// 求值失败 → 从 CellFormula 手动解析
|
||
try
|
||
{
|
||
string byF = ResolveFormulaReferenceText(workbook, cell.CellFormula);
|
||
if (!string.IsNullOrWhiteSpace(byF) && byF != "0") return byF;
|
||
}
|
||
catch { }
|
||
}
|
||
return raw == "0" ? "" : raw;
|
||
}
|
||
|
||
/// <summary>按公式文本读取工作表引用，例如“屏柜分项表!B12”。</summary>
|
||
private static string ResolveFormulaReferenceText(IWorkbook workbook, string text)
|
||
{
|
||
try
|
||
{
|
||
if (string.IsNullOrWhiteSpace(text)) return "";
|
||
string formula = text.Trim();
|
||
if (formula.StartsWith("=")) formula = formula.Substring(1);
|
||
int bang = formula.IndexOf('!');
|
||
if (bang < 0) return "";
|
||
string sheetName = formula.Substring(0, bang).Trim().Trim('\'').Trim('"');
|
||
string cellAddr = formula.Substring(bang + 1).Trim().Trim('\'').Trim('"').Replace("$", "");
|
||
ISheet sheet = workbook.GetSheet(sheetName);
|
||
if (sheet == null) return "";
|
||
var addr = new NPOI.SS.Util.CellReference(cellAddr);
|
||
IRow r = sheet.GetRow(addr.Row);
|
||
return r == null ? "" : GetCellValueString(r.GetCell(addr.Col));
|
||
}
|
||
catch { return ""; }
|
||
}
|
||
|
||
/// <summary>读取单元格字符串值(自动处理数值/公式/空值)。</summary>
|
||
private static string GetCellString(IRow row, int col)
|
||
{
|
||
if (row == null) return "";
|
||
ICell cell = row.GetCell(col);
|
||
if (cell == null) return "";
|
||
return GetCellValueString(cell);
|
||
}
|
||
|
||
private static string GetCellValueString(ICell cell)
|
||
{
|
||
if (cell == null) return "";
|
||
try
|
||
{
|
||
switch (cell.CellType)
|
||
{
|
||
case CellType.String:
|
||
return (cell.StringCellValue ?? "").Trim();
|
||
case CellType.Numeric:
|
||
double d = cell.NumericCellValue;
|
||
if (d == Math.Floor(d) && !double.IsInfinity(d))
|
||
return ((long)d).ToString();
|
||
return d.ToString("0.####");
|
||
case CellType.Boolean:
|
||
return cell.BooleanCellValue.ToString();
|
||
case CellType.Formula:
|
||
try
|
||
{
|
||
if (cell.CachedFormulaResultType == CellType.String)
|
||
return (cell.StringCellValue ?? "").Trim();
|
||
if (cell.CachedFormulaResultType == CellType.Numeric)
|
||
{
|
||
double fd = cell.NumericCellValue;
|
||
if (fd == Math.Floor(fd)) return ((long)fd).ToString();
|
||
return fd.ToString("0.####");
|
||
}
|
||
}
|
||
catch { }
|
||
return "";
|
||
default:
|
||
return "";
|
||
}
|
||
}
|
||
catch { return ""; }
|
||
}
|
||
|
||
/// <summary>读取单元格 decimal 值(非数字返回 0)。</summary>
|
||
private static decimal GetCellDecimal(IRow row, int col)
|
||
{
|
||
if (row == null) return 0m;
|
||
ICell cell = row.GetCell(col);
|
||
if (cell == null) return 0m;
|
||
try
|
||
{
|
||
if (cell.CellType == CellType.Numeric) return (decimal)cell.NumericCellValue;
|
||
if (cell.CellType == CellType.String)
|
||
{
|
||
decimal v;
|
||
if (decimal.TryParse((cell.StringCellValue ?? "").Trim(), out v)) return v;
|
||
}
|
||
if (cell.CellType == CellType.Formula)
|
||
{
|
||
try
|
||
{
|
||
if (cell.CachedFormulaResultType == CellType.Numeric)
|
||
return (decimal)cell.NumericCellValue;
|
||
}
|
||
catch { }
|
||
}
|
||
}
|
||
catch { }
|
||
return 0m;
|
||
}
|
||
|
||
// ====== AI 无界面入口（AI助手接口 /api/importquote 专用，零弹窗复用解析+落库管线） ======
|
||
|
||
/// <summary>AI 无界面导入结果（成功标志+计数+分组预览+错误信息）。</summary>
|
||
internal class AI导入结果
|
||
{
|
||
public bool 成功;
|
||
public string 错误;
|
||
public int 图号数;
|
||
public int 箱号数;
|
||
public int 元件数;
|
||
/// <summary>分组预览：每个元素 {tuhao, xianghaoshu, xianghaos:[{xianghao,xiangming,xinghao,guige,danwei,shuliang,yuanjian}]}。</summary>
|
||
public List<Dictionary<string, object>> 分组预览 = new List<Dictionary<string, object>>();
|
||
}
|
||
|
||
/// <summary>
|
||
/// AI 无界面执行入口：new 一个永不 Show 的本窗体实例，完整复用 ParseAndPreview 解析管线与
|
||
/// ImportToDatabase 事务落库（不重复任何 SQL/识别规则）。控件全程不创建窗口句柄——
|
||
/// DataGridView 只做 DataSource 绑定、行数据全部走 DataTable，因此可在接口后台线程调用。
|
||
/// 落库=false：仅解析并返回分组预览（不落库）；落库=true：解析后事务导入项目库。
|
||
/// ★映射（AI 看网格后确定的列位，null=软件自动识别表头）：
|
||
/// {summarySheet:"屏柜汇总表", detailSheet:"屏柜分项表",
|
||
/// summary:{seq,cabinet,name,model,spec,unit,qty}, detail:{seq,name,spec,unit,qty,price,amount,vendor}}
|
||
/// ★调用方（MainForm）负责落库成功后在 UI 线程做缓存清理+EnterProjectCore 完整刷新。
|
||
/// </summary>
|
||
internal static AI导入结果 AI无界面执行(string projectId, string dbPath, string xlsxPath, bool 落库,
|
||
Dictionary<string, object> 映射 = null)
|
||
{
|
||
var 结果 = new AI导入结果();
|
||
导入报价 隐藏窗体 = null;
|
||
try
|
||
{
|
||
// 拆映射：sheet 名 + 两种列映射
|
||
string 汇总sheet = null, 分项sheet = null;
|
||
汇总列映射 汇总映射 = null;
|
||
分项列映射 分项映射 = null;
|
||
if (映射 != null)
|
||
{
|
||
object v;
|
||
if (映射.TryGetValue("summarySheet", out v) && v is string) 汇总sheet = (string)v;
|
||
if (映射.TryGetValue("detailSheet", out v) && v is string) 分项sheet = (string)v;
|
||
if (映射.TryGetValue("summary", out v) && v is Dictionary<string, object>) 汇总映射 = 字典转汇总映射((Dictionary<string, object>)v);
|
||
if (映射.TryGetValue("detail", out v) && v is Dictionary<string, object>) 分项映射 = 字典转分项映射((Dictionary<string, object>)v);
|
||
}
|
||
|
||
隐藏窗体 = new 导入报价(projectId, dbPath);
|
||
|
||
string parseErr;
|
||
隐藏窗体.ParseAndPreview(xlsxPath, out parseErr, 汇总sheet, 分项sheet, 汇总映射, 分项映射);
|
||
if (隐藏窗体._图号列表.Count == 0)
|
||
{
|
||
结果.成功 = false;
|
||
结果.错误 = string.IsNullOrEmpty(parseErr)
|
||
? "未能从报价表中识别到任何图号/箱号数据（请确认是含\"屏柜汇总表/屏柜分项表\"或\"设备汇总表/设备明细表\"的 xlsx）"
|
||
: parseErr;
|
||
return 结果;
|
||
}
|
||
|
||
// 组装分组预览（无论是否落库都生成，供 AI 先预览后确认）
|
||
int totalXh = 0, totalYj = 0;
|
||
foreach (var th in 隐藏窗体._图号列表)
|
||
{
|
||
var g = new Dictionary<string, object>();
|
||
g["tuhao"] = th.名称;
|
||
g["xianghaoshu"] = th.箱号列表.Count;
|
||
var xhs = new List<Dictionary<string, object>>();
|
||
foreach (var xh in th.箱号列表)
|
||
{
|
||
totalXh++;
|
||
totalYj += xh.元件列表.Count;
|
||
var x = new Dictionary<string, object>();
|
||
x["xianghao"] = xh.箱号 ?? "";
|
||
x["xiangming"] = xh.箱名 ?? "";
|
||
x["xinghao"] = xh.型号 ?? "";
|
||
x["danwei"] = xh.单位 ?? "";
|
||
x["shuliang"] = xh.数量 ?? "";
|
||
x["yuanjian"] = xh.元件列表.Count;
|
||
xhs.Add(x);
|
||
}
|
||
g["xianghaos"] = xhs;
|
||
结果.分组预览.Add(g);
|
||
}
|
||
结果.图号数 = 隐藏窗体._图号列表.Count;
|
||
结果.箱号数 = totalXh;
|
||
结果.元件数 = totalYj;
|
||
|
||
if (落库)
|
||
{
|
||
int thCount, xhCount, yjCount;
|
||
隐藏窗体.ImportToDatabase(out thCount, out xhCount, out yjCount);
|
||
结果.图号数 = thCount;
|
||
结果.箱号数 = xhCount;
|
||
结果.元件数 = yjCount;
|
||
}
|
||
|
||
结果.成功 = true;
|
||
return 结果;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
结果.成功 = false;
|
||
结果.错误 = ex.Message;
|
||
return 结果;
|
||
}
|
||
finally
|
||
{
|
||
// 控件未建句柄，同线程 Dispose 不弹任何 UI；防止每次导入泄漏一个隐藏窗体
|
||
if (隐藏窗体 != null) { try { 隐藏窗体.Dispose(); } catch { } }
|
||
}
|
||
}
|
||
|
||
/// <summary>
|
||
/// AI 看表用（/api/excelgrid）：读 xlsx 指定 sheet 前 N 行×M 列文本网格（公式取缓存结果），
|
||
/// 供 AI(LLM) 判断表结构并给出列映射。sheetName 空=只返回 sheet 清单（名+行数）。
|
||
/// 返回对象直接由接口层序列化为 JSON；失败时 错误 非 null。
|
||
/// </summary>
|
||
internal static object AI网格数据(string xlsxPath, string sheetName, int 最大行, int 最大列, out string 错误)
|
||
{
|
||
错误 = null;
|
||
try
|
||
{
|
||
using (var fs = new FileStream(xlsxPath, FileMode.Open, FileAccess.Read))
|
||
{
|
||
IWorkbook workbook = new XSSFWorkbook(fs);
|
||
if (string.IsNullOrEmpty(sheetName))
|
||
{
|
||
var sheets = new List<object>();
|
||
for (int i = 0; i < workbook.NumberOfSheets; i++)
|
||
{
|
||
ISheet s = workbook.GetSheetAt(i);
|
||
sheets.Add(new Dictionary<string, object> {
|
||
{ "name", s.SheetName }, { "rows", s.LastRowNum + 1 } });
|
||
}
|
||
return new Dictionary<string, object> { { "sheets", sheets } };
|
||
}
|
||
ISheet sheet = workbook.GetSheet(sheetName);
|
||
if (sheet == null) { 错误 = "找不到工作表: " + sheetName; return null; }
|
||
int 行上 = Math.Min(sheet.LastRowNum, Math.Max(1, 最大行) - 1);
|
||
var grid = new List<object>();
|
||
for (int r = 0; r <= 行上; r++)
|
||
{
|
||
IRow row = sheet.GetRow(r);
|
||
var cells = new List<string>();
|
||
for (int c = 0; c < 最大列; c++)
|
||
cells.Add(row == null ? "" : AI单元格文本(row.GetCell(c)));
|
||
grid.Add(cells);
|
||
}
|
||
return new Dictionary<string, object> {
|
||
{ "sheet", sheetName }, { "rows", sheet.LastRowNum + 1 }, { "grid", grid } };
|
||
}
|
||
}
|
||
catch (Exception ex) { 错误 = ex.Message; return null; }
|
||
}
|
||
|
||
/// <summary>单元格文本化：字符串原样、数值去尾零、公式取缓存结果（取不到给公式原文）。</summary>
|
||
private static string AI单元格文本(ICell cell)
|
||
{
|
||
if (cell == null) return "";
|
||
try
|
||
{
|
||
switch (cell.CellType)
|
||
{
|
||
case CellType.String: return cell.StringCellValue ?? "";
|
||
case CellType.Numeric: return cell.NumericCellValue.ToString("0.####");
|
||
case CellType.Boolean: return cell.BooleanCellValue ? "TRUE" : "FALSE";
|
||
case CellType.Formula:
|
||
try
|
||
{
|
||
switch (cell.CachedFormulaResultType)
|
||
{
|
||
case CellType.String: return cell.StringCellValue ?? "";
|
||
case CellType.Numeric: return cell.NumericCellValue.ToString("0.####");
|
||
case CellType.Boolean: return cell.BooleanCellValue ? "TRUE" : "FALSE";
|
||
default: return cell.CellFormula ?? "";
|
||
}
|
||
}
|
||
catch { return cell.CellFormula ?? ""; }
|
||
default: return "";
|
||
}
|
||
}
|
||
catch { return ""; }
|
||
}
|
||
|
||
// ====== 内部数据结构 ======
|
||
|
||
private class 图号数据
|
||
{
|
||
public string 名称;
|
||
public List<箱号数据> 箱号列表 = new List<箱号数据>();
|
||
}
|
||
|
||
private class 箱号数据
|
||
{
|
||
public string 箱号;
|
||
public string 箱名;
|
||
public string 箱柜名称;
|
||
public string 型号;
|
||
public string 规格;
|
||
public string 单位;
|
||
public string 数量;
|
||
public decimal 单价;
|
||
public decimal 总价;
|
||
public List<元件数据> 元件列表 = new List<元件数据>();
|
||
}
|
||
|
||
private class 元件数据
|
||
{
|
||
public string 元件名称;
|
||
public string 规格;
|
||
public string 单位;
|
||
public string 数量;
|
||
public decimal 单价;
|
||
public decimal 总价;
|
||
public string 品牌;
|
||
}
|
||
|
||
/// <summary>从柜号名推算台数："K1/K12"=2, "K3/K5-K8/K10"=6, "K6"=1。</summary>
|
||
private static int CountCabinetsFromName(string name)
|
||
{
|
||
if (string.IsNullOrWhiteSpace(name)) return 1;
|
||
int count = 0;
|
||
// 按 / 分隔：K1/K5-K8/K10 → ["K1", "K5-K8", "K10"]
|
||
foreach (var seg in name.Split('/'))
|
||
{
|
||
var s2 = seg.Trim();
|
||
if (string.IsNullOrEmpty(s2)) continue;
|
||
int dash = s2.IndexOf('-');
|
||
if (dash > 0 && dash < s2.Length - 1)
|
||
{
|
||
// "K5-K8" → 取数字部分 5 到 8 = 4 台
|
||
string left = "", right = "";
|
||
for (int i = 0; i < s2.Length; i++)
|
||
{
|
||
if (char.IsDigit(s2[i]))
|
||
{
|
||
if (i < dash) left += s2[i];
|
||
else right += s2[i];
|
||
}
|
||
}
|
||
int a, b;
|
||
if (int.TryParse(left, out a) && int.TryParse(right, out b) && b >= a)
|
||
count += b - a + 1;
|
||
else
|
||
count += 1;
|
||
}
|
||
else
|
||
{
|
||
count += 1;
|
||
}
|
||
}
|
||
return count > 0 ? count : 1;
|
||
}
|
||
}
|
||
}
|