Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Formating excel when exporting from Silverlight 4.0

Posted on 2010-09-17
7
Medium Priority
?
1,322 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 36

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 36

Accepted Solution

by:
Norie earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

564 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