Cells.ExportArray

[claus_a_madsen]

Hi

When "copying" in data using ExportArray and there are empty cells in the sheet Excel usually sets the cell-value to: [Null] - which can be checked with standard embedded software tests for empty. However AsPose returns (NULL). How to change (NULL) into [Null]?

Regards

Claus

[Amjad_Sahi]

Hi Claus,

Well, I think you may manually do it using your code.

E.g.,

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\test_rangecopy1.xls");
object[,] oExcelRangeValues;
oExcelRangeValues = (System.Object[,])workbook.Worksheets[0].Cells.ExportArray(0, 0, 2, 4);

Workbook wkb = new Workbook();
Worksheet oWorksheet = wkb.Worksheets[0];

//Loop through the array and input "Null" manually
//if the particular array element is null.
for (int i = 0;i<2;i++)
{
for (int j =0;j<4;j++)
{
if (oExcelRangeValues[i,j]==null)
{
oExcelRangeValues[i,j]="Null";

}

}

}
oWorksheet.Cells.ImportTwoDimensionArray(oExcelRangeValues, 5, 2);
wkb.Save("d:\\test\\getarray.xls");

Thank you.

[claus_a_madsen]

Hi

The thing is - if you have exported the data using ExportArray then the data has moved outside of AsPose - the question is how to change the values here. In the Excel-Sheet before ExportArray is being called the values in the empty cells are [Null] (Excels way to inform that the cell is empty). I have not been able to find a data-type/value that matches the (NULL) - which is a problem!

Can I at the same time as when using ExportArray get all the CellTypes from Excel - maybe I can use this instead to change the values....

Regards

Claus

[Amjad_Sahi]

Hi,

Yes you may find it, you may check a cell's value type using Cell.Type Property which denotes CellValueType enum. So you may find whether it is null, double, boolean, string, error etc.

E.g.,

if (cell[i,j].Type ==CellValueType.IsNull)

{

//..............

}

Thank you.

[claus_a_madsen]

Hi

Ok, I see. Now the question is, exists a method - similar to ExportArray - that return an array with all the Cell-Types? - This would be much better, as if that is not possible you would not be able to use ExportArray, well only use it in cases where you know that the sheet has been "filled" out. In case such a method does not exist you need to loop over all the cells and populate the array yourself - as in order to figure out CellType, you would need to loop around anyway.

Can that really be the case?

Regards

Claus