Tuesday, June 9, 2009

Writing Excel file with data set

here is the way by which we can create a excel file. here i hvae use a data set in which all the values are present which i want to write in the excel sheet.

first of all the column head is stored in an array and the value is present in the data set.  You just need to add the COM reference in the application using the Microsoft 12.0 Excel object library.
Use the name space to create the excel object.
                                           

string[] sExcelRow ={ "A", "B", "C", "D", "E", "F", "G", "H", "I",}
 Microsoft.Office.Interop.Excel.Sheets sheets;                 

                  Microsoft.Office.Interop.Excel.Workbooks workbooks = exc.Workbooks;

                  Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

                  sheets = workbook.Worksheets;

                  Microsoft.Office.Interop.Excel._Worksheet worksheet;
                  worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1);


   for (int i = 0; i <>
                  {
                      worksheet.get_Range(sExcelRow[i] + 1,  sExcelRow[i] + 1).Value2 = sExcelCol[i].ToString();
                      worksheet.get_Range(sExcelRow[i] + 1, sExcelRow[i] + 1).Font.Bold = true;
                      worksheet.get_Range(sExcelRow[i] + 1, sExcelRow[i] + 1).HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
                      worksheet.get_Range(sExcelRow[i] + 1, sExcelRow[i] + 1).EntireColumn.AutoFit();



  for (int j = 0; j <>
                  {
                      string strFieldStatus = string.Empty;
                      for (int l = 0; l <>
                      {
                      
                              worksheet.get_Range(sExcelRow[l] + x, sExcelRow[l] + x).Value2 = System.IO.Path.GetFileName(Convert.ToString(ldsUser1.Tables[0].Rows[j][l + 1]));
                              worksheet.get_Range(sExcelRow[l] + x, sExcelRow[l] + x).EntireColumn.AutoFit();
                              worksheet.get_Range(sExcelRow[l] + x, sExcelRow[l] + x).EntireColumn.NumberFormat = "@";

                        }


workbook.SaveAs(strFile1, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, Type.Missing, Type.Missing, Type.Missing);
 workbook.Close(true, Type.Missing, Type.Missing);



Anil Kumar Pandey
System Architect
Green Point Technology (India) Ltd.
Mumbai, Maharshtra
INDIA

Kontera