当前位置：首页 > news >正文

Open xml 操作Excel 透视表（Pivot table）-- 实现Excel多语言报表

news 来源：原创 2024/4/28 3:38:35

我的一个ERP项目中，客户希望使用Excel Pivot table 做分析报表。 ERP 从数据库中读出数据，导出到Excel中的数据源表(统一命名为Data)，刷新Pivot table！

客户还希望对Excel报表提供多语言支持，根据用户的语言生成不同版本的Excel文件。

经过不断尝试，终于成功完成该任务，本篇简要描述这个任务涉及到的知识点。

把一个包含透视表及透视图的Excel .xlsx文件重命名为.zip 文件，然后解压缩到某个文件夹下，就可以看到Excel是如何定义透视表及透视图了, 如下图所示，pivotTables 定义了透视表中行、列及数据字段等， PivotCache 中则定义了Pivot table 的数据源、字段匹配，以及缓存了上一次打开的数据

　　　　　　　　　　　　　　　　　　　　(Excel文件结构)

(pivotTables\pivotTable1.xml截图)

　　　　(pivotCache\pivotCacheDefinition1.xml 截图)

根据以上描述，在导入数据后. 还需要完成以下步骤：

重新设置透视表的数据源到数据区域.

重新设置Pivot table 数据源的代码如下:

 1         //其中sheetName为作为数据源的工作表名，lastReference为数据源中最后一个单元格的引用名,比如最后一列为AG，共10行则为AG10
 2     public static void SetPivotSource(WorkbookPart wbPart, string sheetName, string lastReference)
 3         {
 4             var pivottableCashes = wbPart.PivotTableCacheDefinitionParts;
 5             foreach (PivotTableCacheDefinitionPart pivottablecachePart in pivottableCashes)
 6             {
 7                 pivottablecachePart.PivotCacheDefinition.CacheSource.RemoveAllChildren();
 8 //设置Pivot tabla的数据源为A1:lastReference
 9                 pivottablecachePart.PivotCacheDefinition.CacheSource.Append(new WorksheetSource() {
10                     Sheet = sheetName, Reference = new StringValue("A1:" + lastReference) });
11             }
12         }

//假设Data表格中的最后一列的Reference为AG，总共有100行（加上列头行共101行），则导入数据后调用 
using (SpreadsheetDocument document = SpreadsheetDocument.Open(rawFileName, true))

            {

                WorkbookPart wbPart = document.WorkbookPart;

                                SetPivotSource(wbPart,"data","AG101");

                    }

2.　　翻译Excel 数据源表字段名

也就是翻译及修改Data表格中第一行的单元格内容

  public static void UpdateCellValue(WorkbookPart wbPart, Cell theCell,string newValue)
        {
            string value = theCell.InnerText;
            if (theCell.DataType != null)
            {
                switch (theCell.DataType.Value)
                {
                    case CellValues.SharedString:
                        var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                        if (stringTable != null)
                        {
                            var ele = stringTable.SharedStringTable.ElementAt(int.Parse(value));
                            ele.RemoveAllChildren();
                            ele.Append(new DocumentFormat.OpenXml.Spreadsheet.Text(newValue));
                        }
                        break;

                    case CellValues.Boolean:
                        if (string.Compare(value,"FALSE",true) ==0)
                        {
                            theCell.InnerXml = "1";
                        }
                        else
                        {
                            theCell.InnerXml = "0";
                        }
                        break;
                    default:
                        theCell.InnerXml = newValue;
                        break;
                }
            }
        }

   using (SpreadsheetDocument document = SpreadsheetDocument.Open(rawFileName, true))
            {
                WorkbookPart wbPart = document.WorkbookPart;
                
                var dataSheet = wbPart.Workbook.Descendants<Sheet>().FirstOrDefault(c => string.Compare(c.Name, "Data",true)==0);
                WorksheetPart worksheetPart = (WorksheetPart)wbPart.GetPartById(dataSheet.Id);
                var headerRow = worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements<Row>().
                           FirstOrDefault(c => c.RowIndex == 1);
                var cells = headerRow.Elements<Cell>().ToList();
                foreach (var cell in cells)
                {
                    var rawText = ExcelHelper.GetCellValue(wbPart, cell);
                    ExcelHelper.UpdateCellValue(wbPart, cell, _translator.Translate(rawText));
                    //cell.CellValue = new CellValue(_translator.Translate(rawText));
                    //cell.DataType = new EnumValue<CellValues>(CellValues.String);
                }
                worksheetPart.Worksheet.Save();
    }

