public static void FormatSetJustificationLeft(this ExcelSheet sheet)
{
Range range = sheet.GetUsedRange();
range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
Cleanup.ReleaseObject(range);
}
public static void FormatMergeAndCentre(this ExcelSheet sheet, int row1, int column1, int row2, int column2)
{
Range rng = sheet.SetRange(row1, column1, row2, column2);
rng.Merge();
rng.HorizontalAlignment = XlHAlign.xlHAlignCenter;
rng.VerticalAlignment = XlVAlign.xlVAlignCenter;
Cleanup.ReleaseObject(rng);
}
public static void FormatFreezePanes(this ExcelSheet sheet, int freezeRow = 1, int freezeColumn = 1)
{
sheet.Worksheet.Activate();
Range range = sheet.Worksheet.Cells[freezeRow, freezeColumn];
range.Select();
sheet.ExcelApplication.ActiveWindow.FreezePanes = true;
Cleanup.ReleaseObject(range);
}
public int FindLastUsedColumn(int searchRow = 1)
{
Range searchRange = SetRange(searchRow, 1, searchRow, Worksheet.Columns.Count);
Range lastCell = searchRange.Find(What: "*", After: Worksheet.Cells[searchRow, 1], LookIn: XlFindLookIn.xlFormulas, LookAt: XlLookAt.xlWhole, SearchDirection: XlSearchDirection.xlPrevious, MatchCase: true);
int retInt = lastCell == null ? 1 : lastCell.Column;
Cleanup.ReleaseObject(lastCell);
Cleanup.ReleaseObject(searchRange);
return(retInt);
}
public Range SetRange(int row1, int column1, int row2, int column2)
{
Range cell1 = (Range)Worksheet.Cells[row1, column1];
Range cell2 = (Range)Worksheet.Cells[row2, column2];
Range retRange = Worksheet.Range[cell1, cell2];
Cleanup.ReleaseObject(cell1);
Cleanup.ReleaseObject(cell2);
return(retRange);
}
public bool SheetExists(string sheetName)
{
foreach (Worksheet sht in Worksheets)
{
if (sht.Name == sheetName)
{
Cleanup.ReleaseObject(sht);
return(true);
}
Cleanup.ReleaseObject(sht);
}
return(false);
}
public static void FormatHeadingStandard(this ExcelSheet sheet, int headingRow = 1)
{
Range range = sheet.SetRange(headingRow, 1, headingRow, sheet.FindLastUsedColumn(headingRow));
range.Interior.Pattern = XlPattern.xlPatternSolid;
range.Interior.PatternColorIndex = XlColorIndex.xlColorIndexAutomatic;
range.Interior.Color = COLOR_CELL_HEADING;
range.Interior.TintAndShade = 0;
range.Interior.PatternTintAndShade = 0;
range.Font.Bold = true;
range.WrapText = true;
Cleanup.ReleaseObject(range);
}
public int FindDataRow(string findString, int searchColumn = 1, int startRow = 1)
{
if (string.IsNullOrEmpty(findString))
{
return(0);
}
Range searchRange = SetRange(1, searchColumn, Worksheet.Rows.Count, searchColumn);
Range foundCell = searchRange.Find(What: findString, After: Worksheet.Cells[startRow, searchColumn], LookIn: XlFindLookIn.xlFormulas, LookAt: XlLookAt.xlWhole, SearchDirection: XlSearchDirection.xlPrevious, MatchCase: true);
int retInt = foundCell == null ? 0 : foundCell.Row;
Cleanup.ReleaseObject(foundCell);
Cleanup.ReleaseObject(searchRange);
return(retInt);
}
public static void FormatAutoFitAllColumns(this ExcelSheet sheet)
{
Range rangeA = (Range)sheet.Worksheet.Cells;
rangeA = rangeA.EntireColumn;
rangeA.AutoFit();
for (int i = 1; i <= sheet.FindLastUsedColumn(); i++)
{
Range range = (Range)sheet.Worksheet.Columns[i];
range.ColumnWidth += 3;
Cleanup.ReleaseObject(range);
}
Cleanup.ReleaseObject(rangeA);
}
private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
Cleanup.ReleaseObject(Worksheets);
Workbook.Close(0);
Cleanup.ReleaseObject(Workbook);
Cleanup.ReleaseObject(ExcelWorkbooks);
ExcelApplication.Quit();
Cleanup.ReleaseObject(ExcelApplication);
}
disposedValue = true;
}
}
public static void FormatSetFont(this ExcelSheet sheet)
{
Range range = sheet.GetUsedRange();
range.Font.Name = "Calibri";
range.Font.Size = 10;
range.Font.Strikethrough = false;
range.Font.Superscript = false;
range.Font.Subscript = false;
range.Font.OutlineFont = false;
range.Font.Shadow = false;
range.Font.Underline = XlUnderlineStyle.xlUnderlineStyleNone;
range.Font.ThemeColor = XlThemeColor.xlThemeColorLight1;
range.Font.TintAndShade = 0;
range.Font.ThemeFont = XlThemeFont.xlThemeFontMinor;
Cleanup.ReleaseObject(range);
}
public static void FormatBorders(this ExcelSheet sheet, Range range = null)
{
if (range == null)
{
range = sheet.GetUsedRange();
}
Borders borders = range.Borders;
borders[XlBordersIndex.xlDiagonalDown].LineStyle = XlLineStyle.xlLineStyleNone;
borders[XlBordersIndex.xlDiagonalUp].LineStyle = XlLineStyle.xlLineStyleNone;
borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
borders[XlBordersIndex.xlEdgeBottom].ThemeColor = 1;
borders[XlBordersIndex.xlEdgeBottom].TintAndShade = -0.499984740745262;
borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
borders[XlBordersIndex.xlEdgeTop].ThemeColor = 1;
borders[XlBordersIndex.xlEdgeTop].TintAndShade = -0.499984740745262;
borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThin;
borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
borders[XlBordersIndex.xlEdgeLeft].ThemeColor = 1;
borders[XlBordersIndex.xlEdgeLeft].TintAndShade = -0.499984740745262;
borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThin;
borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
borders[XlBordersIndex.xlEdgeRight].ThemeColor = 1;
borders[XlBordersIndex.xlEdgeRight].TintAndShade = -0.499984740745262;
borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThin;
borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
borders[XlBordersIndex.xlInsideHorizontal].ThemeColor = 1;
borders[XlBordersIndex.xlInsideHorizontal].TintAndShade = -0.499984740745262;
borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin;
borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;
borders[XlBordersIndex.xlInsideVertical].ThemeColor = 1;
borders[XlBordersIndex.xlInsideVertical].TintAndShade = -0.499984740745262;
borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
Cleanup.ReleaseObject(range);
}
public ExcelWorkbook(string filename, string password = "")
{
ExcelApplication = new Application
{
DisplayAlerts = false
};
ExcelWorkbooks = ExcelApplication.Workbooks;
if (File.Exists(filename))
{
try
{
Workbook = ExcelWorkbooks.Open(Filename: filename, UpdateLinks: false, ReadOnly: false, Password: password);
}
catch (Exception ex)
{
disposedValue = false;
Dispose(true);
throw new LocalSystemException("Could not open locked Excel file : " + filename, ex);
}
}
else
{
try
{
Workbook = ExcelWorkbooks.Add(XlWBATemplate.xlWBATWorksheet);
Save(filename, true, password);
Workbook.Close();
Cleanup.ReleaseObject(Workbook);
Workbook = ExcelWorkbooks.Open(Filename: xSaveName, UpdateLinks: false, ReadOnly: false, Password: password);
}
catch (Exception ex)
{
disposedValue = false;
Dispose(true);
throw new LocalSystemException("Could not create new Excel file : " + filename, ex);
}
}
Worksheets = Workbook.Worksheets;
}
private bool disposedValue = false; // To detect redundant calls
protected override void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
GC.Collect();
Cleanup.ReleaseObject(Worksheet);
Cleanup.ReleaseObject(Worksheets);
if (Workbook != null)
{
Workbook.Close(0);
}
Cleanup.ReleaseObject(Workbook);
Cleanup.ReleaseObject(ExcelWorkbooks);
ExcelApplication.Quit();
Cleanup.ReleaseObject(ExcelApplication);
GC.WaitForPendingFinalizers();
}
disposedValue = true;
}
}
public static void FormatFilTableColours(this ExcelSheet sheet, bool hasColour)
{
Range range = sheet.GetUsedRange();
switch (hasColour)
{
case true:
range.Interior.Pattern = XlPattern.xlPatternSolid;
range.Interior.PatternColorIndex = XlColorIndex.xlColorIndexAutomatic;
range.Interior.Color = 15532007;
range.Interior.TintAndShade = 0;
range.Interior.PatternTintAndShade = 0;
break;
case false:
range.Interior.Pattern = XlPattern.xlPatternSolid;
range.Interior.PatternColorIndex = XlColorIndex.xlColorIndexAutomatic;
range.Interior.ThemeColor = XlThemeColor.xlThemeColorDark1;
range.Interior.TintAndShade = 0;
range.Interior.PatternTintAndShade = 0;
break;
}
Cleanup.ReleaseObject(range);
}