Tutorial How To Connect Excel Server From Scratch VBA By Delphi

Case 1.

From Component Palete/panel, drag from office's server tab, components;
TExcelApplication as oXL,
TExelWorkbooks as oWB,
TExcelWorksheet as oSheet

on the declaration section, declare these procedures as public

 public
    procedure conXL;
    procedure disconXL;
    procedure bindXL;

procedure bindXL;
begin
 oXL.Workbooks.Add (EmptyParam, lcid);
 oWB.ConnectTo (oXL.ActiveWorkbook);
 oSheet.ConnectTo (oWB.Worksheets [1] as _Worksheet);
end;

procedure disconXL;
begin
 oXL.ScreenUpdating[lcid] := True;

 oSheet.Disconnect;
 oWB.Disconnect;
 oXL.Disconnect;
end;

procedure conXL;
begin
 oXL.ConnectKind:=ckRunningOrNew;
 oXL.Connect;
 lcid := GetUserDefaultLCID;
 oXL.Visible[lcid]:=True;
end;


the call it on implementation section, such as this procedure below for example;

procedure TmnuFRM.prnCOL;
var
 //... declare variables to be use later
 xrow:integer;
 npos:integer;
 
begin

 //... declared variables initiation/assigning value

 //... call excel server object
 conXL;

 //... make a binding to server object
 bindXL;


 //... form header
 oSheet.Range['A1','A1'].Value:='HEADER LINE 1';
 oSheet.Range['A2','A2'].Value:='HEADER LINE 2';
 oSheet.RAnge['A1','A2'].Font.Bold:=true;


 xrow:=5;
 npos:=xrow;
 oSheet.Range['A'+trim(intToStr(xrow)),'A'+trim(intToStr(xrow))].Value:='Category';
 oSheet.Range['B'+trim(intToStr(xrow)),'B'+trim(intToStr(xrow))].Value:='Class';
 oSheet.Range['C'+trim(intToStr(xrow)),'C'+trim(intToStr(xrow))].Value:='This Year';
 oSheet.Range['E'+trim(intToStr(xrow)),'E'+trim(intToStr(xrow))].Value:='Last Year';
 inc(xrow);
 oSheet.Range['C'+trim(intToStr(xrow)),'C'+trim(intToStr(xrow))].Value:='Qty';
 oSheet.Range['D'+trim(intToStr(xrow)),'D'+trim(intToStr(xrow))].Value:='Sales';
 oSheet.Range['E'+trim(intToStr(xrow)),'E'+trim(intToStr(xrow))].Value:='Qty';
 oSheet.Range['F'+trim(intToStr(xrow)),'F'+trim(intToStr(xrow))].Value:='Sales';

 //format ['A5','F6']
 with oSheet.Range['A'+trim(intToStr(npos)),'F'+trim(intToStr(xrow))] do
 begin
   Select;
   Font.Bold := True;

   With Borders[xlEdgeLeft] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   end;

   With Borders[xlEdgeTop] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;

   With Borders[xlEdgeBottom] do
   begin
     LineStyle := xlDouble;
     Weight := xlThick;
     ColorIndex := xlAutomatic;
   End;

   With Borders[xlEdgeRight] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlInsideVertical] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
 end;

 with oSheet.Range['C'+trim(intToStr(npos)),'F'+trim(intToStr(npos))] do
 begin
    HorizontalAlignment := xlCenter;
    VerticalAlignment := xlBottom;
 end;
 oSheet.Range['C'+trim(intToStr(npos)),'D'+trim(intToStr(npos))].MergeCells:=true;
 oSheet.Range['E'+trim(intToStr(npos)),'F'+trim(intToStr(npos))].MergeCells:=true;
 with oSheet.Range['C'+trim(intToStr(npos)),'F'+trim(intToStr(npos))] do
 begin
   Select;
   With Borders[xlEdgeLeft] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlEdgeTop] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlEdgeBottom] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlEdgeRight] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
   With Borders[xlInsideVertical] do
   begin
     LineStyle := xlContinuous;
     Weight := xlThin;
     ColorIndex := xlAutomatic;
   End;
end;

with oSheet.Range['A'+trim(intToStr(npos)),'F'+trim(intToStr(xrow))] do
begin
  Select;
  With Interior do
  begin
    ColorIndex := 15;
    Pattern    := xlSolid;
  End;
end;

inc(xrow);
npos:=xrow;

....some code and calculation here....


