C#语言：使用EPPlus库将DataTable内容输出到xlsx格式文件

最新推荐文章于 2024-07-25 14:05:46 发布

chenzai1946

最新推荐文章于 2024-07-25 14:05:46 发布

阅读量740

点赞数

文章标签： c#

原文链接：https://my.oschina.net/Tsybius2014/blog/809921

版权

先前工作中遇到一个棘手的问题。之前我写的客户端程序生成xlsx格式文件，使用的方法为在指定计算机上安装Office软件，并通过交互操作Interop的方式调用Office相关COM-API生成xlsx格式文档。但在服务器端（Server-side）Windows系统，对Office程序的自动化操作是不被鼓励和支持的，为防止可能出现的不稳定及死锁，客户端目前使用Interop生成xlsx格式文件的方式不被支持。

（参考MSDN上文章：https://support.microsoft.com/en-us/kb/257757）

为了解决这一问题，我在网上找了下可生成xlsx格式文件的三方类库，试用了下EPPlus。

EPPlus主页地址：http://epplus.codeplex.com

EPPlus下载地址：http://epplus.codeplex.com/releases/view/625020

我要实现的目标是将一个DataTable数据源的内容输出到xlsx格式文件中，为此我写了一个工具类XlsxHelper，可实现如下功能：

1、public static DataTable GenTestDataTable() 生成一个测试用的DataTable数据源

2、public static void SaveToXlsx(DataTable dataTable, string filePath) 将DataTable内容输出到文件

XlsxHelper代码如下：

using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;

namespace XlsxGenTest
{
    /// <summary>
    /// Xlsx操作工具类
    /// </summary>
    public class XlsxHelper
    {
        /// <summary>
        /// 生成测试用DataTable
        /// </summary>
        /// <returns></returns>
        public static DataTable GenTestDataTable()
        {
            DataTable dataTable = new DataTable("学生成绩单");

            dataTable.Columns.Add("学号"); dataTable.Columns.Add("姓名"); dataTable.Columns.Add("性别");
            dataTable.Columns.Add("语文"); dataTable.Columns.Add("数学"); dataTable.Columns.Add("英语");
            dataTable.Columns.Add("物理"); dataTable.Columns.Add("化学"); dataTable.Columns.Add("生物");
            dataTable.Columns.Add("历史"); dataTable.Columns.Add("地理"); dataTable.Columns.Add("政治");

            Random random = new Random(DateTime.Now.Millisecond);
            for (int i = 1; i <= 30; i++)
            {
                dataTable.Rows.Add("stu" + i, "name" + i, "M",
                    random.Next(60, 100), random.Next(60, 100), random.Next(60, 100),
                    random.Next(60, 100), random.Next(60, 100), random.Next(60, 100),
                    random.Next(60, 100), random.Next(60, 100), random.Next(60, 100));
            }

            return dataTable;
        }

