2.4.x/src_junit/JxlKloseTest.java

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;

import jxl.NumberFormulaCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.biff.formula.FormulaException;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.junit.Test;

import schmitzm.junit.TestingClass;
import schmitzm.swing.TestingUtil;

public class JxlKloseTest extends TestingClass {

        @Test
        public void testJxl() throws IOException, WriteException, BiffException,
                        FormulaException {
                InputStream xlsUrl = JxlKloseTest.class
                                .getResourceAsStream("/schmitzm/jxl/gwbalIWEGS_blm_2122010.xls");
                assertNotNull(xlsUrl);

                Workbook wb_vorlage = Workbook.getWorkbook(xlsUrl);
                assertNotNull(wb_vorlage);

                File td = TestingUtil.getNewTempDir();
                File file = new File(td, "new.xls");
                WritableWorkbook wb = Workbook.createWorkbook(file, wb_vorlage);

                WritableSheet sheet = wb.getSheet("domestic extraction");
                assertNotNull(sheet);

                // D3
                {
                        jxl.write.Number cellD3 = (jxl.write.Number) sheet.getCell("D3");
                        assertNotNull(cellD3);
                        log.info("orig value D3 = " + cellD3.getValue());
                        assertEquals(0.8, cellD3.getValue(), 0.00001);

                        // B32, B33 is fixed
                        NumberFormulaCell cellB32 = (NumberFormulaCell) sheet
                                        .getCell("B32");
                        assertNotNull(cellB32);
                        log.info("orig value B32 = " + cellB32.getContents());
                        assertEquals("B33+((-$D$3/100.0)*B33)", cellB32.getFormula());
                        assertEquals(15160.736, cellB32.getValue(), 0.00001);
                }

                // // B7, B8 is dynamic formula
                // NumberFormulaCell cellB7 = (NumberFormulaCell) sheet.getCell("B7");
                // assertNotNull(cellB7);
                // log.info("orig value B7 = " + cellB7.getContents());
                // assertEquals("12402,581", cellB7.getContents());
                // assertEquals("B8+((-$D$3/100.0)*B8)", cellB7.getFormula());

                // D3
                {
                        jxl.write.Number cellD3 = (jxl.write.Number) sheet.getCell("D3");
                        assertNotNull(cellD3);
                        cellD3.setValue(2.0);
                        log.info("new value D3 = " + cellD3.getValue());
                        assertEquals(2.0, cellD3.getValue(), 0.00001);

                }

                wb.write();
                wb.close();

                {

                        // load new file

                        Workbook wbnew = Workbook.getWorkbook(file);
                        Sheet testsheet = wbnew.getSheet("domestic extraction");

                        // B32, B33 is fixed
                        NumberFormulaCell cellB32 = (NumberFormulaCell) testsheet
                                        .getCell("B32");
                        assertNotNull(cellB32);
                        log.info("new value B32 = " + cellB32.getContents());
                        assertEquals("B33+((-$D$3/100.0)*B33)", cellB32.getFormula());
                        assertEquals(14977.34, cellB32.getValue(), 0.00001);
                }

                assertTrue(file.exists());
                assertTrue(file.length() > 100);

                // Desktop.getDesktop().open(file);
        }

        @SuppressWarnings("deprecation")
        @Test
        public void testPoi() throws IOException, WriteException, BiffException,
                        FormulaException {
                InputStream xlsUrl = JxlKloseTest.class
                                .getResourceAsStream("/schmitzm/jxl/gwbalIWEGS_blm_2122010.xls");
                assertNotNull(xlsUrl);

                HSSFWorkbook wb = new HSSFWorkbook(xlsUrl);
                assertNotNull(wb);

                HSSFSheet sheet = wb.getSheet("domestic extraction");
                assertNotNull(sheet);

                // D3
                CellReference cellReference = new CellReference("D3");
                HSSFCell cellD3 = sheet.getRow(cellReference.getRow()).getCell(
                                cellReference.getCol());
                assertNotNull(cellD3);
                log.info("orig value D3 = " + cellD3.getNumericCellValue());
                assertEquals(0.8, cellD3.getNumericCellValue(), 0.00001);

                // B32, B33 is fixed
                CellReference b32Ref = new CellReference("B32");
                HSSFCell cellB32 = sheet.getRow(b32Ref.getRow()).getCell(
                                b32Ref.getCol());
                assertNotNull(cellB32);
                log.info("orig value B32 = " + cellB32.getNumericCellValue());
                assertEquals("B33+((-$D$3/100)*B33)", cellB32.getCellFormula());
                assertEquals(15160.736, cellB32.getNumericCellValue(), 0.00001);

                // D3
                cellD3.setCellValue(2.0);
                log.info("new value D3 = " + cellD3.getNumericCellValue());
                assertEquals(2.0, cellD3.getNumericCellValue(), 0.00001);

                HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);

