提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
提示:这里可以添加本文要记录的大概内容:
例如:使用C#读取Excel文件保存到数据库中使用方法为Microsoft.Office.Interop.Excel。
提示:以下是本篇文章正文内容,下面案例可供参考
一、Microsoft.Office.Interop.Excel引用
示例:C#处理excel,需要引用Microsoft.Office.Interop.Excel;如何找到这个dll。
二、使用步骤
1.引入库
代码如下(示例):
using DevExpress.XtraEditors;
using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Application = Microsoft.Office.Interop.Excel.Application;
2.读入数据
代码如下(示例):
using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Office.Interop.Excel;
using SqlSugar;
namespace ExcelToPostgreSQL
{
class Program
{
static void Main(string[] args)
{
// 配置数据库连接字符串
string connectionString = "Server=127.0.0.1;Port=5432;Database=mydatabase;User Id=myuser;Password=mypassword;";
// 使用SqlSugar创建数据库上下文对象
var db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = connectionString,
DbType = DbType.PostgreSQL,
IsAutoCloseConnection = true,
InitKeyType = InitKeyType.Attribute,
});
// 读取Excel文件
var dataTable = ReadExcelFile("sample.xlsx");
// 将数据保存到数据库
try
{
db.BeginTran(); // 开启事务
// 遍历Excel数据行并插入到数据库
foreach (DataRow row in dataTable.Rows)
{
// 创建实体对象并赋值
var entity = new MyEntity()
{
Column1 = row["Column1"].ToString(),
Column2 = Convert.ToInt32(row["Column2"]),
Column3 = Convert.ToDecimal(row["Column3"])
};
// 插入到数据库
db.Insertable(entity).ExecuteCommand();
}
db.CommitTran(); // 提交事务
Console.WriteLine("数据保存成功!");
}
catch (Exception ex)
{
db.RollbackTran(); // 回滚事务
Console.WriteLine("数据保存失败:" + ex.Message);
}
}
// 读取Excel文件并返回DataTable
private static DataTable ReadExcelFile(string filePath)
{
var dataTable = new DataTable();
// 创建Excel对象
var excelApp = new Application();
var workbooks = excelApp.Workbooks;
var workbook = workbooks.Open(filePath);
// 读取第一个工作表
var worksheet = (Worksheet)workbook.Worksheets[1];
var range = worksheet.UsedRange;
// 读取列名
for (int i = 1; i <= range.Columns.Count; i++)
{
var columnName = ((Range)range.Cells[1, i]).Value.ToString();
dataTable.Columns.Add(columnName);
}
// 读取数据行
for (int i = 2; i <= range.Rows.Count; i++)
{
var row = dataTable.NewRow();
for (int j = 1; j <= range.Columns.Count; j++)
{
var cellValue = ((Range)range.Cells[i, j]).Value;
row[j - 1] = cellValue != null ? cellValue.ToString() : string.Empty;
}
dataTable.Rows.Add(row);
}
// 释放Excel对象
workbook.Close(false);
workbooks.Close();
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
return dataTable;
}
}
// 定义实体类
[SugarTable("MyTable")]
public class MyEntity
{
[SugarColumn(IsPrimaryKey = true)]
public int Id { get; set; }
[SugarColumn(ColumnName = "column1")]
public string Column1 { get; set; }
[SugarColumn(ColumnName = "column2")]
public int Column2 { get; set; }
[SugarColumn(ColumnName = "column3")]
public decimal Column3 { get; set; }
}
}
该处数据库账户密码填写自己配置数据库。
3.读入数据方法
代码如下(示例):
public (bool state, string mes) ImportExcel()
{
// 打开Excel文件
OpenFileDialog fd = new OpenFileDialog();
fd.Filter = "Excel文件(*.xls,xlsx)|*.xls;*.xlsx";
if (fd.ShowDialog() == DialogResult.OK)
{
ControlHelper.MessageBox_Show("操作提醒", "导入中请耐心等待。");
using (var excelApp = new Microsoft.Office.Interop.Excel.Application())
{
string filepath = fd.FileName.ToString();
Workbook workbook = excelApp.Workbooks.Open(filepath);
// 选择工作表
Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Sheets["Sheet"];
// 获取数据范围
Microsoft.Office.Interop.Excel.Range range = worksheet.UsedRange;
object[,] values = range.Value2 as object[,];
// 构造实体对象列表
var models = new List<PQCProductPointDetailModel>();
for (int row = 2; row <= range.Rows.Count; row++)
{
// 创建PQCProductPointDetailModel实例
PQCProductPointDetailModel model = new PQCProductPointDetailModel();
// 读取每一列,跳过第一列
for (int col = 2; col <= range.Columns.Count; col++)
{
Microsoft.Office.Interop.Excel.Range cell = range.Cells[row, col];
switch (col)
{
case 2:
model.Quadrant = Convert.ToInt32(cell.Value2);
break;
case 3:
model.Column = Convert.ToInt32(cell.Value2);
break;
case 4:
model.Row = Convert.ToInt32(cell.Value2);
break;
case 5:
model.Angle = Convert.ToDecimal(cell.Value2);
break;
case 6:
model.GroupNo = cell.Value2.ToString();
break;
case 7:
model.PointNo = Convert.ToInt32(cell.Value2);
break;
case 8:
model.AnQuan = cell.Value2?.ToString();
break;
case 9:
model.CeLiang = cell.Value2?.ToString();
break;
default:
break;
}
}
model.ID = Guid.NewGuid().ToString();
models.Add(model);
}
// 批量插入数据
var res = db.Insertable(models).ExecuteCommand();
if (res != models.Count)
{
//不成功删除插入数据
return (false, $"导入失败");
}
// 关闭数据库连接和 Excel 文件
db.Close();
workbook.Close();
excelApp.Quit();
}
}
else
{
return (true, $"操作已取消");
}
return (true, $"数据导入成功!");
}
该方法跳过第一行第一列数据,根据表格数据switch判断, 读取数据统一插入数据库中。
总结
边写边做的随手记录的小菜鸟,写的不对的大家多多指点。