Asp.net - Writing To Excel File Containing Formulas Is Extremely Slow
Dec 27, 2011
We have an automatic process that opens a template excel file, writes rows of data, and returns the file to the user. This process is usually fast, however I was recently asked to add a summary page with some Excel formulas to one of the templates, and now the process takes forever.
It successfully runs with about 5 records after a few minutes, however this week's record set is almost 400 rows and the longest I've let it run is about half an hour before cancelling it. Without the formulas, it only takes a few seconds to run.
Is there any known issues with writing rows to an Excel file that contains formulas? Or is there a way to tell Excel not to evaluate formulas until the file is opened by a user?
The formulas on the summary Sheet are these:
' Returns count of cells in column where data = Y
=COUNTIF(Sheet1!J15:Sheet1!J10000, "Y")
=COUNTIF(Sheet1!F15:Sheet1!F10000, "Y")
[Code].....
View 3 Replies
ADVERTISEMENT
Apr 17, 2009
I have a dataset with around 15 table adapters. Everytime I make a change, such as add a column, change a type, or just anything, it takes around 3-4 minutes before studio responds again. The processor and ram are in good shape, but there is a spike in network usage by devenv during this time. In fact, devenv has the higheset network usage all together, even with with IE open and browsing. Why would this be and is there any remedy?[code]...
View 3 Replies
Mar 4, 2010
This method for writing a listview to an Excel spreadsheet is very slow.
Public Sub ListviewToActiveWorksheet(ByVal lv As System.Windows.Forms.ListView)
Dim mSheet As Excel.Worksheet
Dim mRow As Integer = 1
[Code].....
View 2 Replies
Apr 6, 2009
I have looked all over for a solution to this problem and have tried a bunch of different things, but nothing seems to work.I have a sub procedure that does the following:
1) adds two columns to a data table (table1)
2) fills a dataset with several tables in order to make calculations (this determines the data that will be stored in the two new columns of table1)
3) updates ONLY table1, and ONLY the two columns that have just been added (these columns are thus empty for every row before the update begins)
As it is now, the da.Update takes about 3 hours to populate the entire table (it updates about 300 records per MINUTE if I'm lucky). There are approximately 40000 records in table1 for this test subset, but there will eventually be up to 1.5-2 million (in other words, this is a small table currently). I have tried using executenonquery() along with a sql UPDATE command instead of da.Update, as well as many other things, but none of them has improved performance at all. I've also tested it on two PCs, one running Vista and one XP, and there were no differences. I'm using Access 2003. I know Access is not ideal, but for now it is the only option.
I know that the root of this is the line of code with the da.Update, because the giant loop before it that I previously thought was causing the slowdown executes in 30 seconds, leaving only the update before the subroutine is complete. I don't know how exactly the da.Update works, but I've suspected that maybe it has something to do with that? Code is below:
[code]...
View 7 Replies
Feb 1, 2011
(using VB2008)I was wondering if it's possible to implement dynamic (runtime) formulas between objects and properties. My inspiration is Excel, where any cell can have a hardcoded value or be a function of other cells. So via analogy of a property acting like a cell, depending upon the situation, I would like to either give it a defined value or make it a function of other properties of objects of the same type.
There seem to be two basic levels of functionality, 1) the dependent property recalculates its value every time it's accessed, or better is 2) it detects when its input properties change value and recalculates itself only once (as in Excel). But so far I've failed to find any way to do either with ordinary properties. It seems like I would either have to turn each property into a class (and use events, references to parent properties, etc), or use reflection heavily. Either way I feel like I'm going off the beaten path and making things complicated. Has anyone done something like this?
View 8 Replies
May 4, 2011
Visual Basic 2010.I excel in transferring reports on problems in formulas..[code]
View 3 Replies
Feb 6, 2011
What I'm trying to do is extract values, colors, comments, and any formulas for a range of cells in a given worksheet. The ultimate goal is convert a spreadsheet someone has been using as a database into Access tables.
worksheet.Cells(row,column).Value 'extract value WORKS
worksheet.Cells(row,column).Font.ColorIndex 'extract color index WORKS
worksheet.Cells(row,column).Comment.Text 'extract comment WORKS
worksheet.Cells(row,column).Formula 'extract formula DOES NOT WORK
The return value for .Formula is same as .Value. That's not what I need. I'm looking for a textual representation of the formula (to extract rate information embedded within the formula).
View 1 Replies
Aug 2, 2011
I have a range of data that I want to replace with new data.So, I want to clear the old data and replace it with new data.But the formulas in the old data are also required to work on new data.[code]...
View 2 Replies
Feb 4, 2012
I have data that I want to have written into a Microsoft Excel File.I found the following question, but this is using a predefined name where as I want to use a Save Command Window on a menu bar.Coding for Excel sheet in Visual Basic 6 Essentially I have two columns of data, Participant Name & Time In Ring, I want to cycle through an array (I suppose) and have each item on its own line.
View 1 Replies
Mar 16, 2010
I'm fairly new to coding in Visual Basic, and I'm working on a project where I have to allow users to write data to an existing Excel worksheet (by entering the data into a form created in Visual Basic.NET). I've created a form that allows users to enter data such as the following:
[Code]...
View 8 Replies
Mar 15, 2010
I'm working on a project where I have to allow users to write data to an existing Excel worksheet (by entering the data into a form created in Visual Basic.NET). I've created a form that allows users to enter data such as the following:
Name (First, Last)
Mailing Address
Email Address
Gender
Age
...and so on. I've programmed the data to be sent to the Excel file upon clicking on the Submit button in the form itself. I've also created the Excel file and placed it on my C: drive, but the problem I'm having is trying to send the data from the VB form to the Excel file itself. Can someone please show me some sample code or point me in the right direction as to how to get the submitted information from the form to save into the Excel file, please?
I'm trying to get the information to show up in rows and columns in Excel like the following below (the dashes are just to show that the information should be in rows and columns):
Name--------------------------Mailing Address-------Email Address-------Gender-------Age
John Doe---------------------2300 Jackson Street-------- xxx@xxxx.com-------Male---------17
View 4 Replies
Jun 29, 2009
I found some code online for a vb paint application and I modified it for my use, which is a signature application. It uses a tablet connected to the pc to write the signature on the screen and then save it as a bitmap for later retrieval.The only issue i am having right now is that if you write on the tablet too fast, the signature gets broken up alot, any way to quicken the paint process in the code so that I can get a smoother signature?
Here is my code:
Imports System
Imports System.Collections
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization
[Code] .....
View 1 Replies
Nov 9, 2011
I did ana application that interacts with excel (reading and writing files)But when the application ends and I close it, the excel file is still in the task manager procceses (ctrl+alt+del ->Proccesses)How can I completely close it?I'm using vb.net 2010 express
View 2 Replies
Apr 13, 2012
I am building a simple proxy in server vb.NET and so far it has been successful, but then whenever i receive data from the internet and write it back to my browser's network stream using NetworkStream.Write the received data tends to come up slowly to my browser due to reasons i do not know of. But when I chain my application to another Proxy/listener on my local computer (e.g Privoxy), data seems to come up very fast to my browser. If anyone has an answer to this I would really love to know.btw here is the part of my code {subroutine} that receives data and forwards to my browser.
Public Sub ForwardRequest(ByVal Client As TcpClient, ByVal ClientMessage As String)
Dim Browser As TcpClient = Client
Dim BrowserStream As NetworkStream = Browser.GetStream
[code]....
View 1 Replies
Oct 15, 2011
using System;
using System.Collections.Generic;
using System.ComponentModel;
[code].....
View 4 Replies
Oct 9, 2009
I have to generate about 800 excel files from an access database.
For the first 10-15 of them it's working nice, a few seconds/excel file but it's constantly taking longer, at the 150th excel file it's taking 10 minutes.
Here is my code:
It's doing this for each nrliste in the access table (about 800 of them)
Dim lista = From ls In Liste _
Where ls!Concatenare = nrliste(i) _
Select ls
[Code].....
View 5 Replies
May 24, 2012
am converting some excel macro to vb.net, and it's almost done, but when i am looping throught +- 3000 rows and checking with 2 sheets it takes about 3 hours !
Dim Site1 As String
Dim Site2 As String
Dim Group1 As String
[code].....
View 2 Replies
Nov 25, 2011
I'm using a background worker to in an Excel VSTO application to throw up a progress dialog box with a status bar and a cancel button to escape from long running calculations. It's working really well, except for one issue. I'd like to use a Modal Dialog, so that the UI behind the dialog gets locked up, instead of a Modeless Dialog. If I use .ShowDialog() instead of .Show(), everything is great until you hit the Cancel button on the form. Following things in the debugger, the cancellation happens, it just takes somewhere in range of 30 seconds. If I use .Show() on my form, then the cancellation occurs immediately as it should.
[Code]...
View 2 Replies
Jun 15, 2009
how to write and overwrite a excel files?write is like create a new excel files and insert data to it using vboverwrite is writing data to the excel file which overwrite the old
View 2 Replies
Jun 21, 2011
I want to write a formula to SUM from column B4 to M4, this should be done using vb.net programming.
I tried using the following stuff:
oXLWsheet.Range(4, 14).Formula = "=SUM(oXLWsheet!$B$4:M$4)"
"=SUM(B4:M4)"
"=SUM(B4,C4,D4,E4,F4,G4,H4,I4,J4,K4,L4,M4)"
Nothing is working for me. I'm getting the following error when I run the code:
"Exception from HRESULT: 0x800A03EC".
View 2 Replies
Oct 20, 2010
I am creating a new spreadsheet from existin data. The new spreadsheet size changes based on the input data. I am using lastrow, and lastcolumn to format new valid cell raqnge. I also need to enter a predefined statement in column A of the the last row.
View 2 Replies
Nov 20, 2010
there is a datagridview, which will display Group Name in first column, Variables in second column.Users will populate it manually. will select a group name in first column, variable in second column. in similar, for some count of rows the group name remains similar, with different variables in second column.There may be different groups with lot of variables in second columns.our requirement is to insert a formula where ever its needed by the users.like getting subtotals at each level of groups.Also in case if they needed to have subtotal after two or three groups they must be able to define the row a formula to add those groups sum.
View 5 Replies
Feb 8, 2011
I am getting an error message when I am attempting to write to an excel worksheet using VB.net 2005
Operation must use an updateable query I have gone thru the S/O search results[URL]..My code is:
[Code]...
View 2 Replies
Oct 20, 2011
I need to loop the "buttonOneClick for one second iterations. The program will send a "P" character, wait one second, send a p, wait one second, etc... Also I need to write the information it receives to an excel spreadsheet.
Here's what I have:
Public Class Form2
Dim buttonOnePush As Boolean = False
Dim buttonTwoPush As Boolean = False
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' Send strings to a serial port.
[Code] .....
View 1 Replies
Aug 19, 2011
I want to import my data, which I receive over USB (every second), to an Excel sheet. Thing is, I want to open only one Excel file and write everything to this one file, and by pressing a "STOP" button, data reception will be canceled and data in the Excel file should be saved and Excel file should close right after that. Here my code,
Dim excel_cnt as Integer<br/>Dim sv_var as Integer = 0<br/><br/>Sub write_to_excel()
Dim xl As New Excel.Application
[Code]....
View 3 Replies
Aug 23, 2010
I'm developing a program that handles money, and there is a pre-made excel spreadsheet that I am to be transfering data into specific cells. How can I go about telling my program to write specifc data into specific cell numer in excel? Also, how can I get my program to write into specific areas of a MS Word Template (my program will generate letter with the same template just different names/addresses/valus)?
View 3 Replies
May 26, 2009
If you know the cell number that you need to write to in excel, say E3 what would you use for the VB2005 code? I can't seem to find an example on this!
View 7 Replies
Feb 2, 2012
I was developing a software with related to Excel, in which I have to write and SAVE the details at appropriate columns. I am currently having a problem in saving the data.
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Open
[Code].....
Very first time I debug the coding I haven't got much problem. But debugging it again gives me an Error AS: "A file named 'Resume.XLW' already exits in this location. Do you want to replace it?"
I was able to see this since I have enabled the display alerts. Disabling it generates a save as window.
View 2 Replies
May 21, 2012
To write code to implement data from visual basic into excel. Write a VB.NET program to store the following numbers into an array named �Scores�: 89.5, 76.2, 90, 86, 81, 97, 61, and 73. Your program will calculate an average score, list each of these scores in a new Excel workbook and show whether each score is below or above average. Your program will then save the Excel workbook as TestScores.xlsx.
In column A1 it should say "average" and next to it (in b1) should have the average of all those scores. in the cells below (starting from A3) it should list the scores and the column next to each score it should say whether each score is above average, average, or below average (compared to the average calculated in cell b1)
View 2 Replies
Nov 27, 2010
I extracted text from a third party software application. All the text is now in string and correct in my Form Labels.
Text look like
Label1
0.25
Label2
[Code]....
But I can't convert the labels from string to double or Decimal.
View 9 Replies
