CATE ( Converting Access To Excel using C# Windows Application)

CATE ( Converting Access To Excel using C# Windows Application)

   5:09 PM       Marianz

---

This code snippet is used in converting access(.mdb,.accdb) to excel(.xls,xlsx);

int row = 1;
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
 
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
 
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Columns.EntireColumn.NumberFormat = "@";
xlWorkSheet.Columns.NumberFormat = "@";
 
xlWorkSheet.Columns.VerticalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Columns.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
 
OleDbConnection oledbConn = new OleDbConnection(mdbConn);
oledbConn.Open();
OleDbCommand thisCommand = oledbConn.CreateCommand();
thisCommand.CommandText = "SELECT " + mdbColumns.Remove(mdbColumns.Length - 2) + " FROM " + mdbTable + "";
OleDbDataReader dr = thisCommand.ExecuteReader();
while (dr.Read())
{
    row++;
    int col = 1;
    foreach (string listColumn in columnList)
    {
    ((Microsoft.Office.Interop.Excel.Range)xlWorkSheet.Cells[row, col]).Value2 = dr[listColumn].ToString();
    col++;
    }
bgWorker.ReportProgress(row);
}
 
dr.Close();
oledbConn.Close();
xlWorkBook.SaveAs(xlsFile + "\\" + xlsfilename + ".xls", 
    XlFileFormat.xlWorkbookNormal, misValue,
    misValue, misValue, misValue, 
    XlSaveAsAccessMode.xlExclusive,
    misValue, misValue, misValue, 
    misValue, misValue);
    
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
 
ImPixObject.ReleaseObject(xlWorkSheet);
ImPixObject.ReleaseObject(xlWorkBook);
ImPixObject.ReleaseObject(xlApp);