Hot answers tagged

30

The three points you listed seem fair: It is a fairly important project so it has to work - a C# solution would not be as stable or work as well as the existing a VBA-based solution. Indeed, later, you tell: "I would like to take this opportunity brush up on my C# skills as I am currently not as competent in C# as I am VBA" (emphasis mine). In other ...


15

When people manage data, there are three fundamentally different ways they can add value: Computing Storage and Retrieval Forwarding and Sharing. For computing at the level of simple arithmetic, you can't beat Excel. Even if you are an experienced programmer, you can build a spreadsheet in a fraction of the time you'll take to write and debug a computer ...


14

Wikipedia (and my math teacher) tells me: Stacked exponents are applied from the top down. This is reflected the way Python evaluates it. Microsoft is wrong (once more) And Ruby evaluates it as Python, so it's correct without doubt, since Matz can't be wrong.


12

I would support going to C#. Others have covered your points very well, so I won't rehash what they've said. There are definitely a lot of good reasons to stick with VBA. However, one of my employers did an impeccable job at creating meaningful documentation. So much so that design decisions were actually written down with justification - if only all ...


11

I hate to say it, but your arguments just don't hold water. Unit testing. This has been a solved problem for a very long time. There are many Unit Testing frameworks out there. Pick one. You should have been doing this already. I recommend ours, because it integrates into the IDE and provides other great features. Source control This one is a bit tougher, ...


10

The reason why in mathematics stacked exponents are applies from the top down is that the other way you just get multiplication of exponents: (((2^3)^4)^5) = 2^(3 * 4 * 5)


7

If you really want something that works well for you, then I suggest you get used to the idea of "unnecessarily complex"... that's the nature of dealing with Microsoft Office file formats. I (sort of) like your idea of "blocks"... I would make sub-classed block objects, like Table, with Columns and Rows independent of the notion of cells. Then use your ...


6

If the end product that the users want is tabular data that they can edit or perform ad-hoc analysis on, then developing in Excel may be suitable or even advantageous. The last Excel application I wrote was in a DNA sequencing lab. The application queried an Oracle database for a list of experiments to perform, organized the needed primers into 96-well ...


5

isValid() could be a method implemented in a Strategy pattern. IMO, the benefit of the pattern is future modifiability. That is, there will be a future need for new implementations of isValid() and you don't want to change your code that iterates and validates. In the definition of Strategy, that's the Context class. Your select block, which I believe would ...


5

I would advise you to stay with SVN for the MS Office documents for two reasons: It is already there and it is (in my opinion) better for keeping Office documents (look here). Has much more third party tools for doing this. The lock, though can be achieved in Git, is not "the Git kind of way of doing things". If you need these features, stick with the tool ...


5

I'd like to add some ideas/thoughts to @WalterMitty's nice answer. He's right in that it really depends on the purpose of the Excel files. I would add however that if they contain complex rules AND data, you could think about leveraging the power of rules engines. Rules engines such as Drools, OpenRules or OpenL Tablets allow you to use Excel files for ...


5

My question is ... how I can map db tables in excel files to give it to users instantly, when they want to download a table? Simple answer: you can't, unless you have the files prepared in advance. .xlsx files are ZIP archives which can't easily be produced in a streaming fashion, so to give one to the user you will need to create it from the database ...


4

When you are trying to persuade people about the advantages of using a different system you should avoid trying to persuade them. I know that sounds very strange. But what I mean is this: People like using the current system. It works for them. It's easy to use and quick to make changes. To persuade people to use another system, treat them like adults: ...


4

Use MySQL's INSERT... ON DUPLICATE KEY UPDATE syntax to automatically handle the insert/update logic. 40,000 is not that many rows - I'd be surprised if that command took more than a few seconds. Note that you can insert many rows at once: INSERT INTO table (id, name) VALUES (id1, name1), (id2, name2), ..., (idN, nameN) ON DUPLICATE KEY UPDATE id=VALUES(id)...


4

