private void ImportMenuItem_Click(object sender, EventArgs e)
{
int soluongHSThanhCong = 0;
int soluongGVThanhCong = 0;
int giaoVienTrung = 0;
int hocSinhTrung = 0;
DataSet ds = new DataSet();
using (OpenFileDialog openFileDialog = new OpenFileDialog()
{
ValidateNames = true, Filter = "Excel Workbook|*.xlsx|Excel Workbook 97-2003|*.xls"
})
try
{
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader;
if (openFileDialog.FilterIndex == 2)
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
reader.Close();
}
}
DataTable dataGiaoVien = ds.Tables["GiaoVien"];
DataTable dataHocSinh = ds.Tables["HocSinh"];
foreach (DataRow row in dataGiaoVien.Rows)
{
if (_giaoVienRepository.FindByCondition(gv => gv.MaGV == row["MaGV"].ToString()).SingleOrDefault() != null)
{
giaoVienTrung++;
continue;
}
else
{
NguoiDung nd = new NguoiDung()
{
TenTaiKhoan = row["TenTaiKhoan"].ToString(),
MatKhauHash = row["MatKhau"].ToString(),
Quyen = 2
};
_nguoiDungRepository.Create(nd);
_nguoiDungRepository.Update();
_giaoVienRepository.Create(new GiaoVien
{
MaGV = row["MaGV"].ToString(),
HoTen = row["HoTen"].ToString(),
NgaySinh = DateTime.Parse(row["NgaySinh"].ToString()),
DiaChi = row["DiaChi"].ToString(),
NguoiDungID = nd.IDNguoiDung
});
_giaoVienRepository.Update();
soluongGVThanhCong++;
}
}
foreach (DataRow row in dataHocSinh.Rows)
{
if (_hocSinhRepository.FindByCondition(nd => nd.MaHS == row["MaHS"].ToString()).SingleOrDefault() != null)
{
hocSinhTrung++;
continue;
}
else
{
NguoiDung nd = new NguoiDung()
{
TenTaiKhoan = row["TenTaiKhoan"].ToString(),
MatKhauHash = row["MatKhau"].ToString(),
Quyen = 3
};
_nguoiDungRepository.Create(nd);
_nguoiDungRepository.Update();
_hocSinhRepository.Create(new HocSinh
{
MaHS = row["MaHS"].ToString(),
HoTen = row["HoTen"].ToString(),
NgaySinh = DateTime.Parse(row["NgaySinh"].ToString()),
DiaChi = row["DiaChi"].ToString(),
Lop = row["Lop"].ToString(),
NguoiDungID = nd.IDNguoiDung
});
_hocSinhRepository.Update();
soluongHSThanhCong++;
}
}
}
catch (Exception excep)
{
MessageBox.Show(excep.Message, "Lỗi file", MessageBoxButtons.OK, MessageBoxIcon.Information);
MessageBox.Show($"Số học sinh thêm thành công: {soluongHSThanhCong}\n" +
$"Số giáo viên thêm thành công: {soluongGVThanhCong}\n" +
$"Số học sinh bị trùng: {hocSinhTrung}\n" +
$"Số giáo viên bị trùng: {giaoVienTrung}\n");
return;
}
MessageBox.Show($"Số học sinh thêm thành công: {soluongHSThanhCong}\n" +
$"Số giáo viên thêm thành công: {soluongGVThanhCong}\n" +
$"Số học sinh bị trùng: {hocSinhTrung}\n" +
$"Số giáo viên bị trùng: {giaoVienTrung}\n");
ReLoad();
}
public async Task <IHttpActionResult> Upload()
{
try
{
var UserId = ((ClaimsIdentity)User.Identity).Claims.FirstOrDefault(c => c.Type.Equals(ClaimTypes.NameIdentifier)).Value;
HttpFileCollection files = HttpContext.Current.Request.Files;
Stream FileStream = null;
IExcelDataReader reader = null;
System.Web.HttpPostedFile Inputfile = files[0];
FileStream = Inputfile.InputStream;
string message = "";
DataSet dsexcelRecords = new DataSet();
List <tblBrand> objBrand = new List <tblBrand>();
BaseModel baseModel = new BaseModel();
//using (DbContextTransaction transaction = entities.Database.BeginTransaction())
//{
if (Inputfile != null && FileStream != null)
{
if (Inputfile.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(FileStream);
}
else if (Inputfile.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(FileStream);
}
else
{
message = "The file format is not supported.";
}
dsexcelRecords = reader.AsDataSet();
reader.Close();
if (dsexcelRecords != null && dsexcelRecords.Tables.Count > 0)
{
DataTable dtBrandRecords = dsexcelRecords.Tables[0];
for (int i = 0; i < dtBrandRecords.Rows.Count; i++)
{
if (i > 0)
{
tblBrand objB = new tblBrand();
objB.BrandName = Convert.ToString(dtBrandRecords.Rows[i][0]);
if (objB.BrandName == "")
{
baseModel.success = false;
baseModel.message = "Brand Name is not exist!";
baseModel.code = 500;
return(Ok(baseModel));
}
var getBrandName = entities.tblBrands.Where(x => x.BrandName.ToLower() == objB.BrandName.ToLower()).FirstOrDefault();
if (getBrandName != null)
{
baseModel.success = false;
baseModel.message = "Brand Name " + objB.BrandName + " already exist!";
baseModel.code = 500;
return(Ok(baseModel));
}
objB.BrandDescription = Convert.ToString(dtBrandRecords.Rows[i][1]);
objB.CreatedBy = Convert.ToInt32(UserId);
objB.CreatedOn = DateTime.Now;
entities.tblBrands.Add(objB);
}
}
entities.SaveChanges();
}
}
baseModel.success = true;
baseModel.message = "File upload successfully.";
baseModel.code = 200;
return(Ok(baseModel));
}
catch (Exception ex)
{
responseData.message = ex.Message != null?ex.Message.ToString() : "server error";
return(Ok(responseData));
}
}
private static void GetExcelData(string inputPath)
{
FileStream stream = null;
try
{
stream = File.Open(inputPath, FileMode.Open, FileAccess.Read);
}
catch
{
EditorUtility.DisplayDialog("注意！！！", "\n请关闭 " + inputPath + " 后再导表！", "确定");
EditorUtility.ClearProgressBar();
throw new Exception("请关闭 " + inputPath + " 后再导表！");
}
IExcelDataReader excelReader = null;
if (inputPath.EndsWith(".xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (inputPath.EndsWith(".xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (!(inputPath.EndsWith(".xls") | inputPath.EndsWith(".xlsx")))
{
ProgressBar.HideBarWithFailInfo("\n无法读取的文件: " + inputPath);
EditorUtility.ClearProgressBar();
throw new Exception("无法读取的文件: " + inputPath);
}
else
{
do
{
// sheet name
string className = excelReader.Name;
string[] types = null; //数据类型
string[] names = null; //字段名
List <ConfigData[]> dataList = new List <ConfigData[]>();
int index = 1;
//开始读取
while (excelReader.Read())
{
//这里读取的是每一行的数据
string[] datas = new string[excelReader.FieldCount];
for (int j = 0; j < excelReader.FieldCount; ++j)
{
object temp = excelReader.GetValue(j);
if (temp != null)
{
datas[j] = temp.ToString();
}
}
//空行不处理
if (datas.Length == 0 || string.IsNullOrEmpty(datas[0]))
{
++index;
continue;
}
//第4行表示类型
if (index == 2)
{
types = datas;
}
//第5行表示变量名
else if (index == 3)
{
names = datas;
}
//后面的表示数据
else if (index > 3)
{
//把读取的数据和数据类型,名称保存起来,后面用来动态生成类
List <ConfigData> configDataList = new List <ConfigData>();
for (int j = 0; j < datas.Length; ++j)
{
ConfigData data = new ConfigData();
data.Type = types[j];
data.Name = names[j];
data.Data = datas[j];
if (string.IsNullOrEmpty(data.Type) || string.IsNullOrEmpty(data.Data))
{
continue; //空的数据不处理
}
configDataList.Add(data);
}
dataList.Add(configDataList.ToArray());
}
++index;
}
if (string.IsNullOrEmpty(className))
{
ProgressBar.HideBarWithFailInfo("\n空的类名（excel页签名）, 路径: " + inputPath);
throw new Exception("空的类名（excel页签名）, 路径: " + inputPath);
}
if (names != null && types != null)
{
if (className != "表格配置说明")
{
//根据刚才的数据来生成C#脚本
ScriptGenerator generator = new ScriptGenerator(inputPath, className, names, types);
//所有生成的类的代码最终保存在这个链表中
codeList.Add(generator.Generate());
if (dataDict.ContainsKey(className))
{
ProgressBar.HideBarWithFailInfo("\n类名重复:" + className + " ,路径: " + inputPath);
throw new Exception("类名重复: " + className + " ,路径: " + inputPath);
}
else
{
dataDict.Add(className, dataList);
}
}
}
}while (excelReader.NextResult());//excelReader.NextResult() Excel表下一个sheet页有没有数据
}
stream.Dispose();
stream.Close();
}
//public static DataSet Parse(string fileName)
//{
// string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
// DataSet data = new DataSet();
// foreach (var sheetName in GetExcelSheetNames(connectionString))
// {
// using (OleDbConnection con = new OleDbConnection(connectionString))
// {
// var dataTable = new DataTable();
// string query = string.Format("SELECT * FROM [{0}]", sheetName);
// con.Open();
// OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
// adapter.Fill(dataTable);
// data.Tables.Add(dataTable);
// }
// }
// return data;
//}
//public static string[] GetExcelSheetNames(string connectionString)
//{
// OleDbConnection con = null;
// DataTable dt = null;
// con = new OleDbConnection(connectionString);
// con.Open();
// dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
// if (dt == null)
// {
// return null;
// }
// String[] excelSheetNames = new String[dt.Rows.Count];
// int i = 0;
// foreach (DataRow row in dt.Rows)
// {
// excelSheetNames[i] = row["TABLE_NAME"].ToString();
// i++;
// }
// return excelSheetNames;
//}
public ExcelExtensionReponse Test(string fileName)
{
ExcelExtensionReponse excelExtensionReponse = new ExcelExtensionReponse();
string JSONString = string.Empty;
IExcelDataReader excelReader;
try
{
FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
if (fileName.Contains("xlsx"))
{
excelReader = ExcelReaderFactory.CreateReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
excelReader.Close();
bool getDesiredColumnExistence = result.Tables[0].Columns.Contains("package no");
if (getDesiredColumnExistence)
{
var regexItem = new Regex("[^0-9a-zA-Z]+");
for (int i = 0; i < result.Tables[0].Columns.Count; i++)
{
if (regexItem.IsMatch(result.Tables[0].Columns[i].ColumnName.ToString()))
{
result.Tables[0].Columns[i].ColumnName = "S_" + Regex.Replace(result.Tables[0].Columns[i].ColumnName, @"[^0-9a-zA-Z]+", "");
}
}
result.AcceptChanges();
JSONString = JsonConvert.SerializeObject(result.Tables[0]);
excelExtensionReponse.ExcelExtensionReponseData = JSONString;
excelExtensionReponse.success = true;
}
else
{
excelExtensionReponse.exception = new ArgumentException("Required Column 'package no' is not found");
}
}
catch (Exception ex)
{
excelExtensionReponse.exception = ex;
}
return(excelExtensionReponse);
}
public QuanLyCauHoi()
{
InitializeComponent();
loadCBCauHoi();
loadDGVDapAn();
set();
btnThemCauHoi.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
if (qlttn.CauHoiQL.Where(ch => ch.NoiDung.ToLower() == txtCauHoi.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.CauHoiQL.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.CauHoiQL.InsertOnSubmit(new CauHoi()
{
NoiDung = txtCauHoi.Text,
CapDo = qlttn.CapDoQL.Where(cd => cd.maCD == int.Parse(cbCapDo.SelectedValue.ToString())).SingleOrDefault()
});
qlttn.SubmitChanges();
}
loadCBCauHoi();
cbDSCH.SelectedItem = cbDSCH.Items[cbDSCH.Items.Count - 1];
txtDapAn.Focus();
};
btnXoaCauHoi.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
if (qlttn.CauHoiQL.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.CauHoiQL
.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString()))
.FirstOrDefault();
qlttn.DapAnQL.DeleteAllOnSubmit(cauHoiHienTai.DapAns);
qlttn.CauHoiQL.DeleteOnSubmit(cauHoiHienTai);
qlttn.SubmitChanges();
}
loadCBCauHoi();
};
btnSuaCauHoi.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
if (qlttn.CauHoiQL.Where(ch => ch.NoiDung.ToLower() == txtCauHoi.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.CauHoiQL
.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString()))
.FirstOrDefault();
cauHoiHienTai.NoiDung = txtCauHoi.Text;
cauHoiHienTai.CapDo = qlttn.CapDoQL.Where(cd => cd.maCD == int.Parse(cbCapDo.SelectedValue.ToString())).FirstOrDefault();
qlttn.SubmitChanges();
}
loadCBCauHoi();
cbDSCH.SelectedItem = cbDSCH.Items[cbDSCH.Items.Count - 1];
};
btnThemDapAn.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
var cauHoiHienTai = qlttn.CauHoiQL
.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString()))
.FirstOrDefault();
if (cauHoiHienTai.DapAns.Where(da => da.NoiDung.ToLower() == txtDapAn.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.DapAnQL.InsertOnSubmit(new DapAn()
{
NoiDung = txtDapAn.Text,
DungSai = ckbDungSai.Checked,
//DungSai = bool.Parse(txtDungSai.Text),
CauHoi = qlttn.CauHoiQL.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString())).SingleOrDefault()
});
qlttn.SubmitChanges();
}
loadDGVDapAn();
dgvDSDA.Rows[dgvDSDA.Rows.GetLastRow(DataGridViewElementStates.Displayed)].Selected = true;
txtDapAn.Focus();
};
btnXoaDapAn.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
var cauHoiHienTai = qlttn.CauHoiQL
.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.DapAnQL.DeleteOnSubmit(cauHoiHienTai.DapAns.Where(da => da.maDA == int.Parse(dgvDSDA.SelectedRows[0].Cells["maDA"].Value.ToString())).FirstOrDefault());
qlttn.SubmitChanges();
}
loadDGVDapAn();
};
btnSuaDapAn.Click += (s, e) =>
{
using (var qlttn = new QLTTNDataContext())
{
var cauHoiHienTai = qlttn.CauHoiQL
.Where(ch => ch.maCH == int.Parse(cbDSCH.SelectedValue.ToString()))
.FirstOrDefault();
if (cauHoiHienTai.DapAns.Where(da => da.NoiDung.ToLower() == txtDapAn.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 = txtDapAn.Text;
dapAnHienTai.DungSai = ckbDungSai.Checked;
//dapAnHienTai.DungSai = bool.Parse(txtDungSai.Text);
qlttn.SubmitChanges();
}
loadDGVDapAn();
txtDapAn.Focus();
};
btnImport.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.CauHoiQL.Where(ch => ch.NoiDung.ToLower() == cauHoiTmp.NoiDung.ToLower()).Count() == 0)
{
qlttn.CauHoiQL.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.CauHoiQL.Max(ch => ch.maCH),
DungSai = rowDapAn["DungSai"].ToString().ToLower() == "true" ? true : false
};
qlttn.DapAnQL.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");
}
}
}
}
};
btnExport.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.CauHoiQL.ToList();
List <DapAn> das = qlttn.DapAnQL.ToList();
for (int i = 0; i < qlttn.CauHoiQL.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);
}
}
};
}
protected void UploadButton_Click(object sender, EventArgs e)
{
int tenantId = (int)HttpContext.Current.Session["TenantID"];
if (ExcelFileUpload.HasFile)
{
using (var stream = ExcelFileUpload.PostedFile.InputStream)
{
IExcelDataReader excelReader = null;
try
{
if (ExcelFileUpload.FileName.EndsWith(".xlsx"))
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else if (ExcelFileUpload.FileName.EndsWith(".xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
this.Log().Info("Importing uploaded Excel school crosswalk: {0}",
ExcelFileUpload.FileName);
if (excelReader == null)
{
throw new Exception("Could not parse Excel file, not .xls or .xlsx");
}
excelReader.IsFirstRowAsColumnNames = true;
var schoolMap = new Dictionary <string, int>();
var schoolTypeMap = new Dictionary <string, int>();
var districtMap = new Dictionary <string, int>();
var codeTypes = DAL.CodeType.GetAll().Tables[0];
var schoolCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'School'")
.First()["CTID"];
var schoolTypeCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'School Type'")
.First()["CTID"];
var districtCodeTypeId = (int)codeTypes
.Select("CodeTypeName = 'School District'")
.First()["CTID"];
var codes = DAL.Codes.GetAll().Tables[0];
foreach (DataRow codeRow in codes.Rows)
{
int ctid = (int)codeRow["CTID"];
if (ctid == schoolCodeTypeId)
{
schoolMap.Add(((string)codeRow["Code"]).ToLower(),
(int)codeRow["CID"]);
}
else if (ctid == schoolTypeCodeTypeId)
{
schoolTypeMap.Add(((string)codeRow["Code"]).ToLower(),
(int)codeRow["CID"]);
}
else if (ctid == districtCodeTypeId)
{
districtMap.Add(((string)codeRow["Code"]).ToLower(),
(int)codeRow["CID"]);
}
}
int?recordCount = null;
var problems = new List <string>();
while (excelReader.Read())
{
// skip column headings
if (recordCount == null)
{
recordCount = 0;
continue;
}
try
{
string schoolName = excelReader.GetString(0);
int? schoolId = null;
if (schoolMap.ContainsKey(schoolName.Trim().ToLower()))
{
schoolId = schoolMap[schoolName.Trim().ToLower()];
}
string schoolTypeName = excelReader.GetString(1);
int? schoolTypeId = null;
if (schoolTypeMap.ContainsKey(schoolTypeName.Trim().ToLower()))
{
schoolTypeId = schoolTypeMap[schoolTypeName.Trim().ToLower()];
}
string district = excelReader.GetString(2);
int? districtId = null;
if (districtMap.ContainsKey(district.Trim().ToLower()))
{
districtId = districtMap[district.Trim().ToLower()];
}
int?minGrade = null;
int?maxGrade = null;
int?minAge = null;
int?maxAge = null;
if (excelReader.FieldCount >= 4)
{
// minGrade
int convertedValue;
if (int.TryParse(excelReader.GetString(3), out convertedValue))
{
maxGrade = convertedValue;
}
}
if (excelReader.FieldCount >= 5)
{
// maxGrade
int convertedValue;
if (int.TryParse(excelReader.GetString(4), out convertedValue))
{
maxGrade = convertedValue;
}
}
if (excelReader.FieldCount >= 6)
{
// minAge
int convertedValue;
if (int.TryParse(excelReader.GetString(5), out convertedValue))
{
maxGrade = convertedValue;
}
}
if (excelReader.FieldCount >= 7)
{
// maxAge
int convertedValue;
if (int.TryParse(excelReader.GetString(6), out convertedValue))
{
maxAge = convertedValue;
}
}
if (schoolId == null)
{
// insert branch
schoolId = DAL.Codes.Insert(new DAL.Codes
{
CTID = schoolCodeTypeId,
Code = schoolName.Trim(),
Description = schoolName.Trim(),
TenID = tenantId
});
schoolMap.Add(schoolName.Trim().ToLower(), (int)schoolId);
}
if (schoolTypeId == null)
{
// insert branch
schoolTypeId = DAL.Codes.Insert(new DAL.Codes
{
CTID = schoolTypeCodeTypeId,
Code = schoolTypeName.Trim(),
Description = schoolTypeName.Trim(),
TenID = tenantId
});
schoolTypeMap.Add(schoolTypeName.Trim().ToLower(), (int)schoolTypeId);
}
if (districtId == null)
{
// insert district
districtId = DAL.Codes.Insert(new DAL.Codes
{
CTID = districtCodeTypeId,
Code = district.Trim(),
Description = district.Trim(),
TenID = tenantId
});
districtMap.Add(district.Trim().ToLower(), (int)districtId);
}
// insert crosswalk
var crosswalk = new DAL.SchoolCrosswalk
{
SchoolID = (int)schoolId,
SchTypeID = (int)schoolTypeId,
DistrictID = (int)districtId,
City = string.Empty,
MinGrade = minGrade ?? 0,
MaxGrade = maxGrade ?? 0,
MinAge = minAge ?? 0,
MaxAge = maxAge ?? 0
};
DAL.SchoolCrosswalk.Insert(crosswalk);
recordCount++;
}
catch (Exception ex)
{
// couldn't import this record
string problem = string.Format("Unable to import record {0}: {1}",
recordCount,
ex.Message);
this.Log().Info(problem);
problems.Add(problem);
}
}
var result = new StringBuilder(string.Format("Imported {0} records and encountered {1} errors in: {2}",
recordCount,
problems.Count,
ExcelFileUpload.FileName));
this.Log().Info(result.ToString());
foreach (var problem in problems)
{
result.Append("<br />");
result.AppendLine(problem);
}
PageMessage = result.ToString();
}
catch (Exception ex)
{
string result = string.Format("Error reading Excel file for school crosswalk: {0} - {1}",
ex.Message,
ex.StackTrace);
this.Log().Error(result);
PageError = result;
}
finally
{
if (excelReader != null)
{
if (!excelReader.IsClosed)
{
excelReader.Close();
}
excelReader.Dispose();
}
}
}
}
odsData.DataBind();
gv.DataSourceID = "odsData";
gv.DataBind();
}
public async Task <ResponseStatus> Upload()
{
ResponseStatus status = new ResponseStatus();
string message = "";
HttpResponseMessage result = null;
var httpRequest = HttpContext.Current.Request;
if (httpRequest.Files.Count > 0)
{
HttpPostedFile file = httpRequest.Files[0];
string vendorId = Convert.ToString(httpRequest.Form["vendorid"]);
Stream stream = file.InputStream;
IExcelDataReader reader = null;
if (file.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
message = "This file format is not supported";
}
DataSet excelRecords = reader.AsDataSet();
reader.Close();
var finalRecords = excelRecords.Tables[0];
for (int i = 0; i < finalRecords.Rows.Count; i++)
{
var mobilenos = finalRecords.Rows[i][0].ToString();
var mobileno = appDbContex.promotionalMobileNos.Where(a => a.mobileno == mobilenos && a.vendorId == vendorId && a.deleted == false).FirstOrDefault();
if (mobileno == null)
{
var guId = Guid.NewGuid();
PromotionalMobileNo promotionalMobileNo = new PromotionalMobileNo
{
id = guId.ToString(),
vendorId = vendorId,
mobileno = finalRecords.Rows[i][0].ToString(),
deleted = false
};
appDbContex.promotionalMobileNos.Add(promotionalMobileNo);
await appDbContex.SaveChangesAsync();
}
}
status.status = true;
status.message = "mobile no upload successfully";
return(status);
}
else
{
result = Request.CreateResponse(HttpStatusCode.BadRequest);
status.status = true;
status.message = result.ToString();
return(status);
}
}
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFile = new OpenFileDialog();
openFile.Filter = "Excell files (*.xls, *.xlsx) | *.xls; *.xlsx";
if (openFile.ShowDialog() == DialogResult.OK)
{
try
{
string FilePath = openFile.FileName;
FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader;
//
string[] FileType = FilePath.Split('.');
if (FileType[FileType.Length - 1].ToUpper() == "XLS")
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (FileType[FileType.Length - 1].ToUpper() == "XLSX")
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
MessageBox.Show("Wrong excell Fomat");
return;
}
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
DataTable dt = result.Tables[0];
//5. Data Reader methods
//while (excelReader.Read())
//{
// //excelReader.GetInt32(0);
//}
dataGridView1.Visible = true;
dataGridView1.DataSource = dt;
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
// save to DB
string SQLcmd = "";
string TotalSQLcmd = "";
string Vender_ID = "";
string Customer_ID = "";
try
{
for (int i = 0; i < dt.Rows.Count; i++)
{
Vender_ID = dt.Rows[i][0].ToString();
Customer_ID = dt.Rows[i][1].ToString();
if (cbtype.Text == "Solder Paste")
{
SQLcmd = "insert into SOLDER_RULE (PART_INTER,PART_NUMBER,MODEL,CUSTOMER,TIME_THAW,TIME_SPIN,TIME_TUNRBACK,TIME_OUTSIDE,SOLDER_RULE.CHECK) values ('" + dt.Rows[i]["PART_INTER"].ToString() + "','" + dt.Rows[i]["PART_NUMBER"].ToString() + "','" + dt.Rows[i]["MODEL"].ToString() + "','" + dt.Rows[i]["CUSTOMER"].ToString() + "'," + dt.Rows[i]["TIME_THAW"].ToString() + "," + dt.Rows[i]["TIME_SPIN"].ToString() + "," + dt.Rows[i]["TIME_TUNRBACK"].ToString() + "," + dt.Rows[i]["TIME_OUTSIDE"].ToString() + "," + dt.Rows[i]["CHECK"].ToString() + " ) on duplicate key update TIME_THAW=" + dt.Rows[i]["TIME_THAW"].ToString() + ",TIME_SPIN=" + dt.Rows[i]["TIME_SPIN"].ToString() + ",TIME_TUNRBACK=" + dt.Rows[i]["TIME_TUNRBACK"].ToString() + ",TIME_OUTSIDE=" + dt.Rows[i]["TIME_OUTSIDE"].ToString() + ", SOLDER_RULE.CHECK=" + dt.Rows[i]["CHECK"].ToString() + " , MODEL='" + dt.Rows[i]["MODEL"].ToString() + "',PART_NUMBER='" + dt.Rows[i]["PART_NUMBER"].ToString() + "',CUSTOMER='" + dt.Rows[i]["CUSTOMER"].ToString() + "';";
}
else
{
SQLcmd = "insert into STENCIL_RULE (CUSTOMER,TIME_USE,STENCIL_RULE.CHECK,PART_INTER,PART_NUMBER,MODEL) values('" + dt.Rows[i]["CUSTOMER"].ToString() + "'," + dt.Rows[i]["TIME_USE"].ToString() + "," + dt.Rows[i]["CHECK"].ToString() + ",'" + dt.Rows[i]["PART_INTER"].ToString() + "','" + dt.Rows[i]["PART_NUMBER"].ToString() + "','" + dt.Rows[i]["MODEL"].ToString() + "') on duplicate key update TIME_USE=" + dt.Rows[i]["TIME_USE"].ToString() + ", STENCIL_RULE.CHECK=" + dt.Rows[i]["CHECK"].ToString() + ", MODEL='" + dt.Rows[i]["MODEL"].ToString() + "',PART_NUMBER='" + dt.Rows[i]["PART_NUMBER"].ToString() + "',CUSTOMER='" + dt.Rows[i]["CUSTOMER"].ToString() + "';";
}
//
//
TotalSQLcmd += SQLcmd;
SQLcmd = "";
}
//
if (sqlconnect.InsertDataMySQL(TotalSQLcmd))
{
MessageBox.Show("Import data complete");
}
else
{
MessageBox.Show("Import data Fail");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
public RedirectResult UploadExcel(HttpPostedFileBase file, int?teacherId, DateTime startDate, DateTime endDate)
{
var teacher = Data.Teachers.Find(teacherId);
if (teacher == null || startDate >= endDate)
{
return(RedirectPermanent("/#/Error"));
}
var extension = Path.GetExtension(file.FileName);
IExcelDataReader excelReader;
if (extension == ".xsl")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(file.InputStream);
}
else if (extension == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(file.InputStream);
}
else
{
return(RedirectPermanent("/#/Error"));
}
excelReader.IsFirstRowAsColumnNames = true;
DataSet data = excelReader.AsDataSet();
DataTable table = data.Tables[0];
string[] daysOfWeek = new string[7] {
"Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
};
Dictionary <string, List <Shedule> > days = new Dictionary <string, List <Shedule> >();
days.Add("Monday", new List <Shedule>());
days.Add("Tuesday", new List <Shedule>());
days.Add("Wednesday", new List <Shedule>());
days.Add("Thursday", new List <Shedule>());
days.Add("Friday", new List <Shedule>());
days.Add("Saturday", new List <Shedule>());
days.Add("Sunday", new List <Shedule>());
for (int i = 0; i < table.Rows.Count; i++)
{
for (int j = 0; j < table.Rows[i].ItemArray.Count(); j++)
{
int currentDay = j / 4;
int currentItemId = j % 4;
var currentItem = table.Rows[i].ItemArray[j];
if (string.IsNullOrEmpty(currentItem.ToString()))
{
continue;
}
if (currentItemId == 0)
{
days[daysOfWeek[currentDay]].Add(new Shedule());
var currentShedule = days[daysOfWeek[currentDay]];
var room = Data.Rooms.Find(currentItem.ToString());
if (room == null)
{
room = new Room(currentItem.ToString());
this.Data.Rooms.Add(room);
this.Data.Rooms.SaveChanges();
}
currentShedule[currentShedule.Count - 1].Room = room;
}
if (currentItemId == 1)
{
var currentShedule = days[daysOfWeek[currentDay]];
currentShedule[currentShedule.Count - 1].Message = currentItem.ToString();
}
if (currentItemId == 2)
{
var currentShedule = days[daysOfWeek[currentDay]];
currentShedule[currentShedule.Count - 1].StartDate = DateTime.Parse(currentItem.ToString());
}
if (currentItemId == 3)
{
var currentShedule = days[daysOfWeek[currentDay]];
currentShedule[currentShedule.Count - 1].EndDate = DateTime.Parse(currentItem.ToString());
}
}
}
var currentDate = startDate;
for (int i = 0; i < i + 1; i++)
{
if (currentDate >= endDate)
{
break;
}
var weeklyShedules = days[currentDate.DayOfWeek.ToString()];
for (int j = 0; j < weeklyShedules.Count; j++)
{
var currentShedule = new Shedule()
{
TeacherId = teacher.Id,
Teacher = teacher,
Room = weeklyShedules[j].Room,
StartDate = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, weeklyShedules[j].StartDate.Hour, weeklyShedules[j].StartDate.Minute, weeklyShedules[j].StartDate.Second),
EndDate = new DateTime(currentDate.Year, currentDate.Month, currentDate.Day, weeklyShedules[j].EndDate.Hour, weeklyShedules[j].EndDate.Minute, weeklyShedules[j].EndDate.Second),
Message = weeklyShedules[j].Message
};
teacher.Shedules.Add(currentShedule);
}
currentDate = currentDate.AddDays(1);
}
this.Data.Teachers.SaveChanges();
return(RedirectPermanent("/#/Excel/" + teacherId));
}
public IHttpActionResult DstvCodeUpload()
{
var uploadfile = HttpContext.Current.Request.Files.Count > 0 ? HttpContext.Current.Request.Files[0] : null;
var date_time = DateTime.UtcNow;
if (uploadfile == null || uploadfile.ContentLength < 0)
{
return(BadRequest("Please upload your file"));
}
//ExcelDataReader works on binary excel file
Stream stream = uploadfile.InputStream;
//We need to written the Interface.
IExcelDataReader reader = null;
if (uploadfile.FileName.EndsWith(".xls"))
{
//reads the excel file with .xls extension
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (uploadfile.FileName.EndsWith(".xlsx"))
{
//reads excel file with .xlsx extension
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
//Shows error if uploaded file is not Excel file
return(BadRequest("This file format is not supported"));
}
//treats the first row of excel file as Column Names
reader.IsFirstRowAsColumnNames = true;
//Adding reader data to DataSet()
DataSet result = reader.AsDataSet();
reader.Close();
//Sending result data to View
var response = result.Tables[0];
var latestupload = new List <Promo>();
var rowCount = response.Rows.Count;
for (int i = 0; i < rowCount; i++)
{
var rowFirstColumn = response.Rows[i][0];
var rowSecondColumn = response.Rows[i][1];
//var rowThirdColumn = response.Rows[i][1];
var promo = new Promo
{
PromoName = rowFirstColumn.ToString(), //month
Code = rowSecondColumn.ToString(),
createdAt = date_time,
updatedAt = date_time
};
_context.Promos.Add(promo);
_context.SaveChanges();
latestupload.Add(promo);
}
return(Ok(latestupload));
}
public async Task <OperationStatus> UpdateStatusFromExcel(string filename,
CancellationToken token,
IProgress <OperationStatus> progress = null)
{
var requestingUser = GetClaimId(ClaimType.UserId);
if (HasPermission(Permission.ManageVendorCodes))
{
var sw = new Stopwatch();
sw.Start();
token.Register(() =>
{
string duration = "";
if (sw != null && sw.Elapsed != null)
{
duration = $" after {((TimeSpan)sw.Elapsed).TotalSeconds.ToString("N2")} seconds";
}
_logger.LogWarning($"Import of {filename} for user {requestingUser} was cancelled{duration}.");
});
string fullPath = Path.Combine(Path.GetTempPath(), filename);
if (!File.Exists(fullPath))
{
_logger.LogError($"Could not find {fullPath}");
return(new OperationStatus
{
PercentComplete = 0,
Status = "Could not find the import file.",
Error = true,
Complete = true
});
}
try
{
using (var stream = new FileStream(fullPath, FileMode.Open))
{
int couponColumnId = 0;
int orderDateColumnId = 0;
int shipDateColumnId = 0;
var issues = new List <string>();
int row = 0;
int totalRows = 0;
int updated = 0;
int alreadyCurrent = 0;
using (var excelReader = ExcelReaderFactory.CreateBinaryReader(stream))
{
while (excelReader.Read())
{
row++;
}
totalRows = row;
row = 0;
excelReader.Reset();
while (excelReader.Read())
{
row++;
if (row % 10 == 0)
{
progress.Report(new OperationStatus
{
PercentComplete = row * 100 / totalRows,
Status = $"Processing row {row}/{totalRows}...",
Error = false
});
}
if (row == 1)
{
progress.Report(new OperationStatus
{
PercentComplete = 1,
Status = $"Processing row {row}/{totalRows}...",
Error = false
});
for (int i = 0; i < excelReader.FieldCount; i++)
{
switch (excelReader.GetString(i).Trim() ?? $"Column{i}")
{
case CouponRowHeading:
couponColumnId = i;
break;
case OrderDateRowHeading:
orderDateColumnId = i;
break;
case ShipDateRowHeading:
shipDateColumnId = i;
break;
}
}
}
else
{
string coupon = null;
DateTime?orderDate = null;
DateTime?shipDate = null;
try
{
coupon = excelReader.GetString(couponColumnId);
}
catch (Exception ex)
{
_logger.LogError($"Parse error on code, row {row}: {ex.Message}");
issues.Add($"Issue reading code on line {row}: {ex.Message}");
}
try
{
orderDate = excelReader.GetDateTime(orderDateColumnId);
}
catch (Exception ex)
{
_logger.LogError($"Parse error on order date, row {row}: {ex.Message}");
issues.Add($"Issue reading order date on row {row}: {ex.Message}");
}
try
{
shipDate = excelReader.GetDateTime(shipDateColumnId);
}
catch (Exception ex)
{
_logger.LogError($"Parse error on ship date, row {row}: {ex.Message}");
issues.Add($"Issue reading ship date on row {row}: {ex.Message}");
}
if (!string.IsNullOrEmpty(coupon) &&
(orderDate != null && shipDate != null))
{
var code = await _vendorCodeRepository.GetByCode(coupon);
if (code == null)
{
_logger.LogError($"File contained code {coupon} which was not found in the database");
issues.Add($"Uploaded file contained code <code>{coupon}</code> which couldn't be found in the database.");
}
else
{
if (orderDate == code.OrderDate && shipDate == code.ShipDate)
{
alreadyCurrent++;
}
else
{
code.IsUsed = true;
if (orderDate != null)
{
code.OrderDate = orderDate;
}
if (shipDate != null)
{
code.ShipDate = shipDate;
}
await _vendorCodeRepository.UpdateSaveNoAuditAsync(code);
updated++;
}
}
}
}
if (token.IsCancellationRequested)
{
break;
}
}
if (!excelReader.IsClosed)
{
excelReader.Close();
}
}
if (token.IsCancellationRequested)
{
return(new OperationStatus
{
PercentComplete = 100,
Status = $"Operation cancelled at row {row}."
});
}
var sb = new StringBuilder("<strong>Import complete</strong>");
if (updated > 0)
{
sb.Append($": {updated} records were updated");
}
if (alreadyCurrent > 0)
{
if (updated > 0)
{
sb.Append(", ");
}
else
{
sb.Append(": ");
}
sb.Append($"{alreadyCurrent} records were already current");
}
sb.Append(".");
if (issues.Count > 0)
{
_logger.LogInformation($"Import complete with issues: {sb.ToString()}");
sb.Append(" Issues detected:<ul>");
foreach (string issue in issues)
{
sb.Append($"<li>{issue}</li>");
}
sb.Append("</ul>");
return(new OperationStatus
{
PercentComplete = 100,
Status = sb.ToString(),
Error = true
});
}
else
{
_logger.LogInformation(sb.ToString());
return(new OperationStatus
{
PercentComplete = 100,
Status = sb.ToString(),
});
}
}
}
finally
{
File.Delete(fullPath);
}
}
else
{
_logger.LogError($"User {requestingUser} doesn't have permission to view all reporting.");
return(new OperationStatus
{
PercentComplete = 0,
Status = "Permission denied.",
Error = true,
Complete = true
});
}
}
private void dt_load()
{
DataSet result;
FileStream fs = File.Open(@"\\172.19.3.129\Program\LN Programs\!!!!SAVE\hh\kt2.xls", FileMode.Open, FileAccess.Read);
IExcelDataReader rd;
rd = ExcelReaderFactory.CreateBinaryReader(fs); //read
rd.IsFirstRowAsColumnNames = true;
result = rd.AsDataSet();
dataGridView1.DataSource = result.Tables[0];
dataGridView1.Columns["NAME"].Frozen = true;
rd.Close();
// TOPTEN
var rcount = dataGridView1.Rows.Count;
for (int irow = 0; irow < rcount; irow++)
{
var rankrow = dataGridView1.Rows[irow].Cells[40].Value;
if (rankrow.ToString() == "1")
{
stt1.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
if (rankrow.ToString() == "2")
{
stt2.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
if (rankrow.ToString() == "3")
{
stt3.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
if (rankrow.ToString() == "4")
{
stt4.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
if (rankrow.ToString() == "5")
{
stt5.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
if (rankrow.ToString() == "6")
{
stt6.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
if (rankrow.ToString() == "7")
{
stt7.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
if (rankrow.ToString() == "8")
{
stt8.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
if (rankrow.ToString() == "9")
{
stt9.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
if (rankrow.ToString() == "10")
{
stt10.Text = dataGridView1.Rows[irow].Cells[1].Value.ToString();
}
}
// END Topten
}
private static void LoadDataBySheetName(string fileName, string sheetName, TestDataType type)
{
FileStream stream = null;
IExcelDataReader excelReader = null;
try
{
FileInfo fileInfo = new FileInfo(fileName);
var ext = fileInfo.Extension;
stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
if (ext.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (ext.Equals(".csv", StringComparison.CurrentCultureIgnoreCase))
{
excelReader = ExcelReaderFactory.CreateCsvReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
var rows = excelReader.AsDataSet().Tables[sheetName].Rows;
//==test plan
if (type == TestDataType.TestPlan)
{
for (int i = 0; i < rows.Count; i++)
{
var key = rows[i].ItemArray.GetValue(0).ToString();
var value = rows[i].ItemArray.GetValue(1).ToString();
TestPlanData.Add(new TestDataModel {
Key = key, Value = value
});
}
return;
}
else if (type == TestDataType.Share) //==share data --> skip the 1st row for header
{
for (int i = 1; i < rows.Count; i++)
{
var key = rows[i].ItemArray.GetValue(0).ToString();
var value = rows[i].ItemArray.GetValue(1).ToString();
SharedData.Add(new TestDataModel {
Key = key, Value = value
});
}
return;
}
else if (type == TestDataType.TestSuite)//==test suite
{
TestCaseDataModel testCaseData = null;
TestDataExtraModel extraData = null;
for (int i = 0; i < rows.Count; i++)
{
extraData = null;
var key = rows[i].ItemArray.GetValue(0).ToString();
if (string.IsNullOrEmpty(key))
{
continue;
}
var value = rows[i].ItemArray.GetValue(1) == null ? string.Empty : rows[i].ItemArray.GetValue(1).ToString();
if (key.EqualsText(TestCaseNameKey, true))
{
if (testCaseData == null) //==begin a test case
{
testCaseData = new TestCaseDataModel();
}
else//next test case
{
if (TestSuitData.TestCaseDataList == null)
{
TestSuitData.TestCaseDataList = new List <TestCaseDataModel>();
}
TestSuitData.TestCaseDataList.Add(testCaseData.Clone() as TestCaseDataModel);
testCaseData = new TestCaseDataModel();
continue;
}
}
else if (key.Contains(ExtraDataKey))
{
if (string.IsNullOrEmpty(value))
{
continue;
}
string extraSheetName = value.Split('!').ToList().FirstOrDefault();
extraData = new TestDataExtraModel {
Key = key, DataExtend = excelReader.AsDataSet().Tables[extraSheetName]
};
if (testCaseData.DataExtendList == null)
{
testCaseData.DataExtendList = new List <TestDataExtraModel>();
}
testCaseData.DataExtendList.Add(extraData.Clone() as TestDataExtraModel);
extraData = null;
continue;
}
if (testCaseData == null && string.IsNullOrEmpty(TestSuitData.TestSuiteId)) //==test suite info
{
if (key.EqualsText(TestSuiteIdKey, true))
{
TestSuitData.TestSuiteId = value;
}
if (TestSuitData.DataList == null)
{
TestSuitData.DataList = new List <TestDataModel>();
}
TestSuitData.DataList.Add(new TestDataModel {
Key = key, Value = value
});
}
else if (testCaseData != null)
{
if (testCaseData.DataList == null)
{
testCaseData.DataList = new List <TestDataModel>();
}
if (key.EqualsText(TestCaseIdKey, true))
{
testCaseData.TestCaseId = value;
}
testCaseData.DataList.Add(new TestDataModel {
Key = key, Value = value
});
}
}
//the latest test case
if (testCaseData != null && testCaseData.TestCaseId != string.Empty)
{
if (TestSuitData.TestCaseDataList == null)
{
TestSuitData.TestCaseDataList = new List <TestCaseDataModel>();
}
TestSuitData.TestCaseDataList.Add(testCaseData);
}
return;
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
excelReader.Close();
stream.Close();
}
}
public ActionResult RowData(HttpPostedFileBase uploadfile)
{
if (ModelState.IsValid)
{
if (uploadfile != null && uploadfile.ContentLength > 0)
{
var dbContextTransaction = db.Database.BeginTransaction();
List <Message> messages = new List <Message>();
//ExcelDataReader works on binary excel file
Stream stream = uploadfile.InputStream;
//We need to written the Interface.
IExcelDataReader reader = null;
if (uploadfile.FileName.EndsWith(".xls"))
{
//reads the excel file with .xls extension
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (uploadfile.FileName.EndsWith(".xlsx"))
{
//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());
}
var name = uploadfile.FileName.Split('.');
var tmp = name[0];
if (tmp == "Raw Data from District")
{
//treats the first row of excel file as Coluymn Names
//reader.IsFirstRowAsColumnNames = true;
//Adding reader data to DataSet()
DataSet result = reader.AsDataSet();
//result.Tables[0].Rows[0].
DataContext contex = new DataContext();
//contex.Database.ExecuteSqlCommand("TRUNCATE TABLE RawDatas");
List <RawData> lstrdm = new List <RawData>();
List <InspectionDaily> linspdaily = new List <InspectionDaily>();
IList <RawData> ilstrdm = new List <RawData>();
IList <InspectionDaily> iinspdaily = new List <InspectionDaily>();
int loop = 0;
RawData rdt = new RawData();
InspectionDaily inspectiondaily = new InspectionDaily();
try
{
foreach (DataRow data in result.Tables[0].Rows)
{
if (loop == 0)
{
loop++;
continue;
}
rdt.StructureNumber = data[1].ToString();
rdt.District = data[3].ToString();
rdt.County = data[4].ToString();
rdt.Control = data[5].ToString();
bool isLetter = !String.IsNullOrEmpty(rdt.Control) && Char.IsLetter(rdt.Control[0]);
rdt.Section = data[6].ToString();
rdt.Milepnt = data[7].ToString();
rdt.StrNo = data[8].ToString();
//rdt.DupRtOver = data[7].ToString();
//rdt.RtStrFunc = data[10].ToString();
//rdt.Blanks1 = data[9].ToString();
// rdt.RtDesign = data[12].ToString();
// rdt.RtBusSfx = data[11].ToString();
// rdt.RtSystem = data[12].ToString();
// rdt.RtNo = data[13].ToString();
// rdt.RtDir = data[14].ToString();
//rdt.BRefMrk = data[15].ToString();
//if (string.IsNullOrEmpty(data[16].ToString())) { rdt.BRefMkSfx = 0; } else { rdt.BRefMkSfx = data[16].ToString()); };
//rdt.BRefMkSfx = data[16].ToString();
// rdt.CIRRfMkSign = data[17].ToString();
// rdt.BRfMkDispl = data[18].ToString();
//rdt.PlaceCode = data[19].ToString();
if (string.IsNullOrEmpty(data[22].ToString()))
{
rdt.FeatXed = "";
}
else
{
rdt.FeatXed = data[22].ToString();
};
//rdt.CritBdg = data[21].ToString();
// rdt.MiPtDatePr = data[22].ToString();
//rdt.AMiPtDateInt = data[23].ToString();
//rdt.Blanks2 = data[24].ToString();
rdt.FacCarried = data[27].ToString();
rdt.Location = data[28].ToString();
//rdt.RtMinVrtClear = data[27].ToString();
//if (string.IsNullOrEmpty(data[28].ToString())) { rdt.OLD16Latitude = 0; } else { rdt.OLD16Latitude = float.Parse(data[28].ToString()); };
//if (string.IsNullOrEmpty(data[29].ToString())) { rdt.OLD17Longitude = 0; } else { rdt.OLD17Longitude = float.Parse(data[29].ToString()); };
// rdt.DetourLgth = data[30].ToString();
// rdt.Toll = data[31].ToString();
// rdt.Custodian = data[32].ToString();
// rdt.Owner = data[33].ToString();
rdt.MaintSect = data[36].ToString();
//rdt.ProjType = data[35].ToString();
// if (string.IsNullOrEmpty(data[36].ToString())) { rdt.CSJWhnBlt = 0; } else { rdt.CSJWhnBlt = double.Parse(data[36].ToString()); };
//rdt.FuncClass = data[37].ToString();
rdt.YrBuilt = data[40].ToString();
//rdt.LanesOn = data[39].ToString();
//rdt.LanesUnder = data[40].ToString();
//if (string.IsNullOrEmpty(data[41].ToString())) { rdt.AADT = 0; } else { rdt.AADT = double.Parse(data[41].ToString()); };
//rdt.AADTYear = data[42].ToString();
//rdt.DesignLoad = data[43].ToString();
//rdt.ApprWidth = data[44].ToString();
//rdt.Median = data[45].ToString();
//rdt.Skew = data[46].ToString();
//rdt.StrFlared = data[47].ToString();
//rdt.TrfSafeFeat = data[48].ToString();
//rdt.HistSignif = data[49].ToString();
//rdt.NavControls = data[50].ToString();
//rdt.MnNavVrtClear = data[51].ToString();
//rdt.NavHrzClear = data[52].ToString();
//rdt.OperStatus = data[53].ToString();
//rdt.LoadType = data[54].ToString();
//rdt.Load1000lb = data[55].ToString();
//rdt.ServOnUnder = data[56].ToString();
//rdt.MnSpanTy = data[57].ToString();
//rdt.MjAprSpTy = data[58].ToString();
//rdt.MnAprSpTy = data[59].ToString();
//rdt.Culvert = data[60].ToString();
//rdt.TunnelTy = data[61].ToString();
//rdt.SubstrMSpan = data[62].ToString();
//rdt.SubMjAprSp = data[63].ToString();
//rdt.SubMnAprSp = data[64].ToString();
//rdt.NoMSpan = data[65].ToString();
//rdt.NoMjAprSp = data[66].ToString();
//rdt.NoMnAprSp = data[67].ToString();
//rdt.TotalNoSpans = data[68].ToString();
//rdt.TotHrzClear = data[69].ToString();
// rdt.MaxSpLgth = data[70].ToString();
// rdt.StrLgth = data[71].ToString();
//rdt.LtSdwalk = data[72].ToString();
//rdt.RtSdwalk = data[73].ToString();
// rdt.RdwyWidth = data[74].ToString();
// rdt.DeckWidth = data[75].ToString();
//rdt.VrtClrOver = data[76].ToString();
//rdt.VrtClrRefFeat = data[77].ToString();
//rdt.VrtClrUnder = data[78].ToString();
//rdt.LatClrRefFeat = data[79].ToString();
//rdt.RtLatClear = data[80].ToString();
//rdt.LtLatClear = data[81].ToString();
//rdt.Blanks3 = data[82].ToString();
//rdt.DeckCond = data[83].ToString();
//rdt.SuperCond = data[84].ToString();
//rdt.SubstrCond = data[85].ToString();
//rdt.ChanProt = data[86].ToString();
//rdt.Culvert = data[87].ToString();
//rdt.OperRate = data[88].ToString();
//rdt.RdApprCond = data[89].ToString();
//rdt.InvRate = data[90].ToString();
//rdt.StrEval = data[91].ToString();
//rdt.DeckGeom = data[92].ToString();
//rdt.UClrVrtHrz = data[93].ToString();
//rdt.SafeLoadCap = data[94].ToString();
//rdt.WaterwayAdeq = data[95].ToString();
//rdt.ApprRdAlign = data[96].ToString();
//rdt.TyWrkRepl = data[97].ToString();
//if (string.IsNullOrEmpty(data[98].ToString())) { rdt.LgthImprov = 0; } else { rdt.LgthImprov = double.Parse(data[98].ToString()); };
//rdt.SpecFlags = data[99].ToString();
//rdt.FHWAResvd = data[100].ToString();
//rdt.CostImprov1 = data[101].ToString();
if (string.IsNullOrEmpty(data[104].ToString()))
{
rdt.LastInspec = 0;
}
else
{
rdt.LastInspec = double.Parse(data[104].ToString());
};
//rdt.DesInspFreq = data[103].ToString();
//rdt.FracCritDet = data[104].ToString();
//rdt.UndwtrInspec = data[105].ToString();
//rdt.SpecialInspec = data[106].ToString();
//rdt.FracCritDate = data[107].ToString();
//rdt.UwtrInsDate = data[108].ToString();
//rdt.SpecInspDate = data[109].ToString();
//if (string.IsNullOrEmpty(data[110].ToString())) { rdt.BdgImprCost = 0; } else { rdt.BdgImprCost = double.Parse(data[110].ToString()); };
//if (string.IsNullOrEmpty(data[111].ToString())) { rdt.RdImprCost = 0; } else { rdt.RdImprCost = double.Parse(data[111].ToString()); };
//if (string.IsNullOrEmpty(data[112].ToString())) { rdt.TotProjCost = 0; } else { rdt.TotProjCost = double.Parse(data[112].ToString()); };
//rdt.YrImprCostEst = data[113].ToString();
//if (string.IsNullOrEmpty(data[114].ToString())) { rdt.BorderBdg = 0; } else { rdt.BorderBdg = double.Parse(data[114].ToString()); };
//rdt.BorderBdgNo = data[115].ToString();
//rdt.DefHwyDesn = data[116].ToString();
//rdt.ParlStrDesn = data[117].ToString();
//rdt.DirofTraffic = data[118].ToString();
//rdt.TempStrDesn = data[119].ToString();
//rdt.NHS = data[120].ToString();
//rdt.OldFAU = data[121].ToString();
//rdt.FHWAResvd_105 = data[121].ToString();
//rdt.YrReconstr = data[122].ToString();
//rdt.WidenCode = data[124].ToString();
//rdt.DeckTyMSp = data[125].ToString();
//rdt.MSpWrSurf = data[126].ToString();
//rdt.DeckMjAprSp = data[127].ToString();
//rdt.MjAprSpWrSrf = data[128].ToString();
//rdt.DkMnAprSp = data[129].ToString();
//rdt.MnAprSpWrSrf = data[130].ToString();
//rdt.AADTTruckPct = data[131].ToString();
//rdt.DesNatlNtwk = data[132].ToString();
//rdt.PierProtect = data[133].ToString();
//rdt.NBIBdgLgth = data[134].ToString();
//rdt.ScourCrit = data[135].ToString();
//if (string.IsNullOrEmpty(data[136].ToString())) { rdt.FutureAADT = 0; } else { rdt.FutureAADT = double.Parse(data[136].ToString()); };
//rdt.FutAADTYear = data[137].ToString();
//rdt.MnNavVrtClear = data[138].ToString();
//rdt.OrigCost = data[139].ToString();
//rdt.DefObs = data[140].ToString();
//if (string.IsNullOrEmpty(data[141].ToString())) { rdt.SuffRate = 0; } else { rdt.SuffRate = double.Parse(data[141].ToString()); };
//rdt.XrefPrinRtID = data[142].ToString();
//rdt.XrfStrFncPRt = data[143].ToString();
//rdt.SuffRatFlag = data[144].ToString();
//rdt.ScourVuln = data[145].ToString();
//rdt.OvHtLoadDmg = data[146].ToString();
//rdt.Blanks4 = data[147].ToString();
//rdt.XRfIRID = data[148].ToString();
//rdt.XRfIRStrFunc = data[149].ToString();
//rdt.Blanks5 = double.Parse(data[150].ToString());
//rdt.DistUse = data[151].ToString();
//rdt.AIRControl = data[152].ToString();
//rdt.AIRSection = data[153].ToString();
//rdt.AIRMilept = data[154].ToString();
//rdt.AIRStrNo = data[155].ToString();
//rdt.AIRDuplOver = data[156].ToString();
//rdt.AIRStrFunct = data[157].ToString();
//rdt.AIRDesignation = data[158].ToString();
//rdt.AIRBusSfx = data[159].ToString();
//rdt.AIRHwySys = data[160].ToString();
//rdt.AIRHwyNo = data[161].ToString();
//rdt.AIRDir = data[162].ToString();
//rdt.CIRRefMrk = data[163].ToString();
//rdt.CIRRefMkSfx = data[164].ToString();
//rdt.CIRRfMkSign = data[165].ToString();
//rdt.CIRRfMkDispl = data[166].ToString();
//rdt.AIRHrzClear = data[167].ToString();
//rdt.AIRMnVrtClear = data[168].ToString();
//rdt.AIRBypsLgth = data[169].ToString();
//rdt.AIRToll = data[170].ToString();
//rdt.AIRFuncClass = data[171].ToString();
//rdt.AIRAADT = data[172].ToString();
//rdt.AIRAADTYr = data[173].ToString();
//rdt.AIRDefHwyDes = data[174].ToString();
//rdt.AIRParlStr = data[175].ToString();
//rdt.AIRTrafDir = data[176].ToString();
//rdt.AIRTempStr = data[177].ToString();
//rdt.AIRNHS = data[178].ToString();
//rdt.OldFAU = data[179].ToString();
//rdt.AIRAADTTrkPct = data[180].ToString();
//rdt.AIRNatlNtwk = data[181].ToString();
//rdt.AIRFutAADT = data[182].ToString();
//rdt.AIRFAADTYr = data[183].ToString();
//rdt.Blanks6 = data[184].ToString();
//rdt.FHWAStrEval = data[185].ToString();
//rdt.FHWADkGeom = data[186].ToString();
//rdt.FHWAUClrVrtHrz = data[187].ToString();
//rdt.BaseHwyNet = data[188].ToString();
//rdt.LRSInvRte = data[189].ToString();
//rdt.LRSSubRte = data[190].ToString();
//rdt.Blanks7 = data[191].ToString();
if (string.IsNullOrEmpty(data[194].ToString()))
{
rdt.GPSLatitude = 0;
}
else
{
rdt.GPSLatitude = double.Parse(data[194].ToString());
};
//rdt.Blanks8 = data[193].ToString();
if (string.IsNullOrEmpty(data[196].ToString()))
{
rdt.GPSLongitude = 0;
}
else
{
rdt.GPSLongitude = double.Parse(data[196].ToString());
};
//rdt.GPSColMeth = data[195].ToString();
//rdt.MethOperRate = data[196].ToString();
//rdt.MethInvRate = data[197].ToString();
//rdt.FedLand = data[198].ToString();
if (string.IsNullOrEmpty(data[201].ToString()))
{
rdt.Latitude = 0;
}
else
{
rdt.Latitude = double.Parse(data[201].ToString());
};
if (string.IsNullOrEmpty(data[202].ToString()))
{
rdt.Longitude = 0;
}
else
{
rdt.Longitude = double.Parse(data[202].ToString());
};
//rdt.AIRBaseHwyNet = data[201].ToString();
//rdt.AIRLRSInvRte = data[202].ToString();
//rdt.AIRLRSSubRte = data[203].ToString();
//rdt.Blanks9 = data[204].ToString();
//inspectiondaily.IdClient = 1;
//inspectiondaily.IDUser = 1;
//if (isLetter)
//{
// inspectiondaily.IdProject = 2;
//}
//else
//{
// inspectiondaily.IdProject = 1;
//}
//// var idproject = validarcontrol(rdt.Control);
////inspectiondaily.IdProject = idproject;
////var numinsp = String.Format("{0}-{1}-{2}-{3}-{4}", rdt.District, rdt.County, rdt.Control, rdt.Section, rdt.StrNo);
//string numinspaux = data[1].ToString();
//var numinsp = String.Format("{0}-{1}-{2}-{3}-{4}", numinspaux.Substring(0,2), numinspaux.Substring(2, 3), numinspaux.Substring(6, 4), numinspaux.Substring(10, 2), numinspaux.Substring(12));
//var exist = db.InspectionDaily.Where(i => i.NumInspection == numinsp).FirstOrDefault();
////if (exist != null)
////{ message.mensaje = "La inpeccion ya se encuentra registrada "; message.fila = loop; break; }
//inspectiondaily.NumInspection = numinsp;
//inspectiondaily.DO = rdt.District;
//inspectiondaily.Company = rdt.County;
//inspectiondaily.Control = rdt.Control;
//inspectiondaily.Section = rdt.Section;
//inspectiondaily.Scope = rdt.FeatXed;
//inspectiondaily.IdValueCheckList = 70;
//inspectiondaily.IdAttach = 4;
//inspectiondaily.Hour = rdt.YrBuilt;
//inspectiondaily.IdStatus = 5;
//inspectiondaily.City = rdt.FacCarried;
//inspectiondaily.TypeInspection = 1;
//inspectiondaily.Address = rdt.Location;
//inspectiondaily.LatitudeIni = rdt.GPSLatitude;
//inspectiondaily.LongitudeIni = rdt.GPSLongitude;
//inspectiondaily.Structure = rdt.StrNo;
//inspectiondaily.MaintanSection = rdt.MaintSect;
//inspectiondaily.Milepnt = rdt.Milepnt;
//inspectiondaily.Owner = rdt.Owner;
///
//contex = contex.BulkInsert(inspectiondaily, loop, 100);
db.RawData.Add(rdt);
//db.InspectionDaily.Add(inspectiondaily);
db.SaveChanges();
//db2.SaveChanges();
loop++;
}
//contex.SaveChanges();
}
catch (Exception ex)
{
dbContextTransaction.Rollback();
reader.Close();
Message message1 = new Message();
message1.mensaje = ex.InnerException.InnerException.ToString();
if (message1.mensaje.Contains("duplicate key"))
{
ViewBag.Script = "<script type='text/javascript'>swal('¡Alert!', 'inspection duplicate in row " + loop + "', 'error');</script>";
}
ViewBag.Files = new SelectList(CombosHelper.GetFiles(), "Value", "Text");
return(View("LoadData"));
}
dbContextTransaction.Commit();
reader.Close();
//Sending result data to View
return(RedirectToAction("Index", "InspectionDailies"));
}
else if (tmp == "Configuration Checklist")
{
DataSet result = reader.AsDataSet();
DataContext contex = new DataContext();
//contex.Database.ExecuteSqlCommand("TRUNCATE TABLE RawDatas");
DataTable dt = result.Tables[0];
var idProject = dt.Rows[0][1];
}
else if (tmp == "Detailed Work Schedule")
{
var contar = 0;
var raw = db.RawData;
if (raw.ToList().Count() == 0)
{
ViewBag.Script = "<script type='text/javascript'>swal('¡Alert!', 'First you need load Row Data','error');</script>";
ViewBag.Files = new SelectList(CombosHelper.GetFiles(), "Value", "Text");
return(View("LoadData"));
}
List <Message> messages1 = new List <Message>();
RawData rowdt = null;
DataSet result = reader.AsDataSet();
//result.Tables[0].Rows[0].
DataContext contex = new DataContext();
//contex.Database.ExecuteSqlCommand("TRUNCATE TABLE RawDatas");
List <InspectionDaily> linspdaily = new List <InspectionDaily>();
IList <InspectionDaily> iinspdaily = new List <InspectionDaily>();
InspectionDaily inspectiondaily = new InspectionDaily();
int loop = 0;
var bandera = true;
var coun = db.RawData;
if (coun != null)
{
try
{
foreach (DataRow data in result.Tables[0].Rows)
{
if (loop == 0)
{
loop++;
continue;
}
var StructureNumber = data[0].ToString();
rowdt = db.RawData.Where(r => r.StructureNumber == StructureNumber)
.FirstOrDefault();
if (rowdt == null)
{
Message message = new Message();
message.fila = loop;
message.mensaje = "The inspection " + StructureNumber + " is new";
loop++;
messages1.Add(message);
bandera = true;
continue;
}
else
{
inspectiondaily.IdClient = 1;
inspectiondaily.IDUser = 1;
inspectiondaily.IdProject = 1;
//if (isLetter)
//{
// inspectiondaily.IdProject = 2;
//}
//else
//{
// inspectiondaily.IdProject = 1;
//}
var idproject = validarcontrol(rowdt.Control);
inspectiondaily.IdProject = idproject;
var numinsp = String.Format("{0}-{1}-{2}-{3}-{4}", rowdt.StructureNumber.Substring(0, 2), rowdt.StructureNumber.Substring(2, 3), rowdt.StructureNumber.Substring(6, 4), rowdt.StructureNumber.Substring(10, 2), rowdt.StructureNumber.Substring(12));
var exist = db.InspectionDaily.Where(i => i.NumInspection == numinsp).FirstOrDefault();
Message message = new Message();
if (exist != null)
{
message.fila = loop;
message.mensaje = "The inspection " + StructureNumber + " was load before ";
loop++;
messages1.Add(message);
continue;
}
inspectiondaily.NumInspection = numinsp;
inspectiondaily.DO = rowdt.District;
inspectiondaily.Company = rowdt.County;
inspectiondaily.Control = rowdt.Control;
inspectiondaily.Section = rowdt.Section;
inspectiondaily.Scope = rowdt.FeatXed;
inspectiondaily.IdValueCheckList = 70;
inspectiondaily.IdAttach = 4;
inspectiondaily.Hour = rowdt.YrBuilt;
inspectiondaily.IdStatus = 5;
inspectiondaily.City = rowdt.FacCarried;
inspectiondaily.TypeInspection = 1;
inspectiondaily.Address = rowdt.Location;
inspectiondaily.LatitudeIni = rowdt.GPSLatitude;
inspectiondaily.LongitudeIni = rowdt.GPSLongitude;
inspectiondaily.Structure = rowdt.StrNo;
inspectiondaily.MaintanSection = rowdt.MaintSect;
inspectiondaily.Milepnt = rowdt.Milepnt;
inspectiondaily.Owner = rowdt.Owner;
db.InspectionDaily.Add(inspectiondaily);
db.SaveChanges();
contar++;
loop++;
}
}
Message message2 = new Message();
message2.mensaje = "Loaded " + contar + " inspections";
messages1.Add(message2);
if (bandera)
{
dbContextTransaction.Commit();
reader.Close();
ViewBag.ok = JsonConvert.SerializeObject(messages1);
//ViewBag.Script = "<script type='text/javascript'>swal('¡info!', '" + messages.ToList() + "','error');</script>";
ViewBag.Files = new SelectList(CombosHelper.GetFiles(), "Value", "Text");
return(View("LoadData"));
}
else
{
ViewBag.Script = "<script type='text/javascript'>swal('¡Alert!', 'No data found','error');</script>";
ViewBag.Files = new SelectList(CombosHelper.GetFiles(), "Value", "Text");
return(View("LoadData"));
}
}
catch (Exception ex)
{
dbContextTransaction.Rollback();
reader.Close();
var mensaje = ex.InnerException.InnerException.ToString();
if (mensaje.Contains("duplicate key"))
{
}
else
{
ViewBag.Script = "<script type='text/javascript'>swal('¡Alert!', 'Error " + ex.Message + "', 'error');</script>";
}
ViewBag.Files = new SelectList(CombosHelper.GetFiles(), "Value", "Text");
return(View("LoadData"));
}
}
else
{
ViewBag.Script = "<script type='text/javascript'>swal('¡Alert!', 'First you need load Row Data', 'error');</script>";
ViewBag.Files = new SelectList(CombosHelper.GetFiles(), "Value", "Text");
return(View("LoadData"));
}
return(RedirectToAction("Index", "InspectionDailies"));
}
}
}
else
{
ModelState.AddModelError("File", "Please upload your file");
}
ViewBag.Files = new SelectList(CombosHelper.GetFiles(), "Value", "Text");
return(RedirectToAction("LoadData", "RawDatas"));
//return View();
}
public ActionResult Upload(ExcelRatingsViewModel ratingModel)
{
if (ModelState.IsValid)
{
if (ratingModel.UploadFile != null && ratingModel.UploadFile.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 = ratingModel.UploadFile.InputStream;
DataSet result = null;
if (ratingModel.UploadFile.FileName.EndsWith(".xls"))
{
IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(stream);
reader.IsFirstRowAsColumnNames = true;
result = reader.AsDataSet();
reader.Close();
}
else if (ratingModel.UploadFile.FileName.EndsWith(".xlsx"))
{
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
reader.IsFirstRowAsColumnNames = true;
result = reader.AsDataSet();
reader.Close();
}
else
{
ModelState.AddModelError("File", "This file format is not supported");
return(View());
}
ratingModel = ProcessExcelDataintoViewModel(ratingModel, result);
ViewBag.Suppliers = selectSuppliers;
var count = 0;
if (ratingModel.RatingRecords.Count > 0)
{
foreach (var record in ratingModel.RatingRecords)
{
if ((record.ErrorInformation != null ? record.ErrorInformation.Count : 0) > 1)
{
count++;
}
}
if (count > 0)
{
ViewBag.Count = count;
//ViewBag.ShowMerge = false;
}
else
{
ViewBag.Count = count;
// ViewBag.ShowMerge = true;
}
}
else
{
ModelState.AddModelError("UploadFile", "Please upload Valid File");
CreateListViewBags();
return(View("Index", ratingModel));
}
}
else
{
ModelState.AddModelError("UploadFile", "Please Upload Your file");
CreateListViewBags();
return(View("Index", ratingModel));
}
}
return(View("ExcelReview", ratingModel));
}
public ActionResult Index(HttpPostedFileBase upload)
{
if (ModelState.IsValid)
{
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;
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", "Este formato de archivo no es compatible");
return(View());
}
int fieldcount = reader.FieldCount;
int rowcount = reader.RowCount;
DataTable dt = new DataTable();
DataRow row;
DataTable dt_ = new DataTable();
try
{
dt_ = reader.AsDataSet().Tables[0];
for (int i = 0; i < dt_.Columns.Count; i++)
{
dt.Columns.Add(dt_.Rows[0][i].ToString());
}
int rowcounter = 0;
for (int row_ = 1; row_ < dt_.Rows.Count; row_++)
{
row = dt.NewRow();
for (int col = 0; col < dt_.Columns.Count; col++)
{
row[col] = dt_.Rows[row_][col].ToString();
rowcounter++;
}
dt.Rows.Add(row);
}
}
catch (Exception ex)
{
ModelState.AddModelError("File", "No se puede subir el archivo");
return(View());
}
DataSet result = new DataSet();
result.Tables.Add(dt);
reader.Close();
reader.Dispose();
DataTable tmp = result.Tables[0];
Session["tmpdata"] = tmp; //store datatable into session
return(RedirectToAction("Index"));
}
else
{
ModelState.AddModelError("File", "Por favor cargue un archivo");
}
}
return(View());
}
public static void Convert(ref string put)
{
OpenFileDialog choofdlog = new OpenFileDialog
{
Filter = "Xlsx Files *.xlsx|*.xlsx|Xls Files *.xls|*.xls|Csv files *.csv|*.csv",
FilterIndex = 1,
Multiselect = false
};
if (choofdlog.ShowDialog() == DialogResult.OK)
{
put = choofdlog.FileName.ToString();
}
if (put == null)
{
MessageBox.Show("Nije odabran file");
return;
}
if (put.Contains(".csv"))
{
return;
}
FileStream stream = File.Open(put, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader;
try
{
//old xls format 93-07
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
catch
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
DataSet result = excelReader.AsDataSet();
excelReader.Close();
result.Tables[0].TableName.ToString();
string csvData = "";
int row_no = 0;
int ind = 0;
while (row_no < result.Tables[ind].Rows.Count) // ind is the index of table
// (sheet name) which you want to convert to csv
{
for (int i = 0; i < result.Tables[ind].Columns.Count; i++)
{
csvData += result.Tables[ind].Rows[row_no][i].ToString().Replace('\n', ' ') + ";";
}
row_no++;
csvData += "\n";
}
if (put.Contains(".xlsx"))
{
put = put.Replace("xlsx", "csv");
}
else
{
put = put.Replace("xls", "csv");
}
string output = put; // define your own filepath & filename
StreamWriter csv = new StreamWriter(@output, false, Encoding.UTF8);
csv.Write(csvData);
csv.Close();
}
public static void ConvertToCsv(string pathToExcel, string pathToCsv)
{
if (pathToExcel == null)
{
throw new ArgumentNullException("pathToExcel");
}
if (pathToExcel == string.Empty)
{
throw new ArgumentException("pathToExcel is emtpy", "pathToExcel");
}
string ext = Path.GetExtension(pathToExcel);
if (ext != ".xls" && ext != ".xlsx")
{
throw new ArgumentException("Expected '.xls' or '.xlsx'", "pathToExcel");
}
if (!File.Exists(pathToExcel))
{
throw new FileNotFoundException("File not found", pathToExcel);
}
if (pathToCsv == null)
{
throw new ArgumentNullException("pathToCsv");
}
if (pathToCsv == string.Empty)
{
throw new ArgumentException("pathToCsv is emtpy", "pathToCsv");
}
if (File.Exists(pathToCsv))
{
throw new IOException(string.Format("CSV file '{0}' already exists", pathToCsv));
}
using (FileStream stream = new FileStream(pathToExcel, FileMode.Open, FileAccess.Read))
{
IExcelDataReader excelReader = null;
if (ext == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
using (excelReader)
{
excelReader.IsFirstRowAsColumnNames = true;
var dataSet = excelReader.AsDataSet();
var table = dataSet.Tables[0];
using (FileStream csvStream = File.Open(pathToCsv, FileMode.CreateNew, FileAccess.Write, FileShare.None))
{
using (StreamWriter sw = new StreamWriter(csvStream))
{
// Write header at first line
StringBuilder sb = new StringBuilder();
for (int i = 0; i < table.Columns.Count; i++)
{
sb.Append(table.Columns[i]);
if (i != table.Columns.Count - 1)
{
sb.Append(',');
}
}
sw.WriteLine(sb.ToString());
// Write rows
for (int i = 0; i < table.Rows.Count; i++)
{
sb.Clear();
for (int j = 0; j < table.Columns.Count; j++)
{
sb.Append(table.Rows[i].ItemArray[j]);
if (j != table.Columns.Count - 1)
{
sb.Append(',');
}
}
sw.WriteLine(sb.ToString());
}
sw.Flush();
sw.Close();
}
}
}
}
}
private void btnNhapExcel_Click(object sender, EventArgs e)
{
int soluongCHThanhCong = 0;
int CHTrung = 0;
DataSet ds = new DataSet();
using (OpenFileDialog openFileDialog = new OpenFileDialog()
{
ValidateNames = true, Filter = "Excel Workbook|*.xlsx|Excel Workbook 97-2003|*.xls"
})
try
{
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader;
if (openFileDialog.FilterIndex == 2)
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
reader.Close();
}
}
DataTable dataCH = ds.Tables["CauHoi"];
DataTable dataDA = ds.Tables["DapAn"];
foreach (DataRow row in dataCH.Rows)
{
if (_cauHoiRepository.FindByCondition(ch => ch.CauHoiDeBai.Equals(row["CauHoiDeBai"].ToString())).SingleOrDefault() != null)
{
CHTrung++;
continue;
}
else
{
CauHoi cauhoi = new CauHoi()
{
CauHoiDeBai = row["CauHoiDeBai"].ToString(),
GoiY = row["GoiY"].ToString(),
CapDo = int.Parse(row["CapDo"].ToString()),
CapHoc = int.Parse(row["Khoi"].ToString()),
MonHoc = row["MonHoc"].ToString()
};
_cauHoiRepository.Create(cauhoi);
_cauHoiRepository.Update();
int maCH = int.Parse(row["MaCH"].ToString());
foreach (DataRow rowDA in dataDA.Rows)
{
if (int.Parse(rowDA["MaCH"].ToString()).Equals(maCH))
{
_dapAnRepository.Create(new DapAn
{
IDCauHoi = cauhoi.IDCauHoi,
DapAnCauHoi = rowDA["DapAnCauHoi"].ToString(),
STTDapAn = int.Parse(rowDA["STT"].ToString()),
IsDung = bool.Parse(rowDA["Dung"].ToString())
});
_dapAnRepository.Update();
}
}
soluongCHThanhCong++;
}
}
}
catch (Exception excep)
{
MessageBox.Show(excep.Message, "Lỗi file", MessageBoxButtons.OK, MessageBoxIcon.Information);
MessageBox.Show($"Số câu hỏi thêm thành công: {soluongCHThanhCong}\n" +
$"Số học sinh bị trùng: {CHTrung}\n");
return;
}
MessageBox.Show($"Số câu hỏi thêm thành công: {soluongCHThanhCong}\n" +
$"Số học sinh bị trùng: {CHTrung}\n");
LoadCauHoi();
}
public ActionResult ThemSachTuFile(HttpPostedFileBase upload)
{
if (ModelState.IsValid)
{
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", "Định dạng file không được hỗ trợ.");
return(View());
}
reader.IsFirstRowAsColumnNames = true;
DataSet result = reader.AsDataSet();
for (int i = 0; i < result.Tables.Count; i++)
{
// Lay ten chu de (ten sheet)
string tencd = result.Tables[i].TableName;
// them chu de vo csdl
if (db.ChuDe.Any(cd => cd.TenChuDe == tencd))
{
}
else
{
ChuDe cd = new ChuDe {
TenChuDe = tencd
};
db.ChuDe.Add(cd);
db.SaveChanges();
}
// them sach vo csdl
int idcd = db.ChuDe.Where(cd => cd.TenChuDe == tencd).Select(cd => cd.ID).FirstOrDefault(); // get ID of table Chu De by TenChuDe
foreach (DataRow row in result.Tables[i].Rows)
{
Sach sach = new Sach
{
ChuDeID = idcd,
SachID = row[0].ToString(),
TenSach = row[1].ToString(),
SoLuong = Int32.Parse(row[2].ToString()),
TrangThai = TrangThai.CoSan
};
if (db.Sach.Any(n => n.SachID == sach.SachID && n.TenSach == sach.TenSach))
{
}
else
{
db.Sach.Add(sach);
db.SaveChanges();
}
}
}
/*
* foreach (DataRow row_chude in result.Tables[0].Rows)
* {
* foreach (DataRow row in result.Tables[row_chude["Tên chủ đề"].ToString()].Rows)
* {
* Sach sach = new Sach
* {
* ChuDeID = Int32.Parse(row_chude["ID"].ToString()),
* SachID = row[1].ToString(),
* TenSach = row[2].ToString(),
* SoLuong = Int32.Parse(row[3].ToString())
* };
*
* if (db.Sach.Any(n => n.SachID == sach.SachID && n.TenSach == sach.TenSach))
* {
*
* }
* else
* {
* db.Sach.Add(sach);
* db.SaveChanges();
* }
* }
* }
*/
reader.Close();
return(RedirectToAction("Index"));
}
else
{
ModelState.AddModelError("File", "Vui lòng chọn file!");
}
}
return(View());
}
protected void btnBulkCustInfoUpload_Click(object sender, EventArgs e)
{
List <Customer> BasicCustList = new List <Customer>();
int totalRec = 0;
logs = "";
FileStream stream;
IExcelDataReader excelReader = null;
System.Data.DataTable dt = null;
DataSet ds;
int currRow = 0;
try
{
if (fUpload.FileName == string.Empty)
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Error: " + "No file selected for Bulk Upload");
return;
}
else
{
if (fUpload.HasFile)
{
string v_file_name = fUpload.FileName; //FileName
string v_file_path = Server.MapPath(v_file_name); //File Path
string v_ext = Path.GetExtension(v_file_path); //v_file_name.Substring(fUpload.FileName.LastIndexOf("."), 4).ToLower(); // File Extension
fUpload.SaveAs(v_file_path);
stream = File.Open(v_file_path, FileMode.Open, FileAccess.ReadWrite);
if (v_ext == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream); // For Excel 2003
}
else if (v_ext == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); // For Excel 2007 to 2010
}
else
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Invalid file type");
return;
}
// Delete the file after upload
// File.Delete(v_file_path);
excelReader.IsFirstRowAsColumnNames = true; // Specify if there is a header in the Excel
ds = excelReader.AsDataSet(); // Fill the dataset with the excel data
dt = ds.Tables[0]; // FIll the datatable
excelReader.Close();
excelReader.Dispose();
int rowCnt = dt.Rows.Count;
int colCount = dt.Columns.Count;
// int count = 0;
//return;
if (colCount > 26)
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Too Many Columns");
return;
}
if (colCount < 26)
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Lesser number of Columns");
return;
}
//Customer clist = new Customer();
for (int i = 0; i < rowCnt; i++)
{
currRow = i;
custObj = new Customer
{
CustomerId = dt.Rows[i][0].ToString(), //CustomerId
CustomerName = dt.Rows[i][1].ToString(), // CustomerName
SecretQuestion = dt.Rows[i][2].ToString(), // SecretQuestion
SecretQuestionAnswer = dt.Rows[i][3].ToString(), // SecretQuestionAnswer
EmailAddress2 = dt.Rows[i][4].ToString(), // EmailAddress2
WebAddress = dt.Rows[i][5].ToString(), //WebAddress
AnniversaryDate1 = dt.Rows[i][6].ToString(), // AnniversaryDate1
AnniversaryType2 = dt.Rows[i][7].ToString(), // AnniversaryType2
AnniversaryDate2 = dt.Rows[i][8].ToString(), //AnniversaryDate2
AnniversaryType3 = dt.Rows[i][9].ToString(), // AnniversaryType3
AnniversaryDate3 = dt.Rows[i][10].ToString(), // AnniversaryDate3
AnniversaryType4 = dt.Rows[i][11].ToString(), // AnniversaryType4
AnniversaryDate4 = dt.Rows[i][12].ToString(), // AnniversaryDate4
AnniversaryType5 = dt.Rows[i][13].ToString(), //AnniversaryType5
AnniversaryDate5 = dt.Rows[i][14].ToString(), // AnniversaryDate5
AnniversaryType6 = dt.Rows[i][15].ToString(), // AnniversaryType6
AnniversaryDate6 = dt.Rows[i][16].ToString(), // AnniversaryDate6
AnniversaryType7 = dt.Rows[i][17].ToString(), // AnniversaryType7
AnniversaryDate7 = dt.Rows[i][18].ToString(), // AnniversaryDate7
AdministrativeArea = dt.Rows[i][19].ToString(), // adminArea
Locality = dt.Rows[i][20].ToString(), // locality
LocationCoordinates = dt.Rows[i][21].ToString(), // locationCoordinates
PostCode = dt.Rows[i][22].ToString(), // postCode
PostOfficeBox = dt.Rows[i][23].ToString(), // postOfficeBox
AreaCode = dt.Rows[i][24].ToString(), // areaCode
ExtensionNo = dt.Rows[i][25].ToString() // extensionNo
};
BasicCustList.Add(custObj);
}
totalRec += InsertExcelData(BasicCustList);
// int cnt = 0;
//foreach (Customer item in BasicCustList)
//{
// totalRec += InsertExcelData(item);
// //UpdateRefData(item);
//}
}//end of else
if (totalRec > 0)
{
lblMsg.Text = MessageFormatter.GetFormattedSuccessMessage(string.Format("Uploaded {0} records", totalRec));
//lnkView.Visible = true;
}
}//end of else
}
catch (Exception ex)
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Error: " + ex.Message + " at row " + (currRow + 1));
}
// }
}
public Dictionary <string, object> UploadTargets(string path_and_file)
{
try
{
//variable for logs
int success = 0;
int fail = 0;
int total = 0;
//get current workweek and year
//di ko sure kung kailangan to
//List<int> curr_ww = workweek_helper.GetCurrentWorkWeek();
//int current_workweek = curr_ww[0];
//int current_year = curr_ww[1];
response = new Dictionary <string, object>();
//kailangan ko to
SupplierModels t = new SupplierModels();
FileStream stream = File.Open(path_and_file, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = null;
string ext = Path.GetExtension(stream.Name);
if (ext == ".xls")
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (ext == ".xlsx")
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
throw new Exception("Invalid file type.");
}
//Choose one of either 3, 4, or 5
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
//DataSet result = excelReader.AsDataSet();
//4. DataSet - Create column names from first row
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();
//5. Data Reader methods
DateTime dateCreated = DateTime.Now;
int ctr = 0;
string log_output = "";
////override function
//if (allowOverride)
//{
// t.DeleteTestTargets("WW" + current_workweek.ToString().PadLeft(2, '0'), current_year);
//}
while (excelReader.Read())
{
if (ctr >= 1)
{
SupplierObject targets = new SupplierObject();
targets.Name = excelReader.IsDBNull(0) ? string.Empty : excelReader.GetString(0);
targets.Email = excelReader.IsDBNull(1) ? string.Empty : excelReader.GetString(1);
targets.ContactNbr = excelReader.IsDBNull(2) ? string.Empty : excelReader.GetString(2);
targets.UnitNbr = excelReader.IsDBNull(3) ? string.Empty : excelReader.GetString(3);
targets.StreetName = excelReader.IsDBNull(4) ? string.Empty : excelReader.GetString(4);
targets.Municipality = excelReader.IsDBNull(5) ? string.Empty : excelReader.GetString(5);
targets.City = excelReader.IsDBNull(6) ? string.Empty : excelReader.GetString(6);
targets.Country = excelReader.IsDBNull(7) ? string.Empty : excelReader.GetString(7);
targets.Zip = excelReader.IsDBNull(8) ? 0 : Convert.ToInt32(excelReader.GetString(8));
targets.ImpexRefNbr = excelReader.IsDBNull(9) ? string.Empty : excelReader.GetString(9);
targets.IsActive = true;
targets.AddedBy = Convert.ToInt32(HttpContext.Current.Session["userId_local"]);
targets.DateAdded = dateCreated;
t.SaveNewSupplier(targets);
total++;
success++;
//buburahin ko to titira ko lang "t.SaveTargets(targets)"
//if (targets.WorkWeek != "WW" + current_workweek.ToString().PadLeft(2, '0') || targets.FiscalYear != current_year)
//{
// fail++;
// log_output += "<p><span class='label label-danger'>[" + targets.ProductGroup + "] [" + targets.PackageGroupA + "] [" + targets.PackageGroupB + "] [" + targets.Target + "] [" + targets.WorkWeek + "] [" + targets.FiscalYear + "] = work week or year mismatch</span></p>";
//}
//else
//{
// if (t.CheckExistingTestTarget(targets) > 0)
// {
// fail++;
// log_output += "<p><span class='label label-danger'>[" + targets.ProductGroup + "] [" + targets.PackageGroupA + "] [" + targets.PackageGroupB + "] [" + targets.Target + "] [" + targets.WorkWeek + "] [" + targets.FiscalYear + "] = already exists</span></p>";
// }
// else
// {
// if (!t.SaveTargets(targets))
// {
// fail++;
// log_output += "<p><span class='label label-danger'>[" + targets.ProductGroup + "] [" + targets.PackageGroupA + "] [" + targets.PackageGroupB + "] [" + targets.Target + "] [" + targets.WorkWeek + "] [" + targets.FiscalYear + "] = failed</span></p>";
// }
// else
// {
// hasSuccess = true;
// success++;
// log_output += "<p><span class='label label-success'>[" + targets.ProductGroup + "] [" + targets.PackageGroupA + "] [" + targets.PackageGroupB + "] [" + targets.Target + "] [" + targets.WorkWeek + "] [" + targets.FiscalYear + "] = added</span></p>";
// }
// }
//}
//hanggang dito
//total++;
}
ctr++;
}
//if (hasSuccess)
//{
// t.RefreshData();
//}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
Dictionary <string, int> stats = new Dictionary <string, int>();
stats.Add("success", success);
stats.Add("fail", fail);
Dictionary <string, string> msg = new Dictionary <string, string>();
if (total <= 0)
{
throw new Exception("Uploaded file is empty or is in the wrong format. Kindly check the guidelines for uploading.");
}
else
{
if (success == total)
{
msg.Add("title", "Uploading Successful!");
msg.Add("body", "New targets uploaded successfully!");
msg.Add("type", "success");
}
else
{
msg.Add("title", "Uploading Unsuccessful!");
msg.Add("body", success + " / " + total + " uploaded successfully!");
msg.Add("type", "danger");
}
}
response.Add("success", true);
response.Add("error", false);
response.Add("stats", stats);
response.Add("logs", log_output);
response.Add("message", msg);
}
catch (Exception e)
{
response.Add("success", false);
response.Add("error", true);
response.Add("message", e.ToString());
}
return(response);
}
protected virtual IExcelDataReader CreateReader(FileStream inputStream) => ExcelReaderFactory.CreateBinaryReader(inputStream);
public FileInformation Read(FileInfo fileInfo)
{
var fileInformation = new FileInformation(fileInfo);
var names = new List <string>();
var stream = System.IO.File.Open(fileInfo.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
var isXml = fileInfo.Extension.Equals(".xlsx", StringComparison.OrdinalIgnoreCase);
var excelReader = isXml ? ExcelReaderFactory.CreateOpenXmlReader(stream) : ExcelReaderFactory.CreateBinaryReader(stream);
excelReader.Read();
for (var i = 0; i < excelReader.FieldCount; i++)
{
var name = excelReader.GetString(i);
if (name != null)
{
names.Add(name);
}
}
excelReader.Close();
foreach (var name in names)
{
var field = new Field("string", _request.DefaultLength, FieldType.NonKey, true, string.Empty)
{
Name = name
};
fileInformation.Fields.Add(field);
}
return(fileInformation);
}
protected void btnBulkCustInfoUpload_Click(object sender, EventArgs e)
{
List <CustomerIndividualBulkUpload> BulkCustIndividualList = new List <CustomerIndividualBulkUpload>();
int totalRec = 0;
logs = "";
FileStream stream;
IExcelDataReader excelReader = null;
System.Data.DataTable dt = null;
DataSet ds;
int currRow = 0;
try
{
if (fUpload.FileName == string.Empty)
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Error: " + "No file selected for Bulk Upload");
return;
}
else
{
if (fUpload.HasFile)
{
string v_file_name = fUpload.FileName; //FileName
string v_file_path = Server.MapPath(v_file_name); //File Path
string v_ext = Path.GetExtension(v_file_path); //v_file_name.Substring(fUpload.FileName.LastIndexOf("."), 4).ToLower(); // File Extension
fUpload.SaveAs(v_file_path);
stream = File.Open(v_file_path, FileMode.Open, FileAccess.ReadWrite);
if (v_ext == ".xls")
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream); // For Excel 2003
}
else if (v_ext == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); // For Excel 2007 to 2010
}
else
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Invalid file type");
return;
}
// Delete the file after upload
// File.Delete(v_file_path);
excelReader.IsFirstRowAsColumnNames = true; // Specify if there is a header in the Excel
ds = excelReader.AsDataSet(); // Fill the dataset with the excel data
dt = ds.Tables[0]; // FIll the datatable
excelReader.Close();
excelReader.Dispose();
int rowCnt = dt.Rows.Count;
int colCount = dt.Columns.Count;
// int count = 0;
//return;
if (colCount > 71)
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Too Many Columns");
return;
}
if (colCount < 71)
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Lesser number of Columns");
return;
}
//CustomerIndividualBulkUpload Indivlist = new CustomerIndividualBulkUpload();
for (int i = 0; i < rowCnt; i++)
{
currRow = i;
IndivObj = new CustomerIndividualBulkUpload
{
CustomerId = dt.Rows[i][0].ToString(),
CustomerName = dt.Rows[i][1].ToString(),
MaidenName = dt.Rows[i][2].ToString(),
SocialSecurityNo = dt.Rows[i][3].ToString(),
TaxNo = dt.Rows[i][4].ToString(),
Title = dt.Rows[i][5].ToString(),
DateOfBirth = dt.Rows[i][6].ToString(),
PlaceOfBirth = dt.Rows[i][7].ToString(),
Religion = dt.Rows[i][8].ToString(),
StateOfOrigin = dt.Rows[i][9].ToString(),
Nationality = dt.Rows[i][10].ToString(),
Heigth = dt.Rows[i][11].ToString(),
Complextion = dt.Rows[i][12].ToString(),
EyeColor = dt.Rows[i][13].ToString(),
Disability = dt.Rows[i][14].ToString(),
Race = dt.Rows[i][15].ToString(),
DocumentType = dt.Rows[i][16].ToString(),
DocumentNo = dt.Rows[i][17].ToString(),
IssuingAuth = dt.Rows[i][18].ToString(),
IssuingCountry = dt.Rows[i][19].ToString(),
IssuingState = dt.Rows[i][20].ToString(),
IssuingDate = dt.Rows[i][21].ToString(),
ExpiryDate = dt.Rows[i][22].ToString(),
IssuingCity = dt.Rows[i][23].ToString(),
IncomeSource = dt.Rows[i][24].ToString(),
IncomeSegment = dt.Rows[i][25].ToString(),
IncomeBand = dt.Rows[i][26].ToString(),
AccountNumber = dt.Rows[i][27].ToString(),
Branch = dt.Rows[i][28].ToString(),
AccountType = dt.Rows[i][29].ToString(),
Currency = dt.Rows[i][30].ToString(),
BankCode = dt.Rows[i][31].ToString(),
BankName = dt.Rows[i][32].ToString(),
NOKFamilyRelationshipType = dt.Rows[i][33].ToString(),
NOKFullName = dt.Rows[i][34].ToString(),
NOKAddress = dt.Rows[i][35].ToString(),
NOKTelephoneNumber = dt.Rows[i][36].ToString(),
NOKDateOfBirth = dt.Rows[i][37].ToString(),
NOKEmploymentDetail = dt.Rows[i][38].ToString(),
NOKOccupation = dt.Rows[i][39].ToString(),
ArrivalDate = dt.Rows[i][40].ToString(),
DepartureDate = dt.Rows[i][41].ToString(),
VisaValidFrom = dt.Rows[i][42].ToString(),
VisaValidTill = dt.Rows[i][43].ToString(),
PassportNumber = dt.Rows[i][44].ToString(),
PassportIssueDate = dt.Rows[i][45].ToString(),
PassportExpiryDate = dt.Rows[i][46].ToString(),
PassportIssueCountry = dt.Rows[i][47].ToString(),
ResidentPermitNumber = dt.Rows[i][48].ToString(),
CaseNumber = dt.Rows[i][49].ToString(),
CaseDescription = dt.Rows[i][50].ToString(),
CaseDate = dt.Rows[i][51].ToString(),
PoliceSummation = dt.Rows[i][52].ToString(),
FamilyRelationship = dt.Rows[i][53].ToString(),
FamilyFullname = dt.Rows[i][54].ToString(),
FamilyAddress = dt.Rows[i][55].ToString(),
FamilyPhoneNumber = dt.Rows[i][56].ToString(),
FamilyDateOfBirth = dt.Rows[i][57].ToString(),
FamilyEmploymentDetail = dt.Rows[i][58].ToString(),
FamilyOccupation = dt.Rows[i][59].ToString(),
EmploymentType = dt.Rows[i][60].ToString(),
CurrentEmployerName = dt.Rows[i][61].ToString(),
CurrentEmployerAddress = dt.Rows[i][62].ToString(),
CurrentEmployerPhone = dt.Rows[i][63].ToString(),
CurrentEmployerPositionHeld = dt.Rows[i][64].ToString(),
EmploymentDate = dt.Rows[i][65].ToString(),
PreviousEmployerAddress = dt.Rows[i][66].ToString(),
PreviousEmployerName = dt.Rows[i][67].ToString(),
PrevEmployerPositionHeld = dt.Rows[i][68].ToString(),
TimeSpentWithPrevEmployer = dt.Rows[i][69].ToString(),
BusinessOccupation = dt.Rows[i][70].ToString(),
};
BulkCustIndividualList.Add(IndivObj);
}
//int cnt = 0;
//foreach (CustomerIndividualBulkUpload item in BulkCustIndividualList)
//{
totalRec += InsertExcelData(BulkCustIndividualList);
// //UpdateRefData(item);
//}
}//end of else
if (totalRec > 0)
{
//lnkView.Visible = true;
}
lblMsg.Text = MessageFormatter.GetFormattedSuccessMessage(string.Format("Uploaded {0} records", totalRec));
}//end of else
}
catch (Exception ex)
{
lblMsg.Text = MessageFormatter.GetFormattedErrorMessage("Error: " + ex.Message + " at row " + (currRow + 1));
}
}
private void btnRead_Click(object sender, EventArgs e)
{
radioButton1.Enabled = false;
radioButton2.Enabled = false;
int StartDay = 0, EndDay = 0;
var CheckFormat = filePath.Split('.').Last();
AllowSaveCode = true;
AllowSaveNull = true;
list.Clear();
Regex emp1 = new Regex(@"1(3|4)(9|0)[\d]/(0[1-9]|1[0-2])/(0[1-9]|[1-2][\d]|3[0-1])");
if (CheckFormat.CompareTo("xls") == 0 || CheckFormat.CompareTo("xlsx") == 0)
{
try
{
//open excel
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//...
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
//IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
//...
//4. DataSet - Create column names from first row
//excelReader.IsFirstRowAsColumnNames = true;
//DataSet result = excelReader.AsDataSet();
if (radioButton2.Checked)
{
var Sheet2 = result.Tables[0];
//var rrr = Sheet2[1].ItemArray[1];
int count = 0;
TraySchedule NimehShab;
var Datemounth = Sheet2.Rows[3][1].ToString().Substring(5, 2);
if (Datemounth.CompareTo("06") == 1)
{
EndDay = 1;
}
foreach (DataRow item in Sheet2.Rows)
{
if (count < 34 - EndDay & count > 2 + StartDay & AllowTrueDate)
{
NimehShab = new TraySchedule();
try
{
var Date = item[1].ToString();
var IsTrueDate = emp1.IsMatch(Date);
if (!IsTrueDate)
{
MessageBox.Show(string.Format(" تاریخ {0} را با فرمت درست وارد کنید", Date));
AllowTrueDate = false;
}
List <TrayItem> t = new List <TrayItem>();
NimehShab.tray = CreateTray(Convert.ToInt16(item[5]), 0, out t);
NimehShab.schedule = CreateSchedule(Date, 3);
NimehShab.trayItem = t.ToList();
t.Clear();
list.Add(NimehShab);
}
catch (IOException)
{
MessageBox.Show("فایل اکسل را ببندید");
}
catch (InvalidCastException)
{
AllowSaveNull = false;
MessageBox.Show(" دارای فیلد خالی");
}
catch
{
MessageBox.Show("");
}
finally
{
//5. Data Reader methods
while (excelReader.Read())
{
//excelReader.GetInt32(0);
}
}
}
count = count + 1;
}
}
else
{
TraySchedule Lunch1;
TraySchedule Lunch2;
TraySchedule Lunch3;
TraySchedule Dinner1;
TraySchedule Dinner2;
TraySchedule Dinner3;
var Sheet2 = result.Tables["code"];
//var rrr = Sheet2[1].ItemArray[1];
int count = 0;
var Datemounth = Sheet2.Rows[3][2].ToString().Substring(5, 2);
if (Datemounth.CompareTo("06") == 1)
{
EndDay = 1;
}
foreach (DataRow item in Sheet2.Rows)
{
if (count < 34 - EndDay & count > 2 + StartDay & AllowTrueDate)
{
Lunch1 = new TraySchedule();
Lunch2 = new TraySchedule();
Lunch3 = new TraySchedule();
Dinner1 = new TraySchedule();
Dinner2 = new TraySchedule();
Dinner3 = new TraySchedule();
try
{
var Date = item[2].ToString();
var IsTrueDate = emp1.IsMatch(Date);
if (!IsTrueDate)
{
MessageBox.Show(string.Format(" تاریخ {0} را با فرمت درست وارد کنید", Date));
AllowTrueDate = false;
}
List <TrayItem> t = new List <TrayItem>();
Lunch1.tray = CreateTray(Convert.ToInt16(item[4]), Convert.ToInt16(item[5]), out t);
Lunch1.schedule = CreateSchedule(Date, 1);
Lunch1.trayItem = t.ToList();
t.Clear();
Lunch2.tray = CreateTray(Convert.ToInt16(item[6]), Convert.ToInt16(item[7]), out t);
Lunch2.schedule = CreateSchedule(Date, 1);
Lunch2.trayItem = t.ToList();
t.Clear();
Lunch3.tray = CreateTray(Convert.ToInt16(item[8]), Convert.ToInt16(item[9]), out t);
Lunch3.schedule = CreateSchedule(Date, 1);
Lunch3.trayItem = t.ToList();
t.Clear();
Dinner1.tray = CreateTray(Convert.ToInt16(item[14]), Convert.ToInt16(item[15]), out t);
Dinner1.schedule = CreateSchedule(Date, 2);
Dinner1.trayItem = t.ToList();
t.Clear();
Dinner2.tray = CreateTray(Convert.ToInt16(item[16]), Convert.ToInt16(item[17]), out t);
Dinner2.schedule = CreateSchedule(Date, 2);
Dinner2.trayItem = t.ToList();
t.Clear();
Dinner3.tray = CreateTray(Convert.ToInt16(item[18]), Convert.ToInt16(item[19]), out t);
Dinner3.schedule = CreateSchedule(Date, 2);
Dinner3.trayItem = t.ToList();
t.Clear();
list.Add(Lunch1);
list.Add(Lunch2);
list.Add(Lunch3);
list.Add(Dinner1);
list.Add(Dinner2);
list.Add(Dinner3);
}
catch (IOException)
{
MessageBox.Show("فایل اکسل را ببندید");
}
catch (InvalidCastException)
{
AllowSaveNull = false;
MessageBox.Show(" دارای فیلد خالی");
}
catch
{
MessageBox.Show("");
}
finally
{
//5. Data Reader methods
while (excelReader.Read())
{
//excelReader.GetInt32(0);
}
}
}
count = count + 1;
}
}
}
catch (IOException)
{
MessageBox.Show("فایل انتخاب شده را ببندید");
AllowSaveNull = false;
}
//6. Free resources (IExcelDataReader is IDisposable)
excelReader.Close();
AllowSaveCode = list.All(p => p.tray != null);
// ----------------------------------------------------------
if (AllowSaveNull & AllowSaveCode & AllowTrueDate)
{
lblNotification.Text = "بارگذاری فایل اکسل با موفقیت انجام شد";
lblNotification.ForeColor = Color.DarkGreen;
btnBazbini.Visible = true;
btnEnteshar.Visible = true;
if (radioButton2.Checked)
{
btnEnteshar.Enabled = false;
}
else
{
btnEnteshar.Enabled = true;
}
}
if (!AllowTrueDate)
{
lblNotification.Text = "فایل را دوباره بارگذاری کنید";
lblNotification.ForeColor = Color.DarkRed;
AllowTrueDate = true;
btnBazbini.Visible = false;
btnEnteshar.Visible = false;
}
}
else
{
MessageBox.Show("فایل ورودی اکسل نیست ");
}
}
/// <summary>
/// Opens the specified file
/// </summary>
/// <param name="fileName">Path to file.</param>
/// <returns>Feature set from file data.</returns>
/// <exception cref="Exception">Throws if no sheets in the file, or
/// Latitude or Longitude column not found.</exception>
public IDataSet Open(string fileName)
{
var fs = new FeatureSet
{
Name = Path.GetFileNameWithoutExtension(fileName),
Filename = fileName
};
var extension = Path.GetExtension(fileName);
IExcelDataReader reader = null;
var stream = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read);
try
{
switch (extension)
{
case ".xls":
reader = ExcelReaderFactory.CreateBinaryReader(stream);
break;
case ".xlsx":
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
break;
default:
goto case ".xls";
}
reader.IsFirstRowAsColumnNames = true;
var ds = reader.AsDataSet();
if (ds.Tables.Count == 0)
{
throw new Exception("There is no sheets in the file");
}
// Find first table (sheet) with Latitude/Longitude columns.
DataTable table = null;
int latColumnIndex = -1;
int lngColumnIndex = -1;
for (int i = 0; i < ds.Tables.Count; i++)
{
table = ds.Tables[i];
latColumnIndex = table.Columns.IndexOf("Latitude");
lngColumnIndex = table.Columns.IndexOf("Longitude");
if (latColumnIndex >= 0 && lngColumnIndex >= 0)
{
break;
}
}
if (latColumnIndex == -1 || lngColumnIndex == -1)
{
throw new Exception("Latitude or Longitude column not found");
}
Debug.Assert(table != null);
for (int i = 0; i < table.Columns.Count; i++)
{
string sFieldName = table.Columns[i].ColumnName;
Type type = table.Columns[i].DataType;
int uniqueNumber = 1;
string uniqueName = sFieldName;
while (fs.DataTable.Columns.Contains(uniqueName))
{
uniqueName = sFieldName + uniqueNumber;
uniqueNumber++;
}
fs.DataTable.Columns.Add(new DataColumn(uniqueName, type));
}
reader.Read();
while (reader.Read())
{
var lat = reader.GetDouble(latColumnIndex);
var lng = reader.GetDouble(lngColumnIndex);
IGeometry geometry = new Point(lng, lat);
IFeature feature = new Feature(geometry);
feature.DataRow = fs.DataTable.NewRow();
for (int j = 0; j < reader.FieldCount; j++)
{
object value = reader.GetValue(j) ?? DBNull.Value;
feature.DataRow[j] = value;
}
fs.Features.Add(feature);
}
}finally
{
if (reader != null)
{
reader.Close();
}
stream.Close();
}
fs.Projection = ProjectionInfo.FromEsriString(Resources.wgs_84_esri_string);
return(fs);
}
public async Task <string> uploadfiles(HttpPostedFileBase fileInput1, HttpPostedFileBase fileInput2, HttpServerUtilityBase server)
{
try
{
await Task.Run(async() => {
//Guardar el archivo como temporal
string TempDirectory = Path.Combine(this.GetTemporaryDirectory() + "\\");
string FileName1 = TempDirectory + "Layout_" + fileInput1.FileName;
string FileName2 = "";
if (fileInput2 != null)
{
FileName2 = TempDirectory + "Layout_" + fileInput2.FileName;
}
fileInput1.SaveAs(FileName1);
if (fileInput2 != null)
{
fileInput2.SaveAs(FileName2);
}
Stream stream1 = fileInput1.InputStream;
Stream stream2 = Stream.Null;
if (fileInput2 != null)
{
stream2 = fileInput2.InputStream;
}
// We return the interface, so that
IExcelDataReader reader1 = null;
IExcelDataReader reader2 = null;
if (fileInput1.FileName.EndsWith(".xls"))
{
reader1 = ExcelReaderFactory.CreateBinaryReader(stream1);
}
else if (fileInput1.FileName.EndsWith(".xlsx"))
{
reader1 = ExcelReaderFactory.CreateOpenXmlReader(stream1);
}
if (fileInput2 != null)
{
if (fileInput2.FileName.EndsWith(".xls"))
{
reader2 = ExcelReaderFactory.CreateBinaryReader(stream2);
}
else if (fileInput2.FileName.EndsWith(".xlsx"))
{
reader2 = ExcelReaderFactory.CreateOpenXmlReader(stream2);
}
}
reader1.IsFirstRowAsColumnNames = true;
if (fileInput2 != null)
{
reader2.IsFirstRowAsColumnNames = true;
}
DataSet result1 = reader1.AsDataSet();
DataSet result2 = null;
if (fileInput2 != null)
{
result2 = reader2.AsDataSet();
}
reader1.Close();
if (fileInput2 != null)
{
reader2.Close();
}
DataTable TSheet1 = result1.Tables[0];
DataTable TSheet2 = new DataTable();
if (fileInput2 != null)
{
TSheet2 = result2.Tables[0];
}
#region File1
List <DataPresupuesto> data = new List <DataPresupuesto>();
int currentRow = 5;
int totalRow = TSheet1.Rows.Count;
//foreach (DataRow row in TSheet1.Rows)
for (int nrow = currentRow; nrow < totalRow; nrow++)
{
DataRow row = TSheet1.Rows[nrow];
String NumProject = row[1].ToString();
//Encuentra la primer partida....
DataRow partidRow = TSheet1.Rows[nrow + 1];
List <LCalendario> Modificado = new List <LCalendario>();
List <LCalendario> Comprometido = new List <LCalendario>();
List <LCalendario> Devengado = new List <LCalendario>();
List <LCalendario> Ejercido = new List <LCalendario>();
DataRow modificadoRow = TSheet1.Rows[nrow + 2];
DataRow comprometidoRow = TSheet1.Rows[nrow + 3];
DataRow devengadoRow = TSheet1.Rows[nrow + 4];
DataRow ejercidoRow = TSheet1.Rows[nrow + 5];
//Sacar los valores del modificado
for (int col = 3; col <= 14; col++)
{
Modificado.Add(new LCalendario {
Mes = (LCalendario.Meses)(col - 2), Monto = float.Parse(modificadoRow[col].ToString())
});
}
//Sacar los valores del Comprometido
for (int col = 3; col <= 14; col++)
{
Comprometido.Add(new LCalendario {
Mes = (LCalendario.Meses)(col - 2), Monto = float.Parse(comprometidoRow[col].ToString())
});
}
//Sacar los valores del Devengado
for (int col = 3; col <= 14; col++)
{
Devengado.Add(new LCalendario {
Mes = (LCalendario.Meses)(col - 2), Monto = float.Parse(devengadoRow[col].ToString())
});
}
//Sacar los valores del Ejercido
for (int col = 3; col <= 14; col++)
{
Ejercido.Add(new LCalendario {
Mes = (LCalendario.Meses)(col - 2), Monto = float.Parse(ejercidoRow[col].ToString())
});
}
data.Add(new DataPresupuesto
{
IdProyecto = NumProject,
ClvPartida = int.Parse(partidRow[1].ToString()),
Modificado = Modificado.ToList(),
Comprometido = Comprometido.ToList(),
Devengado = Devengado.ToList(),
Ejercido = Ejercido.ToList(),
});
//Al final recorrer la lista data y guardar los valores en las tablas necesarias
nrow = nrow + 6;
}
#endregion file 1
await this.saveDatabase(data, (fileInput2 != null ? TSheet2.Rows: null));
//int totalRow2 = TSheet2.Rows.Count;
});
return("");
}
catch (Exception ex)
{
throw (ex);
}
}
public static bool SaveAsCsv(string excelFilePath, string destinationCsvFilePath)
{
using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader = null;
if (excelFilePath.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (excelFilePath.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
if (reader == null)
{
return(false);
}
var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = false
}
});
for (int tableCounter = 0; tableCounter < ds.Tables.Count; tableCounter++)
{
var csvContent = string.Empty;
int row_no = 0;
while (row_no < ds.Tables[tableCounter].Rows.Count)
{
var percentage = ((double)row_no / (double)ds.Tables[tableCounter].Rows.Count) * 100;
percentage = Math.Round(percentage, 2);
Console.Write($"Processing row #{row_no} / {ds.Tables[tableCounter].Rows.Count} ({percentage}%)\r");
var arr = new List <string>();
for (int i = 0; i < ds.Tables[tableCounter].Columns.Count; i++)
{
var currArr = ds.Tables[tableCounter].Rows[row_no][i];
if (!double.TryParse(currArr.ToString(), out var currArrDeci))
{
arr.Add(ds.Tables[tableCounter].Rows[row_no][i].ToString());
}
else if (currArrDeci % 1 != 0)
{
arr.Add(currArrDeci.ToString("N", customNumFormat));
}
else
{
arr.Add(ds.Tables[tableCounter].Rows[row_no][i].ToString());
}
}
row_no++;
csvContent += string.Join(";", arr) + "\n";
}
var fileName = Path.GetFileNameWithoutExtension(excelFilePath);
fileName = (ds.Tables[tableCounter].TableName) + "_" + fileName + ".csv";
var destinationPath = Path.GetDirectoryName(destinationCsvFilePath);
destinationCsvFilePath = destinationPath + "/" + fileName;
StreamWriter csv = new StreamWriter(destinationCsvFilePath, false);
csv.Write(csvContent);
csv.Close();
Console.WriteLine($"CSV Saved: /Output/{Path.GetFileName(destinationCsvFilePath)}");
}
return(true);
}
}
public void NormalizeFiles(string FolderPath, string result_file, string d_date)
{
string[] files = Directory.GetFiles(FolderPath);
List <string> deal_names = new List <string>();
deal_names = Get_normalized_deals(FolderPath, result_file);
foreach (string f in files)
{
Globals.ThisAddIn.Application.ActiveCell.Value = f;
string file_path = Path.Combine(FolderPath, f);
FileStream stream = File.Open(file_path, FileMode.Open, FileAccess.Read);
System.Data.DataSet result = null;
var file_name = file_path.Split('\\').Last().Replace(".xlsx", "").Replace(".xls", "").Replace(".csv", "").Replace(".CSV", "").Replace(".XLSX", "").Replace(".XLS", "");
if (deal_names.Contains(file_name) || file_name.Contains("_back_test_ratings") || file_name.Contains("_back_test_data") || file_name.Contains("_tara_setup_tapes"))
{
} //do nothing
else
{
try
{
using (IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
result = reader.AsDataSet();
}
}
catch
{
try
{
using (IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(stream))
{
result = reader.AsDataSet();
}
}
catch
{
Console.WriteLine(result);
}
}
if (result != null)
{
string server = Globals.ThisAddIn.Get_base_url();
using (WebClient client = new WebClient())
{
string responsebody = "";
var reqparm = new System.Collections.Specialized.NameValueCollection();
string JSON_data = JsonConvert.SerializeObject(result);
string deal_name = stream.Name;
reqparm.Add("param1", JSON_data);
reqparm.Add("param2", deal_name);
reqparm.Add("get_sup", "yes");
reqparm.Add("backtest", "yes");
reqparm.Add("d_date", d_date);
reqparm.Add("irp", "yes");
byte[] responsebytes = client.UploadValues(server + "create_standard_data_sheet", "POST", reqparm);
responsebody = Encoding.UTF8.GetString(responsebytes);
responsebody = responsebody.Replace("}{", "|").Replace("}", "").Replace("[", "").Replace("{", "");
Array raw_loan_data = responsebody.Split('|');
string out_f = result_file + ".csv";
string written_file = Response_to_csv(raw_loan_data, FolderPath, out_f, true, d_date, true);
}
}
}
stream.Close();
}
}