logo
down
shadow

Huge excel file... any advice?


Huge excel file... any advice?

By : keyring
Date : November 17 2020, 11:52 AM
it should still fix some issue "there is no chance of this changing"
You haven't said why there is no chance of changing but one real reason to change is this; a worksheet can only have 1,048,576 rows. If you have 800k rows now and you are still adding to them, then at some point the worksheet will crash. There are also other real limitations to using Excel like this.
code :


Share : facebook icon twitter icon
extract certain sheet from huge excel files into new excel file using matlab

extract certain sheet from huge excel files into new excel file using matlab


By : user3316297
Date : March 29 2020, 07:55 AM
I wish did fix the issue. is that possible to extract out all the data in second sheet from each of the excel file into new excel file? mean that i have a huge excel files in a folder, i would like to extract out all the second sheet from each of the excel file into new excel file. The sheet name is not the same. , Consider the following structure of files:
code :
in\
|__ Book1.xls
|__ Book2.xls
dirName = 'c:\path\to\in';
files = dir( fullfile(dirName,'*.xls') );
files = {files.name}';                    %'

SHEET_NUM = 2;
for i=1:numel(files)
    [~,~,rawData] = xlsread(fullfile(dirName,files{i}), SHEET_NUM);
    xlswrite('out.xls', rawData, i);
end
Advice on using jquery ajax to return a excel file

Advice on using jquery ajax to return a excel file


By : Mateo K
Date : March 29 2020, 07:55 AM
I think the issue was by ths following , could the excel button just populate a form field with the variables and then submit it, without ajax? So long as the content type is set to be application/ms-excel, it would prompt the user to open or save the file (or whatever the browser's default behavior seems to be).
This is essentially how I handle this issue, and seems to work fine.
Microsoft.Office.Interop.Excel or EPPlus for read a huge (or not) Excel file

Microsoft.Office.Interop.Excel or EPPlus for read a huge (or not) Excel file


By : Chakshu
Date : March 29 2020, 07:55 AM
Hope that helps Which approach did yours use? -EPPlus
What are your recommendations on this? -I've found EPPLus to be hugely faster. It is also an easier API to work with in my opinion. For many reasons, one being the lack of COM interop(both for speed and ease of use). Also has less requirements, especially when deploying to a server environment: no installing Excel junk.
code :
var firstColumnRows = sheet.Cells["A2:A"];

// Loop through rows in the first column, get values based on offset
foreach (var cell in firstColumnRows)
{
    var column1CellValue = cell.GetValue<string>();
    var neighborCellValue = cell.Offset(0, 1).GetValue<string>();
}
How to reduce a huge excel file

How to reduce a huge excel file


By : GeorgeGoGo
Date : March 29 2020, 07:55 AM
fixed the issue. Will look into that further I wrote a VBA file to add a tool cleaning these abnormally biggest file. This script clear all columns and rows after the last cells realy used to reset the last cells ( [Ctrl]+[End] ), and it also provides enable images compression.
I dev an AddIns with auto install (just run it with macro enabled) to include in context menu many new buttons:
code :
Sub ClearExcessRowsAndColumns()
    Dim ar As Range, r As Double, c As Double, tr As Double, tc As Double
    Dim wksWks As Worksheet, ur As Range, arCount As Integer, i As Integer
    Dim blProtCont As Boolean, blProtScen As Boolean, blProtDO As Boolean
    Dim shp As Shape
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each wksWks In ActiveWorkbook.Worksheets
      Err.Clear
      'Store worksheet protection settings and unprotect if protected.
      blProtCont = wksWks.ProtectContents
      blProtDO = wksWks.ProtectDrawingObjects
      blProtScen = wksWks.ProtectScenarios
      wksWks.Unprotect ""
      If Err.Number = 1004 Then
         Err.Clear
         MsgBox "'" & wksWks.Name & "' is protected with a password and cannot be checked.", vbInformation
      Else
         Application.StatusBar = "Checking " & wksWks.Name & ", Please Wait..."
         r = 0
         c = 0

         'Determine if the sheet contains both formulas and constants
         Set ur = Union(wksWks.UsedRange.SpecialCells(xlCellTypeConstants), wksWks.UsedRange.SpecialCells(xlCellTypeFormulas))
         'If both fails, try constants only
         If Err.Number = 1004 Then
            Err.Clear
            Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeConstants)
         End If
         'If constants fails then set it to formulas
         If Err.Number = 1004 Then
            Err.Clear
            Set ur = wksWks.UsedRange.SpecialCells(xlCellTypeFormulas)
         End If
         'If there is still an error then the worksheet is empty
         If Err.Number <> 0 Then
            Err.Clear
            If wksWks.UsedRange.Address <> "$A$1" Then
               ur.EntireRow.Delete
            Else
               Set ur = Nothing
            End If
         End If
         'On Error GoTo 0
         If Not ur Is Nothing Then
            arCount = ur.Areas.Count
            'determine the last column and row that contains data or formula
            For Each ar In ur.Areas
               i = i + 1
               tr = ar.Range("A1").Row + ar.Rows.Count - 1
               tc = ar.Range("A1").Column + ar.Columns.Count - 1
               If tc > c Then c = tc
               If tr > r Then r = tr
            Next
            'Determine the area covered by shapes
            'so we don't remove shading behind shapes
            For Each shp In wksWks.Shapes
               tr = shp.BottomRightCell.Row
               tc = shp.BottomRightCell.Column
               If tc > c Then c = tc
               If tr > r Then r = tr
            Next
            Application.StatusBar = "Clearing Excess Cells in " & wksWks.Name & ", Please Wait..."
            Set ur = wksWks.Rows(r + 1 & ":" & wksWks.Rows.Count)
                'Reset row height which can also cause the lastcell to be innacurate
                ur.EntireRow.RowHeight = wksWks.StandardHeight
                ur.Clear

            Set ur = wksWks.Columns(ColLetter(c + 1) & ":" & ColLetter(wksWks.Columns.Count))
                'Reset column width which can also cause the lastcell to be innacurate
                ur.EntireColumn.ColumnWidth = wksWks.StandardWidth
                ur.Clear
         End If
      End If
      'Reset protection.
      wksWks.Protect "", blProtDO, blProtCont, blProtScen
      Err.Clear
    Next
    Application.StatusBar = False
    ' prepare les combinaison de touches pour la validation automatique de la fenetre
    ' Application.SendKeys "%(oe)~{TAB}~"

    ' ouvre la fenetre de compression des images
    Application.CommandBars.ExecuteMso "PicturesCompress"
    Application.ScreenUpdating = True
