Writing To Excel Files in C#

Question

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.

Vajda · Accepted Answer · 2012-03-25 13:47:19Z

2

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);
        }
    }

answered Mar 25, 2012 at 13:47

Vajda

1,7946 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pawan Kumar Over a year ago

Thanks Vajda, I have implemented the logic provided by you.ExcelRange2 = ExcelWorkSheet2.get_Range("A" + newFileRow, columnMaxName + newFileRow); ExcelRange2.Value2 = arrForValues;

siride · Accepted Answer · 2012-03-25 15:08:50Z

2

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.

edited Mar 25, 2012 at 15:08

answered Mar 25, 2012 at 13:42

siride

212k5 gold badges43 silver badges63 bronze badges

Comments

crokett · Accepted Answer · 2012-03-25 13:36:13Z

1

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.

answered Mar 25, 2012 at 13:36

crokett

1,2341 gold badge22 silver badges39 bronze badges

1 Comment

Pawan Kumar Over a year ago

Thanks david. I merged the cells in a range and then did a "range.value2=array".Got exactly what i wanted.

Collectives™ on Stack Overflow

Writing To Excel Files in C#

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related