C#学习笔记之操作Excel

最新推荐文章于 2022-10-26 19:27:57 发布

秋忆夏伤

最新推荐文章于 2022-10-26 19:27:57 发布

阅读量941

点赞数 2

分类专栏： C#学习笔记

本文链接：https://blog.csdn.net/qq_29331365/article/details/51887463

版权

C#学习笔记专栏收录该内容

54 篇文章 1 订阅

订阅专栏

窗口界面如下所示：

代码如下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WinFormTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private int nRet = -101;

        /// 创建Excel工作簿
        private void button1_Click(object sender, EventArgs e)
        {
            OperateExcel.CreateExcel();
        }

        /// 向Excel中写入数据。（只写入当前正在使用的表中）
        private void button2_Click(object sender, EventArgs e)
        {
            // 声明一个二维数组
            string[,] data = new string[5, 3]
            {
                {"Jack", "20", "松江"},
                {"Tom", "22", "闵行"},
                {"Jim", "12", "徐汇"},
                {"Lucy", "25", "宝山"},
                {"Lily", "23", "青浦"},
            };

            // 将二维数组数据写入Excel中：
            nRet = OperateExcel.WriteData(data, 1, 1);
            if (nRet != 0)
            {
                MessageBox.Show("写入数据失败！");
                return;
            }

            // 将单一数据写入Excel中：
            nRet = OperateExcel.WriteData("Test", 6, 4);
            if (nRet != 0)
            {
                MessageBox.Show("写入数据失败！");
                return;
            }

            // 实例化一个DataTable对象，用来存储需要写入Excel的数据
            DataTable dt = new DataTable();
            // 添加列
            dt.Columns.Add("name");
            dt.Columns.Add("age");
            dt.Columns.Add("addr");
            // 添加行
            dt.Rows.Add("张三", "33", "雨花");
            dt.Rows.Add("李四", "22", "鼓楼");
            dt.Rows.Add("王五", "25", "白下");
            dt.Rows.Add("赵六", "28", "秦淮");
            dt.Rows.Add("田七", "15", "玄武");
            DataRow[] datas = dt.Select("name = '王五'"); // 获取 name 列中 "王五" 的行数据 

            nRet = OperateExcel.WriteData(dt, 7, 1);
            if (nRet != 0)
            {
                MessageBox.Show("写入数据失败！");
                return;
            }
        }

        // 向Excel中插入图片。（只插入当前正在使用的表中）
        private void button3_Click(object sender, EventArgs e)
        {
            string fileName = "";

            OpenFileDialog open = new OpenFileDialog();
            open.Title = "请选择文件：";
            open.Filter = "图片文件|*jpg;*bmp;*png";
            if (open.ShowDialog() == DialogResult.OK)
            {
                fileName = open.FileName;
                nRet = OperateExcel.InsertPicture(fileName, 50, 50, 80, 100);
                if (nRet != 0)
                {
                    MessageBox.Show("插入图片失败！");
                    return;
                }
            }
        }

        // 新建工作表
        private void button4_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.CreateSheet("NewSheet");
            if (nRet != 0)
            {
                MessageBox.Show("新建工作表失败！");
                return;
            }
        }

        // 激活工作表
        private void button5_Click(object sender, EventArgs e)
        {
            //nRet = OperateExcel.ActivateSheet(2); // 表的序号从1开始
            nRet = OperateExcel.ActivateSheet("Sheet1");
            if (nRet != 0)
            {
                MessageBox.Show("激活工作表失败！");
                return;
            }
        }

        // 重命名工作表
        private void button6_Click(object sender, EventArgs e)
        {
            //nRet = OperateExcel.ReNameSheet(2, "MySheet");
            nRet = OperateExcel.ReNameSheet("Sheet2", "MySheet");
            if (nRet != 0)
            {
                MessageBox.Show("重命名工作表失败！");
                return;
            }
        }

        // 删除工作表
        private void button7_Click(object sender, EventArgs e)
        {
            //nRet = OperateExcel.DeleteSheet(2);
            nRet = OperateExcel.DeleteSheet("MySheet");
            if (nRet != 0)
            {
                MessageBox.Show("删除工作表失败！");
                return;
            }
        }

        // 保存Excel。(名字为默认为：Sheet1.xlsx；保存默认路径：我的文档。)
        private void button8_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.SaveExcel();
            if (nRet != 0)
            {
                MessageBox.Show("保存Excel失败！");
            }
        }

        // 另存为
        private void button9_Click(object sender, EventArgs e)
        {
            // 打开文件对话框：
            SaveFileDialog saveFile = new SaveFileDialog();

            // 对话框的筛选条件
            saveFile.Filter = "*xls;*xlsx|*xls;*xlsx";
            saveFile.Title = "保存文件：";   // 对话框的标题
            if (saveFile.ShowDialog() == DialogResult.OK)
            {
                string fileName = saveFile.FileName;    // 获取保存文件的全路径和名字
                nRet = OperateExcel.SaveAs(fileName);
                if (nRet != 0)
                {
                    MessageBox.Show("文件另存为失败！");
                    return;
                }
            }
        }

        // 打开已存在的Excel
        private void button10_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFile = new OpenFileDialog();
            openFile.Filter = "*xlsx;*xls|*xlsx;*xls";
            openFile.Title = "打开文件：";
            if (openFile.ShowDialog() == DialogResult.OK)
            {
                string fileName = openFile.FileName;

                OperateExcel.OpenExcel(fileName);
            }
        }

        // 读取指定单元格内容
        private void button11_Click(object sender, EventArgs e)
        {
            string data = "";
            nRet = OperateExcel.ReadData(2, 3, ref data);
            if (nRet != 0)
            {
                MessageBox.Show("读取单元格内容失败！");
                return;
            }
            else
            {
                MessageBox.Show(data);
            }
        }

        // 合并单元格
        private void button12_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.CellsUnite(2, 6, 5, 7);
            if (nRet != 0)
            {
                MessageBox.Show("合并单元格失败！");
                return;
            }
        }

        // 设置字体及字体样式
        private void button13_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.SetFontSize(1, 1, 9, 3, "宋体", 14, true, true, 
                OperateExcel.UnderLineStyle.双线, OperateExcel.ColorIndex.淡紫色);
            if (nRet != 0)
            {
                MessageBox.Show("设置字体样式失败！");
                return;
            }
        }

        // 关闭Excel
        private void button14_Click(object sender, EventArgs e)
        {
            //nRet = OperateExcel.CloseExcel();
            //nRet = OperateExcel.CloseExcel(true);
            nRet = OperateExcel.CloseExcel(false);
            if (nRet != 0)
            {
                MessageBox.Show("关闭Excel失败！");
                return;
            }
        }

        // 设置单元格对齐方式
        private void button15_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.SetAlignment(2, 2, 7, 3, OperateExcel.ExcelHAlign.靠右, OperateExcel.ExcelVAlign.靠上);
            if (nRet != 0)
            {
                MessageBox.Show("设置单元格对齐方式失败！");
                return;
            }
        }

        // 设置行高
        private void button16_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.SetRowHeight(2, 11, 40);
            if (nRet != 0)
            {
                MessageBox.Show("设置行高失败！");
                return;
            }
        }

        // 自动调整行高
        private void button17_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.AutoRowHeight(2, 5);
            if (nRet != 0)
            {
                MessageBox.Show("自动调整行高失败！");
                return;
            }
        }

        // 设置列宽
        private void button18_Click(object sender, EventArgs e)
        {
            //nRet = OperateExcel.SetColWidth("B", "C", 20);
            nRet = OperateExcel.SetColWidth(2, 3, 20);
            if (nRet != 0)
            {
                MessageBox.Show("设置列宽失败！");
                return;
            }
        }

        // 自动调整列宽
        private void button19_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.AutoColWidth("B", "C");
            if (nRet != 0)
            {
                MessageBox.Show("自动调整列宽失败！");
                return;
            }
        }

        // 设置单元格背景色及填充方式
        private void button20_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.SetBackColor(1, 2, 5, 3, OperateExcel.ColorIndex.茶色, OperateExcel.Pattern.Grid);
            if (nRet != 0)
            {
                MessageBox.Show("设置单元格背景色及填充样式失败！");
                return;
            }
        }

        // 设置指定单元格的边框
        private void button21_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.SetBorderStyle(1, 1, 3, 3, true, true, true, true, true, true, true, true, 
                OperateExcel.LineStyle.双线, OperateExcel.BorderWeight.粗, OperateExcel.ColorIndex.橙色);
            if (nRet != 0)
            {
                MessageBox.Show("设置指定单元格的边框失败！");
                return;
            }
        }

        // 删除行
        private void button22_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.DeleteRow(2, 5);
            if (nRet != 0)
            {
                MessageBox.Show("删除行失败！");
            }
        }

        // 删除列
        private void button23_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.DeleteCol("B", "C");
            if (nRet != 0)
            {
                MessageBox.Show("删除列失败！");
            }
        }

        // 插入行
        private void button24_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.InsertRow(3);
            if (nRet != 0)
            {
                MessageBox.Show("插入行失败！");
            }
        }

        // 插入列
        private void button25_Click(object sender, EventArgs e)
        {
            nRet = OperateExcel.InsertCol("B");
            if (nRet != 0)
            {
                MessageBox.Show("插入列失败！");
            }
        }
    }
}