End Sub


Function ColLetter(ColNumber As Integer) As String
    ColLetter = Left(Cells(1, ColNumber).Address(False, False), Len(Cells(1, ColNumber).Address(False, False)) - 1)
End Function
Advice on reading in Excel file using C++

Advice on reading in Excel file using C++


By : newbiee
Date : March 29 2020, 07:55 AM
To fix this issue I see three options here.
Convert to CSV whatever way you can. Use some 3rd party Excel reader like Spreadsheet::ParseExcel for Perl5 or xlrd for Python to convert to any format that your C++ application can digest. Use Excel through its COM or .NET (preferable, easier) interfaces.
Related Posts Related Posts :
  • Excel - VB - Re-ordering /Renaming columns
  • "Overload" F9 (or shift+F9) in Excel spreadsheet with VBA function?
  • ShowAllData method of Worksheet class failed
  • VBA If cell is under a certain length, highlight and display message
  • VBA: Unable to get the Match property of the WorkSheet function class
  • Highlighting the second and more duplicate rows in excel but not first occurrence?
  • Excel add- in focus not returned to the active sheet
  • How to restrict VBA code to a specific worksheet?
  • How to insert column name in the destination table in ssis?
  • Insert text into the background of a cell
  • how do I generate random timestamps in Excel
  • VBScript Opening folder to convert csv to xls
  • Creating a VBA Refresh Macro in Smart View for Oracle
  • Excel Performance issues with countif on multiple rows
  • excel vba http request download data from yahoo finance
  • Excel Vba Loop , not running properly
  • Efficiently hiding / unhiding many (+500) rows in Excel VBA
  • Excel's Find function; Exceptions over values
  • reading data from an excel sheet in perl
  • Using Webservice function in Excel to pull "Next Earnings Date" from Yahoo Finance using Yahoo API
  • Three And (&)'s into an if statement VBA for Excel
  • Certain fractions being calculated in excel 2013
  • Excel formula to find sum of difference
  • countfis or sum if array to work out if given time is between the value of two columns
  • Error in Date Cell in CSV
  • Syntax while trying to include The multi-part identifier [°C] in command text
  • How to find day of the week from known date and month but variable year
  • Modify Marker Transparency with Excel VBA
  • Excel tricky formula
  • Loop Though All UDF Names in Project
  • Searching for function usage in Excel VBA
  • Convert from list to flat CSV with modified data
  • Count how many words from a list appear in a cell
  • Insert Missing Years between 2 years
  • Creating hierarchical sequence in Excel using several functions in formula
  • On Error GoTo statement is still executing although there is no error generated
  • Find last digits with IF
  • Use Excel VBA to find column matches & merge based on the values of two other column
  • PowerPivot not sending Authorization header in Basic Authentication to OData Svc
  • Vlookup not working with text search
  • Paste value from a Userform to a Excel Sheet
  • Excel 2010 Macro to compare two columns for finding the matched value
  • #VALUE error when dealing with long string in UDF in VBA(excel)
  • Refer to a cell in another worksheet by referencing the current sheet's name
  • VBA Excel 2007 : Need to loop copy and loop count number except zero every row above
  • Excel: Conditional probabilities of winning a tiebreaker game
  • How to find groups of individuals who attended multiple events together?
  • Using DateTime when referencing a cell with SUMIFS()
  • VBA Copy & Transpose Data Range
  • Looped macro to create a new sheet, rename it, add data from the web, then loop back through until complete
  • copy paste of tab delimited text results in strange formatting in Excel?
  • Excel user defined function: Nth_Occurence
  • Excel ActiveX Button is clickable, but does not work
  • Array Formula Conditional Count
  • YEAR() fitting to WEEKNUM(...,21) in Excel
  • Macro that follows a link and downloads the table into a new sheet
  • How to Concatenate multiple columns if not empty
  • Excel - List all the combinations of two words from one list, in two other columns
  • How do I do my Pentaho Report take the Excel datasource file along to BA/BI Server?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org