// 选项。可为内置统计方案设置参数 private void button_option_Click(object sender, EventArgs e) { string name = this.comboBox_projectName.Text; switch (name) { case "#典藏移交清单": { TransferColumnOption option = new TransferColumnOption(Program.MainForm.UserDir); option.LoadData(Program.MainForm.AppInfo, ColumnDefPath); PrintOptionDlg dlg = new PrintOptionDlg(); MainForm.SetControlFont(dlg, this.Font, false); dlg.HidePage("tabPage_normal"); dlg.HidePage("tabPage_templates"); dlg.Text = "输出列配置"; dlg.PrintOption = option; dlg.DataDir = Program.MainForm.UserDir; dlg.ColumnItems = option.GetAllColumnItems(); dlg.UiState = Program.MainForm.AppInfo.GetString( "OperLogStatisForm", "columnDialog_uiState", ""); Program.MainForm.AppInfo.LinkFormState(dlg, "OperLogStatisForm_transferOption_formstate"); dlg.ShowDialog(this); Program.MainForm.AppInfo.UnlinkFormState(dlg); Program.MainForm.AppInfo.SetString( "OperLogStatisForm", "columnDialog_uiState", dlg.UiState); if (dlg.DialogResult != DialogResult.OK) { return; } option.SaveData(Program.MainForm.AppInfo, ColumnDefPath); } break; } }
// 创建一个 Sheet IXLWorksheet CreateSheet(XLWorkbook doc, string sheet_name, List <TransferGroup> groups) { IXLWorksheet sheet = doc.Worksheets.Add(Cut(GetSheetName(sheet_name), 31)); // 每个列的最大字符数 // List<int> column_max_chars = new List<int>(); // 准备书目列标题 var biblio_column_option = new TransferColumnOption(Program.MainForm.UserDir); biblio_column_option.LoadData(Program.MainForm.AppInfo, ColumnDefPath); List <Order.ColumnProperty> biblio_title_list = Order.DistributeExcelFile.BuildList(biblio_column_option); List <string> headers = new List <string>(); biblio_title_list.ForEach(o => headers.Add(o.Caption)); int nRowIndex = 1; int nColIndex = 1; // 输出表格标题 { var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1); range.Merge(); range.SetValue(sheet_name); range.Style.Alignment.WrapText = true; range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; range.Style.Font.Bold = true; nRowIndex++; } int lines = 0; groups.ForEach(o => lines += o.Items.Count); { var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1); range.Merge(); range.SetValue($"行数: {lines}, 打印日期: {DateTime.Now.ToString("yyyy-M-d")}"); range.Style.Alignment.WrapText = true; range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; nRowIndex++; } nRowIndex++; foreach (string header in headers) { IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(DomUtil.ReplaceControlCharsButCrLf(header, '*')); cell.Style.Alignment.WrapText = true; cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; cell.Style.Font.Bold = true; //cell.Style.Font.FontName = strFontName; //cell.Style.Alignment.Horizontal = alignments[nColIndex - 1]; nColIndex++; } { // 设置边框 var range = sheet.Range(nRowIndex, 1, nRowIndex, biblio_title_list.Count); range.Style.Border.SetBottomBorderColor(XLColor.Black); range.Style.Border.SetBottomBorder(XLBorderStyleValues.Medium); } nRowIndex++; // 用于发现重复的册 // recpath --> (此路径的第一个) TransferItem Hashtable recpath_table = new Hashtable(); int dup_count = 0; // 记录路径发生重复的行数 int notchange_count = 0; // 馆藏地没有发生改变的行数 int nNo = 1; foreach (var group in groups) { foreach (var item in group.Items) { bool dup = false; if (recpath_table.ContainsKey(item.RecPath)) { dup = true; dup_count++; } else { recpath_table[item.RecPath] = item; } bool onlyWriteLog = StringUtil.IsInList("onlyWriteLog", item.Style); nColIndex = 1; // WriteCell(nNo.ToString()); // WriteCell(item.Barcode); // 获得册记录 XmlDocument itemdom = new XmlDocument(); itemdom.LoadXml(item.NewXml); string strParentID = DomUtil.GetElementText(itemdom.DocumentElement, "parent"); // 输出书目列 WriteBiblioColumns( item.RecPath, strParentID, biblio_title_list, sheet, nColIndex - 1, nRowIndex - 1); OutputTransferColumns( nNo, item, sheet, nColIndex - 1, Order.ColumnProperty.GetTypeList(biblio_title_list, false), nRowIndex - 1); /* * nColIndex += biblio_title_list.Count; * * WriteCell(item.SourceLocation); * WriteCell(item.TargetLocation); * WriteCell(item.BatchNo); * WriteCell(item.OperTime.ToString()); * WriteCell(item.Operator); */ // 设置边框 var range = sheet.Range(nRowIndex, 1, nRowIndex, biblio_title_list.Count); range.Style.Border.SetBottomBorderColor(XLColor.Black); range.Style.Border.SetBottomBorder(XLBorderStyleValues.Hair); if (dup) { range.Style.Fill.BackgroundColor = XLColor.Yellow; } if (onlyWriteLog) { notchange_count++; // 寻找 SourceLocation 和 TargetLocation 列 { int index = biblio_title_list.FindIndex(o => o.Type == "log_sourceLocation"); if (index != -1) { sheet.Cell(nRowIndex, 1 + index).Style.Fill.BackgroundColor = XLColor.LightBlue; } } { int index = biblio_title_list.FindIndex(o => o.Type == "log_targetLocation"); if (index != -1) { sheet.Cell(nRowIndex, 1 + index).Style.Fill.BackgroundColor = XLColor.LightBlue; } } } nRowIndex++; nNo++; } } // 警告行 if (dup_count > 0) { nRowIndex++; var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1); range.Merge(); range.Style.Fill.BackgroundColor = XLColor.DarkGray; range.Style.Font.FontColor = XLColor.White; range.Style.Font.Bold = true; range.SetValue($"警告:表中有 {dup_count} 个重复的册记录行。这些行已显示为黄色背景色"); range.Style.Alignment.WrapText = true; range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; nRowIndex++; } // 警告行 if (notchange_count > 0) { var range = sheet.Range(nRowIndex, nColIndex, nRowIndex, nColIndex + headers.Count - 1); range.Merge(); range.Style.Fill.BackgroundColor = XLColor.DarkGray; range.Style.Font.FontColor = XLColor.White; range.SetValue($"提醒:表中有 {notchange_count} 个册记录行在移交操作中馆藏地并没有发生变化。这些行的源、目标馆藏地列已显示为蓝色背景色"); range.Style.Alignment.WrapText = true; range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; nRowIndex++; } // TODO: 设置 header 和 footer // https://stackoverflow.com/questions/34104107/closedxml-change-existing-footer-header double char_width = DigitalPlatform.dp2.Statis.ClosedXmlUtil.GetAverageCharPixelWidth(Program.MainForm); // 字符数太多的列不要做 width auto adjust const int MAX_CHARS = 30; // 60 int i = 0; foreach (IXLColumn column in sheet.ColumnsUsed()) { int nChars = GetMaxChars(column); column.Width = Math.Min(nChars, MAX_CHARS); #if NO if (nChars < MAX_CHARS) { // column.AdjustToContents(); column.Width = nChars; } else { int nColumnWidth = 100; /* * // 2020/1/6 增加保护判断 * if (i >= 0 && i < list.Columns.Count) * nColumnWidth = list.Columns[i].Width; */ column.Width = (double)nColumnWidth / char_width; // Math.Min(MAX_CHARS, nChars); } #endif i++; } return(sheet); #if NO void WriteCell(string text) { // 统计最大字符数 // int nChars = column_max_chars[nColIndex - 1]; if (text != null) { DigitalPlatform.dp2.Statis.ClosedXmlUtil.SetMaxChars(column_max_chars, nColIndex - 1, text.Length); } IXLCell cell = sheet.Cell(nRowIndex, nColIndex).SetValue(DomUtil.ReplaceControlCharsButCrLf(text, '*')); cell.Style.Alignment.WrapText = true; cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; // cell.Style.Font.FontName = strFontName; /* * if (nColIndex - 1 < alignments.Count) * cell.Style.Alignment.Horizontal = alignments[nColIndex - 1]; * else * cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; */ nColIndex++; } #endif }