I am reading an .xls file using Spreadsheet::ParseExcel and was able to get data as is. But,when reading an .xlsx file using Spreadsheet::XLSX, the read values are truncated. E.g., 2.4578 in .xls and .xlsx file is read as 2.4578 and 2.45, respectively. Please suggest why .xlsx file data is corrupted. ...
Hi A little help needed, I have a Macro automatically creating pivot tables and charts, this is all working fine but I am getting (blank) in my pivot table becuase my range is all the way to 65536. How do I automatically get the lastrow / column in my source data so I dont get any blanks. The data is changing constantly so this needs to...
How would I go around creating a MYSQL table schema inspecting an Excel(or CSV) file. Are there any ready Python libraries for the task? Column headers would be sanitized to column names. Datatype would be estimated based on the contents of the spreadsheet column. When done, data would be loaded to the table. I have an Excel file of ~2...
Hi, I'm using Automation to populate a range in Excel using a querytable.. However, when I try to add a querytable to the qorksheet I get an exception. I checked the connection string and its working fine.. Can some one please help me with this?? public void writeproc1() { try { Worksheet ws = (W...
Hi - VBA newbie over here. I'm trying to use an array formula through excel vba but I can't seem to specify a dynamic range for the formula. I have: Range("xyz").FormulaArray = "=somefunction(Data!RC:R[8]C[49])" But next time it could be Range("xyz").FormulaArray = "=somefunction(Data!RC:R[15]C[32])" This doesn't seem to work. Is the...
i want to take a list of name that are in multiple rows a single excel column, like this: Joe Bob George and convert that into one cell that has this: "Joe", "Bob", "George" ...
I've got a mental block about what I'm sure is a common scenario: I have some data in a csv file that I need to do some very basic reporting from. The data is essentially a table with Resources as column headings and People as row headings, the rest of the table consists of Y/N flag, "Y" if the person has access to the resource, "N" if...
Hello, first off I am fairly new to php like 3 weeks working with it, and am loving it so far. Its a fantastic language. I am running into a issue though, I have a client who wants the information collected in on a form on this website to then be imported into a excel documents that he already has created. Since I am fairly new I've bee...
I'd think there'd be something to do this in Excel, because it seems so basic... BUT: I have two cells and I need to compare them and return the lower of the two. This is very easily done with the following: =IF(A1 > A2, A2, A1) But in a complex formula, this seems insanely unnecessary. It's also possible to do with: =SMALL(A1:A2) ...
I have a 3 dimensional array (5 x 5 x 3) and I need to post (5 x 5 x 1) to Sheet1, (5 x 5 x 2) to Sheet2, (5 x 5 x 3) to Sheet3. Because I am building this 3 dimensional array inside 3 nested for loops, I cannot use a for loop to access the (5 x 5) part of the loop. Is there any identifier that tells excel to index all elements of an a...
I have a program that displays numbers in a System.Windows.Forms.DataGridView. I'm formatting those numbers according to the user's regional settings, which causes problems when I try to copy and paste said numbers into Excel. For example, 123 456 789,00 is the proper localized format in Finnish, but Excel interprets this as a string, no...
I have an intranet page that uses #include to include other files such as rotas or phone number tables. These included files are maintained in microsoft excel. Not all of them are maintained by me (the guy in charge of the intranet itself) so there isn't really the option to refuse to accept excel produced html files. The problem I ha...
I'm using the OleDB DataReader to get data from my excel files (but this problem occurs in DataTable.Fill as well). The thing is that I have a column that should return strings. All's fine and working but recently, an issue came up, because the column's cells had different formats. Some were in numbers and others in text. When I checked ...
Hello, I am using apache POI 3.6 to generate excel (2003) sheets. I want to insert a formula to a cell which calculates a sum of a certain cells on several sheets. I have sheets named a, b and c and want to calculate the sum the cells A1 I tried: cell.setCellFormula("a!A1+b!A1+c!A1"); POI does not produce any errors, but when ...
I'm trying to use Excel VBA to write to a text file. I'm doing a few of these: MyFile1 = "C:\outputFromExcel1.txt" fnum1 = FreeFile() Open MyFile1 For Output As fnum1 and then writing to them like this: Print #fnum1, text All variables in the above are declared just with Dim. I'm writing hundreds of lines to the files and, very r...
Hello, subject. How can I to do it ? thanks. ...
AU-contains numbers from 0-20 AV-needs to have high, moderate, low; based on the number in AU =>5 HIGH 3-4 MODERATE <3 LOW Wrote this below, however I get #NAME? for the answer; so I know some part of it is wrong. Please help. =IF(OR(AU>=5),"High",IF(OR(AU<=4.9>=3),"Moderate",IF(OR(AU<3),"Low"))) ...
I have an application written in VB6 that writes data to a spreadsheet. I'm using the MS Excel 11.0 Object library to create an instance of Excel and open the book: Dim xlApp As Excel.Application, remoteBook As Workbook Set xlApp = New Excel.Application Set remoteBook = xlApp.Workbooks.Open(sheetName) In addition to writing to the wo...
Do we have any free 3rd party library that we can use in asp.net for download Excel sheets? Please provide me some. ...
Good morning everybody. I developed an application plug-in for Excel using VSTO. The thing is that I need to have a cell displaying the current time refreshed every 10 seconds. The =NOW() function of Excel is not refreshed automatically, even when auto-calculation is on. The best solution I could come up with was to create an RTD ser...