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(); } }