如何将条件格式添加到XML / Excel文件？ [英] How to add Conditional Formatting to an XML / Excel file?

问题描述

我通过为它编写XML来生成一个Excel文件。我几乎完成，但我不能让条件格式工作，我想要的方式。

我想将条件应用于某些单元格。例如。对于每个数据行（不是页眉或页脚），如果列7-13中的值大于列6中的值，则列7-13应该高亮显示红色。下面的代码仅适用于第一个数据行。

 < / Table>如何将它应用到一组行？ 
 
" ConditionalFormatting xmlns =urn：schemas-microsoft-com：office：excel> 
< Range> RC7：RC13< / Range> 
<条件> 
< Qualifier>更大< / Qualifier> 
< Value1> RC6< / Value1> 
< Format Style ='background-color：＃F7A9A5'/> 
< / Condition> 
< / ConditionalFormatting> 
 
< / Worksheet> 
< / Workbook> 
  

我不想指定确切的行号（B7-B13）。理想情况下，我可以将它应用到我想要的每一行或通用的一些行如何。

更新：我有另一个问题，列是比较（C6）是一个字符串。如果字符串为空，则不应应用格式。但是，如果该列包含一个数字，则应将其视为一个数字并进行比较。

更新：

这里有更完整的代码：

 <？xml version =1.0？> 
<？mso-application progid =Excel.Sheet？> 
 
 Workbook xmlns =urn：schemas-microsoft-com：office：spreadsheet
 xmlns：o =urn：schemas-microsoft-com：office：office
 xmlns：x =urn：schemas-microsoft-com：office：excel
 xmlns：ss =urn：schemas-microsoft-com：office：spreadsheet
 xmlns：html =http： //www.w3.org/TR/REC-html40\"> 
< DocumentProperties xmlns =urn：schemas-microsoft-com：office：office> 
<作者>索迪斯平台< / Author> 
< LastAuthor> @ HttpContext.Current.User.Identity.Name< / LastAuthor> 
<创建> @ DateTime.Now.ToUniversalTime（）< / Created> 
< LastSaved> @ DateTime.Now.ToUniversalTime（）< / LastSaved> 
< Company> Sodexo< / Company> 
< Version> 1< / Version> 
< / DocumentProperties> 
< OfficeDocumentSettings xmlns =urn：schemas-microsoft-com：office：office> 
< DownloadComponents /> 
< LocationOfComponents HRef =file：/// D：\/> 
< / OfficeDocumentSettings> 
< ExcelWorkbook xmlns =urn：schemas-microsoft-com：office：excel> 
< WindowHeight> 8700< / WindowHeight> 
< WindowWidth> 11355< / WindowWidth> 
< WindowTopX> 480< / WindowTopX> 
< WindowTopY> 120< / WindowTopY> 
< ProtectStructure>假< / ProtectStructure> 
< ProtectWindows> False< / ProtectWindows> 
< / ExcelWorkbook> 
 
<样式> 
<样式ss：ID =表> 
< Borders> 
< Border ss：Position =Topss：Color =＃595959ss：Weight =1ss：LineStyle =Continuous/> 
< Border ss：Position =Bottomss：Color =＃595959ss：Weight =1ss：LineStyle =Continuous/> 
< Border ss：Position =Leftss：Color =＃595959ss：Weight =1ss：LineStyle =Continuous/> 
< Border ss：Position =Rightss：Color =＃595959ss：Weight =1ss：LineStyle =Continuous/> 
< / Borders> 
< Font ss：FontName =Arialss：Size =8/> 
< / style> 
< / Styles> 
 
<工作表ss：Name =摘要> 
<表> 
< Column ss：AutoFitWidth =0ss：Width =200/> 
< Column ss：AutoFitWidth =0ss：Width =80/> 
< Column ss：AutoFitWidth =0ss：Width =130/> 
< Column ss：AutoFitWidth =0ss：Width =75/> 
< Column ss：AutoFitWidth =0ss：Width =75/> 
< Column ss：AutoFitWidth =0ss：Width =75/> 
 
< Row> 
< Cell ss：StyleID =Table> 
< Data ss：Type =String> A< / Data> 
< / Cell> 
< Cell ss：StyleID =Table> 
< Data ss：Type =String> B< / Data> 
< / Cell> 
< Cell ss：StyleID =Table> 
< Data ss：Type =String> C< / Data> 
< / Cell> 
< / Row> 
< / Table> 
 
" ConditionalFormatting xmlns =urn：schemas-microsoft-com：office：excel> 
< Range> RC7：RC13< / Range> 
<条件> 
< Qualifier>更大< / Qualifier> 
< Value1> RC6< / Value1> 
< Format Style ='background-color：＃F7A9A5'/> 
< / Condition> 
< / ConditionalFormatting> 
 
