Add your own alternative version
Stats
1.4M views 26.3K downloads 244 bookmarked
Posted
2 Oct 2003

Comments and Discussions


You need to use the .SpecialCells method.
I have not tested this just yet, but here it goes. To get to the last cell in the last column you can use this:
Where curCell is a an Excel.Range object.
curCell.SpecialCells(xlCellTypeLastCell, System.Type.Missing).Select
Here is the method signature:
.SpecialCells(Type, Value)
The values for Type and Value are listed below.
Type Required XlCellType. The cells to include.
XlCellType can be one of these XlCellType constants.
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells
Value Optional Variant. If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type. Can be one of the following XlSpecialCellsValue constants:
XlSpecialCellsValue can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues





Hi
Need help!
I have 2 excels,I am comparing the 1st with 2nd.
If a row in 1st excel is not found in 2nd excel, then I need to insert the row from 1st excel to 2nd excel.how do i do it?





U can use StreamReader and StreamWritter.........





Hi,
I have a huge and very complex spreadsheet some of the rows locked, hidden, merged, grouped, with formulae all over, protected (and what not!); that I'm supposed to read within C#.
I had some initial problems doing this with the password protection, COM errors etc.. that I could resolve.
Now I'm having an unusual problem..
I read a range of values using 'a while loop' and 'get_range' as follows:
object m;
m = System.Reflection.Missing.Value;
//first time
string strPMSheetField = "BN";
string strPMCurRow = "6";
objPMRange = (Excel.Range)excelPMWorksheet.get_Range(strPMSheetField + strPMCurRow, m);
if (objPMRange.Value2 != null)
strName = objPMRange.Value2.ToString();
else
strName = "";
//loop down the rows
while (strName != "")
{
//add the name to my array list etc. //
//set next row no.
strPMCurRow = Convert.ToInt32(strPMCurRow) + 1;
//read data
objPMRange = (Excel.Range)excelPMWorksheet.get_Range(strPMSheetField + strPMCurRow, m);
if (objPMRange.Value2 != null)
strName = objPMRange.Value2.ToString();
else
strName = "";
}
What is happening is until some rows it gets the value fine, but at some specific rows, it skips reading the current row and
gets the values in the following cell (row).
Not sure, but I'm guessing that some rows are 'locked'/'hidden' under a specific logic and the get_range when it comes across such 'locked' field is simply going to the next one. ???
I couldn't find that specific logic anywhere in spreadsheet to hide/lock but I'm assuming it.
In the debug it always shows 'hidden=false', 'locked=false' or says that property 'threw exception'
My Trials:
1. I tried to set these before I read the value but in vain:
excelPMWorksheet.get_Range(strPMSheetField + strPMCurRow, m1).Locked = false;
excelPMWorksheet.get_Range(strPMSheetField + strPMCurRow, m1).EntireRow.Locked = false;
excelPMWorksheet.get_Range(strPMSheetField + strPMCurRow, m1).EntireRow.Hidden = false;
2. I tried to read the whole range BN6:BO136 into a 2dim Array, the result is the same: If there are 50 rows only 46 would show up and 4 are skipped.
3. I wrote a VBA Module to see if it happens there  it worked fine there (no rows were skipped).
Please help.. I'm lost..
ap





I am not sure what is going on, there must be some strange permissions on the cells. I tried it with just a sample sheet with some rows hidden and some rows locked and it got every value. I have tried just about everything I can think cell formatting wise to get it to skip some rows with no luck. The only suggestion that I have is to take a hard look at the rows that are giving you problems and check out their formatting. Sorry to not be any more help.





after I finished working with a workbook, should I "kill" the worksheet object or leave it to the garbage collector ?
in order to close the workbook what overloads I have to load?
excelWorkbook.Close(___,____,____); ?
I'm almost finished working on my homework thanks to you
(i have to copy the results to dataset and to show a chart, but i know how to do it)
Thanks to you and to THE CODE PROJECT !!!!





excelWorkbook.Close(bool,bool,bool);
I coulnd understand it at first because in the msdn instead of "bool" (true/false) was written "object so I couldnt understand what they ment to say.....
sometimes it's very frastrating to code but I guess this was the easiest "problem" to handle.





hi I'm new in programming...
and where can I see all the procedures in the Excel class ?
thanks a lot !!!





Here is a link the all of the objects in the Excel Namespace: http://msdn.microsoft.com/enus/library/bb149081.aspx[^]
They are pretty much the same functions that you can find in VBA within Excel, so you can look at the object model in VBA for just about the same thing.





