Re: How to Set the column of excel as bold while importing from datatable - C# Discussion Boards

Re: How to Set the column of excel as bold while importing from datatable

26-Sep-13 3:37

I am exporting a datatable to EXCEL . i want to set the font of excel columns as bold and some colour. i was using something like

Excel.Application Obj_Excel_App = new Excel.ApplicationClass();
Excel.Workbook Obj_Excel_ExcelWork = Obj_Excel_App.Workbooks.Add(Type.Missing);
Excel.Range obj_Range;
obj_Range = Obj_Excel_App.get_Range("A1", "E1");
obj_Range.Font.Bold = true;

but here i have to hardcode the range , i dont want to do that.plz help

Richard MacCutchan26-Sep-13 4:31

Richard MacCutchan

26-Sep-13 4:31

You could create a small helper method or class that converts a row and column number into A1 style notation.

Veni, vidi, abiit domum

Re: How to Set the column of excel as bold while importing from datatable

Chris Quinn26-Sep-13 5:09

Chris Quinn

26-Sep-13 5:09

You can refer to a specific column, or set of columns, using the Columns collection and set its properties

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Re: How to Set the column of excel as bold while importing from datatable

superselector26-Sep-13 19:21

Re: How to Set the column of excel as bold while importing from datatable

26-Sep-13 19:21

Actually i want to get the range of excel and set it colour and font

currently i am doing like this

Microsoft.Office.Interop.Excel.Range last = excelApp.Cells.SpecialCells(Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Microsoft.Office.Interop.Excel.Range range = excelApp.get_Range("A1", last);

range.Font.Bold = true;
range.Font.Size = 12;
range.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
range.Cells.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);

but it actually set the colour of all the rows , i just want to do it for first row which is my column header

Chris Quinn26-Sep-13 21:41

Chris Quinn

26-Sep-13 21:41

Your original post said that you wanted to set the attributes of a column, not the row headers.

