Cell/Range Formatting

[Vidya Jayakumar]

Thank you for your Responses.

Attached excel file comes in as a result of using ImportDatatable (DB -> Datatable-> Excelfile via ImportDatatable)

1)In TotalsByFCE Tab A2,A7,A8,A14,A22,A21,A27 values to be left Justified.

2)Rest of the A cell values to be center justified.

3)If you look at the rest of the cells in the same tab, like in column C - C3, c15 and C29 has an amount in them .
Rest of the cells in the same column dont.
When i do
Dim range1 As IRange = sheet1.
Range(3, 3, 29, 9)
range1.NumberFormat = "###,##"
is the result of the values in that range1 in TotalsByFCE Tab
Some cells in that range1 dont have any numbers.
I want them to be formatted as "$ -" similar to doing in excel right click on a cell ->Format cells->Number tab-> Accounting->0 decimal places and symbol as $

How do i achieve this thru Sync?
Basically the format of these cells were General. a $ 5,512,000 number was like 5512000. then i am formatting like above.

4)How do i have a thick border set as outline to TotalsByFCE tab for cells in range (1,1,9,29),
and slightly thin inside this range?
I have done this manually for your illustration purpose. How do i achieve this in Sync.

5)In Other Agency Performers Tab Column B basically is of format type General
I am doing a
Dim amtrange2 As IRange = sheet3.Range(2, 8)
amtrange2.NumberFormat = "###,##"

The format does not change at all Why? It doesn't have any comma or $ sign







Qer_Reports8_ec4968b8.zip

[Yavanaarasi G]

Hi Vidhya,

Thank you for your interest in Essential XlsIO.

1.Left Justified:

You can align the text as left justified by setting the HorizontalAlignment property as HAlignLeft.

Here is the code snippet:

[VB]
'LeftAlignment
Sheet1.Range("A2").HorizontalAlignment = ExcelHAlign.HAlignLeft

2.center justified:

You can also align the text as centre justified by setting the HorizontalAlignment property as HAlignCenter.

Here is the code snippet:

Sheet1.Range("B1:I29").HorizontalAlignment = ExcelHAlign.HAlignCenter



3.Formatting with $ sign:

XlsIO provide support for formatting the value with $ sign by setting the number format as "$#,###,000". As per your requirements in the sheet TotalsByFCE I have formatted the range with the below format:


[VB]
Sheet1.Range(3, 3, 29, 9).NumberFormat = "$#,###,000"


4.Set border as thick:

You can set the border as thick by setting the options in the ExcelBordersIndex as below:


 Sheet1.Range(1, 1, 9, 29).Borders(ExcelBordersIndex.EdgeBottom).LineStyle = ExcelLineStyle.Thick
            Sheet1.Range(1, 1, 9, 29).Borders(ExcelBordersIndex.EdgeLeft).LineStyle = ExcelLineStyle.Thick
            Sheet1.Range(1, 1, 9, 29).Borders(ExcelBordersIndex.EdgeRight).LineStyle = ExcelLineStyle.Thick
            Sheet1.Range(1, 1, 9, 29).Borders(ExcelBordersIndex.EdgeTop).LineStyle = ExcelLineStyle.Thick


5.Formatting with comma:
As per your requirements I have formatted the Column B in the sheet Other Agency Performers with comma by using the number format property:


Sheet2.Range("B2:B8").NumberFormat = "###,##"


Here is the sample for your reference:

http://www.syncfusion.com/development/uploads/XlsIO_Win_CellFormat_ccee9d8c.zip


Please try this and let me know if this helps.

Regards,
G.Yavana




            

[Vidya Jayakumar]

Hi,

Thank you for your help. Everything worked like we wanted it to. I couldn't get one aspect working. Please recall my 3rd Question:-

3)If you look at the rest of the cells in the same tab, like in column C - C3, c15 and C29 has an amount in them .
Rest of the cells in the same column dont.
When i do
Dim range1 As IRange = sheet1.
Range(3, 3, 29, 9)
range1.NumberFormat = "###,##"
is the result of the values in that range1 in TotalsByFCE Tab
Some cells in that range1 dont have any numbers.
I want them to be formatted as "$ -" similar to doing in excel right click on a cell ->Format cells->Number tab-> Accounting->0 decimal places and symbol as $

