在C#中创建、读、写EXCEL文件(基于COM)

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zzukun/article/details/50830439

1. EXCEL Library

在使用C#中的excel模块之前,我们需要先把excel library加入到project中。

首先创建一个空项目,然后创建一个按钮。随后,如下图点击“项目”->“添加引用”:


随后选择microsoft excel 1X.0 object library。



2. 在C#中编程创建excel文件

首先初始化excel object

Excel.Application excelApp = new Excel.ApplicationClass();


在创建excel workbook之前,检查系统是否安装excel

            if(excelApp == null){
                // if equal null means EXCEL is not installed.
                MessageBox.Show("Excel is not properly installed!");
                return;
            }


判断文件是否存在,如果存在就打开workbook,如果不存在就新建一个

            // open a workbook,if not exist, create a new one
            Excel.Workbook workBook;
            if(File.Exists(filename))
            {
                workBook = excelApp.Workbooks.Open(filename, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            }
            else
            {
                workBook = excelApp.Workbooks.Add(true);
            }


在创建完workbook之后,下一步就是新建worksheet并写入数据

            //new a worksheet
            Excel.Worksheet workSheet = workBook.ActiveSheet as Excel.Worksheet;

            //write data
            workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);//获得第i个sheet,准备写入
            workSheet.Cells[1, 3] = "(1,3)Content";


有两个选项可以设置,如下,visable属性设置为true的话,excel程序会启动;false的话,excel只在后台运行。

displayalert设置为true将会显示excel中的提示信息。

            //set visible the Excel will run in background
            excelApp.Visible = false;
            //set false the alerts will not display
            excelApp.DisplayAlerts = false;


保存文件,关闭workbook

            workBook.SaveAs(filename);
            workBook.Close(false, Missing.Value, Missing.Value);


退出并清理objects,回收内存

            //quit and clean up objects
            excelApp.Quit();
            workSheet = null;
            workBook = null;
            excelApp = null;
            GC.Collect();

3.完整代码

最后附上完整代码(Form_Start.cs):

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;

using System.IO;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

namespace InfoExtraction
{
    public partial class Form_Start : Form
    {
        public Form_Start()
        {
            InitializeComponent();
        }

        private void Form_Start_Load(object sender, EventArgs e)
        {

        }

        private void button_run_Click(object sender, EventArgs e)
        {
            string currentPath = Directory.GetCurrentDirectory();
            WriteExcel(currentPath + "\\results.xlsx");

            MessageBox.Show("Success!");
        }

        private void button_exit_Click(object sender, EventArgs e)
        {
            Close();
            Application.Exit();
        }

        public void WriteExcel(string filename) {
            //new an excel object
            Excel.Application excelApp = new Excel.ApplicationClass();
            if(excelApp == null){
                // if equal null means EXCEL is not installed.
                MessageBox.Show("Excel is not properly installed!");
                return;
            }

            // open a workbook,if not exist, create a new one
            Excel.Workbook workBook;
            if(File.Exists(filename))
            {
                workBook = excelApp.Workbooks.Open(filename, 0, false, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            }
            else
            {
                workBook = excelApp.Workbooks.Add(true);
            }

            //new a worksheet
            Excel.Worksheet workSheet = workBook.ActiveSheet as Excel.Worksheet;

            //write data
            workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);//获得第i个sheet,准备写入
            workSheet.Cells[1, 3] = "(1,3)Content";
            
            //set visible the Excel will run in background
            excelApp.Visible = false;
            //set false the alerts will not display
            excelApp.DisplayAlerts = false;

            //workBook.SaveAs(filename, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            workBook.SaveAs(filename);
            workBook.Close(false, Missing.Value, Missing.Value);

            //quit and clean up objects
            excelApp.Quit();
            workSheet = null;
            workBook = null;
            excelApp = null;
            GC.Collect();
        }
    }
}

展开阅读全文

没有更多推荐了,返回首页