I am trying to get a form that was created in visual studio WPF C# to submit to a new excel workbook will save onto a shared network drive. I have done a bit of digging trying to find the best solution and I have came across NPOI but all of the solutions seem pretty complicated compared to what I need to do. Is there some easy resource that I can reference to simply create a workbook and insert data into specific cells -- then save?

The two related packages I have seen in NuGet are DotNetCore.NPOI and NPOI which is the one I should be using?

What I have tried so far is:

private void Button_Click(object sender, RoutedEventArgs e)
    {
        using (var fs = new FileStream("Result12345.xlsx", FileMode.Create, FileAccess.Write))
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet excelSheet = workbook.CreateSheet("Sheet1");
           
            workbook.Write(fs);

            MessageBox.Show("Form submitted successfully");
        }
    }

This gets outputted to : Project Folder \bin\Debug\net6.0-windows and it seems to create the workbook and save (assuming all I need to do is put in the path of the network drive in the file stream then that should be easy) but how do i insert data into cells specific cells?

CodePudding user response：

I have worked with Microsoft Excel via interop COM libraries and should be directly available within your WPF app by adding as reference.

First, in the solution explorer, open the references, right-click and add reference.

Then, pick the Office libraries you are interested in working with, now or future, such as other apps too.

At the top of whatever code, you will then add the "using" clauses

using Microsoft.Office.Interop.Excel;
using System;

And here is a sample code snippet so you have control of whatever active workbook, worksheet, looping through explicit rows/columns and also getting the text of a given cell.

    public void tryingExcel()
    {

        var SomeSampleFile = @"C:\Users\Public\SomeExcelFile.xlsx";
        //Start Excel and get Application object.
        var XL = new Microsoft.Office.Interop.Excel.Application();
        XL.DisplayAlerts = false;
        XL.Workbooks.Add();

        // _Workbook and _Worksheet are part of Microsoft.Office.Interop.Excel
        // via "using" clause at top of code
        _Workbook wb = XL.ActiveWorkbook;
        wb.Sheets.Add();

        _Worksheet ws = wb.ActiveSheet;

        ws.Cells[2, 1] = "Date/Time:";
        ws.Cells[2, 2] = DateTime.Now;

        for (var ir = 4; ir < 10; ir  )
            ws.Cells[ir, 2] = "testing "   ir;;


        for (var ir = 4; ir < 10; ir  )
            ws.Cells[ir, 4] = ws.Cells[ir, 2].Text.Trim();


        XL.ActiveWorkbook.SaveAs(SomeSampleFile, XlFileFormat.xlOpenXMLWorkbook,
                Type.Missing, Type.Missing, false, false,
                XlSaveAsAccessMode.xlNoChange,
                XlSaveConflictResolution.xlLocalSessionChanges,
                Type.Missing, Type.Missing, Type.Missing, false);
        XL.Quit();

    }

CodePudding user response：

I have figured it out like this:

private void Button_Click(object sender, RoutedEventArgs e)
    {
        
        using (var fs = new FileStream(@"\\ipaddress\sharename\Result12345.xlsx", FileMode.Create, FileAccess.Write))
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet excelSheet = workbook.CreateSheet("Sheet1");
            //define cell to insert into
            var cellTest = excelSheet.CreateRow(0).CreateCell(0);
            //set cell value 
            cellTest.SetCellValue("Hello?");
            //save the excel sheet
            workbook.Write(fs);
            MessageBox.Show("Form submitted successfully");
        }
    }

I guess I just didn't understand why I have to create a row / cell when they already exist. Rather than setting something like Cell(0,1).value = "Something"