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);
}
public void BeginWrite()
{
WriteUshortArray(clBegin);
}
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>>();
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 ");
}
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