Attachment #22649 for bug #45918

View | Details | Raw Unified | Return to bug 45918
Collapse All | Expand All




import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument;

	}

	/**
	 * Return the underlying XML bean
	 *
	 * @return the underlying CTWorkbookPr bean
	 */
	public CTWorkbookPr getWorkbookPr() {
		CTWorkbookPr workbookPr = getWorkbook().getWorkbookPr() == null ? getWorkbook().addNewWorkbookPr() : getWorkbook().getWorkbookPr();
		return workbookPr;
	}
	
	/**
	 * Get the PackagePart corresponding to a given sheet.
	 *
	 * @param ctSheet The sheet

Lines 276-281 Link Here

(-)src/java/org/apache/poi/ss/usermodel/DateUtil.java (+1 lines)
276	double d = cell.getNumericCellValue();	276	double d = cell.getNumericCellValue();
277	if ( DateUtil.isValidExcelDate(d) ) {	277	if ( DateUtil.isValidExcelDate(d) ) {
278	CellStyle style = cell.getCellStyle();	278	CellStyle style = cell.getCellStyle();
		279	if(style==null) return false;
279	int i = style.getDataFormat();	280	int i = style.getDataFormat();
280	String f = style.getDataFormatString();	281	String f = style.getDataFormatString();
281	bDate = isADateFormat(i, f);	282	bDate = isADateFormat(i, f);

Lines 17-25 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFCell.java (-7 / +82 lines)
17		17
18	package org.apache.poi.xssf.usermodel;	18	package org.apache.poi.xssf.usermodel;
19		19
		20	import java.text.DateFormat;
		21	import java.text.SimpleDateFormat;
20	import java.util.Calendar;	22	import java.util.Calendar;
21	import java.util.Date;	23	import java.util.Date;
22		24
		25	import org.apache.poi.hssf.record.formula.eval.ErrorEval;
23	import org.apache.poi.hssf.usermodel.HSSFDateUtil;	26	import org.apache.poi.hssf.usermodel.HSSFDateUtil;
24	import org.apache.poi.ss.usermodel.Cell;	27	import org.apache.poi.ss.usermodel.Cell;
25	import org.apache.poi.ss.usermodel.CellStyle;	28	import org.apache.poi.ss.usermodel.CellStyle;
Lines 73-78 Link Here
73	protected SharedStringSource getSharedStringSource() {	76	protected SharedStringSource getSharedStringSource() {
74	return this.sharedStringSource;	77	return this.sharedStringSource;
75	}	78	}
		79
76	protected StylesSource getStylesSource() {	80	protected StylesSource getStylesSource() {
77	return this.stylesSource;	81	return this.stylesSource;
78	}	82	}
Lines 256-269 Link Here
256	}	260	}
257	throw new NumberFormatException("You cannot get a string value from a non-string cell");	261	throw new NumberFormatException("You cannot get a string value from a non-string cell");
258	}	262	}
259		263
		264	/**
		265	* Sets this cell as the active cell for the worksheet
		266	*/
260	public void setAsActiveCell() {	267	public void setAsActiveCell() {
261	row.getSheet().setActiveCell(cell.getR());	268	row.getSheet().setActiveCell(cell.getR());
262	}	269	}
263		270
		271
264	public void setCellComment(Comment comment) {	272	public void setCellComment(Comment comment) {
265	String cellRef =	273	String cellRef = new CellReference(row.getRowNum(), getCellNum()).formatAsString();
266	new CellReference(row.getRowNum(), getCellNum()).formatAsString();
267	row.getSheet().setCellComment(cellRef, (XSSFComment)comment);	274	row.getSheet().setCellComment(cellRef, (XSSFComment)comment);
268	}	275	}
269		276
Lines 352-357 Link Here
352	}	359	}
353	}	360	}
354		361
		362	/**
		363	* set the cells type (numeric, formula or string)
		364	* @see #CELL_TYPE_NUMERIC
		365	* @see #CELL_TYPE_STRING
		366	* @see #CELL_TYPE_FORMULA
		367	* @see #CELL_TYPE_BLANK
		368	* @see #CELL_TYPE_BOOLEAN
		369	* @see #CELL_TYPE_ERROR
		370	*/
