private DataTable GetDataTable(Net.SourceForge.Koogra.IWorksheet ws)
{
DataTable table = new DataTable();
int rowIndex = 0;
int columnIndex = 0;
uint firstRow = ws.FirstRow;
for (uint r = firstRow; r <= ws.LastRow; ++r)
{
table.ExpandRow(rowIndex);
Net.SourceForge.Koogra.IRow row = ws.Rows.GetRow(r);
if (row != null)
{
for (uint colCount = ws.FirstCol; colCount <= ws.LastCol; ++colCount)
{
table.ExpandColumn(columnIndex);
if (row.GetCell(colCount) != null && row.GetCell(colCount).Value != null)
{
table.Rows[rowIndex][columnIndex] = row.GetCell(colCount).Value.ToString();
}
else
{
table.Rows[rowIndex][columnIndex] = "";
}
columnIndex++;
}
columnIndex = 0;
}
rowIndex++;
}
return(table);
}
private DataTable GetData(ExcelContext context)
{
DataTable dt = new DataTable();
try
{
string ext = Path.GetExtension(context.ExcelFile);
Net.SourceForge.Koogra.IWorkbook wb = null;
if (ext.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
{
wb = (Net.SourceForge.Koogra.WorkbookFactory.GetExcel2007Reader(context.ExcelFile));
}
else if (ext.Equals(".xls", StringComparison.OrdinalIgnoreCase))
{
wb = (Net.SourceForge.Koogra.WorkbookFactory.GetExcelBIFFReader(context.ExcelFile));
//读链接内容
Net.SourceForge.Koogra.Excel.Workbook workbook = wb as Net.SourceForge.Koogra.Excel.Workbook;
context.Links = workbook.HyperLinks.AsEnumerable().Select(c => c.Link).ToList();
}
Net.SourceForge.Koogra.IWorksheet ws = wb.Worksheets.GetWorksheetByIndex(0);
int rowIndex = 0;
int columnIndex = 0;
uint firstRow = ws.FirstRow;
if (!context.IncludeHeader)
{
firstRow += 1;
}
for (uint r = firstRow; r <= ws.LastRow; ++r)
{
dt.ExpandRow(rowIndex);
Net.SourceForge.Koogra.IRow row = ws.Rows.GetRow(r);
if (row != null)
{
for (uint colCount = ws.FirstCol; colCount <= ws.LastCol; ++colCount)
{
dt.ExpandColumn(columnIndex);
if (row.GetCell(colCount) != null && row.GetCell(colCount).Value != null)
{
dt.Rows[rowIndex][columnIndex] = row.GetCell(colCount).Value.ToString();
}
else
{
dt.Rows[rowIndex][columnIndex] = "";
}
columnIndex++;
}
columnIndex = 0;
}
rowIndex++;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(dt);
}
public KeywordDictionary Leitor(String filePath)
{
Workbook genericWB = WorkbookFactory.GetExcel2007Reader(filePath);
//pega a folha na pos [0]
Worksheet genericWS = genericWB.Worksheets.GetWorksheetByIndex(0);
StringBuilder SbXls = new StringBuilder();
//for da primeira lina +1 até ultima linha
for (uint r = genericWS.FirstRow + 1; r <= genericWS.LastRow; ++r)
{
Row row = genericWS.Rows.GetRow(r);
String keyword = row.GetCell(0).GetFormattedValue();
String obj = row.GetCell(1).GetFormattedValue();
String action = row.GetCell(2).GetFormattedValue();
dic.AddKeyword(obj, action, keyword);
}
return(dic);
}
public void ReadExcelContent(HttpPostedFileBase File, List <IPTable> data)
{
uint rowNum = 0;
uint colNum = 0;
try
{
Net.SourceForge.Koogra.IWorkbook wb = null;
string fileExt = Path.GetExtension(File.FileName);
if (string.IsNullOrEmpty(fileExt))
{
throw new Exception("File extension not found");
}
if (fileExt.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
{
wb = Net.SourceForge.Koogra.WorkbookFactory.GetExcel2007Reader(File.InputStream);
}
else if (fileExt.Equals(".xls", StringComparison.OrdinalIgnoreCase))
{
wb = Net.SourceForge.Koogra.WorkbookFactory.GetExcelBIFFReader(File.InputStream);
}
for (int i = 0; i < wb.Worksheets.Count; i++)
{
Net.SourceForge.Koogra.IWorksheet ws = wb.Worksheets.GetWorksheetByIndex(i);
IPTable table = data.FirstOrDefault(f => f.Name == ws.Name);
if (table == default(IPTable))
{
continue;
}
for (uint r = ws.FirstRow; r <= ws.LastRow; ++r)
{
rowNum = r;
Net.SourceForge.Koogra.IRow row = ws.Rows.GetRow(r);
if (row != null)
{
for (uint colCount = ws.FirstCol; colCount <= ws.LastCol; ++colCount)
{
colNum = colCount;
string cellData = string.Empty;
if (row.GetCell(colCount) != null && row.GetCell(colCount).Value != null)
{
cellData = row.GetCell(colCount).Value.ToString();
}
if (r == ws.FirstRow)
{
table.AddHeader(cellData);
}
else
{
table.Flow = cellData;
}
}
}
}
}
}
catch (Exception ex)
{
Exception exception = ex;
exception.Source = string.Format("Error occured on row {0} col {1}", rowNum, colNum);
throw ex;
}
}
public static List <Teacher> ReadTeachersFromExcel(string filePath)
{
var data = new StringBuilder();
try
{
Net.SourceForge.Koogra.IWorkbook wb = null;
string fileExt = Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileExt))
{
throw new Exception("File extension not found");
}
if (fileExt.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
{
wb = Net.SourceForge.Koogra.WorkbookFactory.GetExcel2007Reader(filePath);
}
else if (fileExt.Equals(".xls", StringComparison.OrdinalIgnoreCase))
{
wb = Net.SourceForge.Koogra.WorkbookFactory.GetExcelBIFFReader(filePath);
}
Net.SourceForge.Koogra.IWorksheet ws = wb.Worksheets.GetWorksheetByIndex(0);
List <Teacher> list = new List <Teacher>();
for (uint r = ws.FirstRow + 1; r <= ws.LastRow; ++r)
{
Net.SourceForge.Koogra.IRow row = ws.Rows.GetRow(r);
if (row != null)
{
Teacher teacher = new Teacher();
teacher.User = new User();
teacher.Unvan = (int)SG_DAL.Enums.EnumUnvan.Ogretmen;
teacher.GenelBasvuru = true;
teacher.User.Rol = (int)SG_DAL.Enums.EnumRol.ogretmen;
teacher.GorevSayisi = 0;
for (uint colCount = ws.FirstCol; colCount <= ws.LastCol; ++colCount)
{
string cellData = string.Empty;
if (row.GetCell(colCount) != null && row.GetCell(colCount).Value != null)
{
cellData = row.GetCell(colCount).Value.ToString();
switch (colCount)
{
case 0: teacher.User.Ad = cellData;
break;
case 1: teacher.User.Soyad = cellData;
break;
case 2: teacher.User.TCKimlik = Convert.ToInt64(cellData);
teacher.User.Sifre = cellData;
break;
case 3: teacher.User.Email = cellData;
break;
case 4: teacher.User.Tel = cellData;
break;
case 5: teacher.Kidem = cellData;
break;
case 6:
using (SGContext db = new SGContext())
{
var schoolRepo = new Repository <School>(db);
int mebkodu = Convert.ToInt32(cellData);
School sch = schoolRepo.First(d => d.MebKodu == mebkodu);
teacher.SchoolId = sch.SchoolId;
}
break;
default:
break;
}
}
}
list.Add(teacher);
}
}
return(list);
}
catch (Exception)
{
return(new List <Teacher>());
}
}
internal static List <School> ReadSchoolsFromExcel(string filePath)
{
var data = new StringBuilder();
try
{
Net.SourceForge.Koogra.IWorkbook wb = null;
string fileExt = Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileExt))
{
throw new Exception("File extension not found");
}
if (fileExt.Equals(".xlsx", StringComparison.OrdinalIgnoreCase))
{
wb = Net.SourceForge.Koogra.WorkbookFactory.GetExcel2007Reader(filePath);
}
else if (fileExt.Equals(".xls", StringComparison.OrdinalIgnoreCase))
{
wb = Net.SourceForge.Koogra.WorkbookFactory.GetExcelBIFFReader(filePath);
}
Net.SourceForge.Koogra.IWorksheet ws = wb.Worksheets.GetWorksheetByIndex(0);
List <School> list = new List <School>();
for (uint r = ws.FirstRow + 1; r <= ws.LastRow; ++r)
{
Net.SourceForge.Koogra.IRow row = ws.Rows.GetRow(r);
if (row != null)
{
School school = new School();
for (uint colCount = ws.FirstCol; colCount <= ws.LastCol; ++colCount)
{
string cellData = string.Empty;
if (row.GetCell(colCount) != null && row.GetCell(colCount).Value != null)
{
cellData = row.GetCell(colCount).Value.ToString();
switch (colCount)
{
case 0: school.Ad = cellData;
break;
case 1: school.MebKodu = Convert.ToInt32(cellData);
break;
default:
break;
}
}
}
if (school.MebKodu != 0)
{
list.Add(school);
}
}
}
return(list);
}
catch (Exception)
{
return(new List <School>());
}
}
