Solved

Formating excel when exporting from Silverlight 4.0

Posted on 2010-09-17
7
1,304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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!

 
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 34

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 34

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

726 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