Adding data to Excel file using jxl

Question

I am creating an excel file using jxl. At first there should be only some formatting and style information. Then it should be updated every time someone adds new data to it.

public class WriteExcel {

    private static WritableWorkbook workbook;
    private static WritableCellFormat timesStandard;
    private String inputFile;
    final private static int FONT_SIZE = 12;


    public void setOutputFile(String inputFile) {
        this.inputFile = inputFile;
    }

    private void prepareSheet(WritableSheet sheet) throws WriteException {
        sheet.mergeCells(0, 0, 1, 0);
        sheet.mergeCells(3, 0, 4, 0);
        sheet.mergeCells(6, 0, 7, 0);

        WritableFont times12pt = new WritableFont(WritableFont.TIMES, FONT_SIZE);
        timesStandard = new WritableCellFormat(times12pt);

        CellView cv = new CellView();
        cv.setFormat(timesStandard);
    }

    public void write() throws IOException, WriteException {
        File file = new File(inputFile);
        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setLocale(new Locale("en", "EN"));       

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
        workbook.createSheet("First", 0); 
        WritableSheet excelSheet = workbook.getSheet(0);
        prepareSheet(excelSheet);

        workbook.write();
        workbook.close();
    }

    public static void main(String[] args) throws WriteException, IOException {
        WriteExcel test = new WriteExcel();
        test.setOutputFile("c:/Users/H/Desktop/test.xls");
        test.write();
    }
}

I understand that I need another class that will allow me to access the file and add some data to it:

class Modify {

    private static Logger logger = Logger.getLogger(Modify.class);
    private File inputWorkbook;
    private File outputWorkbook;

    public Modify(String input, String output) {
        inputWorkbook = new File(input);
        outputWorkbook = new File(output);
        logger.info("Input file:  " + input);
        logger.info("Output file:  " + output);
    }

    public void readWrite() throws IOException, BiffException, WriteException {
        logger.info("Reading...");
        Workbook w1 = Workbook.getWorkbook(inputWorkbook);

        logger.info("Copying...");
        WritableWorkbook w2 = Workbook.createWorkbook(outputWorkbook, w1);

        if (inputWorkbook.getName().equals("test.xls")) {
            modify(w2);
        }

        w2.write();
        w2.close();
        logger.info("Done");
    }

    private void modify(WritableWorkbook w) throws WriteException {
        logger.info("Modifying...");

        WritableSheet sheet = w.getSheet("First");

        //createContent(sheet); // contains methods responsible for adding data, for example:
        addNumber(sheet, cols, rows, 2);

    private static void addNumber(WritableSheet sheet, int column, int row, double d) throws WriteException, RowsExceededException {
        Number number;
        number = new Number(column, row, d, timesStandard);
        sheet.addCell(number);

    }
}

But in the end, I get an empty Excel file with only merged cells.

How to make changes?

Answer 1

Since you are manipulating an object WritableWorkbookthat is not a member of your class for w2, should your method modify()not be invalid and return instead WritableWorkbook?

, , modify(), - .

-

    private WritableWorkbook modify(WritableWorkbook w) throws WriteException {
    logger.info("Modifying...");

    WritableSheet sheet = w.getSheet("First");

    //createContent(sheet); // contains methods responsible for adding data, for example:
    addNumber(sheet, cols, rows, 2);
    return sheet;
    }

. addNumber . sheet = addNumber(sheet, cols, rows, 2); w2 = modify(w2);

Answer 2

Excel Java JExcel API. , Excel, - .

 WritableWorkbook writableWorkbook = null;
 writableWorkbook = Workbook.createWorkbook(new File("WebSparrow.xls"));
 Workbook wb = null;
 wb = Workbook.getWorkbook(new File("WebSparrow.xls"));

http://www.websparrow.org/tutorials/java/how-to-read-and-write-excel-file-in-java