一聚教程网:一个值得你收藏的教程网站

热门教程

C# 使用微软的Office COM类库操作Excel表

时间:2022-06-25 08:18:26 编辑:袖梨 来源:一聚教程网

使用微软的Office COM中Excel类库来操作Excel是很简单的,一般的操作也就是读取工作表、创建工作表,写入工作表等。使用之前要添加两个引用:Microsoft.Office.Core和Microsoft.Office.Interop.Excel。我列举了一下几种常见操作。

 

 代码如下 复制代码

   #region
    public interface IExcelHelper
    {
        ///


        /// 创建Excel
        ///

        void CreateExcel();

        ///


        /// 创建工作表
        ///

        /// 表名
        void CreateWorkSheet(string sheetName);

        ///


        /// 激活一个Excel工作表,作为当前操作的对象
        ///

        /// 表名
        void ActivateSheet(string sheetName);

        ///


        /// 判断一个表是否存在
        ///

        /// 表名
        /// true:存在 | false:不存在
        bool isExistSheet(string sheetName);

        ///


        /// 删除一个工作表
        ///

        /// 表序号
        void DeleteSheet(int sheetNum);

        ///


        /// 删除一个工作表
        ///

        /// 表名
        void DeleteSheet(string sheetName);

        ///


        /// 读取一个工作表中所有内容,放到DataSet中
        ///

        /// 表名
        /// 数据
        DataSet getDataSet(String sheetName);

        ///


        /// 写入一个单元格
        ///

        /// 表名
        /// 数据
        /// 行号,1开始
        /// 列号,1开始
        void WriteData(string sheetName, string data, int row, int column);

        ///


        /// 将DataTable中的所有数据写入一个工作表
        ///

        /// 表名
        /// 数据
        /// 起始行,1开始
        /// 起始列,1开始
        void WriteData(string sheetName, System.Data.DataTable data, int startRow, int startColumn);

        ///


        /// 保存Excel文件
        ///

        ///
        bool Save();

        ///


        /// 另存为Excel文件
        ///

        /// 文件名(路径和名称)
        /// Excel版本
        ///
        bool SaveAs(string fileName, XlFileFormat version);

        ///


        /// 关闭Excel
        ///

        /// 是否保存
        void Close(bool isSave);

        ///


        /// 关闭Excel
        ///

        /// 是否保存
        /// 保存的文件名
        void Close(bool isSave, string fileName);
    }
    #endregion

下面是一个对IExcelHelper接口的实现类

   

 代码如下 复制代码

