C#基于Microsoft.Excel操作Excel表

掌握基础的计算机操作，如Excel表格使用 #生活技巧# #工作学习技巧# #数字技能培养#

首先搭建环境，分别引入COM组件中的相应Dll

创建Excel表：

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

object misValue = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);

Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = xlWorkBook.Sheets["Sheet1"];

xlWorkSheet.Cells[1, 1] = "数据类型1";

xlWorkSheet.Cells[1, 2] = "数据类型2";

xlWorkSheet.Cells[1, 3] = "数据类型3";

xlWorkSheet.Cells[1, 4] = "数据类型4";

xlWorkSheet.Cells[1, 5] = "数据类型5";

xlWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

xlWorkBook.Save();

xlWorkBook.Close(true, misValue, misValue);

写数据：

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

object misValue = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(path);

Microsoft.Office.Interop.Excel.Sheets Sheets = xlWorkBook.Sheets;

Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = Sheets[1];

Range range = xlWorkSheet.UsedRange;

int rCntTail = range.Rows.Count + 1;

xlWorkSheet.Cells[rCntTail, 1] = data1;

xlWorkSheet.Cells[rCntTail, 2] = data2;

xlWorkSheet.Cells[rCntTail, 3] = data3;

xlWorkSheet.Cells[rCntTail, 4] = data4;

xlWorkSheet.Cells[rCntTail, 5] = data5;

xlWorkBook.Close(true, misValue, misValue);

读数据：

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(path);

Microsoft.Office.Interop.Excel.Sheets Sheets = xlWorkBook.Sheets;

Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = Sheets[1];

Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.UsedRange;

int rCnt = range.Rows.Count;

int cCnt = range.Columns.Count;

string[,] excelData = new string[rCnt, cCnt];

for (int i = 1; i <= rCnt; i++)

{

for (int j = 1; j <= cCnt; j++)

{

string data = "" + (range.Cells[i, j] as Range).Value2;

excelData[i - 1, j - 1] = data;

}

}

xlWorkBook.Close(true, null, null);

完整的演示：

窗体设计：

代码：

using Microsoft.Office.Interop.Excel;

using System;

using System.Collections;

using System.Collections.Generic;

using System.Diagnostics;

using System.Drawing;

using System.IO;

using System.Text;

using System.Windows.Forms;

using Font = System.Drawing.Font;

using Label = System.Windows.Forms.Label;

namespace 操作excel制作报表

