public void TestApplyLabelCellForDateFormatAndNegativeFormat()
{
Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US");
// Create a workbook, IRow and ICell to test with
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
ICell cell1 = row.CreateCell(1);
CellFormat cf = CellFormat.GetInstance("dd/mm/yyyy;(0)");
Label label0 = new Label();
Label label1 = new Label();
cell0.SetCellValue(10);
CellFormatResult result0 = cf.Apply(label0, cell0);
Assert.AreEqual("10/01/1900", result0.Text);
Assert.AreEqual("10/01/1900", label0.Text);
cell1.SetCellValue(-1);
CellFormatResult result1 = cf.Apply(label1, cell1);
Assert.AreEqual("(1)", result1.Text);
Assert.AreEqual("(1)", label1.Text);
}
private String tryColor(String desc, String cname, CellValue getter,
Object value, String expectedText, Color expectedColor)
{
if (cname != null)
{
desc = "[" + cname + "]" + desc;
}
Color origColor = labelForeColor;
CellFormatPart format = new CellFormatPart(desc);
CellFormatResult result = format.Apply(value);
if (!result.Applies)
{
// If this doesn't Apply, no color change is expected
expectedColor = origColor;
}
String actualText = result.Text;
Color actualColor = labelForeColor;
getter.Equivalent(expectedText, actualText, format);
Assert.AreEqual(
expectedColor, actualColor, cname == null ? "no color" : "color " + cname);
return(actualText);
}
public void TestNegativeFormatHasPosAndNegParts()
{
CellFormat fmt = CellFormat.GetInstance("0.00;-0.00");
CellFormatResult result = fmt.Apply(-12.345);
Assert.AreEqual("-12.35", result.Text);
}
public void TestZeroFormatHasOnePart()
{
CellFormat fmt = CellFormat.GetInstance("0.00");
CellFormatResult result = fmt.Apply(0.0);
Assert.AreEqual("0.00", result.Text);
}
public void TestPositiveFormatHasOnePart()
{
CellFormat fmt = CellFormat.GetInstance("0.00");
CellFormatResult result = fmt.Apply(12.345);
Assert.AreEqual("12.35", result.Text);
}
public void TestApplyCellForDateFormat()
{
Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US");
// Create a workbook, IRow and ICell to test with
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
ICell cell1 = row.CreateCell(1);
CellFormat cf = CellFormat.GetInstance("dd/mm/yyyy");
cell0.SetCellValue(10);
CellFormatResult result0 = cf.Apply(cell0);
Assert.AreEqual("10/01/1900", result0.Text);
cell1.SetCellValue(-1);
CellFormatResult result1 = cf.Apply(cell1);
Assert.AreEqual(_255_POUND_SIGNS, result1.Text);
wb.Close();
}
public void TestZeroFormatHasOnePart()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
CellFormat fmt = CellFormat.GetInstance("0.00");
CellFormatResult result = fmt.Apply(0.0);
Assert.AreEqual("0.00", result.Text);
}
public void TestPositiveFormatHasPosAndNegParts()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
CellFormat fmt = CellFormat.GetInstance("0.00;-0.00");
CellFormatResult result = fmt.Apply(12.345);
Assert.AreEqual("12.35", result.Text);
}
public void TestZeroFormatHasOnePart()
{
Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US");
CellFormat fmt = CellFormat.GetInstance("0.00");
CellFormatResult result = fmt.Apply(0.0);
Assert.AreEqual("0.00", result.Text);
}
public void TestNegativeFormatHasPosAndNegParts()
{
Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US");
CellFormat fmt = CellFormat.GetInstance("0.00;-0.00");
CellFormatResult result = fmt.Apply(-12.345);
Assert.AreEqual("-12.35", result.Text);
}
public void TestApplyLabelCellForAtFormat()
{
Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US");
// Create a workbook, IRow and ICell to test with
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
ICell cell1 = row.CreateCell(1);
ICell cell2 = row.CreateCell(2);
ICell cell3 = row.CreateCell(3);
ICell cell4 = row.CreateCell(4);
CellFormat cf = CellFormat.GetInstance("@");
Label label0 = new Label();
Label label1 = new Label();
Label label2 = new Label();
Label label3 = new Label();
Label label4 = new Label();
// case Cell.CELL_TYPE_BLANK
CellFormatResult result0 = cf.Apply(label0, cell0);
Assert.AreEqual(string.Empty, result0.Text);
Assert.AreEqual(string.Empty, label0.Text);
// case Cell.CELL_TYPE_BOOLEAN
cell1.SetCellValue(true);
CellFormatResult result1 = cf.Apply(label1, cell1);
Assert.AreEqual("TRUE", result1.Text);
Assert.AreEqual("TRUE", label1.Text);
// case Cell.CELL_TYPE_NUMERIC
cell2.SetCellValue(1.23);
CellFormatResult result2 = cf.Apply(label2, cell2);
Assert.AreEqual("1.23", result2.Text);
Assert.AreEqual("1.23", label2.Text);
cell3.SetCellValue(123.0);
CellFormatResult result3 = cf.Apply(label3, cell3);
Assert.AreEqual("123", result3.Text);
Assert.AreEqual("123", label3.Text);
// case Cell.CELL_TYPE_STRING
cell4.SetCellValue("abc");
CellFormatResult result4 = cf.Apply(label4, cell4);
Assert.AreEqual("abc", result4.Text);
Assert.AreEqual("abc", label4.Text);
}
public void TestNullTextRaisesException()
{
try
{
bool applies = true;
String text = null;
Color textColor = Color.Black;
CellFormatResult result = new CellFormatResult(applies, text, textColor);
Assert.Fail("Cannot Initialize CellFormatResult with null text parameter");
}
catch (ArgumentException e)
{
//Expected
}
}
public void TestApplyCellForTimeFormat()
{
// Create a workbook, IRow and ICell to test with
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
CellFormat cf = CellFormat.GetInstance("hh:mm");
cell.SetCellValue(DateUtil.ConvertTime("03:04:05"));
CellFormatResult result = cf.Apply(cell);
Assert.AreEqual("03:04", result.Text);
}
public void TestApplyCellForGeneralFormat()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
// Create a workbook, IRow and ICell to test with
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
ICell cell1 = row.CreateCell(1);
ICell cell2 = row.CreateCell(2);
ICell cell3 = row.CreateCell(3);
ICell cell4 = row.CreateCell(4);
CellFormat cf = CellFormat.GetInstance("General");
// case Cell.CELL_TYPE_BLANK
CellFormatResult result0 = cf.Apply(cell0);
Assert.AreEqual(string.Empty, result0.Text);
// case Cell.CELL_TYPE_BOOLEAN
cell1.SetCellValue(true);
CellFormatResult result1 = cf.Apply(cell1);
Assert.AreEqual("TRUE", result1.Text);
// case Cell.CELL_TYPE_NUMERIC
cell2.SetCellValue(1.23);
CellFormatResult result2 = cf.Apply(cell2);
Assert.AreEqual("1.23", result2.Text);
cell3.SetCellValue(123.0);
CellFormatResult result3 = cf.Apply(cell3);
Assert.AreEqual("123", result3.Text);
// case Cell.CELL_TYPE_STRING
cell4.SetCellValue("abc");
CellFormatResult result4 = cf.Apply(cell4);
Assert.AreEqual("abc", result4.Text);
}
public void SetUpClass()
{
System.Threading.Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo("en-US");
// some pre-checks to hunt for a problem in the Maven build
// these checks ensure that the correct locale is set, so a Assert.Failure here
// usually indicates an invalid locale during test-execution
Assert.IsFalse(DateUtil.IsADateFormat(-1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"));
//Locale ul = LocaleUtil.getUserLocale();
//assertTrue(Locale.ROOT.equals(ul) || Locale.getDefault().equals(ul));
String textValue = NumberToTextConverter.ToText(1234.56);
Assert.AreEqual(-1, textValue.IndexOf('E'));
Object cellValueO = 1234.56d;
/*CellFormat cellFormat = new CellFormat("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-");
* CellFormatResult result = cellFormat.apply(cellValueO);
* Assert.AreEqual(" 1,234.56 ", result.text);*/
CellFormat cfmt = CellFormat.GetInstance("_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-");
CellFormatResult result = cfmt.Apply(cellValueO);
Assert.AreEqual(" 1,234.56 ", result.Text,
"This Assert.Failure can indicate that the wrong locale is used during test-execution, ensure you run with english/US via -Duser.language=en -Duser.country=US");
}
internal CellFormatResultWrapper(CellFormatResult result, bool emulateCSV)
{
this.emulateCSV = emulateCSV;
this.result = result;
}
private string GetSheetContent(ISheet sheet, bool needTitle)
{
StringBuilder sbRet = new StringBuilder();
if (needTitle)
{
sbRet.Append(GetColumnHeads());
}
sbRet.Append(string.Format("<tbody>\n"));
IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (IRow)rows.Current;
sbRet.Append(string.Format(" <tr>\n"));
if (needTitle)
{
sbRet.Append(string.Format(" <td class={0}>{1}</td>\n", ROW_HEAD_CLASS, row.RowNum + 1));
}
StringBuilder sbTemp = new StringBuilder();
int mergeCnt = 0;
ICell preCell = null;
ICell cell = null;
for (int i = firstColumn; i < endColumn; i++)
{
String content = " ";
String attrs = "";
ICellStyle style = null;
bool isMerge = false;
if (i >= row.FirstCellNum && i < row.LastCellNum)
{
cell = row.GetCell(i);
if (cell != null)
{
isMerge = cell.IsMergedCell;
style = cell.CellStyle;
attrs = tagStyle(cell, style);
//Set the value that is rendered for the cell
//also applies the format
string format = style.GetDataFormatString();
if (format.IsPresent())
{
MyCellFormat cf = MyCellFormat.GetInstance(format);
CellFormatResult result = cf.Apply(cell);
content = result.Text; //never null
}
if (string.IsNullOrEmpty(content))
{
content = " ";
}
}
}
if (isMerge == true && content == " ")
{
/*
* 因为 NPOI 返回的 cell 没有 mergeCnt 属性，只有一个 IsMergedCell 属性
* 如果有5个单元格，后面四个单元格合并成一个大单元格
* 它返回的其实还是5个单元格，IsMergedCell 分别是： false,true,true,true,true
* 上头这种情况还算好，我们好歹还能猜到后面四个单元格是合并单元格
*
* 但是如果第一个单独，后面四个每两个合并呢？
* TMD返回的还是5个单元格，IsMergedCell 仍然是： false,true,true,true,true
* 所以这里是有问题的，我没法知道后面的四个单元格是四个合并成一个呢，还是两个两个的分别合并
* 这个是没办法的，除非从NPOI的源代码里头去解决这个问题，介于上班呢，要求的是出结果，所以公司是
* 不太会允许我去干这种投入产出比较差的事情的，所以这个问题我采用了一个成本比较低的办法来绕开
*
* 办法就是我们在定义模板的时候，可以通过为每一个合并单元格添加内容来避免。
* 比如说 cell1（内容）, cell2,cell3（内容）, cell4,cell5（内容）
* 这样的话我就能知道 cell1 IsMergedCell = false 是一个独立的单元格
* cell2, cell3, cell4, cell5 的 IsMergedCell 虽然都是 true， 但是因为 cell4 这个位置有内容了，
* 那我就晓得 cell2 和 cell3 是合并的， cell4 和 cell5 也是合并的。
*
* 当然这里还会有个小小的问题，如果 cell4, cell5 里头是一个会被替换掉的内容，也即 $[字段] 这样的东西
* 如果实际的内容为 null 那么 cell4, cell5 合并单元格的内容也就是 null 了，这又回到了之前的问题了，
* 所以此处要求定义模板的时候 $[内容] 后面加一个空格，这样在生成 html 的时候，其实是不影响打印效果的。
* 也即 “$[] ”注意双引号里头的 “]”后头有个空格
*/
if (mergeCnt == 1 && preCell != null && preCell.IsMergedCell == false)
{
sbTemp.Append(string.Format(" <td class={0} {1}{3}>{2}</td>\n", styleName(style), attrs, content, (isMerge) ? " colspan=\"1\"" : ""));
}
else
{
mergeCnt++;
}
}
else
{
sbTemp.Replace("colspan=\"1\"", string.Format("colspan=\"{0}\"", mergeCnt));
mergeCnt = 1;
sbTemp.Append(string.Format(" <td class={0} {1}{3}>{2}</td>\n", styleName(style), attrs, content, (isMerge) ? " colspan=\"1\"" : ""));
}
preCell = cell;
}
sbRet.Append(sbTemp.Replace("colspan=\"1\"", string.Format("colspan=\"{0}\"", mergeCnt)).ToString());
sbRet.Append(string.Format(" </tr>\n"));
}
sbRet.Append(string.Format("</tbody>\n"));
return(sbRet.ToString());
}
