public string UploadHandler() { HttpPostedFile httpPostedFileBase = System.Web.HttpContext.Current.Request.Files[0]; if (httpPostedFileBase != null) { string[] file = httpPostedFileBase.FileName.Split('.'); if (file.Length > 0) { if ((file[file.Length - 1].ToString()) == "xlsx" || (file[file.Length - 1].ToString() == "xls")) { DataTable dt; ExcelFileReader xlReader = new ExcelFileReader(); dt = xlReader.GetExcelDataTable(HttpContext.Request.Files[0]); TempData["ExcelData"] = dt; return(httpPostedFileBase.FileName); } } } else { return("1"); } return("0"); }
private bool ReadRow(ExcelWorksheet excelWorksheet, ExcelFileReader reader, int lineIndex, TParsed row) { bool isEmptyRow = true; switch (reader.DatasetOrientation) { case DataOrientation.Horizontal: for (int i = 0; i < reader.DataRange.Rows; i++) { if (reader.PropertySetters[i].SetValue(excelWorksheet, row, i + reader.DataRange.Start.Row, lineIndex + reader.DataRange.Start.Column)) { isEmptyRow = false; } } break; case DataOrientation.Vertical: for (int i = 0; i < reader.DataRange.Columns; i++) { if (reader.PropertySetters[i].SetValue(excelWorksheet, row, lineIndex + reader.DataRange.Start.Row, i + reader.DataRange.Start.Column)) { isEmptyRow = false; } } break; default: break; } return(!isEmptyRow); }
/// <summary> /// The main method /// </summary> /// <param name="args">The arguments.</param> static void Main(string[] args) { // Retrieve project path string projectPath = AppDomain.CurrentDomain.BaseDirectory.Substring(0, AppDomain.CurrentDomain.BaseDirectory.LastIndexOf("bin")); // Retrieve template document. var templatePath = $"{projectPath}\\Template\\UserList.xlsx"; var fileAsStream = new MemoryStream(File.ReadAllBytes(templatePath)); // Import file with ExcelFileReader From File info to List of dictionary with header List <Dictionary <string, object> > dictionaryResultFromFileInfo = ExcelFileReader.ImportFileAsDictionary(new FileInfo(templatePath), "Users", true); // Import file with ExcelFileReader From File Stream to List of dictionary without header List <Dictionary <string, object> > dictionaryResultFromFileStream = ExcelFileReader.ImportFileAsDictionary(fileAsStream, "Users"); // Import file with ExcelFileReader From File info to Data table DataTable dataTableResultFromFileInfo = ExcelFileReader.ImportFileAsDataTable(new FileInfo(templatePath), "Users"); // Import file with ExcelFileReader From File Stream to Data table DataTable dataTableResultFromFileStream = ExcelFileReader.ImportFileAsDataTable(fileAsStream, "Users"); // Export file with ExcelFileWriter string tempFilePath = $"{projectPath}\\App_Data\\{Guid.NewGuid()}.xlsx"; var streamExport = ExcelFileWriter.ExportToExcel(dataTableResultFromFileStream, "Users"); FileStream file = new FileStream(tempFilePath, FileMode.Create, FileAccess.Write); streamExport.WriteTo(file); file.Close(); streamExport.Close(); Console.Write($"Generated file path : {tempFilePath}"); Console.ReadLine(); }
private void btnLoadExcel_Click(object sender, EventArgs e) { if (opfLoadExcel.ShowDialog() == DialogResult.OK) { DataTable loadExcelData = ExcelFileReader.ReadExcelSheet(opfLoadExcel.FileName, true); this.dataGridView1.DataSource = loadExcelData; } }
static void Main(string[] args) { string excelfile = Environment.CurrentDirectory.Replace("\\bin\\Debug", "\\ConditionsExcel.xlsx"); using (dynamic rFile = new ExcelFileReader <dynamic>(excelfile)) { var ExcelSheet = new List <string[]>(); foreach (var line in rFile) { string[] ConditionLine = { line.Name, line.Explanation, line.Action, line.Get("Commonly known as"), line.Abbrevations, line.Get("See also") }; ExcelSheet.Add(ConditionLine); } var data = ExcelSheet.ToArray(); for (int i = 0; i <= data.Length - 1; i++) { var Array = data[i]; for (int j = 0; j <= Array.Length - 1; j++) { Console.WriteLine(j + " " + Array[j]); } string boundaries = "***********************"; Console.WriteLine(boundaries); Console.ReadLine(); } } /* * * * StreamReader sr = new StreamReader(filepath); * var lines = new List<string[]>(); * int Row = 0; * while (!sr.EndOfStream) { * string[] Line = sr.ReadLine().Split(','); * * lines.Add(Line); * Row++; * } * var data = lines.ToArray(); * * for (int i = 0; i <= data.Length - 1; i++) { * var Array = data[i]; * for (int j = 0; j <= Array.Length - 1; j++) { * * Console.WriteLine(j + " " + Array[j]); * } * string boundaries = "***********************"; * Console.WriteLine(boundaries); * Console.ReadLine(); * } * * * */ Console.ReadLine(); }
public void UpdateAttributes() { var FilePath = "E:\\Plottefil\\PlottingFileConfig.xlsx"; var ExcelReader = new ExcelFileReader(FilePath); var Settings = ExcelReader.ReadFile(); var CAC = new ChangeAttributes(); //CAC.UPDATEATTRIBUTES(Settings, Settings.DrawingData[0].revDate.ToShortDateString()); }
public void t2() { var reader = new ExcelFileReader(); var itmes = reader.ReadDistinctDisciplines( @"D:\dev\C#\ExposurePresenter\XLSInputFiles\PLK Grzegorz branże.xlsx").ToArray(); // var db = new ExposureDb(); // foreach (var item in itmes) // { // db.ExposureRecords.Add(item); // } }
public void CanCountColumnsOnFirstRow() { var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx"); reader.SetActiveSheet("ElementData"); reader.ReadRow(); Assert.That(reader.GetColumnCount(), Is.EqualTo(5)); }
public void WillReturnFalseIfColumnDoesntExist() { var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx"); reader.SetActiveSheet("ElementData"); reader.ReadRow(); Assert.That(reader.GetColumnCount(), Is.EqualTo(5)); string result = ""; Assert.That(reader.ReadColumnAsText(8, out result), Is.False); Assert.That(result, Is.EqualTo("")); }
public void PopulateData(string filePath, ExposureDb db) { var fileReader = new ExcelFileReader(); db.Branches.AddOrUpdate(branch => branch.Name, fileReader.ReadDistinctBrnaches(filePath).ToArray()); db.Brands.AddOrUpdate(brand => brand.Name, fileReader.ReadDistinctBrands(filePath).ToArray()); db.Disciplines.AddOrUpdate(discipline => discipline.Name, fileReader.ReadDistinctDisciplines(filePath).ToArray()); var records = fileReader.ReadExposureRecords(filePath).ToArray(); foreach (var exposureRecord in records) { db.ExposureRecords.AddOrUpdate(record => new { record.Brand, record.Month }, exposureRecord); db.SaveChanges(); } }
public void CanReadInt() { var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx"); reader.SetActiveSheet("ElementData"); reader.ReadRow(); reader.ReadRow(); int result = 0; Assert.That(reader.ReadColumnAsInt(0, out result), Is.True); Assert.That(result, Is.EqualTo(28)); Assert.That(reader.ReadColumnAsInt(1, out result), Is.True); Assert.That(result, Is.EqualTo(3024)); Assert.That(reader.ReadColumnAsInt(3, out result), Is.True); Assert.That(result, Is.EqualTo(302428)); }
public MainWindow() { var windowThrottleInterval = TimeSpan.FromSeconds(1); _windowLocationSaveMethod = new MethodThrottle(SaveWindowLocation, windowThrottleInterval); _windowSizeSaveMethod = new MethodThrottle <Size>(SaveWindowSize, windowThrottleInterval); _windowStateSaveMethod = new MethodThrottle(SaveWindowState, windowThrottleInterval); _documentFileReader = new DocumentFileReader(); _documentFileWriter = new DocumentFileWriter(); _excelFileReader = new ExcelFileReader(); _resXFileReader = new ResXFileReader(); _resXFileWriter = new ResXFileWriter(); InitializeComponent(); SetupInitialWindow(); LoadCommandLineFile(); UpdateTitle(); }
public void CanReadDecimal() { var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx"); reader.SetActiveSheet("DesignData"); reader.ReadRow(); reader.ReadRow(); decimal result = 0; Assert.That(reader.ReadColumnAsDecimal(5, out result), Is.True); Assert.That(result, Is.EqualTo(0.4d)); reader.ReadRow(); Assert.That(reader.ReadColumnAsDecimal(5, out result), Is.True); Assert.That(result, Is.EqualTo(1.2d)); Assert.That(reader.ReadColumnAsDecimal(6, out result), Is.True); Assert.That(result, Is.EqualTo(0.4d)); }
private bool ReadRow(ExcelWorksheet excelWorksheet, ExcelFileReader reader, int lineIndex, IDictionary <string, object> row) { //TODO: better exception handling here bool isEmptyRow = true; switch (reader.DatasetOrientation) { case DataOrientation.Horizontal: for (int i = 0; i < reader.DataRange.Rows; i++) { if (reader.PropertySetters.TryGetValue(i, out var propertySetter)) { if (propertySetter.SetValue(excelWorksheet, i + reader.DataRange.Start.Row, lineIndex + reader.DataRange.Start.Column)) { isEmptyRow = false; row[propertySetter.PropertyInfo.Name] = propertySetter.ParsedValue; } } } break; case DataOrientation.Vertical: for (int i = 0; i < reader.DataRange.Columns; i++) { if (reader.PropertySetters.TryGetValue(i, out var propertySetter)) { if (propertySetter.SetValue(excelWorksheet, lineIndex + reader.DataRange.Start.Row, i + reader.DataRange.Start.Column)) { isEmptyRow = false; row[propertySetter.PropertyInfo.Name] = propertySetter.ParsedValue; } } } break; default: break; } return(!isEmptyRow); }
public void CanReadSeveralRows() { var path = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var reader = new ExcelFileReader(path + "\\BrickRepoTests_BrickList01.xlsx"); reader.SetActiveSheet("ElementData"); reader.ReadRow(); Assert.That(reader.GetColumnCount(), Is.EqualTo(5)); string result = ""; Assert.That(reader.ReadColumnAsText(0, out result), Is.True); Assert.That(result, Is.EqualTo("MaterialID")); reader.ReadRow(); Assert.That(reader.ReadColumnAsText(0, out result), Is.True); Assert.That(result, Is.EqualTo("28")); reader.ReadRow(); Assert.That(reader.ReadColumnAsText(0, out result), Is.True); Assert.That(result, Is.EqualTo("37")); }
private ExcelFileProcess ProcessExpenseSheet(string path, string fileName) { logger.DebugFormat("Processing Expense Sheet Name [{0}]", fileName); DataSet ds = ExcelFileReader.Read(path); DataTable dataTable = ds.Tables[0]; dataTable = dataTable.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((Convert.ToString(field)).Trim(), string.Empty) == 0)).CopyToDataTable(); if (dataTable.Rows.Count == 0) { logger.DebugFormat("No rows found in file [{0}]", fileName); return(new ExcelFileProcess { Message = "No rows to insert.", Response = false, MessageType = MessageClass.Error }); } string missingcolumn = ExcelFileReader.CheckAllColumnExist(dataTable, ColumnList); if (!string.IsNullOrWhiteSpace(missingcolumn)) { logger.Debug(string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn)); return(new ExcelFileProcess { Message = string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn), Response = false, MessageType = MessageClass.Error }); } string info = ExcelFileReader.ValidateData(dataTable, MandatoryColumnList); if (!string.IsNullOrWhiteSpace(info)) { logger.Debug(info); return(new ExcelFileProcess { Message = info, Response = false, MessageType = MessageClass.Error }); } string errors = ExcelFileReader.ValidateDataFormat(dataTable); if (!string.IsNullOrWhiteSpace(errors)) { logger.Debug(errors); return(new ExcelFileProcess { Message = errors, Response = false, MessageType = MessageClass.Error }); } var userCards = userCardManagement.GetAllUserCards(); string accNumberError = ExcelFileReader.ValidateAccountNumber(dataTable, userCards); if (!string.IsNullOrWhiteSpace(accNumberError)) { logger.Debug(accNumberError); return(new ExcelFileProcess { Message = accNumberError, Response = false, MessageType = MessageClass.Error }); } ExcelFileProcess process = InsertData(dataTable, fileName, userCards); return(process); }
public void Setup() { _fileReader = new ExcelFileReader(); }
public void ReadExcelFile() { ExcelFileReader EFR = new ExcelFileReader("O:\\A070000\\A074115\\CAD\\E40 RIE\\E99\\PlottingFileConfig.xlsx"); EFR.ReadFile(); }
private ExcelFileProcess ProcessExpenseSheet(string path, string fileName, DateTime expenseDate) { logger.DebugFormat("Processing User Expense Sheet Name [{0}]", fileName); DataSet ds = ExcelFileReader.Read(path); DataTable table = ds.Tables[0]; table = table.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is System.DBNull || string.Compare((Convert.ToString(field)).Trim(), string.Empty) == 0)).CopyToDataTable(); if (table.Rows.Count == 0) { logger.DebugFormat("No rows found in file [{0}]", fileName); return(new ExcelFileProcess { Message = "No rows to insert.", Response = false, MessageType = MessageClass.Error }); } string missingcolumn = ExcelFileReader.CheckAllColumnExist(table, ColumnList); if (!string.IsNullOrWhiteSpace(missingcolumn)) { logger.Debug(string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn)); return(new ExcelFileProcess { Message = string.Format(Resources.Messages.MSG_GENERIC_COLUMN_MISSING, missingcolumn), Response = false, MessageType = MessageClass.Error }); } string info = ExcelFileReader.ValidateData(table, MandatoryColumnList); if (!string.IsNullOrWhiteSpace(info)) { logger.Debug(info); return(new ExcelFileProcess { Message = info, Response = false, MessageType = MessageClass.Error }); } string errors = ExcelFileReader.ValidateAmount(table, ValidateColumnList); if (!string.IsNullOrWhiteSpace(errors)) { logger.Debug(errors); return(new ExcelFileProcess { Message = errors, Response = false, MessageType = MessageClass.Error }); } var department = departmentManagement.GetAllDepartments(); var allocation = userAllocationManagement.GetAllUsersActiveAllocations(); var users = UserManager.Users.Where(x => !x.IsDeleted).ToList(); errors = ExcelFileReader.ValidateDepartmentAndUsersAndAllocation(table, department, users, allocation); if (!string.IsNullOrWhiteSpace(errors)) { logger.Debug(errors); return(new ExcelFileProcess { Message = errors, Response = false, MessageType = MessageClass.Error }); } ExcelFileProcess process = InsertData(table, fileName, users, expenseDate, allocation); return(process); }
public ProductInfoReader() { excelReader = new ExcelFileReader(); }
public bool Convert(TableInfo info) { Debug.Log(">>转换表格:【" + info.m_RealName + "】开始!!!" + info.m_FullName); m_TabInfo = info; m_WorkBook = ExcelFileReader.ReadExcelFile(m_TabInfo.m_FullName, ""); if (m_WorkBook == null) { string infoStr = "表格:【" + m_TabInfo.m_RealName + "】无法打开. 请检查该表是否被其他程序占用!!!"; Debug.LogError(infoStr); if (EditorUtility.DisplayDialog("提示", infoStr, "确定", "停止")) { return(true); } else { return(false); } } //return true; m_Sheet = m_WorkBook.GetSheetAt(0); if (m_Sheet == null) { string infoStr = "表格:【" + m_TabInfo.m_RealName + "】数据为空. 请检查内部是否有数据!!!\n"; Debug.LogError(infoStr); if (EditorUtility.DisplayDialog("提示", infoStr, "确定", "停止")) { return(true); } else { return(false); } } m_FirstRowIdx = m_Sheet.FirstRowNum; m_LastRowIdx = m_Sheet.LastRowNum; m_NameRow = m_Sheet.GetRow(0); m_TypeRow = m_Sheet.GetRow(1); m_FirstColumIdx = m_NameRow.FirstCellNum; m_LastColumIdx = m_NameRow.LastCellNum; // 确定有效行索引和列索引 IRow rowData = null; for (int row = 3; row <= m_LastRowIdx; ++row) { rowData = m_Sheet.GetRow(row); // 遇到空行则跳过 if (rowData == null) { m_LastRowIdx = row - 1; break; } // 如果该行无数据则跳过 if (rowData.GetCell(0) == null) { m_LastRowIdx = row - 1; break; } if (string.IsNullOrEmpty(rowData.GetCell(0).ToString())) { m_LastRowIdx = row - 1; break; } } ICell cellData = null; for (int cell = m_FirstColumIdx; cell <= m_LastColumIdx; ++cell) { cellData = m_NameRow.GetCell(cell); if (cellData == null) { m_LastColumIdx = cell - 1; break; } string varName = cellData.ToString(); if (string.IsNullOrEmpty(varName)) { m_LastColumIdx = cell - 1; break; } } // 填充数据 m_Names.Clear(); m_Types.Clear(); HashSet <string> nameSet = new HashSet <string>(); string dataStr = ""; for (int cell = m_FirstColumIdx; cell <= m_LastColumIdx; ++cell) { cellData = m_NameRow.GetCell(cell); string varName = cellData.ToString(); if (string.IsNullOrEmpty(varName)) { break; } if (nameSet.Contains(varName)) { string infoStr = "表格:【" + m_TabInfo.m_RealName + "】第" + cell + "列数据名称重复和前面的重复:【" + varName + "】. 请检查!!!\n注意,行列索引从0开始!!!"; Debug.LogError(infoStr); EditorUtility.DisplayDialog("提示", infoStr, "确定"); return(false); } else { if (cell == m_SkipColumn) { varName += "_Skipped"; } varName = HandleName(varName); nameSet.Add(varName); m_Names.Add(varName); } // 处理类型 cellData = m_TypeRow.GetCell(cell); dataStr = cellData.ToString(); // 去掉可能的空格 dataStr = dataStr.Replace(" ", ""); // 全部转为大写 dataStr = dataStr.ToUpper(); DataType type = GetValidType(dataStr); if (type == DataType.NONE) { string infoStr = "表格:【" + m_TabInfo.m_RealName + "】第" + cell + "列数据类型不对:【" + cellData.ToString() + "】. 请检查!!!\n注意,行列索引从0开始!!!"; Debug.LogError(infoStr); EditorUtility.DisplayDialog("提示", infoStr, "确定"); return(false); } m_Types.Add(type); } // 导出数据 if (!ExportTXT()) { return(false); } // 导出脚本 if (!ExportScript()) { return(false); } Debug.Log(">>转换表格:【" + info.m_RealName + "】完成!!!"); return(true); }