Hi mahesh213,
Refer below sample.
Excel Data
| Id |
MemberType |
MemberCategory |
TotalSum |
MemberCount |
| 1 |
Generation1 |
A |
73 |
4 |
| 2 |
Generation2 |
B |
84 |
3 |
| 3 |
Generation3 |
A |
8 |
1 |
| 4 |
Generation4 |
A |
8 |
1 |
| 5 |
Generation5 |
B |
5 |
6 |
Namespaces
using System.Data;
using System.Data.OleDb;
using System.IO;
Controller
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
public ActionResult Upload()
{
DataSet ds = new DataSet("Members");
if (Request.Files.Count > 0)
{
HttpPostedFileBase postedFile = Request.Files[0];
string fileName = Path.GetFileName(postedFile.FileName);
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
postedFile.SaveAs(path + fileName);
string filePath = string.Empty;
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
string conString = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03.
conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
break;
case ".xlsx": //Excel 07 and above.
conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'";
break;
}
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(ds, "Member");
connExcel.Close();
}
}
}
}
foreach (DataColumn column in ds.Tables[0].Columns)
{
column.ColumnName = column.ColumnName.Replace(" ", "_");
}
ds.WriteXml(Server.MapPath("~/Uploads/Member.xml"));
return new JsonResult() { Data = new { FileName = "Member.xml" } };
}
[HttpGet]
public ActionResult Download(string fileName)
{
string filePath = Server.MapPath("~/Uploads/" + fileName);
if (System.IO.File.Exists(filePath))
{
byte[] bytes = System.IO.File.ReadAllBytes(filePath);
return File(bytes, "text/xml", fileName);
}
else
{
return new EmptyResult();
}
}
}
View
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
$('#fuUpload').on('change', function () {
var formData = new FormData();
var files = $(this).get(0).files[0];
formData.append(files.name, files);
$.ajax({
url: "/Home/Upload",
type: 'POST',
cache: false,
contentType: false,
processData: false,
data: formData,
success: function (response) {
window.location = '/Home/Download?filename=' + response.FileName;
}
});
});
});
</script>
</head>
<body>
<input type="file" name="postedFile" id="fuUpload" />
</body>
</html>
Downloaded XML File Data
<?xml version="1.0" standalone="yes"?>
<Members>
<Member>
<Id>1</Id>
<MemberType>Generation1</MemberType>
<MemberCategory>A</MemberCategory>
<TotalSum>73</TotalSum>
<MemberCount>4</MemberCount>
</Member>
<Member>
<Id>2</Id>
<MemberType>Generation2</MemberType>
<MemberCategory>B</MemberCategory>
<TotalSum>84</TotalSum>
<MemberCount>3</MemberCount>
</Member>
<Member>
<Id>3</Id>
<MemberType>Generation3</MemberType>
<MemberCategory>A</MemberCategory>
<TotalSum>8</TotalSum>
<MemberCount>1</MemberCount>
</Member>
<Member>
<Id>4</Id>
<MemberType>Generation4</MemberType>
<MemberCategory>A</MemberCategory>
<TotalSum>8</TotalSum>
<MemberCount>1</MemberCount>
</Member>
<Member>
<Id>5</Id>
<MemberType>Generation5</MemberType>
<MemberCategory>B</MemberCategory>
<TotalSum>5</TotalSum>
<MemberCount>6</MemberCount>
</Member>
</Members>
