Excel column data making alphabet values blank using asp.net

Question

While uploading Excel there are values in columns in alphabets, so taking into datatable the values are getting blank.

below is the code which is making it blank.

string filename = Path.GetFileName(fluploadData.FileName);
        // FileUpload.SaveAs(Server.MapPath("~/") + filename);
        string filenamewithoutrext = string.Empty;
        FileExt = Path.GetExtension(fluploadData.FileName).ToLower();
        if (Path.GetExtension(fluploadData.FileName).ToLower() != ".xls" &&
            Path.GetExtension(fluploadData.FileName).ToLower() != ".xlsx"
        )
        {

            Response.Write("Only .xls,.xlsx are allowed.!");
            return;
        }

        filenamewithoutrext = Path.GetFileNameWithoutExtension(fluploadData.FileName).ToLower();

        string path = Server.MapPath("UploadData\\");
        string filename_ = filenamewithoutrext;

        //   DeleteDirectory(path);
        if (!Directory.Exists(path))   // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
        {
            Directory.CreateDirectory(path);
        }
        else
        {
            foreach (string file in Directory.GetFiles(path))
            {
                File.Delete(file);
            }
        }

        string fname;
        fname = path + filename_ + ".xls";

        fluploadData.SaveAs(fname);
        HttpContext.Current.Session["ExcelFilePath"] = fname;

        string conStr = "";
        System.Data.DataTable dtExcelRows = new System.Data.DataTable();

        switch (FileExt)
        {
            case ".xls": //Excel 97-03
                conStr = System.Configuration.ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = System.Configuration.ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }

        conStr = String.Format(conStr, fname, "YES");

        System.Data.OleDb.OleDbConnection connExcel = new System.Data.OleDb.OleDbConnection(conStr);
        System.Data.OleDb.OleDbCommand cmdExcel = new System.Data.OleDb.OleDbCommand();
        System.Data.OleDb.OleDbDataAdapter oda = new System.Data.OleDb.OleDbDataAdapter();
        cmdExcel.Connection = connExcel;
        connExcel.Open();
        System.Data.DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
        System.Data.DataTable dtExcelColumnsTable = connExcel.GetSchema("Columns");
        //string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString().Replace('\'', ' ').Trim();
        string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString().Trim();  
        cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
        oda.SelectCommand = cmdExcel;
        oda.Fill(dtExcelRows);
        connExcel.Close();

Answer 1

The problem is likely caused by mixed datatypes in a column. For example the first few rows have a number value so it is then presumed to be a column with a numeric type. When a non numeric value is encountered it can't be parsed and is ignored.

See this answer for some ways around this. They may include setting a value of IMEX=1 in the connection string or treating the header row as data (HDR=0) - assuming your column names are non numeric.

You might also want to look into specific libraries which are designed for reading/writing Excel Documents, I have had good experiences with EPPlus but it only works for .xlsx files not .xls, NPOI which I haven't used can do both.

As others have mentioned if you are just using the Excel document as a datasource for the applications it may be better to look into an alternative solution such as SQL. If you have a requirement to process spreadsheets from third parties obviously that would not be an option.