public static ExcelSheet ConvertTransactionListToExcelSheet(List <Transaction> dbData)
{
var data = new ExcelSheet();
foreach (var item in dbData)
{
data.AddNewRow(new ExcelTransaction()
{
AccountingDate = item.AccountingDate,
TransactionId = item.TransactionId,
Type = item.Type,
Account = item.Account,
AccountName = item.AccountName,
PartnerAccount = item.PartnerAccount,
PartnerName = item.PartnerName,
Sum = (double)item.Sum.Value,
Currency = item.Currency.Name,
Message = item.Message
});
}
return(data);
}
void ReadExcel(string path, ExcelSheet <T> excelSheet)
{
ISheet sheet;
using (var stream = new FileStream(path, FileMode.Open))
{
stream.Position = 0;
if (Path.GetExtension(path) == ".xls")
{
HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
else
{
XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format
sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook
}
if (excelSheet.IsHeaderEmpty())
{
IRow headerRow = sheet.GetRow(0);
this.AddHeaderColumns(excelSheet, headerRow);
}
int i = this.GetStartingIndexValue(sheet);
while ((IsReadFromTheBeginning && i <= sheet.LastRowNum) || // from the beginning
(!IsReadFromTheBeginning && i >= (sheet.FirstRowNum + 1))) // from end
{
IRow row = sheet.GetRow(i);
if (row == null ||
row.Cells.All(d => d.CellType == CellType.Blank))
{
i = this.GetNextIteration(i);
continue;
}
T tr = new T();
if (row.GetCell(0) != null)
{
tr.AccountingDate = row.GetCell(0).DateCellValue;
}
if (row.GetCell(1) != null)
{
tr.TransactionId = row.GetCell(1).ToString().Trim();
}
if (row.GetCell(2) != null)
{
tr.Type = row.GetCell(2).ToString().Trim();
}
if (row.GetCell(3) != null)
{
tr.Account = row.GetCell(3).ToString().Trim();
}
if (row.GetCell(4) != null)
{
tr.AccountName = row.GetCell(4).ToString().Trim();
}
if (row.GetCell(5) != null)
{
tr.PartnerAccount = row.GetCell(5).ToString().Trim();
}
if (row.GetCell(6) != null)
{
tr.PartnerName = row.GetCell(6).ToString().Trim();
}
if (row.GetCell(7) != null)
{
tr.Sum = double.Parse(row.GetCell(7).ToString());
}
if (row.GetCell(8) != null)
{
tr.Currency = row.GetCell(8).ToString().Trim();
}
if (row.GetCell(9) != null)
{
tr.Message = row.GetCell(9).ToString().Trim();
}
if (tr is ExcelTransactionExtended)
{
ExcelTransactionExtended cast = tr as ExcelTransactionExtended;
if (row.GetCell(10) != null)
{
cast.IsOmitted = row.GetCell(10).ToString().Trim() == "1";
}
if (row.GetCell(11) != null)
{
cast.GroupId = row.GetCell(11).ToString().Trim();
}
if (row.GetCell(12) != null)
{
cast.TagNames = this.GetIntList(row.GetCell(12).ToString().Trim());
}
if (row.GetCell(13) != null)
{
cast.TagGroupId = row.GetCell(13).ToString().Trim();
}
}
excelSheet.AddNewRow(tr);
i = this.GetNextIteration(i);
}
}
}