ELK/FrameworkLocal/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, bool createNewFile = true, bool isNeedNumeric = true)
        {
            reason = string.Empty;

            try
            {
                SpreadsheetDocument spreadsheetDocument;
                WorkbookPart workbookpart;
                WorksheetPart worksheetPart;
                Sheets sheets;
                if (createNewFile)
                {
                    spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                    workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                }
                else
                {
                    spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);
                    workbookpart = spreadsheetDocument.WorkbookPart;
                    sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
                }
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());
                    sheets = workbookpart.Workbook.GetFirstChild<Sheets>();
                    uint sheetID = 1;
                    if (sheets.Descendants<Sheet>().Count() > 0)
                    {
                        sheetID = sheets.Descendants<Sheet>().Select(S => S.SheetId.Value).Max() + 1;
                    }
                    Sheet sheet = new Sheet()
                    {
                        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = sheetID,
                        Name = ds.Tables[i].TableName,
                    };

                    sheets.Append(sheet);

                    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                    Row rowCaption = new Row();
                    for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
                    {
                        Cell titleCell = new Cell();
                        titleCell.CellValue = new CellValue(ds.Tables[i].Columns[col].Caption);
                        titleCell.DataType = new EnumValue<CellValues>(CellValues.String);
                        rowCaption.Append(titleCell);
                    }
                    sheetData.Append(rowCaption);

                    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++)
                        {
                            Cell dataCell = new Cell();

                            var data = ds.Tables[i].Rows[row][col];
                            bool isNumeric = false;
                            if (isNeedNumeric)
                            {
                                if (data != null)
                                {
                                    if (double.TryParse(data.ToString(), out double doubleValue))
                                    {
                                        // 转换为 double 类型成功，表示为数字
                                        isNumeric = true;
                                    }
                                    else if (int.TryParse(data.ToString(), out int intValue))
                                    {
                                        isNumeric = true;
                                    }
                                    else if (float.TryParse(data.ToString(), out float floatValue))
                                    {
                                        isNumeric = true;
                                    }
                                }
                            }

                            if (isNumeric)
                            {
                                // 处理数字类型的值
                                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);
                            }


                            //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.String);
                            //}
                            //else if (data is int intValue)
                            //{
                            //	dataCell.CellValue = new CellValue(intValue.ToString());
                            //	dataCell.DataType = new EnumValue<CellValues>(CellValues.Number);
                            //}
                            //else if (data is float floatValue)
                            //{
                            //	dataCell.CellValue = new CellValue(floatValue.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.Write(ex);
                reason = ex.Message;
                return false;
            }

            return true;

        }
        public static bool ExportProcessLogToExcel(string filepath, DataSet ds, out string reason, bool createNewFile = true)
        {
            reason = string.Empty;

            try
            {
                SpreadsheetDocument spreadsheetDocument;
                WorkbookPart workbookpart;
                WorksheetPart worksheetPart;
                Sheets sheets;
                if (createNewFile)
                {
                    spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                    workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                }
                else
                {
                    spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);
                    workbookpart = spreadsheetDocument.WorkbookPart;
                    workbookpart.Workbook = new Workbook();
                    sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
                }
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());
                    sheets = workbookpart.Workbook.GetFirstChild<Sheets>();
                    uint sheetID = 1;
                    if (sheets.Descendants<Sheet>().Count() > 0)
                    {
                        sheetID = sheets.Descendants<Sheet>().Select(S => S.SheetId.Value).Max() + 1;
                    }
                    Sheet sheet = new Sheet()
                    {
                        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = sheetID,
                        Name = ds.Tables[i].TableName,
                    };

                    sheets.Append(sheet);

                    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                    Row rowCaption = new Row();
                    for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
                    {
                        Cell titleCell = new Cell();
                        titleCell.CellValue = new CellValue(ds.Tables[i].Columns[col].Caption);
                        titleCell.DataType = new EnumValue<CellValues>(CellValues.String);
                        rowCaption.Append(titleCell);
                    }
                    sheetData.Append(rowCaption);
                    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++)
                        {
                            Cell dataCell = new Cell();

                            var data = ds.Tables[i].Rows[row][col];
                            bool isNumeric = false;
                            if (data != null)
                            {
                                if (double.TryParse(data.ToString(), out double doubleValue))
                                {
                                    // 转换为 double 类型成功，表示为数字
                                    isNumeric = true;
                                }
                                else if (int.TryParse(data.ToString(), out int intValue))
                                {
                                    isNumeric = true;
                                }
                                else if (float.TryParse(data.ToString(), out float floatValue))
                                {
                                    isNumeric = true;
                                }
                            }
                            dataCell.CellValue = new CellValue(data.ToString());
                            // 处理数字类型的值 // 处理非数字类型的值
                            dataCell.DataType = isNumeric ? new EnumValue<CellValues>(CellValues.Number) : new EnumValue<CellValues>(CellValues.String);
                            rowData.Append(dataCell);
                        }
                        sheetData.Append(rowData);
                    }
                }
                workbookpart.Workbook.Save();
                spreadsheetDocument.Close();
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                reason = ex.Message;
                return false;
            }

            return true;

        }

        public static bool ExportProcessLogToExcel(string filepath, DataSet ds, int fristRow, int endRow, out string reason, bool createNewFile = true)
        {
            reason = string.Empty;
            try
            {
                SpreadsheetDocument spreadsheetDocument;
                WorkbookPart workbookpart;
                WorksheetPart worksheetPart;
                Sheets sheets;
                if (createNewFile)
                {
                    spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
                    workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                }
                else
                {
                    spreadsheetDocument = SpreadsheetDocument.Open(filepath, true);
                    workbookpart = spreadsheetDocument.WorkbookPart;
                    workbookpart.Workbook = new Workbook();
                    sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
                }
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());
                    sheets = workbookpart.Workbook.GetFirstChild<Sheets>();
                    uint sheetID = 1;
                    if (sheets.Descendants<Sheet>().Count() > 0)
                    {
                        sheetID = sheets.Descendants<Sheet>().Select(S => S.SheetId.Value).Max() + 1;
                    }
                    Sheet sheet = new Sheet()
                    {
                        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = sheetID,
                        Name = ds.Tables[i].TableName,
                    };

                    sheets.Append(sheet);

                    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                    Row rowCaption = new Row();
                    for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
                    {
                        Cell titleCell = new Cell();
                        titleCell.CellValue = new CellValue(ds.Tables[i].Columns[col].Caption);
                        titleCell.DataType = new EnumValue<CellValues>(CellValues.String);
                        rowCaption.Append(titleCell);
                    }
                    sheetData.Append(rowCaption);
                    for (int row = fristRow; row < endRow; row++)
                    {
                        Row rowData = new Row();
                        for (int col = 0; col < ds.Tables[i].Columns.Count; col++)
                        {
                            Cell dataCell = new Cell();

                            var data = ds.Tables[i].Rows[row][col];
                            bool isNumeric = false;
                            if (data != null)
                            {
                                if (double.TryParse(data.ToString(), out double doubleValue))
                                {
                                    // 转换为 double 类型成功，表示为数字
                                    isNumeric = true;
                                }
                                else if (int.TryParse(data.ToString(), out int intValue))
                                {
                                    isNumeric = true;
                                }
                                else if (float.TryParse(data.ToString(), out float floatValue))
                                {
                                    isNumeric = true;
                                }
                            }
                            dataCell.CellValue = new CellValue(data.ToString());
                            // 处理数字类型的值 // 处理非数字类型的值
                            dataCell.DataType = isNumeric ? new EnumValue<CellValues>(CellValues.Number) : new EnumValue<CellValues>(CellValues.String);
                            rowData.Append(dataCell);
                        }
                        sheetData.Append(rowData);
                    }
                }
                workbookpart.Workbook.Save();
                spreadsheetDocument.Close();
            }
            catch (Exception ex)
            {
                LOG.Write(ex);
                reason = ex.Message;
                return false;
            }

            return true;

        }



    }
}