public void TestReading()
{
HSSFWorkbook workbook = LoadWorkbook("RangePtg.xls");
NPOI.SS.UserModel.Cell cell = workbook.GetSheetAt(0).GetRow(3).GetCell(1);
Assert.AreEqual(10.0, cell.NumericCellValue, 0.0, "Wrong cell value");
Assert.AreEqual("SUM(pineapple:B2)", cell.CellFormula, "Wrong cell formula");
}
public void TestReading()
{
HSSFWorkbook workbook = LoadWorkbook("ErrPtg.xls");
NPOI.SS.UserModel.Cell cell = workbook.GetSheetAt(0).GetRow(3).GetCell(0);
Assert.AreEqual(4.0, cell.NumericCellValue, 0.0, "Wrong cell value");
Assert.AreEqual("ERROR.TYPE(#REF!)", cell.CellFormula, "Wrong cell formula");
}
public override bool Equals(Object obj)
{
NPOI.SS.UserModel.Cell cellb = ((HSSFEvaluationCell)obj)._cell;
return(_cell.RowIndex == cellb.RowIndex &&
_cell.ColumnIndex == cellb.ColumnIndex &&
_cell.CellFormula == cellb.CellFormula &&
_cell.Sheet == cellb.Sheet);
}
public void TestReading()
{
HSSFWorkbook workbook = LoadWorkbook("UnionPtg.xls");
NPOI.SS.UserModel.Cell cell = workbook.GetSheetAt(0).GetRow(4).GetCell((short)2);
Assert.AreEqual(24.0, cell.NumericCellValue, 0.0, "Wrong cell value");
Assert.AreEqual("SUM(A1:B2,B2:C3)", cell.CellFormula, "Wrong cell formula");
}
private void ActivateSheet(NPOI.SS.UserModel.Sheet sheet)
{
DataTable dt = new DataTable(sheet.SheetName);
int maxCx = 0;
int cy = sheet.PhysicalNumberOfRows;
for (int y = 0; y < cy; y++)
{
NPOI.SS.UserModel.Row row = sheet.GetRow(y);
if (row != null)
{
int cx = row.PhysicalNumberOfCells;
maxCx = Math.Max(maxCx, row.FirstCellNum + cx);
}
}
int maxCy = sheet.FirstRowNum + cy;
for (int x = 0; x < maxCx; x++)
{
DataColumn col = dt.Columns.Add("C" + (1 + x), typeof(String));
}
for (int vy = 0; vy < maxCy; vy++)
{
DataRow dr = dt.NewRow();
if (vy >= sheet.FirstRowNum)
{
int y = vy - sheet.FirstRowNum;
NPOI.SS.UserModel.Row row = sheet.GetRow(y);
for (int vx = 0; vx < maxCx; vx++)
{
dr[vx] = "";
if (row != null)
{
if (vx >= row.FirstCellNum)
{
int x = vx - row.FirstCellNum;
NPOI.SS.UserModel.Cell cell = row.GetCell(x);
dr[vx] = (cell != null) ? cell.ToString() : "";
}
}
}
}
dt.Rows.Add(dr);
}
gv.DataSource = dt;
foreach (DataGridViewColumn col in gv.Columns)
{
col.ReadOnly = true;
}
gv.AutoResizeColumns();
gv.AutoResizeRows();
}
private NPOI.SS.UserModel.Cell GetCell(NPOI.SS.UserModel.Sheet sheet, int line, int col)
{
NPOI.SS.UserModel.Row row = GetRow(sheet, line);
NPOI.SS.UserModel.Cell cell = row.GetCell(col);
if (cell == null)
{
cell = row.CreateCell(col);
}
return(cell);
}
public void TestExtraSheetRefs_bug45978()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("ex45978-extraLinkTableSheets.xls");
/*
* ex45978-extraLinkTableSheets.xls is a cut-down version of attachment 22561.
* The original file produces the same error.
*
* This bug was caused by a combination of invalid sheet indexes in the EXTERNSHEET
* record, and eager initialisation of the extern sheet references. Note - the worbook
* has 2 sheets, but the EXTERNSHEET record refers to sheet indexes 0, 1 and 2.
*
* Offset 0x3954 (14676)
* recordid = 0x17, size = 32
* [EXTERNSHEET]
* numOfRefs = 5
* refrec #0: extBook=0 firstSheet=0 lastSheet=0
* refrec #1: extBook=1 firstSheet=2 lastSheet=2
* refrec #2: extBook=2 firstSheet=1 lastSheet=1
* refrec #3: extBook=0 firstSheet=-1 lastSheet=-1
* refrec #4: extBook=0 firstSheet=1 lastSheet=1
* [/EXTERNSHEET]
*
* As it turns out, the formula in question doesn't even use externSheetIndex #1 - it
* uses #4, which resolves to sheetIndex 1 -> 'Data'.
*
* It is not Clear exactly what externSheetIndex #4 would refer to. Excel seems to
* display such a formula as "''!$A2", but then complains of broken link errors.
*/
NPOI.SS.UserModel.Cell cell = wb.GetSheetAt(0).GetRow(1).GetCell(1);
String cellFormula;
try
{
cellFormula = cell.CellFormula;
}
catch (IndexOutOfRangeException e)
{
if (e.Message.Equals("Index: 2, Size: 2"))
{
throw new AssertFailedException("Identified bug 45798");
}
throw e;
}
Assert.AreEqual("Data!$A2", cellFormula);
}
public void SetUp()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet("new sheet");
NPOI.SS.UserModel.Row row1 = sheet.CreateRow(0);
this.cell11 = row1.CreateCell(0);
this.cell12 = row1.CreateCell(1);
this.cell13 = row1.CreateCell(2);
this.cell14 = row1.CreateCell(3);
this.cell15 = row1.CreateCell(4);
this.cell16 = row1.CreateCell(5);
this.evaluator = new HSSFFormulaEvaluator(sheet, wb);
//this.evaluator.SetCurrentRow(row1);
}
public HSSFEvaluationCell(NPOI.SS.UserModel.Cell cell, EvaluationSheet evalSheet)
{
_cell = cell;
_evalSheet = evalSheet;
}
public HSSFEvaluationCell(NPOI.SS.UserModel.Cell cell)
{
_cell = cell;
_evalSheet = new HSSFEvaluationSheet((HSSFSheet)cell.Sheet);
}
public HSSFEvaluationCell(NPOI.SS.UserModel.Cell cell, EvaluationSheet evalSheet)
{
_cell = cell;
_evalSheet = evalSheet;
}
public void CreateFile(Model.Campeonatos.Campeonato campeonato, Model.Boloes.Bolao bolao)
{
if (System.IO.File.Exists(_fileName))
{
System.IO.File.Delete(_fileName);
}
//FileStream stream = new FileStream(_fileName, FileMode.Create, FileAccess.Write);
//NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
NPOI.SS.UserModel.Workbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.Sheet sheetJogos = wb.CreateSheet("Jogos-Grupo");
Business.Campeonatos.Support.Campeonato camp = new Business.Campeonatos.Support.Campeonato(_currentLogin, campeonato);
IList <Framework.DataServices.Model.EntityBaseData> jogos = camp.LoadJogos(0, null, null, DateTime.MinValue, DateTime.MinValue, null);
IList <Framework.DataServices.Model.EntityBaseData> grupos = camp.LoadGrupos();
int count = 0;
int line = 0;
foreach (Model.Campeonatos.Grupo grupo in grupos)
{
if (!string.IsNullOrEmpty(grupo.Nome.Trim()))
{
NPOI.SS.UserModel.Row row = sheetJogos.CreateRow(count++);
NPOI.SS.UserModel.Cell grupoCell = row.CreateCell(0);
grupoCell.SetCellValue("Grupo " + grupo.Nome);
line = 0;
row = sheetJogos.CreateRow(count++);
row.CreateCell(line++).SetCellValue("Jogo");
row.CreateCell(line++).SetCellValue("Data/Hora");
row.CreateCell(line++).SetCellValue("Local");
row.CreateCell(line++).SetCellValue("Time");
row.CreateCell(line++).SetCellValue("Gols");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("x");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("Gols");
row.CreateCell(line++).SetCellValue("Time");
row.CreateCell(line++).SetCellValue("");
foreach (Model.Campeonatos.Jogo jogo in jogos)
{
if (string.Compare(jogo.Grupo.Nome, grupo.Nome, true) == 0)
{
line = 0;
row = sheetJogos.CreateRow(count++);
row.CreateCell(line++).SetCellValue(jogo.JogoLabel);
row.CreateCell(line++).SetCellValue(jogo.DataJogo.ToString("dd/MM/yy HH:mm"));
row.CreateCell(line++).SetCellValue(jogo.Estadio.ToString());
row.CreateCell(line++).SetCellValue(jogo.Time1.Nome);
if (jogo.PartidaValida)
{
row.CreateCell(line++).SetCellValue(jogo.GolsTime1);
}
else
{
row.CreateCell(line++).SetCellValue("");
}
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("x");
row.CreateCell(line++).SetCellValue("");
row.CreateCell(line++).SetCellValue("");
if (jogo.PartidaValida)
{
row.CreateCell(line++).SetCellValue(jogo.GolsTime2);
}
else
{
row.CreateCell(line++).SetCellValue("");
}
row.CreateCell(line++).SetCellValue(jogo.Time2.Nome);
row.CreateCell(line++).SetCellValue("");
}
}
sheetJogos.CreateRow(count++);
}
}
sheetJogos.CreateRow(count++);
CreateFase(sheetJogos, "Oitavas de Final", ref count, jogos);
sheetJogos.CreateRow(count++);
CreateFase(sheetJogos, "Quartas de Final", ref count, jogos);
sheetJogos.CreateRow(count++);
CreateFase(sheetJogos, "Semi Finais", ref count, jogos);
sheetJogos.CreateRow(count++);
CreateFase(sheetJogos, "Final", ref count, jogos);
Business.Boloes.Support.Bolao bolaoBO = new Business.Boloes.Support.Bolao(_currentLogin, bolao.Nome);
IList <Framework.DataServices.Model.EntityBaseData> users = bolaoBO.LoadMembros();
foreach (Framework.Security.Model.UserData user in users)
{
CreateUser(wb, user.UserName, bolaoBO);
}
wb.Write(new FileStream(_fileName, FileMode.Create));
}
public void SetProperty(NPOI.SS.UserModel.Row row, int column)
{
NPOI.SS.UserModel.Cell cell = HSSFCellUtil.GetCell(row, column);
HSSFCellUtil.SetCellStyleProperty(cell, _workbook, _propertyName, _propertyValue);
}
private void SetValue(NPOI.SS.UserModel.Sheet sheet, int line, int col, int value)
{
NPOI.SS.UserModel.Cell cell = GetCell(sheet, line, col);
cell.SetCellValue(value);
}
public HSSFEvaluationCell(NPOI.SS.UserModel.Cell cell)
{
_cell = cell;
_evalSheet = new HSSFEvaluationSheet((HSSFSheet)cell.Sheet);
}
public static EvaluationCell WrapCell(NPOI.SS.UserModel.Cell cell)
{
return(new HSSFEvaluationCell(cell));
}
private void CreateFase(NPOI.SS.UserModel.Sheet sheet, string fase, ref int count, IList <Framework.DataServices.Model.EntityBaseData> list)
{
NPOI.SS.UserModel.Row rowJogo = sheet.CreateRow(count++);
NPOI.SS.UserModel.Cell grupoCellJogo = rowJogo.CreateCell(0);
grupoCellJogo.SetCellValue(fase);
int line = 0;
rowJogo = sheet.CreateRow(count++);
rowJogo.CreateCell(line++).SetCellValue("Jogo");
rowJogo.CreateCell(line++).SetCellValue("Data/Hora");
rowJogo.CreateCell(line++).SetCellValue("Local");
rowJogo.CreateCell(line++).SetCellValue("Time");
rowJogo.CreateCell(line++).SetCellValue("Gols");
rowJogo.CreateCell(line++).SetCellValue("Penal");
rowJogo.CreateCell(line++).SetCellValue("");
rowJogo.CreateCell(line++).SetCellValue("x");
rowJogo.CreateCell(line++).SetCellValue("");
rowJogo.CreateCell(line++).SetCellValue("Penal");
rowJogo.CreateCell(line++).SetCellValue("Gols");
rowJogo.CreateCell(line++).SetCellValue("Time");
rowJogo.CreateCell(line++).SetCellValue("");
foreach (Model.Campeonatos.Jogo jogo in list)
{
if (string.Compare(jogo.Fase.Nome, fase, true) == 0)
{
line = 0;
rowJogo = sheet.CreateRow(count++);
rowJogo.CreateCell(line++).SetCellValue(jogo.JogoLabel);
rowJogo.CreateCell(line++).SetCellValue(jogo.DataJogo.ToString("dd/MM/yy HH:mm"));
rowJogo.CreateCell(line++).SetCellValue(jogo.Estadio.ToString());
rowJogo.CreateCell(line++).SetCellValue(jogo.Time1.Nome);
if (jogo.PartidaValida)
{
rowJogo.CreateCell(line++).SetCellValue(jogo.GolsTime1);
}
else
{
rowJogo.CreateCell(line++).SetCellValue("");
}
if (jogo.PartidaValida && jogo.GolsTime1 == jogo.GolsTime2)
{
rowJogo.CreateCell(line++).SetCellValue(jogo.PenaltisTime1);
}
else
{
rowJogo.CreateCell(line++).SetCellValue("");
}
rowJogo.CreateCell(line++).SetCellValue("");
rowJogo.CreateCell(line++).SetCellValue("x");
rowJogo.CreateCell(line++).SetCellValue("");
if (jogo.PartidaValida && jogo.GolsTime1 == jogo.GolsTime2)
{
rowJogo.CreateCell(line++).SetCellValue(jogo.PenaltisTime2);
}
else
{
rowJogo.CreateCell(line++).SetCellValue("");
}
if (jogo.PartidaValida)
{
rowJogo.CreateCell(line++).SetCellValue(jogo.GolsTime2);
}
else
{
rowJogo.CreateCell(line++).SetCellValue("");
}
rowJogo.CreateCell(line++).SetCellValue(jogo.Time2.Nome);
rowJogo.CreateCell(line++).SetCellValue("");
NPOI.SS.UserModel.Row rowFase = sheet.CreateRow(count++);
rowFase.CreateCell(3).SetCellValue(jogo.DescricaoTime1);
rowFase.CreateCell(11).SetCellValue(jogo.DescricaoTime2);
}
}
}