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.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	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	mojays	1345	import org.junit.Ignore;
24	alfonx	1344	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	mojays	1345	@Ignore
33	alfonx	1344	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	mojays	1345	log.info("new value B7 = " + cellB7.getNumericCellValue());
156	alfonx	1344	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