Excel操作类

 

public class Excel
    {
        private ExcelNS.Application _excelApp = new ExcelNS.Application();
        private ExcelNS.Range _range;

        /// <summary>
        /// 工作簿
        /// </summary>
        private ExcelNS.Workbook _workBook;
        public ExcelNS.Workbook WorkBook
        {
            get { return _workBook; }
        }

        /// <summary>
        /// 工作表集合
        /// </summary>
        public ExcelNS.Worksheets WorkSheets
        {
            get { return (ExcelNS.Worksheets)_workBook.Worksheets; }
        }

        /// <summary>
        /// 工作表数量
        /// </summary>
        /// <returns></returns>
        public int SheetCount
        {
            get { return _workBook.Sheets.Count; }
        }

        /// <summary>
        /// 活动工作表
        /// </summary>
        public ExcelNS.Worksheet ActiveSheet
        {
            get { return (ExcelNS.Worksheet)_workBook.ActiveSheet; }
        }

        /// <summary>
        /// 构造函数，添加一个工作簿
        /// </summary>
        public Excel()
        {
            _excelApp.Visible = false;
            _workBook = _excelApp.Workbooks.Add(Missing.Value);
            ((ExcelNS.Worksheet)_workBook.Sheets[1]).Select(Missing.Value);
        }

        /// <summary>
        /// 保存工作簿
        /// </summary>
        /// <param name="fileName">工作簿名称（文件名 *.xls）</param>
        public void SaveAs(string fileName)
        {
            _workBook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, 
                Missing.Value, Missing.Value);

            _range = null;
            _workBook = null;
            _excelApp.Quit();
            _excelApp = null;
        }

        /// <summary>
        /// 获取工作表
        /// </summary>
        /// <param name="index">工作表序号</param>
        public ExcelNS.Worksheet GetWorkSheet(int index)
        {
            if (index < 1 || index > this.SheetCount)
            {
                throw new Exception("下标超出范围！");
            }

            return (ExcelNS.Worksheet)this._workBook.Sheets[index];
        }

        /// <summary>
        /// 选中并激活工作表
        /// </summary>
        /// <param name="index">工作表序号</param>
        public void SelectSheet(int index)
        {
            if (index < 1 || index > this.SheetCount)
            {
                throw new Exception("下标超出范围！");
            }

            ((ExcelNS.Worksheet)_workBook.Sheets[index]).Select(Missing.Value);
        }

        /// <summary>
        /// 重命名工作表
        /// </summary>
        /// <param name="index">工作表序号</param>
        /// <param name="sheetName">工作表名</param>
        public void RenameSheetName(int index, string sheetName)
        {
            if (index < 1 || index > this.SheetCount)
            {
                throw new Exception("下标超出范围！");
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new Exception("工作表名不能为空！");
            }

            foreach (ExcelNS.Worksheet sht in _workBook.Sheets)
            {
                if (sht.Name == sheetName && sht.Index != index)
                {
                    throw new Exception("工作表名不能重复！");
                }
            }

            ((ExcelNS.Worksheet)_workBook.Sheets[index]).Name = sheetName;
        }

        /// <summary>
        /// 添加工作表
        /// </summary>
        /// <param name="sheetName">工作表名</param>
        public void AddSheet(string sheetName)
        {
            if (string.IsNullOrEmpty(sheetName))
            {
                throw new Exception("工作表名不能为空！");
            }

            foreach (ExcelNS.Worksheet sht in _workBook.Sheets)
            {
                if (sht.Name == sheetName)
                {
                    throw new Exception("工作表名不能重复！");
                }
            }

            ExcelNS.Worksheet sheet = (ExcelNS.Worksheet)_workBook.Sheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            sheet.Name = sheetName;
        }

        /// <summary>
        /// 删除工作表
        /// </summary>
        /// <param name="index">工作表序号</param>
        public void DeleteSheet(int index)
        {
            if (index < 1 || index > this.SheetCount)
            {
                throw new Exception("下标超出范围！");
            }

            ((ExcelNS.Worksheet)_workBook.Sheets[index]).Delete();
        }

        /// <summary>
        /// 为单元格写入值
        /// </summary>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="value"></param>
        public void SetCellValue(int row, int col, object value)
        {
            if (row < 1 || row>65536 || col<1 || col>256)
            {
                throw new Exception("下标超出范围！");
            }

            ActiveSheet.Cells[row, col] = value;
        }

        /// <summary>
        /// 写入行
        /// </summary>
        /// <param name="row">行号</param>
        /// <param name="values">行值</param>
        public void SetRowValues(int row, object[] values)
        {
            if (values.Length > 256)
            {
                throw new Exception("不能超过256列！");
            }

            int col = 1;
            foreach (object value in values)
            {
                ActiveSheet.Cells[row, col] = value;
                col++;
            }
        }

        /// <summary>
        /// 写入列
        /// </summary>
        /// <param name="col">列号</param>
        /// <param name="values">列值</param>
        public void SetColValues(int col, object[] values)
        {
            if (values.Length > 65536)
            {
                throw new Exception("不能超过65536行！");
            }

            int row = 1;
            foreach (object value in values)
            {
                ActiveSheet.Cells[row, col] = value;
                row++;
            }
        }

        /// <summary>
        /// 向单元格插入图片
        /// </summary>
        /// <param name="row">单元格所在行</param>
        /// <param name="col">单元格所在列</param>
        /// <param name="fileName">图片路径</param>
        public void AddPicture(int row, int col, string fileName)
        {
            if (row < 1 || row > 65536 || col < 1 || col > 256)
            {
                throw new Exception("下标超出范围！");
            }

            _range = (ExcelNS.Range)ActiveSheet.Cells[row, col];
            ExcelNS.Pictures pictures = (ExcelNS.Pictures)ActiveSheet.Pictures(Missing.Value);
            ExcelNS.Picture picture = (ExcelNS.Picture)pictures.Insert(fileName, Missing.Value);

            picture.Top = (double)_range.Top;
            picture.Left = (double)_range.Left;
            picture.Width = (double)_range.Width;
            picture.Height = (double)_range.Height;
        }

        /// <summary>
        /// 向单元格插入图片
        /// </summary>
        /// <param name="row">单元格所在行</param>
        /// <param name="col">单元格所在列</param>
        /// <param name="fileName">图片路径</param>
        /// <param name="width">图片宽（大于0）</param>
        /// <param name="height">图片高（大于0）</param>
        public void AddPicture(int row, int col, string fileName, Single width, Single height)
        {
            if (row < 1 || row > 65536 || col < 1 || col > 256)
            {
                throw new Exception("下标超出范围！");
            }

            if (width <= 0)
            {
                throw new Exception("图片宽度必须大于0！");
            }
            if (height <= 0)
            {
                throw new Exception("图片高度必须大于0！");
            }

            _range = (ExcelNS.Range)ActiveSheet.Cells[row, col];
            ExcelNS.Pictures pictures = (ExcelNS.Pictures)ActiveSheet.Pictures(Missing.Value);
            ExcelNS.Picture picture = (ExcelNS.Picture)pictures.Insert(fileName, Missing.Value);

            picture.Top = (double)_range.Top;
            picture.Left = (double)_range.Left;
            picture.Width = width / 5.8;
            picture.Height = height;
        }

        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="row">行号（1-65536）</param>
        /// <param name="height">行高（大于0）</param>
        public void SetRowHeight(Single row, Single height)
        {
            if (row < 1 || row > 65536)
            {
                throw new Exception("下标超出范围！");
            }

            if (height <= 0)
            {
                throw new Exception("行高度必须大于0！");
            }

            _range = (ExcelNS.Range)ActiveSheet.Cells[row, 1];
            _range.RowHeight = height;
        }

        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="col">列号（1-256）</param>
        /// <param name="width">列宽（大于0）</param>
        public void SetColWidth(int col, Single width)
        {
            if (col < 1 || col > 256)
            {
                throw new Exception("下标超出范围！");
            }

            if (width <= 0)
            {
                throw new Exception("列宽度必须大于0！");
            }

            _range = (ExcelNS.Range)ActiveSheet.Cells[1, col];
            _range.ColumnWidth = width / 5.8;
        }

        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="rows">行集合（起始行号:结束行号，如3:5）</param>
        /// <param name="height">行高</param>
        public void SetRowHeight(object rows, Single height)
        {
            if (height <= 0)
            {
                throw new Exception("行高度必须大于0！");
            }

            try
            {
                ((ExcelNS.Range)ActiveSheet.Rows[rows, System.Type.Missing]).RowHeight = height;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="cols">列集合（起始列标签:结束列标签，如B:F）</param>
        /// <param name="width">列宽（大于0）</param>
        public void SetColWidth(object cols, Single width)
        {
            if (width <= 0)
            {
                throw new Exception("列宽度必须大于0！");
            }

            try
            {
                ((ExcelNS.Range)ActiveSheet.Columns[cols, System.Type.Missing]).ColumnWidth = width / 5.8;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

 操作测试Demo

 /// <summary>
        /// 生成文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btnTest_Click(object sender, EventArgs e)
        {
            string fileName = @"D:\" + DateTime.Now.ToString("yyyyMMdd-HHmmss_") + ".xls";
            Excel excel = new Excel();
            //添加工作表
            excel.AddSheet("添加的工作表");

            //选中第二个工作表
            excel.SelectSheet(2);
            excel.SetCellValue(1, 2, "abc");
            excel.AddPicture(1, 5, @"D:\a.jpg");

            //选中第三个工作表
            excel.SelectSheet(3);
            excel.SetCellValue(1, 3, "abc");
            excel.AddPicture(2, 5, @"D:\a.jpg", 100, 80);
            excel.AddPicture(3, 5, @"D:\b.jpg", 100, 80);
            //设置行高、列宽
            excel.SetRowHeight(2, 80);
            excel.SetRowHeight(3, 80);
            excel.SetColWidth(5, 100);

            //选中第一个工作表
            excel.SelectSheet(1);
            //添加行
            excel.SetRowValues(1, new string[] {"序号", "类型A", "类型B", "类型C", "图片" });
            //设置图片列宽
 
            string picName =   @"D:\b.jpg";
            excel.AddPicture(2, 5, picName, 80, 80);
            excel.SetRowHeight(2, 80);
            excel.SetColWidth(5, 100);

            excel.SaveAs(fileName);
        }