Upload Excel File in MVC and C# code

This blog is regarding import data from excel in Database using MVC 4.0 and C#.
in VIEW Code
@{
    ViewBag.Title = “ImportData”;
   }
@using (Html.BeginForm(“ImportData”, “Admin”FormMethod.Post, new { enctype = “multipart/form-data” }))
{
   <input type=”file” id=”fileToUpload” name=”file” />
   <span id=”spanfile“></span>
   <input type=”submit” id=”btnSubmit” value=”Upload” />
}
In the above line “ImportData” is Action and Admin is the Controller and form method to be posted
In Controller Code
[HttpPost]
public ActionResult ImportData(HttpPostedFileBase file)
{
Excel.Workbook MyBook = null;
Excel.Application MyApp = null;
Excel.Worksheet MySheet = null;
DataTable dtmaster = new DataTable();
string fileLocation = “D:/Mahesh/Uploadfiles/Template.xlsx;
//Server.MapPath(“D:/Mahesh/Content/”) + Request.Files[“file”].FileName;
if (ModelState.IsValid)
{
    if (file.ContentLength > 0)
    {
        string filename = file.FileName;
        string[] extension = new string[] { “.xlsx”“.xls”, “.xlx” };
        string ext = file.FileName.Substring(file.FileName.LastIndexOf(‘.’));
    if (extension.Contains(ext))
       {
           MyApp = new Excel.Application();
           MyApp.Visible false;
           MyBook = MyApp.Workbooks.Open(fileLocation);
           MySheet = (Excel.Worksheet)MyBook.Sheets[1];
           //Excel.
          int lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
         System.Array columns = (System.Array)MySheet.get_Range(“A” +
         "1", "H" + “1”).Cells.Value;
        for (int j = 1; j <= columns.Length; j++)
           {
            dtmaster.Columns.Add(Convert.ToString(columns.GetValue(1, j)));
           }
           int _row = 0;
        for (int index = 2; index <= lastRow; index++)
           {
              dtmaster.Rows.Add();
               System.Array MyValues = (System.Array)MySheet.get_Range(“A” +
               index.ToString(), “H” + index.ToString()).Cells.Value;
               for (int i = 1; i <= MyValues.Length; i++)
               {
                 dtmaster.Rows[_row][Convert.ToString(dtmaster.Columns[i – 1].ColumnName)]                             = Convert.ToString(MyValues.GetValue(1, i));
               }
             _row++;
          }
    }
     else
     {
     }
    }
   }
  return View();
   }
here iadding data in datatable after that we can add in to database
System.Array columns = (System.Array)MySheet.get_Range(“A” +
“1”, “H” + “1”).Cells.Value;
here “A”, “H” is called Columns in the Excel. we can mention how many columns we have the data

Split string by comma in MYSql

In Mysql do not have in split method. We should go for different approach.
There are two steps to split string by comma(‘,’) and save into our database table
1.First Create one Function as `splitString`
2 Secondly Create one Store Procedure as ‘AddNames’
Syntax For Creating Function
CREATE FUNCTION splitString(stringToSplit VARCHAR(256), sign VARCHAR(12), position INT)
RETURNS VARCHAR(256)
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(stringToSplit, sign, position),LENGTH(SUBSTRING_INDEX(stringToSplit, sign, position -1)) + 1), sign, ”);
END
Example
Given String = ‘Mahesh,Ramesh,Rakesh’
take second name as separated by comma. For that function
mysql> select splitString(‘Mahesh,Ramesh,Rakesh’,’,’, 2) as name;
———–
| name |
———–
| Ramesh |
———–
This query can be used to get how many comma signs are in the input string

SELECT LENGTH(stringToSplit) – LENGTH(REPLACE(stringToSplit, ‘,’, ”)) INTO noOfCommas;

Example
mysql> SELECT LENGTH(‘Mahesh,Ramesh,Rakesh’) – LENGTH(REPLACE(‘Mahesh,Ramesh,Rakesh’, ‘,’, ”)) INTO @noOfCommas;
Query OK
mysql> select @noOfCommas;
——————-
| @noOfCommas |
——————-
| 2 |
——————-

Using above query and splitString function, write a stored procedure that do some SQL operations on each element of given string.
Following stored procedure will split the string and insert the slpited strings into a table.

CREATE PROCEDURE AddNames( IN fullname varchar(256))
BEGIN
CREATE TABLE IF NOT EXISTS `Names` (`ID` int(11) NOT NULL auto_increment,`NAME` varchar(256) NOT NULL,PRIMARY KEY (`ID`)) AUTO_INCREMENT=1 ;
DECLARE x INT DEFAULT 0;
DECLARE y INT DEFAULT 0;
SET y = 1;
IF NOT fullname IS NULL
THEN
SELECT LENGTH(fullname ) – LENGTH(REPLACE(fullname , ‘,’, ”)) INTO @noOfCommas;
IF @noOfCommas = 0
THEN
INSERT INTO Names(name) VALUES(fullname );
ELSE
SET x = @noOfCommas + 1;
WHILE y <= x DO
SELECT split_string(fullname , ',', y) INTO @flName;
INSERT INTO Names(name) VALUES(@flName);
SET y = y + 1;
END WHILE;
END IF;
END IF;
END

Example of Calling StoredProcedure
mysql> call AddNames(‘Mahesh,Ramesh,Rakesh’);

mysql> select * from Names;
|—-|———–
| ID | NAME |
|—-|———–
| 1 | Mahesh |
| 2 | Ramesh |
| 3 | Rakesh |
|—-|———–

Microsoft Asp.Net Blog Content

Welcome to Microsoft Blogs. Here any one can learn regarding Microsoft technologies. Subscribe this blob for more updates.


This blog contains


  1. Microsoft Technolies
  2. C#
  3. ASP.NET
  4. ADO.NET
  5. ASP.NET MVC
  6. SQL Server

Popular Posts