< / Worksheet> 
 
< / Workbook> 
  

解决方案

将条件格式应用到通用行或者做类似于A：A的东西，它将应用于列A中所有具有值的行，或者可以将条件格式指向NamedRange。如果指定的范围发生变化，则不必更新条件格式。

要处理空字符串，可以使用公式条件格式，并检查单元格不是空的，例如 = NOT（ISBLANK（A：A））

I'm generating an Excel file by writing the XML for it. I am almost done, but I can't get the conditional formatting to work the way I want.

I want to apply a condition to certain cells. E.g. for each data row (not a header or footer), columns 7-13 should highlight red if they are greater than the value in Column 6. The code below works for the first data row only. How can I get it to apply to a set of rows?

    </Table>

    <ConditionalFormatting xmlns="urn:schemas-microsoft-com:office:excel">
        <Range>RC7:RC13</Range>
        <Condition>
            <Qualifier>Greater</Qualifier>
            <Value1>RC6</Value1>
            <Format Style='background-color:#F7A9A5'/>
        </Condition>
    </ConditionalFormatting>

</Worksheet>
</Workbook>

I'd rather not have to specify the exact row number (B7-B13). Ideally, I'd be able to just apply it to each row I want or a generic set of rows some how.

Update: I have another problem, the column being compared (C6) is a string. If the string is empty, the format should not be applied. However, if the column contains a number, it should be treated as a number and compared.

Update:

Here's more complete code:

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:x="urn:schemas-microsoft-com:office:excel"
    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
    xmlns:html="http://www.w3.org/TR/REC-html40">
  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
    <Author>Sodexo Platform</Author>
    <LastAuthor>@HttpContext.Current.User.Identity.Name</LastAuthor>
    <Created>@DateTime.Now.ToUniversalTime()</Created>
    <LastSaved>@DateTime.Now.ToUniversalTime()</LastSaved>
    <Company>Sodexo</Company>
    <Version>1</Version>
  </DocumentProperties>
  <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
    <DownloadComponents/>
    <LocationOfComponents HRef="file:///D:\"/>
  </OfficeDocumentSettings>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>8700</WindowHeight>
    <WindowWidth>11355</WindowWidth>
    <WindowTopX>480</WindowTopX>
    <WindowTopY>120</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>

  <Styles>
    <Style ss:ID="Table">
        <Borders>
            <Border ss:Position="Top" ss:Color="#595959" ss:Weight="1" ss:LineStyle="Continuous"/>
            <Border ss:Position="Bottom" ss:Color="#595959" ss:Weight="1" ss:LineStyle="Continuous"/>
            <Border ss:Position="Left" ss:Color="#595959" ss:Weight="1" ss:LineStyle="Continuous"/>
            <Border ss:Position="Right" ss:Color="#595959" ss:Weight="1" ss:LineStyle="Continuous"/>
        </Borders>
        <Font ss:FontName="Arial" ss:Size="8" />
    </Style>
  </Styles>

<Worksheet ss:Name="Summary">
<Table>
    <Column ss:AutoFitWidth="0" ss:Width="200" /> 
    <Column ss:AutoFitWidth="0" ss:Width="80" /> 
    <Column ss:AutoFitWidth="0" ss:Width="130" />
    <Column ss:AutoFitWidth="0" ss:Width="75" /> 
    <Column ss:AutoFitWidth="0" ss:Width="75" /> 
    <Column ss:AutoFitWidth="0" ss:Width="75" /> 

    <Row>
        <Cell ss:StyleID="Table">
            <Data ss:Type="String">A</Data>
        </Cell>
        <Cell ss:StyleID="Table">
            <Data ss:Type="String">B</Data>
        </Cell>
        <Cell ss:StyleID="Table">
            <Data ss:Type="String">C</Data>
        </Cell>
    </Row>
</Table>

<ConditionalFormatting xmlns="urn:schemas-microsoft-com:office:excel">
    <Range>RC7:RC13</Range>
    <Condition>
        <Qualifier>Greater</Qualifier>
        <Value1>RC6</Value1>
        <Format Style='background-color:#F7A9A5'/>
    </Condition>
</ConditionalFormatting>

</Worksheet>

</Workbook>

解决方案

To apply conditional formatting to a "generic" set of rows or do something like A:A which will apply to all the rows with value in column A, or you could have the conditional format point to a NamedRange. If the named range changes, you will not have to update the conditional format.

To deal with the empty string you could use a formula conditional format and check that a cell in not blank for example =NOT(ISBLANK(A:A))

这篇关于如何将条件格式添加到XML / Excel文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！