Пример #1
0
        public static void FormatSetJustificationLeft(this ExcelSheet sheet)
        {
            Range range = sheet.GetUsedRange();

            range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
            range.VerticalAlignment   = XlVAlign.xlVAlignCenter;
            Cleanup.ReleaseObject(range);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
        }
Пример #5
0
        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);
        }
Пример #6
0
 public bool SheetExists(string sheetName)
 {
     foreach (Worksheet sht in Worksheets)
     {
         if (sht.Name == sheetName)
         {
             Cleanup.ReleaseObject(sht);
             return(true);
         }
         Cleanup.ReleaseObject(sht);
     }
     return(false);
 }
Пример #7
0
        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);
        }
Пример #8
0
        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);
        }
Пример #9
0
        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;
     }
 }
Пример #11
0
        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);
        }
Пример #12
0
        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;
        }
Пример #14
0
 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;
     }
 }
Пример #15
0
        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);
        }