Solved

Formating excel when exporting from Silverlight 4.0

Posted on 2010-09-17
7
1,256 Views
Last Modified: 2013-11-12
Hi,

I have made a sample project in which I export some data to an Excel workbook. I have most of the things working but I am struggling with the following:

Aligning the text in cells
Setting the font family in the cells

I have attached the code and I would be greatful if anyone could have a look and fix it for me.

Best regards
RTSol
using System;

using System.Collections.Generic;

using System.Linq;

using System.Net;

using System.Windows;

using System.Windows.Controls;

using System.Windows.Documents;

using System.Windows.Input;

using System.Windows.Media;

using System.Windows.Media.Animation;

using System.Windows.Shapes;

using System.Windows.Interop;

using System.Runtime.InteropServices.Automation;



namespace excelTest

{

    public partial class MainPage : UserControl

    {

        public MainPage()

        {

            InitializeComponent();

        }



        private void Button_Click(object sender, RoutedEventArgs e)

        {

            string Title = "Hello from Silverlight";



            dynamic excel = AutomationFactory.CreateObject("Excel.Application");

            excel.Visible = true;



            dynamic workbook = excel.workbooks;

            workbook.Add();

            dynamic sheet = excel.ActiveSheet;

            dynamic range;

            range = sheet.Range("A1:G1");

            range.Merge(true);

            range.Font.Size = 14;

            range.Font.Bold = true;



            //range.Font.Family = "Verdana";

            range.Font.ColorIndex = 49;

            range.Interior.ColorIndex = 36;



            //range.center = true;

            //range.Style.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;

            range.HorizontalAlignment = excel.xlCenter;



            range.Value = Title;

            range = sheet.Range("A2");

            range.Value = "100";

            range = sheet.Range("A3");

            range.Value = "50";

            range = sheet.Range("A4");

            range.Formula = "=@Sum(A2:A3)^2 + 4000";

            range.Calculate();



            range = sheet.Range("C3");

            range.Select();

        }

    }

}

Open in new window

0
Comment
Question by:RTSol
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 10

Expert Comment

by:borgunit
ID: 33701437
Try your font and alignment changes per cell and not range. Just index the range and set ie
For X = ? to ?

cells(X,Y).font.your-setting....

You get the idea
0
 
LVL 6

Expert Comment

by:TonySt
ID: 33701685
Why dont you just create another sheet in the workbook like the one you are exporting to and format it the way you want.  Make sure there is no data or formulas in it, You just want the cell formats. Then right click on the data sheet you export to and select "view code" enter in the following;

Private Sub Worksheet_Activate()
With Sheets("SheetFix")
        .Range("A1:M64").Copy
    End With
        Me.Range("A1").PasteSpecial xlPasteFormats
    Application.CutCopyMode = False
    Me.Range("M64").Select
End Sub

the code has to be placed in the "sheet activate" section and will fire off each time the sheet activates copying the format only from the SheetFix sheet.
0
 

Author Comment

by:RTSol
ID: 33702035
Hi,

The problem with the font family is solved - changed font.family to font.name. This could be done on the range itself.

Now I need to set the text alignment in the cell. I tried this

            range.Font.Name = "Verdana"; //Works fine
            range.cells(1, 1).HorizontalAlignment = excel.xlCenter; //Still failing to center the text in the cell

but no alignment happens. Unfortunately there is no intellisense and I have not worked with the AutomationFactory before.

Can you help?

About your comment TonySt - doesn't that require that this template workbook is present with the client?

Best regards
RTSol

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Expert Comment

by:TonySt
ID: 33703610
It only requires that the code and "sheetFix" sheet is present inside the template workbook.
0
 
LVL 33

Expert Comment

by:Norie
ID: 33709533
Is the reason you aren't getting Intellisense because you haven't got a reference to Excel.Interop?

Is that intentional?

Couldn't you add the reference while developing the code?

Then you should get the Intellisense, Object Browser etc.

If you can't have the reference in the final version just remove it and change everything to late-binding, which I assume is what you are currently doing.

If this is incorrect please excuse my ignorance but when I'm automating Excel from C# I always have the reference.

Don't know how I'd get by without it.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 33709893
Right, just found out why you aren't getting Intellisense, you are using late-binding.

That could very well be the root of the alignment problem - xlCenter is an Excel VBA constant which doesn't exist in the context your code is in.

So I tried this, which seemed to work.

  range.HorizontalAlignment = -4108;

-4108 is the value of the constant, which I found in the Excel Object Library.

Now if only I could find out the code for closing the form/page with a button.
0
 

Author Closing Comment

by:RTSol
ID: 33710571
Thanks, the Excel Object Library was a good tip. Now my code is working fine.
I will take a look at programming against Excel more seriously some time. Right now I just need to open a workbook from Silverlight.
Again - thanks a lot!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now