public string Read()
{
if (!File.Exists(Path))
{
throw new FileNotFoundException();
}
FileInfo _file = new FileInfo(Path);
FileStream stream = File.Open(_file.FullName, FileMode.Open, FileAccess.Read);
StringBuilder data = new StringBuilder();
try
{
IExcelDataReader excelReader;
if (_file.Extension == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (_file.Extension == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
throw new Exception("Unknown format!");
}
DataSet result = excelReader.AsDataSet();
excelReader.Close();
foreach (DataTable sheet in result.Tables)
{
int cols = Math.Max(Math.Min(
Convert.ToInt32(ConfigurationManager.AppSettings["Columns"].ToString()),
sheet.Columns.Count), 0);
int rows = Math.Max(Math.Min(
Convert.ToInt32(ConfigurationManager.AppSettings["Rows"].ToString()),
sheet.Rows.Count), 0);
for (int j = 0; j < sheet.Columns.Count; j++)
{
for (int i = 0; i < sheet.Rows.Count; i++)
{
data.Append(sheet.Rows[i].ItemArray[j].ToString());
data.Append("|");
}
}
}
return(data.ToString());
}
catch (IOException ex)
{
_logger.WriteMessage(ex.Message, LevelOfDetalization.Error);
_printer.Print($"Exception occured {ex.Message}");
}
catch (Exception)
{
throw;
}
finally
{
stream.Close();
}
return(data.ToString());
}
protected override IExcelDataReader OpenReader(Stream stream, ExcelReaderConfiguration configuration = null)
{
return(ExcelReaderFactory.CreateOpenXmlReader(stream, configuration));
}
public static void LoadToDatabase()
{
DataSet excelDataSet = null;
IExcelDataReader reader = null;
using (FileStream file = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(file);
reader.IsFirstRowAsColumnNames = true;
excelDataSet = reader.AsDataSet();
}
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
foreach (DataTable table in excelDataSet.Tables)
{
switch (table.TableName)
{
case "PlayerRoster":
foreach (DataRow row in table.Rows)
{
InsertPlayerRoster(con, row);
}
break;
case "CoachRoster":
foreach (DataRow row in table.Rows)
{
InsertCoachRoster(con, row);
}
break;
case "PlayerBio":
foreach (DataRow row in table.Rows)
{
InsertPlayerBio(con, row);
}
break;
case "PitchingStats":
foreach (DataRow row in table.Rows)
{
InsertPitchingStats(con, row);
}
break;
case "FieldingStats":
foreach (DataRow row in table.Rows)
{
InsertFieldingStats(con, row);
}
break;
case "PlayerStatsSummary":
foreach (DataRow row in table.Rows)
{
InsertPlayerStatsSummary(con, row);
}
break;
case "BattingStats":
foreach (DataRow row in table.Rows)
{
InsertBattingStats(con, row);
}
break;
default:
break;
}
}
}
}
protected virtual IExcelDataReader CreateReader(FileStream inputStream) => ExcelReaderFactory.CreateBinaryReader(inputStream);
// Add some programmatically-generated objects to the data store
// Can write one method, or many methods - your decision
// The important idea is that you check for existing data first
// Call this method from a controller action/method
public bool LoadData()
{
// User name
var user = HttpContext.Current.User.Identity.Name;
// Monitor the progress
bool done = false;
// ############################################################
// RoleClaims
if (ds.RoleClaims.Count() == 0)
{
//Add Roles
ds.RoleClaims.Add(new RoleClaim {
Name = "Manager"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "General Manager"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "Coach"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "Player"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "Referee"
});
ds.RoleClaims.Add(new RoleClaim {
Name = "League Rep"
});
ds.SaveChanges();
done = true;
}
if (ds.Positions.Count() == 0)
{
ds.Positions.Add(new Position {
PositionName = "Left Wing"
});
ds.Positions.Add(new Position {
PositionName = "Right Wing"
});
ds.Positions.Add(new Position {
PositionName = "Centre"
});
ds.Positions.Add(new Position {
PositionName = "Defenseman"
});
ds.Positions.Add(new Position {
PositionName = "Goalie"
});
}
if (ds.Teams.Count() == 0)
{
//Add teams
//Path to the XLSX file
var path = HttpContext.Current.Server.MapPath("~/App_Data/TeamData.xlsx");
//Load workbook into a System.Data.Dataset "sourceData"
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet sourceData = reader.AsDataSet();
reader.Close();
//Worksheet name
string worksheetName;
//Get workseet by its name
worksheetName = "Teams";
var worksheet = sourceData.Tables[worksheetName];
//Conver it to a collection of team add
List <TeamAdd> items = worksheet.DataTableToList <TeamAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
// Double fixDate = double.Parse(item.StartDate);
ds.Teams.Add(Mapper.Map <Team>(item));
}
//save changes
ds.SaveChanges();
//if got this far make done = true
done = true;
}
if (ds.Coaches.Count() == 0)
{
//Add teams
//Path to the XLSX file
var path = HttpContext.Current.Server.MapPath("~/App_Data/CoachData.xlsx");
//Load workbook into a System.Data.Dataset "sourceData"
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet sourceData = reader.AsDataSet();
reader.Close();
//Worksheet name
string worksheetName;
//Get workseet by its name
worksheetName = "Coach";
var worksheet = sourceData.Tables[worksheetName];
//Conver it to a collection of team add
List <CoachAdd> items = worksheet.DataTableToList <CoachAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedCoach = ds.Coaches.Add(Mapper.Map <Coach>(item));
var addToTeam = ds.Teams.SingleOrDefault(t => t.TeamName == item.TeamName);
addToTeam.Coaches.Add(addedCoach);
//save changes in loop to ensure Coaches collection is filled properly
ds.SaveChanges();
}
//if got this far make done = true
done = true;
}
if (ds.Players.Count() == 0)
{
//Add Players
//Path to the XLSX file
var path = HttpContext.Current.Server.MapPath("~/App_Data/PlayerData.xlsx");
//Load workbook into a System.Data.Dataset "sourceData"
var stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
DataSet sourceData = reader.AsDataSet();
reader.Close();
//Worksheet name
string worksheetName;
//Get workseet by its name
worksheetName = "CAN";
var worksheet = sourceData.Tables[worksheetName];
//Conver it to a collection of team add
List <PlayerAdd> items = worksheet.DataTableToList <PlayerAdd>();
var montreal = ds.Teams.SingleOrDefault(t => t.TeamName == "Montreal Canadiens");
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
montreal.Players.Add(addedPlayer);
addedPlayer.TeamName = montreal.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "TOR";
worksheet = sourceData.Tables[worksheetName];
var toronto = ds.Teams.SingleOrDefault(t => t.TeamName == "Toronto Maple Leafs");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
toronto.Players.Add(addedPlayer);
addedPlayer.TeamName = toronto.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "BOS";
worksheet = sourceData.Tables[worksheetName];
var boston = ds.Teams.SingleOrDefault(t => t.TeamName == "Boston Bruins");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
boston.Players.Add(addedPlayer);
addedPlayer.TeamName = boston.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "NYR";
worksheet = sourceData.Tables[worksheetName];
var newyork = ds.Teams.SingleOrDefault(t => t.TeamName == "New York Rangers");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
newyork.Players.Add(addedPlayer);
addedPlayer.TeamName = newyork.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "RED";
worksheet = sourceData.Tables[worksheetName];
var red = ds.Teams.SingleOrDefault(t => t.TeamName == "Detroit Red Wings");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
red.Players.Add(addedPlayer);
addedPlayer.TeamName = red.TeamName;
}
//save changes
ds.SaveChanges();
// Load the next worksheet...
// ==========================
//Get workseet by its name
worksheetName = "CHG";
worksheet = sourceData.Tables[worksheetName];
var chicago = ds.Teams.SingleOrDefault(t => t.TeamName == "Chicago Black Hawks");
//Conver it to a collection of team add
items = worksheet.DataTableToList <PlayerAdd>();
//go thorugh collection and add items
foreach (var item in items)
{
var addedPlayer = ds.Players.Add(Mapper.Map <Player>(item));
chicago.Players.Add(addedPlayer);
addedPlayer.TeamName = chicago.TeamName;
}
//save changes
ds.SaveChanges();
//if got this far make done = true
done = true;
}
return(done);
}
public void ReadExcelFile()
{
Debug.Log("读取数据");
//读取一共有多少列
//读取前三行
/*
* 第一行注释
* 第二行名称
* 第三行类型
*/
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
/*
* Read() 从当前工作表中读取一行。
* NextResult() 将光标前进到下一张纸。
* ResultsCount 返回当前工作簿中的工作表数。
* Name 返回当前工作表的名称。
* CodeName 返回当前工作表的VBA代码名称标识符。
* FieldCount 返回当前工作表中的列数。
* RowCount返回当前工作表中的行数。这包括终端空行，否则这些空行被AsDataSet（）排除。InvalidOperationException与一起使用时会抛出CSV文件AnalyzeInitialCsvRows。
* HeaderFooter返回一个对象，其中包含有关页眉和页脚的信息，null如果没有，则不提供任何信息。
* MergeCells 返回当前工作表中合并单元格范围的数组。
* RowHeight返回当前行的视觉高度（以磅为单位）。如果该行是隐藏的，则可以为0。
* GetColumnWidth()以字符为单位返回列的宽度。如果该列是隐藏的，则可以为0。
* GetFieldType()返回当前行中值的类型。永远的类型之一支持Excel中：double，int，bool，DateTime，TimeSpan，string，或者null如果没有价值。
* IsDBNull() 检查当前行中的值是否为空。
* GetValue()返回当前行的值object，null如果没有，则返回。
* GetDouble()，GetInt32()，GetBoolean()，GetDateTime()，GetString()从目前投行的值返回各自的类型。
* GetNumberFormatString()返回一个字符串，其中包含当前行中某个值的格式代码，或者null如果没有值，则返回该字符串。另请参阅下面的“格式化”部分。
* GetNumberFormatIndex()返回当前行中值的数字格式索引。低于164的索引值是指内置数字格式，否则表示自定义数字格式。
* GetCellStyle() 返回一个对象，该对象包含当前行中单元格的样式信息：缩进，水平对齐，隐藏，锁定。
* 除非类型完全匹配，否则将Get*()抛出类型化方法InvalidCastException。
*/
//ResultsCount 返回当前工作簿中的工作表数
Debug.Log($"返回当前工作簿中的工作表数:{reader.ResultsCount}");
Debug.Log($"返回当前工作表的名称:{reader.Name}");
Debug.Log($"返回当前工作表中的列数:{reader.FieldCount}");
//Debug.Log($"Namespace:{result.Namespace}");
if (result.Tables.Count <= 0)
{
return;
}
DataTable dataTable = result.Tables[0];
if (dataTable.Rows.Count < 3)
{
Debug.LogError("Excel表格少于3行数据，请检查");
return;
}
List <string> fieldNameList = new List <string>();
List <string> typeList = new List <string>();
List <string> descriptionList = new List <string>();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
//第一行名称
fieldNameList.Add(dataTable.Rows[0][i].ToString());
//第二行类型
typeList.Add(dataTable.Rows[1][i].ToString());
//第三行注释
descriptionList.Add(dataTable.Rows[2][i].ToString());
}
Debug.Log($"{descriptionList.Count}---{fieldNameList.Count}---{typeList.Count}");
for (int i = 0; i < descriptionList.Count; i++)
{
Debug.Log($"{typeList[i]} {fieldNameList[i]} // {descriptionList[i]}");
}
//Debug.Log(Application.dataPath);
string templatePath = Application.dataPath + "/" + "Editor" + "/" + "Template" + "/" + "81-C# Script-NewBehaviourScript.cs" + ".txt";
string text = System.IO.File.ReadAllText(templatePath);
Debug.Log($"内容：" + text);
//string path = templatePath;
//using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate))
//{
// using (StreamWriter writer = new StreamWriter(fs, System.Text.Encoding.UTF8))
// {
// writer.Write(content);
// }
//}
}
}
}
//--Project Profile Start Here-------------
public ActionResult SaveMapFile(IEnumerable <HttpPostedFileBase> Mapfiles)
{
if (Session["UserInfo"] != null)
{
user = (UserInfo)Session["UserInfo"];
}
if (Mapfiles != null)
{
foreach (var uploadfile in Mapfiles)
{
var fileName = System.IO.Path.GetFileName(uploadfile.FileName);
var physicalPath = System.IO.Path.Combine(Server.MapPath("~/UploadFiles"), fileName);
if (uploadfile != null && uploadfile.ContentLength > 0)
{
//ExcelDataReader works on binary excel file
Stream stream = uploadfile.InputStream;
IExcelDataReader reader = null;
if (uploadfile.FileName.EndsWith(".xls"))
{
//reads the excel file with .xls extension
uploadfile.SaveAs(physicalPath);
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (uploadfile.FileName.EndsWith(".xlsx"))
{
uploadfile.SaveAs(physicalPath);
//reads excel file with .xlsx extension
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
//Shows error if uploaded file is not Excel file
ModelState.AddModelError("File", "This file format is not supported");
//return View();
}
//treats the first row of excel file as Coluymn Names
// reader.IsFirstRowAsColumnNames = false;
//Adding reader data to DataSet()
DataSet result = reader.AsDataSet();
DataTable datatable = result.Tables[0];
foreach (DataColumn column in datatable.Columns)
{
string cName = datatable.Rows[0][column.ColumnName].ToString();
if (!datatable.Columns.Contains(cName) && cName != "")
{
column.ColumnName = cName;
}
}
DataRow row1 = datatable.Rows[0];
datatable.Rows.Remove(row1);
DataRow dr1 = result.Tables[0].NewRow();
for (int i = 0; i < result.Tables[0].Columns.Count; i++)
{
dr1[result.Tables[0].Columns[i].ColumnName] = result.Tables[0].Rows[0][i];
}
DataTable ExcelData = new DataTable();
ExcelData.Columns.Add("ExcelParameter");
ExcelData.Columns.Add("ParameterData");
foreach (DataColumn dr in datatable.Columns)
{
if (dr.ColumnName.Trim() != "")
{
DataRow newCustomersRow = ExcelData.NewRow();
newCustomersRow["ExcelParameter"] = dr.ColumnName.Trim();
ExcelData.Rows.Add(newCustomersRow);
}
}
int g = 0;
foreach (DataColumn drcol in datatable.Columns)
{
string ParameterData = "";
foreach (DataRow dr in datatable.Rows)
{
ParameterData += Convert.ToString(dr[drcol.ColumnName.Trim()]) + "~";
}
ParameterData = ParameterData.Substring(0, ParameterData.Length - 1);
ExcelData.Rows[g]["ParameterData"] = ParameterData;
g++;
}
List <SqlParameter> parameters1 = new List <SqlParameter>();
parameters1.Add(new SqlParameter()
{
ParameterName = "@CompId",
SqlDbType = SqlDbType.Int,
Value = user.Comp_ID,
Direction = System.Data.ParameterDirection.Input
});
parameters1.Add(new SqlParameter()
{
ParameterName = "@tblExceldata",
SqlDbType = SqlDbType.Structured,
Value = ExcelData,
Direction = System.Data.ParameterDirection.Input
});
DataSet dataSet2 = SqlManager.ExecuteDataSet("SP_EleExcelMappingData", parameters1.ToArray());
}
}
}
return(Json("", "text/plain"));
}
public void GLComparison()
{
string expectedReportPath, actualReportPath;
string[] rowToAdd;
bool isCredit;
// 1. Read the expected, actual files into a dataset
expectedReportPath = @"C:\Users\umars\Desktop\GL-Validation_TestData\FebExpectedResult_RowsMismatch.csv";
actualReportPath = @"C:\Users\umars\Desktop\GL-Validation_TestData\FebActual_RowsMismatch.csv";
FileStream streamExpectedResult = File.Open(expectedReportPath, FileMode.Open, FileAccess.Read);
FileStream streamSctualResult = File.Open(actualReportPath, FileMode.Open, FileAccess.Read);
IExcelDataReader expectedDataReader;
expectedDataReader = ExcelReaderFactory.CreateCsvReader(streamExpectedResult);
IExcelDataReader actualDataReader;
actualDataReader = ExcelReaderFactory.CreateCsvReader(streamSctualResult);
DataSet expectedDataSet = expectedDataReader.AsDataSet();
DataSet actualDataSet = actualDataReader.AsDataSet();
expectedDataReader.Dispose();
actualDataReader.Dispose();
DataTable actualTable = actualDataSet.Tables[0];
DataTable expectedTable = expectedDataSet.Tables[0];
// 2. column headers have been applied to the data set
string[] expectedTableColumnNames = new string[] { "Journal Codes", "Accounts", "Description", "Original Amount Debit", "Original Amount Credit",
"Settlement Amount Debit", "Settlement Amount Credit", "Base Amount Debit", "Base Amount Credit",
"Risk Codes", "Syndicates", "Placement Type", "Original Currency", "Settlement Currency" };
string[] finalTableColumnNames = new string[] { "Journal Codes", "Accounts", "Date", "Period", "Original Currency", "Original Amount",
"Settlement Currency", "Settlement Amount", "Credit/Debit", "Syndicate", "Risk Codes", "Placement Type", "CYP", "Z", "Year", "New Account",
"Base Amount" };
for (int i = 0; i < expectedTableColumnNames.Length; i++)
{
expectedTable.Columns[$"Column{i}"].ColumnName = expectedTableColumnNames[i];
}
for (int i = 0; i < finalTableColumnNames.Length; i++)
{
actualTable.Columns[$"Column{i}"].ColumnName = finalTableColumnNames[i];
}
// 3. Data tables have been tranformed in the same manner
DataTable transformedExpectedTable = CreateDataTable(finalTableColumnNames);
DataTable transformedActualTable = CreateDataTable(finalTableColumnNames);
DataTable aggregatedExpectedTable = null;
for (int i = 0; i < expectedTable.Rows.Count; i++)
{
// 2. Check wether this is a Debit or credit row
isCredit = ValidateCreditRow(expectedTable, i);
// 3. Read the rows of this sorted table and store the row in a string [] to be added into the finalTable. will have to add additional
// Credit/Debit field
rowToAdd = ReadTableRowFromExpected(expectedTable, i, isCredit, finalTableColumnNames.Length);
// 4. create a new finalTable and keep adding the rows to it one by one (string [])...
InsertRowInTable(transformedExpectedTable, rowToAdd);
}
for (int i = 0; i < actualTable.Rows.Count; i++)
{
rowToAdd = ReadTableRowFromActual(actualTable, i, finalTableColumnNames.Length);
InsertRowInTable(transformedActualTable, rowToAdd);
}
// 4. Sorting has been applied to both tables (take input form the user)
DataTable sortedExpectedTable = DataValidation.SortDataTable(transformedExpectedTable, transformedExpectedTable.Columns["Journal Codes"],
transformedExpectedTable.Columns["Accounts"], transformedExpectedTable.Columns["Credit/Debit"]);
DataTable sortedActualTable = DataValidation.SortDataTable(transformedActualTable, transformedActualTable.Columns["Journal Codes"],
transformedActualTable.Columns["Accounts"], transformedActualTable.Columns["Credit/Debit"]);
/* Some thoughts for the aggregation process
* 1 - Aggregation will only happen in the Expected file
* 2 - There should be method that takes the expected table, for the current row tells how many more rows below
* have the same journal code and account. p.s. the row variable should be increased by that number...
* 3 - Need a second function to indicate which one of those rows are credit and which ones debit - put them in a strings[]
* 4 - Need a third method now to aggregate the credit/debit rows if more than 1
*/
// 5. Access whether aggregation is required
bool tableToBeAggregated = isTableToAggregated(sortedExpectedTable);
if (tableToBeAggregated)
{
aggregatedExpectedTable = CreateDataTable(finalTableColumnNames);
string[] row;
int numRowsToAggregate;
for (int currentRow = 0; currentRow < sortedExpectedTable.Rows.Count; currentRow++)
{
// since the table needs to be aggregated, i want to know how many rows do i need to aggregate
numRowsToAggregate = NumberOfRowsToAggregate(sortedExpectedTable, currentRow);
if (numRowsToAggregate == 0)
{
row = AggregateRows(sortedExpectedTable, currentRow, numRowsToAggregate);
InsertRowInTable(aggregatedExpectedTable, row);
}
else
{
row = AggregateRows(sortedExpectedTable, currentRow, numRowsToAggregate);
InsertRowInTable(aggregatedExpectedTable, row);
currentRow = currentRow + numRowsToAggregate;
}
}
}
string printExpected = null;
string printActual = null;
// 6. Compare the final expected table content with that of Actual sorted table..
int numberOfMismatches = 0;
bool currentRowSuccess = true;
if (!tableToBeAggregated)
{
if (sortedExpectedTable.Rows.Count != sortedActualTable.Rows.Count)
{
Console.WriteLine($"ERROR!!! The number of rows differs in the two files.\n " +
$" Expected n* of rows in the expected view is: { sortedExpectedTable.Rows.Count}.\n " +
$" Actual n* of rows in the actual view is: { sortedActualTable.Rows.Count}.\n ");
Console.WriteLine("The final expected view is printed as below. Please sort your Actual result sheet for comparison!!! \n");
for (int i = 0; i < sortedExpectedTable.Rows.Count; i++)
{
for (int j = 0; j < sortedExpectedTable.Columns.Count; j++)
{
printExpected += string.Concat("|", sortedExpectedTable.Rows[i][j]);
}
Console.WriteLine($"Row number: {i+1} \t" + printExpected + "\n");
printExpected = null;
}
Assert.Fail("\n The validation cannot continue due to mismatches in the number of rows");
}
printExpected = null;
for (int i = 0; i < sortedActualTable.Rows.Count; i++)
{
Console.WriteLine($"Current Row is number: {i + 1}\n");
for (int j = 0; j < sortedActualTable.Columns.Count; j++)
{
printExpected += string.Concat("|", sortedExpectedTable.Rows[i][j]);
printActual += string.Concat("|", sortedActualTable.Rows[i][j]);
if (sortedExpectedTable.Rows[i][j].ToString() != sortedActualTable.Rows[i][j].ToString())
{
Console.WriteLine($"There is a mismatch at column: {sortedExpectedTable.Columns[j].ColumnName}\n " +
$"Expected file value: {sortedExpectedTable.Rows[i][j]}\n " +
$"Actual file value: {sortedActualTable.Rows[i][j]}");
numberOfMismatches++;
currentRowSuccess = false;
}
}
if (currentRowSuccess)
{
Console.WriteLine($"SUCCESS!!! Row {i + 1} in both files has successfully been validated!\n");
}
else
{
Console.WriteLine($"ATTENTION!!! Row {i + 1} has {numberOfMismatches} number of mismatches!\n");
}
Console.WriteLine(printExpected);
Console.WriteLine(printActual + "\n\n");
printExpected = null;
printActual = null;
numberOfMismatches = 0;
currentRowSuccess = true;
}
}
else
{
printExpected = null;
if (aggregatedExpectedTable.Rows.Count != sortedActualTable.Rows.Count)
{
Console.WriteLine($"ERROR!!! The number of rows differs in the two files.\n " +
$" Expected n* of rows in the expected view is: { aggregatedExpectedTable.Rows.Count}.\n " +
$" Actual n* of rows in the actual view is: { sortedActualTable.Rows.Count}.\n ");
Console.WriteLine("The final expected view is printed as below. Please sort your Actual result sheet for comparison!!! \n");
for (int i = 0; i < aggregatedExpectedTable.Rows.Count; i++)
{
for (int j = 0; j < aggregatedExpectedTable.Columns.Count; j++)
{
printExpected += string.Concat("|", aggregatedExpectedTable.Rows[i][j]);
}
Console.WriteLine($"Row number: {i + 1} \t" + printExpected + "\n");
printExpected = null;
}
Assert.Fail("\n The validation cannot continue due to mismatches in the number of rows");
}
for (int i = 0; i < sortedActualTable.Rows.Count; i++)
{
Console.WriteLine($"Current Row is number: {i + 1}\n");
for (int j = 0; j < sortedActualTable.Columns.Count; j++)
{
//Console.Write(finalTableColumnNames[j] + "\t");
printExpected += string.Concat("|", aggregatedExpectedTable.Rows[i][j]);
printActual += string.Concat("|", sortedActualTable.Rows[i][j]);
if (aggregatedExpectedTable.Rows[i][j].ToString() != sortedActualTable.Rows[i][j].ToString())
{
Console.WriteLine($"There is a mismatch at column: {aggregatedExpectedTable.Columns[j].ColumnName}\n " +
$"Expected file value: {aggregatedExpectedTable.Rows[i][j]}\n " +
$"Actual file value: {sortedActualTable.Rows[i][j]}");
numberOfMismatches++;
currentRowSuccess = false;
}
}
if (currentRowSuccess)
{
Console.WriteLine($"SUCCESS!!! Row {i + 1} in both files has successfully been validated!\n");
}
else
{
Console.WriteLine($"ATTENTION!!! Row {i + 1} has {numberOfMismatches} number of mismatches!\n");
}
Console.WriteLine(printExpected);
Console.WriteLine(printActual + "\n\n");
printExpected = null;
printActual = null;
numberOfMismatches = 0;
currentRowSuccess = true;
}
}
}
public bool fromXLStoSQL(string filePath)
{
Logger.Debug(filePath);
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = null;
if (filePath.EndsWith("xls"))
{
Logger.Debug("Reading from a binary Excel file ('97-2003 format; *.xls)");
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (filePath.EndsWith("xlsx"))
{
Logger.Debug("Reading from a OpenXml Excel file (2007 format; *.xlsx)");
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
// wrong file extension (can't happen, but...)
stream.Close();
return(false);
}
while (excelReader != null && excelReader.Read())
{
int numColumns = excelReader.FieldCount;
// Logger.Debug("Found " + numColumns + " columns in excel file");
if (numColumns < 2 || numColumns > 3)
{
// file format not supported
excelReader.Close();
stream.Close();
return(false);
}
string [] columns = new string [numColumns];
try
{
for (int i = 0; i < numColumns; i++)
{
columns[i] = excelReader.GetString(i);
// Logger.Debug("Column " + i.ToString() + ": " + columns[i]);
}
}
catch (Exception e)
{
Logger.Fatal("Can't read xls file: " + e.ToString());
excelReader.Close();
stream.Close();
return(false);
}
string nom = null;
string nhc = null;
string referencia = null;
if (numColumns >= 3)
{
referencia = columns[0];
nhc = columns[1];
nom = columns[2];
}
else
{
nhc = columns[0];
nom = columns[1];
}
if (nom != null && nhc != null)
{
StringDictionary sql = new StringDictionary();
setSQLTableDefaults("SCAPersona", ref sql);
sql.Add("Nombre", nom);
sql.Add("Nombre1", nom);
sql.Add("NHC", nhc);
int lastInsertRowId = -1;
if (storeSQL("SCAPersona", sql, ref lastInsertRowId) == false)
{
excelReader.Close();
stream.Close();
return(false);
}
if (numColumns >= 3 && referencia != null && lastInsertRowId != -1)
{
/*
* Si el xls té tres columnes, a part d’emplenar
* la taula SCAPersona tal i com ja ho fa,
* hauries d’emplenar també la taula SCAMuestra
* on la primera columna seria la referencia de la mostra.
* Tots els altres camps de SCAMuestra els hauries de posar
* amb un valor per defecte
*/
sql.Clear();
setSQLTableDefaults("SCAMuestra", ref sql);
sql.Add("Referencia", referencia);
sql.Add("IdPersona", lastInsertRowId.ToString());
if (storeSQL("SCAMuestra", sql) == false)
{
excelReader.Close();
stream.Close();
return(false);
}
}
}
else
{
Logger.Debug("Empty fields or wrong number of them. Going to next row.");
}
}
excelReader.Close();
stream.Close();
return(true);
}
private void ForGen(string inPath, string outPath)
{
Assist.ClearDirFiles(outPath);
DirectoryInfo folder = new DirectoryInfo(inPath);
FileSystemInfo[] files = folder.GetFileSystemInfos();
int length = files.Length;
for (int index = 0; index < length; index++)
{
if (files[index] is DirectoryInfo)
{
//this.ForGen(files[index].FullName);
}
else
{
if (files[index].Name.EndsWith(".xlsx"))
{
//DataSet mResultSet = Assist.ExcelToDataSet(files[index].FullName);
FileStream stream = File.Open(files[index].FullName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet mResultSet = excelReader.AsDataSet();
//判断Excel文件中是否存在数据表
if (mResultSet.Tables.Count < 1)
{
return;
}
//默认读取第一个数据表
DataTable mSheet = mResultSet.Tables[0];
string tableName = mSheet.TableName;
//读取数据表行数和列数
int rowCount = mSheet.Rows.Count;
int colCount = mSheet.Columns.Count;
string csvFileName = tableName + ".txt";
string content = "";
for (int i = contentBeginIndex; i < rowCount; i++) //行
{
for (int j = 0; j < colCount; j++) //列
{
content += mSheet.Rows[i][j].ToString();
if (j != colCount - 1)
{
content += ",";
}
}
if (i != rowCount - 1)
{
content += "\n";
}
}
string savePath = Path.Combine(outPath, csvFileName);
//写入文件
using (FileStream fileStream = new FileStream(savePath, FileMode.Create, FileAccess.Write))
{
using (TextWriter textWriter = new StreamWriter(fileStream, System.Text.Encoding.UTF8))
{
textWriter.Write(content.Trim());
}
}
}
}
}
Assist.Log("Gen OK");
}
public List <StopInput> stops()
{
List <StopInput> stops = new List <StopInput>();
string filePath = @".\Data\stopList.xlsx";
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader;
int counter = 0;
if (Path.GetExtension(filePath).ToUpper() == ".XLS")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
while (excelReader.Read())
{
counter++;
if (counter > 1)
{
StopReason reason;
switch (excelReader.GetString(0))
{
case "Mola":
reason = StopReason.Break;
break;
case "Arıza":
reason = StopReason.Trouble;
break;
case "Setup":
reason = StopReason.Setup;
break;
case "Arge":
reason = StopReason.ArGe;
break;
default:
reason = StopReason.Break;
break;
}
string startDate = excelReader.GetString(1);
string endDate = excelReader.GetString(2);
var stopAdd = new StopInput()
{
StopReason = reason,
StartDate = DateTime.Parse(startDate),
EndDate = DateTime.Parse(endDate)
};
stops.Add(stopAdd);
}
}
excelReader.Close();
return(stops);
}
public IEnumerable <string> Read(Stream stream)
{
var groupLoader = new CommissionGroupLoader();
var sheetGroups = groupLoader.LoadForSheet(_sheet, stream);
var commissionTypes = new List <string>();
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var sheetNumber = 0;
do
{
//Increment the sheet number
sheetNumber++;
if (_sheet.Position != sheetNumber)
{
continue;
}
var rowNumber = 0;
var header = new HeaderLocator(_sheet.Config.HeaderIdentifier);
while (reader.Read())
{
rowNumber++;
if (!header.Found)
{
header.Check(reader);
continue;
}
var groupValues = new List <GroupValue>();
if (sheetGroups != null)
{
groupValues = sheetGroups.RowGroups.Single(r => r.RowNumber == rowNumber).GroupValues;
}
//Ignore row if any of the primary field values are empty
var requiredFields = _sheet.Config.Fields.Where(f => Fields.PrimaryFieldNames().Any(p => p == f.Name));
var anyMissingRequiredFields = requiredFields.Any(field =>
{
var fieldValue = Utils.GetValue(reader, ExcelUtils.ColumnToIndex(field.Column));
return(string.IsNullOrWhiteSpace(fieldValue));
});
if (anyMissingRequiredFields)
{
continue;
}
var value = CommissionImportReader.GetCommissionTypeValue(reader, _sheet.Config, groupValues);
commissionTypes.Add(value);
}
} while (reader.NextResult());
}
return(commissionTypes.Distinct().ToList());
}
public void importfileExcel(string tenbang)
{
ExcelSuport exsp = new ExcelSuport();
List <string> tencot = exsp.LayTenCot(tenbang);
try
{
OpenFileDialog open = new OpenFileDialog();
open.Filter = "Chọn file Excel |*.xls; *.xlsx; *.xlsm";
if (open.ShowDialog() == DialogResult.Cancel)
{
return;
}
FileStream stream = new FileStream(open.FileName, FileMode.Open);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet();
string celldautien = "";
foreach (DataTable table in result.Tables)
{
if (table.Columns.Count != tencot.Count)
{
XtraMessageBox.Show("File không hợp lệ! Vui lòng kiểm tra lại!!");
break;
}
else
{
foreach (DataRow dr in table.Rows)
{
if (table.Rows.IndexOf(dr) == 0)
{
celldautien = dr[0].ToString();
continue;
}
else
{
if (celldautien == "MaNV")
{
this.ImportNV(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(),
dr[5].ToString(), dr[6].ToString(), dr[7].ToString(), Convert.ToDateTime(dr[8]));
excelReader.Close();
stream.Close();
XtraMessageBox.Show("Import file thành công", "Thông báo");
}
else
{
if (celldautien == "MaKH")
{
this.ImportKH(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), Convert.ToDateTime(dr[3]), dr[4].ToString(),
dr[5].ToString(), dr[6].ToString(), dr[7].ToString());
excelReader.Close();
stream.Close();
XtraMessageBox.Show("Import file thành công", "Thông báo");
}
else
{
this.ImportHD(dr[0].ToString(), dr[1].ToString(), dr[2].ToString(), dr[3].ToString(), dr[4].ToString(), Convert.ToDateTime(dr[5]));
excelReader.Close();
stream.Close();
XtraMessageBox.Show("Import file thành công", "Thông báo");
}
}
//kh.ImportKH(Convert.ToString(dr[0]), Convert.ToString(dr[1]), Convert.ToString(dr[2]), Convert.ToDateTime(dr[3]),
// Convert.ToBoolean(dr[4]), Convert.ToString(dr[5]), Convert.ToString(dr[6]), Convert.ToString(dr[7]));
}
}
}
}
}
catch (Exception ex)
{
XtraMessageBox.Show("Có lỗi xảy ra, xin chọn lại!", "Thông báo");
}
}
public ActionResult Emitir(HttpPostedFileBase upload)
{
if (ModelState.IsValid)
{
v_codigocotizacion = System.Web.HttpContext.Current.Session["codigocotizacion"].ToString();
v_empresanombre = System.Web.HttpContext.Current.Session["empresanombre"].ToString();
v_empresaruc = System.Web.HttpContext.Current.Session["empresaruc"].ToString();
v_codigocliente = System.Web.HttpContext.Current.Session["codigocliente"].ToString();
v_detmontoplanillaadm = Convert.ToDouble(System.Web.HttpContext.Current.Session["detmontoplanillaadm"]);
v_detmontoplanillaope = Convert.ToDouble(System.Web.HttpContext.Current.Session["detmontoplanillaope"]);
ViewData["txtEmpresaRUC"] = v_empresaruc;
ViewData["txtEmpresaNombre"] = v_empresanombre;
ViewData["txtCodigoCotizacion"] = v_codigocotizacion;
ViewData["txtCodigoCliente"] = v_codigocliente;
ViewData["txtPlanillaMensual"] = v_detmontoplanillaadm + v_detmontoplanillaope;
if (upload != null && upload.ContentLength > 0)
{
// ExcelDataReader works with the binary Excel file, so it needs a FileStream
// to get started. This is how we avoid dependencies on ACE or Interop:
Stream stream = upload.InputStream;
// We return the interface, so that
IExcelDataReader reader = null;
if (upload.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (upload.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
ModelState.AddModelError("File", "Formato de archivo incorrecto.");
return(View());
}
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = false
}
};
DataTable dt = new DataTable();
DataSet result = reader.AsDataSet();
DataSet resultordenado = new DataSet();//Data to be sorted.
//Sort data.
result.Tables[0].DefaultView.Sort = "Column5 ASC";
//Store in new Dataset
resultordenado.Tables.Add(result.Tables[0].DefaultView.ToTable());
string sMensaje = "";
dt = resultordenado.Tables[0];
dt.Columns["Column0"].ColumnName = "Nombres";
dt.Columns["Column1"].ColumnName = "Paterno";
dt.Columns["Column2"].ColumnName = "Materno";
dt.Columns["Column3"].ColumnName = "TipoTrab";
dt.Columns["Column4"].ColumnName = "TipoDoc";
dt.Columns["Column5"].ColumnName = "NroDoc";
dt.Columns["Column6"].ColumnName = "Sexo";
dt.Columns["Column7"].ColumnName = "EstadoCivil";
dt.Columns["Column8"].ColumnName = "Direccion";
dt.Columns["Column9"].ColumnName = "Telefono";
dt.Columns["Column10"].ColumnName = "FechaNac";
dt.Columns["Column11"].ColumnName = "Correo";
dt.Columns["Column12"].ColumnName = "Moneda";
dt.Columns["Column13"].ColumnName = "Sueldo";
dt.Columns["Column14"].ColumnName = "Estado";
int j = 0;
int k = 0;
for (int i = 0; i < dt.Rows.Count - 1; i++)
{
string datoNombres = dt.Rows[i]["Nombres"].ToString();
string datoPaterno = dt.Rows[i]["Paterno"].ToString();
string datoMaterno = dt.Rows[i]["Materno"].ToString();
string datoTipoTrab = dt.Rows[i]["TipoTrab"].ToString();
string datoTipoDoc = dt.Rows[i]["TipoDoc"].ToString();
string datoNroDoc = dt.Rows[i]["NroDoc"].ToString();
string datoNroDocAnt = "";
if (i > 0)
{
datoNroDocAnt = dt.Rows[i - 1]["NroDoc"].ToString();
}
DataRow dr = dt.Rows[i];
if (datoNombres == "Nombres" | datoPaterno == "Paterno")
{
dr.Delete();
dt.AcceptChanges();
}
if (datoNombres == "" | datoPaterno == "" | datoMaterno == "" | datoTipoTrab == "" | datoTipoDoc == "" | datoNroDoc == "")
{
dt.Rows[i]["Estado"] = "Error: Faltan Datos ";
j = j + 1;
}
else if (datoNroDoc == datoNroDocAnt)
{
dt.Rows[i]["Estado"] = "Error : NroDoc Duplicado";
dt.Rows[i - 1]["Estado"] = "Error : NroDoc Duplicado";
k = k + 1;
}
else
{
dt.Rows[i]["Estado"] = "Correcto";
}
}
reader.Close();
if (j == 0 & k == 0)
{
sMensaje = "Datos Correctos";
}
else
{
sMensaje = "Existen errores en los datos, corrija y vuelva a cargar el archivo";
}
ViewBag.Mensaje = sMensaje;
ViewBag.Message = sMensaje;
//ViewBag.showSuccessAlert = true;
return(PartialView(dt));
}
else
{
ModelState.AddModelError("File", "Seleccione un archivo");
}
}
return(PartialView());
}
public static byte[] XlsToConfig(string path)
{
//把xls转成字节流
ByteArray ba = new ByteArray();
try {
if (File.Exists(path) && !path.Contains("$") && (path.EndsWith(".xls") || path.EndsWith(".xlsx")))
{
string fileName = Path.GetFileNameWithoutExtension(path);
fileName = fileName.Split('_')[0];
fileName = fileName.ToLower();
ba.Write(fileName);
//获取数据长度
ByteArray baData = new ByteArray();
using (FileStream st = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
IExcelDataReader reader = ExcelReaderFactory.CreateReader(st);
//Console.Write(reader.Name);
StringBuilder sb = new StringBuilder();
List <string> attributeName = new List <string>();
List <string> atttibuteType = new List <string>();
List <string> attributeNote = new List <string>();
int attCount = 0;
//字段
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
attributeName.Add(reader.GetString(i));
}
}
//类型
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
string t = reader.GetString(i);
atttibuteType.Add(t);
if (!string.IsNullOrEmpty(t))
{
attCount++;
}
}
}
//注释
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
attributeNote.Add(reader.GetString(i));
}
}
//说明
if (reader.Read())
{
}
//数据
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
if (!string.IsNullOrEmpty(attributeName[i]) && !string.IsNullOrEmpty(atttibuteType[i]))
{
string value = reader.GetValue(i).ToString();
switch (atttibuteType[i])
{
case "int":
baData.Write(int.Parse(value));
break;
case "str":
baData.Write(value);
break;
case "bool":
baData.Write(value.Equals("0") ? false : true);
break;
case "long":
baData.Write(long.Parse(value));
break;
default:
break;
}
}
}
baData.Write("#");//每行数据的结束标志符
}
ba.Write(baData.GetLength());
ba.Write(baData.GetBuffer());
baData.Close();
}
}
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
return(ba.GetBuffer());
}
private void BtnUpload_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel Files (*.xls)|*.xls";
DialogResult dialogResult = openFileDialog.ShowDialog();
if (dialogResult == DialogResult.OK)
{
string filepath = openFileDialog.FileName;
FileStream stream = File.Open(filepath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream);
DataSet1.TradeDataTable tradeRows = SendClass.GetTradeRows();
DataSet result = excelReader.AsDataSet();
for (int i = 1; i < result.Tables[0].Rows.Count; i++)
{
DataRow dataRow = tradeRows.NewRow();
dataRow[0] = result.Tables[0].Rows[i][1];
string transaction = result.Tables[0].Rows[i][2].ToString();
dataRow[1] = new DateTime(int.Parse(transaction.Substring(0, 4)),
int.Parse(transaction.Substring(5, 2)), int.Parse(transaction.Substring(8, 2)));
string deadline = result.Tables[0].Rows[i][3].ToString();
dataRow[2] = new DateTime(int.Parse(deadline.Substring(0, 4)),
int.Parse(deadline.Substring(5, 2)), int.Parse(deadline.Substring(8, 2)));
dataRow[3] = result.Tables[0].Rows[i][5];
dataRow[4] = result.Tables[0].Rows[i][6];
dataRow[5] = result.Tables[0].Rows[i][7];
dataRow[6] = result.Tables[0].Rows[i][8];
dataRow[7] = result.Tables[0].Rows[i][9];
dataRow[8] = int.Parse(result.Tables[0].Rows[i][10].ToString());
dataRow[9] = int.Parse(result.Tables[0].Rows[i][11].ToString());
dataRow[10] = result.Tables[0].Rows[i][12];
string inspection = result.Tables[0].Rows[i][13].ToString();
if (inspection.Length > 0)
{
dataRow[11] = new DateTime(int.Parse(inspection.Substring(0, 4)),
int.Parse(inspection.Substring(5, 2)), int.Parse(inspection.Substring(8, 2)));
}
else
{
dataRow[11] = new DateTime();
}
string bill = result.Tables[0].Rows[i][14].ToString();
if (bill.Length > 0)
{
dataRow[12] = new DateTime(int.Parse(bill.Substring(0, 4)),
int.Parse(bill.Substring(5, 2)), int.Parse(bill.Substring(8, 2)));
}
else
{
dataRow[12] = new DateTime();
}
dataRow[13] = new DateTime();
dataRow[14] = result.Tables[0].Rows[i][15];
dataRow[15] = int.Parse(result.Tables[0].Rows[i][0].ToString());
tradeRows.Rows.Add(dataRow);
}
tradeRows.WriteXml(SendClass.filepath + "trade_data.xml");
}
}
/// <summary>
/// 直接把xls转成配置类
/// </summary>
public static void XlsToCfgModel(string src, string dst)
{
try {
if (string.IsNullOrEmpty(content))
{
if (!File.Exists(tempPath))
{
Console.WriteLine("模板文件不存在");
return;
}
StreamReader sr = new StreamReader(tempPath);
content = sr.ReadToEnd();
sr.Dispose();
}
if ((src.EndsWith(".xls") || src.EndsWith(".xlsx")) && !src.Contains("$"))
{
using (FileStream st = File.Open(src, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
IExcelDataReader reader = ExcelReaderFactory.CreateReader(st);
//Console.Write(reader.Name);
StringBuilder sb = new StringBuilder();
List <string> attributeName = new List <string>();
List <string> atttibuteType = new List <string>();
List <string> attributeNote = new List <string>();
int attCount = 0;
//字段
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
attributeName.Add(reader.GetString(i));
}
}
//类型
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
string t = reader.GetString(i);
atttibuteType.Add(t);
if (!string.IsNullOrEmpty(t))
{
attCount++;
}
}
}
//注释
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
attributeNote.Add(reader.GetString(i));
}
}
bool create = false;
if (File.Exists(dst))
{
//存在文件就要比较一下字段，看是否需要覆盖
using (StreamReader sr = new StreamReader(dst)) {
string allText = sr.ReadToEnd();
string tx = allText.Split(new string[] { "//member" }, StringSplitOptions.RemoveEmptyEntries)[1];
tx = tx.Split(new string[] { "//endMember" }, StringSplitOptions.RemoveEmptyEntries)[0];
//有多少个分号，就是有多少个字段 ( 减1是因为 tableName 不算 )
int oldAttCount = Regex.Matches(tx, ";").Count - 1;
//字段个数不相等，要重新生成
if (oldAttCount != attCount)
{
create = true;
}
else
{
//否则查看字段类型是否相同，不同的也要重新生成
int s = 0, e = 0;
for (int i = 0; i < attributeName.Count; i++)
{
if (!allText.Contains(attributeName[i]) && !string.IsNullOrEmpty(atttibuteType[i]))
{
create = true;
break;
}
e = allText.IndexOf(attributeName[i]);
string t = allText.Substring(s, e - s);
if (!t.Contains(atttibuteType[i]))
{
create = true;
break;
}
s = e;
}
}
}
if (create)
{
File.Delete(dst);
}
}
else
{
create = true;
}
if (create)
{
Directory.CreateDirectory(Path.GetDirectoryName(dst));
using (StreamWriter sw = new StreamWriter(dst)) {
string[] contents = content.Split(new string[] { "//member" }, StringSplitOptions.RemoveEmptyEntries);
contents[0] = contents[0].Replace("ConfigModelTemple", Path.GetFileNameWithoutExtension(dst));
sb = new StringBuilder(contents[0]);
sb.Append("//member\n");
sb.Append("\t\tpublic static string tableName=\"");
sb.Append(Path.GetFileNameWithoutExtension(src).ToLower().Split('_')[0]);
sb.Append("\";\n");
for (int i = 0; i < attributeName.Count; i++)
{
if (!string.IsNullOrEmpty(atttibuteType[i]) && AppSetting.Ins.GetValue("outputType").Contains(atttibuteType[i]))
{
sb.Append("\t\t/// <summary>\n");
sb.Append("\t\t/// ");
sb.Append(attributeNote[i]);
sb.Append("\n\t\t/// </summary>\n");
sb.Append("\t\tpublic ");
switch (atttibuteType[i])
{
case "int":
sb.Append("int ");
break;
case "str":
sb.Append("string ");
break;
case "bool":
sb.Append("bool ");
break;
case "long":
sb.Append("long ");
break;
default:
break;
}
sb.Append(attributeName[i]);
sb.Append(";\n");
}
}
sb.Append("//endMember\n");
string[] readContents = contents[1].Split(new string[] { "//read" }, StringSplitOptions.RemoveEmptyEntries);
sb.Append(readContents[0]);
for (int i = 0; i < attributeName.Count; i++)
{
if (!string.IsNullOrEmpty(atttibuteType[i]))
{
sb.Append("\t\t\t");
sb.Append(attributeName[i]);
sb.Append("=");
switch (atttibuteType[i])
{
case "int":
sb.Append("ReadInt();\n");
break;
case "str":
sb.Append("ReadString();\n");
break;
case "bool":
sb.Append("ReadBool();\n");
break;
case "long":
sb.Append("ReadLong();\n");
break;
default:
break;
}
}
}
sb.Append(readContents[1]);
sw.Write(sb.ToString());
sb.Clear();
}
}
}
}
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
public static ImportDataModel GetImportModels(string path)
{
try
{
var model = new ImportDataModel();
var stream = File.OpenRead(path);
IExcelDataReader excelReader = null;
//Reading from a binary Excel file ('97-2003 format; *.xls)
if (Path.GetExtension(path).Equals(".xls", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
//Reading from a OpenXml Excel file (2007 format; *.xlsx)
if (Path.GetExtension(path).Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (excelReader == null)
{
return(model);
}
var pageIndex = 0;
do
{
pageIndex++;
var items = new List <ImportModel>();
var category = (CategoryCode)pageIndex;
var rowIndex = 0;
while (excelReader.Read())
{
rowIndex++;
if (rowIndex < StartRowIndex)
{
continue;
}
if (excelReader.IsDBNull(3))
{
continue;
}
var item = new ImportModel();
item.V1 = excelReader.IsDBNull(0) ? (int?)null : excelReader.GetInt32(0);
item.V2 = excelReader.GetString(1);
item.V3 = excelReader.GetString(2);
item.V4 = excelReader.GetString(3);
item.V5 = excelReader.GetString(4);
item.V6 = excelReader.GetString(5);
item.V7 = excelReader.GetString(6);
item.V8 = excelReader.GetString(7);
item.V9 = excelReader.GetString(8);
item.V10 = excelReader.GetString(9);
item.V11 = excelReader.IsDBNull(10) ? (DateTime?)null : excelReader.GetDateTime(10);
item.V12 = excelReader.IsDBNull(11) ? (double?)null : excelReader.GetDouble(11);
item.V13 = excelReader.IsDBNull(12) ? (double?)null : excelReader.GetDouble(12);
item.V14 = excelReader.IsDBNull(13) ? (double?)null : excelReader.GetDouble(13);
item.V15 = excelReader.IsDBNull(14) ? (double?)null : excelReader.GetDouble(14);
item.V16 = excelReader.IsDBNull(15) ? (double?)null : excelReader.GetDouble(15);
item.V17 = excelReader.IsDBNull(16) ? (double?)null : excelReader.GetDouble(16);
item.V18 = excelReader.IsDBNull(17) ? (double?)null : excelReader.GetDouble(17);
item.V19 = excelReader.IsDBNull(18) ? (double?)null : excelReader.GetDouble(18);
item.V20 = excelReader.IsDBNull(19) ? (double?)null : excelReader.GetDouble(19);
item.V21 = excelReader.IsDBNull(20) ? (double?)null : excelReader.GetDouble(20);
item.V22 = excelReader.IsDBNull(21) ? (double?)null : excelReader.GetDouble(21);
item.V23 = excelReader.IsDBNull(22) ? (double?)null : excelReader.GetDouble(22);
item.V24 = excelReader.IsDBNull(23) ? (double?)null : excelReader.GetDouble(23);
item.V25 = excelReader.IsDBNull(24) ? (DateTime?)null : excelReader.GetDateTime(24);
item.V26 = excelReader.IsDBNull(25) ? (double?)null : excelReader.GetDouble(25);
item.V27 = excelReader.IsDBNull(26) ? (DateTime?)null : excelReader.GetDateTime(26);
item.V28 = excelReader.IsDBNull(27) ? (DateTime?)null : excelReader.GetDateTime(27);
item.V29 = excelReader.IsDBNull(28) ? (DateTime?)null : excelReader.GetDateTime(28);
item.V30 = excelReader.IsDBNull(29) ? (DateTime?)null : excelReader.GetDateTime(29);
item.V31 = excelReader.IsDBNull(30) ? (DateTime?)null : excelReader.GetDateTime(30);
item.V32 = excelReader.IsDBNull(31) ? (DateTime?)null : excelReader.GetDateTime(31);
item.V33 = excelReader.IsDBNull(32) ? (DateTime?)null : excelReader.GetDateTime(32);
item.V34 = excelReader.GetString(33);
item.V35 = excelReader.GetString(34);
item.V36 = excelReader.GetString(35);
item.V37 = excelReader.IsDBNull(36) ? (DateTime?)null : excelReader.GetDateTime(36);
item.V38 = excelReader.GetString(37);
item.V39 = excelReader.IsDBNull(38) ? (DateTime?)null : excelReader.GetDateTime(38);
item.V40 = excelReader.IsDBNull(39) ? (double?)null : excelReader.GetDouble(39);
items.Add(item);
}
model.Items.Add(category, items);
model.PageRows.Add(category, rowIndex);
} while (excelReader.NextResult());
excelReader.Close();
return(model);
}
catch (Exception ex)
{
throw ex;
}
}
public ExcelIterator(Stream stream, ExcelIteratorConfiguration configuration)
{
_configuration = configuration;
_dataReader = ExcelReaderFactory.CreateReader(stream);
}
private bool BulkSave(MemoryStream stream, out int count, out int total)
{
count = 0;
var leadSourcesDict = GetLeadSourceDict();
var pincodeDict = GetPincodeDict();
var subDistDict = GetSubDistDict();
var localityDic = GetLocalityDict();
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
total = reader.RowCount - 1;
reader.Read();
int index = 0;
while (reader.Read())
{
string businessName = reader.GetString(index++);
string proprietorName = reader.GetString(index++);
string proprietorMobile = reader.GetDouble(index++).ToString();
string communicatorName = reader.GetString(index++);
string communicatorMobile = reader.GetDouble(index++).ToString();
string address1 = reader.GetString(index++);
string address2 = reader.GetString(index++);
long pinOrZip = (long)reader.GetDouble(index++);
Guid? pinOrZipId = pincodeDict.ContainsKey(pinOrZip) ? (Guid?)pincodeDict[pinOrZip].Id : null;
string village = reader.GetString(index++);
Guid?villageId = localityDic.ContainsKey(village) ? (Guid?)localityDic[village].Id : null;
string subDistrict = reader.GetString(index++);
Guid? subDistrictId = subDistDict.ContainsKey(subDistrict) ? (Guid?)subDistDict[subDistrict].Id : null;
string mobileNumber = reader.GetDouble(index++).ToString();
string landLine = reader.GetDouble(index++).ToString();
string leadSourceName = reader.GetString(index++);
/**
* Need to reset the column index to zero
*/
index = 0;
LeadSource leadSource = null;
if (leadSourcesDict.ContainsKey(leadSourceName))
{
leadSource = leadSourcesDict[leadSourceName];
}
Lead foundLead = this.service
.FindByMobile(mobileNumber, landLine);
if (foundLead != null)
{
continue;
}
var leadOpenStatus = leadStatusService.FindByName("Open");
Contact proprietorcontact = null;
if (proprietorName != null && proprietorMobile != null &&
proprietorMobile.Length >= 10)
{
proprietorcontact = new Contact
{
Id = Guid.NewGuid(),
FirstName = proprietorName,
MobileNumber = proprietorMobile
};
}
Contact communicatorContact = null;
if (communicatorName != null && communicatorMobile != null &&
communicatorMobile.Length >= 10)
{
communicatorContact = new Contact
{
Id = Guid.NewGuid(),
FirstName = communicatorName,
MobileNumber = communicatorMobile
};
}
var history = new LeadStatusHistory
{
StatusDate = DateTime.Now,
Status = null,
StatusId = leadOpenStatus.Id,
Created = DateTime.Now,
Order = 0,
};
Lead lead = new Lead
{
SourceId = leadSource.Id,
Source = null,
History = new List <LeadStatusHistory>()
{
history
},
CurrentLeadStatusId = history.Id
};
lead.Contact = new BusinessContact
{
BusinessName = businessName,
ContactAddress = new Database.Entity.Crm.Address.Master
{
AddressLine1 = address1,
AddressLine2 = address2,
LocalityOrVillage = village,
LocalityOrVillageId = villageId,
PinOrZip = pinOrZip,
SubDistrict = subDistrict,
SubDistrictId = subDistrictId
},
Proprietor = proprietorcontact,
ContactPerson = communicatorContact,
MobileNumber = mobileNumber,
Landline = landLine,
};
lead = baseService.Save(lead);
if (lead == null)
{
continue;
}
count++;
}
}
return(true);
}
protected void BtnSend_Click(object sender, EventArgs e)
{
if (FUPayamDowlat.HasFile)
{
string FileName = Path.GetFileName(FUPayamDowlat.PostedFile.FileName);
string Extension = Path.GetExtension(FUPayamDowlat.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
var f = new MemoryStream(FUPayamDowlat.FileBytes);
using (var stream = f)
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet(new ExcelDataSetConfiguration() //use library of excsell reader nuget and use help of document to add
{
UseColumnDataType = true,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
EmptyColumnNamePrefix = "Column",
UseHeaderRow = true,
FilterColumn = (rowReader, columnIndex) =>
{
return(true);
}
}
});
DGVpayamDowlat.DataSource = result;
DGVpayamDowlat.DataBind();
string connectionString = @"Data Source=Geek;Initial Catalog=IstgPayamDowlat;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connectionString))
{
try
{
conn.Open();
// Configure the command and parameter.
SqlCommand insertCommand = new SqlCommand("sp_Tvp", conn);
insertCommand.CommandType = CommandType.StoredProcedure;
var param = new SqlParameter()
{
SqlDbType = SqlDbType.Structured,
Value = result.Tables[0],
ParameterName = "@TvpAddressBook"
};
SqlParameter tvp = insertCommand.Parameters.Add(param);
// Execute the command.
insertCommand.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
Response.Write("Error : " + ex.Message.ToString());
}
}
}
}
}
private void LoadFromXml()
{
OpenFileDialog openFileDialog = new OpenFileDialog
{
Multiselect = false,
Filter = "Excel Files|*.xls;*.xlsx;*.xlsm",
RestoreDirectory = true
};
var headers = new List <string>();
_pointersDict = new Dictionary <string, List <NumbersDTO> >();
ExcelDataSetConfiguration c = new ExcelDataSetConfiguration
{
ConfigureDataTable = (s) =>
{
return(new ExcelDataTableConfiguration
{
UseHeaderRow = true,
ReadHeaderRow = rowReader =>
{
for (var i = 0; i < rowReader.FieldCount; i++)
{
headers.Add(Convert.ToString(rowReader.GetValue(i)));
}
},
});
}
};
List <DataRow> drows;
if (openFileDialog.ShowDialog() == true)
{
using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
drows = reader.AsDataSet(c).Tables[0].AsEnumerable().ToList();
}
}
List <NumbersDTO> list = new List <NumbersDTO>();
string currentNumber = String.Empty;
for (int i = 1; i < drows.Count; i++)
{
var row = drows[i].ItemArray;
if (row.First() != DBNull.Value)
{
if (i != 1)
{
_pointersDict.Add(currentNumber, list);
list = new List <NumbersDTO>();
}
currentNumber = row.First().ToString();
}
NumbersDTO dto = new NumbersDTO
{
H = Convert.ToDouble(row[1]),
T1 = Convert.ToDouble(row[2]),
T2 = Convert.ToDouble(row[3])
};
list.Add(dto);
if (i == drows.Count - 1)
{
_pointersDict.Add(currentNumber, list);
}
}
Models.AddRange(_pointersDict.Select(x => x.Key).ToList());
VisibilityCondition = true;
Settings.Default.Save();
}
}
public frmHocSinh(frmLogin frmlogin, HocSinh hs)
{
this.frmlogin = frmlogin;
this.hs = hs;
InitializeComponent();
// thanhbinh
timer1.Start();
timer1.Interval = 1000;
int counter = 3600;
TimeSpan t = TimeSpan.FromSeconds(counter);
timer1.Tick += (s, e) =>
{
if (counter == 0)
{
timer1.Stop();
}
else
{
counter--;
}
t = TimeSpan.FromSeconds(counter);
lblThoiGian.Text = string.Format("{0:D2}:{1:D2}",
t.Minutes,
t.Seconds);
};
// koichen
this.FormClosing += (s, e) =>
{
frmlogin.Show();
};
loadCBCauHoi();
loadDGVDapAn();
set();
dgchBtnThemCH.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
if (qlttn.CauHois.Where(ch => ch.NoiDung.ToLower() == dgchTxtCH.Text.ToLower()).Count() != 0)
{
MessageBox.Show("Câu hỏi này đã có trong danh sách. Xin mời tạo câu hỏi mới", "Trùng record", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
// tìm thử xem có câu hỏi nào chưa có đáp án hay không, nếu như có thì ngừng việc
// thêm câu hỏi mới mà yêu cầu người dùng thêm đáp án cho câu hỏi đó
var cauhoi = qlttn.CauHois.Where(ch => ch.DapAns == null || ch.DapAns.Count < 2).FirstOrDefault();
if (cauhoi != null)
{
MessageBox.Show($"Xin mời nhập tối thiểu 2 đáp án cho câu hỏi sau trước khi thêm câu hỏi mới:{Environment.NewLine} <{cauhoi.NoiDung}>", "Câu hỏi chưa có đáp án", MessageBoxButtons.OK, MessageBoxIcon.Hand);
return;
}
qlttn.CauHois.InsertOnSubmit(new CauHoi()
{
NoiDung = dgchTxtCH.Text,
CapDo = qlttn.CapDos.Where(cd => cd.maCD == int.Parse(dgchCbCapDo.SelectedValue.ToString())).SingleOrDefault()
});
qlttn.SubmitChanges();
}
loadCBCauHoi();
cbDSCH.SelectedItem = cbDSCH.Items[cbDSCH.Items.Count - 1];
dgchTxtDA.Focus();
};
dgchBtnXoaCH.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
if (qlttn.CauHois.Count() <= 1)
{
MessageBox.Show("Không thể xóa vì cần phải có ít nhất một câu hỏi trong Database", "Thông báo lỗi", MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}
var cauHoiHienTai = qlttn.CauHois
.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString()))
.FirstOrDefault();
qlttn.DapAns.DeleteAllOnSubmit(cauHoiHienTai.DapAns);
qlttn.CauHois.DeleteOnSubmit(cauHoiHienTai);
qlttn.SubmitChanges();
}
loadCBCauHoi();
};
dgchBtnSuaCH.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
if (qlttn.CauHois.Where(ch => ch.NoiDung.ToLower() == dgchTxtCH.Text.ToLower()).Count() != 0)
{
MessageBox.Show("Câu hỏi này đã có trong danh sách. Xin mời tạo câu hỏi mới", "Trùng record", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
var cauHoiHienTai = qlttn.CauHois
.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString()))
.FirstOrDefault();
cauHoiHienTai.NoiDung = dgchTxtCH.Text;
cauHoiHienTai.CapDo = qlttn.CapDos.Where(cd => cd.maCD == int.Parse(dgchCbCapDo.SelectedValue.ToString())).FirstOrDefault();
qlttn.SubmitChanges();
}
loadCBCauHoi();
cbDSCH.SelectedItem = cbDSCH.Items[cbDSCH.Items.Count - 1];
};
dgchBtnThemDA.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
var cauHoiHienTai = qlttn.CauHois
.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString()))
.FirstOrDefault();
if (cauHoiHienTai.DapAns.Where(da => da.NoiDung.ToLower() == dgchTxtDA.Text.ToLower()).Count() != 0)
{
MessageBox.Show("Đáp án này đã có trong danh sách. Xin mời tạo đáp án mới", "Lỗi trùng dữ liệu", MessageBoxButtons.OK, MessageBoxIcon.Hand);
return;
}
if (cauHoiHienTai.DapAns.Count >= 6)
{
MessageBox.Show("Mỗi câu hỏi có tối đa 6 đáp án. Xin mời xóa bớt đáp án để thêm mới", "Hướng dẫn", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
qlttn.DapAns.InsertOnSubmit(new DapAn()
{
NoiDung = dgchTxtDA.Text,
DungSai = dgchCkbDungSai.Checked,
//DungSai = bool.Parse(txtDungSai.Text),
CauHoi = qlttn.CauHois.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString())).SingleOrDefault()
});
qlttn.SubmitChanges();
}
loadDGVDapAn();
dgvDSDA.Rows[dgvDSDA.Rows.GetLastRow(DataGridViewElementStates.Displayed)].Selected = true;
dgchTxtDA.Focus();
};
dgchBtnXoaDA.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
var cauHoiHienTai = qlttn.CauHois
.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString()))
.FirstOrDefault();
if (cauHoiHienTai.DapAns.Count <= 2)
{
MessageBox.Show("Mỗi câu hỏi cần phải có tối thiểu 2 đáp án", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (dgvDSDA.SelectedRows.Count == 0)
{
MessageBox.Show("Hãy chọn đáp án cần xóa", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
qlttn.DapAns.DeleteOnSubmit(cauHoiHienTai.DapAns.Where(da => da.maDA == int.Parse(dgvDSDA.SelectedRows[0].Cells["maDA"].Value.ToString())).FirstOrDefault());
qlttn.SubmitChanges();
}
loadDGVDapAn();
};
dgchBtnSuaDA.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
var cauHoiHienTai = qlttn.CauHois
.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString()))
.FirstOrDefault();
if (cauHoiHienTai.DapAns.Where(da => da.NoiDung.ToLower() == dgchTxtDA.Text.ToLower()).Count() != 0)
{
MessageBox.Show("Đáp án này đã có trong danh sách. Xin mời tạo đáp án mới", "Lỗi trùng dữ liệu", MessageBoxButtons.OK, MessageBoxIcon.Hand);
return;
}
var dapAnHienTai = cauHoiHienTai.DapAns.Where(da => da.maDA == int.Parse(dgvDSDA.SelectedRows[0].Cells["maDA"].Value.ToString())).FirstOrDefault();
dapAnHienTai.NoiDung = dgchTxtDA.Text;
dapAnHienTai.DungSai = dgchCkbDungSai.Checked;
//dapAnHienTai.DungSai = bool.Parse(txtDungSai.Text);
qlttn.SubmitChanges();
}
loadDGVDapAn();
dgchTxtDA.Focus();
};
dgchbtnImport.Click += (s, e) =>
{
using (OpenFileDialog ofd = new OpenFileDialog()
{
Filter = "Excel Workbook|*.xlsx|Excel Workbook 97-2003|*.xls", ValidateNames = true
})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
using (var stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader;
DataSet ds;
List <CauHoi> cauHoiBiTrung = new List <CauHoi>();
if (ofd.FilterIndex == 2)
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
reader.Close();
//cb_sheet.Items.Clear();
using (var qlttn = new QLTTNDataContext())
{
DataTable dtCauHoi = ds.Tables["CauHoi"];
DataTable dtDapAn = ds.Tables["DapAn"];
DataRow firstRow = dtCauHoi.Rows[0];
int lastIdent = (int)qlttn.ExecuteQuery <decimal>("select IDENT_CURRENT('dbo.CauHoi')").FirstOrDefault();
foreach (DataRow row in dtCauHoi.Rows)
{
CauHoi cauHoiTmp = new CauHoi()
{
NoiDung = row["NoiDung"].ToString(),
maCD = int.Parse(row["maCD"].ToString()),
};
if (qlttn.CauHois.Where(ch => ch.NoiDung.ToLower() == cauHoiTmp.NoiDung.ToLower()).Count() == 0)
{
qlttn.CauHois.InsertOnSubmit(cauHoiTmp);
qlttn.SubmitChanges();
foreach (DataRow rowDapAn in dtDapAn.Rows)
{
if (rowDapAn["maCH"].ToString() == row["maCH"].ToString())
{
DapAn datmp = new DapAn()
{
NoiDung = rowDapAn["NoiDung"].ToString(),
maCH = qlttn.CauHois.Max(ch => ch.maCH),
DungSai = rowDapAn["DungSai"].ToString().ToLower() == "true" ? true : false
};
qlttn.DapAns.InsertOnSubmit(datmp);
}
}
}
else
{
cauHoiBiTrung.Add(cauHoiTmp);
}
}
qlttn.SubmitChanges();
}
loadCBCauHoi();
loadDGVDapAn();
if (cauHoiBiTrung.Count > 0)
{
string strCauHois = "";
for (int i = 0; i < cauHoiBiTrung.Count; i++)
{
string str = cauHoiBiTrung[i].NoiDung;
int maxLeng = 50;
if (str.Length > maxLeng)
{
str = str.Replace(str.Substring(maxLeng, str.Length - maxLeng), " ...");
}
strCauHois += $"{Environment.NewLine} {i + 1}. {str}";
}
MessageBox.Show($">>>> DANH SÁCH NHỮNG CÂU HỎI BỊ TRÙNG <<<< {strCauHois}", "Không thể import", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
MessageBox.Show("Import danh sách câu hỏi thành công", "Thông báo");
}
}
}
}
};
dgchbtnExport.Click += (s, e) =>
{
using (var sfd = new SaveFileDialog()
{
CreatePrompt = false,
OverwritePrompt = true,
AddExtension = true,
Filter = "Excel Workbook|*.xlsx|Excel Workbook 97-2003|*.xls",
ValidateNames = true
})
{
if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
DataTable dtCauHoi = new DataTable();
dtCauHoi.TableName = "CauHoi";
dtCauHoi.Columns.Add("maCH", typeof(int));
dtCauHoi.Columns.Add("NoiDung", typeof(string));
dtCauHoi.Columns.Add("maCD", typeof(int));
DataTable dtDapAn = new DataTable();
dtDapAn.TableName = "DapAn";
dtDapAn.Columns.Add("maCH", typeof(int));
dtDapAn.Columns.Add("maDA", typeof(int));
dtDapAn.Columns.Add("NoiDung", typeof(string));
dtDapAn.Columns.Add("DungSai", typeof(bool));
using (var qlttn = new QLTTNDataContext())
{
List <CauHoi> chs = qlttn.CauHois.ToList();
List <DapAn> das = qlttn.DapAns.ToList();
for (int i = 0; i < qlttn.CauHois.Count(); i++)
{
List <DapAn> dasTmp = das.Where(da => da.maCH == chs[i].maCH).ToList();
chs[i].maCH = i + 1;
dtCauHoi.Rows.Add(chs[i].maCH, chs[i].NoiDung, chs[i].maCD);
foreach (var da in dasTmp)
{
da.maCH = i + 1;
dtDapAn.Rows.Add(da.maCH, da.maDA, da.NoiDung, da.DungSai);
}
}
};
XLWorkbook wb = new XLWorkbook();
wb.Worksheets.Add(dtCauHoi, dtCauHoi.TableName);
wb.Worksheets.Add(dtDapAn, dtDapAn.TableName);
wb.SaveAs(sfd.InitialDirectory + sfd.FileName);
}
}
};
}
/// <summary>
/// 打开文件
/// </summary>
/// <param name="path"></param>
/// <param name="fail"></param>
/// <returns></returns>
public Dictionary <string, List <Dictionary <string, object> > > Open(string path, Action fail)
{
m_data.Clear();
DataSet book = null;
try
{
using (FileStream fileStream = File.Open(path, FileMode.Open, FileAccess.Read))
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
book = excelReader.AsDataSet();
fileStream.Close();
}
}
catch (Exception e)
{
Util.ShowMessageBox(e.Message, fail);
}
finally
{
if (book != null)
{
string fileName = Path.GetFileNameWithoutExtension(path);
foreach (DataTable sheet in book.Tables)
{
if (IsEnglish(sheet.TableName) && !sheet.TableName.StartsWith("Sheet"))
{
List <Dictionary <string, object> > list = new List <Dictionary <string, object> >();
m_data.Add(sheet.TableName, list);
if (sheet.Rows.Count >= Setting.GetInt(Option.PointIndex))
{
DataRow fieldType = sheet.Rows[Setting.GetInt(Option.TypeIndex)];
DataRow fieldName = sheet.Rows[Setting.GetInt(Option.NameIndex)];
DataRow fieldAttr = sheet.Rows[Setting.GetInt(Option.AttrIndex)];
for (int i = Setting.GetInt(Option.PointIndex); i < sheet.Rows.Count; i++)
{
DataRow row = sheet.Rows[i];
if (!string.IsNullOrEmpty(row[0].ToString()))
{
Dictionary <string, object> dic = new Dictionary <string, object>();
list.Add(dic);
foreach (DataColumn column in sheet.Columns)
{
string name = GetName(fieldName[column].ToString());
string attr = GetAttr(fieldAttr[column].ToString());
if (!string.IsNullOrEmpty(name))
{
string type = GetType(fieldType[column].ToString());
object value = null;
Error error = TryParse(fileName, sheet.TableName, row[column], type, out value);
if (error == Error.Type)
{
Util.ShowMessageBox(string.Format("{0}\n{1}\n字段['{2}'],索引行['{3}']内容与类型不匹配", path, sheet.TableName, name, i), fail);
goto End;
}
else if (error == Error.Lang)
{
Util.ShowMessageBox(string.Format("{0}\n{1}\n字段['{2}'],索引行['{3}']多语言找寻不到", path, sheet.TableName, name, i), fail);
goto End;
}
if (dic.ContainsKey(name))
{
Util.ShowMessageBox(string.Format("{0}\n{1}\n字段['{2}']重复", path, sheet.TableName, name), fail);
goto End;
}
else
{
if (!string.IsNullOrEmpty(attr))
{
switch (attr)
{
case "guid":
case "GUID":
{
foreach (var lt in list)
{
if (lt.ContainsKey(name) && lt[name].Equals(value))
{
Util.ShowMessageBox(string.Format("{0}\n{1}\n{2}\n存在相同主键值 {3}", path, sheet.TableName, name, value), fail);
goto End;
}
}
}
break;
case "none":
case "NONE":
{ }
continue;
}
}
dic.Add(name, value);
}
}
}
}
}
}
}
}
}
End : { }
}
return(m_data);
}
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e)
{
#region Variables
fa_notaCreDeb_List ListaFactura = new fa_notaCreDeb_List();
List <fa_notaCreDeb_Info> Lista_Factura = new List <fa_notaCreDeb_Info>();
fa_cliente_Bus bus_cliente = new fa_cliente_Bus();
fa_cliente_contactos_Bus bus_cliente_contatos = new fa_cliente_contactos_Bus();
tb_sucursal_Bus bus_sucursal = new tb_sucursal_Bus();
fa_parametro_Bus bus_fa_parametro = new fa_parametro_Bus();
fa_TipoNota_Bus bus_tipo_nota = new fa_TipoNota_Bus();
tb_bodega_Bus bus_bodega = new tb_bodega_Bus();
int cont = 0;
int IdNota = 1;
decimal IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSessionActual);
int IdEmpresa = Convert.ToInt32(SessionFixed.IdEmpresa);
#endregion
Stream stream = new MemoryStream(e.UploadedFile.FileBytes);
if (stream.Length > 0)
{
IExcelDataReader reader = null;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
#region Saldo Fact
var info_fa_parametro = bus_fa_parametro.get_info(IdEmpresa);
var IdTipoNota = 1; //default
var infoTipoNota = bus_tipo_nota.get_info(IdEmpresa, IdTipoNota);
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont > 0)
{
var Su_CodigoEstablecimiento = Convert.ToString(reader.GetValue(0)).Trim();
var lst_sucursal = bus_sucursal.get_list(IdEmpresa, false);
var IdSucursal = lst_sucursal.Where(q => q.Su_CodigoEstablecimiento == Su_CodigoEstablecimiento).FirstOrDefault().IdSucursal;
var InfoCliente = bus_cliente.get_info_x_num_cedula(IdEmpresa, Convert.ToString(reader.GetValue(1)));
var infoBodega = bus_bodega.get_info(IdEmpresa, IdSucursal, 1);
if (InfoCliente != null && InfoCliente.IdCliente != 0)
{
//var InfoContactosCliente = bus_cliente_contatos.get_list(IdEmpresa, InfoCliente.IdCliente);
var InfoContactosCliente = bus_cliente_contatos.get_info(IdEmpresa, InfoCliente.IdCliente, 1);
fa_notaCreDeb_Info info = new fa_notaCreDeb_Info
{
IdEmpresa = IdEmpresa,
IdSucursal = IdSucursal,
IdBodega = infoBodega.IdBodega,
IdNota = IdNota++,
dev_IdEmpresa = null,
dev_IdDev_Inven = null,
CodNota = Convert.ToString(reader.GetValue(2)),
CreDeb = "D",
CodDocumentoTipo = null,
Serie1 = null,
Serie2 = null,
NumNota_Impresa = null,
NumAutorizacion = null,
Fecha_Autorizacion = null,
IdCliente = InfoCliente.IdCliente,
IdContacto = InfoContactosCliente.IdContacto,
no_fecha = Convert.ToDateTime(reader.GetValue(5)),
no_fecha_venc = Convert.ToDateTime(reader.GetValue(6)),
IdTipoNota = infoTipoNota.IdTipoNota,
sc_observacion = Convert.ToString(reader.GetValue(7)) == "" ? ("DOCUMENTO #" + Convert.ToString(reader.GetValue(2)) + " CLIENTE: " + InfoCliente.info_persona.pe_nombreCompleto) : Convert.ToString(reader.GetValue(7)),
IdUsuario = SessionFixed.IdUsuario,
NaturalezaNota = null,
IdCtaCble_TipoNota = infoTipoNota.IdCtaCble,
IdPuntoVta = null,
aprobada_enviar_sri = false
};
info.lst_det = new List <fa_notaCreDeb_det_Info>();
info.lst_cruce = new List <fa_notaCreDeb_x_fa_factura_NotaDeb_Info>();
fa_notaCreDeb_det_Info info_detalle = new fa_notaCreDeb_det_Info
{
IdEmpresa = IdEmpresa,
IdSucursal = IdSucursal,
IdBodega = info.IdBodega,
IdNota = info.IdNota,
IdProducto = 1,
sc_cantidad = 1,
sc_Precio = Convert.ToDouble(reader.GetValue(4)),
sc_descUni = 0,
sc_PordescUni = 0,
sc_precioFinal = Convert.ToDouble(reader.GetValue(4)),
sc_subtotal = Convert.ToDouble(reader.GetValue(4)),
sc_iva = 0,
sc_total = Convert.ToDouble(reader.GetValue(4)),
sc_costo = 0,
sc_observacion = Convert.ToString(reader.GetValue(7)),
sc_estado = "A",
vt_por_iva = 0,
IdPunto_Cargo = null,
IdPunto_cargo_grupo = null,
IdCod_Impuesto_Iva = "IVA0",
IdCod_Impuesto_Ice = null,
IdCentroCosto = null,
IdCentroCosto_sub_centro_costo = null,
sc_cantidad_factura = null
};
info.lst_det.Add(info_detalle);
Lista_Factura.Add(info);
}
}
else
{
cont++;
}
}
ListaFactura.set_list(Lista_Factura, IdTransaccionSession);
#endregion
}
}
private void btnBrowse_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
{
grvData.Columns.Clear();
OpenFileDialog ofd = new OpenFileDialog();
if (chkAutoCheck.Checked)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
var result = openFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
btnBrowse.Text = openFileDialog1.FileName;
}
else if (result == DialogResult.Cancel)
{
return;
}
try
{
var stream = new FileStream(btnBrowse.Text, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var sw = new Stopwatch();
sw.Start();
IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream);
var openTiming = sw.ElapsedMilliseconds;
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
//toolStripStatusLabel1.Text = "Elapsed: " + sw.ElapsedMilliseconds.ToString() + " ms (" + openTiming.ToString() + " ms to open)";
var tablenames = GetTablenames(ds.Tables);
cboSheet.DataSource = tablenames;
if (tablenames.Count > 0)
{
cboSheet.SelectedIndex = 0;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), ex.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
if (ofd.ShowDialog() == DialogResult.OK)
{
btnBrowse.Text = ofd.FileName;
cboSheet.DataSource = null;
cboSheet.DataSource = TextUtils.ListSheetInExcel(ofd.FileName);
}
}
}
public override DataTableResponseMessage Execute()
{
DataTableResponseMessage rm = null;
try
{
//Using ExcelDataReader Package
rm = VerifyInputFile();
if (rm != null)
{
return(rm);
}
rm = new DataTableResponseMessage();
FileInfo fi = new FileInfo(input_file);
DataTableCollection tables;
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
using (var stream = File.Open(input_file, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet();
tables = result.Tables;
}
}
DataTable dt_template = GetDataTable();
dt_template.TableName = System.IO.Path.GetFileNameWithoutExtension(fi.FullName);
TemplateField[] fields = Fields;
int numTables = tables.Count;
for (int i = 0; i < numTables; i++)
{
var worksheet = tables[i];
string analyte_id = worksheet.Rows[2][0].ToString();
int numRows = worksheet.Rows.Count;
int numCols = worksheet.Columns.Count;
//Data row starts on Excel row 6, 5 for zero based
for (int row = 5; row < numRows; row++)
{
string fileName = worksheet.Rows[row][0].ToString().Trim();
if (String.IsNullOrWhiteSpace(fileName))
{
break;
}
string aliquot_id = worksheet.Rows[row][2].ToString();
//Measured val can contain string of 'NA' or 'NF'
string measured_val = worksheet.Rows[row][5].ToString().Trim();
double d_measured_val;
if (!Double.TryParse(measured_val, out d_measured_val))
{
d_measured_val = 0.0;
}
double dilution_factor = Convert.ToDouble(worksheet.Rows[row][40].ToString());
DateTime analysis_datetime = fi.CreationTime.Date.Add(DateTime.Parse(worksheet.Rows[row][30].ToString()).TimeOfDay);
//Area
string userDefined1 = worksheet.Rows[row][14].ToString();
//ISTD Area
string userDefined2 = worksheet.Rows[row][16].ToString();
DataRow dr = dt_template.NewRow();
dr[0] = aliquot_id;
dr[1] = analyte_id;
dr[2] = d_measured_val;
dr[4] = dilution_factor;
dr[5] = analysis_datetime;
dr[8] = userDefined1;
dr[9] = userDefined2;
dt_template.Rows.Add(dr);
}
}
rm.TemplateData = dt_template;
}
catch (Exception ex)
{
rm.LogMessage = string.Format("Processor: {0}, InputFile: {1}, Exception: {2}", name, input_file, ex.Message);
rm.ErrorMessage = string.Format("Problem executing processor {0} on input file {1}.", name, input_file);
}
return(rm);
}
/*
* PRIVATE METHODS
*/
private bool Import(string path)
{
Log.Debug("Start --> Import messages from " + path);
var extension = Path.GetExtension(path).ToLower();
var sw = new Stopwatch();
using (var stream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
sw.Start();
IExcelDataReader reader = null;
if (extension == ".xls")
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (extension == ".xlsx")
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (extension == ".csv")
{
reader = ExcelReaderFactory.CreateCsvReader(stream);
}
if (reader == null)
{
return(false);
}
_ds = reader.AsDataSet(new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
});
stream.Close();
}
var dt = _ds.Tables[0];
var errorMessagesSheetNr = _ds.Tables.IndexOf("Fehlertexte");
if (errorMessagesSheetNr == -1)
{
UpdateStatusText("Wrong file, dose not contain sheet 'Fehlertexte'!");
return(false);
}
foreach (DataRow row in dt.Rows)
{
var position = row[0] as string;
//var visiPos = row[1] as string;
// var prio = row[2] as string;
var messageNumber = row[3] as string;
// var bit = row[4] as string;
var messageText = row[5] as string;
if (!string.IsNullOrEmpty(position))
{
UpdateMessage(new Message
{
Position = position,
MessageText = messageText,
MessageNumber = messageNumber
});
}
}
var totalMessages = CounterUpdate + CounterAdd;
var filename = Path.GetFileName(path);
StatusText = "Import " + totalMessages + " messages (New:" + CounterAdd + " , Updates: " + CounterUpdate +
") from " + filename + " in " + sw.ElapsedMilliseconds + "ms";
Log.Info(StatusText);
return(true);
}
public static void FileUploadComplete(object sender, DevExpress.Web.FileUploadCompleteEventArgs e)
{
#region Variables
ba_Banco_Cuenta_List ListaBanco = new ba_Banco_Cuenta_List();
List <ba_Banco_Cuenta_Info> Lista_Banco = new List <ba_Banco_Cuenta_Info>();
ba_Banco_Cbte_List ListaCbte = new ba_Banco_Cbte_List();
List <ba_Cbte_Ban_Info> Lista_Cbte = new List <ba_Cbte_Ban_Info>();
tb_banco_Bus bus_banco = new tb_banco_Bus();
tb_sucursal_Bus bus_sucursal = new tb_sucursal_Bus();
int cont = 0;
decimal IdTransaccionSession = Convert.ToDecimal(SessionFixed.IdTransaccionSessionActual);
int IdEmpresa = Convert.ToInt32(SessionFixed.IdEmpresa);
#endregion
Stream stream = new MemoryStream(e.UploadedFile.FileBytes);
if (stream.Length > 0)
{
IExcelDataReader reader = null;
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
#region Banco
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont > 0)
{
ba_Banco_Cuenta_Info info = new ba_Banco_Cuenta_Info
{
IdEmpresa = IdEmpresa,
IdBanco = Convert.ToInt32(reader.GetValue(0)),
IdBanco_Financiero = Convert.ToInt32(reader.GetValue(1)),
ba_Tipo = Convert.ToString(reader.GetValue(2)),
ba_Num_Cuenta = Convert.ToString(reader.GetValue(3)),
ba_num_digito_cheq = Convert.ToInt32(reader.GetValue(4)),
IdCtaCble = null,
IdUsuario = SessionFixed.IdUsuario,
};
#region GetInfo
tb_banco_Info banco = bus_banco.get_info(info.IdBanco);
info.ba_descripcion = banco.ba_descripcion + " " + info.ba_Tipo + " " + info.ba_Num_Cuenta;
info.Imprimir_Solo_el_cheque = false;
Lista_Banco.Add(info);
#endregion
}
else
{
cont++;
}
}
ListaBanco.set_list(Lista_Banco, IdTransaccionSession);
#endregion
cont = 0;
//Para avanzar a la siguiente hoja de excel
reader.NextResult();
#region Cbte
while (reader.Read())
{
if (!reader.IsDBNull(0) && cont > 0)
{
ba_Cbte_Ban_Info info = new ba_Cbte_Ban_Info
{
IdEmpresa = IdEmpresa,
IdTipo_Persona = Convert.ToString(reader.GetValue(0)),
Su_Descripcion = Convert.ToString(reader.GetValue(1)),
IdBanco = Convert.ToInt32(reader.GetValue(2)),
cb_Fecha = Convert.ToDateTime(reader.GetValue(3)),
cb_Observacion = Convert.ToString(reader.GetValue(4)),
cb_Valor = Convert.ToInt32(reader.GetValue(5)),
IdUsuario = SessionFixed.IdUsuario,
};
#region GetInfo
tb_sucursal_Info sucursal = bus_sucursal.GetInfo(IdEmpresa, info.Su_Descripcion);
info.Su_Descripcion = sucursal.Su_Descripcion;
info.IdSucursal = sucursal.IdSucursal;
Lista_Cbte.Add(info);
#endregion
}
else
{
cont++;
}
}
var ListCuenta = ListaBanco.get_list(IdTransaccionSession);
var lst = (from q in ListCuenta
join c in Lista_Cbte
on q.IdBanco equals c.IdBanco
select new ba_Cbte_Ban_Info
{
IdEmpresa = c.IdEmpresa,
IdTipo_Persona = c.IdTipo_Persona,
IdSucursal = c.IdSucursal,
ba_descripcion = q.ba_descripcion,
Su_Descripcion = c.Su_Descripcion,
IdBanco = c.IdBanco,
cb_Valor = c.cb_Valor,
cb_Fecha = c.cb_Fecha,
cb_Observacion = c.cb_Observacion
}).ToList();
Lista_Cbte = lst;
ListaCbte.set_list(Lista_Cbte, IdTransaccionSession);
#endregion
}
}
private void button1_Click(object sender, EventArgs e)
{
if (String.IsNullOrEmpty(this.TxtCategory.Text.Trim()) ||
String.IsNullOrEmpty(this.TxtDataPoint.Text.Trim()) ||
String.IsNullOrEmpty(this.TxtExcel.Text.Trim()))
{
MessageBox.Show("Please fill the Excel, Category and Data Point information.");
}
else
{
DirectoryInfo diTestCaseDataPoint = CreateTestCaseDir();
DirectoryInfo diEntityDataPoint = CreateEntityDir();
try
{
string excelPath = this.TxtExcel.Text.Trim();
String OutputExcelPath = Path.Combine(diTestCaseDataPoint.FullName, "Excel", new FileInfo(excelPath).Name);
File.Copy(excelPath, OutputExcelPath, true);
int header = 3;
// 加载Excel文件
using (FileStream excelFile = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelFile);
// The result of each spreadsheet will be created in the result.Tables
excelReader.IsFirstRowAsColumnNames = true;
DataSet book = excelReader.AsDataSet();
// 数据检测
if (book.Tables.Count < 1)
{
throw new Exception("Excel文件中没有找到Sheet: " + excelPath);
}
foreach (DataTable sheet in book.Tables)
{
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet中没有数据: " + excelPath);
}
if (sheet.TableName == "Config")
{
//TODO:
}
else
{
String JsonPath = "";
if (sheet.TableName.ToLower().Contains("method") || sheet.TableName.ToLower().Contains("change"))
{
JsonPath = Path.Combine(diTestCaseDataPoint.FullName, "MockData", sheet.TableName + "_" + this.TxtDataPoint.Text.Trim() + ".json");
JsonExporter exporterForJson = new JsonExporter(sheet, header, false);
exporterForJson.SaveToFile(JsonPath, new UTF8Encoding(false));
String JavaPath = Path.Combine(diEntityDataPoint.FullName, sheet.TableName + ".java");
JavaDefineGenerator exporterForJava = new JavaDefineGenerator(sheet);
exporterForJava.SaveToFile(JavaPath, new UTF8Encoding(false), "." + this.TxtCategory.Text.Trim() + "." + this.TxtDataPoint.Text.Trim());
}
else if (sheet.TableName.ToLower().Contains("init"))
{
JsonPath = Path.Combine(diTestCaseDataPoint.FullName, "InitData", sheet.TableName + "_" + this.TxtDataPoint.Text.Trim() + ".json");
JsonExporter exporterForJson2 = new JsonExporter(sheet, header, false);
exporterForJson2.SaveToFile(JsonPath, new UTF8Encoding(false));
}
else if (sheet.TableName.ToLower().Contains("clean"))
{
JsonPath = Path.Combine(diTestCaseDataPoint.FullName, "CleanData", sheet.TableName + "_" + this.TxtDataPoint.Text.Trim() + ".json");
JsonExporter exporterForJson = new JsonExporter(sheet, header, false);
exporterForJson.SaveToFile(JsonPath, new UTF8Encoding(false));
}
else if (sheet.TableName.ToLower().Contains("delta"))
{
JsonPath = Path.Combine(diTestCaseDataPoint.FullName, "DeltaData", sheet.TableName + "_" + this.TxtDataPoint.Text.Trim() + ".json");
JsonExporter2 exporterForJson2 = new JsonExporter2(sheet, header, false);
exporterForJson2.SaveToFile(JsonPath, new UTF8Encoding(false));
}
}
}
}
MessageBox.Show("Generate completed.", "Excel2Json", MessageBoxButtons.OK);
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}
}
