public static DataTable getDataExcelFromFileToDataTable(string filePath) { using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read)) { // Auto-detect format, supports: // - Binary Excel files (2.0-2003 format; *.xls) // - OpenXml Excel files (2007 format; *.xlsx) using (var reader = ExcelReaderFactory.CreateReader(stream)) { var result = reader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (data) => new ExcelDataTableConfiguration() { UseHeaderRow = true } }); //Get all Table DataTableCollection tables = result.Tables; //Get Table DataTable dt = tables[0]; return(dt); } } }
public void Remove() { DataTableCollection tbcol = _dataset[0].Tables; tbcol.Clear(); /* _tables is array of DataTables defined in Setup */ tbcol.AddRange(_tables); /* removing a recently added table */ int count = tbcol.Count; tbcol.Remove(_tables[0]); Assert.Equal(count - 1, tbcol.Count); DataTable tbl = null; /* removing a null reference. must generate an Exception */ try { tbcol.Remove(tbl); Assert.False(true); } catch (Exception e) { Assert.Equal(typeof(ArgumentNullException), e.GetType()); } /* removing a table that is not there in collection */ try { tbcol.Remove(new DataTable("newTable")); Assert.False(true); } catch (Exception e) { Assert.Equal(typeof(ArgumentException), e.GetType()); } }
private static DataTable ExcelToDataTable(string fileName, string SheetName) { // Open file and return as Stream using (System.IO.FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read)) { using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream)) { excelReader.IsFirstRowAsColumnNames = true; //Return as dataset DataSet result = excelReader.AsDataSet(); //Get all the tables DataTableCollection table = result.Tables; // store it in data table DataTable resultTable = table[SheetName]; //excelReader.Dispose(); //excelReader.Close(); // return return(resultTable); } } }
public static DataTable ExcelToDataTable(String FileName, String SheetName) { using (System.IO.FileStream stream = File.Open(FileName, FileMode.Open, FileAccess.Read)) { using (IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream)) { DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration() { ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true } } ); //Get all the tables DataTableCollection table = result.Tables; //storing in a DataTable DataTable resultTable = table[SheetName]; return(resultTable); } } }
/// <summary> /// Reading all the datas from Excelsheet /// </summary> /// <param name="fileName"></param> /// <returns></returns> private static DataTable ExcelToDataTable(string fileName) { //open file and returns as Stream FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read); //Createopenxmlreader via ExcelReaderFactory IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx //Set the First Row as Column Name var conf = new ExcelDataSetConfiguration { ConfigureDataTable = _ => new ExcelDataTableConfiguration { UseHeaderRow = true } }; //Return as DataSet DataSet result = excelReader.AsDataSet(); //Get all the Tables DataTableCollection table = result.Tables; //Store it in DataTable DataTable resultTable = table["Sheet1"]; //return return(resultTable); }
private async void DialogControllerImportarPlanilha_Accepting(object sender, DialogControllerAcceptingEventArgs e) { DataTableCollection dtcollectionImport = null; ((DialogController)sender).AcceptAction.Enabled["NoEnabled"] = false; //Necessário para não fechar a janela após a conclusão do processamento e.Cancel = true; e.AcceptActionArgs.Action.Caption = "Procesando"; var parametros = (ParametrosImportSpoolJuntaExcel)e.AcceptActionArgs.SelectedObjects[0]; MemoryStream stream = new MemoryStream(); stream.Seek(0, SeekOrigin.Begin); var arquivo = parametros.Padrao; arquivo.SaveToStream(stream); stream.Seek(0, SeekOrigin.Begin); using (var excelReader = new ExcelDataReaderHelper.Excel.Reader(stream)) { dtcollectionImport = excelReader.CreateDataTableCollection(false); } var import = new ImportSpoolEJunta(objectSpace, parametrosImportSpoolJuntaExcel); var progress = new Progress <ImportProgressReport>(import.LogTrace); await Observable.Start(() => import.ImportarSpools(dtcollectionImport["SGS"], progress)); await Observable.Start(() => import.ImportarJuntas(dtcollectionImport["SGJ"], progress)); objectSpace.CommitChanges(); e.AcceptActionArgs.Action.Caption = "Finalizado"; ((DialogController)sender).AcceptAction.Enabled["NoEnabled"] = true; }
public List <List <string> > readDataTable(string path, string sheetName) { using (var stream = File.Open(path, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { var result = reader.AsDataSet(); DataTableCollection table = result.Tables; DataTable resultTable = table[sheetName]; List <List <string> > dataList = new List <List <string> >(); foreach (DataRow row in resultTable.Rows) { List <string> rowList = new List <string>(); foreach (var item in row.ItemArray) { rowList.Add(item.ToString()); } dataList.Add(rowList); } return(dataList); } } }
private void ReadContext() { // Fix encoding error System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); using (var stream = File.Open(FilePath, FileMode.Open, FileAccess.Read)) { using (var reader = ExcelReaderFactory.CreateReader(stream)) { DataSet result = reader.AsDataSet(); DataTableCollection tables = result.Tables; foreach (DataTable table in tables) { foreach (DataRow row in table.Rows) { // Skip header row if (table.Rows.IndexOf(row) == 0) { continue; } int ID = Convert.ToInt32(row.ItemArray[0]); switch (table.TableName) { case "PERSONS": Persons.Add(new Person { ID = ID, Description = Convert.ToString(row.ItemArray[1]) }); break; case "LOGS": Logs.Add(new Log { ID = ID, PersonID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2]) }); break; case "BLOCKS": Blocks.Add(new Block { ID = ID, LogID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2]) }); break; case "CYCLES": Cycles.Add(new Cycle { ID = ID, BlockID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2]) }); break; case "DAYS": Days.Add(new Day { ID = ID, CycleID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2]) }); break; case "WORKOUTS": Workouts.Add(new Workout { ID = ID, DayID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2]) }); break; case "EXERCISES": Exercises.Add(new Exercise { ID = ID, WorkoutID = Convert.ToInt32(row.ItemArray[1]), Description = Convert.ToString(row.ItemArray[2]) }); break; case "SETS": Sets.Add(new Set { ID = ID, ExerciseID = Convert.ToInt32(row.ItemArray[1]), Weight = Convert.ToDouble(row.ItemArray[2]), Intensity = Convert.ToInt32(row.ItemArray[3]), Repetitions = Convert.ToInt32(row.ItemArray[4]), }); break; } } } } } }
private void buttonSearchCalculate_Click(object sender, EventArgs e) { textBoxOutputs.Text = ""; textBoxOutputs.Text += "==== Scan Result ====" + Environment.NewLine; //Toggle settings. List <bool> columnToggles = ColumnToggles(); int totalCharacters = 0; //Get all xlsx. string[] files = Directory.GetFiles(".\\", "*.xlsx", SearchOption.AllDirectories); for (int i = 0; i < files.Length; ++i) { int totalCharactersInFile = 0; textBoxOutputs.Text += Environment.NewLine; textBoxOutputs.Text += "File[" + files[i] + "]:" + Environment.NewLine; try { //Open the file. using (FileStream file = File.Open(files[i], FileMode.Open, FileAccess.Read)) { //Create excel reader. using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(file)) { int[] characterCountForColumnFile = new int[26]; DataSet dataSet = reader.AsDataSet(); DataTableCollection dataTableCollection = dataSet.Tables; for (int j = 0; j < dataTableCollection.Count; j++) { DataTable table = dataTableCollection[j]; //A ~ Z = 26 columns. int[] characterCountForColumnTab = new int[26]; textBoxOutputs.Text += " Tab[" + j + "]: " + table.TableName + Environment.NewLine; DataRow[] rows = table.Select(); for (int x = 0; x < rows.Length; ++x) { //string cell = rows[x].Field<string>(x); //if (x < characterCountForColumnTab.Length && columnToggles[x] == true) { // characterCountForColumnTab[x] += cell.Length; //} Object[] cells = rows[x].ItemArray; for (int y = 0; y < cells.Length; ++y) { //Debug.Write(cells.Length); if (cells[y] != null) { string content = cells[y].ToString(); //Add the character count of this cell. if (y < characterCountForColumnTab.Length && columnToggles[y] == true) { characterCountForColumnTab[y] += content.Length; } } } } //Print the column with characters. int totalCharactersInTab = 0; for (int x = 0; x < characterCountForColumnTab.Length; ++x) { if (characterCountForColumnTab[x] > 0) { textBoxOutputs.Text += " Column[" + COLUMN_LETTER[x] + "] characters: " + characterCountForColumnTab[x] + Environment.NewLine; totalCharactersInTab += characterCountForColumnTab[x]; } } textBoxOutputs.Text += " Total characters in tab: " + totalCharactersInTab + Environment.NewLine; totalCharactersInFile += totalCharactersInTab; } } } } catch (Exception exception) { textBoxOutputs.Text += "Unable to open file[" + files[i] + "]. Ignore it(" + exception.Message + ")"; } textBoxOutputs.Text += "Total characters in file: " + totalCharactersInFile + Environment.NewLine; totalCharacters += totalCharactersInFile; } textBoxOutputs.Text += Environment.NewLine; textBoxOutputs.Text += "Total characters: " + totalCharacters + Environment.NewLine; textBoxOutputs.Text += Environment.NewLine; textBoxOutputs.Text += "== " + files.Length + " files scanned ==" + Environment.NewLine; }
public override object RecuperarItem(object item, int valor, object valorDefault) { DataTableCollection linhaItem = (DataTableCollection)item; return(base.ItemRetorno(linhaItem[valor], valorDefault)); }
public static void Read(string filename) { if (!CuelistCtrl.saved) { bool?result = DialogCtrl.Show(DialogType.QUESTION, OptionType.YESNO, "Open Cuelist", "Do you want to save the current cuelist?"); if (result == true) { if (!Write(false)) { return; } } } if (filename == null) { Microsoft.Win32.OpenFileDialog dlg = new Microsoft.Win32.OpenFileDialog(); dlg.Filter = "CC Files (*.cc)|*.cc"; bool?result = dlg.ShowDialog(); if (result == true) { filename = dlg.FileName; } else { return; } } using (DataSet dataSet = new DataSet("Cuelist")) { try { dataSet.ReadXml(filename); } catch (Exception e) { LogCtrl.Error(e.Message); DialogCtrl.Show(DialogType.ERROR, OptionType.OKCANCEL, "Error", "Can't open file!"); return; } DataTableCollection collection = dataSet.Tables; ClearCuelist(); for (int i = 0; i < collection.Count; i++) { DataTable table = collection[i]; switch (table.TableName) { case "Settings": SetSettingsFromTable(table); break; case "Script": ScriptlistCtrl.LoadScripts(table); break; case "Beamer": BeamerlistCtrl.LoadBeamers(table); break; case "MidiMap": MidiController.LoadMidiMap(table); break; default: CuelistCtrl.SetCuesFromTable(table); break; } } } CuelistCtrl.saved = true; SetPath(filename); CuelistCtrl.DisableEditMode(); RecentFilesCtrl.Add(filename); LogCtrl.Status("Open file: " + filename); }
/// <summary>
/// <summary> /// Search data source meta-data for a target. /// </summary> /// <param name="request">Search target.</param> /// <param name="cancellationToken">Cancellation token.</param> public virtual Task <string[]> Search(Target request, CancellationToken cancellationToken) { string target = request.target == "select metric" ? "" : request.target; // Attempt to parse an expression that has SQL SELECT syntax bool parseSelectExpression(string selectExpression, out string tableName, out string[] fieldNames, out string expression, out string sortField, out int topCount) { tableName = null; fieldNames = null; expression = null; sortField = null; topCount = 0; if (string.IsNullOrWhiteSpace(selectExpression)) { return(false); } // RegEx instance used to parse meta-data for target search queries using a reduced SQL SELECT statement syntax if (s_selectExpression is null) { s_selectExpression = new Regex(@"(SELECT\s+(TOP\s+(?<MaxRows>\d+)\s+)?(\s*(?<FieldName>\w+)(\s*,\s*(?<FieldName>\w+))*)?\s*FROM\s+(?<TableName>\w+)\s+WHERE\s+(?<Expression>.+)\s+ORDER\s+BY\s+(?<SortField>\w+))|(SELECT\s+(TOP\s+(?<MaxRows>\d+)\s+)?(\s*(?<FieldName>\w+)(\s*,\s*(?<FieldName>\w+))*)?\s*FROM\s+(?<TableName>\w+)\s+WHERE\s+(?<Expression>.+))|(SELECT\s+(TOP\s+(?<MaxRows>\d+)\s+)?(\s*(?<FieldName>\w+)(\s*,\s*(?<FieldName>\w+))*)?\s*FROM\s+(?<TableName>\w+))", RegexOptions.Compiled | RegexOptions.IgnoreCase); } Match match = s_selectExpression.Match(selectExpression.ReplaceControlCharacters()); if (!match.Success) { return(false); } tableName = match.Result("${TableName}").Trim(); fieldNames = match.Groups["FieldName"].Captures.Cast <Capture>().Select(capture => capture.Value).ToArray(); expression = match.Result("${Expression}").Trim(); sortField = match.Result("${SortField}").Trim(); string maxRows = match.Result("${MaxRows}").Trim(); if (string.IsNullOrEmpty(maxRows) || !int.TryParse(maxRows, out topCount)) { topCount = int.MaxValue; } return(true); } return(Task.Factory.StartNew(() => { return TargetCache <string[]> .GetOrAdd($"search!{target}", () => { if (!(request.target is null)) { // Attempt to parse search target as a SQL SELECT statement that will operate as a filter for in memory metadata (not a database query) if (parseSelectExpression(request.target.Trim(), out string tableName, out string[] fieldNames, out string expression, out string sortField, out int takeCount)) { DataTableCollection tables = Metadata.Tables; List <string> results = new List <string>(); if (tables.Contains(tableName)) { DataTable table = tables[tableName]; List <string> validFieldNames = new List <string>(); for (int i = 0; i < fieldNames?.Length; i++) { string fieldName = fieldNames[i].Trim(); if (table.Columns.Contains(fieldName)) { validFieldNames.Add(fieldName); } } fieldNames = validFieldNames.ToArray(); if (fieldNames.Length == 0) { fieldNames = table.Columns.Cast <DataColumn>().Select(column => column.ColumnName).ToArray(); } // If no filter expression or take count was specified, limit search target results - user can // still request larger results sets by specifying desired TOP count. if (takeCount == int.MaxValue && string.IsNullOrWhiteSpace(expression)) { takeCount = MaximumSearchTargetsPerRequest; } void executeSelect(IEnumerable <DataRow> queryOperation) { results.AddRange(queryOperation.Take(takeCount).Select(row => string.Join(",", fieldNames.Select(fieldName => row[fieldName].ToString())))); } if (string.IsNullOrWhiteSpace(expression)) { if (string.IsNullOrWhiteSpace(sortField)) { executeSelect(table.Select()); } else { if (Common.IsNumericType(table.Columns[sortField].DataType)) { decimal parseAsNumeric(DataRow row) { decimal.TryParse(row[sortField].ToString(), out decimal result); return result; } executeSelect(table.Select().OrderBy(parseAsNumeric)); } else { executeSelect(table.Select().OrderBy(row => row[sortField].ToString())); } } } else { executeSelect(table.Select(expression, sortField)); } foreach (DataRow row in table.Select(expression, sortField).Take(takeCount)) { results.Add(string.Join(",", fieldNames.Select(fieldName => row[fieldName].ToString()))); } } return results.ToArray(); } } // Non "SELECT" style expressions default to searches on ActiveMeasurements meta-data table return Metadata.Tables["ActiveMeasurements"].Select($"ID LIKE '{InstanceName}:%' AND PointTag LIKE '%{target}%'").Take(MaximumSearchTargetsPerRequest).Select(row => $"{row["PointTag"]}").ToArray(); }); }, cancellationToken));
private static void Execute(Options options) { /* 判断 ExcelPath 为目录路径还是文件路径 */ string[] excelPaths = null; if (Directory.Exists(options.ExcelPath)) { DirectoryInfo dirInfo = new DirectoryInfo(options.ExcelPath); excelPaths = dirInfo.GetFiles().Where(fileInfo => ExcelUtil.IsSupported(fileInfo.Name)).Select(fileInfo => fileInfo.FullName).ToArray(); } else if (File.Exists(options.ExcelPath)) { if (ExcelUtil.IsSupported(options.ExcelPath)) { excelPaths = new[] { options.ExcelPath }; } } if (excelPaths == null) { Console.WriteLine("No supported excel file found."); return; } /* 对目标 Excel 文件进行转译 */ foreach (var excelPath in excelPaths) { string excelName = Path.GetFileName(excelPath); Console.WriteLine("[{0}]", excelName); DataTableCollection dataTables = ExcelReader.ReadExcelToDataTables(excelPath); foreach (DataTable dataTable in dataTables) { /* 开始转换 DataTable */ string sheetName = dataTable.TableName; int rowsCount = dataTable.Rows.Count; int columnsCount = dataTable.Columns.Count; Console.WriteLine(" sheet {0}(rows: {1}, column: {2})...", sheetName, rowsCount.ToString(), columnsCount.ToString()); ESheet sheetType = ExcelUtil.GetSheetType(sheetName); string fileName = sheetType switch { ESheet.EnumSheet => options.EnumNamePrefix + sheetName.Substring(4), ESheet.ParamSheet => options.ParamNamePrefix + sheetName.Substring(5), ESheet.ClassSheet => options.ClassNamePrefix + sheetName, _ => null }; /* 生成 JSON 数据 */ Console.WriteLine(" generate json..."); string jsonContent = DataWriter.DataTableToJSON(dataTable, options); if (!string.IsNullOrEmpty(jsonContent)) { if (!Directory.Exists(options.JSONPath)) { Directory.CreateDirectory(options.JSONPath); } string jsonPath = string.Format("{0}/{1}.json", options.JSONPath, fileName); File.WriteAllText(jsonPath, jsonContent, Encoding.UTF8); } /* 生成 C# 代码 */ Console.WriteLine(" generate csharp code..."); string codeContent = CodeWriter.DataTableToCSharp(dataTable, excelName, options); if (!string.IsNullOrEmpty(codeContent)) { if (!Directory.Exists(options.CSharpCodePath)) { Directory.CreateDirectory(options.CSharpCodePath); } string codePath = string.Format("{0}/{1}.cs", options.CSharpCodePath, fileName); File.WriteAllText(codePath, codeContent, Encoding.UTF8); } } } } }
/// <summary> /// Recupera la prima tabella di una DataTableCollection se esiste, null altrimenti /// </summary> /// <param name="tables">collection di DataTable</param> /// <returns>La prima DataTable disponibile della collection</returns> public static DataTable FirstOrDefault(this DataTableCollection tables) { return(tables == null || tables.Count == 0 ? null : tables[0]); }
/// <summary> /// 投资信息显示 /// </summary> /// <returns></returns> public DataTable Tendershowmain() { DataTableCollection dc = SqlHelper.GetTableProducts("pro_qyyTenderShowMain", null); return(dc[0]); }
/// <summary> /// 债权转让信息显示 /// </summary> /// <returns></returns> public DataTable Zhuaiquanshowmain() { DataTableCollection dc = SqlHelper.GetTableProducts("proc_qyyZhaiquanShowmain", null); return(dc[0]); }
/// <summary> /// 将table转换为json /// </summary> /// <param name="sheetDataTable"></param> /// <returns></returns> public string ConvertDataTableToJson(DataTableCollection excelCollection) { StringBuilder sbs = new StringBuilder(); sbs.Append("{\n"); // 遍历分页 for (int sheetIndex = 0; sheetIndex < excelCollection.Count; sheetIndex++) { DataTable sheetDataTable = excelCollection[sheetIndex]; if (sheetDataTable.Rows.Count > 0) { sbs.Append("\t\"" + sheetDataTable.TableName + "\":[\n\t\t"); string str = ""; int startRow; int startCol; // 查找表头,确定开始行数和开始列数 GetStartRowAndCol(sheetDataTable, out startRow, out startCol); DataRow startRowData = sheetDataTable.Rows[startRow]; // 开始的第一行,用作key值 DataColumn startColData = sheetDataTable.Columns[startCol]; // 开始的第一列 // 遍历行 for (int row = startRow + 1; row < sheetDataTable.Rows.Count; row++) { DataRow dr = sheetDataTable.Rows[row]; if (dr[startColData.ColumnName].ToString() == string.Empty) { break; } // 遍历列 string result = ""; for (int col = startCol; col < sheetDataTable.Columns.Count; col++) { DataColumn dc = sheetDataTable.Columns[col]; if (dr[dc.ColumnName].ToString() == string.Empty || startRowData[dc.ColumnName].ToString() == string.Empty) { break; } result += string.Format(",\n\t\t\t\"{0}\":\"{1}\"", startRowData[dc.ColumnName], dr[dc.ColumnName]); } result = result.Substring(1); result = ",\n\t\t{" + result + "\n\t\t}"; str += result; } if (str == "") { Debug.LogError(sheetDataTable.TableName + "没找到表头或表为空(表头名要和分页名相同)"); } str = str.Substring(4); sbs.Append(str); if (sheetIndex == excelCollection.Count - 1) { sbs.Append("\n\t]"); } else { sbs.Append("\n\t],\n\t"); } } } sbs.Append("\n}"); return(sbs.ToString()); }
public static void go() { // Set Access connection and select strings. // The path to BugTypes.MDB must be changed if you build the sample // from the command line: #if USINGPROJECTSYSTEM string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\BugTypes.MDB"; #else string strAccessConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Aaron\\Documents" + "\\Databases\\PurdueGPA.accdb"; #endif string strAccessSelect = "SELECT * FROM tblHW"; // Create the dataset and add the Categories table to it: DataSet myDataSet = new DataSet(); OleDbConnection myAccessConn = null; try { myAccessConn = new OleDbConnection(strAccessConn); } catch (Exception ex) { MessageBox.Show("Error: Failed to create a database connection. \n{0}", ex.Message); return; } try { OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect, myAccessConn); OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand); myAccessConn.Open(); myDataAdapter.Fill(myDataSet, "tblHW"); } catch (Exception ex) { MessageBox.Show("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message); return; } finally { myAccessConn.Close(); } // A dataset can contain multiple tables, so let's get them all // into an array: DataTableCollection dta = myDataSet.Tables; foreach (DataTable dt in dta) { MessageBox.Show("Found data table {0}", dt.TableName); } // The next two lines show two different ways you can get the // count of tables in a dataset: MessageBox.Show(myDataSet.Tables.Count + " tables in data set"); MessageBox.Show(dta.Count + " tables in data set"); // The next several lines show how to get information on a // specific table by name from the dataset: MessageBox.Show(myDataSet.Tables["tblHW"].Rows.Count + " rows in tblHW table"); // The column info is automatically fetched from the database, so // we can read it here: MessageBox.Show(myDataSet.Tables["tblHW"].Columns.Count + " columns in tblHW table"); DataColumnCollection drc = myDataSet.Tables["tblHW"].Columns; int i = 0; foreach (DataColumn dc in drc) { // Print the column subscript, then the column's name and its // data type: MessageBox.Show("Column name " + dc.ColumnName + " is " + i + " of type " + dc.DataType); } DataRowCollection dra = myDataSet.Tables["tblHW"].Rows; foreach (DataRow dr in dra) { // Print the CategoryID as a subscript, then the CategoryName: MessageBox.Show("CategoryName " + dr[0] + " is " + dr[1]); } }
/// <summary> /// 只支持.xlsx格式,不支持03版本excel(.xls) /// </summary> /// <param name="filePath"></param> /// <returns></returns> public static List <ExcelTable> Read(string filePath) { Debug.Log("read file:" + filePath); List <ExcelTable> excelTables = new List <ExcelTable>(); string fileName = Path.GetFileName(filePath); FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read); IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fs); DataSet result = excelReader.AsDataSet(); //获得一个xlsx文件中所有的tables DataTableCollection tables = result.Tables; fs.Close(); foreach (DataTable table in tables) { ExcelTable et = new ExcelTable(); et.tableName = table.TableName; ////中文表不读,用于整体性的说明、注释作用 ////要导出的表需要以t_开头 if (et.tableName.ContainChinese() || !et.tableName.StartsWith("t_")) { continue; } Debug.Log("begin read------>fileName:" + fileName + ",tableName:" + et.tableName); et.rowCount = table.Rows.Count; if (et.rowCount < 6) { Debug.LogWarnning("fileName:" + fileName + ", tableName:" + et.tableName + ", line count less than 6, we will not handle with it"); continue; } for (int i = 0; i < table.Columns.Count; i++) { //init ExcelField string name = table.Rows[0][i].ToString(); if (name.ContainChinese())//中文字段用于注释,不处理 { continue; } string typeDes = table.Rows[1][i].ToString(); string tag = table.Rows[3][i].ToString(); if (i == 0 && tag == "KVT")//check KVT format whether correct { et.isKVT = true; if (table.Rows[0][0].ToString() != "Key" || table.Rows[0][1].ToString() != "Value" || table.Rows[0][2].ToString() != "KeyDes") { Debug.ThrowException("table:" + et.tableName + "is KVT,but field name is not correct!"); } if (table.Rows[1][0].ToString() != "string") { Debug.ThrowException("table:" + et.tableName + "is KVT,but key's type is not string"); } } string defaultValue = table.Rows[2][i].ToString(); string fieldDes = table.Rows[4][i].ToString(); string fieldDesMore = table.Rows[5][i].ToString(); ExcelField field = new ExcelField(et.tableName, name, typeDes, defaultValue, tag, fieldDes, fieldDesMore, i == 0 ? true : false); //read Field datas for (int j = 6; j < et.rowCount; j++) { string tmpValue = table.Rows[j][i].ToString().Trim(); field.AddData(tmpValue == "" ? defaultValue : tmpValue); } et.AddExcelField(field); } #region old //////for (int i = 0; i < et.rowCount; i++) //////{ ////// if (i == 0)//获得key ////// { ////// for (int j = 0; j < et.columnCount; j++) ////// { ////// var key = table.Rows[0][j].ToString(); ////// if (j == 0)//主键 ////// { ////// et.primaryKeyName = key; ////// et.primaryKeyType = table.Rows[1][j].ToString(); ////// if (table.Rows[2][j].ToString().Contains("KVT")) ////// { ////// et.isKVPairTable = true; ////// et.valueKeyType = table.Rows[1][1].ToString(); ////// if (table.Rows[0][1].ToString() != "Value" || table.Rows[0][2].ToString() != "KeyDes") ////// { ////// throw new Exception(et.tableName + "表有问题,KVT表,要求第二个字段名为 Value,第三个字段名为 KeyDes"); ////// } ////// } ////// else ////// { ////// et.isKVPairTable = false; ////// } ////// } ////// Console.WriteLine("@@初始化map, key:" + key); ////// et.tableContent[key] = new List<string>(); ////// if (et.keys.Contains(key)) ////// { ////// throw new Exception("fatal error, table:" + et.tableName + ",有重复的字段:" + key); ////// } ////// else ////// { ////// if (!key.ContainChinese() && key != "KeyDes")//字段非中文才加入,且 KeyDes字段不用加入 ////// { ////// et.keys.Add(key); ////// } ////// } ////// } ////// Console.WriteLine("@@初始化map key完毕"); ////// } ////// else ////// { ////// for (int k = 0; k < et.columnCount; k++) ////// { ////// string key = table.Rows[0][k].ToString(); ////// string property = table.Rows[i][k].ToString(); ////// Console.WriteLine("table:" + et.tableName + " , key: " + key + " , property:" + property); ////// //主键的数据要保持唯一性 ////// if (k == 0 && i > 4 && et.tableContent[key].Contains(property)) ////// { ////// throw new Exception("fatal error, table:" + et.tableName + "的主键" + key + ",property:" + property); ////// } ////// else ////// { ////// if (property == "") ////// { ////// property = "囧";//对于数据表中,用户没有填写的空格,默认赋值为 囧 ,读取的时候需要特殊处理 ////// } ////// et.tableContent[key].Add(property); ////// } ////// } ////// } //////} #endregion excelTables.Add(et); } return(excelTables); }
internal FeatureTableCollection(DataTableCollection dataTables) { _dataTables = dataTables; }
public static DataTable ReadDataTable(DataTableCollection dataTableCollection, string tableName) { return((from DataTable dtbl in dataTableCollection where dtbl.TableName == tableName select dtbl).FirstOrDefault()); }
public PurchaserDetailsBuilder(DataTableCollection tables) { this.tables = tables; }
/// <summary> /// Populates the collections internal. /// </summary> /// <param name="tables">The tables.</param> /// <param name="filter">The filter.</param> internal void PopulateCollectionsInternal(DataTableCollection tables, string filter) { PopulateCollections(tables, filter); }
public DataTableCollectionLexer(JsonSerializer serializer, DataTableCollection dataTables) { _serializer = serializer; _dataTables = dataTables; }
public static DataTable[] ToArray(this DataTableCollection tables) { return(tables.AsEnumerable().ToArray()); }
private void FilterSort_Load(object sender, EventArgs e) { // Connection string string connString = @"server=.\sql2012; database=AdventureWorks; Integrated Security=true"; // Query1 string sql1 = @" select * from Production.Product where Name Like 'Mountain%'"; // Query2 string sql2 = @" select * from Production.Location where CostRate > 10.0 "; // Combine queries string sql = sql1 + sql2; // Create connection SqlConnection conn = new SqlConnection(connString); try { // Create Data Adapter SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = new SqlCommand(sql, conn); // Create and Fill Data Set DataSet ds = new DataSet(); da.Fill(ds, "Production.Product"); // Get the data tables collection DataTableCollection dtc = ds.Tables; // Display data from first data table // // Display output header txtSort.AppendText("Results from Product table:\n"); txtSort.AppendText("***************************************************\n"); txtSort.AppendText("Name\t\t\t\tProductNumber\n"); txtSort.AppendText("_________________________________________\n"); // set display filter string fl = "Color = 'Black'"; // Set sort string srt = "ProductNumber asc"; // display filtered and sorted data foreach (DataRow row in dtc["Production.Product"].Select(fl, srt)) { txtSort.AppendText(row["Name"].ToString().PadRight(25)); txtSort.AppendText("\t\t"); txtSort.AppendText(row["ProductNumber"].ToString()); txtSort.AppendText(Environment.NewLine); } txtSort.AppendText("============================================\n"); // Display data from second data table // Display output header txtSort.AppendText("Results from Location table:\n"); txtSort.AppendText("***********************************************\n"); txtSort.AppendText("Name\t\t\tCostRate\n"); txtSort.AppendText("__________________________________________\n"); // Display data foreach (DataRow row in dtc[1].Rows) { txtSort.AppendText(row["Name"].ToString().PadRight(25)); txtSort.AppendText("\t"); txtSort.AppendText(row["CostRate"].ToString()); txtSort.AppendText(Environment.NewLine); } } catch (Exception ex) { MessageBox.Show(ex.Message + ex.StackTrace); } finally { // Connection close conn.Close(); } }
private void btnExcel_Click(object sender, EventArgs e) { try { pbar.Visible = true; Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); // Loop over DataTables in DataSet. DataSet ds = new DataSet(); DataTable dtGST = new DataTable(); foreach (DataGridViewColumn col in dgvGST.Columns) { dtGST.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvGST.Rows) { DataRow dRow = dtGST.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } dtGST.Rows.Add(dRow); } DataTable dtCate = new DataTable(); foreach (DataGridViewColumn col in dgvCategory.Columns) { dtCate.Columns.Add(col.HeaderText); } foreach (DataGridViewRow row in dgvCategory.Rows) { DataRow dRow = dtCate.NewRow(); foreach (DataGridViewCell cell in row.Cells) { dRow[cell.ColumnIndex] = cell.Value; } dtCate.Rows.Add(dRow); } ds.Tables.Add(dtGST); ds.Tables.Add(dtCate); DataTableCollection collection = ds.Tables; for (int i = collection.Count; i > 0; i--) { Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataTable table = collection[i - 1]; xlWorksheet.Name = table.TableName; for (int j = 1; j < table.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < table.Rows.Count; k++) { for (int l = 0; l < table.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = table.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); } ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; pbar.Visible = false; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btneExcelReport_Click(object sender, EventArgs e) { try { Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlWorkbook = ExcelApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); System.Data.DataSet ds = new DataSet(); ds = gstR1Controller.getGSTR1Report(Convert.ToDateTime(dtpFrom.Value.ToShortDateString()), Convert.ToDateTime(dtpTo.Value.ToShortDateString()), Utility.FinancilaYearId, "GSTR1"); DataTableCollection collection = ds.Tables; ds.Tables[0].TableName = "b2b"; ds.Tables[0].Columns["GSTIN_UINofRecipient"].ColumnName = "GSTIN/UIN of Recipient"; ds.Tables[0].Columns["InvoiceNumber"].ColumnName = "Invoice Number"; ds.Tables[0].Columns["Invoicedate"].ColumnName = "Invoice date"; ds.Tables[0].Columns["InvoiceValue"].ColumnName = "Invoice Value"; ds.Tables[0].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[0].Columns["ReverseCharge"].ColumnName = "Reverse Charge"; ds.Tables[0].Columns["InvoiceType"].ColumnName = "Invoice Type"; ds.Tables[0].Columns["E_CommerceGSTIN"].ColumnName = "E-Commerce GSTIN"; ds.Tables[0].Columns["Rate"].ColumnName = "Rate"; ds.Tables[0].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[0].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[1].TableName = "b2cl"; ds.Tables[1].Columns["InvoiceNumber"].ColumnName = "Invoice Number"; ds.Tables[1].Columns["Invoicedate"].ColumnName = "Invoice date"; ds.Tables[1].Columns["InvoiceValue"].ColumnName = "Invoice Value"; ds.Tables[1].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[1].Columns["E_CommerceGSTIN"].ColumnName = "E-Commerce GSTIN"; ds.Tables[1].Columns["Rate"].ColumnName = "Rate"; ds.Tables[1].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[1].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[2].TableName = "b2cs"; ds.Tables[2].Columns["Type"].ColumnName = "Type"; ds.Tables[2].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[2].Columns["Rate"].ColumnName = "Rate"; ds.Tables[2].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[2].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[2].Columns["E_CommerceGSTIN"].ColumnName = "E-Commerce GSTIN"; ds.Tables[3].TableName = "cndr"; ds.Tables[3].Columns["GSTIN_UINofRecipient"].ColumnName = "GSTIN/UIN of Recipient"; ds.Tables[3].Columns["Invoice_AdvanceReceiptNumber"].ColumnName = "Invoice/Advance Receipt Number"; ds.Tables[3].Columns["Invoice_AdvanceReceiptdate"].ColumnName = "Invoice/Advance Receipt date"; ds.Tables[3].Columns["Note_RefundVoucherNumber"].ColumnName = "Note/Refund Voucher Number"; ds.Tables[3].Columns["Note_RefundVoucherdate"].ColumnName = "Note/Refund Voucher date"; ds.Tables[3].Columns["DocumentType"].ColumnName = "Document Type"; ds.Tables[3].Columns["ReasonForIssuingdocument"].ColumnName = "Reason For Issuing document"; ds.Tables[3].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[3].Columns["Note_RefundVoucherValue"].ColumnName = "Note/Refund Voucher Value"; ds.Tables[3].Columns["Rate"].ColumnName = "Rate"; ds.Tables[3].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[3].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[3].Columns["PreGST"].ColumnName = "Pre GST"; ds.Tables[4].TableName = "cndur"; ds.Tables[4].Columns["URType"].ColumnName = "UR Type"; ds.Tables[4].Columns["Note_RefundVoucherNumber"].ColumnName = "Note/Refund Voucher Number"; ds.Tables[4].Columns["Note_RefundVoucherdate"].ColumnName = "Note/Refund Voucher date"; ds.Tables[4].Columns["DocumentType"].ColumnName = "Document Type"; ds.Tables[4].Columns["Invoice_AdvanceReceiptNumber"].ColumnName = "Invoice/Advance Receipt Number"; ds.Tables[4].Columns["Invoice_AdvanceReceiptdate"].ColumnName = "Invoice/Advance Receipt date"; ds.Tables[4].Columns["ReasonForIssuingdocument"].ColumnName = "Reason For Issuing document"; ds.Tables[4].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[4].Columns["Note_RefundVoucherValue"].ColumnName = "Note/Refund Voucher Value"; ds.Tables[4].Columns["Rate"].ColumnName = "Rate"; ds.Tables[4].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[4].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[4].Columns["PreGST"].ColumnName = "Pre GST"; ds.Tables[5].TableName = "exp"; ds.Tables[5].Columns["ExportType"].ColumnName = "Export Type"; ds.Tables[5].Columns["InvoiceNumber"].ColumnName = "Invoice Number"; ds.Tables[5].Columns["Invoicedate"].ColumnName = "Invoice date"; ds.Tables[5].Columns["InvoiceValue"].ColumnName = "Invoice Value"; ds.Tables[5].Columns["PortCode"].ColumnName = "Port Code"; ds.Tables[5].Columns["ShippingBillNumber"].ColumnName = "Shipping Bill Number"; ds.Tables[5].Columns["ShippingBillDate"].ColumnName = "Shipping Bill Date"; ds.Tables[5].Columns["Rate"].ColumnName = "Rate"; ds.Tables[5].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[6].TableName = "at"; ds.Tables[6].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[6].Columns["Rate"].ColumnName = "Rate"; ds.Tables[6].Columns["GrossAdvanceReceived"].ColumnName = "Gross Advance Received"; ds.Tables[6].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[7].TableName = "atadj"; ds.Tables[7].Columns["PlaceOfSupply"].ColumnName = "Place Of Supply"; ds.Tables[7].Columns["Rate"].ColumnName = "Rate"; ds.Tables[7].Columns["GrossAdvanceAdjusted"].ColumnName = "Gross Advance Adjusted"; ds.Tables[7].Columns["CessAmount"].ColumnName = "Cess Amount"; ds.Tables[8].TableName = "exemp"; ds.Tables[8].Columns["Description"].ColumnName = "Description"; ds.Tables[8].Columns["NilRatedSupplies"].ColumnName = "Nil Rated Supplies"; ds.Tables[8].Columns["Exempted"].ColumnName = "Exempted (other than nil rated/non GST supply )"; ds.Tables[8].Columns["NonGSTsupplies"].ColumnName = "Non-GST supplies"; ds.Tables[9].TableName = "hsn"; ds.Tables[9].Columns["HSNCode"].ColumnName = "HSN"; ds.Tables[9].Columns["Description"].ColumnName = "Description"; ds.Tables[9].Columns["UQC"].ColumnName = "UQC"; ds.Tables[9].Columns["TotalQuantity"].ColumnName = "Total Quantity"; ds.Tables[9].Columns["TaxableValue"].ColumnName = "Taxable Value"; ds.Tables[9].Columns["IntegratedTaxAmount"].ColumnName = "Integrated Tax Amount"; ds.Tables[9].Columns["CentralTaxAmount"].ColumnName = "Central Tax Amount"; ds.Tables[9].Columns["State_UTTaxAmount"].ColumnName = "State/UT Tax Amount"; ds.Tables[9].Columns["CessAmount"].ColumnName = "Cess Amount"; for (int i = collection.Count; i > 0; i--) { Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null; //Create Excel Sheets xlSheets = ExcelApp.Sheets; xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing); System.Data.DataTable table = collection[i - 1]; xlWorksheet.Name = table.TableName; for (int j = 1; j < table.Columns.Count + 1; j++) { ExcelApp.Cells[1, j] = table.Columns[j - 1].ColumnName; } // Storing Each row and column value to excel sheet for (int k = 0; k < table.Rows.Count; k++) { for (int l = 0; l < table.Columns.Count; l++) { ExcelApp.Cells[k + 2, l + 1] = table.Rows[k].ItemArray[l].ToString(); } } ExcelApp.Columns.AutoFit(); } ((Microsoft.Office.Interop.Excel.Worksheet)ExcelApp.ActiveWorkbook.Sheets[ExcelApp.ActiveWorkbook.Sheets.Count]).Delete(); ExcelApp.Visible = true; } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void popoloWs(DataSet excO) { cbWorkSheet.Items.Clear(); dtbc = excO.Tables; cbWorkSheet.Items.AddRange(dtbc.Cast <DataTable>().Select(t => t.TableName).ToArray <string>()); }