Solved

Formating excel when exporting from Silverlight 4.0

Posted on 2010-09-17
7
1,295 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
converting excel into labels 6 37
autofill formulas using macro 8 52
3,000 rows of comma separated values 14 31
Permutacion of 2 numbers COUNT 8 20
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

734 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