Tician

C# and Excel

Jul 22nd, 2016
73
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
C# 3.08 KB | None | 0 0
  1. /* Welcome again. I have a new project and for this I need to get SQL, Microsoft Office Excel and SQL work together. But first things first: We have to add some reference so we can use Excel in C#.
  2. Open a new project and select "Project" at the top, then click "add reference". In "Com" you will find "Microsoft Excel xx.x Object Library". Select it with a tick. Now we are ready to start a little code: */
  3.  
  4. using System;
  5. using System.Collections.Generic;
  6. using System.Linq;
  7. using System.Text;
  8. using System.Threading.Tasks;
  9. using Excel = Microsoft.Office.Interop.Excel; //don't forget this, we need to add it. And for comfortability we define a short word
  10.  
  11. namespace ConsoleApplication1
  12. {
  13.     class Program
  14.     {
  15.         static void Main(string[] args)
  16.         {
  17.                         //some variables we need.
  18.             Excel.Application xlApp = null; //the variable for the application itself. We initislize it with "null", so we can use the variable later and don't get errors
  19.             Excel.Workbook xlWb; //something that defines all our Datasheets (the tables all together)
  20.             Excel.Worksheet xlWs; //Something for a single worksheet (table)
  21.  
  22.             try
  23.             {
  24.                 //initialisation for the Excel Process
  25.                 xlApp = new Excel.Application(); //an Object for our Excel-Application
  26.                 xlApp.Visible = true; //Or do you want an invisible back-ground excel in your taskmanager?^^
  27.                 xlApp.ScreenUpdating = true; //out Code will be slower, in a huge code we would see how the Cells fill up. Leave it false if you want to make it faster (But set it to true at the end of the code)
  28.  
  29.                 //Define some things in Excel itself we need
  30.                 var myCount = xlApp.Workbooks.Count;
  31.                 xlWb = xlApp.Workbooks.Add(System.Reflection.Missing.Value);
  32.                 xlWs = xlWb.ActiveSheet;
  33.  
  34.                 //That is it, at this point (+ catch and finally) we should have an exmpty Excel that opens and closes after we start the program
  35.                 /* Doing something with the cells is really simple, let's see some examples:
  36.  
  37.                 This writes "Hello" into the Cell B3. First number is the column (numbers), the second one is the row (letters). Of course you can use variables for the numbers which you can use in Loops or something
  38.                 xlWs.Cells[3, 2] = "Hello";
  39.                 */
  40.             }
  41.  
  42.             catch (Exception ex)
  43.             {
  44.                 String myErrorMessage = ex.Message;
  45.                 Console.WriteLine(myErrorMessage);
  46.                 Console.ReadKey();
  47.             }
  48.  
  49.             finally
  50.             {
  51.                 /*End Excel. Be careful if you don't have this, Excel will be stuck in your Taskmanager (even when you close the Application) and cause weird Errors when you open it too often without noticing. I am talking out of experience ;) */
  52.                 if (xlApp != null)
  53.                 {
  54.                     xlApp.Quit();
  55.                     Console.ReadKey();                
  56.                 }
  57.             }
  58.  
  59.         }
  60.     }
  61. }
Add Comment
Please, Sign In to add comment