0

Im currently working on a project that requires me to write my somewhat massive output to an excel spreadsheet so I decided to speed up the process using Apache POI. However, I've found that anything past 10 row entries will cause new rows to be placed at the beginning. i.e. After the 9th row, the 10th row will go to the top of the spreadsheet and push everything else down. This occurs even with the example file from TutorialsPoint which I will include below. 

import java.io.File;
import java.io.FileOutputStream;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Writesheet 
{
   public static void main(String[] args) throws Exception 
   {
      //Create blank workbook
      XSSFWorkbook workbook = new XSSFWorkbook(); 
      //Create a blank sheet
      XSSFSheet spreadsheet = workbook.createSheet( 
      " Employee Info ");
      //Create row object
      XSSFRow row;
      //This data needs to be written (Object[])
      Map < String, Object[] > empinfo = 
      new TreeMap < String, Object[] >();
      empinfo.put( "1", new Object[] { 
      "EMP ID", "EMP NAME", "DESIGNATION" });
      empinfo.put( "2", new Object[] { 
      "tp01", "Gopal", "Technical Manager" });
      empinfo.put( "3", new Object[] { 
      "tp02", "Manisha", "Proof Reader" });
      empinfo.put( "4", new Object[] { 
      "tp03", "Masthan", "Technical Writer" });
      empinfo.put( "5", new Object[] { 
      "tp04", "Satish", "Technical Writer" });
      empinfo.put( "6", new Object[] { 
      "tp05", "Krishna", "Technical Writer" });
       //Iterate over data and write to sheet
      Set < String > keyid = empinfo.keySet();
      int rowid = 0;
       for (String key : keyid)
       {
         row = spreadsheet.createRow(rowid++);
         Object [] objectArr = empinfo.get(key);
         int cellid = 0;
         for (Object obj : objectArr)
         {
            Cell cell = row.createCell(cellid++);
            cell.setCellValue((String)obj);
         }
      }
      //Write the workbook in file system
      FileOutputStream out = new FileOutputStream( 
      new File("Writesheet.xlsx"));
      workbook.write(out);
      out.close();
      System.out.println( 
      "Writesheet.xlsx written successfully" );
   }
}

Above is the example code I mentioned. If you add more rows (wherever the total is more than 10) the spreadsheet will be out of order. Or you could use a for loop. 

for(int i = 6; i < 20; i++){
  String num = Integer.toString(i);
  empinfo.put(num, new Object[] {"test", num});
}

Forgive me, I am in no way proficient in the art of programming. If anyone can help, I would really appreciate it!

Racehorse35
  • 121
  • 10

1 Answers1

3

The problem is that you're using a TreeMap with Strings as keys. So once you add the key "10" to the tree map, the order will be different (probably 1,10,2,3,4...). If you change the tree map to use Integers as keys, it will keep the order you expect.

More on String ordering: Because your TreeMap was initialized without a Comparator, it orders it's keys by their 'Natural' ordering (see https://docs.oracle.com/javase/tutorial/collections/interfaces/order.html). The 'Natural' ordering of Strings is not what the average person expects (see Java String Number Comparator).

Community
  • 1
  • 1
lucasvw
  • 1,345
  • 17
  • 36