来自于 http://blog.abcenter.xyz/2019/06/13/excel-npoi读写示例
Epplus:Nuget地址,项目地址
参考:Epplus上手
/// <summary>
/// 从Excel中第一列第二行起取数据,并查询oracle数据库中数据取ima70字段值写入同行的第二列
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button46_Click(object sender, EventArgs e)
{
//Opening an existing Excel file
FileInfo fi = new FileInfo(@"D:\Luxshare-ict2019\工作\SAP切换\料号对比\目标数据\Compare.xlsx");
using (ExcelPackage excelPackage = new ExcelPackage(fi))
{
//Get a WorkSheet by index. Note that EPPlus indexes are base 1, not base 0!
ExcelWorksheet firstWorksheet = excelPackage.Workbook.Worksheets[1];
Get a WorkSheet by name. If the worksheet doesn't exist, throw an exeption
//ExcelWorksheet namedWorksheet = excelPackage.Workbook.Worksheets["SomeWorksheet"];
If you don't know if a worksheet exists, you could use LINQ,
So it doesn't throw an exception, but return null in case it doesn't find it
//ExcelWorksheet anotherWorksheet =
// excelPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == "SomeWorksheet");
string cOString = "data source=topprod;user id=帐号;password=密码;Unicode=True";
OracleConnection con = new OracleConnection();
con.ConnectionString = cOString;
con.Open();
ExcelWorksheets worksheets = excelPackage.Workbook.Worksheets;//取Excel的所有Sheet
string part = string.Empty;
string ima70 = string.Empty;
string sqlStr = string.Empty;
foreach (var worksheet in worksheets)
{
int rowCount = worksheet.Dimension.End.Row;//sheet行数
for (int i = 2; i <= rowCount; i++)//从第2行取到最后一行
{
part= worksheet.Cells[i, 1].Value.ToString();
if (string.IsNullOrWhiteSpace(part))
{
continue;
}
sqlStr = $"SELECT IMA70 FROM LXXT.IMA_FILE WHERE IMA01 ='{part}'";
OracleDataAdapter oda = new OracleDataAdapter(sqlStr, con);
System.Data.DataSet ods = new System.Data.DataSet();
oda.Fill(ods);
//ods.Tables[0];
if (ods.Tables[0].Rows.Count>0)
{
worksheet.Cells[i, 2].Value = ods.Tables[0].Rows[0][0].ToString();//如果从数据库中取导致,写入第二列
}
System.Diagnostics.Debug.WriteLine($"{worksheet.Name}:{i.ToString()},{part}");//实时查看处理进度。
//if (i>4)
//{
// break;
//}
}
//break;
}
//Save your file
excelPackage.Save();
con.Close();
}