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);
}
