static void Main(string[] args)
{
// If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile ef = ExcelFile.Load("SimpleTemplate.xlsx");
// Select the first worksheet from the file.
ExcelWorksheet ws = ef.Worksheets[0];
// Create DataTable from an Excel worksheet.
DataTable dataTable = ws.CreateDataTable(new CreateDataTableOptions()
{
ColumnHeaders = true,
StartRow = 1,
NumberOfColumns = 5,
NumberOfRows = ws.Rows.Count - 1,
Resolution = ColumnTypeResolution.AutoPreferStringCurrentCulture
});
// Write DataTable content
StringBuilder sb = new StringBuilder();
sb.AppendLine("DataTable content:");
foreach (DataRow row in dataTable.Rows)
{
sb.AppendFormat("{0}\t{1}\t{2}\t{3}\t{4}", row[0], row[1], row[2], row[3], row[4]);
sb.AppendLine();
}
Console.WriteLine(sb.ToString());
}
public static DataTable ExcelToDS(string Path)
{
// string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
// OleDbConnection conn = new OleDbConnection(strConn);
ExcelFile excelFile = new ExcelFile();
excelFile.LoadXls(Path);
ExcelWorksheet sheet = excelFile.Worksheets[0];
DataTable specificRangeData = sheet.CreateDataTable(ColumnTypeResolution.Auto);
return(specificRangeData);
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [" + tableName + "$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
conn.Close();
return(ds.Tables[0]);
}
public void ImportExcelWorksheet(ExcelWorksheet worksheet)
{
// Extract the data from the worksheet to newly created DataTable starting at
// second row and first column (first row is for attributes) until the first empty row appears.
var dataTable = worksheet.CreateDataTable(new CreateDataTableOptions()
{
StartRow = 0,
StartColumn = 0,
ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
});
// TO DO
}
public Task <DataTable> ParseExcel(string filePath)
{
return(Task.Run(() =>
{
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
ExcelFile excelBook = ExcelFile.Load(filePath);
ExcelWorksheet excelSheet = excelBook.Worksheets[0];
var idColumn = excelSheet.Columns[0];
var nameColumn = excelSheet.Columns[1];
string test = (string)excelSheet.Cells[1, 1].Value;
for (int i = 0; i < 100; i++)
{
Danger danger = new Danger();
danger.Id = (string)excelSheet.Cells[i, 0].Value.ToString();
danger.Name = (string)excelSheet.Cells[i, 1].Value;
danger.Discription = (string)excelSheet.Cells[i, 2].Value;
danger.SourceOfThrear = (string)excelSheet.Cells[i, 3].Value;
danger.SubjectOfThreat = (string)excelSheet.Cells[i, 4].Value;
string ConfidenceProblem = (string)excelSheet.Cells[i, 5].Value.ToString();
if (ConfidenceProblem == "1")
{
danger.ConfidenceProblem = true;
}
else
{
danger.ConfidenceProblem = false;
}
string FullnessProblem = (string)excelSheet.Cells[i, 6].Value.ToString();
if (FullnessProblem == "1")
{
danger.FullnesProblem = true;
}
else
{
danger.FullnesProblem = false;
}
string AccessProblem = (string)excelSheet.Cells[i, 7].Value.ToString();
if (AccessProblem == "1")
{
danger.AccessRroblem = true;
}
else
{
danger.AccessRroblem = false;
}
//dataController.dangersList.Add(danger);
}
CreateDataTableOptions options = new CreateDataTableOptions();
return excelSheet.CreateDataTable(options);
}));
}
public static List <Employee> LoadFromXls(String fName, String sName, int members, int teamCount, int freeAgents) // 10 teams, 10 free agents
{
var workbook = ExcelFile.Load(fName);
ExcelWorksheet sheet = null;
List <Employee> contents = new List <Employee>();
foreach (ExcelWorksheet ew in workbook.Worksheets) // Select appropriate worksheet
{
if (ew.Name == sName)
{
sheet = ew;
}
}
if (sheet != null)
{
var dataTable = sheet.CreateDataTable(new CreateDataTableOptions()
{
StartRow = 3,
StartColumn = 2,
NumberOfRows = 65,
NumberOfColumns = 19,
ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
});
Trace.WriteLine(dataTable.Rows.Count);
int c = 0;
int t = 0;
String teamName = "";
foreach (DataRow row in dataTable.Rows)
{
if (c == 0)
{
t++;
if (t <= teamCount)
{
teamName = "Team " + t;
}
else
{
teamName = "Free Agents";
}
}
if (c < members || t > teamCount)
{
String name = (String)row[0];
String role = (String)row[1];
List <int> pbw = new List <int>();
int points = 0;
for (int i = 2; i < row.ItemArray.Length; i++)
{
if (i != 14)
{
pbw.Add((int)row[i]);
points += (int)row[i];
}
}
Employee e = new Employee(name, pbw, role, teamName);
contents.Add(e);
}
c++;
if (c >= members)
{
if (t <= teamCount)
{
c = 0;
}
}
}
}
else
{
throw new IOException("Sample data file formatted incorrectly; needs a sheet labelled \"Main\".");
}
return(contents);
}