OK. I saw that there are excel 2003 documentation (VBA) and 2007(the link in your answer).
I found what I wanted but there are only vb examples... When I tried to convert it to c# I got errors...
all I need to do is to copy a range of columns from one workbook to anoter through c#.
can you advice me about this?
http://msdn.microsoft.com/enus/library/bb178833.aspx
http://msdn.microsoft.com/enus/library/bb178854.aspx
http://msdn.microsoft.com/enus/library/bb210384.aspx
Worksheet.Copy Method
http://msdn.microsoft.com/enus/library/bb179176.aspx
Worksheet.Paste Method
http://msdn.microsoft.com/enus/library/bb179200.aspx
With Worksheets("Sheet1")
.Range("C1:C5")
.Copy.Range("D1:D5")
.PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
End With
When I tried to convert from vb to c# i got this :
CONVERSION ERROR: Code could not be converted. Details:
 line 3 col 75: invalid EndOfStmt
Please check for any errors in the original code and try again.
thank you very much.





Here is an Example of how you could do it:
Excel.Range destination = (Excel.Range)excelWorksheet.get_Range("C1", "C4");<br />
excelWorksheet.get_Range("A1", "A4").Copy((Excel.Range)destination);
This takes the Contents of A1:A4 and copies them over to C1:C4. Hope this helps.





Thanks a LOT !!!!!!!
this is how I did it (with your assistance, of course)
Excel.Application excelApp = new Excel.ApplicationClass();
excelApp.Visible = true;
string workbookPath = "e:/111.xls";
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);
string workbookPath2 = "e:/222.xls";
Excel.Workbook excelWorkbook2 = excelApp.Workbooks.Open(workbookPath2, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Excel.Sheets excelSheets2 = excelWorkbook2.Worksheets;
string currentSheet2 = "Sheet1";
Excel.Worksheet excelWorksheet2 = (Excel.Worksheet)excelSheets2.get_Item(currentSheet2);
Excel.Range destination = (Excel.Range)excelWorksheet2.get_Range("C1", "D9");
excelWorksheet.get_Range("A1", "B9").Copy((Excel.Range)destination);
by the way, is copying thousands of columns is fast with this Excel Engine ?
or in that case I sould use third party components(like Infragistics Excel Engine) ?
thanks again, by the way how did you learn to work with this Excel class ?





Hi,
I am using the above code in Visual studio 2005, but I keeping getting Com Exception error
Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
any suggestions whats going wrong?
Thanks
/M





That is odd, when are you getting the exception, at Compile Time? I have used the code in VS 2005 and have not had any problems. I assume you have Excel installed on the machine you are getting the exception from.





I am getting error at runtime on this line
Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);





Here is the exception detail:
System.Runtime.InteropServices.COMException was unhandled
Message="Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"
Source="Interop.Excel"
ErrorCode=2147352565
StackTrace:
at Excel.Sheets.get_Item(Object Index)
at ExcelExample.ExcelClass.Main(String[] args) in C:\Documents and Settings\Manu\My Documents\Visual Studio 2005\Projects\ExcelExample\ExcelClass.cs:line 38
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()





I just played with it a little in VS 2005 and did not have a problem. Are you sure that the Sheet you are trying to access exists with the name you are using? Also what version of Excel are you using, I have tested this on XP and 2003 formats.





I am using VS 2005, and I used Excel 2003 when I had this exception.
Now I am using Excel 2007 and having issues with libraries.
How to use this code for Excel 2007?





For the using statement I had to do this:
using Excel = Microsoft.Office.Interop.Excel;
There is one other line you will have to change which is switching XlWBATemplate.xlWBATWorksheet to Excel.XlWBATemplate.xlWBATWorksheet .
Hope this helps.





When I have a date cell in excel such as 3/15/2008
When I read it into C# string, I get the value as 39527.
How to Convert into datetime?
If I use Convert.ToDateTime(decimal/string/double..), I'm getting an invalid cast exception.
Please help
Thanks
ap





