Click here to Skip to main content
15,892,059 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
hi how can i import data from excel sheet to sql server by using asp.net
Posted

 
Share this answer
 
v2
Comments
sandeep nagabhairava 7-Apr-12 1:28am    
hi i referred those links but i have this error
"The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."
Get data into dataset and you can insert data in SQL:
----------------------------------------------------------
C#
DataSet  dsResult = new DataSet();
dsResult = obj.ImportExcelXML(strFilePath, true, true);




public static DataSet ImportExcelXML(string inputFileStream, bool hasHeaders, bool autoDetectColumnType)
    {
        XmlDocument doc = new XmlDocument();
        doc.Load(inputFileStream);
        XmlNamespaceManager nsmgr = new XmlNamespaceManager(doc.NameTable);

        nsmgr.AddNamespace("o", "urn:schemas-microsoft-com:office:office");
        nsmgr.AddNamespace("x", "urn:schemas-microsoft-com:office:excel");
        nsmgr.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");

        DataSet ds = new DataSet();

        foreach (XmlNode node in doc.DocumentElement.SelectNodes("//ss:Worksheet", nsmgr))
        {
            DataTable dt = new DataTable(node.Attributes["ss:Name"].Value);
            ds.Tables.Add(dt);
            XmlNodeList rows = node.SelectNodes("ss:Table/ss:Row", nsmgr);
            if (rows.Count > 0)
            {
                List<columntype> columns = new List<columntype>();
                int startIndex = 0;
                if (hasHeaders)
                {
                    foreach (XmlNode data in rows[0].SelectNodes("ss:Cell/ss:Data", nsmgr))
                    {
                        columns.Add(new ColumnType(typeof(string)));//default to text
                        dt.Columns.Add(data.InnerText, typeof(string));
                    }
                    startIndex++;
                }
                if (autoDetectColumnType && rows.Count > 0)
                {
                    XmlNodeList cells = rows[startIndex].SelectNodes("ss:Cell", nsmgr);
                    int actualCellIndex = 0;
                    for (int cellIndex = 0; cellIndex < cells.Count; cellIndex++)
                    {
                        XmlNode cell = cells[cellIndex];
                        if (cell.Attributes["ss:Index"] != null)
                            actualCellIndex = int.Parse(cell.Attributes["ss:Index"].Value) - 1;

                        ColumnType autoDetectType = getType(cell.SelectSingleNode("ss:Data", nsmgr));

                        if (actualCellIndex >= dt.Columns.Count)
                        {
                            dt.Columns.Add("Column" + actualCellIndex.ToString(), autoDetectType.type);
                            columns.Add(autoDetectType);
                        }
                        else
                        {
                            dt.Columns[actualCellIndex].DataType = autoDetectType.type;
                            columns[actualCellIndex] = autoDetectType;
                        }

                        actualCellIndex++;
                    }
                }
                for (int i = startIndex; i < rows.Count; i++)
                {
                    DataRow row = dt.NewRow();
                    XmlNodeList cells = rows[i].SelectNodes("ss:Cell", nsmgr);
                    int actualCellIndex = 0;
                    for (int cellIndex = 0; cellIndex < cells.Count; cellIndex++)
                    {
                        XmlNode cell = cells[cellIndex];
                        if (cell.Attributes["ss:Index"] != null)
                            actualCellIndex = int.Parse(cell.Attributes["ss:Index"].Value) - 1;

                        XmlNode data = cell.SelectSingleNode("ss:Data", nsmgr);

                        if (actualCellIndex >= dt.Columns.Count)
                        {
                            for (int ii = dt.Columns.Count; ii < actualCellIndex; ii++)
                            {
                                dt.Columns.Add("Column" + actualCellIndex.ToString(), typeof(string));
                                columns.Add(getDefaultType());
                            }
                            ColumnType autoDetectType = getType(cell.SelectSingleNode("ss:Data", nsmgr));
                            dt.Columns.Add("Column" + actualCellIndex.ToString(), typeof(string));
                            columns.Add(autoDetectType);
                        }
                        if (data != null)
                            row[actualCellIndex] = data.InnerText;

                        actualCellIndex++;
                    }

                    dt.Rows.Add(row);
                }
            }
        }
        return ds;

        //
        //<?mso-application progid="Excel.Sheet"??>
        //<workbook>
        // <worksheet ss:name="Sheet1" xmlns:ss="#unknown">
        //  <table>
        //   <row>
        //    <cell><data ss:type="String">Item Number</data></cell>
        //    <cell><data ss:type="String">Description</data></cell>
        //    <cell ss:styleid="s21"><data ss:type="String">Item Barcode</data></cell>
        //   </row>
        // </table></worksheet>
        //</workbook>
    }
    private static ColumnType getDefaultType()
    {
        return new ColumnType(typeof(String));
    }

    private static ColumnType getType(XmlNode data)
    {
        string type = null;
        if (data.Attributes["ss:Type"] == null || data.Attributes["ss:Type"].Value == null)
            type = "";
        else
            type = data.Attributes["ss:Type"].Value;

        switch (type)
        {
            case "DateTime":
                return new ColumnType(typeof(DateTime));
            case "Boolean":
                return new ColumnType(typeof(Boolean));
            case "Number":
                return new ColumnType(typeof(Decimal));
            case "":
                decimal test2;
                if (data == null || String.IsNullOrEmpty(data.InnerText) || decimal.TryParse(data.InnerText, out test2))
                {
                    return new ColumnType(typeof(Decimal));
                }
                else
                {
                    return new ColumnType(typeof(String));
                }
            default://"String"
                return new ColumnType(typeof(String));
        }
    }
    struct ColumnType
    {
        public Type type;
        private string name;
        public ColumnType(Type type) { this.type = type; this.name = type.ToString().ToLower(); }
        public object ParseString(string input)
        {
            if (String.IsNullOrEmpty(input))
                return DBNull.Value;
            switch (type.ToString())
            {
                case "system.datetime":
                    return DateTime.Parse(input);
                case "system.decimal":
                    return decimal.Parse(input);
                case "system.boolean":
                    return bool.Parse(input);
                default:
                    return input;
            }
        }
    }</columntype></columntype>
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900