Java Code Examples for org.apache.poi.ss.usermodel.Cell#setCellFormula()
The following examples show how to use
org.apache.poi.ss.usermodel.Cell#setCellFormula() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
| Source File: ExcelExportOfTemplateUtil.java From jeewx with Apache License 2.0 | 7 votes |
|
/**
* 创建文本类型的Cell
*
* @param row
* @param index
* @param text
* @param style
* @param entity
* @param workbook
*/
private static void createStringCell(Row row, int index, String text,
ExcelExportEntity entity, Workbook workbook) {
Cell cell = row.createCell(index);
switch (entity.getType()) {
case 1:
RichTextString Rtext = workbook instanceof HSSFWorkbook ? new HSSFRichTextString(
text) : new XSSFRichTextString(text);
cell.setCellValue(Rtext);
break;
case 2:
cell.setCellType(Cell.CELL_TYPE_FORMULA);
cell.setCellFormula(entity.getCellFormula());
break;
}
}
Example 2
| Source File: GenerateDoc.java From danyuan-application with Apache License 2.0 | 6 votes |
|
@SuppressWarnings("deprecation")
private static XSSFSheet copySheet(XSSFSheet sheetFrom, XSSFSheet sheetTo) {
// 初期化
CellRangeAddress region = null;
Row rowFrom = null;
Row rowTo = null;
Cell cellFrom = null;
Cell cellTo = null;
// セル結合のコピー
for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
region = sheetFrom.getMergedRegion(i);
if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
sheetTo.addMergedRegion(region);
}
}
// セルのコピー
for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
rowFrom = sheetFrom.getRow(intRow);
rowTo = sheetTo.createRow(intRow);
if (null == rowFrom) {
continue;
}
rowTo.setHeight(rowFrom.getHeight());
for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
// セル幅のコピー
sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
cellFrom = rowFrom.getCell(intCol);
cellTo = rowTo.createCell(intCol);
if (null == cellFrom) {
continue;
}
// セルスタイルとタイプのコピー
cellTo.setCellStyle(cellFrom.getCellStyle());
cellTo.setCellType(cellFrom.getCellType());
// タイトル内容のコピー
// 不同数据类型处理
int cellFromType = cellFrom.getCellType();
cellTo.setCellType(cellFromType);
if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
cellTo.setCellValue(cellFrom.getDateCellValue());
} else {
cellTo.setCellValue(cellFrom.getNumericCellValue());
}
} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
cellTo.setCellValue(cellFrom.getRichStringCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
cellTo.setCellValue(cellFrom.getBooleanCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
cellTo.setCellFormula(cellFrom.getCellFormula());
} else { // nothing29
}
}
}
// 枠線の設定
sheetTo.setDisplayGridlines(false);
// sheetTo.setDisplayGuts(true);
// sheetTo.setDisplayRowColHeadings(true);
// 剪切
// sheetTo.shiftRows(13, 15, 31, false, false, false);
// Excelのズーム設定
sheetTo.setZoom(85, 100);
// シートを戻る。
return sheetTo;
}
Example 3
| Source File: SheetBuilder.java From lams with GNU General Public License v2.0 | 6 votes |
|
/**
* Sets the cell value using object type information.
*
* @param cell cell to change
* @param value value to set
*/
private void setCellValue(Cell cell, Object value) {
if (value == null || cell == null) {
return;
} else if (value instanceof Number) {
double doubleValue = ((Number) value).doubleValue();
cell.setCellValue(doubleValue);
} else if (value instanceof Date) {
cell.setCellValue((Date) value);
} else if (value instanceof Calendar) {
cell.setCellValue((Calendar) value);
} else if (isFormulaDefinition(value)) {
cell.setCellFormula(getFormula(value));
} else {
cell.setCellValue(value.toString());
}
}
Example 4
| Source File: PoiSheetUtility.java From autopoi with Apache License 2.0 | 5 votes |
|
private static void cloneCell(Cell cNew, Cell cOld) {
cNew.setCellComment(cOld.getCellComment());
cNew.setCellStyle(cOld.getCellStyle());
switch (cNew.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN: {
cNew.setCellValue(cOld.getBooleanCellValue());
break;
}
case Cell.CELL_TYPE_NUMERIC: {
cNew.setCellValue(cOld.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_STRING: {
cNew.setCellValue(cOld.getStringCellValue());
break;
}
case Cell.CELL_TYPE_ERROR: {
cNew.setCellValue(cOld.getErrorCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
cNew.setCellFormula(cOld.getCellFormula());
break;
}
}
}
Example 5
| Source File: CellFormulaHandler.java From xlsmapper with Apache License 2.0 | 5 votes |
|
/**
* セルに数式を設定する
* @param field フィールド情報
* @param config システム情報
* @param cell セル情報
* @param targetBean 処理対象のフィールドが定義されているクラスのインスタンス。
* @throws ConversionException 数式の解析に失敗した場合。
*/
public void handleFormula(final FieldAccessor field, final Configuration config, final Cell cell, final Object targetBean) {
ArgUtils.notNull(field, "field");
ArgUtils.notNull(config, "config");
ArgUtils.notNull(cell, "cell");
final String evaluatedFormula = createFormulaValue(config, cell, targetBean);
if(Utils.isEmpty(evaluatedFormula)) {
cell.setCellType(CellType.BLANK);
return;
}
try {
cell.setCellFormula(evaluatedFormula);
cell.setCellType(CellType.FORMULA);
} catch(FormulaParseException e) {
// 数式の解析に失敗した場合
String message = MessageBuilder.create("cell.failParseFormula")
.var("property", field.getNameWithClass())
.var("cellAddress", CellPosition.of(cell).toString())
.var("formula", evaluatedFormula)
.format();
throw new ConversionException(message, e, field.getType());
}
}
Example 6
| Source File: SpreadsheetSetCellFormula.java From openbd-core with GNU General Public License v3.0 | 5 votes |
|
public cfData execute( cfSession _session, List<cfData> parameters ) throws cfmRunTimeException {
cfSpreadSheetData spreadsheet = null;
String formula;
int rowNo, columnNo;
/*
* Collect up the parameters
*/
spreadsheet = (cfSpreadSheetData)parameters.get(3);
formula = parameters.get(2).getString();
rowNo = parameters.get(1).getInt() - 1;
columnNo = parameters.get(0).getInt() - 1;
if ( rowNo < 0 )
throwException(_session, "row must be 1 or greater (" + rowNo + ")");
if ( columnNo < 0 )
throwException(_session, "column must be 1 or greater (" + columnNo + ")");
/*
* Perform the insertion
*/
Sheet sheet = spreadsheet.getActiveSheet();
Row row = sheet.getRow( rowNo );
if ( row == null )
row = sheet.createRow( rowNo );
Cell cell = row.getCell( columnNo );
if ( cell == null )
cell = row.createCell( columnNo );
cell.setCellFormula( formula );
return cfBooleanData.TRUE;
}
Example 7
| Source File: SheetUtility.java From openbd-core with GNU General Public License v3.0 | 5 votes |
|
public static void cloneCell( Cell cNew, Cell cOld ){
cNew.setCellComment( cOld.getCellComment() );
cNew.setCellStyle( cOld.getCellStyle() );
cNew.setCellType( cOld.getCellType() );
switch ( cNew.getCellType() ){
case Cell.CELL_TYPE_BOOLEAN:{
cNew.setCellValue( cOld.getBooleanCellValue() );
break;
}
case Cell.CELL_TYPE_NUMERIC:{
cNew.setCellValue( cOld.getNumericCellValue() );
break;
}
case Cell.CELL_TYPE_STRING:{
cNew.setCellValue( cOld.getStringCellValue() );
break;
}
case Cell.CELL_TYPE_ERROR:{
cNew.setCellValue( cOld.getErrorCellValue() );
break;
}
case Cell.CELL_TYPE_FORMULA:{
cNew.setCellFormula( cOld.getCellFormula() );
break;
}
case Cell.CELL_TYPE_BLANK:{
cNew.setCellValue( cOld.getNumericCellValue() );
break;
}
}
}
Example 8
| Source File: PoiSheetUtility.java From easypoi with Apache License 2.0 | 5 votes |
|
private static void cloneCell(Cell cNew, Cell cOld) {
cNew.setCellComment(cOld.getCellComment());
cNew.setCellStyle(cOld.getCellStyle());
switch (cNew.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN: {
cNew.setCellValue(cOld.getBooleanCellValue());
break;
}
case Cell.CELL_TYPE_NUMERIC: {
cNew.setCellValue(cOld.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_STRING: {
cNew.setCellValue(cOld.getStringCellValue());
break;
}
case Cell.CELL_TYPE_ERROR: {
cNew.setCellValue(cOld.getErrorCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
cNew.setCellFormula(cOld.getCellFormula());
break;
}
}
}
Example 9
| Source File: PoiSheetUtility.java From jeasypoi with Apache License 2.0 | 5 votes |
|
private static void cloneCell(Cell cNew, Cell cOld) {
cNew.setCellComment(cOld.getCellComment());
cNew.setCellStyle(cOld.getCellStyle());
switch (cNew.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN: {
cNew.setCellValue(cOld.getBooleanCellValue());
break;
}
case Cell.CELL_TYPE_NUMERIC: {
cNew.setCellValue(cOld.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_STRING: {
cNew.setCellValue(cOld.getStringCellValue());
break;
}
case Cell.CELL_TYPE_ERROR: {
cNew.setCellValue(cOld.getErrorCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA: {
cNew.setCellFormula(cOld.getCellFormula());
break;
}
}
}
Example 10
| Source File: AbstractExcelWriteExecutor.java From easyexcel with Apache License 2.0 | 5 votes |
|
protected CellData converterAndSet(WriteHolder currentWriteHolder, Class clazz, Cell cell, Object value,
ExcelContentProperty excelContentProperty, Head head, Integer relativeRowIndex) {
if (value == null) {
return new CellData(CellDataTypeEnum.EMPTY);
}
if (value instanceof String && currentWriteHolder.globalConfiguration().getAutoTrim()) {
value = ((String)value).trim();
}
CellData cellData = convert(currentWriteHolder, clazz, cell, value, excelContentProperty);
if (cellData.getFormula() != null && cellData.getFormula()) {
cell.setCellFormula(cellData.getFormulaValue());
}
if (cellData.getType() == null) {
cellData.setType(CellDataTypeEnum.EMPTY);
}
WriteHandlerUtils.afterCellDataConverted(writeContext, cellData, cell, head, relativeRowIndex, Boolean.FALSE);
switch (cellData.getType()) {
case STRING:
cell.setCellValue(cellData.getStringValue());
return cellData;
case BOOLEAN:
cell.setCellValue(cellData.getBooleanValue());
return cellData;
case NUMBER:
cell.setCellValue(cellData.getNumberValue().doubleValue());
return cellData;
case IMAGE:
setImageValue(cellData, cell);
return cellData;
case EMPTY:
return cellData;
default:
throw new ExcelDataConvertException(cell.getRow().getRowNum(), cell.getColumnIndex(), cellData,
excelContentProperty, "Not supported data:" + value + " return type:" + cell.getCellType()
+ "at row:" + cell.getRow().getRowNum());
}
}
Example 11
| Source File: CellUtils.java From java-master with Apache License 2.0 | 5 votes |
|
public static void fillFuncCell(Cell cell, String funcStr, CellStyle style) {
cell.setCellType(CellType.NUMERIC);
cell.setCellFormula(funcStr);
if (funcStr == null) {
return;
}
cell.setCellStyle(style);
}
Example 12
| Source File: AbstractExcelFactory.java From myexcel with Apache License 2.0 | 4 votes |
|
/**
* 创建单元格
*
* @param td td
* @param sheet sheet
* @param currentRow 当前行
*/
protected void createCell(Td td, Sheet sheet, Row currentRow) {
Cell cell;
if (td.isFormula()) {
cell = currentRow.createCell(td.getCol(), CellType.FORMULA);
cell.setCellFormula(td.getContent());
} else {
String content = td.getContent();
switch (td.getTdContentType()) {
case STRING:
cell = currentRow.createCell(td.getCol(), CellType.STRING);
cell.setCellValue(content);
break;
case DOUBLE:
cell = currentRow.createCell(td.getCol(), CellType.NUMERIC);
if (null != content) {
cell.setCellValue(Double.parseDouble(content));
}
break;
case DATE:
cell = currentRow.createCell(td.getCol());
if (td.getDate() != null) {
cell.setCellValue(td.getDate());
} else if (td.getLocalDateTime() != null) {
cell.setCellValue(td.getLocalDateTime());
} else if (td.getLocalDate() != null) {
cell.setCellValue(td.getLocalDate());
}
break;
case BOOLEAN:
cell = currentRow.createCell(td.getCol(), CellType.BOOLEAN);
if (null != content) {
cell.setCellValue(Boolean.parseBoolean(content));
}
break;
case NUMBER_DROP_DOWN_LIST:
cell = currentRow.createCell(td.getCol(), CellType.NUMERIC);
String firstEle = setDropDownList(td, sheet, content);
if (firstEle != null) {
cell.setCellValue(Double.parseDouble(firstEle));
}
break;
case BOOLEAN_DROP_DOWN_LIST:
cell = currentRow.createCell(td.getCol(), CellType.BOOLEAN);
firstEle = setDropDownList(td, sheet, content);
if (firstEle != null) {
cell.setCellValue(Boolean.parseBoolean(firstEle));
}
break;
case DROP_DOWN_LIST:
cell = currentRow.createCell(td.getCol(), CellType.STRING);
firstEle = setDropDownList(td, sheet, content);
if (firstEle != null) {
cell.setCellValue(firstEle);
}
break;
case LINK_URL:
cell = setLink(td, currentRow, HyperlinkType.URL);
break;
case LINK_EMAIL:
cell = setLink(td, currentRow, HyperlinkType.EMAIL);
break;
case IMAGE:
cell = currentRow.createCell(td.getCol());
setImage(td, sheet);
break;
default:
cell = currentRow.createCell(td.getCol(), CellType.STRING);
cell.setCellValue(content);
break;
}
}
// 设置单元格样式
this.setCellStyle(currentRow, cell, td);
if (td.getCol() != td.getColBound()) {
for (int j = td.getCol() + 1, colBound = td.getColBound(); j <= colBound; j++) {
cell = currentRow.createCell(j);
this.setCellStyle(currentRow, cell, td);
}
}
if (td.getColSpan() > 0 || td.getRowSpan() > 0) {
sheet.addMergedRegion(new CellRangeAddress(td.getRow(), td.getRowBound(), td.getCol(), td.getColBound()));
}
}
Example 13
| Source File: Util.java From Knowage-Server with GNU Affero General Public License v3.0 | 4 votes |
|
private static void copyCell(Cell oldCell, Cell newCell, List<CellStyle> styleList) {
if (styleList != null) {
if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
newCell.setCellStyle(oldCell.getCellStyle());
} else {
DataFormat newDataFormat = newCell.getSheet().getWorkbook().createDataFormat();
CellStyle newCellStyle = getSameCellStyle(oldCell, newCell, styleList);
if (newCellStyle == null) {
Font oldFont = oldCell.getSheet().getWorkbook().getFontAt(oldCell.getCellStyle().getFontIndex());
Font newFont = newCell.getSheet().getWorkbook().findFont(oldFont.getBoldweight(), oldFont.getColor(), oldFont.getFontHeight(),
oldFont.getFontName(), oldFont.getItalic(), oldFont.getStrikeout(), oldFont.getTypeOffset(), oldFont.getUnderline());
if (newFont == null) {
newFont = newCell.getSheet().getWorkbook().createFont();
newFont.setBoldweight(oldFont.getBoldweight());
newFont.setColor(oldFont.getColor());
newFont.setFontHeight(oldFont.getFontHeight());
newFont.setFontName(oldFont.getFontName());
newFont.setItalic(oldFont.getItalic());
newFont.setStrikeout(oldFont.getStrikeout());
newFont.setTypeOffset(oldFont.getTypeOffset());
newFont.setUnderline(oldFont.getUnderline());
newFont.setCharSet(oldFont.getCharSet());
}
short newFormat = newDataFormat.getFormat(oldCell.getCellStyle().getDataFormatString());
newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
newCellStyle.setFont(newFont);
newCellStyle.setDataFormat(newFormat);
newCellStyle.setAlignment(oldCell.getCellStyle().getAlignment());
newCellStyle.setHidden(oldCell.getCellStyle().getHidden());
newCellStyle.setLocked(oldCell.getCellStyle().getLocked());
newCellStyle.setWrapText(oldCell.getCellStyle().getWrapText());
newCellStyle.setBorderBottom(oldCell.getCellStyle().getBorderBottom());
newCellStyle.setBorderLeft(oldCell.getCellStyle().getBorderLeft());
newCellStyle.setBorderRight(oldCell.getCellStyle().getBorderRight());
newCellStyle.setBorderTop(oldCell.getCellStyle().getBorderTop());
newCellStyle.setBottomBorderColor(oldCell.getCellStyle().getBottomBorderColor());
newCellStyle.setFillBackgroundColor(oldCell.getCellStyle().getFillBackgroundColor());
newCellStyle.setFillForegroundColor(oldCell.getCellStyle().getFillForegroundColor());
newCellStyle.setFillPattern(oldCell.getCellStyle().getFillPattern());
newCellStyle.setIndention(oldCell.getCellStyle().getIndention());
newCellStyle.setLeftBorderColor(oldCell.getCellStyle().getLeftBorderColor());
newCellStyle.setRightBorderColor(oldCell.getCellStyle().getRightBorderColor());
newCellStyle.setRotation(oldCell.getCellStyle().getRotation());
newCellStyle.setTopBorderColor(oldCell.getCellStyle().getTopBorderColor());
newCellStyle.setVerticalAlignment(oldCell.getCellStyle().getVerticalAlignment());
styleList.add(newCellStyle);
}
newCell.setCellStyle(newCellStyle);
}
}
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:
newCell.setCellType(Cell.CELL_TYPE_BLANK);
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
formulaInfoList.add(new FormulaInfo(oldCell.getSheet().getSheetName(), oldCell.getRowIndex(), oldCell.getColumnIndex(), oldCell.getCellFormula()));
break;
default:
break;
}
}
Example 14
| Source File: ExcelTestHelper.java From dremio-oss with Apache License 2.0 | 4 votes |
|
private static void generateSheetData(final Sheet sheet, final CellStyle style, short startingRow) {
int currentRow = startingRow;
// Create first row values
Row row1 = sheet.createRow(currentRow++);
row1.createCell(0).setCellValue(1.0);
row1.createCell(1).setCellValue("One");
row1.createCell(2).setCellValue("One");
Cell c13 = row1.createCell(3);
c13.setCellValue(LocaleUtil.getLocaleCalendar(1983, 04/*zero based*/, 18, 4, 0, 0));
c13.setCellStyle(style);
Cell c14 = row1.createCell(4);
c14.setCellFormula("A2+1");
// For formulas we read pre-computed values. Editors set the precomputed value by default. We need to add it here
// explicitly as the library doesn't pre compute the formula value.
c14.setCellValue(2.0d);
row1.createCell(5).setCellValue(true);
row1.createCell(6).setCellFormula("B2*20");
row1.createCell(6).setCellValue("#ERROR");
// Create second row values
Row row2 = sheet.createRow(currentRow++);
row2.createCell(0).setCellValue(2.0);
row2.createCell(1).setCellValue("Two");
row2.createCell(2).setCellValue("Two");
Cell c23 = row2.createCell(3);
c23.setCellValue(LocaleUtil.getLocaleCalendar(2013, 06/*zero based*/, 05, 5, 0, 1));
c23.setCellStyle(style);
Cell c24 = row2.createCell(4);
c24.setCellFormula("A3+1");
c24.setCellValue(3.0d);
row2.createCell(5).setCellValue(false);
row2.createCell(6).setCellFormula("B3*20");
row2.createCell(6).setCellValue("#ERROR");
// Create third row values
Row row3 = sheet.createRow(currentRow++);
row3.createCell(0).setCellValue(3.0);
row3.createCell(1).setCellValue("Three and Three");
row3.createCell(5).setCellValue(false);
// Create fourth row values
Row row4 = sheet.createRow(currentRow++);
row4.createCell(0).setCellValue(4.0);
row4.createCell(1).setCellValue("Four and Four, Five and Five");
// Create fifth row values
Row row5 = sheet.createRow(currentRow++);
row5.createCell(0).setCellValue(5.0);
sheet.addMergedRegion(new CellRangeAddress(startingRow + 2, startingRow + 2, 1, 2));
sheet.addMergedRegion(new CellRangeAddress(startingRow + 2, startingRow + 4, 5, 5));
sheet.addMergedRegion(new CellRangeAddress(startingRow + 3, startingRow + 4, 1, 2));
}
Example 15
| Source File: JavaToExcel.java From hy.common.report with Apache License 2.0 | 4 votes |
|
/**
* 复制单位格（空白行的复制，即只复制格式和固定文字，不填充数据）
*
* @author ZhengWei(HY)
* @createDate 2017-07-03
* @version v1.0
*
* @param i_RTemplate 模板对象
* @param i_TemplateCell 模板中的单元格对象
* @param i_DataWorkbook 数据工作薄
* @param i_DataCell 数据中的单元格对象
* @param io_RSystemValue 系统变量信息
* @param i_Datas 本行对应的数据
* @param io_RValue 小计循环的迭代器
* @return
*/
public final static void copyCellByBlankSpace(RTemplate i_RTemplate ,Cell i_TemplateCell ,RWorkbook i_DataWorkbook ,Cell i_DataCell ,RSystemValue io_RSystemValue)
{
// 复制样式
i_DataCell.setCellStyle(i_DataWorkbook.getCellStyle(i_RTemplate ,i_TemplateCell.getCellStyle().getIndex()));
// 复制评论
copyComment(i_RTemplate ,i_TemplateCell ,i_DataWorkbook ,i_DataCell);
// 复制数据类型
CellType v_CellType = i_TemplateCell.getCellTypeEnum();
// i_DataCell.setCellType(v_CellType); 不能在此统一设置，原因是：下面代码对类型是有浮动的
if ( v_CellType == CellType.NUMERIC )
{
i_DataCell.setCellType(v_CellType);
if ( HSSFDateUtil.isCellDateFormatted(i_TemplateCell) )
{
i_DataCell.setCellValue(i_TemplateCell.getDateCellValue());
}
else
{
i_DataCell.setCellValue(i_TemplateCell.getNumericCellValue());
}
}
else if ( v_CellType == CellType.STRING )
{
RichTextString v_TemplateRichText = i_TemplateCell.getRichStringCellValue();
String v_ValueName = v_TemplateRichText.toString();
if ( i_RTemplate.isExists(v_ValueName) )
{
i_DataCell.setCellType(v_CellType);
i_DataCell.setCellValue("");
}
else
{
i_DataCell.setCellType(v_CellType);
copyRichTextStyle(i_RTemplate ,v_TemplateRichText ,i_DataWorkbook ,i_DataCell);
}
}
else if ( v_CellType == CellType.BOOLEAN )
{
i_DataCell.setCellType(v_CellType);
i_DataCell.setCellValue(i_TemplateCell.getBooleanCellValue());
}
else if ( v_CellType == CellType.FORMULA)
{
i_DataCell.setCellType(v_CellType);
i_DataCell.setCellFormula(ExcelFormula.calcFormulaOffset(i_TemplateCell ,i_DataCell));
}
else
{
// Nothing.
i_DataCell.setCellType(v_CellType);
}
}
Example 16
| Source File: AbstractExtractorTest.java From TomboloDigitalConnector with MIT License | 4 votes |
|
protected Workbook makeWorkbook(String sheetName, List<List<Object>> rowSpecs){
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet(sheetName);
for (List<Object> rowSpec : rowSpecs) {
int rowId = (sheet.getPhysicalNumberOfRows()==0)?0:sheet.getLastRowNum()+1;
Row row = sheet.createRow(rowId);
for (Object cellSpec : rowSpec) {
// Note that sheet.getLastRowNum() and row.getLastCellNum() do not behave alike
int cellId = (row.getPhysicalNumberOfCells()==0)?0:row.getLastCellNum();
if (cellSpec == null) {
row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
continue;
}
switch (cellSpec.getClass().getCanonicalName()) {
case "java.lang.Integer":
row.createCell(cellId).setCellValue((Integer)cellSpec);
break;
case "java.lang.String":
row.createCell(cellId).setCellValue((String)cellSpec);
break;
case "java.lang.Double":
row.createCell(cellId).setCellValue((Double)cellSpec);
break;
case "java.lang.Boolean":
row.createCell(cellId).setCellValue((Boolean)cellSpec);
break;
case "java.util.Date":
row.createCell(cellId).setCellValue((Date)cellSpec);
break;
case "java.util.Calendar":
row.createCell(cellId).setCellValue((Calendar)cellSpec);
break;
case "org.apache.poi.ss.formula.Formula":
row.createCell(cellId).setCellType(Cell.CELL_TYPE_FORMULA);
Cell cell = row.getCell(row.getLastCellNum());
cell.setCellFormula(((Formula)cellSpec).toString());
break;
default:
row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
}
}
}
return workbook;
}
Example 17
| Source File: GenerateDoc.java From danyuan-application with Apache License 2.0 | 4 votes |
|
/**
* @方法名 copySheet
* @功能 复制sheet
* @参数 @param sheetFrom
* @参数 @param sheetTo
* @参数 @return
* @返回 HSSFSheet
* @author Administrator
* @throws
*/
@SuppressWarnings("deprecation")
private static HSSFSheet copySheet(HSSFSheet sheetFrom, HSSFSheet sheetTo) {
// 初期化
CellRangeAddress region = null;
Row rowFrom = null;
Row rowTo = null;
Cell cellFrom = null;
Cell cellTo = null;
// セル結合のコピー
for (int i = 0; i < sheetFrom.getNumMergedRegions(); i++) {
region = sheetFrom.getMergedRegion(i);
if ((region.getFirstColumn() >= sheetFrom.getFirstRowNum()) && (region.getLastRow() <= sheetFrom.getLastRowNum())) {
sheetTo.addMergedRegion(region);
}
}
// セルのコピー
for (int intRow = sheetFrom.getFirstRowNum(); intRow <= sheetFrom.getLastRowNum(); intRow++) {
rowFrom = sheetFrom.getRow(intRow);
rowTo = sheetTo.createRow(intRow);
if (null == rowFrom) {
continue;
}
rowTo.setHeight(rowFrom.getHeight());
for (int intCol = 0; intCol < rowFrom.getLastCellNum(); intCol++) {
// セル幅のコピー
sheetTo.setDefaultColumnStyle(intCol, sheetFrom.getColumnStyle(intCol));
sheetTo.setColumnWidth(intCol, sheetFrom.getColumnWidth(intCol));
cellFrom = rowFrom.getCell(intCol);
cellTo = rowTo.createCell(intCol);
if (null == cellFrom) {
continue;
}
// セルスタイルとタイプのコピー
cellTo.setCellStyle(cellFrom.getCellStyle());
cellTo.setCellType(cellFrom.getCellType());
// タイトル内容のコピー
// 不同数据类型处理
int cellFromType = cellFrom.getCellType();
cellTo.setCellType(cellFromType);
if (cellFromType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cellFrom)) {
cellTo.setCellValue(cellFrom.getDateCellValue());
} else {
cellTo.setCellValue(cellFrom.getNumericCellValue());
}
} else if (cellFromType == HSSFCell.CELL_TYPE_STRING) {
cellTo.setCellValue(cellFrom.getRichStringCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (cellFromType == HSSFCell.CELL_TYPE_BOOLEAN) {
cellTo.setCellValue(cellFrom.getBooleanCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_ERROR) {
cellTo.setCellErrorValue(cellFrom.getErrorCellValue());
} else if (cellFromType == HSSFCell.CELL_TYPE_FORMULA) {
cellTo.setCellFormula(cellFrom.getCellFormula());
} else { // nothing29
}
}
}
// 枠線の設定
sheetTo.setDisplayGridlines(false);
// sheetTo.setDisplayGuts(true);
// sheetTo.setDisplayRowColHeadings(true);
// 剪切
// sheetTo.shiftRows(13, 15, 31, false, false, false);
// Excelのズーム設定
sheetTo.setZoom(85, 100);
// シートを戻る。
return sheetTo;
}
Example 18
| Source File: ExcelUtils.java From onetwo with Apache License 2.0 | 4 votes |
|
public static void copyRow(Sheet worksheet, Row newRow, Row sourceRow) {
Workbook workbook = worksheet.getWorkbook();
for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
Cell oldCell = sourceRow.getCell(i);
Cell newCell = newRow.createCell(i);
if (oldCell == null) {
newCell = null;
continue;
}
CellStyle newCellStyle = workbook.createCellStyle();
newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
newCell.setCellStyle(newCellStyle);
if (oldCell.getCellComment() != null) {
newCell.setCellComment(oldCell.getCellComment());
}
if (oldCell.getHyperlink() != null) {
newCell.setHyperlink(oldCell.getHyperlink());
}
newCell.setCellType(oldCell.getCellType());
switch (oldCell.getCellType()) {
case Cell.CELL_TYPE_BLANK:
newCell.setCellValue(oldCell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN:
newCell.setCellValue(oldCell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_ERROR:
newCell.setCellErrorValue(oldCell.getErrorCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
newCell.setCellFormula(oldCell.getCellFormula());
break;
case Cell.CELL_TYPE_NUMERIC:
newCell.setCellValue(oldCell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
newCell.setCellValue(oldCell.getRichStringCellValue());
break;
}
}
for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
(newRow.getRowNum() +
(cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
)),
cellRangeAddress.getFirstColumn(),
cellRangeAddress.getLastColumn());
worksheet.addMergedRegion(newCellRangeAddress);
}
}
}
Example 19
| Source File: FormulaCell.java From excel-io with MIT License | 4 votes |
|
@Override
public Cell attachTo(final Row row) {
final Cell cell = new EmptyCell(this.position).attachTo(row);
cell.setCellFormula(this.value);
return cell;
}
