The intersection of Excel table and database

Keywords: Excel Database

If it is convenient, please pay attention to it. It must be closed. Thank you!

Recently, I have just learned how to store the data of Excel table in the database and how to write the content of the database into the excel table.
Step 1, first "reference", add reference
Microsoft.Office.Interop.Excel
Add to namespace
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
Store the data in the table into the database



           Excel.ApplicationClass xlsApp = new Excel.ApplicationClass();
            xlsApp.DisplayAlerts = false;//No reminders when running the macro
            xlsApp.Visible = true;//Make open excel display
            Excel.Workbook workbook = xlsApp.Workbooks.Add(true);
            string a = System.IO.Directory.GetCurrentDirectory();//Get the current working directory
   string fn=a+"\\Temp\\Cabinet.xlsx";//fn represents the cabinet. xlsx file under the Temp folder in the current directory
            //Open file
          workbook = xlsApp.Workbooks.Open(fn, 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 mySheet = workbook.Sheets[1] as Worksheet;
            //Determine whether the data in the table to be fetched is empty
            if (((Excel.Range)mySheet.Cells[row, 2]).Value2 != null){
            //Take the value of the first row and the second column of the table, whichever value you want.
            //Note: for multi column consolidation, only the first column is the data item of the consolidated column.
           string c = (((Excel.Range)mySheet.Cells[1, 2]).Value2.ToString());
           //Then the procedure of accessing c into database is omitted.
           /*If you want to write the values in the database to a table, change the last line of code to
          * mySheet.Cells[1, 2] = dr["dateItem"];//dr["dateItem"] is the data extracted from the database
          * /
           }
           //Save and close table
              workbook.Save();
            workbook.Close(Type.Missing, Type.Missing, Type.Missing);
            workbook = null;
            xlsApp.DisplayAlerts = true;
            xlsApp.Quit();
            xlsApp = null;
           //If you do not close it, you will not only have that work form, but also an empty form sheet1

But there is a problem with this one. There will be an empty table sheet1, although it has no impact on the program operation. But I don't know how this came from. It's OK to close it directly as above.
If anyone knows how sheet 1 comes, please let me know. Thank you

Posted by awiedman on Tue, 05 May 2020 04:54:31 -0700