PM> Install-Package Spire.XLS

using Spire.Xls;

namespace CopyRows
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("ContactList.xlsx");

            // Get the source worksheet
            Worksheet sheet1 = workbook.Worksheets[0];

            // Get the destination worksheet
            Worksheet sheet2 = workbook.Worksheets[1];

            // Get the desired row that you want to copy
            CellRange row = sheet1.Rows[0];

            // Copy the row from the source worksheet to the first row of the destination worksheet
            sheet1.CopyRow(row, sheet2, 1, CopyRangeOptions.All);

            int columns = sheet1.Columns.Length;

            // Copy the column widths of the cells in the source row to the corresponding cells in the destination row
            for (int i = 0; i < columns; i++)
            {
                double columnWidth = row.Columns[i].ColumnWidth;
                sheet2.Rows[0].Columns[i].ColumnWidth = columnWidth;
            }

            // Save the workbook to a file
            workbook.SaveToFile("CopyRow.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

using Spire.Xls;

namespace CopyColumns
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("ContactList.xlsx");

            // Get the source worksheet
            Worksheet sheet1 = workbook.Worksheets[0];

            // Get the destination worksheet
            Worksheet sheet2 = workbook.Worksheets[1];

            // Get the desired column that you want to copy
            CellRange column = sheet1.Columns[0];

            // Copy the column from the source worksheet to the first column of the destination worksheet
            sheet1.CopyColumn(column, sheet2, 1, CopyRangeOptions.All);

            int rows = column.Rows.Length;

            // Copy the row heights of cells in the source column to the corresponding cells in the destination column
            for (int i = 0; i < rows; i++)
            {
                double rowHeight = column.Rows[i].RowHeight;
                sheet2.Columns[0].Rows[i].RowHeight = rowHeight;
            }

            // Save the workbook to a file
            workbook.SaveToFile("CopyColumn.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

using Spire.Xls;

namespace CopyCells
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("ContactList.xlsx");

            // Get the source worksheet
            Worksheet sheet1 = workbook.Worksheets[0];
            // Get the destination worksheet
            Worksheet sheet2 = workbook.Worksheets[1];

            // Get the source cell range
            CellRange range1 = sheet1.Range["A1:E7"];
            // Get the destination cell range
            CellRange range2 = sheet2.Range["A1:E7"];

            // Copy the source cell range from the source worksheet to the destination cell range in the destination worksheet
            range1.Copy(range2, CopyRangeOptions.All);

            // Copy the row heights and column widths of the source cell range to the destination cell range
            for (int i = 0; i < range1.Rows.Length; i++)
            {
                CellRange row = range1.Rows[i];
                for (int j = 0; j < row.Columns.Length; j++)
                {
                    CellRange column = row.Columns[j];
                    range2.Rows[i].Columns[j].ColumnWidth = column.ColumnWidth;
                    range2.Rows[i].RowHeight = row.RowHeight;
                }
            }

            // Save the workbook to a file
            workbook.SaveToFile("CopyCells.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

PM> Install-Package Spire.XLS

<person>
    <name>John Doe</name>
    <age>30</age>
</person>

<person id="1">
    <name>John Doe</name>
    <age>30</age>
</person>

using System;
using System.Xml.Linq;

class Program
{
    static void Main()
    {
        // Load the XML file
        XDocument doc = XDocument.Load("data.xml");
        XElement root = doc.Root;

        // Iterate through elements
        foreach (XElement person in root.Elements("person"))
        {
            string name = person.Element("name")?.Value;
            string age = person.Element("age")?.Value;

            // Output the name and age
            Console.WriteLine($"Name: {name}, Age: {age}");
        }
    }
}

using Spire.Xls;
using System.Xml.Linq;

namespace ConvertXmlToExcel
{
    class Program
    {
        static void Main(string[] args)
        {

            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Remove default worksheets
            workbook.Worksheets.Clear();

            // Add a worksheet and name it
            Worksheet worksheet = workbook.Worksheets.Add("Books");

            // Load an XML file
            XDocument xmlDoc = XDocument.Load(@"C:\Users\Administrator\Desktop\Books.xml");
            XElement xmlRoot = xmlDoc.Root;

            // Get the first "book" element
            XElement firstBook = xmlRoot.Element("book");

            // Extract header information and convert it into a list
            var headers = firstBook.Elements().ToList();

            // Write header to Excel
            for (int colIndex = 0; colIndex < headers.Count; colIndex++)
            {
                string headerText = headers[colIndex].Name.LocalName;
                worksheet.SetValue(1, colIndex + 1, headerText);
            }

            // Write other data to Excel by iterating over each book element and each data node within it
            int rowIndex = 2;
            foreach (XElement book in xmlRoot.Elements("book"))
            {
                var dataNodes = book.Elements().ToList();
                for (int colIndex = 0; colIndex < dataNodes.Count; colIndex++)
                {
                    string value = dataNodes[colIndex].Value;
                    worksheet.SetValue(rowIndex, colIndex + 1, value);
                }
                rowIndex++;
            }

            // Set column width
            worksheet.AllocatedRange.AutoFitColumns();

            // Set alignment
            worksheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Left;

            // Set font style
            worksheet.Range["A1:F1"].Style.Font.IsBold = true;

            // Save the workbook to an Excel file
            workbook.SaveToFile("output/XmlToExcel.xlsx");

            // Dispose resources
            workbook.Dispose();
        }
    }
}

using Spire.Xls;
using Spire.Xls.Core;
using System.Xml.Linq;

namespace ConvertXmlToPdf
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();

            // Remove default worksheets
            workbook.Worksheets.Clear();

            // Add a worksheet and name it
            Worksheet worksheet = workbook.Worksheets.Add("Books");

            // Load an XML file
            XDocument xmlDoc = XDocument.Load(@"C:\Users\Administrator\Desktop\Books.xml");
            XElement xmlRoot = xmlDoc.Root;

            // Get the first "book" element
            XElement firstBook = xmlRoot.Element("book");

            // Extract header information and convert it into a list
            var headers = firstBook.Elements().ToList();

            // Write header to Excel
            for (int colIndex = 0; colIndex < headers.Count; colIndex++)
            {
                string headerText = headers[colIndex].Name.LocalName;
                worksheet.SetValue(1, colIndex + 1, headerText);
            }

            // Write other data to Excel by iterating over each book element and each data node within it
            int rowIndex = 2;
            foreach (XElement book in xmlRoot.Elements("book"))
            {
                var dataNodes = book.Elements().ToList();
                for (int colIndex = 0; colIndex < dataNodes.Count; colIndex++)
                {
                    string value = dataNodes[colIndex].Value;
                    worksheet.SetValue(rowIndex, colIndex + 1, value);
                }
                rowIndex++;
            }

            // Set column width
            worksheet.AllocatedRange.AutoFitColumns();

            // Set alignment
            worksheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Left;

            // Set font style
            worksheet.Range["A1:F1"].Style.Font.IsBold = true;

            // Fit worksheet on one page
            workbook.ConverterSetting.SheetFitToPage = true;

            // Get the PageSetup object
            PageSetup pageSetup = worksheet.PageSetup;

            // Set page margins
            pageSetup.TopMargin = 0.3;
            pageSetup.BottomMargin = 0.3;
            pageSetup.LeftMargin = 0.3;
            pageSetup.RightMargin = 0.3;

            // Preserve gridlines 
            pageSetup.IsPrintGridlines = true;

            // Save the worksheet to a PDF file
            worksheet.SaveToPdf("output/XmlToPdf.pdf");

            // Dispose resources
            workbook.Dispose();
        }
    }
}

