private void AutoFit(MSOpenXML.Worksheet worksheet) { MSOpenXML.MergeCells mergeCells = worksheet.Elements <MSOpenXML.MergeCells>().Count() > 0 ? mergeCells = worksheet.Elements <MSOpenXML.MergeCells>().First() : null; List <MergeCell> cells = mergeCells != null?mergeCells.Elements <MergeCell>().ToList() : new List <MergeCell>(); Dictionary <string, int> d = new Dictionary <string, int>(); foreach (var row in worksheet.Descendants <Row>()) { foreach (var cell in row.Elements <Cell>()) { if (cell.CellValue == null) { continue; } if (cells.Exists(x => Contains(x.Reference.Value, cell.CellReference.Value))) { continue; } int s = cell.CellValue.Text.Length; if (cell.StyleIndex != null) { if (cell.StyleIndex.Value == 1) { s = 10; } if (cell.StyleIndex.Value == 3) { s = decimal.Parse(cell.CellValue.Text, _en_us_ci.NumberFormat).ToString("n2").Length; } } string c = GetColumnName(cell.CellReference); if (d.ContainsKey(c)) { d[c] = Math.Max(d[c], s); } else { d[c] = s; } } } Columns columns = new Columns(); foreach (var item in d) { columns.Append(CreateColumnData(GetColumnIndex(item.Key) + 1, GetColumnIndex(item.Key) + 1, item.Value * 1.2)); } worksheet.InsertBefore(columns, worksheet.Elements <MSOpenXML.SheetData>().First()); }
private void MergeRange(MSOpenXML.Worksheet worksheet, string cell1Name, string cell2Name) { // Open the document for editing. if (worksheet == null || string.IsNullOrEmpty(cell1Name) || string.IsNullOrEmpty(cell2Name)) { return; } if (cell1Name == cell2Name) { return; } // Verify if the specified cells exist, and if they do not exist, create them. CreateCellIfNotExist(worksheet, cell1Name); CreateCellIfNotExist(worksheet, cell2Name); MSOpenXML.MergeCells mergeCells; if (worksheet.Elements <MSOpenXML.MergeCells>().Count() > 0) { mergeCells = worksheet.Elements <MSOpenXML.MergeCells>().First(); } else { mergeCells = new MSOpenXML.MergeCells(); // Insert a MergeCells object into the specified position. if (worksheet.Elements <MSOpenXML.CustomSheetView>().Count() > 0) { worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.CustomSheetView>().First()); } else if (worksheet.Elements <MSOpenXML.DataConsolidate>().Count() > 0) { worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.DataConsolidate>().First()); } else if (worksheet.Elements <MSOpenXML.SortState>().Count() > 0) { worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SortState>().First()); } else if (worksheet.Elements <MSOpenXML.AutoFilter>().Count() > 0) { worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.AutoFilter>().First()); } else if (worksheet.Elements <MSOpenXML.Scenarios>().Count() > 0) { worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.Scenarios>().First()); } else if (worksheet.Elements <MSOpenXML.ProtectedRanges>().Count() > 0) { worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.ProtectedRanges>().First()); } else if (worksheet.Elements <MSOpenXML.SheetProtection>().Count() > 0) { worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SheetProtection>().First()); } else if (worksheet.Elements <MSOpenXML.SheetCalculationProperties>().Count() > 0) { worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SheetCalculationProperties>().First()); } else { worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SheetData>().First()); } } // Create the merged cell and append it to the MergeCells collection. MSOpenXML.MergeCell mergeCell = new MSOpenXML.MergeCell() { Reference = cell1Name + ":" + cell2Name }; mergeCells.Append(mergeCell); }