2

I'm working on a database application that uses excel as its interface. Essentially, it's an excel macro that connects to a database through a .NET library.

The main problem is that the clients need to be able to manage huge amounts of data (5000-20000 lines, and about 160 columns). I've managed to minimize the delays on both the database access, and by loading the whole chunk of data as strings directly into the spreadsheet range .Value property (formatted as a string[,]). It was fast, easy and efficient.

But now the client needs support for excel formats. As, in, I need the excel to recognize dates as such, numbers as such, and so on. This, while supporting non-uniform datatypes: a date column might contain a date format, but might as well include text or something else.

I've tried several approaches: -Keep on loading the strings as such, preformatting the column according to its expected datetype (ws.range(range.EntireColumn.Address).NumberFormat = "0", for example). It didn't work. -Load the whole chunk, as usual, and then force Excel to re-load the columns with non-text data types, by reasigning the whole row:

With ws.range(ws.Cells(4, 11), ws.Cells(ws.UsedRange.rows.Count, 11))
    .NumberFormat = "dd/mm/yyyy"
    .Value = .Value
End With

But this one starts to be quite slow when I need to reformat 30 columns on a document with ~7000 rows or so.

-Finally, I tried modifying the function, so that it creates an object[,] array, with the elements in its proper datatypes (datetime, int, and so on). Again, excel is horribly slow at unpacking generic object.

My only guess is that I could load the chunks by data type, but that messes horribly with my current methods.

Is there any alternative to either enhance speed or cut down on transformations?

Thanks in advance.

EDIT:

To clarify: The excel acts as a heavy client that allows several users (each one with its own excel) to read and update a remote database. The biggest problem was actually dealing with table locks for simultaneous writes and such. The problem now is that the amount of data is getting so large that I need to do something to speed up import and export times. I've already made the exporting times bearable (most of the time users don't upload that much data to the database), but the importing times are slow, and the main bottleneck is writting to the spreadsheet.

Furthermore, the users are already accustomed to a similar tool that they were using from another company before I came in, so they are very adamant about keeping the same format. That app sends a request to a webservice, from which it downloads a CSV with the info. Then loads that CSV as a data source into the excel. That approach is not valid for me because I don't have administrative access to the machine where the DB is, nor any other that I can use as a bridge for the WS. I only have administrative privileges to the database itself. I guess I could build the CSV from the queried data in the dll, and then use the same approach than them, though.

user1393610
  • 31
  • 3
  • Why are you using a macro? You can query to a database from excel. – paparazzo Oct 06 '14 at 17:02
  • From what I've found, you can only query to an (external) database to read, not to update it, with the integrated connection to SQL services. The macros and the .NET dll are used to handle writting back to the database, as well as handling concurrence with other users. – user1393610 Oct 06 '14 at 17:46
  • Then why is the title loading to Excel? – paparazzo Oct 06 '14 at 17:53
  • Because I've got to also load the information back to excel. It's a full read-write interface between a database and an excel document. I'll clarify it in the description of the problem – user1393610 Oct 06 '14 at 17:55
  • Have you tried writing an Excel add-on? – Carles Company Oct 06 '14 at 18:19
  • Unless there is a way to write Excel Add-ins with a free development platform, I can't. I'm stuck with Visual Studio Express, as the company won't pay for a license. I'd love to know any way to do that, though. – user1393610 Oct 06 '14 at 18:24
  • Ouch - they won't buy a $600 piece of software? – paparazzo Oct 06 '14 at 18:34
  • Nop. I tried. They've been dragging their feet for months already, and the previous guy had been asking for the same thing for two years. I guess the administratives don't feel it is a necessity. – user1393610 Oct 06 '14 at 18:55
  • Is there any way you could convince your users to interact with the database through Access instead? Its ability to link to dbs is pretty decent. – Blackhawk Oct 06 '14 at 19:18
  • I doubt it. The problem is that before I started, they already were using an application of similar characteristics developed by other company, and their answer to any alternative (I originally intended to do it as a MFC application) is "But this other app already does it". I'll add a bit of background on how that one works. – user1393610 Oct 06 '14 at 19:59
  • 1
    @user1393610 I understand your story but can you narrow your specific problem? Normally, people don't work and can't handle working with 7000 rows x 30 columns at the same time... that's just inefficient anyway - why don't you work on the data in memory (recordset?) and just build a wrapper interface for the users - allow them to do what they need to do but don't dump 210K cells on them as I am sure they aren't capable of reading it all at the same time... you with me? –  Oct 07 '14 at 08:28
  • More like 7K rows x 160 columns, of which 30 are potentially numbers/dates. But yes, that was what I thought at first, load only the data they are actually viewing. There are two problems with that: First, they need to do tons of processing with the data (dynamic tables, formulas, copying whole chunks of data to other spreadsheets, and so on) that concerns the whole sheet, and second, time. I had about 4-5 days to develop a functional core for the application, and it is currently in production. I need to leave something stable so that I can start developing something decent. – user1393610 Oct 07 '14 at 08:55
  • In regards to the last question: I'm pretty much asking for a way to make excel load the data as its intended type from a string matrix on the first try, rather than having to load it and then reload it to make excel understand the data. – user1393610 Oct 07 '14 at 11:38

1 Answers1

0

If you want to create formatted Excel data files in your .NET code then this is a list of popular libraries that can do it:

and article Create Excel (.XLS and .XLSX) file from C# has a longer discussion of various options

If you want to update remote database from Excel VBA code then article VBA code to loop and update MS access database column from Excel and this Google query: "site:stackoverflow.com excel vba update database"

If you want to invisibly open an Excel data file created by your .NET backend for further processing/merging then article Open Excel file for reading with VBA without display has some tips how you can do it

If you want to get better answers from more people then try to clearly narrow the problem down into 1 clear question (https://stackoverflow.com/help/how-to-ask) and also provide some code in a form of Minimal, Complete, and Verifiable example

Community
  • 1
  • 1
xmojmr
  • 8,073
  • 5
  • 31
  • 54