PM> Install-Package Spire.XLS

using System.Data.SQLite;
using Spire.Xls;

namespace ExcelToSQLite
{
    class Program
    {
        static void Main(string[] args)
        {
            // Excel file path
            string excelFilePath = "Sample.xlsx";

            // SQLite database path
            string sqliteFilePath = "output/Database.db";

            // Open the Excel file
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(excelFilePath);

            // If the database file doesn't exist, create it
            if (!File.Exists(sqliteFilePath))
            {
                SQLiteConnection.CreateFile(sqliteFilePath);
                Console.WriteLine("A new SQLite database file has been created: output.db");
            }

            // Create SQLite connection
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source={sqliteFilePath};Version=3;"))
            {
                connection.Open();

                // Iterate through each worksheet
                foreach (Worksheet sheet in workbook.Worksheets)
                {
                    string tableName = sheet.Name;

                    // Get the first row as column names
                    var columns = sheet.Rows[0].CellList;
                    string createTableQuery = $"CREATE TABLE IF NOT EXISTS [{tableName}] (";

                    foreach (var column in columns)
                    {
                        createTableQuery += $"[{column.Value}] TEXT,";
                    }
                    createTableQuery = createTableQuery.TrimEnd(',') + ");";

                    // Create table
                    using (SQLiteCommand createTableCommand = new SQLiteCommand(createTableQuery, connection))
                    {
                        createTableCommand.ExecuteNonQuery();
                    }

                    // Insert data
                    for (int i = 1; i < sheet.Rows.Length; i++) // Skip the first row
                    {
                        var row = sheet.Rows[i];
                        string insertQuery = $"INSERT INTO [{tableName}] VALUES (";
                        foreach (var cell in row.CellList)
                        {
                            insertQuery += $"'{cell.Value?.Replace("'", "''")}',"; // Prevent SQL injection
                        }
                        insertQuery = insertQuery.TrimEnd(',') + ");";

                        using (SQLiteCommand insertCommand = new SQLiteCommand(insertQuery, connection))
                        {
                            insertCommand.ExecuteNonQuery();
                        }
                    }
                }

                connection.Close();
                workbook.Dispose();
            }

            Console.WriteLine("Excel data has been successfully written to the new SQLite database!");
        }
    }
}

