0

thanks for the help, i am getting an exception: Error saving file C:\U..... {"Object reference not set to an instance of an object."}

StackTrace: at OfficeOpenXml.ExcelPackage.Save() at MINAG.SMEA.Business.ExcelBusinessService.CreateTableExcel(Decimal surveyId) in c:\Users\luciano\Desktop\New folder\implementation\MINAG.SMEA.Business\ExcelBusinessService.cs:line 2048

(only that on stack trace)

My current code is:

FileInfo newFile = new FileInfo(file);
                ExcelPackage excPck = new ExcelPackage(newFile);

                foreach (SURVEY_BASE_TABLE_LIST item in survey.SURVEY_BASE_TABLE_LIST)
                {
                    BASE_TABLE_LIST table = item.BASE_TABLE_LIST;
                    Int32 limit = table.CODE.Length;
                    if (limit > 31)
                        limit = 31;
                    string dynamicRangeFormula = "OFFSET('" + table.CODE.Substring(0, limit) + "'!$A$1;0;0;COUNTA('" + table.CODE.Substring(0, limit) + "'!$A$1:$A$1000)+1;COUNTA('" + table.CODE.Substring(0, limit) + "'!$1:$1))";
                    var ws = excPck.Workbook.Worksheets.Add("Pivot_" + table.CODE);
                    ExcelNamedRange newRange = new ExcelNamedRange(table.CODE.Substring(0, limit), null, ws, "C3", excPck.Workbook.Names.Count)
                        {
                            Formula = dynamicRangeFormula
                        };

                    var excelPivot = ws.PivotTables.Add(ws.Cells["A1"], newRange, "Pivot_" + table.CODE);



                }
                excPck.Save();

Any ideas?

Luciano Pinto
  • 43
  • 1
  • 9
  • Btw i assum the error is on the pivot table/range, but on step by step debug everything seems fine – Luciano Pinto Aug 05 '15 at 09:33
  • Probably have to do some additonal work on your pivot table like set the RowFields and such. Check out example 12 of the EPPLus source code (RunSample12 function): https://epplus.codeplex.com/SourceControl/latest#SampleApp/Sample12.cs – Ernie S Aug 05 '15 at 12:18
  • The poblem with all the availed examples is that they use a preset datasource var dataRange = wsData.Cells["A1"].LoadFromCollection( from s in list orderby s.LastName, s.FirstName select s, true, OfficeOpenXml.Table.TableStyles.Medium2); In my case my range has to be an offset formula.. i managed to get this to work with interop.. but i cant run interop on IIS on the server :\ – Luciano Pinto Aug 05 '15 at 13:48
  • I think I see what you are trying to do now. You want a named range but you want its RANGE to be based on relative formula - not to be confused with the range's formula property. That is probably beyond the ability of EPPlus currently. You could insert the XML for the range manually with some XML manipulation. You would pull out the xmldocument for the workbook and insert it similar to how it is done here: http://stackoverflow.com/questions/28493050/importing-excel-file-with-all-the-conditional-formatting-rules-to-epplus – Ernie S Aug 05 '15 at 22:02

1 Answers1

1

To build a new range you want ExcelNamedRange which has a public constructor and not ExcelRange. So something like this will get you a new named range with a formula:

var ws = excPck.Workbook.Worksheets.Add("Pivot_" + table.CODE);
var newRange = new ExcelNamedRange("NewRange", null, ws, "C3", excPck.Workbook.Names.Count)
{
    Formula = "SUM(A1:A4)"
};
excPck.Workbook.Names.Add(table.CODE, newRange);

RESPONSE TO COMMENT FOR VERSION 5:

Yes, in EPPlus 5 they seemed to have set the constructors to internal. Bummer. But, looks like you can do it with the AddFormula method:

var sheetName = "Pivot_" + table.CODE;
var ws = excPck.Workbook.Worksheets.Add(sheetName);
var range = excPck.Workbook.Names.AddFormula(sheetName, "SUM(A1:A4)");
range.Address = $"{sheetName}!G2";
Ernie S
  • 13,902
  • 4
  • 52
  • 79