【.Net 6.0--通用帮助类--ExcelHelper】

返回首页

前言

Excel帮助类，无需安装office即可使用，包含了读取excel到datatable（ExcelToDataTable)、保存datatable到excel（DataTableToExcel）、设置单元格枚举值（SetCellList）、合并单元格（SetCellMerge）等方法。

需要引用Aspose.Cells，依赖文件，我已共享资源，请需要的同学自行下载。

---

代码示例

using Aspose.Cells;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace VW.API.Common.Utils
{
    /// <summary>
    /// 合并单元格实体类
    /// </summary>
    public class CellMergeModel
    {
        /// <summary>
        /// 开始行
        /// </summary>
        public int FirstRow { set; get; }
        /// <summary>
        /// 开始列
        /// </summary>
        public int FirstColumn { set; get; }
        /// <summary>
        /// 合并行数
        /// </summary>
        public int TotalRows { set; get; }
        /// <summary>
        /// 合并列数
        /// </summary>
        public int TotalColumns { set; get; }
    }

    /// <summary>
    /// 单元格下拉实体类
    /// </summary>
    public class CellListModel
    {
        /// <summary>
        /// 开始行
        /// </summary>
        public int StartRow { set; get; }
        /// <summary>
        /// 开始列
        /// </summary>
        public int StartColumn { set; get; }
        /// <summary>
        /// 结束行
        /// </summary>
        public int EndRow { set; get; }
        /// <summary>
        /// 结束列
        /// </summary>
        public int EndColumn { set; get; }
        /// <summary>
        /// list
        /// </summary>
        public string Formula1 { set; get; }
    }

    /// <summary>
    /// ExcelHelper 的摘要说明：Excel帮助类
    /// </summary>
    public static class ExcelHelper
    {
        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="mergeModels">合并单元格参数</param>
        /// <param name="sheetIndex">sheet序号</param>
        /// <returns></returns>
        public static bool SetCellMerge(string filePath, List<CellMergeModel> mergeModels, int sheetIndex = 0)
        {
            try
            {
                Workbook book = new Workbook(filePath);
                Cells cells = book.Worksheets[sheetIndex].Cells;

                foreach (CellMergeModel mergeModel in mergeModels)
                {
                    cells.Merge(mergeModel.FirstRow, mergeModel.FirstColumn, mergeModel.TotalRows, mergeModel.TotalColumns);
                }

                book.Save(filePath);

                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// List单元格
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="cellListModels">List单元格参数</param>
        /// <param name="sheetIndex">sheet序号</param>
        /// <returns></returns>
        public static bool SetCellList(string filePath, List<CellListModel> cellListModels, int sheetIndex = 0)
        {
            try
            {
                Workbook workbook = new Workbook(filePath);
                Worksheet worksheet = workbook.Worksheets[sheetIndex];

                ValidationCollection validations = worksheet.Validations;
                foreach (CellListModel cellListModel in cellListModels)
                {
                    CellArea area = new CellArea
                    {
                        StartRow = cellListModel.StartRow,
                        EndRow = cellListModel.EndRow,
                        StartColumn = cellListModel.StartColumn,
                        EndColumn = cellListModel.EndColumn
                    };

                    Validation validation = validations[validations.Add(area)];
                    validation.Type = ValidationType.List;
                    validation.Operator = OperatorType.None;
                    validation.InCellDropDown = true;
                    validation.Formula1 = cellListModel.Formula1;
                    validation.ShowError = true;
                    validation.AlertStyle = ValidationAlertType.Stop;
                    validation.ErrorTitle = "错误";
                    validation.ErrorMessage = "请从列表中选择一项";
                }

                workbook.Save(filePath);

                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        private static DataTable ExcelToDataTable(Workbook workbook,
            int sheet = 0,
            int firstRow = 0,
            int firstColumn = 0,
            bool showTitle = true)
        {
            Cells cells = workbook.Worksheets[sheet].Cells;
            //excel->datatable
            DataTable dataTable = cells.ExportDataTableAsString(firstRow, firstColumn, cells.MaxDataRow + 1, cells.MaxColumn + 1, showTitle);

            //获取合并单元格
            ArrayList arrayLists = cells.MergedCells;
            foreach (CellArea item in arrayLists)
            {
                if (item.StartRow < firstRow)
                    continue;
                for (int i = item.StartRow - firstRow; i <= item.EndRow - firstRow; i++)
                {
                    for (int j = item.StartColumn; j <= item.EndColumn; j++)
                    {
                        if (item.StartColumn == item.EndColumn)//合并行
                            dataTable.Rows[i][j] = dataTable.Rows[item.StartRow - firstRow][item.StartColumn];
                    }
                }
            }

            return dataTable;
        }

        /// <summary>
        /// excel-datatable
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetIndex">sheet序号</param>
        /// <param name="firstRow">开始行</param>
        /// <param name="firstColumn">开始列</param>
        /// <param name="showTitle">是否显示列名（如果多维表头，设置为false，否则列名显示有问题，无法绑定到dataview显示）</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string filePath,
            int sheetIndex = 0,
            int firstRow = 0,
            int firstColumn = 0,
            bool showTitle = true)
        {
            try
            {
                Workbook workbook = new Workbook(filePath);

                return ExcelToDataTable(workbook, sheetIndex, firstRow, firstColumn, showTitle);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// excel-datatable
        /// </summary>
        /// <param name="stream">文件Steam</param>
        /// <param name="sheetIndex">sheet序号</param>
        /// <param name="firstRow">开始行</param>
        /// <param name="firstColumn">开始列</param>
        /// <param name="showTitle">是否显示列名</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(Stream stream,
            int sheetIndex = 0,
            int firstRow = 0,
            int firstColumn = 0,
            bool showTitle = true)
        {
            try
            {
                Workbook workbook = new Workbook(stream);

                return ExcelToDataTable(workbook, sheetIndex, firstRow, firstColumn, showTitle);
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// datatable->excel（普通导出）
        /// </summary>
        /// <param name="dtData">导出数据</param>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public static bool DataTableToExcel(DataTable dtData, string filePath, int sheetIndex = 0, string sheetName = "默认sheet")
        {
            try
            {
                Workbook book = sheetIndex != 0 ? new Workbook(filePath) : new Workbook();
                Worksheet sheet = sheetIndex != 0 && book.Worksheets.Count <= sheetIndex ? book.Worksheets.Add(sheetName) : book.Worksheets[sheetIndex];
                sheet.Cells.Clear();
                Cells cells = sheet.Cells;
                int Colnum = dtData.Columns.Count; // 表格列数 
                int Rownum = dtData.Rows.Count; // 表格行数 
                // 生成行 列名行 
                for (int i = 0; i < Colnum; i++)
                {
                    cells[0, i].PutValue(dtData.Columns[i].ColumnName); // 添加表头
                }
                // 生成数据行 
                for (int i = 0; i < Rownum; i++)
                {
                    for (int k = 0; k < Colnum; k++)
                    {
                        cells[1 + i, k].PutValue(dtData.Rows[i][k].ToString()); // 添加数据
                    }
                }
                sheet.AutoFitColumns(); // 自适应宽
                book.Save(filePath);

                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// datatable->excel（模板导出）
        /// </summary>
        /// <param name="dtData">导出数据</param>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetIndex">sheet序号</param>
        /// <param name="excelStartRow">excel开始行</param>
        /// <param name="dataTableStartRow">datatable开始行</param>
        /// <param name="dataTableEndRow">datatable结束行</param>
        /// <returns></returns>
        public static bool DataTableToExcelByTemplate(DataTable dtData,
            string filePath,
            int sheetIndex = 0,
            int excelStartRow = 0,
            int dataTableStartRow = 0,
            int dataTableEndRow = 0)
        {
            try
            {
                Workbook book = new Workbook(filePath);
                Cells cells = book.Worksheets[sheetIndex].Cells;

                for (int i = dataTableStartRow; i <= dataTableEndRow; i++)
                {
                    //插入行
                    cells.InsertRow(excelStartRow + i);

                    for (int k = 0; k < dtData.Columns.Count; k++)
                    {
                        cells[excelStartRow + i, k].PutValue(dtData.Rows[i][k].ToString());
                        //取开始行的单元格样式，并且赋值给当前单元格
                        cells[excelStartRow + i, k].SetStyle(cells[excelStartRow - 1, k].GetStyle());
                    }
                }

                book.Save(filePath);

                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}