[HttpPost]
public ActionResult Index(HttpPostedFileBase file)
{
DataTable dt
= new DataTable
(); DataTable dtOk
= new DataTable
(); DataTable dtErr
= new DataTable
();
if (file != null && file.ContentLength > 0 && System.IO.Path.GetExtension(file.FileName).ToLower() == ".xlsx")
{
string path = Path.Combine(Server.MapPath("~/UploadFile"), Path.GetFileName(file.FileName));
file.SaveAs(path);
using (XLWorkbook workbook
= new XLWorkbook
(path
)) {
IXLWorksheet worksheet = workbook.Worksheet(1);
bool FirstRow = true;
string readRange = "1:1";
foreach (IXLRow row in worksheet.RowsUsed())
{
if (FirstRow)
{
readRange = string.Format("{0}:{1}", 1, row.LastCellUsed().Address.ColumnNumber);
foreach (IXLCell cell in row.Cells(readRange))
{
dt.Columns.Add(cell.Value.ToString());
dtOk.Columns.Add(cell.Value.ToString());
dtErr.Columns.Add(cell.Value.ToString());
}
FirstRow = false;
}
else
{
dt.Rows.Add();
int cellIndex = 0;
foreach (IXLCell cell in row.Cells(readRange))
{
dt.Rows[dt.Rows.Count - 1][cellIndex] = cell.Value.ToString();
cellIndex++;
}
}
}
if (FirstRow)
{
ViewBag.Message = "Empty Excel File!";
}
}
}
else
{
ViewBag.Message = "Please select file with .xlsx extension!";
}
string connection = "Data Source=(localDb)\\LocalDb;Initial Catalog=ExcelDataBaseReader;Integrated Security=True";
SqlConnection con
= new SqlConnection
(connection
);
SqlBulkCopy sqlBulkCopy
= new SqlBulkCopy
(con
); sqlBulkCopy.DestinationTableName = "tbl_ProgettiSociali2";
for (int i = 0; i < dt.Rows.Count; i++)
{
int j = 0;
string ANNO = dt.Rows[i]["ANNO"].ToString();
string NRPROTOCOLLO = dt.Rows[i]["NRPROTOCOLLO"].ToString();
string DATA_INSERIMENTO = dt.Rows[i]["DATA INSERIMENTO"].ToString();
DateTime datetime_DATA_INSERIMENTO;
bool isValidData_DATA_INSERIMENTO = DateTime.TryParseExact(DATA_INSERIMENTO, "dd/MM/yyyy hh:mm:ss", CultureInfo.InvariantCulture,
DateTimeStyles.None, out datetime_DATA_INSERIMENTO);
if (isValidData_DATA_INSERIMENTO == true)
{
DateTime aDate = Convert.ToDateTime(dt.Rows[i]["DATA INSERIMENTO"]);
j++;
}
/*else
{
ViewBag.Message = "Formato DATA_INSERIMENTO errato alla riga n" + (i) + " del file excel.";
}*/
string TIPOLOGIA = dt.Rows[i]["TIPOLOGIA"].ToString();
string STATO = dt.Rows[i]["STATO"].ToString();
string AMBITO_DINTERVETO = dt.Rows[i]["AMBITO D'INTERVENTO"].ToString();
string SOGGETTI_DESTINATARI = dt.Rows[i]["SOGGETTI DESTINATARI"].ToString();
string TITOLO_INIZIATIVA = dt.Rows[i]["TITOLO INIZIATIVA"].ToString();
string DATA_INIZIO = dt.Rows[i]["DATA INIZIO(GG/MM/AAAA)"].ToString();
DateTime datetime_DATA_INIZIO;
bool isValidData_DATA_INIZIO = DateTime.TryParseExact(DATA_INIZIO, "dd/MM/yyyy hh:mm:ss", CultureInfo.InvariantCulture,
DateTimeStyles.None, out datetime_DATA_INIZIO);
if (isValidData_DATA_INIZIO == true)
{
DateTime bDate = Convert.ToDateTime(dt.Rows[i]["DATA INIZIO(GG/MM/AAAA)"]);
j++;
}
/*else
{
ViewBag.Message = "Formato DATA_INIZIO errato alla riga n" + (i) + " del file excel.";
}*/
string DATA_FINE = dt.Rows[i]["DATA FINE(GG/MM/AAAA)"].ToString();
DateTime datetime_DATA_FINE;
bool isValidData_DATA_FINE = DateTime.TryParseExact(DATA_FINE, "dd/MM/yyyy hh:mm:ss", CultureInfo.InvariantCulture,
DateTimeStyles.None, out datetime_DATA_FINE);
if (isValidData_DATA_FINE == true)
{
DateTime cDate = Convert.ToDateTime(dt.Rows[i]["DATA FINE(GG/MM/AAAA)"]);
j++;
}
/*else
{
ViewBag.Message = "Formato DATA_FINE errato alla riga n" + (i) + " del file excel.";
}*/
if(j == 3)
{
dtOk.Rows.Add(dt.Row[i]);
}
else if (j != 3)
{
dtErr.Rows.Add(dt.Row[i]);
}
}
con.Open();
sqlBulkCopy.WriteToServer(dtOk);
con.Close();
//sqlBulkCopy.WriteToServer(dt)
return View(dtErr);
| 
