Answered by:
Set excel field color
Question

User585544258 posted
My program sends an email with an excel attachment. The program works just fine. I'm trying to enhance it by adding red to different fields.
I have 2 SQL tables. NewItem and ICSWNewItem. Currently, I'm just outputting NewItem Based on a batch number. Here is the code:
xlApp = New Excel.Application xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") Dim xlRange As Excel.Range = xlWorkSheet.Range("A1:T2000") xlRange.NumberFormat = "@" Dim xlRange2 As Excel.Range = xlWorkSheet.Range("U1:V2000") 'cost field xlRange2.NumberFormat = "$#,##0.00000" Dim xlRange3 As Excel.Range = xlWorkSheet.Range("W1:AB2000") xlRange3.NumberFormat = "@" Dim xlRange4 As Excel.Range = xlWorkSheet.Range("AC1:AC2000") 'salesprice field xlRange4.NumberFormat = "$#,##0.00000" Dim xlRange5 As Excel.Range = xlWorkSheet.Range("AD1:BV2000") xlRange5.NumberFormat = "@" Dim Conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ReportingConnectionString").ToString()) Dim Cmd As New SqlCommand("SELECT Batch, SetupType, UserComments, ICSPNewChange, ICSWNewChange, Prod, ProdChange as ProdSetup, Description1Change as Desc1Setup, Description2Change as Desc2Setup, whse,Prodcat, Statustype, ArpType, Vendno, ProdLine, VendProd,VendName, StdPack, StockLevel, AnnualUsage, Cost, (Cost*StockLevel) as ExtendedCost, VendQuote, QuoteExpDate, LeadTime, Custno, CustName,CustomerPart as CustomerStockCode, SalesPrice as Resale, OldPart, SXQuoteNo,ContractNumber, ContractExpDate,custblanket, UOM, Description1, Description2, UnitWeight, ExtDesc, QuoteAtt, CatalogAtt, ImageAtt, CutsheetAtt, ICMComments, ItemTeamComments, BOMComments, ROMComments, Requestor, ICM, ROM, BOM, ICMApproved, ItemTeamApproved, BOMApproved, ROMApproved, EnterDate, ICMDate, BOMDate, ROMDate, ItemTeamDate, Stage, MSDSfl, msdssheetno, msdschgdt, MSDSAtt, WebPageItem, ReplProd, ICMRejfl, BOMRejfl, ItemTeamRejfl, ItemTeamMember, NewItemKey, Record, cono FROM NewItem Where batch = @str1", Conn) Dim Str1 As SqlParameter = Cmd.Parameters.Add("@Str1", Data.SqlDbType.VarChar, 48) Str1.Value = Label19.Text Dim adapter As New SqlDataAdapter(Cmd) Cmd.CommandType = CommandType.Text Dim ds As New DataSet adapter.Fill(ds) For i = 0 To ds.Tables(0).Columns.Count  1 xlWorkSheet.Cells(1, i + 1) = ds.Tables(0).Columns(i).ColumnName Next For i = 0 To ds.Tables(0).Rows.Count  1 For j = 0 To ds.Tables(0).Columns.Count  1 xlWorkSheet.Cells(i + 2, j + 1) = _ ds.Tables(0).Rows(i).Item(j) Next Next Dim Str5 As String Str5 = CleanCat2(Label19.Text) Dim time As String time = CleanCat(Date.Now) FilePath = "D:\Batches\Batch" + Str5 + time + ".xls" xlApp.DisplayAlerts = False xlWorkSheet = xlWorkBook.ActiveSheet xlWorkBook.SaveAs(FilePath) xlWorkBook.Close() xlApp.Quit() releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorkSheet) xlWorkBook = Nothing xlApp = Nothing GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL") proc.Kill() Next
So, what I want to do now is somehow color a field red if it is different than the original(ICSWNewItem).I was thinking of 2 different ways to do this.
1. is to write all of the information to the excel file, then do the comparison afterwards, and then color the field red.
2. Compare the different tables first, and then change the code above to make the fields red as it outputs the information to the excel file.
Any help would be very much appreciated.
Thanks
Monday, January 18, 2010 2:00 PM
Answers

