private static void SetCellValue(NPOI.SS.UserModel.Cell cell, NPOI.SS.UserModel.CellValue cv)
{
NPOI.SS.UserModel.CellType cellType = cv.CellType;
switch (cellType)
{
case NPOI.SS.UserModel.CellType.BOOLEAN:
cell.SetCellValue(cv.BooleanValue);
break;
case NPOI.SS.UserModel.CellType.ERROR:
cell.CellErrorValue = cv.ErrorValue;
break;
case NPOI.SS.UserModel.CellType.NUMERIC:
cell.SetCellValue(cv.NumberValue);
break;
case NPOI.SS.UserModel.CellType.STRING:
cell.SetCellValue(new HSSFRichTextString(cv.StringValue));
break;
//case NPOI.SS.UserModel.CellType.BLANK:
//// never happens - blanks eventually get translated to zero
//case NPOI.SS.UserModel.CellType.FORMULA:
//// this will never happen, we have already evaluated the formula
default:
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
}
/// <summary>
/// Use this to create new cells within the row and return it.
/// The cell that is returned is a CELL_TYPE_BLANK. The type can be changed
/// either through calling setCellValue or setCellType.
/// </summary>
/// <param name="columnIndex">the column number this cell represents</param>
/// <param name="type">a high level representation of the created cell.</param>
/// <returns></returns>
public NPOI.SS.UserModel.Cell CreateCell(int columnIndex, NPOI.SS.UserModel.CellType type)
{
Cell cell = new HSSFCell(book, sheet, RowNum, (short)columnIndex, type);
AddCell(cell);
sheet.Sheet.AddValueRecord(RowNum, ((HSSFCell)cell).CellValueRecord);
return(cell);
}
private CellValue(NPOI.SS.UserModel.CellType cellType, double numberValue, bool booleanValue,
String textValue, int errorCode)
{
_cellType = cellType;
_numberValue = numberValue;
_booleanValue = booleanValue;
_textValue = textValue;
_errorCode = errorCode;
}
public void TestReadSheetWithRK()
{
HSSFWorkbook h = OpenSample("rk.xls");
NPOI.SS.UserModel.ISheet s = h.GetSheetAt(0);
ICell c = s.GetRow(0).GetCell(0);
NPOI.SS.UserModel.CellType a = c.CellType;
Assert.AreEqual(a, NPOI.SS.UserModel.CellType.Numeric);
}
private static void SetCellType(NPOI.SS.UserModel.Cell cell, NPOI.SS.UserModel.CellValue cv)
{
NPOI.SS.UserModel.CellType cellType = cv.CellType;
switch (cellType)
{
case NPOI.SS.UserModel.CellType.BOOLEAN:
case NPOI.SS.UserModel.CellType.ERROR:
case NPOI.SS.UserModel.CellType.NUMERIC:
case NPOI.SS.UserModel.CellType.STRING:
cell.SetCellType(cellType);
return;
case NPOI.SS.UserModel.CellType.BLANK:
// never happens - blanks eventually get translated to zero
break;
case NPOI.SS.UserModel.CellType.FORMULA:
// this will never happen, we have already evaluated the formula
break;
}
throw new InvalidOperationException("Unexpected cell value type (" + cellType + ")");
}
private CellValue(NPOI.SS.UserModel.CellType cellType, double numberValue, bool booleanValue,
String textValue, int errorCode)
{
_cellType = cellType;
_numberValue = numberValue;
_booleanValue = booleanValue;
_textValue = textValue;
_errorCode = errorCode;
}
/// <summary>
/// Загрузка из XSLX.
/// </summary>
/// <param name="FileName">Имя файла с полным путем к XLSX файлу</param>
private void LoadXLSX(string FileName)
{
BeginOfLoad();
DateTime dateTime1 = DateTime.Now;
NPOI.XSSF.UserModel.XSSFWorkbook book = null; //Книга.
NPOI.SS.UserModel.ISheet sheet = null; //Лист.
try
{
FileStream fs = new FileStream(FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs);
if (fs != null)
{
fs.Close();
}
}
catch (Exception ex)
{
sys.SM("Ошибка открытия файла шаблона: " + ex.Message);
return;
}
//Устанавливаем текущий лист.
string errorMes = "";
if (!sys.SetCurrentSheet(book, 1, true, out sheet, out errorMes))
{
return;
}
string[] arrLine = null; //= new string[sheet.LastCellNum + 1];
int columnsCount = 0;
//Код выполняется в отдельном потоке
var task1 = Task.Factory.StartNew(() =>
{
//запускаем цикл по строкам
for (int row = sheet.FirstRowNum; row <= sheet.LastRowNum; row++)
{
//получаем строку
var currentRow = sheet.GetRow(row);
if (currentRow == null)
{
continue; //null когда строка содержит только пустые ячейки
}
//запускаем цикл по столбцам
if (arrLine == null)
{
columnsCount = currentRow.LastCellNum;
arrLine = new string[currentRow.LastCellNum];
}
for (int col = 0; col < columnsCount; col++)
{
if (loadStop)
{
return;
}
//получаем значение ячейки
string value = "";
var currentCell = currentRow.GetCell(col);
if (currentCell == null)
{
arrLine[col] = value;
continue;
}
NPOI.SS.UserModel.CellType ctype = currentCell.CellType;
if (ctype == NPOI.SS.UserModel.CellType.String)
{
value = currentCell.StringCellValue;
}
else if (ctype == NPOI.SS.UserModel.CellType.Boolean)
{
value = currentCell.BooleanCellValue.ToString();
}
else if (ctype == NPOI.SS.UserModel.CellType.Formula)
{
value = currentCell.CellFormula;
}
else if (ctype == NPOI.SS.UserModel.CellType.Numeric)
{
value = currentCell.NumericCellValue.ToString();
}
else if (ctype == NPOI.SS.UserModel.CellType.Error)
{
value = currentCell.ErrorCellValue.ToString();
}
arrLine[col] = value;
}
if (row == sheet.FirstRowNum)
{
for (int i = 0; i < arrLine.Length; i++)
{
if (arrLine[i] == "")
{
arrLine[i] = "Column";
}
}
Arr.SetUniqValue(arrLine); //Меняем название колонок, если они дублируются.
//Потому чтонельзя добавлять в dt колонки с одинаковым именем.
for (int i = 0; i < arrLine.Length; i++)
{
dt.Columns.Add(arrLine[i], typeof(string));
}
}
else
{
dt.Rows.Add(arrLine);
}
}
});
//После завершения обновляем GUI компаненты.
var task2 = task1.ContinueWith((previous) =>
{
EndOfLoad(dateTime1);
}, TaskScheduler.FromCurrentSynchronizationContext());
return;
}