Yesterday I tried
Dim sheet1range As IRange = sheet1.Range(2, 4, dtrowcount, dtcolumncount)
sheet1range.NumberFormat = "$ ####,##,0"

Result is as in the TotalsbyFCE tab. Cells with no numbers are replaced by my format $0 as you can see from the file i have attached.
I want it to be like $ -
for Illustration purpose i have done that in column D, TotalsbyFCE tab.
Is there anyway to format the excel cell into accounting format just like we do it in Excel
(similar to doing in a excel file - right click on a cell ->choose Format cells->select Number tab-> select Accounting->0 decimal places and symbol as $)
this aspect is very critical for us,
Can this be achieved thru Sync?

I appreciate your help on this.

Vidya





Qer_Reports_Final_51f06dd0.zip

[Yavanaarasi G]

Hi Vidhya,

Thank you for your providing the information.

You can format the cells like [Format cells->select Number tab-> select Accounting->0 decimal places and symbol as $] as below:

[VB]

//Format cells like Accounting $- 

Sheet1.Range(4, 3, 28, 3).NumberFormat = "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""_);_(@_)"

Also you can format the cells c3,c15,c29 like in your attached file by using the below code snippet:

//Format rest of the cells c3,c15,c29 :

Sheet1.Range("C3").NumberFormat = "$ #,###,##0"
Sheet1.Range("C15").NumberFormat = "$#,###,000"
Sheet1.Range("C29").NumberFormat = "$#,###,000"

If you want to format the cell as "$0" you can set the format as below:

//Format the cell like $0

Sheet1.Range(1, 4, 28, 4).NumberFormat = "$ #,###,##0"

Note that for any formatting in XlsIO you can use the custom format type which is specified in Ms Excel.

Here is the sample for your reference:

http://www.syncfusion.com/development/uploads/XlsIO_Win_CellFormat_8aee3b01.zip

Please try this and let me know if this helps.

Regards,
G.Yavana

[Vidya Jayakumar]

Thank you so much. that worked on first try.

I was trying to understand the format.I see that in your number format "string" if the number coming in is regular number ("_(""$""* #,##0_);) then format it as regular number itself("_(""$""* #,##0_);) else format it as (_(""$""* ""-""_);)
Question:-What does the last part of String mean "_(@_)"
If you will explain, i can learn to format any excel formatting myself right away.

Vidya


>Hi Vidhya,

Thank you for your providing the information.

You can format the cells like [Format cells->select Number tab-> select Accounting->0 decimal places and symbol as $] as below:

[VB]

//Format cells like Accounting $- 

Sheet1.Range(4, 3, 28, 3).NumberFormat = "_(""$""* #,##0_);_(""$""* (#,##0);_(""$""* ""-""_);_(@_)"

Also you can format the cells c3,c15,c29 like in your attached file by using the below code snippet:

//Format rest of the cells c3,c15,c29 :

Sheet1.Range("C3").NumberFormat = "$ #,###,##0"
Sheet1.Range("C15").NumberFormat = "$#,###,000"
Sheet1.Range("C29").NumberFormat = "$#,###,000"

If you want to format the cell as "$0" you can set the format as below:

//Format the cell like $0

Sheet1.Range(1, 4, 28, 4).NumberFormat = "$ #,###,##0"

Note that for any formatting in XlsIO you can use the custom format type which is specified in Ms Excel.

Here is the sample for your reference:

http://www.syncfusion.com/development/uploads/XlsIO_Win_CellFormat_8aee3b01.zip

Please try this and let me know if this helps.

Regards,
G.Yavana

[Yavanaarasi G]

Hi Vidhya,

Last part of String mean "_(@_)":

The last part of the string "-(@_)" is used to display the actual text. You can either use @ or omit the last section. It is the custom format that is used for "Accounting type" in Ms Excel. As I mentioned earlier any formatting in XlsIO can be applied by setting the custom format type which is specified in Ms Excel. You can set the format in the NumberFormat Property.

Regards,
G.Yavana