using System.Data;
using System.Data.SQLite;
using Spire.Xls;

namespace SQLiteToExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            // SQLite database path
            string sqliteFilePath = "Sample.db";

            // Excel file path
            string excelFilePath = "output/DatabaseToExcel.xlsx";

            // Create a new Workbook instance
            Workbook workbook = new Workbook();
            // Clear the default worksheet
            workbook.Worksheets.Clear();

            // Create SQLite connection
            using (SQLiteConnection connection = new SQLiteConnection($"Data Source={sqliteFilePath};Version=3;"))
            {
                connection.Open();

                // Get all table names
                DataTable tables = connection.GetSchema("Tables");

                // Iterate through each table
                foreach (DataRow tableRow in tables.Rows)
                {
                    string tableName = tableRow["TABLE_NAME"].ToString();

                    // Create a new worksheet
                    Worksheet sheet = workbook.Worksheets.Add(tableName);

                    // Get table data
                    string selectQuery = $"SELECT * FROM [{tableName}]";
                    using (SQLiteCommand command = new SQLiteCommand(selectQuery, connection))
                    {
                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            // Get column names and write them in the first row
                            for (int col = 0; col < reader.FieldCount; col++)
                            {
                                sheet.Range[1, col + 1].Value = reader.GetName(col);
                            }
                            // Set the font style for the header
                            sheet.Rows[0].Style.Font.IsBold = true;
                            sheet.Rows[0].Style.Font.Size = 12;

                            // Write data rows
                            int rowIndex = 2;
                            while (reader.Read())
                            {
                                for (int col = 0; col < reader.FieldCount; col++)
                                {
                                    sheet.Range[rowIndex, col + 1].Value = reader.GetValue(col).ToString();
                                    // Auto-fit column width
                                    sheet.AutoFitColumn(col + 1);
                                }
                                // Set the font style for data rows
                                sheet.Rows[rowIndex - 1].Style.Font.Size = 11;
                                rowIndex++;
                            }
                        }
                    }
                }

                connection.Close();
            }

            // Save the Excel file
            workbook.SaveToFile(excelFilePath);
            workbook.Dispose();
            Console.WriteLine("Data has been successfully exported to the Excel file!");
        }
    }
}

PM> Install-Package Spire.XLS

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;
using System;

namespace GroupDataInPivotTableByDates
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create an instance of the Workbook class
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("Sample1.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            // Get the first pivot table in the worksheet
            XlsPivotTable pt = sheet.PivotTables[0] as XlsPivotTable;

            // Get the "Date" pivot field 
            PivotField ptField = pt.PivotFields["Date"] as PivotField;
            // Specify the start date time and end date time
            DateTime start = new DateTime(2024, 1, 1);
            DateTime end = new DateTime(2024, 10, 14);

            // Create a PivotGroupByTypes array to specify the grouping categories, such as days and months
            PivotGroupByTypes[] groupByTypes = new PivotGroupByTypes[]
            {
                PivotGroupByTypes.Days,
                PivotGroupByTypes.Months
            };

            // Group the data in the pivot field based on the specified grouping categories
            ptField.CreateGroup(start, end, groupByTypes);

            // Refresh the pivot table
            pt.Cache.IsRefreshOnLoad = true;

            // Save the result file
            workbook.SaveToFile("GroupPivotTableDataByDates.xlsx", FileFormat.Version2016);
            workbook.Dispose();
        }
    }
}

using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.PivotTables;

namespace GroupDataInPivotTableByNumbers
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create an instance of the Workbook class
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("Sample2.xlsx");

            // Get the first worksheet
            Worksheet pivotSheet = workbook.Worksheets[0];

            // Get the first pivot table in the worksheet
            XlsPivotTable pt = pivotSheet.PivotTables[0] as XlsPivotTable;

            // Group data of the "SalesAmount" pivot field based on based on numeric values
            PivotField ptField = pt.PivotFields["SalesAmount"] as PivotField;
            ptField.CreateGroup(1500, 4500, 200);

            // Calculate the pivot table data
            pt.CalculateData();
            // Refresh the pivot table
            pt.Cache.IsRefreshOnLoad = true;

            // Save the result file
            workbook.SaveToFile("GroupPivotTableDataByNumbers.xlsx", FileFormat.Version2016);
            workbook.Dispose();
        }
    }
}