Export data to excel file 2003, 2007 and Above

Question

I'm using these connection strings, depending on the extension of the file:

For 2003 : Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;

For 2007 : Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;

Here is to get the connection.

string con_excel = "";

        switch (Extension.ToLower())
        {
            case ".xls":
                con_excel = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                break;

            case ".xlsx":
                con_excel = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                break;
        }
        con_excel = con_excel.Replace("filename", filePath);

The following is the code to generate excel file.

        Excel.Application oXL;
        Excel._Workbook oWB;
        Excel._Worksheet oSheet;

        oXL = new Excel.Application();
        oXL.Visible = false;

        oXL.SheetsInNewWorkbook = 1;
        oWB = (Excel._Workbook)(oXL.Workbooks.Add());
        oSheet = (Excel._Worksheet)oWB.ActiveSheet;

        try
        {
            string[] colNames = new string[dataTable.Columns.Count];

            int col = 0;

            foreach (DataColumn dc in dataTable.Columns)
                colNames[col++] = dc.ColumnName;

            char lastColumn = (char)(65 + dataTable.Columns.Count - 1);

            oSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
            oSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
            oSheet.get_Range("A1", lastColumn + "1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;

            DataRow[] dr = dataTable.Select();

            string[,] rowData = new string[dr.Count<DataRow>(), dataTable.Columns.Count + 1];

            int rowCnt = 0;
            foreach (DataRow row in dr)
            {
                for (col = 0; col < dataTable.Columns.Count; col++)
                {
                    rowData[rowCnt, col] = row[col].ToString();
                }
                rowCnt++;
            }
            rowCnt++;
            oSheet.get_Range("A2", lastColumn + rowCnt.ToString()).Value = rowData;

            oXL.Visible = false;
            oXL.UserControl = true;

            String sNewFolderName = "Report_" + intReportId;
            filename = Server.MapPath("Your Report\\" + sNewFolderName + DateTime.Now.ToString("dd-MM-yyyy_hh-mm-ss") + Extension);
            oSheet.SaveAs(filename);

            System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);

            oXL.Quit();

            Marshal.ReleaseComObject(oSheet);
            Marshal.ReleaseComObject(oWB);
            Marshal.ReleaseComObject(oXL);

            oSheet = null;
            oWB = null;
            oXL = null;
            GC.GetTotalMemory(false);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.GetTotalMemory(true);

            //The excel is created and opened for insert value. We most close this excel using this system        
            Process[] localByName = Process.GetProcessesByName("EXCEL");
            foreach (Process process in localByName)
            {
                process.Kill();
            }

2007 file format is ok.

I tried to upload the 2003(.xls) excel file and then also generate 2003(.xls) format. But when i open that file, i got the following error.

The file you are trying to open "FileName.xls" in a different format than specified file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

Is this because of connection string ?

senthilkumar2185 senthilkumar2185 · Accepted Answer · 2013-11-26T04:17:21

Try this Code upload Excel 2003 and 2007 file

  if (filenam.ToString() == ".xls")
    { constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathnam + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; }
    else if (filenam.ToString() == ".xlsx")
    { constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathnam + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; }
    else { Response.Write("<script>alert('Load Excel file Only')</script>"); }
    string Qry = "SELECT [Customer], [Project], [Contact], [Designation], [Phone], [EmailID],[Region] FROM [Sheet1$]";
    OleDbConnection conn = new OleDbConnection(constr);
    if (conn.State == ConnectionState.Closed)
    {
    conn.Open();
    OleDbCommand cmd = new OleDbCommand(Qry, conn);
    OleDbDataAdapter da = new OleDbDataAdapter();
    da.SelectCommand = cmd;
    DataTable dt = new DataTable();
    da.Fill(dt);
    if (dt != null && dt.Rows.Count > 0)
    {
    gv_upload.DataSource = dt;
    gv_upload.DataBind();
    }
    da.Dispose(); conn.Close(); conn.Dispose();

Export data to excel file 2003, 2007 and Above

1 Answers