In c#.net i need to read input list and output list from excel

Hi

how to read data from excel to c#.net .Please let me.I have attached the sample document for reference.

In c#.net i need to read input list and output list.how to do .

Thank you inadvance
Book2.xlsx
ganeahdevAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
HI,
pls try

var xlApp=new Microsoft.Office.Interop.Excel.Application();
var wb=xlApp.Workbooks.Open(fn, ReadOnly: false);
xlApp.Visible=true;
var ws=wb.Worksheets[1] as Worksheet;
var r1=ws.Range["D6"].Resize[2, 10];
var array1=r1.Value;
var r2=ws.Range["D12"].Resize[4, 10];
var array2=r2.Value

Open in new window

Regards

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ganeahdevAuthor Commented:
Hi Rgonzo1971

Thank you it will work .

In c#.net i need to read input list and output list.These input value will pass through the wcf services.In wcf services calculation will be done and finally i need to show output list to my excel.

is it is possible to creat a collection or list to display result on excel

Regards
Rgonzo1971Commented:
the method I know is to use an array to populate a range

range.Value = arr;
ganeahdevAuthor Commented:
Hi Rgonzo1971,


Iam using DocumentFormat.OpenXml.dll. i have mention the code below i am getting error and not able to get result

How to display output in the excel after calculation completed.


private DataSet ReadExcel(string excelFile)
        {
            DataSet ds = CreateDataSet();
            //Open the excel file
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFile, true))
            {
                Workbook workbook = document.WorkbookPart.Workbook;
                SharedStringTable sharedStrings = null;
                if (document.WorkbookPart.SharedStringTablePart != null)
                    sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
                //Get all the sheets
                IEnumerable<Sheet> sheets = workbook.Descendants<Sheet>();

                foreach (Sheet sheet in sheets)
                {
                    WorksheetPart wsp = (WorksheetPart)document.WorkbookPart.GetPartById(sheet.Id);
                    //Get all the rows in the worksheet
                    IEnumerable<Row> dataRows = from row in wsp.Worksheet.Descendants<Row>()
                                                where row.RowIndex > 1
                                                select row;
                   
                    foreach (Row row in dataRows)
                    {
                        Collection<string> textValue = new Collection<string>();
                        // Get all the cells in the row
                        IEnumerable<Cell> cells = CreateExcelFile.GetEnumerator(row);

                        foreach (Cell cell in cells)
                        {
                            if (sharedStrings != null)
                            {
                                if (cell.ChildElements.Count > 0)
                                    textValue.Add(cell.DataType != null
                                        && cell.DataType.HasValue
                                        && cell.DataType == CellValues.SharedString
                                            ? sharedStrings.ChildElements[
                                            int.Parse(cell.CellValue.InnerText)].InnerText
                                            : cell.CellValue.InnerText);
                                else
                                    textValue.Add(string.Empty);
                            }
                            else
                                textValue.Add(cell.CellValue.InnerText);
                        }

                        switch (Convert.ToInt32(sheet.SheetId.Value))
                        {
                            case 1: ds.Tables[0].Rows.Add(textValue.ToArray());
                                break;
                            case 2: ds.Tables[1].Rows.Add(textValue.ToArray());
                                break;
                        }
                    }// end of rows in excel


foreach (Column col in dataRows)
                    {
                        Collection<string> textValue = new Collection<string>();
                        // Get all the cells in the row
                        IEnumerable<Cell> cells = CreateExcelFile.GetEnumerator(col);

                        foreach (Cell cell in cells)
                        {
                            if (sharedStrings != null)
                            {
                                if (cell.ChildElements.Count > 0)
                                    textValue.Add(cell.DataType != null
                                        && cell.DataType.HasValue
                                        && cell.DataType == CellValues.SharedString
                                            ? sharedStrings.ChildElements[
                                            int.Parse(cell.CellValue.InnerText)].InnerText
                                            : cell.CellValue.InnerText);
                                else
                                    textValue.Add(string.Empty);
                            }
                            else
                                textValue.Add(cell.CellValue.InnerText);
                        }

                        switch (Convert.ToInt32(sheet.SheetId.Value))
                        {
                            case 1: ds.Tables[0].Rows.Add(textValue.ToArray());
                                break;
                            case 2: ds.Tables[1].Rows.Add(textValue.ToArray());
                                break;
                        }
                    }// end of rows in excel                }
            } // close the spreadsheet document
            return ds;
        }
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.