OperateExcel类如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

// 这种写法，可以防止Application在其他程序集中存在时 出现错误。
using Excel = Microsoft.Office.Interop.Excel;   // 引用程序集：Microsoft.Office.Interop.Excel
using Microsoft.Office.Core;                    // 引用程序集：Office
using System.Data;
using System.Windows.Forms;   


namespace WinFormTest
{
    class OperateExcel
    {

        /// <summary>
        /// 水平对齐方式
        /// </summary>
        public enum ExcelHAlign
        {
            常规 = 1,
            靠左,
            居中,
            靠右,
            填充,
            两端对齐,
            跨列居中,
            分散对齐
        }

        /// <summary>
        /// 垂直对齐方式
        /// </summary>
        public enum ExcelVAlign
        {
            靠上 = 1,
            居中,
            靠下,
            两端对齐,
            分散对齐
        }

        /// <summary>
        /// 边框粗细
        /// </summary>
        public enum BorderWeight
        {
            极细 = 1,
            细 = 2,
            粗 = -4138,
            极粗 = 4
        }

        /// <summary>
        /// 线样式
        /// </summary>
        public enum LineStyle
        {
            连续直线 = 1,
            短线 = -4115,
            线点相间 = 4,
            短线间两点 = 5,
            点 = -4118,
            双线 = -4119,
            无 = -4142,
            少量倾斜点 = 13
        }

