locked
Help Importing mulitple date Columns from excel using EP Plus RRS feed

  • Question

  • User325035487 posted

    I have a large excel file with ten columns containing date format. Using EpPlus I wrote a cs file to handle the import. Find the code below for the cs file in my App_Code

    public class ImportXL
        {
    
            public List<string> ImportBioAsset(FileInfo file, int HospID,string EmpID)
            {
                var resultMessages = new List<string>();
                var totalImported = 0;
                try
                {
                    using (var excelPackage = new ExcelPackage(file))
                    {
                        string AssetNo,InstallDate,CommissionDate,PODate,WarrantyDate,LastPPM,ELCTDate,CalibrationDate,CalDueDate,MCStart,MCEnd;
                        if (!file.Name.EndsWith("xlsx"))
                        {
                            resultMessages.Add("Select File is not an Excel file");
                            return resultMessages;
                        }
                        var worksheet = excelPackage.Workbook.Worksheets[1];
                        if (worksheet == null)
                        {
                            resultMessages.Add("File was empty!");
                            return resultMessages;
                        }
                        using (var headers = worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
                        {
                            var expectedHeaders = new[] {"InstallDate","CommissionDate","PODate","WarrantyDate","LastPPM","ELCTDate","CalibrationDate","CalDueDate","MCStart","MCEnd"};
                            if (!expectedHeaders.All(e => headers.Any(h => h.Value.Equals(e))))
                            {
                                resultMessages.Add("Some Columns are missing from the file");
                                return resultMessages;
                            }
                            AssetNo = headers.First(h => h.Value.Equals("AssetNo")).Address[0].ToString();
                            InstallDate = headers.First(h => h.Value.Equals("InstallDate")).Address[0].ToString();
                            CommissionDate = headers.First(h => h.Value.Equals("CommissionDate")).Address[0].ToString();
                            PODate = headers.First(h => h.Value.Equals("PODate")).Address[0].ToString();
                            WarrantyDate = headers.First(h => h.Value.Equals("WarrantyDate")).Address[0].ToString();
                            LastPPM = headers.First(h => h.Value.Equals("LastPPM")).Address[0].ToString();
                            ELCTDate = headers.First(h => h.Value.Equals("ELCTDate")).Address[0].ToString();
                            CalibrationDate = headers.First(h => h.Value.Equals("CalibrationDate")).Address[0].ToString();
                            CalDueDate = headers.First(h => h.Value.Equals("CalDueDate")).Address[0].ToString();
                            MCStart = headers.First(h => h.Value.Equals("MCStart")).Address[0].ToString();
                            MCEnd = headers.First(h => h.Value.Equals("MCEnd")).Address[0].ToString();
                        }
                        var lastRow = worksheet.Dimension.End.Row;
                        while (lastRow >= 1)
                        {
                            var range = worksheet.Cells[lastRow, 1, lastRow,2];
                            if (range.Any(c => c.Value != null))
                            {
                                break;
                            }
                            lastRow--;
                        }
                        ConnectionStringSettings mySetting = ConfigurationManager.ConnectionStrings["data"];
                        var conString = mySetting.ConnectionString;
                        using (SqlConnection connection = new SqlConnection(conString))
                        {
                            for (var row = 2; row <= lastRow; row++)
                            {
                                using (SqlCommand command = new SqlCommand())
                                {
                                    command.Connection = connection;
                                    command.CommandType = CommandType.Text;
                                    command.CommandText = "IF NOT EXISTS (SELECT 1 FROM AssetListBio WHERE AssetNo = @AssetNo) INSERT INTO AssetListBio (AssetNo,InstallDate,CommissionDate,PODate,WarrantyDate,LastPPM,ELCTDate,CalibrationDate,CalDueDate,MCStart,MCEnd) VALUES (@AssetNo,@InstallDate,@CommissionDate,@PODate,@WarrantyDate,@LastPPM,@ELCTDate,@CalibrationDate,@CalDueDate,@MCStart,@MCEnd)";
                                    command.Parameters.AddWithValue("@AssetNo", ((object)worksheet.Cells[AssetNo + row].Value) ?? DBNull.Value);
                                    SqlParameter insdate = new SqlParameter("@InstallDate", worksheet.Cells[InstallDate + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[InstallDate + row].Value));
                                    insdate.IsNullable = true;
                                    insdate.Direction = ParameterDirection.Input;
                                    insdate.SqlDbType = SqlDbType.DateTime;
                                    command.Parameters.Add(insdate);
                                    SqlParameter comdate = new SqlParameter("@CommissionDate", worksheet.Cells[CommissionDate + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[CommissionDate + row].Value));
                                    comdate.IsNullable = true;
                                    comdate.Direction = ParameterDirection.Input;
                                    comdate.SqlDbType = SqlDbType.DateTime;
                                    command.Parameters.Add(comdate);
                                    //SqlParameter poodate = new SqlParameter("@PODate", worksheet.Cells[PODate + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[PODate + row].Value));
                                    //poodate.IsNullable = true;
                                    //poodate.Direction = ParameterDirection.Input;
                                    //poodate.SqlDbType = SqlDbType.DateTime;
                                    //command.Parameters.Add(poodate);
                                    SqlParameter wrndate = new SqlParameter("@WarrantyDate", worksheet.Cells[WarrantyDate + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[WarrantyDate + row].Value));
                                    wrndate.IsNullable = true;
                                    wrndate.Direction = ParameterDirection.Input;
                                    wrndate.SqlDbType = SqlDbType.DateTime;
                                    command.Parameters.Add(wrndate);
                                    SqlParameter lpmdate = new SqlParameter("@LastPPM", worksheet.Cells[LastPPM + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[LastPPM + row].Value));
                                    lpmdate.IsNullable = true;
                                    lpmdate.Direction = ParameterDirection.Input;
                                    lpmdate.SqlDbType = SqlDbType.DateTime;
                                    command.Parameters.Add(lpmdate);
                                    SqlParameter ellcdate = new SqlParameter("@ELCTDate", worksheet.Cells[ELCTDate + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[ELCTDate + row].Value));
                                    ellcdate.IsNullable = true;
                                    ellcdate.Direction = ParameterDirection.Input;
                                    ellcdate.SqlDbType = SqlDbType.DateTime;
                                    command.Parameters.Add(ellcdate);
                                    SqlParameter calibdate = new SqlParameter("@CalibrationDate", worksheet.Cells[CalibrationDate + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[CalibrationDate + row].Value));
                                    calibdate.IsNullable = true;
                                    calibdate.Direction = ParameterDirection.Input;
                                    calibdate.SqlDbType = SqlDbType.DateTime;
                                    command.Parameters.Add(calibdate);
                                    SqlParameter caldudate = new SqlParameter("@CalDueDate", worksheet.Cells[CalDueDate + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[CalDueDate + row].Value));
                                    caldudate.IsNullable = true;
                                    caldudate.Direction = ParameterDirection.Input;
                                    caldudate.SqlDbType = SqlDbType.DateTime;
                                    command.Parameters.Add(caldudate);
                                    SqlParameter mcsdate = new SqlParameter("@MCStart", worksheet.Cells[MCStart + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[MCStart + row].Value));
                                    mcsdate.IsNullable = true;
                                    mcsdate.Direction = ParameterDirection.Input;
                                    mcsdate.SqlDbType = SqlDbType.DateTime;
                                    command.Parameters.Add(mcsdate);
                                    SqlParameter mcedate = new SqlParameter("@MCEnd", worksheet.Cells[MCEnd + row].Value == null ? (Object)DBNull.Value : ConvertDate((object)worksheet.Cells[MCEnd + row].Value));
                                    mcedate.IsNullable = true;
                                    mcedate.Direction = ParameterDirection.Input;
                                    mcedate.SqlDbType = SqlDbType.DateTime;
                                    command.Parameters.Add(mcedate);
                                    try
                                    {
                                        connection.Open();
                                        int recordsAffected = command.ExecuteNonQuery();
                                        totalImported++;
                                    }
                                    catch (Exception ex)
                                    {
                                        // error here
                                        resultMessages.Add(string.Format("Record on line #{0} failed: {1}\n", row, ex.Message));
                                    }
                                    finally
                                    {
                                        connection.Close();
                                    }
                                }
                            }
                        }
                    }
                    resultMessages.Insert(0, string.Format("{0} records successfully imported.\n", totalImported));
                    return resultMessages;
                }
                catch (IOException ex)
                {
                    resultMessages.Add("File still open. Please close Excel File before importing!");
                    return resultMessages;
                }
            }
    
            private object ConvertDate(object p)
            {
                DateTime? excel = null;
                if (p!=null)
                {
                    var d = (double)p;
                    excel = DateTime.FromOADate(d);             
                }
                return excel;
            }
        }

    This working fine for two columns of date perfectly.

    Excel File date columns are all formatted as date of same type.

    My Database screenshot after successful import of this file

    As you can see only two columns imported accurately. The remaining dates are either 1900-01-01 or 1899-12-31

    If you notice the end of my cs I am using a method

    private object ConvertDate(object p)
            {
                DateTime? excel = null;
                if (p!=null)
                {
                    var d = (double)p;
                    excel = DateTime.FromOADate(d);             
                }
                return excel;
            }

    before this I was not able to import and was getting error cannot convert double to date time. I got this method from Stackoverflow here: Link

    Can anyone tell me why this method is working fine for some columns while not for others. The strangest thing is i created the excel file from scratch and put same dates and formats. Only the two columns named Install and Commission Dates are imported. I tried interchanging column names.

    In addition why is not saving as database null when my excel field is null. I tried interchanging the codes and excel columns. Stumped here. Thanks in advance

    Sunday, August 30, 2015 12:56 PM

All replies

  • User325035487 posted

    I forgot to include code for importing.cshtml

    if (IsPost)
        {
            var xlsRoot = Server.MapPath("~/App_Data/CMMS/Uploaded/");
            var filename = Guid.NewGuid().ToString() + ".xlsx";
            var umSavePath = Path.Combine(xlsRoot, filename);
            var excelfile = Request.Files["excelmanu"];
            excelfile.SaveAs(umSavePath);
            ImportXLSX test = new ImportXL();
            FileInfo fi = new FileInfo(umSavePath);
            List<string> messages = new List<string>();
            messages = test.ImportBioAsset(fi,hid,WebSecurity.CurrentUserName);
            foreach (var r in messages){
                <p>@r.ToString()</p>
            }
    
            if (File.Exists(umSavePath))
            {
                File.Delete(umSavePath);
    
            }
    
        }

    Sunday, August 30, 2015 1:03 PM