To find the last used cell in a row, I have used this code (VBA, but you should be able to work out what it is doing.

Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long

Set WS = Worksheets("Sheet1")
With WS
    Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
    LastCellRowNumber = LastCell.Row
End With

=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================

Need help with datatable

superselector26-Sep-13 1:23

26-Sep-13 1:23

Hi i have a datatable having columns Col1 and Col2

Col1 Col2
A,B,C,D P,Q,R

I want to get the final output in datatable as

Col1 Col2
A P
B Q
C R
D

The first datatable will always have one row but the number of columns will vary . The final datatable should be exact replica of first datatable but the column values in first datatable separated by comma should be displayed as a row in final table. Please help.

Gergo Bogdan26-Sep-13 1:58

Gergo Bogdan

26-Sep-13 1:58

Is there a way to identify how many items does the first column have?
I can see that the P, Q, R values are for the second column, how would you know where to start splitting?

superselector wrote:

Col1 Col2
A P
B Q
C R
D

superselector26-Sep-13 2:09

26-Sep-13 2:09

The number of items may vary in a culumn.. but one thing is consistent that values will be seperated by comma,its just a sample i have given for understanding of the question. also the number of columns in first datatable is dynamic. it will change based on certain condition but irrespective of the number of columns i want to replicate this datatable and display those comma separated values in a row. The final datatable should have as many rows as the highest lenght of the splitted value

Gergo Bogdan26-Sep-13 3:03

Gergo Bogdan

26-Sep-13 3:03

OK, maybe I was not clear, lets have this example:

DataTable 1:
Column1, Column2, Column3

DataTable 2:
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8

The final result for you should be like below?

Column1, Column2, Column3
Value1,  Value2,  Value3
Value4,  Value5,  Value6
Value7,  Value8

if the above representation is OK, its quite simple, you just have to:
1. Add to the new DataTable the content of the first DataTable
2. Add a new row to the new DataTable, start to add the values
3. When you reached the length of the first DataTable (or first row of the current DataTable) you need to start a new row.

superselector26-Sep-13 3:31

26-Sep-13 3:31

Thanks , I was able to get it working.

Get Images from scanner in c#

Ron Nicholson26-Sep-13 3:41

Ron Nicholson

26-Sep-13 3:41

Ok, if I am understanding correctly, I would approach it like this.

Loop through each column
Convert the data from that column to a 'table' - I do this with some SSRS reports. There are a lot of links about doing this example 1, example 2.

I would try putting each into it's own table then combining them in the end, but it seems like there should be an easier way.

sanket16425-Sep-13 21:49

sanket164

25-Sep-13 21:49

I am Making Application in visual studio 2010 in windows form using C#

I want to get scan images from scanner.

How can i do???

Please help

Thank You
Sanket

Re: Get Images from scanner in c#

Pete O'Hanlon25-Sep-13 22:39

Pete O'Hanlon

25-Sep-13 22:39

A quick search found this[^] article here on CodeProject.

Chill _Maxxx_

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

Problem using DataContractSerializer

larsp77725-Sep-13 4:27

25-Sep-13 4:27

Hi! I trying to use DataContractSerializer to save objects with crossreference. It´s two lists of objects.

I doesn´t get a error but even though the file is created it does´t work to get it back.

Maybe it´s better to use BinaryFormatter?

Some of my code:

DataContractSerializer xs = new DataContractSerializer(typeof(List<Customer>));
        DataContractSerializer xs2 = new DataContractSerializer(typeof(List<Book>));

Book-class:

    [KnownType(typeof(FaktaBok))]
    [KnownType(typeof(BarnBok))]
    [KnownType(typeof(SportBok))]
    [DataContractAttribute()]
    public class Book
    {
        [DataMember]
        protected int isbn = 0;

        [DataMember]
        protected string titel = null;

        [DataMember]
        protected string author;

        [DataMember]
        protected int price;

        [DataMember]
        protected String isType = null;

        [DataMember]
        protected Kund biblioteksKund = null;

One derived class:

[DataContractAttribute()]
     public class SportBok : Bok
     {
         [DataMember]
         private string sport;


Serialize: 

 using (Stream s = File.Create(path))
                {
                    xs.WriteObject(s, CustomerList);
                }

                using (Stream s2 = File.Create(path2))
                {
                    xs2.WriteObject(s2, BookList);
                }


DeSerialize:

 if (File.Exists(path))
 {
      using (Stream s = File.OpenRead(path))
       {
             CustomerList = (List<Kund>)xs.ReadObject(s);
       }
 }

if (File.Exists(path2))
{
      using (Stream s2 = File.OpenRead(path2))
      {
          BookList = (List<Bok>)xs2.ReadObject(s2);
      }
}

Member 424999726-Sep-13 20:22

Member 4249997

26-Sep-13 20:22

I have done some modification for your code -

[KnownType(typeof (FaktaBok))]
    [KnownType(typeof (BarnBok))]
    [KnownType(typeof (SportBok))]
    [DataContractAttribute()]
    public class Book
    {
        [DataMember] 
        public int IsBn = 0;

        [DataMember]
        public string Titel = null;

        [DataMember]
        public string Author;

        [DataMember]
        public int Price;

        [DataMember]
        public String IsType = null;

    }

    public void SerializerTestMethod()
        {
            List<Book> books = new List<Book>
                                   {
                                       new Book()
                                           {
                                               Author = "author1",
                                               IsBn = 1,
                                               IsType = "istype1",
                                               Price = 10,
                                               Titel = "booktitle1"
                                           },
                                       new Book()
                                           {
                                               Author = "author2",
                                               IsBn = 2,
                                               IsType = "istype2",
                                               Price = 20,
                                               Titel = "booktitle2"
                                           }
                                   };
            string writeTo = @"FilePath";

            DataContractSerializer xs2 = new DataContractSerializer(typeof (List<Book>));

            using (Stream s2 = File.Create(writeTo))
            {
                xs2.WriteObject(s2, books);
            }
            List<Book> outBook = null;
            if (File.Exists(writeTo))
            {
                using (Stream s2 = File.OpenRead(writeTo))
                {
                    outBook = (List<Book>) xs2.ReadObject(s2);
                }
            }
            
        }

}

}

larsp77726-Sep-13 21:53

26-Sep-13 21:53

Well, the reason I didn´t even get an error was because the Form_Load didn´t fire.

But now that I fixed that I get "unable to serilalize" due to elements not being closed or something like that.

The application is a library and customers can lend books.
What I do is save a list of books in a customer objekt so you know what books the customer borrowed.

What exactly is your code doing?

Member 424999726-Sep-13 23:26

Member 4249997

26-Sep-13 23:26

As per the subject, problem using DataContractSerializer, I have updated your contract with public properties. And the DataContract is getting serialized.

larsp77727-Sep-13 1:10

27-Sep-13 1:10

The only thing I can see you have changed is that you made the SerializerTestMethod.

Did you change anything else?

Member 424999727-Sep-13 2:00

Member 4249997

27-Sep-13 2:00

When using DataContract it is advisible to use Public properties instead of protected.

That is my other change ... Following is the serialized datacontract with sample data.

XML

<ArrayOfBook xmlns="http://schemas.datacontract.org/2004/07/TestSampApp" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <Book>
        <Author>author1</Author>
        <IsBn>1</IsBn>
        <IsType>istype1</IsType>
        <Price>10</Price>
        <Titel>booktitle1</Titel>
    </Book>
    <Book>
        <Author>author2</Author>
        <IsBn>2</IsBn>
        <IsType>istype2</IsType>
        <Price>20</Price>
        <Titel>booktitle2</Titel>
    </Book>
</ArrayOfBook>

larsp77727-Sep-13 2:02