//format rows
with oSheet.Range['A'+trim(intToStr(npos)),'F'+trim(intToStr(xrow))] do
begin
  Select;
  With Borders[xlEdgeLeft] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeTop] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeBottom] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeRight] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlInsideVertical] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlInsideHorizontal] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
end;

//display total
inc(xrow);

oSheet.Range['A'+trim(intToStr(xrow)),'A'+trim(intToStr(xrow))].Value:='Ttl Class';
xcol:=ord('A')+1;
for x:=1 to ttlLST.Count do
begin
  colTMP:=chr(xcol+x-1)+trim(intToStr(xrow));
  ttlLST.Strings[x-1]:='0';
end;
with oSheet.Range['C'+trim(intToStr(xrow)),'F'+trim(intToStr(xrow))] do
begin
  FormulaR1C1:='=SUM(R[-'+trim(intToSTr(xrow-npos-1))+']C:R[-1]C)';
end;

//format total
with oSheet.Range['A'+trim(intToStr(xrow)),'F'+trim(intToStr(xrow))] do
begin
  Select;
  HorizontalAlignment := xlRight;
  VerticalAlignment   := xlBottom;
  WrapText            := False;
  Orientation         := 0;
  AddIndent           := False;
  IndentLevel         := 0;
  ShrinkToFit         := False;
  ReadingOrder        := xlContext;
  MergeCells          := False;
  Font.Bold           := True;
  With Interior do
  begin
    ColorIndex        := 15;
    Pattern           := xlSolid;
  End;
end;

with oSheet.Range['C'+trim(intToStr(xrow)),'F'+trim(intToStr(xrow))] do
begin
  Select;
  With Borders[xlEdgeLeft] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeTop] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeBottom] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlEdgeRight] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
  With Borders[xlInsideVertical] do
  begin
    LineStyle := xlContinuous;
    Weight    := xlThin;
    ColorIndex:= xlAutomatic;
  End;
end;

//... disconnect excel
disconXL;


end;
that's it. you will get an excel window with a worksheet formated.

Progress Dialog Form Wait For Long Proccess in C#

By perschluter.com

If you execute a time-consuming operation, it can cause your user interface to seem it has stopped responding even if it hasn’t.

When you have time-consuming operations you can use the BackgroundWorker class to show a alert form, displaying that your application is in progress. Create a BackgroundWorker and listen for events that report the progress of your operation and signal when your operation is finished.



Create a new Windows Form Application.
The main form, Form1, is automatically created. So add another form to the project and name it “AlertForm”.

Add a label to the form, change the name to labelMessage.
Add a progressbar.

Add a button, change the name to buttonCancel and change the text to Cancel.

Add the following code to the Click event:

// Create a copy of the event to work with

EventHandler<EventArgs> ea = Canceled;

/* If there are no subscribers, ea will be null so we need to check

    * to avoid a NullReferenceException. */

if (ea != null)

    ea(this, e);

 

 

We need to create two properties so we can update the label text and the progressbar from Form1, so add the following properties:

 

public string Message

{

    set { labelMessage.Text = value; }

}

 

public int ProgressValue

{

    set { progressBar1.Value = value; }

}

 

 

 we are done with the AlertForm, so switch over to Form1 

 

Add one button and one label to the form, rename the button to buttonStart and the label to labelResult.

 

Add a BackgroundWorker to the form. In the properties window, change WorkerReportsProgress and WorkerSupportsCancellation to true.
In the properties window, click on events button (the yellow flash) and you will see that the backgroundworker has three events. We will use them all, so double-click on all of them. (After creating an event you have to go back to design view to create the next).

 

Scroll up to the beginning and add a field that will be used when we declare the AlertForm.

 

AlertForm alert;

 

Add the following code to the buttonStart_Click event

if (backgroundWorker1.IsBusy != true)

{

    // create a new instance of the alert form

    alert = new AlertForm();

    // event handler for the Cancel button in AlertForm

    alert.Canceled += new EventHandler<EventArgs>(buttonCancel_Click);

    alert.Show();

    // Start the asynchronous operation.

    backgroundWorker1.RunWorkerAsync();

}

Opens the AlertForm and starts the backgroundworker.
write the buttonCancel_Click event.
 If the user clicks on Cancel, the backgroundworker will cancel and the AlertForm window will close.

// This event handler cancels the backgroundworker, fired from Cancel button in AlertForm.

private void cancelAsyncButton_Click(object sender, EventArgs e)

