how to input invalid formula in Excel using Java

Question

I'm currently making a code that creates an Excel using Java jxl. I've encountered no problem as long as the formula I'm using exists. The problem is, I need to input a formula which excel will not be able to find because we use a special excel to read it. I need my code to retain the invalid formula instead of writing =1 ERROR() on the cell so that when my file is opened using our special excel, it gets the value. How do I go about this?

Here is my code:

WorkbookSettings ws = new WorkbookSettings();
            ws.setLocale(new Locale("en", "EN"));
            workbook = Workbook.createWorkbook(file, ws);
            WritableSheet s = workbook.createSheet("Sheet1", 0);
            WritableFont wf = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD);
            DateFormat dateFormat = new DateFormat("M/d/yyyy H:mm");
            NumberFormat dp = new NumberFormat("#");
            NumberFormat dp2 = new NumberFormat("#.##");
            ResultSetMetaData rsmd = rs.getMetaData();
            int nRow = 0;
            Label l = null;
            Formula f = null;
            for(int index = 1; index <= rsmd.getColumnCount(); index++) {
                l = new Label(index-1,nRow,rsmd.getColumnName(index),new WritableCellFormat(wf));
                s.addCell(l);
            }

            nRow++;
            while(rs.next()) {
                String valueDate = VerifyNull.ifNull(rs.getString(1), Constant.BLANK);
                f = new Formula(0,nRow,valueDate,new WritableCellFormat(dp));
                s.addCell(f);
                String rateDate = VerifyNull.ifNull(rs.getString(2), Constant.BLANK);
                f = new Formula(1,nRow,rateDate,new WritableCellFormat(dp));
                s.addCell(f);
                String market = VerifyNull.ifNull(rs.getString(3), Constant.BLANK);
                l = new Label(3,nRow,market,new WritableCellFormat(wf));
                s.addCell(l);
                String ccy = VerifyNull.ifNull(rs.getString(4), Constant.BLANK);
                l = new Label(4,nRow,ccy,new WritableCellFormat(wf));
                s.addCell(l);
                String label = VerifyNull.ifNull(rs.getString(5), Constant.BLANK);
                l = new Label(5,nRow,label,new WritableCellFormat(wf));
                s.addCell(l);
                String quoteDate = VerifyNull.ifNull(rs.getString(6), Constant.BLANK);
                f = new Formula(1,nRow,quoteDate,new WritableCellFormat(dp));
                s.addCell(f);
                String bid = "="+VerifyNull.ifNull(rs.getString(7), Constant.BLANK);

/** The value in the bid = "BDP("EURON Curncy","PX_BID","DATA")1"; */

                f = new Formula(6,nRow,bid,new WritableCellFormat(dp2));
                s.addCell(f);

/** The value in the offer = "BDP("EURON Curncy","PX_ASK","DATA")1"; */

                String offer = "="+VerifyNull.ifNull(rs.getString(8), Constant.BLANK);
                f = new Formula(7,nRow,offer,new WritableCellFormat(dp2));
                s.addCell(f);
                String timeStamp = VerifyNull.ifNull(rs.getString(9), Constant.BLANK);
                f = new Formula(8,nRow,timeStamp,new WritableCellFormat(dateFormat));
                s.addCell(f);
                nRow++;
            }
            workbook.write();
            workbook.close();

Telcontar · Accepted Answer · 2010-11-02 10:28:13Z

1

I supose that your "special excel" is that you have a plug in with visual basic functions to use whithin cell formulas

I dont know if the current version of JXL support macros or at least don't delete it when you modify an existent Workbook, but in your code your are not updating an existing Workbook, your are creating a new one

have you tried to use your code opening an existing workbook in which the visual basic function works? (it can be an empty one). If it works, you can use that file as a template, copy it to the location you want, rename it, and edit it with JXL

answered Nov 2, 2010 at 10:28

Telcontar

4,8648 gold badges33 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

neocotic · Accepted Answer · 2010-11-02 09:26:27Z

0

Sorry, I only have time for a quick reply, but have you looked at Apache POI at all? Its API might be of use to you.

However, I'm not 100% sure this is for writing Excel as I think its primary use is reading MS Office files. Still, might be worth a look though.

answered Nov 2, 2010 at 9:26

neocotic

2,13118 silver badges18 bronze badges

Comments

duffymo · Accepted Answer · 2010-11-02 09:30:24Z

0

The problem is, I need to input a formula which excel will not be able to find because we use a special excel to read it. I need my code to retain the invalid formula instead of writing =1 ERROR() on the cell so that when my file is opened using our special excel, it gets the value.

Your problem isn't JExcel or POI. No API will be able to help you with a formula that it cannot be found.

What is this "special excel"? If it's another file, simply read it in and use it.

Your question is confusing. I'm having a hard time understanding what exactly is going on. I'm voting to close unless you can clarify.

answered Nov 2, 2010 at 9:30

duffymo

310k46 gold badges377 silver badges572 bronze badges

Collectives™ on Stack Overflow

how to input invalid formula in Excel using Java

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related