OpenXml应用：将Excel文件导入sql server数据库

需求

将Excel文件导入数据库

分析

Excel文件中有多个页签，每个页签中的列相同，页签名称做为分类，包含名称和编码，导入分类表，所有页签数据导入相同的表，每次数据总数变化不大，数量也不算多，所以写库就简单循环处理了。

设计

从Excel中读数据

循环所有页签，跳过不需要导入的

读取页签名称，解析出分类名称和编码

读取所有行数据，存入DataTable

首行为列名

其余行为数据

页签数据加入数据列表

数据入库

循环所有数据列表

插入分类

插入数据

实现

项目添加引用

DocumentFormat.OpenXml

WindowsBase

    class GoldBranch1
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <param name="ignoreSheetName">无须导入的页签</param>
        public void ImportToDb(string excelFilePath, string[] ignoreSheetName)
        {
            using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(excelFilePath, false))
            {
                var workbookPart = spreadsheetDoc.WorkbookPart;
                var sheets = workbookPart.Workbook.Sheets.Elements<Sheet>().ToArray();

                var sheetCount = sheets.Count();
                var bankDataList = new List<BankData>(sheetCount);
                //循环所有页签
                for (int i = 0; i < sheetCount; i++)
                {
                    var currentSheet = sheets[i];
                    string sheetName = currentSheet.Name.Value.Trim();

                    if (ignoreSheetName.Contains(sheetName))
                    {
                        continue;
                    }

                    var bankData = new BankData();
                    //页签名称，解析出分类名称和编码
                    ParseBankInfo(bankData, sheetName);

                    var worksheetPart = (WorksheetPart)(workbookPart.GetPartById(currentSheet.Id));
                    //读取所有行数据，将数据存入DataTable
                    var dtbl = ReadFromSheet(workbookPart, worksheetPart);

                    bankData.bankData = dtbl;

                    //页签数据加入数据列表
                    bankDataList.Add(bankData);
                }
                //入库
                foreach (var bankData in bankDataList)
                {
                    WriteToDb(bankData);
                }
            }
        }

        private void ParseBankInfo(BankData bankData, string originalName)
        {
            //分类（03）
            var leftIndex = originalName.IndexOf("（");
            var rightIndex = originalName.IndexOf("）");
            if(leftIndex<0)
            {
                leftIndex = originalName.IndexOf("(");
            }
            if(rightIndex<0)
            {
                rightIndex = originalName.IndexOf(")");
            }

            bankData.bankName = originalName.Substring(0, leftIndex);

            bankData.bankCode = originalName.Substring(leftIndex + 1, rightIndex - leftIndex-1);
        }

        public DataTable ReadFromSheet(WorkbookPart workbookPart, WorksheetPart worksheetPart /*SheetData sheetData*/)
        {
            var dtbl = new DataTable();

            var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

            var rowElements = sheetData.Elements<Row>();

            处理合单元格,excel文件中只有一处合并,直接取消合并代码就不用处理了,哈
            //if (worksheetPart.Worksheet.Descendants<MergeCells>().Count() > 0)
            //{
            //    MergeCells mergeCells = worksheetPart.Worksheet.Descendants<MergeCells>().First();
            //    foreach (MergeCell mergeCell in mergeCells)
            //    {
            //        //"A377:A383"
            //    }
            //}
            //MergeCells mcs = worksheetPart.Worksheet.Descendants<MergeCells>().First();

            var rowIndex = 0;
            foreach (var row in rowElements)
            {
                var cellIndex = -1;
                DataRow newRow = null;

                foreach (var cell in row.Elements<Cell>())
                {
                    cellIndex++;

                    if (rowIndex>0 && cellIndex >= dtbl.Columns.Count)
                    {
                        break;
                    }

                    if (cell.DataType==null && cell.CellValue==null)
                        continue;

                    string cellValue = null;
                    if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
                    {
                        cellValue = GetSharedString(workbookPart, Convert.ToInt32(cell.CellValue.Text));
                    }
                    else // if (cell.DataType.Value == CellValues.String)
                    {
                        cellValue = cell.CellValue.Text;
                    }

                    if (cellValue != null)
                    {
                        cellValue = cellValue.Trim();
                    }

                    if (rowIndex == 0)
                    {
                        //首行为列
                        dtbl.Columns.Add(cellValue);
                    }
                    else
                    {
                        //数据行
                        if (cellIndex == 0)
                        {
                            newRow = dtbl.NewRow();

                            dtbl.Rows.Add(newRow);
                        }

                        newRow[cellIndex] = cellValue;
                    }
                }
                rowIndex++;
            }

            return dtbl;
        }

        public string GetSharedString(WorkbookPart wbPart, int index)
        {
            var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

            SharedStringTable stringTable = stringTablePart.SharedStringTable;

            var strItem = stringTable.ChildElements[index];

            //var text = strItem.GetFirstChild<Text>();

            return strItem.InnerText;
        }

        private void WriteToDb(BankData bankData)
        {
            var conn = System.Configuration.ConfigurationSettings.AppSettings["conn"];

            var sql = "INSERT INTO [dbo].[Bank]([Code],[Name]) VALUES(@Code,@Name)";

            var parameter1 = new System.Data.SqlClient.SqlParameter("@Code", bankData.bankCode);
            var parameter2 = new System.Data.SqlClient.SqlParameter("@Name", bankData.bankName);
            //to do: delete data first
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, parameter1, parameter2);

            foreach (DataRow row in bankData.bankData.Rows)
            {
                sql = "INSERT INTO [dbo].[GoldBranch]" +
                    "       (BankCode,[Name],[Area],[Telephone],[Address],[IsChuanShiPhysicalGold],[IsChuanShiBuyBack],[IsCunJinTong])" +
                    " VALUES(@BankCode,@Name, @Area, @Telephone, @Address, @IsChuanShiPhysicalGold, @IsChuanShiBuyBack, @IsCunJinTong)";

                var parameter0 = new System.Data.SqlClient.SqlParameter("@BankCode", bankData.bankCode);
                parameter1 = new System.Data.SqlClient.SqlParameter("@Name", row["名称"]);
                parameter2 = new System.Data.SqlClient.SqlParameter("@Area", row[0]);
                var parameter3 = new System.Data.SqlClient.SqlParameter("@Telephone", row[2]);//"联系电话"
                var parameter4 = new System.Data.SqlClient.SqlParameter("@Address", row["地址"]);
                var parameter5 = new System.Data.SqlClient.SqlParameter("@IsChuanShiPhysicalGold", row["是否..."]);
                var parameter6 = new System.Data.SqlClient.SqlParameter("@IsChuanShiBuyBack", row["是否..."]);
                var parameter7 = new System.Data.SqlClient.SqlParameter("@IsCunJinTong", row["是否..."]);

                SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql,
                    parameter0, parameter1, parameter2, parameter3, parameter4, parameter5, parameter6, parameter7);
            }
        }
    }
    class BankData
    {
        public string bankName;
        public string bankCode;

        public DataTable bankData;
    }