How to add color format to excel file

Started by BVVNAIDU937, May 11, 2017, 06:11:25 AM

Previous topic - Next topic

BVVNAIDU937

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

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) Select
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
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

BVVNAIDU937

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

Hi TD,

Could you please help on above request

td

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. 
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

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

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

Perhaps in your autofit section something like (again assuming the .csv is tabular) oXL = Excel Object, oWS=Curent worksheet

Code (WINBATCH) Select

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

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

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

Hi TD,

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

Thanks in Advance.

td

Perhaps you should consider Stan's suggestion and create a template.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

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. 

mattcarl

I encountered this thread while tracking down another issue.
Thought others mind find it valuable.

The example given saves your file with an older *.xls  extension.
Inherent to that Excel standard, only 65536 rows are allowed.
I wanted/needed to output more... and thus needed the extension *.xlsx   

There is an easy solution:

Add a new variable called  "xlWorkbookDefault"  and set to = 51
xlNormal           = -4143
xlWorkbookDefault  = 51     ;    Workbook default   


Change your value for cXLS to have then longer extension
cXLS               = StrReplace( StrUpper(cCSV), ".CSV",".XLSX")


Change the value in you save as line...  remove xlNormal and replace with xlWorkBookDefault  (or value 51)
oACT.SaveAs ( cXLS, xlWorkBookDefault, , , , ,@True )   



Thats it.....  find other tidbits at: 
https://docs.microsoft.com/en-us/office/vba/api/Excel.XlFileFormat