private MSOpenXML.Cell CreateCellIfNotExist(MSOpenXML.Worksheet worksheet, string cellName) { string columnName = GetColumnName(cellName); uint rowIndex = GetRowIndex(cellName); IEnumerable <MSOpenXML.Row> rows = worksheet.Descendants <MSOpenXML.Row>().Where(r => r.RowIndex.Value == rowIndex); // If the Worksheet does not contain the specified row, create the specified row. // Create the specified cell in that row, and insert the row into the Worksheet. if (rows.Count() == 0) { MSOpenXML.Row row = new MSOpenXML.Row() { RowIndex = rowIndex }; MSOpenXML.Cell cell = new MSOpenXML.Cell() { CellReference = cellName }; row.Append(cell); worksheet.Descendants <MSOpenXML.SheetData>().First().Append(row); return(cell); } else { MSOpenXML.Row row = rows.First(); IEnumerable <MSOpenXML.Cell> cells = row.Elements <MSOpenXML.Cell>().Where(c => c.CellReference.Value == cellName); // If the row does not contain the specified cell, create the specified cell. if (cells.Count() == 0) { MSOpenXML.Cell cell = new MSOpenXML.Cell() { CellReference = cellName }; row.Append(cell); return(cell); } else { return(cells.First()); } } }
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()); }