C#导出excel表格

最新推荐文章于 2024-08-13 11:32:35 发布

yb305小白

最新推荐文章于 2024-08-13 11:32:35 发布

阅读量410

点赞数

分类专栏： c#学习

本文链接：https://blog.youkuaiyun.com/yb305/article/details/52190301

版权

c#学习专栏收录该内容

7 篇文章

订阅专栏

本文介绍如何使用 C# 和 Microsoft.Office.Interop.Excel 进行 Excel 表格的导出操作，包括添加引用、创建 DataTable、启动导出线程并设置表格样式等关键步骤。

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

1.创建工程，添加excel表格的引用（office.dll、Microsoft.Office.Interop.Excel.dll）

（1）.点击添加引用在"COM"栏中选择“Microsoft Office 12.0 Object Library”导入到工程中；

(2) 在添加引用页点击“.Net”项选择“Microsoft.Office.Interop.Excel”导入到工程中；

2.代码

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.Threading;

namespace ExportExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

/// <summary>
///导出按钮
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.Add("学号", Type.GetType("System.String"));
dt.Columns.Add("姓名", Type.GetType("System.String"));
dt.Columns.Add("班级", Type.GetType("System.String"));
List<Student> list = getStudentList();
for (int i = 0; i < list.Count; i++)
{
DataRow row = dt.NewRow();
row["学号"] = list[i].No;
row["姓名"] = list[i].Name;
row["班级"] = list[i].Classname;
dt.Rows.Add(row);
}
total = dt.Rows.Count;
showProgressBarForm(dt);
}

//创建代理。
private ProgressBarForm myProcessBar = null;//弹出的子窗体(用于显示进度条)
private delegate bool IncreaseHandle(int nValue, int vinfo);//代理创建
private IncreaseHandle myIncrease = null;//声明代理，用于后面的实例化代理
private int total = 0;

/// <summary>
///启动线程
/// </summary>
/// <param name="dt">The dt.</param>
private void showProgressBarForm(DataTable dt)
{
// 启动线程
System.Threading.Thread thread = new System.Threading.Thread(new ParameterizedThreadStart(ThreadFun));
thread.Start(dt);
}

/// <summary>
/// 显示进度条信息
/// </summary>
private void ShowProcessBar()
{
myProcessBar = new ProgressBarForm(total);
myIncrease = new IncreaseHandle(myProcessBar.Increase);
myProcessBar.ShowDialog();
myProcessBar = null;
}

/// <summary>
/// 线程函数，用于处理调用
/// </summary>
private void ThreadFun(Object obj)
{
try
{
DataTable dt = (DataTable)obj;
MethodInvoker mi = new MethodInvoker(ShowProcessBar);
this.BeginInvoke(mi);
Thread.Sleep(10);

object objReturn = null;

Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp != null)
{
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
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];//工作表
Microsoft.Office.Interop.Excel.Range range;
for (int i = 0; i < dt.Columns.Count; i++)//设置工作表表头样式
{
string columnName = dt.Columns[i].ColumnName;
worksheet.Cells[1, i + 1] = columnName;//表头列名
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;//表头颜色
range.Font.Bold = true;
range.ColumnWidth = 20;//表头列宽
range.Rows.RowHeight = 25;//行高

//range.EntireColumn.AutoFit(); //自动调整列宽
range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//居中对齐
range.WrapText = true; //文本自动换行
range.Interior.ColorIndex = 43; //填充颜色为淡紫色
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();//excel单元格赋值
}
objReturn = this.Invoke(this.myIncrease, new object[] { 1, r });//触发更改进度条信息
Thread.Sleep(1000);//等待的时间可改
}
worksheet.Name = "报表";
xlApp.Visible = true;
}
}
catch (Exception e1)
{
if (myProcessBar != null && myProcessBar.DialogResult == DialogResult.OK)
{
myProcessBar.Close();
}
}

}

/// <summary>
/// 测试数据
/// </summary>
/// <returns></returns>
private List<Student> getStudentList()
{
List<Student> list = new List<Student>();
list.Add(new Student("00", "张三", "一班"));
list.Add(new Student("01", "张1", "一班"));
list.Add(new Student("02", "张2", "一班"));
list.Add(new Student("03", "张3", "一班"));
list.Add(new Student("04", "张4", "一班"));
list.Add(new Student("05", "张5", "一班"));
list.Add(new Student("06", "张6", "一班"));
list.Add(new Student("07", "张7", "一班"));
list.Add(new Student("08", "张8", "一班"));
list.Add(new Student("09", "张9", "一班"));
list.Add(new Student("10", "张10", "一班"));
list.Add(new Student("11", "张11", "一班"));
list.Add(new Student("12", "张12", "一班"));
list.Add(new Student("13", "张13", "一班"));
list.Add(new Student("14", "张14", "一班"));
return list;
}

class Student
{
private string no;

public string No
{
get { return no; }
set { no = value; }
}
private string name;

public string Name
{
get { return name; }
set { name = value; }
}
private string classname;

public string Classname
{
get { return classname; }
set { classname = value; }
}
public Student(string no, string name, string classname)
{
this.no = no;
this.name = name;
this.classname = classname;
}

}
}
}