Use this function to convert the serial date (Excel returns the days since 1/1/1900):
DateTime GetExcelDate(int serialDate)<br />
{<br />
int l = serialDate + 68569 + 2415019;<br />
int n = (int)(( 4 * l ) / 146097);<br />
l = l  (int)(( 146097 * n + 3 ) / 4);<br />
int i = (int)(( 4000 * ( l + 1 ) ) / 1461001);<br />
l = l  (int)(( 1461 * i ) / 4) + 31;<br />
int j = (int)(( 80 * l ) / 2447);<br />
int day = l  (int)(( 2447 * j ) / 80);<br />
l = (int)(j / 11);<br />
int month = j + 2  ( 12 * l );<br />
int year = 100 * ( n  49 ) + i + l;<br />
return new DateTime(year, month, day);<br />
}
Now Excel will return a double so 3/15/2008 is actually 39527.0. The decimal part is for the time. So if you need a time instead of just the date, you will have to modify the code a bit. I did get the algorithm to do this here: http://www.codeproject.com/KB/datetime/exceldmy.aspx[^]
It seems that Excel fixed the 2/29/1900 problem, that the article mentioned, so do not worry about that anymore.





Thanks a lot! This helped!





Thanks a lot...GRAZIE MILLE (in Italian)





i am automating my formatted excel work book. now i want to first retreive the last filled row of an already existing excel sheet and then want to fill my array day next to that row. please help me ASAP. its bugging me.
shrads





You can get the last row of your sheet using the following code where CurSheet is the name of your WorkSheet object:
Excel.Range lastRowRange = CurSheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, System.Type.Missing);<br />
int lastRow = lastRowRange.Row;
After you get the last row, you can just add one to it and insert your values into the row after the last row in the sheet.
lastRow++;<br />
Excel.Range nextRowFirstCell = (Excel.Range)CurSheet.get_Range("A" + lastRow, "A" + lastRow);
Then you are ready to start filling in values.





i am automating my formatted excel work book. now i want to first retreive the last filled row of an already existing excel sheet and then want to fill my array day next to that row. please help me ASAP. its bugging me.....
shraddha
shradhs..





