Excel NPOI读写示例

来自于 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();

    }