0

I am trying to do a large, match and index on an excel file I read from. Large works fine but It throws an error on the Match line. If i hardcode K = an int, it works just fine.

Current error:

An exception of type 'System.InvalidOperationException' occurred in NPOI.dll but was not handled in user code

I am doing a large, match and index to find top 4 of a list of unsorted rows, i want the associated values to go with them. My logic works just fine in vba or hard coding

Additional information: Cannot get a numeric value from a error formula cell

My ranges are C10:C30 and K10:K30, both ranges are right. 

string cellrange = new CellRangeAddress(firstrow + 1, sheet1.LastRowNum, 10, 10).ToString();
string cellsrange = cellrange.Remove(0, 18);
string cellsranges = cellsrange.Remove(cellsrange.Length - 1, 1);

string states = new CellRangeAddress(firstrow + 1, sheet1.LastRowNum, 2, 2).ToString();
string statesrange = states.Remove(0, 18);
string statesranges = statesrange.Remove(statesrange.Length - 1, 1);

for (int row = 1; row <= 4; row++)
{
    sheet1.CreateRow(row + firstrow + 1).CreateCell(15).SetCellFormula("LARGE(" + cellsranges + "," + row + ")");
    HSSFFormulaEvaluator.EvaluateAllFormulaCells(hssfwb);

    double j = sheet1.GetRow(row + firstrow + 1).GetCell(15).NumericCellValue;

    sheet1.CreateRow(row + firstrow + 1).CreateCell(16).SetCellFormula("MATCH(" + j + "," + cellsranges + ", " + 0 + ")");
    HSSFFormulaEvaluator.EvaluateAllFormulaCells(hssfwb);

    double k = sheet1.GetRow(row + firstrow + 1).GetCell(16).NumericCellValue;

    sheet1.CreateRow(row + firstrow + 1).CreateCell(17).SetCellFormula("INDEX(" + statesranges + "," + k + "," + 1 + ")");
    HSSFFormulaEvaluator.EvaluateAllFormulaCells(hssfwb);

    string stateperc = sheet1.GetRow(row + firstrow+1).GetCell(17).StringCellValue + "-" + j.ToString();

    MessageBox.Show(stateperc);
}
Prisoner
  • 1,839
  • 2
  • 22
  • 38
James
  • 21
  • 8
  • 1
    If you wrap the 'double k =' in a try catch what exception do you get back? – Joshua Drake Oct 31 '16 at 20:52
  • "Cannot get an error value from a error formula cell" – James Oct 31 '16 at 21:32
  • can you narrow down to a line of code by attaching a debugger? – Andrei Epure Oct 31 '16 at 22:15
  • its being thrown when it hits the double k = sheet1.... line. Its saying the Match formula gives an error which makes no sense to me – James Oct 31 '16 at 22:24
  • Have you check those formulas are correct and did return correct value? – Prisoner Nov 01 '16 at 01:46
  • yes they are correct, i have copied them exactly from c# into excel and it works – James Nov 01 '16 at 02:05
  • Have you step though and check those values in code? – Prisoner Nov 01 '16 at 02:53
  • yes I used the text visualizer and copied into excel and it works – James Nov 01 '16 at 12:13
  • sheet1.CreateRow(row + firstrow + 1).CreateCell(16).SetCellValue(5.3); --- if this is put in and comment out the Match and evalutor row, it picks up the k has 5.3 as expected. Something is wrong wiht the match line and I have no clue what – James Nov 01 '16 at 18:37
  • I have found the issue, if i use a 1 or -1 in match it works but not 0. Any idea how to fix this? – James Nov 01 '16 at 18:46
  • If i run it with a -1, it runs but seems to skip every other line and I have no clue why. The row number that counts is right and should be the thing that the Large value is reading off but its not working properly – James Nov 01 '16 at 21:05
  • Are the values in the sheet strings? If so you may want to look at http://stackoverflow.com/a/30125586/19308 or Formulas? http://stackoverflow.com/a/32682857/19308 – Joshua Drake Nov 02 '16 at 19:56

0 Answers0