how to upload excel file and add columns into database?

Question

i'm using open xml sdk dll in my Asp net MVC for uploading excel into my website.

there is 4 column in my excel file Name , Email , Company Name and Phone Number.

i use following code to get excel file from my view and save it in local disk and then access to it's cell for saving in my database.

**but there is problem in debugging the code. when i want to access cell that contain string it shows me it contains 0 but there is no problem in reading numbers.

it shows zero when my foreach item goes on cells that contains string .** any idea's?

edited Question: i managed to read cell's value with this code modification but there is another problem.according to This answer i should read shared string to read string but when i read DataType , it valued null in some random moment. so i cant read some cell's. i cant find the problem

here is controller code:

    [HttpPost]
    public IHttpActionResult UploadExcelFile()
    {
        try
        {
            var rnd = new Random();
            string filePath = "";
            var httpRequest = HttpContext.Current.Request;
            if (httpRequest.Files.Count < 1)
            {
                return Content(HttpStatusCode.BadRequest, "there's no file selected");
            }

            var guid = rnd.Next(999);
            var postedFile = httpRequest.Files[0];
            var serverFilePath = HttpContext.Current.Server.MapPath("~/Content/Email/" + guid + Path.GetExtension(postedFile.FileName));
            filePath = "/Content/Email/" + guid + Path.GetExtension(postedFile.FileName);
            postedFile.SaveAs(serverFilePath);


            using (SpreadsheetDocument spreadsheetDocument =SpreadsheetDocument.Open(serverFilePath, false))
            {
                List<string> colName = new List<string>(new string[] { "A", "B", "C","D" });

                var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants<Sheet>();
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                Worksheet worksheet = worksheetPart.Worksheet;
                var rowCount = GetRowCount(worksheet);
                for (int i = 0; i < rowCount; i++)
                {  
                    Cell cell = GetCell(worksheet, colName[i], i+1);
                    Row row = GetRow(worksheet, i+1);
                    foreach (var item in row.ChildElements)
                    {
                        List<string> cellValue = new List<string>();

                        if (cell.DataType != null)
                        {
                            if (cell.DataType == CellValues.SharedString)
                            {
                                int id = -1;

                                if (Int32.TryParse(cell.InnerText, out id))
                                {
                                    SharedStringItem x = GetSharedStringItemById(workbookPart, id);

                                    if (x.Text != null)
                                    {
                                        cellValue.Add(x.Text.Text);
                                    }
                                    else if (item.InnerText != null)
                                    {
                                        cellValue.Add(x.InnerText);
                                    }
                                    else if (item.InnerXml != null)
                                    {
                                        cellValue.Add(x.InnerXml);
                                    }
                                }
                            }
                        }
                        else
                        {
                            cellValue.Add(item.InnerText);
                        }


                    }

                }



            }


            return Ok(new { message = "Record has been saved successfully" });
        }
        catch (Exception e)
        {
            return Content(HttpStatusCode.BadRequest, "there's problem with Server");

            throw;
        }
    }

Which library are you using? Are you using the Open XML SDK? Epplus? ClosedXML? NPOI? — Panagiotis Kanavos, Dec 19 '18 at 07:56
In any case, in Excel `.Text` gives you a formatted string. `.Value` will give you the actual value. In case of numbers, `.Text` will return a formatted string while `.Value` will return the int or fload stored in the cell. — Panagiotis Kanavos, Dec 19 '18 at 07:58
I assume it's 2.7.1+, because the `CellValue` wasn't available in previous versions. Check the [Retrieveing a Value](https://learn.microsoft.com/en-us/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet?view=openxml-2.8.1#retrieving-the-value) in [How to: Retrieve the values of cells in a spreadsheet document (Open XML SDK)](https://learn.microsoft.com/en-us/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet?view=openxml-2.8.1). You should use `Cell.DataType.Value` or try to find the string in the shared strings table — Panagiotis Kanavos, Dec 19 '18 at 08:56
The Open XML format allows saving strings that are used in multiple locations in a shared strings table. Cells that use this value contain a index to that table, not the actual string. `CellValue` refers to a specific XML element and the `InnerXml`, `OuterXml`, `Text`, `InnerText` properties refer to that XML element's text and inner text. When that `cell` element contains a reference, there's no free-standing text to return. I suspect that if you check the `InnerXml` or `OuterXml` properties — Panagiotis Kanavos, Dec 19 '18 at 08:58
Libraries like EPPlus, ClosedXML and NPOI have an API similar to that exposed by Excel Interop. The Open XML SDK though works at a lower level and exposes the package's details. — Panagiotis Kanavos, Dec 19 '18 at 09:02

score -2 · Answer 1 · answered Dec 19 '18 at 07:48

-2

        var connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\mybook.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=1;\"";


        var OledbConn = new OleDbConnection(connString);

        DataTable schemaTable = new DataTable();
        var OledbCmd = new OleDbCommand();
        OledbCmd.Connection = OledbConn;
        OledbConn.Open();
        OledbCmd.CommandText = "Select * from [StudentDetails$]";
        OleDbDataReader dr = OledbCmd.ExecuteReader();
        DataTable ContentTable = null;
        if (dr.HasRows)
        {
            ContentTable = new DataTable();
            ContentTable.Columns.Add("Name", typeof(string));
            ContentTable.Columns.Add("Email", typeof(string));
            ContentTable.Columns.Add("Company Name", typeof(string));
            ContentTable.Columns.Add("Phone Number", typeof(string));
            while (dr.Read())
            {
                if (dr[0].ToString().Trim() != string.Empty && dr[1].ToString().Trim() != string.Empty && dr[2].ToString().Trim() != string.Empty && dr[0].ToString().Trim() != " " && dr[1].ToString().Trim() != " " && dr[2].ToString().Trim() != " ")
                    ContentTable.Rows.Add(dr[0].ToString().Trim(), dr[1].ToString().Trim(), dr[2].ToString().Trim(), dr[3].ToString().Trim());

            }
        }
        dr.Close();
    }

answered Dec 19 '18 at 07:48

vijay joshi

38
1
10

all the row will get into ContentTable (DataTable ) now you can do whatever you want – vijay joshi Dec 19 '18 at 07:52
Code-only answers are *not* good answers unless it's obvious what's going on. In this case the suggestion to use the JET driver instead of a native library will actually make things worse. It doesn't answer the OP's question either – Panagiotis Kanavos Dec 19 '18 at 07:54
Even worse, converting Excel values to strings with `ToString()` using the local culture guarantees conversion errors, especially for numbers and dates. For strings, it's simply wastefull, the value is already a string – Panagiotis Kanavos Dec 19 '18 at 08:00
This very code. As it is, no modifications. What happens if one cell contains a *date*? Dates are *binary values*, not strings with separators. What would that code produce in the US? How would that differ from the US or France? Same for numbers with decimals. You'd gett *different* strings after calling `ToString()` on a number in the US, US, France or India – Panagiotis Kanavos Dec 19 '18 at 08:11
What would i do can you elobrate @PanagiotisKanavos – vijay joshi Dec 19 '18 at 08:39

how to upload excel file and add columns into database?

1 Answers1