Showing posts with label cell. Show all posts
Showing posts with label cell. Show all posts

Wednesday, October 5, 2022

[FIXED] What can you use instead of tick marks ' in PHP?

      , , , ,      No comments   

Issue

I am trying to set the value of a cell in an Excel spreadsheet to a formula that references a worksheet and has several parameters to it. My formula is:

=SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "NP")+SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PHBO")+SUMIFS('Sheet1'!AY:AY,'Shee1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PR", 'Sheet1'!AN:AN, "Both")+SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PR", 'Sheet1'!AN:AN, "Area")

My PHP is:

$objPHPExcel->setActiveSheetIndex(1)
            ->setCellValue('B2', 'XXX');

Where XXX = the formula above. The problem is because of the ' and " marks in the formula I get errors.


Solution

<?PHP
$stmt = <<<EOF
=SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "NP")+SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PHBO")+SUMIFS('Sheet1'!AY:AY,'Shee1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PR", 'Sheet1'!AN:AN, "Both")+SUMIFS('Sheet1'!AY:AY,'Sheet1'!J:J, "Q1", 'Sheet1'!M:M, "Extensions", 'Sheet1'!AW:AW, "PR", 'Sheet1'!AN:AN, "Area")
EOF;

$objPHPExcel->setActiveSheetIndex(1)
        ->setCellValue('B2', $stmt);
?>

Assigns your String to the variable $stmt then uses that variable in your function. This allows for the ' and " to be read as characters in the string instead of being read by the interpreter to break the sting.



Answered By - Sparky
Answer Checked By - Robin (PHPFixing Admin)

Sunday, August 28, 2022

[FIXED] How to Check CSV file headers for specific word in Python

      , , , ,      No comments   

Issue

I have several CSV files and their headers are similar for the first 10 columns and then they are different. So I need to check each of the CSV files and see if in the column 11 they have word ABC or XYZ or SOS so then I can apply my other function on each CSV depending on the header. Can anyone please help me with the if conditions that I need to apply to read the headers and compare column 11 in header with the strings I mentioned?


Solution

I'd use glob to loop for each csv, and pandas to read the columns names.

import glob

path = r"*.csv"  # The path to the folder containing your csv files

for file_name in glob.glob(path):  # Looping through the list of paths 
    df = pd.read_csv(file_name)

    list_of_column_names = list(df.columns)  # Getting the headers of your dataframe

    if 'ABC' in str(list_of_column_names[11]):
        print('ABC case')
    elif 'XYZ' in str(list_of_column_names[11]):
        print('XYZ case')
    elif 'SOS' in str(list_of_column_names[11]):
        print('SOS case')
    else:
        print('Other Case')


Answered By - Guillaume
Answer Checked By - Candace Johnson (PHPFixing Volunteer)

Monday, July 18, 2022

[FIXED] How to align text in the box in HTML so that it does not affect the spacing on each side

      , , , ,      No comments   

Issue

I am a newbie in HTML and currently working on my University project work and I really need some help. I am using a background box for the "blog-like" website which needs to align title and short text on the left and contents with hyperlinks on the right.

