发布：2024/6/15 0:18:02作者：管理员 来源：本站 浏览次数：641
1.在项目上右键——》管理Nuget包——》搜索NPOI——》安装
2.编写一个ExcelHelper类
class ExcelHelper
{
/// <summary>
/// 将DataTable数据保存至Excel文件
/// </summary>
/// <param name="dt"></param>
/// <param name="txtPath"></param>
/// <returns></returns>
public static bool DataTableToExcel(DataTable dt, string txtPath)
{
bool result = false;
IWorkbook workbook;
FileStream? fs = null;
IRow row;
ISheet sheet;
ICell cell;
try
{
if (dt != null && dt.Rows.Count > 0)
{
// 新建工作簿对象
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet("Sheet1");//创建一个名称为Sheet1的表
int rowCount = dt.Rows.Count;//行数
int columnCount = dt.Columns.Count;//列数
//设置列头
row = sheet.CreateRow(0);//excel第一行设为列头
for (int c = 0; c < columnCount; c++)
{
cell = row.CreateCell(c);
cell.SetCellValue(dt.Columns[c].ColumnName);
}
//设置每行每列的单元格,
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//excel第二行开始写入数据
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
using (fs = File.OpenWrite(txtPath))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
}
MessageBox.Show("导出成功");
return result;
}
catch (Exception)
{
fs?.Close();
return false;
}
}
/// <summary>
/// 将excel导入到datatable
/// </summary>
/// <param name="filePath">excel路径</param>
/// <param name="isColumnName">第一行是否是列名</param>
/// <returns>返回datatable</returns>
public static DataTable? ExcelToDataTable(string filePath, bool isColumnName)
{
DataTable? dataTable = null;
FileStream? fs = null;
DataColumn column;
DataRow dataRow;
IWorkbook? workbook = null;
ISheet sheet;
IRow row;
ICell cell;
int startRow = 0;
try
{
using (fs = File.OpenRead(filePath))
{
// 版本后缀控制
if (filePath.IndexOf(".xlsx") > 0)
workbook = new XSSFWorkbook(fs);
// 版本后缀控制
else if (filePath.IndexOf(".xls") > 0)
workbook = new HSSFWorkbook(fs);
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet
dataTable = new DataTable();
if (sheet != null)
{
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0);//第一行
int cellCount = firstRow.LastCellNum;//列数
//构建datatable的列
if (isColumnName)
{
startRow = 1;//如果第一行是列名，则从第二行开始读取
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
cell = firstRow.GetCell(i);
if (cell != null)
{
if (cell.StringCellValue != null)
{
column = new DataColumn(cell.StringCellValue);
dataTable.Columns.Add(column);
}
}
}
}
else
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
column = new DataColumn("column" + (i + 1));
dataTable.Columns.Add(column);
}
}
//填充行
for (int i = startRow; i <= rowCount; ++i)
{
row = sheet.GetRow(i);
if (row == null) continue;
dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
cell = row.GetCell(j);
if (cell == null)
{
dataRow[j] = "";
}
else
{
//CellType(未知 = -1,数字 = 0,字符串 = 1,公式 = 2,空 = 3,布尔 = 4,错误 = 5,)
switch (cell.CellType)
{
case CellType.Blank:
dataRow[j] = "";
break;
case CellType.Numeric:
short format = cell.CellStyle.DataFormat;
//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
if (format == 14 || format == 31 || format == 57 || format == 58)
dataRow[j] = cell.DateCellValue;
else
dataRow[j] = cell.NumericCellValue;
break;
case CellType.String:
dataRow[j] = cell.StringCellValue;
break;
}
}
}
dataTable.Rows.Add(dataRow);
}
}
}
}
}
return dataTable;
}
catch (Exception)
{
fs?.Close();
throw new Exception("操作无法完成,因为文件已在另一个程序中打开");
}
}
/// <summary>
/// 将datatable中数据插入数据库中
/// </summary>
/// <param name="helper"></param>
/// <param name="dataTable"></param>
/// <param name="tablename"></param>
public static void InsertIntoDB(SqlHelper helper, DataTable dataTable, string tablename)
{
//声明事务
SQLiteTransaction? transaction = null;
try
{
// 开始一个事务
transaction = helper.BeginTransaction();
//获取当前表的列数
string colCountSql = $"SELECT COUNT(*) FROM PRAGMA_TABLE_INFO('{tablename}');";
int colCount = Convert.ToInt32(helper.ExecuteScalar(colCountSql));
// 获取列名
string columnNames = string.Join(", ", dataTable.Columns.Cast<DataColumn>().Select(c => c.ColumnName));
// 构造SQL
string insertSql = $"INSERT INTO {tablename} ({columnNames}) " +
$"VALUES ({string.Join(",", Enumerable.Repeat("?", colCount))});";
foreach (DataRow dataRow in dataTable.Rows)
{
// 构建并提供参数列表
List<SQLiteParameter> parameters = [];
// 为当前行创建参数并设置其值
for (int i = 0; i < colCount; i++)
{
var parameter = new SQLiteParameter
{
Value = dataRow[i] ?? DBNull.Value
};
parameters.Add(parameter);
}
// 执行SQL插入操作
int result = helper.ExecuteNonQueryInTransaction(insertSql, [.. parameters]);
}
// 成功则提交事务
transaction.Commit();
}
catch (IndexOutOfRangeException)
{
transaction?.Rollback();
throw new Exception($"导入失败: Excel文件的列数与数据库表的列数不匹配，请检查文件结构是否正确");
}
catch (SQLiteException sqliteEx) when (sqliteEx.ResultCode == SQLiteErrorCode.Constraint)
{
transaction?.Rollback();
throw new Exception($"导入失败: 数据库唯一键约束冲突");
}
catch (Exception ex)
{
// 如果发生任何错误，回滚事务
if (transaction != null && transaction.Connection != null && transaction.Connection.State == ConnectionState.Open)
{
transaction.Rollback();
}
throw new Exception($"导入失败: {ex.Message}");
}
}
}
3.第三个方法插入数据库，我使用的是sqlite，大家注意更换自己的数据库。
另外在插入时我使用了事务，确保数据要么全部插入成功，要么全部失败。
因此在操作数据库的工具类中需要增加开启事务的方法和使用事务进行增删改的方法
/// <summary>
/// 开启事务
/// </summary>
/// <returns></returns>
public SQLiteTransaction BeginTransaction()
{
return _connection.BeginTransaction();
}
/// <summary>
/// 使用事务执行增删改
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ExecuteNonQueryInTransaction(string sql, params SQLiteParameter[] parameters)
{
using var command = new SQLiteCommand(sql, _connection);
// 将当前事务赋给command对象，确保该命令在事务范围内执行
command.Transaction = _currentTransaction;
command.Parameters.AddRange(parameters);
return command.ExecuteNonQuery();
}
© Copyright 2014 - 2025 柏港建站平台 ejk5.com. 渝ICP备16000791号-4