Converting this Office,Interop code to Aspose.Cells code

Aspose.Cells Product Family
1

Can someone please help me to convert this typically Microst excel code over to do the same thing with Aspose.cells?

What is does: Opens a workbook, looks at sheet called “2024”, goes thru every row checking column B for a specific date…if it finds that date,then it copies that entire row entire another sheet called “Copied”…then the file looks at all rows in “Copied” and enters them into a database…any help would be appreciated as Aspose.Cells is completely new to me. Thank you.


        Dim xlApp As New Microsoft.Office.Interop.Excel.Application
        Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim xlWorkSheet, xlWorksheet2 As Microsoft.Office.Interop.Excel.Worksheet
        Try

            Dim SavePath As String = System.Windows.Forms.Application.StartupPath & "\temp\myfile.xlsx"
            If File.Exists(SavePath) Then
                File.Delete(SavePath)
            End If
            If Not File.Exists(SavePath) Then
                File.Copy("C:\Users\PC\EXCEL SHEETS\EXCEL SHEETS DONT ADD ANYTHING EXCEPT EXCEL FILES\MyFile.xlsx", SavePath, True)
            End If

            If File.Exists(SavePath) Then


                xlWorkBook = xlApp.Workbooks.Open(SavePath)
                'MsgBox("File opened.")
                xlWorkSheet = xlWorkBook.Worksheets("2024")
                xlWorksheet2 = xlWorkBook.Worksheets("Copied")

     

                'Dim dte As Date = Date.Now.AddDays(-1).ToShortDateString
                form.dtp.DateTimePattern = Syncfusion.WinForms.Input.Enums.DateTimePattern.ShortDate
                Dim dte As Date = form.dtp.Value

                'MsgBox(dte.ToString)
                Dim LastRow As Long
                Dim i As Long, j As Long
                Dim count As Integer = 0

                'Find the last used row in a Column: column B in this example
                With xlWorkSheet
                    LastRow = .Cells(.Rows.Count, "B").End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row

                End With

                'MsgBox(LastRow)
                'first row number where you need to paste values in Sheet1
                With xlWorksheet2
                    j = .Cells(.Rows.Count, "A").End(Microsoft.Office.Interop.Excel.XlDirection.xlUp).Row
                End With

                'With xlWorkSheet
                '    For i = 1 To LastRow


                '        If .Cells(i, 2).ToString <> dte.ToShortDateString() Then
                '            MsgBox("No test records found for this date.", MsgBoxStyle.Exclamation, "No Records Found!")
                '            Exit Sub
                '        End If

                '    Next
                'End With

                With xlWorkSheet
                    For i = 1 To LastRow


                        If .Cells(i, 2).text = dte.ToShortDateString() Then
                            'MsgBox("Records found")
                       end if
2

@zcast69

To convert your Microsoft Office Interop code to Aspose.Cells, you can follow the example below. This code opens a workbook, checks for a specific date in the “2024” sheet, and copies the entire row to another sheet called “Copied” if the date matches.

Here’s how you can implement this using Aspose.Cells:

using Aspose.Cells;
using System;
using System.IO;

class Program
{
    static void Main()
    {
        // Define the path to the Excel file
        string savePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "temp", "myfile.xlsx");

        // Check if the file exists, if not, copy it from the source
        if (!File.Exists(savePath))
        {
            File.Copy(@"C:\Users\PC\EXCEL SHEETS\EXCEL SHEETS DONT ADD ANYTHING EXCEPT EXCEL FILES\MyFile.xlsx", savePath, true);
        }

        // Load the workbook
        Workbook workbook = new Workbook(savePath);
        
        // Access the "2024" worksheet
        Worksheet sourceSheet = workbook.Worksheets["2024"];
        // Access the "Copied" worksheet
        Worksheet destSheet = workbook.Worksheets["Copied"];

        // Define the date to search for
        DateTime searchDate = DateTime.Now.AddDays(-1); // Example: yesterday's date

        // Get the last row in the source sheet
        int lastRow = sourceSheet.Cells.MaxDataRow + 1; // MaxDataRow is zero-based

        // Initialize a counter for the destination sheet
        int destRow = destSheet.Cells.MaxDataRow + 1;

        // Loop through each row in the source sheet
        for (int i = 0; i < lastRow; i++)
        {
            // Check if the date in column B (index 1) matches the search date
            if (sourceSheet.Cells[i, 1].Value != null && 
                DateTime.TryParse(sourceSheet.Cells[i, 1].Value.ToString(), out DateTime cellDate) && 
                cellDate.Date == searchDate.Date)
            {
                // Copy the entire row to the destination sheet
                for (int j = 0; j < sourceSheet.Cells.MaxDataColumn + 1; j++)
                {
                    destSheet.Cells[destRow, j].Value = sourceSheet.Cells[i, j].Value;
                }
                destRow++; // Move to the next row in the destination sheet
            }
        }

        // Save the modified workbook
        workbook.Save(savePath);
    }
}

Explanation:

  1. File Handling: The code checks if the specified Excel file exists and copies it if it doesn’t.
  2. Workbook and Worksheet Access: It loads the workbook and accesses the relevant worksheets.
  3. Date Comparison: It checks each row in the “2024” sheet for a specific date in column B.
  4. Row Copying: If a match is found, it copies the entire row to the “Copied” sheet.
  5. Saving the Workbook: Finally, it saves the modified workbook.

This should help you achieve the same functionality as your original Interop code using Aspose.Cells. If you have any further questions or need additional modifications, feel free to ask!

3

@zcast69,

To insert final data to some data source (e.g., database), you my try to use Cells.ExportDataTable() or Cells.ExportDataTableAsString() method to export the data in the “Copied” worksheet to fill a datatable. And, later you may insert/update your database table(s) using ADO.NET components by yourselves.