logo
down
shadow

How to count rows and Columns of excel sheet in Java


How to count rows and Columns of excel sheet in Java

By : chandra
Date : November 23 2020, 09:01 AM
This might help you XSSF sheet has a method called getPhysicalNumberOfRows() to get what looks like you want for rows, but I don't know about columns.
code :


Share : facebook icon twitter icon
Count number of rows in a column of Excel sheet(Java code provided)

Count number of rows in a column of Excel sheet(Java code provided)


By : Voller Energie
Date : March 29 2020, 07:55 AM
it should still fix some issue In reference to my previous question How to calculate number of rows in a column of Excel document using Java i was able to calculate the total number of columns in the given sheet. Now half of the work is yet to be done as i want to calculate the number of rows in a particular column. Possible solution could be using 2d array and storing column index and the total rows or using map, etc. How i can achieve this? Java code is provided here. I'm getting right count(column count) for my demo file. Please modify/suggest changes as required. , Try this:
code :
private void excelReader() {
    String data;
    try {
        InputStream is = new FileInputStream("Read.xlsx");
        Workbook wb = WorkbookFactory.create(is);
        Sheet sheet = wb.getSheetAt(0);
        Iterator rowIter = sheet.rowIterator();
        Row r = (Row)rowIter.next();
        short lastCellNum = r.getLastCellNum();
        int[] dataCount = new int[lastCellNum];
        int col = 0;
        rowIter = sheet.rowIterator();
        while(rowIter.hasNext()) {
            Iterator cellIter = ((Row)rowIter.next()).cellIterator();
            while(cellIter.hasNext()) {
                Cell cell = (Cell)cellIter.next();
                col = cell.getColumnIndex();
                dataCount[col] += 1;
                DataFormatter df = new DataFormatter();
                data = df.formatCellValue(cell);
                System.out.println("Data: " + data);
            }
        }
        is.close();
        for(int x = 0; x < dataCount.length; x++) {
            System.out.println("col " + x + ": " + dataCount[x]);
        }
    }
    catch(Exception e) {
        e.printStackTrace();
        return;
    }
}
Col0    Col1    Col2    Col3    Col4
1       a       x       a       q
2       b       y       s       w
3       c       z       d       e
4       d               f       r
5       e                       t
                                y
How to find columns count of csv(Excel) sheet in ETL?

How to find columns count of csv(Excel) sheet in ETL?


By : ml80
Date : March 29 2020, 07:55 AM
To fix the issue you can do Just read the first row of the CSV file using Text-File-Input setting header rows to 0. Usually, the first row contains field names. If you read the whole row into a single field, you can use Split-Field-To-Rows to have a single fieldname per row and the number of rows tells you the number of fields. There are other ways, but this one easily prepares for a subsequent metadata injection - if that's what you have in mind.
how to iterate first columns then rows to read excel sheet in java

how to iterate first columns then rows to read excel sheet in java


By : Robert Thwaites
Date : March 29 2020, 07:55 AM
should help you out If i understand you question properly then you are trying to iterate over column first then row . This is what you might be trying to do. Hope it helps :
code :
Sheet sheet = workbook.getSheetAt(0);
    for(int i=0; i<sheet.getRow(sheet.getTopRow()).getLastCellNum(); i++){
        Iterator<Row> rowIterator=sheet.rowIterator();
        while(rowIterator.hasNext()){
            System.out.println(rowIterator.next().getCell(i)); //do what you want

        }

    }
