Populate an Excel xlsx xls spread sheet with Apache POI Library

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

Source code & libraries here

<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	/*&lt;br /&gt;<br />
	* To change this template, choose Tools | Templates&lt;br /&gt;<br />
	* and open the template in the editor.&lt;br /&gt;<br />
	*/&lt;br /&gt;<br />
	package excelpoi;<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	import java.io.FileOutputStream;&lt;br /&gt;<br />
	import java.io.FileInputStream;&lt;br /&gt;<br />
	import java.io.FileNotFoundException;&lt;br /&gt;<br />
	import java.io.IOException;<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	import org.apache.poi.ss.usermodel.Sheet;&lt;br /&gt;<br />
	import org.apache.poi.ss.usermodel.Row;&lt;br /&gt;<br />
	import org.apache.poi.ss.usermodel.Cell;&lt;br /&gt;<br />
	import org.apache.poi.ss.usermodel.WorkbookFactory; // This is included in poi-ooxml-3.6-20091214.jar&lt;br /&gt;<br />
	import org.apache.poi.ss.usermodel.Workbook;&lt;br /&gt;<br />
	import org.apache.poi.openxml4j.exceptions.InvalidFormatException;<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	import org.apache.poi.xssf.usermodel.XSSFWorkbook;<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	/**&lt;br /&gt;<br />
	*&lt;br /&gt;<br />
	* @author Freemon Sandlewould&lt;br /&gt;<br />
	*/&lt;br /&gt;<br />
	public class ExcelPOI {<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	public static void main(String[] args)&lt;br /&gt;<br />
	{&lt;br /&gt;<br />
	try&lt;br /&gt;<br />
	{&lt;br /&gt;<br />
	Workbook wbook = new XSSFWorkbook();&lt;br /&gt;<br />
	wbook = WorkbookFactory.create(new FileInputStream(&amp;quot;Input.xlsx&amp;quot;) );&lt;br /&gt;<br />
	Sheet wsheet = wbook.getSheetAt(0); // Does not work with getSheetAt0) -&amp;gt; works with 1! strange<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	//Row row = sheet.getRow(10); // Set value of the first cell.&lt;br /&gt;<br />
	//Cell cell = row.getCell(10);&lt;br /&gt;<br />
	//Cell cell=row.getCell(10, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	//Row row = sheet.createRow((short)10);&lt;br /&gt;<br />
	//Cell cell = row.createCell((short)15);&lt;br /&gt;<br />
	//cell.setCellValue(&amp;quot;fudge&amp;quot;);<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	Row row = wsheet.getRow(7);&lt;br /&gt;<br />
	//if (row == null) {row=wsheet.createRow(10);}&lt;br /&gt;<br />
	Cell cell = row.getCell(7);&lt;br /&gt;<br />
	//if (cell == null){cell = row.createCell(10);}<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	System.out.println( &amp;quot;before set cellvalue= &amp;quot; + cell.getNumericCellValue());&lt;br /&gt;<br />
	//System.out.println( &amp;quot;before set cellvalue= &amp;quot; + cell.getStringCellValue());<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	//cell.setCellType(Cell.CELL_TYPE_NUMERIC);&lt;br /&gt;<br />
	//cell.setCellValue(7);&lt;br /&gt;<br />
	cell.setCellValue(13);&lt;br /&gt;<br />
	System.out.println( &amp;quot;after set cellvalue= &amp;quot; + cell.getNumericCellValue());&lt;br /&gt;<br />
	//System.out.println( &amp;quot;after set cellvalue= &amp;quot; + cell.getStringCellValue());<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	//FileOutputStream fileOut = new FileOutputStream(&amp;quot;OutputFile.xls&amp;quot;); // Write newly modified workbook to a file.&lt;br /&gt;<br />
	FileOutputStream fileOut = new FileOutputStream(&amp;quot;OutputFile.xlsx&amp;quot;);&lt;br /&gt;<br />
	wbook.write(fileOut);&lt;br /&gt;<br />
	fileOut.close();&lt;br /&gt;<br />
	System.out.println(&amp;quot;DONE!&amp;quot;);&lt;br /&gt;<br />
	}&lt;br /&gt;<br />
	catch(FileNotFoundException e)&lt;br /&gt;<br />
	{&lt;br /&gt;<br />
	System.out.println(e);&lt;br /&gt;<br />
	}&lt;br /&gt;<br />
	catch(IOException e)&lt;br /&gt;<br />
	{&lt;br /&gt;<br />
	System.out.println(e);&lt;br /&gt;<br />
	}&lt;br /&gt;<br />
	catch(InvalidFormatException e)&lt;br /&gt;<br />
	{&lt;br /&gt;<br />
	System.out.println(e);&lt;br /&gt;<br />
	}<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	}<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	/**&lt;br /&gt;<br />
	* @param args the command line arguments&lt;br /&gt;<br />
	*/<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />
	}<br />
	&lt;/p&gt;
</p>

<p>
	&lt;p&gt;<br />