public void TestCachedTypeChange()
{
HSSFSheet sheet = (HSSFSheet) new HSSFWorkbook().CreateSheet("Sheet1");
HSSFCell cell = (HSSFCell)sheet.CreateRow(0).CreateCell(0);
cell.CellFormula = ("A1");
cell.SetCellValue("abc");
ConfirmStringRecord(sheet, true);
cell.SetCellValue(123);
NPOI.HSSF.Record.Record[] recs = RecordInspector.GetRecords(sheet, 0);
if (recs.Length == 28 && recs[23] is StringRecord)
{
throw new AssertionException("Identified bug - leftover StringRecord");
}
ConfirmStringRecord(sheet, false);
// string to error code
cell.SetCellValue("abc");
ConfirmStringRecord(sheet, true);
cell.SetCellErrorValue((byte)ErrorConstants.ERROR_REF);
ConfirmStringRecord(sheet, false);
// string to boolean
cell.SetCellValue("abc");
ConfirmStringRecord(sheet, true);
cell.SetCellValue(false);
ConfirmStringRecord(sheet, false);
}
public new void TestUpdateCachedFormulaResultFromErrorToNumber_bug46479()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.CreateSheet("Sheet1") as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cellA1 = row.CreateCell(0) as HSSFCell;
HSSFCell cellB1 = row.CreateCell(1) as HSSFCell;
cellB1.CellFormula = "A1+1";
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
cellA1.SetCellErrorValue(FormulaError.NAME.Code);
fe.EvaluateFormulaCell(cellB1);
cellA1.SetCellValue(2.5);
fe.NotifyUpdateCell(cellA1);
try
{
fe.EvaluateInCell(cellB1);
}
catch (InvalidOperationException e)
{
if (e.Message.Equals("Cannot get a numeric value from a error formula cell"))
{
Assert.Fail("Identified bug 46479a");
}
}
Assert.AreEqual(3.5, cellB1.NumericCellValue, 0.0);
wb.Close();
}
private static void CopyCell(XSSFCell oldCell, HSSFCell newCell, Dictionary <int, XSSFCellStyle> styleMap, HSSFWorkbook retVal)
{
if (styleMap != null)
{
int stHashCode = oldCell.CellStyle.Index;
XSSFCellStyle sourceCellStyle = null;
if (styleMap.TryGetValue(stHashCode, out sourceCellStyle))
{
}
HSSFCellStyle destnCellStyle = (HSSFCellStyle)newCell.CellStyle;
if (sourceCellStyle == null)
{
sourceCellStyle = (XSSFCellStyle)oldCell.Sheet.Workbook.CreateCellStyle();
}
// destnCellStyle.CloneStyleFrom(oldCell.CellStyle);
if (!styleMap.Any(p => p.Key == stHashCode))
{
styleMap.Add(stHashCode, sourceCellStyle);
}
destnCellStyle.VerticalAlignment = VerticalAlignment.Top;
newCell.CellStyle = (HSSFCellStyle)destnCellStyle;
}
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
default:
break;
}
}
protected static void CopyRow(int blockRowCount, int tagRowBlockIndex, HSSFRow srcRow, HSSFRow tagRow)
{
tagRow.Height = srcRow.Height;
//tagRow.RowStyle = srcRow.RowStyle;
for (int i = 0; i < srcRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
HSSFCell srcCell = (HSSFCell)srcRow.GetCell(i);
HSSFCell tagCell = (HSSFCell)tagRow.CreateCell(i);
// If the old cell is null jump to next cell
if (srcCell == null)
{
continue;
}
// Copy style from old cell and apply to new cell
tagCell.CellStyle = srcCell.CellStyle;
// If there is a cell comment, copy
if (tagCell.CellComment != null)
{
tagCell.CellComment = srcCell.CellComment;
}
// If there is a cell hyperlink, copy
if (srcCell.Hyperlink != null)
{
tagCell.Hyperlink = srcCell.Hyperlink;
}
// Set the cell data type
tagCell.SetCellType(srcCell.CellType);
// Set the cell data value
switch (srcCell.CellType)
{
case CellType.BLANK:
tagCell.SetCellValue(srcCell.StringCellValue);
break;
case CellType.BOOLEAN:
tagCell.SetCellValue(srcCell.BooleanCellValue);
break;
case CellType.ERROR:
tagCell.SetCellErrorValue(srcCell.ErrorCellValue);
break;
case CellType.FORMULA:
int sheetIndex = srcRow.Sheet.Workbook.GetSheetIndex(srcRow.Sheet);
Ptg[] ptgs = HSSFFormulaParser.Parse(srcCell.CellFormula, srcRow.Sheet.Workbook as HSSFWorkbook, FormulaType.CELL, sheetIndex);
foreach (Ptg ptg in ptgs)
{
if (ptg is RefPtgBase)
{
RefPtgBase refptg = ptg as RefPtgBase;
if (refptg.Row >= srcRow.RowNum - tagRowBlockIndex && refptg.Row <= srcRow.RowNum - tagRowBlockIndex + blockRowCount)
{
refptg.Row += tagRow.RowNum - srcRow.RowNum;
}
}
else if (ptg is AreaPtgBase)
{
AreaPtgBase aptg = ptg as AreaPtgBase;
if (aptg.FirstRow >= srcRow.RowNum - tagRowBlockIndex && aptg.FirstRow <= srcRow.RowNum - tagRowBlockIndex + blockRowCount)
{
aptg.FirstRow += tagRow.RowNum - srcRow.RowNum;
aptg.LastRow += tagRow.RowNum - srcRow.RowNum;
}
}
}
tagCell.CellFormula = HSSFFormulaParser.ToFormulaString(srcRow.Sheet.Workbook as HSSFWorkbook, ptgs);
break;
case CellType.NUMERIC:
tagCell.SetCellValue(srcCell.NumericCellValue);
break;
case CellType.STRING:
tagCell.SetCellValue(srcCell.RichStringCellValue);
break;
case CellType.Unknown:
tagCell.SetCellValue(srcCell.StringCellValue);
break;
}
}
}
//reference link : http://www.zachhunter.com/2010/05/npoi-copy-row-helper/
/// <summary>複製指定工作表的指定資料列到指定工作表的指定資料列</summary>
/// <param name="sourceSheet">來源工作表</param>
/// <param name="targetSheet">目標工作表</param>
/// <param name="sourceIndex">來源資料列索引</param>
/// <param name="targetIndex">目標資料列索引</param>
private void CopyRow(HSSFSheet sourceSheet
, HSSFSheet targetSheet
, int sourceIndex
, int targetIndex)
{
// Get the source / new row
HSSFRow newRow;
HSSFRow sourceRow;
newRow = targetSheet.GetRow(targetIndex) as HSSFRow;
sourceRow = sourceSheet.GetRow(sourceIndex) as HSSFRow;
if (newRow == null)
{
newRow = targetSheet.CreateRow(targetIndex) as HSSFRow;
}
if (sourceRow == null)
{
sourceRow = sourceSheet.CreateRow(sourceIndex) as HSSFRow;
}
newRow.HeightInPoints = sourceRow.HeightInPoints;
// Loop through source columns to add to new row
//複製儲存格內容
for (int i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
HSSFCell oldCell = sourceRow.GetCell(i) as HSSFCell;
HSSFCell newCell = newRow.CreateCell(i) as HSSFCell;
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
newCell.CellStyle = oldCell.CellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
//進行合併儲存格設定搬移作業
CellRangeAddress cellRangeAddress;
CellRangeAddress newCellRangeAddress;
for (int i = 0; i < sourceSheet.NumMergedRegions; i++)
{
cellRangeAddress = sourceSheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
newCellRangeAddress = new CellRangeAddress(newRow.RowNum
, (cellRangeAddress.LastRow - cellRangeAddress.FirstRow)
+ newRow.RowNum
, cellRangeAddress.FirstColumn
, cellRangeAddress.LastColumn
);
targetSheet.AddMergedRegion(newCellRangeAddress);
}
}
this.MoveCursorPosition();
}
/// <summary>
/// HSSFRow Copy Command
///
/// Description: Inserts a existing row into a new row, will automatically push down
/// any existing rows. Copy is done cell by cell and supports, and the
/// command tries to copy all properties available (style, merged cells, values, etc...)
/// </summary>
/// <param name="workbook">Workbook containing the worksheet that will be changed</param>
/// <param name="worksheet">WorkSheet containing rows to be copied</param>
/// <param name="sourceRowNumber">Source Row Number</param>
/// <param name="destinationRowNumber">Destination Row Number</param>
public static void CopyRow(HSSFSheet worksheet, int sourceRowNumber, int destinationRowNumber)
{
// Get the source / new row
HSSFRow newRow = (HSSFRow)worksheet.GetRow(destinationRowNumber);
HSSFRow sourceRow = (HSSFRow)worksheet.GetRow(sourceRowNumber);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
worksheet.ShiftRows(destinationRowNumber, worksheet.LastRowNum, 1);
}
else
{
newRow = (HSSFRow)worksheet.CreateRow(destinationRowNumber);
}
// Loop through source columns to add to new row
for (int i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
HSSFCell oldCell = (HSSFCell)sourceRow.GetCell(i);
HSSFCell newCell = (HSSFCell)newRow.CreateCell(i);
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
//HSSFCellStyle newCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
//newCellStyle.CloneStyleFrom(oldCell.CellStyle); ;
newCell.CellStyle = oldCell.CellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data type
newCell.SetCellType(oldCell.CellType);
//Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < worksheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.LastRow -
cellRangeAddress.FirstRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
}
}