Rows.Count Method is not working well in Excel VBA(It gets rows number in wrong sheet although I specified what sheet to

Rows.Count Method is not working well in Excel VBA(It gets rows number in wrong sheet although I specified what sheet to


By : Rajeshwari Sachin
Date : March 29 2020, 07:55 AM
I wish this help you , The cells has no defined parent.
code :
iTotalRows = src.Worksheets("Example").Range("B1:B" & src.Worksheets("Example").Cells(src.Worksheets("Example").Rows.Count, "B").End(xlUp).Row).Rows.Count 
with src.Worksheets("Example")
    iTotalRows = .cells(.rows.count, "B").end(xlup).row
end with
MsgBox iTotalRows
Copy rows from one sheet into other sheet and after 7 columns go to next row using vba in excel

Copy rows from one sheet into other sheet and after 7 columns go to next row using vba in excel


By : Boğaçhan Uyar
Date : March 29 2020, 07:55 AM
I wish did fix the issue. You can Set your range in "F2" sheet to be dynamic, and then loop through each cell in the Range by using For Each C In Rng.
Inside this loop, I have CellCount which represent the number of the cell inside the Range, every 7 columns I reset the column number here:
code :
Col = CellCount Mod 7 ' get the column number , every 7 columns reset the column
PasteRow = Int((CellCount - 1) / 7) + 1 ' get the row number
Option Explicit

Sub CopyUpto7Columns()

Dim ws1 As Worksheet, ws2 As Worksheet
Dim Rng As Range, C As Range
Dim LastRow As Long, CellCount As Long
Dim PasteRow As Long, Col As Long

Set ws1 = Sheets("F2")
Set ws2 = Sheets("List1")

With ws1
    ' get dynamic last row
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

    ' set the range object in sheet "F2" up to column 31
    Set Rng = .Range(.Cells(1, 1), .Cells(LastRow, 31))
End With

PasteRow = 1 ' start pasting from the first row
CellCount = 1 ' reset cell count in Range

' loop through range (cell by cell)
For Each C In Rng
    PasteRow = Int((CellCount - 1) / 7) + 1 ' get the row number

    Col = CellCount Mod 7 ' get the column number , every 7 columns reset the column
    If Col = 0 Then Col = 7

    ws2.Cells(PasteRow, Col).Value = C.Value
    CellCount = CellCount + 1
Next C

End Sub
Related Posts Related Posts :
  • My shape moved but will pause for a while
  • How to solve syntax error in eclipse Mars.1
  • Shibboleth idp configuration for ldap authentication
  • Do something when app starts?
  • Android Runtime Exception font asset not found
  • How to query using objectify in Google App Engine app
  • List value is printing address instead of value
  • StringUtils.isBlank return false for null Strings
  • Sent parameter through PATCH android
  • i am not able to clear the code below given using throwable concept in java
  • How to get System time instead of Server time in java?
  • How to push json data into an arrya object in MONGO DB by java
  • Saving RelationshipEntity not working
  • Error on "instanceOf" when checking a subClass
  • Java - Splitting a image into 4 images
  • "Pinging" a server in Java
  • RoboGuice Proguard CreationException
  • Android Array list returns Null after adding values
  • Jar Executable what am I doing wrong?
  • How do I mitigate Connection leak triggered for connection com.mysql.jdbc.JDBC4Connection@11d08960,
  • How do i iterate values from a 2d array using conditions
  • Hide Button in CustomPagerAdapter
  • Java GC: does it ever make sense to call super.finalize() in a class that only extends Object?
  • 64bit HashMap in Java
  • Hibernate wildcard query on two columns
  • Want to Generate Auto Login Program Using Java
  • Spring security Oauth 2 with ajax login and form login
  • Parse findInBackground doesn't add to global arraylist?
  • Remove random item from ArrayList
  • in SpringMVC getMessage from i18n properties in a function outside the controller
  • How do I add JCheckBox next to each name that has been entered into an arraylist from a JTextField?
  • Return node ID along with complete node
  • InvocationTargetException. Cannot cast class X to class X. When invoked in Scala Imain through spark-submit
  • how to print elements of a 1D array into a N x N box
  • How to map csv file to pojo class in java
  • How, in Java, to encode default vhost %2F in URL/URI so it doesn't get changed to slash and fail to work
  • Tomcat 5.5 https configuration in Windows Server 2008 64bit
  • Enabling FIPS 140-2 compliance mode in MULE ESB
  • How to pass String PackageName to PkgName under parseXML() and run XMLParser class as a new activity when click on list
  • Error while creating spring -hibernate configuration file
  • Hand off control between java threads
  • NoClassDefFoundError: scala/collection/Seq
  • Getting stack overflow error on one machine but not the other on the same code
  • How can I loop AsyncAjaxRequest in GWT?
  • Failed to find provider info for urbanairship.provider
  • Android ListView Adapter Crash issue/Duplicates
  • Displaying Splash screen only once on user preference
  • Loop List<WebElement> doesn't stop despite no more WebElements
  • Check if pattern exists in a String
  • Swing - Key Problems (Arrow Keys, Tab, etc.) in GUIs with TextComponents
  • Why the solution for binary search tree validity is not working?
  • Double encrypting 2048 RSA?
  • create junit test for runnable class
  • Unable to read and sort information from a text file in Java
  • My device isn't getting detected in android studio (driver issue)
  • Representing a Graph in Java
  • How to XML mapping using jxls
  • Quick Sort stackoverflow error for large arrays
  • Parsing Apache Tika XML Output returns Unknown Tag
  • java simple UI loop, can't find bug
  • shadow
    Privacy Policy - Terms - Contact Us © animezone.co