trunk/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	alfonx	1344	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