excel interview questions
Top excel frequently asked interview questions
I am programmatically exporting data (using PHP 5.2) into a .csv test file.
Example data: Numéro 1 (note the accented e).
The data is utf-8 (no prepended BOM)
When I open this file in MS excel is displays as Numéro 1
I am able to open this in a text editor (UltraEdit) which displays it correctly. UE reports the character is decimal 233.
How can I export text data in a .csv file so that MS excel will correctly render it, preferably without forcing the use of the import wizard, or non-default wizard settings?
Source: (StackOverflow)
I have an Excel file that has some Spanish characters (tildes, etc.) that I need to convert to a CSV file to use as an import file. However, when I do Save As CSV it mangles the "special" Spanish characters that aren't ASCII characters. It also seems to do this with the left and right quotes and long dashes that appear to be coming from the original user creating the Excel file in Mac.
Since CSV is just a text file I'm sure it can handle a UTF8 encoding, so I'm guessing it is an Excel limitation, but I'm looking for a way to get from Excel to CSV and keep the non-Ascii characters in tact.
Source: (StackOverflow)
I'm looking for a class for creating CSV Excel files.
Expected features:
- Extremely simple to use
- Escapes commas and quotes so excel handles them fine
- Exports date and datetimes in timezone-proof format
Do you know any class capable of this?
Source: (StackOverflow)
Does anyone happen to know if there is a token I can add to my csv for a certain field so Excel doesn't try to convert it to a date?
I'm trying to write a .csv file from my application and one of the values happens to look enough like a date that Excel is automatically converting it from text to a date. I've tried putting all of my text fields (including the one that looks like a date) within double quotes, but that has no effect.
Source: (StackOverflow)
How can I create an Excel Spreadsheet with C# without requiring Excel to be installed on the machine that's running the code?
Source: (StackOverflow)
In a cell in Excel sheet I have a Date value like:
01/01/2010 14:30:00
I want to convert that Date to Text and also want the Text to look exactly like Date. So a Date value of 01/01/2010 14:30:00 should look like 01/01/2010 14:30:00 but internally it should be Text.
How can I do that in Excel?
Source: (StackOverflow)
I have an Excel spreadsheet containing a list of strings. Each string is made up of several words, but the number of words in each string is different.
Using built in Excel functions (no VBA), is there a way to isolate the last word in each string?
Examples:
Are you classified as human? -> human?
Negative, I am a meat popsicle -> popsicle
Aziz! Light! -> Light!
Source: (StackOverflow)
I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords.
Is there a way of removing or cracking the password on a VBA project?
Source: (StackOverflow)
How can I construct the following string in an Excel formula:
Maurice "The Rocket" Richard
If I'm using single quotes, it's trivial = "Maurice 'The Rocket' Richard" but what about double quotes?
Source: (StackOverflow)
How do you convert a numerical number to an Excel column name in C# without using automation getting the value directly from Excel.
Excel 2007 has a possible range of 1 to 16384, which is the number of columns that it supports. The resulting values should be in the form of excel column names, e.g. A, AA, AAA etc.
Source: (StackOverflow)
I'm using the Excel interop in C# (ApplicationClass) and have placed the following code in my finally clause:
while (System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet) != 0) { }
excelSheet = null;
GC.Collect();
GC.WaitForPendingFinalizers();
Although, this kind of works the Excel.exe process is still in the background even after I close Excel. It is only released once my application is manually closed.
Anyone realize what I am doing wrong, or has an alternative to ensure interop objects are properly disposed of.
Source: (StackOverflow)
This question already has an answer here:
I want excel files on a website to open in Excel when clicked, not get saved on desktop, or get opened embedded in a browser etc. Now obviously it all depends on how everything is configured for each user, but what's the best Content-Type and other settings to achieve just that most of the time?
Source: (StackOverflow)
MS Excel has the following observed MIME types:
application/vnd.ms-excel (official)application/msexcelapplication/x-msexcelapplication/x-ms-excelapplication/x-excelapplication/x-dos_ms_excelapplication/xlsapplication/x-xlsapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet (xlsx)
Is there any one type that would work for all versions? If not, do we need to set response.setContentType() with each one of these mime types individually?
Also, we use file streaming in our application to display document (not just excel - any type of document). In doing so, how can we retain the filename if the user opts to save the file - currently, the name of the servlet that renders the file appears as the default name.
Source: (StackOverflow)
I'm looking for a way to convert xlsx files to csv files in linux, i do not want to use PHP/Perl or anything like that since I'm looking at processing several millions of lines, so i need something quick. I found a program on the ubuntu repo's called xls2csv but it will only convert xls(Office 2003) files(which i'm currently using) but, i need to add support for the newer excel files, any ideas?
Source: (StackOverflow)
If I select a cell containing a formula, I know I can drag the little box in the right-hand corner downwards to apply the formula to more cells of the column. Unfortunately, I need to do this for 300,000 rows!
Is there a short-cut, similar to ctrl+space, that will apply a formula to the entire column, or to a selected part of the column?
Source: (StackOverflow)