如何在Excel中将数据从Excel工作表导入到sql中?

问题描述

我如何使用asp.net将数据从Excel工作表导入到sql服务器中?

hi how can i import data from excel sheet to sql server by using asp.net

推荐答案

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>

这篇关于如何在Excel中将数据从Excel工作表导入到sql中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！