UniformMaterialManagementSystem/UniformMaterialManagementSystem/Utils/ContractUtil.cs

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using UniformMaterialManagementSystem.Entities;

namespace UniformMaterialManagementSystem.Utils
{
    public static class ContractUtil
    {
        public static void ExportToExcel(string path, List<ContractDetail> contracts, Dictionary<string, string> dictionary)
        {
            var templateFilePath = "Template\\附件1被装材料采购合同明细数据模板.xlsx";

            File.Copy(templateFilePath, path);
            using var spreadsheetDocument = SpreadsheetDocument.Open(path, isEditable: true);
            var workbookPart = spreadsheetDocument.WorkbookPart;

            var sheets = spreadsheetDocument.WorkbookPart?.Workbook.GetFirstChild<Sheets>()?.Elements<Sheet>();
            var relationshipId = sheets?.First().Id?.Value;

            if (relationshipId == null) return;
            var worksheetPart = (WorksheetPart?)workbookPart?.GetPartById(relationshipId);

            if (worksheetPart == null) return;
            var worksheet = worksheetPart.Worksheet;
            var sheetData = worksheet.GetFirstChild<SheetData>();

            var stylesPart = workbookPart?.WorkbookStylesPart;
            if (stylesPart != null)
            {
                UpdateStyleSheet(stylesPart.Stylesheet);
            }

            // 修改成品企业名称
            var cellReference = "C3";
            var cell = sheetData?.Descendants<Cell>().FirstOrDefault(c => c.CellReference?.Value == cellReference);
            if (cell != null) // 解决直接更新单元格，Excel报错问题，先删后插
            {
                cell.Remove();

                var rowIndex = uint.Parse(new string(cellReference.Where(char.IsDigit).ToArray()));

                var row = sheetData?.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == rowIndex);
                if (row == null)
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData?.Append(row);
                }

                var refCell = row.Elements<Cell>().FirstOrDefault(c => string.Compare(c.CellReference?.Value, cellReference, StringComparison.OrdinalIgnoreCase) > 0);
                var companyName = dictionary.ContainsKey("ProductCompanyName") ? dictionary["ProductCompanyName"] : "";
                var newCell = new Cell
                {
                    CellReference = cellReference,
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellValue = new CellValue(companyName) //Todo：输入成品企业名称
                };

                row.InsertBefore(newCell, refCell);
            }

