Excel常用操作封装

最近做了一个Excel相关的项目，需要读写Excel文件，所以对Excel的一些功能进行了封装，方便以后使用。

采用单子模式，这样可以防止打开多个Excel进程，不知道有没有道理。在程序的最后退出Excel进程。

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Excel;
using System.Drawing;

namespace 订单管理系统
{
    public class ExcelHelper
    {
        private Excel.Application app;
        private Workbooks wbs;

        private ExcelHelper()
        {
            app = new Excel.Application();
            if (app != null)
            {
                wbs = app.Workbooks;
            }
            else
            {
                throw new OrderManageSystemException("Excel couldn't be started.");
            }
        }

        public Workbook Create()
        {
            return wbs.Add(XlWBATemplate.xlWBATWorksheet);
        }

        public Workbook Open(string fileName)
        {
            return wbs.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        }

        public Worksheet GetSheet(Workbook wb, string sheetName)
        {
            return wb.Sheets[sheetName] as Worksheet;
        }

        public Worksheet AddSheet(Workbook wb, string sheetName)
        {
            Worksheet sheet = wb.Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing) as Worksheet;
            sheet.Name = sheetName;
            return sheet;
        }

        public void DeleteSheet(Workbook wb, string sheetName)
        {
            GetSheet(wb, sheetName).Delete();
        }

        public void DeleteSheet(Worksheet sheet)
        {
            sheet.Delete();
        }

        public Worksheet RenameSheet(Workbook wb, string oldSheetName, string newSheetName)
        {
            Worksheet sheet = GetSheet(wb, oldSheetName);
            sheet.Name = newSheetName;
            return sheet;
        }

        public Worksheet RenameSheet(Worksheet sheet, string sheetName)
        {
            sheet.Name = sheetName;
            return sheet;
        }

        public void SetCellValue(Worksheet sheet, string cell, object value)
        {
            sheet.get_Range(cell, Type.Missing).Value2 = value;
        }

        public object GetCellValue(Worksheet sheet, string cell)
        {
            return sheet.get_Range(cell, Type.Missing).Value2;
        }

        public void SetFont(Worksheet sheet, object start, object end, string name, bool italic, bool bold, bool outlineFont, bool shadow, int size, bool strikethrough, bool underline, Color fontColor, bool outline)
        {
            Range range = sheet.get_Range(start, end);
            range.Font.Name = name;
            range.Font.Italic = italic;
            range.Font.Bold = bold;
            range.Font.Color = ColorTranslator.ToOle(fontColor);
            range.Font.OutlineFont = outline;
            range.Font.Shadow = shadow;
            range.Font.Size = size;
            range.Font.Strikethrough = strikethrough;
            range.Font.Underline = underline;
        }

        public void SetBorders(Worksheet sheet, object start, object end, XlLineStyle lineStyle, XlBorderWeight weight, XlColorIndex colorIndex, Color color)
        {
            Range range = sheet.get_Range(start, end);
            range.BorderAround(lineStyle, weight, colorIndex, ColorTranslator.ToOle(color));
        }

        public void SetTopBorder(Worksheet sheet, object start, object end, XlLineStyle lineStyle, XlBorderWeight weight, XlColorIndex colorIndex, Color color)
        {
            Range range = sheet.get_Range(start, end);
            Border border = range.Borders.get_Item(XlBordersIndex.xlEdgeTop);
            border.LineStyle = lineStyle;
            border.ColorIndex = colorIndex;
            border.Color = ColorTranslator.ToOle(color);
        }

        public void SetBoottomBorder(Worksheet sheet, object start, object end, XlLineStyle lineStyle, XlBorderWeight weight, XlColorIndex colorIndex, Color color)
        {
            Range range = sheet.get_Range(start, end);
            Border border = range.Borders.get_Item(XlBordersIndex.xlEdgeBottom);
            border.LineStyle = lineStyle;
            border.ColorIndex = colorIndex;
            border.Color = ColorTranslator.ToOle(color);
        }

        public void SetLeftBorder(Worksheet sheet, object start, object end, XlLineStyle lineStyle, XlBorderWeight weight, XlColorIndex colorIndex, Color color)
        {
            Range range = sheet.get_Range(start, end);
            Border border = range.Borders.get_Item(XlBordersIndex.xlEdgeLeft);
            border.LineStyle = lineStyle;
            border.ColorIndex = colorIndex;
            border.Color = ColorTranslator.ToOle(color);
        }

        public void SetRightBorder(Worksheet sheet, object start, object end, XlLineStyle lineStyle, XlBorderWeight weight, XlColorIndex colorIndex, Color color)
        {
            Range range = sheet.get_Range(start, end);
            Border border = range.Borders.get_Item(XlBordersIndex.xlEdgeRight);
            border.LineStyle = lineStyle;
            border.ColorIndex = colorIndex;
            border.Color = ColorTranslator.ToOle(color);
        }

        public void SetAlignment(Worksheet sheet, object start, object end, XlHAlign horizontalAlignment, XlVAlign verticalAlignment)
        {
            Range range = sheet.get_Range(start, end);
            range.HorizontalAlignment = horizontalAlignment;
            range.VerticalAlignment = verticalAlignment;
        }

        public void SetOtherProperty(Worksheet sheet, object start, object end, int rowHeight, int columnWidth, bool autofit, bool wrapText, Color interiorColor)
        {
            Range range = sheet.get_Range(start, end);
            range.RowHeight = rowHeight;
            range.ColumnWidth = columnWidth;
            if (autofit)
            {
                range.AutoFit();
            }
            range.WrapText = wrapText;
            range.Interior.Color = ColorTranslator.ToOle(interiorColor);
        }

        public void Merge(Worksheet sheet, object start, object end)
        {
            Range range = sheet.get_Range(start, end);
            range.Merge(Type.Missing);
        }

        public void SaveAs(Workbook wb, string fileName)
        {
            wb.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        }

        public void Save(Workbook wb)
        {
            wb.Save();
        }

        public void Close(Workbook wb)
        {
            wb.Close(Type.Missing, Type.Missing, Type.Missing);
        }

        ~ExcelHelper()
        {
            for (int i = 0; i < wbs.Count; i++)
            {
                Close(wbs.get_Item(i));
            }
            wbs.Close();
            wbs = null;
            app = null;
            GC.Collect();
        }

        public static ExcelHelper Instance
        {
            get
            {
                return Nested.instance;
            }
        }

        private class Nested
        {
            internal static readonly ExcelHelper instance = new ExcelHelper();
        }
    }
}