C# Excel文档操作源码

最新推荐文章于 2025-05-28 09:57:02 发布

原创最新推荐文章于 2025-05-28 09:57:02 发布 · 454 阅读

0 ·

CC 4.0 BY-SA版权

C# 专栏收录该内容

23 篇文章

订阅专栏

这篇博客展示了如何使用C#的Microsoft.Office.Interop.Excel库来创建、写入和读取Excel文件。作者创建了一个名为`ExcelOperator`的类，用于封装Excel操作。在`CreateExcel`方法中，程序从数据库中获取数据并填充到Excel表格中，同时计算并显示平均值，不及格的成绩会以红色高亮。最后，博客还提供了读取Excel文件并将数据展示在DataGridView控件上的示例代码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;

namespace ExcelOperator
{
public class ExcelOperator
{
public Application ExcelApplincation;
public Workbook ExcelWorkBook;
public Worksheet ExcelWorkSheet;

public ExcelOperator()
{
ExcelApplincation = new Application();
ExcelApplincation.Visible = true;
}

public void CreateExcel()
{
try
{
ExcelWorkBook = ExcelApplincation.Workbooks.Add();

ExcelWorkSheet = ExcelWorkBook.Worksheets[1] as Worksheet;

//写入表格
WriteExcel();

object fileName = Path.Combine(Directory.GetCurrentDirectory(), "1.xlsx");

ExcelWorkBook.SaveAs(fileName);
}
catch (System.Exception ex)
{

}
finally
{
((_Application)ExcelApplincation).Quit();
}
}

public Range this[object row, object col]
{
get { return ExcelWorkSheet.Cells[row, col]; }
}

public void WriteExcel()
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "MXC-PC";
builder.InitialCatalog = "student";
builder.UserID = "sa";
builder.Password = "south";

SqlDataAdapter adapter = new SqlDataAdapter("select *from student_info", builder.ConnectionString);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);

string[] fields = new string[] { "学号", "姓名", "年龄", "年级", "成绩", "性别" };

for (int col = 0; col < dataSet.Tables[0].Columns.Count; col++)
{
Range range = this[1, col + 1];
range.Value2 = fields[col];
}

int row = 2;
for (int i = 0; i < dataSet.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < dataSet.Tables[0].Columns.Count; j++)
{
Range range = this[row, j + 1];
range.Value2 = dataSet.Tables[0].Rows[i][j];

if (j == 4 && range.Value2 < 60) //不及格
{
range.Interior.Color = Color.Red;
}
}
row++;
}

Range range1 = this[row, 1];
range1.Value2 = "平均值";

Range rangeAge = this[row, 3];
rangeAge.Formula = string.Format("=AVERAGE(C2:C{0})", row-1);

Range rangeGrade = this[row, 4];
rangeGrade.Formula = string.Format("=AVERAGE(D2:D{0})", row - 1);

Range rangeResult = this[row, 5];
rangeResult.Formula = string.Format("=AVERAGE(E2:E{0})", row - 1);
}
}
}

//读取

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
Workbook workBook = excelApp.Workbooks.Open(Path.Combine(Directory.GetCurrentDirectory(), "1.xlsx"));
Worksheet workSheet = workBook.Worksheets[1] as Worksheet;
try
{
int row = 2, col = 1;

Range range = workSheet.Cells[row, col];
while (range.Value2 != null)
{
object[] values = new object[6];
while (range.Value2 != null)
{
values[col - 1] = range.Value2;
col++;
range = workSheet.Cells[row, col];
}

DataGridViewRow viewRow = new DataGridViewRow();
viewRow.CreateCells(dataGridView1, values);
dataGridView1.Rows.Add(viewRow);
row++;
col = 1;
range = workSheet.Cells[row, col];
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excelApp.Quit();
}