I’m using aspose.cells in an asp.net application. Before this I was using the interop methods and had no problems. The problem is that some formulas are not being correctly calculated with some specific inputs. For checking I’ve made a save of the .xls and compared with my original .xls and everything is in order. I can’t send the original .xls for privacy reasons but bellow is the code I’m using to access the .xls.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Aspose.Cells;
using System.Collections;
namespace Endesa.App_Code
{
public class DatosExcel
{
private Workbook workbook;
private static DatosExcel instancia = null;
private String pathExcel = HttpContext.Current.Server.MapPath(“./Archivo/EcoCare7Rev6.xls”);
private DatosExcel()
{
}
public static DatosExcel getInstancia()
{
if (instancia == null)
instancia = new DatosExcel();
return instancia;
}
//devuelve los datos de las celdas en el rango y hoja especificados
public ArrayList getRango_ArrayList(int hoja, String celda_supIzq, String celda_infDer)
{
ArrayList list = new ArrayList();
SortedList letras = new SortedList();
letras.Add(“A”, 1); letras.Add(“B”, 2); letras.Add(“C”, 3); letras.Add(“D”, 4); letras.Add(“E”, 5); letras.Add(“F”, 6);
letras.Add(“G”, 7); letras.Add(“H”, 8); letras.Add(“I”, 9); letras.Add(“J”, 10); letras.Add(“K”, 11); letras.Add(“L”, 12);
letras.Add(“M”, 13); letras.Add(“N”, 14); letras.Add(“O”, 15); letras.Add(“P”, 16); letras.Add(“Q”, 17); letras.Add(“R”, 18); letras.Add(“S”, 19);
letras.Add(“T”, 20); letras.Add(“U”, 21); letras.Add(“V”, 22); letras.Add(“W”, 23); letras.Add(“X”, 24); letras.Add(“Y”, 25); letras.Add(“Z”, 26);
int fila1, col1, fila2, col2, cantFilas, cantCols;
fila1 = Int16.Parse(celda_supIzq.Substring(1));
fila2 = Int16.Parse(celda_infDer.Substring(1));
cantFilas = fila2 - fila1;
int indexCol1 = letras.IndexOfKey(celda_supIzq.Substring(0, 1));
col1 = (int)letras.GetByIndex(indexCol1);
int indexCol2 = letras.IndexOfKey(celda_infDer.Substring(0, 1));
col2 = (int)letras.GetByIndex(indexCol2);
cantCols = col2 - col1;
Worksheet worksheet = workbook.Worksheets[hoja - 1];
object[,] array = worksheet.Cells.ExportArray(fila1 - 1, col1 - 1, cantFilas, cantCols);
foreach (object obj in array)
{
list.Add(obj);
}
return list;
}
//devuelve el valor de la celda en la hoja especificada
public String getCelda(int hoja, String celda)
{
//selecciono la hoja
Worksheet worksheet = workbook.Worksheets[hoja - 1];
//agarro la celda
String texto = worksheet.Cells[celda].Value.ToString();
return texto;
}
//ingresa los datos en la hoja indicada
public void setDatosIngresados(int hoja, SortedList datosIngresados)
{
//selecciono la hoja
Worksheet worksheet = workbook.Worksheets[hoja - 1];
int nro;
foreach (DictionaryEntry entry in datosIngresados)
{
if (int.TryParse((string)entry.Value, out nro))
worksheet.Cells[(string)entry.Key].PutValue(nro);
else
worksheet.Cells[(string)entry.Key].PutValue((string)entry.Value);
}
workbook.CalculateFormula();
}
//ingresa el dato en la hoja y celda indicada
public void setCeldaIngresada(int hoja, String celda, String dato)
{
//selecciono la hoja
Worksheet worksheet = workbook.Worksheets[hoja - 1];
worksheet.Cells[celda].PutValue(dato);
workbook.CalculateFormula();
}
//abre el EXCEL
public void conectar()
{
workbook = new Workbook();
workbook.Open(pathExcel);
}
//cierra el EXCEL
public void desconectar()
{
workbook = null;
}
}
}
Please HELP!!!
Hi,
Thank you for considering Aspose.
From your provided code it is not clear to us that which formulas are actually causing the problem. Please provide us further details about the formulas which are causing problem in formula calculation. We will appreciate it a lot if you can share your template file with us. As your template file has some confidential data, so you may make this thread as private by checking “Keep this post as private” checkbox at the bottom when replying to the post, thus only Aspose staff and you, would be able to see your attachments.
Alternatively, you may post your template file(s) directly to us. Since your file contains confidential data, you can email to us, so we can resolve your issue asap. To email please follow these under mentioned steps,
1: click the Contact button in the Post.
2: In the drop down list options click "Send nausherwan.aslam an Email”.
3: Attach the template file and send it.
4: Once you have done it, kindly confirm us on this thread.
Thank You & Best Regards,
Thanks for the rapid response.
I’ll check if I can send you the Excel file and code.
Meanwhile I’ll try to explain the problem with more detail.
The problem appeared after doing the following in this order:
- “getInstancia” if first called
- “conectar”
- The “setDatosIngresados” method is called to Export the data to the Excel
(after this is when I tried the “save” method)
- The “getCelda” method is called
This last call returns “#N/A”. For a couple of cells, not for all.
The Excel has a VERY complicated logic for calculating the results. For this it uses fix and variable (user input) values in 10 different worksheets. My guess is that the problem might by in the order aspose.cells is calculating the results and/or where I place the “calculateFormula” method.
Thanks.
Felipe
Hi,
Thanks for further details, but it seems like some formula calculation problem. Your provided code is only setting the values in the excel template file and all the formulas are already in your excel file. The calculate formula actually calculates the results of all the formulas. Now, the #N/A may be occurring due to some wrong formula, wrong parameters in formula or may be some formula which is not supported by Aspose.Cells. So, we will not be sure what the problem is, until we can either know the exact formulas which are not working or we can check your template excel file. Hopefully you understand my point.
Thank You & Best Regards,
Hi Felipe,
Thank you for considering Aspose.
We have received your template file. We will look into it and figure out the problem soon.
Thank You & Best Regards,
Hi Felipe,
Thank you for considering Aspose.
Please try the attached latest fix of Aspose.Cells (V4.6.0.9) and let us know if you still face any issue.
Thank You & Best Regards,
Hi Felipe,
Thank you for considering Aspose.
Please try the attached latest version of Aspose.Cells. We have fixed the issue of calculating the formulas in the provided template file.
Thank You & Best Regards,