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，代码比较完整，待优化。