导入主要的多一步就是excel列名和数据库字段不一样,无法相对应,我也查过资料,如果写成指定插入的话,无法做成通用接口,实用性不高;
1.首先将Exel读取到datatable里面,方便操作(建议导入之前先备份)
public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
{
DataTable data = new DataTable();
NPOI.SS.UserModel.ISheet sheet = null;
int startRow = 0;
try
{
if (!File.Exists(fileName))
{
return null;
}
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
NPOI.SS.UserModel.IWorkbook workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
NPOI.SS.UserModel.IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum;
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
NPOI.SS.UserModel.ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
if (row == null) continue;
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
return data;
}
catch (Exception ex)
{
throw ex;
}
}
外面只需要传excel所在位置即可
var data = ExcelHelper.ReadExcelToDataTable("D:\\20190425\\测试模板.xlsx");
2.操作返回的datatable就比较方便了 ,将excel换成和数据库对应的字段,我这里用的是dictionary;
foreach (DataColumn dc in dt.Columns)
{
if (dic.Keys.Contains(dc.ColumnName))
dc.ColumnName = dic[dc.ColumnName].ToString();
}
#region DATATABLE 转 dicyionary
List<Dictionary<string, object>> list2 = new List<Dictionary<string, object>>();
foreach (DataRow dr in dt.Rows)
{
Dictionary<string, object> dct = new Dictionary<string, object>();
foreach (DataColumn dc in dt.Columns)
{
dct.Add(dc.ColumnName, dr[dc.ColumnName].ToString());
}
list2.Add(dct);
}
3.将更改完之后的datatable转为dictionary,然后添加到数据库(最好之前需要先查询一下插入数据库)
var fil = QueryPageFilter.Create("ZT_YWYYB").Equal("ID", id);
var yyb = ServiceAppContext.Instance.DataBaseClassHelper.GetQueryDic(fil);
Dictionary<object, object> dic = new Dictionary<object, object>();
List<string> list = new List<string>();
var filter = QueryPageFilter.Create("ZT_YYZDB").Equal("SSYY", id).Equal("SFDCZD", true).Equal("SFSC", false);
var table = ServiceAppContext.Instance.DataBaseClassHelper.GetQueryResultN(filter);
foreach (var item in table)
{
list.Add(item["ZDMC"].ToString());
dic.Add(item["XSM"], item["ZDMC"]);
}
var filter2 = QueryPageFilter.Create(yyb["SSBM"].ToString()).Equal("SFSC", false).ReturnFields(list.ToArray());
var phg = ServiceAppContext.Instance.DataBaseClassHelper.GetQueryResultN(filter2);
List<Dictionary<string, object>> list2 = new List<Dictionary<string, object>>();
foreach (DataRow dr in dt.Rows)
{
Dictionary<string, object> dct = new Dictionary<string, object>();
foreach (DataColumn dc in dt.Columns)
{
dct.Add(dc.ColumnName, dr[dc.ColumnName].ToString());
}
list2.Add(dct);
}
#endregion
foreach (var item in list2)
{
if (!item.Keys.Contains("ID"))
item.Add("ID", Guid.NewGuid().ToString());
else if (item["ID"] != null && string.IsNullOrEmpty(item["ID"].ToString()))
{
item["ID"] = Guid.NewGuid().ToString();
}
}
ServiceAppContext.Instance.DataBaseClassHelper.UpdateObjects(yyb["SSBM"].ToString(), list2.ToArray());
}