1.概要
生成excel文件并保存
2.运行结果
2.1 生成文件
2.2 生成的内容
3.试验
3.1 试验1:使用 Microsoft Office Interop Excel 组件导出 Excel 文件(需要安装 Microsoft Excel 或者 Microsoft Office 套件)
using System;
using System.Data;
using System.IO;
using Microsoft.Office.Interop.Excel;
namespace ExcelOffice
{
internal class Program
{
static void Main(string[] args)
{
Console.WriteLine("Excel试验-ClosedXML");
Program p = new Program();
p.main();
Console.ReadKey();
}
private void main()
{
test();
}
private void test()
{
// 创建一个 DataTable 对象来存储数据
System.Data.DataTable dataTable = new System.Data.DataTable("MyData");
// 添加列到 DataTable
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Age", typeof(int));
// 向 DataTable 中添加数据行
dataTable.Rows.Add("张三", 30);
dataTable.Rows.Add("李四", 25);
// 使用 Microsoft Office Interop Excel 组件导出 Excel 文件
Application excelApp = new Application();
Workbook workbook = excelApp.Workbooks.Add();
//Workbook workbook = excelApp.Workbooks.Add(null);
//Workbook workbook = excelApp.Workbooks[0];
Worksheet worksheet = (Worksheet)workbook.Worksheets[1];
int row = 1;
foreach (DataRow dataRow in dataTable.Rows)
{
worksheet.Cells[row, 1] = dataRow["Name"].ToString();
worksheet.Cells[row, 2] = dataRow["Age"];
row++;
}
// 将 Excel 文件保存到磁盘
string fileName = @"MyExcelFile.xlsx";
workbook.SaveAs(fileName);
// 关闭 Excel 应用程序和工作簿对象,并释放资源
workbook.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
}
}
}
3.2 试验2-NPOI
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Data;
using System.IO;
namespace Excel试验
{
internal class Program
{
static void Main(string[] args)
{
Console.WriteLine("Excel试验-NPOI");
Program p = new Program();
p.main();
Console.ReadKey();
}
private void main()
{
test();
}
private void test()
{
// 创建一个 DataTable 对象来存储数据
DataTable dataTable = new DataTable("MyData");
// 添加列到 DataTable
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Age", typeof(int));
// 向 DataTable 中添加数据行
dataTable.Rows.Add("张三", 30);
dataTable.Rows.Add("李四", 25);
IWorkbook workbook = new HSSFWorkbook();
ISheet worksheet = workbook.CreateSheet("Sheet1");
int row = 0;
foreach (DataRow dataRow in dataTable.Rows)
{
IRow newRow = worksheet.CreateRow(row);
newRow.CreateCell(0).SetCellValue(dataRow["Name"].ToString());
newRow.CreateCell(1).SetCellValue(Convert.ToInt32(dataRow["Age"]));
row++;
}
// 将 Excel 文件保存到磁盘
string fileName = @"MyExcelFile.xls";
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
// 释放资源
workbook.Dispose();
}
}
}
3.3 试验3 EPPlus
using OfficeOpenXml;
using System;
using System.Data;
using System.IO;
namespace ExcelEPPlus
{
internal class Program
{
static void Main(string[] args)
{
Console.WriteLine("Excel试验-EPPlus");
Program p = new Program();
p.main();
Console.ReadKey();
}
private void main()
{
test();
}
private void test()
{
// 创建一个 DataTable 对象来存储数据
DataTable dataTable = new DataTable("MyData");
// 添加列到 DataTable
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Age", typeof(int));
// 向 DataTable 中添加数据行
dataTable.Rows.Add("张三", 30);
dataTable.Rows.Add("李四", 25);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
// 使用 EPPlus 组件导出 Excel 文件
using (ExcelPackage excelPackage = new ExcelPackage())
{
ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
int row = 1;
foreach (DataRow dataRow in dataTable.Rows)
{
worksheet.Cells[row, 1].Value = dataRow["Name"].ToString();
worksheet.Cells[row, 2].Value = Convert.ToInt32(dataRow["Age"]);
row++;
}
// 将 Excel 文件保存到磁盘
string fileName = @"MyExcelFile.xlsx";
FileInfo fileInfo = new FileInfo(fileName);
excelPackage.SaveAs(fileInfo);
}
}
}
}
3.4 试验3 ClosedXML
using ClosedXML.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ExcelClosedXML
{
internal class Program
{
static void Main(string[] args)
{
Console.WriteLine("Excel试验-ClosedXML");
Program p = new Program();
p.main();
Console.ReadKey();
}
private void main()
{
test();
}
private void test()
{
// 创建一个 DataTable 对象来存储数据
DataTable dataTable = new DataTable("MyData");
// 添加列到 DataTable
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Age", typeof(int));
// 向 DataTable 中添加数据行
dataTable.Rows.Add("张三", 30);
dataTable.Rows.Add("李四", 25);
// 使用 ClosedXML 组件导出 Excel 文件
using (XLWorkbook workbook = new XLWorkbook())
{
IXLWorksheet worksheet = workbook.AddWorksheet("MySheet");
int row = 1;
foreach (DataRow dataRow in dataTable.Rows)
{
worksheet.Cell(row, 1).Value = dataRow["Name"].ToString();
worksheet.Cell(row, 2).Value = Convert.ToInt32(dataRow["Age"]);
// 设置单元格样式
worksheet.Cell(row, 2).Style.Font.Bold = true;
worksheet.Cell(row, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right;
row++;
}
// 将 Excel 文件保存到磁盘
string fileName = @"MyExcelFile.xlsx";
workbook.SaveAs(fileName);
}
}
}
}