Hi,
How to Fill Different Sheets using dataset in same excel File in ASP.Net(C#).
Dataset.tables[0]=sheet(1)
Dataset.tables[1]=sheet(2)
..........
Dataset.tables[n]=sheet(n)
How to handle like this,
Thanks





Does this method work for web applications or is it just for Windows applications?
I tried a similar method for Word and although it worked fine locally (generated Word documents), it did not work when run remotely, from a workstation (using http://sitename/generateWord.aspx).






I have an urgent situation that must collect worksheets from multiple xls files and add them to a single workbook. How would one attempt to bring this about using vb.net or c#.net? <urgent>
Cybertec





I'm using VS 2005 and I added the "Microsoft Excel 11.0 Object Library" to my project, and I specified "using Excel;", but when I try to compile, I get the error "The type or namespace name 'Excel' could not be found (are you missing a using directive or an assembly reference?)"
I know the page said to use 10.0, but that is not available on the list; only 11.0 and 5.0 are. Does it make a difference that I used 11.0 instead?
What else might be wrong?
Thanks.
Cat





That is an odd problem. I have the Excel 11.0 library working for mine, so that should not be the problem. I am shooting in the dark right now on this one, but check the references folder in your project and make sure the Excel and Microsoft.Office.Core (I think this one is put in automatically when you add the Excel one), and make sure in the properties of references that Copy Local is true and Isolated is false, that is what they are on in my project. If this does not work, let me know and we can try to figure out something else.





They are. But I just found out what was wrong. I had to resolve it to the suggested "using Microsoft.Office.Interop.Excel;" before it would work. Then I had to go and resolve every instance of "Excel" to the same thing.
Now if only I could find out why the .get_Range(cell, cell) won't work...
Cat





In the using statement up top you can use:
using Excel = Microsoft.Office.Interop.Excel;
That way you can use Excel.whatever in your code.
When you use the .get_Range , make sure you cast it to a Excel.Range object.





Ah, thank you! That helped a lot, both things.
Cat





Hi,
I have hat the some problems. They are solved now but now I have another.
The name 'XlWBATemplate' does not exist in the current context
I have now idea whats wrong.
Thank your for your help





Try using this line:
Excel.Workbook newWorkbook = <br />
excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Basically all I did was add an Excel. in front of XlWBATemplate.xlWBATWorksheet<br />
Hope this helps.





I have several Excel spreadsheets which are outputted by SAP that I need to insert into a database. I have been able to do a bulk copy but I have to first edit the spreadsheet to fix errors. The spreadsheets have a date column (set to text format) that have invalid dates (00/00/0000). Can I perform a find and replace on them?
Or is there a way to view the spreadsheet prior to importing into a SQL table in order to fix errors?
Or is there another way to insert Excel spreadsheet into SQL server on a row by row fashion which allows me to fix the errors?
Thanks
Mike





You can do a find and replace on the cells by using the following code:
excelWorksheet.Cells.Replace("f", "ff", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, true, System.Type.Missing, false, false);
Where excelWorksheet is an Excel.Worksheet object and you are replacing "f" with "ff". Hope this helps.





Thanks! That helped a lot!
Mike





I am not able to do a find.. Can anyone help?





rng = excelCell.Find(JobNm, excelApp.ActiveCell, null, Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, true, System.Type.Missing, false);
i am using this...
But its throwing runtime exception....
Please anyone help





Him iam unable to compare two excel cell values in c#.net. Also please suggest me how to read null cell value in excel.
please help me .
suresh kumar g





To compare two cell values you would need two Excel.Range objects such as:
Excel.Range cell1 = (Excel.Range)excelWorksheet.get_Range("A1", "A1");<br />
Excel.Range cell2 = (Excel.Range)excelWorksheet.get_Range("A3", "A3");
Where excelWorksheet is an Excel.Worksheet object. You can then use the .Value2 method to compare their values. To check for null values just use the following code:
if (cell2.Value2 != null)<br />
{<br />
}<br />
else<br />
{<br />
}





I'm dealing with many worksheets where I cannot just load "sheet1", because the names have been changed.
I just want to loade the 1st sheet from the workbook, and have had no luck figuring out how to make the missing parameter work.
I'm using C++.net 2005 and have implemented the code from this article, making it all work nicely but cannot, load the worksheet unless I provide the actual name. How do I work around this?
Thanks,
David Sexton





To get the first sheet you use the syntax:
Excel.Sheets workSheets = workBook.Worksheets;<br />
Excel.Worksheet curSheet = (Excel.Worksheet)workSheets[1];<br />
curSheet.Select(System.Type.Missing);
Couple of things here, first, the Worksheets object is a 1 based array, in other words the first element is 1 not 0. Second, System.Type.Missing, is used for "optional" parameters. Hope this helps.





Thanks for your help; it works!
I converted everything to C++.net 2005 for this test app. Here's the code for anyone else using VS2005:
Excel::_Application ^excelApp; // Create Excel app pointer.
Excel::_Workbook ^excelWorkbook; // Create workbook pointer.
Excel::Sheets ^workSheets; // Create worksheet pointer.
Excel::Worksheet ^currentWorksheet; // Create Ind. sheet pointer.
Excel::Range ^excelRange; // Create Ind. cell pointer.
String ^workBookPath = "C:\\tmp\\CAN\\MEC90000C1\\MEC90000C116",
^cellData;
// Create Excel application.
excelApp = gcnew Excel::ApplicationClass();
// excelApp>Visible = true; // Make Excel app visible; not using.
excelApp>DisplayAlerts = false; // Suppress prompts.
// Open existing workbook.
excelWorkbook = excelApp>Workbooks>Open(workBookPath, 0, false, 5, "", "", true,
Excel::XlPlatform::xlWindows, "", true, false, 0, false, true, false);
workSheets = excelWorkbook>Worksheets; // Get worksheets from opened workbook.
currentWorksheet = (Excel::Worksheet^) workSheets[1]; // Access 1st worksheet.
excelRange = (Excel::Range^) currentWorksheet>Range::get("B1","B1"); // Set range.
cellData = (String^) excelRange>Text; // Get value.
excelRange = (Excel::Range^) currentWorksheet>Range::get("B2","B2"); // Set range.
excelRange>Value2 = cellData; // Write to a cell.
excelWorkbook>Save(); // Save workbook.
// Save as CSV file.
excelWorkbook>SaveAs(workBookPath, Excel::XlFileFormat::xlCSV, "", "", false, false,
Excel::XlSaveAsAccessMode::xlNoChange, "", false, "", "", true);
//excelWorkbook>Close(false, workBookPath, false); // Close workbook; not using.
excelApp>Application>Quit(); // Exit Excel; closes workbook and Excel.





I want to add a reference for excel to my project, but I didn't succeeded.
I did the instructions step by step and the reference came with yellow triangle of error.
what should I do?
please help me.





That is strange. What version of Excel are you using? Also, what version of Windows are you using?





You have to have the Interop files installed on the dev machine for this to work. The Basic install of Office does not include these files where the Full install does.
If "using Microsoft.Office.Interop;" fails you have to install them. Hope this helps
Darroll
Just Another Developer






General News Suggestion Question Bug Answer Joke Praise Rant Admin Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