User585544258 posted
For those that want to know the way I completed this, here is the code. I put this code right after the ranges were created.
Dim Conn2 As New SqlConnection(ConfigurationManager.ConnectionStrings("ReportingConnectionString").ToString()) Dim Cmd2 As New SqlCommand("SELECT '0' as Batch, case when SetupType = 'Contract' then '1' else '3' end as SetupType, '1' as UserComments, case when ICSPNewChange = 'N' then '1' else '3' end as ICSPNewChange,case when ICSWNewChange = 'N' then '1' else '3' end as ICSWNewChange,case when ICSWNewItem.prod IS null then '1' else '0' end as Prod,case when newitem.Prod = newitem.prodchange then '0' else '1' end as ProdSetup,case when ICSPNewChange = 'N' then '1' else '3' end as Desc1Setup,case when ICSPNewChange = 'N' then '1' else '3' end as Desc2Setup,case when ICSWNewItem.whse IS null then '1' else '0' end as Whse,case when ICSWNewItem.prodcat = newitem.prodcat then '0' else '1' end as Prodcat,case when ICSWNewItem.Statustype = newitem.Statustype then '0' else '1' end as Statustype,case when ICSWNewItem.arptype = newitem.ArpType then '0' else '1' end as Arptype,case when ICSWNewItem.arpvendno = newitem.Vendno then '0' else '1' end as Vendno,case when ICSWNewItem.prodline = newitem.ProdLine then '0' else '1' end as Prodline,case when ICSWNewItem.vendprod = newitem.VendProd then '0' else '1' end as VendProd,case when ICSWNewItem.vendprod = newitem.VendProd then '0' else '1' end as VendName,case when ICSWNewItem.unitstnd = newitem.StdPack then '0' else '1' end as StdPack,case when ICSWNewItem.minthreshold = newitem.StockLevel then '0' else '1' end as StockLevel,case when ICSWNewItem.minthreshold = newitem.StockLevel then '0' else '1' end as AnnualUsage,case when ICSWNewItem.replcost = newitem.Cost then '0' else '1' end as Cost,'3' as ExtendedCost,case when ICSWNewItem.User3 = newitem.VendQuote then '0' else '1' end as VendQuote,case when ICSWNewItem.User8 = newitem.QuoteExpDate then '0' else '1' end as QuoteExpDate,case when ICSWNewItem.leadtmavg = newitem.LeadTime then '0' else '1' end as LeadTime,'3' as Custno,'3' as CustName,'3' as CustomerStockCode,'3' as Resale,'3' as OldPart,'3' as SXQuoteNo,'3' as ContractNumber,'3' as ContractExpDate,'3' as custblanket,case when ICSWNewItem.unitstock = newitem.UOM then '0' else '1' end as UOM,case when ICSPNewChange = 'N' then '1' else '3' end as Description1,case when ICSPNewChange = 'N' then '1' else '3' end as Description2,'3' as UnitWeight,'1' as ExtDesc,'1' as QuoteAtt,'1' as CatalogAtt,'1' as ImageAtt,'1' as CutsheetAtt,'1' as ICMComments,'1' as ItemTeamComments,'1' as BOMComments,'1' as ROMComments,'0' as Requestor,'0' as ICM,'0' as ROM,'0' as BOM,'0' as ICMApproved,'0' as ItemTeamApproved,'0' as BOMApproved,'0' as ROMApproved,'0' as EnterDate,'0' as ICMDate,'0' as BOMDate,'0' as ROMDate,'0' as ItemTeamDate,'0' as Stage,'3' as MSDSfl,'3' as msdssheetno,'3' as msdschgdt,'3' as MSDSAtt,'1' as WebPageItem,'1' as ReplProd,'0' as ICMRejfl,'0' as BOMRejfl,'0' as ItemTeamRejfl,'0' as ItemTeamMember,'0' as NewItemKey,'0' as Record,'0' as cono FROM NewItem LEFT OUTER JOIN ICSWNewItem ON NewItem.whse = ICSWNewItem.Whse AND NewItem.ProdChange = ICSWNewItem.Prod Where batch = @str10", Conn2) Dim Str10 As SqlParameter = Cmd2.Parameters.Add("@Str10", Data.SqlDbType.VarChar, 48) Str10.Value = Label19.Text Dim adapter2 As New SqlDataAdapter(Cmd2) Cmd2.CommandType = CommandType.Text Dim ds2 As New DataSet adapter2.Fill(ds2) For i = 0 To ds2.Tables(0).Columns.Count  1 xlWorkSheet.Cells(1, i + 1) = ds2.Tables(0).Columns(i).ColumnName Next For i = 0 To ds2.Tables(0).Rows.Count  1 For j = 0 To ds2.Tables(0).Columns.Count  1 xlWorkSheet.Cells(i + 2, j + 1) = ds2.Tables(0).Rows(i).Item(j) If ds2.Tables(0).Rows(i).Item(j) = 3 Then xlWorkSheet.Cells(i + 2, j + 1).Font.Color = RGB(100, 100, 255) End If If ds2.Tables(0).Rows(i).Item(j) = 1 Then xlWorkSheet.Cells(i + 2, j + 1).Font.Color = RGB(255, 0, 0) End If Next Next
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, January 20, 2010 5:33 PM
All replies

