Efficiently get formatted cell values

Question

I would like to be able to efficiently extract a multidimensional array of formatted cell values ​​from Excel. When I say formatted values, I mean that I would like to receive them in the same way as they appear in Excel with all NumberFormat cells used.

The Range.Value and Range.Value2 properties are great for extracting the cell values ​​of a large number of cells into a multidimensional array. But these are actual cell values ​​(well, at least with Range.Value2, I'm not quite sure what Range.Value does with some values).

If I want to get the actual text displayed in the cells, I can use the Range.Text property. This has some reservations. Firstly, you need to auto-confirm the cells, otherwise you can get something like #### if not all the text is visible with the current cell width. Secondly, Range.Text does not work more than one cell at a time, so you have to iterate over all the cells in the range, and this can be extremely slow for large datasets.

Another method I tried was to copy the range to the clipboard and then parse the text of the clipboard as a stream of data, separated by tabs, and transfer it to a multidimensional array. It seems to work fine, although it is slower than getting Range.Value2, it is much faster for large datasets than getting Range.Text. However, I do not like the idea of ​​using a system clipboard. If it was a very long operation that took 60 seconds, and while this operation is in progress, the user may decide to switch to another application and will be very unhappy to find that their clipboard either does not work or has mysterious data in it.

Is there a way so that I can efficiently get formatted cell values ​​into a multidimensional array?

I have added some sample code that runs from a pair of ribbon buttons in a VSTO application. First set some good test values ​​and number formats, and the second button will display what they look like when they are retrieved using one of these methods in the MessageBox.

Sample output on my system (it may be different on yours due to regional settings):

Output using Range.Value 1/25/2008 3:19:32 PM 5.12345 2008-01-25 15:19:32 0.456 Output using Range.Value2 39472.6385648148 5.12345 2008-01-25 15:19:32 0.456 Output using Clipboard Copy 1/25/2008 15:19 5.12 2008-01-25 15:19:32 45.60% Output using Range.Text and Autofit 1/25/2008 15:19 5.12 2008-01-25 15:19:32 45.60% 

The Range.Text and Clipboard methods give the correct result, but, as explained above, both problems have: Range.Text is slow, and Clipboard is bad practice.

  private void SetSampleValues() { var sheet = (Microsoft.Office.Interop.Excel.Worksheet) Globals.ThisAddIn.Application.ActiveSheet; sheet.Cells.ClearContents(); sheet.Cells.ClearFormats(); var range = sheet.Range["A1"]; range.NumberFormat = "General"; range.Value2 = "2008-01-25 15:19:32"; range = sheet.Range["A2"]; range.NumberFormat = "@"; range.Value2 = "2008-01-25 15:19:32"; range = sheet.Range["B1"]; range.NumberFormat = "0.00"; range.Value2 = "5.12345"; range = sheet.Range["B2"]; range.NumberFormat = "0.00%"; range.Value2 = ".456"; } private string ArrayToString(ref object[,] vals) { int dim1Start = vals.GetLowerBound(0); //Excel Interop will return index-1 based arrays instead of index-0 based int dim1End = vals.GetUpperBound(0); int dim2Start = vals.GetLowerBound(1); int dim2End = vals.GetUpperBound(1); var sb = new StringBuilder(); for (int i = dim1Start; i <= dim1End; i++) { for (int j = dim2Start; j <= dim2End; j++) { sb.Append(vals[i, j]); if (j != dim2End) sb.Append("\t"); } sb.Append("\n"); } return sb.ToString(); } private void GetCellValues() { var sheet = (Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet; var usedRange = sheet.UsedRange; var sb = new StringBuilder(); sb.Append("Output using Range.Value\n"); var vals = (object [,]) usedRange.Value; //1-based array sb.Append(ArrayToString(ref vals)); sb.Append("\nOutput using Range.Value2\n"); vals = (object[,])usedRange.Value2; //1-based array sb.Append(ArrayToString(ref vals)); sb.Append("\nOutput using Clipboard Copy\n"); string previousClipboardText = Clipboard.GetText(); usedRange.Copy(); string clipboardText = Clipboard.GetText(); Clipboard.SetText(previousClipboardText); vals = new object[usedRange.Rows.Count, usedRange.Columns.Count]; //0-based array ParseClipboard(clipboardText,ref vals); sb.Append(ArrayToString(ref vals)); sb.Append("\nOutput using Range.Text and Autofit\n"); //if you dont autofit, Range.Text may give you something like ##### usedRange.Columns.AutoFit(); usedRange.Rows.AutoFit(); vals = new object[usedRange.Rows.Count, usedRange.Columns.Count]; int startRow = usedRange.Row; int endRow = usedRange.Row + usedRange.Rows.Count - 1; int startCol = usedRange.Column; int endCol = usedRange.Column + usedRange.Columns.Count - 1; for (int r = startRow; r <= endRow; r++) { for (int c = startCol; c <= endCol; c++) { vals[r - startRow, c - startCol] = sheet.Cells[r, c].Text; } } sb.Append(ArrayToString(ref vals)); MessageBox.Show(sb.ToString()); } //requires reference to Microsoft.VisualBasic to get TextFieldParser private void ParseClipboard(string text, ref object[,] vals) { using (var tabReader = new TextFieldParser(new StringReader(text))) { tabReader.SetDelimiters("\t"); tabReader.HasFieldsEnclosedInQuotes = true; int row = 0; while (!tabReader.EndOfData) { var fields = tabReader.ReadFields(); for (int i = 0; i < fields.Length; i++) vals[row, i] = fields[i]; row++; } } } private void button1_Click(object sender, RibbonControlEventArgs e) { SetSampleValues(); } private void button2_Click(object sender, RibbonControlEventArgs e) { GetCellValues(); } 

Answer 1

I found a partial solution. Apply the NumberFormat value to the collapsible double value of Value2. This only works for single cells, since returning an array for NumberFormat with different formats in the array returns System.DBNull.

 double.Parse(o.Value2.ToString()).ToString(o.NumberFormat.ToString()) 

Dates do not work with this. If you know which columns contain certain things, such as a formatted date, you can use DateTime.FromOADate in double and then the value. ToString (format) using NumberFormat. The code below is approaching but not complete.

 <snip> sb.Append("\nOutput using Range.Value2\n"); vals = (object[,])usedRange.Value2; //1-based array var format = GetFormat(usedRange); sb.Append(ArrayToString(ref vals, format)); </snip> private static object[,] GetFormat(Microsoft.Office.Interop.Excel.Range range) { var rows = range.Rows.Count; var cols = range.Columns.Count; object[,] vals = new object[rows, cols]; for (int r = 1; r <= rows; ++r) { for (int c = 1; c <= cols; ++c) { vals[r-1, c-1] = range[r, c].NumberFormat; } } return vals; } private static string ArrayToString(ref object[,] vals, object[,] numberformat = null) { int dim1Start = vals.GetLowerBound(0); //Excel Interop will return index-1 based arrays instead of index-0 based int dim1End = vals.GetUpperBound(0); int dim2Start = vals.GetLowerBound(1); int dim2End = vals.GetUpperBound(1); var sb = new StringBuilder(); for (int i = dim1Start; i <= dim1End; i++) { for (int j = dim2Start; j <= dim2End; j++) { if (numberformat != null) { var format = numberformat[i-1, j-1].ToString(); double v; if (double.TryParse(vals[i, j].ToString(), out v)) { if (format.Contains(@"/") || format.Contains(":")) {// parse a date var date = DateTime.FromOADate(v); sb.Append(date.ToString(format)); } else { sb.Append(v.ToString(format)); } } else { sb.Append(vals[i, j].ToString()); } } else { sb.Append(vals[i, j]); } if (j != dim2End) sb.Append("\t"); } sb.Append("\n"); } return sb.ToString(); }