Tuesday, March 29, 2016

Read/Write operation on Excel using C#

First thing we need to take reference of below namespace:

using Excel = Microsoft.Office.Interop.Excel;

Classes used :
  • Excel.Application -Top level object in the Excel object model, used to specify application level properties and application level methods
  • Excel.Worbook - Represents a single workbook within the Excel application
  • Excel.Worksheet - A member of the Worksheets collection in the Workbook object 
Initializing the Class Variable:

MyApp = new Excel.Application();
MyApp.Visible = false;   // Run Excel in background.
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet)MyBook.Sheets[1];

// Explicit cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;

MySheet .Cells[1, 2] = "Percentage(%)"; // Here 1 is the rowIndex and 2 is the columnIndex


// Reading rows from used row.
 
 
//I presume you are actually looking for the last used row. In that case you need to write like this:

Range UsedRange = worksheet.UsedRange;
int lastUsedRow = UsedRange.Row + UsedRange.Rows.Count - 1;
 
  // Writing data into excel files.
        internal static void WriteToFile(Excel.Application myExcelApp,
  Excel.Workbook xlWorkBook,
  Excel.Worksheet xlWorkSheet,
  Dictionary<int, string> file1Diff,
  Dictionary<int, string> file2Diff)
        {
            //Difference between column/rows and cell
            int i= 2;  
            
            foreach (KeyValuePair<int, string> pair in file1Diff)
            {
                xlWorkSheet.UsedRange.Rows.Cells[i, 1] = pair.Key.ToString();
                xlWorkSheet.UsedRange.Rows.Cells[i, 2] = pair.Value.ToString();
                    i++;
            }
            i = 2;
            foreach (KeyValuePair<int, string> pair in file2Diff)
            {
                xlWorkSheet.UsedRange.Rows.Cells[i, 3] = pair.Key.ToString();
                xlWorkSheet.UsedRange.Rows.Cells[i, 4] = pair.Value.ToString();
                i++;
            }
            xlWorkBook.Save();
        }

 

No comments:

Post a Comment