Excel upload EPPlus bulk upload read excel to datatable dynamically
public Result CrudUploadExcel()
{
Result er = new Result();
try
{
var
httpRequest = HttpContext.Current.Request;
if
(httpRequest.Files.Count > 0)
{
var
postedFile = httpRequest.Files[0];
DataTable dtExcel
= new DataTable();
AMAT.Utilities.ExcelComponent.ExcelReader
oExcelReader = new AMAT.Utilities.ExcelComponent.ExcelReader(postedFile.InputStream,
"Master");
//FileInfo
fileInfo = new FileInfo("C:\\Users\\Downloads\\ashwini20k.xlsx");
using (ExcelPackage p = new ExcelPackage(postedFile.InputStream))
{
//AMAT.Utilities.ExcelComponent.ExcelReader
oExcelReader1 = new AMAT.Utilities.ExcelComponent.ExcelReader(p.Stream,
"Master");
//dtExcel = ReadExcelToTable();
ExcelWorksheet worksheet =
p.Workbook.Worksheets[1];
int noOfCol =
worksheet.Dimension.End.Column; //get
Column Count
int noOfRow =
worksheet.Dimension.End.Row; //get
row count
int rowIndex = 1;
for (int c = 1;
c <= noOfCol; c++)
{
dtExcel.Columns.Add(worksheet.Cells[rowIndex, c].Text);
}
rowIndex = 2;
for (int r =
rowIndex; r <= noOfRow; r++)
{
DataRow dr =
dtExcel.NewRow();
for (int c = 1;
c <= noOfCol; c++)
{
dr[c - 1] =
worksheet.Cells[r, c].Value;
}
dtExcel.Rows.Add(dr);
}
}
if
(oExcelReader.CurrentSheet == null)
{
er.IsSuccess = Constants.FAIL;
er.Message = "Incorrect File.";
}
}
}
catch (Exception ex)
{
Common.WriteToLog("API", "UploadExcel()", ex);
er.IsSuccess = Constants.FAIL;
er.Message = ex.Message;
}
return er;
}
public Result
CommonCrudUploadExcel()
{
Result er = new Result();
try
{
var
httpRequest = HttpContext.Current.Request;
if
(httpRequest.Files.Count > 0)
{
var
postedFile = httpRequest.Files[0];
DataTable dtExcel
= new DataTable();
AMAT.Utilities.ExcelComponent.ExcelReader
oExcelReader = new AMAT.Utilities.ExcelComponent.ExcelReader(postedFile.InputStream,
"Master");
using (ExcelPackage p = new ExcelPackage(postedFile.InputStream))
{
ExcelWorksheet worksheet =
p.Workbook.Worksheets[1];
int noOfCol =
worksheet.Dimension.End.Column;
int noOfRow =
worksheet.Dimension.End.Row;
oExcelReader.ReadDocument(noOfCol, ref
dtExcel);
}
if
(oExcelReader.CurrentSheet == null)
{
er.IsSuccess = Constants.FAIL;
er.Message = "Incorrect File.";
return er;
}
string user =
httpRequest.Params["user"];
string table =
httpRequest.Params["table"];
if
(dtExcel.Rows.Count > 20000)
{
er.IsSuccess = Constants.FAIL;
er.Message = "Upload limit 20,000.";
return er;
}
}
}
catch (Exception ex)
{
Common.WriteToLog("API", "UploadExcel()", ex);
er.IsSuccess = Constants.FAIL;
er.Message = ex.Message;
}
return er;
}