{

    if (backgroundWorker1.WorkerSupportsCancellation == true)

    {

        // Cancel the asynchronous operation.

        backgroundWorker1.CancelAsync();

        // Close the AlertForm

        alert.Close();

    }

}

 

 

add code to the three event we added for the backgroundworker.

 

start with the DoWork event.

This is where you will do your time-consuming work. Deleting files, downloading files, or what it is that you want to do.

 

We also pass a value to BackgroundWorker.ReportProgress() that will be passed to the label and progressbar that we have in AlertForm.

 

 

BackgroundWorker worker = sender as BackgroundWorker;

 

for (int i = 1; i <= 10; i++)

{

    if (worker.CancellationPending == true)

    {

        e.Cancel = true;

        break;

    }

    else

    {

        // Perform a time consuming operation and report progress.

        worker.ReportProgress(i * 10);

        System.Threading.Thread.Sleep(500);

    }

}

 

 

When we call BackgroundWorker.ReportProgress, the backgroundworker will run ProcessChanged event.  

We change the text on the label in our main form, and we pass values over to the label and progressbar in AlertForm.

 

 

// Show the progress in main form (GUI)

labelResult.Text = (e.ProgressPercentage.ToString() + "%");

// Pass the progress to AlertForm label and progressbar

alert.Message = "In progress, please wait... " + e.ProgressPercentage.ToString() + "%";

alert.ProgressValue = e.ProgressPercentage;

 

 

 the RunWorkerCompleted event 

 

lets add code that changes our label text to Canceled if the user cancels, Done when it is completed or shows an error message if something went wrong.
And finally we close the AlertForm window.

 

if (e.Cancelled == true)

{

    labelResult.Text = "Canceled!";

}

else if (e.Error != null)

{

    labelResult.Text = "Error: " + e.Error.Message;

}

else

{

    labelResult.Text = "Done!";

}

// Close the AlertForm

alert.Close();

 

 

 

Splash screen/form on C# - By Tom Clement -

http://www.codeproject.com/Articles/5454/A-Pretty-Good-Splash-Screen-in-C

A good splash screen will:

• Run on a separate thread
• Fade in as it appears, and fade out as it disappears
• Display a running status message that is updated using a static method
• Display and update a predictive self-calibrating owner-drawn smooth-gradient progress bar
• Display the number of seconds remaining before load is complete

We start with the creation of a simple splash screen, followed by the code changes required for the addition of each feature. You can skip to the bottom of the article to see the complete source code. I've also included a small test project in the download that demonstrates the splash screen.

Start out by creating a Windows Forms project. Name it SplashScreen. Rename Form1.cs to SplashScreen.cs.

Because the splash screen will only need a single instance, you can simplify your code by using static methods to access it. By just referencing the SplashScreen project, a component can launch, update or close the splash screen without needing an object reference. Add the following code to SplashScreen.cs: 

static SplashScreen ms_frmSplash = null;
// A static entry point to launch SplashScreen.
static public void ShowForm()
{
  ms_frmSplash = new SplashScreen();
  Application.Run(ms_frmSplash);
}
// A static method to close the SplashScreen
static public void CloseForm()
{
  ms_frmSplash.Close();
}

A splash screen displays information about your application while it is loading and initializing its components. If you are going to display any dynamic information during that time, you should put it on a separate thread to prevent it from freezing when initialization is hogging the main thread.
Start by using the Threading namespace: 

using System.Threading;  

Declare a static variable to hold the thread:

static Thread ms_oThread = null; 

Now add a method to create and launch the splash screen on its own thread. Wait before returning to ensure that the static methods aren't called before the form exists:

 static public void ShowSplashScreen()
{
  // Make sure it is only launched once.
  if( ms_frmSplash != null )
    return;
  ms_oThread = new Thread( new ThreadStart(SplashScreen.ShowForm));
  ms_oThread.IsBackground = true;
  ms_oThread.SetApartmentState(ApartmentState.STA);
  ms_oThread.Start();
  while (ms_frmSplash == null || ms_frmSplash.IsHandleCreated == false)
  {
    System.Threading.Thread.Sleep(TIMER_INTERVAL);
  }
}Now ShowForm() can be made private, since the form will now be shown using ShowSplashScreen(). 

// A static entry point to launch SplashScreen.
static private void ShowForm()

Prize winner in Competition "C# Oct 2003"

Download source and demo - 34.5 KB