#region
    public class ExcelHelper : IExcelHelper
    {
        protected String filePath;
        protected Application excel;
        protected Workbook workBook;

        protected OleDbConnection connection = null;
        protected string connStr = null;

        public ExcelHelper()
        {

        }

        public ExcelHelper(String filePath,bool ReadOnly)
        {
            this.filePath = filePath;
            this.connStr = getConnectionStr(filePath);
            LoadWorkbook(ReadOnly);
        }

        ///


        /// 从Excel中加载workbook
        ///

        private void LoadWorkbook(bool ReadOnly)
        {
            excel = new Application();
            excel.Visible = false;
            workBook = excel.Workbooks.Open(this.filePath, Missing.Value, ReadOnly,
                Missing.Value, Missing.Value, Missing.Value, true, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value);
        }

        ///


        /// 创建Excel工作薄
        ///

        public void CreateExcel()
        {
            excel = new Application();
            workBook = excel.Application.Workbooks.Add(true);
        }

        ///


        /// 将数据写入Excel
        ///

        /// 要写入的字符串
        /// 写入的行
        /// 写入的列
        public void WriteData(string sheetName, string data, int row, int column)
        {
            Worksheet worksheet = (Worksheet)excel.Worksheets[sheetName];
            worksheet.Cells[row, column] = data;
        }

        ///


        /// 将一个数据表写入一个sheet中
        ///

        /// 表名
        /// 数据表
        /// 起始行,从1开始
        /// 起始列,从1开始
        public void WriteData(string sheetName, System.Data.DataTable data,
            int startRow, int startColumn)
        {
            Worksheet worksheet = (Worksheet)excel.Worksheets[sheetName];
            for (int i = 0; i <= data.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= data.Columns.Count - 1; j++)
                {
                    worksheet.Cells[startRow + i, startColumn + j] = data.Rows[i][j].ToString();
                }
            }
        }


        ///


        /// 新建工作表
        ///

        /// 工作表名
        public void CreateWorkSheet(string sheetName)
        {
            Worksheet newWorksheet = (Worksheet)workBook.Worksheets.Add(
                Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            newWorksheet.Name = sheetName;
        }
        ///
        /// 激活工作表
        ///

        /// 工作表名
        public void ActivateSheet(string sheetName)
        {
            Worksheet worksheet = (Worksheet)excel.Worksheets[sheetName];
            worksheet.Activate();
        }

        ///


        /// 激活工作表
        ///

        /// 工作表序号
        public void ActivateSheet(int sheetNum)
        {
            Worksheet worksheet = (Worksheet)excel.Worksheets[sheetNum];
            worksheet.Activate();
        }
        ///
        /// 删除一个工作表
        ///

        /// 删除的工作表名
        public void DeleteSheet(int sheetNum)
        {
            ((Worksheet)workBook.Worksheets[sheetNum]).Delete();
        }
        ///
        /// 删除一个工作表
        ///

        /// 删除的工作表序号
        public void DeleteSheet(string sheetName)
        {
            ((Worksheet)workBook.Worksheets[sheetName]).Delete();
        }


        ///


        /// 判断一个工作表是否存在
        ///

        ///
        ///
        public bool isExistSheet(string sheetName)
        {
            if (workBook.Worksheets[sheetName] != null)
            {
                return true;
            }
            else return false;
        }

        ///


        /// 从Excel中获取一个Sheet,转化为DataSet
        ///

        /// 表名
        ///
        public DataSet getDataSet(String sheetName)
        {
            if (!isExistSheet(sheetName))
            {
                return null;
            }
            connection = new OleDbConnection(connStr);
            connection.Open();
            OleDbDataAdapter dataAdapter =
                new OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", connection);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            connection.Close();
            return dataSet;
        }

        private String getConnectionStr(String filePath)
        {
            if (filePath.EndsWith(".xlsx"))
            {
                // 2007以上的Excel版本
                return string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};"
                    +"Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'", filePath);
            }
            return "provider=microsoft.jet.oledb.4.0;data source="
                + filePath + ";extended properties='Excel 8.0;HDR=yes;IMEX=2'";
        }

        ///


        /// 保存Excel
        ///

        /// 保存成功返回True
        public bool Save()
        {
            if (filePath == "")
            {
                return false;
            }
            else
            {
                try
                {
                    workBook.Save();
                    return true;
                }
                catch (Exception)
                {
                    return false;
                }
            }
        }
        ///
        /// Excel文档另存为
        ///

        /// 保存完整路径加文件名
        /// 保存的Excel文件版本
        /// 保存成功返回True
        public bool SaveAs(string fileName, XlFileFormat version)
        {
            try
            {
                workBook.SaveAs(fileName, version, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
                    Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        ///


        /// 关闭Excel
        ///

        /// 是否保存
        public void Close(bool isSave)
        {
            workBook.Close(isSave, Type.Missing, Type.Missing);
            excel.Quit();
            workBook = null;
            excel = null;
            GC.Collect();
        }
        ///
        /// 关闭Excel
        ///

        /// 是否保存
        /// 存储文件名
        public void Close(bool isSave, string fileName)
        {
            workBook.Close(isSave, fileName, Type.Missing);
            excel.Quit();
            workBook = null;
            excel = null;
            GC.Collect();
        }
    }
    #endregion

使用IExcelHelper导入,操作,导出Excel
很简单,就是导入一个工作表的数据,绑定到DataGridView,编辑内容后导出excel。

 代码如下 复制代码

DataSet dataset1 = null;

private void TestReadExcel()
{
    IExcelHelper helper = new ExcelHelper("d:test.xls",true);
    dataset1 = helper.getDataSet("Sheet1");
    this.dataGridView1.DataSource = dataset1.Tables[0];
    helper.Close(false);
    helper = null;
}

private void TestWriteExcel()
{
    IExcelHelper helper = new ExcelHelper();
    helper.CreateExcel();
    helper.CreateWorkSheet("用户表");
    helper.WriteData("用户表", dataset1.Tables[0], 1, 1);
    helper.SaveAs("d:export.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8);
    helper.Close(false);
    helper = null;
}

private void button1_Click(object sender, EventArgs e)
{
    TestReadExcel();
}

private void button2_Click(object sender, EventArgs e)
{
    TestWriteExcel();
}

运行winform后,点击“读取Excel”

然后我们修改一下其中的一个单元格的值,点击“写入Excel”试试。

 

在D盘中我的test.xls原始数据如下:

导出后的数据如下:

热门栏目