Automate Excel Operations: Your Business Needs It Right Away!

.NET developers face challenges in implementing data validation logic on excel as the process is time-consuming. The introduction of the module ‘Generic Excel Operation’ has helped us in resolving this major challenge of the developers as they can now perform multiple operations on Excel using a single click.

The Generic Excel Operation module performs insert, update, and delete operation simultaneously. The validation operation uses multithreading to make your program 10 times faster as compared to any other programming. More than 10000 records can be validated within one or two minutes if the correct data is uploaded in the database. In case of an error, errors are highlighted in the existing excel file with a yellow color and a tooltip is set at the header of the excel file to explain what type of data is valid for the field.

The module leverages technologies such as MVC 5 (Visual Studio 2013), SQL Server 2012, and Open XML (SpreadsheetLight). Users can perform any type of validation for “.xlsx” file; however, we are still in the process of creating a module for “.xls.”

With the implementation of this module, excel files can become more user-friendly as users do not have to use multiple modules for automating excel operations.

How to start using the module?

1. The table name in the database should start with the prefix upld_ + <<Table Name>>. The table can have additional columns in the following format:

    [Record_ID] [bigint] AutoIncrement,

   [CreatedDate] [datetime] default getdate() ,

   [ModifiedDate] [datetime],

   [Status] [bit]  DEFAULT ((1)),

   [CreatedBy] [nvarchar](50) NULL

2. Select the table from the drop down menu
3. Execute the SQL query given in app_data folder
4. Run the query in your database and add connections string in webconfig file with the name used in the application (DBConnection file)
5. Start validating data

   All validations such as data type/column length/any custom validation can be performed with the help of this module. The module is not architecture-specific and only involves logical development.

6. Steps to install the module

   Step 1: Download the Generic Excel Operation module from GitHub library.

   Step 2: Run a Program

   Select the table name which you want to download.

   • You can download table with data or table without data based on your requirement.
      

   

   • Enter the data that needs to be uploaded in your application if you have downloaded an excel file without data.
      

   

   • In the column ‘Action,’ you can perform insert/update/delete operations. To perform these operations, enter a/m/d respectively.  ‘a’ stands for Insert, ‘m’ for update, and ‘d’ for deletion. Do not add data to the already existing fields of the database.
      

   

   
   Step 3: Upload the excel file without tampering/removing any data.

   

   Step 4: In case of an error, check the column ‘Check Status’ in order to identify the error.

   

   The cell highlighted in yellow color shows invalid data and the header shows which type of value is valid for this column.

   

   After you are done re-inserting the data, the existing data in your excel file will look similar to the below image.

   

   Step 5: After the corrections have been made, successfully upload the excel file. Once the file is uploaded successfully, the page will show that the invalid records are zero.

   

   Step 6: Reset button on the page to refresh the content.

   

Digital Transformation comprises the largest portfolio of work for Kellton Tech. Learn more about how our digital expertise is increasing ROI of clients looking for .NET development services.