C＃代码只在excel中写入第一列数据 [英] C# code write only first column data in excel
问题描述
你好朋友......
我参与了一个项目，我需要编写代码来读取excel文件并将数据保存在另一个excel文件中。但是，Source excel文件可以包含任意数量的列，即Somtetimes它可能包含3个文件列，或者有时它可能包含8个文件列，例如folows
Col- A Col-B Col-C
1 3 5
2 4 6
源Excel的列文件是动态的。但我知道他们的数据类型。一切都是字符串。这些列可以包含任意数量的值。所有列彼此之间具有固定数量的行。假设所有列可能有2000行。我需要将目标文件中的列保存为低于其他列的值，如下所示。
列值
Col-A 1
Col-A 2
Col-B 3
Col-B 4
Col-C 5
Col-C 6
这个我参考下面的链接
生成Excel文件而不使用Microsoft Excel [ ^ ]
根据我在课程后创建的链接
Hello Friends...
I m involved in project in which i need to code for reading excel file and save the data in another excel file. But Source excel file can contain any number of columns i.e Somtetimes it may contain 3 columns in file or sometimes it may contain 8 columns in file etc such as folows
Col-A Col-B Col-C
1 3 5
2 4 6
The columns of Source Excel file are dynamic. But I know their datatypes. All are string. And these columns can contain any number of values. All columns have a fixed number of rows to each other. Suppose All columns may have a 2000 rows. I need to save columns in destination file as one below other with values such as follows.
Columns Values
Col-A 1
Col-A 2
Col-B 3
Col-B 4
Col-C 5
Col-C 6
for this i refer the link as below
Generate Excel files without using Microsoft Excel[^]
As per the link i have created following class
class ExcelWriter
{
private Stream stream;
private BinaryWriter writer;
private ushort[] clBegin = { 0x0809, 8, 0, 0x10, 0, 0 };
private ushort[] clEnd = { 0x0A, 00 };
private void WriteUshortArray(ushort[] value)
{
for (int i = 0; i < value.Length; i++)
writer.Write(value[i]);
}
public ExcelWriter(Stream stream)
{
this.stream = stream;
writer = new BinaryWriter(stream);
}
public void WriteCell(int row, int col, string value)
{
ushort[] clData = { 0x0204, 0, 0, 0, 0, 0 };
int iLen = value.Length;
byte[] plainText = Encoding.ASCII.GetBytes(value);
clData[1] = (ushort)(8 + iLen);
clData[2] = (ushort)row;
clData[3] = (ushort)col;
clData[5] = (ushort)iLen;
WriteUshortArray(clData);
writer.Write(plainText);
}
public void WriteCell(int row, int col)
{
ushort[] clData = { 0x0201, 6, 0, 0, 0x17 };
clData[2] = (ushort)row;
clData[3] = (ushort)col;
WriteUshortArray(clData);
}
/// <summary>
/// Must be called once for creating XLS file header
/// </summary>
public void BeginWrite()
{
WriteUshortArray(clBegin);
}
/// <summary>
/// Ends the writing operation, but do not close the stream
/// </summary>
public void EndWrite()
{
WriteUshortArray(clEnd);
writer.Flush();
}
}
private void button1_Click(object sender, EventArgs e)
{
string _filename = @"E:\Import File\Working\t_C15.csv";
StreamReader ObjStreamReader = new StreamReader(File.OpenRead(_filename));
string[] line = ObjStreamReader.ReadLine().Split(';');
string[] machinename = _filename.Split('_');
for (int i = 2; i < line.Length; i++)
{
line[i] = string.Concat(line[i].Trim('"'), "_" ,machinename[machinename.Length - 1].Remove(machinename[machinename.Length - 1].IndexOf(".csv")));
}
List<system.collections.generic.list><string>> ObjListColumns = new List<list><string>>();
//here
for (int i = 1; i < line.Length; i++)
{
ObjListColumns.Add(new List<string>());
}
string[] values = null;
while (!ObjStreamReader.EndOfStream)
{
values = null;
values = ObjStreamReader.ReadLine().Split(';');
for (int i = 0; i < ObjListColumns.Count; i++)
{
ObjListColumns[i].Add(values[i + 1]);
}
}
ObjStreamReader.Close();
for (int i = 0; i < ObjListColumns[0].Count; i++)
{
ObjListColumns[0][i] = ObjListColumns[0][i].Trim('"').Trim('\\').Trim('"');
}
for (int i = 0; i < ObjListColumns.Count; i++)
{
for (int j = 0; j < ObjListColumns[i].Count; j++)
{
ObjListColumns[i][j] = ObjListColumns[i][j].Trim('"');
}
}
FileStream stream = new FileStream(@"E:\Import File\Working\abc.csv", FileMode.OpenOrCreate);
ExcelWriter writer = new ExcelWriter(stream);
writer.BeginWrite();
writer.WriteCell(0, 0, "[Data]");
writer.WriteCell(1, 0, "Tagname");
writer.WriteCell(1, 1, "TimeStamp");
writer.WriteCell(1, 2, "Value");
writer.WriteCell(1, 3, "DataQuality");
DateTime starttime = DateTime.Now;
for (int i = 2; i < line.Length; i++)
{
for (int j = 0; j < ObjListColumns[i-1].Count; j++)
{
writer.WriteCell(j + 2, 0, line[i].ToString());
writer.WriteCell(j + 2, 1, ObjListColumns[0][j].ToString());
writer.WriteCell(j + 2, 2, ObjListColumns[i - 1][j].ToString());
writer.WriteCell(j + 2, 3, "Good");
}
}
writer.EndWrite();
stream.Close();
DateTime endtime = DateTime.Now;
MessageBox.Show("Compeleted in ");
}
但它只写下第一列数据如下，
列值
Col-A 1
Col-A
未写入更多列数据。请帮帮我
But It writes only first column data as follows,
Columns Values
Col-A 1
Col-A 2
further columns data are not being written.Please help me
推荐答案
我希望这有帮助
http://www.codeproject.com/ Tips / 613782 / Read-and-Write-Excel-File-Dynamically
I hope this helps
http://www.codeproject.com/Tips/613782/Read-and-Write-Excel-File-Dynamically
你只是在阅读文件的第一行。
You are only reading the first line in the file.
string[] line = ObjStreamReader.ReadLine().Split(';');
在循环中发出ReadLine（将其分配给行）并处理结果，直到它返回一个空的sting []数组。
Issue ReadLine in a loop(assigning it to line) and processing the result, till it returns an empty sting[] array.
看起来你的问题太复杂了。实际上.csv并不是一个很好的东西，它是一个windows常规，意思是逗号分隔的谓词或类似于linces的东西，意味着它是一个包含行值的行的文件，用逗号分隔。
现在你正在使用分号，因为windows使用环境表中的ListSeperator，这是特定于文化的，比如在DK中我们使用分号，因为我们已经在数字上使用了数字，而在英国使用了数字之间的点，因此实际上可以使用逗号。
无论如何，如果你认为你不需要做任何伏都教而只需要在那里做一个文件由excel文件关联偶然偷走的格式，它将在Windows中为您提供Excel图标，因此可以在您的设置中工作，考虑像这样的简单方法。
Appears you're overcomplicating the matter. In fact .csv is not an excel thing, it's a windows convention meaning something like 'comma seperated verdices' or something along those linces and means that it is a file containing rows with column values seperated by commas.
Now you're using semicolon and that's because windows use the ListSeperator from the Environment table which is culture specific, like say in DK we'd use semicolon because we have already spent our comma on numbers with digits, whilst in UK the use the dot between digits and therefore can actually use a comma.
Anyway, if you consider that you don't have to do any voodoo and only must make a file in that format which incidentally is 'stolen' by excel file association which will give you an Excel icon in windows and therefore will work in your setup, consider a simper approach like thisone.
public class ExcelBot
{
public static void Reorganize(string source, string destination, Encoding encoding, char seperator = ';')
{
string[] columns;
List<StringBuilder> columnData;
string content;
using (var fs = new FileStream(source, FileMode.Open, FileAccess.Read))
{
var reader = new StreamReader(fs, encoding);
string header = reader.ReadLine();
if (string.IsNullOrEmpty(header))
throw new ArgumentException("Source doesn't appear to have the expected format: " + source);
columns = header.Split(seperator);
columnData = new List<StringBuilder>();
foreach (var clmn in columns)
{
columnData.Add(new StringBuilder());
}
content = reader.ReadToEnd();
}
string[] rows = content.Split("\n".ToCharArray());
for (int i = 0; i < rows.Length; i++)
{
string[] rowColumns = rows[i].TrimEnd().Split(seperator);
if (rowColumns.Length < columns.Length)
continue;
for (int j = 0; j < columns.Length; j++)
{
columnData[j].AppendLine(columns[j] + seperator + rowColumns[j]);
}
}
var sb = new StringBuilder();
sb.AppendLine("Column" + seperator + "Value");
foreach (var builder in columnData)
sb.Append(builder.ToString());
using (var fs = new FileStream(destination, FileMode.Create))
{
var writer = new StreamWriter(fs, encoding);
writer.Write(sb.ToString());
writer.Flush();
}
}
}
这篇关于C＃代码只在excel中写入第一列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
