How to read an Excel file using C# MVC 4

Reading Excel file using C# MVC 4 is very easy. Here I am sharing the code how you can read cell by cell in excel sheets.

1) Right click on Project name on solution explore and then “Add Reference..”
Add Reference

2) On opening of “Reference Manager” popup, look for “Microsoft.Office.Interop.Excel” in “Assemblies” > “Extensions” list and check it. And click “OK” button to enable it.

add_reference
3) Now to use it in your controller add below namespace:

using Excel = Microsoft.Office.Interop.Excel;

4) Create a method which will read the excel file content and return it. You can refer to below code which expect parameter “fullpath” of excel file on server.

public string excelParsing(string fullpath)
{
    string data = "";
    //Create COM Objects. Create a COM object for everything that is referenced
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fullpath);
    Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    Excel.Range xlRange = xlWorksheet.UsedRange;

    int rowCount = xlRange.Rows.Count;
    int colCount = xlRange.Columns.Count;

    //iterate over the rows and columns and print to the console as it appears in the file
    //excel is not zero based!!
    for (int i = 1; i <= rowCount; i++)
    {
        for (int j = 1; j <= colCount; j++)
        {
             //either collect data cell by cell or DO you job like insert to DB 
            if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                data += xlRange.Cells[i, j].Value2.ToString();
        }
    }

    return data;
}

Hope it will help someone to in reading excel file using C# MVC4.

  Comments or questions are welcome  

ASP.net MVC 4 Razor File Uploading

Sharing simple and basic code using which we can easily upload a single file to the server. You can add validation before you move to production server as the code does not validate the file type. One should validate file type before moving to production server as a user can upload any unwanted file to the server and take control over the server data.

HTML: [You can place below code in view folder]

<form method="post" name="exportData" action="~/ControllerName/saveFile" enctype = "multipart/form-data" >
<div>File :</div>
<input name="file" id="file" type="file">
<input type="submit" id="upload" name="upload" />
</form>

Server :[You can add below code in controller]

[HttpPost]
public ActionResult saveFile(HttpPostedFileBase file)
{
    if (file != null && file.ContentLength > 0)
    {
        string folderPath = System.Configuration.ConfigurationManager.AppSettings.Get("expimp_folder");
        var fileName = Path.GetFileName(file.FileName);
        var path = folderPath+"/"+fileName;
        file.SaveAs(path);
    }
    return new EmptyResult();
}

Hope it will help someone!!!

  Comments or questions are welcome  

Export CSV file using C# MVC

Sharing here how you can export CSV file on runtime using C# MVC 4.5.
In below code I am executing query to get data and then exporting it as CSV files.

public ActionResult exportData(FormCollection exportForm)
{
    var exportReport = exportForm["exportList"];
    if (exportReport != "")
    {
        var BL = new businesslogic();
        string sqlQuery = "SELECT * FROM " + exportReport;
        DataSet tableDataSet = BL.GetResultsDS(cs, sqlQuery);

        string data = ""; //will store file data
        bool flag = true;
        string headings = ""; //to store column headings
        foreach (DataTable table in tableDataSet.Tables)
        {
            foreach (DataRow row in table.Rows)
            {
                data += "\n"; // New line for each column
                foreach (DataColumn column in table.Columns)
                {
                    if(flag)
                        headings += column + ",";
                    data +=row[column]+",";
                }
                flag = false; //once we get heading setting to false;
            }
        }
        data = headings+data;
        Response.Clear();
        Response.ContentType = "application/CSV";
        Response.AddHeader("content-disposition", "attachment; filename=\"" + exportReport + ".csv\"");
        Response.Write(data);
        Response.End();
        return new EmptyResult();
    }
    else
    {  
         //redirecting to different page if no form data found.
        return RedirectToAction("index", "ExportImport");
    }
}

Hope this will help someone!!!

Generating and Formatting excelsheet in c# using EPPlus

  Comments or questions are welcome  

ASP. net MVC 4 – You do not have permission to use the bulk load statement.

When trying to execute store procedure having bulk insert, getting error as
You do not have permission to use the bulk load statement.

Error is popping up due to permission is not granted to SQL user accessing DB. So Below I am sharing how you can provide permission to user to fix this issue.
1) Go to MS SQL server management and connect to db.
2) In Object Explorer, go to Security and then login.
3) Look for your user and right click on it.
SSMS - objecte explorer
4) “Login Properties” popup will open
5) Click on “Server Roles” and check “bulkadmin”.
SSMS - Login Properties

Now if you try to execute again you must not get the error.
Hope this will help someone!!!

  Comments or questions are welcome