OPENXML将EXCEL转DataTable及空值处理，插入数据库

public class LoadMore
    {
        public bool InsertSQL(string path, string sheetName)
        {
            FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);
            DataTable dt = ReadExcel(sheetName, fs);
            dt.Rows[0].Delete();
            fs.Close();
            OleDbCommand oleDBAccess = null;
            OleDbConnection AccessCon = null;
            string Connect = System.Configuration.ConfigurationManager.AppSettings["connectionString"] + System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["data"]);
            AccessCon = new OleDbConnection(Connect);
            AccessCon.Open();
            oleDBAccess = new OleDbCommand();
            oleDBAccess.Connection = AccessCon;
            oleDBAccess.Transaction = AccessCon.BeginTransaction();
            try
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
//去掉第一行的说明

                    if (dt.Rows[i][0].ToString() == "")
                    {
                        continue;
                    }
                    else
                    {
                        string sqlstr = "insert into table values('" + dt.Rows[i][0].ToString() + "','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "')";
//只读取前5列

                        oleDBAccess.CommandText = sqlstr;
                        oleDBAccess.ExecuteNonQuery();
                    }
                }
                oleDBAccess.Transaction.Commit();
                return true;
            }
            catch
            {
                oleDBAccess.Transaction.Rollback();
                return false;
            }
            finally
            {
                AccessCon.Close();
                dt.Dispose();
//上传完毕删除文件

                if (File.Exists(path))
                {
                    File.Delete(path);
                }
            }
        }

        private DataTable ReadExcel(string sheetName, Stream stream)
        {
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, false))
            {
                IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
                if (sheets.Count() == 0)
                {
                    return null;
                }
                WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(sheets.First().Id);
                SharedStringTable stringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                IEnumerable<Row> rows = worksheetPart.Worksheet.Descendants<Row>();

                DataTable dt = new DataTable("Excel");
                foreach (Row row in rows)
                {
                    if (row.RowIndex == 1)
                    {
                        //continue;
                        GetDataColumn(row, stringTable, ref dt);
                    }
                    GetDataRow(row, stringTable, ref dt);
                }
                return dt;
            }
        }

        string CheckRow = "ABCDE";
        private void GetDataRow(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            DataRow dr = dt.NewRow();
            int i = 0;
            int nullRowCount = i;
            foreach (Cell cell in row)
            {
                string crf = cell.CellReference;
                string cellVal = "";
                cellVal = GetValue(cell, stringTable);
                if (cellVal == string.Empty)
                {
                    nullRowCount++;
                }
//判断当前数据是否跳过EXCEL空值

                if (crf[0].ToString() == CheckRow[i].ToString())
                {
                    dr[i] = cellVal;
                    i++;
                }
                else
                {
//根据跳过的数据条数填充相应的DataTable为空值，下面的代码临时写的

                    int k = 1;
                    for (int j = 0; j < CheckRow.Length; j++)
                    {
                        if (crf[0].ToString() == CheckRow[j].ToString())
                        {
                            k = j;
                        }
                    }
                    for (int m=i; m < k; m++)
                    {
                        dr[m] = "";
                    }
                    dr[k] = cellVal;
                    i = k + 1;
                }
            }
            if (nullRowCount != i)
            {
                dt.Rows.Add(dr);
            }
        }

        private string GetValue(Cell cell, SharedStringTable stringTable)
        {
            string value = string.Empty;
            try
            {
                if (cell.ChildElements.Count == 0)
                    return value;
                value = double.Parse(cell.CellValue.InnerText).ToString();
                if ((cell.DataType != null) && (cell.DataType == CellValues.SharedString))
                {
                    value = stringTable.ChildElements[Int32.Parse(value)].InnerText;
                }
            }
            catch (Exception)
            {
                value = "N/A";
            }
            return value;
        }

        private void GetDataColumn(Row row, SharedStringTable stringTable, ref DataTable dt)
        {
            DataColumn col = new DataColumn();
            Dictionary<string, int> columnCount = new Dictionary<string, int>();
            foreach (Cell cell in row)
            {
                string cellVal = GetValue(cell, stringTable);
                col = new DataColumn(cellVal);
                if (IsContainsColumn(dt, col.ColumnName))
                {
                    if (!columnCount.ContainsKey(col.ColumnName))
                        columnCount.Add(col.ColumnName, 0);
                    col.ColumnName = col.ColumnName + (columnCount[col.ColumnName]++);
                }
                dt.Columns.Add(col);
            }
        }

        private bool IsContainsColumn(DataTable dt, string columnName)
        {
            if (dt == null || columnName == null)
            {
                return false;
            }
            return dt.Columns.Contains(columnName);
        }
    }

使用openxml将excel数据导入数据库的时候，遇到空值会报错，分析XML文档发现的文档中没有存储excel表格中的空值，比如我的文档中出现<c r="A2" t="s"><v>19637</v></c><c r="C2"><v>2000</v></c>，是跳过了“B2”里面的空值，上面的代码是根据 r 的值去比较跳过的数据，然后给DataTable赋值为空，避免了插入数据库报错。

使用的是Access数据库和Excel2007，代码比较完整，待优化。