public void Save()
{
if (wbPart != null)
{
wbPart.Workbook.Save();
}
if (xlDoc != null)
{
xlDoc.Close();
xlDoc.Dispose();
}
}
protected virtual void Dispose(bool disposing)
{
if (disposing)
{
if (_excel_file != null)
{
_excel_file.Close();
_excel_file.Dispose();
_excel_file = null;
}
_stream?.Dispose();
}
}
/// <summary>
/// This method get the content of the Excel file
/// </summary>
/// <param name="file">Excel File</param>
/// <returns>Table of the content of the Excel file</returns>
/// <author>Edwin (Origin) - Abigail Rodriguez(Edit)</author>
public ActionResult ImpExcel(HttpPostedFileBase file)
{
Dictionary <string, int> orderCell = new Dictionary <string, int>();
string[] arrayalf = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
for (int i = 0; i < arrayalf.Length; i++)
{
orderCell.Add(arrayalf[i], i);
}
DataSet ds = new DataSet();
List <List <string> > tr = new List <List <string> >();
try
{
if (Request.Files["file"].ContentLength > 0 /* && (idcategory != "0" && idcategory != null)*/)
{
string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
}
string fileLocation2 = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation2))
{
System.IO.File.Delete(fileLocation2);
}
Request.Files["file"].SaveAs(fileLocation2);
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileLocation2, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();;
foreach (Row r in sheetData.Elements <Row>())
{
List <string> td = new List <string>();
int index = 0;
foreach (Cell c in r.Elements <Cell>())
{
string cellIndex = c.CellReference.ToString().Substring(0, 1);
bool validate = false;
int numcellx = 0;
foreach (var x in orderCell)
{
if (x.Key == cellIndex)
{
numcellx = x.Value;
}
if (x.Key == cellIndex && x.Value == index)
{
validate = true;
break;
}
}
if (validate == false)
{
numcellx = numcellx - index;
for (int i = 0; i < numcellx; i++)
{
td.Add("");
}
index = index + numcellx;
}
Int32 id = -1;
if (c.DataType != null && c.DataType.Value == CellValues.SharedString)
{
if (Int32.TryParse(c.InnerText, out id))
{
SharedStringItem item = GetSharedStringItemById(workbookPart, id);
if (item.Text != null)
{
td.Add(item.Text.Text);
}
else if (item.InnerText != null)
{
td.Add(item.InnerText);
}
else if (item.InnerXml != null)
{
td.Add(item.InnerXml);
}
}
else
{
td.Add(c.CellValue.Text);
}
}
else
{
try
{
td.Add(c.CellValue.Text);
}
catch (Exception ex)
{
td.Add("");
}
}
index++;
}
tr.Add(td);
}
spreadsheetDocument.Close();
}
List <List <string> > data = new List <List <string> >();
ViewData["categoriedata"] = data;
ViewData["Filelocation"] = fileLocation2;
ViewData["Filerequest"] = file;
return(View(tr));
}
else
{
return(null);
}
}
catch (Exception ex)
{
return(null);
}
}