viewpoint-particle

Author Topic: How to add color format to excel file  (Read 676 times)

BVVNAIDU937

  • Newbie
  • *
  • Posts: 10
How to add color format to excel file
« on: May 11, 2017, 06:11:25 am »
Hi Winbatch Tech Team,

We are doing one small project for convert csv to excel file,by using the below program am able to convert but i need some color format in that  converted excel file.could some one help me please.

CSV to Excel Program.

;////////////////////////////////////////////////////////////////////////
;//  WINBATCH - CSV File To Excel - Could be used with LAFF databases  //

; some standard pre-formats to practice with
xlRAF3DEffects1    = 13
xlRAF3DEffects2    = 14
xlRAFAccounting1   = 5
xlRAFAccounting2   = 6
xlRAFAccounting3   = 7
xlRAFAccounting4   = 17
xlRAFClassic1      = 1
xlRAFClassic2      = 14
xlRAFClassic3      = 2
xlNormal           = -4143
cCSV               = AskFileName("Select Comma Separated File",".\","CSV Files|*.csv|","*.csv",1)
cXLS               = StrReplace( StrUpper(cCSV), ".CSV",".XLS")
cText              = StrCat("Transferring %cCSV%",@CRLF,"TO %cXLS%")

IF FileExist(cXLS) Then  FileDelete(cXLS)
BoxOpen("Creating Excel WorkBook",cText)

oAPP               = ObjectOpen( "Excel.Application" )
oAPP.Visible       = @FALSE
oAPP.DisplayAlerts = @FALSE
oWK                = oAPP.workbooks

oWK.open(cCSV)
oACT               = oAPP.ActiveWorkbook
oWKS               = oAPP.Worksheets
oWS                = oAPP.Worksheets(1)

oWS.Activate
gosub AutoFit
oACT.SaveAs ( cXLS,xlNormal  , , , , ,@True)
ObjectClose(oWS)
oACT.Close()
ObjectClose(oACT)
ObjectClose(oWK)
ObjectClose(oAPP)
BoxShut()
IF FileExist(cXLS) Then  Message("Conversion Successful","%cXLS% Created.")
Exit

:AutoFit
oCell              = oWS.Cells(1,1)
oRegion            = oCell.CurrentRegion
oCols              = oRegion.Columns
oCols.AutoFit
oCell              = oWS.UsedRange
oCell.AutoFormat(:: Format=xlRAFClassic1)

ObjectClose(oRegion)
ObjectClose(oCell)
Return

td

  • Tech Support
  • *****
  • Posts: 2139
    • WinBatch
Re: How to add color format to excel file
« Reply #1 on: May 11, 2017, 09:42:11 am »
There are plenty of VBA examples of cell coloration on the Web. VBA is usually a straight forward conversion to  WIL syntax. Here's a very crude code snippet that sort of shows one approach:

Code: Winbatch
strXLS = "C:\Projects\Automation\RegTest\TestData\":"Cat320.xls"
objExcel = ObjectCreate("excel.application" )
objExcel.Visible          = 1  
objExcel.ScreenUpdating   = 1  
objExcel.UserControl      = 1
objExcel.DisplayAlerts    = 0
objExcel.Workbooks.Open(strXLS )

objWS = objExcel.Workbooks(1).Worksheets(1)
objWS.Activate()
xlSolid = 1
RangeStr = "A1:Z100"                          
objWS.Range(RangeStr).EntireColumn.Autofit
objWS.Range(RangeStr).Interior.ColorIndex = 3    ; Red
objWS.Range(RangeStr).EntireColumn.Autofit    
objWS.Range(RangeStr).Interior.Pattern = xlSolid
objExcel.ActiveWorkBook.Close(::savechanges= @False)
objExcel.quit
objExcel = 0

Here's a link to the MSFT documentation for the "interior" object:
https://msdn.microsoft.com/en-us/library/office/ff196598.aspx
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


BVVNAIDU937

  • Newbie
  • *
  • Posts: 10
Re: How to add color format to excel file
« Reply #2 on: May 11, 2017, 10:04:29 am »
Thanks for your support TD,

Could you please tell me where we can insert the above color code in below mentioned program.I'm bit confusing where i have to change.

My Program:

#DefineSubroutine udfCSVtoXLS(cCSV)
;////////////////////////////////////////////////////////////////////////
;//  WINBATCH - CSV File To Excel - Could be used with LAFF databases  //

; Some standard pre-formats to practice with
xlRAF3DEffects1    = 13
xlRAF3DEffects2    = 14
xlRAFAccounting1   = 4
xlRAFAccounting2   = 5
xlRAFAccounting3   = 6
xlRAFAccounting4   = 17
xlRAFClassic1      = 1
xlRAFClassic2      = 2
xlRAFClassic3      = 3
xlNormal           = -4143
;cCSV               = AskFileName("Select Comma Separated File",".\","CSV Files|*.csv|","*.csv",1)
cXLS               = StrReplace(cCSV, ".CSV",".xls")
cXLS               = StrReplace(cCSV, ".csv",".xls")
cText              = StrCat("Transferring %cCSV%", @CRLF, "to %cXLS%.")

ErrorMode(@Off)
oAPP               = ObjectOpen("Excel.Application")
ErrorMode(@Cancel)
If oAPP==0 || oAPP==@False Then
   Display(2, ApplName, "Excel 2003/2007 is not installed. Cannot export to .xls.")
   BoxShut()
   Return
Endif

If FileExist(cXLS)==@false Then
   ErrorMode(@Off)
   ChkA=FileDelete(cXLS)
   ErrorMode(@Cancel)
   If ChkA==@False Then
      Display(2, ApplName, "%cXLS% cannot be created. Check access rights or file may be open.")
      Return
   Endif
Endif

Display(2, ApplName, "Creating Excel WorkBook %cText%")

oAPP.Visible       = @FALSE
oAPP.DisplayAlerts = @FALSE
oWK                = oAPP.workbooks

oWK.open(cCSV)
oACT               = oAPP.ActiveWorkbook
oWKS               = oAPP.Worksheets
oWS                = oAPP.Worksheets(1)

oWS.Activate
gosub AutoFit

oACT.SaveAs (cXLS, xlNormal, , , , ,@True )
ObjectClose(oWS)
oACT.Close()
ObjectClose(oACT)
ObjectClose(oWK)
ObjectClose(oAPP)
BoxShut()
If FileExist(cXLS)==@True Then Display(2, ApplName, "Conversion successful: %cXLS% created.")
Return


:AutoFit
oCell              = oWS.Cells(1,1)
oRegion            = oCell.CurrentRegion
oCols              = oRegion.Columns
oCols.AutoFit
oCell              = oWS.UsedRange
oCell.AutoFormat(:: Format=xlRAFClassic2)
ObjectClose(oRegion)
ObjectClose(oCell)
Return
#EndFunction


#DefineSubroutine udfSortXLS()
file = "%Temp%\udfSortXLS.log"
xlAscending = 1
xlYes =1
xlTopToBottom = 1
xlSortNormal = 0
= 1

objExcel = ObjectCreate("Excel.Application")
objExcel.DisplayAlerts = @FALSE
objExcel.visible = @TRUE

objExcel.WorkBooks.Open(file)
objWork_Sheet =  objExcel.Activeworkbook.ActiveSheet


objWork_Sheet.Columns("C:C").Select
objWork_Sheet.Range("A1:E666").sort(:: Key1=objWork_Sheet.Range("b1"), Order1=xlAscending, Header=xlYes, OrderCustom=1, MatchCase=@FALSE, Orientation=xlTopToBottom, DataOption1=xlSortNormal )


Pause("Notice", "Sorted Ascending based on Column B")


objRangecol = 0
objRange = 0
objWork_Sheet = 0
objExcel.WorkBooks.Close()
objExcel.Quit()
objWork_Sheet = 0
objExcel = 0
Return

BVVNAIDU937

  • Newbie
  • *
  • Posts: 10
Re: How to add color format to excel file
« Reply #3 on: May 12, 2017, 08:50:40 am »
Hi TD,

Could you please help on above request

td

  • Tech Support
  • *****
  • Posts: 2139
    • WinBatch
Re: How to add color format to excel file
« Reply #4 on: May 12, 2017, 12:00:37 pm »
You need to access the Interior property  and it is a property of the Range object so, logically, you could use it when you have the Range object available.  The Cells method simply returns a range of all the cells in the spread sheet and you directly access the Range object in another place.   Some where around either of those locations would make sense. 
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


stanl

  • Pundit
  • *****
  • Posts: 630
Re: How to add color format to excel file
« Reply #5 on: May 13, 2017, 03:23:47 am »
If you are loading the .csv data into a new sheet as a table then want to assign a pre-exisiting format such as xlRAF3DEffects1, first access the .usedrange property then assign the format, otherwise as Tony replied use the .interior property.

BVVNAIDU937

  • Newbie
  • *
  • Posts: 10
Re: How to add color format to excel file
« Reply #6 on: May 13, 2017, 05:25:08 am »
Hi Stanl,

Could you please tell me the Pre-existing excel formats ,so that i can use those in above code and could you please help me to where i could changes in above code according to TD suggestion.

Thanks for your valuable support 


stanl

  • Pundit
  • *****
  • Posts: 630
Re: How to add color format to excel file
« Reply #7 on: May 13, 2017, 08:08:50 am »
Perhaps in your autofit section something like (again assuming the .csv is tabular) oXL = Excel Object, oWS=Curent worksheet

Code: Winbatch
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="MyTable"
oWS.Range("MyTable[#All]").Select
oWS.ListObjects("MyTable").TableStyle = "TableStyleMedium2"
oXL.ActiveWindow.DisplayGridlines = @False
oWS.Cells(1,1).Select()
 

BVVNAIDU937

  • Newbie
  • *
  • Posts: 10
Re: How to add color format to excel file
« Reply #8 on: May 16, 2017, 05:56:16 am »
Hi TD,

By using your give excel code is working fine but i'm not able to insert the "Conditional Formatting" feature in above code and i tried Microsoft example VBA scripts,couldn't able to write the code. Please find the below winbatch code.

Could you please help me

File= "C:\LOGS\":"ServerSpace.xls"
objExcel = ObjectCreate("excel.application" )
objExcel.Visible          = 0
objExcel.ScreenUpdating   = 1   
objExcel.UserControl      = 1
objExcel.DisplayAlerts    = 0
xlCellTypeAllFormatConditions = -4172
objExcel.Workbooks.Open(File)
objWS = objExcel.Workbooks(1).Worksheets(1)
objWS.Activate()
xlSolid =1
RangeStr1= "A1:G1"
RangeStr2 = "A2:G2"
RangeStr3 = "A3:G3"
RangeStr4= "A3:G3" 
objWS.Range(RangeStr1).EntireColumn.Autofit
objWS.Range(RangeStr1).Font.Name = "Calibri"
objWS.Range(RangeStr1).Font.Size = "12"
objWS.Range(RangeStr2).Font.Name = "Calibri"
objWS.Range(RangeStr2).Font.Size = "12"
objWS.Range(RangeStr3).Font.Name = "Calibri"
objWS.Range(RangeStr3).Font.Size = "12"
objWS.Range(RangeStr1).Font.FontStyle = " "
objWS.Range(RangeStr1).Interior.ColorIndex =4
objWS.Range(RangeStr1).Font.ColorIndex =1
objWS.Range(RangeStr2).Interior.ColorIndex =32
objWS.Range(RangeStr2).Font.ColorIndex =2
objWS.Range(RangeStr3).Interior.ColorIndex =32
objWS.Range(RangeStr3).Font.ColorIndex =2
objWS.Range(RangeStr1).EntireColumn.Autofit   
objWS.Range(RangeStr1).Interior.Pattern = xlSolid
objWS.Range(RangeStr2).Interior.Pattern = xlSolid
objWS.Range(RangeStr3).Interior.Pattern = xlSolid
objExcel.ActiveWorkbook.Save()
objExcel.quit
objExcel = 0

stanl

  • Pundit
  • *****
  • Posts: 630
Re: How to add color format to excel file
« Reply #9 on: May 16, 2017, 08:00:41 am »
Applying 'Conditional' formatting from scratch can be a bit difficult. I have always found it easier to create a template and set the formatting in the selected sheet or sheets, then just fill in the data and refresh.

BVVNAIDU937

  • Newbie
  • *
  • Posts: 10
Re: How to add color format to excel file
« Reply #10 on: May 17, 2017, 05:34:55 am »
Hi TD,

Could you please help on above request.Still i'n not able to implement "Conditional Formatting" in Excel.

Thanks in Advance.

td

  • Tech Support
  • *****
  • Posts: 2139
    • WinBatch
Re: How to add color format to excel file
« Reply #11 on: May 17, 2017, 09:02:44 am »
Perhaps you should consider Stan's suggestion and create a template.
"Success is a lousy teacher. It seduces smart people into thinking they can't lose."
  - Bill Gates


stanl

  • Pundit
  • *****
  • Posts: 630
Re: How to add color format to excel file
« Reply #12 on: May 18, 2017, 02:15:17 pm »
Another reason to consider a template is just to avoid re-writing code. I use WB for the compiler and have EXE's in multiple places. Almost everything lately involves working with Excel, and it is a big NO GO to have to re-write, re-compile and replace an EXE for the frequent requested changes involving formatting - especially when working with slicers, linked data or powerpivot.