        /// <summary>
        /// 将DataTable内容保存到Xlsx格式文件中
        /// </summary>
        /// <param name="dataTable">数据源</param>
        /// <param name="filePath">输出文件地址</param>
        public static void SaveToXlsx(DataTable dataTable, string filePath)
        {
            int rowCount = dataTable.Rows.Count;
            int colCount = dataTable.Columns.Count;

            int startRow = 4;
            int startCol = 2;

            using (ExcelPackage excelPackage = new ExcelPackage())
            {
                ExcelWorkbook excelWorkbook = excelPackage.Workbook;
                ExcelWorksheet currentWorksheet = excelWorkbook.Worksheets.Add(dataTable.TableName);

                //生成标题（第一行）、副标题（第二行）
                currentWorksheet.Cells[1, 1, 1, colCount + 1].Merge = true; //合并单元格
                currentWorksheet.Cells[1, 1].Value = dataTable.TableName; //表名
                currentWorksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中
                currentWorksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中
                currentWorksheet.Cells[1, 1].Style.Font.Name = "黑体"; //设置字体
                currentWorksheet.Cells[1, 1].Style.Font.Size = 25; //设置字号
                currentWorksheet.Cells[1, 1].Style.Font.Bold = true; //文字加粗
                currentWorksheet.Row(1).Height = 60; //设置单元格高度
                currentWorksheet.Cells[2, 1, 2, colCount + 1].Merge = true;
                currentWorksheet.Cells[2, 1].Value = string.Format("报表生成于：{0}", DateTime.Now.ToString());
                currentWorksheet.Cells[2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                currentWorksheet.Cells[2, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中
                currentWorksheet.Cells[2, 1].Style.Font.Name = "宋体";
                currentWorksheet.Cells[2, 1].Style.Font.Size = 15;
                currentWorksheet.Row(2).Height = 30;

                //生成标题行（第三行）
                currentWorksheet.Cells[3, 1].Value = "序号";
                currentWorksheet.Column(1).Width = 8.38; //设置列宽
                for (int i = 0; i < colCount; i++)
                {
                    currentWorksheet.Cells[3, startCol + i].Value = dataTable.Columns[i].ColumnName;
                    currentWorksheet.Column(startCol + i).Width = 20;
                }
                //调整标题行字体
                using (var range = currentWorksheet.Cells[3, 1, 3, startCol + colCount - 1])
                {
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    range.Style.Font.Name = "宋体";
                    range.Style.Font.Size = 15;
                    range.Style.Font.Bold = true;
                }

                //生成数据（第四行及以后）
                for (int i = 0; i < rowCount; i++)
                {
                    currentWorksheet.Cells[startRow + i, 1].Value = (i + 1).ToString();
                    for (int j = 0; j < colCount; j++)
                    {
                        currentWorksheet.Cells[startRow + i, startCol + j].Value = dataTable.Rows[i][j];
                    }
                }
                //序号列，靠右
                using (var range = currentWorksheet.Cells[startRow, 1, startRow + rowCount - 1, 1])
                {
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }
                //其他列，居中
                using (var range = currentWorksheet.Cells[startRow, startCol, startRow + rowCount - 1, startCol + colCount - 1])
                {
                    range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }
                //所有列，其他属性
                using (var range = currentWorksheet.Cells[startRow, 1, startRow + rowCount - 1, startCol + colCount - 1])
                {
                    range.Style.Font.Name = "宋体";
                    range.Style.Font.Size = 11;
                }

                //设置边框，控制台应用程序需要手工添加引用System.Drawing
                using (var range = currentWorksheet.Cells[1, 1, startRow + rowCount - 1, startCol + colCount - 1])
                {
                    range.Style.Border.Top.Style = ExcelBorderStyle.Medium;
                    range.Style.Border.Top.Color.SetColor(Color.Black);
                    range.Style.Border.Right.Style = ExcelBorderStyle.Medium;
                    range.Style.Border.Right.Color.SetColor(Color.Black);
                    range.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
                    range.Style.Border.Bottom.Color.SetColor(Color.Black);
                    range.Style.Border.Left.Style = ExcelBorderStyle.Medium;
                    range.Style.Border.Left.Color.SetColor(Color.Black);
                }

                //生成Xlsx文档
                excelPackage.SaveAs(new FileInfo(filePath));
            }
        }
    }
}

需要注意之处：

1、手动添加引用EPPlus.dll即可使用EPPlus相关类库，我使用的版本为EPPlus4.1

2、如建立的工程为Windows控制台应用程序，若要指定颜色，需手动添加引用System.Drawing

调用此工具类，Program.cs代码如下：

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace XlsxGenTest
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                //获取测试用DataTable数据源
                DataTable dataTable = XlsxHelper.GenTestDataTable();
                //将数据源内容保存到Xlsx格式文档中
                string fileName = "test.xlsx";
                if (File.Exists(fileName))
                {
                    File.Delete(fileName);
                }
                XlsxHelper.SaveToXlsx(dataTable, "test.xlsx");

                Console.WriteLine("生成完毕");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }

            Console.Read();
        }
    }
}

生成结果test.xlsx如下：