1706 lines
80 KiB
C#
1706 lines
80 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.IO;
|
||
using System.Xml;
|
||
using System.Windows.Forms;
|
||
using System.Data.SQLite;
|
||
|
||
namespace 成套报价软件
|
||
{
|
||
/// <summary>
|
||
/// 甲方报表 的交互逻辑。
|
||
/// 接收项目编号,从报价库读取数据,用 NPOI 填充 Excel 模板导出。
|
||
/// </summary>
|
||
public partial class 甲方报表
|
||
{
|
||
// ====== 字段 ======
|
||
|
||
private string _projectId;
|
||
private string _quoteDbPath;
|
||
|
||
// 模板目录(相对程序根目录)
|
||
private const string TemplateDir = @"xiangmu\xlsx\按图号";
|
||
private const string TuhaoTableName = "advancedDataGridView9Table";
|
||
|
||
// ====== 构造函数 ======
|
||
|
||
// SharpDevelop 设计器需要无参数构造函数；运行时由主窗口调用带参数版本。
|
||
public 甲方报表()
|
||
{
|
||
InitializeComponent();
|
||
}
|
||
|
||
public 甲方报表(string projectId)
|
||
{
|
||
InitializeComponent();
|
||
_projectId = projectId;
|
||
_quoteDbPath = GetQuoteDbPath(projectId);
|
||
|
||
this.Load += 甲方报表_Load;
|
||
this.btn打开模板.Click += btn打开模板_Click;
|
||
this.btn导入模板.Click += btn导入模板_Click;
|
||
this.btn选择文件.Click += btn选择文件_Click;
|
||
this.btn确定.Click += btn确定_Click;
|
||
this.btn关闭.Click += btn关闭_Click;
|
||
}
|
||
|
||
// ====== 窗体事件 ======
|
||
|
||
private void 甲方报表_Load(object sender, EventArgs e)
|
||
{
|
||
LoadTemplateList();
|
||
|
||
// 默认导出文件名: 项目名称_甲方报价.xlsx (读取项目信息里的项目名称)
|
||
var projInfo = LoadProjectInfo();
|
||
string projName = GetDict(projInfo, "项目名称");
|
||
if (string.IsNullOrWhiteSpace(projName)) projName = _projectId; // 项目名称为空时退回项目编号
|
||
string safeName = MakeSafeName(projName);
|
||
string defaultName = safeName + "_甲方报价.xlsx";
|
||
// ★ 文本框只显示文件名,不显示完整路径
|
||
txt导出文件.Text = defaultName;
|
||
|
||
cmb报表类型.Text = "按图号";
|
||
}
|
||
|
||
/// <summary>扫描模板目录,把所有 .xlsx 文件名填入下拉框。</summary>
|
||
private void LoadTemplateList()
|
||
{
|
||
cmb报表模板.Items.Clear();
|
||
string dir = GetTemplateDirFullPath();
|
||
if (!Directory.Exists(dir)) return;
|
||
|
||
foreach (var file in Directory.GetFiles(dir, "*.xlsx"))
|
||
{
|
||
cmb报表模板.Items.Add(Path.GetFileNameWithoutExtension(file));
|
||
}
|
||
if (cmb报表模板.Items.Count > 0)
|
||
cmb报表模板.SelectedIndex = 0;
|
||
}
|
||
|
||
private void btn打开模板_Click(object sender, EventArgs e)
|
||
{
|
||
string path = GetSelectedTemplatePath();
|
||
if (path == null || !File.Exists(path))
|
||
{
|
||
MessageBox.Show(this, "请先选择一个模板。", "甲方报表", MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
System.Diagnostics.Process.Start(path);
|
||
}
|
||
|
||
private void btn导入模板_Click(object sender, EventArgs e)
|
||
{
|
||
using (var dlg = new OpenFileDialog())
|
||
{
|
||
dlg.Filter = "Excel 文件|*.xlsx";
|
||
dlg.Title = "选择要导入的模板文件";
|
||
if (dlg.ShowDialog() != DialogResult.OK) return;
|
||
|
||
string dir = GetTemplateDirFullPath();
|
||
if (!Directory.Exists(dir)) Directory.CreateDirectory(dir);
|
||
|
||
string destPath = Path.Combine(dir, Path.GetFileName(dlg.FileName));
|
||
File.Copy(dlg.FileName, destPath, true);
|
||
LoadTemplateList();
|
||
|
||
string name = Path.GetFileNameWithoutExtension(dlg.FileName);
|
||
int idx = cmb报表模板.Items.IndexOf(name);
|
||
if (idx >= 0) cmb报表模板.SelectedIndex = idx;
|
||
|
||
MessageBox.Show(this, "模板导入成功。", "导入模板",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
}
|
||
}
|
||
|
||
private void btn选择文件_Click(object sender, EventArgs e)
|
||
{
|
||
using (var dlg = new SaveFileDialog())
|
||
{
|
||
dlg.Filter = "Excel 文件|*.xlsx";
|
||
dlg.Title = "选择导出文件路径";
|
||
dlg.FileName = Path.GetFileName(txt导出文件.Text);
|
||
if (dlg.ShowDialog() == DialogResult.OK)
|
||
// ★ 写回完整路径:用户选的目录必须被尊重(ResolveOutputPath 对 rooted 路径直接使用)
|
||
txt导出文件.Text = dlg.FileName;
|
||
}
|
||
}
|
||
|
||
private void btn确定_Click(object sender, EventArgs e)
|
||
{
|
||
string templatePath = GetSelectedTemplatePath();
|
||
if (templatePath == null || !File.Exists(templatePath))
|
||
{
|
||
MessageBox.Show(this, "请选择有效的报表模板。", "甲方报表",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
|
||
// ★ 文本框只存文件名,导出时转成完整路径
|
||
string outputPath = ResolveOutputPath();
|
||
if (string.IsNullOrEmpty(outputPath))
|
||
{
|
||
MessageBox.Show(this, "请指定导出文件名。", "甲方报表",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
|
||
if (!File.Exists(_quoteDbPath))
|
||
{
|
||
MessageBox.Show(this, "当前项目的报价数据库不存在:\n" + _quoteDbPath,
|
||
"甲方报表", MessageBoxButtons.OK, MessageBoxIcon.Warning);
|
||
return;
|
||
}
|
||
|
||
try
|
||
{
|
||
var projInfo = LoadProjectInfo();
|
||
var companyInfo = 企业信息.LoadSettings();
|
||
var tuhaoList = LoadTuhaoData();
|
||
|
||
// 调试日志
|
||
var dbg = new System.Text.StringBuilder();
|
||
dbg.AppendLine("projInfo条数: " + projInfo.Count);
|
||
foreach (var kv in projInfo) dbg.AppendLine(" " + kv.Key + "=" + kv.Value);
|
||
dbg.AppendLine("companyInfo条数: " + companyInfo.Count);
|
||
foreach (var kv in companyInfo) dbg.AppendLine(" " + kv.Key + "=" + kv.Value);
|
||
dbg.AppendLine("tuhaoList条数: " + tuhaoList.Count);
|
||
int cabTotal = 0;
|
||
foreach (var t in tuhaoList) { dbg.AppendLine(" 图号=" + t.Tuhao + " 箱号数=" + t.Cabinets.Count); cabTotal += t.Cabinets.Count; }
|
||
dbg.AppendLine("箱号总数: " + cabTotal);
|
||
dbg.AppendLine("_quoteDbPath: " + _quoteDbPath);
|
||
dbg.AppendLine("_projectId: " + _projectId);
|
||
dbg.AppendLine("templatePath: " + templatePath);
|
||
dbg.AppendLine("outputPath: " + outputPath);
|
||
File.WriteAllText(Path.Combine(GetAppBaseDir(), "甲方报表_调试.log"), dbg.ToString());
|
||
|
||
if (tuhaoList.Count == 0 || TotalCabinetCount(tuhaoList) == 0)
|
||
{
|
||
MessageBox.Show(this, "当前项目没有箱号数据,无法导出。\n\n" + dbg.ToString(), "甲方报表",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
return;
|
||
}
|
||
|
||
this.Cursor = Cursors.WaitCursor;
|
||
ExportExcel(templatePath, outputPath, tuhaoList, projInfo, companyInfo,
|
||
chk表格带Excel公式.Checked, chk箱柜链接.Checked);
|
||
this.Cursor = Cursors.Default;
|
||
|
||
MessageBox.Show(this, "导出成功!\n" + outputPath, "甲方报表",
|
||
MessageBoxButtons.OK, MessageBoxIcon.Information);
|
||
|
||
if (MessageBox.Show(this, "是否打开导出的文件?", "甲方报表",
|
||
MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
|
||
{
|
||
System.Diagnostics.Process.Start(outputPath);
|
||
}
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
this.Cursor = Cursors.Default;
|
||
MessageBox.Show(this, "导出失败:\n" + ex.Message + "\n\n" + ex.StackTrace,
|
||
"甲方报表", MessageBoxButtons.OK, MessageBoxIcon.Error);
|
||
}
|
||
}
|
||
|
||
private void btn关闭_Click(object sender, EventArgs e)
|
||
{
|
||
this.Close();
|
||
}
|
||
|
||
// ====== 数据模型 ======
|
||
|
||
private class TuhaoData
|
||
{
|
||
public string ShowOrder;
|
||
public string Tuhao;
|
||
public string TuMing;
|
||
public string TaiShu;
|
||
public decimal XianguiCount;
|
||
public decimal UnitPrice;
|
||
public decimal TotalPrice;
|
||
public decimal CnTotalPrice;
|
||
public List<CabinetData> Cabinets = new List<CabinetData>();
|
||
}
|
||
|
||
private class CabinetData
|
||
{
|
||
public string ShowOrder;
|
||
public string CabinetNo;
|
||
public string BoxName; // 箱号表."箱名"(进线柜、PT柜等名称)
|
||
public string Model;
|
||
public string SpecName;
|
||
public string Unit;
|
||
public string Count;
|
||
public decimal OfferUnitPrice;
|
||
public decimal OfferTotalPrice;
|
||
public string Remark;
|
||
public string CabinetName; // 图号表.图名
|
||
public string PaperModelShowOrder = null; // 图号序号
|
||
// ★ 查元件表用内部ID(改名不丢明细)
|
||
public string InnerId;
|
||
public List<ElementData> Elements = new List<ElementData>();
|
||
}
|
||
|
||
private class ElementData
|
||
{
|
||
public string ShowOrder;
|
||
public string Name;
|
||
public string Spec;
|
||
public string Unit;
|
||
public string Count;
|
||
public decimal OfferUnitPrice;
|
||
public decimal OfferTotalPrice;
|
||
public string DrawingSpec;
|
||
public string FactName;
|
||
public string Remark;
|
||
public string ShowSpecName; // 图纸规格优先,否则用规格
|
||
}
|
||
|
||
// ====== 数据读取 ======
|
||
|
||
private string GetAppBaseDir()
|
||
{
|
||
return AppDomain.CurrentDomain.BaseDirectory;
|
||
}
|
||
|
||
private string GetTemplateDirFullPath()
|
||
{
|
||
return Path.Combine(GetAppBaseDir(), TemplateDir);
|
||
}
|
||
|
||
private string GetSelectedTemplatePath()
|
||
{
|
||
string name = cmb报表模板.Text;
|
||
if (string.IsNullOrEmpty(name)) return null;
|
||
return Path.Combine(GetTemplateDirFullPath(), name + ".xlsx");
|
||
}
|
||
|
||
/// <summary>把字符串清理为合法文件名(非法字符替换为 _)。</summary>
|
||
private static string MakeSafeName(string s)
|
||
{
|
||
if (string.IsNullOrEmpty(s)) return "未命名";
|
||
string r = s;
|
||
foreach (char c in Path.GetInvalidFileNameChars()) r = r.Replace(c, '_');
|
||
return r;
|
||
}
|
||
|
||
/// <summary>把文本框里的文件名转为完整导出路径(默认存到 exe 所在目录)。</summary>
|
||
private string ResolveOutputPath()
|
||
{
|
||
string fn = (txt导出文件.Text ?? "").Trim();
|
||
if (string.IsNullOrEmpty(fn)) return "";
|
||
// 如果用户填的是完整路径就直接用,否则拼到 exe 目录下
|
||
if (Path.IsPathRooted(fn)) return fn;
|
||
return Path.Combine(GetAppBaseDir(), fn);
|
||
}
|
||
|
||
private string GetQuoteDbPath(string projectId)
|
||
{
|
||
string safeName = projectId;
|
||
foreach (char c in Path.GetInvalidFileNameChars())
|
||
safeName = safeName.Replace(c, '_');
|
||
return Path.Combine(GetAppBaseDir(), @"sjk\bjb\项目" + safeName + ".db");
|
||
}
|
||
|
||
private string GetConnString(string dbPath)
|
||
{
|
||
// ★ A2(2026-10-01):原为裸串(无 WAL/超时,并发易锁死)——统一收敛到 数据库连接.串
|
||
return 数据库连接.串(dbPath);
|
||
}
|
||
|
||
private Dictionary<string, string> LoadProjectInfo()
|
||
{
|
||
var result = new Dictionary<string, string>();
|
||
if (!File.Exists(_quoteDbPath)) return result;
|
||
try
|
||
{
|
||
using (var conn = new SQLiteConnection(GetConnString(_quoteDbPath)))
|
||
{
|
||
conn.Open();
|
||
string sql = "SELECT * FROM 项目信息 LIMIT 1;";
|
||
using (var cmd = new SQLiteCommand(sql, conn))
|
||
using (var reader = cmd.ExecuteReader())
|
||
{
|
||
if (reader.Read())
|
||
{
|
||
for (int i = 0; i < reader.FieldCount; i++)
|
||
{
|
||
string key = reader.GetName(i);
|
||
object val = reader.GetValue(i);
|
||
result[key] = val == DBNull.Value ? "" : val.ToString();
|
||
}
|
||
}
|
||
}
|
||
}
|
||
}
|
||
catch { }
|
||
return result;
|
||
}
|
||
|
||
private List<TuhaoData> LoadTuhaoData()
|
||
{
|
||
var result = new List<TuhaoData>();
|
||
if (!File.Exists(_quoteDbPath)) return result;
|
||
|
||
using (var conn = new SQLiteConnection(GetConnString(_quoteDbPath)))
|
||
{
|
||
conn.Open();
|
||
string sql = "SELECT * FROM " + TuhaoTableName + ";";
|
||
using (var cmd = new SQLiteCommand(sql, conn))
|
||
using (var reader = cmd.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
var t = new TuhaoData
|
||
{
|
||
ShowOrder = SafeStr(reader["序号"]),
|
||
Tuhao = SafeStr(reader["图号"]),
|
||
TuMing = SafeStr(reader["图名"]),
|
||
TaiShu = SafeStr(reader["台数"]),
|
||
XianguiCount = SafeDec(reader["箱柜数量"]),
|
||
UnitPrice = SafeDec(reader["单价"]),
|
||
TotalPrice = SafeDec(reader["总价"]),
|
||
CnTotalPrice = SafeDec(reader["成本总价"]),
|
||
};
|
||
LoadCabinetsForTuhao(conn, t);
|
||
result.Add(t);
|
||
}
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
private void LoadCabinetsForTuhao(SQLiteConnection conn, TuhaoData tuhao)
|
||
{
|
||
string realTbl = LookupTableByIndex(conn, "箱号", tuhao.Tuhao, "");
|
||
if (string.IsNullOrEmpty(realTbl)) return;
|
||
|
||
try
|
||
{
|
||
string sql = "SELECT * FROM \"" + realTbl + "\";";
|
||
using (var cmd = new SQLiteCommand(sql, conn))
|
||
using (var reader = cmd.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
var c = new CabinetData
|
||
{
|
||
ShowOrder = SafeStr(reader["序号"]),
|
||
CabinetNo = SafeStr(reader["箱号"]),
|
||
BoxName = SafeStrCol(reader, "箱名"), // 老库没有此列时返回空串
|
||
Model = SafeStr(reader["型号"]),
|
||
SpecName = SafeStr(reader["规格"]),
|
||
Unit = SafeStr(reader["单位"]),
|
||
Count = SafeStr(reader["数量"]),
|
||
OfferUnitPrice = SafeDec(reader["单台报出"]),
|
||
OfferTotalPrice = SafeDec(reader["报出价"]),
|
||
Remark = SafeStr(reader["备注"]),
|
||
CabinetName = tuhao.TuMing,
|
||
// ★ 查元件表用内部ID(改名不丢明细)
|
||
InnerId = SafeStrCol(reader, "内部ID"),
|
||
};
|
||
LoadElementsForCabinet(conn, tuhao.Tuhao, c);
|
||
tuhao.Cabinets.Add(c);
|
||
}
|
||
}
|
||
}
|
||
catch { }
|
||
}
|
||
|
||
private void LoadElementsForCabinet(SQLiteConnection conn, string tuhao, CabinetData cab)
|
||
{
|
||
// ★ 查元件表用【内部ID】(用户改箱号显示名后仍能查到;旧逻辑按显示名查会丢明细)
|
||
string 查询键 = string.IsNullOrWhiteSpace(cab.InnerId) ? cab.CabinetNo : cab.InnerId;
|
||
string realTbl = LookupTableByIndex(conn, "元件", tuhao, 查询键);
|
||
if (string.IsNullOrEmpty(realTbl)) return;
|
||
|
||
try
|
||
{
|
||
string sql = "SELECT * FROM \"" + realTbl + "\";";
|
||
using (var cmd = new SQLiteCommand(sql, conn))
|
||
using (var reader = cmd.ExecuteReader())
|
||
{
|
||
while (reader.Read())
|
||
{
|
||
var e = new ElementData
|
||
{
|
||
ShowOrder = SafeStr(reader["序号"]),
|
||
Name = SafeStr(reader["元件名称"]),
|
||
Spec = SafeStr(reader["规格"]),
|
||
Unit = SafeStr(reader["单位"]),
|
||
Count = SafeStr(reader["数量"]),
|
||
OfferUnitPrice = SafeDec(reader["报出单价"]),
|
||
OfferTotalPrice = SafeDec(reader["报出价"]),
|
||
DrawingSpec = SafeStr(reader["图纸规格"]),
|
||
FactName = SafeStr(reader["品牌"]),
|
||
Remark = SafeStr(reader["备注"]),
|
||
};
|
||
e.ShowSpecName = string.IsNullOrEmpty(e.DrawingSpec) ? e.Spec : e.DrawingSpec;
|
||
cab.Elements.Add(e);
|
||
}
|
||
}
|
||
}
|
||
catch { }
|
||
}
|
||
|
||
private string LookupTableByIndex(SQLiteConnection conn, string type,
|
||
string tuhao, string xianghao)
|
||
{
|
||
try
|
||
{
|
||
string sql = "SELECT 实际表名 FROM 表索引 WHERE 类型=@t AND 图号=@th AND 箱号=@xh LIMIT 1;";
|
||
using (var cmd = new SQLiteCommand(sql, conn))
|
||
{
|
||
cmd.Parameters.AddWithValue("@t", type);
|
||
cmd.Parameters.AddWithValue("@th", tuhao);
|
||
cmd.Parameters.AddWithValue("@xh", xianghao ?? "");
|
||
object o = cmd.ExecuteScalar();
|
||
return (o == null || o == DBNull.Value) ? "" : o.ToString();
|
||
}
|
||
}
|
||
catch { return ""; }
|
||
}
|
||
|
||
// ====== Excel 导出(纯 C# zip+xml 操作,不依赖任何 Excel 库) ======
|
||
|
||
private void ExportExcel(string templatePath, string outputPath,
|
||
List<TuhaoData> tuhaoList,
|
||
Dictionary<string, string> projInfo,
|
||
Dictionary<string, string> companyInfo,
|
||
bool useFormula, bool useHyperlink)
|
||
{
|
||
// 1. 复制模板到输出路径
|
||
File.Copy(templatePath, outputPath, true);
|
||
|
||
// 2. 构建占位符映射
|
||
var map = BuildPlaceholderMap(tuhaoList, projInfo, companyInfo);
|
||
|
||
// 3. 读取 xlsx (zip) 中的 sharedStrings.xml 和所有 sheet
|
||
// 先解析 sharedStrings,把占位符替换为实际值
|
||
// 同时处理 sheet 中的行数据填充
|
||
var entries = new Dictionary<string, byte[]>(); // entryName -> content
|
||
|
||
using (var archive = System.IO.Compression.ZipFile.OpenRead(outputPath))
|
||
{
|
||
foreach (var entry in archive.Entries)
|
||
{
|
||
using (var s = entry.Open())
|
||
using (var ms = new MemoryStream())
|
||
{
|
||
s.CopyTo(ms);
|
||
entries[entry.FullName] = ms.ToArray();
|
||
}
|
||
}
|
||
}
|
||
|
||
// 4. 解析并替换 sharedStrings.xml(如果有)
|
||
bool hasSharedStrings = false;
|
||
foreach (var key in new List<string>(entries.Keys))
|
||
{
|
||
if (key == "xl/sharedStrings.xml")
|
||
{
|
||
hasSharedStrings = true;
|
||
string xml = System.Text.Encoding.UTF8.GetString(entries[key]);
|
||
xml = ReplacePlaceholdersInXml(xml, map);
|
||
entries[key] = System.Text.Encoding.UTF8.GetBytes(xml);
|
||
}
|
||
}
|
||
|
||
// 5. 如果没有 sharedStrings,字符串可能内联在 sheet 的 inlineStr 中
|
||
// 或者完全不存在(空模板)——遍历所有 sheet 做替换
|
||
if (!hasSharedStrings)
|
||
{
|
||
foreach (var key in new List<string>(entries.Keys))
|
||
{
|
||
if (key.StartsWith("xl/worksheets/sheet") && key.EndsWith(".xml"))
|
||
{
|
||
string xml = System.Text.Encoding.UTF8.GetString(entries[key]);
|
||
xml = ReplacePlaceholdersInXml(xml, map);
|
||
entries[key] = System.Text.Encoding.UTF8.GetBytes(xml);
|
||
}
|
||
}
|
||
}
|
||
|
||
// 5.5 处理 styles.xml: 根据项目设置的"项目小数位"调整数字格式的小数位数
|
||
// 模板中价格单元格用了 0.00 / #,##0.00 等格式
|
||
// 需根据报价软件中"项目小数位"设置(保留整数/保留N位小数)动态调整
|
||
int priceDecimals = 2; // 默认2位
|
||
if (projInfo != null && projInfo.ContainsKey("项目小数位"))
|
||
{
|
||
int n = 价格格式化.解析小数位(projInfo["项目小数位"]);
|
||
if (n >= 0) priceDecimals = n;
|
||
}
|
||
// 新的小数部分: 0位→空(整数), N位→.后跟N个0
|
||
string decPart = priceDecimals > 0 ? "." + new string('0', priceDecimals) : "";
|
||
foreach (var key in new List<string>(entries.Keys))
|
||
{
|
||
if (key == "xl/styles.xml")
|
||
{
|
||
string xml = System.Text.Encoding.UTF8.GetString(entries[key]);
|
||
var sdoc = new XmlDocument();
|
||
sdoc.LoadXml(xml);
|
||
var snsmgr = new XmlNamespaceManager(sdoc.NameTable);
|
||
snsmgr.AddNamespace("s", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
foreach (XmlNode nf in sdoc.SelectNodes("//s:numFmt", snsmgr))
|
||
{
|
||
string code = ((XmlElement)nf).GetAttribute("formatCode");
|
||
if (string.IsNullOrEmpty(code)) continue;
|
||
// 把所有 .0+ 替换为新的小数部分
|
||
string newCode = System.Text.RegularExpressions.Regex.Replace(code, @"\.0+", decPart);
|
||
if (newCode != code)
|
||
((XmlElement)nf).SetAttribute("formatCode", newCode);
|
||
}
|
||
entries[key] = System.Text.Encoding.UTF8.GetBytes(sdoc.OuterXml);
|
||
}
|
||
}
|
||
|
||
// 6. 填充汇总表和分项表数据行
|
||
var dbgLog = new System.Text.StringBuilder();
|
||
dbgLog.AppendLine("hasSharedStrings=" + hasSharedStrings);
|
||
foreach (var key in entries.Keys)
|
||
{
|
||
if (key.StartsWith("xl/worksheets/sheet"))
|
||
dbgLog.AppendLine(" sheet entry: " + key + " size=" + entries[key].Length);
|
||
}
|
||
FillSummaryAndDetail(entries, tuhaoList, map, dbgLog, useFormula, useHyperlink);
|
||
File.WriteAllText(Path.Combine(GetAppBaseDir(), "甲方报表_调试.log"), dbgLog.ToString());
|
||
|
||
// ★ 公式单元格无缓存值:强制 Excel 打开时全量重算,否则公式列显示 0/空
|
||
string wbKey = "xl/workbook.xml";
|
||
if (entries.ContainsKey(wbKey))
|
||
{
|
||
string wb = System.Text.Encoding.UTF8.GetString(entries[wbKey]);
|
||
if (wb.Contains("<calcPr"))
|
||
{
|
||
if (!wb.Contains("fullCalcOnLoad"))
|
||
wb = System.Text.RegularExpressions.Regex.Replace(wb, "<calcPr([^>]*)/>",
|
||
m => "<calcPr" + m.Groups[1].Value + " fullCalcOnLoad=\"1\"/>");
|
||
}
|
||
else if (wb.Contains("</workbook>"))
|
||
{
|
||
wb = wb.Replace("</workbook>", "<calcPr calcId=\"191029\" fullCalcOnLoad=\"1\"/></workbook>");
|
||
}
|
||
entries[wbKey] = System.Text.Encoding.UTF8.GetBytes(wb);
|
||
}
|
||
|
||
// 7. 写回 xlsx
|
||
File.Delete(outputPath);
|
||
using (var fs = new FileStream(outputPath, FileMode.Create))
|
||
using (var archive = new System.IO.Compression.ZipArchive(fs, System.IO.Compression.ZipArchiveMode.Create))
|
||
{
|
||
foreach (var kv in entries)
|
||
{
|
||
var entry = archive.CreateEntry(kv.Key);
|
||
using (var s = entry.Open())
|
||
s.Write(kv.Value, 0, kv.Value.Length);
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>构建占位符映射表</summary>
|
||
private Dictionary<string, string> BuildPlaceholderMap(
|
||
List<TuhaoData> tuhaoList,
|
||
Dictionary<string, string> projInfo,
|
||
Dictionary<string, string> companyInfo)
|
||
{
|
||
var map = new Dictionary<string, string>();
|
||
map["[Project.ProjectNo]"] = GetDict(projInfo, "项目编号");
|
||
map["[Project.ProjectName]"] = GetDict(projInfo, "项目名称");
|
||
map["[Project.CreateTime]"] = GetDict(projInfo, "项目日期");
|
||
map["[Project.CustomerName]"] = GetDict(projInfo, "客户名称");
|
||
map["[Project.CustomerContact]"] = GetDict(projInfo, "联系人");
|
||
map["[Project.CustomerPhone]"] = GetDict(projInfo, "联系电话");
|
||
map["[Company.CompanyName]"] = GetDict(companyInfo, "企业名称");
|
||
map["[Company.Contact]"] = GetDict(companyInfo, "联系人");
|
||
map["[Company.Phone]"] = GetDict(companyInfo, "电话");
|
||
map["[PaperModel.PaperDesc]"] = "按图号";
|
||
|
||
// ★ 诊断日志:记录完整计算过程,便于排查"导出总价 vs 软件显示总价"不一致
|
||
var calcLog = new System.Text.StringBuilder();
|
||
calcLog.AppendLine("====== BuildPlaceholderMap 计算日志 ======");
|
||
calcLog.AppendLine("projInfo 中的小数位设置:");
|
||
if (projInfo != null)
|
||
{
|
||
foreach (var kv in projInfo)
|
||
{
|
||
if (kv.Key.Contains("小数位") || kv.Key.Contains("取整"))
|
||
calcLog.AppendLine(" " + kv.Key + " = [" + kv.Value + "]");
|
||
}
|
||
}
|
||
else
|
||
{
|
||
calcLog.AppendLine(" projInfo == null!");
|
||
}
|
||
|
||
decimal totalQty = 0, totalOffer = 0;
|
||
foreach (var t in tuhaoList)
|
||
{
|
||
// 项目总台数=所有图号台数合计(与汇总表总计行G列SUM公式一致)
|
||
totalQty += SafeDec(t.TaiShu);
|
||
// 图号合计(1台设备价格)=SUM(所有箱号的(单台合计×箱号数量))
|
||
decimal tuhaoSubtotal = 0;
|
||
calcLog.AppendLine("图号[" + t.Tuhao + "] 图名[" + t.TuMing + "] 台数[" + t.TaiShu + "] 箱号数[" + t.Cabinets.Count + "]");
|
||
foreach (var c in t.Cabinets)
|
||
{
|
||
decimal singleTotal = 0;
|
||
foreach (var e in c.Elements)
|
||
{
|
||
singleTotal += SafeDec(e.Count) * e.OfferUnitPrice;
|
||
calcLog.AppendLine(" 元件[" + e.Name + "] 数量[" + e.Count + "] 报出单价[" + e.OfferUnitPrice + "] 报出价[" + (SafeDec(e.Count) * e.OfferUnitPrice) + "]");
|
||
}
|
||
// ★ 不提前四舍五入:与软件计算链一致(软件全程用小数累加,显示时才按小数位格式化)
|
||
// 提前 ROUND 会让多个箱号的小数部分被截断,导致总价偏小(如 23249→23247)
|
||
calcLog.AppendLine(" 箱号[" + c.CabinetNo + "] 数量[" + c.Count + "] 单台合计(未舍入)[" + singleTotal + "] 箱号总计[" + (singleTotal * SafeDec(c.Count)) + "]");
|
||
tuhaoSubtotal += singleTotal * SafeDec(c.Count);
|
||
}
|
||
// 图号总计=图号合计×台数, 项目总计=SUM(所有图号总计)
|
||
calcLog.AppendLine(" 图号合计[" + tuhaoSubtotal + "] × 台数[" + t.TaiShu + "] = 图号总计[" + (tuhaoSubtotal * SafeDec(t.TaiShu)) + "]");
|
||
totalOffer += tuhaoSubtotal * SafeDec(t.TaiShu);
|
||
}
|
||
calcLog.AppendLine("项目总价(未舍入) = " + totalOffer);
|
||
map["[Project.Qty]"] = totalQty.ToString("0.##");
|
||
|
||
// 总报价金额格式: 根据项目"项目小数位"设置决定显示精度
|
||
// (与 styles.xml 数字格式保持一致,确保C列显示与价格列一致)
|
||
int priceDec = 2; // 默认2位
|
||
if (projInfo != null && projInfo.ContainsKey("项目小数位"))
|
||
{
|
||
int n = 价格格式化.解析小数位(projInfo["项目小数位"]);
|
||
if (n >= 0) priceDec = n;
|
||
}
|
||
// ★ 同时检查"单台小数位"(软件图号表用这个显示,可能与"项目小数位"不同)
|
||
int danTaiDec = -1;
|
||
if (projInfo != null && projInfo.ContainsKey("单台小数位"))
|
||
{
|
||
danTaiDec = 价格格式化.解析小数位(projInfo["单台小数位"]);
|
||
}
|
||
calcLog.AppendLine("priceDec(项目小数位)=" + priceDec + " danTaiDec(单台小数位)=" + danTaiDec);
|
||
string priceFmt = priceDec > 0 ? "0." + new string('0', priceDec) : "0";
|
||
// 按"项目小数位"设置统一四舍五入,确保显示数字与大写金额完全一致(与软件显示行为对齐)
|
||
decimal roundedTotalOffer = Math.Round(totalOffer, priceDec, MidpointRounding.AwayFromZero);
|
||
calcLog.AppendLine("roundedTotalOffer(按" + priceDec + "位舍入) = " + roundedTotalOffer);
|
||
calcLog.AppendLine("priceFmt = [" + priceFmt + "]");
|
||
calcLog.AppendLine("[Project.ProjectOfferTotalAmt] = [" + roundedTotalOffer.ToString(priceFmt) + "]");
|
||
calcLog.AppendLine("[Project.CnAmt] = [" + ConvertToChineseAmount(roundedTotalOffer) + "]");
|
||
calcLog.AppendLine("====== 计算日志结束 ======");
|
||
try
|
||
{
|
||
System.IO.File.WriteAllText(System.IO.Path.Combine(GetAppBaseDir(), "甲方报表_计算日志.txt"), calcLog.ToString());
|
||
}
|
||
catch { }
|
||
map["[Project.ProjectOfferTotalAmt]"] = roundedTotalOffer.ToString(priceFmt);
|
||
map["[Project.CnAmt]"] = ConvertToChineseAmount(roundedTotalOffer);
|
||
map["[PaperModel.Qty]"] = totalQty.ToString("0.##");
|
||
map["[PaperModel.OfferTotalPrice]"] = roundedTotalOffer.ToString(priceFmt);
|
||
return map;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 将金额转换为中文大写(符合财务规范)。
|
||
/// 例: 1234.56 → 壹仟贰佰叁拾肆元伍角陆分
|
||
/// 10000.00 → 壹万元整
|
||
/// </summary>
|
||
private static string ConvertToChineseAmount(decimal amount)
|
||
{
|
||
string[] digit = { "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖" };
|
||
string[] posUnit = { "", "拾", "佰", "仟" }; // 段内单位(个/十/百/千)
|
||
string[] segUnit = { "", "万", "亿", "万亿" }; // 段间单位
|
||
|
||
bool negative = amount < 0;
|
||
amount = Math.Abs(amount);
|
||
|
||
// 四舍五入到分
|
||
long fenTotal = (long)Math.Round(amount * 100m);
|
||
long intPart = fenTotal / 100;
|
||
int jiao = (int)((fenTotal / 10) % 10);
|
||
int fen = (int)(fenTotal % 10);
|
||
|
||
if (intPart == 0 && jiao == 0 && fen == 0)
|
||
return "零元整";
|
||
|
||
// 整数部分: 按4位一段分组转换
|
||
string intStr = "";
|
||
if (intPart > 0)
|
||
{
|
||
string numStr = intPart.ToString();
|
||
int segCount = (numStr.Length + 3) / 4;
|
||
numStr = numStr.PadLeft(segCount * 4, '0');
|
||
|
||
var sb = new System.Text.StringBuilder();
|
||
bool lastSegHadNum = false;
|
||
|
||
for (int s = 0; s < segCount; s++)
|
||
{
|
||
string seg = numStr.Substring(s * 4, 4);
|
||
bool segAllZero = true;
|
||
string segStr = "";
|
||
bool innerZero = false;
|
||
|
||
for (int i = 0; i < 4; i++)
|
||
{
|
||
int d = seg[i] - '0';
|
||
if (d == 0)
|
||
{
|
||
innerZero = true;
|
||
}
|
||
else
|
||
{
|
||
segAllZero = false;
|
||
if (innerZero && segStr.Length > 0)
|
||
segStr += "零";
|
||
segStr += digit[d] + posUnit[3 - i];
|
||
innerZero = false;
|
||
}
|
||
}
|
||
|
||
if (!segAllZero)
|
||
{
|
||
// 段间补零: 上一段有数字且当前段以零开头
|
||
if (lastSegHadNum && seg[0] == '0')
|
||
sb.Append("零");
|
||
sb.Append(segStr);
|
||
sb.Append(segUnit[segCount - 1 - s]);
|
||
lastSegHadNum = true;
|
||
}
|
||
}
|
||
intStr = sb.ToString() + "元";
|
||
}
|
||
|
||
// 角分部分
|
||
string decStr = "";
|
||
if (jiao == 0 && fen == 0)
|
||
{
|
||
decStr = "整";
|
||
}
|
||
else
|
||
{
|
||
if (intPart > 0 && jiao == 0)
|
||
decStr += "零";
|
||
if (jiao > 0)
|
||
decStr += digit[jiao] + "角";
|
||
if (fen > 0)
|
||
decStr += digit[fen] + "分";
|
||
}
|
||
|
||
string result = intStr + decStr;
|
||
if (negative) result = "负" + result;
|
||
return result;
|
||
}
|
||
|
||
/// <summary>在 XML 文本中替换所有占位符(XML实体安全)</summary>
|
||
private string ReplacePlaceholdersInXml(string xml, Dictionary<string, string> map)
|
||
{
|
||
// 占位符在 XML 中可能以原始文本或数字实体形式出现
|
||
// 直接对整个 XML 字符串做 Replace 即可(占位符是 ASCII 字符,不会被实体编码)
|
||
foreach (var kv in map)
|
||
{
|
||
xml = xml.Replace(kv.Key, XmlEscape(kv.Value));
|
||
}
|
||
return xml;
|
||
}
|
||
|
||
/// <summary>XML 实体转义</summary>
|
||
private static string XmlEscape(string s)
|
||
{
|
||
if (string.IsNullOrEmpty(s)) return "";
|
||
return s.Replace("&", "&").Replace("<", "<").Replace(">", ">");
|
||
}
|
||
|
||
// ====== 汇总表和分项表填充 ======
|
||
|
||
// 记录每个箱号在分项表中的关键行号(供汇总表公式引用)
|
||
private class CabinetRowInfo
|
||
{
|
||
public int CabRow; // 柜号行
|
||
public int SingleTotalRow; // 单台合计行
|
||
public int GrandRow; // 总计行(单台×数量)
|
||
}
|
||
|
||
private void FillSummaryAndDetail(Dictionary<string, byte[]> entries,
|
||
List<TuhaoData> tuhaoList, Dictionary<string, string> map, System.Text.StringBuilder dbgLog,
|
||
bool useFormula, bool useHyperlink)
|
||
{
|
||
if (tuhaoList.Count == 0) return;
|
||
|
||
var sheetKeys = new List<string>();
|
||
foreach (var key in entries.Keys)
|
||
if (key.StartsWith("xl/worksheets/sheet") && key.EndsWith(".xml"))
|
||
sheetKeys.Add(key);
|
||
sheetKeys.Sort();
|
||
|
||
// 先填分项表,记录每个箱号的行号
|
||
var cabRowInfos = new List<CabinetRowInfo>();
|
||
if (sheetKeys.Count >= 3)
|
||
{
|
||
dbgLog.AppendLine("调用 FillDetailSheet: " + sheetKeys[2]);
|
||
FillDetailSheet(entries, sheetKeys[2], tuhaoList, cabRowInfos, dbgLog, useFormula);
|
||
dbgLog.AppendLine("cabRowInfos.Count=" + cabRowInfos.Count);
|
||
}
|
||
|
||
// 再填汇总表,用公式引用分项表的行号
|
||
if (sheetKeys.Count >= 2)
|
||
{
|
||
dbgLog.AppendLine("调用 FillSummarySheet: " + sheetKeys[1]);
|
||
FillSummarySheet(entries, sheetKeys[1], tuhaoList, cabRowInfos, dbgLog, useFormula, useHyperlink);
|
||
}
|
||
}
|
||
|
||
// ====== 汇总表 ======
|
||
// 模板结构: 行11=图名行([PaperModel.PaperDesc]), 行12=表头, 行13=数据行, 行14=合计, 行15=总计
|
||
// 每个图号: 1行图名 + 表头 + N行箱号数据(公式引用分项表), 然后合计行, 最后总计行
|
||
|
||
private void FillSummarySheet(Dictionary<string, byte[]> entries, string sheetKey,
|
||
List<TuhaoData> tuhaoList, List<CabinetRowInfo> cabRowInfos, System.Text.StringBuilder dbgLog,
|
||
bool useFormula, bool useHyperlink)
|
||
{
|
||
string xml = System.Text.Encoding.UTF8.GetString(entries[sheetKey]);
|
||
var doc = new XmlDocument();
|
||
doc.LoadXml(xml);
|
||
|
||
XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
|
||
nsmgr.AddNamespace("s", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
|
||
XmlNode sheetData = doc.SelectSingleNode("//s:sheetData", nsmgr);
|
||
if (sheetData == null) { dbgLog.AppendLine(" [汇总] sheetData=null,返回"); return; }
|
||
|
||
// 找模板行
|
||
XmlNode tplPaperRow = null; // 行11: 图名行
|
||
XmlNode tplHeaderRow = null; // 行12: 表头行
|
||
XmlNode tplDataRow = null; // 行13: 数据行
|
||
XmlNode tplTotalRow = null; // 行14: 合计行
|
||
XmlNode tplGrandRow = null; // 行15: 总计行
|
||
XmlNode tplExplainRow = null; // 行16: 报价说明行
|
||
XmlNode tplSignRow = null; // 行17: 报价人/审核人行
|
||
|
||
foreach (XmlNode row in sheetData.SelectNodes("s:row", nsmgr))
|
||
{
|
||
string r = ((XmlElement)row).GetAttribute("r");
|
||
if (r == "11") tplPaperRow = row;
|
||
else if (r == "12") tplHeaderRow = row;
|
||
else if (r == "13") tplDataRow = row;
|
||
else if (r == "14") tplTotalRow = row;
|
||
else if (r == "15") tplGrandRow = row;
|
||
else if (r == "16") tplExplainRow = row;
|
||
else if (r == "17") tplSignRow = row;
|
||
}
|
||
|
||
dbgLog.AppendLine(" [汇总] tplPaperRow=" + (tplPaperRow != null) + " tplHeaderRow=" + (tplHeaderRow != null) + " tplDataRow=" + (tplDataRow != null) + " tplTotalRow=" + (tplTotalRow != null) + " tplGrandRow=" + (tplGrandRow != null) + " tplExplainRow=" + (tplExplainRow != null) + " tplSignRow=" + (tplSignRow != null));
|
||
|
||
if (tplDataRow == null) { dbgLog.AppendLine(" [汇总] tplDataRow=null,返回"); return; }
|
||
|
||
// 克隆报价说明行、报价人行(稍后追加到末尾)
|
||
XmlNode explainTplClone = tplExplainRow != null ? tplExplainRow.CloneNode(true) : null;
|
||
XmlNode signTplClone = tplSignRow != null ? tplSignRow.CloneNode(true) : null;
|
||
|
||
// 计算总箱号数
|
||
int totalCabinets = 0;
|
||
foreach (var t in tuhaoList) totalCabinets += t.Cabinets.Count;
|
||
|
||
// 记录柜号超链接: (汇总表行号, 分项表行号) 用于生成 <hyperlinks>
|
||
var cabLinks = new List<KeyValuePair<int, int>>();
|
||
|
||
// 在 styles.xml 中添加超链接样式(基于B列原始样式s=51,只把fontId改为14蓝色下划线)
|
||
int hyperlinkStyleIdx = -1;
|
||
if (useHyperlink)
|
||
foreach (var stk in new List<string>(entries.Keys))
|
||
{
|
||
if (stk != "xl/styles.xml") continue;
|
||
string sxml = System.Text.Encoding.UTF8.GetString(entries[stk]);
|
||
var sdoc = new XmlDocument();
|
||
sdoc.LoadXml(sxml);
|
||
var snsmgr = new XmlNamespaceManager(sdoc.NameTable);
|
||
snsmgr.AddNamespace("s", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
XmlNode cellXfsNode = sdoc.SelectSingleNode("//s:cellXfs", snsmgr);
|
||
if (cellXfsNode != null)
|
||
{
|
||
int curCount = 0;
|
||
int.TryParse(((XmlElement)cellXfsNode).GetAttribute("count"), out curCount);
|
||
// 基于B列原始样式(cellXfs[51]): 保留边框borderId=16/对齐/保护,只改fontId=14(蓝色下划线)
|
||
XmlElement newXf = sdoc.CreateElement("xf", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
newXf.SetAttribute("numFmtId", "49");
|
||
newXf.SetAttribute("fontId", "14");
|
||
newXf.SetAttribute("fillId", "0");
|
||
newXf.SetAttribute("borderId", "16");
|
||
newXf.SetAttribute("xfId", "0");
|
||
newXf.SetAttribute("applyNumberFormat", "1");
|
||
newXf.SetAttribute("applyFont", "1");
|
||
newXf.SetAttribute("applyFill", "1");
|
||
newXf.SetAttribute("applyBorder", "1");
|
||
newXf.SetAttribute("applyAlignment", "1");
|
||
newXf.SetAttribute("applyProtection", "1");
|
||
XmlElement align = sdoc.CreateElement("alignment", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
align.SetAttribute("horizontal", "left");
|
||
align.SetAttribute("vertical", "center");
|
||
newXf.AppendChild(align);
|
||
XmlElement prot = sdoc.CreateElement("protection", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
prot.SetAttribute("locked", "0");
|
||
newXf.AppendChild(prot);
|
||
cellXfsNode.AppendChild(newXf);
|
||
hyperlinkStyleIdx = curCount;
|
||
((XmlElement)cellXfsNode).SetAttribute("count", (curCount + 1).ToString());
|
||
}
|
||
entries[stk] = System.Text.Encoding.UTF8.GetBytes(sdoc.OuterXml);
|
||
break;
|
||
}
|
||
|
||
// 清理 mergeCells 中涉及行 >=11 的合并区域(这些行会被重建)
|
||
XmlNode mergeCellsNode = doc.SelectSingleNode("//s:mergeCells", nsmgr);
|
||
if (mergeCellsNode != null)
|
||
{
|
||
var toRemove = new List<XmlNode>();
|
||
foreach (XmlNode mc in mergeCellsNode.SelectNodes("s:mergeCell", nsmgr))
|
||
{
|
||
string range = ((XmlElement)mc).GetAttribute("ref");
|
||
if (MergeCellRangeOverlapsRow(range, 11))
|
||
toRemove.Add(mc);
|
||
}
|
||
foreach (XmlNode mc in toRemove) mergeCellsNode.RemoveChild(mc);
|
||
// 更新 count 属性
|
||
int remain = mergeCellsNode.SelectNodes("s:mergeCell", nsmgr).Count;
|
||
if (remain == 0)
|
||
{
|
||
doc.DocumentElement.RemoveChild(mergeCellsNode);
|
||
mergeCellsNode = null;
|
||
}
|
||
else
|
||
{
|
||
((XmlElement)mergeCellsNode).SetAttribute("count", remain.ToString());
|
||
}
|
||
}
|
||
|
||
// 删除原模板行 11~17
|
||
if (tplSignRow != null) sheetData.RemoveChild(tplSignRow);
|
||
if (tplExplainRow != null) sheetData.RemoveChild(tplExplainRow);
|
||
if (tplGrandRow != null) sheetData.RemoveChild(tplGrandRow);
|
||
if (tplTotalRow != null) sheetData.RemoveChild(tplTotalRow);
|
||
if (tplDataRow != null) sheetData.RemoveChild(tplDataRow);
|
||
if (tplHeaderRow != null) sheetData.RemoveChild(tplHeaderRow);
|
||
if (tplPaperRow != null) sheetData.RemoveChild(tplPaperRow);
|
||
|
||
// 找到插入点 (行10 之后)
|
||
XmlNode insertAfter = null;
|
||
foreach (XmlNode row in sheetData.SelectNodes("s:row", nsmgr))
|
||
{
|
||
if (((XmlElement)row).GetAttribute("r") == "10")
|
||
{
|
||
insertAfter = row;
|
||
break;
|
||
}
|
||
}
|
||
|
||
// 逐图号生成行,记录每个图号区域的合计行号(供总计行SUM引用)
|
||
int rowNum = 11;
|
||
var subtotalRows = new List<int>(); // 每个图号的合计行行号
|
||
var tuhaoTotalRows = new List<int>(); // 每个图号的总计行行号(供总计行SUM引用)
|
||
int cabInfoIndex = 0; // 全局箱号索引(对应 cabRowInfos)
|
||
int tuhaoIdx = 0; // 图号序号(0基)
|
||
|
||
// 为图号总计行创建带橙色背景(fillId=5)的样式(基于汇总表合计行各列自身样式)
|
||
var totalStyleMap = new Dictionary<string, string>(); // 原样式索引 -> 新样式索引
|
||
if (tplTotalRow != null)
|
||
{
|
||
string sxml = System.Text.Encoding.UTF8.GetString(entries["xl/styles.xml"]);
|
||
var sdoc = new XmlDocument();
|
||
sdoc.LoadXml(sxml);
|
||
var snsmgr = new XmlNamespaceManager(sdoc.NameTable);
|
||
snsmgr.AddNamespace("s", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
XmlNode cellXfsNode = sdoc.SelectSingleNode("//s:cellXfs", snsmgr);
|
||
if (cellXfsNode != null)
|
||
{
|
||
int curCount = 0;
|
||
int.TryParse(((XmlElement)cellXfsNode).GetAttribute("count"), out curCount);
|
||
// 收集合计行各列的样式索引(去重)
|
||
var baseStyles = new HashSet<string>();
|
||
foreach (XmlNode cell in tplTotalRow.ChildNodes)
|
||
{
|
||
if (!(cell is XmlElement)) continue;
|
||
string s = ((XmlElement)cell).GetAttribute("s");
|
||
if (!string.IsNullOrEmpty(s)) baseStyles.Add(s);
|
||
}
|
||
// 为每个样式创建带橙色背景的副本
|
||
foreach (string baseStyle in baseStyles)
|
||
{
|
||
int baseIdx;
|
||
if (!int.TryParse(baseStyle, out baseIdx)) continue;
|
||
if (baseIdx >= cellXfsNode.ChildNodes.Count) continue;
|
||
XmlNode origXf = cellXfsNode.ChildNodes[baseIdx];
|
||
XmlElement newXf = (XmlElement)origXf.CloneNode(true);
|
||
newXf.SetAttribute("fillId", "4"); // 灰色背景(与图号表头一致)
|
||
newXf.SetAttribute("applyFill", "1");
|
||
cellXfsNode.AppendChild(newXf);
|
||
totalStyleMap[baseStyle] = curCount.ToString();
|
||
curCount++;
|
||
}
|
||
((XmlElement)cellXfsNode).SetAttribute("count", curCount.ToString());
|
||
entries["xl/styles.xml"] = System.Text.Encoding.UTF8.GetBytes(sdoc.OuterXml);
|
||
}
|
||
}
|
||
|
||
foreach (var tuhao in tuhaoList)
|
||
{
|
||
string tuhaoName = !string.IsNullOrEmpty(tuhao.TuMing) ? tuhao.TuMing : tuhao.Tuhao;
|
||
int tuhaoCabCount = tuhao.Cabinets.Count;
|
||
|
||
// 1) 图名行
|
||
if (tplPaperRow != null)
|
||
{
|
||
XmlNode paperRow = tplPaperRow.CloneNode(true);
|
||
((XmlElement)paperRow).SetAttribute("r", rowNum.ToString());
|
||
UpdateRowCellReferences(paperRow, rowNum);
|
||
SetCellInRow(paperRow, "A", tuhaoName, nsmgr);
|
||
InsertRowAfter(sheetData, paperRow, ref insertAfter);
|
||
AddMergeCell(doc, ref mergeCellsNode, "A" + rowNum + ":J" + rowNum);
|
||
rowNum++;
|
||
}
|
||
|
||
// 2) 表头行
|
||
if (tplHeaderRow != null)
|
||
{
|
||
XmlNode headerRow = tplHeaderRow.CloneNode(true);
|
||
((XmlElement)headerRow).SetAttribute("r", rowNum.ToString());
|
||
UpdateRowCellReferences(headerRow, rowNum);
|
||
InsertRowAfter(sheetData, headerRow, ref insertAfter);
|
||
rowNum++;
|
||
}
|
||
|
||
// 3) 数据行(用公式引用分项表)
|
||
int dataStartRow = rowNum;
|
||
int cabIdxInTuhao = 0; // 图号内箱号序号(0基)
|
||
foreach (var cab in tuhao.Cabinets)
|
||
{
|
||
XmlNode dataRow = tplDataRow.CloneNode(true);
|
||
((XmlElement)dataRow).SetAttribute("r", rowNum.ToString());
|
||
UpdateRowCellReferences(dataRow, rowNum);
|
||
|
||
// 用公式引用分项表
|
||
if (cabInfoIndex < cabRowInfos.Count)
|
||
{
|
||
var info = cabRowInfos[cabInfoIndex];
|
||
// A列序号: 图号序号-箱号序号 (如 1-1, 1-2, 2-1)
|
||
SetCellInRow(dataRow, "A", (tuhaoIdx + 1) + "-" + (cabIdxInTuhao + 1), nsmgr);
|
||
if (useFormula)
|
||
{
|
||
SetCellFormulaInRow(dataRow, "B", "屏柜分项表!B" + info.CabRow, nsmgr);
|
||
SetCellFormulaInRow(dataRow, "C", "屏柜分项表!G" + info.CabRow, nsmgr);
|
||
SetCellFormulaInRow(dataRow, "D", "屏柜分项表!D" + info.CabRow, nsmgr);
|
||
SetCellFormulaInRow(dataRow, "E", "屏柜分项表!I" + info.CabRow, nsmgr);
|
||
SetCellFormulaInRow(dataRow, "G", "屏柜分项表!E" + info.GrandRow, nsmgr);
|
||
SetCellFormulaInRow(dataRow, "H", "屏柜分项表!G" + info.SingleTotalRow, nsmgr);
|
||
SetCellFormulaInRow(dataRow, "I", "屏柜分项表!G" + info.GrandRow, nsmgr);
|
||
}
|
||
else
|
||
{
|
||
// C#计算: 单台合计=ROUND(SUM(元件数量×单价),0), 箱号总计=单台合计×数量
|
||
decimal elemSum = 0;
|
||
foreach (var elem in cab.Elements) elemSum += SafeDec(elem.Count) * elem.OfferUnitPrice;
|
||
// 不提前四舍五入,与软件计算链一致(小数累加,由Excel数字格式控制显示)
|
||
decimal singleTotal = elemSum;
|
||
decimal cabTotal = singleTotal * SafeDec(cab.Count);
|
||
SetCellInRow(dataRow, "B", cab.CabinetNo, nsmgr);
|
||
// ★ 与公式分支的模板列义对齐:B柜号/C名称(箱名)/D型号/E规格
|
||
// 旧代码 C写型号/D写规格/E写图名 是错位占位
|
||
string 名称值 = !string.IsNullOrEmpty(cab.BoxName) ? cab.BoxName : cab.CabinetName;
|
||
SetCellInRow(dataRow, "C", 名称值, nsmgr);
|
||
SetCellInRow(dataRow, "D", cab.Model, nsmgr);
|
||
SetCellInRow(dataRow, "E", cab.SpecName, nsmgr);
|
||
SetCellNumericInRow(dataRow, "G", SafeDec(cab.Count), nsmgr);
|
||
SetCellNumericInRow(dataRow, "H", singleTotal, nsmgr);
|
||
SetCellNumericInRow(dataRow, "I", cabTotal, nsmgr);
|
||
}
|
||
SetCellInRow(dataRow, "F", "台", nsmgr);
|
||
// B列柜号设置超链接样式(蓝色下划线)
|
||
if (hyperlinkStyleIdx >= 0)
|
||
SetCellStyle(dataRow, "B", hyperlinkStyleIdx, nsmgr);
|
||
}
|
||
SetCellInRow(dataRow, "J", cab.Remark, nsmgr);
|
||
|
||
// 记录柜号超链接: 汇总表B{rowNum} → 分项表B{info.CabRow}
|
||
if (cabInfoIndex < cabRowInfos.Count)
|
||
cabLinks.Add(new KeyValuePair<int, int>(rowNum, cabRowInfos[cabInfoIndex].CabRow));
|
||
|
||
InsertRowAfter(sheetData, dataRow, ref insertAfter);
|
||
rowNum++;
|
||
cabInfoIndex++;
|
||
cabIdxInTuhao++;
|
||
}
|
||
|
||
// 4) 合计行(用SUM公式)
|
||
if (tplTotalRow != null)
|
||
{
|
||
XmlNode totalRow = tplTotalRow.CloneNode(true);
|
||
((XmlElement)totalRow).SetAttribute("r", rowNum.ToString());
|
||
UpdateRowCellReferences(totalRow, rowNum);
|
||
SetCellInRow(totalRow, "B", "合计", nsmgr);
|
||
SetCellInRow(totalRow, "F", "台", nsmgr);
|
||
if (useFormula)
|
||
{
|
||
SetCellFormulaInRow(totalRow, "G", "SUM(G" + dataStartRow + ":G" + (rowNum - 1) + ")", nsmgr);
|
||
SetCellFormulaInRow(totalRow, "I", "SUM(I" + dataStartRow + ":I" + (rowNum - 1) + ")", nsmgr);
|
||
}
|
||
else
|
||
{
|
||
decimal sumQty = 0, sumPrice = 0;
|
||
foreach (var cab in tuhao.Cabinets)
|
||
{
|
||
sumQty += SafeDec(cab.Count);
|
||
decimal elemSum = 0;
|
||
foreach (var elem in cab.Elements) elemSum += SafeDec(elem.Count) * elem.OfferUnitPrice;
|
||
sumPrice += elemSum * SafeDec(cab.Count);
|
||
}
|
||
SetCellNumericInRow(totalRow, "G", sumQty, nsmgr);
|
||
SetCellNumericInRow(totalRow, "I", sumPrice, nsmgr);
|
||
}
|
||
InsertRowAfter(sheetData, totalRow, ref insertAfter);
|
||
subtotalRows.Add(rowNum);
|
||
rowNum++;
|
||
}
|
||
|
||
// 图号总计行(放在合计行下面,台数×合计行总价=图号总价)
|
||
if (tplTotalRow != null)
|
||
{
|
||
XmlNode taiShuRow = tplTotalRow.CloneNode(true);
|
||
((XmlElement)taiShuRow).SetAttribute("r", rowNum.ToString());
|
||
UpdateRowCellReferences(taiShuRow, rowNum);
|
||
SetCellInRow(taiShuRow, "B", "总计", nsmgr);
|
||
SetCellInRow(taiShuRow, "F", "套", nsmgr);
|
||
SetCellNumericInRow(taiShuRow, "G", SafeDec(tuhao.TaiShu), nsmgr);
|
||
// I列=合计行总价(rowNum-1) × 台数(当前行G列)
|
||
if (useFormula)
|
||
SetCellFormulaInRow(taiShuRow, "I", "I" + (rowNum - 1) + "*G" + rowNum, nsmgr);
|
||
else
|
||
{
|
||
decimal sumPrice = 0;
|
||
foreach (var cab in tuhao.Cabinets)
|
||
{
|
||
decimal elemSum = 0;
|
||
foreach (var elem in cab.Elements) elemSum += SafeDec(elem.Count) * elem.OfferUnitPrice;
|
||
sumPrice += elemSum * SafeDec(cab.Count);
|
||
}
|
||
SetCellNumericInRow(taiShuRow, "I", sumPrice * SafeDec(tuhao.TaiShu), nsmgr);
|
||
}
|
||
InsertRowAfter(sheetData, taiShuRow, ref insertAfter);
|
||
// 设置图号总计行各列样式(带橙色背景,与分项表总计行一致)
|
||
foreach (XmlNode cell in taiShuRow.ChildNodes)
|
||
{
|
||
if (!(cell is XmlElement)) continue;
|
||
string origStyle = ((XmlElement)cell).GetAttribute("s");
|
||
if (totalStyleMap.ContainsKey(origStyle))
|
||
((XmlElement)cell).SetAttribute("s", totalStyleMap[origStyle]);
|
||
}
|
||
tuhaoTotalRows.Add(rowNum);
|
||
rowNum++;
|
||
}
|
||
tuhaoIdx++;
|
||
}
|
||
|
||
// 5) 总计行(C列总报价金额、G列总台数用SUM公式, H列大写金额由占位符替换)
|
||
if (tplGrandRow != null)
|
||
{
|
||
XmlNode grandRow = tplGrandRow.CloneNode(true);
|
||
((XmlElement)grandRow).SetAttribute("r", rowNum.ToString());
|
||
UpdateRowCellReferences(grandRow, rowNum);
|
||
SetCellInRow(grandRow, "B", "总计", nsmgr);
|
||
SetCellInRow(grandRow, "F", "套", nsmgr);
|
||
if (useFormula)
|
||
{
|
||
// C列=ROUND(SUM(所有图号总计行I列),0) 总报价金额
|
||
// G列=SUM(所有图号总计行G列) 总台数
|
||
if (tuhaoTotalRows.Count > 0)
|
||
{
|
||
var iRefs = new List<string>();
|
||
var gRefs = new List<string>();
|
||
foreach (int tr in tuhaoTotalRows) { iRefs.Add("I" + tr); gRefs.Add("G" + tr); }
|
||
SetCellFormulaInRow(grandRow, "C", "ROUND(SUM(" + string.Join(",", iRefs.ToArray()) + "),0)", nsmgr);
|
||
SetCellFormulaInRow(grandRow, "G", "SUM(" + string.Join(",", gRefs.ToArray()) + ")", nsmgr);
|
||
}
|
||
// H列大写金额=TEXT(C列,"[DBNum2]")&"元整" (跟着C列总报价金额变化)
|
||
SetCellFormulaInRow(grandRow, "H", "TEXT(C" + rowNum + ",\"[DBNum2]\")&\"元整\"", nsmgr);
|
||
}
|
||
// useFormula=false 时保留模板占位符,由 sharedStrings.xml 替换填充
|
||
InsertRowAfter(sheetData, grandRow, ref insertAfter);
|
||
// 总计行合并区域(模板原行15有 C15:D15 和 H15:J15)
|
||
AddMergeCell(doc, ref mergeCellsNode, "C" + rowNum + ":D" + rowNum);
|
||
AddMergeCell(doc, ref mergeCellsNode, "H" + rowNum + ":J" + rowNum);
|
||
rowNum++;
|
||
}
|
||
|
||
// 6) 追加报价说明行(原模板行16)
|
||
if (explainTplClone != null)
|
||
{
|
||
((XmlElement)explainTplClone).SetAttribute("r", rowNum.ToString());
|
||
UpdateRowCellReferences(explainTplClone, rowNum);
|
||
InsertRowAfter(sheetData, explainTplClone, ref insertAfter);
|
||
AddMergeCell(doc, ref mergeCellsNode, "A" + rowNum + ":J" + rowNum);
|
||
rowNum++;
|
||
}
|
||
|
||
// 7) 追加报价人/审核人行(原模板行17)
|
||
if (signTplClone != null)
|
||
{
|
||
((XmlElement)signTplClone).SetAttribute("r", rowNum.ToString());
|
||
UpdateRowCellReferences(signTplClone, rowNum);
|
||
InsertRowAfter(sheetData, signTplClone, ref insertAfter);
|
||
AddMergeCell(doc, ref mergeCellsNode, "B" + rowNum + ":C" + rowNum);
|
||
AddMergeCell(doc, ref mergeCellsNode, "D" + rowNum + ":G" + rowNum);
|
||
AddMergeCell(doc, ref mergeCellsNode, "H" + rowNum + ":I" + rowNum);
|
||
rowNum++;
|
||
}
|
||
|
||
// 8) 添加柜号超链接(点击B列柜号跳转到分项表对应区域)
|
||
if (useHyperlink && cabLinks.Count > 0)
|
||
{
|
||
XmlNode hyperlinksNode = doc.CreateElement("hyperlinks", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
foreach (var link in cabLinks)
|
||
{
|
||
XmlElement hl = doc.CreateElement("hyperlink", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
hl.SetAttribute("ref", "B" + link.Key);
|
||
hl.SetAttribute("location", "屏柜分项表!B" + link.Value);
|
||
hl.SetAttribute("display", "=屏柜分项表!B" + link.Value);
|
||
hyperlinksNode.AppendChild(hl);
|
||
}
|
||
// 按OOXML规范,hyperlinks在mergeCells之后;若没有mergeCells则放sheetData之后
|
||
XmlNode refNode = mergeCellsNode ?? sheetData;
|
||
refNode.ParentNode.InsertAfter(hyperlinksNode, refNode);
|
||
}
|
||
|
||
entries[sheetKey] = System.Text.Encoding.UTF8.GetBytes(doc.OuterXml);
|
||
}
|
||
|
||
/// <summary>在 insertAfter 之后插入行,并更新 insertAfter</summary>
|
||
private void InsertRowAfter(XmlNode sheetData, XmlNode newRow, ref XmlNode insertAfter)
|
||
{
|
||
if (insertAfter == null)
|
||
sheetData.AppendChild(newRow);
|
||
else
|
||
sheetData.InsertAfter(newRow, insertAfter);
|
||
insertAfter = newRow;
|
||
}
|
||
|
||
/// <summary>确保 mergeCells 节点存在并返回它(按 OOXML 顺序插入到 sheetData 之后)</summary>
|
||
private XmlNode EnsureMergeCells(XmlDocument doc, ref XmlNode mergeCellsNode)
|
||
{
|
||
if (mergeCellsNode != null) return mergeCellsNode;
|
||
XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);
|
||
nsmgr.AddNamespace("s", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
mergeCellsNode = doc.CreateElement("mergeCells", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
((XmlElement)mergeCellsNode).SetAttribute("count", "0");
|
||
// mergeCells 必须位于 sheetData 之后(OOXML schema 顺序)
|
||
XmlNode sheetData = doc.SelectSingleNode("//s:sheetData", nsmgr);
|
||
if (sheetData != null && sheetData.NextSibling != null)
|
||
doc.DocumentElement.InsertAfter(mergeCellsNode, sheetData);
|
||
else if (sheetData != null)
|
||
doc.DocumentElement.AppendChild(mergeCellsNode);
|
||
else
|
||
doc.DocumentElement.AppendChild(mergeCellsNode);
|
||
return mergeCellsNode;
|
||
}
|
||
|
||
/// <summary>添加一个合并区域到 mergeCells,并自动更新 count</summary>
|
||
private void AddMergeCell(XmlDocument doc, ref XmlNode mergeCellsNode, string range)
|
||
{
|
||
XmlNode mc = EnsureMergeCells(doc, ref mergeCellsNode);
|
||
XmlElement me = doc.CreateElement("mergeCell", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
|
||
me.SetAttribute("ref", range);
|
||
mc.AppendChild(me);
|
||
int cnt = 0;
|
||
int.TryParse(((XmlElement)mc).GetAttribute("count"), out cnt);
|
||
((XmlElement)mc).SetAttribute("count", (cnt + 1).ToString());
|
||
}
|
||
|
||
/// <summary>判断合并区域 "A11:J17" 是否与 >= startRow 的行重叠</summary>
|
||
private static bool MergeCellRangeOverlapsRow(string range, int startRow)
|
||
{
|
||
if (string.IsNullOrEmpty(range)) return false;
|
||
string[] parts = range.Split(':');
|
||
if (parts.Length != 2) return false;
|
||
int endRowNum = ParseRowNumber(parts[1]);
|
||
int startRowNum = ParseRowNumber(parts[0]);
|
||
if (endRowNum == 0) endRowNum = startRowNum;
|
||
return endRowNum >= startRow || startRowNum >= startRow;
|
||
}
|
||
|
||
/// <summary>从单元格引用(如 "K13")解析行号</summary>
|
||
private static int ParseRowNumber(string cellRef)
|
||
{
|
||
if (string.IsNullOrEmpty(cellRef)) return 0;
|
||
string digits = "";
|
||
for (int i = 0; i < cellRef.Length; i++)
|
||
{
|
||
if (char.IsDigit(cellRef[i])) digits += cellRef[i];
|
||
}
|
||
int n;
|
||
return int.TryParse(digits, out n) ? n : 0;
|
||
}
|
||
|
||
// ====== 分项表 ======
|
||
|
||
// ============ 明细表:字符串模板替换法(零 DOM——上万像素级 DOM+OuterXml 双份字符串会 OOM) ============
|
||
|
||
/// <summary>行字符串:更新行号与全部单元格引用(r="列行号")。
|
||
/// ★ 用 MatchEvaluator 而非 "$1" 替换串:"$1"+数字会被 .NET 贪婪解析成不存在的分组($1120),原样输出毁掉引用。</summary>
|
||
private static string 行换号(string rowXml, int rowNum)
|
||
{
|
||
rowXml = System.Text.RegularExpressions.Regex.Replace(rowXml, "<row r=\"\\d+\"", m => "<row r=\"" + rowNum + "\"");
|
||
return System.Text.RegularExpressions.Regex.Replace(rowXml, "r=\"([A-Z]+)\\d+\"",
|
||
m => "r=\"" + m.Groups[1].Value + rowNum + "\"");
|
||
}
|
||
|
||
/// <summary>行字符串:设置某列单元格内容(innerXml=单元格内部 XML;tAttr=类型属性,null=移除 t)。</summary>
|
||
private static string 行设单元格(string rowXml, string col, int rowNum, string innerXml, string tAttr)
|
||
{
|
||
string r = col + rowNum;
|
||
var m = System.Text.RegularExpressions.Regex.Match(rowXml, "<c r=\"" + r + "\"([^>]*?)(?:/>|>.*?</c>)");
|
||
if (!m.Success) return rowXml;
|
||
string attrs = m.Groups[1].Value;
|
||
attrs = System.Text.RegularExpressions.Regex.Replace(attrs, "\\s*t=\"[^\"]*\"", "");
|
||
if (!string.IsNullOrEmpty(tAttr)) attrs += " t=\"" + tAttr + "\"";
|
||
return rowXml.Substring(0, m.Index) + "<c r=\"" + r + "\"" + attrs + ">" + innerXml + "</c>" + rowXml.Substring(m.Index + m.Length);
|
||
}
|
||
|
||
private static string 行设文本(string rowXml, string col, int rowNum, string text)
|
||
{ return 行设单元格(rowXml, col, rowNum, "<v>" + XmlEscape(text) + "</v>", "str"); }
|
||
|
||
private static string 行设数值(string rowXml, string col, int rowNum, decimal v)
|
||
{ return 行设单元格(rowXml, col, rowNum, "<v>" + v.ToString(System.Globalization.CultureInfo.InvariantCulture) + "</v>", null); }
|
||
|
||
private static string 行设公式(string rowXml, string col, int rowNum, string formula)
|
||
{ return 行设单元格(rowXml, col, rowNum, "<f>" + XmlEscape(formula) + "</f>", null); }
|
||
|
||
private void FillDetailSheet(Dictionary<string, byte[]> entries, string sheetKey,
|
||
List<TuhaoData> tuhaoList, List<CabinetRowInfo> cabRowInfos, System.Text.StringBuilder dbgLog,
|
||
bool useFormula)
|
||
{
|
||
string xml = System.Text.Encoding.UTF8.GetString(entries[sheetKey]);
|
||
|
||
// 提取全部行块,定位模板行 11~16 与头/尾(零 DOM)
|
||
var 行匹配 = System.Text.RegularExpressions.Regex.Matches(
|
||
xml, "<row r=\"(\\d+)\"[^>]*?(?:/>|>.*?</row>)");
|
||
if (行匹配.Count == 0) return;
|
||
|
||
string[] tpl = new string[6];
|
||
int bodyStart = -1, bodyEnd = -1;
|
||
foreach (System.Text.RegularExpressions.Match m in 行匹配)
|
||
{
|
||
int rn = int.Parse(m.Groups[1].Value);
|
||
if (rn >= 11 && rn <= 16)
|
||
{
|
||
tpl[rn - 11] = m.Value;
|
||
if (bodyStart < 0) bodyStart = m.Index;
|
||
bodyEnd = m.Index + m.Length;
|
||
}
|
||
}
|
||
string tplPaperRow = tpl[0], tplCabRow = tpl[1], tplHeaderRow = tpl[2],
|
||
tplElemRow = tpl[3], tplSingleRow = tpl[4], tplGrandRow = tpl[5];
|
||
if (tplCabRow == null || tplElemRow == null) return;
|
||
|
||
string 头 = xml.Substring(0, bodyStart); // 行1~10
|
||
string 尾 = xml.Substring(bodyEnd); // 行17(签名)及之后
|
||
|
||
var sb = new System.Text.StringBuilder();
|
||
int rowNum = 11;
|
||
decimal grandQty = 0, grandTotal = 0;
|
||
int tuhaoIdx = 0;
|
||
|
||
foreach (var tuhao in tuhaoList)
|
||
{
|
||
string tuhaoName = !string.IsNullOrEmpty(tuhao.TuMing) ? tuhao.TuMing : tuhao.Tuhao;
|
||
|
||
// 图名行
|
||
if (tplPaperRow != null)
|
||
{
|
||
string row = 行换号(tplPaperRow, rowNum);
|
||
row = 行设文本(row, "A", rowNum, tuhaoName);
|
||
sb.Append(row);
|
||
rowNum++;
|
||
}
|
||
|
||
// 每个箱号: 柜号行 + 表头 + 元件行*N + 单台合计 + 总计
|
||
int cabOrder = 0;
|
||
foreach (var cab in tuhao.Cabinets)
|
||
{
|
||
cabOrder++;
|
||
int cabRowNum = rowNum;
|
||
|
||
// 柜号行
|
||
{
|
||
string row = 行换号(tplCabRow, rowNum);
|
||
row = 行设文本(row, "A", rowNum, (tuhaoIdx + 1) + "-" + cabOrder);
|
||
row = 行设文本(row, "B", rowNum, "柜号：" + cab.CabinetNo);
|
||
row = 行设文本(row, "D", rowNum, cab.Model);
|
||
// ★ G列"名称："= 箱名(进线柜/PT柜等);库里没有时退回图名(与旧版行为一致)
|
||
string 名称值 = !string.IsNullOrEmpty(cab.BoxName) ? cab.BoxName : tuhaoName;
|
||
row = 行设文本(row, "G", rowNum, 名称值);
|
||
row = 行设文本(row, "I", rowNum, cab.SpecName);
|
||
sb.Append(row);
|
||
rowNum++;
|
||
}
|
||
|
||
// 表头行
|
||
if (tplHeaderRow != null)
|
||
{
|
||
sb.Append(行换号(tplHeaderRow, rowNum));
|
||
rowNum++;
|
||
}
|
||
|
||
// 元件行(G列用 =E*F 公式)
|
||
int elemStartRow = rowNum;
|
||
if (cab.Elements.Count == 0)
|
||
{
|
||
sb.Append(行换号(tplElemRow, rowNum));
|
||
rowNum++;
|
||
}
|
||
else
|
||
{
|
||
int elemOrder = 0;
|
||
foreach (var elem in cab.Elements)
|
||
{
|
||
elemOrder++;
|
||
string row = 行换号(tplElemRow, rowNum);
|
||
row = 行设文本(row, "A", rowNum, elemOrder.ToString());
|
||
row = 行设文本(row, "B", rowNum, elem.Name);
|
||
row = 行设文本(row, "C", rowNum, elem.ShowSpecName);
|
||
row = 行设文本(row, "D", rowNum, elem.Unit);
|
||
row = 行设数值(row, "E", rowNum, SafeDec(elem.Count));
|
||
row = 行设数值(row, "F", rowNum, elem.OfferUnitPrice);
|
||
// G列: 公式 =E{行}*F{行} 或直接值
|
||
if (useFormula)
|
||
row = 行设公式(row, "G", rowNum, "E" + rowNum + "*F" + rowNum);
|
||
else
|
||
row = 行设数值(row, "G", rowNum, SafeDec(elem.Count) * elem.OfferUnitPrice);
|
||
row = 行设文本(row, "H", rowNum, elem.FactName);
|
||
row = 行设文本(row, "I", rowNum, elem.Remark);
|
||
sb.Append(row);
|
||
rowNum++;
|
||
}
|
||
}
|
||
int elemEndRow = rowNum - 1;
|
||
|
||
// 单台合计行(G列用 =ROUND(SUM(G{起}:G{止}),0) 公式)
|
||
int singleRowNum = rowNum;
|
||
if (tplSingleRow != null)
|
||
{
|
||
string row = 行换号(tplSingleRow, rowNum);
|
||
row = 行设文本(row, "B", rowNum, "单台合计");
|
||
if (useFormula)
|
||
row = 行设公式(row, "G", rowNum, "ROUND(SUM(G" + elemStartRow + ":G" + elemEndRow + "),0)");
|
||
else
|
||
{
|
||
decimal elemSum = 0;
|
||
foreach (var elem in cab.Elements) elemSum += SafeDec(elem.Count) * elem.OfferUnitPrice;
|
||
row = 行设数值(row, "G", rowNum, elemSum);
|
||
}
|
||
sb.Append(row);
|
||
rowNum++;
|
||
}
|
||
|
||
// 总计行(G列用 =G{单台合计}*E{总计} 公式)
|
||
int grandRowNum = rowNum;
|
||
if (tplGrandRow != null)
|
||
{
|
||
string row = 行换号(tplGrandRow, rowNum);
|
||
row = 行设文本(row, "B", rowNum, "总计");
|
||
row = 行设文本(row, "D", rowNum, "台");
|
||
row = 行设数值(row, "E", rowNum, SafeDec(cab.Count));
|
||
if (useFormula)
|
||
row = 行设公式(row, "G", rowNum, "G" + singleRowNum + "*E" + rowNum);
|
||
else
|
||
{
|
||
decimal elemSum = 0;
|
||
foreach (var elem in cab.Elements) elemSum += SafeDec(elem.Count) * elem.OfferUnitPrice;
|
||
row = 行设数值(row, "G", rowNum, elemSum * SafeDec(cab.Count));
|
||
}
|
||
sb.Append(row);
|
||
rowNum++;
|
||
}
|
||
|
||
// 记录行号供汇总表引用
|
||
cabRowInfos.Add(new CabinetRowInfo
|
||
{
|
||
CabRow = cabRowNum,
|
||
SingleTotalRow = singleRowNum,
|
||
GrandRow = grandRowNum
|
||
});
|
||
|
||
grandQty += SafeDec(cab.Count);
|
||
grandTotal += cab.OfferTotalPrice;
|
||
}
|
||
tuhaoIdx++;
|
||
}
|
||
|
||
entries[sheetKey] = System.Text.Encoding.UTF8.GetBytes(头 + sb.ToString() + 尾);
|
||
}
|
||
|
||
// ====== XML 行/单元格操作辅助 ======
|
||
|
||
private class RowSpec
|
||
{
|
||
public string Type = null;
|
||
public CabinetData Cab = null;
|
||
public ElementData Elem = null;
|
||
public int CabOrder = 0;
|
||
}
|
||
|
||
/// <summary>更新行内所有单元格的行号引用 (如 r="A13" → r="A15")</summary>
|
||
private void UpdateRowCellReferences(XmlNode rowNode, int newRowNum)
|
||
{
|
||
foreach (XmlNode cell in rowNode.ChildNodes)
|
||
{
|
||
if (cell is XmlElement)
|
||
{
|
||
string r = ((XmlElement)cell).GetAttribute("r");
|
||
if (!string.IsNullOrEmpty(r))
|
||
{
|
||
// r 格式如 "K3" → 替换数字部分
|
||
string colPart = "";
|
||
for (int i = 0; i < r.Length; i++)
|
||
{
|
||
if (char.IsLetter(r[i])) colPart += r[i];
|
||
else break;
|
||
}
|
||
((XmlElement)cell).SetAttribute("r", colPart + newRowNum.ToString());
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>按行号查找行节点</summary>
|
||
private XmlNode FindRowByNumber(XmlNode sheetData, int rowNum, XmlNamespaceManager nsmgr)
|
||
{
|
||
foreach (XmlNode row in sheetData.SelectNodes("s:row", nsmgr))
|
||
{
|
||
if (((XmlElement)row).GetAttribute("r") == rowNum.ToString())
|
||
return row;
|
||
}
|
||
return null;
|
||
}
|
||
|
||
/// <summary>设置行中指定列的字符串值</summary>
|
||
private void SetCellInRow(XmlNode rowNode, string colLetter, string value, XmlNamespaceManager nsmgr)
|
||
{
|
||
string targetRef = colLetter + ((XmlElement)rowNode).GetAttribute("r");
|
||
foreach (XmlNode cell in rowNode.ChildNodes)
|
||
{
|
||
if (!(cell is XmlElement)) continue;
|
||
string r = ((XmlElement)cell).GetAttribute("r");
|
||
if (r == targetRef)
|
||
{
|
||
// 清除现有内容,设置新值
|
||
cell.InnerXml = "<v xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" + XmlEscape(value) + "</v>";
|
||
((XmlElement)cell).SetAttribute("t", "str");
|
||
return;
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>设置行中指定列的数值</summary>
|
||
private void SetCellNumericInRow(XmlNode rowNode, string colLetter, decimal value, XmlNamespaceManager nsmgr)
|
||
{
|
||
string targetRef = colLetter + ((XmlElement)rowNode).GetAttribute("r");
|
||
foreach (XmlNode cell in rowNode.ChildNodes)
|
||
{
|
||
if (!(cell is XmlElement)) continue;
|
||
string r = ((XmlElement)cell).GetAttribute("r");
|
||
if (r == targetRef)
|
||
{
|
||
cell.InnerXml = "<v xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" + value.ToString(System.Globalization.CultureInfo.InvariantCulture) + "</v>";
|
||
((XmlElement)cell).RemoveAttribute("t"); // 数值类型不需要 t 属性
|
||
return;
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>设置行中指定列的公式</summary>
|
||
private void SetCellFormulaInRow(XmlNode rowNode, string colLetter, string formula, XmlNamespaceManager nsmgr)
|
||
{
|
||
string targetRef = colLetter + ((XmlElement)rowNode).GetAttribute("r");
|
||
foreach (XmlNode cell in rowNode.ChildNodes)
|
||
{
|
||
if (!(cell is XmlElement)) continue;
|
||
string r = ((XmlElement)cell).GetAttribute("r");
|
||
if (r == targetRef)
|
||
{
|
||
// 公式格式: <f>公式</f>
|
||
cell.InnerXml = "<f xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" + XmlEscape(formula) + "</f>";
|
||
((XmlElement)cell).RemoveAttribute("t"); // 公式单元格不需要 t 属性
|
||
return;
|
||
}
|
||
}
|
||
}
|
||
|
||
/// <summary>设置行中指定列单元格的样式索引(s属性)</summary>
|
||
private void SetCellStyle(XmlNode rowNode, string colLetter, int styleIdx, XmlNamespaceManager nsmgr)
|
||
{
|
||
string targetRef = colLetter + ((XmlElement)rowNode).GetAttribute("r");
|
||
foreach (XmlNode cell in rowNode.ChildNodes)
|
||
{
|
||
if (!(cell is XmlElement)) continue;
|
||
string r = ((XmlElement)cell).GetAttribute("r");
|
||
if (r == targetRef)
|
||
{
|
||
((XmlElement)cell).SetAttribute("s", styleIdx.ToString());
|
||
return;
|
||
}
|
||
}
|
||
}
|
||
|
||
// ====== 清理不需要的方法 ======
|
||
|
||
private static string FindPythonExe() { return "python.exe"; }
|
||
private static string JsonEscape(string s) { return s ?? ""; }
|
||
|
||
// ====== 通用辅助 ======
|
||
|
||
private static string SafeStr(object o)
|
||
{
|
||
if (o == null || o == DBNull.Value) return "";
|
||
return o.ToString();
|
||
}
|
||
|
||
/// <summary>防御性读取字符串列:老项目库的箱号表可能还没有"箱名"列,缺列时返回空串而不是抛异常。</summary>
|
||
private static string SafeStrCol(System.Data.SQLite.SQLiteDataReader reader, string col)
|
||
{
|
||
try
|
||
{
|
||
int i = reader.GetOrdinal(col);
|
||
if (i < 0) return "";
|
||
if (reader.IsDBNull(i)) return "";
|
||
return SafeStr(reader.GetValue(i));
|
||
}
|
||
catch { return ""; }
|
||
}
|
||
|
||
private static decimal SafeDec(object o)
|
||
{
|
||
if (o == null || o == DBNull.Value) return 0m;
|
||
decimal d;
|
||
return decimal.TryParse(o.ToString(), out d) ? d : 0m;
|
||
}
|
||
|
||
private static string GetDict(Dictionary<string, string> dict, string key)
|
||
{
|
||
string v;
|
||
return dict.TryGetValue(key, out v) ? (v ?? "") : "";
|
||
}
|
||
|
||
private static int TotalCabinetCount(List<TuhaoData> list)
|
||
{
|
||
int n = 0;
|
||
foreach (var t in list) n += t.Cabinets.Count;
|
||
return n;
|
||
}
|
||
void Chk显示图纸规格选中状态更改(object sender, EventArgs e)
|
||
{
|
||
|
||
}
|
||
void Btn打开模板单击(object sender, EventArgs e)
|
||
{
|
||
|
||
}
|
||
void Chk表格带Excel公式选中状态更改(object sender, EventArgs e)
|
||
{
|
||
|
||
}
|
||
}
|
||
}
|