locked
Set excel field color RRS feed

  • Question

  • User-585544258 posted

    My program sends an e-mail 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

  • User-585544258 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

  • User-585544258 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
  • User-585544258 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