I've come across the problem where Excel will not play nice with UTF-8 encoded CSV files. See this related question on Stack Overflow. I've followed the solution of exporting as UTF-16, however it seems that with UTF-16, the comma character doesn't work as a delimiter, only the tab character. However I can't find anywhere what the corr...
I use Apache POI to write excel sheets. I'm very happy with it, but I have the following problem: my program is multi-currency and internationalized. In the excel sheet I write, I want my currency cells to be properly formatted. For that, I need a routine that would build the right Excel currency format (eg: "[$$-409]#,##0.00;-[$$-409]...
I had an application for returning closest matches to certain values in a large cluster of values( as in my earlier question) and I chose a VBA solution. While in the course of using the said application, I observed that the results for the value of 0.5 were not correct. I had been using the VBA Round funtion which I found to be retur...
Is there a better way I can fill out a spread sheet on a web server (using asp.net) than using interop? EDIT: I wasn't very clear as to what I require: I have a template that I must use that is provided by our customer. In the template are some macros that are password protected that I do not have access to so I cannot generate the exc...
I need to read a Excel 2007 xlsx file in a java application. Does anyone know of a good api to accomplish this task? Thanks in advance for any advice given. -MrPortico ...
I want to use Excel to capture survey results. I want to have a large number of questions each with 5 radio buttons ranging from Excellent to Poor. Each radio button should correspond to a numerical value stored in a set of data cells somewhere (e.g Excellent = 13, Poor = 10, Good = 5 etc). I would like to be able to cut and paste these ...
Hi, Can anyone help me with this...I need to copy a set of rows from one tab to another tab of the same excel document by just clicking a button. Also, can I also get information on how can I copy a set of rows that are hidden and paste it in the same tab without copyng the "hidden" format? Thanks!!! ...
Hi, is there anyone who makes use of the Microsoft Open XML SDK 2.0 for editing Excel Spreadsheet, I edited Open XML documents already by only using XML methods, but as there exists the SDK, does someone know if there are really easy methods for some essential functions? For Example the inserting of content in a cell. Also the Open XML ...
Excel has a Conditional Formatting... option under the Format menu that allows you to change the style/color/font/whatever of a cell depending upon its value. But it only allows three conditions. How do I get Excel to display say, six different background cell colors depending upon the value of the cell? (IE Make the cell red if the v...
Hi All, I am having trouble with a very simple Perl process. I am basically querying an Oracle database and I want to load it into Excel. I have been able to use DBIx::Dump and it works. However, I need to be able to use a variety of Excel formatting tools. And I think Spreadsheet::WriteExcel is the best module that outputs to Excel ...
How can I programmatically remove a (known) password from an Excel VBA project? To be clear; I want to remove the password from the VBA Project, not the workbook or any worksheets. ...
I'm trying to select the "used range". I get the worksheet's UsedRange, then I invoke the Select function on it. I receive HRESULT 0x800a03ec on the select call. Here's the code: COleVariant result; HRESULT hr = AutoWrap(DISPATCH_METHOD, &result, irange, L"Select", 0); I won't bother posting the code for the AutoWrap function (unl...
I am trying to migrate structured documents (i.e. documents that are mostly some metadata and one big table) to a database. When I try to move tabular data from Word to Excel, my main point of pain is handling CRLFs within a cell in Word. Any solution for this? Now, since I will be transferring from Word to Access: What will be the de...
I have code that lets me select a single item in arange: COleVariant vItems = cstrAddr; hr = AutoWrap( DISPATCH_PROPERTYGET, &vCell, irange, L"Item", 2, COleVariant((short)(1)), COleVariant((short)(1))); if (FAILED(hr)) return hr; // Use the dispatch interface to select ...
I have columns with count of Customers and count of orders. how can i show the average count of orders? ...
I would like to cross-reference construct a distance chart similar to the one here (example is a road-distance cross-reference chart) and, ideally, store the data in SQL Server 2008 (preferably the Express version). It needs these properties / abilities Every column has a corresponding row with the same name (ie. not misspelled like my...
I know how to use the OleDbConnection class to open an excel file and select from it, but is there a simple method to read the data from an excel file that has been read into a Stream? And is there also a similar method for QuickBooks? ...
We have a number of Excel-based membership rosters for a non-profit organization and we need to merge these into one master MS Access table. From time to time, we receive updated copies of the Excel-based membership rosters and I need to synchronize these with the master MS Access table. I'm looking for the optimal way to accomplish th...
I'm trying to automate some stuff in MS Excel. When I try to set the Calculation property I get the following error message: 'Unable to set the Calculation property of the Application class' I believe this property should be settable. Any advice appreciated! ...
I'm using Excel 2007 to create a log-scale chart of numbers (specifically the Zimbabwean dollar exchange rate) over time. I'm using an x-y scatterplot and noticing one odd quirk. The range of y values (numbers) spans a factor of about 10^30. On every chart I make using this data, half the gridlines are missing. Specifically, only the gr...