User585544258 posted
OK, so I thought about this more. My first issue is how do I get the data to know whether or not the field should be red. So, here is a query that does that. I'm so lost I have no idea if this will work. In essence, what this is doing is a bunch of "case when" to determine if the field is the same. If it returns a 1, I want it to be red if it returns a 0 I want it to be black. Here is the code for that, it is just partial, because the whole thing is large.
SELECT '0' as Batch, case when SetupType = 'Contract' then '1' else '0' end as SetupType, '1' as UserComments, case when ICSPNewChange = 'N' then '1' else '0' end as ICSPNewChange, case when ICSWNewChange = 'N' then '1' else '0' end as ICSWNewChange, case when ICSWNewItem.prod IS null then '1' else '0' end as Prod, case when newitem.Prod = newitem.prodchange then '0' else '1' end as ProdSetup, case when ICSPNewChange = 'N' then '1' else '0' end as Desc1Setup, case when ICSPNewChange = 'N' then '1' else '0' end as Desc2Setup, case when ICSWNewItem.whse IS null then '1' else '0' end as Whse FROM NewItem LEFT OUTER JOIN ICSWNewItem ON NewItem.whse = ICSWNewItem.Whse AND NewItem.ProdChange = ICSWNewItem.Prod
My next problem, and main reason for this post, is......
How in the world do I get this to help me change the fields in excel to red?
Thanks for any help.
Tuesday, January 19, 2010 10:20 AM 
User585544258 posted
For those that want to know the way I completed this, here is the code. I put this code right after the ranges were created.
Dim Conn2 As New SqlConnection(ConfigurationManager.ConnectionStrings("ReportingConnectionString").ToString()) Dim Cmd2 As New SqlCommand("SELECT '0' as Batch, case when SetupType = 'Contract' then '1' else '3' end as SetupType, '1' as UserComments, case when ICSPNewChange = 'N' then '1' else '3' end as ICSPNewChange,case when ICSWNewChange = 'N' then '1' else '3' end as ICSWNewChange,case when ICSWNewItem.prod IS null then '1' else '0' end as Prod,case when newitem.Prod = newitem.prodchange then '0' else '1' end as ProdSetup,case when ICSPNewChange = 'N' then '1' else '3' end as Desc1Setup,case when ICSPNewChange = 'N' then '1' else '3' end as Desc2Setup,case when ICSWNewItem.whse IS null then '1' else '0' end as Whse,case when ICSWNewItem.prodcat = newitem.prodcat then '0' else '1' end as Prodcat,case when ICSWNewItem.Statustype = newitem.Statustype then '0' else '1' end as Statustype,case when ICSWNewItem.arptype = newitem.ArpType then '0' else '1' end as Arptype,case when ICSWNewItem.arpvendno = newitem.Vendno then '0' else '1' end as Vendno,case when ICSWNewItem.prodline = newitem.ProdLine then '0' else '1' end as Prodline,case when ICSWNewItem.vendprod = newitem.VendProd then '0' else '1' end as VendProd,case when ICSWNewItem.vendprod = newitem.VendProd then '0' else '1' end as VendName,case when ICSWNewItem.unitstnd = newitem.StdPack then '0' else '1' end as StdPack,case when ICSWNewItem.minthreshold = newitem.StockLevel then '0' else '1' end as StockLevel,case when ICSWNewItem.minthreshold = newitem.StockLevel then '0' else '1' end as AnnualUsage,case when ICSWNewItem.replcost = newitem.Cost then '0' else '1' end as Cost,'3' as ExtendedCost,case when ICSWNewItem.User3 = newitem.VendQuote then '0' else '1' end as VendQuote,case when ICSWNewItem.User8 = newitem.QuoteExpDate then '0' else '1' end as QuoteExpDate,case when ICSWNewItem.leadtmavg = newitem.LeadTime then '0' else '1' end as LeadTime,'3' as Custno,'3' as CustName,'3' as CustomerStockCode,'3' as Resale,'3' as OldPart,'3' as SXQuoteNo,'3' as ContractNumber,'3' as ContractExpDate,'3' as custblanket,case when ICSWNewItem.unitstock = newitem.UOM then '0' else '1' end as UOM,case when ICSPNewChange = 'N' then '1' else '3' end as Description1,case when ICSPNewChange = 'N' then '1' else '3' end as Description2,'3' as UnitWeight,'1' as ExtDesc,'1' as QuoteAtt,'1' as CatalogAtt,'1' as ImageAtt,'1' as CutsheetAtt,'1' as ICMComments,'1' as ItemTeamComments,'1' as BOMComments,'1' as ROMComments,'0' as Requestor,'0' as ICM,'0' as ROM,'0' as BOM,'0' as ICMApproved,'0' as ItemTeamApproved,'0' as BOMApproved,'0' as ROMApproved,'0' as EnterDate,'0' as ICMDate,'0' as BOMDate,'0' as ROMDate,'0' as ItemTeamDate,'0' as Stage,'3' as MSDSfl,'3' as msdssheetno,'3' as msdschgdt,'3' as MSDSAtt,'1' as WebPageItem,'1' as ReplProd,'0' as ICMRejfl,'0' as BOMRejfl,'0' as ItemTeamRejfl,'0' as ItemTeamMember,'0' as NewItemKey,'0' as Record,'0' as cono FROM NewItem LEFT OUTER JOIN ICSWNewItem ON NewItem.whse = ICSWNewItem.Whse AND NewItem.ProdChange = ICSWNewItem.Prod Where batch = @str10", Conn2) Dim Str10 As SqlParameter = Cmd2.Parameters.Add("@Str10", Data.SqlDbType.VarChar, 48) Str10.Value = Label19.Text Dim adapter2 As New SqlDataAdapter(Cmd2) Cmd2.CommandType = CommandType.Text Dim ds2 As New DataSet adapter2.Fill(ds2) For i = 0 To ds2.Tables(0).Columns.Count  1 xlWorkSheet.Cells(1, i + 1) = ds2.Tables(0).Columns(i).ColumnName Next For i = 0 To ds2.Tables(0).Rows.Count  1 For j = 0 To ds2.Tables(0).Columns.Count  1 xlWorkSheet.Cells(i + 2, j + 1) = ds2.Tables(0).Rows(i).Item(j) If ds2.Tables(0).Rows(i).Item(j) = 3 Then xlWorkSheet.Cells(i + 2, j + 1).Font.Color = RGB(100, 100, 255) End If If ds2.Tables(0).Rows(i).Item(j) = 1 Then xlWorkSheet.Cells(i + 2, j + 1).Font.Color = RGB(255, 0, 0) End If Next Next
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, January 20, 2010 5:33 PM