我通过操作Excel文档的内部OOXML(Office Open XML)来解决了这个问题。如果您不知道如何打开Excel文档(以查看其内部组成的xml文档),请参考以下链接之一:
无论您使用的方法是什么,现在导航到您数据透视表的“PivotCache”。在我的“解压缩的Excel文件”中,文件路径为:Root > xl > pivotCache > pivotCacheDefinition1.xml
我的副本有大约800行XML,我想象它甚至可能更多,但只关注您要重新格式化的部分。(如果使用Visual Studio,请确保您对XML进行了美化,以便它不是一条长的压缩行Ctrl+K,Ctrl+D)
搜索属性:groupBy="months"
我的示例找到
<cacheField name="Months" numFmtId="0" databaseField="0">
<fieldGroup base="2">
<rangePr groupBy="months" startDate="2019-01-02T00:00:00" endDate="2019-12-27T00:00:00"/>
<groupItems count="14">
<s v="<1/2/2019"/>
<s v="Jan"/>
<s v="Feb"/>
<s v="Mar"/>
<s v="Apr"/>
<s v="May"/>
<s v="Jun"/>
<s v="Jul"/>
<s v="Aug"/>
<s v="Sep"/>
<s v="Oct"/>
<s v="Nov"/>
<s v="Dec"/>
<s v=">12/27/2019"/>
</groupItems>
</fieldGroup>
</cacheField>
</cacheFields>
...而你所要做的就是将“Jan”替换为“1”,或者你想要的每个月的字符串表示!
更进一步,但仍然回答了如何格式化分组列的问题,是将groupBy="days"进行格式化,结果如下:
<fieldGroup par="5" base="2">
<rangePr groupBy="days" startDate="2019-01-02T00:00:00" endDate="2019-12-27T00:00:00"/>
<groupItems count="368">
<s v="(blank)"/>
<s v="1-Jan"/>
<s v="2-Jan"/>
<s v="3-Jan"/>...
列表还有很多,确切地说是368个元素。365天,加上第一个和最后一个元素,其中包括2月29日。如果您正在尝试格式化按天分组的行,则需要更改这些元素。例如:
<s v="1-Jan"/>
你可以将它们更改为中文,例如(或任何你喜欢的语言,只需进行366个元素替换)(再次,不要改变第一个和最后一个元素)。
因此,“1-Jan”“2-Jan” 可以变成:
<s v="1月1日"/>
<s v="1月2日"/>...
你可以像之前处理月份那样处理这些。
然而,手动处理天数会更加繁琐,因为你需要为每一天创建一个元素,所以我编写了一个 C# 函数,你可以修改它以快速将元素输出到文件(以便复制并粘贴到你的 .xml 文件中)。
public void GenerateExcelGroupedDateFormatPivotTableElements()
{
int year = 2019;
var sb = new StringBuilder();
for (int i = 1; i <= 12; i++)
{
var daysInMonth = DateTime.DaysInMonth(year, i);
for (int j = 1; j <= daysInMonth; j++)
{
string monthName = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(i);
sb.AppendLine($@"<s v=""{monthName}-{j}""/>");
}
}
File.WriteAllText("_ExcelPivotTableGroupedDateHelper.txt",sb.ToString());
}
重要提示:如果您使用此函数来输出您的日期,请手动添加2月29日,如果您要覆盖的原始设置中有2月29日(就像我的一样)。
免责声明:我不是Excel专家,可能在Excel表格的OOXML中有更好的方法来完成这个任务。这只是我今天发现的东西,我想分享一下我找到的答案,因为我自己找不到发布的答案。
欢迎提供此操作的后续建议和注意事项。
祝编码愉快!