            // 修改填报人及电话
            var editUserCellReference = "D3";
            var editUserCell = sheetData?.Descendants<Cell>().FirstOrDefault(c => c.CellReference?.Value == editUserCellReference);
            if (editUserCell != null) // 解决直接更新单元格，Excel报错问题，先删后插
            {
                editUserCell.Remove();

                var rowIndex = uint.Parse(new string(editUserCellReference.Where(char.IsDigit).ToArray()));

                var row = sheetData?.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == rowIndex);
                if (row == null)
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData?.Append(row);
                }

                var refCell = row.Elements<Cell>().FirstOrDefault(c => string.Compare(c.CellReference?.Value, editUserCellReference, StringComparison.OrdinalIgnoreCase) > 0);
                var editUserAndTel = dictionary.ContainsKey("EditUserAndTel") ? dictionary["EditUserAndTel"] : "";
                var newCell = new Cell
                {
                    CellReference = editUserCellReference,
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellValue = new CellValue("填报人及电话：" + editUserAndTel) //Todo：输入填报人及联系电话
                };

                row.InsertBefore(newCell, refCell);
            }

            // 修改填报日期
            var editDateCellReference = "G3";
            var editDateCell = sheetData?.Descendants<Cell>().FirstOrDefault(c => c.CellReference?.Value == editDateCellReference);
            if (editDateCell != null) // 解决直接更新单元格，Excel报错问题，先删后插
            {
                editDateCell.Remove();

                var rowIndex = uint.Parse(new string(editDateCellReference.Where(char.IsDigit).ToArray()));

                var row = sheetData?.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == rowIndex);
                if (row == null)
                {
                    row = new Row() { RowIndex = rowIndex };
                    sheetData?.Append(row);
                }

                var refCell = row.Elements<Cell>().FirstOrDefault(c => string.Compare(c.CellReference?.Value, editDateCellReference, StringComparison.OrdinalIgnoreCase) > 0);
                var editDate = dictionary.ContainsKey("EditDate") ? dictionary["EditDate"] : "";
                var newCell = new Cell
                {
                    CellReference = editDateCellReference,
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellValue = new CellValue("填报日期：" + editDate) //Todo：输入填报日期
                };

                row.InsertBefore(newCell, refCell);
            }

            // 添加明细
            var styleCount = stylesPart?.Stylesheet.CellFormats?.Count;
            for (var i = 0; i < contracts.Count; i++)
            {
                var row = new Row() { RowIndex = (UInt32Value)((uint)i + 5) };

                var numberCell = new Cell
                {
                    DataType = new EnumValue<CellValues>(CellValues.Number),
                    CellValue = new CellValue(i + 1),
                    StyleIndex = styleCount == null ? 0 : styleCount - 4
                };

                var contractNoCell = new Cell
                {
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellValue = new CellValue(contracts[i].Contract.ContractNo),
                    StyleIndex = styleCount == null ? 0 : styleCount - 1
                };

                var companyNameCell = new Cell
                {
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellValue = new CellValue(contracts[i].Contract.Company.Name),
                    StyleIndex = styleCount == null ? 0 : styleCount - 1
                };

                var signTimeCell = new Cell()
                {
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellValue = new CellValue(contracts[i].Contract.SigningDate.ToString("yyyy-M-dd")),
                    StyleIndex = styleCount == null ? 0 : styleCount - 3
                };

                var materialNameCell = new Cell()
                {
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellValue = new CellValue(contracts[i].Material.Name),
                    StyleIndex = styleCount == null ? 0 : styleCount - 1
                };

                var measureUnitCell = new Cell()
                {
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellValue = new CellValue(contracts[i].Material.MeasureUnit),
                    StyleIndex = styleCount == null ? 0 : styleCount - 1
                };

                var contractQtyCell = new Cell()
                {
                    DataType = new EnumValue<CellValues>(CellValues.Number),
                    CellValue = new CellValue(contracts[i].ContractQty),
                    StyleIndex = styleCount == null ? 0 : styleCount - 2
                };

                var deliveryTimeCell = new Cell()
                {
                    DataType = new EnumValue<CellValues>(CellValues.String),
                    CellValue = new CellValue(contracts[i].DeliveryTime.ToString("yyyy-M-dd")),
                    StyleIndex = styleCount == null ? 0 : styleCount - 3
                };

                row.Append(numberCell);
                row.Append(contractNoCell);
                row.Append(companyNameCell);
                row.Append(signTimeCell);
                row.Append(materialNameCell);
                row.Append(measureUnitCell);
                row.Append(contractQtyCell);
                row.Append(deliveryTimeCell);

                sheetData?.Append(row);
            }

            worksheet.Save();

            //var newDocument = spreadsheetDocument.Clone(path);
            //newDocument.Dispose();
        }

        private static void UpdateStyleSheet(Stylesheet stylesheet)
        {
            #region Number format

            uint datetimeFormat = 164;
            uint digits4Format = 165;

            var numberingFormats = stylesheet.NumberingFormats ?? new NumberingFormats();
            numberingFormats.Append(new NumberingFormat // Datetime format
            {
                NumberFormatId = UInt32Value.FromUInt32(datetimeFormat),
                FormatCode = StringValue.FromString("yyyy-m-dd")
            });

            numberingFormats.Append(new NumberingFormat // four digits format
            {
                NumberFormatId = UInt32Value.FromUInt32(digits4Format),
                FormatCode = StringValue.FromString("#0.00")
            });

            numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);

            stylesheet.NumberingFormats ??= numberingFormats;

            #endregion

            #region Borders

            var borders = stylesheet.Borders ?? new Borders();

            borders.Append(new Border    //Boarder Index 1: All
            {
                LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
                RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
                TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
                BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
                DiagonalBorder = new DiagonalBorder()
            });

            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);

            stylesheet.Borders ??= borders;

            #endregion

            #region Cell format

            var cellFormats = stylesheet.CellFormats ?? new CellFormats();

            var borderCount = stylesheet.Borders?.Count;

            cellFormats.Append(new CellFormat
            {
                NumberFormatId = 1,
                BorderId = borderCount == null ? 0 : borderCount - 1,
                ApplyNumberFormat = BooleanValue.FromBoolean(false)
            });

            cellFormats.Append(new CellFormat
            {
                NumberFormatId = datetimeFormat,
                BorderId = borderCount == null ? 0 : borderCount - 1,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            });

            cellFormats.Append(new CellFormat
            {
                NumberFormatId = digits4Format,
                BorderId = borderCount == null ? 0 : borderCount - 1,
                ApplyNumberFormat = BooleanValue.FromBoolean(true)
            });

            cellFormats.Append(new CellFormat
            {
                NumberFormatId = 0,
                BorderId = borderCount == null ? 0 : borderCount - 1,
                ApplyNumberFormat = BooleanValue.FromBoolean(false)
            });

            cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);

            stylesheet.CellFormats ??= cellFormats;

            #endregion

            stylesheet.Save();
        }
    }
}