C#将行添加到foreach循环内的DataTable
问题描述:
我试图从文本文件中添加变量到数据表中以将其转换为CSV文件,但我不断收到此错误:“名为”Machine Number“的列已经存在属于这个数据表”林不知道该怎么做,任何帮助,将不胜感激,谢谢:)C#将行添加到foreach循环内的DataTable
代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Windows.Forms;
using System.IO;
using System.Globalization;
using System.Text;
namespace Ispy
{
public partial class Form1 : Form
{
private DataGridView dexRead;
DataColumn column;
DataTable Data = new DataTable("ISpy");
DataSet dataSet = new DataSet();
DataRow row;
public Form1()
{
InitializeComponent();
ReadFiles();
}
private void ReadFiles()
{
DataTable dataTable;
//Location of Dex Files
DirectoryInfo DexFiles = new DirectoryInfo(Properties.Settings.Default.Dex_File_Path);
//List of file names in Dex File folder
List<string> DexNames = new List<string>();
//Location of Input File
string ExcelFile = Properties.Settings.Default.Excel_File_Path;
//Read Input File
string[] ExcelLines = File.ReadAllLines(ExcelFile);
//Add names of each file to a list
foreach (FileInfo DexFile in DexFiles.GetFiles("*.dex"))
{
DexNames.Add(DexFile.Name);
}
//Excel Input and Dex File Data Marriage
foreach (string Line in ExcelLines)
{
row = Data.NewRow();
dataTable = new DataTable();
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "Machine Number";
column.ReadOnly = false;
column.Unique = true;
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Customer";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "MEI Total Vend Count";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "DEX Total Vend Count";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "Stock Sold";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "Capacity";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.DateTime");
column.ColumnName = "Next Scheduled Visit Date";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Scheduled Visit In:";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Scheduled Visit Day";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Next Visit Stock Prediction";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Route Number";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Route Driver Name";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Current Stock %";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.DateTime");
column.ColumnName = "Date/Time of DEX";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Telemetry Provider";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Days since last refill";
column.AutoIncrement = false;
Data.Columns.Add(column);
//column = new DataColumn();
//column.DataType = System.Type.GetType("System.String");
//column.ColumnName = "Machine @40% Stock in";
//column.AutoIncrement = false;
//Data.Columns.Add(column);
//column = new DataColumn();
//column.DataType = System.Type.GetType("System.String");
//column.ColumnName = "Machine @30% Stock in";
//column.AutoIncrement = false;
//Data.Columns.Add(column);
//column = new DataColumn();
//column.DataType = System.Type.GetType("System.String");
//column.ColumnName = "Optimal Fill Date";
//column.AutoIncrement = false;
//Data.Columns.Add(column);
//column = new DataColumn();
//column.DataType = System.Type.GetType("System.String");
//column.ColumnName = "Optimal Fill Date In:";
//column.AutoIncrement = false;
//Data.Columns.Add(column);
//column = new DataColumn();
//column.DataType = System.Type.GetType("System.String");
//column.ColumnName = "Optimal Fill Day";
//column.AutoIncrement = false;
//Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Sector";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Products";
column.AutoIncrement = false;
Data.Columns.Add(column);
column = new DataColumn();
column.DataType = System.Type.GetType("System.String");
column.ColumnName = "Machine Type";
column.AutoIncrement = false;
Data.Columns.Add(column);
string[] LineInfo = Line.Split(',');
//Input File Variables (.Trim('"') is to remove artifacts leftover from Input File)
string MachineNumber = LineInfo[0].Trim('"');
string MachineLocation = LineInfo[1].Trim('"');
string TelemetryProvider = LineInfo[7].Trim('"');
string Capacity = LineInfo[9].Trim('"');
string MEIVendCount = LineInfo[10].Trim('"');
string MEICashCount = LineInfo[11].Trim('"');
string LastVisitDate = LineInfo[12].Trim('"');
string MachinePHYSID = LineInfo[13].Trim('"');
string NextScheduledVisit = LineInfo[14].Trim('"');
string RouteName = LineInfo[16].Trim('"');
string DriverName = LineInfo[17].Trim('"');
string MachineModel = LineInfo[18].Trim('"');
string MachineType = LineInfo[19].Trim('"');
string MachineSector = LineInfo[20].Trim('"');
string DEXVendCount = "";
string DEXCashCount = "";
string Difference = "";
string NextScheduledVisitDays = "";
string NextScheduledVisitDay = "";
string NextVisitStockPrediction = "";
string MachineStockSold = "";
string DexNameDate = "";
string DaysSinceLastFill = "";
string MachineStockAt30In = "";
string OptimalFillDate = "";
string OptimalFillDay = "";
//Read each Dex File and retrieve data
foreach (string DexName in DexNames)
{
string[] DexNameData = DexName.Split('_', '.');
int DexPHYSID = Int32.Parse(DexNameData[0]);
string dexNameDate = DexNameData[1] + DexNameData[2];
try
{
//Marriage of Excel File Data and Dex File Data
if (Int32.Parse(MachinePHYSID) == DexPHYSID)
{
//Dex File Variable's
string MeterLine = "";
//Calculate location of each Dex File
string DexFilePath = DexFiles.ToString() + DexName;
//Read all of the Dex File's lines and add to an array
string[] DexLines = File.ReadAllLines(DexFilePath);
//Find Meter Read line and add to an array
foreach (string DexLine in DexLines)
{
MeterLine = Array.Find(DexLines,
element => element.StartsWith("VA1", StringComparison.Ordinal));
}
//Split data from Meter Read line
if (MeterLine != null)
{
string[] MeterReads = MeterLine.Split('*');
//Assign Dex values to Dex variables
DEXCashCount = MeterReads[1];
DEXVendCount = MeterReads[2];
}
DateTime creationDate = DateTime.ParseExact(dexNameDate, "yyyyMMddHHmmss", CultureInfo.InvariantCulture);
DateTime nextScheduledVisit = DateTime.ParseExact(NextScheduledVisit, "ddMMyy", CultureInfo.InvariantCulture);
TimeSpan scheduleDays = DateTime.Today - nextScheduledVisit;
TimeSpan LastVisitDays = DateTime.Today - DateTime.ParseExact(LastVisitDate, "ddMMyy", CultureInfo.InvariantCulture);
int Differential = Int32.Parse(DEXVendCount) - Int32.Parse(MEIVendCount);
int stockSold = Int32.Parse(Capacity) - Differential;
double percent = 0;
if (stockSold != 0)
{
percent = (double)(stockSold * 100)/Int32.Parse(Capacity);
}
else
{
percent = 0;
}
row["Machine Number"] = Int32.Parse(MachineNumber);
row["Customer"] = MachineLocation;
row["MEI Total Vend Count"] = Int32.Parse(MEIVendCount);
row["DEX Total Vend Count"] = Int32.Parse(DEXVendCount);
row["Stock Sold"] = Differential;
row["Capacity"] = Int32.Parse(Capacity);
row["Next Scheduled Visit Date"] = DateTime.ParseExact(NextScheduledVisit, "ddMMyy", CultureInfo.InvariantCulture);
row["Scheduled Visit In:"] = scheduleDays.Days.ToString() + " Days";
row["Scheduled Visit Day"] = nextScheduledVisit.DayOfWeek.ToString();
row["Next Visit Stock Prediction"] = "N/A";
row["Route Number"] = RouteName;
row["Route Driver Name"] = DriverName;
row["Current Stock %"] = percent.ToString() + " %";
row["Date/Time of DEX"] = creationDate;
row["Telemetry Provider"] = TelemetryProvider;
row["Days since last refill"] = LastVisitDays.Days.ToString() + " Days";
row["Sector"] = MachineSector;
row["Products"] = MachineModel;
row["Machine Type"] = MachineType;
dataTable.ImportRow(row);
}
}
catch(Exception e)
{
}
}
}
dataSet.Tables.Add(Data);
StringBuilder sb = new StringBuilder();
IEnumerable<string> columnNames = Data.Columns.Cast<DataColumn>().
Select(column1 => column.ColumnName);
sb.AppendLine(string.Join(",", columnNames));
foreach (DataRow row1 in Data.Rows)
{
IEnumerable<string> fields = row1.ItemArray.Select(field => field.ToString());
sb.AppendLine(string.Join(",", fields));
}
File.WriteAllText(@"\\DC01\Dev\Dexr\Excel Files\Output\test.csv", sb.ToString());
}
private void Load_Properties()
{
string configFile = "config.cfg";
string path = Path.Combine(Environment.CurrentDirectory, @"Data\", configFile);
}
}
}
答
需要声明数据表,并添加数据列只有一次在foreach循环之外。
//Prepare Datatable and Add All Columns Here
dataTable = new DataTable();
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "Machine Number";
column.ReadOnly = false;
column.Unique = true;
column.AutoIncrement = false;
//Excel Input and Dex File Data Marriage
foreach (string Line in ExcelLines)
{
//Add new row and assign values to columns, no need to add columns again and again in loop which will throw exception
row = dataTable.NewRow();
//Map all the values in the columns
row["ColumnName"]= value;
//At the end just add that row in datatable
dataTable.Rows.Add(row);
}
+0
这样做给了我一个csv文件,只有标题所有标记为机器类型 –
+0
谢谢,它是Data.ImportRow,这是行搞乱了,使用Data.Rows.Add工作 –
答
我看到你在foreach循环列添加到数据表的数据,每次 所以尽量循环
你只需要添加列一次,然后下面的所有线路外添加这些列。你正在为每一行添加列。 –