c# 通过com组件读取、写入excel

1、电脑首先要装excel
2、导入dll

20180910154744605.png

3、读取

private void read()
        {
            // excel的前2排2列有数据
            string _FileName = "C:\\Users\\abc\\Desktop\\test.xlsx";
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，您的电脑可能没装Excel");
                return;
            }
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            workbooks.Open(_FileName, missing, true, missing, missing, missing,
                missing, missing, missing, true, missing, missing, missing, missing, missing);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlApp.Worksheets.get_Item(1);
            int rows = 2;
            int column = 2;
            for (int i = 1; i <= rows; i++)
            {
                for (int j = 1; j <= column; j++)
                {
                    string name = Convert.ToString(((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[i, j]).Value);
                    Console.WriteLine(name);
                }
            }
        }

4、写入

private void write()
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，您的电脑可能没装Excel");
                return;
            }
            Microsoft.Office.Interop.Excel.Range range;
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];

            // 写入数据
            int startRow = 4;
            int endRow = 7;
            int startColumn = 4;
            int endColumn = 7;
            int value = 0;
            for (int i = startRow; i <= endRow; i++)
            {
                for (int j = startColumn; j <= endColumn; j++ )
                {
                    worksheet.Cells[i, j] = ++value;
                }
            }
            // 列宽自适应
            worksheet.Columns.EntireColumn.AutoFit();
            // 全局居中对齐
            xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;

            string _FileName = "C:\\Users\\abc\\Desktop\\test_write.xlsx";
            // 复制虚表，保存文件，关闭Application，销毁资源
            try
            {
                workbook.Saved = true;
                workbook.SaveCopyAs(_FileName);
            }
            catch (Exception ex)
            {
                MessageBox.Show("到处文件出错" + ex.Message);
            }
            xlApp.Quit();
            // 强行销毁
            GC.Collect();
        }