        /// <summary>
        /// 下划线方式
        /// </summary>
        public enum UnderLineStyle
        {
            无下划线 = -4142,
            双线 = -4119,
            双线充满全格 = 5,
            单线 = 2,
            单线充满全格 = 4
        }

        /// <summary>
        /// 单元格填充方式
        /// </summary>
        public enum Pattern
        {
            Automic = -4105,
            Checker = 9,
            CrissCross = 16,
            Down = -4121,
            Gray16 = 17,
            Gray25 = -4124,
            Gray50 = -4125,
            Gray75 = -4126,
            Gray8 = 18,
            Grid = 15,
            Horizontal = -4128,
            LightDown = 13,
            LightHorizontal = 11,
            Lightup = 14,
            LightVertical = 12,
            None = -4142,
            SemiGray75 = 10,
            Solid = 1,
            Up = -4162,
            Vertical = -4166
        }

        /// <summary>
        /// 常用颜色定义，对应Excel中颜色名
        /// </summary>
        public enum ColorIndex
        {
            黑色 = 1,
            白色 = 2,
            红色 = 3,
            鲜绿色 = 4,
            蓝色 = 5,
            黄色 = 6,
            粉红色 = 7,
            青绿色 = 8,
            深红色 = 9,
            绿色 = 10,
            深蓝色 = 11,
            深黄色 = 12,
            紫罗兰色 = 13,
            青色 = 14,
            灰色25 = 15,
            灰色50 = 16,
            叫不上名色1 = 17,
            叫不上名色2 = 18,
            叫不上名色3 = 19,
            叫不上名色4 = 20,
            叫不上名色5 = 21,
            叫不上名色6 = 22,
            叫不上名色7 = 23,
            叫不上名色8 = 24,
            叫不上名色9 = 25,
            叫不上名色10 = 26,
            叫不上名色11 = 27,
            叫不上名色12 = 28,
            叫不上名色13 = 29,
            叫不上名色14 = 30,
            叫不上名色15 = 31,
            叫不上名色16 = 32,
            天蓝色 = 33,
            浅青绿色 = 34,
            浅绿色 = 35,
            浅黄色 = 36,
            淡蓝色 = 37,
            玫瑰红 = 38,
            淡紫色 = 39,
            茶色 = 40,
            浅蓝色 = 41,
            水绿色 = 42,
            酸橙色 = 43,
            金色 = 44,
            浅橙色 = 45,
            橙色 = 46,
            蓝灰色 = 47,
            灰色40 = 48,
            深青色 = 49,
            海绿色 = 50,
            深绿色 = 51,
            橄榄色 = 52,
            褐色 = 53,
            梅红色 = 54,
            靛蓝色 = 55,
            灰色80 = 56            
        }


