Detecting Excel Cell Formats
To find the format of cells use Excel's Cell("format",A1) function rather that interrogating the datatypes which would be much slower, harder and prone to problems, eg: 0.45% != 45%.
private void button1_Click(object sender, EventArgs e)
{
// evaluate the Format of Cells A1 thru to A7
using (var rnEvaluate = xlApp.Range["C1:C1"].WithComCleanup())
{
for (int i = 1; i < 8; i++)
{
rnEvaluate.Resource.Value2 = "=CELL(\"format\",A" + i.ToString() + ")";
string cellFormat = GetExcelCellFormat(rnEvaluate.Resource.Value2);
System.Diagnostics.Debug.Write(cellFormat);
}
}
}
private string GetExcelCellFormat(string cellFormat = "G")
{
switch (cellFormat.Substring(0, 1))
{
case "F" :
return "Number";
break;
case "P" :
return "Percentage";
break;
case "C":
return "Currency";
break;
case "D":
return "Date";
break;
default :
return "General";
break;
}
}
The .WithComCleanup() is because I am using VSTO Contrib.
Detecting All Excel Cell Formats at once
Is there any way that would allow me to load texts or formats all at once?
Simply use the above method to detect all the cell formats (using AutoFill) and add them to a objectArray. Say for example I wanted to know the cell formats for columns A & B:
Using this VBA code I could to get all cell formats (at once without iterating over cells):
Range("C1").Select
ActiveCell.Value2 = "=CELL(""format"",A1)"
'Fill Down
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C6"), Type:=xlFillDefault
'Fill Across
Range("C1:C6").Select
Selection.AutoFill Destination:=Range("C1:D6"), Type:=xlFillDefault
Here is the above VBA code converted to C# and storing the formats in an object array:
var filepath = @"C:\temp\test\book2.xlsx";
var xlApp = new Microsoft.Office.Interop.Excel.Application();
//Optional but recommended if the user shouldn't see Excel.
xlApp.Visible = false;
xlApp.ScreenUpdating = false;
//AddToMru parameter is optional, but recommended in automation scenarios.
var workbook = xlApp.Workbooks.Open(filepath, AddToMru: false);
//This operation may take a little bit of time but no where near 15 minutes!!!
var cell = xlApp.Range["C1:C1"];
cell.Value2 = "=CELL(\"format\",A1)";
//Fill Down
cell.AutoFill(xlApp.Range["C1:C6"], Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillDefault);
//Fill Across
cell = xlApp.Range["C1:C6"];
cell.AutoFill(xlApp.Range["C1:D6"], Microsoft.Office.Interop.Excel.XlAutoFillType.xlFillDefault);
//Get cell formats into object array
object[,] rangeFormats = xlApp.get_Range("C1:D6").Value2;
The Excel Percentage Conversion Trick
I have some inconsistent data. Sometimes there is 0.45, and sometimes 0.45%
If the only data inconsistencies you're having is with % values then here is a trick.
Presumably the percentage values will be in a column, to convert them, copy the column of values (in column A):
Make sure you set a column with values of 100 (as shown in column B)
Right click the cell in the column of 100's and choose Paste Special:
Choose Values and Multiply:
Excel will convert them to real numbers:
Obviously you can do this programmatically. Simply record the operation as a Macro and convert the VBA to C#.
and of course later i see it as 0.0045 in code.
Note: The code is right, 0.45% is not 45%, 0.45% is less than half a percent! If a particular customer is sending you files expecting you to break the laws of mathematics and treat 0.45% = 45% then there is a good chance they may suddenly start getting 100x more or 100x less. I'd politely point out they need to change it. Don't try and program around this. If this is why you want to look at Cell Formats then all you're doing is troubleshooting a symptom rather than fixing the root cause which will exacerbate the issue and hide a much bigger problem. Just politely point it out to the sources you have no control over there could be some massive problems by x100 fold and insist it needs to be corrected. Otherwise I expect to see a hilarious story about it in DailyWTF featuring this code:
var val = rangeValues[1,1].ToString();
var cellFormat = rangeFormat[1,1].ToString();
if (val.EndsWith("%") && val.Replace("%","") < 1 && cellFormat == "G") {
dailyWTFval = val.Replace("%","") * 100;
}
else
dailyWTFval = val;
}
