epplus发出formular1c1损坏的excel产生

Question

我正在生成一个带有一组数据的Excel表，其中包括必须应用于列上的公式。

ex：

C2= (B2-A2)/A2, C3= (B3-A3)/A3, C101 = (B101-A101)/A101

这些公式正常工作，并且在我引用列R（ID = 18）时正确生成Excel，而没有任何错误，但是当将其更改为ID = 399的列时会损坏Excel。

这是我带有数据的XML代码。在这里 ColumnId指出哪个列FieldCode表示列主题，而FieldCalculation表示该列的公式。

  <Header>
    <RowId>1</RowId>
    <HeaderCells>
      <HeaderCell>
        <ColumnId>2</ColumnId>
        <FieldCode>ST1001</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>1</ColumnId>
        <FieldCode>ST1000</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>3</ColumnId>
        <FieldCode>ST1002</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>4</ColumnId>
        <FieldCode>ST1003</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>5</ColumnId>
        <FieldCode>ST1004</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>6</ColumnId>
        <FieldCode>ST1005</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>7</ColumnId>
        <FieldCode>ST1006</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>8</ColumnId>
        <FieldCode>ST1007</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>9</ColumnId>
        <FieldCode>ST1008</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>10</ColumnId>
        <FieldCode>ST1009</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>11</ColumnId>
        <FieldCode>ST1010</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>12</ColumnId>
        <FieldCode>ST1011</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>13</ColumnId>
        <FieldCode>ST1012</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>14</ColumnId>
        <FieldCode>ST1013</FieldCode>
        <FieldCalculation>UPPER(TRIM(CONCATENATE('ST1004', 'ST1005', 'ST1006', 'ST1007, 'ST1008', 'ST1009', 'ST1010', 'ST1011', 'ST1012')))</FieldCalculation>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>15</ColumnId>
        <FieldCode>ST1030</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>400</ColumnId>
        <FieldCode>ST1041</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>401</ColumnId>
        <FieldCode>ST1043</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>399</ColumnId>
        <FieldCode>ST1045</FieldCode>
        <FieldCalculation>(ST1043-ST1041)/ST1041</FieldCalculation>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>19</ColumnId>
        <FieldCode>ST1073</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>20</ColumnId>
        <FieldCode>ST1074</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>21</ColumnId>
        <FieldCode>ST1075</FieldCode>
      </HeaderCell>
      <HeaderCell>
        <ColumnId>22</ColumnId>
        <FieldCode>ST1076</FieldCode>
        <FieldCalculation>ST1073+ST1074+ST1075</FieldCalculation>
      </HeaderCell>
    </HeaderCells>
  </Header>

这是将fieldCalculation转换为formular1c1的代码，用列的绝对位置替换标题主题。

ex：

1）UPPER(TRIM(CONCATENATE('ST1004', 'ST1005', 'ST1006', 'ST1007, 'ST1008', 'ST1009', 'ST1010', 'ST1011', 'ST1012')))转换为UPPER(TRIM(CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3],RC[-2],RC[-1])))

2）(ST1043-ST1041)/ST1041转换为(RC[2]-RC[1])/RC[1]

3）ST1073+ST1074+ST1075转换为RC[-3]+RC[-2]+RC[-1]

代码：

    /// <summary>
    /// Get the formula for the field
    /// </summary>
    /// <param name="formulaString">The formula string with header topics</param>
    /// <param name="headercells">The header cells list in the excel</param>
    /// <param name="columnId">The current column id</param>
    /// <returns></returns>
    public static string GetFieldFormula(string fieldCalculation, List<HeaderCell> headercells, int columnId)
    {
        string[] multiArray = fieldCalculation.Split(new Char[]
        { '!', '@', '#', '$', '%', '^', '&', '*', '(', ')','-', '_', '=', '+', '{', '}', '[', ']', ';', ':', '"', '<', '>', '?', ',', '.', '/', '~', '`', '\',  '|'});
        foreach (string column in multiArray)
        {
            string columnname = column.Replace("'", "").Trim();
            int? machingcellId = headercells.Find(headcell => headcell.FieldCode == columnname)?.ColumnId;
            if (machingcellId > 0)
            {
                string matchingcellname = "RC[" + (machingcellId - columnId) + "]";
                fieldCalculation= Regex.Replace(fieldCalculation, column, matchingcellname);
            }
        }
        return fieldCalculation;
    }

然后将此字段公式应用于整列，如下所示。

此处i是当前列ID，headerCells对象包含了必不可少的XML数据。

    fieldCalculation = if (!string.IsNullOrWhiteSpace(fieldCalculation))
    {
        string formulaString = null;
        formulaString = GetFieldFormula(fieldCalculation, headerCells, i); 
        sheet.Cells[2, i, rowcount - 2, i].FormulaR1C1 = formulaString;                    
    }
In this example when I change ST1045 (id =399) column's id to 18, the excel is not corrupted. If I apply only one formula at a time out of these three formula, then also the excel is not corrupted. I cant find what is the exact reason for this issue. I have used the latest version of EPPlus 4.5.3.1. Please give your suggestions.  

Answer 1

我可以通过更改Excel版本或限制应用公式的行数来解决此问题。

asfaiu此问题是由于打开从Epplus库创建的Excel文件时所消耗的内存。

我最初使用Excel 2007，当我更改为Excel 2016时，该文件并没有损坏。

由于我正在构建的应用程序应支持Excel 2010，因此我限制了应用公式的行数。如果不是，则将公式应用于列中的所有行。然后问题也是解决的。

Answer 2

我使用此：

ws.Cells[row,column+1].Formula = Regex.Replace(ws.Cells[row,column].Formula, @"[A-Z]+(?=d)+", ExcelCellAddress.GetColumnLetter(column+1));

通过REGEX更改列的字母，该字母验证了在公式中搜索单元格的字母和数字[A-Z]+(?=d)+;