最近闲来无事封装了使用NPOI对excle文件增删改查的方法,亲测可用附代码:
/// <summary>
/// 根据路径和表名得到Dictionary数据字典
/// </summary>
/// <param name="excelPath"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public Dictionary<int, Dictionary<string, string>> GetDictionaryBySheetName(string excelPath, string sheetName)
{
Dictionary<int, Dictionary<string, string>> sheetDic = new Dictionary<int, Dictionary<string, string>>();
DataTable dt = GetDataTableBySheetName(excelPath, sheetName);
if (dt.Rows.Count <= 0) return sheetDic;
//1 获取表头
int index = 0;
foreach (DataRow itemRow in dt.Rows)
{
Dictionary<string, string> rowDic = new Dictionary<string, string>();
for (int columnsI = 0; columnsI < dt.Columns.Count; columnsI++)
{
rowDic.Add(dt.Columns[columnsI].ToString(), itemRow[columnsI].ToString());
}
sheetDic.Add(index, rowDic);
index++;
}
return sheetDic;
}
/// <summary>
/// 根据路径和表名得到datatabel
/// </summary>
/// <param name="excelPath"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public DataTable GetDataTableBySheetName(string excelPath, string sheetName)
{
DataTable dt = new DataTable();
dt.TableName = sheetName;
IWorkbook workbook = GetOpenWorkbookByExtension(excelPath);
ISheet sheet;
if (!TryPassWorkbook(workbook, sheetName, out sheet)) return dt;
//1 读取Excel 第一行创建列头
IRow headRow = sheet.GetRow(0); //如果需要指定列头 可以在此处传入参数(修改该方法)
ICell cell = null;
string tempStr = string.Empty;
for (int columnI = 0; columnI < headRow.LastCellNum; columnI++)
{
cell = headRow.GetCell(columnI);
tempStr = cell == null ? columnI.ToString() : cell.ToString();
if (dt.Columns.Contains(tempStr))
{
throw new Exception(string.Format("EXCLE表{0}存在相同列属性【{1}】", sheet.SheetName, tempStr));
//处理excel列值相同问题
}
else
{
dt.Columns.Add(tempStr, typeof(string));
}
}
IRow row = null;
//2 填充数据
for (int rowI = 1; rowI <= sheet.LastRowNum; rowI++)
{
row = sheet.GetRow(rowI);
DataRow dtRow = dt.NewRow();
if (null == row) continue;
//3 遍历单元格
for (int columJ = 0; columJ < dt.Columns.Count; columJ++)
{
cell = row.GetCell(columJ);
if (0 == columJ && (null == cell || "##" == cell.ToString())) break;
if (null == cell) continue;
tempStr = cell.ToString();
dtRow[columJ] = tempStr;
}
dt.Rows.Add(dtRow);
}
return dt;
}
/// <summary>
/// 移除工作薄中Sheet
/// </summary>
/// <param name="excelPath">文件路径</param>
/// <param name="sheetName">表名</param>
/// <returns>返回结果</returns>
public bool RemoveSheetByName(string excelPath, string sheetName)
{
//1 得到工作簿
IWorkbook workbook = GetOpenWorkbookByExtension(excelPath);
if (null == workbook) return false;
int sheetIndex = workbook.GetSheetIndex(sheetName);
if (sheetIndex < 0) return false;
workbook.RemoveSheetAt(sheetIndex);
return SaveExcelFile(excelPath, workbook);
}
public bool RemoveSheetByName(string excelPath, int sheetIndex)
{
//1 得到工作簿
IWorkbook workbook = GetOpenWorkbookByExtension(excelPath);
if (null == workbook) return false;
if (workbook.NumberOfSheets < sheetIndex) return false;
workbook.RemoveSheetAt(sheetIndex);
return SaveExcelFile(excelPath, workbook);
}
有需要可以下载(添加现有项就能用):https://download.csdn.net/download/weixin_43542114/12424263