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
/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package excelpoi;
import java.io.FileOutputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.WorkbookFactory; // This is included in poi-ooxml-3.6-20091214.jar
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
*
* @author Freemon Sandlewould
*/
public class ExcelPOI {
public static void main(String[] args)
{
try
{
Workbook wbook = new XSSFWorkbook();
wbook = WorkbookFactory.create(new FileInputStream("Input.xlsx") );
Sheet wsheet = wbook.getSheetAt(0); // Does not work with getSheetAt0) -> works with 1! strange
//Row row = sheet.getRow(10); // Set value of the first cell.
//Cell cell = row.getCell(10);
//Cell cell=row.getCell(10, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK );
//Row row = sheet.createRow((short)10);
//Cell cell = row.createCell((short)15);
//cell.setCellValue("fudge");
Row row = wsheet.getRow(7);
//if (row == null) {row=wsheet.createRow(10);}
Cell cell = row.getCell(7);
//if (cell == null){cell = row.createCell(10);}
System.out.println( "before set cellvalue= " + cell.getNumericCellValue());
//System.out.println( "before set cellvalue= " + cell.getStringCellValue());
//cell.setCellType(Cell.CELL_TYPE_NUMERIC);
//cell.setCellValue(7);
cell.setCellValue(13);
System.out.println( "after set cellvalue= " + cell.getNumericCellValue());
//System.out.println( "after set cellvalue= " + cell.getStringCellValue());
//FileOutputStream fileOut = new FileOutputStream("OutputFile.xls"); // Write newly modified workbook to a file.
FileOutputStream fileOut = new FileOutputStream("OutputFile.xlsx");
wbook.write(fileOut);
fileOut.close();
System.out.println("DONE!");
}
catch(FileNotFoundException e)
{
System.out.println(e);
}
catch(IOException e)
{
System.out.println(e);
}
catch(InvalidFormatException e)
{
System.out.println(e);
}
}
/**
* @param args the command line arguments
*/
}
1 Comment
The best is CKEditor