how to read data from excel sheet and insert into database table in C#

Reading excel file using EPPlus package and inserting into table is very easy in C# MVC framework.
Here I have created a simple method which expect file name (with full path) as only parameter and read first worksheet and dump data into table.

Following item you need to considered before you copy below code:
1) Below code only read first sheet of the workbook. Yes you can modify it anytime to read multiple sheets.
2) It assumes that, data which need to be inserted start from the top of the excel. Means there is no column header.
3) And below insert statement does not include column name (which specific to my requirement). Such statments called “blind insert“. One should include column name in insert statements. Blind insert are highly not recommanded.

public bool readXLS(string FilePath)
{
    FileInfo existingFile = new FileInfo(FilePath);
    using (ExcelPackage package = new ExcelPackage(existingFile))
    {
        //get the first worksheet in the workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
        int colCount = worksheet.Dimension.End.Column;  //get Column Count
        int rowCount = worksheet.Dimension.End.Row;     //get row count
        string queryString = "INSERT INTO tableName VALUES";        //Here I am using "blind insert". You can specify the column names Blient inset is strongly not recommanded
        string eachVal = "";
        bool status;
        for (int row = 1; row <= rowCount; row++)
        {
            queryString += "(";
            for (int col = 1; col <= colCount; col++)
            {
                eachVal = worksheet.Cells[row, col].Value.ToString().Trim();
                queryString += "'" + eachVal + "',";
            }
            queryString = queryString.Remove(queryString.Length - 1, 1);    //removing last comma (,) from the string
            if (row % 1000 == 0)        //On every 1000 query will execute, as maximum of 1000 will be executed at a time. 
            {
                queryString += ")";
                status = this.runQuery(queryString);    //executing query
                if (status == false)
                    return status;
                queryString = "INSERT INTO tableName VALUES";
            }
            else
            {
                queryString += "),";
            }
        }
        queryString = queryString.Remove(queryString.Length - 1, 1);    //removing last comma (,) from the string
        status = this.runQuery(queryString);    //executing query
        return status;
    }
}

Generating and Formatting excelsheet in c# using EPPlus

Getting data from database in DataTable format and then generating Excel sheet and formatting it using EPPlus is very easy in C# MVC. Here I am showing how you can generate excel using DataTable.
If you do not want any formatting and just want to genrate Excel file you can do like below.I have created a method which takes 2 parameters first as Datatable and second name of the file including full path.
Before get started make sure you have added below namespaces in your controller.

using OfficeOpenXml;
using OfficeOpenXml.Style;
public void GenerateExcel(DataTable DT, string fullFileName)
{
    using (ExcelPackage excel = new ExcelPackage(fullFileName))
    {
        ExcelWorksheet sheetcreate = excel.Workbook.Worksheets.Add("Accounts");
        sheetcreate.Cells["A1"].LoadFromDataTable(DT, true);
        pck.Save();
    }
}

Now if you want to format data inside the excel sheet here is same method with formatting options:

public void GenerateExcel(DataTable DT, string fullFileName)
{
    var file = new FileInfo(fullFileName);
    string currentFileName = System.IO.Path.GetFileName(fullFileName);
    ExcelPackage excel = new ExcelPackage(file);
    var sheetcreate = excel.Workbook.Worksheets.Add("Sheet1");
           
    int col = 0;
    foreach (DataColumn column in DT.Columns)  //printing column headings
    {
        sheetcreate.Cells[1,++col].Value = column.ColumnName;
        sheetcreate.Cells[1, col].Style.Font.Bold = true;
        sheetcreate.Cells[1, col].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
        sheetcreate.Cells[1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
    }
    if (DT.Rows.Count > 0)
    {
        int row = 1;
        decimal checkDecimal;
        for (int eachRow = 0; eachRow < DT.Rows.Count; )    //looping each row
        {

            for (int eachColumn = 1; eachColumn <= col; eachColumn++)   //looping each column in a row
            {
                var eachRowObject = sheetcreate.Cells[row + 1, eachColumn];
                eachRowObject.Style.Fill.PatternType = ExcelFillStyle.Solid;
                eachRowObject.Value = DT.Rows[eachRow][(eachColumn - 1)].ToString();

                if (decimal.TryParse(DT.Rows[eachRow][(eachColumn - 1)].ToString(), out checkDecimal))      //verifying value is number to make it right align
                {
                    eachRowObject.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }
                eachRowObject.Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);     // adding border to each cells

                if (eachRow % 2 == 0)       //alternatively adding color to each cell.
                    eachRowObject.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#e0e0e0"));
                else
                    eachRowObject.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#ffffff"));
            }
            eachRow++;
            row++;
                    
        }        
    }
    sheetcreate.Cells.AutoFitColumns();
    excel.Save();
}