                // // B32, B33 is fixed
                log.info("new value B32 = " + cellB32.getNumericCellValue());
                assertEquals("B33+((-$D$3/100)*B33)", cellB32.getCellFormula());
                assertEquals(14977.34, cellB32.getNumericCellValue(), 0.00001);

                // B7, B8 is dynamic formula
                CellReference b7cellRef = new CellReference("B7");
                HSSFCell cellB7 = sheet.getRow(b7cellRef.getRow()).getCell(
                                b7cellRef.getCol());
                assertNotNull(cellB7);
                log.info("orig value B7 = " + cellB7.getNumericCellValue());
                assertEquals("B8+((-$D$3/100)*B8)", cellB7.getCellFormula());
                assertEquals(9038.2964, cellB7.getNumericCellValue(), 0.0001);
                
        }

}
1	import static org.junit.Assert.assertEquals;
2	import static org.junit.Assert.assertNotNull;
3	import static org.junit.Assert.assertTrue;
4
5	import java.io.File;
6	import java.io.IOException;
7	import java.io.InputStream;
8
9	import jxl.NumberFormulaCell;
10	import jxl.Sheet;
11	import jxl.Workbook;
12	import jxl.biff.formula.FormulaException;
13	import jxl.read.biff.BiffException;
14	import jxl.write.WritableSheet;
15	import jxl.write.WritableWorkbook;
16	import jxl.write.WriteException;
17
18	import org.apache.poi.hssf.usermodel.HSSFCell;
19	import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
20	import org.apache.poi.hssf.usermodel.HSSFSheet;
21	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
22	import org.apache.poi.hssf.util.CellReference;
23	import org.junit.Test;
24
25	import schmitzm.junit.TestingClass;
26	import schmitzm.swing.TestingUtil;
27
28	public class JxlKloseTest extends TestingClass {
29
30	@Test
31	public void testJxl() throws IOException, WriteException, BiffException,
32	FormulaException {
33	InputStream xlsUrl = JxlKloseTest.class
34	.getResourceAsStream("/schmitzm/jxl/gwbalIWEGS_blm_2122010.xls");
35	assertNotNull(xlsUrl);
36
37	Workbook wb_vorlage = Workbook.getWorkbook(xlsUrl);
38	assertNotNull(wb_vorlage);
39
40	File td = TestingUtil.getNewTempDir();
41	File file = new File(td, "new.xls");
42	WritableWorkbook wb = Workbook.createWorkbook(file, wb_vorlage);
43
44	WritableSheet sheet = wb.getSheet("domestic extraction");
45	assertNotNull(sheet);
46
47	// D3
48	{
49	jxl.write.Number cellD3 = (jxl.write.Number) sheet.getCell("D3");
50	assertNotNull(cellD3);
51	log.info("orig value D3 = " + cellD3.getValue());
52	assertEquals(0.8, cellD3.getValue(), 0.00001);
53
54	// B32, B33 is fixed
55	NumberFormulaCell cellB32 = (NumberFormulaCell) sheet
56	.getCell("B32");
57	assertNotNull(cellB32);
58	log.info("orig value B32 = " + cellB32.getContents());
59	assertEquals("B33+((-$D$3/100.0)*B33)", cellB32.getFormula());
60	assertEquals(15160.736, cellB32.getValue(), 0.00001);
61	}
62
63	// // B7, B8 is dynamic formula
64	// NumberFormulaCell cellB7 = (NumberFormulaCell) sheet.getCell("B7");
65	// assertNotNull(cellB7);
66	// log.info("orig value B7 = " + cellB7.getContents());
67	// assertEquals("12402,581", cellB7.getContents());
68	// assertEquals("B8+((-$D$3/100.0)*B8)", cellB7.getFormula());
69
70	// D3
71	{
72	jxl.write.Number cellD3 = (jxl.write.Number) sheet.getCell("D3");
73	assertNotNull(cellD3);
74	cellD3.setValue(2.0);
75	log.info("new value D3 = " + cellD3.getValue());
76	assertEquals(2.0, cellD3.getValue(), 0.00001);
77
78	}
79
80	wb.write();
81	wb.close();
82
83	{
84
85	// load new file
86
87	Workbook wbnew = Workbook.getWorkbook(file);
88	Sheet testsheet = wbnew.getSheet("domestic extraction");
89
90	// B32, B33 is fixed
91	NumberFormulaCell cellB32 = (NumberFormulaCell) testsheet
92	.getCell("B32");
93	assertNotNull(cellB32);
94	log.info("new value B32 = " + cellB32.getContents());
95	assertEquals("B33+((-$D$3/100.0)*B33)", cellB32.getFormula());
96	assertEquals(14977.34, cellB32.getValue(), 0.00001);
97	}
98
99	assertTrue(file.exists());
100	assertTrue(file.length() > 100);
101
102	// Desktop.getDesktop().open(file);
103	}
104
105	@SuppressWarnings("deprecation")
106	@Test
107	public void testPoi() throws IOException, WriteException, BiffException,
108	FormulaException {
109	InputStream xlsUrl = JxlKloseTest.class
110	.getResourceAsStream("/schmitzm/jxl/gwbalIWEGS_blm_2122010.xls");
111	assertNotNull(xlsUrl);
112
113	HSSFWorkbook wb = new HSSFWorkbook(xlsUrl);
114	assertNotNull(wb);
115
116	HSSFSheet sheet = wb.getSheet("domestic extraction");
117	assertNotNull(sheet);
118
119	// D3
120	CellReference cellReference = new CellReference("D3");
121	HSSFCell cellD3 = sheet.getRow(cellReference.getRow()).getCell(
122	cellReference.getCol());
123	assertNotNull(cellD3);
124	log.info("orig value D3 = " + cellD3.getNumericCellValue());
125	assertEquals(0.8, cellD3.getNumericCellValue(), 0.00001);
126
127	// B32, B33 is fixed
128	CellReference b32Ref = new CellReference("B32");
129	HSSFCell cellB32 = sheet.getRow(b32Ref.getRow()).getCell(
130	b32Ref.getCol());
131	assertNotNull(cellB32);
132	log.info("orig value B32 = " + cellB32.getNumericCellValue());
133	assertEquals("B33+((-$D$3/100)*B33)", cellB32.getCellFormula());
134	assertEquals(15160.736, cellB32.getNumericCellValue(), 0.00001);
135
136	// D3
137	cellD3.setCellValue(2.0);
138	log.info("new value D3 = " + cellD3.getNumericCellValue());
139	assertEquals(2.0, cellD3.getNumericCellValue(), 0.00001);
140
141	HSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
142
143	// // B32, B33 is fixed
144	log.info("new value B32 = " + cellB32.getNumericCellValue());
145	assertEquals("B33+((-$D$3/100)*B33)", cellB32.getCellFormula());
146	assertEquals(14977.34, cellB32.getNumericCellValue(), 0.00001);
147
148	// B7, B8 is dynamic formula
149	CellReference b7cellRef = new CellReference("B7");
150	HSSFCell cellB7 = sheet.getRow(b7cellRef.getRow()).getCell(
151	b7cellRef.getCol());
152	assertNotNull(cellB7);
153	log.info("orig value B7 = " + cellB7.getNumericCellValue());
154	assertEquals("B8+((-$D$3/100)*B8)", cellB7.getCellFormula());
155	assertEquals(9038.2964, cellB7.getNumericCellValue(), 0.0001);
156
157	}
158
159	}
Name	Value
svn:eol-style	native
svn:keywords	Id URL
svn:mime-type	text/plain