first of all, I am very new to C#. I would like to select every row of my excel sheet and put it in a text doc. The problem is, that I only need certain columns(21 out of 70+).
Here is my code:
For example:
Excel:
|1 2 3 4 5
1|x y c v b
2|x y c v b
3|x y c v b
And I need every row 1 to 3 but only the data from column 2,3,5
In my text doc I want it to like like:
y c b
y c b
y c b
But atm it looks like:
y
y
y
c
c
c
b
b
b
int[] spalten = new int[] { 5, 22, 24, 27, 29, 32, 34, 37, 39, 43, 45, 48, 50, 54, 56, 59, 61, 65, 67, 71, 73 };
for (int x = 0; x <= 20; x++)
{
//loop all columns
for (int j = 4; j <= 74; j++)
{
//loop all rows
for (int i = 5; worksheet.Cells[i, 5].Value != null; i++)
{
//add the cell data to the List
if (j == spalten[x])
{
if (worksheet.Cells[i, j].Value == null)
{
Console.WriteLine("leer");
string Inhalt = "leer" + "\t";
string[] lines = { Inhalt };
File.AppendAllLines(Path.Combine(docPath, "Daten2.txt"), lines);
}
else
{
excelData.Add(worksheet.Cells[i, j].Value.ToString());
Console.WriteLine(worksheet.Cells[i, j].Value);
string Inhalt = worksheet.Cells[i, j].Value.ToString()+"\t";
string[] lines = { Inhalt };
File.AppendAllLines(Path.Combine(docPath, "Daten2.txt"), lines);
}
}
}
}
}
Change the order of your loops: loop over the rows first, then over the columns for the current row. Inside the inner loop, concatenate the column values into a single string.
For performance reasons, try to do as little work as possible inside the loop (e.g. do not access worksheet.Cells[] twice with the same indices). Use StringBuilder to concatenate strings. You can use foreach to loop over the configured columns only.
var configuredColumns = new int[] { 5, 22, 24, 27, 29, 32, 34, 37, 39, 43, 45, 48, 50, 54, 56, 59, 61, 65, 67, 71, 73 };
// loop over all data rows (ignore first 5 rows which are headers)
// stop looping if the current row has no data in column 5
var allRowTexts = new List<string>();
for (int row = 5; worksheet.Cells[row, 5].Value != null; row++) {
// loop over the configured columns
var rowText = new StringBuilder();
foreach (var col in configuredColumns) {
var cell = worksheet.Cells[row, col];
if (cell.Value == null) {
rowText.Append("leer" + "\t");
}
else {
rowText.Append(cell.Value.ToString() + "\t");
}
}
// rowText now contains all column values for the current row
allRowTexts.Add(rowText.ToString());
}
// write all rows into file
File.AppendAllLines(Path.Combine(docPath, "Daten2.txt"), allRowTexts);