        // Excel.Application：这种写法，可以防止Application在其他程序集中存在时出现错误。
        private static Excel.Application myExcel = null;     // 相当于Excel句柄，用来操做Excel
        private static Excel.Workbook myWorkBook = null;     // Excel工作簿(就是Excel文件)
        private static Excel.Worksheet myWorkSheet = null;   // Excel工作表。工作簿包含工作表

        /// <summary>
        /// 创建Excel表格
        /// </summary>
        public static void CreateExcel()
        {
            myExcel = new Excel.Application();                      // 实例化Application对象。

            // 创建Excel文件，但此处不会显示Excel。
            // 如果是创建，add()方法中为 true；如果是打开已存在的Excel，则在add()中写入 Excel 路径即可
            myWorkBook = myExcel.Application.Workbooks.Add(true);   
            myExcel.Visible = true;                                 // 显示Excel，如果没有该句，Excel不会显示。
        }

        /// <summary>
        /// 向Excel中写入数据，默认写入的是正在使用的表
        /// </summary>
        /// <param name="data">写入Excel中的二维数组</param>
        /// <param name="startRow">起始行(从1开始)</param>
        /// <param name="startCol">起始列(从1开始)</param>
        public static int WriteData(string[,] data, int startRow, int startCol)
        {
            if (myExcel == null)
            {
                return -1;
            }
            int rowNum = data.GetLength(0); // 行数
            int colNum = data.GetLength(1); // 列数

            for (int i = 0; i < rowNum; i++)
            {
                for (int j = 0; j < colNum; j++)
                {
                    // Cells：指Excel中的单元格，Cells[i,j]:表示第i行，第j列的单元格。
                    // 在Excel中，单元格的内容中以单引号（'）开始，表示纯文本。
                    myExcel.Cells[startRow + i, startCol + j] = "'" + data[i, j];
                }
            }
            return 0;
        }

        /// <summary>
        /// 向Excel中写入数据，默认写入的是正在使用的表
        /// </summary>
        /// <param name="data">写入Excel中的数据</param>
        /// <param name="rowNum">指定的行</param>
        /// <param name="colNum">指定的列</param>
        public static int WriteData(string data, int rowNum, int colNum)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 将数据写入指定的单元格中
            myExcel.Cells[rowNum, colNum] = "'" + data;

            return 0;
        }

        /// <summary>
        /// 向Excel中写入数据，默认写入的是正在使用的表
        /// </summary>
        /// <param name="data">要写入的数据表</param>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        public static int WriteData(DataTable data, int startRow, int startCol)
        {
            if (myExcel == null)
            {
                return -1;
            }

            for (int i = 0; i < data.Rows.Count; i++)   // 行数
            {
                for (int j = 0; j < data.Columns.Count; j++)    // 列数
                {
                    // 从 DataTable 中取值并存入 Excel 表中。
                    myExcel.Cells[startRow + i, startCol + j] = "'" + data.Rows[i][j].ToString();
                }
            }

            return 0;
        }

        /// <summary>
        /// 向Excel中插入图片
        /// </summary>
        /// <param name="pictureName">图片的全路径加名字</param>
        /// <param name="left">左边距</param>
        /// <param name="top">上边距</param>
        /// <param name="width">宽度</param>
        /// <param name="height">高度</param>
        public static int InsertPicture(string pictureName, int left, int top, int width, int height)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            myWorkSheet = myExcel.ActiveSheet;

            // 向Excel中插入图片。当参数3写成：MsoTriState.msoFalse；会出现错误：指定的值超出了范围。
            myWorkSheet.Shapes.AddPicture(pictureName, MsoTriState.msoFalse, MsoTriState.msoTrue, left, top, width, height);