355	public void setCellType(int cellType) {	371	public void setCellType(int cellType) {
356	switch (cellType) {	372	switch (cellType) {
357	case CELL_TYPE_BOOLEAN:	373	case CELL_TYPE_BOOLEAN:
Lines 379-391 Link Here
379	this.cell.setV(String.valueOf(value));	395	this.cell.setV(String.valueOf(value));
380	}	396	}
381		397
		398
		399	/**
		400	* set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
		401	* a date.
		402	*
		403	* @param value the date value to set this cell to. For formulas we'll set the
		404	* precalculated value, for numerics we'll set its value. For other types we
		405	* will change the cell to a numeric cell and set its value.
		406	*/
382	public void setCellValue(Date value) {	407	public void setCellValue(Date value) {
383	setCellValue(HSSFDateUtil.getExcelDate(value, false /this.book.isUsing1904DateWindowing()/)); // FIXME	408	boolean date1904 = this.row.getSheet().getWorkbook().getWorkbookPr().getDate1904();
		409	setCellValue(HSSFDateUtil.getExcelDate(value, date1904));
384	}	410	}
385		411
		412	/**
		413	* set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
		414	* a date.
		415	*
		416	* This will set the cell value based on the Calendar's timezone. As Excel
		417	* does not support timezones this means that both 20:00+03:00 and
		418	* 20:00-03:00 will be reported as the same value (20:00) even that there
		419	* are 6 hours difference between the two times. This difference can be
		420	* preserved by using <code>setCellValue(value.getTime())</code> which will
		421	* automatically shift the times to the default timezone.
		422	*
		423	* @param value the date value to set this cell to. For formulas we'll set the
		424	* precalculated value, for numerics we'll set its value. For othertypes we
		425	* will change the cell to a numeric cell and set its value.
		426	*/
386	public void setCellValue(Calendar value) {	427	public void setCellValue(Calendar value) {
387	// TODO Auto-generated method stub	428	boolean date1904 = this.row.getSheet().getWorkbook().getWorkbookPr().getDate1904();
388		429	setCellValue( HSSFDateUtil.getExcelDate(value, date1904 ));
389	}	430	}
390		431
391	public void setCellValue(String str) {	432	public void setCellValue(String str) {
Lines 414-421 Link Here
414	this.cell.setV(value ? TRUE_AS_STRING : FALSE_AS_STRING);	455	this.cell.setV(value ? TRUE_AS_STRING : FALSE_AS_STRING);
415	}	456	}
416		457
		458	/**
		459	* Returns a string representation of the cell
		460	*
		461	* This method returns a simple representation,
		462	* anthing more complex should be in user code, with
		463	* knowledge of the semantics of the sheet being processed.
		464	*
		465	* Formula cells return the formula string,
		466	* rather than the formula result.
		467	* Dates are displayed in dd-MMM-yyyy format
		468	* Errors are displayed as #ERR<errIdx>
		469	*/
417	public String toString() {	470	public String toString() {
418	return "[" + this.row.getRowNum() + "," + this.getCellNum() + "] " + this.cell.getV();	471	// return "[" + this.row.getRowNum() + "," + this.getCellNum() + "] " + this.cell.getV();
		472	switch (getCellType()) {
		473	case CELL_TYPE_BLANK:
		474	return "";
		475	case CELL_TYPE_BOOLEAN:
		476	return getBooleanCellValue() ? "TRUE" : "FALSE";
		477	case CELL_TYPE_ERROR:
		478	return ErrorEval.getText(getErrorCellValue());
		479	case CELL_TYPE_FORMULA:
		480	return getCellFormula();
		481	case CELL_TYPE_NUMERIC:
		482	//TODO apply the dataformat for this cell
		483	if (HSSFDateUtil.isCellDateFormatted(this)) {
		484	DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
		485	return sdf.format(getDateCellValue());
		486	} else {
		487	return getNumericCellValue() + "";
		488	}
		489	case CELL_TYPE_STRING:
		490	return getRichStringCellValue().toString();
		491	default:
		492	return "Unknown Cell Type: " + getCellType();
		493	}
419	}	494	}
420		495
421	/**	496	/**





import junit.framework.TestCase;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.XSSFTestDataSamples;
import org.apache.poi.xssf.model.CommentsTable;
import org.apache.poi.xssf.model.SharedStringSource;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.SharedStringSource;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComments;

        } catch (NumberFormatException e) {
            // success
        }
        
        cell.setCellValue(cal);
        assertEquals(before1904,cell.getDateCellValue());
        
    }
    
    public void testSetGetError() throws Exception {

    		fail();
    	} catch(IllegalArgumentException e) {}
    }
    
    
    public void testHSSFXSSFToString(){
	Workbook xwb = new XSSFWorkbook();
    	Sheet xsheet = xwb.createSheet();
    	XSSFCell xcell = (XSSFCell) xsheet.createRow(0).createCell((short)0);
    	
    	Workbook hwb=new HSSFWorkbook();
    	Sheet hsheet=hwb.createSheet();
    	HSSFCell hcell = (HSSFCell) hsheet.createRow(0).createCell((short)0);

    	//BLANK
    	assertEquals(hcell.toString(),xcell.toString());
    	System.out.println("BLANK==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
    	//BOOLEAN
    	xcell.setCellValue(true);
    	xcell.setCellType(Cell.CELL_TYPE_BOOLEAN);
    	hcell.setCellValue(true);
    	hcell.setCellType(Cell.CELL_TYPE_BOOLEAN);
    	System.out.println("BOOLEAN==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
    	assertEquals(hcell.toString(),xcell.toString());
    	
	//NUMERIC

    	xcell.setCellValue(1234);
    	xcell.setCellType(Cell.CELL_TYPE_NUMERIC);
    	hcell.setCellValue(1234);
    	hcell.setCellType(Cell.CELL_TYPE_NUMERIC);
    	System.out.println("NUMERIC==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
    	assertEquals(hcell.toString(),xcell.toString());
    	
    	//DATE ********************
    	
    	Calendar cal = Calendar.getInstance();
        cal.set(1903, 1, 8);
    	xcell.setCellValue(cal.getTime());
    	CellStyle xstyle=xwb.createCellStyle();
        DataFormat format = xwb.createDataFormat();
    	xstyle.setDataFormat(format.getFormat("YYYY-MM-DD"));
    	xcell.setCellStyle(xstyle);

    	hcell.setCellValue(cal.getTime());
    	CellStyle hstyle=hwb.createCellStyle();
        DataFormat hformat = hwb.createDataFormat();
    	hstyle.setDataFormat(hformat.getFormat("YYYY-MM-DD"));
    	hcell.setCellStyle(hstyle);
    	
    	System.out.println("DATE==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
    	assertEquals(hcell.toString(),xcell.toString());
    	
    	
    	//STRING
    	xcell.setCellValue(new XSSFRichTextString("text string"));
    	xcell.setCellType(Cell.CELL_TYPE_STRING);
    	hcell.setCellValue(new HSSFRichTextString("text string"));
    	hcell.setCellType(Cell.CELL_TYPE_STRING);
    	System.out.println("STRING==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
    	assertEquals(hcell.toString(),xcell.toString());
    	
    	//ERROR
    	xcell.setCellErrorValue(Cell.ERROR_VALUE);
    	xcell.setCellType(Cell.CELL_TYPE_ERROR);

    	hcell.setCellErrorValue((byte)0);
    	hcell.setCellType(Cell.CELL_TYPE_ERROR);

    	System.out.println("ERROR==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
    	assertEquals(hcell.toString(),xcell.toString());
    	
    	//FORMULA
    	xcell.setCellFormula("A1+B2");
    	hcell.setCellValue("A1+B2");
    	System.out.println("FORMULA==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
    	assertEquals(hcell.toString(),xcell.toString());
    	
    }
    
    
}

Return to bug 45918

Lines 57-62 Link Here

(-)src/ooxml/java/org/apache/poi/xssf/usermodel/XSSFWorkbook.java (+11 lines)
57	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet;	57	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDialogsheet;
58	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;	58	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet;
59	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;	59	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbook;
		60	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorkbookPr;
60	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;	61	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;
61	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf;	62	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTXf;
62	import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument;	63	import org.openxmlformats.schemas.spreadsheetml.x2006.main.WorkbookDocument;
Lines 200-205 Link Here
200	}	201	}
201		202
202	/**	203	/**
		204	* Return the underlying XML bean
		205	*
		206	* @return the underlying CTWorkbookPr bean
		207	*/
		208	public CTWorkbookPr getWorkbookPr() {
		209	CTWorkbookPr workbookPr = getWorkbook().getWorkbookPr() == null ? getWorkbook().addNewWorkbookPr() : getWorkbook().getWorkbookPr();
		210	return workbookPr;
		211	}
		212
		213	/**
203	* Get the PackagePart corresponding to a given sheet.	214	* Get the PackagePart corresponding to a given sheet.
204	*	215	*
205	* @param ctSheet The sheet	216	* @param ctSheet The sheet

Lines 23-39 Link Here

(-)src/ooxml/testcases/org/apache/poi/xssf/usermodel/TestXSSFCell.java (-1 / +87 lines)
23		23
24	import junit.framework.TestCase;	24	import junit.framework.TestCase;
25		25
		26	import org.apache.poi.hssf.usermodel.HSSFCell;
		27	import org.apache.poi.hssf.usermodel.HSSFRichTextString;
		28	import org.apache.poi.hssf.usermodel.HSSFWorkbook;
26	import org.apache.poi.ss.usermodel.Cell;	29	import org.apache.poi.ss.usermodel.Cell;
27	import org.apache.poi.ss.usermodel.CellStyle;	30	import org.apache.poi.ss.usermodel.CellStyle;
28	import org.apache.poi.ss.usermodel.Comment;	31	import org.apache.poi.ss.usermodel.Comment;
29	import org.apache.poi.ss.usermodel.CreationHelper;	32	import org.apache.poi.ss.usermodel.CreationHelper;
		33	import org.apache.poi.ss.usermodel.DataFormat;
30	import org.apache.poi.ss.usermodel.Row;	34	import org.apache.poi.ss.usermodel.Row;
31	import org.apache.poi.ss.usermodel.Sheet;	35	import org.apache.poi.ss.usermodel.Sheet;
32	import org.apache.poi.ss.usermodel.Workbook;	36	import org.apache.poi.ss.usermodel.Workbook;
33	import org.apache.poi.xssf.XSSFTestDataSamples;	37	import org.apache.poi.xssf.XSSFTestDataSamples;
34	import org.apache.poi.xssf.model.CommentsTable;	38	import org.apache.poi.xssf.model.CommentsTable;
		39	import org.apache.poi.xssf.model.SharedStringSource;
35	import org.apache.poi.xssf.model.SharedStringsTable;	40	import org.apache.poi.xssf.model.SharedStringsTable;
36	import org.apache.poi.xssf.model.SharedStringSource;
37	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;	41	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCell;
38	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;	42	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComment;
39	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComments;	43	import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTComments;
Lines 108-113 Link Here
108	} catch (NumberFormatException e) {	112	} catch (NumberFormatException e) {
109	// success	113	// success
110	}	114	}
		115
		116	cell.setCellValue(cal);
		117	assertEquals(before1904,cell.getDateCellValue());
		118
111	}	119	}
112		120
113	public void testSetGetError() throws Exception {	121	public void testSetGetError() throws Exception {
Lines 379-382 Link Here
379	fail();	387	fail();
380	} catch(IllegalArgumentException e) {}	388	} catch(IllegalArgumentException e) {}
381	}	389	}
		390
		391
		392	public void testHSSFXSSFToString(){
		393	Workbook xwb = new XSSFWorkbook();
		394	Sheet xsheet = xwb.createSheet();
		395	XSSFCell xcell = (XSSFCell) xsheet.createRow(0).createCell((short)0);
		396
		397	Workbook hwb=new HSSFWorkbook();
		398	Sheet hsheet=hwb.createSheet();
		399	HSSFCell hcell = (HSSFCell) hsheet.createRow(0).createCell((short)0);
		400
		401	//BLANK
		402	assertEquals(hcell.toString(),xcell.toString());
		403	System.out.println("BLANK==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
		404	//BOOLEAN
		405	xcell.setCellValue(true);
		406	xcell.setCellType(Cell.CELL_TYPE_BOOLEAN);
		407	hcell.setCellValue(true);
		408	hcell.setCellType(Cell.CELL_TYPE_BOOLEAN);
		409	System.out.println("BOOLEAN==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
		410	assertEquals(hcell.toString(),xcell.toString());
		411
		412	//NUMERIC
		413
		414	xcell.setCellValue(1234);
		415	xcell.setCellType(Cell.CELL_TYPE_NUMERIC);
		416	hcell.setCellValue(1234);
		417	hcell.setCellType(Cell.CELL_TYPE_NUMERIC);
		418	System.out.println("NUMERIC==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
		419	assertEquals(hcell.toString(),xcell.toString());
		420
		421	//DATE ********************
		422
		423	Calendar cal = Calendar.getInstance();
		424	cal.set(1903, 1, 8);
		425	xcell.setCellValue(cal.getTime());
		426	CellStyle xstyle=xwb.createCellStyle();
		427	DataFormat format = xwb.createDataFormat();
		428	xstyle.setDataFormat(format.getFormat("YYYY-MM-DD"));
		429	xcell.setCellStyle(xstyle);
		430
		431	hcell.setCellValue(cal.getTime());
		432	CellStyle hstyle=hwb.createCellStyle();
		433	DataFormat hformat = hwb.createDataFormat();
		434	hstyle.setDataFormat(hformat.getFormat("YYYY-MM-DD"));
		435	hcell.setCellStyle(hstyle);
		436
		437	System.out.println("DATE==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
		438	assertEquals(hcell.toString(),xcell.toString());
		439
		440
		441	//STRING
		442	xcell.setCellValue(new XSSFRichTextString("text string"));
		443	xcell.setCellType(Cell.CELL_TYPE_STRING);
		444	hcell.setCellValue(new HSSFRichTextString("text string"));
		445	hcell.setCellType(Cell.CELL_TYPE_STRING);
		446	System.out.println("STRING==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
		447	assertEquals(hcell.toString(),xcell.toString());
		448
		449	//ERROR
		450	xcell.setCellErrorValue(Cell.ERROR_VALUE);
		451	xcell.setCellType(Cell.CELL_TYPE_ERROR);
		452
		453	hcell.setCellErrorValue((byte)0);
454	hcell.setCellType(Cell.CELL_TYPE_ERROR);
455
456	System.out.println("ERROR==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
457	assertEquals(hcell.toString(),xcell.toString());
458
459	//FORMULA
460	xcell.setCellFormula("A1+B2");
461	hcell.setCellValue("A1+B2");
462	System.out.println("FORMULA==> xssf="+xcell.toString() + " - hssf="+hcell.toString());
463	assertEquals(hcell.toString(),xcell.toString());
464
465	}
466
467
382	}	468	}