Manipulating Excel Spreadsheats using C sharp

I need to manipulate Excel Spreadsheets in C sharp .net 2010. The sum total of what I need to do is loop through an excel spreadsheet, add three columns to the left of the original columns based on some calculations from the other spreadsheets plus some other functions. What is the latest and greatest way to do this? Currently I am opening the excel spreadsheet and reading the records I need and printing them to screen with this function succefully:

    public static void parseData(string excelFileName)
    {
        try
        {
            //Workbook workBook = _excelApp.Workbooks.Open(excelFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //Worksheet wkSheet = (Worksheet)_excelApp.Worksheets[1];
            //OLEObject oleObject = wkSheet.OLEObjects(Type.Missing);
            using (OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\""))
            {
                connection.Open();
                OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [Sheet1$]", connection);
                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

                objAdapter1.SelectCommand = objCmdSelect;
                objAdapter1.Fill(ds);
            }

            foreach (System.Data.DataTable myTable in ds.Tables)
            {
                //xmlResultSet.Append("<TemplateData>");                                        
                foreach (DataRow myRow in myTable.Rows)
                {
                    //Console.WriteLine("Ca0mon, please work :{0}", myRow);
                    Console.WriteLine("This thing better Work-- MRN: {0}| Patient Name: {1}| Address: {2}| CSSV: {3}", myRow[0], myRow[1], myRow[2], myRow[3]);
                }
            }

        }
        catch(Exception ex)
        {
        }
    }

Any advice on how to approach this with the latest and greatest 开发者_Python百科.net Tools? Thanks for your help.

You can also use OpenXML Framework to manipulate XML based Office documents. I think it's better to use this approach when you're sure that your clients only have Office 2007 or 2010.

The approach by using Microsoft.Office.Interop.Excel is dangerous because you link your code only to your office version. It's better to write your code using LateBinding and support multiple Office Versions.

The third approach, yours, is working but I think it's only practicable in simple scenarios.

With your Office installation, opt to install the Interop libraries. After you have done this, go to Add Reference, click on the COM tab, and add Microsoft Excel 12.0 Objects library.

Use the namespace using Microsoft.Office.Interop.Excel;

You should then go through a tutorial such as this one on MSDN.

You can use open source libraries such as NPOI https://npoi.codeplex.com/ Or paid products such as Aspose.Cells, Syncfusion Excel library, Softartisan Excel library etc. The advantage of these libraries is that you don't need MS Office installed on the server.