excel

expand data into unmerged cells

I have a spreadsheet that has merged cells in it an I want to unmerge the cells and have the data expanded into the unmerge rows. How can I do this programetically or through macros? ...

Present tabular data on webpage so the user can directly copy it into Excel.

On my webpage I want to able to present tabular data and give the user the possibility to copy it directly into excel. This however doesn't work, all of the data is pasted in one cell: <table> <tr><td>Column 1</td><td>Column 2</td></tr> <tr><td>Data 1.1</td><td>Data 2.1</td></tr> <tr><td>Data 1.2</td><td>Data 2.2</td></tr> </table> Ho...

Excel Combining Multiple Rows

I feel like I am missing something simple I would like to do with Excel but I am asking the question incorrectly on Google...here it goes. I'm taking a look at some Excel sheets for a friend who runs a race timing company. At the end of a race he has an excel sheet with the following format for a series of races Name | Gender | Age | ...

Why is there still a row limit in Microsoft Excel?

Until Office 2007, Excel has a maximum of 65,000 rows. Office 2007 bumped that up to a max of 1 million rows, which is nicer of course; but I'm curious -- why is there a limit at all? Obviously, performance will slow down exponetially as you increase the spreadsheet size; but it shouldn't be very hard to have Excel optimize for that by s...

Open XML SDK 2.0 - how to update a cell in a spreadsheet?

I want to update a cell in a spreadsheet that is used by a chart, using the Open XML SDK 2.0 (CTP). All the code samples I have found insert new cells. I am struggling with retrieving the right worksheet. public static void InsertText(string docName, string text, uint rowIndex, string columnName) { // Open the document for editing....

Is there a better way (besides COM) to remote-control Excel?

I'm working on a regression-testing tool that will validate a very large number of Excel spreadsheets. At the moment I control them via COM from a Python script using the latest version of the pywin32 product. Unfortunately COM seems to have a number of annoying drawbacks: For example, the slightest upset seems to be able to break the c...

EXCEL XOR multiple bits

Okay I have two cells with a string of bits 0111010 and 0101011. I want to XOR the two together so that the resulting cell would be 0010001. I know you can use this for boolean values =OR(AND(A1,NOT(A2)),AND(A2,NOT(A1))) but it doesn't work for a string of bits. ...

Open .csv file containing special characters in Excel

I have a system that exports a .csv file and some of the rows contain special box characters such that the data looks like this: Please specify the primary type of opportunity which you’re proposing: └─ Please specify what type of sport: └─ What is this person’s vocation? └── How long have they been in the industry? However, when I op...

Load CSS In Excel

Hi, in .net I have created an excel report which takes a datagrid's render output and puts it into the excel. The problem I am having is that the CSS classes that are used in the datagrid are not properly displayed because the CSS file is not loaded on the excel document. Can someone recommend a good way to go around this other than in...

Implementing Excel and VB's IRR function

I need to port the IRR function found in Excel and VB to ActionScript. Excel IRR formula Any idea how to find the "source" to such functions? does anyone have this implemented in some other c-like language? ...

How to troubleshoot why Excel disables an add-in?

I've written an Microsoft Excel add-in in .NET using Add-in Express, and deployed it to about 50 different user sites, all running Office 2003 on Windows XP. The add-in works fine at the majority of sites, but in a few cases, Excel seems to disable the add-in after a while, and they have to find it on the disabled items list to re-enabl...

copy & paste isolation with win32com & python

Is there a way of using python and win32com to copy and paste such that python scripts can run in the background and not mess up the "users" copy and paste ability? from win32com.client import Dispatch import win32com.client xlApp = Dispatch("Excel.Application") xlWb = xlApp.Workbooks.Open(filename_xls) ws = xlWb.Worksheets(1) xlApp.Vi...

Does anyone know a better alternative to MS Excel's Solver?

My company has to crunch a lot of data and part of the process involves running the solver and plotting a graph through resulting data points. Obviously there is a lot of copy and paste involved and the whole process is shaky, error prone and all round cluster-fudge. I was wondering if there was an alternative to the solver that can be u...

Merging Cells in Excel using C#

I have a database which contains 5 tables. Each table contains 24 rows and each row contains 4 columns. I want to display these records in Excel sheet. The heading of each table is the name of the table, but I am unable to merge the columns for heading. Please help me. ...

What does <> mean?

I have seen this before in SQL and VB, I am now reverse engineering an Excel speadsheet and have come across the following formula: =IF(D23<>0,"Insufficent",0) I am converting it to ActionScript: var result:String = [condition] ? 0 : "Insufficient"; but I am unsure of what D23 <> 0 means, is it simply "not equal"? ...

Migrating to VSTO / Excel Interop Options

Hi We currently have an Excel spreadsheet containing a large amount of VBA code. The VBA code is responsible for: Manipulating, transforming and formatting sheet data. Making calls to C# and C++ COM exposed functionality. In doing so marshalling the sheet data into the C#/C++ methods, getting the results and updating the sheet with ...

Excel: How do I take the first character of 1 cell and prepend it to another cell and place it in a 3rd cell?

Hi I have first names in one column and second names in another, I want to create a third column that contains the first character from the first name and add it to the surname creating first initial + surname. John & Smith = jsmith How can I do this using Excel? Thanks all ...

SQL behind an external data query in Excel

I have inherited an Excel spreadsheet which contains an external data query. I can edit the query, which provides a list of the columns returned, but it does provide the table that this data comes from. Is there any way to retrieve the actual SQL behind the query? ...

Implementing Search As You Type in Excel 2003

I have a excel worksheet with thousands of records. I would find/implement a Search as you type application on Excel. Are there any plugins for Excel that does this? Or do I need to use VB to code this. If so, how do I go about it? Thanks. For example, I want to highlight and jump to first matching cell as user types in the search term ...

Error: <target>.ColumnName and <source>.ColumnName have conflicting properties: DataType property mismatch.

I am trying to merge multiple excel files using DataTable.Merge() option For Each fileName As String In Directory.GetFiles("C:\TEMP\", "*.xls") Dim connectionString As String = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=""Excel 8.0;HDR=NO;IMEX=1;""", fileName) Dim adapter As...