{

public partial class Form1 : Form

{

string path;

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

public Form1()

{

InitializeComponent();

}

private void button1_Click_1(object sender, EventArgs e)

{

string excelName = textBox1.Text;

if (excelName != null && excelName != "")

{

FolderBrowserDialog folder = new FolderBrowserDialog();

folder.Description = "选择目录";

if (folder.ShowDialog() == DialogResult.OK)

{

string folderPath = folder.SelectedPath;

string filePath = folderPath + "\\" + excelName + ".xls";

path = filePath;

object misValue = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);

Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = xlWorkBook.Sheets["Sheet1"];

xlWorkSheet.Cells[1, 1] = "数据类型1";

xlWorkSheet.Cells[1, 2] = "数据类型2";

xlWorkSheet.Cells[1, 3] = "数据类型3";

xlWorkSheet.Cells[1, 4] = "数据类型4";

xlWorkSheet.Cells[1, 5] = "数据类型5";

xlWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

xlWorkBook.Save();

xlWorkBook.Close(true, misValue, misValue);

if (File.Exists(filePath))

{

MessageBox.Show("创建成功！");

}

}

}

}

private void button2_Click(object sender, EventArgs e)

{

if (path == null)

{

return;

}

string data1 = textBox2.Text;

string data2 = textBox3.Text;

string data3 = textBox4.Text;

string data4 = textBox5.Text;

string data5 = textBox6.Text;

object misValue = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(path);

Microsoft.Office.Interop.Excel.Sheets Sheets = xlWorkBook.Sheets;

Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = Sheets[1];

Range range = xlWorkSheet.UsedRange;

int rCntTail = range.Rows.Count + 1;

xlWorkSheet.Cells[rCntTail, 1] = data1;

xlWorkSheet.Cells[rCntTail, 2] = data2;

xlWorkSheet.Cells[rCntTail, 3] = data3;

xlWorkSheet.Cells[rCntTail, 4] = data4;

xlWorkSheet.Cells[rCntTail, 5] = data5;

xlWorkBook.Close(true, misValue, misValue);

MessageBox.Show("写入成功！");

}

private void button3_Click(object sender, EventArgs e)

{

if (path == null)

{

var result = MessageBox.Show("当前Excel对象未被实例化，是否从已有文件中打开？", "操作提示",

MessageBoxButtons.YesNo,

MessageBoxIcon.Question);

if (result == DialogResult.No)

{

return;

}

button4_Click(sender, e);

}

if (path == null)

{

return;

}

Microsoft.Office.Interop.Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(path);

Microsoft.Office.Interop.Excel.Sheets Sheets = xlWorkBook.Sheets;

Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet = Sheets[1];

Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.UsedRange;

int rCnt = range.Rows.Count;

int cCnt = range.Columns.Count;

string[,] excelData = new string[rCnt, cCnt];

for (int i = 1; i <= rCnt; i++)

{

for (int j = 1; j <= cCnt; j++)

{

string data = "" + (range.Cells[i, j] as Range).Value2;

excelData[i - 1, j - 1] = data;

}

}

xlWorkBook.Close(true, null, null);

Table_Load(excelData);

}

private void Table_Load(string[,] excelData)

{

panel1.Controls.Clear();

TableLayoutPanel table = new TableLayoutPanel();

int rExcelLength = excelData.GetLength(0);

int cExcelLength = excelData.GetLength(1);

table.Dock = DockStyle.Top;

table.ColumnCount = cExcelLength;

table.Height = table.RowCount * 40;

for (int i = 0; i < cExcelLength; i++)

{

table.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle(System.Windows.Forms.SizeType.Percent, table.Width * 100 / excelData.GetLength(1) * 0.1f));

}

for (int i = 0; i < rExcelLength; i++)

{

table.RowCount++;

table.Height = table.RowCount * 44;

table.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Absolute, 40));

table.CellBorderStyle = TableLayoutPanelCellBorderStyle.OutsetPartial;

for (int j = 0; j < cExcelLength; j++)

{

Label label1 = new Label();

label1.Text = excelData[i, j];

label1.Width = 200;

label1.Height = 40;

label1.Font = new Font("隶书", 13, FontStyle.Bold);

label1.TextAlign = ContentAlignment.MiddleCenter;

table.Controls.Add(label1, j, i);

}

}

panel1.Controls.Add(table);

}

private void button4_Click(object sender, EventArgs e)

{

OpenFileDialog dialog = new OpenFileDialog();

dialog.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx";

if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)

{

path = dialog.FileName;

}

}

}

}

效果：

生成的外部Excel文件内容：

网址：C#基于Microsoft.Excel操作Excel表 https://www.yuejiaxmz.com/news/view/439722

相关内容

《Excel图表之道——如何制作专业有效的商务图表(彩》【价格 目录 书评 正版】
Python操作Excel的Xlwings教程（八）——Excel使用VBA调用Python
如何在Excel中高效合并多个工作表：三种方法详解
自动化任务：利用Excel VBA优化日常工作
如何在Excel中轻松制作实用的日历日程表？
Excel电子表格：提升工作效率的必备工具
工作中常用的25个Excel操作技巧，附详细步骤，收藏备用
Excel在线表格，提升工作效率的必备工具
excel出入库自动表怎么做
【Excel】常态化繁杂操作实现自动化处理（无需VBA编程）

随便看看