            return 0;
        }

        /// <summary>
        /// 新建工作表
        /// </summary>
        /// <param name="sheetName">工作表名字</param>
        public static int CreateSheet(string sheetName)
        {
            if (myWorkBook == null)
            {
                return -1;
            }

            // 在当前工作簿的工作表集合中添加工作表，返回新工作表对象。
            // 参数1（before）：指定工作表对象，新建的工作表将置于该工作表之前；
            // 参数2（after）：指定工作表对象，新建的工作表将置于该工作表之后；
            // 参数3（count）：指定新建工作表的数量；
            // 参数4（Type）：指定工作表类型。
            // Type.Missing：表示默认。
            Excel.Worksheet worksheet = myWorkBook.Worksheets.Add(Type.Missing, Type.Missing, 2, Type.Missing);

            // 设置新工作表的名字，如果新建工作表超过两个，则只给一个新表设置名字。
            worksheet.Name = sheetName;

            return 0;
        }

        /// <summary>
        /// 激活工作表
        /// </summary>
        /// <param name="sheetNum">表的序号(从1开始)</param>
        public static int ActivateSheet(int sheetNum)
        {
            if (myWorkBook == null)
            {
                return -1;
            }

            // 根据表的序号获取表对象。
            Excel._Worksheet workSheet = myWorkBook.Worksheets[sheetNum];

            // 如果使用 Worksheet 类，会出现错误：Activate 方法和非方法之间存在二义性，请使用方法组。
            // 解决办法是： 用 _Worksheet 代替 Worksheet。
            workSheet.Activate();

            return 0;
        }

        /// <summary>
        /// 激活工作表
        /// </summary>
        /// <param name="sheetName">表的名字</param>
        public static int ActivateSheet(string sheetName)
        {
            if (myWorkBook == null)
            {
                return -1;
            }

            // 根据表的名字获取表对象：
            Excel._Worksheet workSheet = myWorkBook.Worksheets[sheetName];
            workSheet.Activate();

            return 0;
        }

        /// <summary>
        /// 重命名工作表
        /// </summary>
        /// <param name="sheetNum">表的序号</param>
        /// <param name="newSheetName">新表名</param>
        public static int ReNameSheet(int sheetNum, string newSheetName)
        {
            if (myWorkBook == null)
            {
                return -1;
            }

            // 根据表的序号获取表对象：
            Excel._Worksheet workSheet = myWorkBook.Worksheets[sheetNum];
            workSheet.Name = newSheetName;

            return 0;
        }

        /// <summary>
        /// 重命名工作表
        /// </summary>
        /// <param name="oleSheetName">原表名</param>
        /// <param name="newSheetName">新表名</param>
        public static int ReNameSheet(string oleSheetName, string newSheetName)
        {
            if (myWorkBook == null)
            {
                return -1;
            }

            // 根据表的名字获取表对象：
            Excel._Worksheet workSheet = myWorkBook.Worksheets[oleSheetName];
            workSheet.Name = newSheetName;

            return 0;
        }

        /// <summary>
        /// 删除工作表
        /// </summary>
        /// <param name="sheetNum">表的序号(从1开始)</param>
        public static int DeleteSheet(int sheetNum)
        {
            if (myWorkBook == null)
            {
                return -1;
            }

            // 根据表的序号获取表对象：
            Excel._Worksheet workSheet = myWorkBook.Worksheets[sheetNum];
            workSheet.Delete();

            return 0;
        }

        /// <summary>
        /// 删除工作表
        /// </summary>
        /// <param name="sheetName">表的名字</param>
        public static int DeleteSheet(string sheetName)
        {
            if (myWorkBook == null)
            {
                return -1;
            }

            // 根据表的名字获取表对象：
            try
            {
                Excel._Worksheet workSheet = myWorkBook.Worksheets[sheetName];
                workSheet.Delete();
            }
            catch (Exception)
            {
                return -2;
            }

            return 0;
        }

        /// <summary>
        /// 保存Excel。名字为默认值：Sheet1.xlsx；保存默认文件夹：我的文档。
        /// </summary>
        public static int SaveExcel()
        {
            if (myWorkBook == null)
            {
                return -1;
            }

            myWorkBook.Save();

            return 0;
        }

        /// <summary>
        /// 另存为
        /// </summary>
        /// <param name="fileName">文件的全路径和名字</param>
        public static int SaveAs(string fileName)
        {
            if (myWorkBook == null)
            {
                return -1;
            }

            myWorkBook.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);

            return 0;
        }

        /// <summary>
        /// 关闭Excel。会自动弹出“是否保存”提示，如果选择“保存”，会自动弹出保存路径和修改文件名。
        /// </summary>
        public static int CloseExcel()
        {
            if (myWorkBook == null || myExcel == null)
            {
                return -1;
            }

            myWorkBook.Close(Type.Missing, Type.Missing, Type.Missing); // 关闭工作簿，但是Excel框架还在。
            myExcel.Quit();     // 关闭Excel。如果没有这句，只是把Excel中的表格关闭，Excel还存在。    
            myWorkBook = null;
            myExcel = null;

            return 0;
        }

        /// <summary>
        /// 关闭Excel。
        /// </summary>
        /// <param name="isSave">是否保存。如果为true，直接跳出保存路径和修改文件名; 如果为false，直接关闭</param>
        public static int CloseExcel(bool isSave)
        {
            if (myWorkBook == null || myExcel == null)
            {
                return -1;
            }

            myWorkBook.Close(isSave, Type.Missing, Type.Missing); // 关闭工作簿，但是Excel框架还在。
            myExcel.Quit();     // 关闭Excel。如果没有这句，只是把Excel中的表格关闭，Excel还存在。    
            myWorkBook = null;
            myExcel = null;


            return 0;
        }

        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="startRow">开始行</param>
        /// <param name="endRow">结束行</param>
        /// <param name="height">行高</param>
        public static int SetRowHeight(int startRow, int endRow, int height)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取要设置行高的行
            Excel.Range range = workSheet.Rows[startRow.ToString() + ":" + endRow.ToString(), Type.Missing];
            // 设置行高
            range.RowHeight = height;

            return 0;
        }

        /// <summary>
        /// 自动调整行高
        /// </summary>
        /// <param name="startRow">开始行</param>
        /// <param name="endRow">起始行</param>
        public static int AutoRowHeight(int startRow, int endRow)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取需要自动调整行高的行
            Excel.Range range = workSheet.Rows[startRow.ToString() + ":" + endRow.ToString(), Type.Missing];
            // 根据内容自动调整行高
            range.AutoFit();

            return 0;
        }

        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="startCol">开始列(对应列的字母)</param>
        /// <param name="endCol">起始列(对应列的字母)</param>
        /// <param name="width">列宽</param>
        public static int SetColWidth(string startCol, string endCol, int width)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取需要调整列宽的列
            Excel.Range range = workSheet.Columns[startCol + ":" + endCol, Type.Missing];
            // 设置列宽
            range.ColumnWidth = width;

            return 0;
        }

        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="startCol">开始列的序号</param>
        /// <param name="endCol">结束列的序号</param>
        /// <param name="width">列的宽度</param>
        public static int SetColWidth(int startCol, int endCol, int width)
        {
            // 根据列的序号获取列的名字
            string strStartCol = GetColName(startCol);
            string strEndCol = GetColName(endCol);

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取需要调整列宽的列
            Excel.Range range = workSheet.Columns[strStartCol + ":" + strEndCol, Type.Missing];
            // 设置列宽
            range.ColumnWidth = width;

            return 0;
        }

        /// <summary>
        /// 根据列的序号获取列的名字
        /// </summary>
        /// <param name="num">列的序号</param>
        /// <returns>返回列的名字</returns>
        private static string GetColName(int num)
        {
            int m, n;
            m = num / 26;
            n = num % 26;
            if (n == 0)
            {
                m -= 1;
                n = 26;
            }

            string s = GetLetter(m) + GetLetter(n);
            return s;
        }

        private static string GetLetter(int num)
        {
            switch (num)
            {
                case 1:
                    return "A";
                case 2:
                    return "B";
                case 3:
                    return "C";
                case 4:
                    return "D";
                case 5:
                    return "E";
                case 6:
                    return "F";
                case 7:
                    return "G";
                case 8:
                    return "H";
                case 9:
                    return "I";
                case 10:
                    return "J";
                case 11:
                    return "K";
                case 12:
                    return "L";
                case 13:
                    return "M";
                case 14:
                    return "N";
                case 15:
                    return "O";
                case 16:
                    return "P";
                case 17:
                    return "Q";
                case 18:
                    return "R";
                case 19:
                    return "S";
                case 20:
                    return "T";
                case 21:
                    return "U";
                case 22:
                    return "V";
                case 23:
                    return "W";
                case 24:
                    return "X";
                case 25:
                    return "Y";
                case 26:
                    return "Z";
                default:
                    return "";
            }
        }

        /// <summary>
        /// 自动调整列宽
        /// </summary>
        /// <param name="startCol">开始列（对应列的字母）</param>
        /// <param name="endCol">结束列（对应列的字母）</param>
        public static int AutoColWidth(string startCol, string endCol)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取要自动调整列宽的列
            Excel.Range range = workSheet.Columns[startCol + ":" + endCol, Type.Missing];
            // 根据列中的内容自动调整列宽
            range.AutoFit();

            return 0;
        }

        /// <summary>
        /// 打开已存在的Excel
        /// </summary>
        /// <param name="fileName">Excel文件的全路径和文件名</param>
        public static int OpenExcel(string fileName)
        {
            myExcel = new Excel.Application();
            myWorkBook = myExcel.Workbooks.Add(fileName);   // 将已存在的Excel添加到Workbooks集合中
            myExcel.Visible = true;                         // 显示Excel

            return 0;
        }

        /// <summary>
        /// 读取指定单元格的内容
        /// </summary>
        /// <param name="rowNum">行序号</param>
        /// <param name="colNum">列序号</param>
        /// <param name="data">读取的内容</param>
        public static int ReadData(int rowNum, int colNum, ref string data)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取工作表中指定的单元格（下面两种方法都可以）
            //Excel.Range range = workSheet.Range[myExcel.Cells[rowNum, colNum], myExcel.Cells[rowNum, colNum]];
            Excel.Range range = myExcel.Range[myExcel.Cells[rowNum, colNum], myExcel.Cells[rowNum, colNum]];
            // 获取单元格中的数据
            data = range.Text.ToString();

            return 0;
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        /// <param name="endRow">结束行</param>
        /// <param name="endCol">结束列</param>
        public static int CellsUnite(int startRow, int startCol, int endRow, int endCol)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;

            // 获取工作表中需要合并的单元格的范围（下面两种方法都可以）
            Excel.Range range = workSheet.Range[myExcel.Cells[startRow, startCol], myExcel.Cells[endRow, endCol]];
            //Excel.Range range = myExcel.Range[myExcel.Cells[startRow, startCol], myExcel.Cells[endRow, endCol]];

            // 合并单元格
            range.MergeCells = true;

            return 0;
        }

        /// <summary>
        /// 设置表格中的字体及字体样式
        /// </summary>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        /// <param name="endRow">结束行</param>
        /// <param name="endCol">结束列</param>
        /// <param name="fontName">字体名字</param>
        /// <param name="fontSize">字体大小</param>
        /// <param name="isBold">是否加粗</param>
        /// <param name="isItalic">是否斜体</param>
        /// <param name="underLine">下划线类型</param>
        /// <param name="color">字体颜色</param>
        public static int SetFontSize(int startRow, int startCol, int endRow, int endCol, string fontName,
            int fontSize, bool isBold, bool isItalic, UnderLineStyle underLine, ColorIndex color)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取工作表中需要设置的单元格范围
            Excel.Range range = workSheet.Range[myExcel.Cells[startRow, startCol], myExcel.Cells[endRow, endCol]];

            range.Font.Name = fontName;
            range.Font.Size = fontSize;
            range.Font.Italic = isItalic;
            range.Font.Bold = isBold;
            range.Font.Underline = underLine;
            range.Font.Color = color;

            return 0;
        }

        /// <summary>
        /// 设置单元格对齐方式
        /// </summary>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        /// <param name="endRow">结束行</param>
        /// <param name="endCol">结束列</param>
        /// <param name="hAlign">水平对齐</param>
        /// <param name="vAlign">垂直对齐</param>
        public static int SetAlignment(int startRow, int startCol, int endRow, int endCol, ExcelHAlign hAlign, ExcelVAlign vAlign)
        {
            if (myExcel == null)
            {
                return -1;
            }

            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            Excel.Range range = workSheet.Range[myExcel.Cells[startRow, startCol], myExcel.Cells[endRow, endCol]];

            range.HorizontalAlignment = hAlign; // 设置单元格水平对齐方式
            range.VerticalAlignment = vAlign;   // 设置单元格垂直对齐方式

            return 0;
        }

        /// <summary>
        /// 设置单元格的背景颜色及填充方式
        /// </summary>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        /// <param name="endRow">结束行</param>
        /// <param name="endCol">结束列</param>
        /// <param name="color">背景颜色</param>
        /// <param name="pattern">填充方式</param>
        public static int SetBackColor(int startRow, int startCol, int endRow, int endCol, ColorIndex color, Pattern pattern)
        {
            if (myExcel == null)
            {
                return -1;
            }

            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            Excel.Range range = workSheet.Range[myExcel.Cells[startRow, startCol], myExcel.Cells[endRow, endCol]];

            range.Interior.Color = color;       // 设置背景色
            range.Interior.Pattern = pattern;   // 设置背景样式

            return 0;
        }

        /// <summary>
        /// 设置指定单元格的边框样式
        /// </summary>
        /// <param name="startRow">开始行</param>
        /// <param name="startCol">开始列</param>
        /// <param name="endRow">结束行</param>
        /// <param name="endCol">结束列</param>
        /// <param name="isDrawTop">是否划上边框</param>
        /// <param name="isDrawBottom">是否划下边框</param>
        /// <param name="isDrawLeft">是否划左边框</param>
        /// <param name="isDrawRight">是否划右边框</param>
        /// <param name="isDrawHInside">是否划水平内边框</param>
        /// <param name="isDrawVInside">是否划垂直内边框</param>
        /// <param name="isDrawDiagonalDown">是否划斜向下线</param>
        /// <param name="isDrawDiagonalUp">是否划斜向上线</param>
        /// <param name="lineStyle">边框线样式</param>
        /// <param name="borderWeight">边框粗细</param>
        /// <param name="color">边框颜色</param>
        public static int SetBorderStyle(int startRow, int startCol, int endRow, int endCol, bool isDrawTop, 
            bool isDrawBottom, bool isDrawLeft, bool isDrawRight, bool isDrawHInside, bool isDrawVInside, 
            bool isDrawDiagonalDown, bool isDrawDiagonalUp, LineStyle lineStyle, BorderWeight borderWeight, 
            ColorIndex color)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取表中指定的单元格范围
            Excel.Range range = workSheet.Range[myExcel.Cells[startRow, startCol], myExcel.Cells[endRow, endCol]];

            // 清除所有边框
            range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = LineStyle.无;
            range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = LineStyle.无;
            range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = LineStyle.无;
            range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = LineStyle.无;
            range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = LineStyle.无;
            range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = LineStyle.无;
            range.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = LineStyle.无;
            range.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = LineStyle.无;

            // 按参数设置边框
            if (isDrawTop)
            {
                range.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = lineStyle;
                range.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = borderWeight;
                range.Borders[Excel.XlBordersIndex.xlEdgeTop].Color = color;
            }

            if (isDrawBottom)
            {
                range.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = lineStyle;
                range.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = borderWeight;
                range.Borders[Excel.XlBordersIndex.xlEdgeBottom].Color = color;
            }

            if (isDrawLeft)
            {
                range.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = lineStyle;
                range.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = borderWeight;
                range.Borders[Excel.XlBordersIndex.xlEdgeLeft].Color = color;
            }

            if (isDrawRight)
            {
                range.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = lineStyle;
                range.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = borderWeight;
                range.Borders[Excel.XlBordersIndex.xlEdgeRight].Color = color;
            }

            if (isDrawHInside)
            {
                range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = lineStyle;
                range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = borderWeight;
                range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Color = color;
            }

            if (isDrawVInside)
            {
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = lineStyle;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = borderWeight;
                range.Borders[Excel.XlBordersIndex.xlInsideVertical].Color = color;
            }

            if (isDrawDiagonalDown)
            {
                range.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = lineStyle;
                range.Borders[Excel.XlBordersIndex.xlDiagonalDown].Weight = borderWeight;
                range.Borders[Excel.XlBordersIndex.xlDiagonalDown].Color = color;
            }

            if (isDrawDiagonalUp)
            {
                range.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = lineStyle;
                range.Borders[Excel.XlBordersIndex.xlDiagonalUp].Weight = borderWeight;
                range.Borders[Excel.XlBordersIndex.xlDiagonalUp].Color = color;
            }

            return 0;
        }

        /// <summary>
        /// 删除行
        /// </summary>
        /// <param name="startRow">起始行</param>
        /// <param name="endRow">结束行</param>
        public static int DeleteRow(int startRow, int endRow)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取要删除的行范围
            Excel.Range range = workSheet.Rows[startRow.ToString() + ":" + endRow.ToString(), Type.Missing];
            // 删除行
            range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);

            return 0;
        }

        /// <summary>
        /// 删除列
        /// </summary>
        /// <param name="startCol">开始列（对应的字母）</param>
        /// <param name="endCol">结束列（对应的字母）</param>
        public static int DeleteCol(string startCol, string endCol)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取要删除的列的范围
            Excel.Range range = workSheet.Columns[startCol + ":" + endCol, Type.Missing];
            // 删除列
            range.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);

            return 0;
        }

        /// <summary>
        /// 插入行
        /// </summary>
        /// <param name="rowNum">要插入行的位置</param>
        /// <returns></returns>
        public static int InsertRow(int rowNum)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取要插入的行的位置
            Excel.Range range = workSheet.Rows[rowNum.ToString(), Type.Missing];
            
            // 在指定行的上面插入行
            //range.Insert(Type.Missing, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            range.Insert(Type.Missing, Excel.XlInsertFormatOrigin.xlFormatFromRightOrBelow);

            return 0;
        }

        /// <summary>
        /// 插入列
        /// </summary>
        /// <param name="strCol">指定要插入列的位置</param>
        /// <returns></returns>
        public static int InsertCol(string strCol)
        {
            if (myExcel == null)
            {
                return -1;
            }

            // 获取正在使用的工作表
            Excel.Worksheet workSheet = myExcel.ActiveSheet;
            // 获取要插入列的位置
            Excel.Range range = workSheet.Columns[strCol, Type.Missing];
            // 在指定列的前面插入列
            range.Insert(Type.Missing, Excel.XlInsertShiftDirection.xlShiftToRight);

            return 0;
        }
    }
}