Java Programming Language Table of Contents
- Java POI Poorly Obfuscated Interface Project to Load a SpreadSheet with Data
- Java File Operations with Text Box – Load and Save Text you type in
- Java in Eclipse Integrated Development Environment IDE
- Java Software Internationalization
- Java Multilistener – Event Demo that shows how you can listen to one or to all
- Java Serial Port
- Java – Set the Size of a FrameView programmatically
- Populate an Excel xlsx xls spread sheet with Apache POI Library
End TOC
Microsoft purposefully obfuscates their code. If you want to populate a .xls or .xlsx you need some help. The Apache POI project is exactly what you need.
Libraries required include
- poi
- poi-ooxml
- poi-ooxml-schemas
- xbean
- dom4j
This project takes Input.xlsx and writes a value of 13 to a cell and outputs the file as Output.xlsx
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 | </p><p> /*<br /> * To change this template, choose Tools | Templates<br /> * and open the template in the editor.<br /> */<br /> package excelpoi;</p><p> import java.io.FileOutputStream;<br /> import java.io.FileInputStream;<br /> import java.io.FileNotFoundException;<br /> import java.io.IOException;</p><p> import org.apache.poi.ss.usermodel.Sheet;<br /> import org.apache.poi.ss.usermodel.Row;<br /> import org.apache.poi.ss.usermodel.Cell;<br /> import org.apache.poi.ss.usermodel.WorkbookFactory; // This is included in poi-ooxml-3.6-20091214.jar<br /> import org.apache.poi.ss.usermodel.Workbook;<br /> import org.apache.poi.openxml4j.exceptions.InvalidFormatException;</p><p> import org.apache.poi.xssf.usermodel.XSSFWorkbook;</p><p> /**<br /> *<br /> * @author Freemon Sandlewould<br /> */<br /> public class ExcelPOI {</p><p> public static void main(String[] args)<br /> {<br /> try<br /> {<br /> Workbook wbook = new XSSFWorkbook();<br /> wbook = WorkbookFactory.create(new FileInputStream("Input.xlsx") );<br /> Sheet wsheet = wbook.getSheetAt(0); // Does not work with getSheetAt0) -> works with 1! strange</p><p> //Row row = sheet.getRow(10); // Set value of the first cell.<br /> //Cell cell = row.getCell(10);<br /> //Cell cell=row.getCell(10, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );</p><p> //Row row = sheet.createRow((short)10);<br /> //Cell cell = row.createCell((short)15);<br /> //cell.setCellValue("fudge");</p><p> Row row = wsheet.getRow(7);<br /> //if (row == null) {row=wsheet.createRow(10);}<br /> Cell cell = row.getCell(7);<br /> //if (cell == null){cell = row.createCell(10);}</p><p> System.out.println( "before set cellvalue= " + cell.getNumericCellValue());<br /> //System.out.println( "before set cellvalue= " + cell.getStringCellValue());</p><p> //cell.setCellType(Cell.CELL_TYPE_NUMERIC);<br /> //cell.setCellValue(7);<br /> cell.setCellValue(13);<br /> System.out.println( "after set cellvalue= " + cell.getNumericCellValue());<br /> //System.out.println( "after set cellvalue= " + cell.getStringCellValue());</p><p> //FileOutputStream fileOut = new FileOutputStream("OutputFile.xls"); // Write newly modified workbook to a file.<br /> FileOutputStream fileOut = new FileOutputStream("OutputFile.xlsx");<br /> wbook.write(fileOut);<br /> fileOut.close();<br /> System.out.println("DONE!");<br /> }<br /> catch(FileNotFoundException e)<br /> {<br /> System.out.println(e);<br /> }<br /> catch(IOException e)<br /> {<br /> System.out.println(e);<br /> }<br /> catch(InvalidFormatException e)<br /> {<br /> System.out.println(e);<br /> }</p><p> }</p><p> /**<br /> * @param args the command line arguments<br /> */</p><p> }</p><p> |
1 Comment
The best is CKEditor