How retrieve each specific column's values by

问题:

I am editing uploaded excel workbooks using C# with the same logic I used to do using VBA. I am using SyncFusion to open the workbooks but however, the code below is not letting me read the whole column to apply the logic. Why?

public void AppendID(string excelFilePath, HttpResponse response)
{

    using (ExcelEngine excelEngine = new ExcelEngine())
    {

        IApplication application = excelEngine.Excel;
        application.DefaultVersion = ExcelVersion.Excel2007;
        IWorkbook workbook = application.Workbooks.Open(excelFilePath);
        workbook.Version = ExcelVersion.Excel97to2003;
        workbook.Allow3DRangesInDataValidation = true;

        //Accessing worksheet via name

        IWorksheet worksheet = workbook.Worksheets[2];

When I try to define the range, the error will appear "Two names not allowed".

        var prismaID = worksheet.UsedRange["C15:C"].Value;
        var type = worksheet.UsedRange["F15:F"].Value;
        var placements = worksheet.UsedRange["I15:I"].Value;

        if (!type.Contains("PKG"))
        {
            placements = placements + prismaID;
        }

        worksheet.Range["G7"].Text = "Testing";

        workbook.SaveAs(excelFilePath);
        workbook.Close();

    }
}

Logic:

Let's say I have three columns and how to use the following logic to manipulate usedRange cells?

ID    Condition   Name        Output
1       Yes       Sarah        Sarah(1)
2       No        George       George
3       Yes       John(3)      John(3)

The logics to apply:

1. Move the first column 'ID' to the end of the column 'Name' but
2. if Column 'Condition' contains 'No'then don't move the first column
3. or if it contains the same 'ID' already.

Here is the VBA code:

With xlSheet  
    LastRow = xlSheet.UsedRange.Rows.Count
    Set target = .Range(.Cells(15, 9), .Cells(LastRow, 9))
    values = target.Value

    Set ptype=.Range(.Cells(15,6),.Cells(LastRow,6))
    pvalues=ptype.Value

    For i = LBound(values, 1) To UBound(values, 1)
        'if Statement for test keywords
        If InStr(1,pvalues(i,1),"Package")= 0 AND InStr(1,pvalues(i,1),"Roadblock")= 0 Then 
            If Instr(values(I,1),.Cells(i + 15 - LBound(values, 1), 3)) = 0 Then
            'If InStr(1,values(i,1),"(")=0 Then
                values(i, 1) = values(i, 1) & "(" & .Cells(i + 15 - LBound(values, 1), 3) & ")"
            End If 
        End If
    Next

    target.Value = values
End With

回答1:

Your requirement can be achieved by appending column ID with column Name using XlsIO.

Please refer below code snippet for the same.

Code Snippet:

for(int row = 1; row<= worksheet.Columns[1].Count; row++) 
    { 
        if (worksheet[row, 2].Value == "yes" && !worksheet[row, 3].Value.EndsWith(")")) 
            worksheet[row, 4].Value = worksheet[row, 3].Value + "(" + worksheet[row, 1].Value + ")"; 
        else 
            worksheet[row, 4].Value = worksheet[row, 3].Value; 
    } 

We have prepared simple sample and the sample can be downloaded from the following link.

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/Sample859524528.zip

I work for Syncfusion.

回答2:

So I am working with templates in excel, and I developed this logic.

I create a coupling of the first row of column names and the rows using the first cell as the key to bind the data in groups to a multi value dictionary. I use the below function, which can be adapted to skip rows before parsing allowing you to target the proper row for binding. Book is ExcelDataReader.AsDataSet()

    public static MultiValueDictionary<string, ILookup<string, string>> ParseTemplate(string Sheet, ref List<string> keys)
    {
        int xskip = 0;
        MultiValueDictionary<string, ILookup<string, string>> mvd = new MultiValueDictionary<string, ILookup<string, string>>();

        var sheetRows = Book.Tables[Sheet];
        //Parse First row
        var FirstRow = sheetRows.Rows[0];

        for (var Columns = 0; Columns < sheetRows.Columns.Count; Columns++)
        {
            if (xskip == 0)
            {
                xskip = 1;
                continue;
            }
            keys.Add(FirstRow[Columns].ToString());
        }
        //Skip First Row
        xskip = 0;
        //Create a binding of first row and all subsequent rows
        foreach (var row in sheetRows.Select().Skip(1))
        {
            //Make the key the first cell of each row
            var key = row[0];
            List<string> rows = new List<string>();
            foreach (var item in row.ItemArray)
            {
                if (xskip == 0)
                {
                    xskip = 1;
                    continue;
                }
                rows.Add(item.ToString());
            }
            mvd.Add(key.ToString(), keys.Zip(rows, (m, n) => new { Key = m, Value = n }).ToLookup(x => x.Key, y => y.Value));
            xskip = 0;
        }
        return mvd;
    }

}

//This is example of what a function to parse this could do.
foreach(var Key in mvd.Keys)
{
     var KeywithValues = mvd[Key];
     foreach(ColumnName in Keys)
     {
         KeywithValues[ColumnName].
     }
}

Hope it helps.