The above code will generate and save excel file in location specified in “fullFileName”, but if you want to download immediately after creation replace below code with “excel.save()”

excel.Workbook.Properties.Title = "Attempts";
this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
this.Response.AddHeader("content-disposition",string.Format("attachment;  filename={0}", "ExcellData.xlsx"));
this.Response.BinaryWrite(excel.GetAsByteArray());

Few tips for formattings:
1) Formatting text, possible values:”Italic”, “Bold”

sheetcreate.Cells[1, 1].Style.Font.Bold = true;

2) For merging cells

sheetcreate.Cells[1, 1, 1, 5].Merge = true; //Address "A1:A5"

3) Setting up border

sheetcreate.Cells[1, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);

4) Setting up cell alignment. It can be “Center”, “Right” and “Left”.

 sheetcreate.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

5) Setting up background color

 sheetcreate.Cells[1, 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#e0e0e0"));

6) To avoid word wrap in excel use blow function. It should be written after creation of excel sheet.

sheetcreate.Cells.AutoFitColumns();

Starting windows services from C# MVC 4

Sometime you need to start/stop windows services from your C# MVC application. Here i am sharing how you can do that:

1) To access windows service you need to add below namespace in your controller:

using System.ServiceProcess;

If you are getting error as “namespace name ‘ServiceController’ could not be found” while adding above namesapce you need to add using “Add Reference”. For more details see my blog HERE
2) Add below code to your controller:

ServiceController service = new ServiceController("Test Windows Service");
service.Start();

It will start your services.

  Comments or questions are welcome  

The type or namespace name ‘ServiceController’ could not be found

May times you need to add namespaces to your application, here I sharing how you can add “System.ServiceProcess” required to incorporate windows services to your application.

Follow the below steps add references:
1) Right click on solution explore and click on add reff
Add Reference
2( go to Assembly => Framework and look for “System.ServiceProcess” and enable it.
enable_Service_Process
Click OK to enable it.

3) Now add belowline at the top:

using System.ServiceProcess;

4) Add the code below:

ServiceController svcController = new ServiceController("Test Windows Service");

if (svcController != null)
{
    try
    {
        //svcController.Stop();
        //svcController.WaitForStatus(ServiceControllerStatus.Stopped, TimeSpan.FromSeconds(10));
        svcController.Start();
    }
    catch
    {
    }
}
  Comments or questions are welcome  

Visual Studio not loading few files and folder in Solution Explorer

I am building a project in C# MVC4 using Microsoft Visual Studio 2012 and getting this issue. Whenever i add or delete any project files from explorer it does not reflects in solution explorer. Then I made practice to update files and folder from solution explorer itself to avoid such issue.
But sometime it happens like if we get any new files/folder during SVN updates it does not reflect in solution explorer. To overcome this issue you can do following steps to resolve it:

1. Open Visual Studio and open the project.
2. Click on “Project” in menu and look for “Show All Files”.
VS_Show_All_Files

3) This option will show all files/folders added outside Solution Explorer.
4) Select all files and folder which you want to include and right click and choose “Include in Project”
VS_Project_Files_folders
Now it start showing in your solution explorer.