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