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); }
private DataTable ReadFromXLS(string xlsFile) { Net.SourceForge.Koogra.IWorkbook wb = GetWorkBook(xlsFile); if (wb.Worksheets.Count > 0) { Net.SourceForge.Koogra.IWorksheet ws = wb.Worksheets.GetWorksheetByIndex(0); return(GetDataTable(ws)); } return(null); }
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>()); } }