Kepler-Release/Venus/Framework/Common/Utilities/ExcelHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aitex.Core.RT.Log;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace MECF.Framework.Common.Utilities
{
    public class ExcelHelper
    {
        public static bool ExportToExcel(string filepath, DataSet ds, out string reason)
        {
            reason = string.Empty;

            try
            {
                SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                workbookpart.Workbook = new Workbook();
                WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData());
                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    Sheet sheet = new Sheet()
                    {
                        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name = ds.Tables[i].TableName,
                    };

                    sheets.Append(sheet);

                    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                    for (int row = 0; row < ds.Tables[i].Rows.Count; row++)
                    {
                        Row rowData = new Row();

                        for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
                        {
                            if (row == 0)
                            {
                                Cell titleCell = new Cell();
                                titleCell.CellValue = new CellValue(ds.Tables[i].Columns[col].Caption);
                                titleCell.DataType = new EnumValue<CellValues>(CellValues.String);
                                rowData.Append(titleCell);
                                continue;
                            }

                            Cell dataCell = new Cell();

                            var data = ds.Tables[i].Rows[row][col];

                            if (data is DateTime time)
                            {
                                dataCell.CellValue = new CellValue(time);
                                dataCell.DataType = new EnumValue<CellValues>(CellValues.String);
                            }
                            else if (data is double value)
                            {
                                dataCell.CellValue = new CellValue(data.ToString());
                                dataCell.DataType = new EnumValue<CellValues>(CellValues.Number);
                            }
                            else
                            {
                                dataCell.CellValue = new CellValue(data.ToString());
                                dataCell.DataType = new EnumValue<CellValues>(CellValues.String);
                            }

                            rowData.Append(dataCell);
                        }
                        sheetData.Append(rowData);
                    }
                }
                workbookpart.Workbook.Save();
                spreadsheetDocument.Close();
            }
            catch (Exception ex)
            {
                LOG.WriteExeption(ex);
                reason = ex.Message;
                return false;
            }

            return true;

        }

    }
}