CODEWITHSUNDEEP
javaexcelmultidimensional-arraymethodsapache-poi
Fco.Jav.Y
Fco.Jav.Y

Reputation: 65

Write 2d arrays of integers on Excel using Apache POI

I am trying to export 2d arrays of integers to Excel. Precisely, I need to export 2 arrays of integers of the same dimension. I previously installed the libraries for using Apache POI in Java using Eclipse IDE.

My idea is to pass as parameters for the writeExcel method 2 arrays of integers (arrayOne and arrayTwo) and get as a result an Excel file where 2 matrices were written with their respective values. For example, for the case where both matrices are from 5x5 dimension this is what I tried:

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExportToExcel {
    
    public static void WriteExcel(int[][] arrayOne, int[][] arrayTwo) {
        
        String fileName = "MyExcelFile.xlsx";
        String sheet = "5x5";
        
        XSSFWorkbook book = new XSSFWorkbook();
        XSSFSheet hoja1 = book.createSheet(sheet);
        
        // Header of the de Excel File
        String[] header = new String[] {"col 0", "col 1", "col 2", "col 3", "col 4"}; 
    
        // Fill the Excel File
        for (int i = 0; i <= ruta.length; i++) {
            
            XSSFRow row = hoja1.createRow(i);
            
            for (int j = 0; j < header.length; j++) {
                
                if (i == 0) {
                    
                    XSSFCell cell = row.createCell(j);
                    cell.setCellValue(header[j]);
                    
                }
                
                    else {
                    
                        XSSFCell cell = row.createCell(j);
                        // some code here??
                        
                    }
                
            }
        }
        
    }

}

Clearly, in the previous code are missing parts that I don't know how to include. Thus, if I pass as a parameters to the method the following 2 matrices:

arrayOne: [[2, 0, 1, 3, 4], [0, 1, 2, 3, 4], [0, 2, 1, 4, 3], [2, 1, 0, 4, 3], [2, 0, 1, 3, 4]]
arrayTwo: [[1, 1, 4, 1, 1], [4, 4, 0, 4, 4], [0, 0, 1, 0, 0], [2, 3, 3, 3, 3], [3, 2, 2, 2, 2]]

The expected result in Excel must be something like:

enter image description here

Thanks in advance if anyone can help me with this question.

Upvotes: 0

Views: 644

Answers (1)

A.Ktns
A.Ktns

Reputation: 653

You should iterate through the whole excel sheet and then check if you are in the desired cell, write the appropriate value.

One way to do this, is with CellReference . With that you can reference to a cell and then take its row/col index an write on it.

For example:

CellReference A1 = new CellReference("A1");

int row = cr.getRow();
int col = cr.getCol();

Now you got the index for row/col of A1 and you can write on this position like this:

Row row1 = ((sheet.getRow(row)==null) ? sheet.createRow(row) : sheet.getRow(row));

With the line above you check if the row exists, and if not, then you create one.

Cell cell;
if (row1.getCell(col) == null) {
    cell = row1.createCell(col);
} else {
    cell = row1.getCell(col);
}

With the above, you check the specific cell on the specific row if its empty, and if it is then you create a new one, else you the its reference.

Now, you are ready to write on A1 cell. Simply do:

cell.SetCellValue("arrayOne");

So now, just use row and col that represent the index for row/col in your sheet and write in the cells of your choice.

For example by doing col+=1 you getting B1 and by repeating the above code in a for loop and increment every time the col and/or row index inside your loop, you can write the arrays in the way you want on your sheet.

Please feel free to ask me for extra clarification.

Upvotes: 1

Related Questions