I'd say do the filtering as part of the processing. Programming in Excel is significantly more painful and limited than any server-side technology you could possibly be using. CSV as an output format is much easier to work with than Excel proper, and virtually every programming language can easily output CSV without requiring any libraries (even writing ...


4

You can point out that even Microsoft states in this article: Updating the code to improve features or to fix bugs would require that the Office artifact be re-emailed, re-structured, and re-worked by every single user and in every single file that has been using the customization. The article is about different approaches of developing stuff based ...


4

It really depends on how you intend to move to C# (ie what technology you are going to use). If you are going to use OpenXML, then you are talking a total re-write which, if you have a solution that works, isn't recommended. If you are talking about using Interop, you may find that the process is surprisingly smooth. I've moved a lot of code from VBA to C# ...


4

Gain control of how report data is formatted. Excel simply isn't a data format. It has it's own presentation ideas. You can make this work and it sounds like a lot has already been built help you do this. So maybe you don't want to swim against the tide at this point but you're tied to a lot of things you don't want to be tied to. Different versions of ...


4

You wrote The project I'm working on is web based price calculation and I'll only be here for 3 months and there are nobody in the office that can do any coding Note that these two statements are already showing the main issue here: if the company for which you are working really wants to offer a web based price calculation in a permanent fashion - ...


4

I've shied away from Access because my team is not comfortable using SQL and overall dislike the Access Interface. Here's your answer. There is a strong argument to use Excel because it is what your team is comfortable with, and that is pretty darned valuable. But managing multiple workbooks (with changing names) has already created several version ...


3

Here is a solution I have used often in the past: create a regular Excel document (typically in xlsx format) as as template, containing all column headers, including their title and a default formatting for the columns and maybe formatting for title cells. embed that template into the resources of your program. At runtime, the first step is to extract the ...


3

There are several libraries available in the Java world, the most popular being Apache POI I believe, which is an open source library that enables you to read and write Excel files (officially supports versions 97 to 2007 but should work fine with 2010 too).


3

.xlsx files are actually XML so you can use most any language out there. Perl's CPAN seems to have a number of options for instance. Most of it is going to boil down to how much money you want to spend vs completeness of the open source modules/ability to write your own compiler.


2

As others already said, xlsx is nothing but bunch of zipped XMLs, and there is (some) documentation online, so it's not big deal making it by hand. Anyways, there are lots of libraries around for different languages. We are using Ruby and there is a nice library called axlsx. We also brewed our own, called spreadshoot (more DSL-ish), but it's far from ...


2

Break the large query into smaller queries and loop through each batch. Try smaller batches will take well under 60 seconds to avoid the risk of a timeout due to server overload, which might push the time over 60 seconds. Checking the student ID sounds more reliable than using a time comparison. Are you sure you have an index on student ID? And is student ...


2

To (1): the layout of your scenario sheet should be independent from the layout of the master sheet. The save and load routines should do the mapping between them. So when the input areas in the master sheet changes, you don't have to change all of your scenarios sheets, you only have to change your save and load routines. Same can be achieved for the ...


2

I have some experience with the sort of scheduling problem you present, and the technical stack you're using, and so I can speak from authority and say this is a database problem and belongs in SQL Server, not Excel!. Assuming that these are regular visits that are already scheduled and you only need to allocate your staff to the various tasks, (and staff ...


2

While reflection is slow, doing it a handful of thousand times while processing a file will probably not be an issue: the file access is likely to be slower still. Still, you should be able to improve the performance of the code you posted by moving the initiation of ' field' outside of your loop.


2

Code version control is not the best tool to work on Office files, because they are binary and these tools work on file-level modification. Use a collaboration tool, like MediaWiki (free) or Atlassian Confluence (paid), from which you can easily extract Word document. Or use LaTex to generate the Office files. Let me expand... If you need to collaborate you ...


2

Write a lot of tests. Use the manual output from the people who have been generating those files up to now as the results that must come out of your tests; on the other hand, present very simple situations to those people and collect their results as the first tests on which you start developing. (I can't emphasize enough the words "very simple situations")....


Only top voted, non community-wiki answers of a minimum length are eligible