Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>In our desktop environment, I have reached the best mix between performance, flexibility and stability by using Excel via COM.</p> <p>Access to Excel is always via the same thread.</p> <p>I use late-binding (in VB.Net) to make my app version independent. </p> <p>The rest of the application is developed in C#, only this part and some other small parts are in VB, because they are easier in VB.Net.</p> <pre><code>Dim workBook As Object = GetObject(fileName) Dim workSheet As Object = workBook.WorkSheets.Item(WorkSheetNr) Dim range As Object = workSheet.Cells.Item(1, 1) Dim range2 As Object = range.CurrentRegion Dim rrow As Integer = range2.Row ' For XL97, first convert to integer. XL97 will generate an error ' Dim rcolumn As Integer = range2.Column Dim top As Object = workSheet.Cells.Item(rrow, rcolumn) Dim bottom As Object = top.Offset(range2.Rows.Count - 1, range2.Columns.Count - 1) range = workSheet.Range(top, bottom) Dim values As Object(,) values = range.Value </code></pre> <p>Here you have a 2-dimensional array containing the values from Excel. The last statement gets the data from Excel to .Net.</p> <p>Since the limits on the size of a Excel sheet, these cannot get very large, so memory should not be a problem.</p> <p>We have done some tests on performance, on multiple systems. It is optimized to create as few as possible (out-of-process) COM calls.</p> <p>This way was the one that has given us the best performance, specially since the data is directly in an array, and access to this data is faster as going through a dataset.</p> <p>Slow in this solution is starting Excel. But if you need to process multiple files, right after each other, the cost of starting Excel is made only once.</p> <p>Also I would not use this solution in a server environment.</p>
    singulars
    1. This table or related slice is empty.
    1. POC# 2.0 Fastest way to parse Excel spreadsheet
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USGvS
    1. USGvS
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHe wants fast for large sets of data, have you compared the speed of the COM based operation to a datasource based method? I would also be interested to know if your solution can reasonably keep up with the datasource method. (I would rather read and not hold a large datatable in memory)
      singulars
      1. PO
      1. USStingyJack
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload