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.Ignore;
import org.junit.Test;

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

public class JxlKloseTest extends TestingClass {

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