3.　　翻译pivotCacheDefinition缓存区的字段定义。

 using (SpreadsheetDocument document = SpreadsheetDocument.Open(rawFileName, true))
            {
                WorkbookPart wbPart = document.WorkbookPart;
            var pivottableCashes = wbPart.PivotTableCacheDefinitionParts;
                foreach (PivotTableCacheDefinitionPart pivottablecachePart in pivottableCashes)
                {
                    pivottablecachePart.PivotCacheDefinition.RefreshOnLoad = true;
                    var pivotCacheFields = pivottablecachePart.PivotCacheDefinition.CacheFields;
                    foreach (OpenXmlElement pivotCacheField in pivotCacheFields)
                    {
                        OpenXmlAttribute nameEle = pivotCacheField.GetAttribute("name", "");
                        nameEle.Value = _translator.Translate(nameEle.Value);
                        pivotCacheField.SetAttribute(nameEle);
                    }   
                }
    }

4.　　翻译Pivot Table 透视表定义区域的数据字段名,以及图表区的数据源表名

 //sheet为Sheet类型对象
            oxPart = wbPart.GetPartById(sheet.Id);
                    //Translate Pivot table data(numeric) field defination, such as "Sum of [Vat...]"
                    if (oxPart.ContentType.Contains("worksheet"))
                    {
                        wsP = (WorksheetPart)oxPart;
                        tbDefParts = wsP.PivotTableParts;
                        foreach (PivotTablePart ptPart in tbDefParts)
                        {
                            dataFileds = ptPart.PivotTableDefinition.DataFields;
                            foreach (DataField df in dataFileds)
                            {
                                if (df.Name.Value.StartsWith(SUM_OF))
                                {
                                    df.Name = new StringValue(_translator.Translate(SUM_OF) + " " +
                                        _translator.Translate(df.Name.Value.Replace(SUM_OF, "").Trim()));
                                }
                            }
                        }
                    }

 var index =  rawFileName.LastIndexOf(@"\");
                var filename = rawFileName.Substring(index+1);
                foreach(ChartsheetPart cspart in wbPart.ChartsheetParts)
                {
                    var chartparts = cspart.DrawingsPart.ChartParts ;
                    foreach(ChartPart cp in chartparts)
                    {
                        PivotSource pivotSource =  cp.RootElement.OfType<PivotSource>().First();
                        string originalName = pivotSource.PivotTableName.InnerText;
                        Regex reg = new Regex(@"^[[]([^]]+)[]]([^!]+)!(.*)$");
                        var matches = reg.Matches(originalName);
                        if (matches.Count > 0 && matches[0].Groups.Count >3)
                        {
                            string newName = string.Format("[{0}]{1}!{2}", filename,
                                _translator.Translate(matches[0].Groups[2].Value), matches[0].Groups[3].Value);
                            pivotSource.PivotTableName = new PivotTableName(newName);
                        }
                    }

5. 翻译表格名，需要翻译所有除了Data表外的工作表名。

  foreach (Sheet sheet in sheets)
                {
                    if (string.Compare(sheet.Name, "data", true) != 0)
                    {
                        var translatedName = _translator.Translate(sheet.Name);
                        if (!string.IsNullOrEmpty(translatedName) && translatedName.Length > 30)
                        {
                            translatedName = translatedName.Substring(0, 30);
                        }
                        sheet.Name = translatedName;
                    }
        }