Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Formating excel when exporting from Silverlight 4.0

Posted on 2010-09-17
7
Medium Priority
?
1,315 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 35

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 35

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.

604 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