CODEWITHSUNDEEP
c#excel
Pawan Kumar
Pawan Kumar

Reputation: 247

Writing To Excel Files in C#

I have a Excel File, i am able to read single row from "Excel file" Cell by Cell and store it in a ArrayList. ExcelRange reads one row at a time , stores it into ArrayList (arrForValues).

ExcelRange = ExcelWorkSheet.get_Range("A"+rowNumber,columnMaxName+rowNumber );
items = (object[,])ExcelRange.Value2;

for (int i = 1; i <= nColumn; i++)
{
    arrForValues.Add(items[1, i]);
}

I want to write row to another Excel file.There is some condition which needs to be satisfied for "particular Row" to get selected for writing.

Is there any way i can write complete ArrayList("Single Row") to ExcelFile instead of Cell By Cell Wrinting.

Thanks in Advance.

Upvotes: 1

Views: 6962

Answers (3)

siride
siride

Reputation: 210015

I highly recommend just getting FlexCel. It's fairly cheap and it has methods for copying rows and columns.

EDIT: I see that you mean to copy between workbooks. It's still easier with FlexCel than COM or the Interop stuff.

Upvotes: 2

Vajda
Vajda

Reputation: 1794

You can write whole array of objects by using range's set_value method

Here is example:

class Program
    {
        static void Main(string[] args)
        {
            string file = AppDomain.CurrentDomain.BaseDirectory + "SetArrayToExcel.xlsx";
            Excel.Application excelApp = new Excel.Application();
            excelApp.Visible = true;
            Excel.Workbook wb = excelApp.Workbooks.Open(file, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing
                , Type.Missing, Type.Missing);
            object[,] testTable = new object[2, 2]{{"6-Feb-10", 0.1}, {"26-Mar-10", 1.2}};
            Excel.Worksheet ws = wb.ActiveSheet as Excel.Worksheet;
            Excel.Range rng = ws.get_Range("rngSetValue", Type.Missing);


            //rng.Value2 = testTable;
            rng.set_Value(Type.Missing, testTable);
        }
    }

Upvotes: 2

David Green
David Green

Reputation: 1234

I do some writing to Excel in some code I have. The only way I have found to do it is with a foreach to iterate through my list of values and using an indexer to keep track of what cell it is going into. You might investigate the Range class and the Cells property: http://msdn.microsoft.com/en-US/library/microsoft.office.tools.excel.namedrange.cells(v=vs.80).aspx

It didn't work for what I am doing but it might for you.

Another alternative would be to merge the cells in a range, build a string of all the values in your array and set the merged range value equal to that string but that might not be what you want.

Upvotes: 1

Related Questions