So far I have used a cell to align them but to say the least, it looks very ugly and moreover affects spacing. No matter what I tried I can't change the contents on both sides without a response from another. Is it possible to arrange content somehow another way instead of using a single cell for an entire box and dividing percentage for each side? I have uploaded a picture of the website below

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

  <head>
    <title>
      Your Personal Guide To Best Hardcore Events
    </title>
    <style type="text/css">
      body {
        background: url(http://webprojects.eecs.qmul.ac.uk/at315/background.jpg);
        background-repeat: no-repeat;
        background-size: 100% 100%;
        background-attachment: fixed
      }

      .background {
        display: flex;
        justify-content: center;
      }

      div.transbox {
        margin: 0px;
        background-color: #ffffff;
        border: 1.5px solid black;
        opacity: 0.6;
        filter: alpha(opacity=60);
        width: 100%;
        max-width: 1300px;
      }

    </style>
  </head>

  <body>
    <p align="center">
      <img src="http://webprojects.eecs.qmul.ac.uk/at315/header.png" style="width:70%;" border="0" alt="" />
    </p>
    <div class="background">
      <div class="transbox">
        <table width="100%" border="0" cellpadding="0" cellspacing="0">
          <tr>
            <th width="75%" align="left">
              <h2>
                    Articles:
                  </h2>
            </th>
            <th width="20%" align="center">
              <ul style="list-style: none;">
                <li>
                  <a href="http://webprojects.eecs.qmul.ac.uk/rr305/CourseWork/blog.xml" style="text-decoration:none">Homepage</a>
                  <br />
                  <br />
                </li>
                <li>
                  <small><a href="http://www.qmul.ac.uk" style=
                      "text-decoration:none">Architects</a></small>
                </li>
                <li>
                  <small><a href="https://www.facebook.com" style=
                      "text-decoration:none">Northlane</a></small>
                </li>
                <li>
                  <small><a href="http://www.bbc.co.uk" style=
                      "text-decoration:none">Attila</a></small>
                </li>
              </ul>
            </th>
          </tr>
          <tr>
            <td align="left">
              <h4>
                    <strong>Architects Rocking Brixton</strong>
                  </h4>Read our article about Architects headlining their biggest sold-out UK show!
              <hr />
            </td>
          </tr>
          <tr>
            <td></td>
          </tr>
          <tr>
            <td align="left">
              <h4>
                    <strong>Architects Rocking Brixton</strong>
                  </h4>Read our article about Architects headlining their biggest sold-out UK show!
              <hr />
            </td>
          </tr>
          <tr>
            <td></td>
          </tr>
          <tr>
            <td align="left">
              <h4>
                    <strong>Architects Rocking Brixton</strong>
                  </h4>Read our article about Architects headlining their biggest sold-out UK show!
              <br />
              <br />
            </td>
          </tr>
        </table>
      </div>
    </div>
  </body>

</html>

Main page of the website


Solution

In this situations it is best to use flex and max width. It is very good to responsive design so.

.background {
display: flex;
justify-content: center;
}

div.transbox {
margin: 30px;
background-color: #ffffff;
border: 1px solid black;
opacity: 0.6;
filter: alpha(opacity=60);
width: 100%;
max-width: 1000px;
}

Change your browser size, It work good for all sizes. And the best part is if the screen has width < 1000px the table fills all of the screen.



Answered By - Farzin Kanzi
Answer Checked By - Terry (PHPFixing Volunteer)

Sunday, July 10, 2022

[FIXED] How can I make a cell reference a function of a variable?

      , , , ,      No comments   

Issue

I will be using the same macro 24 times, but the ranges will differ (but at a constant rate) every time. Therefore I thought it would be smart to write the references as a function in order to reduce the length of the code, and also make it easier to change.

Now it looks like this:

    Sheets("Planned time").Select
    Range("I15:NJ32").Select
    Selection.Copy
    Sheets("Diff").Select
    Range("I9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

But I would like it to be something like this:

    Sheets("Planned time").Select
    Range("I(15+171x):NJ(32+171x)").Select
    Selection.Copy
    Sheets("Diff").Select
    Range("I(9+56x)").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Where the macro loops 24 times, but for each loop, increase the value of x by 1. x would need to be 0 in the first iteration, and 23 in the last one (totalling 24 iterations).

I know the coding is probably far off, but I think it illustrates what I want to accomplish in a good way.

Any assistance would be greatly appreciated.

UPDATE: Solved

Dim x As Integer

For x = 0 To 23

Sheets("Planned time").Select

Range("I" & cstr(15+171*x) & ":NJ" & cstr(32+171*x)).Select

Selection.Copy

Sheets("Diff").Select

Range("I" & cstr(9+56*x)).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Next x

Solution

You want to build a string expression that, when evaluated in VBA, will be a valid address:

Range("I" & cstr(15+171*x) & ":NJ" & cstr(32+171*x)).Select

I assume you know how to build a FOR loop.

You can also 'build' range references by using numeric cell(row, column) expressions:

Range(cell(15+171*x,10),cell(32+171*x,374)).select


Answered By - tinazmu
Answer Checked By - Mary Flores (PHPFixing Volunteer)

[FIXED] How to reference cells in range?

      , , ,      No comments   

Issue

Based on the text ("SNV") present in column L of the "HiddenSheet" worksheet, I would like to select and copy cells in columns 1 to 6 for all rows for which the "SNV" text is present in column L.

Then I would like to paste the values of the copied cells in the SNVReports worksheet.

Sub Macro2()

a = Worksheets("HiddenSheet").Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To a

    If Worksheets("HiddenSheet").Cells(i, 12).Value = "SNV" Then

        Worksheets("HiddenSheet").Range(Cells(i, 1), Cells(i, 6)).Copy
        Worksheets("SNVReports").Activate
        b = Worksheets("SNVReports").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("SNVReports").Cells(b + 1, 1).Select
        ActiveSheet.Paste
        Worksheets("HiddenSheet").Activate

    End If
Next

Application.CutCopyMode = False

End Sub

I sometimes receive:

"Application-defined or object-defined error"

and it is apparently related to my range:

Worksheets("HiddenSheet").Range(Cells(i, 1), Cells(i, 6)).Copy

Solution

Your Cells(i,#) references aren't qualified. So if the SNVReports tab is active when the macro runs, it's confused as to what range you're talking about.

The whole code could do with a tidy-up:

Sub Macro2a()

    Dim sourcesheet As Worksheet
    Dim destsheet As Worksheet
    Dim lastsourcerow as Long
    Dim lastdestrow as Long
    Dim i as Long

    Set sourcesheet = Worksheets("HiddenSheet")
    Set destsheet = Worksheets("SNVReports")

    With sourcesheet

        lastsourcerow = .Cells(.Rows.Count, 1).End(xlUp).Row

        For i = 1 To lastsourcerow

            If .Cells(i, 12).Value = "SNV" Then
                lastdestrow = destsheet.Cells(destsheet.Rows.Count, 1).End(xlUp).Row
               .Range(.Cells(i, 1), .Cells(i, 6)).Copy destsheet.Cells(lastdestrow + 1, 1)
            End If

        Next

    End With

End Sub


Answered By - CLR
Answer Checked By - Senaida (PHPFixing Volunteer)

Total Pageviews

Featured Post

Why Learn PHP Programming

Why Learn PHP Programming A widely-used open source scripting language PHP is one of the most popular programming languages in the world. It...