I am developing a selenium application. Selenium visits my sharepoint site and from there it downloads one excel sheet. For now,forget about selenium.
I want c# to handle the excel sheet opened. Handle in the sense I would like to do some filtering a coloumn and do store all the values(after filtering) to a temporary variable?Is this possible?
How can I acheive this?
Any comments would be really helpful.
Yes, it is possible. The easy to find but hard to do solution would be Office Interop: E.g. open excel and force it to execute your program. This works, but is hacky, slow and hard to debug. I would recommend EPPLUS - this can easily open and modify xlsx files and is fast as hell compared to excle interop. It's free http://epplus.codeplex.com/
The official way to work with Office documents in general is to use Microsoft's own Open XML SDK to handle Office XML files (docx, xlsx etc). You don't need to install Excel on your machine to use the SDK. You can find documentation and samples for working with Excel files at "Working with Open XML and Excel"
Using Interop is a horrible idea, because you have to create a new instance of Excel for every document, which you have to ensure it's closed when you finish. This is an unmanagable situation for server applications (e.g. web sites) where multiple threads may be trying to work with Excel files at the same time. Excel Interop is a quick way to bring down your server.
There have already been a few solutions out there how to handle Word/Excel/Powerpoint documents with C#. I'm actually just going to repeat what they say, but I'll provide some more code. I had to develop a software that allows automatization and the first question you need to ask yourself, what is the time constraint. That's why I'm going for, because it is pretty fast in implementation. You can find something about it here:
Due to some limitation with objects, you might take a look at NetOffice, which actually does the same functionalities than Microsoft Office offers, but it helps you to handle objects easier.
http://netoffice.codeplex.com/
To be honest, if you post a question on Microsoft or NetOffice forum, any programming that involves Interop with Microsoft, will be a paid service and you won't have that fast a reply. In that case, you'll have to look up by yourself. Now for the OpenXML: http://openxmldeveloper.org/ I won't be able to tell you much about it, because it requires more time and more knowledge to use it, but you'll find a lot of videos how to work with it and how to handle objects/replacements of objects. Also a pro for OpenXML it became a standard and recommanded by the Admininistrator of Microsoft forums. Con of Microsoft Office Interop -> Server management and automatization won't work, depending on the server configuration. Also you'll probably face a lot of issues with COM setups, rights, which will lead to a lot of hours/days of debugging. I wish, I could tell you more about the OpenXML, but didn't have the time to work more in dept with it.
Now about your issue in C#, if you plan to do it with Microsoft.Office.Interop, you might first of all download/integrate the library into your Visual Studio. Now, following code will help you to open a file, go through worksheets and go through rows/columns and to save it.
Code:
using Excel = NetOffice.ExcelApi;
Excel.Application xlsApp = new Excel.Application();
or:
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook xls = null;
excel = new Microsoft.Office.Interop.Excel.Application();
object missing = Type.Missing;
object trueObject = true;
excel.Visible = false;
excel.DisplayAlerts = false;
xls = excel.Workbooks.Open(excelFile, missing, trueObject, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing);
You can choose between both. But I would go for the Microsoft one:
try
{
// Must be surrounded by try catch to work.
// http://naimishpandya.wordpress.com/2010/12/31/hide-power-point-application-window-in-net-office-automation/
xlsApp.Visible = true;
xlsApp.DisplayAlerts = false;
}
catch (Exception e)
{
Console.WriteLine("-------Error hiding the application-------");
Console.WriteLine("Occured error might be: " + e.StackTrace);
}
Excel.Workbook workbook;
workbook = xlsApp.Workbooks.Open(configuration.XLSExportedFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Now, you want to move between worksheets or maybe shapes in that worksheet:
foreach (Excel.Worksheet sheet in workbook.Sheets)
{
workbook.Activate();
sheet.Activate();
foreach (Excel.Shape shape in sheet.Shapes)
{
}
}
Now if you want to paste some values into a specific range, here you go:
sheet.Range(cellmapp).PasteSpecial();
Where cellmapp, you a range from A1:B2 for example. For autofiting values to their cell size, you can do following code:
sheet.Columns.AutoFit();
sheet.Rows.AutoFit();
Now, once you finished all your modification process, very important with COM, always close them.
workbook.SaveAs(File);
workbook.Close();
xlsApp.Quit();
xlsApp.Dispose();
In case you are afraid some Excel processes are stuck, go for
private void ManageXLSProcesses()
{
Process[] processes = System.Diagnostics.Process.GetProcessesByName("Excel");
foreach (Process p in processes)
p.Kill();
}
But you should avoid doing it in the harsh way. It's up to you, to see which way to go if you work with COM or OpenXML, but the future will be OpenXML, because Microsoft Office Interop are easy to write, but hard to handle during runtime. This is supposed to give you an overview of the coming code that youll write. Make your own choices of implementation. Good advice, spend more time into OpenXML since it's a standard, because COM management is going to be harsh in the future with newest version of Microsoft Office 2013 ++.
It is prossible with Interop, check this link http://www.dotnetperls.com/excel . u can either do the filter through excel or load all columns in an own object and do the filtering with C#, whatever you prefer.
