Click here to Skip to main content
14,579,459 members
Rate this:
Please Sign up or sign in to vote.
See more:
I'm working on a Windows Form application with no C# experience but want to learn. I want to read an xlsx file, extract data, and write/output the selected data to another spreadsheet.
As you can see from my code, I'm manually writing to the spreadsheet, which is the opposite of what I'm trying to achieve.

I've checked some forums and some recommend Spire but I can't find the reference to add it to my project. I also found that oledb is a great option but I'm not sure how to write it without the long list of errors.

I'm using Visual Studio Community edition v 16.5.4
Any suggestion is appreciated.

What I have tried:

System.Threading.Thread.Sleep(5000);
OpenFileDialog ofd = new OpenFileDialog();
ofd.ShowDialog();
System.Threading.Thread.Sleep(10000);

//read excel file

{
    //Create COM Objects. Create a COM object for everything that is referenced
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\benjamin.johnson\Documents\CACE_Issues_List_2019-53.xlsx");
    Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    Excel.Range xlRange = xlWorksheet.UsedRange;

    int rowCount = xlRange.Rows.Count;
    int colCount = xlRange.Columns.Count;

    //iterate over the rows and columns and print to the console as it appears in the file

    for (int i = 1; i <= rowCount; i++)
    {
        for (int j = 1; j <= colCount; j++)
        {
            //new line
            if (j == 1)
                Console.Write("\r\n");

            //write the value to the console
            if (xlRange.Cells[i, j] != null && xlRange.Cells[i, j].Value2 != null)
                Console.Write(xlRange.Cells[i, j].Value2.ToString() + "\t");
        }
    }

    //cleanup
    GC.Collect();
    GC.WaitForPendingFinalizers();

    //release com objects to fully kill excel process from running in the background
    Marshal.ReleaseComObject(xlRange);
    Marshal.ReleaseComObject(xlWorksheet);

    //close and release
    xlWorkbook.Close();
    Marshal.ReleaseComObject(xlWorkbook);

    //quit and release
    xlApp.Quit();
    Marshal.ReleaseComObject(xlApp);

}
//write to excel file

{

    Excel.Application excelApp = new Excel.Application();
    if (excelApp != null)
    {
        //Excel.Application excelApp = new Excel.Application();
        Excel.Workbook excelWorkbook = excelApp.Workbooks.Add();
        Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets.Add();
        excelApp.Visible = true;

        excelWorksheet.Cells[1, 1] = "Opt/Req";
        excelWorksheet.Cells[1, 2] = "Type";
        excelWorksheet.Cells[1, 3] = "Accepted Date";
        excelWorksheet.Cells[1, 4] = "Bug#";
        excelWorksheet.Cells[1, 5] = "Title";
        excelWorksheet.Cells[1, 6] = "Subsystem";
        excelWorksheet.Cells[1, 7] = "Design";
        excelWorksheet.Cells[1, 8] = "Segment";
        excelWorksheet.Cells[1, 9] = "Release";

        excelApp.Columns.AutoFit();
        excelWorksheet.Name = "Issues";

        if (File.Exists("TestIssueTracker2020.xlsx"))
        {
            File.Delete("TestIssueTracker2020.xlsx");
        }

        excelApp.ActiveWorkbook.SaveAs("TestIssueTracker2020", Excel.XlFileFormat.xlWorkbookNormal);

        excelWorkbook.Close();
        excelApp.Quit();

        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorksheet);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelWorkbook);
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excelApp);
        GC.Collect();
        GC.WaitForPendingFinalizers();

        MessageBox.Show("Excel file created, you can find the file on your Documents folder");
    }
Posted
Updated 13-May-20 21:33pm
v3

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

I'd suggest to use EPPlus, because there's a lot of documentation: Getting Started · JanKallman/EPPlus Wiki · GitHub[^]. Do not forget to install NuGet package first: NuGet Gallery | EPPlus 5.1.2[^].

For Spire.XLS, you have to install this NuGet package: NuGet Gallery | Spire.XLS 10.4.9[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100