/// <summary> /// 保存sheet数据生成data文件 /// </summary> /// <param name="FileName">数据文件(.tydd文件)的完整路径及文件名(包括扩展名)</param> /// <param name="configFileName">配置文件(.gzg文件)的完整路径及文件名(包括扩展名)</param> /// <param name="sheet">Sheet对象</param> /// <param name="worksheetPart">WorksheetPart对象</param> /// <param name="ExcelCfg">配置对象</param> /// <param name="ExcelLastWriteTime">Excel文件的最后更新时间</param> private static bool SaveData(string FileName, string configFileName, Sheet sheet, WorkbookPart workbookPart, ExcelConfig ExcelCfg, long ExcelLastWriteTime) { var worksheetPart = workbookPart.GetPartById(sheet.Id) as WorksheetPart; if (worksheetPart.Worksheet.Descendants<Row>().Count() <= 0) return false; // Sheet配置 SheetConfig Sheetcfg = ExcelCfg.GetSheetConfig(sheet.Name); if (Sheetcfg == null) { Console.Error.WriteLine(string.Format(Resources.ExcelConfigFileUnMatchExcel, configFileName, sheet.Name)); return false; } //获取WorkbookPart中NumberingFormats样式集合 var stylesList = GetNumberFormatsStyle(workbookPart); // 保存 using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write)) { using (var bw = new BinaryWriter(fs)) { // 保存对应excel文件的最后更新时间 bw.Write(ExcelLastWriteTime); // 写入列信息和其MD5 try { var colomnInfoMD5Bytes = Sheetcfg.ColomnInfoBytesMD5.ConvertHexStringToByteArray(); bw.Write(colomnInfoMD5Bytes.Length); bw.Write(colomnInfoMD5Bytes); var colomnInfoBytes = Sheetcfg.ColomnInfoBytesStr.ConvertHexStringToByteArray(); bw.Write(colomnInfoBytes.Length); bw.Write(colomnInfoBytes); } catch (Exception e) { Console.Error.WriteLine(e); return false; } bw.Flush(); using (ProtoWriter protoWriter = ProtoBufExtention.CreateProtoWriterSimplely(fs)) { // 每行数据 foreach (var row in worksheetPart.Worksheet.Descendants<Row>()) { if (row.RowIndex == 1) continue; var cellUnitList = new List<CellUnit>(); int i = 0, exportColumnIndex = 0; foreach (Cell cell in row) { // 将CellReference换算成1起始列序号,以检查空格子 string cellReference = cell.CellReference; int columnIndex = 0; int factor = 1; for (int pos = cellReference.Length - 1; pos >= 0; pos--) // R to L { if (char.IsLetter(cellReference[pos])) // for letters (columnName) { columnIndex += factor * ((cellReference[pos] - 'A') + 1); factor *= 26; } } while (i < columnIndex - 1) // 出现空格子 { //SaveDate(i, Sheetcfg, null, workbookPart, stylesList, protoWriter); if (!AddCellUnit(i, ref exportColumnIndex, Sheetcfg, null, workbookPart, stylesList, cellUnitList)) return false; ++i; } //SaveDate(i, Sheetcfg, cell, workbookPart, stylesList, protoWriter); if (!AddCellUnit(i, ref exportColumnIndex, Sheetcfg, cell, workbookPart, stylesList, cellUnitList)) return false; ++i; } while (i < Sheetcfg.ColumnConfigData.Count) // 末尾出现空格子 { //SaveDate(i, Sheetcfg, null, workbookPart, stylesList, protoWriter); if (!AddCellUnit(i, ref exportColumnIndex, Sheetcfg, null, workbookPart, stylesList, cellUnitList)) return false; ++i; } protoWriter.WriteSimplely(cellUnitList.Count); foreach (var cellUnit in cellUnitList) cellUnit.WriteWithProtoWriter(protoWriter); } } } } return true; }
public ConfigManager(Excel.Workbook workbook) { if (null == workbook) { return; } Wb = workbook; string FilePath = GetCustomConfigPath(); if (true == bFirstOpen) { Wb.SheetActivate += Wb_SheetActivate; Wb.SheetDeactivate += Wb_SheetDeactivate; Wb.AfterSave += Wb_AfterSave; // 初始化数据 显示UI bFirstOpen = false; // 加载配置数据 if (File.Exists(FilePath)) { ExcelCfg = ExcelConfig.LoadConfig(FilePath); if (ExcelCfg == null) { if (MessageBox.Show(Properties.Resources.ConfigFileInvalidMessage, Properties.Resources.MessageBoxCaption, MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK) ExcelCfg = new ExcelConfig(); } } else ExcelCfg = new ExcelConfig(); } myUserControlList.Clear(); // 当前激活的sheet Excel.Worksheet curws = (Excel.Worksheet)Wb.ActiveSheet; // 获取所有sheet foreach (Excel.Worksheet ws in Wb.Worksheets) { if (ws == null) { continue; } // 创建sheet对象 FieldSettingControl myControl = new FieldSettingControl(); int casheWidth = myControl.Width; CustomTaskPane TaskPane = Globals.ThisAddIn.CustomTaskPanes.Add(myControl, ws.Name); try { TaskPane.Width = casheWidth + 15; // 加上15看起来才是原控件的宽度,原因不明 } catch { } // 加载sheet配置 myControl.InitData(Wb, ws, TaskPane, ExcelCfg.GetConfig(ws.Name)); // 显示当前sheet的字段操作UI if (ws.Index == curws.Index) { TaskPane.Visible = true; myControl.Visible = true; } else { TaskPane.Visible = false; myControl.Visible = false; } // 保存到列表 myUserControlList.Add(myControl); } }